-- =========================================================
-- Watermon – ESP8266 + PHP/MySQL (boias 25/50/75/100)
-- Script de criação do banco e tabelas (MySQL 5.7/8.0)
-- =========================================================

CREATE DATABASE IF NOT EXISTS watermon
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE watermon;

CREATE TABLE IF NOT EXISTS devices (
  id              VARCHAR(64)  PRIMARY KEY,
  name            VARCHAR(100) NOT NULL,
  token           CHAR(64)     NOT NULL,
  created_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS readings (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  device_id      VARCHAR(64)  NOT NULL,
  level_percent  DECIMAL(5,2) NOT NULL,
  estado_sensor  TINYINT      NULL,
  created_at     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_readings_device
    FOREIGN KEY (device_id) REFERENCES devices(id)
    ON DELETE CASCADE ON UPDATE CASCADE,

  INDEX idx_readings_device (device_id),
  INDEX idx_readings_created (created_at),
  INDEX idx_readings_level   (level_percent)
) ENGINE=InnoDB;

-- Exemplo: cadastre dois dispositivos
INSERT INTO devices (id, name, token)
VALUES
  ('esp8266-01', 'Tanque A', SHA2('segredo-a', 256)),
  ('esp8266-02', 'Tanque B', SHA2('segredo-b', 256))
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  token = VALUES(token);

-- (Opcional) Usuário dedicado ao app
-- CREATE USER 'watermon_user'@'localhost' IDENTIFIED BY 'SuaSenhaForteAqui!';
-- GRANT SELECT, INSERT, UPDATE, DELETE ON watermon.* TO 'watermon_user'@'localhost';
-- FLUSH PRIVILEGES;

-- (Opcional) Limpeza de dados antigos (via CRON/Event Scheduler)
-- DELETE FROM readings WHERE created_at < NOW() - INTERVAL 180 DAY;
