-- ============================================================================
-- Phase M6-p2c-a — multi-defect-per-verdict child table + per-zone caps prep
-- ============================================================================
--
-- Background (CLAUDE.md §9, invariant 46, M6 / M6-p1 / M6-p2 narrative):
--
--   Through M6-p1 and M6-p2{a,b} the FI verdict screen records ONE defect
--   per Reject (or Pass-with-defect-record) JOBFORM_QC row — the
--   `defect_code` / `defect_zone` / `defect_size_mm` columns added to
--   JOBFORM_QC by the original M1 schema (§15 reserved the columns; M6
--   started populating them via the ATPD picker). Practical floor reality
--   is that one inspection routinely turns up MULTIPLE defects on the same
--   physical unit — e.g. a windshield can carry a Zone-C bubble AT 1.4mm
--   AND a Zone-B scratch at 32mm AND a Zone-A dig at 0.5mm in the same FI
--   pass. Forcing one Reject row per defect (current shape) inflates the
--   M8 Evaluation Report's defect counts (3 entries vs 1 verdict event),
--   makes the qty_rejected cascade per-serial counter drift (FI cascade
--   bumps qty_rejected += 1 per row, but the physical unit is one unit),
--   and bloats the photo-upload flow (every defect-as-row gets its own
--   qcId so photos taken in one inspection have to be split N ways).
--
--   M6-p2c-a fixes the data model: ONE JOBFORM_QC row per verdict event
--   (matches the physical inspection), with a child table
--   `JOBFORM_QC_DEFECT` holding 0..N defects per verdict. Pre-p2c clients
--   keep working — they send single defect_code/zone/size_mm fields on the
--   body, the server back-fills a single JOBFORM_QC_DEFECT row from those
--   fields (the JOBFORM_QC columns are ALSO populated for backwards-compat
--   reads on stages.php and qc/list.php). New clients send `defects[]`
--   array on the body and the server fans out one child row per element.
--
--   Per-zone qty caps + min-distance enforcement (the second half of
--   M6-p2c per CLAUDE.md §9) lands in M6-p2c-b. This migration ships the
--   COLUMNS but no enforcement code uses them yet — they're populated for
--   the catalogue rows that have an obvious cap (VD QAC §"ALLOWED QUANTITY
--   OF DEFECTS" specifies caps per zone for bubbles + scratches; everything
--   else stays NULL for now).
--
-- Three changes:
--
--   (A) NEW TABLE — `JOBFORM_QC_DEFECT`. One row per defect within a
--       JOBFORM_QC verdict. Columns mirror what JOBFORM_QC carried before
--       plus parent FK. Append-only (no UPDATE / no soft-delete) so the
--       audit story stays clean.
--
--   (B) NEW COLUMNS on `ATPD_DEFECT_LIMITS` — `max_qty_per_glass INT NULL`
--       (cap per zone — e.g. Zone A allows max 6 defects per glass per
--       VD QAC; NULL means no qty cap) and `min_distance_mm DECIMAL(7,3)
--       NULL` (defects must be at least N mm apart on the glass; NULL =
--       no spacing rule).
--
--       Seeded values per VD QAC §"ALLOWED QUANTITY OF DEFECTS":
--         Zone A (central viewing — strictest): max 6 / 100 mm min dist
--         Zone B (50mm border):                 max 10 / 100 mm min dist
--         Zone C (obscuration band):            max 15 / 100 mm min dist
--       Applied only to defect types where qty caps make sense — bubbles,
--       digs, stones-and-knots, scratches, inside-dirt, lint-hair. Not
--       allowed types (CRACK / DELAMINATION / CRACKING) stay NULL because
--       any cap is meaningless — one is too many.
--
--   (C) `JOBFORM_QC.defect_code` / `defect_zone` / `defect_size_mm` STAY
--       in the schema per CLAUDE.md never-drop rule (sql/CLAUDE.md). They
--       become "single-defect fallback fields" — the server-side code
--       writes the FIRST defect from the `defects[]` array into these
--       columns so legacy readers (stages.php pre-p2c-a, qc/list.php
--       pre-p2c-a, mobile clients pre-p2c-a) keep rendering correctly.
--       The child table is the canonical multi-defect store; legacy
--       columns are the back-compat surface.
--
-- All changes idempotent via information_schema probes / CREATE TABLE
-- IF NOT EXISTS / ON DUPLICATE KEY UPDATE. New tables — no legacy
-- row-size budget concern (the CRTSALEGOODS / EMPLOYEE dance doesn't
-- apply; ATPD_DEFECT_LIMITS is slim and was created in migration 33).
--
-- ============================================================================

-- ----------------------------------------------------------------------------
-- (A) JOBFORM_QC_DEFECT — multi-defect child table
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS JOBFORM_QC_DEFECT (
    id              INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    qc_id           INT UNSIGNED  NOT NULL,
    defect_code     VARCHAR(20)   NOT NULL,
    defect_zone     ENUM('A','B','C') NULL,
    defect_size_mm  DECIMAL(7,3)  NULL,
    qty             INT UNSIGNED  NOT NULL DEFAULT 1,
    notes           VARCHAR(500)  NULL,
    created_at      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_qc_defect_qc (qc_id),
    KEY idx_qc_defect_code (defect_code),
    KEY idx_qc_defect_zone (defect_zone),
    CONSTRAINT fk_qc_defect_qc FOREIGN KEY (qc_id)
        REFERENCES JOBFORM_QC(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- (B) ATPD_DEFECT_LIMITS — extend with per-zone qty cap + min-distance
-- ----------------------------------------------------------------------------
-- Add columns idempotently via information_schema probes.
SET @col_qty := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME = 'ATPD_DEFECT_LIMITS'
       AND COLUMN_NAME = 'max_qty_per_glass'
);
SET @sql_qty := IF(@col_qty = 0,
    'ALTER TABLE ATPD_DEFECT_LIMITS ADD COLUMN max_qty_per_glass INT NULL AFTER limit_unit',
    'SELECT 1');
PREPARE stmt FROM @sql_qty;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_dist := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME = 'ATPD_DEFECT_LIMITS'
       AND COLUMN_NAME = 'min_distance_mm'
);
SET @sql_dist := IF(@col_dist = 0,
    'ALTER TABLE ATPD_DEFECT_LIMITS ADD COLUMN min_distance_mm DECIMAL(7,3) NULL AFTER max_qty_per_glass',
    'SELECT 1');
PREPARE stmt FROM @sql_dist;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ----------------------------------------------------------------------------
-- Seed: per-zone qty caps + min-distance per VD QAC §"ALLOWED QUANTITY OF
-- DEFECTS" for sized defects. Applied only to defect types where qty caps
-- make sense (bubbles / digs / stones / scratches / inside-dirt / lint-hair).
-- Not-allowed types stay NULL (any qty would be too many).
--
-- The cap is "max defects per glass per zone" per VD QAC convention.
-- min_distance_mm is "defects must be at least N mm apart on the glass".
-- ----------------------------------------------------------------------------
UPDATE ATPD_DEFECT_LIMITS l
   JOIN ATPD_DEFECT_TYPES t ON t.id = l.defect_type_id
    SET l.max_qty_per_glass =
            CASE l.area_code
                WHEN 'A' THEN 6
                WHEN 'B' THEN 10
                WHEN 'C' THEN 15
            END,
        l.min_distance_mm = 100.000
  WHERE l.is_allowed = 1
    AND t.code IN ('BUBBLE','OPEN_BUBBLE','STONE_KNOT','DIG',
                   'INSIDE_DIRT','SCRATCH','LINT_HAIR');

-- ============================================================================
-- Verification
-- ============================================================================
--
--   -- Child table shape
--   SHOW CREATE TABLE JOBFORM_QC_DEFECT;
--
--   -- Cap seed: expect ~21 rows (7 defects × 3 areas) with caps populated
--   SELECT t.code, l.area_code, l.is_allowed, l.limit_value, l.limit_unit,
--          l.max_qty_per_glass, l.min_distance_mm
--     FROM ATPD_DEFECT_LIMITS l
--     JOIN ATPD_DEFECT_TYPES  t ON t.id = l.defect_type_id
--    WHERE l.max_qty_per_glass IS NOT NULL
--    ORDER BY t.sort_order, l.area_code;
--
--   -- The not-allowed defect types stay NULL on caps:
--   SELECT t.code, l.area_code, l.is_allowed, l.max_qty_per_glass
--     FROM ATPD_DEFECT_LIMITS l
--     JOIN ATPD_DEFECT_TYPES  t ON t.id = l.defect_type_id
--    WHERE t.code IN ('CRACK','DELAMINATION','CRACKING');
--   -- Expect max_qty_per_glass IS NULL on every row.
--
-- ============================================================================