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

-- 供水设备管理菜单顺序迁移（KingbaseES）
-- 目的：将“设备异常管理”排在“设备维修派单”之前。
BEGIN;

DO $$
DECLARE
    root_id bigint;
    device_id bigint;
BEGIN
    SELECT menu_id INTO root_id
      FROM app_user.sys_menu
     WHERE parent_id = 0
       AND (path = 'subSystem/waterSupply' OR menu_name = '供水管网安全运行监测系统')
     ORDER BY menu_id
     LIMIT 1;

    IF root_id IS NULL THEN
        RAISE EXCEPTION '供水根菜单不存在，未执行设备菜单顺序调整';
    END IF;

    SELECT menu_id INTO device_id
      FROM app_user.sys_menu
     WHERE parent_id = root_id
       AND (menu_name = '监测设备管理' OR path IN ('subSystem/waterSupply/device', 'device'))
     ORDER BY menu_id
     LIMIT 1;

    IF device_id IS NULL THEN
        RAISE EXCEPTION '供水设备管理菜单不存在，未执行设备菜单顺序调整';
    END IF;

    UPDATE app_user.sys_menu
       SET order_num = 2
     WHERE parent_id = device_id
       AND path = 'abnormal';

    UPDATE app_user.sys_menu
       SET order_num = 3
     WHERE parent_id = device_id
       AND path = 'maintain';
END $$;

COMMIT;