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

-- ===========================================================================
-- LINE-POST TENANT-UK CLOSURE VERIFY —— 全部 SELECT 真值标量断言，无 SET / 无用户变量
-- ===========================================================================

-- ---------- §1 索引已含 tenant_id（schema correctness 的核心断言） ----------
SELECT COUNT(*) = 1 FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'LineSkillMaster'
    AND INDEX_NAME = 'IX_LineSkillMaster' AND COLUMN_NAME = 'tenant_id';
SELECT COUNT(*) = 1 FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'LineSkillDetail'
    AND INDEX_NAME = 'IX_LineSkillDetail' AND COLUMN_NAME = 'tenant_id';

-- 索引仍是 UNIQUE
SELECT MIN(NON_UNIQUE) = 0 FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'LineSkillMaster' AND INDEX_NAME = 'IX_LineSkillMaster';
SELECT MIN(NON_UNIQUE) = 0 FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'LineSkillDetail' AND INDEX_NAME = 'IX_LineSkillDetail';

-- 索引列数与顺序（tenant_id 必须在第 1 位）
SELECT COUNT(*) = 5 FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'LineSkillMaster' AND INDEX_NAME = 'IX_LineSkillMaster';
SELECT COUNT(*) = 6 FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'LineSkillDetail' AND INDEX_NAME = 'IX_LineSkillDetail';
SELECT COUNT(*) = 1 FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'LineSkillMaster'
    AND INDEX_NAME = 'IX_LineSkillMaster' AND COLUMN_NAME = 'tenant_id' AND SEQ_IN_INDEX = 1;
SELECT COUNT(*) = 1 FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'LineSkillDetail'
    AND INDEX_NAME = 'IX_LineSkillDetail' AND COLUMN_NAME = 'tenant_id' AND SEQ_IN_INDEX = 1;

-- ---------- §2 目标行数 ----------
SELECT COUNT(*) = 11 FROM LineSkillMaster
  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(*) = 11 FROM LineSkillDetail
  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);

-- ---------- §3 Site 必须全为 NULL（不得被写成租户/工厂/组织码） ----------
SELECT COUNT(*) = 11 FROM LineSkillMaster
  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 Site IS NULL;
SELECT COUNT(*) = 11 FROM LineSkillDetail
  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 Site IS NULL;

-- ---------- §4 FK 闭合 ----------
SELECT COUNT(*) = 0 FROM LineSkillMaster m
  WHERE m.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 LineMaster l WHERE l.tenant_id = m.tenant_id AND l.Line = m.ProdLine);
SELECT COUNT(*) = 0 FROM LineSkillDetail d
  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)
    AND NOT EXISTS (SELECT 1 FROM LineSkillMaster m WHERE m.RecID = d.LineSkillRecID AND m.tenant_id = d.tenant_id);
SELECT COUNT(*) = 0 FROM LineSkillDetail d
  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)
    AND NOT EXISTS (SELECT 1 FROM PersonSkill p WHERE p.Id = d.PersonSkillId AND p.tenant_id = d.tenant_id);

-- ---------- §5 scope / domain ----------
SELECT COUNT(*) = 11 FROM LineSkillMaster
  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 Domain = '8010' AND JOBNo = '102';
SELECT COUNT(*) = 11 FROM LineSkillDetail
  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 Domain = '8010' AND JOBNo = '102' AND SkillNo = '102';

-- ---------- §6 业务键唯一 ----------
SELECT COUNT(*) = 0 FROM (
  SELECT ProdLine FROM LineSkillMaster
  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 ProdLine HAVING COUNT(*) > 1) x;
SELECT COUNT(*) = 0 FROM (
  SELECT LineSkillRecID, PersonSkillId FROM LineSkillDetail
  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 LineSkillRecID, PersonSkillId HAVING COUNT(*) > 1) x;

-- ---------- §7 源与他租户零变更 ----------
SELECT COUNT(*) = 40 FROM LineSkillMaster WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='AIDOP' AND t.Status=1 LIMIT 1);
SELECT COUNT(*) = 40 FROM LineSkillDetail WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='AIDOP' AND t.Status=1 LIMIT 1);
SELECT COUNT(*) = 6 FROM LineSkillMaster WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='AIDOPB' AND t.Status=1 LIMIT 1);
SELECT COUNT(*) = 210 FROM LineSkillDetail WHERE tenant_id = (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id=t.OrgId WHERE o.Code='AIDOPB' AND t.Status=1 LIMIT 1);

-- ---------- §8 既有 S0 回归 ----------
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(*) = 5 FROM PersonSkill 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(*) = 10 FROM EmpSkills 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);