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

-- 1.0.539.verify.sql — HARD ASSERTIONS，每条 SELECT 都必须返回 1
--
-- ⚠️ AutoVersionUpdate 按分号切分本文件，并把【每一个切片】都当断言、要求返回 1。因此：
--      ① 不得放"仅供排查"的 informational 查询（它返回的是结果集，不是 1）
--      ② 文件必须以最后一条断言的分号结尾，末尾不能留纯注释块
--      ③ 注释里不得出现任何字面分号，否则会被切在错误的位置
--    正因为 runner 只认「返回 1」，它没有"告警"这一档 —— 只阻断或不阻断。
--    故全局卫生类观测放在同目录的 1.0.539.warnings.ops.sql（runner 不执行，人工按需跑）。
--
-- ── 断言口径（重要，改本文件前先读）────────────────────────────────────────────────
-- 只断言【本批 writer 实际控制的产出】，不断言全库状态。判据是「列的归属」：
--
--   本批新建、且只由 S3 标准层 INSERT 写入 → BATCH INVARIANT，必须阻断
--     · mdp_std_purchase_request.work_order
--     · mdp_std_purchase_request.sales_order_entry_id
--     · mdp_std_purchase_order.purchase_request_no
--
--   本批之前就存在、且已确认存在本批边界外的写入者 → GLOBAL HYGIENE，只观测不阻断
--     · mdp_std_purchase_order.work_order
--       （本批仅做过一次性归一，稳态不归本批管。实测租户 838 的 S3_MDP_FULL 批次
--          在贴源侧同批次同 source_biz_key 归一结果为 NULL 的前提下，标准层仍出现
--          26 行字面量 —— 该值不可能由当前标准层 INSERT 在该批次产生，写入者未定位）
--     · mdp_std_purchase_receipt.tenant_id
--       （写入者已定位：MaterialWarehouse/PurchaseReceiptMdpSyncService.cs，
--          批次前缀 S5_PUR_RCT_STD_，属 S5 模块边界，见 CLAUDE.md 第九节模块边界表）
--
-- 这两项失败都不能证明本批身份桥失败 —— 身份桥完全不经过它们：
--   PO.purchase_request_no → PR.pr_no → PR.sales_order_entry_id → mdp_std_so.order_entry_id
--
-- 注意 mdp_std_so 在 (tenant_id, order_entry_id) 上并不唯一（实测 25 组重复），
-- 所以凡是经它取订单的断言一律用 DISTINCT，不用行数。

-- ── ① PR 身份列已就位 ──────────────────────────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`COLUMNS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_std_purchase_request'
           AND `COLUMN_NAME` IN ('work_order','sales_order_entry_id')) = 2;

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

SELECT (SELECT COUNT(DISTINCT `INDEX_NAME`) FROM `information_schema`.`STATISTICS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_std_purchase_request'
           AND `INDEX_NAME` IN ('idx_std_pr_entry','idx_std_pr_wo')) = 2;

-- ── ② PO → PR 指针已就位 ───────────────────────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`COLUMNS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_std_purchase_order'
           AND `COLUMN_NAME` = 'purchase_request_no' AND `IS_NULLABLE` = 'YES') = 1;

SELECT (SELECT COUNT(*) FROM `information_schema`.`STATISTICS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_std_purchase_order'
           AND `INDEX_NAME` = 'idx_std_po_req') > 0;

-- ── ③ 收货 → PO 身份键与索引已就位 ─────────────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`COLUMNS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_std_purchase_receipt'
           AND `COLUMN_NAME` IN ('ord_nbr','ord_line')) = 2;

SELECT (SELECT COUNT(*) FROM `information_schema`.`STATISTICS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_std_purchase_receipt'
           AND `INDEX_NAME` = 'idx_std_rct_po') > 0;

-- ── ④ S3 标准层自身不得有非法租户 ──────────────────────────────────────────────────
--     只覆盖 S3 转换独占写入的两张表。收货表有 S5 writer，见 warnings.ops.sql
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_request`
         WHERE `tenant_id` IS NULL OR `tenant_id` <= 0) = 0;

SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_order`
         WHERE `tenant_id` IS NULL OR `tenant_id` <= 0) = 0;

-- ── ⑤ sales_order_entry_id 有值时必须能在同租户的 mdp_std_so 里命中 ────────────────
--     0 也算非法：无法确定订单行就必须留 NULL，不得落 0
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_request` p
         WHERE p.`sales_order_entry_id` IS NOT NULL
           AND (p.`sales_order_entry_id` <= 0
             OR NOT EXISTS (SELECT 1 FROM `mdp_std_so` s
                             WHERE s.`tenant_id` = p.`tenant_id`
                               AND s.`order_entry_id` = p.`sales_order_entry_id`))) = 0;

-- ── ⑥ PO → PR 桥可跑通（全程只读 mdp_std_*）────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_order` o
          JOIN `mdp_std_purchase_request` p
            ON p.`tenant_id` = o.`tenant_id` AND p.`pr_no` = o.`purchase_request_no`
         WHERE o.`purchase_request_no` IS NOT NULL) > 0;

-- ── ⑦ PO → 销售订单 桥可跑通（全程只读 mdp_std_*，不触碰任何源业务表）──────────────
SELECT (SELECT COUNT(DISTINCT s.`order_id`) FROM `mdp_std_purchase_order` o
          JOIN `mdp_std_purchase_request` p
            ON p.`tenant_id` = o.`tenant_id` AND p.`pr_no` = o.`purchase_request_no`
          JOIN `mdp_std_so` s
            ON s.`tenant_id` = p.`tenant_id` AND s.`order_entry_id` = p.`sales_order_entry_id`
         WHERE p.`sales_order_entry_id` IS NOT NULL) > 0;

-- ── ⑧ 收货 → PO 桥可跑通（收货侧贴源无 Req，必须经 PO 才能到 PR）───────────────────
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_receipt` r
          JOIN `mdp_std_purchase_order` o
            ON o.`tenant_id` = r.`tenant_id` AND o.`po_no` = r.`ord_nbr` AND o.`po_line` = r.`ord_line`) > 0;

-- ── ⑨ 无跨租户命中 ─────────────────────────────────────────────────────────────────
--     ⑤ 已保证"同租户能命中"，这里补的是"绝不能靠别的租户命中"
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_request` p
          JOIN `mdp_std_so` s ON s.`order_entry_id` = p.`sales_order_entry_id`
         WHERE p.`sales_order_entry_id` IS NOT NULL
           AND s.`tenant_id` <> p.`tenant_id`) = 0;

SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_order` o
          JOIN `mdp_std_purchase_request` p ON p.`pr_no` = o.`purchase_request_no`
         WHERE o.`purchase_request_no` IS NOT NULL
           AND p.`tenant_id` <> o.`tenant_id`
           AND NOT EXISTS (SELECT 1 FROM `mdp_std_purchase_request` q
                            WHERE q.`tenant_id` = o.`tenant_id`
                              AND q.`pr_no` = o.`purchase_request_no`)) = 0;

-- ── ⑩ 本批 writer 独占的身份列不得出现字面量 'null' 或空串 ─────────────────────────
--     这三列全部由 1.0.539 新建，只有 S3 标准层 INSERT 写它们 → 属 BATCH INVARIANT
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_request`
         WHERE `work_order` IN ('null', '')) = 0;

SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_order`
         WHERE `purchase_request_no` IN ('null', '')) = 0;

-- ── ⑪ 归因 Grain 必须是 PO 行：一条 PO 行不得被归给多张销售订单 ─────────────────────
SELECT (SELECT COUNT(*) FROM (
          SELECT o.`tenant_id`, o.`po_no`, o.`po_line`
            FROM `mdp_std_purchase_order` o
            JOIN `mdp_std_purchase_request` p
              ON p.`tenant_id` = o.`tenant_id` AND p.`pr_no` = o.`purchase_request_no`
            JOIN `mdp_std_so` s
              ON s.`tenant_id` = p.`tenant_id` AND s.`order_entry_id` = p.`sales_order_entry_id`
           WHERE p.`sales_order_entry_id` IS NOT NULL
           GROUP BY o.`tenant_id`, o.`po_no`, o.`po_line`
          HAVING COUNT(DISTINCT s.`order_id`) > 1) t) = 0;

-- ── ⑫ 投影 / 派生正确性：本批三列必须逐行等于其定义式 ──────────────────────────────
--     这才是本批能担保的东西。刻意【不】断言"PO 的指针一定能找到 PR"——
--     PR 行能否进标准层取决于转换既有的工厂谓词
--     COALESCE(NULLIF(g.factory_id,0),1)=@FactoryId，与本批投影无关：
--     实测 PR20260903080128786 贴源 factory_id=838257186320453，被 @FactoryId=1 挡下，
--     而其 PO 行 factory_id=1 得以入库，于是指针悬空。那是既有作用域行为，
--     不是本批的投影缺陷，断言它等于替边界外逻辑背书。

-- (a) PO.purchase_request_no 必须逐行等于贴源 $.Req 的归一值
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_order` o
          JOIN `mdp_stg_purchase_order` g
            ON g.`tenant_id` = o.`tenant_id` AND g.`source_biz_key` = o.`source_biz_key`
           AND g.`source_table` = 'PurOrdDetail'
         WHERE IFNULL(o.`purchase_request_no`, '#')
            <> IFNULL(NULLIF(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(g.`raw_data`, '$.Req')), 'null'), ''), '#')) = 0;

-- (b) PR.work_order 必须逐行等于贴源 $.pr_mono 的归一值
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_request` p
          JOIN `mdp_stg_supply_demand` g
            ON g.`tenant_id` = p.`tenant_id` AND g.`source_biz_key` = p.`source_biz_key`
           AND g.`source_table` = 'srm_pr_main'
         WHERE IFNULL(p.`work_order`, '#')
            <> IFNULL(NULLIF(NULLIF(JSON_UNQUOTE(JSON_EXTRACT(g.`raw_data`, '$.pr_mono')), 'null'), ''), '#')) = 0;

-- (c) PR.sales_order_entry_id 必须逐行等于同租户工单桥的派生值，不得自行发明
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_request` p
          LEFT JOIN `mdp_std_work_order_schedule` w
            ON w.`tenant_id` = p.`tenant_id` AND w.`work_order` = p.`work_order`
         WHERE p.`work_order` IS NOT NULL
           AND IFNULL(p.`sales_order_entry_id`, -1) <> IFNULL(w.`sales_order_entry_id`, -1)) = 0;

-- (d) 没有工单号就不可能有订单行 Id —— 杜绝绕过工单桥的野路子赋值
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_request`
         WHERE `work_order` IS NULL AND `sales_order_entry_id` IS NOT NULL) = 0;

-- ── ⑬ 本批不得建 Stage-3 KPI 事实表 ────────────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`TABLES`
         WHERE `TABLE_SCHEMA` = DATABASE()
           AND `TABLE_NAME` IN ('dwd_procurement_kpi','dwd_purchase_kpi')) = 0;

-- ── ⑭ 本批不得引入 Stage-3 完成事件 ────────────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `aidop_action_run_log`
         WHERE `action_code` = 'S3_PROCUREMENT_COMPLETE') = 0;

SELECT (SELECT COUNT(*) FROM `mdp_std_action_event`
         WHERE `action_code` = 'S3_PROCUREMENT_COMPLETE') = 0;

-- ── ⑮ Stage 1 / Stage 2 未被本批触碰 ───────────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`TABLES`
         WHERE `TABLE_SCHEMA` = DATABASE()
           AND `TABLE_NAME` IN ('dwd_order_review_kpi','dwd_product_design_kpi')) = 2;

SELECT (SELECT COUNT(*) FROM `dwd_order_review_kpi` k
         WHERE k.`tenant_id` IS NULL OR k.`tenant_id` <= 0
            OR NOT EXISTS (SELECT 1 FROM `mdp_std_so` s
                            WHERE s.`tenant_id` = k.`tenant_id`
                              AND s.`order_id` = k.`sales_order_id`)) = 0;

SELECT (SELECT COUNT(*) FROM `dwd_product_design_kpi` p
         WHERE p.`tenant_id` IS NULL OR p.`tenant_id` <= 0
            OR NOT EXISTS (SELECT 1 FROM `mdp_std_so` s
                            WHERE s.`tenant_id` = p.`tenant_id`
                              AND s.`order_id` = p.`sales_order_id`)) = 0;