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

-- 1.0.508.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_label_type','mdp_stg_s0_sop_file_type','mdp_stg_s0_quality_base_type','mdp_stg_s0_qms_dict')) = 4;

SELECT (SELECT COUNT(*) FROM information_schema.TABLES
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ('ref_label_type','ref_sop_file_type','ref_quality_base_type','ref_sampling_scheme','ref_inspection_item','ref_inspection_method','ref_inspection_frequency','ref_inspection_instrument')) = 8;

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_label_type'
                 AND INDEX_NAME = 'uk_stg_s0_label_type' 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_sop_file_type'
                 AND INDEX_NAME = 'uk_stg_s0_sop_file_type' 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_quality_base_type'
                 AND INDEX_NAME = 'uk_stg_s0_quality_base_type' 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_qms_dict'
                 AND INDEX_NAME = 'uk_stg_s0_qms_dict' 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 = 'ref_label_type'
                 AND INDEX_NAME = 'uk_ref_label_type' AND NON_UNIQUE = 0), '')
       = 'tenant_id,label_type_code';

SELECT IFNULL((SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
               FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ref_sop_file_type'
                 AND INDEX_NAME = 'uk_ref_sop_file_type' AND NON_UNIQUE = 0), '')
       = 'tenant_id,file_type_code';

SELECT IFNULL((SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
               FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ref_quality_base_type'
                 AND INDEX_NAME = 'uk_ref_quality_base_type' AND NON_UNIQUE = 0), '')
       = 'tenant_id,type_category,type_code';

SELECT IFNULL((SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
               FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ref_sampling_scheme'
                 AND INDEX_NAME = 'uk_ref_sampling_scheme' AND NON_UNIQUE = 0), '')
       = 'tenant_id,scheme_code';

SELECT IFNULL((SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
               FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ref_inspection_item'
                 AND INDEX_NAME = 'uk_ref_inspection_item' AND NON_UNIQUE = 0), '')
       = 'tenant_id,item_code';

SELECT IFNULL((SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
               FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ref_inspection_method'
                 AND INDEX_NAME = 'uk_ref_inspection_method' AND NON_UNIQUE = 0), '')
       = 'tenant_id,method_code';

SELECT IFNULL((SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
               FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ref_inspection_frequency'
                 AND INDEX_NAME = 'uk_ref_inspection_frequency' AND NON_UNIQUE = 0), '')
       = 'tenant_id,frequency_code';

SELECT IFNULL((SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
               FROM information_schema.STATISTICS
               WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ref_inspection_instrument'
                 AND INDEX_NAME = 'uk_ref_inspection_instrument' AND NON_UNIQUE = 0), '')
       = 'tenant_id,instrument_code';

SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ('ref_label_type','ref_sop_file_type','ref_quality_base_type','ref_sampling_scheme','ref_inspection_item','ref_inspection_method','ref_inspection_frequency','ref_inspection_instrument')
          AND COLUMN_NAME = 'tenant_id' AND IS_NULLABLE = 'NO') = 8;

SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ('mdp_stg_s0_label_type','mdp_stg_s0_sop_file_type','mdp_stg_s0_quality_base_type','mdp_stg_s0_qms_dict')
          AND COLUMN_NAME = 'tenant_id' AND IS_NULLABLE = 'NO') = 4;

SELECT (SELECT COUNT(*) FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ref_label_type'
          AND INDEX_NAME = 'uk_ref_label_type' 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 = 'ref_sop_file_type'
          AND INDEX_NAME = 'uk_ref_sop_file_type' 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 = 'ref_quality_base_type'
          AND INDEX_NAME = 'uk_ref_quality_base_type' 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 = 'ref_sampling_scheme'
          AND INDEX_NAME = 'uk_ref_sampling_scheme' 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 = 'ref_inspection_item'
          AND INDEX_NAME = 'uk_ref_inspection_item' 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 = 'ref_inspection_method'
          AND INDEX_NAME = 'uk_ref_inspection_method' 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 = 'ref_inspection_frequency'
          AND INDEX_NAME = 'uk_ref_inspection_frequency' 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 = 'ref_inspection_instrument'
          AND INDEX_NAME = 'uk_ref_inspection_instrument' 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 = 'ref_sop_file_type'
          AND COLUMN_NAME = 'source_updated_at') = 0;

SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME IN ('ref_sampling_scheme','ref_inspection_item','ref_inspection_method',
                             'ref_inspection_frequency','ref_inspection_instrument')
          AND COLUMN_NAME IN ('status_code','enable_code') AND DATA_TYPE = 'varchar') = 10;

SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ref_inspection_frequency'
          AND COLUMN_NAME = 'remark') = 1;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_LABEL_TYPE'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'bar_type'
          AND `source_table_name` COLLATE utf8mb4_bin = 'BarCodeType'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_label_type'
          AND `status` = 1 AND `sync_mode` = 'FULL' AND `incr_column` IS NULL) = 1;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_SOP_FILE_TYPE'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'image_type_id'
          AND `source_table_name` COLLATE utf8mb4_bin = 'ImageType'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_sop_file_type'
          AND `status` = 1 AND `sync_mode` = 'FULL' AND `incr_column` IS NULL) = 1;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_QUALITY_BASE_TYPE'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'type_category,type_code'
          AND `source_table_name` COLLATE utf8mb4_bin = 'qms_quality_base_type'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_quality_base_type'
          AND `status` = 1 AND `sync_mode` = 'FULL' AND `incr_column` IS NULL) = 1;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_SAMPLING_SCHEME'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'FNUMBER'
          AND `source_table_name` COLLATE utf8mb4_bin = 'qms_sampscheme'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_qms_dict'
          AND `status` = 1 AND `sync_mode` = 'FULL' AND `incr_column` IS NULL) = 1;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_INSPECTION_ITEM'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'FNUMBER'
          AND `source_table_name` COLLATE utf8mb4_bin = 'qms_inspectionitems'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_qms_dict'
          AND `status` = 1 AND `sync_mode` = 'FULL' AND `incr_column` IS NULL) = 1;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_INSPECTION_METHOD'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'FNUMBER'
          AND `source_table_name` COLLATE utf8mb4_bin = 'qms_inspection_method'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_qms_dict'
          AND `status` = 1 AND `sync_mode` = 'FULL' AND `incr_column` IS NULL) = 1;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_INSPECTION_FREQUENCY'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'FNUMBER'
          AND `source_table_name` COLLATE utf8mb4_bin = 'qms_inspectionfreq'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_qms_dict'
          AND `status` = 1 AND `sync_mode` = 'FULL' AND `incr_column` IS NULL) = 1;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_INSPECTION_INSTRUMENT'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'FNUMBER'
          AND `source_table_name` COLLATE utf8mb4_bin = 'qms_inspectioninstru'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_qms_dict'
          AND `status` = 1 AND `sync_mode` = 'FULL' AND `incr_column` IS NULL) = 1;

SELECT (SELECT COUNT(*) FROM `mdp_entity` e JOIN `mdp_source` s ON s.`id` = e.`source_id`
        WHERE e.`entity_code` LIKE 'S0\_%' AND s.`source_code` <> 'AIDOPDEV_MYSQL') = 0;