-- =====================================================================
-- V20 · 中控屏 P3（体验与运营）
-- scene_run(场景执行单据) / panel_diagnostic(诊断事件)
-- 手工迁移（Flyway 历史版本冲突，不自动跑）；幂等，可重复执行。
-- =====================================================================

-- ===== scene_run：场景执行单据（云端场景涉及本地执行屏时，屏上报执行结果） =====
CREATE TABLE IF NOT EXISTS scene_run (
  id               BIGINT       NOT NULL AUTO_INCREMENT,
  scene_id         BIGINT       NOT NULL,
  home_id          BIGINT       NOT NULL,
  trigger_source   VARCHAR(32)  DEFAULT 'manual' COMMENT 'manual/schedule/voice/automation',
  status           VARCHAR(16)  DEFAULT 'pending' COMMENT 'pending/running/partial/success/failed',
  execute_panel_id BIGINT       DEFAULT NULL COMMENT '负责本地设备执行的屏',
  attempt_id       VARCHAR(64)  DEFAULT NULL COMMENT '屏侧幂等键',
  report_id        VARCHAR(64)  DEFAULT NULL,
  report_seq       BIGINT       DEFAULT 0,
  result_json      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_scene_run_home (home_id, id),
  KEY idx_scene_run_scene (scene_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='场景执行单据';

-- ===== panel_diagnostic：屏诊断事件 =====
CREATE TABLE IF NOT EXISTS panel_diagnostic (
  id          BIGINT       NOT NULL AUTO_INCREMENT,
  panel_id    BIGINT       NOT NULL,
  boot_id     VARCHAR(64)  DEFAULT NULL,
  level       VARCHAR(16)  DEFAULT 'INFO' COMMENT 'INFO/WARN/ERROR',
  category    VARCHAR(32)  DEFAULT NULL COMMENT 'network/mqtt/ota/command/config',
  message     TEXT         DEFAULT NULL,
  data_json   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_diag_panel (panel_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='中控屏诊断事件';