255 lines
15 KiB
SQL
255 lines
15 KiB
SQL
-- =============================================================================
|
||
-- 域 2：执行域（7 张）+ 设备域（4 张）= 11 张
|
||
-- 来源：公寓端/03 表 3-3 / 3-4；字段口径见 公寓端/03 表 3-9、表 3-11、表 3-15
|
||
-- 说明：gy_jcxx 存量 70 列（docs/11 表 2-1），此处按接口契约给出核心列 + CR-02 表 3-32
|
||
-- 两处补列；其余存量列在阶段二冻结后由 07_patch_columns.sql 幂等补齐。
|
||
-- 硬约束：ryzt / finish_jc / finish_ly / stoped 只有 apt.duty.state 一个写入口，
|
||
-- SQL 必须带 gyid 与条件更新（where id=? and ryzt=?），禁止直接 UPDATE。
|
||
-- =============================================================================
|
||
|
||
-- 12 叫乘主表（执行事实 + 业务终态）
|
||
CREATE TABLE IF NOT EXISTS gy_jcxx (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
jcid VARCHAR(64) NOT NULL COMMENT '叫乘 ID',
|
||
jlid VARCHAR(64) NULL,
|
||
cc VARCHAR(64) NULL,
|
||
ryid VARCHAR(64) NULL,
|
||
xm VARCHAR(64) NULL,
|
||
fjh VARCHAR(32) NULL,
|
||
kcsj DATETIME(3) NULL COMMENT '开车时刻',
|
||
jcsj DATETIME(3) NULL COMMENT '叫乘时刻（公寓复算，权威）',
|
||
jcsj_platform DATETIME(3) NULL COMMENT '平台计划基线（仅对照）',
|
||
ryzt VARCHAR(8) NOT NULL DEFAULT '0' COMMENT '叫乘状态 0~5（存量数字码，不跨端）',
|
||
finish_jc VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '0 未完成 / 1 已完成',
|
||
finish_ly VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '0 在寓 / 1 已离寓',
|
||
stoped VARCHAR(2) NOT NULL DEFAULT '0' COMMENT '人工停止',
|
||
sjlysj DATETIME(3) NULL COMMENT '实际离寓时间；与 finish_ly 共同判「已入住」',
|
||
call_mode VARCHAR(20) NOT NULL DEFAULT 'SIP' COMMENT 'CR-02 表 3-32 补列：SIP / TABLET',
|
||
dispatch_seq BIGINT NULL COMMENT 'CR-02 表 3-32 补列：平板派发序号',
|
||
biz_version BIGINT NOT NULL DEFAULT 1 COMMENT '条件更新乐观锁',
|
||
occurred_at DATETIME(3) NULL COMMENT '业务发生时刻（不判日切）',
|
||
received_at DATETIME(3) NULL COMMENT '本地入库时刻',
|
||
platform_recv_at DATETIME(3) NULL COMMENT '平台接收时刻（归日以此为准）',
|
||
deleted TINYINT NOT NULL DEFAULT 0,
|
||
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
update_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_jcxx_gyid_jcid (gyid, jcid),
|
||
KEY idx_jcxx_gyid_time (gyid, jcsj),
|
||
KEY idx_jcxx_gyid_state (gyid, ryzt, finish_ly),
|
||
KEY idx_jcxx_gyid_seq (gyid, dispatch_seq),
|
||
CONSTRAINT ck_jcxx_ryzt CHECK (ryzt IN ('0','1','2','3','4','5')),
|
||
CONSTRAINT ck_jcxx_finish_jc CHECK (finish_jc IN ('0','1')),
|
||
CONSTRAINT ck_jcxx_finish_ly CHECK (finish_ly IN ('0','1')),
|
||
CONSTRAINT ck_jcxx_stoped CHECK (stoped IN ('0','1')),
|
||
CONSTRAINT ck_jcxx_mode CHECK (call_mode IN ('SIP','TABLET'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='叫乘主表；状态字段单写入口 apt.duty.state';
|
||
|
||
-- 13 叫乘归档（只读，#13 ARCHIVE 摘要来源）
|
||
CREATE TABLE IF NOT EXISTS gy_jcxx_his (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
jcid VARCHAR(64) NOT NULL,
|
||
jlid VARCHAR(64) NULL,
|
||
cc VARCHAR(64) NULL,
|
||
ryid VARCHAR(64) NULL,
|
||
xm VARCHAR(64) NULL,
|
||
fjh VARCHAR(32) NULL,
|
||
jcsj DATETIME(3) NULL,
|
||
ryzt VARCHAR(8) NULL,
|
||
finish_jc VARCHAR(2) NULL,
|
||
finish_ly VARCHAR(2) NULL,
|
||
sjlysj DATETIME(3) NULL,
|
||
archive_batch VARCHAR(64) NULL,
|
||
archived_at DATETIME(3) NULL,
|
||
summary_payload JSON NULL COMMENT '§15 分组计数 satisfaction{} / roomOccupancy{}',
|
||
deleted TINYINT NOT NULL DEFAULT 0,
|
||
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
update_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_jcxxhis_gyid_jcid (gyid, jcid),
|
||
KEY idx_jcxxhis_gyid_date (gyid, archived_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='叫乘归档历史 [推断]';
|
||
|
||
-- 14 离寓/入住事件（设备与人工登记事实）
|
||
CREATE TABLE IF NOT EXISTS gygl_ly_event (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
event_id VARCHAR(64) NOT NULL COMMENT '事件幂等键',
|
||
event_type VARCHAR(20) NOT NULL COMMENT 'CHECKIN / CHECKOUT',
|
||
event_source VARCHAR(20) NOT NULL COMMENT 'DEVICE / FACE / MANUAL',
|
||
jcid VARCHAR(64) NULL,
|
||
ryid VARCHAR(64) NULL,
|
||
fjh VARCHAR(32) NULL,
|
||
occurred_at DATETIME(3) NOT NULL,
|
||
received_at DATETIME(3) NULL,
|
||
platform_recv_at DATETIME(3) NULL,
|
||
report_state VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING/SENT/FAILED/DEAD/RETRIED',
|
||
deleted TINYINT NOT NULL DEFAULT 0,
|
||
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
update_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_lye_gyid_event (gyid, event_id),
|
||
KEY idx_lye_gyid_type_time (gyid, event_type, occurred_at),
|
||
CONSTRAINT ck_lye_type CHECK (event_type IN ('CHECKIN','CHECKOUT')),
|
||
CONSTRAINT ck_lye_source CHECK (event_source IN ('DEVICE','FACE','MANUAL')),
|
||
CONSTRAINT ck_lye_report CHECK (report_state IN ('PENDING','SENT','FAILED','DEAD','RETRIED'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='离寓/入住事件 [推断+§13]';
|
||
|
||
-- 15 房态（唯一写入口 apt-stay，条件更新带 state_version）
|
||
CREATE TABLE IF NOT EXISTS gy_rooms (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
room_id VARCHAR(64) NOT NULL,
|
||
room_no VARCHAR(32) NOT NULL,
|
||
room_floor VARCHAR(32) NULL,
|
||
room_status VARCHAR(8) NOT NULL DEFAULT '1' COMMENT '1 空闲/2 住满/3 待清扫/4 不能入住/5 未满/6 保洁中',
|
||
in_person_count INT NOT NULL DEFAULT 0,
|
||
state_version BIGINT NOT NULL DEFAULT 1 COMMENT '乐观锁：where id=? and state_version=?',
|
||
disabled_reason VARCHAR(256) NULL,
|
||
deleted TINYINT NOT NULL DEFAULT 0,
|
||
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
update_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_rooms_gyid_room (gyid, room_id),
|
||
KEY idx_rooms_gyid_status (gyid, room_status, room_floor),
|
||
CONSTRAINT ck_rooms_status CHECK (room_status IN ('1','2','3','4','5','6'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='房间与房态（apt-stay 写入）';
|
||
|
||
-- 16 保洁任务
|
||
CREATE TABLE IF NOT EXISTS gy_clean_task (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
task_id VARCHAR(64) NOT NULL,
|
||
room_id VARCHAR(64) NULL,
|
||
state VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING/PROCESSING/DONE/CANCELLED',
|
||
assignee_ref VARCHAR(64) NULL,
|
||
scheduled_at DATETIME(3) NULL,
|
||
finished_at DATETIME(3) NULL,
|
||
deleted TINYINT NOT NULL DEFAULT 0,
|
||
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
update_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_cleantask_gyid_task (gyid, task_id),
|
||
KEY idx_cleantask_gyid_state (gyid, state, scheduled_at),
|
||
CONSTRAINT ck_cleantask_state CHECK (state IN ('PENDING','PROCESSING','DONE','CANCELLED'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='保洁任务 [推断]';
|
||
|
||
-- 17 报警记录
|
||
CREATE TABLE IF NOT EXISTS gy_bjxx (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
alarm_id VARCHAR(64) NOT NULL,
|
||
jcid VARCHAR(64) NULL,
|
||
room_id VARCHAR(64) NULL,
|
||
alarm_type VARCHAR(32) NOT NULL,
|
||
severity VARCHAR(20) NOT NULL DEFAULT 'WARN',
|
||
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',
|
||
deleted TINYINT NOT NULL DEFAULT 0,
|
||
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
update_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_bjxx_gyid_alarm (gyid, alarm_id),
|
||
KEY idx_bjxx_gyid_type_time (gyid, alarm_type, occurred_at),
|
||
KEY idx_bjxx_gyid_handled (gyid, handled_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='报警记录 [推断]';
|
||
|
||
-- 18 报警日汇总（#13 摘要来源）
|
||
CREATE TABLE IF NOT EXISTS gy_bjcstj (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
stat_date DATE NOT NULL,
|
||
alarm_count INT NOT NULL DEFAULT 0,
|
||
handled_count INT NOT NULL DEFAULT 0,
|
||
report_state VARCHAR(20) NOT NULL DEFAULT 'PENDING',
|
||
deleted TINYINT NOT NULL DEFAULT 0,
|
||
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
update_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_bjcstj_gyid_date (gyid, stat_date)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='报警日汇总 [推断]';
|
||
|
||
-- 19 设备档案（含 §12 人脸设备范围补列）
|
||
CREATE TABLE IF NOT EXISTS gy_sb (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
device_code VARCHAR(64) NOT NULL COMMENT '设备编码，入库大写',
|
||
device_type VARCHAR(32) NOT NULL COMMENT 'TABLET / FACE / SIP / BROADCAST',
|
||
room_id VARCHAR(64) NULL,
|
||
state VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
|
||
face_scope_type VARCHAR(20) NULL COMMENT '§12：设备人脸范围类型',
|
||
face_scope_ref VARCHAR(128) NULL COMMENT '§12：设备人脸范围引用',
|
||
last_seen_at DATETIME(3) NULL,
|
||
biz_version BIGINT NOT NULL DEFAULT 1,
|
||
deleted TINYINT NOT NULL DEFAULT 0,
|
||
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
update_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_sb_gyid_code (gyid, device_code),
|
||
KEY idx_sb_gyid_type_state (gyid, device_type, state),
|
||
CONSTRAINT ck_sb_state CHECK (state IN ('ACTIVE','DISABLED','OFFLINE'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='设备档案（含 §12 补列）';
|
||
|
||
-- 20 设备心跳与在线状态
|
||
CREATE TABLE IF NOT EXISTS gy_sb_xt (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
device_code VARCHAR(64) NOT NULL,
|
||
online_state VARCHAR(20) NOT NULL DEFAULT 'OFFLINE',
|
||
app_version VARCHAR(32) NULL,
|
||
ip_ref VARCHAR(64) NULL COMMENT '只存引用，不落内网地址',
|
||
heartbeat_at DATETIME(3) NULL,
|
||
deleted TINYINT NOT NULL DEFAULT 0,
|
||
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
update_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_sbxt_gyid_code (gyid, device_code),
|
||
KEY idx_sbxt_gyid_state (gyid, online_state, heartbeat_at),
|
||
CONSTRAINT ck_sbxt_state CHECK (online_state IN ('ONLINE','OFFLINE','UNKNOWN'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='设备心跳 [推断]';
|
||
|
||
-- 21 设备历史指令（存量兼容，只读，不新增写入）
|
||
CREATE TABLE IF NOT EXISTS gy_sb_zl (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
command_id VARCHAR(64) NOT NULL,
|
||
device_code VARCHAR(64) NULL,
|
||
command_type VARCHAR(32) NULL,
|
||
state VARCHAR(20) NULL,
|
||
occurred_at DATETIME(3) NULL,
|
||
deleted TINYINT NOT NULL DEFAULT 0,
|
||
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_sbzl_gyid_cmd (gyid, command_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='设备历史指令（只读存量兼容）[推断]';
|
||
|
||
-- 22 设备指令队列（只入队 PENDING，无心跳不伪造 ACKED）
|
||
CREATE TABLE IF NOT EXISTS apt_device_command (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
command_id VARCHAR(64) NOT NULL COMMENT '命令幂等键',
|
||
device_code VARCHAR(64) NOT NULL,
|
||
command_type VARCHAR(32) NOT NULL COMMENT 'FACE_ISSUE/FACE_ERASE/FACE_FULL_SYNC/PLAY_TASK',
|
||
payload_json JSON NULL,
|
||
state VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING/SENT/ACKED/EXPIRED/FAILED',
|
||
protocol_confirmed TINYINT NOT NULL DEFAULT 0 COMMENT '默认 0，设备 ACK 前不置 1',
|
||
face_scope_ref VARCHAR(128) NULL COMMENT '§12 补列',
|
||
sent_at DATETIME(3) NULL,
|
||
acked_at DATETIME(3) NULL,
|
||
expire_at DATETIME(3) NULL,
|
||
last_error VARCHAR(512) NULL,
|
||
deleted TINYINT NOT NULL DEFAULT 0,
|
||
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
update_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (id),
|
||
UNIQUE KEY uk_devcmd_gyid_cmd (gyid, command_id),
|
||
KEY idx_devcmd_gyid_state (gyid, state, create_time),
|
||
CONSTRAINT ck_devcmd_state CHECK (state IN ('PENDING','SENT','ACKED','EXPIRED','FAILED'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='设备指令队列（§12 补列）';
|