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

-- =====================================================================
-- 应急与指挥调度模块（dispatch）改造脚本 —— 建表 / 加列 / 字典 seed
-- 数据库：KingbaseES（PG 兼容模式），业务 schema = app_user
--
-- 目的：
--   1) 事件域由「按操作拆 9 张表」收敛为 dis_event（主表）+ dis_event_log（日志表）
--   2) 预案域由 dis_event_plan + dis_plan_approval 两套收敛为
--      dis_plan + dis_plan_version + dis_plan_log
--   3) dis_event_grade 字段字典化并补业务编号列
--   4) 事件来源/渠道/管网类型/类别/等级/区域、预案类型/等级等全部落 sys_dict_data
--
-- 设计口径（2026-10-14 与业务确认）：
--   · 事件等级：3 级 —— Ⅰ级(紧急) / Ⅱ级(较大) / Ⅲ级(一般)（原型口径）
--   · 事件类别、事件等级分别使用 dis_event_category / dis_event_level
--   · 事件状态：5 态 —— 0 待核实 / 1 已核实 / 2 处置中 / 3 已解除 / 4 不实事件
--     存量「待受理」归 0、「已关闭」归 3
--
-- 幂等性：本脚本可重复执行。
--   建表用 CREATE TABLE IF NOT EXISTS；加列用 ADD COLUMN IF NOT EXISTS；
--   字典先 DELETE 同 dict_type 再 INSERT。
--
--    本版同时完成 dis_plan_library 的字段收敛：将类型/等级统一为字典编码，
--    并删除已废弃的 event_region / topic / plan_summary 列。
-- =====================================================================

BEGIN;

-- =====================================================================
-- 一、事件域
-- =====================================================================

-- ---------------------------------------------------------------------
-- 1.1 dis_event 事件主表（全生命周期当前值）
--     表若已存在（旧版），下列 ADD COLUMN 只会补齐缺失列；
--     旧列 source / category / level 的搬迁见第四节「存量数据迁移」。
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS app_user.dis_event (
    id               int8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    event_no         varchar(32),
    description      text,
    event_source     varchar(20),
    channel          varchar(20),
    location         varchar(255),
    longitude        numeric(12,6),
    latitude         numeric(12,6),
    happen_time      timestamp,
    reporter_phone   varchar(20),
    event_category   varchar(20),
    event_level      varchar(20),
    status           int2 NOT NULL DEFAULT 0,
    verify_result    int2,
    verifier         varchar(64),
    verify_time      timestamp,
    verify_opinion   varchar(500),
    handler          varchar(64),
    handler_phone    varchar(20),
    department       varchar(128),
    dispose_time     timestamp,
    release_time     timestamp,
    release_reason   varchar(20),
    release_note     varchar(500),
    last_visit_time  timestamp,
    next_visit_time  timestamp,
    need_upgrade     int2,
    create_user_id   int8,
    create_time      timestamp DEFAULT CURRENT_TIMESTAMP,
    update_user_id   int8,
    update_time      timestamp DEFAULT CURRENT_TIMESTAMP,
    del_flag         char(1) DEFAULT '0'
);

ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS event_no        varchar(32);
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS description     text;
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS event_source    varchar(20);
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS channel         varchar(20);
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS location        varchar(255);
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS longitude       numeric(12,6);
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS latitude        numeric(12,6);
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS happen_time     timestamp;
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS reporter_phone  varchar(20);
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS event_category  varchar(20);
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS event_level     varchar(20);
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS status          int2 DEFAULT 0;
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS verify_result   int2;
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS verifier        varchar(64);
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS verify_time     timestamp;
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS verify_opinion  varchar(500);
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS handler         varchar(64);
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS handler_phone   varchar(20);
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS department      varchar(128);
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS dispose_time    timestamp;
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS release_time    timestamp;
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS release_reason  varchar(20);
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS release_note    varchar(500);
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS last_visit_time timestamp;
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS next_visit_time timestamp;
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS need_upgrade    int2;
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 create_time     timestamp DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS update_user_id  int8;
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS update_time     timestamp DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS del_flag        char(1) DEFAULT '0';

COMMENT ON TABLE  app_user.dis_event IS '应急事件主表：一个事件一行，承载全生命周期当前值；过程留痕见 dis_event_log';
COMMENT ON COLUMN app_user.dis_event.id              IS '主键ID，int8 自增';
COMMENT ON COLUMN app_user.dis_event.event_no        IS '事件编号（业务序号：SJ+yyyyMMddHHmmss+4位随机，后端保存前生成，普通更新不可修改）';
COMMENT ON COLUMN app_user.dis_event.description     IS '事件描述';
COMMENT ON COLUMN app_user.dis_event.event_source    IS '事件来源：字典 dis_event_source';
COMMENT ON COLUMN app_user.dis_event.channel         IS '来源渠道：字典 dis_event_channel';
COMMENT ON COLUMN app_user.dis_event.location        IS '发生地点';
COMMENT ON COLUMN app_user.dis_event.longitude       IS '经度（发生地点地理编码得到）';
COMMENT ON COLUMN app_user.dis_event.latitude        IS '纬度（发生地点地理编码得到）';
COMMENT ON COLUMN app_user.dis_event.happen_time     IS '发生时间';
COMMENT ON COLUMN app_user.dis_event.reporter_phone  IS '上报人联系电话';
COMMENT ON COLUMN app_user.dis_event.event_category  IS '事件类别：字典 dis_event_category（管道泄漏/路面积水/设施损坏/管道堵塞）';
COMMENT ON COLUMN app_user.dis_event.event_level     IS '事件等级：字典 dis_event_level（1-Ⅰ级紧急 2-Ⅱ级较大 3-Ⅲ级一般）';
COMMENT ON COLUMN app_user.dis_event.status          IS '事件状态：EventStatusEnum —— 0-待核实 1-已核实 2-处置中 3-已解除 4-不实事件；终态为 3、4';
COMMENT ON COLUMN app_user.dis_event.verify_result   IS '核实结论：0-真实 1-不实；仅 status in (1,4) 时有值';
COMMENT ON COLUMN app_user.dis_event.verifier        IS '核验人';
COMMENT ON COLUMN app_user.dis_event.verify_time     IS '核实时间';
COMMENT ON COLUMN app_user.dis_event.verify_opinion  IS '核实意见；判定为不实时填核销原因（字典 dis_event_false_reason）';
COMMENT ON COLUMN app_user.dis_event.handler         IS '责任处置人（后续工作催办的催办对象）';
COMMENT ON COLUMN app_user.dis_event.handler_phone   IS '责任处置人联系电话';
COMMENT ON COLUMN app_user.dis_event.department      IS '责任单位 / 转接接收部门';
COMMENT ON COLUMN app_user.dis_event.dispose_time    IS '开始处置时间（status 1→2 时写入）';
COMMENT ON COLUMN app_user.dis_event.release_time    IS '解除时间（status 2→3 时写入）';
COMMENT ON COLUMN app_user.dis_event.release_reason  IS '解除原因：字典 dis_event_release_reason';
COMMENT ON COLUMN app_user.dis_event.release_note    IS '解除说明 / 处置结果补充';
COMMENT ON COLUMN app_user.dis_event.last_visit_time IS '最近回访时间（取最近一次处置跟踪的跟踪时间）';
COMMENT ON COLUMN app_user.dis_event.next_visit_time IS '下次回访时间（取最近一次处置跟踪填写的值）';
COMMENT ON COLUMN app_user.dis_event.need_upgrade    IS '是否需要升级：字典 dis_yes_no（0-否 1-是），取最近一次处置跟踪填写的值';
COMMENT ON COLUMN app_user.dis_event.del_flag        IS '删除标志（0=正常 2=删除）';

ALTER TABLE app_user.dis_event
    DROP COLUMN IF EXISTS reporter,
    DROP COLUMN IF EXISTS reporter_id,
    DROP COLUMN IF EXISTS report_time,
    DROP COLUMN IF EXISTS create_by,
    DROP COLUMN IF EXISTS update_by,
    DROP COLUMN IF EXISTS loss_amount,
    DROP COLUMN IF EXISTS plan_id,
    DROP COLUMN IF EXISTS event_type,
    DROP COLUMN IF EXISTS event_region,
    DROP COLUMN IF EXISTS before_url,
    DROP COLUMN IF EXISTS after_url;

CREATE UNIQUE INDEX IF NOT EXISTS uk_dis_event_no       ON app_user.dis_event(event_no);
CREATE INDEX IF NOT EXISTS idx_dis_event_status         ON app_user.dis_event(status);
CREATE INDEX IF NOT EXISTS idx_dis_event_create_time    ON app_user.dis_event(create_time DESC);
CREATE INDEX IF NOT EXISTS idx_dis_event_source         ON app_user.dis_event(event_source);
CREATE INDEX IF NOT EXISTS idx_dis_event_level          ON app_user.dis_event(event_level);

-- ---------------------------------------------------------------------
-- 1.2 dis_event_log 事件日志表
--     一张表承载全部环节留痕：接报登记 / 初判核实 / 不实核销 / 电话转接 /
--     上报报送 / 开始处置 / 处置跟踪 / 事件解除 / 工作催办 / 信息修改。
--     替代旧 dis_event_transfer_log + dis_event_track_log + dis_event_urge_detail。
--     日志完全由业务代码写入，不使用触发器/自定义函数。
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS app_user.dis_event_log (
    id              int8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    event_id        int8 NOT NULL,
    event_no        varchar(32),
    oper_type       int2 NOT NULL CHECK (oper_type BETWEEN 0 AND 9),
    oper_desc       text,
    status_from     int2,
    status_to       int2,
    targets         varchar(500),
    receiver_dept   varchar(128),
    receiver_phone  varchar(20),
    method          varchar(20),
    urge_result     varchar(20),
    trend           varchar(20),
    need_upgrade    int2,
    next_visit_time timestamp,
    attach_url      text,
    oper_user_id    int8,
    oper_user_name  varchar(128),
    oper_time       timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE app_user.dis_event_log ADD COLUMN IF NOT EXISTS event_no        varchar(32);
ALTER TABLE app_user.dis_event_log ADD COLUMN IF NOT EXISTS oper_desc       text;
ALTER TABLE app_user.dis_event_log ADD COLUMN IF NOT EXISTS status_from     int2;
ALTER TABLE app_user.dis_event_log ADD COLUMN IF NOT EXISTS status_to       int2;
ALTER TABLE app_user.dis_event_log ADD COLUMN IF NOT EXISTS targets         varchar(500);
ALTER TABLE app_user.dis_event_log ADD COLUMN IF NOT EXISTS receiver_dept   varchar(128);
ALTER TABLE app_user.dis_event_log ADD COLUMN IF NOT EXISTS receiver_phone  varchar(20);
ALTER TABLE app_user.dis_event_log ADD COLUMN IF NOT EXISTS method          varchar(20);
ALTER TABLE app_user.dis_event_log ADD COLUMN IF NOT EXISTS urge_result     varchar(20);
ALTER TABLE app_user.dis_event_log ADD COLUMN IF NOT EXISTS trend           varchar(20);
ALTER TABLE app_user.dis_event_log ADD COLUMN IF NOT EXISTS need_upgrade    int2;
ALTER TABLE app_user.dis_event_log ADD COLUMN IF NOT EXISTS next_visit_time timestamp;
ALTER TABLE app_user.dis_event_log ADD COLUMN IF NOT EXISTS attach_url      text;
ALTER TABLE app_user.dis_event_log ADD COLUMN IF NOT EXISTS oper_user_id    int8;
ALTER TABLE app_user.dis_event_log ADD COLUMN IF NOT EXISTS oper_user_name  varchar(128);
ALTER TABLE app_user.dis_event_log ADD COLUMN IF NOT EXISTS oper_time       timestamp DEFAULT CURRENT_TIMESTAMP;

COMMENT ON TABLE  app_user.dis_event_log IS '应急事件操作日志：一个事件 N 行，记录全部环节动作与状态变更轨迹；仅由业务代码写入';
COMMENT ON COLUMN app_user.dis_event_log.event_id        IS '关联 dis_event.id';
COMMENT ON COLUMN app_user.dis_event_log.event_no        IS '事件编号（冗余，便于按编号单独查日志）';
COMMENT ON COLUMN app_user.dis_event_log.oper_type       IS 'EventLogOperTypeEnum：0-接报登记 1-初判核实 2-不实核销 3-电话转接 4-上报报送 5-开始处置 6-处置跟踪 7-事件解除 8-工作催办 9-信息修改';
COMMENT ON COLUMN app_user.dis_event_log.oper_desc       IS '操作说明/备注：跟踪内容、催办内容、核实意见、解除说明等';
COMMENT ON COLUMN app_user.dis_event_log.status_from     IS '变更前事件状态；不改状态的动作为 NULL';
COMMENT ON COLUMN app_user.dis_event_log.status_to       IS '变更后事件状态；不改状态的动作为 NULL';
COMMENT ON COLUMN app_user.dis_event_log.targets         IS '操作对象：转接/报送部门名称（多个逗号分隔）；工作催办时填目标接收人';
COMMENT ON COLUMN app_user.dis_event_log.receiver_dept   IS '催办接收部门（oper_type=8）';
COMMENT ON COLUMN app_user.dis_event_log.receiver_phone  IS '催办接收人电话（oper_type=8）';
COMMENT ON COLUMN app_user.dis_event_log.method          IS '催办方式：字典 dis_event_urge_method（oper_type=8）';
COMMENT ON COLUMN app_user.dis_event_log.urge_result     IS '催办结果：字典 dis_event_urge_result（oper_type=8）';
COMMENT ON COLUMN app_user.dis_event_log.trend           IS '态势评估：字典 dis_event_trend（oper_type=6 处置跟踪）';
COMMENT ON COLUMN app_user.dis_event_log.need_upgrade    IS '是否需要升级：字典 dis_yes_no（oper_type=6 处置跟踪）';
COMMENT ON COLUMN app_user.dis_event_log.next_visit_time IS '下次回访时间（oper_type=6 处置跟踪）';
COMMENT ON COLUMN app_user.dis_event_log.attach_url      IS '附件URL，多个使用逗号分隔';
COMMENT ON COLUMN app_user.dis_event_log.oper_user_id    IS '操作人ID，系统操作可为空';
COMMENT ON COLUMN app_user.dis_event_log.oper_user_name  IS '操作人昵称(sys_user.nick_name)；自动操作使用系统名称';
COMMENT ON COLUMN app_user.dis_event_log.oper_time       IS '操作时间';

CREATE INDEX IF NOT EXISTS idx_dis_event_log_event ON app_user.dis_event_log(event_id, oper_time, id);
CREATE INDEX IF NOT EXISTS idx_dis_event_log_type  ON app_user.dis_event_log(oper_type);
CREATE INDEX IF NOT EXISTS idx_dis_event_log_no    ON app_user.dis_event_log(event_no);

-- =====================================================================
-- 二、预案域（编辑与审批合并为同一套数据）
-- =====================================================================

-- ---------------------------------------------------------------------
-- 2.1 dis_plan 预案主表
-- ---------------------------------------------------------------------
CREATE SEQUENCE IF NOT EXISTS app_user.dis_plan_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE IF NOT EXISTS app_user.dis_plan (
    id             int8 NOT NULL DEFAULT nextval('app_user.dis_plan_id_seq'::regclass) PRIMARY KEY,
    plan_no        varchar(32),
    plan_name      varchar(200) NOT NULL,
    plan_type      varchar(20),
    plan_level     varchar(20),
    compile_unit   varchar(128),
    publish_unit   varchar(128),
    publish_date   varchar(20),
    is_template    int2 NOT NULL DEFAULT 0,
    version        varchar(20),
    content        text,
    approve_result varchar(20),
    approve_time   timestamp,
    approve_note   varchar(500),
    create_by      varchar(64),
    create_time    timestamp DEFAULT CURRENT_TIMESTAMP,
    update_by      varchar(64),
    update_time    timestamp DEFAULT CURRENT_TIMESTAMP,
    del_flag       char(1) DEFAULT '0'
);

-- 不在此处执行 OWNED BY：存量 Kingbase 表的 id 可能仍是 Identity，
-- Identity 列不能直接重新绑定序列。存量库请执行对应的序列迁移脚本
--（例如 dispatch_plan_version_id_sequence.sql）后再绑定所有权。

ALTER TABLE app_user.dis_plan ADD COLUMN IF NOT EXISTS plan_no        varchar(32);
ALTER TABLE app_user.dis_plan ADD COLUMN IF NOT EXISTS plan_type      varchar(20);
ALTER TABLE app_user.dis_plan ADD COLUMN IF NOT EXISTS plan_level     varchar(20);
ALTER TABLE app_user.dis_plan ADD COLUMN IF NOT EXISTS compile_unit   varchar(128);
ALTER TABLE app_user.dis_plan ADD COLUMN IF NOT EXISTS publish_unit   varchar(128);
ALTER TABLE app_user.dis_plan ADD COLUMN IF NOT EXISTS publish_date   varchar(20);
ALTER TABLE app_user.dis_plan ADD COLUMN IF NOT EXISTS is_template    int2 DEFAULT 0;
ALTER TABLE app_user.dis_plan ADD COLUMN IF NOT EXISTS status         int2 DEFAULT 0;
ALTER TABLE app_user.dis_plan ADD COLUMN IF NOT EXISTS version        varchar(20);
ALTER TABLE app_user.dis_plan ADD COLUMN IF NOT EXISTS content        text;
ALTER TABLE app_user.dis_plan ADD COLUMN IF NOT EXISTS approve_result varchar(20);
ALTER TABLE app_user.dis_plan ADD COLUMN IF NOT EXISTS approve_time   timestamp;
ALTER TABLE app_user.dis_plan ADD COLUMN IF NOT EXISTS approve_note   varchar(500);
ALTER TABLE app_user.dis_plan ADD COLUMN IF NOT EXISTS create_by      varchar(64);
ALTER TABLE app_user.dis_plan ADD COLUMN IF NOT EXISTS create_time    timestamp DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE app_user.dis_plan ADD COLUMN IF NOT EXISTS update_by      varchar(64);
ALTER TABLE app_user.dis_plan ADD COLUMN IF NOT EXISTS update_time    timestamp DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE app_user.dis_plan ADD COLUMN IF NOT EXISTS del_flag       char(1) DEFAULT '0';

-- 正文富文本承载图片附件，移除历史独立附件字段。
ALTER TABLE app_user.dis_plan
    DROP COLUMN IF EXISTS attach_name,
    DROP COLUMN IF EXISTS attach_path;

COMMENT ON TABLE  app_user.dis_plan IS '应急预案主表：预案编辑与预案审批共用同一套数据，状态流转 草稿→待审核→已通过/已驳回';
COMMENT ON COLUMN app_user.dis_plan.plan_no        IS '预案编号（业务序号：YA+yyyyMMdd+4位随机）';
COMMENT ON COLUMN app_user.dis_plan.plan_name      IS '预案名称';
COMMENT ON COLUMN app_user.dis_plan.plan_type      IS '预案类型：字典 dis_plan_type（综合/专项/现场处置）';
COMMENT ON COLUMN app_user.dis_plan.plan_level     IS '预案等级：字典 dis_plan_level（Ⅰ级/Ⅱ级/Ⅲ级）';
COMMENT ON COLUMN app_user.dis_plan.compile_unit   IS '编制单位';
COMMENT ON COLUMN app_user.dis_plan.publish_unit   IS '发布单位';
COMMENT ON COLUMN app_user.dis_plan.publish_date   IS '发布日期';
COMMENT ON COLUMN app_user.dis_plan.is_template    IS '是否预案模板：0-否 1-是（新建预案时可套用模板的章节结构）';
COMMENT ON COLUMN app_user.dis_plan.status         IS 'PlanStatusEnum：0-草稿 1-待审核 2-已通过 3-已驳回';
COMMENT ON COLUMN app_user.dis_plan.version        IS '当前生效版本号；尚未审批通过时为空';
COMMENT ON COLUMN app_user.dis_plan.content        IS '当前正文（富文本 HTML）；生效正文以版本表快照为准';
COMMENT ON COLUMN app_user.dis_plan.approve_result IS '审批结果：通过 / 不通过';
COMMENT ON COLUMN app_user.dis_plan.approve_time   IS '审批完成时间';
COMMENT ON COLUMN app_user.dis_plan.approve_note   IS '审批意见；不通过时为驳回原因（必填）';

CREATE UNIQUE INDEX IF NOT EXISTS uk_dis_plan_no     ON app_user.dis_plan(plan_no);
CREATE INDEX IF NOT EXISTS idx_dis_plan_status       ON app_user.dis_plan(status);
CREATE INDEX IF NOT EXISTS idx_dis_plan_template     ON app_user.dis_plan(is_template);

-- ---------------------------------------------------------------------
-- 2.2 dis_plan_version 预案版本快照表
-- ---------------------------------------------------------------------
CREATE SEQUENCE IF NOT EXISTS app_user.dis_plan_version_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE IF NOT EXISTS app_user.dis_plan_version (
    id          int8 NOT NULL DEFAULT nextval('app_user.dis_plan_version_id_seq'::regclass) PRIMARY KEY,
    plan_id     int8 NOT NULL,
    plan_no     varchar(32),
    version     varchar(20) NOT NULL,
    content     text,
    change_note varchar(500),
    editor      varchar(64),
    edit_time   timestamp,
    ver_status  int2 NOT NULL DEFAULT 0
);

ALTER TABLE app_user.dis_plan_version ADD COLUMN IF NOT EXISTS plan_no     varchar(32);
ALTER TABLE app_user.dis_plan_version ADD COLUMN IF NOT EXISTS content     text;
ALTER TABLE app_user.dis_plan_version ADD COLUMN IF NOT EXISTS change_note varchar(500);
ALTER TABLE app_user.dis_plan_version ADD COLUMN IF NOT EXISTS editor      varchar(64);
ALTER TABLE app_user.dis_plan_version ADD COLUMN IF NOT EXISTS edit_time   timestamp;
ALTER TABLE app_user.dis_plan_version ADD COLUMN IF NOT EXISTS ver_status  int2 DEFAULT 0;

ALTER TABLE app_user.dis_plan_version
    DROP COLUMN IF EXISTS attach_name,
    DROP COLUMN IF EXISTS attach_path;

COMMENT ON TABLE  app_user.dis_plan_version IS '应急预案版本快照：审核通过后的正文落地为版本；修订已通过预案先生成「审核中」候选版本，旧版本仍生效';
COMMENT ON COLUMN app_user.dis_plan_version.plan_id     IS '关联 dis_plan.id';
COMMENT ON COLUMN app_user.dis_plan_version.version     IS '版本号，如 v3.1';
COMMENT ON COLUMN app_user.dis_plan_version.content     IS '该版本正文快照（富文本 HTML）';
COMMENT ON COLUMN app_user.dis_plan_version.change_note IS '变更说明；修订已通过预案时必填';
COMMENT ON COLUMN app_user.dis_plan_version.editor      IS '修订人';
COMMENT ON COLUMN app_user.dis_plan_version.edit_time   IS '修订时间';
COMMENT ON COLUMN app_user.dis_plan_version.ver_status  IS 'PlanVersionStatusEnum：0-历史 1-当前生效 2-审核中 3-已驳回';

CREATE INDEX IF NOT EXISTS idx_dis_plan_version_plan ON app_user.dis_plan_version(plan_id, ver_status);
CREATE UNIQUE INDEX IF NOT EXISTS uk_dis_plan_version ON app_user.dis_plan_version(plan_id, version);

-- ---------------------------------------------------------------------
-- 2.3 dis_plan_log 预案操作日志表
--     替代旧 dis_plan_approval + dis_plan_approval_step（原型审批为单环节）
-- ---------------------------------------------------------------------
CREATE SEQUENCE IF NOT EXISTS app_user.dis_plan_log_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE IF NOT EXISTS app_user.dis_plan_log (
    id             int8 NOT NULL DEFAULT nextval('app_user.dis_plan_log_id_seq'::regclass) PRIMARY KEY,
    plan_id        int8 NOT NULL,
    plan_no        varchar(32),
    oper_type      int2 NOT NULL CHECK (oper_type BETWEEN 0 AND 4),
    oper_desc      text,
    version        varchar(20),
    status_from    int2,
    status_to      int2,
    oper_user_id   int8,
    oper_user_name varchar(128),
    oper_time      timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE app_user.dis_plan_log ADD COLUMN IF NOT EXISTS plan_no        varchar(32);
ALTER TABLE app_user.dis_plan_log ADD COLUMN IF NOT EXISTS oper_desc      text;
ALTER TABLE app_user.dis_plan_log ADD COLUMN IF NOT EXISTS version        varchar(20);
ALTER TABLE app_user.dis_plan_log ADD COLUMN IF NOT EXISTS status_from    int2;
ALTER TABLE app_user.dis_plan_log ADD COLUMN IF NOT EXISTS status_to      int2;
ALTER TABLE app_user.dis_plan_log ADD COLUMN IF NOT EXISTS oper_user_id   int8;
ALTER TABLE app_user.dis_plan_log ADD COLUMN IF NOT EXISTS oper_user_name varchar(128);
ALTER TABLE app_user.dis_plan_log ADD COLUMN IF NOT EXISTS oper_time      timestamp DEFAULT CURRENT_TIMESTAMP;

COMMENT ON TABLE  app_user.dis_plan_log IS '应急预案操作日志：新建/提交审核/审批通过/审批驳回/修订全部留痕';
COMMENT ON COLUMN app_user.dis_plan_log.oper_type  IS 'PlanLogOperTypeEnum：0-新建 1-提交审核 2-审批通过 3-审批驳回 4-修订';
COMMENT ON COLUMN app_user.dis_plan_log.oper_desc  IS '操作说明/审批意见';
COMMENT ON COLUMN app_user.dis_plan_log.version    IS '本次操作涉及的版本号';
COMMENT ON COLUMN app_user.dis_plan_log.status_from IS '变更前预案状态';
COMMENT ON COLUMN app_user.dis_plan_log.status_to   IS '变更后预案状态';

CREATE INDEX IF NOT EXISTS idx_dis_plan_log_plan ON app_user.dis_plan_log(plan_id, oper_time, id);

-- ---------------------------------------------------------------------
-- 2.4 dis_plan_library 数字预案库字段收敛
--     event_type / event_level 与 dis_plan 使用同一套字典编码；
--     event_region、topic、plan_summary 已不再参与模板匹配，直接移除。
--     创建人统一使用 create_user_id，移除 create_by 登录名快照。
-- ---------------------------------------------------------------------
ALTER TABLE app_user.dis_plan_library
    ALTER COLUMN event_type TYPE varchar(20),
    ALTER COLUMN event_level TYPE varchar(20);

-- 将历史上保存的中文标签转换为 dis_plan_type / dis_plan_level 的字典值。
UPDATE app_user.dis_plan_library
   SET event_type = CASE trim(event_type)
       WHEN '综合' THEN '1'
       WHEN '专项' THEN '2'
       WHEN '现场处置' THEN '3'
       WHEN '供水' THEN '2'
       WHEN '燃气' THEN '2'
       WHEN '排水' THEN '2'
       ELSE event_type END,
       event_level = CASE trim(event_level)
       WHEN 'Ⅰ级' THEN '1'
       WHEN 'I级' THEN '1'
       WHEN '1级' THEN '1'
       WHEN 'Ⅱ级' THEN '2'
       WHEN 'II级' THEN '2'
       WHEN '2级' THEN '2'
       WHEN 'Ⅲ级' THEN '3'
       WHEN 'III级' THEN '3'
       WHEN '3级' THEN '3'
       ELSE event_level END;

ALTER TABLE app_user.dis_plan_library
    DROP COLUMN IF EXISTS event_region,
    DROP COLUMN IF EXISTS topic,
    DROP COLUMN IF EXISTS plan_summary,
    DROP COLUMN IF EXISTS create_by;

COMMENT ON COLUMN app_user.dis_plan_library.event_type
    IS '预案类型：字典 dis_plan_type，与 dis_plan.plan_type 使用同一编码';
COMMENT ON COLUMN app_user.dis_plan_library.event_level
    IS '预案等级：字典 dis_plan_level，与 dis_plan.plan_level 使用同一编码';
CREATE INDEX IF NOT EXISTS idx_dis_plan_library_type_level
    ON app_user.dis_plan_library(event_type, event_level);

-- =====================================================================
-- 三、事件分级（保留原表，字段字典化 + 补业务编号）
-- =====================================================================
CREATE SEQUENCE IF NOT EXISTS app_user.dis_event_grade_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE IF NOT EXISTS app_user.dis_event_grade (
    id             int8 NOT NULL DEFAULT nextval('app_user.dis_event_grade_id_seq'::regclass) PRIMARY KEY,
    event_category varchar(20),
    event_level    varchar(20),
    criteria       text,
    response       varchar(500),
    remark         varchar(500),
    create_time    timestamp DEFAULT CURRENT_TIMESTAMP,
    update_time    timestamp DEFAULT CURRENT_TIMESTAMP,
    del_flag       char(1) DEFAULT '0'
);


ALTER TABLE app_user.dis_event_grade ADD COLUMN IF NOT EXISTS event_category varchar(20);
ALTER TABLE app_user.dis_event_grade ADD COLUMN IF NOT EXISTS event_level    varchar(20);
ALTER TABLE app_user.dis_event_grade ADD COLUMN IF NOT EXISTS criteria       text;
ALTER TABLE app_user.dis_event_grade ADD COLUMN IF NOT EXISTS response       varchar(500);
ALTER TABLE app_user.dis_event_grade ADD COLUMN IF NOT EXISTS remark         varchar(500);
ALTER TABLE app_user.dis_event_grade ADD COLUMN IF NOT EXISTS create_time    timestamp DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE app_user.dis_event_grade ADD COLUMN IF NOT EXISTS update_time    timestamp DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE app_user.dis_event_grade ADD COLUMN IF NOT EXISTS del_flag       char(1) DEFAULT '0';

-- 分级标准不再单独保存管网类型，清理存量库中的历史遗留列。
ALTER TABLE app_user.dis_event_grade DROP COLUMN IF EXISTS event_type;
ALTER TABLE app_user.dis_event_grade DROP COLUMN IF EXISTS status;
ALTER TABLE app_user.dis_event_grade DROP COLUMN IF EXISTS create_by;
ALTER TABLE app_user.dis_event_grade DROP COLUMN IF EXISTS update_by;

COMMENT ON TABLE  app_user.dis_event_grade IS '应急事件分级分类标准，同时作为初判核实的辅助核实依据';
COMMENT ON COLUMN app_user.dis_event_grade.event_category IS '事件类别：字典 dis_event_category';
COMMENT ON COLUMN app_user.dis_event_grade.event_level    IS '事件等级：字典 dis_event_level';
ALTER TABLE app_user.dis_event_grade DROP COLUMN IF EXISTS sub_category;
ALTER TABLE app_user.dis_event_grade DROP COLUMN IF EXISTS scenario;
COMMENT ON COLUMN app_user.dis_event_grade.criteria       IS '分级分类判断标准';
COMMENT ON COLUMN app_user.dis_event_grade.response       IS '响应要求，如：30 分钟内启动预案';

CREATE INDEX IF NOT EXISTS idx_dis_event_grade_query    ON app_user.dis_event_grade(event_category, event_level);

-- =====================================================================
-- 四、存量数据迁移 —— 见独立文件 dispatch_refactor_migrate.sql
--     本脚本只负责建表 / 加列 / 字典，可安全用于全新库与既有库。
--     存量数据搬迁依赖旧表旧列（source / level / category / dis_event_report 等），
--     在新库上必然因缺列缺表报错，因此单独拆出，只在有存量数据的环境执行。
-- =====================================================================

-- =====================================================================
-- 五、字典（sys_dict_type / sys_dict_data）
--     编码口径：dict_value 存编码，dict_label 存中文，list_class 配徽标色。
-- =====================================================================

DELETE FROM app_user.sys_dict_data
 WHERE dict_type IN ('dis_event_source','dis_event_channel','dis_event_category',
                     'dis_event_level','dis_event_urge_method','dis_event_urge_result',
                     'dis_event_trend','dis_event_false_reason','dis_event_release_reason',
                     'dis_transfer_dept','dis_report_target',
                     'dis_plan_type','dis_plan_level','dis_yes_no');

DELETE FROM app_user.sys_dict_type
 WHERE dict_type IN ('dis_event_type', 'dis_event_region');

INSERT INTO app_user.sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT v.dict_name, v.dict_type, '0', 'admin', CURRENT_TIMESTAMP, v.remark
  FROM (VALUES
        ('应急事件来源',     'dis_event_source',         '应急事件来源：1巡查上报 2呼叫中心 3视频监控 4上级下达'),
        ('应急事件来源渠道', 'dis_event_channel',        '应急事件来源渠道'),
        ('应急事件类别',     'dis_event_category',       '应急事件类别：故障类别'),
        ('应急事件等级',     'dis_event_level',          '应急事件等级：1Ⅰ级紧急 2Ⅱ级较大 3Ⅲ级一般'),
        ('工作催办方式',     'dis_event_urge_method',    '工作催办方式：1电话 2短信 3系统消息'),
        ('工作催办结果',     'dis_event_urge_result',    '工作催办结果：0未反馈 1已反馈'),
        ('处置态势评估',     'dis_event_trend',          '处置跟踪的态势评估：1趋于平稳 2仍在发展 3明显恶化'),
        ('不实事件核销原因', 'dis_event_false_reason',   '初判核实判定不实时的核销原因'),
        ('事件解除原因',     'dis_event_release_reason', '事件解除原因'),
        ('转接报送部门',     'dis_transfer_dept',        '电话转接 / 上报报送的接收部门'),
        ('上报报送层级',     'dis_report_target',        '上报报送的层级对象'),
        ('应急预案类型',     'dis_plan_type',            '应急预案类型：1综合 2专项 3现场处置'),
        ('应急预案等级',     'dis_plan_level',           '应急预案等级：1Ⅰ级 2Ⅱ级 3Ⅲ级'),
        ('通用是否',         'dis_yes_no',               '通用是否：0否 1是')
       ) AS v(dict_name, dict_type, remark)
 WHERE NOT EXISTS (SELECT 1 FROM app_user.sys_dict_type t WHERE t.dict_type = v.dict_type);

INSERT INTO app_user.sys_dict_data
    (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
VALUES
-- 事件来源
(1, '巡查上报',         '1', 'dis_event_source',  '', 'primary', 'Y', '0', 'admin', CURRENT_TIMESTAMP, '应急事件来源：巡查上报'),
(2, '呼叫中心',         '2', 'dis_event_source',  '', 'info',    'N', '0', 'admin', CURRENT_TIMESTAMP, '应急事件来源：呼叫中心'),
(3, '视频监控',         '3', 'dis_event_source',  '', 'info',    'N', '0', 'admin', CURRENT_TIMESTAMP, '应急事件来源：视频监控'),
(4, '上级下达',         '4', 'dis_event_source',  '', 'warning', 'N', '0', 'admin', CURRENT_TIMESTAMP, '应急事件来源：上级下达'),
-- 来源渠道
(1, '12345市民热线',    '1', 'dis_event_channel', '', '',        'N', '0', 'admin', CURRENT_TIMESTAMP, '来源渠道：12345市民热线'),
(2, '移动终端巡查系统', '2', 'dis_event_channel', '', '',        'N', '0', 'admin', CURRENT_TIMESTAMP, '来源渠道：移动终端巡查系统'),
(3, '省应急管理厅平台', '3', 'dis_event_channel', '', '',        'N', '0', 'admin', CURRENT_TIMESTAMP, '来源渠道：省应急管理厅平台'),
(4, '交警监控共享平台', '4', 'dis_event_channel', '', '',        'N', '0', 'admin', CURRENT_TIMESTAMP, '来源渠道：交警监控共享平台'),
(5, '其他',             '9', 'dis_event_channel', '', '',        'N', '0', 'admin', CURRENT_TIMESTAMP, '来源渠道：其他'),
-- 管网类型
-- 事件类别
(1, '管道泄漏',         '1', 'dis_event_category', '', 'danger',  'N', '0', 'admin', CURRENT_TIMESTAMP, '事件类别：管道泄漏'),
(2, '路面积水',         '2', 'dis_event_category', '', 'warning', 'N', '0', 'admin', CURRENT_TIMESTAMP, '事件类别：路面积水'),
(3, '设施损坏',         '3', 'dis_event_category', '', 'info',    'N', '0', 'admin', CURRENT_TIMESTAMP, '事件类别：设施损坏'),
(4, '管道堵塞',         '4', 'dis_event_category', '', 'info',    'N', '0', 'admin', CURRENT_TIMESTAMP, '事件类别：管道堵塞'),
-- 事件等级（3 级口径）
(1, 'Ⅰ级（紧急）',     '1', 'dis_event_level',   '', 'danger',  'N', '0', 'admin', CURRENT_TIMESTAMP, '事件等级：Ⅰ级紧急'),
(2, 'Ⅱ级（较大）',     '2', 'dis_event_level',   '', 'warning', 'N', '0', 'admin', CURRENT_TIMESTAMP, '事件等级：Ⅱ级较大'),
(3, 'Ⅲ级（一般）',     '3', 'dis_event_level',   '', 'info',    'Y', '0', 'admin', CURRENT_TIMESTAMP, '事件等级：Ⅲ级一般'),
-- 发生区域（按实际片区维护，以下为占位默认值）
-- 催办方式
(1, '电话',             '1', 'dis_event_urge_method', '', '',    'Y', '0', 'admin', CURRENT_TIMESTAMP, '催办方式：电话'),
(2, '短信',             '2', 'dis_event_urge_method', '', '',    'N', '0', 'admin', CURRENT_TIMESTAMP, '催办方式：短信'),
(3, '系统消息',         '3', 'dis_event_urge_method', '', '',    'N', '0', 'admin', CURRENT_TIMESTAMP, '催办方式：系统消息'),
-- 催办结果
(1, '未反馈',           '0', 'dis_event_urge_result', '', 'info',   'Y', '0', 'admin', CURRENT_TIMESTAMP, '催办结果：未反馈'),
(2, '已反馈',           '1', 'dis_event_urge_result', '', 'success','N', '0', 'admin', CURRENT_TIMESTAMP, '催办结果：已反馈'),
-- 态势评估
(1, '趋于平稳',         '1', 'dis_event_trend',   '', 'success', 'Y', '0', 'admin', CURRENT_TIMESTAMP, '态势评估：趋于平稳'),
(2, '仍在发展',         '2', 'dis_event_trend',   '', 'warning', 'N', '0', 'admin', CURRENT_TIMESTAMP, '态势评估：仍在发展'),
(3, '明显恶化',         '3', 'dis_event_trend',   '', 'danger',  'N', '0', 'admin', CURRENT_TIMESTAMP, '态势评估：明显恶化'),
-- 不实核销原因
(1, '误报',             '1', 'dis_event_false_reason', '', 'info', 'Y', '0', 'admin', CURRENT_TIMESTAMP, '核销原因：误报'),
(2, '重复上报',         '2', 'dis_event_false_reason', '', 'info', 'N', '0', 'admin', CURRENT_TIMESTAMP, '核销原因：重复上报'),
(3, '演练测试',         '3', 'dis_event_false_reason', '', 'info', 'N', '0', 'admin', CURRENT_TIMESTAMP, '核销原因：演练测试'),
(4, '其他',             '9', 'dis_event_false_reason', '', '',     'N', '0', 'admin', CURRENT_TIMESTAMP, '核销原因：其他'),
-- 解除原因
(1, '事件已处置完成',   '1', 'dis_event_release_reason', '', 'success', 'Y', '0', 'admin', CURRENT_TIMESTAMP, '解除原因：事件已处置完成'),
(2, '事件不存在',       '2', 'dis_event_release_reason', '', 'info',    'N', '0', 'admin', CURRENT_TIMESTAMP, '解除原因：事件不存在'),
(3, '误报',             '3', 'dis_event_release_reason', '', 'info',    'N', '0', 'admin', CURRENT_TIMESTAMP, '解除原因：误报'),
(4, '其他原因',         '9', 'dis_event_release_reason', '', '',        'N', '0', 'admin', CURRENT_TIMESTAMP, '解除原因：其他原因'),
-- 转接报送部门（转接弹窗多选、列表按责任单位筛选共用）
(1, '市政部门',         '1', 'dis_transfer_dept',  '', '',        'N', '0', 'admin', CURRENT_TIMESTAMP, '转接部门：市政部门'),
(2, '消防部门',         '2', 'dis_transfer_dept',  '', 'warning', 'N', '0', 'admin', CURRENT_TIMESTAMP, '转接部门：消防部门'),
(3, '排水管理部',       '3', 'dis_transfer_dept',  '', '',        'N', '0', 'admin', CURRENT_TIMESTAMP, '转接部门：排水管理部'),
(4, '管网维护部',       '4', 'dis_transfer_dept',  '', '',        'N', '0', 'admin', CURRENT_TIMESTAMP, '转接部门：管网维护部'),
(5, '设备运维部',       '5', 'dis_transfer_dept',  '', '',        'N', '0', 'admin', CURRENT_TIMESTAMP, '转接部门：设备运维部'),
(6, '安全管理部',       '6', 'dis_transfer_dept',  '', '',        'N', '0', 'admin', CURRENT_TIMESTAMP, '转接部门：安全管理部'),
-- 上报报送层级（报送弹窗多选）
(1, '区级应急办',       '1', 'dis_report_target',  '', '',        'N', '0', 'admin', CURRENT_TIMESTAMP, '报送层级：区级应急办'),
(2, '市级应急办',       '2', 'dis_report_target',  '', 'primary', 'N', '0', 'admin', CURRENT_TIMESTAMP, '报送层级：市级应急办'),
(3, '省级应急厅',       '3', 'dis_report_target',  '', 'warning', 'N', '0', 'admin', CURRENT_TIMESTAMP, '报送层级：省级应急厅'),
(4, '国务院应急管理部', '4', 'dis_report_target',  '', 'danger',  'N', '0', 'admin', CURRENT_TIMESTAMP, '报送层级：国务院应急管理部'),
-- 预案类型
(1, '综合',             '1', 'dis_plan_type',     '', 'primary', 'N', '0', 'admin', CURRENT_TIMESTAMP, '预案类型：综合'),
(2, '专项',             '2', 'dis_plan_type',     '', 'success', 'N', '0', 'admin', CURRENT_TIMESTAMP, '预案类型：专项'),
(3, '现场处置',         '3', 'dis_plan_type',     '', 'info',    'N', '0', 'admin', CURRENT_TIMESTAMP, '预案类型：现场处置'),
-- 预案等级
(1, 'Ⅰ级',             '1', 'dis_plan_level',    '', 'danger',  'N', '0', 'admin', CURRENT_TIMESTAMP, '预案等级：Ⅰ级'),
(2, 'Ⅱ级',             '2', 'dis_plan_level',    '', 'warning', 'N', '0', 'admin', CURRENT_TIMESTAMP, '预案等级：Ⅱ级'),
(3, 'Ⅲ级',             '3', 'dis_plan_level',    '', 'info',    'N', '0', 'admin', CURRENT_TIMESTAMP, '预案等级：Ⅲ级'),
-- 通用是否
(1, '否',               '0', 'dis_yes_no',        '', 'info',    'Y', '0', 'admin', CURRENT_TIMESTAMP, '通用是否：否'),
(2, '是',               '1', 'dis_yes_no',        '', 'warning', 'N', '0', 'admin', CURRENT_TIMESTAMP, '通用是否：是');

COMMIT;

-- =====================================================================
-- 六、执行后自检
-- =====================================================================
-- 1) 新表是否齐全（用 to_regclass，避免 information_schema.tables 触发
--    sys_freespace 权限校验；在部分 KingbaseES 实例上普通账号会报权限错误）
--    SELECT to_regclass('app_user.dis_event'),        to_regclass('app_user.dis_event_log'),
--           to_regclass('app_user.dis_event_grade'),  to_regclass('app_user.dis_plan'),
--           to_regclass('app_user.dis_plan_version'), to_regclass('app_user.dis_plan_log');
-- 2) 字典是否写入
--    SELECT dict_type, count(*) FROM app_user.sys_dict_data
--     WHERE dict_type LIKE 'dis_%' GROUP BY dict_type ORDER BY dict_type;
-- 3) 事件状态分布是否符合预期（只应是 0~4）
--    SELECT status, count(*) FROM app_user.dis_event GROUP BY status ORDER BY status;
-- 4) 日志是否迁移到位
--    SELECT oper_type, count(*) FROM app_user.dis_event_log GROUP BY oper_type ORDER BY oper_type;