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

-- WIP-SUPPLIER.verify.sql

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

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

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

-- 🔴 supplier_name 必须可空（SortName 有 21 行 NULL；设 NOT NULL 会让 21 行被滤掉导致对账 FAIL）
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'dim_supplier' AND COLUMN_NAME = 'supplier_name' AND IS_NULLABLE = 'YES') = 1;
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'dim_supplier' AND COLUMN_NAME = 'source_updated_at' AND IS_NULLABLE = 'YES') = 1;

-- RecID 无消费方（srm_purchase.supplier_id 命中 0/4449），故不得物化 legacy_source_id
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'dim_supplier'
        AND COLUMN_NAME IN ('legacy_source_id','rec_id','supplier_id')) = 0;

-- 全空的 PII 与近空列不得被物化
SELECT (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'dim_supplier'
        AND COLUMN_NAME IN ('address','contact','position','contact_info','certification_level',
                            'pur_contact','remark','cr_terms','curr','tax_class',
                            'ap_start_day1','ap_end_day1','ap_start_day2','ap_end_day2')) = 0;

SELECT (SELECT COUNT(*) FROM `mdp_entity`
        WHERE `entity_code` = 'S0_SUPPLIER'
          AND `biz_key_expr` COLLATE utf8mb4_bin = 'Supp'
          AND `source_table_name` COLLATE utf8mb4_bin = 'SuppMaster'
          AND `target_table_name` COLLATE utf8mb4_bin = 'mdp_stg_s0_supplier'
          AND `status` = 1 AND `sync_mode` = 'FULL' AND `incr_column` IS NULL
          AND `batch_size` = 20000) = 1;

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

-- 源侧身份前提：tenant + Supp 无重复、无空白、无非法租户
SELECT (SELECT COUNT(*) FROM (SELECT 1 FROM `SuppMaster` GROUP BY `tenant_id`,`Supp` HAVING COUNT(*) > 1) x) = 0;
SELECT (SELECT COUNT(*) FROM `SuppMaster` WHERE `Supp` IS NULL OR TRIM(`Supp`) = '') = 0;
SELECT (SELECT COUNT(*) FROM `SuppMaster` WHERE `tenant_id` IS NULL OR `tenant_id` <= 0) = 0;

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