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

-- 1.0.521.verify.sql — 每条 SELECT 都必须返回 1

-- ── ① 两个销售订单实体已声明为 FULL（这是「最新批次 = 源侧现存」成立的前提）──────────
SELECT (SELECT COUNT(*) FROM `mdp_entity`
         WHERE `entity_code` IN ('S1_SEORDER','S1_SEORDER_ENTRY')
           AND `sync_mode` = 'FULL') = 2;

-- 声明为 FULL 后不得再残留增量水位列，否则语义自相矛盾
SELECT (SELECT COUNT(*) FROM `mdp_entity`
         WHERE `entity_code` IN ('S1_SEORDER','S1_SEORDER_ENTRY')
           AND `incr_column` IS NOT NULL) = 0;

-- ── ② 标准层不得再有「不在贴源最新批次里」的订单行（孤儿已清空）────────────────────
-- 与迁移同一判据，且同样带「该租户贴源确实有数据」的闸门。
SELECT (SELECT COUNT(*) FROM `mdp_std_so` s
         WHERE s.`source_table` = 'crm_seorderentry'
           AND EXISTS (SELECT 1 FROM `mdp_stg_so` g
                        WHERE g.`source_table` = 'crm_seorderentry'
                          AND g.`tenant_id` = s.`tenant_id`)
           AND NOT EXISTS (
                SELECT 1 FROM `mdp_stg_so` lb
                 WHERE lb.`source_table` = 'crm_seorderentry'
                   AND lb.`tenant_id` = s.`tenant_id`
                   AND lb.`source_biz_key` = s.`source_biz_key`
                   AND lb.`sync_batch_id` = (SELECT x.`sync_batch_id` FROM `mdp_stg_so` x
                                              WHERE x.`source_table` = 'crm_seorderentry'
                                                AND x.`tenant_id` = s.`tenant_id`
                                              ORDER BY x.`sync_time` DESC, x.`id` DESC
                                              LIMIT 1))) = 0;

-- ── ③ 清理不得误伤：贴源最新批次里的行必须都还在标准层 ──────────────────────────────
-- 反向断言，防止「把孤儿连同存活行一起删干净」也能通过 ② 的情况。
SELECT (SELECT COUNT(*) FROM `mdp_stg_so` lb
         WHERE lb.`source_table` = 'crm_seorderentry'
           AND lb.`sync_batch_id` = (SELECT x.`sync_batch_id` FROM `mdp_stg_so` x
                                      WHERE x.`source_table` = 'crm_seorderentry'
                                        AND x.`tenant_id` = lb.`tenant_id`
                                      ORDER BY x.`sync_time` DESC, x.`id` DESC
                                      LIMIT 1)
           AND IFNULL(JSON_UNQUOTE(JSON_EXTRACT(lb.`raw_data`,'$.bill_no')),'') <> ''
           AND NOT EXISTS (SELECT 1 FROM `mdp_std_so` s
                            WHERE s.`source_table` = 'crm_seorderentry'
                              AND s.`tenant_id` = lb.`tenant_id`
                              AND s.`source_biz_key` = lb.`source_biz_key`)) = 0;

-- ── ④ 标准层的唯一键未被本批动过（隔离列必须仍在）──────────────────────────────────
SELECT IFNULL((SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX)
                 FROM information_schema.STATISTICS
                WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mdp_std_so'
                  AND INDEX_NAME = 'uk_source_key' AND NON_UNIQUE = 0), '')
       = 'tenant_id,source_system,source_table,source_biz_key';