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

-- ============================================================================
-- S5-IQC-B1 · 原材料检规 ↔ 物料 派生映射桥表
--
-- 目标：为 S5 IQC 建立 qms_jygf → ItemMaster 的稳定映射基础设施。
--       本批 **不改变任何现有业务写路径行为**：不动 qms_qcp_inspbill、
--       不动 GenerateInspBill、不动 qms_jygf / qms_jygfzb、不动报检数据。
--
-- ⚠️ 数据回填范围：**仅 UAT-A 租户（SysOrg.Code = 'UATTEST_CHL'）**
-- ⚠️ 明确不回填 797403760988229 的 4704 条历史检规，不治理其他租户。
-- ⚠️ 表结构本身是多租户通用的；只有下方 ③ 的 seed 是 tenant scoped。
--
-- 写入范围：CREATE TABLE 1 · INSERT(仅 UAT-A) · UPDATE 0 · DELETE 0 · 其他 DDL 0
--
-- ---------------------------------------------------------------------------
-- 【分词等价性保证 —— 本脚本最重要的约束】
--
-- 运行期分词口径唯一定义在 C# S5IqcMaterialTokenizer：
--     分隔符 = ; ； , ， 、 : ： / 空格 Tab CR LF        （**不含 . - _**，它们出现在合法物料码内部）
--     标准化 = TRIM + UPPER
--     分级   = EXACT(命中 ItemMaster) / NOT_FOUND / GLUE_UNRESOLVED
--
-- SQL 无法可靠复刻上述正则。为杜绝「迁移一套规则、运行期另一套规则」，本脚本
-- **只为不含任何分隔符的单 token 检规生成映射**（见 ③ 的 NOT LIKE 守卫）——
-- 此时分词退化为恒等变换 TRIM(UPPER(wlbm))，SQL 与 C# 结果**可证明相同**。
-- 含分隔符的检规一律不由本脚本落地，必须调用 POST /api/S5IqcSpecMap/rebuild 生成。
--
-- 实测（2026-09-02）UAT-A 8 条检规的 wlbm 全部无分隔符 → 本脚本覆盖 8/8，无遗漏。
-- 该守卫同时保证：即使将来 UAT-A 新增了多物料检规，脚本也只会「少做」不会「做错」。
-- ---------------------------------------------------------------------------
--
-- 幂等：CREATE TABLE IF NOT EXISTS + INSERT ... WHERE NOT EXISTS(唯一业务键)。
--       重复执行结果恒等，不产生重复行。
-- ============================================================================

-- ===========================================================================
-- ① 桥表
--    唯一约束 uk_s5_iqc_map(tenant_id, spec_id, raw_token, is_manual)：
--      raw_token 决定 material_code，故该组合即「spec_id + material_code 唯一」的
--      等价防重复机制，且在 material_code 为 NULL(NOT_FOUND/GLUE) 时依然有效
--      （MySQL 唯一索引允许多个 NULL，若用 material_code 做键将无法防重）。
-- ===========================================================================
CREATE TABLE IF NOT EXISTS ado_s5_iqc_spec_material_map (
    id             BIGINT       NOT NULL COMMENT '主键(雪花)',
    tenant_id      BIGINT       NOT NULL COMMENT '租户ID',
    spec_id        BIGINT       NOT NULL COMMENT '原材料检规ID → qms_jygf.id',
    spec_no        VARCHAR(100) NULL     COMMENT '检规文件编号 wjbh(冗余，随主表同步刷新)',
    spec_version   VARCHAR(50)  NULL     COMMENT '检规版本 bb(冗余，随主表同步刷新)',
    seq            INT          NOT NULL DEFAULT 0 COMMENT 'token 在 wlbm 中的序号；人工绑定行为 0',
    raw_token      VARCHAR(200) NOT NULL COMMENT 'wlbm 切分出的原始片段(保留原貌供审计)',
    material_code  VARCHAR(100) NULL     COMMENT '标准化后的物料码；仅 EXACT/MANUAL 落值，其余 NULL(绝不猜物料)',
    match_status   VARCHAR(20)  NOT NULL COMMENT 'EXACT / MANUAL / NOT_FOUND / GLUE_UNRESOLVED',
    source_raw     TEXT         NULL     COMMENT '检规 wlbm 原始全串(审计用)',
    is_manual      TINYINT      NOT NULL DEFAULT 0 COMMENT '1=人工绑定，自动同步永不删除',
    note           VARCHAR(500) NULL     COMMENT '人工绑定备注',
    create_time    DATETIME     NULL,
    update_time    DATETIME     NULL,
    create_user_id BIGINT       NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_s5_iqc_map (tenant_id, spec_id, raw_token, is_manual),
    KEY idx_s5_iqc_map_item (tenant_id, material_code),
    KEY idx_s5_iqc_map_spec (tenant_id, spec_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  COMMENT='S5 IQC 原材料检规↔物料 派生映射(B-1)';

-- ===========================================================================
-- ② 目标租户解析（UAT-A）。解析不到则下方 INSERT 全部为空，脚本安全空转。
-- ===========================================================================
SET @uat_a := (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);

-- ===========================================================================
-- ③ UAT-A 回填（仅无分隔符的单 token 检规；仅 EXACT/NOT_FOUND 两态）
--
--    分隔符守卫：wlbm 不得含 ; ； , ， 、 : ： / 空格 Tab CR LF
--    （'\\' 为 MySQL LIKE 默认转义符，此处无需转义任何目标字符）
--    GLUE_UNRESOLVED 在「单 token」前提下只可能因长度越界或含中文产生，
--    本脚本对这两种情况也如实落 GLUE_UNRESOLVED，与 C# Classify 完全一致。
-- ===========================================================================
INSERT INTO ado_s5_iqc_spec_material_map
    (id, tenant_id, spec_id, spec_no, spec_version, seq, raw_token, material_code,
     match_status, source_raw, is_manual, note, create_time, update_time, create_user_id)
SELECT
    -- 确定性 ID = 3000000000000030000 + 该 spec 在本租户 qms_jygf 中按 id 升序的稳定排名。
    -- 【为什么不用 @rn := @rn + 1】MySQL 8 明确规定 SELECT 中用户变量的求值顺序未定义，
    --   实测会产生重复值并触发 PRIMARY 冲突（1062）。
    -- 【为什么不用 ROW_NUMBER()】窗口序号是对**过滤后**结果集编号，重跑时若部分行已存在，
    --   剩余行会被重新编号 → 与首次写入的 id 冲突。
    -- 相关子查询排名只依赖 qms_jygf 自身内容，与本表已有多少行无关，故重跑恒等。
    3000000000000030000 + (
        SELECT COUNT(*) FROM qms_jygf r
        WHERE r.tenant_id = g.tenant_id AND r.id <= g.id
    ),
    g.tenant_id,
    g.id,
    g.wjbh,
    g.bb,
    1,
    TRIM(g.wlbm),
    CASE WHEN im.ItemNum IS NOT NULL THEN UPPER(TRIM(g.wlbm)) ELSE NULL END,
    CASE
        WHEN im.ItemNum IS NOT NULL THEN 'EXACT'
        WHEN CHAR_LENGTH(TRIM(g.wlbm)) < 4 OR CHAR_LENGTH(TRIM(g.wlbm)) > 16 THEN 'GLUE_UNRESOLVED'
        WHEN TRIM(g.wlbm) REGEXP '[\\x{4E00}-\\x{9FFF}]' THEN 'GLUE_UNRESOLVED'
        ELSE 'NOT_FOUND'
    END,
    g.wlbm,
    0,
    NULL,
    NOW(), NOW(), NULL
FROM qms_jygf g
LEFT JOIN ItemMaster im
       ON im.tenant_id = g.tenant_id
      AND im.ItemNum = UPPER(TRIM(g.wlbm))
WHERE @uat_a IS NOT NULL
  AND g.tenant_id = @uat_a
  AND g.wlbm IS NOT NULL
  AND TRIM(g.wlbm) <> ''
  -- 分词等价性守卫：只处理无分隔符的单 token
  AND TRIM(g.wlbm) NOT LIKE '%;%'
  AND TRIM(g.wlbm) NOT LIKE '%；%'
  AND TRIM(g.wlbm) NOT LIKE '%,%'
  AND TRIM(g.wlbm) NOT LIKE '%，%'
  AND TRIM(g.wlbm) NOT LIKE '%、%'
  AND TRIM(g.wlbm) NOT LIKE '%:%'
  AND TRIM(g.wlbm) NOT LIKE '%：%'
  AND TRIM(g.wlbm) NOT LIKE '%/%'
  AND TRIM(g.wlbm) NOT LIKE '% %'
  AND TRIM(g.wlbm) NOT LIKE CONCAT('%', CHAR(9), '%')
  AND TRIM(g.wlbm) NOT LIKE CONCAT('%', CHAR(13), '%')
  AND TRIM(g.wlbm) NOT LIKE CONCAT('%', CHAR(10), '%')
  -- 幂等：同 (tenant, spec, raw_token, is_manual) 已存在则跳过
  AND NOT EXISTS (
        SELECT 1 FROM ado_s5_iqc_spec_material_map x
        WHERE x.tenant_id = g.tenant_id
          AND x.spec_id   = g.id
          AND x.raw_token = TRIM(g.wlbm)
          AND x.is_manual = 0);