-- =============================================================================
-- IT-4 入住作业核心补列与状态字典（可重放）：CI-02 ~ CI-05。
-- 依据：公寓端/11 §3（表 11-7 / 11-8 / 11-9）、CR-02 §13 表 3-57 验收 18~20、13 表 13-14。
-- 口径：
--   1) 59 张业务表不变：只补列、不放宽既有事实列语义、不新增 gy_jcxx 状态字段。
--   2) `checkin_state` / `sync_state` 两条流程态字典按表 11-4 / 11-9 扩展：
--        checkin_state: SUBMITTED/REVIEWING/APPROVED/ROOM_ADJUSTED/REJECTED/CHECKED_IN/CANCELLED/CLOSED
--        sync_state   : PENDING/REPORTED/MATCHED/UNMATCHED/TASK_READY
--      这是**流程态**字典替换（表 11-4），不是新增状态字段；gy_jcxx 的 ryzt/finish_ly 仍只经 apt.duty.state 写入。
--   3) `gygl_ly_event.event_type` 按 CR-02 表 3-14 增补 ROOM_SWAP（审核期换房的证据事件）。
--   4) 复用既有列：`audited_by` / `audited_at` 即表 11-7 的 reviewed_by / reviewed_at（不重复建列）。
--   5) 依赖 00_common.sql 的 apt_add_column_if_missing（与 07_patch_columns.sql 同一辅助）。
-- =============================================================================

-- 1. apt_local_task 申请阶段补列（表 11-7）
CALL apt_add_column_if_missing('apt_local_task', 'device_code',
    'device_code VARCHAR(64) NULL COMMENT ''签到终端来源（表 11-7）''');
CALL apt_add_column_if_missing('apt_local_task', 'device_event_id',
    'device_event_id VARCHAR(64) NULL COMMENT ''设备侧幂等键（表 11-7）''');
CALL apt_add_column_if_missing('apt_local_task', 'work_no',
    'work_no VARCHAR(64) NULL COMMENT ''工号快照（平台匹配输入）''');
CALL apt_add_column_if_missing('apt_local_task', 'name_snapshot',
    'name_snapshot VARCHAR(128) NULL COMMENT ''姓名快照（不替代 apt_master_person）''');
CALL apt_add_column_if_missing('apt_local_task', 'reg_id',
    'reg_id VARCHAR(64) NULL COMMENT ''关联 apt_person_face_reg''');
CALL apt_add_column_if_missing('apt_local_task', 'reg_state_at_apply',
    'reg_state_at_apply VARCHAR(20) NULL COMMENT ''申请时注册态，必须是 CONFIRMED''');
CALL apt_add_column_if_missing('apt_local_task', 'jch',
    'jch VARCHAR(32) NULL COMMENT ''机次快照''');
CALL apt_add_column_if_missing('apt_local_task', 'kcsj_snapshot',
    'kcsj_snapshot DATETIME(3) NULL COMMENT ''开车时刻快照（不写主数据）''');
CALL apt_add_column_if_missing('apt_local_task', 'verified_ryid',
    'verified_ryid VARCHAR(64) NULL COMMENT ''N-38 核验通过的人员''');
CALL apt_add_column_if_missing('apt_local_task', 'verified_at',
    'verified_at DATETIME(3) NULL COMMENT ''N-38 核验时刻（≤5 min 才能提交申请）''');
CALL apt_add_column_if_missing('apt_local_task', 'feature_version',
    'feature_version BIGINT NULL COMMENT ''核验所用特征版本''');
CALL apt_add_column_if_missing('apt_local_task', 'source',
    'source VARCHAR(20) NOT NULL DEFAULT ''DEVICE'' COMMENT ''DEVICE / DUTY / SELF（CR-02 表 3-14）''');
CALL apt_add_column_if_missing('apt_local_task', 'submitted_at',
    'submitted_at DATETIME(3) NULL COMMENT ''申请提交时刻''');
CALL apt_add_column_if_missing('apt_local_task', 'occurred_at',
    'occurred_at DATETIME(3) NULL COMMENT ''业务发生时刻（设备事件时间，不判日切）''');
CALL apt_add_column_if_missing('apt_local_task', 'review_note',
    'review_note VARCHAR(256) NULL COMMENT ''审核说明 / 驳回原因''');
CALL apt_add_column_if_missing('apt_local_task', 'adjusted_by',
    'adjusted_by VARCHAR(64) NULL COMMENT ''房间调整操作人''');
CALL apt_add_column_if_missing('apt_local_task', 'adjusted_at',
    'adjusted_at DATETIME(3) NULL COMMENT ''房间调整时刻''');
CALL apt_add_column_if_missing('apt_local_task', 'adjust_reason',
    'adjust_reason VARCHAR(256) NULL COMMENT ''房间调整理由''');
CALL apt_add_column_if_missing('apt_local_task', 'reported_at',
    'reported_at DATETIME(3) NULL COMMENT ''#9 CHECKIN 入队时刻''');
CALL apt_add_column_if_missing('apt_local_task', 'matched_at',
    'matched_at DATETIME(3) NULL COMMENT ''#7 匹配结论落库时刻''');
CALL apt_add_column_if_missing('apt_local_task', 'task_ready_at',
    'task_ready_at DATETIME(3) NULL COMMENT ''任务就绪（jcsj 复算完成）时刻''');
CALL apt_add_column_if_missing('apt_local_task', 'matched_plan_key',
    'matched_plan_key VARCHAR(128) NULL COMMENT ''平台匹配键（仅登记，不替代 jcid）''');
CALL apt_add_column_if_missing('apt_local_task', 'base_kcsj',
    'base_kcsj DATETIME(3) NULL COMMENT ''平台补齐的开車时刻（要素非叫乘时刻）''');
CALL apt_add_column_if_missing('apt_local_task', 'base_jcfs',
    'base_jcfs VARCHAR(32) NULL COMMENT ''平台补齐的叫乘方式''');
CALL apt_add_column_if_missing('apt_local_task', 'origin_jcsj',
    'origin_jcsj DATETIME(3) NULL COMMENT ''本地指定叫乘时刻（锁定时保留）''');
CALL apt_add_column_if_missing('apt_local_task', 'derived_rule_version',
    'derived_rule_version BIGINT NULL COMMENT ''jcsj 复算所用规则版本，升级不回溯''');
CALL apt_add_column_if_missing('apt_local_task', 'jllx',
    'jllx VARCHAR(8) NULL COMMENT ''1=本地锁定交路要素（D8 例外②）''');

-- 2. 幂等键：设备事件唯一、申请单唯一（表 11-7）
SET @uk_device_event = (SELECT IF(COUNT(*) = 0,
    'ALTER TABLE apt_local_task ADD UNIQUE KEY uk_lt_gyid_device_event (gyid, device_code, device_event_id)',
    'DO 0')
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'apt_local_task'
    AND INDEX_NAME = 'uk_lt_gyid_device_event');
PREPARE apt_stmt_uk_de FROM @uk_device_event;
EXECUTE apt_stmt_uk_de;
DEALLOCATE PREPARE apt_stmt_uk_de;

SET @uk_apply = (SELECT IF(COUNT(*) = 0,
    'ALTER TABLE apt_local_task ADD UNIQUE KEY uk_lt_gyid_apply (gyid, apply_id)',
    'DO 0')
  FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'apt_local_task'
    AND INDEX_NAME = 'uk_lt_gyid_apply');
PREPARE apt_stmt_uk_apply FROM @uk_apply;
EXECUTE apt_stmt_uk_apply;
DEALLOCATE PREPARE apt_stmt_uk_apply;

-- 3. 流程态字典（表 11-4 / 11-9）：先删旧 CHECK，再按新字典重建，可重放
SET @drop_checkin = (SELECT IF(COUNT(*) > 0,
    'ALTER TABLE apt_local_task DROP CHECK ck_lt_checkin',
    'DO 0')
  FROM information_schema.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = 'apt_local_task'
    AND CONSTRAINT_NAME = 'ck_lt_checkin' AND CONSTRAINT_TYPE = 'CHECK');
PREPARE apt_stmt_drop_ci FROM @drop_checkin;
EXECUTE apt_stmt_drop_ci;
DEALLOCATE PREPARE apt_stmt_drop_ci;

SET @add_checkin = (SELECT IF(COUNT(*) = 0,
    'ALTER TABLE apt_local_task ADD CONSTRAINT ck_lt_checkin CHECK (checkin_state IN (''SUBMITTED'',''REVIEWING'',''APPROVED'',''ROOM_ADJUSTED'',''REJECTED'',''CHECKED_IN'',''CANCELLED'',''CLOSED''))',
    'DO 0')
  FROM information_schema.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = 'apt_local_task'
    AND CONSTRAINT_NAME = 'ck_lt_checkin' AND CONSTRAINT_TYPE = 'CHECK');
PREPARE apt_stmt_add_ci FROM @add_checkin;
EXECUTE apt_stmt_add_ci;
DEALLOCATE PREPARE apt_stmt_add_ci;

SET @drop_sync = (SELECT IF(COUNT(*) > 0,
    'ALTER TABLE apt_local_task DROP CHECK ck_lt_sync',
    'DO 0')
  FROM information_schema.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = 'apt_local_task'
    AND CONSTRAINT_NAME = 'ck_lt_sync' AND CONSTRAINT_TYPE = 'CHECK');
PREPARE apt_stmt_drop_sync FROM @drop_sync;
EXECUTE apt_stmt_drop_sync;
DEALLOCATE PREPARE apt_stmt_drop_sync;

SET @add_sync = (SELECT IF(COUNT(*) = 0,
    'ALTER TABLE apt_local_task ADD CONSTRAINT ck_lt_sync CHECK (sync_state IN (''PENDING'',''REPORTED'',''MATCHED'',''UNMATCHED'',''TASK_READY''))',
    'DO 0')
  FROM information_schema.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = 'apt_local_task'
    AND CONSTRAINT_NAME = 'ck_lt_sync' AND CONSTRAINT_TYPE = 'CHECK');
PREPARE apt_stmt_add_sync FROM @add_sync;
EXECUTE apt_stmt_add_sync;
DEALLOCATE PREPARE apt_stmt_add_sync;

-- 4. gygl_ly_event.event_type 增补 ROOM_SWAP（CR-02 表 3-14；审核期换房证据）
SET @drop_lye = (SELECT IF(COUNT(*) > 0,
    'ALTER TABLE gygl_ly_event DROP CHECK ck_lye_type',
    'DO 0')
  FROM information_schema.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = 'gygl_ly_event'
    AND CONSTRAINT_NAME = 'ck_lye_type' AND CONSTRAINT_TYPE = 'CHECK');
PREPARE apt_stmt_drop_lye FROM @drop_lye;
EXECUTE apt_stmt_drop_lye;
DEALLOCATE PREPARE apt_stmt_drop_lye;

SET @add_lye = (SELECT IF(COUNT(*) = 0,
    'ALTER TABLE gygl_ly_event ADD CONSTRAINT ck_lye_type CHECK (event_type IN (''CHECKIN'',''CHECKOUT'',''ROOM_SWAP''))',
    'DO 0')
  FROM information_schema.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = 'gygl_ly_event'
    AND CONSTRAINT_NAME = 'ck_lye_type' AND CONSTRAINT_TYPE = 'CHECK');
PREPARE apt_stmt_add_lye FROM @add_lye;
EXECUTE apt_stmt_add_lye;
DEALLOCATE PREPARE apt_stmt_add_lye;

-- 5. 校验
SELECT '=== IT-4 apt_local_task 补列（应 27 行）===' AS section;
SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'apt_local_task'
  AND COLUMN_NAME IN ('device_code','device_event_id','work_no','name_snapshot','reg_id',
    'reg_state_at_apply','jch','kcsj_snapshot','verified_ryid','verified_at','feature_version',
    'source','submitted_at','occurred_at','review_note','adjusted_by','adjusted_at','adjust_reason',
    'reported_at','matched_at','task_ready_at','matched_plan_key','base_kcsj','base_jcfs',
    'origin_jcsj','derived_rule_version','jllx')
ORDER BY COLUMN_NAME;

SELECT '=== 流程态字典 CHECK（checkin_state 应 8 值 / sync_state 应 5 值）===' AS section;
SELECT CONSTRAINT_NAME, CHECK_CLAUSE
FROM information_schema.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
  AND CONSTRAINT_NAME IN ('ck_lt_checkin','ck_lt_sync','ck_lye_type')
ORDER BY CONSTRAINT_NAME;

SELECT '=== 幂等唯一键（应 2 行）===' AS section;
SELECT INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS cols
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'apt_local_task'
  AND INDEX_NAME IN ('uk_lt_gyid_device_event','uk_lt_gyid_apply')
GROUP BY INDEX_NAME;