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

-- 统一报警审核。KingbaseES（PostgreSQL 兼容模式）。先备份，停止报警/工单写入，
-- 完整执行事务，再同时部署管理端、API 服务和前端；不要拆开执行或忽略错误。
-- audit_status 与 alarm_status 独立；确认报警创建工单，但不改变设备或报警处理状态。
-- 日志操作字典与 AlarmLogOperTypeEnum 对齐：0触发/1确认/2误报/3处理/4关联工单/5工单操作。
-- 用数据库触发器统一覆盖各采集服务、API、演练及直接入库路径，不在多个服务重复初始化日志。
-- 仅调整结构与菜单，不迁移或补录历史数据；旧 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,4,5)),
    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处理 4关联工单 5工单操作';
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 '操作人名称';
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, alarm_time DESC);

CREATE OR REPLACE FUNCTION app_user.record_alarm_lifecycle() RETURNS trigger AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO app_user.alarm_log(alarm_id, oper_type, oper_desc, oper_user_name, oper_time)
        VALUES (NEW.id, 0, concat(coalesce(NEW.warning_type, NEW.warning_code, '监测报警'), '；实际值：', NEW.actual_value,
                '；触发时下限：', NEW.min_value, '；触发时上限：', NEW.max_value),
                '监测系统', coalesce(NEW.alarm_time, NEW.create_time, CURRENT_TIMESTAMP));
    ELSIF NEW.alarm_status IS DISTINCT FROM OLD.alarm_status AND NEW.alarm_status = 1 THEN
        INSERT INTO app_user.alarm_log(alarm_id, oper_type, oper_desc, oper_user_name, oper_time)
        VALUES (NEW.id, 3, '报警已处理', '系统', clock_timestamp());
    END IF;
    IF (TG_OP = 'INSERT' AND NEW.order_id IS NOT NULL)
       OR (TG_OP = 'UPDATE' AND NEW.order_id IS DISTINCT FROM OLD.order_id AND NEW.order_id IS NOT NULL) THEN
        INSERT INTO app_user.alarm_log(alarm_id, oper_type, oper_desc, oper_user_name, oper_time)
        VALUES (NEW.id, 4, '已关联工单ID：' || NEW.order_id, '系统', clock_timestamp());
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS trg_alarm_lifecycle_log ON app_user.alarm_data;
CREATE TRIGGER trg_alarm_lifecycle_log AFTER INSERT OR UPDATE OF alarm_status, order_id ON app_user.alarm_data
FOR EACH ROW EXECUTE PROCEDURE app_user.record_alarm_lifecycle();

CREATE OR REPLACE FUNCTION app_user.record_alarm_work_order_log() RETURNS trigger AS $$
BEGIN
    INSERT INTO app_user.alarm_log(alarm_id, oper_type, oper_desc, attach_url, oper_user_id, oper_user_name, oper_time)
    SELECT a.id, 5, NEW.oper_desc, NEW.attach_url, NEW.oper_user_id, NEW.oper_user_name,
           coalesce(NEW.oper_time, CURRENT_TIMESTAMP)
    FROM app_user.alarm_data a WHERE a.order_id = NEW.order_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS trg_work_order_alarm_log ON app_user.work_order_log;
CREATE TRIGGER trg_work_order_alarm_log AFTER INSERT ON app_user.work_order_log
FOR EACH ROW EXECUTE PROCEDURE app_user.record_alarm_work_order_log();

-- 四条都是已有页面菜单：精准更新 ID，统一 path，保留父级、排序、图标与已有角色关联。
UPDATE app_user.sys_menu SET path = 'alarmAudit', component = 'subSystem/alarm/index', 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;