schema.sql

-- Database schema for disc-cloud-tracker
-- Compatible with MySQL 5.7+ / MariaDB 10.3+

CREATE DATABASE IF NOT EXISTS disc
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_general_ci;

USE disc;

CREATE TABLE `mesures` (
  `id` int NOT NULL AUTO_INCREMENT,
  `V1` decimal(10,2) DEFAULT NULL,
  `V2` decimal(10,2) DEFAULT NULL,
  `POW` decimal(10,2) DEFAULT NULL,
  `TEMP` decimal(10,2) DEFAULT NULL,
  `R1` decimal(10,2) DEFAULT NULL,
  `GSM_DATA` text,
  `SERIAL_DATA_LATITUDE` decimal(10,6) DEFAULT NULL,
  `LATITUDE` decimal(10,6) DEFAULT NULL,
  `LONGITUDE` decimal(10,6) DEFAULT NULL,
  `ACPLUG` int DEFAULT NULL,
  `DISC` varchar(25) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ADRESSE` mediumtext,
  PRIMARY KEY (`id`),
  KEY `DISC` (`DISC`),
  KEY `timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Optional view: latest measurement per device (equivalent to what json.php does)
CREATE OR REPLACE VIEW `check_disc` AS
  SELECT m1.*
  FROM mesures m1
  INNER JOIN (
    SELECT DISC, MAX(timestamp) AS max_ts
    FROM mesures
    GROUP BY DISC
  ) m2 ON m1.DISC = m2.DISC AND m1.timestamp = m2.max_ts;

-- Optional dedicated user with minimal privileges
-- CREATE USER 'disc'@'%' IDENTIFIED BY 'CHANGE_ME';
-- GRANT SELECT, INSERT ON disc.* TO 'disc'@'%';
-- FLUSH PRIVILEGES;