261 lines
16 KiB
SQL
261 lines
16 KiB
SQL
-- =============================================================================
|
||
-- 域 1：主数据快照 + 计划任务 + 本地建单（11 张）
|
||
-- 来源：公寓端/03 表 3-1（7）、表 3-2（3）、CR-01 表 1-17（apt_local_task）
|
||
-- 列级定义：apt_local_task 见 CR-01 表 1-17 + 公寓端/11 表 11-7；
|
||
-- 其余 10 张文档只给「表名/owner/写入口」，字段按接口契约与
|
||
-- 公寓端/03 表 3-9/3-15 通用规则推断（标注 [推断]），待阶段二冻结评审。
|
||
-- =============================================================================
|
||
|
||
-- 1 主数据版本（platform-down，只读快照；Mock 时 source='LOCAL'）
|
||
CREATE TABLE IF NOT EXISTS apt_master_version (
|
||
id BIGINT NOT NULL AUTO_INCREMENT COMMENT '代理主键',
|
||
gyid VARCHAR(32) NOT NULL COMMENT '公寓隔离标识',
|
||
domain VARCHAR(32) NOT NULL COMMENT '快照域 APARTMENT/ROOM/PERSON/ROUTE/RULE/HOLIDAY',
|
||
source_version BIGINT NOT NULL DEFAULT 0 COMMENT '平台版本号，单调递增',
|
||
source VARCHAR(20) NOT NULL DEFAULT 'PLATFORM' COMMENT 'PLATFORM / LOCAL（Mock 数据标 LOCAL）',
|
||
state VARCHAR(20) NOT NULL DEFAULT 'APPLIED' COMMENT 'PENDING/APPLIED/FAILED',
|
||
applied_at DATETIME(3) NULL COMMENT '生效时刻',
|
||
remark 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_mv_gyid_domain (gyid, domain),
|
||
KEY idx_mv_gyid_state (gyid, state, applied_at),
|
||
CONSTRAINT ck_mv_source CHECK (source IN ('PLATFORM','LOCAL')),
|
||
CONSTRAINT ck_mv_state CHECK (state IN ('PENDING','APPLIED','FAILED'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='主数据版本快照 [推断]';
|
||
|
||
-- 2 公寓档案
|
||
CREATE TABLE IF NOT EXISTS apt_master_apartment (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
apartment_code VARCHAR(64) NOT NULL COMMENT '公寓编码',
|
||
apartment_name VARCHAR(128) NULL,
|
||
city_code VARCHAR(32) NULL,
|
||
address VARCHAR(256) NULL,
|
||
contact_ref VARCHAR(128) NULL COMMENT '联系方式只存引用',
|
||
state VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
|
||
source_version BIGINT NOT NULL DEFAULT 0,
|
||
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_ma_gyid_code (gyid, apartment_code),
|
||
CONSTRAINT ck_ma_state CHECK (state IN ('ACTIVE','DISABLED'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='公寓档案 [推断]';
|
||
|
||
-- 3 房间主数据（含 CR-02 表 3-32 补列 building；Q-20 未决前列存在但不启用）
|
||
CREATE TABLE IF NOT EXISTS apt_master_room (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
room_id VARCHAR(64) NOT NULL COMMENT '房间主键（平台下发）',
|
||
room_no VARCHAR(32) NOT NULL COMMENT '房号',
|
||
room_floor VARCHAR(32) NULL COMMENT '楼层（Q-20 未决前的筛选落点）',
|
||
building VARCHAR(64) NULL COMMENT '楼栋；Q-20 未决，保留列不启用（表 3-32）',
|
||
bed_count INT NOT NULL DEFAULT 0,
|
||
orientation VARCHAR(32) NULL,
|
||
room_type VARCHAR(32) NULL,
|
||
state VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
|
||
state_version BIGINT NOT NULL DEFAULT 1 COMMENT '房态乐观锁，与 source_version 语义不同',
|
||
source_version BIGINT NOT NULL DEFAULT 0 COMMENT '平台快照版本；改快照字段返回 40904',
|
||
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_mr_gyid_room (gyid, room_id),
|
||
KEY idx_mr_gyid_floor_no (gyid, room_floor, room_no),
|
||
CONSTRAINT ck_mr_state CHECK (state IN ('ACTIVE','DISABLED'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='房间主数据（platform-down 只读）[推断+补列]';
|
||
|
||
-- 4 人员主数据（含 公寓端/10 表 10-6 人脸补列：媒体只存引用与校验和）
|
||
CREATE TABLE IF NOT EXISTS apt_master_person (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
ryid VARCHAR(64) NOT NULL COMMENT '人员 ID',
|
||
xm VARCHAR(64) NOT NULL COMMENT '姓名（快照字段，改则返回 40904）',
|
||
work_no VARCHAR(64) NULL COMMENT '工号，平台侧校验唯一性',
|
||
dept_code VARCHAR(64) NULL,
|
||
person_category VARCHAR(20) NOT NULL DEFAULT 'TRANSIENT' COMMENT 'TRANSIENT / LONG_TERM',
|
||
photo_ref VARCHAR(512) NULL COMMENT '照片引用，不存原图二进制',
|
||
photo_checksum VARCHAR(128) NULL,
|
||
feature_ref VARCHAR(512) NULL COMMENT '特征值引用，不存特征大对象',
|
||
feature_checksum VARCHAR(128) NULL,
|
||
feature_algo VARCHAR(32) NULL,
|
||
feature_version BIGINT NOT NULL DEFAULT 1,
|
||
face_sync_state VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING/SYNCED/FAILED/STALE',
|
||
state VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
|
||
source_version BIGINT NOT NULL DEFAULT 0,
|
||
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_mp_gyid_ryid (gyid, ryid),
|
||
KEY idx_mp_gyid_workno (gyid, work_no),
|
||
KEY idx_mp_gyid_name (gyid, xm),
|
||
CONSTRAINT ck_mp_category CHECK (person_category IN ('TRANSIENT','LONG_TERM')),
|
||
CONSTRAINT ck_mp_face CHECK (face_sync_state IN ('PENDING','SYNCED','FAILED','STALE'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='人员主数据（含 §12 补列）';
|
||
|
||
-- 5 交路主数据
|
||
CREATE TABLE IF NOT EXISTS apt_master_route (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
jlid VARCHAR(64) NOT NULL COMMENT '交路 ID；重复新增返回 40901',
|
||
cc VARCHAR(64) NOT NULL COMMENT '车次（必填，缺则 40001）',
|
||
kcsj DATETIME(3) NOT NULL COMMENT '开车时刻（必填）',
|
||
route_name VARCHAR(128) NULL,
|
||
start_station VARCHAR(64) NULL,
|
||
end_station VARCHAR(64) NULL,
|
||
state VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
|
||
stop_reason VARCHAR(256) NULL COMMENT '停用原因（必填，审计留痕）',
|
||
source_version BIGINT NOT NULL DEFAULT 0,
|
||
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_mroute_gyid_jlid (gyid, jlid),
|
||
KEY idx_mroute_gyid_cc_time (gyid, cc, kcsj),
|
||
CONSTRAINT ck_mroute_state CHECK (state IN ('ACTIVE','STOPPED'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='交路主数据 [推断]';
|
||
|
||
-- 6 叫乘规则（规则体用 JSON，避免频繁改表）
|
||
CREATE TABLE IF NOT EXISTS apt_master_rule (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
rule_id VARCHAR(64) NOT NULL,
|
||
rule_type VARCHAR(32) NOT NULL COMMENT 'CALL_TIME / HOLIDAY / TIMEOUT',
|
||
rule_version BIGINT NOT NULL DEFAULT 1,
|
||
rule_body JSON NULL COMMENT '规则体 JSON',
|
||
effective_from DATETIME(3) NULL,
|
||
effective_to DATETIME(3) NULL,
|
||
state VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
|
||
source_version BIGINT NOT NULL DEFAULT 0,
|
||
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_mrule_gyid_id_ver (gyid, rule_id, rule_version),
|
||
KEY idx_mrule_gyid_type (gyid, rule_type, state)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='叫乘规则快照 [推断]';
|
||
|
||
-- 7 节假日
|
||
CREATE TABLE IF NOT EXISTS apt_holiday (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
holiday_date DATE NOT NULL,
|
||
holiday_name VARCHAR(64) NULL,
|
||
holiday_type VARCHAR(20) NOT NULL DEFAULT 'HOLIDAY',
|
||
source_version BIGINT NOT NULL DEFAULT 0,
|
||
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_holiday_gyid_date (gyid, holiday_date)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='节假日表 [推断]';
|
||
|
||
-- 8 平台计划下行落库（jcsj 仅基线，权威值恒由公寓复算 CR-C06）
|
||
CREATE TABLE IF NOT EXISTS gy_syn_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 COMMENT '房间号',
|
||
kcsj DATETIME(3) NULL,
|
||
jcsj DATETIME(3) NULL COMMENT '平台计划基线（非权威）',
|
||
computed_jcsj DATETIME(3) NULL COMMENT '公寓复算值（权威）',
|
||
plan_version BIGINT NOT NULL DEFAULT 1,
|
||
sync_state VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING/SYNCED/FAILED/DRIFT',
|
||
source_version BIGINT NOT NULL DEFAULT 0,
|
||
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_syn_gyid_jcid (gyid, jcid),
|
||
KEY idx_syn_gyid_jlid_time (gyid, jlid, jcsj),
|
||
CONSTRAINT ck_syn_state CHECK (sync_state IN ('PENDING','SYNCED','FAILED','DRIFT'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='平台计划下行 [推断]';
|
||
|
||
-- 9 计划同步状态
|
||
CREATE TABLE IF NOT EXISTS gy_syn_jcxx_status (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
jcid VARCHAR(64) NOT NULL,
|
||
sync_state VARCHAR(20) NOT NULL DEFAULT 'PENDING',
|
||
last_sync_at DATETIME(3) NULL,
|
||
error_code VARCHAR(64) NULL,
|
||
error_msg VARCHAR(512) NULL,
|
||
retry_count INT NOT NULL DEFAULT 0,
|
||
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_synst_gyid_jcid (gyid, jcid),
|
||
KEY idx_synst_gyid_state (gyid, sync_state, last_sync_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='计划同步状态 [推断]';
|
||
|
||
-- 10 叫乘任务租约锁（防止重复生成任务）
|
||
CREATE TABLE IF NOT EXISTS gy_call_duty_match_lock (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
lock_key VARCHAR(128) NOT NULL COMMENT '租约键',
|
||
jcid VARCHAR(64) NULL,
|
||
owner_ref VARCHAR(64) NULL COMMENT '持锁实例',
|
||
lease_until DATETIME(3) NOT NULL COMMENT '租约到期',
|
||
state VARCHAR(20) NOT NULL DEFAULT 'HELD',
|
||
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_lock_gyid_key (gyid, lock_key),
|
||
KEY idx_lock_gyid_lease (gyid, lease_until),
|
||
CONSTRAINT ck_lock_state CHECK (state IN ('HELD','RELEASED','EXPIRED'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='叫乘任务租约锁 [推断]';
|
||
|
||
-- 11 本地建单 / 入住申请（CR-01 表 1-17 + 公寓端/11 表 11-7 补列）
|
||
-- Q-40 未冻结前：扩展要素落 request_payload JSON 兜底，不新增列（表 13-10）
|
||
CREATE TABLE IF NOT EXISTS apt_local_task (
|
||
id BIGINT NOT NULL AUTO_INCREMENT,
|
||
gyid VARCHAR(32) NOT NULL,
|
||
task_id VARCHAR(64) NOT NULL COMMENT '本地任务 ID（幂等键）',
|
||
jcid VARCHAR(64) NULL COMMENT '预分配叫乘 ID，与 apply_id 一对一',
|
||
apply_id VARCHAR(64) NULL COMMENT '入住申请 ID',
|
||
origin VARCHAR(20) NOT NULL DEFAULT 'LOCAL' COMMENT 'LOCAL/PLATFORM/DEVICE',
|
||
local_version BIGINT NOT NULL DEFAULT 1,
|
||
biz_version BIGINT NOT NULL DEFAULT 1,
|
||
match_state VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING/MATCHED/UNMATCHED/LOCKED/REJECTED',
|
||
checkin_state VARCHAR(24) NOT NULL DEFAULT 'SUBMITTED' COMMENT 'SUBMITTED/AUDITED/CHECKED_IN/REJECTED',
|
||
sync_state VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT 'PENDING/SENT/FAILED/DEAD/RETRIED',
|
||
fjh VARCHAR(32) NULL,
|
||
fjh_final VARCHAR(32) NULL COMMENT '审核后房间',
|
||
room_adjust_seq INT NOT NULL DEFAULT 0,
|
||
face_verify_state VARCHAR(20) NULL COMMENT 'VERIFIED/UNVERIFIED/SKIPPED',
|
||
face_device_code VARCHAR(64) NULL,
|
||
face_verified_at DATETIME(3) NULL,
|
||
ryid VARCHAR(64) NULL,
|
||
xm VARCHAR(64) NULL,
|
||
jlid VARCHAR(64) NULL,
|
||
cc VARCHAR(64) NULL,
|
||
jcsj DATETIME(3) NULL,
|
||
request_payload JSON NULL COMMENT 'Q-40 冻结前的 JSON 兜底留痕',
|
||
audited_by VARCHAR(64) NULL,
|
||
audited_at DATETIME(3) NULL,
|
||
checked_in_at DATETIME(3) NULL,
|
||
reject_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_lt_gyid_task (gyid, task_id),
|
||
UNIQUE KEY uk_lt_gyid_jcid (gyid, jcid),
|
||
KEY idx_lt_gyid_apply (gyid, apply_id),
|
||
KEY idx_lt_gyid_state (gyid, match_state, checkin_state),
|
||
CONSTRAINT ck_lt_match CHECK (match_state IN ('PENDING','MATCHED','UNMATCHED','LOCKED','REJECTED')),
|
||
CONSTRAINT ck_lt_checkin CHECK (checkin_state IN ('SUBMITTED','AUDITED','CHECKED_IN','REJECTED')),
|
||
CONSTRAINT ck_lt_sync CHECK (sync_state IN ('PENDING','SENT','FAILED','DEAD','RETRIED')),
|
||
CONSTRAINT ck_lt_face CHECK (face_verify_state IS NULL OR face_verify_state IN ('VERIFIED','UNVERIFIED','SKIPPED'))
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='本地建单/入住申请（申请阶段不建 gy_jcxx 行）';
|