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

-- 1.0.509.verify.sql
-- 契约（CURRENT.md > F-11）：禁 SET @var · 每条返回 1/0 · 一条一断言 · 末条为真 SELECT。
-- 断言强度沿用 Phase 1 Final Review：UK 断言**精确列序**（CREATE TABLE IF NOT EXISTS 会静默 no-op，
-- 只数列数会放过结构漂移）；字符串等值一律 COLLATE utf8mb4_bin（biz_key_expr 大小写是致命维度）。


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

SELECT (SELECT COUNT(*) FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ('bridge_employee_work_duty','dim_line_post','bridge_line_post_skill')) = 3;

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_emp_work_duty'
                 AND INDEX_NAME = 'uk_stg_s0_emp_work_duty' 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_post'
                 AND INDEX_NAME = 'uk_stg_s0_line_post' 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 = 'bridge_employee_work_duty'
                 AND INDEX_NAME = 'uk_bridge_employee_work_duty' AND NON_UNIQUE = 0), '')
       = 'tenant_id,employee_code,duty,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_line_post'
                 AND INDEX_NAME = 'uk_dim_line_post' AND NON_UNIQUE = 0), '')
       = 'tenant_id,line_code,job_no';

SELECT IFNULL((SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
               FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'bridge_line_post_skill'
                 AND INDEX_NAME = 'uk_bridge_line_post_skill' AND NON_UNIQUE = 0), '')
       = 'tenant_id,line_code,job_no,skill_code';

SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ('bridge_employee_work_duty','dim_line_post','bridge_line_post_skill')
          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_emp_work_duty','mdp_stg_s0_line_post')
          AND COLUMN_NAME = 'tenant_id' AND IS_NULLABLE = 'NO') = 2;

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

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

SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_line_post'
          AND COLUMN_NAME = 'run_crew' AND NUMERIC_PRECISION = 10 AND NUMERIC_SCALE = 3) = 1;

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

SELECT (SELECT COUNT(*) FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'bridge_line_post_skill'
          AND NON_UNIQUE = 0
          AND COLUMN_NAME IN ('legacy_line_skill_rec_id','legacy_person_skill_id')) = 0;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_EMP_WORK_DUTY'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'Employee,Duty,Location'
          AND `source_table_name` COLLATE utf8mb4_bin = 'EmpWorkDutyMaster'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_emp_work_duty'
          AND `status` = 1 AND `sync_mode` = 'FULL' AND `incr_column` IS NULL) = 1;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_LINE_POST'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'ProdLine,JOBNo'
          AND `source_table_name` COLLATE utf8mb4_bin = 'LineSkillMaster'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_line_post'
          AND `status` = 1 AND `sync_mode` = 'FULL' AND `incr_column` IS NULL) = 1;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_LINE_POST_SKILL'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'ProdLine,JOBNo,SkillNo'
          AND `source_table_name` COLLATE utf8mb4_bin = 'LineSkillDetail'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_line_post'
          AND `status` = 1 AND `sync_mode` = 'FULL' AND `incr_column` IS NULL) = 1;

SELECT (SELECT COUNT(*) FROM `mdp_entity` WHERE `source_table_name` = 'EmpSkills') = 0;