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

-- 易积水点：责任单位 / 责任人改为关联系统部门与用户
--
-- 背景：原表只存 duty_unit / duty_person / duty_phone 三个自由文本，
-- 与 sys_dept / sys_user 没有任何关联。带来的问题是：
--   · 同一个部门被手输成多种写法，统计口径无法收敛；
--   · 人员调岗、改名后台账失真，也点不进去看这个人是谁；
--   · 联系电话靠手填，容易与人事信息不一致。
--
-- 本脚本做两件事：
--   1. 幂等补两列：duty_dept_id（sys_dept.dept_id）、duty_user_id（sys_user.user_id）；
--   2. 尽力回填：按名称精确匹配 sys_dept.dept_name / sys_user.nick_name，
--      匹配上的补 ID，匹配不上的保持 NULL（不猜、不改已有文本）。
--
-- 保留原有三列作为「快照」：
--   duty_unit / duty_person / duty_phone 继续存当时的名称与号码。
--   列表、地图、详情直接读快照，不必回表 join；人员改名也不影响历史记录。
--
-- 前置：结束上一次失败执行遗留的 aborted 事务（事务外执行只会告警，不报错）
ROLLBACK;

BEGIN;

-- =====================================================================
-- 1. 幂等补列
-- =====================================================================
DO $$
DECLARE
  col record;
BEGIN
  IF to_regclass('app_user.drainage_waterlogging_point') IS NULL THEN
    RAISE NOTICE '表 app_user.drainage_waterlogging_point 不存在，请先执行 migration_drainage_waterlogging_point.sql';
    RETURN;
  END IF;

  FOR col IN
    SELECT * FROM (VALUES
        ('duty_dept_id', 'bigint'),
        ('duty_user_id', 'bigint')
      ) AS x(name, type)
  LOOP
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
         WHERE table_schema = 'app_user'
           AND table_name = 'drainage_waterlogging_point'
           AND column_name = col.name
    ) THEN
      EXECUTE format('ALTER TABLE app_user.drainage_waterlogging_point ADD COLUMN %I %s',
                     col.name, col.type);
      RAISE NOTICE '已补列：%', col.name;
    END IF;
  END LOOP;
END $$;

COMMENT ON COLUMN app_user.drainage_waterlogging_point.duty_dept_id IS '责任单位（sys_dept.dept_id，duty_unit 存其名称快照）';
COMMENT ON COLUMN app_user.drainage_waterlogging_point.duty_user_id IS '责任人（sys_user.user_id，duty_person / duty_phone 存其姓名与电话快照）';

CREATE INDEX IF NOT EXISTS idx_dwp_duty_dept ON app_user.drainage_waterlogging_point (duty_dept_id);
CREATE INDEX IF NOT EXISTS idx_dwp_duty_user ON app_user.drainage_waterlogging_point (duty_user_id);

-- =====================================================================
-- 2. 存量数据回填（只做精确匹配，匹配不上就留空）
--    同一名称对应多个部门/用户时取 ID 最小的那个，避免一对多歧义放大。
-- =====================================================================
DO $$
DECLARE
  v_dept integer := 0;
  v_user integer := 0;
BEGIN
  IF to_regclass('app_user.drainage_waterlogging_point') IS NULL THEN
    RETURN;
  END IF;

  -- 2.1 责任单位：duty_unit 文本 → dept_id
  UPDATE app_user.drainage_waterlogging_point p
     SET duty_dept_id = d.dept_id
    FROM (
      SELECT dept_name, MIN(dept_id) AS dept_id
        FROM app_user.sys_dept
       WHERE del_flag = '0'
       GROUP BY dept_name
    ) d
   WHERE p.duty_dept_id IS NULL
     AND p.duty_unit IS NOT NULL
     AND trim(p.duty_unit) = d.dept_name;
  GET DIAGNOSTICS v_dept = ROW_COUNT;

  -- 2.2 责任人：duty_person 文本 → user_id
  UPDATE app_user.drainage_waterlogging_point p
     SET duty_user_id = u.user_id
    FROM (
      SELECT nick_name, MIN(user_id) AS user_id
        FROM app_user.sys_user
       WHERE del_flag = '0'
       GROUP BY nick_name
    ) u
   WHERE p.duty_user_id IS NULL
     AND p.duty_person IS NOT NULL
     AND trim(p.duty_person) = u.nick_name;
  GET DIAGNOSTICS v_user = ROW_COUNT;

  RAISE NOTICE '存量回填：责任单位匹配 % 条，责任人匹配 % 条', v_dept, v_user;
END $$;

-- =====================================================================
-- 3. 结果核验（可选，取消注释执行）
-- =====================================================================
-- 还没关联上的记录（需要人工在页面上重新选一次部门/人员）
--    SELECT point_code, point_name, duty_unit, duty_person
--      FROM app_user.drainage_waterlogging_point
--     WHERE del_flag = '0'
--       AND ((duty_unit IS NOT NULL AND duty_dept_id IS NULL)
--         OR (duty_person IS NOT NULL AND duty_user_id IS NULL));

COMMIT;