-- 系统设置：下线 SmartAdmin 框架演示/运维类菜单
-- reload、文件管理、单号管理、功能Demo、文档中心、代码生成（含子菜单与功能点）

BEGIN;

CREATE TEMP TABLE IF NOT EXISTS tmp_hide_menu (menu_id BIGINT PRIMARY KEY) ON COMMIT DROP;
TRUNCATE tmp_hide_menu;

-- 1) 按名称/路径/组件匹配根菜单
INSERT INTO tmp_hide_menu (menu_id)
SELECT menu_id FROM t_menu m
WHERE m.deleted_flag = 0
  AND (
        m.menu_name IN ('Reload', 'reload', '文件管理', '单号管理', '功能Demo', '功能demo', '文档中心', '代码生成')
     OR m.path LIKE '/support/reload%'
     OR m.path LIKE '/reload%'
     OR m.path LIKE '/support/file%'
     OR m.path LIKE '/file%'
     OR m.path LIKE '/support/serial-number%'
     OR m.path LIKE '/serial-number%'
     OR m.path LIKE '/support/code-generator%'
     OR m.path LIKE '/code-generator%'
     OR m.path LIKE '/support/help-doc%'
     OR m.path LIKE '/help-doc%'
     OR m.component LIKE '%/support/reload/%'
     OR m.component LIKE '%/support/file/%'
     OR m.component LIKE '%/support/serial-number/%'
     OR m.component LIKE '%/support/code-generator/%'
     OR m.component LIKE '%/support/help-doc/%'
  )
  AND NOT EXISTS (SELECT 1 FROM tmp_hide_menu t WHERE t.menu_id = m.menu_id);

-- 2) 递归纳入全部下级菜单与功能点
DO $$
DECLARE
    v_added INT;
BEGIN
    LOOP
        INSERT INTO tmp_hide_menu (menu_id)
        SELECT m.menu_id
        FROM t_menu m
        INNER JOIN tmp_hide_menu p ON m.parent_id = p.menu_id
        WHERE m.deleted_flag = 0
          AND NOT EXISTS (SELECT 1 FROM tmp_hide_menu x WHERE x.menu_id = m.menu_id);
        GET DIAGNOSTICS v_added = ROW_COUNT;
        EXIT WHEN v_added = 0;
    END LOOP;
END $$;

-- 3) 软删除菜单
UPDATE t_menu
SET deleted_flag = 1,
    visible_flag = 0,
    disabled_flag = 1,
    update_time  = NOW()
WHERE menu_id IN (SELECT menu_id FROM tmp_hide_menu);

-- 4) 清理角色授权
DELETE FROM t_role_menu
WHERE menu_id IN (SELECT menu_id FROM tmp_hide_menu);

COMMIT;

-- 验证（应无结果）
SELECT menu_id, menu_name, path, parent_id
FROM t_menu
WHERE deleted_flag = 0
  AND (
        menu_name IN ('Reload', 'reload', '文件管理', '单号管理', '功能Demo', '功能demo', '文档中心', '代码生成')
     OR path LIKE '/support/reload%'
     OR path LIKE '/support/file%'
     OR path LIKE '/support/serial-number%'
     OR path LIKE '/support/code-generator%'
     OR path LIKE '/support/help-doc%'
     OR path LIKE '/help-doc%'
  )
ORDER BY menu_id;