-- 一级菜单结构重组（PostgreSQL / KingBase）
-- 保留顶层：接报案中心、案件管理、涉案资金、涉案财物、卷宗管理、审议流程、数据大屏、系统设置
-- 其余 parent_id=0 的目录/页面移入「系统设置」下成为二级菜单

BEGIN;

DO $$
DECLARE
    v_system_settings_id BIGINT;
    v_max_sort INT;
    v_next_sort INT;
    r RECORD;
    v_moved INT := 0;
BEGIN
    SELECT menu_id INTO v_system_settings_id
    FROM t_menu
    WHERE menu_name = '系统设置' AND deleted_flag = 0
    ORDER BY menu_id
    LIMIT 1;

    IF v_system_settings_id IS NULL THEN
        INSERT INTO t_menu (menu_name, menu_type, parent_id, sort, path, component,
                            frame_flag, cache_flag, visible_flag, disabled_flag,
                            perms_type, icon, deleted_flag, create_user_id, create_time, update_time)
        VALUES ('系统设置', 1, 0, 99, '/setting', NULL,
                false, false, true, false, 1, 'SettingOutlined', false, 1, NOW(), NOW())
        RETURNING menu_id INTO v_system_settings_id;
    END IF;

    SELECT COALESCE(MAX(sort), 0) INTO v_max_sort
    FROM t_menu
    WHERE parent_id = v_system_settings_id AND deleted_flag = 0;

    v_next_sort := v_max_sort + 10;

    -- 卷宗管理提升为一级菜单（位于涉案财物之后）
    UPDATE t_menu
    SET parent_id = 0, sort = 5, update_time = NOW()
    WHERE menu_name = '卷宗管理' AND deleted_flag = 0 AND menu_type = 2;

    FOR r IN
        SELECT menu_id, menu_name
        FROM t_menu
        WHERE parent_id = 0
          AND deleted_flag = 0
          AND menu_type IN (1, 2)
          AND menu_name NOT IN (
              '接报案中心', '案件管理', '涉案资金', '涉案财物', '卷宗管理',
              '审议流程', '数据大屏', '系统设置'
          )
        ORDER BY sort NULLS LAST, menu_id
    LOOP
        UPDATE t_menu
        SET parent_id = v_system_settings_id,
            sort = v_next_sort,
            update_time = NOW()
        WHERE menu_id = r.menu_id;

        RAISE NOTICE '已移入系统设置: % (menu_id=%)', r.menu_name, r.menu_id;
        v_next_sort := v_next_sort + 10;
        v_moved := v_moved + 1;
    END LOOP;

    UPDATE t_menu SET sort = 1,  update_time = NOW() WHERE menu_name = '接报案中心' AND parent_id = 0 AND menu_type = 1 AND deleted_flag = 0;
    UPDATE t_menu SET sort = 2,  update_time = NOW() WHERE menu_name = '案件管理'   AND parent_id = 0 AND menu_type = 1 AND deleted_flag = 0;
    UPDATE t_menu SET sort = 3,  update_time = NOW() WHERE menu_name = '涉案资金'   AND parent_id = 0 AND menu_type = 1 AND deleted_flag = 0;
    UPDATE t_menu SET sort = 4,  update_time = NOW() WHERE menu_name = '涉案财物'   AND parent_id = 0 AND menu_type = 1 AND deleted_flag = 0;
    UPDATE t_menu SET sort = 5,  update_time = NOW() WHERE menu_name = '卷宗管理'   AND parent_id = 0 AND menu_type = 2 AND deleted_flag = 0;
    UPDATE t_menu SET sort = 6,  update_time = NOW() WHERE menu_name = '审议流程'   AND parent_id = 0 AND menu_type = 1 AND deleted_flag = 0;
    UPDATE t_menu SET sort = 7,  update_time = NOW() WHERE menu_name = '数据大屏'   AND parent_id = 0 AND menu_type = 1 AND deleted_flag = 0;
    UPDATE t_menu SET sort = 99, update_time = NOW() WHERE menu_name = '系统设置'   AND parent_id = 0 AND menu_type = 1 AND deleted_flag = 0;

    RAISE NOTICE '一级菜单重组完成，移入系统设置 % 项', v_moved;
END $$;

COMMIT;

-- 验证顶层菜单
SELECT menu_id, menu_name, sort, parent_id
FROM t_menu
WHERE parent_id = 0 AND deleted_flag = 0 AND menu_type IN (1, 2)
ORDER BY sort, menu_id;