-- =============================================================================
-- schema_sht30.sql - SHT30 온습도 모니터링 시스템 신규 설치용 통합 스키마
--
-- 신규 설치: 이 파일 하나만 실행합니다.
--   SOURCE /path/to/sql/schema_sht30.sql;
--
-- 기존(누수 v2605) 설치를 SHT30 전용으로 전환할 때는 이 파일이 아니라
-- sql/migration_drop_leak.sql 을 사용합니다(데이터 보존 + 누수 잔재 정리).
--
-- 누수(leak) 관련 요소는 포함하지 않습니다:
--   - 테이블: leak_photo, leak_incident
--   - 컬럼:  sensor_log.is_leak, sensor_status.is_leak, leak_periodic_count
--   - 이벤트: event_type 의 leak_alert / leak_recovery
-- 온습도 임계 경보(고온/저온/고습/저습) 판정과 SMS 발송은 서버 PHP(config.php
-- 의 METRIC_* 상수 + api/sensor_data.php)에서 수행하며, 발송 이력은 sms_log 에
-- 남습니다. 별도의 경보 상태 테이블은 두지 않습니다(sms_log 기준 쿨다운/복구 판정).
-- =============================================================================

SET NAMES utf8mb4;
SET time_zone = '+09:00';

CREATE TABLE IF NOT EXISTS `sensor_log` (
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `device_id`       VARCHAR(64)     NOT NULL COMMENT 'STM32 기기 ID',
    `device_location` VARCHAR(100)    NOT NULL DEFAULT '' COMMENT '설치 위치',
    `sensor_id`       TINYINT UNSIGNED NOT NULL COMMENT '센서 번호',
    `sensor_name`     VARCHAR(100)    NOT NULL DEFAULT '' COMMENT '센서 이름',
    `event_type`      ENUM('startup','periodic')
                      NOT NULL DEFAULT 'periodic',
    `sensor_time`     DATETIME        NOT NULL COMMENT '센서 측정 시각 (기기 기준)',
    `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'DB 저장 시각',

    PRIMARY KEY (`id`),
    INDEX `idx_sensor_time` (`sensor_time`),
    INDEX `idx_device_sensor` (`device_id`, `sensor_id`),
    INDEX `idx_event_type` (`event_type`, `created_at`)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='센서 이벤트 전체 로그';

CREATE TABLE IF NOT EXISTS `sensor_status` (
    `sensor_id`       TINYINT UNSIGNED NOT NULL,
    `device_id`       VARCHAR(64)      NOT NULL,
    `sensor_name`     VARCHAR(100)     NOT NULL DEFAULT '',
    `device_location` VARCHAR(100)     NOT NULL DEFAULT '',
    `last_event_type` VARCHAR(20)      NOT NULL DEFAULT '',
    `last_seen`       DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP
                       ON UPDATE CURRENT_TIMESTAMP,
    `offline_alerted` TINYINT(1)       NOT NULL DEFAULT 0
                       COMMENT '오프라인 알림 발송 여부 (1=발송됨, 0=미발송)',

    PRIMARY KEY (`sensor_id`)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='센서별 최신 상태 (업서트)';

CREATE TABLE IF NOT EXISTS `sensor_metric` (
    `id`                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `device_id`          VARCHAR(64)      NOT NULL DEFAULT '',
    `device_location`    VARCHAR(100)     NOT NULL DEFAULT '',
    `sensor_id`          TINYINT UNSIGNED NOT NULL,
    `sensor_name`        VARCHAR(100)     NOT NULL DEFAULT '',
    `metric_type`        VARCHAR(30)      NOT NULL DEFAULT 'sht30',
    `temperature_c`      DECIMAL(6,2)     NULL,
    `humidity_percent`   DECIMAL(5,2)     NULL,
    `metric_status`      VARCHAR(30)      NOT NULL DEFAULT 'normal'
                          COMMENT '서버 임계 판정 결과: normal/high_temp/low_temp/high_humidity/low_humidity',
    `measured_at`        DATETIME         NOT NULL,
    `created_at`         DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    INDEX `idx_sensor_measured` (`sensor_id`, `measured_at`),
    INDEX `idx_device_sensor` (`device_id`, `sensor_id`),
    INDEX `idx_metric_type` (`metric_type`, `created_at`),
    INDEX `idx_status_measured` (`metric_status`, `measured_at`)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='SHT30 온습도 측정 이력 (온도/습도/임계 상태)';

CREATE TABLE IF NOT EXISTS `sms_log` (
    `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `sensor_id`      TINYINT UNSIGNED NOT NULL,
    `sensor_name`    VARCHAR(100)   NOT NULL DEFAULT '',
    `device_id`      VARCHAR(64)    NOT NULL DEFAULT '',
    `message`        TEXT           NOT NULL,
    `receivers`      VARCHAR(500)   NOT NULL COMMENT '쉼표 구분 수신자 번호',
    `status`         ENUM('success','fail') NOT NULL,
    `result_code`    VARCHAR(20)    NOT NULL DEFAULT '',
    `result_message` VARCHAR(255)   NOT NULL DEFAULT '',
    `sent_at`        DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    INDEX `idx_sent_at` (`sent_at`),
    INDEX `idx_sensor_sent` (`sensor_id`, `sent_at`)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='SMS 발송 이력 (온습도 임계 경보/복구, 장비 오프라인/복구)';