-- 1.0.564 —— 库位角色配置表 + 165 事务码规则化映射回填 + 隔离原因分级
-- 前提：165 的 MES/WMS APP 不可修改，故一律用它已在推的数据（事务码、库位、数量方向）推导阶段码。
-- 幂等：可重复执行；不覆盖运维手工维护（role_source='MANUAL'）的库位角色。

-- ① 库位角色配置表。角色缺失时按 UNKNOWN 处理，规则不成立、流水进隔离，绝不猜测。
CREATE TABLE IF NOT EXISTS mdp_location_role (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL DEFAULT 0,
    domain VARCHAR(50) NOT NULL DEFAULT '',
    location VARCHAR(100) NOT NULL,
    location_role VARCHAR(24) NOT NULL DEFAULT 'UNKNOWN' COMMENT 'INSPECT/MATERIAL/SEMI/LINE/FG/SCRAP/OTHER/UNKNOWN',
    role_source VARCHAR(24) NOT NULL DEFAULT 'AUTO_DESCR' COMMENT 'RATIFIED=已定口径 AUTO_DESCR=按库位说明推断 MANUAL=运维维护',
    remark VARCHAR(255) NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_location_role (tenant_id, domain, location),
    KEY idx_location_role_role (tenant_id, location_role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='库位中立角色（阶段码判定用）';

-- ② 隔离表补原因列：非阶段/待核验/角色未配置/真未知 语义不同，不能一律当告警
SET @sql := IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='mdp_std_inv_trans_unmapped' AND COLUMN_NAME='unmapped_reason')=0, 'ALTER TABLE `mdp_std_inv_trans_unmapped` ADD COLUMN `unmapped_reason` VARCHAR(40) NOT NULL DEFAULT ''UNKNOWN_CODE''', 'DO 0');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ③ 按已复制到本库的 LocationMaster 生成角色初值（可被运维纠正；已存在的行不动）。
--    半成品必须先判，否则会被「成品」关键词吃掉。
INSERT IGNORE INTO mdp_location_role (tenant_id, domain, location, location_role, role_source, remark)
SELECT IFNULL(lm.tenant_id, 0), IFNULL(lm.Domain, ''), lm.location,
       CASE
         WHEN lm.location = '1000' THEN 'INSPECT'
         WHEN IFNULL(lm.descr,'') LIKE '%待检%' OR IFNULL(lm.descr,'') LIKE '%检验%' OR IFNULL(lm.descr,'') LIKE '%IQC%' THEN 'INSPECT'
         WHEN IFNULL(lm.descr,'') LIKE '%半成品%' THEN 'SEMI'
         WHEN IFNULL(lm.descr,'') LIKE '%线边%' OR IFNULL(lm.descr,'') LIKE '%产线%' THEN 'LINE'
         WHEN IFNULL(lm.descr,'') LIKE '%成品%' THEN 'FG'
         WHEN IFNULL(lm.descr,'') LIKE '%原料%' OR IFNULL(lm.descr,'') LIKE '%物料%' OR IFNULL(lm.descr,'') LIKE '%材料%' THEN 'MATERIAL'
         WHEN IFNULL(lm.descr,'') LIKE '%报废%' OR IFNULL(lm.descr,'') LIKE '%废品%' THEN 'SCRAP'
         ELSE 'UNKNOWN'
       END,
       CASE WHEN lm.location = '1000' THEN 'RATIFIED' ELSE 'AUTO_DESCR' END,
       CONCAT('自动生成于 1.0.564；库位说明：', IFNULL(lm.descr,''))
FROM LocationMaster lm
WHERE TRIM(lm.location) <> ''
  AND IFNULL(lm.typed,'') <> 'Supp';

-- ④ 待检仓口径（2026-08-11 定：WMS 扫码收货只入待检仓 Loc=1000）强制到位，但不覆盖手工维护
UPDATE mdp_location_role
SET location_role = 'INSPECT', role_source = 'RATIFIED'
WHERE location = '1000'
  AND role_source <> 'MANUAL'
  AND location_role <> 'INSPECT';

-- ⑤ 回填存量 trans_type。分支与 NeutralTransTypeCodes 逐条一致（守卫测试比对）。
--    只处理 165 实测码，避免误伤 T8（src_trans_type_raw 是中文 lbs）与标准 API 入站的已映射行。
UPDATE mdp_std_inv_trans t
LEFT JOIN mdp_location_role r
       ON r.tenant_id = t.tenant_id AND r.domain = IFNULL(t.domain,'') AND r.location = IFNULL(t.location,'')
SET t.trans_type = CASE
      WHEN t.src_trans_type_raw IN ('inv-frz','inv-unfrz') THEN NULL
      WHEN t.src_trans_type_raw='iss-wo' THEN 'MAT_ISSUE'
      WHEN t.src_trans_type_raw='rct-po' THEN 'MAT_RECEIPT'
      WHEN t.src_trans_type_raw='rct-unp' THEN 'MAT_RECEIPT'
      WHEN t.src_trans_type_raw='iss-unp' THEN 'MAT_ISSUE'
      WHEN t.src_trans_type_raw='iss-tr-ins' THEN 'MAT_IQC_RELEASE'
      WHEN t.src_trans_type_raw='iss-tr' AND IFNULL(t.qty_change,0)<0 AND IFNULL(r.location_role,'UNKNOWN')='INSPECT' THEN 'MAT_IQC_RELEASE'
      WHEN t.src_trans_type_raw='iss-tr' AND IFNULL(t.qty_change,0)<0 AND IFNULL(r.location_role,'UNKNOWN')='MATERIAL' THEN 'MAT_PICK'
      WHEN t.src_trans_type_raw='iss-tr' AND IFNULL(t.qty_change,0)<0 AND IFNULL(r.location_role,'UNKNOWN')='FG' THEN 'FG_PICK'
      WHEN t.src_trans_type_raw='rct-tr' AND IFNULL(t.qty_change,0)>0 AND IFNULL(r.location_role,'UNKNOWN')='MATERIAL' THEN 'MAT_PUTAWAY'
      WHEN t.src_trans_type_raw='rct-tr' AND IFNULL(t.qty_change,0)>0 AND IFNULL(r.location_role,'UNKNOWN')='LINE' THEN 'MAT_LINE'
      WHEN t.src_trans_type_raw='rct-tr' AND IFNULL(t.qty_change,0)>0 AND IFNULL(r.location_role,'UNKNOWN')='FG' THEN 'FG_PUTAWAY'
      WHEN t.src_trans_type_raw='rct-wo' AND IFNULL(t.qty_change,0)>0 AND IFNULL(r.location_role,'UNKNOWN')='FG' THEN 'FG_PROD_RECEIPT'
      WHEN t.src_trans_type_raw='rct-wo' AND IFNULL(t.qty_change,0)>0 AND IFNULL(r.location_role,'UNKNOWN')='SEMI' THEN 'MAT_RECEIPT'
      WHEN t.src_trans_type_raw='rct-wo' AND IFNULL(t.qty_change,0)>0 AND IFNULL(r.location_role,'UNKNOWN')='MATERIAL' THEN 'MAT_RECEIPT'
      WHEN t.src_trans_type_raw='MAT_RECEIPT' THEN 'MAT_RECEIPT'
      WHEN t.src_trans_type_raw='MAT_IQC_RELEASE' THEN 'MAT_IQC_RELEASE'
      WHEN t.src_trans_type_raw='MAT_PUTAWAY' THEN 'MAT_PUTAWAY'
      WHEN t.src_trans_type_raw='MAT_PICK' THEN 'MAT_PICK'
      WHEN t.src_trans_type_raw='MAT_ISSUE' THEN 'MAT_ISSUE'
      WHEN t.src_trans_type_raw='MAT_LINE' THEN 'MAT_LINE'
      WHEN t.src_trans_type_raw='FG_RECEIPT' THEN 'FG_RECEIPT'
      WHEN t.src_trans_type_raw='FG_PUTAWAY' THEN 'FG_PUTAWAY'
      WHEN t.src_trans_type_raw='FG_PICK' THEN 'FG_PICK'
      WHEN t.src_trans_type_raw='FG_SHIP' THEN 'FG_SHIP'
      WHEN t.src_trans_type_raw='FG_PROD_RECEIPT' THEN 'FG_PROD_RECEIPT'
      WHEN t.src_trans_type_raw='FG_FQC_RELEASE' THEN 'FG_FQC_RELEASE'
      ELSE NULL END
WHERE t.src_trans_type_raw IN ('iss-wo','rct-po','iss-tr','rct-tr','iss-unp','rct-wo','iss-tr-ins','VMI','rct-rv','rct-po-ins','iss-rv','rct-unp','inv-frz','inv-unfrz','iss-po')
  AND (t.trans_type IS NULL OR t.trans_type NOT IN ('MAT_RECEIPT','MAT_IQC_RELEASE','MAT_PUTAWAY','MAT_PICK','MAT_ISSUE','MAT_LINE','FG_RECEIPT','FG_PUTAWAY','FG_PICK','FG_SHIP','FG_PROD_RECEIPT','FG_FQC_RELEASE'));

-- ⑥ 已判定为非阶段事件的码（冻结/解冻）退出隔离表：它们不是待办
DELETE FROM mdp_std_inv_trans_unmapped WHERE src_trans_type_raw IN ('inv-frz','inv-unfrz');

-- ⑦ 已成功映射的行退出隔离表
DELETE u FROM mdp_std_inv_trans_unmapped u
JOIN mdp_std_inv_trans t
  ON t.tenant_id = u.tenant_id AND t.source_system = u.source_system AND t.src_rec_id = u.src_rec_id
WHERE t.trans_type IS NOT NULL;

-- ⑧ 隔离原因归位：待核验 / 库位角色未配置 / 真未知
UPDATE mdp_std_inv_trans_unmapped
SET unmapped_reason = CASE
      WHEN src_trans_type_raw IN ('rct-po-ins','rct-rv','iss-rv','iss-po','VMI') THEN 'PENDING_VERIFY'
      WHEN src_trans_type_raw IN ('iss-tr','rct-tr','rct-wo') THEN 'LOCATION_ROLE_UNCONFIGURED'
      ELSE 'UNKNOWN_CODE' END;