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

-- =====================================================================================
-- 1.0.496  S8-SYSUSER-ONLY-1 · 人员身份统一为系统账号 + 旧运行数据 CLEAN RESET
--
-- 背景（真库取证，只读）：
--   · ado_s8_exception.assignee_id 34 个非空值里只有 20 个能解析到 EmployeeMaster.RecID，
--     0 个能解析到 SysUser.Id，另有 14 行是 assignee_id = 1（两边都不存在的脏值）。
--   · ado_s8_exception_timeline.operator_id 同一列混装两套 ID 空间：
--     CLAIM 写 EmployeeMaster.RecID（19 行），START_PROGRESS / VERIFY_* / CREATE 写 SysUser.Id。
--   · EmployeeMaster 1105 行只有 8 行绑定了 sys_user_id（0.7%），
--     所有「经 EmployeeMaster.SysUserId 换算」的通知与审批流转办路径实际上长期静默失败。
--
-- 本次动作：物理列改名为显式 *_user_id，并清空旧运行数据（不做逐条身份迁移 —— 业务已确认
-- 旧 S8 历史数据无保留要求；混装两套 ID 空间的历史值也没有可靠的迁移依据）。
--
-- 幂等：六个阶段全部可重复执行。第二次运行时 PHASE 3 清 0 行、PHASE 4 全部跳过。
--
-- 安全边界：
--   · RESET 表为**硬编码白名单**，绝不使用 LIKE 'ado_s8_%'（否则将来新增的表会被无声删掉）。
--   · 只清 S8 自己的运行数据。SysUser / SysRole / SysUserRole / SysOrg /
--     DepartmentMaster / EmployeeMaster 一行不动。
--   · 配置态表（exception_type / scene_config / notification_layer / role_permission_config /
--     dashboard_cell_config / watch_rule 的参数列）一行不动。
--   · 审批流表按 **BizType 白名单**定点删除 S8 自己发起的实例与任务，
--     绝不整表清空（ApprovalFlowInstance / Task 由 S5/S6/S7 共用）。
-- =====================================================================================

-- ─────────────────────────────────────────────────────────────────────────────────────
-- PHASE 1 · PREFLIGHT
--   目的：把「执行前长什么样」原样记下来。出问题时这张表是唯一的现场。
-- ─────────────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `ado_s8_sysuser_only_reset_1_0_496` (
  `id`          BIGINT       NOT NULL AUTO_INCREMENT,
  `phase`       VARCHAR(16)  NOT NULL COMMENT 'BEFORE / AFTER',
  `table_name`  VARCHAR(96)  NOT NULL,
  `table_class` VARCHAR(32)  NOT NULL COMMENT 'RUNTIME / FLOW_RUNTIME / POLICY_RUNTIME_STATE',
  `row_count`   BIGINT       NOT NULL,
  `noted_at`    DATETIME     NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_reset_1_0_496_phase` (`phase`, `table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='1.0.496 CLEAN RESET 前后行数留证';

-- 重复执行时先清掉上一轮的留证，避免同一 phase 出现多份互相矛盾的计数。
DELETE FROM `ado_s8_sysuser_only_reset_1_0_496`;

-- ── RESET 白名单（就是下面这 7 张，没有第 8 张）──
INSERT INTO `ado_s8_sysuser_only_reset_1_0_496` (`phase`,`table_name`,`table_class`,`row_count`,`noted_at`)
SELECT 'BEFORE','ado_s8_exception','RUNTIME',COUNT(*),NOW() FROM `ado_s8_exception`
UNION ALL SELECT 'BEFORE','ado_s8_exception_timeline','RUNTIME',COUNT(*),NOW() FROM `ado_s8_exception_timeline`
UNION ALL SELECT 'BEFORE','ado_s8_detection_log','RUNTIME',COUNT(*),NOW() FROM `ado_s8_detection_log`
UNION ALL SELECT 'BEFORE','ado_s8_rule_detection_state','RUNTIME',COUNT(*),NOW() FROM `ado_s8_rule_detection_state`
UNION ALL SELECT 'BEFORE','ado_s8_notification_log','RUNTIME',COUNT(*),NOW() FROM `ado_s8_notification_log`
UNION ALL SELECT 'BEFORE','ado_s8_evidence','RUNTIME',COUNT(*),NOW() FROM `ado_s8_evidence`
UNION ALL SELECT 'BEFORE','ado_s8_decision_record','RUNTIME',COUNT(*),NOW() FROM `ado_s8_decision_record`;

-- 审批流：只统计 S8 自己的三种 BizType，别的模块的行不在本次视野里。
INSERT INTO `ado_s8_sysuser_only_reset_1_0_496` (`phase`,`table_name`,`table_class`,`row_count`,`noted_at`)
SELECT 'BEFORE','ApprovalFlowInstance(S8 BizType)','FLOW_RUNTIME',COUNT(*),NOW()
  FROM `ApprovalFlowInstance`
 WHERE `BizType` IN ('EXCEPTION_REPORT','EXCEPTION_ESCALATION','EXCEPTION_CLOSURE')
UNION ALL
SELECT 'BEFORE','ApprovalFlowTask(S8 BizType)','FLOW_RUNTIME',COUNT(*),NOW()
  FROM `ApprovalFlowTask` t
  JOIN `ApprovalFlowInstance` i ON i.`Id` = t.`InstanceId`
 WHERE i.`BizType` IN ('EXCEPTION_REPORT','EXCEPTION_ESCALATION','EXCEPTION_CLOSURE');

-- 规则：只清运行态列，参数列（含租户已配的默认部门）保持不动，见 PHASE 3 说明。
INSERT INTO `ado_s8_sysuser_only_reset_1_0_496` (`phase`,`table_name`,`table_class`,`row_count`,`noted_at`)
SELECT 'BEFORE','ado_s8_watch_rule(last_run_at NOT NULL)','POLICY_RUNTIME_STATE',COUNT(*),NOW()
  FROM `ado_s8_watch_rule` WHERE `last_run_at` IS NOT NULL;

-- ─────────────────────────────────────────────────────────────────────────────────────
-- PHASE 2 · ARCHIVE / DIAGNOSTICS
--   不做整表备份（业务已确认旧数据无保留要求，全量备份只会把混装 ID 的脏数据换个地方留着）。
--   只留一份**证据摘要**：这次到底删掉了什么形状的身份数据。
-- ─────────────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `ado_s8_idspace_evidence_1_0_496` (
  `id`            BIGINT      NOT NULL AUTO_INCREMENT,
  `metric`        VARCHAR(96) NOT NULL,
  `metric_value`  BIGINT      NOT NULL,
  `noted_at`      DATETIME    NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='1.0.496 清理前的 ID 空间混用取证';

DELETE FROM `ado_s8_idspace_evidence_1_0_496`;

-- 只在旧列还在时取证；改过名之后这些指标已无处可取，跳过即可（幂等）。
SET @has_old_assignee := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ado_s8_exception' AND COLUMN_NAME = 'assignee_id');

SET @sql := IF(@has_old_assignee = 1, '
INSERT INTO `ado_s8_idspace_evidence_1_0_496` (`metric`,`metric_value`,`noted_at`)
SELECT ''assignee_notnull'', COUNT(*), NOW() FROM `ado_s8_exception` WHERE `assignee_id` IS NOT NULL
UNION ALL SELECT ''assignee_resolves_employee'', COUNT(*), NOW()
  FROM `ado_s8_exception` e JOIN `EmployeeMaster` m ON m.`RecID` = e.`assignee_id`
UNION ALL SELECT ''assignee_resolves_sysuser'', COUNT(*), NOW()
  FROM `ado_s8_exception` e JOIN `SysUser` u ON u.`Id` = e.`assignee_id`
UNION ALL SELECT ''assignee_dangling'', COUNT(*), NOW()
  FROM `ado_s8_exception` e
 WHERE e.`assignee_id` IS NOT NULL
   AND NOT EXISTS (SELECT 1 FROM `EmployeeMaster` m WHERE m.`RecID` = e.`assignee_id`)
   AND NOT EXISTS (SELECT 1 FROM `SysUser` u WHERE u.`Id` = e.`assignee_id`)
', 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ─────────────────────────────────────────────────────────────────────────────────────
-- PHASE 3 · RUNTIME DATA RESET
--
--   删除顺序：明细 → 主表 → 审批流。全部按白名单点名，没有一条通配。
-- ─────────────────────────────────────────────────────────────────────────────────────

-- ① S8 异常运行数据
DELETE FROM `ado_s8_exception_timeline`;
DELETE FROM `ado_s8_evidence`;
DELETE FROM `ado_s8_decision_record`;
DELETE FROM `ado_s8_exception`;

-- ② 规则检测运行数据（不清会让规则以为"这些对象我已经报过了"，重启后一条都建不出来）
DELETE FROM `ado_s8_detection_log`;
DELETE FROM `ado_s8_rule_detection_state`;

-- ③ 通知投递日志（全部指向已删除的异常）
DELETE FROM `ado_s8_notification_log`;

-- ④ 审批流：**只删 S8 自己的三种 BizType**。
--    ApprovalFlowInstance / ApprovalFlowTask 由 S5/S6/S7 共用，整表清空会毁掉别的模块。
--    先删子表（Task）再删父表（Instance），否则 Task 会成为查不到来源的孤儿。
DELETE t FROM `ApprovalFlowTask` t
  JOIN `ApprovalFlowInstance` i ON i.`Id` = t.`InstanceId`
 WHERE i.`BizType` IN ('EXCEPTION_REPORT','EXCEPTION_ESCALATION','EXCEPTION_CLOSURE');

DELETE FROM `ApprovalFlowInstance`
 WHERE `BizType` IN ('EXCEPTION_REPORT','EXCEPTION_ESCALATION','EXCEPTION_CLOSURE');

-- ⑤ 规则的**运行态列**清零。
--    刻意只清运行态、不动参数列，也不删行：
--      · watch_rule 上没有任何人员身份列，本次改造不要求重建它；
--      · 删行会连带删掉租户已配的默认部门（实测 2 个租户已配 245 / 253），
--        而 Provisioning 只能把它们恢复成 NULL —— 那是纯粹的损失，换不来任何东西；
--      · 也会删掉无代码定义的孤儿行，与 S8RuleProvisioningService「孤儿不删、不改、不启用」相矛盾。
--    enabled 一列同样不碰：真库 11 行当前全部为 0，Provisioning 新建行也恒为 false，
--    因此 reset 之后 Rule 01 仍然是停用状态 —— §十八 要的就是这个结果。
UPDATE `ado_s8_watch_rule`
   SET `last_run_at` = NULL,
       `last_status` = NULL,
       `last_error` = NULL,
       `last_duration_ms` = NULL,
       `last_run_id` = NULL,
       `next_run_at` = NULL,
       `lock_token` = NULL,
       `locked_by` = NULL,
       `lock_until` = NULL,
       `running_started_at` = NULL,
       `consecutive_failure_count` = 0
 WHERE `last_run_at` IS NOT NULL
    OR `last_status` IS NOT NULL
    OR `last_run_id` IS NOT NULL
    OR `lock_token` IS NOT NULL
    OR `running_started_at` IS NOT NULL
    OR `consecutive_failure_count` <> 0;

-- ─────────────────────────────────────────────────────────────────────────────────────
-- PHASE 4 · SCHEMA IDENTITY CHANGE
--
--   三种到场状态都要能收敛（幂等）：
--     (a) 只有旧列        → CHANGE COLUMN 改名
--     (b) 新旧列同时存在  → CodeFirst 已经把新列加进来了；此时数据已在 PHASE 3 清空，
--                          直接丢掉旧列即可，不需要也不应该做数据搬运
--     (c) 只有新列        → 已经改过，跳过
-- ─────────────────────────────────────────────────────────────────────────────────────

-- ── ado_s8_exception.assignee_id → assignee_user_id ──
SET @old := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='ado_s8_exception' AND COLUMN_NAME='assignee_id');
SET @new := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='ado_s8_exception' AND COLUMN_NAME='assignee_user_id');
SET @sql := CASE
  WHEN @old = 1 AND @new = 0 THEN 'ALTER TABLE `ado_s8_exception` CHANGE COLUMN `assignee_id` `assignee_user_id` BIGINT NULL COMMENT ''处理人账号Id（SysUser.Id）'''
  WHEN @old = 1 AND @new = 1 THEN 'ALTER TABLE `ado_s8_exception` DROP COLUMN `assignee_id`'
  ELSE 'DO 0' END;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── ado_s8_exception.verifier_id → verifier_user_id ──
SET @old := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='ado_s8_exception' AND COLUMN_NAME='verifier_id');
SET @new := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='ado_s8_exception' AND COLUMN_NAME='verifier_user_id');
SET @sql := CASE
  WHEN @old = 1 AND @new = 0 THEN 'ALTER TABLE `ado_s8_exception` CHANGE COLUMN `verifier_id` `verifier_user_id` BIGINT NULL COMMENT ''检验人账号Id（SysUser.Id）'''
  WHEN @old = 1 AND @new = 1 THEN 'ALTER TABLE `ado_s8_exception` DROP COLUMN `verifier_id`'
  ELSE 'DO 0' END;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── ado_s8_exception.reporter_id → reporter_user_id ──
SET @old := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='ado_s8_exception' AND COLUMN_NAME='reporter_id');
SET @new := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='ado_s8_exception' AND COLUMN_NAME='reporter_user_id');
SET @sql := CASE
  WHEN @old = 1 AND @new = 0 THEN 'ALTER TABLE `ado_s8_exception` CHANGE COLUMN `reporter_id` `reporter_user_id` BIGINT NULL COMMENT ''提报人账号Id（SysUser.Id）'''
  WHEN @old = 1 AND @new = 1 THEN 'ALTER TABLE `ado_s8_exception` DROP COLUMN `reporter_id`'
  ELSE 'DO 0' END;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── ado_s8_exception_timeline.operator_id → operator_user_id ──
SET @old := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='ado_s8_exception_timeline' AND COLUMN_NAME='operator_id');
SET @new := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='ado_s8_exception_timeline' AND COLUMN_NAME='operator_user_id');
SET @sql := CASE
  WHEN @old = 1 AND @new = 0 THEN 'ALTER TABLE `ado_s8_exception_timeline` CHANGE COLUMN `operator_id` `operator_user_id` BIGINT NULL COMMENT ''操作人账号Id（SysUser.Id）；系统动作为 NULL'''
  WHEN @old = 1 AND @new = 1 THEN 'ALTER TABLE `ado_s8_exception_timeline` DROP COLUMN `operator_id`'
  ELSE 'DO 0' END;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── 索引重建 ──
--
-- 这里有一个**实测踩到过的坑**，不要简化成「索引名不存在才建」：
-- CodeFirst 若先于本脚本把 assignee_user_id 建了出来，PHASE 4 会走 (b) DROP COLUMN 分支，
-- 而 MySQL 在 DROP COLUMN 时会把该列**从复合索引里摘掉、索引本身保留**。
-- 结果是 idx_s8_exception_assignee_status 退化成 (tenant_id, factory_id, status, is_deleted) ——
-- 索引名还在，覆盖的列却少了最关键的那一个，「我的待办」从此走不到索引，
-- 而且没有任何报错。因此判据必须是「索引里有没有那一列」，不是「索引名在不在」。
SET @has_col := (SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='ado_s8_exception'
    AND INDEX_NAME='idx_s8_exception_assignee_status' AND COLUMN_NAME='assignee_user_id');
SET @has_idx := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='ado_s8_exception'
    AND INDEX_NAME='idx_s8_exception_assignee_status');
SET @sql := IF(@has_idx = 1 AND @has_col = 0,
  'ALTER TABLE `ado_s8_exception` DROP INDEX `idx_s8_exception_assignee_status`', 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @has_idx := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='ado_s8_exception'
    AND INDEX_NAME='idx_s8_exception_assignee_status');
SET @sql := IF(@has_idx = 0,
  'ALTER TABLE `ado_s8_exception` ADD INDEX `idx_s8_exception_assignee_status` (`tenant_id`,`factory_id`,`assignee_user_id`,`status`,`is_deleted`)',
  'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @has_col := (SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='ado_s8_exception'
    AND INDEX_NAME='idx_s8_exception_verifier_status' AND COLUMN_NAME='verifier_user_id');
SET @has_idx := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='ado_s8_exception'
    AND INDEX_NAME='idx_s8_exception_verifier_status');
SET @sql := IF(@has_idx = 1 AND @has_col = 0,
  'ALTER TABLE `ado_s8_exception` DROP INDEX `idx_s8_exception_verifier_status`', 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
SET @has_idx := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='ado_s8_exception'
    AND INDEX_NAME='idx_s8_exception_verifier_status');
SET @sql := IF(@has_idx = 0,
  'ALTER TABLE `ado_s8_exception` ADD INDEX `idx_s8_exception_verifier_status` (`tenant_id`,`factory_id`,`verifier_user_id`,`status`,`is_deleted`)',
  'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ─────────────────────────────────────────────────────────────────────────────────────
-- PHASE 5 · POSTCHECK
--   任何一条不成立就抛错中止，不让"半成功"通过。
-- ─────────────────────────────────────────────────────────────────────────────────────
INSERT INTO `ado_s8_sysuser_only_reset_1_0_496` (`phase`,`table_name`,`table_class`,`row_count`,`noted_at`)
SELECT 'AFTER','ado_s8_exception','RUNTIME',COUNT(*),NOW() FROM `ado_s8_exception`
UNION ALL SELECT 'AFTER','ado_s8_exception_timeline','RUNTIME',COUNT(*),NOW() FROM `ado_s8_exception_timeline`
UNION ALL SELECT 'AFTER','ado_s8_detection_log','RUNTIME',COUNT(*),NOW() FROM `ado_s8_detection_log`
UNION ALL SELECT 'AFTER','ado_s8_rule_detection_state','RUNTIME',COUNT(*),NOW() FROM `ado_s8_rule_detection_state`
UNION ALL SELECT 'AFTER','ado_s8_notification_log','RUNTIME',COUNT(*),NOW() FROM `ado_s8_notification_log`
UNION ALL SELECT 'AFTER','ado_s8_evidence','RUNTIME',COUNT(*),NOW() FROM `ado_s8_evidence`
UNION ALL SELECT 'AFTER','ado_s8_decision_record','RUNTIME',COUNT(*),NOW() FROM `ado_s8_decision_record`;

INSERT INTO `ado_s8_sysuser_only_reset_1_0_496` (`phase`,`table_name`,`table_class`,`row_count`,`noted_at`)
SELECT 'AFTER','ApprovalFlowInstance(S8 BizType)','FLOW_RUNTIME',COUNT(*),NOW()
  FROM `ApprovalFlowInstance`
 WHERE `BizType` IN ('EXCEPTION_REPORT','EXCEPTION_ESCALATION','EXCEPTION_CLOSURE')
UNION ALL
SELECT 'AFTER','ApprovalFlowTask(S8 BizType)','FLOW_RUNTIME',COUNT(*),NOW()
  FROM `ApprovalFlowTask` t
  JOIN `ApprovalFlowInstance` i ON i.`Id` = t.`InstanceId`
 WHERE i.`BizType` IN ('EXCEPTION_REPORT','EXCEPTION_ESCALATION','EXCEPTION_CLOSURE')
UNION ALL
SELECT 'AFTER','ado_s8_watch_rule(last_run_at NOT NULL)','POLICY_RUNTIME_STATE',COUNT(*),NOW()
  FROM `ado_s8_watch_rule` WHERE `last_run_at` IS NOT NULL;

-- ① 白名单表必须为 0
SET @left := (SELECT COALESCE(SUM(`row_count`),0) FROM `ado_s8_sysuser_only_reset_1_0_496` WHERE `phase`='AFTER');
DROP TABLE IF EXISTS `tmp_s8_496_guard`;
CREATE TABLE `tmp_s8_496_guard` (
  probe BIGINT NOT NULL,
  CONSTRAINT s8_496_BLOCKED_reset_tables_not_empty CHECK (probe = 0)
) COMMENT '1.0.496 断言门禁；通过后自动删除';
INSERT INTO `tmp_s8_496_guard` (probe) VALUES (@left);
DROP TABLE IF EXISTS `tmp_s8_496_guard`;

-- ② 四个身份列必须已改名到位（新列在、旧列不在）
SET @bad := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND ((TABLE_NAME='ado_s8_exception' AND COLUMN_NAME IN ('assignee_id','verifier_id','reporter_id'))
      OR (TABLE_NAME='ado_s8_exception_timeline' AND COLUMN_NAME='operator_id')));
DROP TABLE IF EXISTS `tmp_s8_496_guard`;
CREATE TABLE `tmp_s8_496_guard` (
  probe BIGINT NOT NULL,
  CONSTRAINT s8_496_BLOCKED_old_identity_column_still_present CHECK (probe = 0)
) COMMENT '1.0.496 断言门禁；通过后自动删除';
INSERT INTO `tmp_s8_496_guard` (probe) VALUES (@bad);
DROP TABLE IF EXISTS `tmp_s8_496_guard`;

SET @ok := (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND ((TABLE_NAME='ado_s8_exception' AND COLUMN_NAME IN ('assignee_user_id','verifier_user_id','reporter_user_id'))
      OR (TABLE_NAME='ado_s8_exception_timeline' AND COLUMN_NAME='operator_user_id')));
DROP TABLE IF EXISTS `tmp_s8_496_guard`;
CREATE TABLE `tmp_s8_496_guard` (
  probe BIGINT NOT NULL,
  CONSTRAINT s8_496_BLOCKED_new_identity_column_missing CHECK (probe = 0)
) COMMENT '1.0.496 断言门禁；通过后自动删除';
INSERT INTO `tmp_s8_496_guard` (probe) VALUES ((4 - @ok));
DROP TABLE IF EXISTS `tmp_s8_496_guard`;

-- ③ 两个身份索引必须真的覆盖身份列（不是「索引名还在」就算数，见 PHASE 4 的坑）
SET @idx_ok := (SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='ado_s8_exception'
    AND ((INDEX_NAME='idx_s8_exception_assignee_status' AND COLUMN_NAME='assignee_user_id')
      OR (INDEX_NAME='idx_s8_exception_verifier_status' AND COLUMN_NAME='verifier_user_id')));
DROP TABLE IF EXISTS `tmp_s8_496_guard`;
CREATE TABLE `tmp_s8_496_guard` (
  probe BIGINT NOT NULL,
  CONSTRAINT s8_496_BLOCKED_identity_index_lost_its_column CHECK (probe = 0)
) COMMENT '1.0.496 断言门禁；通过后自动删除';
INSERT INTO `tmp_s8_496_guard` (probe) VALUES ((2 - @idx_ok));
DROP TABLE IF EXISTS `tmp_s8_496_guard`;

-- ④ 平台主数据与配置态一行都不能少 —— 这是本脚本最重要的一条断言。
--    RESET 允许清运行数据，但只要下面任何一张表被清空，就说明白名单被写错了。
SET @masters := (SELECT (SELECT COUNT(*) FROM `SysUser`)
                      + (SELECT COUNT(*) FROM `SysRole`)
                      + (SELECT COUNT(*) FROM `EmployeeMaster`)
                      + (SELECT COUNT(*) FROM `DepartmentMaster`));
DROP TABLE IF EXISTS `tmp_s8_496_guard`;
CREATE TABLE `tmp_s8_496_guard` (
  probe BIGINT NOT NULL,
  CONSTRAINT s8_496_BLOCKED_platform_master_wiped CHECK (probe = 0)
) COMMENT '1.0.496 断言门禁；通过后自动删除';
INSERT INTO `tmp_s8_496_guard` (probe) VALUES (IF(@masters = 0, 1, 0));
DROP TABLE IF EXISTS `tmp_s8_496_guard`;

SET @configs := (SELECT (SELECT COUNT(*) FROM `ado_s8_exception_type`)
                      + (SELECT COUNT(*) FROM `ado_s8_scene_config`)
                      + (SELECT COUNT(*) FROM `ado_s8_notification_layer`)
                      + (SELECT COUNT(*) FROM `ado_s8_role_permission_config`)
                      + (SELECT COUNT(*) FROM `ado_s8_watch_rule`));
DROP TABLE IF EXISTS `tmp_s8_496_guard`;
CREATE TABLE `tmp_s8_496_guard` (
  probe BIGINT NOT NULL,
  CONSTRAINT s8_496_BLOCKED_product_config_wiped CHECK (probe = 0)
) COMMENT '1.0.496 断言门禁；通过后自动删除';
INSERT INTO `tmp_s8_496_guard` (probe) VALUES (IF(@configs = 0, 1, 0));
DROP TABLE IF EXISTS `tmp_s8_496_guard`;

-- ─────────────────────────────────────────────────────────────────────────────────────
-- PHASE 6 · IDEMPOTENT RERUN GUARD
--   第二次执行时：PHASE 3 的 DELETE 命中 0 行、PHASE 4 全部落到 'DO 0'、
--   PHASE 5 的断言依旧成立。下面这条只是把「本脚本已收敛」这件事也记进留证表。
-- ─────────────────────────────────────────────────────────────────────────────────────
INSERT INTO `ado_s8_sysuser_only_reset_1_0_496` (`phase`,`table_name`,`table_class`,`row_count`,`noted_at`)
VALUES ('AFTER','__migration_converged__','GUARD',1,NOW());