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

-- ============================================================================
-- S0-UAT-DEMO-MASTER-DATA-CLOSURE-1 · VERIFY
--
-- ⚠️ 执行契约（AutoVersionUpdate.RunVerifyScriptIfExists）：
--    每条语句经 db.Ado.SqlQuerySingle<object>(statement) 执行，取「首行首列」，
--    并要求 IsTruthy(result) == true，否则抛错、整个 migration 判 Failed。
--    因此本文件的硬性约束：
--      1) 禁止 SET @var —— SqlQuerySingle 对 SET 返回 null → 立即判失败；
--         租户 ID 一律内联子查询，每条语句自包含。
--      2) 每条语句必须返回「通过时为真」的标量（用 = / <=> 比较得 1/0），
--         不能返回「错误条数」（0 = 通过会被 IsTruthy 判成 false）。
--      3) 一条语句一个断言，不用多行 UNION 汇总。
--
-- 全部为 SELECT，无任何写操作。
-- PRE 阶段可手工执行本文件了解基线；POST / RERUN 由 AutoVersionUpdate 自动运行。
-- ============================================================================

-- ---------------------------------------------------------------------------
-- §1 目标计数（RERUN 后必须仍然成立 —— 这就是幂等的直接证据）
-- ---------------------------------------------------------------------------
SELECT COUNT(*) = 26 FROM EmployeeMaster        WHERE tenant_id = (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);
SELECT COUNT(*) = 13 FROM SuppMaster            WHERE tenant_id = (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);
SELECT COUNT(*) = 15 FROM EmpWorkDutyMaster     WHERE tenant_id = (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);
SELECT COUNT(*) = 57 FROM NbrTypeMaster         WHERE tenant_id = (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);
SELECT COUNT(*) = 20 FROM LocationShelfMaster   WHERE tenant_id = (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);
SELECT COUNT(*) =  8 FROM CostCtrMaster         WHERE tenant_id = (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);
SELECT COUNT(*) =  3 FROM S0OrderReviewCycle    WHERE tenant_id = (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);
SELECT COUNT(*) =  3 FROM S0ProductDesignCycle  WHERE tenant_id = (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);
SELECT COUNT(*) =  3 FROM S0MaterialPlanCycle   WHERE tenant_id = (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);
SELECT COUNT(*) =  3 FROM S0OrderScheduleCycle  WHERE tenant_id = (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);
SELECT COUNT(*) =  4 FROM S0CategoryLeadTime    WHERE tenant_id = (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);

-- 25 名员工有部门（19 复制 + 4 seed + 2 WB8 补正；WB8-UNB 仍无部门，属刻意保留）
SELECT COUNT(*) = 25 FROM EmployeeMaster WHERE tenant_id = (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 Department IS NOT NULL AND Department <> '';
-- 5 名 login-bound assignee
SELECT COUNT(*) =  5 FROM EmployeeMaster WHERE tenant_id = (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 sys_user_id IS NOT NULL;

-- 回归：本批不得改动的对象
SELECT COUNT(*) =  5 FROM CustMaster       WHERE tenant_id = (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);
SELECT COUNT(*) =  7 FROM DepartmentMaster WHERE tenant_id = (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);
SELECT COUNT(*) = 18 FROM LocationMaster   WHERE tenant_id = (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);
SELECT COUNT(*) = 20 FROM LineMaster       WHERE tenant_id = (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);
SELECT COUNT(*) = 56 FROM ItemMaster       WHERE tenant_id = (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);
SELECT COUNT(*) =  7 FROM WorkCtrMaster    WHERE tenant_id = (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);
SELECT COUNT(*) = 91 FROM NbrControl       WHERE tenant_id = (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);


-- ---------------------------------------------------------------------------
-- §2 逻辑 FK 校验
-- ---------------------------------------------------------------------------
-- 2.1 Employee → DepartmentMaster：无悬空
SELECT COUNT(*) = 0 FROM EmployeeMaster e LEFT JOIN DepartmentMaster d ON d.tenant_id=e.tenant_id AND d.Department=e.Department WHERE e.tenant_id=(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 e.Department IS NOT NULL AND e.Department<>'' AND d.RecID IS NULL;

-- 2.2 Employee → SysUser 且同租户：无悬空
SELECT COUNT(*) = 0 FROM EmployeeMaster e LEFT JOIN SysUser u ON u.Id=e.sys_user_id AND u.TenantId=e.tenant_id WHERE e.tenant_id=(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 e.sys_user_id IS NOT NULL AND u.Id IS NULL;

-- 2.3 srm_purchase → SuppMaster：PRE=3 悬空 → POST 必须 0
SELECT COUNT(DISTINCT p.supplier_number) = 0 FROM srm_purchase p LEFT JOIN SuppMaster s ON s.tenant_id=p.tenant_id AND s.Supp=p.supplier_number WHERE p.tenant_id=(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 s.RecID IS NULL;

-- 2.4 WorkCtrMaster → DepartmentMaster：PRE=7 悬空 → POST 必须 0
SELECT COUNT(*) = 0 FROM WorkCtrMaster w LEFT JOIN DepartmentMaster d ON d.tenant_id=w.tenant_id AND d.Department=w.Department WHERE w.tenant_id=(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 d.RecID IS NULL;

-- 2.5 EmpWorkDuty → Employee / Location / Item：无悬空
SELECT COUNT(*) = 0 FROM EmpWorkDutyMaster k LEFT JOIN EmployeeMaster e ON e.tenant_id=k.tenant_id AND e.Domain=k.Domain AND e.Employee=k.Employee LEFT JOIN LocationMaster m ON m.tenant_id=k.tenant_id AND m.location=k.Location LEFT JOIN ItemMaster i ON i.tenant_id=k.tenant_id AND i.ItemNum=k.ItemNum1 WHERE k.tenant_id=(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 (e.RecID IS NULL OR m.RecID IS NULL OR i.RecID IS NULL);

-- 2.6 LocationShelf → LocationMaster（shelf.domain_code ↔ LocationMaster.Domain）：无悬空
SELECT COUNT(*) = 0 FROM LocationShelfMaster s LEFT JOIN LocationMaster l ON l.tenant_id=s.tenant_id AND l.Domain=s.domain_code AND l.location=s.location WHERE s.tenant_id=(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 l.RecID IS NULL;

-- 2.7 NbrControl → NbrTypeMaster：期望剩 42 条悬空（源租户仅覆盖 49/91，其余全库无定义，本批不造数）
SELECT COUNT(*) = 42 FROM NbrControl n LEFT JOIN NbrTypeMaster t2 ON t2.tenant_id=n.tenant_id AND t2.NbrType=n.NbrType WHERE n.tenant_id=(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 t2.RecID IS NULL;

-- 2.8 ProductDesignCycle.item_type → ItemMaster.ItemType：无悬空
SELECT COUNT(*) = 0 FROM S0ProductDesignCycle c WHERE c.tenant_id=(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 NOT EXISTS (SELECT 1 FROM ItemMaster i WHERE i.tenant_id=c.tenant_id AND i.ItemType=c.item_type);

-- 2.9 七部门 7/7 均至少 1 名员工
SELECT COUNT(*) = 0 FROM (SELECT d.Department FROM DepartmentMaster d LEFT JOIN EmployeeMaster e ON e.tenant_id=d.tenant_id AND e.Department=d.Department WHERE d.tenant_id=(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) GROUP BY d.Department HAVING COUNT(e.RecID)=0) z;

-- 2.10 五名 login-bound assignee 均有合法部门
SELECT COUNT(*) = 5 FROM SysUser u JOIN EmployeeMaster e ON e.sys_user_id=u.Id AND e.tenant_id=u.TenantId JOIN DepartmentMaster d ON d.tenant_id=e.tenant_id AND d.Department=e.Department WHERE u.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 u.Account IN ('UATPlanA','UATPurchaseA','UATWarehouseA','UATQualityA','UATExceptionA');

-- 2.11 WB8-UNB 保持未绑定（WP-WB8「未绑定应仅告警」用例的 fixture，不得被本批污染）
SELECT COUNT(*) = 1 FROM EmployeeMaster WHERE tenant_id=(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 Employee='WB8-UNB' AND sys_user_id IS NULL;


-- ---------------------------------------------------------------------------
-- §3 作用域纯净（新增/受影响表不得出现 0 / 1 / 默认租户 / NULL）
-- ---------------------------------------------------------------------------
SELECT COUNT(*) = 0 FROM EmployeeMaster      WHERE tenant_id IS NULL OR tenant_id IN (0,1,1300000000001);
SELECT COUNT(*) = 0 FROM SuppMaster          WHERE tenant_id IS NULL OR tenant_id IN (0,1,1300000000001);
SELECT COUNT(*) = 0 FROM EmpWorkDutyMaster   WHERE tenant_id IS NULL OR tenant_id IN (0,1,1300000000001);
SELECT COUNT(*) = 0 FROM NbrTypeMaster       WHERE tenant_id IS NULL OR tenant_id IN (0,1,1300000000001);
SELECT COUNT(*) = 0 FROM LocationShelfMaster WHERE tenant_id IS NULL OR tenant_id IN (0,1,1300000000001);
SELECT COUNT(*) = 0 FROM CostCtrMaster       WHERE tenant_id IS NULL OR tenant_id IN (0,1,1300000000001);

-- 本批新增行（Domain='8010'）的作用域三件套必须全部落在目标口径（company/factory = 部门锚）
SELECT COUNT(*) = 0 FROM EmployeeMaster e WHERE e.tenant_id=(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 e.Domain='8010' AND (e.company_ref_id <> (SELECT d.company_ref_id FROM DepartmentMaster d WHERE d.tenant_id=e.tenant_id LIMIT 1) OR e.factory_ref_id <> (SELECT d.factory_ref_id FROM DepartmentMaster d WHERE d.tenant_id=e.tenant_id LIMIT 1));
-- 供应商同理
SELECT COUNT(*) = 0 FROM SuppMaster s WHERE s.tenant_id=(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 (s.company_ref_id <> (SELECT d.company_ref_id FROM DepartmentMaster d WHERE d.tenant_id=s.tenant_id LIMIT 1) OR s.factory_ref_id <> (SELECT d.factory_ref_id FROM DepartmentMaster d WHERE d.tenant_id=s.tenant_id LIMIT 1));


-- ---------------------------------------------------------------------------
-- §4 源租户零变更守卫（标记式不变量，非绝对总数）
--
--   ⚠️ 刻意不写「源租户当前总数 = 464 / 3185 / …」这类硬编码常量：
--      源租户会被其它合法批次正常增长，把它固化成 migration 契约，会在
--      未来的 fresh environment / 延迟执行场景下产生误报。
--
--   本节改用「本批次写入标记」作为稳定不变量：本批所有写入一律带
--   CreateUser / create_user = 'UAT_DEMO_SEED'，且 tenant_id 恒为 @dst。
--   若任何一条带本批标记的行出现在源租户，即证明发生了越界写入。
--
--   源租户「行数完全未变」的运行时证据由独立的
--   WIP-S0-UAT-CLOSURE1.snapshot.sql 在 PRE / POST 两次执行后比对给出，
--   不进入本永久 verify 契约。
-- ---------------------------------------------------------------------------
SELECT COUNT(*) = 0 FROM EmployeeMaster       WHERE tenant_id <> (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 CreateUser  = 'UAT_DEMO_SEED';
SELECT COUNT(*) = 0 FROM SuppMaster           WHERE tenant_id <> (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 CreateUser  = 'UAT_DEMO_SEED';
SELECT COUNT(*) = 0 FROM NbrTypeMaster        WHERE tenant_id <> (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 CreateUser  = 'UAT_DEMO_SEED';
SELECT COUNT(*) = 0 FROM CostCtrMaster        WHERE tenant_id <> (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 CreateUser  = 'UAT_DEMO_SEED';
SELECT COUNT(*) = 0 FROM EmpWorkDutyMaster    WHERE tenant_id <> (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 CreateUser  = 'UAT_DEMO_SEED';
SELECT COUNT(*) = 0 FROM LocationShelfMaster  WHERE tenant_id <> (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 create_user = 'UAT_DEMO_SEED';
SELECT COUNT(*) = 0 FROM S0OrderReviewCycle   WHERE tenant_id <> (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 create_user = 'UAT_DEMO_SEED';
SELECT COUNT(*) = 0 FROM S0ProductDesignCycle WHERE tenant_id <> (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 create_user = 'UAT_DEMO_SEED';
SELECT COUNT(*) = 0 FROM S0MaterialPlanCycle  WHERE tenant_id <> (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 create_user = 'UAT_DEMO_SEED';
SELECT COUNT(*) = 0 FROM S0OrderScheduleCycle WHERE tenant_id <> (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 create_user = 'UAT_DEMO_SEED';
SELECT COUNT(*) = 0 FROM S0CategoryLeadTime   WHERE tenant_id <> (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 create_user = 'UAT_DEMO_SEED';
-- 本批 UPDATE 同样只允许落在目标租户
SELECT COUNT(*) = 0 FROM EmployeeMaster WHERE tenant_id <> (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 UpdateUser = 'UAT_DEMO_SEED';
SELECT COUNT(*) = 0 FROM WorkCtrMaster  WHERE tenant_id <> (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 UpdateUser = 'UAT_DEMO_SEED';


-- ---------------------------------------------------------------------------
-- §5 业务键唯一性（rerun 未产生重复的直接证据）
-- ---------------------------------------------------------------------------
SELECT COUNT(*) = 0 FROM (SELECT Domain, Employee FROM EmployeeMaster WHERE tenant_id=(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) GROUP BY Domain, Employee HAVING COUNT(*)>1) a;
SELECT COUNT(*) = 0 FROM (SELECT factory_ref_id, Supp FROM SuppMaster WHERE tenant_id=(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) GROUP BY factory_ref_id, Supp HAVING COUNT(*)>1) b;
SELECT COUNT(*) = 0 FROM (SELECT Domain, NbrClass, NbrType FROM NbrTypeMaster WHERE tenant_id=(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) GROUP BY Domain, NbrClass, NbrType HAVING COUNT(*)>1) c;
SELECT COUNT(*) = 0 FROM (SELECT Domain, CostCtr FROM CostCtrMaster WHERE tenant_id=(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) GROUP BY Domain, CostCtr HAVING COUNT(*)>1) d;
SELECT COUNT(*) = 0 FROM (SELECT Domain, Employee, Duty, Location FROM EmpWorkDutyMaster WHERE tenant_id=(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) GROUP BY Domain, Employee, Duty, Location HAVING COUNT(*)>1) e;
SELECT COUNT(*) = 0 FROM (SELECT domain_code, location, inv_shelf FROM LocationShelfMaster WHERE tenant_id=(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) GROUP BY domain_code, location, inv_shelf HAVING COUNT(*)>1) f;
SELECT COUNT(*) = 0 FROM (SELECT factory_ref_id, order_type FROM S0OrderReviewCycle WHERE tenant_id=(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) GROUP BY factory_ref_id, order_type HAVING COUNT(*)>1) g;