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

-- 统一报警审核。KingbaseES（PostgreSQL 兼容模式）。
-- 部署顺序：必须先执行 migration_alarm_case_work_order_id.sql（4 表 order_id→work_order_id 改名
--   + alarm_data 删 alarm_time），再执行本脚本，日志索引使用 create_time。
--   列未先落地会因列不存在而报错。
-- 先备份，停止报警/工单写入，完整执行事务，再同时部署管理端、API 服务和前端；不要拆开执行或忽略错误。
-- audit_status 与 alarm_status 独立；确认报警创建工单，但不改变设备或报警处理状态。
-- 日志操作字典与共享 AlarmLogOperTypeEnum 对齐：0触发/1确认/2误报/3处理。
-- 仅调整结构与菜单，不迁移或补录历史数据；旧 alarm_audit 表不再由新代码读写。
BEGIN;
LOCK TABLE app_user.alarm_data, app_user.work_order_log IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE app_user.sys_menu, app_user.sys_role_menu IN SHARE ROW EXCLUSIVE MODE;

ALTER TABLE app_user.alarm_data ADD COLUMN IF NOT EXISTS audit_status integer DEFAULT 0;
ALTER TABLE app_user.alarm_data ALTER COLUMN audit_status SET DEFAULT 0;
ALTER TABLE app_user.alarm_data ALTER COLUMN audit_status SET NOT NULL;
COMMENT ON COLUMN app_user.alarm_data.audit_status IS '审核结果：0-未审核 1-确认报警 2-误报警，独立于报警处理状态';
DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conrelid = 'app_user.alarm_data'::regclass
                   AND conname = 'ck_alarm_data_audit_status') THEN
        ALTER TABLE app_user.alarm_data ADD CONSTRAINT ck_alarm_data_audit_status CHECK (audit_status IN (0,1,2));
    END IF;
END $$;

-- KingbaseES 显式序列自增，与 work_order_log 的主键策略一致，各表使用独立序列。
CREATE SEQUENCE IF NOT EXISTS app_user.alarm_log_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
CREATE TABLE IF NOT EXISTS app_user.alarm_log (
    log_id int8 NOT NULL DEFAULT nextval('app_user.alarm_log_id_seq'::regclass) PRIMARY KEY,
    alarm_id varchar(64) NOT NULL,
    oper_type integer NOT NULL CHECK (oper_type IN (0,1,2,3)),
    oper_desc text,
    attach_url text,
    oper_user_id int8,
    oper_user_name varchar(128),
    oper_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);
ALTER SEQUENCE app_user.alarm_log_id_seq OWNED BY app_user.alarm_log.log_id;
COMMENT ON COLUMN app_user.alarm_log.log_id IS '日志主键，int8，使用 alarm_log_id_seq 序列自增长';
COMMENT ON TABLE app_user.alarm_log IS '报警操作日志，记录报警触发、审核及处理；确认日志包含转工单号';
COMMENT ON COLUMN app_user.alarm_log.alarm_id IS '关联 alarm_data.id';
COMMENT ON COLUMN app_user.alarm_log.oper_type IS 'AlarmLogOperTypeEnum：0触发 1确认 2误报 3处理；工单日志只写work_order_log';
COMMENT ON COLUMN app_user.alarm_log.oper_desc IS '操作详情或审核意见';
COMMENT ON COLUMN app_user.alarm_log.attach_url IS '附件地址，多个用逗号分隔';
COMMENT ON COLUMN app_user.alarm_log.oper_user_id IS '操作人ID，系统操作可为空';
COMMENT ON COLUMN app_user.alarm_log.oper_user_name IS '操作人昵称(sys_user.nick_name)；自动操作使用系统名称';
COMMENT ON COLUMN app_user.alarm_log.oper_time IS '操作时间';
CREATE INDEX IF NOT EXISTS idx_alarm_log_alarm_time ON app_user.alarm_log(alarm_id, oper_time, log_id);
CREATE INDEX IF NOT EXISTS idx_alarm_review_query ON app_user.alarm_data(alarm_status, audit_status, create_time DESC);

-- 报警日志完全由业务代码写入；删除旧触发器/自定义函数，避免重复日志。
DROP TRIGGER IF EXISTS trg_alarm_lifecycle_log ON app_user.alarm_data;
DROP TRIGGER IF EXISTS trg_work_order_alarm_log ON app_user.work_order_log;
DROP FUNCTION IF EXISTS app_user.record_alarm_lifecycle();
DROP FUNCTION IF EXISTS app_user.record_alarm_work_order_log();

-- 四条都是已有页面菜单：精准更新 ID，统一 path，保留父级、排序、图标与已有角色关联。
UPDATE app_user.sys_menu SET path = 'alarmAudit', component = 'subSystem/alarm/common/audit', perms = 'alarm:review:list',
    query = CASE menu_id
        WHEN 2118 THEN '{"rootTypeId":1}'
        WHEN 2142 THEN '{"rootTypeId":2}'
        WHEN 2097 THEN '{"rootTypeId":3,"warning_code":"WARN-GAS-CONCENTRATION"}'
        WHEN 2024 THEN '{"rootTypeId":4}' END,
    update_time = CURRENT_TIMESTAMP
WHERE menu_id IN (2118,2142,2097,2024);

-- 仅为新建按钮补角色授权，重复执行不得恢复管理员已经撤销的审核权限。
WITH new_buttons AS (
INSERT INTO app_user.sys_menu(menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache,
                              menu_type, visible, status, perms, icon, create_by, create_time)
SELECT (SELECT coalesce(max(menu_id), 0) FROM app_user.sys_menu) + row_number() OVER (ORDER BY p.menu_id),
       '报警审核', p.menu_id, 1, '', NULL, 1, 0, 'F', '0', '0', 'alarm:review:audit', '#', 'admin', CURRENT_TIMESTAMP
FROM app_user.sys_menu p WHERE p.menu_id IN (2118,2142,2097,2024)
AND NOT EXISTS (SELECT 1 FROM app_user.sys_menu c WHERE c.parent_id = p.menu_id AND c.perms = 'alarm:review:audit')
RETURNING menu_id, parent_id
)
INSERT INTO app_user.sys_role_menu(role_id, menu_id)
SELECT DISTINCT rm.role_id, c.menu_id FROM app_user.sys_role_menu rm
JOIN new_buttons c ON c.parent_id = rm.menu_id
WHERE rm.menu_id IN (2118,2142,2097,2024)
AND NOT EXISTS (SELECT 1 FROM app_user.sys_role_menu existing WHERE existing.role_id = rm.role_id AND existing.menu_id = c.menu_id);

COMMIT;