-- =====================================================================
-- V19 · 中控屏 P2（实时性与完整性）
-- panel_event(家庭增量事件) / ota_attempt(单设备升级尝试) / panel_configuration(配置下发)
-- product_property 扩展(semantic/channel/completionMode) / firmware 扩展(packageType/targetModel/minVersion)
-- 手工迁移（Flyway 历史版本冲突，不自动跑）；幂等，可重复执行。
-- =====================================================================

DELIMITER $$

-- 复用 V18 的加列过程（V18 内已 DROP，这里重建）
DROP PROCEDURE IF EXISTS add_panel_col$$
CREATE PROCEDURE add_panel_col(IN col VARCHAR(64), IN def TEXT)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = 'iot_platform' AND TABLE_NAME = 'device' AND COLUMN_NAME = col
  ) THEN
    SET @s = CONCAT('ALTER TABLE device ADD COLUMN ', col, ' ', def);
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END$$

DROP PROCEDURE IF EXISTS add_pp_col$$
CREATE PROCEDURE add_pp_col(IN col VARCHAR(64), IN def TEXT)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = 'iot_platform' AND TABLE_NAME = 'product_property' AND COLUMN_NAME = col
  ) THEN
    SET @s = CONCAT('ALTER TABLE product_property ADD COLUMN ', col, ' ', def);
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END$$

DROP PROCEDURE IF EXISTS add_fw_col$$
CREATE PROCEDURE add_fw_col(IN col VARCHAR(64), IN def TEXT)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = 'iot_platform' AND TABLE_NAME = 'firmware' AND COLUMN_NAME = col
  ) THEN
    SET @s = CONCAT('ALTER TABLE firmware ADD COLUMN ', col, ' ', def);
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END$$

DELIMITER ;

-- ===== panel_event：家庭增量事件（长轮询游标 = id） =====
CREATE TABLE IF NOT EXISTS panel_event (
  id          BIGINT       NOT NULL AUTO_INCREMENT,
  home_id     BIGINT       NOT NULL,
  panel_id    BIGINT       DEFAULT NULL COMMENT '目标屏(空=家庭内所有屏)',
  device_id   VARCHAR(64)  DEFAULT NULL,
  event_type  VARCHAR(32)  NOT NULL COMMENT 'DEVICE_STATE/COMMAND_RESULT/DEVICE_REGISTERED/DEVICE_OFFLINE/CONFIG_CHANGED/SCENE_RUN',
  payload     TEXT         DEFAULT NULL COMMENT 'JSON',
  create_time DATETIME(3)  DEFAULT CURRENT_TIMESTAMP(3),
  update_time DATETIME(3)  DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  tenant_id   BIGINT       DEFAULT NULL,
  create_by   BIGINT       DEFAULT NULL,
  update_by   BIGINT       DEFAULT NULL,
  deleted     TINYINT      DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_event_home (home_id, id),
  KEY idx_event_panel (panel_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='中控屏家庭增量事件';

-- ===== ota_attempt：单设备升级尝试（替代任务聚合计数，支持幂等上报） =====
CREATE TABLE IF NOT EXISTS ota_attempt (
  id            BIGINT       NOT NULL AUTO_INCREMENT,
  ota_task_id   BIGINT       NOT NULL,
  home_id       BIGINT       DEFAULT NULL,
  device_id     VARCHAR(64)  NOT NULL,
  attempt_id    VARCHAR(64)  DEFAULT NULL COMMENT '屏侧幂等键',
  report_id     VARCHAR(64)  DEFAULT NULL,
  report_seq    BIGINT       DEFAULT 0,
  stage         VARCHAR(32)  DEFAULT 'pending' COMMENT 'pending/downloading/installing/success/failed',
  progress      INT          DEFAULT 0,
  status        VARCHAR(16)  DEFAULT 'pending' COMMENT 'pending/success/failed',
  error_code    VARCHAR(32)  DEFAULT NULL,
  message       VARCHAR(255) DEFAULT NULL,
  old_version   VARCHAR(32)  DEFAULT NULL,
  new_version   VARCHAR(32)  DEFAULT NULL,
  create_time   DATETIME(3)  DEFAULT CURRENT_TIMESTAMP(3),
  update_time   DATETIME(3)  DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  tenant_id     BIGINT       DEFAULT NULL,
  create_by     BIGINT       DEFAULT NULL,
  update_by     BIGINT       DEFAULT NULL,
  deleted       TINYINT      DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uk_attempt (ota_task_id, device_id),
  KEY idx_attempt_device (device_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='单设备 OTA 升级尝试';

-- ===== panel_configuration：屏配置下发 =====
CREATE TABLE IF NOT EXISTS panel_configuration (
  id          BIGINT       NOT NULL AUTO_INCREMENT,
  panel_id    BIGINT       NOT NULL,
  version     BIGINT       NOT NULL DEFAULT 1 COMMENT '每次更新递增',
  config_json TEXT         NOT NULL COMMENT 'JSON 配置',
  checksum    VARCHAR(64)  DEFAULT NULL,
  create_time DATETIME(3)  DEFAULT CURRENT_TIMESTAMP(3),
  update_time DATETIME(3)  DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  tenant_id   BIGINT       DEFAULT NULL,
  create_by   BIGINT       DEFAULT NULL,
  update_by   BIGINT       DEFAULT NULL,
  deleted     TINYINT      DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uk_panel_cfg (panel_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='中控屏配置下发';

-- ===== product_property 语义字段 =====
CALL add_pp_col('semantic', "VARCHAR(32) DEFAULT NULL COMMENT '语义标签 dimmer/thermostat/lock/cover 等'");
CALL add_pp_col('channel', "VARCHAR(32) DEFAULT NULL COMMENT '物理通道 zigbee/matter/ir 等'");
CALL add_pp_col('completionMode', "VARCHAR(16) DEFAULT NULL COMMENT 'action 完成模式 sync/async'");

-- ===== firmware 扩展 =====
CALL add_fw_col('package_type', "VARCHAR(32) DEFAULT NULL COMMENT 'full/delta'");
CALL add_fw_col('target_model', "VARCHAR(64) DEFAULT NULL COMMENT '适用机型'");
CALL add_fw_col('min_version', "VARCHAR(32) DEFAULT NULL COMMENT '最低可升级版本'");

DROP PROCEDURE IF EXISTS add_panel_col;
DROP PROCEDURE IF EXISTS add_pp_col;
DROP PROCEDURE IF EXISTS add_fw_col;