pipe-ner-server/pipe-network-service/sql/migration_dispatch_user_id.sql

-- =====================================================================
-- 应急与指挥调度模块（dispatch）—— 人员字段补存「用户ID」迁移脚本
-- 数据库：KingbaseES（PG 兼容模式），业务 schema = app_user
--
-- 背景（问题）：
--   原设计只在业务表里存人员的「名称快照」（接报人 / 核验人 / 责任处置人 / 修订人 /
--   评估人 / 创建人 / 修改人），没有存 sys_user.user_id，导致：
--     ① 用户改名后，历史数据无法再关联到本人；
--     ② 无法按用户维度筛选（如「查我的接报」）；
--     ③ 与日志表的 oper_user_id 口径不一致（日志早就存了 id + 名称两份）。
--
-- 本脚本做什么：
--   1) 补 *_id 列（int8，与 sys_user.user_id 同类型）：
--        dis_event            verifier_id / handler_id / create_user_id / update_user_id
--        dis_plan             create_user_id / update_user_id
--        dis_plan_version     editor_id
--        dis_event_grade      create_user_id / update_user_id
--        dis_event_assess     assessor_id / create_user_id / update_user_id
--        dis_plan_library     create_user_id
--        dis_plan_match_record create_user_id
--   2) 按名称回填存量数据的 *_id —— **仅当该名称在 sys_user 中唯一命中时才回填**，
--      命中多条或查不到的一律留空，避免张冠李戴。
--
-- 口径说明（重要）：
--   · 名称列（verifier / handler / editor / assessor / create_by / update_by）
--     **保留不删**，作为快照冗余：① 兼容存量数据；② 用户离职被删后仍能展示文字。
--     展示口径：以 *_id 反查 sys_user.nick_name 为准，查不到再退回名称快照。
--   · dis_event 接报人统一由 create_user_id 关联，不再保留 reporter / reporter_id；
--     该表 create_by / update_by 亦已移除，审计只使用 create_user_id / update_user_id。
--   · dis_plan_library 已移除 create_by，创建人只使用 create_user_id，不再按旧名称回填。
--   · create_by / update_by 是 RuoYi 通用字段，存的是**登录名 sys_user.user_name**，
--     故回填按 user_name 匹配；其余业务人员列存的是**昵称 sys_user.nick_name**，按 nick_name 匹配。
--   · 不变更任何现存列的类型与取值，纯新增，不破坏既有功能。
--   · dis_event_log / dis_plan_log 早在建模时就是 oper_user_id + oper_user_name 双字段，本脚本不涉及。
--
-- 幂等性：ADD COLUMN IF NOT EXISTS，可重复执行；回填只作用于 *_id IS NULL 的行。
--
-- ⚠️ KingbaseES 方言红线（本库实测）：
--   · `||` 在本实例被解析为**布尔 OR**，禁止用于字符串拼接，必须用 concat()。
--   · information_schema.tables 会触发 sys_freespace() 权限校验，业务账号可能无权限，
--     判断表是否存在请用 to_regclass()。
-- ⚠️ 客户端执行提示：
--   · 语句失败后事务会挂起（25P02），后续查询一律报「当前事务被终止」，先执行 ROLLBACK; 再重试。
--   · 选中运行时不要连带注释行，客户端会把注释并入语句并吃掉一个减号，导致语法错。
--   · 本脚本为单事务，请整体执行（BEGIN; … COMMIT;），不要逐段选中执行。
-- =====================================================================

BEGIN;

-- =====================================================================
-- 一、补列
-- =====================================================================

-- 1.1 dis_event 事件主表
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS verifier_id     int8;
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS handler_id      int8;
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS create_user_id  int8;
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS update_user_id  int8;

COMMENT ON COLUMN app_user.dis_event.verifier_id    IS '核验人用户ID（sys_user.user_id）；初判核实时的当前登录用户';
COMMENT ON COLUMN app_user.dis_event.handler_id     IS '责任处置人用户ID（sys_user.user_id）；与 handler 快照、handler_phone 同时写入';
COMMENT ON COLUMN app_user.dis_event.create_user_id IS '创建人用户ID（sys_user.user_id）';
COMMENT ON COLUMN app_user.dis_event.update_user_id IS '最后修改人用户ID（sys_user.user_id）';

-- 1.2 dis_plan 预案主表
ALTER TABLE app_user.dis_plan ADD COLUMN IF NOT EXISTS create_user_id int8;
ALTER TABLE app_user.dis_plan ADD COLUMN IF NOT EXISTS update_user_id int8;

COMMENT ON COLUMN app_user.dis_plan.create_user_id IS '创建人用户ID（sys_user.user_id）；create_by 存登录名，本列存ID';
COMMENT ON COLUMN app_user.dis_plan.update_user_id IS '最后修改人用户ID（sys_user.user_id）；update_by 存登录名，本列存ID';

-- 1.3 dis_plan_version 预案版本快照表
ALTER TABLE app_user.dis_plan_version ADD COLUMN IF NOT EXISTS editor_id int8;

COMMENT ON COLUMN app_user.dis_plan_version.editor_id IS '修订人用户ID（sys_user.user_id）；与 editor 名称快照同时写入';

-- 1.4 dis_event_grade 事件分级标准表
ALTER TABLE app_user.dis_event_grade ADD COLUMN IF NOT EXISTS create_user_id int8;
ALTER TABLE app_user.dis_event_grade ADD COLUMN IF NOT EXISTS update_user_id int8;

COMMENT ON COLUMN app_user.dis_event_grade.create_user_id IS '创建人用户ID（sys_user.user_id）；create_by 存登录名，本列存ID';
COMMENT ON COLUMN app_user.dis_event_grade.update_user_id IS '最后修改人用户ID（sys_user.user_id）；update_by 存登录名，本列存ID';

-- 1.5 dis_event_assess 事后评估表
ALTER TABLE app_user.dis_event_assess ADD COLUMN IF NOT EXISTS assessor_id    int8;
ALTER TABLE app_user.dis_event_assess ADD COLUMN IF NOT EXISTS create_user_id int8;
ALTER TABLE app_user.dis_event_assess ADD COLUMN IF NOT EXISTS update_user_id int8;

COMMENT ON COLUMN app_user.dis_event_assess.assessor_id    IS '评估人用户ID（sys_user.user_id）；与 assessor 名称快照同时写入';
COMMENT ON COLUMN app_user.dis_event_assess.create_user_id IS '创建人用户ID（sys_user.user_id）；create_by 存登录名，本列存ID';
COMMENT ON COLUMN app_user.dis_event_assess.update_user_id IS '最后修改人用户ID（sys_user.user_id）；update_by 存登录名，本列存ID';

-- 1.6 数字预案库 / 匹配记录（只有创建人，无人工操作列）
ALTER TABLE app_user.dis_plan_library      ADD COLUMN IF NOT EXISTS create_user_id int8;
ALTER TABLE app_user.dis_plan_match_record ADD COLUMN IF NOT EXISTS create_user_id int8;

COMMENT ON COLUMN app_user.dis_plan_library.create_user_id      IS '创建人用户ID（sys_user.user_id）';
COMMENT ON COLUMN app_user.dis_plan_match_record.create_user_id IS '创建人用户ID（sys_user.user_id）；create_by 存登录名，本列存ID';

-- 1.7 索引：列表可能按「我的接报 / 我核实的」筛选
CREATE INDEX IF NOT EXISTS idx_dis_event_verifier_id ON app_user.dis_event(verifier_id);
CREATE INDEX IF NOT EXISTS idx_dis_event_handler_id  ON app_user.dis_event(handler_id);

-- =====================================================================
-- 二、存量数据回填
--     匹配口径：昵称列 → sys_user.nick_name；create_by/update_by → sys_user.user_name。
--     只回填「唯一命中」的名称；同名（如两个「张伟」）或查不到的一律留空。
-- =====================================================================

-- 2.1 dis_event —— 核验人（昵称）
UPDATE app_user.dis_event e
   SET verifier_id = m.user_id
  FROM (SELECT nick_name, min(user_id) AS user_id, count(*) AS cnt
          FROM app_user.sys_user WHERE del_flag = '0' GROUP BY nick_name) m
 WHERE e.verifier_id IS NULL
   AND e.verifier IS NOT NULL
   AND e.verifier = m.nick_name
   AND m.cnt = 1;

-- 2.2 dis_event —— 责任处置人（昵称）
UPDATE app_user.dis_event e
   SET handler_id = m.user_id
  FROM (SELECT nick_name, min(user_id) AS user_id, count(*) AS cnt
          FROM app_user.sys_user WHERE del_flag = '0' GROUP BY nick_name) m
 WHERE e.handler_id IS NULL
   AND e.handler IS NOT NULL
   AND e.handler = m.nick_name
   AND m.cnt = 1;

-- 2.5 dis_plan —— 创建人 / 修改人（登录名）
UPDATE app_user.dis_plan p
   SET create_user_id = m.user_id
  FROM (SELECT user_name, min(user_id) AS user_id, count(*) AS cnt
          FROM app_user.sys_user WHERE del_flag = '0' GROUP BY user_name) m
 WHERE p.create_user_id IS NULL
   AND p.create_by IS NOT NULL
   AND p.create_by = m.user_name
   AND m.cnt = 1;

UPDATE app_user.dis_plan p
   SET update_user_id = m.user_id
  FROM (SELECT user_name, min(user_id) AS user_id, count(*) AS cnt
          FROM app_user.sys_user WHERE del_flag = '0' GROUP BY user_name) m
 WHERE p.update_user_id IS NULL
   AND p.update_by IS NOT NULL
   AND p.update_by = m.user_name
   AND m.cnt = 1;

-- 2.6 dis_plan_version —— 修订人（昵称）
UPDATE app_user.dis_plan_version v
   SET editor_id = m.user_id
  FROM (SELECT nick_name, min(user_id) AS user_id, count(*) AS cnt
          FROM app_user.sys_user WHERE del_flag = '0' GROUP BY nick_name) m
 WHERE v.editor_id IS NULL
   AND v.editor IS NOT NULL
   AND v.editor = m.nick_name
   AND m.cnt = 1;

-- 2.7 dis_event_grade —— 创建人 / 修改人（登录名）
UPDATE app_user.dis_event_grade g
   SET create_user_id = m.user_id
  FROM (SELECT user_name, min(user_id) AS user_id, count(*) AS cnt
          FROM app_user.sys_user WHERE del_flag = '0' GROUP BY user_name) m
 WHERE g.create_user_id IS NULL
   AND g.create_by IS NOT NULL
   AND g.create_by = m.user_name
   AND m.cnt = 1;

UPDATE app_user.dis_event_grade g
   SET update_user_id = m.user_id
  FROM (SELECT user_name, min(user_id) AS user_id, count(*) AS cnt
          FROM app_user.sys_user WHERE del_flag = '0' GROUP BY user_name) m
 WHERE g.update_user_id IS NULL
   AND g.update_by IS NOT NULL
   AND g.update_by = m.user_name
   AND m.cnt = 1;

-- 2.8 dis_event_assess —— 评估人（昵称）
UPDATE app_user.dis_event_assess a
   SET assessor_id = m.user_id
  FROM (SELECT nick_name, min(user_id) AS user_id, count(*) AS cnt
          FROM app_user.sys_user WHERE del_flag = '0' GROUP BY nick_name) m
 WHERE a.assessor_id IS NULL
   AND a.assessor IS NOT NULL
   AND a.assessor = m.nick_name
   AND m.cnt = 1;

-- 2.9 dis_event_assess —— 创建人 / 修改人（登录名）
UPDATE app_user.dis_event_assess a
   SET create_user_id = m.user_id
  FROM (SELECT user_name, min(user_id) AS user_id, count(*) AS cnt
          FROM app_user.sys_user WHERE del_flag = '0' GROUP BY user_name) m
 WHERE a.create_user_id IS NULL
   AND a.create_by IS NOT NULL
   AND a.create_by = m.user_name
   AND m.cnt = 1;

UPDATE app_user.dis_event_assess a
   SET update_user_id = m.user_id
  FROM (SELECT user_name, min(user_id) AS user_id, count(*) AS cnt
          FROM app_user.sys_user WHERE del_flag = '0' GROUP BY user_name) m
 WHERE a.update_user_id IS NULL
   AND a.update_by IS NOT NULL
   AND a.update_by = m.user_name
   AND m.cnt = 1;

-- 2.10 dis_plan_match_record —— 创建人（登录名）；预案库不再保留 create_by。
UPDATE app_user.dis_plan_match_record r
   SET create_user_id = m.user_id
  FROM (SELECT user_name, min(user_id) AS user_id, count(*) AS cnt
          FROM app_user.sys_user WHERE del_flag = '0' GROUP BY user_name) m
 WHERE r.create_user_id IS NULL
   AND r.create_by IS NOT NULL
   AND r.create_by = m.user_name
   AND m.cnt = 1;

COMMIT;

-- =====================================================================
-- 三、执行后自检（另开查询窗口执行，不属于本事务）
-- =====================================================================
-- 1) 列是否补齐
--    SELECT table_name, column_name FROM information_schema.columns
--     WHERE table_schema = 'app_user' AND column_name LIKE '%_id'
--       AND table_name IN ('dis_event','dis_plan','dis_plan_version','dis_event_grade',
--                          'dis_event_assess','dis_plan_library','dis_plan_match_record')
--     ORDER BY table_name, column_name;
-- 2) 回填覆盖率（total = 有名称的行数，filled = 已回填用户ID的行数）
--    SELECT 'dis_event.verifier' AS col, count(verifier) AS total, count(verifier_id) AS filled FROM app_user.dis_event
--     UNION ALL SELECT 'dis_event.handler',       count(handler),    count(handler_id)    FROM app_user.dis_event
--     UNION ALL SELECT 'dis_plan.create_by',      count(create_by),  count(create_user_id) FROM app_user.dis_plan
--     UNION ALL SELECT 'dis_plan_version.editor', count(editor),     count(editor_id)     FROM app_user.dis_plan_version;
-- 3) 名称与ID是否对得上（应返回 0 行）
--    SELECT id, verifier, verifier_id FROM app_user.dis_event e
--     WHERE verifier_id IS NOT NULL
--       AND NOT EXISTS (SELECT 1 FROM app_user.sys_user u WHERE u.user_id = e.verifier_id AND u.nick_name = e.verifier);