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

-- ============================================================================
-- 1.0.554 · R2-A：mdp_entity 三列 schema drift 收口
--
-- 背景（已取证）：
--   S1_MDP_SYNC_TRANSFORM / S2_MDP_SYNC_TRANSFORM 在服务启动期用裸 SQL
--   INSERT INTO mdp_entity (...) ... ON DUPLICATE KEY UPDATE 注册实体，
--   其显式列清单不含 inbound_enabled / inbound_max_rows / stale_guard_enabled。
--   生产这三列为 NOT NULL 且缺 DEFAULT，sql_mode 含 STRICT_TRANS_TABLES，
--   于是 MySQL 报 Error 1364: Field 'xxx' doesn't have a default value。
--   MySQL 每次只报第一个缺失列，故三列必须同批收口，否则要三次部署才收敛。
--
-- 漂移成因（已取证，非 migration 写错）：
--   1.0.536 的 SQL 文本对三列都写了正确的 TYPE + DEFAULT（见其 :118/:124/:130），
--   但 mdp_inbound_* 六表的 create_time 实测为 2026-09-15 23:03:31~33，
--   由 SqlSugar CodeFirst 抢先创建；1.0.536 正式执行是 2026-09-16 20:36:37，
--   晚 21 小时，其 IF(@exist=0, ADD COLUMN ...) 守卫整段 no-op。
--   CodeFirst 按实体生成 int / 无 DEFAULT，与 contract 不符。
--
-- 设计原则：TARGET-STATE RECONCILIATION，不是 OBJECT-EXISTENCE MIGRATION。
--   判定「定义是否等于目标」，而非「列是否存在」——后者正是 1.0.536 的失败模式。
--   COLUMN_DEFAULT 比较必须用 NULL-safe 的 <=>：COLUMN_DEFAULT 为 NULL 时
--   `COLUMN_DEFAULT = '500'` 求值为 NULL（非 FALSE），WHERE 按假丢弃 → 漏检。
--   实证：仅 inbound_max_rows 缺 DEFAULT 的环境下，`=` 谓词 drift=0（静默跳过），
--   `<=>` 谓词 drift=1（正确检出），而该环境实际 INSERT 确实报 1364。
--
-- 注释处理：drift 判定只比对 type / nullable / default，不比对 COMMENT，
--   避免与 CodeFirst 的注释同步反复互踩。inbound_enabled 保留生产现有注释
--   （信息量更大），另两列原为空，补回 1.0.536 的契约注释。
--
-- 算法：本地 3307 同构演练实测 —— INSTANT 被拒（Need to rebuild the table）、
--   INPLACE 被拒（Cannot change column type INPLACE）、LOCK=NONE 被拒
--   （COPY algorithm requires a lock）。故显式声明 ALGORITHM=COPY, LOCK=SHARED，
--   让锁行为在脚本内可见可审计，且未来 MySQL 行为变化会显式报错而非静默改变。
--   202 行实测耗时 72ms，写阻塞约 72ms，读不受影响。
--
-- 范围边界：只动 mdp_entity 三列。
--   不碰 uk_inbound_grant（1.0.553 已建，仅回归断言）。
--   不碰 R2-B 的 7 项 dormant drift。不碰任何 P0 对象。
-- ============================================================================

-- ── Step 0 · G1 表存在 ──────────────────────────────────────────────────────
SET @t := (SELECT COUNT(*) FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'mdp_entity');
SELECT IF(@t <> 1, 'R2A-G1 FAIL: mdp_entity 不存在', 'R2A-G1 ok') AS gate_g1;
SET @sql := IF(@t <> 1,
  "SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'R2A-G1: mdp_entity 不存在，中止'",
  'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── Step 0 · G2 inbound_enabled data-fit（TINYINT 值域 + 无 NULL）───────────
SET @g2_null := (SELECT COUNT(*) FROM `mdp_entity` WHERE `inbound_enabled` IS NULL);
SET @g2_oor  := (SELECT COUNT(*) FROM `mdp_entity` WHERE `inbound_enabled` NOT BETWEEN -128 AND 127);
SELECT @g2_null AS g2_null_rows, @g2_oor AS g2_out_of_range;
SELECT IF(@g2_null <> 0 OR @g2_oor <> 0, 'R2A-G2 FAIL: inbound_enabled 存在 NULL 或超出 TINYINT 值域', 'R2A-G2 ok') AS gate_g2;
SET @sql := IF(@g2_null <> 0 OR @g2_oor <> 0,
  "SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'R2A-G2: inbound_enabled 存在 NULL 或超出 TINYINT 值域，中止'",
  'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── Step 0 · G3 stale_guard_enabled data-fit ───────────────────────────────
SET @g3_null := (SELECT COUNT(*) FROM `mdp_entity` WHERE `stale_guard_enabled` IS NULL);
SET @g3_oor  := (SELECT COUNT(*) FROM `mdp_entity` WHERE `stale_guard_enabled` NOT BETWEEN -128 AND 127);
SELECT @g3_null AS g3_null_rows, @g3_oor AS g3_out_of_range;
SELECT IF(@g3_null <> 0 OR @g3_oor <> 0, 'R2A-G3 FAIL: stale_guard_enabled 存在 NULL 或超出 TINYINT 值域', 'R2A-G3 ok') AS gate_g3;
SET @sql := IF(@g3_null <> 0 OR @g3_oor <> 0,
  "SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'R2A-G3: stale_guard_enabled 存在 NULL 或超出 TINYINT 值域，中止'",
  'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── Step 0 · G4 inbound_max_rows data-fit（类型不变，只查 NULL；不新增业务范围）──
SET @g4_null := (SELECT COUNT(*) FROM `mdp_entity` WHERE `inbound_max_rows` IS NULL);
SELECT @g4_null AS g4_null_rows;
SELECT IF(@g4_null <> 0, 'R2A-G4 FAIL: inbound_max_rows 存在 NULL', 'R2A-G4 ok') AS gate_g4;
SET @sql := IF(@g4_null <> 0,
  "SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'R2A-G4: inbound_max_rows 存在 NULL，中止'",
  'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── Step 0 · G5 行数基线（只记录，不作硬编码门禁）──────────────────────────
SET @rows_before := (SELECT COUNT(*) FROM `mdp_entity`);
SELECT @rows_before AS rows_before;

-- ── Step 0 · G6 回归断言：uk_inbound_grant 必须仍存在且定义正确 ─────────────
--    该索引由 1.0.553 创建，本脚本绝不修改它，只确认未被破坏。
SET @uk_cols := (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');
SET @uk_unique := (SELECT MIN(`NON_UNIQUE`) FROM information_schema.`STATISTICS`
                    WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_inbound_grant'
                      AND `INDEX_NAME` = 'uk_inbound_grant');
SELECT @uk_cols AS uk_inbound_grant_cols, @uk_unique AS uk_non_unique;
SELECT IF(NOT (@uk_cols <=> 'access_key,entity_code') OR NOT (@uk_unique <=> 0), 'R2A-G6 FAIL: uk_inbound_grant 缺失或定义不符（应由 1.0.553 保证）', 'R2A-G6 ok') AS gate_g6;
SET @sql := IF(NOT (@uk_cols <=> 'access_key,entity_code') OR NOT (@uk_unique <=> 0),
  "SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'R2A-G6: uk_inbound_grant 缺失或定义不符（应由 1.0.553 保证），中止'",
  'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── Step 1 · 目标态比对（NULL-safe <=>；只比 type / nullable / default）─────
SET @drift := (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'))
  ));
SELECT @drift AS drift_before;

-- ── Step 2 · 仅 drift > 0 时执行；三列同一条 ALTER；显式算法与锁 ────────────
SET @sql := IF(@drift > 0,
  'ALTER TABLE `mdp_entity`
     MODIFY COLUMN `inbound_enabled`     TINYINT NOT NULL DEFAULT 0
            COMMENT ''入站开通开关。不走 CodeFirst 自动加列，以 UpdateScripts 为准。'',
     MODIFY COLUMN `inbound_max_rows`    INT     NOT NULL DEFAULT 500
            COMMENT ''单请求行数上限（快照类 2000）'',
     MODIFY COLUMN `stale_guard_enabled` TINYINT NOT NULL DEFAULT 1
            COMMENT ''乱序 stale 守卫'',
     ALGORITHM=COPY, LOCK=SHARED',
  'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── Step 3 · 事后自断言：drift 必须归零 ────────────────────────────────────
SET @drift_after := (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'))
  ));
SELECT @drift_after AS drift_after;
SELECT IF(@drift_after <> 0, 'R2A-POST1 FAIL: 三列未全部收口到目标态', 'R2A-POST1 ok') AS gate_post1;
SET @sql := IF(@drift_after <> 0,
  "SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'R2A-POST: 三列未全部收口到目标态，中止'",
  'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── Step 4 · 行数完整性：ALTER 不得增减行 ──────────────────────────────────
SET @rows_after := (SELECT COUNT(*) FROM `mdp_entity`);
SELECT @rows_before AS rows_before, @rows_after AS rows_after;
SELECT IF(@rows_after <> @rows_before, 'R2A-POST2 FAIL: mdp_entity 行数在 ALTER 前后不一致', 'R2A-POST2 ok') AS gate_post2;
SET @sql := IF(@rows_after <> @rows_before,
  "SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'R2A-POST: mdp_entity 行数在 ALTER 前后不一致，中止'",
  'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;