-- 1.0.132.sql
-- S0-2026-05-28｜物料替代 ItemSubstituteDetail 数据治理导入
--
-- 业务背景：
--   前端「物料替代」页面（/aidop/s0/manufacturing/material-substitution）
--   后端 AdoS0ItemSubstituteDetailsController 只查 ItemSubstituteDetail 表（当前 0 行）。
--   导入数据落在 ic_bom / ic_bom_child / ic_substitute_group_detail 三张 legacy 表。
--   本脚本将三表 JOIN 后排除原料自身行，导入到 ItemSubstituteDetail。
--
-- 拍板口径：
--   ParentItem      = ic_bom.item_number
--   ItemNum         = ic_bom_child.item_number
--   SubstituteItem  = ic_substitute_group_detail.icitem_number
--   SubstituteQty   = ic_substitute_group_detail.replace_qty
--   SubstituteType  = '替代'
--   Domain          = '8010'  （基于 factory_id 字符串化兜底，非正式业务域；后续如业务给正式 Domain，另开批次修正）
--   tenant_id       = ic_bom_child.tenant_id
--   排除原料自身    : g.icitem_number <> c.item_number
--
-- 预期导入行数：1147（已在阶段 2 + 阶段 3 各跑一次只读校验确认）
--
-- 操作流程：
--   1) 执行段 1【执行前校验】，5 项指标必须命中预期
--   2) 执行段 2【INSERT SELECT】（含 NOT EXISTS 幂等保护）
--   3) 执行段 3【执行后对账】，本批次行数应 = 1147
--   4) 如需回滚，先跑段 4 的 SELECT 预览，再人工取消注释 DELETE


-- ===========================================================================
-- 段 1：执行前校验（只读）
-- 所有 5 项必须全绿才允许执行段 2
-- ===========================================================================

-- 校验 1.1：预计导入行数（应 = 1147）
SELECT COUNT(*) AS expected_insert_rows
FROM ic_bom_child c
INNER JOIN ic_bom b
  ON b.Id = c.bom_id
  AND b.IsDeleted = 0
INNER JOIN ic_substitute_group_detail g
  ON g.substitute_code = c.substitute_code
WHERE c.IsDeleted = 0
  AND NULLIF(TRIM(c.substitute_code),'') IS NOT NULL
  AND NULLIF(TRIM(g.icitem_number),'') IS NOT NULL
  AND g.icitem_number <> c.item_number;

-- 校验 1.2：空值检查（5 个字段都应 = 0）
SELECT
  SUM(b.item_number IS NULL OR TRIM(b.item_number) = '') AS empty_parent,
  SUM(c.item_number IS NULL OR TRIM(c.item_number) = '') AS empty_itemnum,
  SUM(g.icitem_number IS NULL OR TRIM(g.icitem_number) = '') AS empty_substitute,
  SUM(g.replace_qty IS NULL) AS null_qty,
  SUM(c.tenant_id IS NULL OR c.tenant_id = 0) AS empty_tenant
FROM ic_bom_child c
INNER JOIN ic_bom b
  ON b.Id = c.bom_id
  AND b.IsDeleted = 0
INNER JOIN ic_substitute_group_detail g
  ON g.substitute_code = c.substitute_code
WHERE c.IsDeleted = 0
  AND NULLIF(TRIM(c.substitute_code),'') IS NOT NULL
  AND g.icitem_number <> c.item_number;

-- 校验 1.3：三元组重复（total = distinct = 1147；dup = 0）
SELECT total_rows, distinct_triples, total_rows - distinct_triples AS dup_rows
FROM (
  SELECT
    COUNT(*) AS total_rows,
    COUNT(DISTINCT CONCAT_WS('|', c.tenant_id, '8010', b.item_number, c.item_number, g.icitem_number)) AS distinct_triples
  FROM ic_bom_child c
  INNER JOIN ic_bom b
    ON b.Id = c.bom_id
    AND b.IsDeleted = 0
  INNER JOIN ic_substitute_group_detail g
    ON g.substitute_code = c.substitute_code
  WHERE c.IsDeleted = 0
    AND NULLIF(TRIM(c.substitute_code),'') IS NOT NULL
    AND g.icitem_number <> c.item_number
) t;

-- 校验 1.4：目标表当前行数（应 = 0；若 > 0，需评估 NOT EXISTS 的去重效果）
SELECT COUNT(*) AS target_existing_rows
FROM ItemSubstituteDetail;

-- 校验 1.5：tenant_id 三表一致性（应只出现 797403760988229 一行）
SELECT
  c.tenant_id AS child_tenant,
  b.tenant_id AS bom_tenant,
  g.tenant_id AS group_tenant,
  COUNT(*) AS row_cnt
FROM ic_bom_child c
INNER JOIN ic_bom b
  ON b.Id = c.bom_id
INNER JOIN ic_substitute_group_detail g
  ON g.substitute_code = c.substitute_code
WHERE c.IsDeleted = 0
  AND NULLIF(TRIM(c.substitute_code),'') IS NOT NULL
GROUP BY c.tenant_id, b.tenant_id, g.tenant_id;


-- ===========================================================================
-- 段 2：INSERT SELECT（数据导入）
-- 含 NOT EXISTS 幂等保护：重复执行不会产生重复数据
-- ===========================================================================

INSERT INTO ItemSubstituteDetail
  (ParentItem, ItemNum, SubstituteItem, SubstituteQty,
   SubstituteType, Remark, Domain, CreateUser, CreateTime, tenant_id)
SELECT
  b.item_number                                                                          AS ParentItem,
  c.item_number                                                                          AS ItemNum,
  g.icitem_number                                                                        AS SubstituteItem,
  g.replace_qty                                                                          AS SubstituteQty,
  '替代'                                                                                  AS SubstituteType,
  CONCAT('migrated from ic_bom_child Id=', c.Id, ', substitute_code=', c.substitute_code) AS Remark,
  '8010'                                                                                 AS Domain,
  'data-migration'                                                                       AS CreateUser,
  NOW()                                                                                  AS CreateTime,
  c.tenant_id                                                                            AS tenant_id
FROM ic_bom_child c
INNER JOIN ic_bom b
  ON b.Id = c.bom_id
  AND b.IsDeleted = 0
INNER JOIN ic_substitute_group_detail g
  ON g.substitute_code = c.substitute_code
WHERE c.IsDeleted = 0
  AND NULLIF(TRIM(c.substitute_code),'') IS NOT NULL
  AND NULLIF(TRIM(g.icitem_number),'') IS NOT NULL
  AND g.icitem_number <> c.item_number
  AND NOT EXISTS (
    SELECT 1
    FROM ItemSubstituteDetail x
    WHERE x.tenant_id = c.tenant_id
      AND x.Domain = '8010'
      AND x.ParentItem = b.item_number
      AND x.ItemNum = c.item_number
      AND x.SubstituteItem = g.icitem_number
  );


-- ===========================================================================
-- 段 3：执行后对账（只读）
-- ===========================================================================

-- 对账 3.1：本批次导入行数（应 = 1147）
SELECT COUNT(*) AS migrated_rows
FROM ItemSubstituteDetail
WHERE CreateUser = 'data-migration'
  AND Remark LIKE 'migrated from ic_bom_child Id=%, substitute_code=%'
  AND Domain = '8010'
  AND tenant_id = 797403760988229;

-- 对账 3.2：ItemSubstituteDetail 总行数（应 = 1147，假设目标表初始为 0）
SELECT COUNT(*) AS total_rows FROM ItemSubstituteDetail;

-- 对账 3.3：按 Domain / SubstituteType / tenant_id 分组
SELECT Domain, SubstituteType, tenant_id, COUNT(*) AS row_cnt
FROM ItemSubstituteDetail
GROUP BY Domain, SubstituteType, tenant_id
ORDER BY row_cnt DESC;


-- ===========================================================================
-- 段 4：回滚 SQL 草案
-- 默认全部注释，仅在需要完整回退本批次时人工评审后取消注释
-- 四重限定（CreateUser + Remark + Domain + tenant_id）避免误删人工维护数据
-- ===========================================================================

-- 回滚 4.1：先 SELECT 预览要删除的行数（应 = 阶段 3 实际 INSERT 行数）
-- SELECT COUNT(*) AS rollback_rows_preview
-- FROM ItemSubstituteDetail
-- WHERE CreateUser = 'data-migration'
--   AND Remark LIKE 'migrated from ic_bom_child Id=%, substitute_code=%'
--   AND Domain = '8010'
--   AND tenant_id = 797403760988229;

-- 回滚 4.2：实际 DELETE（仅当预览数对账无误时才取消注释）
-- DELETE FROM ItemSubstituteDetail
-- WHERE CreateUser = 'data-migration'
--   AND Remark LIKE 'migrated from ic_bom_child Id=%, substitute_code=%'
--   AND Domain = '8010'
--   AND tenant_id = 797403760988229;