-- ============================================================
-- Phase M6-p1-h2 — JOBFORM.qty_rejected cascade column
-- ============================================================
--
-- Adds qty_rejected to JOBFORM. The other three cascade levels
-- (JOBFORM_TASK, JOBFORM_STAGE, JOBFORM_GLASS) already have
-- qty_rejected from migration 17 (the M1 schema baseline). The
-- top-level JOBFORM was the only one missing the column.
--
-- Per CLAUDE.md §15 and the M6-p1-h2 cascade work in
-- qc_create.php: when an FI Reject fires, qty_rejected bumps
-- by 1 on the rejected unit's TASK, the FI STAGE, the GLASS,
-- AND the JOBFORM, all inside the same db_tx() that already
-- handles the upstream qty_completed cascade per M6-p1.
--
-- Backfill recomputes JOBFORM.qty_rejected = SUM of
-- JOBFORM_GLASS.qty_rejected across the JF, mirroring the M4-p2
-- shape that maintains JOBFORM.qty_completed = SUM(glasses).
-- Idempotent: existing rows get the recomputed value; new rows
-- default to 0 via the column default.
--
-- Glass-level backfill recomputes JOBFORM_GLASS.qty_rejected
-- from JOBFORM_QC where verdict='Reject' AND jobform_glass_id
-- matches AND task's parent stage is 'Final Inspection' — the
-- count of "actual FI rejections" for the glass. Stage-level
-- backfill keeps the simpler "leave existing values alone"
-- posture since the M6-p1 production deploys haven't been
-- bumping stage.qty_rejected; the M6-p1-h2 cascade onwards
-- will bring it into line on subsequent rejections.
--
-- Idempotent via information_schema column probe.
--
-- ============================================================

-- Step 1 — add the column to JOBFORM if missing.
SET @col_exists := (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME = 'JOBFORM'
       AND COLUMN_NAME = 'qty_rejected'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE JOBFORM ADD COLUMN qty_rejected INT UNSIGNED NOT NULL DEFAULT 0 AFTER qty_completed',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Step 2 — recompute JOBFORM_GLASS.qty_rejected from JOBFORM_QC
-- (count of FI Reject verdicts on the glass). This is the
-- canonical "how many FI Rejects has this glass seen" value.
-- Pre-M6-p1-h2 cascade didn't write glass.qty_rejected, so the
-- column is essentially uninitialised for production data — the
-- backfill sets it to the right value once.
UPDATE JOBFORM_GLASS g
   SET qty_rejected = COALESCE((
       SELECT COUNT(*)
         FROM JOBFORM_QC q
         JOIN JOBFORM_TASK t ON t.id = q.task_id
         JOIN JOBFORM_STAGE s ON s.id = t.jobform_stage_id
        WHERE q.jobform_glass_id = g.id
          AND q.verdict = 'Reject'
          AND s.stage = 'Final Inspection'
   ), 0);

-- Step 3 — JOBFORM.qty_rejected = SUM(glasses.qty_rejected).
UPDATE JOBFORM j
   SET qty_rejected = COALESCE((
       SELECT SUM(qty_rejected) FROM JOBFORM_GLASS WHERE jobform_id = j.id
   ), 0);