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

-- ============================================================================
-- 1.0.433 verify：S5 冷链入站六个管理接口必须已登记权限，且未扩散到业务角色。
--
-- 约定（AutoVersionUpdate.RunVerifyScriptIfExists）：
--   每条语句取「首行首列」当布尔断言，必须为真；返回 0 行会得到 NULL 判假并中断启动。
--   故每条断言一律返回标量真值，且不使用 SET（SET 不返回行 → 必然判假）。
--
-- 断言口径：全部钉死到精确 Permission / 精确 TenantId / 精确 RoleId，
--   不使用「RoleCode JOIN 后跨租户 COUNT」这种会产生跨租户假阳性的写法
--   （该缺陷见 1.0.386.verify.sql，已由 1.0.431.verify.sql 纠正）。
--
-- 环境容错：非本项目夹具库（查不到 AIDOP 租户 / 库存查询菜单）上主脚本为空操作，
--   断言同步短路为真。
-- ============================================================================

-- 1) 六个权限点全部登记，且 Type=3 / Status=1
SELECT IF(
  (SELECT COUNT(*) FROM SysMenu WHERE Name = 'aidopS5InventoryStockQuery' AND Type = 2) = 0,
  1,
  (SELECT COUNT(*) = 6 FROM SysMenu
    WHERE Type = 3 AND Status = 1
      AND Permission IN (
        's5InventoryInbound:bootstrap',
        's5InventoryInbound:run-once',
        's5InventoryInbound:reconcile',
        's5InventoryInbound:transform-std',
        's5InventoryInbound:transform-inventory-std',
        's5InventoryInbound:daily-recon'))
) AS ok_six_permissions_registered;

-- 2) 无重复登记（同一 Permission 只允许一条 Type=3）
SELECT IF(
  (SELECT COUNT(*) FROM (
     SELECT Permission FROM SysMenu
      WHERE Type = 3
        AND Permission LIKE 's5InventoryInbound:%'
      GROUP BY Permission HAVING COUNT(*) > 1
   ) dup) = 0,
  1, 0
) AS ok_no_duplicate_permission;

-- 3) 挂载点正确：六个权限点都挂在「库存查询」页面菜单下
SELECT IF(
  (SELECT COUNT(*) FROM SysMenu WHERE Name = 'aidopS5InventoryStockQuery' AND Type = 2) = 0,
  1,
  (SELECT COUNT(*) = 6 FROM SysMenu m
    WHERE m.Type = 3
      AND m.Permission LIKE 's5InventoryInbound:%'
      AND m.Pid = (SELECT Id FROM SysMenu WHERE Name = 'aidopS5InventoryStockQuery' AND Type = 2 LIMIT 1))
) AS ok_mounted_under_stock_query;

-- 4) 管理租户（AIDOP，拥有 AccountType=888 且无角色的 AIDopAdmin）已获得全部六条租户级授权
SELECT IF(
  (SELECT COUNT(*) FROM SysTenant t JOIN SysOrg o ON o.Id = t.OrgId
    WHERE o.Code = 'AIDOP' AND t.Status = 1) = 0,
  1,
  (SELECT COUNT(*) = 6
     FROM SysTenantMenu tm
     JOIN SysMenu m ON m.Id = tm.MenuId
    WHERE tm.TenantId = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id = t.OrgId
                          WHERE o.Code = 'AIDOP' AND t.Status = 1 LIMIT 1)
      AND m.Type = 3
      AND m.Permission LIKE 's5InventoryInbound:%')
) AS ok_admin_tenant_granted;

-- 5) UAT 租户（全部账号 AccountType=777）**不得**获得租户级授权
SELECT IF(
  (SELECT COUNT(*) FROM SysTenant t JOIN SysOrg o ON o.Id = t.OrgId
    WHERE o.Code = 'UATTEST_CHL' AND t.Status = 1) = 0,
  1,
  (SELECT COUNT(*) = 0
     FROM SysTenantMenu tm
     JOIN SysMenu m ON m.Id = tm.MenuId
    WHERE tm.TenantId = (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)
      AND m.Type = 3
      AND m.Permission LIKE 's5InventoryInbound:%')
) AS ok_uat_tenant_not_granted;

-- 6) UAT_FUNCTION_USER（精确 RoleId 838258976469061，租户 838257186181189）无任何该权限
SELECT IF(
  (SELECT COUNT(*) FROM SysRole
    WHERE Id = 838258976469061 AND TenantId = 838257186181189) = 0,
  1,
  (SELECT COUNT(*) = 0
     FROM SysRoleMenu rm
     JOIN SysMenu m ON m.Id = rm.MenuId
    WHERE rm.RoleId = 838258976469061
      AND m.Type = 3
      AND m.Permission LIKE 's5InventoryInbound:%')
) AS ok_uat_function_user_has_none;

-- 7) 权限未扩散到**任何**角色（本批刻意不做 SysRoleMenu 授权）
SELECT (
  SELECT COUNT(*) = 0
    FROM SysRoleMenu rm
    JOIN SysMenu m ON m.Id = rm.MenuId
   WHERE m.Type = 3 AND m.Permission LIKE 's5InventoryInbound:%'
) AS ok_no_role_holds_permission;