AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.527.verify.sql

-- 1.0.527.verify.sql — 每条 SELECT 都必须返回 1

-- ── ① 三张表已创建 ──────────────────────────────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`TABLES`
         WHERE `TABLE_SCHEMA` = DATABASE()
           AND `TABLE_NAME` IN ('mdp_stg_action_run','mdp_std_action_event','dwd_order_review_kpi')) = 3;

-- ── ② tenant_id 必须是三张表唯一键的第一列（Principle 1 的结构性保证）──────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`STATISTICS`
         WHERE `TABLE_SCHEMA` = DATABASE()
           AND `NON_UNIQUE` = 0
           AND `SEQ_IN_INDEX` = 1
           AND `COLUMN_NAME` = 'tenant_id'
           AND `INDEX_NAME` IN ('uk_stg_action_run','uk_std_action_event','uk_dwd_order_review_kpi')) = 3;

-- ── ③ STG 满足 MdpStagingWriter canonical 契约（缺一列即写入期抛异常）───────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`COLUMNS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_stg_action_run'
           AND `COLUMN_NAME` IN ('tenant_id','source_system','source_table','source_row_id',
                                 'source_biz_key','raw_data','sync_batch_id','sync_time',
                                 'process_status','create_time','update_time')) = 11;

-- ── ④ STD 事件层关键列齐备（审计事实层：状态 + 起止时间 + 归因键）──────────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`COLUMNS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_std_action_event'
           AND `COLUMN_NAME` IN ('tenant_id','action_run_id','action_code','biz_type','biz_id',
                                 'biz_no','action_status','action_start_time','action_end_time',
                                 'message','detail_json')) = 11;

-- ── ⑤ DWD KPI 关键列齐备，且本批【不得】出现 TargetHours / kpi_status ──────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`COLUMNS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'dwd_order_review_kpi'
           AND `COLUMN_NAME` IN ('tenant_id','sales_order_id','sales_order_no',
                                 'review_start_time','review_end_time','actual_hours',
                                 'calculation_status')) = 7;

SELECT (SELECT COUNT(*) FROM `information_schema`.`COLUMNS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'dwd_order_review_kpi'
           AND `COLUMN_NAME` IN ('target_hours','kpi_status','factory_ref_id','order_type')) = 0;

-- ── ⑥ MDP 实体已注册且配置正确（本库源 + INCR + 主游标 + biz_key）──────────────────
SELECT (SELECT COUNT(*) FROM `mdp_entity`
         WHERE `entity_code` = 'S1_ACTION_RUN_LOG'
           AND `source_id` = 1
           AND `source_table_name` = 'aidop_action_run_log'
           AND `target_table_name` = 'mdp_stg_action_run'
           AND `sync_mode` = 'INCR'
           AND `incr_column` = 'start_time'
           AND `biz_key_expr` = 'id'
           AND `status` = 1) = 1;

-- 实体注册必须唯一，重复执行迁移不得产生第二行
SELECT (SELECT COUNT(*) FROM `mdp_entity` WHERE `entity_code` = 'S1_ACTION_RUN_LOG') = 1;

-- ── ⑦ Snapshot Integrity：本批不得改造 mdp_std_so ───────────────────────────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`COLUMNS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_std_so'
           AND `COLUMN_NAME` IN ('review_start_time','review_end_time','confirm_time',
                                 'status_change_time','actual_hours')) = 0;