-- S6 Phase 1C · 过程检验录入 + 数值/非数值判定 + 结果聚合（幂等）
-- 原则：快照(config)不可变；结果(execution)分离到独立表。仅 UATAdminA 租户 838257186181189 补 Golden sample_count。

-- ============ ① 样本量 sample_count：源检规明细 + 快照明细各加一列 ============
SET @e := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='qms_gcjygfzb' AND COLUMN_NAME='sample_count');
SET @s := IF(@e=0, 'ALTER TABLE qms_gcjygfzb ADD COLUMN sample_count INT NULL COMMENT ''要求样本量(判定完成用)''', 'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

SET @e := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='ado_s6_process_inspection_snapshot_line' AND COLUMN_NAME='sample_count');
SET @s := IF(@e=0, 'ALTER TABLE ado_s6_process_inspection_snapshot_line ADD COLUMN sample_count INT NULL COMMENT ''冻结的要求样本量''', 'SELECT 1');
PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;

-- ============ ② Golden 样本量（租户 838）：NUMERIC 项 3 个样本、NON_NUMERIC 项 1 个 ============
SET @T := 838257186181189;
UPDATE qms_gcjygfzb SET sample_count=3 WHERE tenant_id=@T AND lrlx='NUMERIC' AND (sample_count IS NULL OR sample_count=0);
UPDATE qms_gcjygfzb SET sample_count=1 WHERE tenant_id=@T AND lrlx='NON_NUMERIC' AND (sample_count IS NULL OR sample_count=0);

-- 回填既有快照明细的 sample_count（按 source_line_id 对齐源检规明细；只补空值）
UPDATE ado_s6_process_inspection_snapshot_line sl
JOIN qms_gcjygfzb z ON z.id = sl.source_line_id AND z.tenant_id = sl.tenant_id
SET sl.sample_count = z.sample_count
WHERE sl.tenant_id=@T AND (sl.sample_count IS NULL OR sl.sample_count=0) AND z.sample_count IS NOT NULL;

-- ============ ③ 过程检验结果-头（可变执行结果，与快照分离）============
CREATE TABLE IF NOT EXISTS ado_s6_process_inspection_result (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    bill_id BIGINT NOT NULL,
    work_order_no VARCHAR(100) NULL,
    item_code VARCHAR(100) NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'IN_PROGRESS' COMMENT 'NOT_STARTED/IN_PROGRESS/COMPLETED',
    overall_result VARCHAR(16) NULL COMMENT 'PASS/FAIL/PENDING/INVALID',
    inspector_id BIGINT NULL,
    inspector_name VARCHAR(100) NULL,
    started_at DATETIME NULL,
    completed_at DATETIME NULL,
    create_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_s6_result_bill (tenant_id, bill_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S6 过程检验结果-头';

-- ============ ④ 过程检验结果-明细（逐样本；UK 保证幂等）============
CREATE TABLE IF NOT EXISTS ado_s6_process_inspection_result_item (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    bill_id BIGINT NOT NULL,
    result_id BIGINT NOT NULL,
    snapshot_line_id BIGINT NOT NULL,
    result_type VARCHAR(20) NULL COMMENT '冻结自快照 NUMERIC/NON_NUMERIC',
    sample_index INT NOT NULL,
    actual_numeric DECIMAL(18,6) NULL,
    actual_non_numeric VARCHAR(8) NULL COMMENT 'OK/NG',
    judgement VARCHAR(16) NULL COMMENT 'PASS/FAIL/INVALID（后端计算）',
    remark VARCHAR(500) NULL,
    inspector_id BIGINT NULL,
    inspection_time DATETIME NULL,
    create_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_s6_result_item (bill_id, snapshot_line_id, sample_index),
    KEY idx_s6_result_item (tenant_id, bill_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S6 过程检验结果-明细(逐样本)';