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

/* =====================================================================
   应急与指挥调度模块改造 · 业务编号 / 分级状态 修复脚本
   =====================================================================
   文件：dispatch_refactor_fix_number.sql
   用途：修补 dispatch_refactor_migrate.sql「三、四」两段因方言问题未生效的部分。

   ---------------------------------------------------------------------
   背景：本实例的 KingbaseES 处于 MySQL 兼容语义，`||` 被解析为「逻辑或」
         （boolean OR），不是字符串连接符。实测：
             SELECT 'GJ' || '20261014' || '001';      -- 返回 boolean  t
             SELECT concat('GJ','20261014','001');    -- 返回 GJ20261014001
         于是 migrate 里用 `||` 生成业务编号的语句：
           · dis_event_grade —— 整列被写成字符串 'true'，第 2 行即撞
             uk_dis_event_grade_no，报「重复键违反唯一约束」并整段回滚；
           · dis_plan        —— 同理（uk_dis_plan_no），或在只有 1 行时
             静默写入一行脏值 'true'。

   ---------------------------------------------------------------------
   为什么不能靠「重跑 migrate」修：
     migrate 三段的主表搬迁 INSERT 带 `WHERE NOT EXISTS (... plan_name 相同)`，
     已经插进去的那一行（plan_no = 'true'）会被去重跳过，编号永远停错。
     所以需要本脚本按 id 逐行补号。

   ---------------------------------------------------------------------
   本脚本做什么（全部幂等，可重复执行；不删除任何数据）：
     1) 清脏编号：plan_no / grade_no 中不符合规范的（如 'true'）置为 NULL
     2) 事件分级状态归一：中文 → 0/1，并把 status 列类型从 varchar 改成 int2
     3) 分级的口径映射：旧列 category/level（若还在）→ 新列；中文等级/类别 → 数字
     4) 重新取号：YA+yyyyMMdd+4位序号 / GJ+yyyyMMdd+3位序号（按 id 顺序）
     5) dis_plan_version.plan_no 与主表对齐
   注意：4) 已统一改用 concat()，不再使用 `||`。

   执行前提：dispatch_refactor.sql 已执行（新表 + 唯一索引 + 字典已就绪）。
   执行方式：本脚本只有一个 BEGIN; ... COMMIT;，可整段选中运行。
   ===================================================================== */

BEGIN;

/* ---------------------------------------------------------------------
   1) 清脏编号：非规范编号一律置 NULL，交给第 4 步重新生成
      YA 规范：YA + 8 位日期 + 4 位序号 = YA + 12 位数字
      GJ 规范：GJ + 8 位日期 + 3 位序号 = GJ + 11 位数字
   --------------------------------------------------------------------- */
UPDATE app_user.dis_plan
   SET plan_no = NULL
 WHERE plan_no IS NOT NULL
   AND plan_no !~ '^YA[0-9]{12}$';

UPDATE app_user.dis_event_grade
   SET grade_no = NULL
 WHERE grade_no IS NOT NULL
   AND grade_no !~ '^GJ[0-9]{11}$';

/* ---------------------------------------------------------------------
   2) 事件分级状态归一：启用/停用 → 0/1，并把列类型改成 int2
      （migrate 四段整体回滚，所以 status 现在很可能还是 varchar + 中文；
        后端按 Integer 处理，WHERE status = 2 打在 varchar 列上会直接报类型错）
   --------------------------------------------------------------------- */
UPDATE app_user.dis_event_grade SET status = '0' WHERE status::text IN ('启用', '正常');
UPDATE app_user.dis_event_grade SET status = '1' WHERE status::text IN ('停用', '禁用');
UPDATE app_user.dis_event_grade SET status = '0' WHERE status::text !~ '^[0-9]+$';

DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.columns
                WHERE table_schema = 'app_user' AND table_name = 'dis_event_grade'
                  AND column_name = 'status' AND data_type <> 'smallint') THEN
        EXECUTE 'ALTER TABLE app_user.dis_event_grade ALTER COLUMN status TYPE int2 USING status::int2';
    END IF;
END $$;

/* ---------------------------------------------------------------------
   3) 分级口径映射（旧列若已被 drop 删除，本段自动跳过，是安全的空操作）
   --------------------------------------------------------------------- */
DO $$
BEGIN
    -- 3.1 旧列 category / level → 新列（必须早于下面的口径映射）
    IF EXISTS (SELECT 1 FROM information_schema.columns
                WHERE table_schema = 'app_user' AND table_name = 'dis_event_grade' AND column_name = 'category') THEN
        EXECUTE 'UPDATE app_user.dis_event_grade SET event_category = category WHERE event_category IS NULL AND category IS NOT NULL';
    END IF;
    IF EXISTS (SELECT 1 FROM information_schema.columns
                WHERE table_schema = 'app_user' AND table_name = 'dis_event_grade' AND column_name = 'level') THEN
        EXECUTE 'UPDATE app_user.dis_event_grade SET event_level = "level" WHERE event_level IS NULL AND "level" IS NOT NULL';
    END IF;
END $$;

-- 3.2 等级口径：旧 4 级 → 新 3 级
UPDATE app_user.dis_event_grade SET event_level = '1'
 WHERE event_level IN ('一级', 'Ⅰ级', '特别重大', '重大', '紧急');
UPDATE app_user.dis_event_grade SET event_level = '2'
 WHERE event_level IN ('二级', 'Ⅱ级', '较大');
UPDATE app_user.dis_event_grade SET event_level = '3'
 WHERE event_level IN ('三级', '四级', 'Ⅲ级', 'Ⅳ级', '一般');

-- 3.3 类别口径
UPDATE app_user.dis_event_grade SET event_category = '1' WHERE event_category IN ('管网泄漏', '管道泄漏');
UPDATE app_user.dis_event_grade SET event_category = '2' WHERE event_category IN ('路面积水', '积水');
UPDATE app_user.dis_event_grade SET event_category = '3' WHERE event_category IN ('设施损坏', '塌陷');
UPDATE app_user.dis_event_grade SET event_category = '4' WHERE event_category IN ('管道堵塞', '堵塞');

/* ---------------------------------------------------------------------
   4) 重新取号
      说明：子查询读的是语句开始时的快照，row_number() 按 id 排序；
            NOT EXISTS 逐个比对，运行期已占用的编号直接跳过（该行保持
            NULL，由页面人工补号），确保不会撞 uk_dis_plan_no /
            uk_dis_event_grade_no。
            CURRENT_TIMESTAMP 在同一事务内是常量，同批日期段一致。
   --------------------------------------------------------------------- */

-- 4.1 dis_plan：YA + yyyyMMdd + 4 位序号
UPDATE app_user.dis_plan g
   SET plan_no = c.new_no
  FROM (SELECT t.id,
               concat('YA', to_char(CURRENT_TIMESTAMP, 'YYYYMMDD'),
                      lpad(CAST(t.rn AS varchar), 4, '0')) AS new_no
          FROM (SELECT id, row_number() OVER (ORDER BY id) AS rn
                  FROM app_user.dis_plan
                 WHERE plan_no IS NULL) t) c
 WHERE g.id = c.id
   AND NOT EXISTS (SELECT 1 FROM app_user.dis_plan x WHERE x.plan_no = c.new_no);

-- 4.2 dis_event_grade：GJ + yyyyMMdd + 3 位序号
UPDATE app_user.dis_event_grade g
   SET grade_no = c.new_no
  FROM (SELECT t.id,
               concat('GJ', to_char(CURRENT_TIMESTAMP, 'YYYYMMDD'),
                      lpad(CAST(t.rn AS varchar), 3, '0')) AS new_no
          FROM (SELECT id, row_number() OVER (ORDER BY id) AS rn
                  FROM app_user.dis_event_grade
                 WHERE grade_no IS NULL) t) c
 WHERE g.id = c.id
   AND NOT EXISTS (SELECT 1 FROM app_user.dis_event_grade x WHERE x.grade_no = c.new_no);

/* ---------------------------------------------------------------------
   5) 版本快照的编号与主表对齐
      只处理主表已有编号的版本行；重复执行结果一致（幂等）。
   --------------------------------------------------------------------- */
UPDATE app_user.dis_plan_version v
   SET plan_no = d.plan_no
  FROM app_user.dis_plan d
 WHERE v.plan_id = d.id
   AND d.plan_no IS NOT NULL
   AND (v.plan_no IS NULL OR v.plan_no <> d.plan_no);

COMMIT;

/* =====================================================================
   6) 结果自检（只读）—— 请核对：
      · dis_event_grade：status 只应是 0/1；grade_no 形如 GJ20261014001
      · dis_plan        ：plan_no   形如 YA202610140001
      · 都不应出现 'true'
   ===================================================================== */
SELECT id, grade_no, event_category, event_level, status
  FROM app_user.dis_event_grade ORDER BY id;

SELECT id, plan_no, plan_name, status
  FROM app_user.dis_plan ORDER BY id;

/* 若下面这条返回 0 行，说明编号已全部规范 */
SELECT 'dis_plan' AS tbl, count(*) AS bad FROM app_user.dis_plan
 WHERE plan_no IS NOT NULL AND plan_no !~ '^YA[0-9]{12}$'
UNION ALL
SELECT 'dis_event_grade', count(*) FROM app_user.dis_event_grade
 WHERE grade_no IS NOT NULL AND grade_no !~ '^GJ[0-9]{11}$';