-- =============================================================================
-- 绘境AI · huijing 死菜单清理（2026-06-11 审计版）
-- 适用库：ruoyi-vue-pro（staging：mini-desktop docker game-staging-mysql，MySQL 8.4.8）
-- 性质：软删（deleted=1），全量可回滚（备份表 + 固化删除集表）
-- 判据：16 棵死顶级子树（views 已被 fork 删除，点开全空白）；详见
--       docs/agent-specs/2026-06-11-admin-cloud清理-codex执行单.md §1 层A
-- 执行方式：分 4 段执行，每段闸门校验通过才进下一段（勿一次性灌整个文件）
-- =============================================================================

-- ---------- 段1：备份（若备份表已存在=有前次执行残留，必须停下人核，禁止覆盖） ----------
CREATE TABLE system_menu_bak_20260611 AS SELECT * FROM system_menu;
CREATE TABLE system_role_menu_bak_20260611 AS SELECT * FROM system_role_menu;

-- ---------- 段2：固化删除集（递归收集 16 棵死子树全部后代） ----------
DROP TABLE IF EXISTS cleanup_menu_del_20260611;
CREATE TABLE cleanup_menu_del_20260611 (id BIGINT PRIMARY KEY);
INSERT INTO cleanup_menu_del_20260611
WITH RECURSIVE del AS (
  SELECT id FROM system_menu
   WHERE id IN (1117,1185,1254,1281,2084,2159,2160,2262,2362,2397,2563,2758,4000,5100,6400,6500)
     AND deleted = 0
  UNION ALL
  SELECT m.id FROM system_menu m JOIN del d ON m.parent_id = d.id WHERE m.deleted = 0
)
SELECT id FROM del;

-- ---------- 段3：软删（带审计 updater，遵守审计列非空红线） ----------
UPDATE system_menu m JOIN cleanup_menu_del_20260611 d ON m.id = d.id
   SET m.deleted = 1, m.updater = 'cleanup-20260611', m.update_time = NOW();

UPDATE system_role_menu rm JOIN cleanup_menu_del_20260611 d ON rm.menu_id = d.id
   SET rm.deleted = 1, rm.updater = 'cleanup-20260611', rm.update_time = NOW()
 WHERE rm.deleted = 0;

-- ---------- 段4（仅回滚时用，平时勿执行）：全量恢复 ----------
-- UPDATE system_menu m JOIN system_menu_bak_20260611 b ON m.id = b.id SET m.deleted = b.deleted;
-- UPDATE system_role_menu rm JOIN system_role_menu_bak_20260611 b ON rm.id = b.id SET rm.deleted = b.deleted;
