-- =============================================================================
-- IT-0 验收校验脚本（表 13-10 验收③ / MS-0）
-- 预期：business_table_count = 59；missing_pk = 0；missing_gyid = 0
-- 口径：apt_* 36 + gy_*/gygl_* 18 + duty_* 5 = 59（CR-02 表 3-25、公寓端/03 表 3-8）
--       apt_ops_probe 为连通性探针表，显式排除，不计入 59 张。
-- 追加：第 7~13 段为 IT-3（DV-3）增量校验，见文件末段说明。
-- =============================================================================
SELECT '=== 1. 业务表总数（应为 59）===' AS section;
SELECT COUNT(*) AS business_table_count
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
  AND (TABLE_NAME LIKE 'apt\_%' OR TABLE_NAME LIKE 'gy\_%'
       OR TABLE_NAME LIKE 'gygl\_%' OR TABLE_NAME LIKE 'duty\_%')
  AND TABLE_NAME <> 'apt_ops_probe';

SELECT '=== 2. 分前缀分布（应为 apt 36 / gy 17 / gygl 1 / duty 5）===' AS section;
SELECT CASE
         WHEN TABLE_NAME LIKE 'apt\_%'  THEN 'apt'
         WHEN TABLE_NAME LIKE 'gygl\_%' THEN 'gygl'
         WHEN TABLE_NAME LIKE 'gy\_%'   THEN 'gy'
         ELSE 'duty'
       END AS prefix, COUNT(*) AS cnt
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND (TABLE_NAME LIKE 'apt\_%' OR TABLE_NAME LIKE 'gy\_%'
       OR TABLE_NAME LIKE 'gygl\_%' OR TABLE_NAME LIKE 'duty\_%')
  AND TABLE_NAME <> 'apt_ops_probe'
GROUP BY prefix ORDER BY prefix;

SELECT '=== 3. 缺主键的业务表（应为 0 行）===' AS section;
SELECT t.TABLE_NAME
FROM information_schema.TABLES t
WHERE t.TABLE_SCHEMA = DATABASE()
  AND (t.TABLE_NAME LIKE 'apt\_%' OR t.TABLE_NAME LIKE 'gy\_%'
       OR t.TABLE_NAME LIKE 'gygl\_%' OR t.TABLE_NAME LIKE 'duty\_%')
  AND t.TABLE_NAME <> 'apt_ops_probe'
  AND NOT EXISTS (
      SELECT 1 FROM information_schema.TABLE_CONSTRAINTS c
      WHERE c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
        AND c.CONSTRAINT_TYPE = 'PRIMARY KEY'
  );

SELECT '=== 4. 缺 gyid 列的业务表（应为 0 行）===' AS section;
SELECT t.TABLE_NAME
FROM information_schema.TABLES t
WHERE t.TABLE_SCHEMA = DATABASE()
  AND (t.TABLE_NAME LIKE 'apt\_%' OR t.TABLE_NAME LIKE 'gy\_%'
       OR t.TABLE_NAME LIKE 'gygl\_%' OR t.TABLE_NAME LIKE 'duty\_%')
  AND t.TABLE_NAME <> 'apt_ops_probe'
  AND NOT EXISTS (
      SELECT 1 FROM information_schema.COLUMNS c
      WHERE c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
        AND c.COLUMN_NAME = 'gyid'
  );

SELECT '=== 5. 外键数量（应为 0：docs/10 表 5-6 明确不建外键）===' AS section;
SELECT COUNT(*) AS foreign_key_count
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = DATABASE() AND CONSTRAINT_TYPE = 'FOREIGN KEY';

SELECT '=== 6. 探针表与基线种子 ===' AS section;
SELECT (SELECT COUNT(*) FROM apt_ops_probe) AS probe_rows,
       (SELECT COUNT(*) FROM apt_master_version WHERE source = 'LOCAL') AS local_baseline_rows,
       (SELECT COUNT(*) FROM apt_ops_selfcheck) AS selfcheck_item_rows;

-- =============================================================================
-- IT-3 追加校验（DV-3 / 13 表 13-13 验收①）
-- 预期：IT-3 增量列 6 条；人脸权限点 7 行（6 F + 1 C）；IT-7 置灰项授权 0；
--       N-34 可申请设备 2 台；注册链三处「泄漏」计数均为 0。
-- =============================================================================
SELECT '=== 7. IT-3 增量列（应 6：回执态 3 + §12 设备范围 3）===' AS section;
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND (   (TABLE_NAME = 'apt_person_face_reg' AND COLUMN_NAME IN ('platform_version','unit_code'))
       OR (TABLE_NAME = 'apt_master_person'  AND COLUMN_NAME = 'face_platform_version')
       OR (TABLE_NAME = 'gy_sb' AND COLUMN_NAME IN ('device_class','face_scope_value','face_cache_enabled')))
ORDER BY TABLE_NAME, COLUMN_NAME;

SELECT '=== 8. 人脸权限点与页面菜单（应 7 行：6 F + 1 C）===' AS section;
SELECT menu_id, menu_name, menu_type, visible, status, perms
FROM sys_menu WHERE menu_id BETWEEN 3201 AND 3216 ORDER BY menu_id;

SELECT '=== 9. IT-7 预留点置灰且未授权（3213~3216 应为 0）===' AS section;
SELECT COUNT(*) AS it3_grey_bound
FROM sys_role_menu rm JOIN sys_menu m ON m.menu_id = rm.menu_id
WHERE m.menu_id IN (3213, 3214, 3215, 3216);

SELECT '=== 10. N-34 可申请设备（state <> DISABLED，应 2）===' AS section;
SELECT COUNT(*) AS it3_apply_device_count
FROM gy_sb
WHERE device_code IN ('DEV-CHECKIN-01', 'DEV-TABLET-01', 'DEV-CHECKIN-99')
  AND state <> 'DISABLED';

SELECT '=== 11. 注册链一致性：APPLIED 不得带 ryid、CONFIRMED 必带 ryid（均应 0）===' AS section;
SELECT
    (SELECT COUNT(*) FROM apt_person_face_reg
      WHERE reg_state = 'APPLIED' AND (confirmed_ryid IS NOT NULL OR local_only <> 1)) AS it3_applied_leak,
    (SELECT COUNT(*) FROM apt_person_face_reg
      WHERE reg_state = 'CONFIRMED' AND confirmed_ryid IS NULL) AS it3_confirmed_without_ryid,
    (SELECT COUNT(*) FROM apt_person_face_reg
      WHERE reg_state = 'REJECTED' AND confirmed_ryid IS NOT NULL) AS it3_rejected_with_ryid;

SELECT '=== 12. 双路状态链独立：CONFIRMED 行不得缺 upload_state / dispatch_state（应 0）===' AS section;
SELECT reg_id, upload_state, dispatch_state, verdict, platform_version
FROM apt_person_face_reg
WHERE reg_state = 'CONFIRMED' AND (upload_state IS NULL OR dispatch_state IS NULL);

SELECT '=== 13. 确认行必须有对应人员主档（应 0 孤儿）===' AS section;
SELECT COUNT(*) AS it3_confirmed_orphan
FROM apt_person_face_reg r
WHERE r.reg_state = 'CONFIRMED'
  AND NOT EXISTS (
      SELECT 1 FROM apt_master_person p
      WHERE p.gyid = r.gyid AND p.ryid = r.confirmed_ryid AND p.deleted = 0
  );

-- =============================================================================
-- IT-4 追加校验（入住作业核心 / 表 11-4、表 11-9、CR-02 表 3-57 验收 18~20）
-- 预期：增量列 26 条；流程态字典 8/5 值；入住权限点 5 行；三处链路泄漏均为 0。
-- =============================================================================
SELECT '=== 14. IT-4 apt_local_task 增量列（应 27）===' AS section;
SELECT COUNT(*) AS it4_local_task_columns
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');

SELECT '=== 15. 流程态字典（checkin_state 8 值 / sync_state 5 值 / 事件含 ROOM_SWAP）===' 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 '=== 16. 入住权限点与页面菜单（应 5 行）===' AS section;
SELECT menu_id, menu_name, menu_type, visible, status, perms
FROM sys_menu WHERE menu_id IN (3005, 3202, 3217, 3218, 3219) ORDER BY menu_id;

SELECT '=== 17. 任务就绪必须已复算 jcsj（应 0）===' AS section;
SELECT COUNT(*) AS it4_task_ready_without_jcsj
FROM apt_local_task
WHERE sync_state = 'TASK_READY' AND jcsj IS NULL AND deleted = 0;

SELECT '=== 18. CHECKED_IN 必须有 gy_jcxx 行（应 0 孤儿）===' AS section;
SELECT COUNT(*) AS it4_checked_in_orphan
FROM apt_local_task t
WHERE t.checkin_state = 'CHECKED_IN' AND t.deleted = 0
  AND NOT EXISTS (
      SELECT 1 FROM gy_jcxx j WHERE j.gyid = t.gyid AND j.jcid = t.jcid AND j.deleted = 0
  );

SELECT '=== 19. 未入住不得提前生成叫乘行（应 0 泄漏）===' AS section;
SELECT COUNT(*) AS it4_checkin_leak
FROM apt_local_task t
WHERE t.checkin_state IN ('SUBMITTED','REVIEWING','APPROVED','ROOM_ADJUSTED','REJECTED')
  AND t.deleted = 0
  AND EXISTS (SELECT 1 FROM gy_jcxx j WHERE j.gyid = t.gyid AND j.jcid = t.jcid AND j.deleted = 0);

-- =============================================================================
-- IT-5 追加校验（叫乘执行与状态机 / 表 3-10、表 4-9、表 5-20、表 7-3 M3）
-- 预期：增量列 11 条；叫乘权限点 5 行；平板字典 3 个 CHECK；
--       状态一致性四类泄漏全为 0；平板链路无 #24 语音摘要。
-- =============================================================================
SELECT '=== 20. IT-5 gy_jcxx 增量列（应 12）===' AS section;
SELECT COUNT(*) AS it5_jcxx_columns
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'gy_jcxx'
  AND COLUMN_NAME IN ('jcsj_s','hjxysj','jcbjsj_s','fjsj_s','lybjsj','is_jcbj',
                      'is_lybj','is_temp_call','leave_type','leave_event_id','leave_remark','jch');

SELECT '=== 21. 叫乘权限点与页面菜单（应 5 行）===' AS section;
SELECT menu_id, menu_name, menu_type, visible, status, perms
FROM sys_menu WHERE menu_id IN (3004, 3220, 3221, 3222, 3203) ORDER BY menu_id;

SELECT '=== 22. 平板字典 CHECK（应 3：face / result_type / applied_state）===' AS section;
SELECT CONSTRAINT_NAME, CHECK_CLAUSE
FROM information_schema.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
  AND CONSTRAINT_NAME IN ('ck_tabletresult_face','ck_tabletresult_type','ck_tabletresult_applied')
ORDER BY CONSTRAINT_NAME;

SELECT '=== 23. 终态一致性：ryzt=3 必须 finish_jc=1（应 0）===' AS section;
SELECT COUNT(*) AS it5_answered_without_finish
FROM gy_jcxx
WHERE deleted = 0 AND ryzt = '3' AND finish_jc <> '1';

SELECT '=== 24. 已结束叫乘不得回退到未完成态（应 0）===' AS section;
SELECT COUNT(*) AS it5_finished_regressed
FROM gy_jcxx
WHERE deleted = 0 AND finish_jc = '1' AND ryzt IN ('0','1');

SELECT '=== 25. 平板任务与结果一致：DONE 任务必须有结果行（应 0）===' AS section;
SELECT COUNT(*) AS it5_task_done_without_result
FROM apt_tablet_task t
WHERE t.pull_state = 'DONE'
  AND NOT EXISTS (
      SELECT 1 FROM apt_tablet_call_result r
      WHERE r.gyid = t.gyid AND r.task_id = t.task_id
  );

SELECT '=== 26. 平板链路不得上报 #24 语音摘要（应 0）===' AS section;
SELECT COUNT(*) AS it5_tablet_voice_summary
FROM apt_report_outbox o
WHERE o.biz_type = 'VOICE_SUMMARY'
  AND EXISTS (
      SELECT 1 FROM gy_jcxx j
      WHERE j.gyid = o.gyid AND j.jcid = o.biz_id AND j.call_mode = 'TABLET'
  );