-- ============================================================================
-- Phase M6-p1-h8 — one-shot cleanup of rejected-and-replaced serials
-- ============================================================================
--
-- Purpose:
--   Historical state cleanup. Rejects fired BEFORE the M6-p1-h6 cascade
--   deploy left worker rows + completion rows for the rejected serial in
--   place on per-unit upstream stages (Lamination + Autoclave + Final
--   Inspection). Symptom on the floor: tap Assign on Lamination → 409
--   prereq-shortfall "only 0 unit(s) available" because the COUNT
--   includes the orphaned rejected-serial worker rows. The h6 cascade
--   was supposed to delete them; this migration retroactively cleans up.
--
--   The h8 server delta on task_assign.php also makes the prereq COUNT
--   and the qty_allocated bump's newSerialCount exclude rejected-
--   replaced serials, so the bug is defended-in-depth — but cleaning
--   the DB makes operational queries / reports honest about reality.
--
-- What it does:
--   1. Identifies all rejected-and-replaced serials: any JOBFORM_SERIAL
--      row whose `id` appears in some OTHER row's `retry_of_serial_id`.
--      (Per M6-p1, only FI Reject creates this link.)
--   2. DELETEs the rows in JOBFORM_TASK_WORKER for those serials on
--      per-unit upstream tasks (stages: Lamination, Autoclave, Final
--      Inspection). Pre-lam tasks use serial_id=0 (shared work) — those
--      rows aren't affected by serial_id filtering.
--   3. DELETEs the rows in JOBFORM_TASK_SERIAL_COMPLETION for those
--      serials on the same tasks.
--   4. Recomputes JOBFORM_TASK.qty_allocated for every touched task —
--      per-unit qty_allocated = COUNT(DISTINCT serial_id) among the
--      surviving worker rows.
--
-- Idempotency:
--   Re-runs are no-ops: the rejected-replaced set is set-defined by the
--   retry_of FK, so a second run finds zero remaining orphans to
--   delete. The recompute step writes the same value as the previous
--   run produced.
--
-- Dependencies:
--   * migration 17     — JOBFORM_TASK_WORKER + JOBFORM_TASK_SERIAL_COMPLETION + JOBFORM_SERIAL
--   * migration 28     — JOBFORM_TASK.qty_allocated
--   * migration 34     — JOBFORM_SERIAL.retry_of_serial_id (the rejected-replaced FK)
--   * task_assign.php  — h8 prereq COUNT exclusion is the rolling defence
--                        in depth; this migration cleans the historical state.
--
-- Safety:
--   * Scoped to rejected-replaced serials only — a serial that's been
--     reprocessed (Repacking / PC Removal) is NOT in the set (Reprocess
--     doesn't allocate a new retry serial per M6-p1's design; it cycles
--     the same serial back through the pipeline). So this won't disturb
--     in-flight Reprocess data.
--   * No DELETE on rows for non-rejected serials.
--   * No DELETE on rows for pre-lam tasks (serial_id=0 shared assignments).
-- ============================================================================

-- ----------------------------------------------------------------------------
-- (1) DELETE orphaned JOBFORM_TASK_WORKER rows for rejected-replaced serials
--     on per-unit upstream stages.
-- ----------------------------------------------------------------------------
DELETE w FROM JOBFORM_TASK_WORKER w
  JOIN JOBFORM_TASK t   ON t.id = w.task_id
  JOIN JOBFORM_STAGE s  ON s.id = t.jobform_stage_id
 WHERE s.stage IN ('Lamination', 'Autoclave', 'Final Inspection')
   AND w.serial_id IN (
       SELECT DISTINCT parent.id
         FROM JOBFORM_SERIAL parent
         JOIN JOBFORM_SERIAL retry
           ON retry.retry_of_serial_id = parent.id
   );

-- ----------------------------------------------------------------------------
-- (2) DELETE orphaned JOBFORM_TASK_SERIAL_COMPLETION rows for the same set.
-- ----------------------------------------------------------------------------
DELETE c FROM JOBFORM_TASK_SERIAL_COMPLETION c
  JOIN JOBFORM_TASK t   ON t.id = c.task_id
  JOIN JOBFORM_STAGE s  ON s.id = t.jobform_stage_id
 WHERE s.stage IN ('Lamination', 'Autoclave', 'Final Inspection')
   AND c.serial_id IN (
       SELECT DISTINCT parent.id
         FROM JOBFORM_SERIAL parent
         JOIN JOBFORM_SERIAL retry
           ON retry.retry_of_serial_id = parent.id
   );

-- ----------------------------------------------------------------------------
-- (3) Recompute JOBFORM_TASK.qty_allocated for per-unit upstream tasks
--     whose glass has at least one rejected-replaced serial (= the tasks
--     that COULD have been affected by the cleanup above).
--
--     For per-unit tasks: qty_allocated = COUNT(DISTINCT serial_id) of
--     the surviving worker rows. Pre-lam tasks (Glass Cutting + Film
--     Cutting + PC Cutting + Glass Bending + Printing) are NOT touched
--     because pre-lam qty_allocated tracks shared-work-batch commitment
--     and is decremented by the M6-p1-h6 cascade directly; that logic
--     stays correct.
-- ----------------------------------------------------------------------------
UPDATE JOBFORM_TASK t
   JOIN JOBFORM_STAGE s ON s.id = t.jobform_stage_id
   LEFT JOIN (
       SELECT w.task_id, COUNT(DISTINCT w.serial_id) AS distinct_serials
         FROM JOBFORM_TASK_WORKER w
        WHERE w.serial_id != 0
        GROUP BY w.task_id
   ) wc ON wc.task_id = t.id
   SET t.qty_allocated = COALESCE(wc.distinct_serials, 0)
 WHERE s.stage IN ('Lamination', 'Autoclave', 'Final Inspection')
   AND s.jobform_glass_id IN (
       SELECT DISTINCT pg.jobform_glass_id
         FROM JOBFORM_SERIAL pg
         JOIN JOBFORM_SERIAL retry
           ON retry.retry_of_serial_id = pg.id
   );

-- ============================================================================
-- Verification queries
-- ============================================================================
--
-- (A) Identify rejected-replaced serials globally:
--     SELECT parent.id, parent.temp_serial, parent.jobform_glass_id,
--            retry.id AS retry_id, retry.temp_serial AS retry_serial
--       FROM JOBFORM_SERIAL parent
--       JOIN JOBFORM_SERIAL retry ON retry.retry_of_serial_id = parent.id
--      ORDER BY parent.id;
--
-- (B) After migration, expect 0 orphans:
--     SELECT COUNT(*) FROM JOBFORM_TASK_WORKER w
--      JOIN JOBFORM_TASK t  ON t.id = w.task_id
--      JOIN JOBFORM_STAGE s ON s.id = t.jobform_stage_id
--      WHERE s.stage IN ('Lamination','Autoclave','Final Inspection')
--        AND w.serial_id IN (SELECT parent.id FROM JOBFORM_SERIAL parent
--                              JOIN JOBFORM_SERIAL retry
--                                ON retry.retry_of_serial_id = parent.id);
--     -- Expect 0.
--
-- (C) Spot-check JF 1003/26 FDL row's Lam tasks:
--     SELECT t.id, s.stage, t.section, t.qty_required, t.qty_completed, t.qty_allocated
--       FROM JOBFORM_TASK t JOIN JOBFORM_STAGE s ON s.id=t.jobform_stage_id
--      WHERE s.jobform_glass_id = <FDL_glass_id>
--        AND s.stage IN ('Lamination','Autoclave','Final Inspection')
--      ORDER BY s.stage, t.section;
--     Expect qty_allocated = 4 (FDL1..FDL4) on each Lam section task; the
--     rejected FDL5's worker rows are gone, qty_allocated dropped from 5→4.