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

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

SELECT (SELECT COUNT(*) FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME IN ('mdp_stg_s0_employee','mdp_stg_s0_line')) = 2;

SELECT (SELECT COUNT(*) FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME IN ('dim_employee','dim_line')) = 2;

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_employee'
                 AND INDEX_NAME = 'uk_stg_s0_employee' 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_line'
                 AND INDEX_NAME = 'uk_stg_s0_line' 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_employee'
                 AND INDEX_NAME = 'uk_dim_employee' AND NON_UNIQUE = 0), '')
       = 'tenant_id,employee_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_line'
                 AND INDEX_NAME = 'uk_dim_line' AND NON_UNIQUE = 0), '')
       = 'tenant_id,line_code';

SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME IN ('dim_employee','dim_line')
          AND COLUMN_NAME = 'tenant_id' AND IS_NULLABLE = 'NO') = 2;

SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME IN ('mdp_stg_s0_employee','mdp_stg_s0_line')
          AND COLUMN_NAME = 'tenant_id' AND IS_NULLABLE = 'NO') = 2;

SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME IN ('mdp_stg_s0_employee','mdp_stg_s0_line')
          AND COLUMN_NAME = 'source_biz_key' AND CHARACTER_MAXIMUM_LENGTH = 300) = 2;

SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_employee'
          AND COLUMN_NAME = 'sys_user_id' AND IS_NULLABLE = 'YES') = 1;

SELECT (SELECT COUNT(*) FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_employee'
          AND NON_UNIQUE = 0 AND COLUMN_NAME = 'sys_user_id') = 0;

SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_employee'
          AND COLUMN_NAME = 'department_code' AND IS_NULLABLE = 'YES') = 1;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` IN ('S0_EMPLOYEE','S0_LINE')
          AND `status` = 1 AND `sync_mode` = 'FULL' AND `incr_column` IS NULL) = 2;

SELECT (SELECT COUNT(*) FROM `mdp_entity` e JOIN `mdp_source` s ON s.`id` = e.`source_id`
        WHERE e.`entity_code` IN ('S0_EMPLOYEE','S0_LINE')
          AND s.`source_code` = 'AIDOPDEV_MYSQL') = 2;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_EMPLOYEE'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'Employee'
          AND `source_table_name` COLLATE utf8mb4_bin = 'EmployeeMaster'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_employee'
          AND `batch_size` > 613) = 1;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_LINE'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'Line'
          AND `source_table_name` COLLATE utf8mb4_bin = 'LineMaster'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_line'
          AND `batch_size` > 109) = 1;

SELECT (SELECT COUNT(*) FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_employee'
          AND INDEX_NAME = 'uk_dim_employee'
          AND COLUMN_NAME IN ('domain_code','company_id','factory_id','sys_user_id')) = 0;

SELECT (SELECT COUNT(*) FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_line'
          AND INDEX_NAME = 'uk_dim_line'
          AND COLUMN_NAME IN ('domain_code','company_id','factory_id')) = 0;

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