-- ============================================================================
-- S0 STEP 2A QUALITY · VERIFY
--
-- 执行契约（AutoVersionUpdate.RunVerifyScriptIfExists）：
--   每条语句经 SqlQuerySingle<object> 取首行首列并要求 IsTruthy == true。因此：
--     ① 禁止 SET @var（返回 null 即判失败）→ 租户一律内联子查询，每条语句自包含
--     ② 每条语句返回「通过为真」的标量（用 = 比较得 1/0），不得返回「错误条数」
--     ③ 一条语句一个断言，不用多行 UNION 汇总
--     ④ 文件必须以最后一条 SQL 的 ; 结束，末尾不得残留 comment-only 块
--
-- ⚠️ 作用域边界（CTO 2026-08-28 修正）：
--   本文件**不得**断言「全物理表不存在 tenant_id IN (0,1,1300000000001)」——
--   默认租户存在 47 行历史 [AI测试] 质量数据、797 侧 qms_gcjygf 另有 3 行，
--   均属另批 S0-DEFAULT-TENANT-SEED-CONTAMINATION-CLEANUP-1，本批 DO NOT TOUCH。
--   §4 改为 TARGET PAYLOAD SCOPE：只验证本批 payload 在 @dst 精确存在、且未错落他租户。
--   源租户 / 默认租户 MUTATION = 0 由 PRE/POST snapshot 证明，不进本永久契约。
-- ============================================================================

-- ---------------------------------------------------------------------------
-- §1 目标计数（14 条）
-- ---------------------------------------------------------------------------
SELECT COUNT(*) = 5 FROM qms_inspection_method  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 qms_inspectionitems    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 qms_inspectioninstru   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 qms_inspectionfreq     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 qms_sampscheme         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 qms_quality_base_type  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 qms_inspectioncrit     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 qms_inspectionstd      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 qms_inspectpro         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 qms_jygf               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 qms_gcjygf             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 qms_fqcjygf            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 qms_oqcjygf            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 qms_lymjbmd            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 闭合（6 条）
-- ---------------------------------------------------------------------------
-- 2.1 items → method 无悬空
SELECT COUNT(*) = 0 FROM qms_inspectionitems i LEFT JOIN qms_inspection_method m ON m.id = i.FCHECKMETHOD AND m.tenant_id = i.tenant_id WHERE i.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 i.FCHECKMETHOD IS NOT NULL AND m.id IS NULL;
-- 2.2 items → method 已映射 5 条，且指向同租户 method
SELECT COUNT(*) = 5 FROM qms_inspectionitems i JOIN qms_inspection_method m ON m.id = i.FCHECKMETHOD AND m.tenant_id = i.tenant_id WHERE i.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.3 语义正确：QI-001→QM-003 · QI-002→QM-001 · QI-003→QM-003 · QI-004→QM-005 · QI-005→QM-001
SELECT COUNT(*) = 5 FROM qms_inspectionitems i JOIN qms_inspection_method m ON m.id = i.FCHECKMETHOD AND m.tenant_id = i.tenant_id WHERE i.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 CONCAT(i.FNUMBER,'>',m.FNUMBER) IN ('QI-001>QM-003','QI-002>QM-001','QI-003>QM-003','QI-004>QM-005','QI-005>QM-001');
-- 2.4 jygf / gcjygf / fqcjygf / oqcjygf → ItemMaster 无悬空
SELECT COUNT(*) = 0 FROM (SELECT wlbm, tenant_id FROM qms_jygf UNION ALL SELECT wlbm, tenant_id FROM qms_gcjygf UNION ALL SELECT wlbm, tenant_id FROM qms_fqcjygf UNION ALL SELECT wlbm, tenant_id FROM qms_oqcjygf) q LEFT JOIN ItemMaster i ON i.tenant_id = q.tenant_id AND i.ItemNum = q.wlbm WHERE q.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 q.wlbm IS NOT NULL AND i.RecID IS NULL;
-- 2.5 lymjbmd → SuppMaster 无悬空
SELECT COUNT(*) = 0 FROM qms_lymjbmd l LEFT JOIN SuppMaster s ON s.tenant_id = l.tenant_id AND s.Supp = l.gysbm WHERE l.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.gysbm IS NOT NULL AND s.RecID IS NULL;
-- 2.6 lymjbmd → ItemMaster 无悬空
SELECT COUNT(*) = 0 FROM qms_lymjbmd l LEFT JOIN ItemMaster i ON i.tenant_id = l.tenant_id AND i.ItemNum = l.wlbm WHERE l.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.wlbm IS NOT NULL AND i.RecID IS NULL;

-- ---------------------------------------------------------------------------
-- §3 组织引用改写（3 条）：目标租户内不得残留 AIDOP 根组织 Id
-- ---------------------------------------------------------------------------
SELECT COUNT(*) = 0 FROM qms_inspectioncrit 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 FCREATEORGID = (SELECT t2.OrgId FROM SysTenant t2 JOIN SysOrg o2 ON o2.Id=t2.OrgId WHERE o2.Code='AIDOP' AND t2.Status=1 LIMIT 1);
SELECT COUNT(*) = 0 FROM qms_inspectionstd  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 FCREATEORGID = (SELECT t2.OrgId FROM SysTenant t2 JOIN SysOrg o2 ON o2.Id=t2.OrgId WHERE o2.Code='AIDOP' AND t2.Status=1 LIMIT 1);
SELECT COUNT(*) = 0 FROM qms_inspectpro     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 (FCREATEORGID = (SELECT t2.OrgId FROM SysTenant t2 JOIN SysOrg o2 ON o2.Id=t2.OrgId WHERE o2.Code='AIDOP' AND t2.Status=1 LIMIT 1) OR FUSEORGID = (SELECT t3.OrgId FROM SysTenant t3 JOIN SysOrg o3 ON o3.Id=t3.OrgId WHERE o3.Code='AIDOP' AND t3.Status=1 LIMIT 1));

-- ---------------------------------------------------------------------------
-- §4 TARGET PAYLOAD SCOPE（14 条）
--     只验证「本批 payload 在 @dst 精确存在」+「CONSTRUCT payload 未错落他租户」。
--     COPY 类业务键在源租户 797 本就合法存在，故不做「全库唯一」断言。
-- ---------------------------------------------------------------------------
SELECT COUNT(*) = 5 FROM qms_inspection_method 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 FNUMBER IN ('QM-001','QM-002','QM-003','QM-004','QM-005');
SELECT COUNT(*) = 5 FROM qms_inspectionitems   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 FNUMBER IN ('QI-001','QI-002','QI-003','QI-004','QI-005');
SELECT COUNT(*) = 5 FROM qms_inspectioninstru  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 FNUMBER IN ('QG-001','QG-002','QG-003','QG-004','QG-005');
SELECT COUNT(*) = 4 FROM qms_inspectionfreq    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 FNUMBER IN ('JYPC-001','JYPC-002','JYPC-003','JYPC-004');
SELECT COUNT(*) = 4 FROM qms_sampscheme        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 FNUMBER IN ('QS-001','QS-002','QS-003','QS-004');
SELECT COUNT(*) = 4 FROM qms_quality_base_type 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 CONCAT(type_category,'|',type_code) IN ('检验类型|IQC','检验类型|PQC','不良分类|制成不良','判定结论|合格');
SELECT COUNT(*) = 3 FROM qms_inspectioncrit    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 FNUMBER IN ('JYYJ-001','JYYJ-002','JYYJ-003');
SELECT COUNT(*) = 3 FROM qms_inspectionstd     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 FNUMBER IN ('JYBZ-001','JYBZ-002','JYBZ-003');
SELECT COUNT(*) = 4 FROM qms_inspectpro        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 FNUMBER IN ('PLAN-FQC-001','PLAN-IQC-001','PLAN-OQC-001','PLAN-PQC-001');
-- jygf：7 行且 Y101-1-11 的 A.1 / X.1 两版必须共存
SELECT COUNT(*) = 7 FROM qms_jygf 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 CONCAT(wjbh,'|',bb) IN ('F03-1-21|A.0','F101-1-39|A.0','M05-1-19|A.4','U01-1-63|A.1','Y101-1-11|A.1','Y101-1-11|X.1','Y101-1-36|X.1');
SELECT COUNT(*) = 2 FROM qms_jygf 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 wjbh = 'Y101-1-11' AND bb IN ('A.1','X.1');
-- CONSTRUCT payload 必须只出现在 @dst
SELECT COUNT(*) = 0 FROM qms_gcjygf  WHERE wjbh LIKE 'UATD-GC-%'  AND 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(*) = 0 FROM qms_fqcjygf WHERE wjbh LIKE 'UATD-FQC-%' AND 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(*) = 0 FROM qms_oqcjygf WHERE wjbh LIKE 'UATD-OQC-%' AND 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);

-- ---------------------------------------------------------------------------
-- §5 逻辑业务键唯一性（14 条）
--     DB 层这 14 张表**没有任何业务唯一键**，本节是唯一的重复防线。
-- ---------------------------------------------------------------------------
SELECT COUNT(*) = 0 FROM (SELECT FNUMBER FROM qms_inspection_method 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 FNUMBER HAVING COUNT(*) > 1) a;
SELECT COUNT(*) = 0 FROM (SELECT FNUMBER FROM qms_inspectionitems   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 FNUMBER HAVING COUNT(*) > 1) b;
SELECT COUNT(*) = 0 FROM (SELECT FNUMBER FROM qms_inspectioninstru  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 FNUMBER HAVING COUNT(*) > 1) c;
SELECT COUNT(*) = 0 FROM (SELECT FNUMBER FROM qms_inspectionfreq    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 FNUMBER HAVING COUNT(*) > 1) d;
SELECT COUNT(*) = 0 FROM (SELECT FNUMBER FROM qms_sampscheme        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 FNUMBER HAVING COUNT(*) > 1) e;
SELECT COUNT(*) = 0 FROM (SELECT FNUMBER FROM qms_inspectioncrit    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 FNUMBER HAVING COUNT(*) > 1) f;
SELECT COUNT(*) = 0 FROM (SELECT FNUMBER FROM qms_inspectionstd     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 FNUMBER HAVING COUNT(*) > 1) g;
SELECT COUNT(*) = 0 FROM (SELECT FNUMBER FROM qms_inspectpro        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 FNUMBER HAVING COUNT(*) > 1) h;
SELECT COUNT(*) = 0 FROM (SELECT type_category, type_code FROM qms_quality_base_type 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 type_category, type_code HAVING COUNT(*) > 1) i;
SELECT COUNT(*) = 0 FROM (SELECT wjbh, bb FROM qms_jygf 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 wjbh, bb HAVING COUNT(*) > 1) j;
SELECT COUNT(*) = 0 FROM (SELECT wjbh FROM qms_gcjygf  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 wjbh HAVING COUNT(*) > 1) k;
SELECT COUNT(*) = 0 FROM (SELECT wjbh FROM qms_fqcjygf 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 wjbh HAVING COUNT(*) > 1) l;
SELECT COUNT(*) = 0 FROM (SELECT wjbh FROM qms_oqcjygf 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 wjbh HAVING COUNT(*) > 1) m;
SELECT COUNT(*) = 0 FROM (SELECT dim_type, IFNULL(gysbm,'') g2, IFNULL(wlbm,'') w2 FROM qms_lymjbmd 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 dim_type, g2, w2 HAVING COUNT(*) > 1) n;

-- ---------------------------------------------------------------------------
-- §6 污染隔离（2 条）：目标租户内不得出现历史 [AI测试] 痕迹
-- ---------------------------------------------------------------------------
SELECT COUNT(*) = 0 FROM qms_inspectionitems 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 (FNUMBER LIKE 'AI-%' OR FNAME LIKE '%AI测试%');
SELECT COUNT(*) = 0 FROM qms_gcjygf 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 (syxh LIKE '%AI测试%' OR fj LIKE '%AI测试%' OR wlbm LIKE '%AI测试%');

-- ---------------------------------------------------------------------------
-- §7 回归（3 条）：本批不得触碰的既有 S0 主数据
-- ---------------------------------------------------------------------------
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(*) = 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);
