-- WIP-C3C.verify.sql

SELECT (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME IN ('mdp_stg_s0_item_op_condition','bridge_item_op_condition',
                           'mdp_stg_s0_item_op_image','bridge_item_op_image')) = 4;

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

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

-- 父键列名必须逐字 item_code
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME IN ('bridge_item_op_condition','bridge_item_op_image')
        AND COLUMN_NAME = 'item_code') = 2;
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME IN ('bridge_item_op_condition','bridge_item_op_image')
        AND COLUMN_NAME IN ('item_num','dim_item_id')) = 0;

-- is_main 是属性不是键
SELECT (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE()
        AND INDEX_NAME = 'uk_bridge_item_op_condition' AND COLUMN_NAME = 'is_main') = 0;
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'bridge_item_op_condition' AND COLUMN_NAME = 'is_main') = 1;

-- element_code 必须与 item_code 并存且分离（证明未把要素编码当物料码）
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'bridge_item_op_condition'
        AND COLUMN_NAME IN ('item_code','element_code')) = 2;

-- 🔴 image 桥绝不能有 source_updated_at（源零时间列）
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'bridge_item_op_image' AND COLUMN_NAME = 'source_updated_at') = 0;
-- 而 condition 桥有（源 UpdateTime 存在）
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'bridge_item_op_condition' AND COLUMN_NAME = 'source_updated_at') = 1;

-- LEGACY_SCOPE 不进唯一键
SELECT (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE()
        AND INDEX_NAME IN ('uk_bridge_item_op_condition','uk_bridge_item_op_image')
        AND COLUMN_NAME IN ('domain_code','company_id','factory_id')) = 0;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_ITEM_OP_CONDITION'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'ItemNum,CodeType'
          AND `source_table_name` COLLATE utf8mb4_bin = 'ItemOpCondition'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_item_op_condition'
          AND `status` = 1 AND `sync_mode` = 'FULL' AND `incr_column` IS NULL
          AND `batch_size` = 5000) = 1;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_ITEM_OP_IMAGE'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'item_num,op,image_type_id'
          AND `source_table_name` COLLATE utf8mb4_bin = 'QualitySegmentImage'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_item_op_image'
          AND `status` = 1 AND `sync_mode` = 'FULL' AND `incr_column` IS NULL
          AND `batch_size` = 5000) = 1;

-- 源侧前提：三个业务键分量当前无 NULL/空串（W6 风险的当前状态）
SELECT (SELECT SUM(item_num IS NULL OR item_num = '' OR op IS NULL OR op = ''
                   OR image_type_id IS NULL OR image_type_id = '') FROM `QualitySegmentImage`) = 0;
SELECT (SELECT COUNT(*) FROM (SELECT 1 FROM `ItemOpCondition`
        GROUP BY `tenant_id`,`ItemNum`,`CodeType` HAVING COUNT(*) > 1) x) = 0;