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

-- 1.0.551.verify.sql
--   P0-A 数据纠偏的执行后校验。返回单列 ok：1 = 全部断言通过，0 = 任一失败。
--   提交前随主脚本一并 rename 为 1.0.<next>.verify.sql 并注册 csproj Copy 条目。
--
--   V1 std 种群 A 归零且仅剩真实 PW
--   V2 DWD 行数等于保全子集行数
--   V3 po_no / po_line 无 NULL 且无空串
--   V4 新唯一键下无重复
--   V5 唯一键 DDL 真实存在且列序正确
--   V6 租户守卫 CHECK 恰好一套、无语义重复
--   V7 保全历史与迁移前子集完全一致
--   V8 非法租户未混入
SELECT CASE WHEN

-- V1 · std：种群 A 归零
    (
      SELECT COUNT(*) FROM `mdp_std_process_outsource_order`
      WHERE `sync_batch_id` LIKE 'S3\_MDP\_%' AND `source_biz_key` NOT LIKE '%|%'
        AND `work_order` = '' AND `po_no` IS NULL AND `po_line` IS NULL
    ) = 0

-- V1b · std：幸存者全部是真实 PW（biz_key 含管道符且 po_no 非空）
AND (
      SELECT COUNT(*) FROM `mdp_std_process_outsource_order`
      WHERE `source_biz_key` NOT LIKE '%|%' OR IFNULL(`po_no`,'') = ''
    ) = 0

-- V1c · std：幸存者数量必须大于 0（防止误把整表清空）
AND (SELECT COUNT(*) FROM `mdp_std_process_outsource_order`) > 0

-- V2 · DWD 行数 == 保全子集行数（迁移刚完成时，尚未跑新批次）
AND (SELECT COUNT(*) FROM `dwd_process_outsource_delivery`)
  = (SELECT COUNT(*) FROM `dwd_process_outsource_delivery_keep_p0a`
     WHERE `tenant_id` NOT IN (0,1,1300000000001)
       AND IFNULL(`po_no`,'') <> '' AND IFNULL(`po_line`,'') <> '')

-- V3 · NULL / 空串：收口后 po_no / po_line 既不为 NULL 也不为空串
AND (SELECT COUNT(*) FROM `dwd_process_outsource_delivery` WHERE `po_no`   IS NULL) = 0
AND (SELECT COUNT(*) FROM `dwd_process_outsource_delivery` WHERE `po_line` IS NULL) = 0
AND (SELECT COUNT(*) FROM `dwd_process_outsource_delivery` WHERE `po_no`   = '')    = 0
AND (SELECT COUNT(*) FROM `dwd_process_outsource_delivery` WHERE `po_line` = '')    = 0

-- V3b · 列定义确已收口为 NOT NULL
AND (
      SELECT COUNT(*) FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_process_outsource_delivery'
        AND COLUMN_NAME IN ('po_no','po_line') AND IS_NULLABLE='YES'
    ) = 0

-- V4 · 新唯一键下无重复
AND (
      SELECT COUNT(*) FROM (
        SELECT 1 FROM `dwd_process_outsource_delivery`
        GROUP BY `tenant_id`,`factory_id`,`stat_date`,`po_no`,`po_line`
        HAVING COUNT(*) > 1
      ) dup
    ) = 0

-- V5 · 唯一键 DDL 存在、恰好 5 列、且列序完全正确
AND (
      SELECT COUNT(*) FROM information_schema.STATISTICS
      WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_process_outsource_delivery'
        AND INDEX_NAME='uk_po_line_stat' AND NON_UNIQUE=0
    ) = 5
AND (
      SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ',')
      FROM information_schema.STATISTICS
      WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_process_outsource_delivery'
        AND INDEX_NAME='uk_po_line_stat'
    ) = 'tenant_id,factory_id,stat_date,po_no,po_line'

-- V5b · 旧唯一键已移除（否则膨胀语义仍在）
AND (
      SELECT COUNT(*) FROM information_schema.STATISTICS
      WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_process_outsource_delivery'
        AND INDEX_NAME='uk_work_op_stat'
    ) = 0

-- V6 · 租户守卫 CHECK 恰好一套，且名字是稳定名（无语义重复）
AND (
      SELECT COUNT(*) FROM information_schema.CHECK_CONSTRAINTS cc
      JOIN information_schema.TABLE_CONSTRAINTS tc
        ON tc.CONSTRAINT_SCHEMA=cc.CONSTRAINT_SCHEMA AND tc.CONSTRAINT_NAME=cc.CONSTRAINT_NAME
      WHERE tc.TABLE_SCHEMA=DATABASE() AND tc.TABLE_NAME='dwd_process_outsource_delivery'
        AND tc.CONSTRAINT_TYPE='CHECK'
        AND cc.CHECK_CLAUSE LIKE '%tenant_id%' AND cc.CHECK_CLAUSE LIKE '%1300000000001%'
    ) = 1
AND (
      SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
      WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='dwd_process_outsource_delivery'
        AND CONSTRAINT_TYPE='CHECK' AND CONSTRAINT_NAME='ck_dwd_process_outsource_valid_tenant'
    ) = 1

-- V7 · 保全历史与迁移前子集完全一致（按业务键集合双向比对，差集为 0）
AND (
      SELECT COUNT(*) FROM `dwd_process_outsource_delivery_keep_p0a` k
      WHERE k.`tenant_id` NOT IN (0,1,1300000000001)
        AND IFNULL(k.`po_no`,'') <> '' AND IFNULL(k.`po_line`,'') <> ''
        AND NOT EXISTS (
          SELECT 1 FROM `dwd_process_outsource_delivery` d
          WHERE d.`tenant_id`=k.`tenant_id` AND d.`factory_id`=k.`factory_id`
            AND d.`stat_date`=k.`stat_date` AND d.`po_no`=k.`po_no` AND d.`po_line`=k.`po_line`
        )
    ) = 0
AND (
      SELECT COUNT(*) FROM `dwd_process_outsource_delivery` d
      WHERE NOT EXISTS (
          SELECT 1 FROM `dwd_process_outsource_delivery_keep_p0a` k
          WHERE k.`tenant_id`=d.`tenant_id` AND k.`factory_id`=d.`factory_id`
            AND k.`stat_date`=d.`stat_date` AND k.`po_no`=d.`po_no` AND k.`po_line`=d.`po_line`
        )
    ) = 0

-- V8 · 非法租户未混入 DWD
AND (SELECT COUNT(*) FROM `dwd_process_outsource_delivery` WHERE `tenant_id` IN (0,1,1300000000001)) = 0

-- V9 · 元数据留证已写入，且记录了清理前的量级
AND (
      SELECT COUNT(*) FROM `aidop_migration_derived_cleanup_log`
      WHERE `migration_version`='1.0.551' AND `table_name`='dwd_process_outsource_delivery'
        AND `estimated_rows` > 1000000
    ) = 1

-- V10 · std 备份表存在且非空（回滚路径可用）
AND (SELECT COUNT(*) FROM `mdp_std_process_outsource_order_bak_p0a`) > 0

THEN 1 ELSE 0 END AS ok;