-- =====================================================================================
-- 1.0.503  S8-RESPONSIBILITY-POOL-1 · 规则复核账号池 / 升级账号池
--
-- 目标：把 Rule 的责任模型补齐成三个池 —— 处理 / 复核 / 升级，成员一律 SysUser.Id。
--   · 处理池 = 谁负责处理这条规则产生的异常（1.0.498 已建，本脚本不动它）
--   · 复核池 = 谁有资格被选为这条规则的复核人（本脚本新建）
--   · 升级池 = 异常超时 / 触发升级时找谁（本脚本新建）
--
-- 存储选型（最低风险优先，不是审美优先）：
--   · 复核 + 升级合并进**一张**关系表 ado_s8_rule_responsibility_user，
--     用 responsibility_type 区分。两类的读写操作完全同构，分两张表等于把同一段
--     服务代码抄两遍 —— 抄出来的两份迟早不一致，而不一致的那一刻没有任何报错。
--   · 处理池**不迁**。它已有稳定的存量表与四条生产读路径（就绪门禁 / 认领资格 /
--     可见性收敛 / 通知收件人解析）。为了"三张表长得一样"去迁它，收益是观感、
--     代价是动四条已经跑通的链路。存储差异被收敛在服务层的统一 reader 里，
--     调用方看到的仍是同一套 API。
--   · 身份用 rule_code 而非 rule_id：与 1.0.498 同口径 —— watch_rule 的行会被
--     clean reset + provisioning 重建、id 全部变化，绑 id 会让责任池在那一刻
--     集体失联，而且不报错：规则照常跑，只是谁都不再"负责"它。
--
-- 表由 CodeFirst(InitTables) 创建；本脚本只补索引 + 断言，**不写任何成员数据**
-- （成员是 per-tenant 的人工配置，写死在迁移里既不适配新租户、也会覆盖管理员的选择，
--  更会让"责任池是空的"这个必须被看见的事实被一批假成员盖住）。
-- =====================================================================================

-- ─────────────────────────────────────────────────────────────────────────────────────
-- PHASE 1 · PREFLIGHT
--   建表与 CodeFirst 等价；两者都执行时以先到者为准，后者跳过。
-- ─────────────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `ado_s8_rule_responsibility_user` (
  `id`                  BIGINT       NOT NULL AUTO_INCREMENT,
  `tenant_id`           BIGINT       NOT NULL COMMENT '租户（唯一安全边界）',
  `rule_code`           VARCHAR(128) NOT NULL COMMENT '规则编码，与 provisioning 身份口径一致',
  `responsibility_type` VARCHAR(32)  NOT NULL COMMENT 'REVIEWER / ESCALATION',
  `user_id`             BIGINT       NOT NULL COMMENT '成员账号 SysUser.Id',
  `created_at`          DATETIME     NOT NULL,
  `created_by`          VARCHAR(64)  NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='S8 规则复核/升级账号池';

-- 处理池必须已存在：本批的统一 reader 会路由到它，缺表等于三个池里少一个。
SET @has_handler := (SELECT COUNT(*) FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ado_s8_rule_handler_user');
DROP TABLE IF EXISTS `tmp_s8_503_guard`;
CREATE TABLE `tmp_s8_503_guard` (
  probe BIGINT NOT NULL,
  CONSTRAINT s8_503_BLOCKED_handler_pool_table_missing CHECK (probe = 0)
) COMMENT '1.0.503 断言门禁；通过后自动删除';
INSERT INTO `tmp_s8_503_guard` (probe) VALUES ((1 - @has_handler));
DROP TABLE IF EXISTS `tmp_s8_503_guard`;

-- ─────────────────────────────────────────────────────────────────────────────────────
-- PHASE 2 · SCHEMA
--   唯一索引带 responsibility_type：同一账号既当复核人又当升级人是合法配置，
--   两条行必须能共存；而"同一类型下重复配同一人"必须在 DB 层挡住 ——
--   没有它，重复成员会让"移出池"变成删一行、剩一行：界面显示已移出，实际仍然算数。
--   反查索引：按 (tenant, user) 反查该账号承担的责任，避免全表扫。
-- ─────────────────────────────────────────────────────────────────────────────────────
SET @has_uk := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='ado_s8_rule_responsibility_user'
    AND INDEX_NAME='uk_s8_rule_responsibility');

-- 加唯一索引前先去重，否则 ALTER 会失败并把整个迁移卡住。
SET @sql := IF(@has_uk = 0, '
DELETE a FROM `ado_s8_rule_responsibility_user` a
  JOIN `ado_s8_rule_responsibility_user` b
    ON a.`tenant_id` = b.`tenant_id`
   AND a.`rule_code` = b.`rule_code`
   AND a.`responsibility_type` = b.`responsibility_type`
   AND a.`user_id` = b.`user_id`
   AND a.`id` > b.`id`', 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IF(@has_uk = 0,
  'ALTER TABLE `ado_s8_rule_responsibility_user` ADD UNIQUE INDEX `uk_s8_rule_responsibility` (`tenant_id`,`rule_code`,`responsibility_type`,`user_id`)',
  '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_rule_responsibility_user'
    AND INDEX_NAME='idx_s8_rule_responsibility_user');
SET @sql := IF(@has_idx = 0,
  'ALTER TABLE `ado_s8_rule_responsibility_user` ADD INDEX `idx_s8_rule_responsibility_user` (`tenant_id`,`user_id`)',
  'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ─────────────────────────────────────────────────────────────────────────────────────
-- PHASE 3 · POSTCHECK
-- ─────────────────────────────────────────────────────────────────────────────────────
-- ① 唯一索引必须覆盖四列。只查索引名存在是不够的 —— 1.0.496 踩过：
--    列被 DROP 之后 MySQL 会把它移出复合索引却保留索引名，名字还在、约束已经没了。
SET @cols := (SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME='ado_s8_rule_responsibility_user'
    AND INDEX_NAME='uk_s8_rule_responsibility'
    AND COLUMN_NAME IN ('tenant_id','rule_code','responsibility_type','user_id'));
DROP TABLE IF EXISTS `tmp_s8_503_guard`;
CREATE TABLE `tmp_s8_503_guard` (
  probe BIGINT NOT NULL,
  CONSTRAINT s8_503_BLOCKED_unique_index_missing CHECK (probe = 0)
) COMMENT '1.0.503 断言门禁；通过后自动删除';
INSERT INTO `tmp_s8_503_guard` (probe) VALUES ((4 - @cols));
DROP TABLE IF EXISTS `tmp_s8_503_guard`;

-- ② 不允许出现「池成员不属于该行租户」的数据。
--    这是本批的核心安全断言：责任池的隔离维度只有 tenant_id，工厂不参与。
SET @bad_tenant := (SELECT COUNT(*) FROM `ado_s8_rule_responsibility_user` r
  JOIN `SysUser` u ON u.`Id` = r.`user_id`
 WHERE u.`TenantId` <> r.`tenant_id`);
DROP TABLE IF EXISTS `tmp_s8_503_guard`;
CREATE TABLE `tmp_s8_503_guard` (
  probe BIGINT NOT NULL,
  CONSTRAINT s8_503_BLOCKED_cross_tenant_member CHECK (probe = 0)
) COMMENT '1.0.503 断言门禁；通过后自动删除';
INSERT INTO `tmp_s8_503_guard` (probe) VALUES (@bad_tenant);
DROP TABLE IF EXISTS `tmp_s8_503_guard`;

-- ③ responsibility_type 只允许目录内的两个值。
--    HANDLER 不该出现在本表 —— 它在存量表里；出现即说明有人绕过了服务层写入。
SET @bad_type := (SELECT COUNT(*) FROM `ado_s8_rule_responsibility_user`
  WHERE `responsibility_type` NOT IN ('REVIEWER','ESCALATION'));
DROP TABLE IF EXISTS `tmp_s8_503_guard`;
CREATE TABLE `tmp_s8_503_guard` (
  probe BIGINT NOT NULL,
  CONSTRAINT s8_503_BLOCKED_unknown_responsibility_type CHECK (probe = 0)
) COMMENT '1.0.503 断言门禁；通过后自动删除';
INSERT INTO `tmp_s8_503_guard` (probe) VALUES (@bad_type);
DROP TABLE IF EXISTS `tmp_s8_503_guard`;

-- ─────────────────────────────────────────────────────────────────────────────────────
-- PHASE 4 · IDEMPOTENT RERUN GUARD
--   第二次执行：建表跳过、索引已存在跳过、断言依旧成立。
--   本脚本不写任何成员数据，也不绑定任何具体账号或角色，
--   因此重跑不会改变任何一条责任归属 —— 部署后三个池的初始状态一律为空，
--   由管理员在「责任分配」页显式配置。空池会被就绪门禁挡在启用之前（fail closed）。
-- ─────────────────────────────────────────────────────────────────────────────────────