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

-- 1.0.517.verify.sql — 每条 SELECT 都必须返回 1

-- ── B1 · 三列到位、类型/可空性符合仓库约定 ─────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'dwd_requirement_examine_detail'
        AND COLUMN_NAME IN ('bom_level','material_role','is_current_flag')) = 3;

SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'dwd_requirement_examine_detail' AND COLUMN_NAME = 'bom_level'
        AND DATA_TYPE = 'int' AND IS_NULLABLE = 'YES') = 1;

-- 用 DATA_TYPE + CHARACTER_MAXIMUM_LENGTH 而不是 COLUMN_TYPE 字符串比较：
-- information_schema 在部分 MySQL 版本上是 utf8mb3，对它强加 COLLATE utf8mb4_bin 会直接报
-- "COLLATION 'utf8mb4_bin' is not valid for CHARACTER SET 'utf8mb3'"，让 verify 脚本自己把启动打挂。
-- 这两个列是数值/枚举型元数据，不需要区分大小写的比较。
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'dwd_requirement_examine_detail' AND COLUMN_NAME = 'material_role'
        AND DATA_TYPE = 'varchar' AND CHARACTER_MAXIMUM_LENGTH = 32 AND IS_NULLABLE = 'YES') = 1;

-- 布尔列必须是 tinyint NOT NULL DEFAULT 0（仓库约定：<noun>_flag，不用 bit、不用 is_* 裸名）
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'dwd_requirement_examine_detail' AND COLUMN_NAME = 'is_current_flag'
        AND DATA_TYPE = 'tinyint' AND IS_NULLABLE = 'NO' AND COLUMN_DEFAULT = '0') = 1;

-- 不得引入 bit 类型的布尔列
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'dwd_requirement_examine_detail' AND DATA_TYPE = 'bit') = 0;

-- 不得混入 S8 专属列（本表是通用中台权威表，不给单一消费方开后门）
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'dwd_requirement_examine_detail'
        AND (COLUMN_NAME LIKE 's8/_%' ESCAPE '/' OR COLUMN_NAME LIKE '%/_s8/_%' ESCAPE '/'
             OR COLUMN_NAME IN ('exception_id','rule_code','watch_id'))) = 0;

-- ── B1 · 索引支撑 (tenant_id, is_current_flag, morder_no) ───────────────────────────
SELECT IFNULL((SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dwd_requirement_examine_detail'
                 AND INDEX_NAME = 'idx_req_exam_current'), '')
       = 'tenant_id,is_current_flag,morder_no';

-- ── B3 · 标准列契约已写进 schema，且未新增别名/重算列 ───────────────────────────────
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'dwd_requirement_examine_detail'
        AND COLUMN_NAME IN ('need_count','use_qty','lack_qty')
        AND COLUMN_COMMENT LIKE '标准列%') = 3;

SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'dwd_requirement_examine_detail'
        AND COLUMN_NAME IN ('required_qty','satisfied_qty','shortage_qty')) = 0;

-- ── B3 · 齐套恒等式 lack + use = need 在现存数据上成立（空表也算通过）─────────────────
SELECT (SELECT COUNT(*) FROM `dwd_requirement_examine_detail`
        WHERE ABS(IFNULL(`lack_qty`,0) + IFNULL(`use_qty`,0) - IFNULL(`need_count`,0)) > 0.0001) = 0;

-- ── B7 · 前修行已清空（material_role IS NULL 精确等价于前修行）───────────────────────
SELECT (SELECT COUNT(*) FROM `dwd_requirement_examine_detail` WHERE `material_role` IS NULL) = 0;

-- ── B2 · 角色取值只允许两个枚举，且永不为空 ─────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `dwd_requirement_examine_detail`
        WHERE `material_role` NOT IN ('FINISHED_GOOD_ROOT','INPUT_MATERIAL')) = 0;

-- ── B2 · level 与角色一致：bom_level=1 必为根，bom_level<>1 必为投入料 ────────────────
SELECT (SELECT COUNT(*) FROM `dwd_requirement_examine_detail`
        WHERE (`bom_level` = 1 AND `material_role` <> 'FINISHED_GOOD_ROOT')
           OR (`bom_level` <> 1 AND `material_role` <> 'INPUT_MATERIAL')) = 0;

-- bom_level 为 NULL（脏值/缺失）时必须保守落 INPUT_MATERIAL，绝不能落成根
SELECT (SELECT COUNT(*) FROM `dwd_requirement_examine_detail`
        WHERE `bom_level` IS NULL AND `material_role` <> 'INPUT_MATERIAL') = 0;

-- ── B4 · 每个 (tenant, 归一 factory) 作用域内最多只有一个 current 批次 ────────────────
-- factory_id 必须归一后再分组：实测存量里 296189/1019060 行 factory_id IS NULL，
-- 且 594/3439 个批次同时含 factory_id=1 与 NULL，不归一会漏掉半个批次。
SELECT (SELECT COUNT(*) FROM (
          SELECT `tenant_id`, COALESCE(NULLIF(`factory_id`,0),1) AS f
          FROM `dwd_requirement_examine_detail`
          WHERE `is_current_flag` = 1
          GROUP BY 1, 2
          HAVING COUNT(DISTINCT `calc_batch_id`) > 1) x) = 0;

-- is_current_flag 只允许 0/1
SELECT (SELECT COUNT(*) FROM `dwd_requirement_examine_detail`
        WHERE `is_current_flag` NOT IN (0,1)) = 0;

-- ── 1.0.356 建立的作用域 CHECK 必须仍在（本批不得把它改掉）──────────────────────────
SELECT (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
        WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = 'dwd_requirement_examine_detail'
          AND CONSTRAINT_NAME = 'ck_dwd_requirement_valid_scope') = 1;

-- 原有唯一键未被本批动过
SELECT IFNULL((SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dwd_requirement_examine_detail'
                 AND INDEX_NAME = 'uk_dwd_req_exam_detail' AND NON_UNIQUE = 0), '')
       = 'tenant_id,row_id,calc_batch_id';