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

-- ============================================================================
-- 1.0.529 校验 · S5 IQC Inspection Detail · Batch 1 Persistence Foundation
-- 约定：每条必须返回真值，否则 AutoVersionUpdate 判定该版本失败。
--       全部为纯 SELECT（runner 用 SqlQuerySingle 取首行首列，不支持 SET 变量）。
--
-- 覆盖 Batch 1 验收项 A/B/C/D/E/F：表存在 · 列逐项 · tenant-first 索引 · CHECK ·
--                                  qms_* 未改 · 无业务数据写入
-- ============================================================================

-- ---- A1 · 两张新表都存在 ----
SELECT COUNT(*) = 2 FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('ado_s5_iqc_insp_bill_item', 'ado_s5_iqc_insp_bill_item_sample');

-- ---- A2 · engine / collation 与仓库惯例一致 ----
SELECT COUNT(*) = 2 FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('ado_s5_iqc_insp_bill_item', 'ado_s5_iqc_insp_bill_item_sample')
  AND ENGINE = 'InnoDB' AND TABLE_COLLATION = 'utf8mb4_0900_ai_ci';

-- ---- B1 · item 表列数 = 24 ----
SELECT COUNT(*) = 24 FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ado_s5_iqc_insp_bill_item';

-- ---- B2 · sample 表列数 = 13 ----
SELECT COUNT(*) = 13 FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ado_s5_iqc_insp_bill_item_sample';

-- ---- B3 · method_tool 必须是 varchar(1000)（源 jygjjyff 实测 538 字符，500 会截断） ----
SELECT COUNT(*) = 1 FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ado_s5_iqc_insp_bill_item'
  AND COLUMN_NAME = 'method_tool'
  AND DATA_TYPE = 'varchar' AND CHARACTER_MAXIMUM_LENGTH = 1000;

-- ---- B4 · 其余快照列宽逐项 ----
SELECT COUNT(*) = 7 FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ado_s5_iqc_insp_bill_item'
  AND ((COLUMN_NAME = 'inspection_item'     AND CHARACTER_MAXIMUM_LENGTH = 500)
    OR (COLUMN_NAME = 'inspection_standard' AND CHARACTER_MAXIMUM_LENGTH = 1000)
    OR (COLUMN_NAME = 'image_category'      AND CHARACTER_MAXIMUM_LENGTH = 500)
    OR (COLUMN_NAME = 'sampling_scheme'     AND CHARACTER_MAXIMUM_LENGTH = 500)
    OR (COLUMN_NAME = 'spec_remark'         AND CHARACTER_MAXIMUM_LENGTH = 500)
    OR (COLUMN_NAME = 'upper_limit'         AND CHARACTER_MAXIMUM_LENGTH = 50)
    OR (COLUMN_NAME = 'lower_limit'         AND CHARACTER_MAXIMUM_LENGTH = 50));

-- ---- B5 · item 表 NOT NULL 业务列恰好是这 4 个（id 由 PK 保证，不计入） ----
SELECT COUNT(*) = 4 FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ado_s5_iqc_insp_bill_item'
  AND IS_NULLABLE = 'NO'
  AND COLUMN_NAME IN ('tenant_id', 'inspection_bill_id', 'source_spec_id', 'source_spec_item_id');

-- ---- B6 · seq 必须允许 NULL（源 qms_jygfzb.xh 实测 9 行为 NULL） ----
SELECT COUNT(*) = 1 FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ado_s5_iqc_insp_bill_item'
  AND COLUMN_NAME = 'seq' AND IS_NULLABLE = 'YES';

-- ---- B7 · sample 表 NOT NULL 列 ----
SELECT COUNT(*) = 5 FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ado_s5_iqc_insp_bill_item_sample'
  AND IS_NULLABLE = 'NO'
  AND COLUMN_NAME IN ('tenant_id', 'inspection_bill_id', 'item_id', 'sample_index', 'sample_value_raw');

-- ---- B8 · 两个 DECIMAL 真源列精度 = (18,6)，与 ado_s5_iqc_sqe_disposition 惯例一致 ----
SELECT COUNT(*) = 3 FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND ((TABLE_NAME = 'ado_s5_iqc_insp_bill_item'        AND COLUMN_NAME IN ('qualified_qty', 'unqualified_qty'))
    OR (TABLE_NAME = 'ado_s5_iqc_insp_bill_item_sample' AND COLUMN_NAME = 'sample_numeric'))
  AND NUMERIC_PRECISION = 18 AND NUMERIC_SCALE = 6;

-- ---- C1 · tenant-first：所有非 PRIMARY 索引的首列必须是 tenant_id ----
SELECT COUNT(*) = 0 FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('ado_s5_iqc_insp_bill_item', 'ado_s5_iqc_insp_bill_item_sample')
  AND INDEX_NAME <> 'PRIMARY'
  AND SEQ_IN_INDEX = 1
  AND COLUMN_NAME <> 'tenant_id';

-- ---- C2 · item UK 恰为 (tenant_id, inspection_bill_id, source_spec_item_id) ----
SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) = 'tenant_id,inspection_bill_id,source_spec_item_id'
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ado_s5_iqc_insp_bill_item'
  AND INDEX_NAME = 'uk_s5_iqc_item_scope' AND NON_UNIQUE = 0;

-- ---- C3 · sample UK 恰为 (tenant_id, item_id, sample_index) ----
SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) = 'tenant_id,item_id,sample_index'
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ado_s5_iqc_insp_bill_item_sample'
  AND INDEX_NAME = 'uk_s5_iqc_sample_scope' AND NON_UNIQUE = 0;

-- ---- C4 · 两张表不得存在 FOREIGN KEY（实测 204 张 ado_* 表 FK 数 = 0） ----
SELECT COUNT(*) = 0 FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('ado_s5_iqc_insp_bill_item', 'ado_s5_iqc_insp_bill_item_sample')
  AND CONSTRAINT_TYPE = 'FOREIGN KEY';

-- ---- D1 · 8 条 CHECK 约束齐备 ----
SELECT COUNT(*) = 8 FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('ado_s5_iqc_insp_bill_item', 'ado_s5_iqc_insp_bill_item_sample')
  AND CONSTRAINT_TYPE = 'CHECK'
  AND CONSTRAINT_NAME IN ('ck_s5_iqc_item_tenant', 'ck_s5_iqc_item_result_type',
                          'ck_s5_iqc_item_truth', 'ck_s5_iqc_item_judgement',
                          'ck_s5_iqc_item_jsrc',
                          'ck_s5_iqc_sample_tenant', 'ck_s5_iqc_sample_index',
                          'ck_s5_iqc_sample_judgement');

-- ---- D2 · 两条 valid-tenant CHECK 确实排除 0 / 1 / 主租户哨兵 ----
SELECT COUNT(*) = 2 FROM information_schema.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
  AND CONSTRAINT_NAME IN ('ck_s5_iqc_item_tenant', 'ck_s5_iqc_sample_tenant')
  AND CHECK_CLAUSE LIKE '%1300000000001%'
  AND CHECK_CLAUSE LIKE '%is not null%';

-- ---- D3 · 单一真源 CHECK 显式覆盖 NUMERIC 分支且不依赖三值逻辑隐式放行 ----
SELECT COUNT(*) = 1 FROM information_schema.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
  AND CONSTRAINT_NAME = 'ck_s5_iqc_item_truth'
  AND CHECK_CLAUSE LIKE '%result_type%is null%'
  AND CHECK_CLAUSE LIKE '%qualified_qty%';

-- ---- E1 · qms_jygfzb 不得被新增 lrlx 列（本批对 qms_* 零 ALTER 的结构证明） ----
SELECT COUNT(*) = 0 FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'qms_jygfzb' AND COLUMN_NAME = 'lrlx';

-- ---- F1 · 本批完成时 item 表必须为空（零业务数据写入） ----
SELECT COUNT(*) = 0 FROM ado_s5_iqc_insp_bill_item;

-- ---- F2 · 本批完成时 sample 表必须为空 ----
SELECT COUNT(*) = 0 FROM ado_s5_iqc_insp_bill_item_sample;