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

-- 统一设备报警阈值配置（KingbaseES）
-- 1) equipment_warning_type 保存设备类型的预警指标元数据；
-- 2) warning_threshold 保存设备编码 + warning_code 的实际阈值；
-- 3) 旧供水/排水/燃气/窨井菜单统一指向同一页面，通过 query.rootTypeId 隔离设备范围。

CREATE TABLE IF NOT EXISTS app_user.equipment_warning_type (
    id int8 GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    equipment_type_id int8 NOT NULL,
    warning_code varchar(50) NOT NULL,
    warning_name varchar(50) NOT NULL,
    warning_unit varchar(50),
    data_type int2 NOT NULL DEFAULT 0,
    remark varchar(255),
    create_time sys."timestamp" DEFAULT now(),
    update_time sys."timestamp" DEFAULT now()
);

COMMENT ON TABLE app_user.equipment_warning_type IS '设备预警指标类型配置';
COMMENT ON COLUMN app_user.equipment_warning_type.equipment_type_id IS '设备类型ID';
COMMENT ON COLUMN app_user.equipment_warning_type.warning_code IS '报警编码';
COMMENT ON COLUMN app_user.equipment_warning_type.warning_name IS '报警名称';
COMMENT ON COLUMN app_user.equipment_warning_type.warning_unit IS '监测单位';
COMMENT ON COLUMN app_user.equipment_warning_type.data_type IS '数字类型：0最小+最大，1最小值，2最大值';

CREATE INDEX IF NOT EXISTS idx_equipment_warning_type_type ON app_user.equipment_warning_type(equipment_type_id);
CREATE UNIQUE INDEX IF NOT EXISTS uk_equipment_warning_type_code ON app_user.equipment_warning_type(equipment_type_id, warning_code);

-- 旧入口按 menu_id 精准迁移，避免同名菜单被误改：
-- 2119 供水、2194 排水、2017 窨井盖、2192 燃气。
-- 燃气、排水的阈值配置父目录使用业务路径，替换旧数字路径 1/2。
UPDATE app_user.sys_menu
   SET path = 'thresholdConfig'
 WHERE menu_id IN (2191, 2193);

UPDATE app_user.sys_menu
       SET component = 'subSystem/lifeCompany/warningThreshold/index',
       path = 'warningThreshold',
       query = '{"rootTypeId":1}'
 WHERE menu_id = 2119;

UPDATE app_user.sys_menu
       SET component = 'subSystem/lifeCompany/warningThreshold/index',
       path = 'warningThreshold',
       query = '{"rootTypeId":2}'
 WHERE menu_id = 2194;

UPDATE app_user.sys_menu
       SET component = 'subSystem/lifeCompany/warningThreshold/index',
       path = 'warningThreshold',
       query = '{"rootTypeId":4}'
 WHERE menu_id = 2017;

UPDATE app_user.sys_menu
       SET component = 'subSystem/lifeCompany/warningThreshold/index',
       path = 'warningThreshold',
       query = '{"rootTypeId":3}'
 WHERE menu_id = 2192;

-- 生命线预警联动处置系统下新增两级菜单：
-- 阈值管理(2241, parent_id=2006) -> 报警阈值(2242)。
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM app_user.sys_menu WHERE menu_id = 2241) THEN
        INSERT INTO app_user.sys_menu
            (menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
             is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
        VALUES
            (2241, '阈值管理', 2006, 30, 'thresholdManagement', 'ParentView', '',
             'LifeCompanyThresholdManagement', '1', '0', 'M', '0', '0', '', 'setting', 'admin', now(), '报警阈值管理');
    ELSE
        UPDATE app_user.sys_menu
           SET menu_name = '阈值管理', parent_id = 2006, order_num = 30,
               path = 'thresholdManagement', component = 'ParentView', query = '',
               route_name = 'LifeCompanyThresholdManagement', menu_type = 'M',
               visible = '0', status = '0', icon = 'setting', remark = '报警阈值管理'
         WHERE menu_id = 2241;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM app_user.sys_menu WHERE menu_id = 2242) THEN
        INSERT INTO app_user.sys_menu
            (menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
             is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
        VALUES
            (2242, '报警阈值', 2241, 1, 'warningThreshold',
             'subSystem/lifeCompany/warningThreshold/index', '', 'LifeCompanyAlarmThreshold',
             '1', '0', 'C', '0', '0', 'warning:threshold:list', 'chart', 'admin', now(), '报警阈值配置');
    ELSE
        UPDATE app_user.sys_menu
           SET menu_name = '报警阈值', parent_id = 2241, order_num = 1,
               path = 'warningThreshold', component = 'subSystem/lifeCompany/warningThreshold/index',
               query = '', route_name = 'LifeCompanyAlarmThreshold', menu_type = 'C',
               visible = '0', status = '0', perms = 'warning:threshold:list',
               icon = 'chart', remark = '报警阈值配置'
         WHERE menu_id = 2242;
    END IF;
END $$;

-- 统一菜单路由名称；menu_id 精确限定为新建的全量报警阈值入口。
UPDATE app_user.sys_menu
   SET path = 'warningThreshold',
       component = 'subSystem/lifeCompany/warningThreshold/index',
       perms = 'warning:threshold:list'
 WHERE menu_id = 2242;

ALTER TABLE app_user.warning_threshold DROP COLUMN IF EXISTS warning_type;
ALTER TABLE app_user.warning_threshold DROP COLUMN IF EXISTS remark;