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

-- ============================================================================
-- 1.0.555 · R2-A2：mdp_entity 两列补 DEFAULT（短期恢复，不动类型）
--
-- 为什么需要本脚本（1.0.554 的效果被撤销，已实证）：
--   1.0.554 把 inbound_enabled / stale_guard_enabled 收口为 TINYINT + DEFAULT，
--   migration 与 verify 均 Success。但下一次启动时 SqlSugar CodeFirst
--   （EnableInitTable=true）按实体声明 `public int` 把它们改回 INT，
--   其 ALTER 形态为 `alter table t change column c c int NOT NULL`——
--   不带 DEFAULT 子句，于是连带抹掉 DEFAULT。
--   对照组：inbound_max_rows 类型本就等于实体声明的 INT，CodeFirst 无差异
--   可纠正、不生成 ALTER，其 DEFAULT 500 完整穿越了一次 CodeFirst 遍历。
--
--   结论：只要列类型与实体声明一致，CodeFirst 就不会重写该列，DEFAULT 得以存活。
--   故本批策略 = 类型保持 INT（与实体一致），只补 DEFAULT。
--   类型漂移（INT vs 契约 TINYINT）留 R2-B，待 CodeFirst schema authority 定案。
--
-- 1.0.554 保持历史不变：不修改、不改 hash、不改 migration log、不重放。
--
-- 算法：本地 3307 实测 ALTER COLUMN ... SET DEFAULT 接受 INSTANT / INPLACE,LOCK=NONE
--   / COPY 三者。取 INSTANT（纯元数据变更，不重建表，无写阻塞），实测 58ms。
--
-- 范围：只动 mdp_entity 的 inbound_enabled / stale_guard_enabled 两列的 DEFAULT。
--   inbound_max_rows 只做前置断言，绝不 ALTER。
--   不碰 MdpEntity.cs / CodeFirst / EnableInitTable / R2-B / P0 / 1.0.554。
-- ============================================================================

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

-- ── G2 inbound_enabled 无 NULL ─────────────────────────────────────────────
SET @g2 := (SELECT COUNT(*) FROM `mdp_entity` WHERE `inbound_enabled` IS NULL);
SELECT @g2 AS g2_null_rows;
SELECT IF(@g2 <> 0, 'R2A2-G2 FAIL: inbound_enabled 存在 NULL', 'R2A2-G2 ok') AS gate_g2;
SET @sql := IF(@g2 <> 0,
  "SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'R2A2-G2: inbound_enabled 存在 NULL，中止'", 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── G3 stale_guard_enabled 无 NULL ─────────────────────────────────────────
SET @g3 := (SELECT COUNT(*) FROM `mdp_entity` WHERE `stale_guard_enabled` IS NULL);
SELECT @g3 AS g3_null_rows;
SELECT IF(@g3 <> 0, 'R2A2-G3 FAIL: stale_guard_enabled 存在 NULL', 'R2A2-G3 ok') AS gate_g3;
SET @sql := IF(@g3 <> 0,
  "SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'R2A2-G3: stale_guard_enabled 存在 NULL，中止'", 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── G4 行数基线（只记录，不硬编码）────────────────────────────────────────
SET @rows_before := (SELECT COUNT(*) FROM `mdp_entity`);
SELECT @rows_before AS rows_before;

-- ── G5 inbound_max_rows 必须已是 INT NOT NULL DEFAULT 500 ──────────────────
--    它由 1.0.554 修好并已穿越一次 CodeFirst 遍历；若 500 丢失说明前提变了，
--    属 SCOPE CHANGED，本脚本不得顺手补。
SET @g5 := (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');
SELECT @g5 AS g5_inbound_max_rows_ok;
SELECT IF(@g5 <> 1,
  'R2A2-G5 FAIL: inbound_max_rows 不是 INT NOT NULL DEFAULT 500 — SCOPE CHANGED',
  'R2A2-G5 ok') AS gate_g5;
SET @sql := IF(@g5 <> 1,
  "SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'R2A2-G5: inbound_max_rows 不符前提（应为 INT NOT NULL DEFAULT 500），SCOPE CHANGED，中止'",
  'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── G6 类型守卫：两列必须已是 INT NOT NULL；本脚本不改类型 ──────────────────
SET @g6 := (SELECT COUNT(*) FROM information_schema.`COLUMNS`
             WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_entity'
               AND `COLUMN_NAME` IN ('inbound_enabled', 'stale_guard_enabled')
               AND `COLUMN_TYPE` = 'int' AND `IS_NULLABLE` = 'NO');
SELECT @g6 AS g6_int_notnull_cols;
SELECT IF(@g6 <> 2,
  'R2A2-G6 FAIL: 两列类型不是 INT NOT NULL — 本脚本不自动改类型',
  'R2A2-G6 ok') AS gate_g6;
SET @sql := IF(@g6 <> 2,
  "SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'R2A2-G6: inbound_enabled / stale_guard_enabled 类型不是 INT NOT NULL，本脚本不自动改类型，中止'",
  'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── Step 1 · 逐列目标态比对（NULL-safe <=>），仅对仍漂移的列生成子句 ────────
SET @need_ie := (SELECT COUNT(*) FROM information_schema.`COLUMNS`
                  WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_entity'
                    AND `COLUMN_NAME` = 'inbound_enabled'
                    AND NOT (`COLUMN_DEFAULT` <=> '0'));
SET @need_sg := (SELECT COUNT(*) FROM information_schema.`COLUMNS`
                  WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_entity'
                    AND `COLUMN_NAME` = 'stale_guard_enabled'
                    AND NOT (`COLUMN_DEFAULT` <=> '1'));
SELECT @need_ie AS need_inbound_enabled, @need_sg AS need_stale_guard_enabled;

-- CONCAT_WS 会跳过 NULL，于是只有需要改的列才进入 ALTER 子句
SET @c1 := IF(@need_ie > 0, 'ALTER COLUMN `inbound_enabled` SET DEFAULT 0', NULL);
SET @c2 := IF(@need_sg > 0, 'ALTER COLUMN `stale_guard_enabled` SET DEFAULT 1', NULL);
SET @clauses := CONCAT_WS(', ', @c1, @c2);

-- ── Step 2 · 仅在有漂移列时执行；ALGORITHM=INSTANT（纯元数据，无写阻塞）────
SET @sql := IF(@clauses IS NULL OR @clauses = '',
  'DO 0',
  CONCAT('ALTER TABLE `mdp_entity` ', @clauses, ', ALGORITHM=INSTANT'));
SELECT @sql AS planned_statement;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── Step 3 · 事后自断言：两列 DEFAULT 必须到位 ─────────────────────────────
SET @after := (SELECT COUNT(*) FROM information_schema.`COLUMNS`
                WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'mdp_entity'
                  AND ((`COLUMN_NAME` = 'inbound_enabled'     AND `COLUMN_DEFAULT` <=> '0')
                    OR (`COLUMN_NAME` = 'stale_guard_enabled' AND `COLUMN_DEFAULT` <=> '1')));
SELECT @after AS defaults_in_place;
SELECT IF(@after <> 2, 'R2A2-POST1 FAIL: 两列 DEFAULT 未全部到位', 'R2A2-POST1 ok') AS gate_post1;
SET @sql := IF(@after <> 2,
  "SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'R2A2-POST: 两列 DEFAULT 未全部到位，中止'", 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── Step 4 · 行数完整性 ────────────────────────────────────────────────────
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, 'R2A2-POST2 FAIL: 行数变化', 'R2A2-POST2 ok') AS gate_post2;
SET @sql := IF(@rows_after <> @rows_before,
  "SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'R2A2-POST: mdp_entity 行数在 ALTER 前后不一致，中止'", 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;