AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.445.sql

-- S6 Phase 1B · 过程检规物料映射 + 自动唯一解析 + 检规快照（幂等）
-- 仅针对 UATAdminA 所属租户 838257186181189 补最小 Golden 数据；不污染其他租户。
-- 表名/字段服从 S6/QMS 现有命名。全部 nullable/兼容历史；重跑不重复。

-- ============ ① S0 过程检规明细 ResultType 列（lrlx：NUMERIC / NON_NUMERIC）============
SET @exist := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='qms_gcjygfzb' AND COLUMN_NAME='lrlx');
SET @sql := IF(@exist=0, 'ALTER TABLE qms_gcjygfzb ADD COLUMN lrlx VARCHAR(20) NULL COMMENT ''录入类型 ResultType: NUMERIC/NON_NUMERIC''', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ============ ② 过程检规↔物料 规范映射表（运行期 Resolver 只查此表，不再 split wlbm）============
CREATE TABLE IF NOT EXISTS ado_s6_ipqc_spec_material_map (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    spec_id BIGINT NOT NULL,
    spec_code VARCHAR(100) NULL COMMENT 'wjbh 文件编号',
    spec_version VARCHAR(50) NULL COMMENT 'bb 版本',
    item_code VARCHAR(100) NOT NULL,
    match_status VARCHAR(20) NOT NULL DEFAULT 'EXACT' COMMENT 'EXACT/NOT_FOUND/GLUE_UNRESOLVED/INVALID',
    source_raw_value TEXT NULL COMMENT 'wlbm 原始片段',
    create_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_s6_spec_item (tenant_id, spec_id, item_code),
    KEY idx_s6_map_item (tenant_id, item_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S6 过程检规↔物料 规范映射';

-- ============ ③ 过程检验单（Phase 1B 最小业务骨架）============
CREATE TABLE IF NOT EXISTS ado_s6_process_inspection_bill (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    factory_id BIGINT NULL,
    work_order_no VARCHAR(100) NOT NULL,
    lot_serial VARCHAR(100) NULL,
    item_code VARCHAR(100) NULL,
    item_name VARCHAR(200) NULL,
    specification VARCHAR(200) NULL,
    spec_id BIGINT NULL,
    spec_code VARCHAR(100) NULL,
    spec_version VARCHAR(50) NULL,
    inspection_status VARCHAR(20) NOT NULL DEFAULT 'DRAFT' COMMENT 'DRAFT/ACTIVE（Phase 1C 扩展状态机）',
    created_by BIGINT NULL,
    created_by_name VARCHAR(100) NULL,
    create_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_s6_bill_wo (tenant_id, work_order_no, inspection_status),
    KEY idx_s6_bill_spec (tenant_id, spec_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S6 过程检验单（Phase 1B 骨架）';

-- ============ ④ 检规快照-头 ============
CREATE TABLE IF NOT EXISTS ado_s6_process_inspection_snapshot_head (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    bill_id BIGINT NOT NULL,
    spec_id BIGINT NULL,
    spec_code VARCHAR(100) NULL,
    spec_version VARCHAR(50) NULL,
    effective_date VARCHAR(50) NULL,
    item_code VARCHAR(100) NULL,
    specification VARCHAR(200) NULL,
    source_tenant_id BIGINT NULL,
    create_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_s6_snap_head_bill (bill_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S6 检规快照-头';

-- ============ ⑤ 检规快照-明细 ============
CREATE TABLE IF NOT EXISTS ado_s6_process_inspection_snapshot_line (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    bill_id BIGINT NOT NULL,
    source_line_id BIGINT NULL COMMENT 'qms_gcjygfzb.id',
    inspection_item VARCHAR(500) NULL COMMENT 'jyxm',
    process_code VARCHAR(100) NULL COMMENT 'gxdh',
    process_name VARCHAR(200) NULL COMMENT 'gxmc',
    method VARCHAR(500) NULL COMMENT 'jyff',
    spec_value VARCHAR(500) NULL COMMENT 'jygg 检验规格',
    tech_standard VARCHAR(500) NULL COMMENT 'jsbz 技术标准',
    lower_limit VARCHAR(100) NULL COMMENT 'xx',
    upper_limit VARCHAR(100) NULL COMMENT 'sx',
    inspection_frequency VARCHAR(200) NULL COMMENT 'jypc',
    result_type VARCHAR(20) NULL COMMENT 'lrlx NUMERIC/NON_NUMERIC',
    sort_no INT NULL,
    create_time DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_s6_snap_line_bill (tenant_id, bill_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='S6 检规快照-明细';

-- ============ ⑥ Golden 数据（租户 838257186181189）============
SET @T := 838257186181189;

-- ⑥.1 新增 AMBIGUOUS 伙伴检规 UATD-GC-006（物料 3124C0015 的第二张、不同 wjbh，同样有效）
INSERT INTO qms_gcjygf (id, tenant_id, syxh, wjbh, bb, sxrj, wlbm, version)
SELECT 3000000000000011006, @T, 'UAT-AMBIG-型号', 'UATD-GC-006', 'A.0', '2026-08-28', '3124C0015', 1
WHERE NOT EXISTS (SELECT 1 FROM qms_gcjygf WHERE id=3000000000000011006);

-- ⑥.2 新增 VERSION 更高版本 UATD-GC-003 v2（同 wjbh，sxrj 更新 → Resolver 应取此版本；物料 3221C0031）
INSERT INTO qms_gcjygf (id, tenant_id, syxh, wjbh, bb, sxrj, wlbm, version)
SELECT 3000000000000011007, @T, '压（FBE6035B）', 'UATD-GC-003', 'A.1', '2026-08-29', '3221C0031', 2
WHERE NOT EXISTS (SELECT 1 FROM qms_gcjygf WHERE id=3000000000000011007);

-- ⑥.3 明细行（每检规 2 行：1 NUMERIC + 1 NON_NUMERIC，含 lrlx；glid 内联直指归属检规）。固定 id 保证幂等。
--     GC-001(11001,91HC0497 MATCHED) GC-002(11002,3124C0015) GC-006(11006,3124C0015 AMBIG)
--     GC-003v1(11003,3221C0031) GC-003v2(11007,3221C0031) GC-004(11004,3121C0035) GC-005(11005,322AD001)
INSERT INTO qms_gcjygfzb (id, tenant_id, glid, gxdh, gxmc, jyxm, jyff, jygg, jsbz, jypc, xx, sx, lrlx)
SELECT * FROM (
    SELECT 3000000000000012001 id, @T tenant_id, 3000000000000011001 glid, 'OP10' gxdh, '装配' gxmc, '关键尺寸A' jyxm, '游标卡尺' jyff, '10.0±0.2' jygg, 'GB/T-A' jsbz, '首件' jypc, '9.8' xx, '10.2' sx, 'NUMERIC' lrlx
    UNION ALL SELECT 3000000000000012002, @T, 3000000000000011001, 'OP10', '装配', '外观', '目视', '无划伤/无毛刺', '表面完好', '巡检', NULL, NULL, 'NON_NUMERIC'
    UNION ALL SELECT 3000000000000012003, @T, 3000000000000011002, 'OP20', '成型', '厚度B', '千分尺', '2.00±0.05', 'GB/T-B', '首件', '1.95', '2.05', 'NUMERIC'
    UNION ALL SELECT 3000000000000012004, @T, 3000000000000011002, 'OP20', '成型', '色泽', '目视', '色差合格', '标准色卡', '巡检', NULL, NULL, 'NON_NUMERIC'
    UNION ALL SELECT 3000000000000012005, @T, 3000000000000011006, 'OP30', '注塑', '孔径C', '塞规', '5.00±0.10', 'GB/T-C', '首件', '4.90', '5.10', 'NUMERIC'
    UNION ALL SELECT 3000000000000012006, @T, 3000000000000011006, 'OP30', '注塑', '飞边', '目视', '无飞边', '外观规范', '巡检', NULL, NULL, 'NON_NUMERIC'
    UNION ALL SELECT 3000000000000012007, @T, 3000000000000011003, 'OP40', '压合', '压力D', '压力计', '50±2N', 'GB/T-D', '首件', '48', '52', 'NUMERIC'
    UNION ALL SELECT 3000000000000012008, @T, 3000000000000011003, 'OP40', '压合', '密封', '气密测试', '无泄漏', '密封标准', '巡检', NULL, NULL, 'NON_NUMERIC'
    UNION ALL SELECT 3000000000000012009, @T, 3000000000000011007, 'OP50', '包装', '长度E', '卷尺', '100±1', 'GB/T-E', '首件', '99', '101', 'NUMERIC'
    UNION ALL SELECT 3000000000000012010, @T, 3000000000000011007, 'OP50', '包装', '标签', '目视', '标签正确', '标签规范', '巡检', NULL, NULL, 'NON_NUMERIC'
    UNION ALL SELECT 3000000000000012011, @T, 3000000000000011004, 'OP60', '终检', '重量F', '电子秤', '20±0.5g', 'GB/T-F', '末件', '19.5', '20.5', 'NUMERIC'
    UNION ALL SELECT 3000000000000012012, @T, 3000000000000011004, 'OP60', '终检', '清洁度', '目视', '无异物', '洁净标准', '巡检', NULL, NULL, 'NON_NUMERIC'
    UNION ALL SELECT 3000000000000012013, @T, 3000000000000011005, 'OP70', '装配', '扭矩G', '扭矩扳手', '8±0.5Nm', 'GB/T-G', '首件', '7.5', '8.5', 'NUMERIC'
    UNION ALL SELECT 3000000000000012014, @T, 3000000000000011005, 'OP70', '装配', '功能', '功能测试', '功能正常', '功能规范', '巡检', NULL, NULL, 'NON_NUMERIC'
) g
WHERE NOT EXISTS (SELECT 1 FROM qms_gcjygfzb z WHERE z.id=g.id);

-- ⑥.4 规范映射（EXACT）：为 7 张 Golden 检规各建 1 条 (spec,item)。幂等 UK(tenant,spec_id,item_code)。
INSERT INTO ado_s6_ipqc_spec_material_map (tenant_id, spec_id, spec_code, spec_version, item_code, match_status, source_raw_value)
SELECT @T, g.id, g.wjbh, g.bb, g.wlbm, 'EXACT', g.wlbm
FROM qms_gcjygf g
WHERE g.tenant_id=@T AND g.id IN (
    3000000000000011001,3000000000000011002,3000000000000011003,
    3000000000000011004,3000000000000011005,3000000000000011006,3000000000000011007)
  AND NOT EXISTS (
    SELECT 1 FROM ado_s6_ipqc_spec_material_map m
    WHERE m.tenant_id=@T AND m.spec_id=g.id AND m.item_code=g.wlbm);