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

-- ============================================================================
-- 1.0.554.verify.sql · R2-A 执行后校验
--   返回单列 ok：1 = 全部断言通过，0 = 任一失败。
--
--   V1  inbound_enabled     = tinyint / NOT NULL / DEFAULT 0
--   V2  inbound_max_rows    = int     / NOT NULL / DEFAULT 500
--   V3  stale_guard_enabled = tinyint / NOT NULL / DEFAULT 1
--   V4  三列无 NULL（NOT NULL 语义未被削弱）
--   V5  值域合法：inbound_enabled / stale_guard_enabled ∈ {0,1}
--   V6  drift 计数为 0（与 migration 同口径，NULL-safe <=>）
--   V7  uk_inbound_grant 仍存在且列序正确（1.0.553 产物，本批只读回归）
--   V8  R2-B 对象未被本批触碰（列数守恒）
--   V9  P0 对象未被触碰（UK 列序 / CHECK / po_no·po_line NOT NULL）
--   V10 mdp_entity 仍有行（ALTER 未清空表）
--
--   注：不比对 COLUMN_COMMENT —— 注释不属于功能契约，且会与 CodeFirst 的
--       注释同步互踩，纳入比对会造成假失败。
-- ============================================================================
SELECT CASE WHEN

-- V1 · inbound_enabled
    (SELECT COUNT(*) FROM information_schema.`COLUMNS`
      WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_entity'
        AND `COLUMN_NAME` = 'inbound_enabled'
        AND `COLUMN_TYPE` = 'tinyint' AND `IS_NULLABLE` = 'NO'
        AND `COLUMN_DEFAULT` <=> '0') = 1

-- V2 · inbound_max_rows（类型本就是 int，本批只补 DEFAULT）
AND (SELECT COUNT(*) FROM information_schema.`COLUMNS`
      WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_entity'
        AND `COLUMN_NAME` = 'inbound_max_rows'
        AND `COLUMN_TYPE` = 'int' AND `IS_NULLABLE` = 'NO'
        AND `COLUMN_DEFAULT` <=> '500') = 1

-- V3 · stale_guard_enabled
AND (SELECT COUNT(*) FROM information_schema.`COLUMNS`
      WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_entity'
        AND `COLUMN_NAME` = 'stale_guard_enabled'
        AND `COLUMN_TYPE` = 'tinyint' AND `IS_NULLABLE` = 'NO'
        AND `COLUMN_DEFAULT` <=> '1') = 1

-- V4 · 三列无 NULL
AND (SELECT COUNT(*) FROM `mdp_entity`
      WHERE `inbound_enabled` IS NULL
         OR `inbound_max_rows` IS NULL
         OR `stale_guard_enabled` IS NULL) = 0

-- V5 · 值域合法
AND (SELECT COUNT(*) FROM `mdp_entity` WHERE `inbound_enabled`     NOT IN (0, 1)) = 0
AND (SELECT COUNT(*) FROM `mdp_entity` WHERE `stale_guard_enabled` NOT IN (0, 1)) = 0

-- V6 · drift 归零（与 migration Step 1/3 同口径）
AND (SELECT COUNT(*) FROM information_schema.`COLUMNS`
      WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_entity' AND (
         (`COLUMN_NAME` = 'inbound_enabled'
            AND NOT (`COLUMN_TYPE` = 'tinyint' AND `IS_NULLABLE` = 'NO' AND `COLUMN_DEFAULT` <=> '0'))
      OR (`COLUMN_NAME` = 'inbound_max_rows'
            AND NOT (`COLUMN_TYPE` = 'int'     AND `IS_NULLABLE` = 'NO' AND `COLUMN_DEFAULT` <=> '500'))
      OR (`COLUMN_NAME` = 'stale_guard_enabled'
            AND NOT (`COLUMN_TYPE` = 'tinyint' AND `IS_NULLABLE` = 'NO' AND `COLUMN_DEFAULT` <=> '1'))
      )) = 0

-- V7 · uk_inbound_grant 回归（1.0.553 产物；NON_UNIQUE=0 数的是索引列数=2）
AND (SELECT COUNT(*) FROM information_schema.`STATISTICS`
      WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_inbound_grant'
        AND `INDEX_NAME` = 'uk_inbound_grant' AND `NON_UNIQUE` = 0) = 2
AND (SELECT GROUP_CONCAT(`COLUMN_NAME` ORDER BY `SEQ_IN_INDEX` SEPARATOR ',')
       FROM information_schema.`STATISTICS`
      WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_inbound_grant'
        AND `INDEX_NAME` = 'uk_inbound_grant') = 'access_key,entity_code'

-- V8 · R2-B 对象未被触碰（列数守恒，取自审计基线）
AND (SELECT COUNT(*) FROM information_schema.`COLUMNS`
      WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_inbound_grant')   = 13
AND (SELECT COUNT(*) FROM information_schema.`COLUMNS`
      WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_inbound_request')  = 21
AND (SELECT COUNT(*) FROM information_schema.`COLUMNS`
      WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_inbound_envelope') = 10
AND (SELECT COUNT(*) FROM information_schema.`COLUMNS`
      WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_field_map')        = 10

-- V9 · P0 对象未被触碰
AND (SELECT GROUP_CONCAT(`COLUMN_NAME` ORDER BY `SEQ_IN_INDEX` SEPARATOR ',')
       FROM information_schema.`STATISTICS`
      WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'dwd_process_outsource_delivery'
        AND `INDEX_NAME` = 'uk_po_line_stat') = 'tenant_id,factory_id,stat_date,po_no,po_line'
AND (SELECT COUNT(*) FROM information_schema.`TABLE_CONSTRAINTS`
      WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'dwd_process_outsource_delivery'
        AND `CONSTRAINT_TYPE` = 'CHECK'
        AND `CONSTRAINT_NAME` = 'ck_dwd_process_outsource_valid_tenant') = 1
AND (SELECT COUNT(*) FROM information_schema.`COLUMNS`
      WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'dwd_process_outsource_delivery'
        AND `COLUMN_NAME` IN ('po_no', 'po_line') AND `IS_NULLABLE` = 'YES') = 0

-- V10 · mdp_entity 仍有行（ALTER 未清空表）
AND (SELECT COUNT(*) FROM `mdp_entity`) > 0

THEN 1 ELSE 0 END AS ok;