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

-- ============================================================================
-- 1.0.434 verify：UAT 租户 838257186181189 的组织引用必须已全部迁回本租户组织，
--                 且租户 797403760988229 的 167 行必须一行未动。
--
-- 约定（AutoVersionUpdate.RunVerifyScriptIfExists）：每条语句取「首行首列」当布尔断言，
--   必须为真；返回 0 行会得到 NULL 判假并中断启动。故每条断言一律返回标量真值，
--   且不使用 SET（SET 不返回行 → 必然判假）。
--
-- 环境容错：目标租户/组织不存在的库上主脚本为空操作，断言以「该库本就没有这些数据」
--   的形式自然为真（COUNT=0 / 比较成立），无需额外短路。
--
-- CROSS-TENANT 统计口径：与 B-1 / B-2A 审计一致——业务行 tenant_id 与其
--   company_ref_id/factory_ref_id 所指 SysOrg.TenantId 不一致即计一行。
--   统计范围为**实测存在跨租户引用的 6 张表**（其余 21 张含 company_ref_id 的表
--   在本脚本编写时实测均为 0，见 V5 注释）。
-- ============================================================================

-- V1：目标组织确实属于 UAT 租户（防止把业务数据指向他租户组织）
SELECT (SELECT COUNT(*) FROM `SysOrg`
         WHERE `Id` = 838257186320453 AND `TenantId` = 838257186181189) = 1 AS v1_target_org_belongs_to_uat;

-- V2：UAT 租户在 NbrControl 中不再引用这两个 Demo 租户固定组织 ID
SELECT (SELECT COUNT(*) FROM `NbrControl`
         WHERE `tenant_id` = 838257186181189
           AND (`company_ref_id` IN (1329900200001, 1329900200002)
             OR `factory_ref_id` IN (1329900200001, 1329900200002))) = 0 AS v2_uat_no_demo_org_ref;

-- V3：UAT 租户 NbrControl 全部行的组织归属正确（company 与 factory 都必须同租户）
SELECT (SELECT COUNT(*) FROM `NbrControl` t
          LEFT JOIN `SysOrg` oc ON oc.`Id` = t.`company_ref_id`
          LEFT JOIN `SysOrg` ofa ON ofa.`Id` = t.`factory_ref_id`
         WHERE t.`tenant_id` = 838257186181189
           AND ((oc.`Id` IS NOT NULL AND oc.`TenantId` <> t.`tenant_id`)
             OR (ofa.`Id` IS NOT NULL AND ofa.`TenantId` <> t.`tenant_id`))) = 0 AS v3_uat_org_scope_consistent;

-- V4：租户 797403760988229 的 167 行跨租户引用**完全未变**（本批边界的关键断言）
SELECT (
  SELECT SUM(n) FROM (
    SELECT COUNT(*) AS n FROM `NbrControl` t LEFT JOIN `SysOrg` oc ON oc.`Id`=t.`company_ref_id` LEFT JOIN `SysOrg` ofa ON ofa.`Id`=t.`factory_ref_id`
      WHERE t.`tenant_id`=797403760988229 AND ((oc.`Id` IS NOT NULL AND oc.`TenantId`<>t.`tenant_id`) OR (ofa.`Id` IS NOT NULL AND ofa.`TenantId`<>t.`tenant_id`))
    UNION ALL
    SELECT COUNT(*) FROM `NbrTypeMaster` t LEFT JOIN `SysOrg` oc ON oc.`Id`=t.`company_ref_id` LEFT JOIN `SysOrg` ofa ON ofa.`Id`=t.`factory_ref_id`
      WHERE t.`tenant_id`=797403760988229 AND ((oc.`Id` IS NOT NULL AND oc.`TenantId`<>t.`tenant_id`) OR (ofa.`Id` IS NOT NULL AND ofa.`TenantId`<>t.`tenant_id`))
    UNION ALL
    SELECT COUNT(*) FROM `LocationShelfMaster` t LEFT JOIN `SysOrg` oc ON oc.`Id`=t.`company_ref_id` LEFT JOIN `SysOrg` ofa ON ofa.`Id`=t.`factory_ref_id`
      WHERE t.`tenant_id`=797403760988229 AND ((oc.`Id` IS NOT NULL AND oc.`TenantId`<>t.`tenant_id`) OR (ofa.`Id` IS NOT NULL AND ofa.`TenantId`<>t.`tenant_id`))
    UNION ALL
    SELECT COUNT(*) FROM `EmployeeMaster` t LEFT JOIN `SysOrg` oc ON oc.`Id`=t.`company_ref_id` LEFT JOIN `SysOrg` ofa ON ofa.`Id`=t.`factory_ref_id`
      WHERE t.`tenant_id`=797403760988229 AND ((oc.`Id` IS NOT NULL AND oc.`TenantId`<>t.`tenant_id`) OR (ofa.`Id` IS NOT NULL AND ofa.`TenantId`<>t.`tenant_id`))
    UNION ALL
    SELECT COUNT(*) FROM `CostCtrMaster` t LEFT JOIN `SysOrg` oc ON oc.`Id`=t.`company_ref_id` LEFT JOIN `SysOrg` ofa ON ofa.`Id`=t.`factory_ref_id`
      WHERE t.`tenant_id`=797403760988229 AND ((oc.`Id` IS NOT NULL AND oc.`TenantId`<>t.`tenant_id`) OR (ofa.`Id` IS NOT NULL AND ofa.`TenantId`<>t.`tenant_id`))
    UNION ALL
    SELECT COUNT(*) FROM `DepartmentMaster` t LEFT JOIN `SysOrg` oc ON oc.`Id`=t.`company_ref_id` LEFT JOIN `SysOrg` ofa ON ofa.`Id`=t.`factory_ref_id`
      WHERE t.`tenant_id`=797403760988229 AND ((oc.`Id` IS NOT NULL AND oc.`TenantId`<>t.`tenant_id`) OR (ofa.`Id` IS NOT NULL AND ofa.`TenantId`<>t.`tenant_id`))
  ) x
) = 167 AS v4_other_tenant_167_untouched;

-- V5：全库 CROSS-TENANT 迁移后应恰为 167（= 258 − 91）。
--     统计范围为编写时实测存在跨租户引用的 6 张表；其余 21 张含 company_ref_id 的表当时实测为 0。
--     **剩余 167 行是本批有意保留的范围，不是失败。**
SELECT (
  SELECT SUM(n) FROM (
    SELECT COUNT(*) AS n FROM `NbrControl` t LEFT JOIN `SysOrg` oc ON oc.`Id`=t.`company_ref_id` LEFT JOIN `SysOrg` ofa ON ofa.`Id`=t.`factory_ref_id`
      WHERE (oc.`Id` IS NOT NULL AND oc.`TenantId`<>t.`tenant_id`) OR (ofa.`Id` IS NOT NULL AND ofa.`TenantId`<>t.`tenant_id`)
    UNION ALL
    SELECT COUNT(*) FROM `NbrTypeMaster` t LEFT JOIN `SysOrg` oc ON oc.`Id`=t.`company_ref_id` LEFT JOIN `SysOrg` ofa ON ofa.`Id`=t.`factory_ref_id`
      WHERE (oc.`Id` IS NOT NULL AND oc.`TenantId`<>t.`tenant_id`) OR (ofa.`Id` IS NOT NULL AND ofa.`TenantId`<>t.`tenant_id`)
    UNION ALL
    SELECT COUNT(*) FROM `LocationShelfMaster` t LEFT JOIN `SysOrg` oc ON oc.`Id`=t.`company_ref_id` LEFT JOIN `SysOrg` ofa ON ofa.`Id`=t.`factory_ref_id`
      WHERE (oc.`Id` IS NOT NULL AND oc.`TenantId`<>t.`tenant_id`) OR (ofa.`Id` IS NOT NULL AND ofa.`TenantId`<>t.`tenant_id`)
    UNION ALL
    SELECT COUNT(*) FROM `EmployeeMaster` t LEFT JOIN `SysOrg` oc ON oc.`Id`=t.`company_ref_id` LEFT JOIN `SysOrg` ofa ON ofa.`Id`=t.`factory_ref_id`
      WHERE (oc.`Id` IS NOT NULL AND oc.`TenantId`<>t.`tenant_id`) OR (ofa.`Id` IS NOT NULL AND ofa.`TenantId`<>t.`tenant_id`)
    UNION ALL
    SELECT COUNT(*) FROM `CostCtrMaster` t LEFT JOIN `SysOrg` oc ON oc.`Id`=t.`company_ref_id` LEFT JOIN `SysOrg` ofa ON ofa.`Id`=t.`factory_ref_id`
      WHERE (oc.`Id` IS NOT NULL AND oc.`TenantId`<>t.`tenant_id`) OR (ofa.`Id` IS NOT NULL AND ofa.`TenantId`<>t.`tenant_id`)
    UNION ALL
    SELECT COUNT(*) FROM `DepartmentMaster` t LEFT JOIN `SysOrg` oc ON oc.`Id`=t.`company_ref_id` LEFT JOIN `SysOrg` ofa ON ofa.`Id`=t.`factory_ref_id`
      WHERE (oc.`Id` IS NOT NULL AND oc.`TenantId`<>t.`tenant_id`) OR (ofa.`Id` IS NOT NULL AND ofa.`TenantId`<>t.`tenant_id`)
  ) x
) = 167 AS v5_global_cross_tenant_167;

-- V6：UAT NbrControl 行数守恒（迁移只改 org 列，不增删行）
SELECT (SELECT COUNT(*) FROM `NbrControl` WHERE `tenant_id` = 838257186181189) = 91 AS v6_uat_rowcount_preserved;