-- V18 中控屏直连接入（P1 一期） -- 对应文档：docs/10寸中控屏/05-评审回复与落地方案.md -- -- 说明： -- 1) 生产库 device 表的 parent_device_id / is_gateway 两列已存在（历史手工补列）， -- 本次不再重复 ADD；新环境请确认 init.sql 建表语句中已包含这两列。 -- 2) 本文件按团队现状以手动方式执行（Flyway 历史版本号存在重复）。 -- 3) 各表均包含 BaseEntity 审计列（create_time/update_time/deleted/tenant_id/create_by/update_by）。 -- ============ 中控屏终端身份 ============ CREATE TABLE IF NOT EXISTS panel_terminal ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT '主键', serial_number VARCHAR(64) NOT NULL COMMENT '屏序列号(与预置终端记录核对)', installation_id VARCHAR(64) DEFAULT NULL COMMENT '当前应用安装实例ID', bootstrap_secret_hash VARCHAR(128) NOT NULL COMMENT '终端初始化凭证 BCrypt 哈希', home_id BIGINT DEFAULT NULL COMMENT '绑定的家庭ID', member_id BIGINT DEFAULT NULL COMMENT '关联的会员ID(业务上下文)', gateway_device_id VARCHAR(64) DEFAULT NULL COMMENT '对应的网关设备 deviceId', role VARCHAR(16) NOT NULL DEFAULT 'GATEWAY' COMMENT 'GATEWAY / DISPLAY', enabled TINYINT NOT NULL DEFAULT 1 COMMENT '1启用 0禁用', features VARCHAR(512) DEFAULT NULL COMMENT '支持的协议/功能(JSON数组文本)', last_seen_at DATETIME DEFAULT NULL COMMENT '最近心跳时间', last_ip VARCHAR(64) DEFAULT NULL COMMENT '最近来源IP', current_boot_id VARCHAR(64) DEFAULT NULL COMMENT '当前有效启动实例', state_epoch BIGINT NOT NULL DEFAULT 0 COMMENT '状态世代(bootId 变更时递增)', app_version VARCHAR(32) DEFAULT NULL COMMENT '应用版本', model VARCHAR(64) DEFAULT NULL COMMENT '屏型号', create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', deleted TINYINT DEFAULT 0 COMMENT '逻辑删除 0未删 1已删', tenant_id BIGINT DEFAULT NULL COMMENT '租户ID', create_by BIGINT DEFAULT NULL COMMENT '创建人', update_by BIGINT DEFAULT NULL COMMENT '更新人', PRIMARY KEY (id), UNIQUE KEY uk_panel_serial (serial_number) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='中控屏终端身份'; -- ============ 中控屏刷新令牌（支持轮换与撤销） ============ CREATE TABLE IF NOT EXISTS panel_refresh_token ( id BIGINT NOT NULL AUTO_INCREMENT, panel_id BIGINT NOT NULL COMMENT 'panel_terminal.id', token_hash VARCHAR(128) NOT NULL COMMENT 'refresh token 的 SHA-256 哈希', boot_id VARCHAR(64) DEFAULT NULL COMMENT '签发时的启动实例', expires_at DATETIME NOT NULL COMMENT '过期时间', revoked TINYINT NOT NULL DEFAULT 0 COMMENT '1已撤销/已轮换', create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', deleted TINYINT DEFAULT 0 COMMENT '逻辑删除', tenant_id BIGINT DEFAULT NULL COMMENT '租户ID', create_by BIGINT DEFAULT NULL COMMENT '创建人', update_by BIGINT DEFAULT NULL COMMENT '更新人', PRIMARY KEY (id), UNIQUE KEY uk_panel_refresh_hash (token_hash), KEY idx_panel_refresh_panel (panel_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='中控屏刷新令牌(支持轮换与撤销)'; -- ============ 设备控制命令单据（状态机，支持中控屏异步执行回执） ============ CREATE TABLE IF NOT EXISTS device_command ( id BIGINT NOT NULL AUTO_INCREMENT, command_id VARCHAR(64) NOT NULL COMMENT '对外命令ID', device_id VARCHAR(64) NOT NULL COMMENT '平台业务设备ID', panel_id BIGINT DEFAULT NULL COMMENT '执行屏(execution_source=LOCAL_PANEL 时)', home_id BIGINT DEFAULT NULL COMMENT '所属家庭', command_type VARCHAR(16) NOT NULL COMMENT 'set / action', params_json TEXT COMMENT '命令参数(JSON)', execution_source VARCHAR(16) NOT NULL DEFAULT 'CLOUD' COMMENT 'CLOUD / LOCAL_PANEL', state VARCHAR(24) NOT NULL DEFAULT 'QUEUED' COMMENT 'QUEUED/DELIVERED/EXECUTING/ACCEPTED/CONFIRMED/PARTIAL_FAILURE/FAILED/EXPIRED/CANCELLED/UNKNOWN', terminal TINYINT NOT NULL DEFAULT 0 COMMENT '是否终态', route_revision BIGINT DEFAULT 0 COMMENT '执行归属版本', schema_revision BIGINT DEFAULT 0 COMMENT '依据的物模型版本', completion_mode VARCHAR(16) DEFAULT 'STATE' COMMENT 'STATE / ACK_ONLY', delivery_token VARCHAR(128) DEFAULT NULL COMMENT '投递凭证(一次性)', delivery_expires_at DATETIME DEFAULT NULL COMMENT '投递租期截止', execution_token VARCHAR(128) DEFAULT NULL COMMENT '执行凭证', expires_at DATETIME NOT NULL COMMENT '命令总有效期截止', result_json TEXT COMMENT '执行结果与证据(JSON)', report_seq BIGINT DEFAULT 0 COMMENT '已接受的最大回报序号', error_code VARCHAR(64) DEFAULT NULL COMMENT '结构化错误码', error_message VARCHAR(512) DEFAULT NULL COMMENT '错误说明', create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', deleted TINYINT DEFAULT 0 COMMENT '逻辑删除', tenant_id BIGINT DEFAULT NULL COMMENT '租户ID', create_by BIGINT DEFAULT NULL COMMENT '创建人', update_by BIGINT DEFAULT NULL COMMENT '更新人', PRIMARY KEY (id), UNIQUE KEY uk_command_id (command_id), KEY idx_cmd_device (device_id), KEY idx_cmd_panel_state (panel_id, state) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备控制命令单据(支持中控屏异步执行回执)'; -- ============ 设备属性级当前状态（带序号，防止旧状态覆盖新状态） ============ CREATE TABLE IF NOT EXISTS device_state ( id BIGINT NOT NULL AUTO_INCREMENT, device_id VARCHAR(64) NOT NULL COMMENT '平台业务设备ID', property_code VARCHAR(64) NOT NULL COMMENT '属性码', property_value VARCHAR(512) DEFAULT NULL COMMENT '属性值(文本化存储)', state_epoch BIGINT NOT NULL DEFAULT 0 COMMENT '屏端状态世代', sequence BIGINT NOT NULL DEFAULT 0 COMMENT '该世代内设备级递增序号', quality VARCHAR(16) DEFAULT 'GOOD' COMMENT 'GOOD / STALE / UNAVAILABLE', reported_at DATETIME DEFAULT NULL COMMENT '屏观测时间', received_at DATETIME DEFAULT NULL COMMENT '平台接收时间', create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', deleted TINYINT DEFAULT 0 COMMENT '逻辑删除', tenant_id BIGINT DEFAULT NULL COMMENT '租户ID', create_by BIGINT DEFAULT NULL COMMENT '创建人', update_by BIGINT DEFAULT NULL COMMENT '更新人', PRIMARY KEY (id), UNIQUE KEY uk_state_device_prop (device_id, property_code), KEY idx_state_device (device_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备属性级当前状态(带序号)'; -- ============ 幂等记录（保留 7 天） ============ CREATE TABLE IF NOT EXISTS idempotency_record ( id BIGINT NOT NULL AUTO_INCREMENT, scope_key VARCHAR(128) NOT NULL COMMENT '范围键: 租户+调用主体+方法+规范化路径', idempotency_key VARCHAR(128) NOT NULL COMMENT '客户端幂等键', request_hash VARCHAR(128) NOT NULL COMMENT '请求内容指纹(用于冲突检测)', response_json TEXT COMMENT '首次响应快照(用于重放)', expires_at DATETIME NOT NULL COMMENT '过期时间(默认7天)', create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', deleted TINYINT DEFAULT 0 COMMENT '逻辑删除', tenant_id BIGINT DEFAULT NULL COMMENT '租户ID', create_by BIGINT DEFAULT NULL COMMENT '创建人', update_by BIGINT DEFAULT NULL COMMENT '更新人', PRIMARY KEY (id), UNIQUE KEY uk_idem_scope_key (scope_key, idempotency_key), KEY idx_idem_expires (expires_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='幂等记录(保留7天)'; -- ============ device 表新增中控屏相关列（幂等，可重复执行） ============ DELIMITER $$ 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$$ DELIMITER ; CALL add_panel_col('executor_panel_id', "BIGINT DEFAULT NULL COMMENT '执行屏ID(LOCAL_PANEL 时)'"); CALL add_panel_col('route_revision', "BIGINT DEFAULT 0 COMMENT '执行归属版本'"); CALL add_panel_col('schema_revision', "BIGINT DEFAULT 0 COMMENT '设备级物模型版本'"); CALL add_panel_col('local_device_id', "VARCHAR(64) DEFAULT NULL COMMENT '屏内稳定设备ID'"); CALL add_panel_col('protocol', "VARCHAR(32) DEFAULT NULL COMMENT 'MATTER/ZIGBEE/BLE_GATT/BLUETOOTH_MESH'"); CALL add_panel_col('execution_source', "VARCHAR(16) DEFAULT 'CLOUD' COMMENT 'CLOUD / LOCAL_PANEL'"); CALL add_panel_col('schema_json', "TEXT COMMENT '设备级标准控制模型(JSON，中控屏登记/更新时上报)'"); DROP PROCEDURE IF EXISTS add_panel_col; SET @idx = (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'iot_platform' AND TABLE_NAME = 'device' AND INDEX_NAME = 'idx_device_executor'); SET @s = IF(@idx = 0, 'CREATE INDEX idx_device_executor ON device(executor_panel_id)', 'SELECT 1'); PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;