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

-- 1.0.544.verify.sql — HARD ASSERTIONS，每条 SELECT 都必须返回 1
--
-- ⚠️ AutoVersionUpdate 按分号切分本文件，并把【每一个切片】都当断言、要求返回 1。因此：
--      ① 不得放"仅供排查"的 informational 查询
--      ② 文件必须以最后一条断言的分号结尾，末尾不能留纯注释块
--      ③ 注释里不得出现任何字面分号
--
-- 口径：只断言本批 Writer 独占产出的 mdp_std_purchase_order_completion。
--   结构断言迁移后即可全过；数据断言在表为空时也成立，Writer 跑过之后才真正有判别力。

-- ── ① 表与 Canonical 唯一键就位 ─────────────────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`TABLES`
         WHERE `TABLE_SCHEMA` = DATABASE()
           AND `TABLE_NAME` = 'mdp_std_purchase_order_completion') = 1;

SELECT (SELECT GROUP_CONCAT(`COLUMN_NAME` ORDER BY `SEQ_IN_INDEX`)
          FROM `information_schema`.`STATISTICS`
         WHERE `TABLE_SCHEMA` = DATABASE()
           AND `TABLE_NAME` = 'mdp_std_purchase_order_completion'
           AND `INDEX_NAME` = 'uk_mdp_std_po_completion') = 'tenant_id,po_no,po_line';

-- ①b provenance 列绝不得混进业务唯一键
SELECT (SELECT COUNT(*) FROM `information_schema`.`STATISTICS`
         WHERE `TABLE_SCHEMA` = DATABASE()
           AND `TABLE_NAME` = 'mdp_std_purchase_order_completion'
           AND `INDEX_NAME` = 'uk_mdp_std_po_completion'
           AND `COLUMN_NAME` IN ('domain','source_row_id','source_id','potype')) = 0;

-- ── ② Canonical 无重复：一条采购行只能一行 ──────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_order_completion`) =
       (SELECT COUNT(DISTINCT `tenant_id`, `po_no`, `po_line`)
          FROM `mdp_std_purchase_order_completion`);

-- ── ③ 租户合法：绝不落无主 Fact ─────────────────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_order_completion`
         WHERE `tenant_id` IS NULL OR `tenant_id` <= 0) = 0;

-- ── ④ completion_status 取值域封闭 ──────────────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_order_completion`
         WHERE `completion_status` NOT IN ('COMPLETED','NOT_COMPLETED','UNKNOWN')) = 0;

-- ── ⑤ 映射必须严格由 raw_status 派生 ────────────────────────────────────────────────
-- ⑤a raw_status 不是 'C' 就绝不能是 COMPLETED（IFNULL 是必需的，NULL 比较不会为真）
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_order_completion`
         WHERE `completion_status` = 'COMPLETED'
           AND IFNULL(`raw_status`, '#') <> 'C') = 0;

-- ⑤b raw_status='C' 必须 COMPLETED
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_order_completion`
         WHERE `raw_status` = 'C' AND `completion_status` <> 'COMPLETED') = 0;

-- ⑤c raw_status 为空串必须 NOT_COMPLETED
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_order_completion`
         WHERE `raw_status` = '' AND `completion_status` <> 'NOT_COMPLETED') = 0;

-- ⑤d raw_status 为 NULL 绝不能滑成 NOT_COMPLETED —— 未知不得当未完成
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_order_completion`
         WHERE `raw_status` IS NULL AND `completion_status` <> 'UNKNOWN') = 0;

-- ⑤e 既非 'C' 也非空串的已知值一律 UNKNOWN
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_order_completion`
         WHERE `raw_status` IS NOT NULL AND `raw_status` NOT IN ('C','')
           AND `completion_status` <> 'UNKNOWN') = 0;

-- ── ⑥ provenance 必须完整：Fact 只能由实际观测到的 Source B 行产生 ──────────────────
--     由 Source A 反向捏造的行拿不到 source_row_id，本条即是 NOT_OBSERVED 不得物化的结构守卫
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_order_completion`
         WHERE IFNULL(`source_system`, '') = ''
            OR IFNULL(`source_row_id`, '') = ''
            OR `observed_at` IS NULL
            OR IFNULL(`sync_batch_id`, '') = '') = 0;

-- ⑥b source_system 必须是已登记且启用的数据源
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_order_completion` c
         WHERE NOT EXISTS (SELECT 1 FROM `mdp_source` s
                            WHERE s.`source_code` = c.`source_system`
                              AND s.`status` = 1)) = 0;

-- ── ⑦ 每条 Fact 必须同租户命中唯一的本地采购行 ──────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_order_completion` c
         WHERE NOT EXISTS (SELECT 1 FROM `mdp_std_purchase_order` o
                            WHERE o.`tenant_id` = c.`tenant_id`
                              AND o.`po_no` = c.`po_no`
                              AND o.`po_line` = c.`po_line`)) = 0;

-- ⑦b 禁止跨租户错配：同一 (po_no, po_line) 不得在 Fact 与本地 PO 之间分属不同租户
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_order_completion` c
          JOIN `mdp_std_purchase_order` o
            ON o.`po_no` = c.`po_no` AND o.`po_line` = c.`po_line`
         WHERE o.`tenant_id` <> c.`tenant_id`) = 0;

-- ── ⑧ 本批不得引入完成时间列 —— 当前无权威 ──────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`COLUMNS`
         WHERE `TABLE_SCHEMA` = DATABASE()
           AND `TABLE_NAME` = 'mdp_std_purchase_order_completion'
           AND `COLUMN_NAME` IN ('completion_time','completed_at','procurement_completion_time')) = 0;

-- ── ⑨ 本批不得建 Stage-3 聚合 / Read Model ──────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`TABLES`
         WHERE `TABLE_SCHEMA` = DATABASE()
           AND `TABLE_NAME` IN ('mdp_std_stage3_completion','dwd_stage3_completion',
                                'dwd_procurement_completion')) = 0;

-- ── ⑩ 上游批次资产不得回归 ──────────────────────────────────────────────────────────
--     1.0.541：IQC 标准事实层仍在且 Grain 未被改动
SELECT (SELECT GROUP_CONCAT(`COLUMN_NAME` ORDER BY `SEQ_IN_INDEX`)
          FROM `information_schema`.`STATISTICS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_std_iqc_inspection'
           AND `INDEX_NAME` = 'uk_mdp_std_iqc_insp') = 'tenant_id,source_row_id';

--     1.0.540：分录上的采购单号列仍在且仍可空
SELECT (SELECT COUNT(*) FROM `information_schema`.`COLUMNS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'qms_qcp_insappnentry'
           AND `COLUMN_NAME` = 'purchase_order_no' AND `IS_NULLABLE` = 'YES') = 1;

--     Source A 基础事实的唯一键不得被本批改动
SELECT (SELECT GROUP_CONCAT(`COLUMN_NAME` ORDER BY `SEQ_IN_INDEX`)
          FROM `information_schema`.`STATISTICS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_std_purchase_order'
           AND `INDEX_NAME` = 'uk_po_line') = 'tenant_id,po_no,po_line';