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

-- 排水分区「积水等级」字典（KingbaseES）
-- 用途：排水分区基础数据管理（psgw_drainage_partition.level）的积水等级改为字典驱动，
--      下拉框、列表标签配色、详情展示统一读取 sys_dict_data。
-- 口径：与易积水点（WaterloggingPoint.waterlogging_level）保持一致
--      0-未积水 / 1-轻度积水 / 2-中度积水 / 3-严重积水。
-- 说明：只维护字典定义，不改表结构、不改业务数据；脚本幂等，可重复执行。
--
-- =====================================================================
-- 为什么本脚本要「显式指定主键」
-- ---------------------------------------------------------------------
-- 本库由 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（步骤 3），让后续新增不受影响；
--      若确实没有关联序列（纯 auto_increment），步骤 3 只提示、不报错。
-- =====================================================================
--
-- 前置（必须先执行）：结束上一次失败执行遗留的 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_waterlogging_level', '0', 'admin', CURRENT_TIMESTAMP,
       '积水等级：0未积水 1轻度积水 2中度积水 3严重积水'
 WHERE NOT EXISTS (SELECT 1 FROM app_user.sys_dict_type WHERE dict_type = 'drainage_waterlogging_level');

UPDATE app_user.sys_dict_type
   SET dict_name = '积水等级',
       status = '0',
       update_by = 'admin',
       update_time = CURRENT_TIMESTAMP,
       remark = '积水等级：0未积水 1轻度积水 2中度积水 3严重积水'
 WHERE dict_type = 'drainage_waterlogging_level';

-- 2. 字典数据（幂等重写；dict_code 显式取 max+序号；list_class 决定列表标签配色）
DELETE FROM app_user.sys_dict_data WHERE dict_type = 'drainage_waterlogging_level';

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_waterlogging_level',
       '', v.list_class, v.is_default, '0', 'admin', CURRENT_TIMESTAMP, v.remark
  FROM (VALUES
        (1, 1, '未积水',   '0', 'success', 'Y', '积水等级：未积水'),
        (2, 2, '轻度积水', '1', 'primary', 'N', '积水等级：轻度积水'),
        (3, 3, '中度积水', '2', 'warning', 'N', '积水等级：中度积水'),
        (4, 4, '严重积水', '3', 'danger',  'N', '积水等级：严重积水')
       ) AS v(rn, dict_sort, dict_label, dict_value, list_class, is_default, remark)
 ORDER BY v.rn;

-- 3. 自增计数器校准（放在插入之后：此时 max 已含新插入的 4 行）
--    三种方式依次尝试，任一成功即可；都不成功只提示，不影响本脚本已插入的数据。
--      方式一 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 $$;

-- 4. 存量数据体检（只查不改）：level 为空或不在 0-3 的记录
--    SELECT id, name, "level" FROM app_user.psgw_drainage_partition
--     WHERE "level" IS NULL OR "level" NOT IN (0, 1, 2, 3);

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

COMMIT;