-- 流转卡工序记录 投入/产出/不合格 按工序收率重算 | 目标库 mom-hm | 2026-10-15
--
-- 口径（与 docs/流转卡投入产出按收率重算方案.md 一致，与生产任务数量同源）：
--   产出(pᵢ) = CEIL( Q / (rᵢ₊₁ × … × rₙ) )
--   投入(p₁) = CEIL( 产出(p₁) / r₁ )        -- 首道
--   投入(pᵢ) = 产出(pᵢ₋₁)                    -- i ≥ 2，取 sort 升序上一道产出
--   不合格品(pᵢ) = 投入(pᵢ) - 产出(pᵢ)
--
-- 备份（重算前）：
--   SELECT id,card_id,sort,process_id,input_quantity,output_quantity,unqualified_quantity
--   FROM mes_pro_card_process WHERE deleted=0 ORDER BY card_id,sort;   -> task-before.tsv
--
-- 说明：因 MySQL 不允许 UPDATE 与自身子查询同时出现（errno 1093），用临时表暂存重算结果。

CREATE TEMPORARY TABLE tmp_card_recalc AS
SELECT t.cp_id,
       t.out_qty,
       COALESCE(LAG(t.out_qty) OVER (PARTITION BY t.work_order_id ORDER BY t.sort ASC),
                CEIL(t.out_qty / COALESCE(t.yield_rate, 1))) AS in_qty
FROM (
  SELECT cp2.id AS cp_id, c.work_order_id, cp2.sort, wop.yield_rate,
         CEIL(wo.quantity / COALESCE(EXP(SUM(LN(COALESCE(wop.yield_rate, 1)))
              OVER (PARTITION BY c.work_order_id ORDER BY cp2.sort DESC
                    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)), 1)) AS out_qty
  FROM mes_pro_card_process cp2
  JOIN mes_pro_card c ON c.id = cp2.card_id AND c.deleted = 0
  JOIN mes_pro_work_order wo ON wo.id = c.work_order_id
  JOIN mes_pro_work_order_process wop
    ON wop.work_order_id = c.work_order_id AND wop.process_id = cp2.process_id AND wop.deleted = 0
  WHERE cp2.deleted = 0
) t;

UPDATE mes_pro_card_process cp
JOIN tmp_card_recalc x ON x.cp_id = cp.id
SET cp.input_quantity       = x.in_qty,
    cp.output_quantity      = x.out_qty,
    cp.unqualified_quantity = x.in_qty - x.out_qty;

DROP TEMPORARY TABLE tmp_card_recalc;