AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.424.verify.sql

-- ===========================================================================
-- UAT 租户占位菜单收口 VERIFY —— 全部 SELECT 真值标量断言
--
-- 约定（AutoVersionUpdate.RunVerifyScriptIfExists，AutoVersionUpdate.cs:225-248）：
--   逐条 SqlQuerySingle<object> 取「首行首列」，非真值即抛异常 → 迁移 Failed
--   → StopApplicationOnFailure 默认 true → 应用启动中止。
--   因此本文件：
--     ① 不得出现 SET / 非 SELECT 语句（返回 null = 判失败）
--     ② 每条必须是布尔标量，不能是多列报表（首列标签恒真 = 断言失效）
--     ③ 不得断言本机计数（16 / 25 / 22 之类），否则在没有 UATTEST_CHL 的
--        环境上必然误判。以下全部写成**环境无关的不变量**：该租户不存在时
--        抑制清单为空，各断言自然成立。
--
-- 人工对账报表与回滚见同目录 UAT-PLACEHOLDER-MENU-HIDE.ops.sql
-- （无版本号前缀 → Version.TryParse 失败 → AutoVersionUpdate 永不自动执行）。
-- ===========================================================================

-- ---------- A. 核心不变量：抑制清单中的每一对，在 SysTenantMenu 中都不存在 ----------
SELECT COUNT(*) = 0 FROM SysTenantMenu tm
  JOIN ado_tenant_menu_suppression s ON s.TenantId = tm.TenantId AND s.MenuId = tm.MenuId;

-- ---------- B. 租户隔离：抑制清单只允许出现 UATTEST_CHL 一个租户 ----------
SELECT COUNT(*) = 0 FROM ado_tenant_menu_suppression s
  WHERE s.TenantId <> IFNULL(
      (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id = t.OrgId
        WHERE o.Code = 'UATTEST_CHL' AND t.Status = 1 LIMIT 1), -1);

-- ---------- C. 全局 SysMenu 零改动：被抑制的菜单必须仍存在于 SysMenu ----------
SELECT COUNT(*) = 0 FROM ado_tenant_menu_suppression s
  LEFT JOIN SysMenu m ON m.Id = s.MenuId
  WHERE m.Id IS NULL;

-- ---------- D. 绝不误伤真实功能：抑制对象只能是占位页或纯目录 ----------
SELECT COUNT(*) = 0 FROM ado_tenant_menu_suppression s
  JOIN SysMenu m ON m.Id = s.MenuId
  WHERE m.Component <> '/aidop/planning/index' AND m.Component <> 'Layout';

-- ---------- E. 被抑制的目录必须确实「非按钮子项 100% 是占位」----------
SELECT COUNT(*) = 0 FROM ado_tenant_menu_suppression s
  JOIN SysMenu m ON m.Id = s.MenuId
  WHERE m.Component = 'Layout'
    AND EXISTS (SELECT 1 FROM SysMenu c
                 WHERE c.Pid = m.Id AND c.Type <> 3
                   AND (c.Component IS NULL OR c.Component <> '/aidop/planning/index'));

-- ---------- F. 效果断言：S0/S5/S6/S7 占位菜单对该租户不可见 ----------
--            S9 / S4 / M11-M16 本轮不处理，不纳入断言。
SELECT COUNT(*) = 0 FROM SysMenu m
  JOIN SysTenantMenu tm ON tm.MenuId = m.Id
   AND tm.TenantId = IFNULL(
       (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id = t.OrgId
         WHERE o.Code = 'UATTEST_CHL' AND t.Status = 1 LIMIT 1), -1)
  WHERE m.Component = '/aidop/planning/index'
    AND (m.Path LIKE '/aidop/s0/%' OR m.Path LIKE '/aidop/s5/%'
         OR m.Path LIKE '/aidop/s6/%' OR m.Path LIKE '/aidop/s7/%');

-- ---------- G. 不留空壳父目录：该租户可见的 /aidop 目录必须仍有可见子菜单 ----------
SELECT COUNT(*) = 0 FROM SysMenu p
  JOIN SysTenantMenu tm ON tm.MenuId = p.Id
   AND tm.TenantId = IFNULL(
       (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id = t.OrgId
         WHERE o.Code = 'UATTEST_CHL' AND t.Status = 1 LIMIT 1), -1)
  WHERE p.Status = 1 AND p.Type = 1 AND p.Path LIKE '/aidop/%'
    AND NOT EXISTS (
        SELECT 1 FROM SysMenu c
          JOIN SysTenantMenu ctm ON ctm.MenuId = c.Id AND ctm.TenantId = tm.TenantId
         WHERE c.Pid = p.Id AND c.Status = 1 AND c.Type <> 3);

-- ---------- H. 跨租户回归：这些菜单在其他租户仍有授权（证明未跨租户误删）----------
--            抑制清单为空时自然成立。
SELECT (SELECT COUNT(*) FROM ado_tenant_menu_suppression) = 0
    OR (SELECT COUNT(*) FROM SysTenantMenu tm
         WHERE tm.MenuId IN (SELECT MenuId FROM ado_tenant_menu_suppression)
           AND tm.TenantId NOT IN (SELECT TenantId FROM ado_tenant_menu_suppression)) > 0;