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 级 —— Ⅰ级(紧急) / Ⅱ级(较大) / Ⅲ级(一般)（原型口径）
--   · 事件类别：保留两级 —— event_type(管网类型) + event_category(故障类别)
--   · 事件状态：5 态 —— 0 待核实 / 1 已核实 / 2 处置中 / 3 已解除 / 4 不实事件
--     存量「待受理」归 0、「已关闭」归 3
--
-- 幂等性：本脚本可重复执行。
--   建表用 CREATE TABLE IF NOT EXISTS；加列用 ADD COLUMN IF NOT EXISTS；
--   字典先 DELETE 同 dict_type 再 INSERT。
--
-- ⚠️ 本脚本只做「新增」，不删除任何旧表/旧列/旧数据。
--    废弃旧表与旧列的 DROP 语句放在独立的 dispatch_refactor_drop.sql，
--    确认新结构运行正常后再单独执行。
-- =====================================================================

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,
    report_time      timestamp,
    reporter         varchar(64),
    reporter_phone   varchar(20),
    event_type       varchar(20),
    event_category   varchar(20),
    event_level      varchar(20),
    event_region     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,
    plan_id          int8,
    loss_amount      numeric(14,2),
    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'
);

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 report_time     timestamp;
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS reporter        varchar(64);
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_type      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 event_region    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 plan_id         int8;
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS loss_amount     numeric(14,2);
ALTER TABLE app_user.dis_event ADD COLUMN IF NOT EXISTS create_by       varchar(64);
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_by       varchar(64);
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.report_time     IS '接报时间';
COMMENT ON COLUMN app_user.dis_event.reporter        IS '接报人';
COMMENT ON COLUMN app_user.dis_event.reporter_phone  IS '上报人联系电话';
COMMENT ON COLUMN app_user.dis_event.event_type      IS '管网类型：字典 dis_event_type（供水/燃气/排水/其他）';
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.event_region    IS '发生区域：字典 dis_event_region';
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.plan_id         IS '应用预案ID（dis_plan.id）';
COMMENT ON COLUMN app_user.dis_event.loss_amount     IS '直接经济损失（万元）';
COMMENT ON COLUMN app_user.dis_event.del_flag        IS '删除标志（0=正常 2=删除）';

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_report_time    ON app_user.dis_event(report_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);
CREATE INDEX IF NOT EXISTS idx_dis_event_type           ON app_user.dis_event(event_type);

-- ---------------------------------------------------------------------
-- 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 TABLE IF NOT EXISTS app_user.dis_plan (
    id             int8 GENERATED BY DEFAULT AS IDENTITY 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),
    attach_name    varchar(255),
    attach_path    varchar(1000),
    is_template    int2 NOT NULL DEFAULT 0,
    status         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'
);

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 attach_name    varchar(255);
ALTER TABLE app_user.dis_plan ADD COLUMN IF NOT EXISTS attach_path    varchar(1000);
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';

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.attach_name    IS '附件名称';
COMMENT ON COLUMN app_user.dis_plan.attach_path    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 TABLE IF NOT EXISTS app_user.dis_plan_version (
    id          int8 GENERATED BY DEFAULT AS IDENTITY 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;

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 TABLE IF NOT EXISTS app_user.dis_plan_log (
    id             int8 GENERATED BY DEFAULT AS IDENTITY 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);

-- =====================================================================
-- 三、事件分级（保留原表，字段字典化 + 补业务编号）
-- =====================================================================
CREATE TABLE IF NOT EXISTS app_user.dis_event_grade (
    id             int8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    grade_no       varchar(32),
    event_category varchar(20),
    event_type     varchar(20),
    event_level    varchar(20),
    sub_category   varchar(50),
    scenario       varchar(200),
    criteria       text,
    response       varchar(500),
    status         int2 NOT NULL DEFAULT 0,
    remark         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'
);

ALTER TABLE app_user.dis_event_grade ADD COLUMN IF NOT EXISTS grade_no       varchar(32);
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_type     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 sub_category   varchar(50);
ALTER TABLE app_user.dis_event_grade ADD COLUMN IF NOT EXISTS scenario       varchar(200);
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 status         int2 DEFAULT 0;
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_by      varchar(64);
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_by      varchar(64);
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';

COMMENT ON TABLE  app_user.dis_event_grade IS '应急事件分级分类标准，同时作为初判核实的辅助核实依据';
COMMENT ON COLUMN app_user.dis_event_grade.grade_no       IS '标准编号（业务序号：GJ+yyyyMMdd+3位随机）';
COMMENT ON COLUMN app_user.dis_event_grade.event_category IS '事件类别：字典 dis_event_category';
COMMENT ON COLUMN app_user.dis_event_grade.event_type     IS '管网类型：字典 dis_event_type';
COMMENT ON COLUMN app_user.dis_event_grade.event_level    IS '事件等级：字典 dis_event_level';
COMMENT ON COLUMN app_user.dis_event_grade.sub_category   IS '类别细分';
COMMENT ON COLUMN app_user.dis_event_grade.scenario       IS '适用场景';
COMMENT ON COLUMN app_user.dis_event_grade.criteria       IS '分级分类判断标准';
COMMENT ON COLUMN app_user.dis_event_grade.response       IS '响应要求，如：30 分钟内启动预案';
COMMENT ON COLUMN app_user.dis_event_grade.status         IS '标准生效状态：0-启用 1-停用';

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

-- =====================================================================
-- 四、存量数据迁移 —— 见独立文件 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_type','dis_event_category',
                     'dis_event_level','dis_event_region','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');

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_type',           '应急事件管网类型：1供水管网 2燃气管网 3排水管网 9其他'),
        ('应急事件类别',     'dis_event_category',       '应急事件类别：故障类别'),
        ('应急事件等级',     'dis_event_level',          '应急事件等级：1Ⅰ级紧急 2Ⅱ级较大 3Ⅲ级一般'),
        ('应急事件区域',     'dis_event_region',         '应急事件发生区域'),
        ('工作催办方式',     '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_type',    '', 'primary', 'N', '0', 'admin', CURRENT_TIMESTAMP, '应急管网类型：供水管网'),
(2, '燃气管网',         '2', 'dis_event_type',    '', 'warning', 'N', '0', 'admin', CURRENT_TIMESTAMP, '应急管网类型：燃气管网'),
(3, '排水管网',         '3', 'dis_event_type',    '', 'info',    'N', '0', 'admin', CURRENT_TIMESTAMP, '应急管网类型：排水管网'),
(4, '其他',             '9', 'dis_event_type',    '', '',        '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_region',  '', '',        'N', '0', 'admin', CURRENT_TIMESTAMP, '发生区域：城东片区（按实际维护）'),
(2, '城南片区',         '2', 'dis_event_region',  '', '',        'Y', '0', 'admin', CURRENT_TIMESTAMP, '发生区域：城南片区（按实际维护）'),
(3, '城北片区',         '3', 'dis_event_region',  '', '',        'N', '0', 'admin', CURRENT_TIMESTAMP, '发生区域：城北片区（按实际维护）'),
(4, '太常片区',         '4', 'dis_event_region',  '', '',        'N', '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;