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

-- 排水「所属片区」字典（KingbaseES）
-- 用途：排水子系统里凡是出现「所属区域 / 所属片区」的下拉框，统一读取本字典，
--      不再从业务表的存量值里去重生成选项（旧做法会把脏数据回灌到下拉里）。
-- 口径：与既有 DistrictEnum（com.zksy.base.enums.DistrictEnum）、
--      equipment_base.district 保持一致：0-城南 / 1-城北 / 2-太常。
--      库中存编码，中文只在前端展示。
-- 影响：drainage_waterlogging_point.district（varchar）由「中文地名」改为「编码」。
-- 说明：脚本幂等，可重复执行；只维护字典 + 归一存量片区值，不改表结构。
--
-- =====================================================================
-- 为什么本脚本要「显式指定主键」
-- ---------------------------------------------------------------------
-- 本库由 MySQL 迁移至 Kingbase，sys_dict_data / sys_dict_type 建表沿用了 MySQL 的
-- auto_increment（KES 的 MySQL 兼容自增，不是 PG 的 serial/identity）。迁数据时是带显式
-- 主键写入的，自增计数器没有跟着推进，于是「INSERT 不写主键」会发出一个已被占用的号：
--     ERROR: 重复键违反唯一约束 "sys_dict_data_pkey"
--     DETAIL: 键值 "dict_code"=(415) 已经存在
-- 且该列不是 identity 列，pg_get_serial_sequence / pg_depend 都拿不到序列，
-- ALTER COLUMN ... RESTART WITH 也会报「不是标识列」。
--
-- 因此处理方式是：
--   ① INSERT 一律写成 max(主键) + n，显式指定，彻底绕开自增计数器；
--   ② 插入完再把能找到的序列 setval 到最新 max（步骤 5），让后续新增不受影响；
--      若确实没有关联序列（纯 auto_increment），步骤 5 只提示、不报错。
-- =====================================================================
--
-- 前置（必须先执行）：结束上一次失败执行遗留的 aborted 事务
--   上一次语句报错后，PG/KES 会把该事务标记为已失败，此时同一连接上后续任何语句都只返回
--   「当前事务被终止，事务块结束之前的查询被忽略」，必须先回滚才能继续。
--   注意：本句会回滚该连接上所有未提交事务；若你有别的未提交手工操作，请先单独提交再跑本脚本。
ROLLBACK;

BEGIN;

-- 1. 字典类型（幂等登记；dict_id 显式取 max+1）
INSERT INTO app_user.sys_dict_type
    (dict_id, dict_name, dict_type, status, create_by, create_time, remark)
SELECT (SELECT COALESCE(MAX(dict_id), 0) FROM app_user.sys_dict_type) + 1,
       '所属片区', 'drainage_district', '0', 'admin', CURRENT_TIMESTAMP,
       '排水所属片区：0城南 1城北 2太常（与 DistrictEnum 对齐）'
 WHERE NOT EXISTS (SELECT 1 FROM app_user.sys_dict_type WHERE dict_type = 'drainage_district');

UPDATE app_user.sys_dict_type
   SET dict_name = '所属片区',
       status = '0',
       update_by = 'admin',
       update_time = CURRENT_TIMESTAMP,
       remark = '排水所属片区：0城南 1城北 2太常（与 DistrictEnum 对齐）'
 WHERE dict_type = 'drainage_district';

-- 2. 字典数据（幂等重写；dict_code 显式取 max+序号）
--    片区不是严重性等级，list_class 统一留空走默认色，不做颜色区分。
DELETE FROM app_user.sys_dict_data WHERE dict_type = 'drainage_district';

INSERT INTO app_user.sys_dict_data
    (dict_code, dict_sort, dict_label, dict_value, dict_type, css_class, list_class,
     is_default, status, create_by, create_time, remark)
SELECT (SELECT COALESCE(MAX(dict_code), 0) FROM app_user.sys_dict_data) + v.rn,
       v.dict_sort, v.dict_label, v.dict_value, 'drainage_district',
       '', '', v.is_default, '0', 'admin', CURRENT_TIMESTAMP, v.remark
  FROM (VALUES
        (1, 1, '城南', '0', 'N', '所属片区：城南'),
        (2, 2, '城北', '1', 'N', '所属片区：城北'),
        (3, 3, '太常', '2', 'N', '所属片区：太常')
       ) AS v(rn, dict_sort, dict_label, dict_value, is_default, remark)
 ORDER BY v.rn;

-- 3. 列注释对齐（幂等，可重复执行）
COMMENT ON COLUMN app_user.drainage_waterlogging_point.district
    IS '所属片区（字典 drainage_district：0城南 1城北 2太常）';

-- 4. 存量片区值归一：中文地名 → 编码
--    只处理能精确匹配的三个片区；其余值（历史脏数据，如乱填的 '123123'）
--    一律原样保留，交由步骤 6 的体检输出，由人工确认后再处理，避免误伤真实数据。
UPDATE app_user.drainage_waterlogging_point
   SET district = CASE TRIM(district)
                    WHEN '城南' THEN '0'
                    WHEN '城北' THEN '1'
                    WHEN '太常' THEN '2'
                  END
 WHERE TRIM(district) IN ('城南', '城北', '太常');

-- 5. 自增计数器校准（放在插入之后：此时 max 已含新插入的 3 行）
--    三种方式依次尝试，任一成功即可；都不成功只提示，不影响本脚本已插入的数据。
--      方式一 pg_get_serial_sequence（serial / identity）
--      方式二 从列默认值里解析 nextval('...')（KES 常见形态）
--      方式三 pg_depend 关联序列（兜底）
DO $$
DECLARE
  t         record;
  v_seq     text;
  v_max     bigint;
  v_default text;
BEGIN
  FOR t IN
    SELECT * FROM (VALUES ('sys_dict_data', 'dict_code'),
                          ('sys_dict_type', 'dict_id')) AS x(tbl, col)
  LOOP
    v_seq := NULL;
    v_default := NULL;

    -- 方式一
    BEGIN
      v_seq := pg_get_serial_sequence(format('app_user.%I', t.tbl), t.col);
    EXCEPTION WHEN OTHERS THEN
      v_seq := NULL;
    END;

    -- 方式二：解析列默认值里的 nextval('xxx')
    IF v_seq IS NULL THEN
      BEGIN
        SELECT pg_get_expr(ad.adbin, ad.adrelid) INTO v_default
          FROM pg_attribute a
          JOIN pg_attrdef ad ON ad.adrelid = a.attrelid AND ad.adnum = a.attnum
         WHERE a.attrelid = format('app_user.%I', t.tbl)::regclass
           AND a.attname::text = t.col
           AND a.attnum > 0
           AND NOT a.attisdropped;
      EXCEPTION WHEN OTHERS THEN
        v_default := NULL;
      END;

      IF v_default LIKE '%nextval%' THEN
        v_seq := substring(v_default from 'nextval\(''([^'']+)''');
      END IF;
    END IF;

    -- 方式三
    IF v_seq IS NULL THEN
      BEGIN
        SELECT d.objid::regclass::text INTO v_seq
          FROM pg_depend d
          JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
          JOIN pg_class sc ON sc.oid = d.objid AND sc.relkind = 'S'
         WHERE d.refobjid = format('app_user.%I', t.tbl)::regclass
           AND a.attname::text = t.col
           AND d.deptype IN ('a', 'i')
         LIMIT 1;
      EXCEPTION WHEN OTHERS THEN
        v_seq := NULL;
      END;
    END IF;

    IF v_seq IS NULL THEN
      RAISE NOTICE '%.% 没有关联序列（应为 MySQL 兼容 auto_increment），跳过校准。本次数据已显式指定主键插入，不影响本脚本；但后续在该表手工新增行仍可能撞主键。',
                   t.tbl, t.col;
      CONTINUE;
    END IF;

    EXECUTE format('SELECT COALESCE(max(%I), 0) FROM app_user.%I', t.col, t.tbl) INTO v_max;
    BEGIN
      PERFORM setval(v_seq::regclass, GREATEST(v_max, 1), true);
      RAISE NOTICE '%.% 自增序列已校准: % (max=%, next=%)', t.tbl, t.col, v_seq, v_max, v_max + 1;
    EXCEPTION WHEN OTHERS THEN
      RAISE NOTICE '%.% 序列校准失败: %，跳过', t.tbl, t.col, SQLERRM;
    END;
  END LOOP;
END $$;

-- 6. 存量数据体检（只提示不改数据）：片区值不在 0/1/2 的记录
--    出现提示说明该行是历史脏数据，前端下拉已由字典接管不会再产生新脏值，
--    存量部分需要人工判定归属后单独 UPDATE。
DO $$
DECLARE
  r      record;
  v_rows bigint := 0;
BEGIN
  FOR r IN
    SELECT district, COUNT(*) AS cnt
      FROM app_user.drainage_waterlogging_point
     WHERE del_flag = '0'
       AND (district IS NULL OR TRIM(district) = '' OR TRIM(district) NOT IN ('0', '1', '2'))
     GROUP BY district
     ORDER BY COUNT(*) DESC
  LOOP
    IF r.district IS NULL OR TRIM(r.district) = '' THEN
      RAISE NOTICE '片区待核对: (空值) —— % 条', r.cnt;
    ELSE
      RAISE NOTICE '片区待核对: % —— % 条', r.district, r.cnt;
    END IF;
    v_rows := v_rows + r.cnt;
  END LOOP;

  IF v_rows = 0 THEN
    RAISE NOTICE '片区值全部合法（0/1/2），无需处理。';
  ELSE
    RAISE NOTICE '共 % 条记录片区值不合法，请人工核对后处理。', v_rows;
  END IF;
END $$;

-- 7. 结果核验（可选，取消注释执行）：应返回 3 行，dict_value 依次 0/1/2
--    SELECT dict_value, dict_label FROM app_user.sys_dict_data
--     WHERE dict_type = 'drainage_district' ORDER BY dict_sort;

COMMIT;