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

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

-- ── ① 表与唯一键就位，且 Grain = (tenant_id, source_row_id) ────────────────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`TABLES`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_std_iqc_inspection') = 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_iqc_inspection'
           AND `INDEX_NAME` = 'uk_mdp_std_iqc_insp') = 'tenant_id,source_row_id';

-- ── ② 两条正交轴必须都在：不得只留一个 is_completed 布尔 ───────────────────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`COLUMNS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_std_iqc_inspection'
           AND `COLUMN_NAME` IN ('raw_pd','quality_result','flow_status','completion_time')) = 4;

-- ── ③ 本批绝不得引入任何行级身份列 ─────────────────────────────────────────────────
--     上游 FSRCBILLID / FSRCBILLENTRYSEQ 实测全空，行级归因属 SOURCE CONTRACT GAP
SELECT (SELECT COUNT(*) FROM `information_schema`.`COLUMNS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_std_iqc_inspection'
           AND `COLUMN_NAME` IN ('po_line','purchase_order_line','receipt_line',
                                 'source_entry_seq','source_bill_entry_seq')) = 0;

-- ── ④ 租户合法：本批 Writer 只投 tenant_id > 0 的检验单 ────────────────────────────
SELECT (SELECT COUNT(*) FROM `mdp_std_iqc_inspection`
         WHERE `tenant_id` IS NULL OR `tenant_id` <= 0) = 0;

-- ── ⑤ 质量映射必须严格来自 raw_pd ──────────────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `mdp_std_iqc_inspection`
         WHERE `raw_pd` = 0 AND `quality_result` <> 'PASS') = 0;

SELECT (SELECT COUNT(*) FROM `mdp_std_iqc_inspection`
         WHERE `raw_pd` = 1 AND `quality_result` <> 'FAIL') = 0;

-- ⑤c pd 为 NULL 时绝不能是 PASS —— 这是「未判定不得滑成合格」的硬底线
SELECT (SELECT COUNT(*) FROM `mdp_std_iqc_inspection`
         WHERE `raw_pd` IS NULL AND `quality_result` <> 'PENDING') = 0;

-- ⑤d 取值域封闭
SELECT (SELECT COUNT(*) FROM `mdp_std_iqc_inspection`
         WHERE `quality_result` NOT IN ('PASS','FAIL','PENDING')) = 0;

-- ── ⑥ 流程与质量是正交轴：流程完成不得被当成质量合格 ───────────────────────────────
--     判据：存在 flow_status=2（Approved）却 quality_result='FAIL' 的行是【合法】的，
--     所以这里不能断言二者一致。真正要守的是反向：不得因为流程完成就把 PASS 写上去。
--     表述为：quality_result 必须能由 raw_pd 单独推出（⑤ 已保证），此处补断流程列不污染质量列。
SELECT (SELECT COUNT(*) FROM `mdp_std_iqc_inspection`
         WHERE `quality_result` = 'PASS' AND (`raw_pd` IS NULL OR `raw_pd` <> 0)) = 0;

-- ── ⑦ 完成时间只能来自终态审批流，Running 状态不得伪造 ─────────────────────────────
SELECT (SELECT COUNT(*) FROM `mdp_std_iqc_inspection`
         WHERE `completion_time` IS NOT NULL AND IFNULL(`flow_status`, -1) <> 2) = 0;

-- ⑦b 完成时间必须逐行等于被采纳实例的 EndTime，不得自造
SELECT (SELECT COUNT(*) FROM `mdp_std_iqc_inspection` s
          JOIN `ApprovalFlowInstance` i ON i.`Id` = s.`flow_instance_id`
         WHERE s.`completion_time` IS NOT NULL
           AND s.`completion_time` <> i.`EndTime`) = 0;

-- ── ⑧ 租户 Authority 单向：投出来的租户必须等于源检验单的租户 ───────────────────────
--     审批流表没有 TenantId，绝不允许它反向决定租户
SELECT (SELECT COUNT(*) FROM `mdp_std_iqc_inspection` s
          JOIN `qms_qcp_inspbill` b ON b.`id` = s.`iqc_bill_id`
         WHERE b.`tenant_id` <> s.`tenant_id`) = 0;

-- ── ⑨ 收货单号与采购单号互不覆盖 ───────────────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `mdp_std_iqc_inspection`
         WHERE `receipt_no` IS NOT NULL AND `purchase_order_no` IS NOT NULL
           AND `receipt_no` = `purchase_order_no`) = 0;

-- ⑨b 采购单号必须逐行等于分录上的权威值（1.0.540 所写），不得另行推导
SELECT (SELECT COUNT(*) FROM `mdp_std_iqc_inspection` s
          JOIN `qms_qcp_insappnentry` e
            ON e.`id` = s.`application_entry_id` AND e.`tenant_id` = s.`tenant_id`
         WHERE IFNULL(s.`purchase_order_no`, '#') <> IFNULL(NULLIF(e.`purchase_order_no`, ''), '#')) = 0;

-- ⑨c 收货单号同理
SELECT (SELECT COUNT(*) FROM `mdp_std_iqc_inspection` s
          JOIN `qms_qcp_insappnentry` e
            ON e.`id` = s.`application_entry_id` AND e.`tenant_id` = s.`tenant_id`
         WHERE IFNULL(s.`receipt_no`, '#') <> IFNULL(NULLIF(e.`FSRCORDERNUM`, ''), '#')) = 0;

-- ── ⑩ 分录只能经 hid 单值外键解析，不得"从申请单下挑一条" ──────────────────────────
SELECT (SELECT COUNT(*) FROM `mdp_std_iqc_inspection` s
          JOIN `qms_qcp_inspbill` b ON b.`id` = s.`iqc_bill_id`
         WHERE IFNULL(s.`application_entry_id`, -1) <> IFNULL(b.`hid`, -1)) = 0;

-- ── ⑪ 无采购身份的行必须如实为 NULL，禁止猜值 ──────────────────────────────────────
--     判据：分录本身没有采购单号时，标准层也必须是 NULL
SELECT (SELECT COUNT(*) FROM `mdp_std_iqc_inspection` s
          LEFT JOIN `qms_qcp_insappnentry` e
                 ON e.`id` = s.`application_entry_id` AND e.`tenant_id` = s.`tenant_id`
         WHERE s.`purchase_order_no` IS NOT NULL
           AND (e.`id` IS NULL OR IFNULL(e.`purchase_order_no`, '') = '')) = 0;

-- ── ⑫ Canonical 无重复：一张检验单只能一行 ─────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `mdp_std_iqc_inspection`) =
       (SELECT COUNT(DISTINCT `tenant_id`, `iqc_bill_id`) FROM `mdp_std_iqc_inspection`);

-- ── ⑬ 不得落字面量 'null' / 空串 ───────────────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `mdp_std_iqc_inspection`
         WHERE `purchase_order_no` IN ('null','') OR `receipt_no` IN ('null','')
            OR `iqc_bill_no` IN ('null','') OR `material_code` IN ('null','')) = 0;

-- ── ⑭ 本批不得建 Stage-3 聚合事实 / 完成事件 ───────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`TABLES`
         WHERE `TABLE_SCHEMA` = DATABASE()
           AND `TABLE_NAME` IN ('dwd_iqc_completion','dwd_procurement_kpi','mdp_std_stage3_completion')) = 0;

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

-- ── ⑮ 上游批次的既有资产不得回归 ───────────────────────────────────────────────────
--     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;

--     1.0.536：采购身份桥的订单行 Id 若有值仍须同租户命中
SELECT (SELECT COUNT(*) FROM `mdp_std_purchase_request` p
         WHERE p.`sales_order_entry_id` IS NOT NULL
           AND 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;