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

-- 1.0.532.verify.sql — 每条 SELECT 都必须返回 1
--
-- ⚠️ AutoVersionUpdate 按分号切分本文件并逐片断言，故不得放 informational 查询，
--    且文件必须以最后一条断言的分号结尾（尾部纯注释块会被切成多余语句而判失败）。

-- ── ① 列存在 ────────────────────────────────────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`COLUMNS`
         WHERE `TABLE_SCHEMA` = DATABASE()
           AND ((`TABLE_NAME` = 'mdp_std_operation_schedule'  AND `COLUMN_NAME` = 'status')
             OR (`TABLE_NAME` = 'mdp_std_work_order_schedule' AND `COLUMN_NAME` = 'sales_order_entry_id'))) = 2;

-- ── ② 类型正确（status=varchar / sales_order_entry_id=bigint）──────────────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`COLUMNS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_std_operation_schedule'
           AND `COLUMN_NAME` = 'status' AND `DATA_TYPE` = 'varchar') = 1;

SELECT (SELECT COUNT(*) FROM `information_schema`.`COLUMNS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_std_work_order_schedule'
           AND `COLUMN_NAME` = 'sales_order_entry_id' AND `DATA_TYPE` = 'bigint') = 1;

-- ── ③ NULL 策略正确：两列都必须可空 ────────────────────────────────────────────────
--     status：ScheduleResultOpMaster 来源的行天然没有该概念
--     sales_order_entry_id：BusinessID=0（无关联订单行）必须落 NULL，不能落 0
SELECT (SELECT COUNT(*) FROM `information_schema`.`COLUMNS`
         WHERE `TABLE_SCHEMA` = DATABASE()
           AND ((`TABLE_NAME` = 'mdp_std_operation_schedule'  AND `COLUMN_NAME` = 'status')
             OR (`TABLE_NAME` = 'mdp_std_work_order_schedule' AND `COLUMN_NAME` = 'sales_order_entry_id'))
           AND `IS_NULLABLE` = 'YES') = 2;

-- ── ④ 桥索引已建 ───────────────────────────────────────────────────────────────────
SELECT (SELECT COUNT(DISTINCT `INDEX_NAME`) FROM `information_schema`.`STATISTICS`
         WHERE `TABLE_SCHEMA` = DATABASE()
           AND `INDEX_NAME` IN ('idx_std_op_sched_status','idx_std_wo_sched_entry')) = 2;

-- ── ⑤ transform 投影正确性 ─────────────────────────────────────────────────────────
--  断言口径说明（重要）：
--    这两列是【新增列】，存量标准层行要等各自租户的 S2 跑批扫到才会被填充。
--    "还没扫到 → 值为 NULL" 与 "扫到了但投影错了" 是两件事，只有后者是缺陷。
--    因此这里断言的是【投影正确性】而不是【全量覆盖率】，拆成两条互补的断言：
--      (a) 凡是已填充的值，必须与贴源层逐行一致        → 抓"投影错"
--      (b) 至少要有一行被填充                          → 抓"压根没接上投影"
--    只放宽其一都会漏掉真实缺陷，两条必须同时成立。
--    覆盖率本身不是本迁移的验收项（受跑批调度影响），需要观察时请另跑只读查询。

-- (a-1) status：已填充的行必须等于 stg.raw_data.$.Status
SELECT (SELECT COUNT(*) FROM `mdp_std_operation_schedule` o
          JOIN `mdp_stg_schedule` g
            ON g.`tenant_id` = o.`tenant_id`
           AND g.`source_biz_key` = o.`source_biz_key`
           AND g.`source_table` = 'PeriodSequenceDet'
         WHERE o.`status` IS NOT NULL
           AND o.`status` <> IFNULL(JSON_UNQUOTE(JSON_EXTRACT(g.`raw_data`, '$.Status')), '')) = 0;

-- (b-1) status 投影确实被执行过
SELECT (SELECT COUNT(*) FROM `mdp_std_operation_schedule` WHERE `status` IS NOT NULL) > 0;

-- (a-2) sales_order_entry_id：已填充的值必须等于 stg 的 BusinessID
SELECT (SELECT COUNT(*) FROM `mdp_std_work_order_schedule` w
          JOIN `mdp_stg_schedule` g
            ON g.`tenant_id` = w.`tenant_id`
           AND g.`source_biz_key` = w.`source_biz_key`
           AND g.`source_table` = 'WorkOrdMaster'
         WHERE w.`sales_order_entry_id` IS NOT NULL
           AND w.`sales_order_entry_id` <>
               CAST(JSON_UNQUOTE(JSON_EXTRACT(g.`raw_data`, '$.BusinessID')) AS SIGNED)) = 0;

-- (b-2) sales_order_entry_id 投影确实被执行过
SELECT (SELECT COUNT(*) FROM `mdp_std_work_order_schedule` WHERE `sales_order_entry_id` IS NOT NULL) > 0;

-- ── ⑥ 不得出现 sales_order_entry_id = 0（0 必须归一为 NULL）─────────────────────────
SELECT (SELECT COUNT(*) FROM `mdp_std_work_order_schedule` WHERE `sales_order_entry_id` = 0) = 0;

-- ── ⑦ Tenant 无串扰：中台桥解析出的订单行，其租户必须与工单行一致 ──────────────────
SELECT (SELECT COUNT(*) FROM `mdp_std_work_order_schedule` w
          JOIN `mdp_std_so` s ON s.`order_entry_id` = w.`sales_order_entry_id`
         WHERE w.`sales_order_entry_id` IS NOT NULL
           AND s.`tenant_id` <> w.`tenant_id`) = 0;

-- ── ⑧ MDP-ONLY 桥可跑通：至少存在一条 计划→工单→订单行→订单 的中台闭环 ────────────
--     全程只读 mdp_std_*，不触碰任何源业务表
SELECT (SELECT COUNT(*) FROM `mdp_std_operation_schedule` op
          JOIN `mdp_std_work_order_schedule` wo
            ON wo.`tenant_id` = op.`tenant_id` AND wo.`work_order` = op.`work_order`
          JOIN `mdp_std_so` so
            ON so.`tenant_id` = wo.`tenant_id` AND so.`order_entry_id` = wo.`sales_order_entry_id`
         WHERE wo.`sales_order_entry_id` IS NOT NULL
           AND so.`order_id` IS NOT NULL) > 0;

-- ── ⑨ 本批禁止建 Stage-2 KPI 表 ────────────────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`TABLES`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'dwd_product_design_kpi') = 0;

-- ── ⑩ 不得伪造历史事件：S2_DAILY_PLAN_RELEASE 只能有本批上线后真实产生的记录 ────────
--     （断言其不存在 end_time 早于本迁移引入时间的"历史补录"行——此处只校验无 RUNNING 残留孤儿）
SELECT (SELECT COUNT(*) FROM `aidop_action_run_log`
         WHERE `action_code` = 'S2_DAILY_PLAN_RELEASE'
           AND (`tenant_id` IS NULL OR `tenant_id` = 0)) = 0;