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

-- 1.0.504.verify.sql
-- 契约（CURRENT.md > F-11，由 AutoVersionUpdate.RunVerifyScriptIfExists 逐条执行取首行首列判真）：
--   ① 禁止 SET @var（返回 null 即判失败）→ 一律内联子查询，每条语句自包含
--   ② 每条返回「通过为真」的标量（用 = 期望值 得 1/0），不得返回错误条数
--   ③ 一条语句一个断言，不用 UNION 汇总
--   ④ 文件以最后一条 SQL 的分号结束，末尾不得残留 comment-only 块
-- 本文件只做结构级断言：migration 执行时 dim 尚无数据，数据正确性由 S0DimReconciler 在刷新时验证。
--
-- 断言强度说明（Final Review 补强，勿弱化）：
--   · UK 一律断言「精确列序」而非列数——脚本用 CREATE TABLE IF NOT EXISTS，
--     若目标库已存在同名表但 UK 列不同，建表会静默 no-op，只数列数会放过结构漂移。
--   · 字符串等值一律加 COLLATE utf8mb4_bin——表默认 utf8mb4_0900_ai_ci 大小写不敏感，
--     而 raw_data 的 JSON 路径大小写正是致命维度（货架若写成 PascalCase 会静默退化成 GUID）。

SELECT (SELECT COUNT(*) FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME IN ('mdp_stg_s0_work_center','mdp_stg_s0_department','mdp_stg_s0_location')) = 3;

SELECT (SELECT COUNT(*) FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME IN ('dim_work_center','dim_department','dim_location','dim_location_shelf')) = 4;

SELECT IFNULL((SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
               FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mdp_stg_s0_work_center'
                 AND INDEX_NAME = 'uk_stg_s0_work_center' AND NON_UNIQUE = 0), '')
       = 'tenant_id,source_system,source_table,source_row_id';

SELECT IFNULL((SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
               FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mdp_stg_s0_department'
                 AND INDEX_NAME = 'uk_stg_s0_department' AND NON_UNIQUE = 0), '')
       = 'tenant_id,source_system,source_table,source_row_id';

SELECT IFNULL((SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
               FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mdp_stg_s0_location'
                 AND INDEX_NAME = 'uk_stg_s0_location' AND NON_UNIQUE = 0), '')
       = 'tenant_id,source_system,source_table,source_row_id';

SELECT IFNULL((SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
               FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_work_center'
                 AND INDEX_NAME = 'uk_dim_work_center' AND NON_UNIQUE = 0), '')
       = 'tenant_id,domain_code,work_center_code';

SELECT IFNULL((SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
               FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_department'
                 AND INDEX_NAME = 'uk_dim_department' AND NON_UNIQUE = 0), '')
       = 'tenant_id,domain_code,department_code';

SELECT IFNULL((SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
               FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_location'
                 AND INDEX_NAME = 'uk_dim_location' AND NON_UNIQUE = 0), '')
       = 'tenant_id,domain_code,location_code';

SELECT IFNULL((SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
               FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_location_shelf'
                 AND INDEX_NAME = 'uk_dim_location_shelf' AND NON_UNIQUE = 0), '')
       = 'tenant_id,domain_code,location_code,shelf_code';

SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME IN ('dim_work_center','dim_department','dim_location','dim_location_shelf')
          AND COLUMN_NAME = 'tenant_id' AND IS_NULLABLE = 'NO') = 4;

SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME IN ('mdp_stg_s0_work_center','mdp_stg_s0_department','mdp_stg_s0_location')
          AND COLUMN_NAME = 'tenant_id' AND IS_NULLABLE = 'NO') = 3;

SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME IN ('mdp_stg_s0_work_center','mdp_stg_s0_department','mdp_stg_s0_location')
          AND COLUMN_NAME = 'source_biz_key' AND CHARACTER_MAXIMUM_LENGTH = 300) = 3;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` IN ('S0_WORK_CENTER','S0_DEPARTMENT','S0_LOCATION','S0_LOCATION_SHELF')
          AND `status` = 1 AND `sync_mode` = 'FULL' AND `incr_column` IS NULL) = 4;

SELECT (SELECT COUNT(*) FROM `mdp_entity` e JOIN `mdp_source` s ON s.`id` = e.`source_id`
        WHERE e.`entity_code` IN ('S0_WORK_CENTER','S0_DEPARTMENT','S0_LOCATION','S0_LOCATION_SHELF')
          AND s.`source_code` = 'AIDOPDEV_MYSQL') = 4;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_WORK_CENTER'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'Domain,WorkCtr'
          AND `source_table_name` COLLATE utf8mb4_bin = 'WorkCtrMaster'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_work_center') = 1;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_DEPARTMENT'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'Domain,Department'
          AND `source_table_name` COLLATE utf8mb4_bin = 'DepartmentMaster'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_department') = 1;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_LOCATION'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'Domain,location'
          AND `source_table_name` COLLATE utf8mb4_bin = 'LocationMaster'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_location') = 1;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_LOCATION_SHELF'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'domain_code,location,inv_shelf'
          AND `source_table_name` COLLATE utf8mb4_bin = 'LocationShelfMaster'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_location') = 1;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `target_table_name` = 'mdp_stg_location'
          AND `entity_code` LIKE 'S0\_%') = 0;