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

-- WIP-C3A.verify.sql
-- 契约（CURRENT.md > F-11）：禁 SET @var · 每条返回 1/0 · 一条一断言 · 末条为真 SELECT。

SELECT (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME IN ('mdp_stg_s0_item_op_line','bridge_item_op_line',
                           'mdp_stg_s0_item_routing_op','bridge_item_routing_op')) = 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_line'
                 AND INDEX_NAME = 'uk_bridge_item_op_line' AND NON_UNIQUE = 0), '')
       = 'tenant_id,item_code,operation_no,line_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_routing_op'
                 AND INDEX_NAME = 'uk_bridge_item_routing_op' AND NON_UNIQUE = 0), '')
       = 'tenant_id,item_code,operation_code';

-- 父键列名必须逐字是 item_code：OrphanFromWhere 用 p.<col> <=> d.<col> 同名列 join
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME IN ('bridge_item_op_line','bridge_item_routing_op')
        AND COLUMN_NAME = 'item_code') = 2;

SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME IN ('bridge_item_op_line','bridge_item_routing_op')
        AND COLUMN_NAME IN ('item_num','dim_item_id','parent_id')) = 0;

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

-- 影子列绝不能出现在目标表
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'bridge_item_op_line'
        AND COLUMN_NAME IN ('internal_equipment_type_code')) = 0;
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'bridge_item_op_line'
        AND COLUMN_NAME = 'internal_equipment_code') = 1;

-- 不可物化的列绝不能出现在目标表
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'bridge_item_routing_op'
        AND COLUMN_NAME IN ('milestone_operation','milestone_op','is_active','is_enabled',
                            'routing_code','descr','op')) = 0;

-- Dec 精度必须与源逐位一致：op_line 五列 (18,6)
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'bridge_item_op_line'
        AND COLUMN_NAME IN ('rate','setup_time','overlap_time','sequence_no','standard_staff_count')
        AND NUMERIC_PRECISION = 18 AND NUMERIC_SCALE = 6) = 5;

-- routing_op：UDeci* 是 (18,5)，run_time 是 (18,6)，不得统一
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'bridge_item_routing_op'
        AND COLUMN_NAME IN ('udeci1','udeci2','udeci3','udeci4','udeci5')
        AND NUMERIC_PRECISION = 18 AND NUMERIC_SCALE = 5) = 5;
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'bridge_item_routing_op'
        AND COLUMN_NAME = 'run_time' AND NUMERIC_PRECISION = 18 AND NUMERIC_SCALE = 6) = 1;

-- run_crew 在本桥是字符串（源 varchar(8)），不得建成 decimal
SELECT (SELECT DATA_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'bridge_item_op_line' AND COLUMN_NAME = 'run_crew') = 'varchar';

-- domain_code 在 routing_op 必须可空（RecID=106435 那行 Domain 为 NULL）
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'bridge_item_routing_op'
        AND COLUMN_NAME = 'domain_code' AND IS_NULLABLE = 'YES') = 1;

-- mdp_entity 契约：biz_key_expr 须与 SourceBizKeyColumns 逐字一致（AssertEntityContractAsync 硬比对）
SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_ITEM_OP_LINE'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'Part,Op,Line'
          AND `source_table_name` COLLATE utf8mb4_bin = 'ProdLineDetail'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_item_op_line'
          AND `status` = 1 AND `sync_mode` = 'FULL' AND `incr_column` IS NULL
          AND `batch_size` = 20000) = 1;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_ITEM_ROUTING_OP'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'MaterialCode,OperationCode'
          AND `source_table_name` COLLATE utf8mb4_bin = 'RoutingOpDetail'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_item_routing_op'
          AND `status` = 1 AND `sync_mode` = 'FULL' AND `incr_column` IS NULL
          AND `batch_size` = 100000) = 1;

-- batch_size 必须够装下最大租户（AssertNoSourceDuplicateAsync 是 >= 严格判定）
SELECT (SELECT `batch_size` FROM `mdp_entity` WHERE `entity_code` = 'S0_ITEM_OP_LINE')
       > (SELECT MAX(c) FROM (SELECT COUNT(*) c FROM `ProdLineDetail` GROUP BY `tenant_id`) x);
SELECT (SELECT `batch_size` FROM `mdp_entity` WHERE `entity_code` = 'S0_ITEM_ROUTING_OP')
       > (SELECT MAX(c) FROM (SELECT COUNT(*) c FROM `RoutingOpDetail` GROUP BY `tenant_id`) x);

-- 未因本批放大其它 entity 的 batch_size
SELECT (SELECT COUNT(*) FROM `mdp_entity` WHERE `entity_code` LIKE 'S0\_%'
        AND `batch_size` NOT IN (5000, 20000, 50000, 100000)) = 0;