-- 1.0.454 校验：S7 FQC UAT Golden Fixture · UATA-A01
-- 每条必须返回真值，否则 AutoVersionUpdate 判定该版本失败。
-- 全部为纯 SELECT（runner 用 SqlQuerySingle 取首行首列，不支持 SET 变量）。

-- ---- 1 · 主表恰好 1 条 ----
SELECT COUNT(*) = 1 FROM qms_ccjygf
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 = 'UATA-FQC-3121C0035' AND bb = 'A.0';

-- ---- 2 · 明细恰好 4 条 ----
SELECT COUNT(*) = 4 FROM qms_ccjygfzb z
JOIN qms_ccjygf g ON g.id = z.glid AND g.tenant_id = z.tenant_id
WHERE z.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 g.wjbh = 'UATA-FQC-3121C0035' AND g.bb = 'A.0';

-- ---- 3 · 桥表恰好 1 条有效映射（运行链路可消费状态） ----
SELECT COUNT(*) = 1 FROM ado_s7_fqc_spec_material_map
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 material_code = '3121C0035' AND status = 1 AND match_status IN ('EXACT', 'MANUAL');

-- ---- 4 · 端到端关联链：map → qms_ccjygf → 4 条 qms_ccjygfzb（复刻 ResolveInternalAsync 的 JOIN 条件） ----
SELECT COUNT(*) = 4 FROM ado_s7_fqc_spec_material_map m
JOIN qms_ccjygf   g ON g.id = m.jygfid AND g.tenant_id = m.tenant_id
JOIN qms_ccjygfzb z ON z.glid = g.id  AND z.tenant_id = g.tenant_id
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 m.material_code = '3121C0035' AND m.status = 1 AND m.match_status IN ('EXACT', 'MANUAL');

-- ---- 5 · 唯一候选：该物料不得解析出多个检规（否则 ChooseSpec 会以歧义拒绝） ----
SELECT COUNT(DISTINCT m.jygfid) = 1 FROM ado_s7_fqc_spec_material_map m
JOIN qms_ccjygf g ON g.id = m.jygfid AND g.tenant_id = m.tenant_id
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 m.material_code = '3121C0035' AND m.status = 1 AND m.match_status IN ('EXACT', 'MANUAL');

-- ---- 6 · lrlx 分布 = 2 NUMERIC + 2 NON_NUMERIC，且无非法值（FqcApplyService.cs:237） ----
SELECT SUM(z.lrlx = 'NUMERIC') = 2 AND SUM(z.lrlx = 'NON_NUMERIC') = 2 AND SUM(z.lrlx NOT IN ('NUMERIC','NON_NUMERIC') OR z.lrlx IS NULL) = 0
FROM qms_ccjygfzb z JOIN qms_ccjygf g ON g.id = z.glid AND g.tenant_id = z.tenant_id
WHERE z.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 g.wjbh = 'UATA-FQC-3121C0035' AND g.bb = 'A.0';

-- ---- 7 · NUMERIC 两条上下限齐全且 下限 < 上限；NON_NUMERIC 两条上下限必须为空 ----
SELECT COUNT(*) = 0 FROM qms_ccjygfzb z JOIN qms_ccjygf g ON g.id = z.glid AND g.tenant_id = z.tenant_id
WHERE z.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 g.wjbh = 'UATA-FQC-3121C0035' AND g.bb = 'A.0'
  AND ( (z.lrlx = 'NUMERIC'     AND (z.UpperToleranceLimit IS NULL OR z.LowerToleranceLimit IS NULL
                                     OR CAST(z.LowerToleranceLimit AS DECIMAL(18,4)) >= CAST(z.UpperToleranceLimit AS DECIMAL(18,4))))
     OR (z.lrlx = 'NON_NUMERIC' AND (z.UpperToleranceLimit IS NOT NULL OR z.LowerToleranceLimit IS NOT NULL)) );

-- ---- 8 · 每条明细都有非空检验项目（FqcApplyService.cs:236 会因空 jyxm 拒绝生成） ----
SELECT COUNT(*) = 0 FROM qms_ccjygfzb z JOIN qms_ccjygf g ON g.id = z.glid AND g.tenant_id = z.tenant_id
WHERE z.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 g.wjbh = 'UATA-FQC-3121C0035' AND g.bb = 'A.0'
  AND (z.jyxm IS NULL OR TRIM(z.jyxm) = '');

-- ---- 9 · 跨租户回归：3121C0035 的桥表映射只允许存在于 UAT 租户 ----
SELECT COUNT(*) = 0 FROM ado_s7_fqc_spec_material_map
WHERE material_code = '3121C0035'
  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);

-- ---- 10 · 跨租户回归：UATA-FQC-* 检规不得落到任何其他租户 ----
SELECT COUNT(*) = 0 FROM qms_ccjygf
WHERE wjbh LIKE 'UATA-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);

-- ---- 11 · 797 未被污染：检规主表仍 68 / 明细仍 1053 / 桥表仍 1554 ----
SELECT (SELECT COUNT(*) FROM qms_ccjygf                   WHERE tenant_id = 797403760988229) = 68
   AND (SELECT COUNT(*) FROM qms_ccjygfzb                 WHERE tenant_id = 797403760988229) = 1053
   AND (SELECT COUNT(*) FROM ado_s7_fqc_spec_material_map WHERE tenant_id = 797403760988229) = 1554;

-- ---- 12 · 本轮不碰 qms_fqcjygf：UAT 侧仍恰好 3 行（归并留待后续独立批次） ----
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);

-- ---- 13 · 幂等自证：重复执行不会产生第二条主表/桥表行（唯一性即幂等成立的充分证据） ----
SELECT COUNT(*) = 0 FROM (
  SELECT tenant_id, wjbh, bb FROM qms_ccjygf WHERE wjbh = 'UATA-FQC-3121C0035'
  GROUP BY tenant_id, wjbh, bb HAVING COUNT(*) > 1
) d;