-- Phase M8-p1-h5-fix2 — re-backfill JOBFORM_STAGE_COMPLETION_LOG for
-- Completed stages that migration 60 SKIPPED.
--
-- Migration 60's backfill gated on `s.qty_completed > 0` and used
-- `qty_completed` as the produced count. But on OLDER Job Forms a stage
-- can sit at status='Completed' with a STALE `qty_completed = 0` — the
-- per-stage qty_completed cascade only landed in M4-p3, so completions
-- recorded before that never populated it. Those Completed stages were
-- therefore skipped by migration 60 and never produced a log row, so the
-- Production Summary's PRODUCED counts dropped them entirely (e.g. JF
-- 1002/26 "DOOR LEFT" missing from Lamination onwards).
--
-- This migration inserts one row per Completed (glass, stage) that has NO
-- existing log row, using **qty_required** as the produced count (a
-- Completed stage means every unit cleared it). It does NOT touch stages
-- that already have a log row (NOT EXISTS gate) — so it never double-counts
-- and is safe to re-run.
--
-- subitem_kind is written with the NEW 3-bucket rule (is_bended + name),
-- though the Summary read re-derives the kind live so the stored value is
-- advisory only.

INSERT INTO JOBFORM_STAGE_COMPLETION_LOG
    (jobform_id, jobform_glass_id, stage, subitem_kind, qty, completed_at, completed_by, created_at)
SELECT
    g.jobform_id,
    s.jobform_glass_id,
    s.stage,
    CASE
        WHEN COALESCE(g.is_bended, 0) <> 1 THEN 'Flat'
        WHEN LOWER(COALESCE(si.CRTSUBITEM_NAME, p.CRTSITEM_NAME, '')) LIKE '%windshield%' THEN 'WS'
        ELSE 'Door'
    END                                                 AS subitem_kind,
    s.qty_required                                      AS qty,
    COALESCE(s.completed_at, NOW())                     AS completed_at,
    NULL                                                AS completed_by,
    NOW()                                               AS created_at
FROM JOBFORM_STAGE s
JOIN JOBFORM_GLASS g  ON g.id = s.jobform_glass_id
LEFT JOIN CRTPARTS  p  ON p.CRTSITEM_ID = g.glass_part_id
LEFT JOIN CRTSUBITEMS si ON si.CRTSUBITEM_ID = p.CRTSITEM_CRTSUBITEMID
WHERE s.status = 'Completed'
  AND s.qty_required > 0
  AND NOT EXISTS (
      SELECT 1 FROM JOBFORM_STAGE_COMPLETION_LOG l
       WHERE l.jobform_glass_id = s.jobform_glass_id
         AND l.stage = s.stage
  );