-- ============================================================================
-- Phase M6-p2c-b — per-defect root_cause + per-zone cap enforcement prep
-- ============================================================================
--
-- M6-p2c-a shipped the multi-defect child table (JOBFORM_QC_DEFECT) with
-- code / zone / size / qty / notes. The user surfaced a gap on first
-- production review: **root cause should be per-defect, not per-verdict**.
-- A glass with a Zone-C bubble caused by Environmental dust AND a Zone-B
-- scratch caused by Negligence is a real floor case; recording one
-- root_cause on the parent JOBFORM_QC row collapses the audit story
-- ("which root cause produced which defect?" becomes unanswerable).
--
-- This migration adds JOBFORM_QC_DEFECT.root_cause as a nullable
-- 4-bucket enum (same shape as JOBFORM_QC.root_cause per invariant 46)
-- and backfills it from the parent QC row's root_cause for every
-- existing child row so the multi-defect rows from p2c-a stop having
-- a NULL audit field after deploy.
--
-- The parent JOBFORM_QC.root_cause column STAYS in the schema per
-- never-drop rule (sql/CLAUDE.md). It now carries "the dominant /
-- primary root cause" for the verdict event (server-side derived from
-- the first defect element on submit) — pre-p2c-b readers (qc/list.php
-- pre-p2c-b aggregations, the M5-p2 GlassUnitDetail history view) still
-- render a sensible single root_cause per verdict.
--
-- Two changes:
--
--   (A) Add JOBFORM_QC_DEFECT.root_cause ENUM(...) NULL via the
--       information_schema column probe so re-runs are no-ops.
--
--   (B) Backfill existing child rows: copy the parent JOBFORM_QC's
--       root_cause down to every JOBFORM_QC_DEFECT row that has
--       root_cause IS NULL AND whose parent has a non-null root_cause.
--       Idempotent — re-runs find no rows to update once the
--       backfill has run.
--
-- The per-zone cap ENFORCEMENT (the actual 422 gate that refuses a
-- submission pushing a zone over its cap) lands server-side in
-- agg-api/v1/jobforms/qc_create.php — no migration needed beyond what
-- 38 already shipped (caps are in ATPD_DEFECT_LIMITS.max_qty_per_glass).
--
-- ============================================================================

-- ----------------------------------------------------------------------------
-- (A) Add JOBFORM_QC_DEFECT.root_cause idempotently
-- ----------------------------------------------------------------------------
SET @col_rc := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME = 'JOBFORM_QC_DEFECT'
       AND COLUMN_NAME = 'root_cause'
);
SET @sql_rc := IF(@col_rc = 0,
    "ALTER TABLE JOBFORM_QC_DEFECT
        ADD COLUMN root_cause
            ENUM('Environmental','Unknown','Item Defects','Negligence')
            NULL AFTER defect_size_mm",
    'SELECT 1');
PREPARE stmt FROM @sql_rc;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ----------------------------------------------------------------------------
-- (B) Backfill — copy the parent JOBFORM_QC.root_cause down to children
--     that have root_cause IS NULL. Catches the small body of p2c-a rows
--     created before this migration ran. Skips rows that already have a
--     root_cause set + rows whose parent has no root_cause (Pass+record
--     without RC, Reprocess where RC isn't meaningful).
-- ----------------------------------------------------------------------------
UPDATE JOBFORM_QC_DEFECT d
   JOIN JOBFORM_QC q ON q.id = d.qc_id
    SET d.root_cause = q.root_cause
  WHERE d.root_cause IS NULL
    AND q.root_cause IS NOT NULL;

-- ============================================================================
-- Verification
-- ============================================================================
--
--   -- Confirm column exists
--   SHOW COLUMNS FROM JOBFORM_QC_DEFECT LIKE 'root_cause';
--
--   -- Distribution of per-defect root causes
--   SELECT root_cause, COUNT(*) AS n
--     FROM JOBFORM_QC_DEFECT
--    GROUP BY root_cause
--    ORDER BY n DESC;
--
--   -- Catch any child rows still missing root_cause (Pass+record entries
--   -- where the parent had no RC; expected to remain NULL):
--   SELECT d.id, d.qc_id, d.defect_code, d.defect_zone, q.verdict
--     FROM JOBFORM_QC_DEFECT d
--     JOIN JOBFORM_QC q ON q.id = d.qc_id
--    WHERE d.root_cause IS NULL
--    ORDER BY d.id DESC
--    LIMIT 20;
--
-- ============================================================================