-- WIP-C3B.verify.sql

SELECT (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME IN ('mdp_stg_s0_item_bom','bridge_item_bom',
                           'mdp_stg_s0_item_substitute','bridge_item_substitute')) = 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_bom'
                 AND INDEX_NAME = 'uk_bridge_item_bom' AND NON_UNIQUE = 0), '')
       = 'tenant_id,item_code,component_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 = 'bridge_item_substitute'
                 AND INDEX_NAME = 'uk_bridge_item_substitute' AND NON_UNIQUE = 0), '')
       = 'tenant_id,parent_item_code,item_code,substitute_item_code';

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

-- 🔴 legacy 代理键绝不能进唯一键（跨租户泄漏向量）
SELECT (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE()
        AND INDEX_NAME = 'uk_bridge_item_bom'
        AND COLUMN_NAME IN ('legacy_parent_item_id','legacy_component_item_id',
                            'company_id','factory_id')) = 0;

-- 但它们必须作为技术属性存在（供 legacy 对账），且带租户前缀的索引才可安全反查
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'bridge_item_bom'
        AND COLUMN_NAME IN ('legacy_parent_item_id','legacy_component_item_id')) = 2;

-- 🔴 bit(1) 且含 NULL 的 IsActive 绝不能被物化；其余零信息列同理
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'bridge_item_bom'
        AND COLUMN_NAME IN ('is_active','is_enabled','sequence_num','structure_type',
                            'line_op','remark','start_eff','end_eff')) = 0;

-- Dec 精度必须与源逐位一致
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'bridge_item_bom'
        AND COLUMN_NAME IN ('qty','scrap','qty_consumed','qty_exchanged')
        AND NUMERIC_PRECISION = 18 AND NUMERIC_SCALE = 5) = 4;
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'bridge_item_substitute'
        AND COLUMN_NAME = 'substitute_qty' AND NUMERIC_PRECISION = 18 AND NUMERIC_SCALE = 6) = 1;

-- 三个 item 引用必须都在替代料桥里
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'bridge_item_substitute'
        AND COLUMN_NAME IN ('parent_item_code','item_code','substitute_item_code')) = 3;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_ITEM_BOM'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'ParentItem,ComponentItem'
          AND `source_table_name` COLLATE utf8mb4_bin = 'ProductStructureMaster'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_item_bom'
          AND `status` = 1 AND `sync_mode` = 'FULL' AND `incr_column` IS NULL
          AND `batch_size` = 200000) = 1;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_ITEM_SUBSTITUTE'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'ParentItem,ItemNum,SubstituteItem'
          AND `source_table_name` COLLATE utf8mb4_bin = 'ItemSubstituteDetail'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_item_substitute'
          AND `status` = 1 AND `sync_mode` = 'FULL' AND `incr_column` IS NULL
          AND `batch_size` = 5000) = 1;

-- batch_size 必须够装下最大租户
SELECT (SELECT `batch_size` FROM `mdp_entity` WHERE `entity_code` = 'S0_ITEM_BOM')
       > (SELECT MAX(c) FROM (SELECT COUNT(*) c FROM `ProductStructureMaster` GROUP BY `tenant_id`) x);

-- 源侧身份前提：tenant+ParentItem+ComponentItem 无重复
SELECT (SELECT COUNT(*) FROM (SELECT 1 FROM `ProductStructureMaster`
        GROUP BY `tenant_id`,`ParentItem`,`ComponentItem` HAVING COUNT(*) > 1) x) = 0;