-- 1.0.507.verify.sql
-- 契约见 CURRENT.md > F-11：禁 SET @var · 每条返回 1/0 · 一条一断言 · 末条为真 SELECT。
-- 断言强度沿用 Phase 1 Final Review 结论：UK 断言精确列序；字符串等值加 COLLATE utf8mb4_bin。

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

SELECT (SELECT COUNT(*) FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME IN ('dim_customer','dim_person_skill')) = 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_customer'
                 AND INDEX_NAME = 'uk_stg_s0_customer' 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_person_skill'
                 AND INDEX_NAME = 'uk_stg_s0_person_skill' 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_customer'
                 AND INDEX_NAME = 'uk_dim_customer' AND NON_UNIQUE = 0), '')
       = 'tenant_id,customer_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_person_skill'
                 AND INDEX_NAME = 'uk_dim_person_skill' AND NON_UNIQUE = 0), '')
       = 'tenant_id,skill_code';

SELECT (SELECT COUNT(*) FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_customer'
          AND INDEX_NAME = 'uk_dim_customer'
          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 = 'dim_person_skill'
          AND INDEX_NAME = 'uk_dim_person_skill'
          AND COLUMN_NAME IN ('company_id','factory_id')) = 0;

SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME IN ('dim_customer','dim_person_skill')
          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_customer','mdp_stg_s0_person_skill')
          AND COLUMN_NAME = 'tenant_id' AND IS_NULLABLE = 'NO') = 2;

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

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` IN ('S0_CUSTOMER','S0_PERSON_SKILL')
          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_CUSTOMER','S0_PERSON_SKILL')
          AND s.`source_code` = 'AIDOPDEV_MYSQL') = 2;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_CUSTOMER'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'Cust'
          AND `source_table_name` COLLATE utf8mb4_bin = 'CustMaster'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_customer'
          AND `batch_size` > 1267) = 1;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_PERSON_SKILL'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'Code'
          AND `source_table_name` COLLATE utf8mb4_bin = 'PersonSkill'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_person_skill'
          AND `batch_size` > 36) = 1;

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