197 lines
11 KiB
SQL
197 lines
11 KiB
SQL
-- =============================================================================
|
||
-- 域 6：CR-02 新增业务表 T-01~T-08（8 张）
|
||
-- 列级定义来源：CR-02 表 3-27（T-01~T-03）、表 3-28（T-04/T-05/T-08）、
|
||
-- 表 3-29（T-06/T-07）；§12 表 10-6 的补列已合并进 T-04
|
||
-- 迁移顺序：按 CR-02 表 3-33，本域在存量 44 张之后执行；DDL 与回填分离。
|
||
-- =============================================================================
|
||
|
||
-- 52 T-01 平板设备
|
||
CREATE TABLE IF NOT EXISTS apt_tablet_device (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
device_code VARCHAR(64) NOT NULL COMMENT '设备编码，入库大写',
|
||
room_id VARCHAR(64) NULL,
|
||
activation_code_ref VARCHAR(128) NULL COMMENT '激活码只存引用，不存明文',
|
||
register_status VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT 'ACTIVE/DISABLED/PENDING',
|
||
app_version VARCHAR(32) NULL,
|
||
last_seen_at DATETIME(3) NULL,
|
||
last_cursor VARCHAR(128) NULL,
|
||
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_tabletdev_gyid_code (gyid, device_code),
|
||
CONSTRAINT ck_tabletdev_status CHECK (register_status IN ('ACTIVE','DISABLED','PENDING'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='T-01 平板设备';
|
||
|
||
-- 53 T-02 平板任务（按 jcsj 顺序派发，dispatch_seq 单调递增）
|
||
CREATE TABLE IF NOT EXISTS apt_tablet_task (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
task_id VARCHAR(64) NOT NULL,
|
||
jcid VARCHAR(64) NOT NULL,
|
||
dispatch_seq BIGINT NOT NULL,
|
||
call_mode VARCHAR(20) NOT NULL DEFAULT 'TABLET',
|
||
jcsj DATETIME(3) NULL COMMENT '叫乘基准时刻（本地复算值）',
|
||
device_code VARCHAR(64) NULL,
|
||
pull_state VARCHAR(20) NOT NULL DEFAULT 'WAITING' COMMENT 'WAITING/PULLED/EXPIRED/DONE',
|
||
pulled_at DATETIME(3) NULL,
|
||
pulled_by VARCHAR(64) NULL,
|
||
expire_at DATETIME(3) NULL,
|
||
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_tablettask_gyid_task (gyid, task_id),
|
||
KEY idx_tablettask_gyid_seq (gyid, dispatch_seq),
|
||
KEY idx_tablettask_gyid_time (gyid, jcsj),
|
||
CONSTRAINT ck_tablettask_pull CHECK (pull_state IN ('WAITING','PULLED','EXPIRED','DONE'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='T-02 平板派发任务';
|
||
|
||
-- 54 T-03 平板叫乘结果（result_id 幂等；平板链路不发 #24）
|
||
CREATE TABLE IF NOT EXISTS apt_tablet_call_result (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
result_id VARCHAR(64) NOT NULL COMMENT '幂等键',
|
||
task_id VARCHAR(64) NOT NULL,
|
||
jcid VARCHAR(64) NOT NULL,
|
||
face_verify_state VARCHAR(20) NOT NULL COMMENT 'VERIFIED/UNVERIFIED/SKIPPED',
|
||
verified_ryid VARCHAR(64) NULL,
|
||
result_type VARCHAR(20) NOT NULL COMMENT 'ANSWERED/NO_ANSWER/FAILED/CANCELLED',
|
||
person_count INT NOT NULL DEFAULT 0,
|
||
device_code VARCHAR(64) NOT NULL,
|
||
occurred_at DATETIME(3) NOT NULL,
|
||
received_at DATETIME(3) NOT NULL,
|
||
applied_state VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING/APPLIED/IGNORED',
|
||
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_tabletresult_gyid_id (gyid, result_id),
|
||
KEY idx_tabletresult_gyid_task (gyid, task_id),
|
||
KEY idx_tabletresult_gyid_state (gyid, applied_state, occurred_at),
|
||
CONSTRAINT ck_tabletresult_face CHECK (face_verify_state IN ('VERIFIED','UNVERIFIED','SKIPPED')),
|
||
CONSTRAINT ck_tabletresult_type CHECK (result_type IN ('ANSWERED','NO_ANSWER','FAILED','CANCELLED')),
|
||
CONSTRAINT ck_tabletresult_applied CHECK (applied_state IN ('PENDING','APPLIED','IGNORED'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='T-03 平板叫乘结果';
|
||
|
||
-- 55 T-04 人脸注册申请（生效点在公寓；平台回执不决定本地生效）
|
||
-- 合并 公寓端/10 表 10-6 补列：双路状态链、媒体引用、person_category
|
||
CREATE TABLE IF NOT EXISTS apt_person_face_reg (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
reg_id VARCHAR(64) NOT NULL,
|
||
work_no VARCHAR(64) NULL,
|
||
name VARCHAR(64) NOT NULL,
|
||
face_feature_ref VARCHAR(256) NOT NULL COMMENT '不存特征大对象',
|
||
photo_ref VARCHAR(512) NULL COMMENT '不存原图二进制',
|
||
photo_checksum VARCHAR(128) NULL,
|
||
feature_checksum VARCHAR(128) NULL,
|
||
feature_algo VARCHAR(32) NULL,
|
||
feature_version BIGINT NOT NULL DEFAULT 1,
|
||
person_category VARCHAR(20) NOT NULL DEFAULT 'TRANSIENT' COMMENT 'TRANSIENT / LONG_TERM',
|
||
reg_state VARCHAR(20) NOT NULL DEFAULT 'APPLIED' COMMENT 'APPLIED/CONFIRMED/REJECTED/ARCHIVED',
|
||
local_only TINYINT(1) NOT NULL DEFAULT 1,
|
||
verdict VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT '平台同步回执 ACCEPTED/REJECTED',
|
||
confirmed_ryid VARCHAR(64) NULL,
|
||
reject_reason VARCHAR(256) NULL,
|
||
verdict_version BIGINT NULL,
|
||
apply_device_code VARCHAR(64) NULL,
|
||
applied_at DATETIME(3) NULL,
|
||
confirmed_by VARCHAR(64) NULL,
|
||
confirmed_at DATETIME(3) NULL,
|
||
upload_state VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT '上传平台状态链（独立）',
|
||
dispatch_state VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT '下发设备状态链（独立）',
|
||
face_sync_state VARCHAR(20) NOT NULL DEFAULT 'PENDING',
|
||
registered_by VARCHAR(64) NULL,
|
||
device_code VARCHAR(64) NULL,
|
||
occurred_at DATETIME(3) NOT NULL,
|
||
report_state VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING/SENT/FAILED/DEAD/RETRIED',
|
||
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_facereg_gyid_reg (gyid, reg_id),
|
||
KEY idx_facereg_gyid_state (gyid, reg_state, occurred_at),
|
||
KEY idx_facereg_gyid_workno (gyid, work_no),
|
||
CONSTRAINT ck_facereg_state CHECK (reg_state IN ('APPLIED','CONFIRMED','REJECTED','ARCHIVED')),
|
||
CONSTRAINT ck_facereg_verdict CHECK (verdict IN ('PENDING','ACCEPTED','REJECTED')),
|
||
CONSTRAINT ck_facereg_category CHECK (person_category IN ('TRANSIENT','LONG_TERM')),
|
||
CONSTRAINT ck_facereg_upload CHECK (upload_state IN ('PENDING','SENT','FAILED','DEAD','ACKED')),
|
||
CONSTRAINT ck_facereg_dispatch CHECK (dispatch_state IN ('PENDING','ISSUED','FAILED','DEAD','ACKED')),
|
||
CONSTRAINT ck_facereg_report CHECK (report_state IN ('PENDING','SENT','FAILED','DEAD','RETRIED'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='T-04 人脸注册申请（§12 补列已合并）';
|
||
|
||
-- 56 T-05 换乘单（CR-02 表 3-28）
|
||
CREATE TABLE IF NOT EXISTS apt_transfer_order (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
transfer_id VARCHAR(64) NOT NULL,
|
||
jcid VARCHAR(64) NOT NULL,
|
||
jlid VARCHAR(64) NOT NULL,
|
||
old_room_id VARCHAR(64) NULL,
|
||
new_room_id VARCHAR(64) NULL,
|
||
new_dep_time DATETIME(3) NOT NULL,
|
||
new_call_time DATETIME(3) NOT NULL,
|
||
recomputed_call_time DATETIME(3) NOT NULL COMMENT '与 new_call_time 不等时告警，不静默覆盖',
|
||
state VARCHAR(20) NOT NULL DEFAULT 'DRAFT',
|
||
command_id VARCHAR(64) NULL,
|
||
reason VARCHAR(256) NULL,
|
||
biz_version BIGINT NOT NULL DEFAULT 1,
|
||
applied_at DATETIME(3) NULL,
|
||
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_transfer_gyid_id (gyid, transfer_id),
|
||
KEY idx_transfer_gyid_jcid (gyid, jcid)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='T-05 换乘单';
|
||
|
||
-- 57 T-06 保洁人员
|
||
CREATE TABLE IF NOT EXISTS apt_clean_staff (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
staff_id VARCHAR(64) NOT NULL,
|
||
name VARCHAR(64) NOT NULL,
|
||
state VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
|
||
phone_ref VARCHAR(128) NULL COMMENT '联系方式只存引用',
|
||
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_cleanstaff_gyid_id (gyid, staff_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='T-06 保洁人员';
|
||
|
||
-- 58 T-07 保洁记录
|
||
CREATE TABLE IF NOT EXISTS apt_clean_record (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
record_id VARCHAR(64) NOT NULL,
|
||
staff_id VARCHAR(64) NOT NULL,
|
||
room_id VARCHAR(64) NOT NULL,
|
||
clean_type VARCHAR(20) NOT NULL,
|
||
state VARCHAR(20) NOT NULL,
|
||
task_id VARCHAR(64) NULL,
|
||
started_at DATETIME(3) NULL,
|
||
finished_at DATETIME(3) NULL,
|
||
remark VARCHAR(256) NULL,
|
||
report_state VARCHAR(20) NOT NULL DEFAULT 'PENDING',
|
||
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_cleanrecord_gyid_id (gyid, record_id),
|
||
KEY idx_cleanrecord_gyid_room (gyid, room_id, started_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='T-07 保洁记录';
|
||
|
||
-- 59 T-08 设备报警（CR-02 表 3-28；alarm_event_key 与 /ws/duty 去重一致）
|
||
CREATE TABLE IF NOT EXISTS apt_device_alarm (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
alarm_id VARCHAR(64) NOT NULL,
|
||
device_code VARCHAR(64) NOT NULL,
|
||
alarm_source VARCHAR(20) NOT NULL DEFAULT 'DEVICE',
|
||
alarm_type VARCHAR(20) NOT NULL,
|
||
severity VARCHAR(20) NOT NULL DEFAULT 'WARN',
|
||
alarm_event_key VARCHAR(128) NOT NULL,
|
||
occurred_at DATETIME(3) NOT NULL,
|
||
handled_at DATETIME(3) NULL,
|
||
handled_by VARCHAR(64) NULL,
|
||
handled_result VARCHAR(256) NULL,
|
||
biz_version BIGINT NOT NULL DEFAULT 1,
|
||
report_state VARCHAR(20) NOT NULL DEFAULT 'PENDING',
|
||
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_devalarm_gyid_id (gyid, alarm_id),
|
||
UNIQUE KEY uk_devalarm_gyid_key (gyid, alarm_event_key),
|
||
KEY idx_devalarm_gyid_state (gyid, alarm_type, occurred_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='T-08 设备报警';
|