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

-- 1.0.540.verify.sql — HARD ASSERTIONS，每条 SELECT 都必须返回 1
--
-- ⚠️ AutoVersionUpdate 按分号切分本文件，并把【每一个切片】都当断言、要求返回 1。因此：
--      ① 不得放"仅供排查"的 informational 查询
--      ② 文件必须以最后一条断言的分号结尾，末尾不能留纯注释块
--      ③ 注释里不得出现任何字面分号
--
-- 口径：本批只做【文档级】采购单身份，只断言本批 writer 独占的那一列。
--   行级身份（PO Line）属 SOURCE CONTRACT GAP —— 上游 FSRCBILLID / FSRCBILLENTRYSEQ 实测 0/4 全空，
--   本文件因此【刻意不断言】任何 PO 行号，也不断言覆盖率（覆盖率取决于热关注跑批调度）。

-- ── ① 新列已就位，且必须可空 ───────────────────────────────────────────────────────
--     可空是硬要求：本库 14 行 fixture 分录在上游已不存在，没有权威采购单号可填
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;

SELECT (SELECT COUNT(*) FROM `information_schema`.`COLUMNS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'qms_qcp_insappnentry'
           AND `COLUMN_NAME` = 'purchase_order_no'
           AND `DATA_TYPE` = 'varchar') = 1;

-- ── ② 不得有默认值：宁可为空，不造假 ───────────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`COLUMNS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'qms_qcp_insappnentry'
           AND `COLUMN_NAME` = 'purchase_order_no'
           AND `COLUMN_DEFAULT` IS NOT NULL) = 0;

-- ── ③ 本批不得新增任何 PO 行号载体 ─────────────────────────────────────────────────
--     行级缺口必须由上游补 FSRCBILLID / FSRCBILLENTRYSEQ，不允许本地自造列顶替
SELECT (SELECT COUNT(*) FROM `information_schema`.`COLUMNS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'qms_qcp_insappnentry'
           AND `COLUMN_NAME` IN ('purchase_order_line','po_line','purchase_order_entry_seq',
                                 'source_entry_id','receipt_line')) = 0;

-- ── ④ 既有语义不被破坏：FSRCORDERNUM 仍是收货单号，不得被写成采购单号 ──────────────
--     判据：凡两列都非空的行，二者不得相等（相等即说明有人把采购单号灌进了收货号列）
SELECT (SELECT COUNT(*) FROM `qms_qcp_insappnentry`
         WHERE `purchase_order_no` IS NOT NULL AND `purchase_order_no` <> ''
           AND `FSRCORDERNUM` IS NOT NULL AND `FSRCORDERNUM` <> ''
           AND `FSRCORDERNUM` = `purchase_order_no`) = 0;

-- ── ⑤ 不得落字面量 'null' / 空串 / 带空白（writer 已做 Trim 归一）──────────────────
SELECT (SELECT COUNT(*) FROM `qms_qcp_insappnentry`
         WHERE `purchase_order_no` IN ('null', '')
            OR `purchase_order_no` <> TRIM(`purchase_order_no`)) = 0;

-- ── ⑥ 有采购单号的行，租户必须合法 ─────────────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `qms_qcp_insappnentry`
         WHERE `purchase_order_no` IS NOT NULL
           AND (`tenant_id` IS NULL OR `tenant_id` <= 0)) = 0;

-- ── ⑦ 采购单号若能命中标准层，必须是【同租户】命中 ─────────────────────────────────
--     禁止只按单号跨租户关联。表述为：存在跨租户命中、却不存在同租户命中的行 = 0
SELECT (SELECT COUNT(*) FROM `qms_qcp_insappnentry` e
         WHERE e.`purchase_order_no` IS NOT NULL
           AND EXISTS (SELECT 1 FROM `mdp_std_purchase_order` o
                        WHERE o.`po_no` = e.`purchase_order_no` AND o.`tenant_id` <> e.`tenant_id`)
           AND NOT EXISTS (SELECT 1 FROM `mdp_std_purchase_order` o2
                            WHERE o2.`po_no` = e.`purchase_order_no` AND o2.`tenant_id` = e.`tenant_id`)) = 0;

-- ── ⑧ 一条分录只能有一个采购单号（列本身保证），且不得出现重复放大 ──────────────────
--     id 是主键，行数必须等于去重后的 id 数 —— 防止镜像扇出造出重复分录
SELECT (SELECT COUNT(*) FROM `qms_qcp_insappnentry`) =
       (SELECT COUNT(DISTINCT `id`) FROM `qms_qcp_insappnentry`);

-- ── ⑨ 本批不得改动 IQC 质量语义：pd 仍只允许既有取值 ───────────────────────────────
SELECT (SELECT COUNT(*) FROM `qms_qcp_inspbill`
         WHERE `pd` IS NOT NULL AND `pd` NOT IN (0, 1)) = 0;

-- ── ⑩ 1.0.536 采购身份桥不得回归：PR 的订单行 Id 若有值必须仍能同租户命中 ────────────
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;

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

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