-- AGG-UAE — Migration 58 — M7-p5d-h16 JOBFORM cascade backfill.
--
-- Backfills the JOBFORM_STAGE + JOBFORM status + qty_completed
-- columns that the M7-p5b set_assign.php and M7-p5d-h8
-- mark_cell_complete.php endpoints SKIPPED since they shipped. The
-- M4-p2 backfill (migration 23) did the equivalent at its time but
-- only covered task_assign.php / task_complete.php; any JF whose
-- workflow ran exclusively through Auto Schedule since M7-p5b
-- stayed at 'Not Started' on the Job Form list even though the
-- supervisors had been working on it all day.
--
-- This migration is IDEMPOTENT — re-running is safe. It catches:
--   (a) JFs that have any worker assignment → bump 'Not Started'
--       to 'On Process'.
--   (b) JOBFORM_STAGE.qty_completed = MIN(JOBFORM_TASK.qty_completed)
--       for every stage.
--   (c) JOBFORM_STAGE.status flips — Pending → In Progress when any
--       task progress; In Progress → Completed when no incomplete
--       tasks. Skipped + already-Completed left alone.
--   (d) JOBFORM.qty_completed = SUM(JOBFORM_GLASS.qty_completed).
--   (e) JOBFORM.status → Completed when every glass full.
--
-- Run order: apply this migration AFTER deploying the
-- jobform_cascade.php library + the patched set_assign.php +
-- mark_cell_complete.php. From here forward the live cascade keeps
-- things in sync; this backfill is a one-shot recovery for the
-- accumulated stale rows.

-- (a) Not Started → On Process for any JF with worker assignments.
UPDATE JOBFORM jf
   SET jf.status = 'On Process'
 WHERE jf.status = 'Not Started'
   AND EXISTS (
     SELECT 1
       FROM JOBFORM_GLASS g
       JOIN JOBFORM_STAGE s  ON s.jobform_glass_id = g.id
       JOIN JOBFORM_TASK  t  ON t.jobform_stage_id = s.id
       JOIN JOBFORM_TASK_WORKER w ON w.task_id = t.id
      WHERE g.jobform_id = jf.id
   );

-- (b) JOBFORM_STAGE.qty_completed = MIN(JOBFORM_TASK.qty_completed)
--     for every stage that has at least one task.
UPDATE JOBFORM_STAGE s
  JOIN (
    SELECT jobform_stage_id, COALESCE(MIN(qty_completed), 0) AS min_done
      FROM JOBFORM_TASK
     GROUP BY jobform_stage_id
  ) m ON m.jobform_stage_id = s.id
   SET s.qty_completed = m.min_done;

-- (c1) Pending → In Progress where qty_completed > 0 AND status='Pending'.
UPDATE JOBFORM_STAGE s
   SET s.status = 'In Progress',
       s.started_at = COALESCE(s.started_at, NOW())
 WHERE s.status = 'Pending'
   AND s.qty_completed > 0;

-- (c2) Pending / In Progress → Completed when all tasks done.
--      Skip Skipped + already-Completed; don't downgrade.
UPDATE JOBFORM_STAGE s
   SET s.status = 'Completed',
       s.completed_at = COALESCE(s.completed_at, NOW())
 WHERE s.status IN ('Pending', 'In Progress')
   AND s.qty_required > 0
   AND NOT EXISTS (
     SELECT 1 FROM JOBFORM_TASK t
      WHERE t.jobform_stage_id = s.id
        AND t.qty_completed < t.qty_required
   );

-- (d) JOBFORM.qty_completed = SUM(JOBFORM_GLASS.qty_completed).
UPDATE JOBFORM jf
  JOIN (
    SELECT jobform_id, COALESCE(SUM(qty_completed), 0) AS total
      FROM JOBFORM_GLASS
     GROUP BY jobform_id
  ) g ON g.jobform_id = jf.id
   SET jf.qty_completed = g.total;

-- (e) JOBFORM.status → 'Completed' when every glass is fully done.
UPDATE JOBFORM jf
   SET jf.status = 'Completed'
 WHERE jf.status IN ('Not Started', 'On Process', 'In Progress')
   AND EXISTS (SELECT 1 FROM JOBFORM_GLASS WHERE jobform_id = jf.id)
   AND NOT EXISTS (
     SELECT 1 FROM JOBFORM_GLASS g
      WHERE g.jobform_id = jf.id
        AND g.qty_completed < g.qty_required
   );