-- ============================================================================
-- Phase M6-p1-h4 — VD QAC adoption + defect_code widening
-- ============================================================================
--
-- Purpose:
--   (1) Widen JOBFORM_QC.defect_code from VARCHAR(10) → VARCHAR(20) so
--       full-length codes (DELAMINATION = 12 chars, OPEN_BUBBLE = 11,
--       REAM_STRING = 11, INSIDE_DIRT = 11) fit. The migration-17
--       column was specced for short codes (FL/CD/etc.) but migration
--       33 seeded the ATPD_DEFECT_TYPES.code column at VARCHAR(20) and
--       the mobile sends the same value back on POST /qc — so any FI
--       Pass+Record or Reject with a long-code defect was failing with
--       "data too long for field defect_code".
--
--   (2) Re-seed ATPD_DEFECT_LIMITS to follow the VD QAC (Vehicle Dynamics
--       Quality Acceptance Criteria) convention for commercial bullet-
--       resistant glass with a black ceramic enamel band hiding the
--       perimeter. The migration-33 seed followed ATPD-2352R verbatim
--       which defines Area A as the concealed edge under the frame
--       (zero defects allowed by default — appropriate for military
--       transparent armour where the bond to the gasket is structurally
--       critical). For AGG's COMMERCIAL glass the convention inverts:
--         * Area A = CENTRAL viewing area (strictest visible-defect
--                    tolerance — operator looks through here).
--         * Area B = the 50mm BORDER around the daylight opening
--                    (intermediate).
--         * Area C = the OBSCURATION band / under-frame zone (most
--                    permissive — defects there are visually hidden
--                    by the black ceramic print or the frame edge).
--       This matches the VEHICLE DYNAMICS_QAC_WITH DEFECTS reference
--       document (Level I Windshield criteria) the user supplied. Under
--       the m33 seeding "almost every defect returned Reject when the
--       inspector picked Area A" — because the spec's "concealed edge
--       defaults to zero unless overridden" rule applied uniformly. With
--       the h4 re-seed every non-crack defect carries a sensible size
--       limit in A (strict but allowed) and progressively-larger limits
--       in B then C.
--
-- Idempotency:
--   * defect_code widen via INFORMATION_SCHEMA probe (re-runnable; no-op
--     when already at VARCHAR(20)).
--   * Limits re-seed via INSERT ... ON DUPLICATE KEY UPDATE on (defect_type_id,
--     area_code). Re-runs refresh existing rows; new rows insert. The
--     migration-33 seed used the same shape and the UNIQUE KEY uq_type_area
--     gates the upsert.
--
-- Dependencies:
--   * sql/17-phase-m1-jobforms.sql           — JOBFORM_QC.defect_code base.
--   * sql/33-phase-m6-atpd-defect-catalogue.sql — ATPD_DEFECT_TYPES (codes)
--                                            + ATPD_DEFECT_LIMITS table.
--   * agg-api/v1/jobforms/qc_create.php      — writes JOBFORM_QC.defect_code.
--   * agg-api/v1/atpd/defect_types.php       — reads ATPD_DEFECT_LIMITS.
-- ============================================================================

SET @schema_db = DATABASE();

-- ----------------------------------------------------------------------------
-- (1) Widen JOBFORM_QC.defect_code to VARCHAR(20)
-- ----------------------------------------------------------------------------
SET @col_len = (
    SELECT CHARACTER_MAXIMUM_LENGTH
      FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = @schema_db
       AND TABLE_NAME = 'JOBFORM_QC'
       AND COLUMN_NAME = 'defect_code'
);
SET @sql = IF(@col_len IS NULL OR @col_len < 20,
    'ALTER TABLE JOBFORM_QC MODIFY COLUMN defect_code VARCHAR(20) NULL',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ----------------------------------------------------------------------------
-- (2) Re-seed ATPD_DEFECT_LIMITS with VD QAC convention.
--     New area semantics:
--       A = Central viewing area (strictest).
--       B = Border within 50mm of daylight-opening perimeter.
--       C = Obscuration band / under-frame area (most permissive).
--     Cracks/Delamination/Crazing stay "not allowed anywhere" — both
--     sources agree these are structural defects scrapping the unit
--     regardless of location.
--     Numerical limits follow VD QAC Level I Windshield criteria —
--     the AGG commercial product reference standard.
-- ----------------------------------------------------------------------------

INSERT INTO ATPD_DEFECT_LIMITS (defect_type_id, area_code, is_allowed, limit_value, limit_unit, note)
SELECT t.id, x.area_code, x.is_allowed, x.limit_value, x.limit_unit, x.note
FROM ATPD_DEFECT_TYPES t
JOIN (
    -- DELAMINATION — structural defect, never allowed
    SELECT 'DELAMINATION' AS code, 'A' AS area_code, 0 AS is_allowed, NULL AS limit_value, NULL AS limit_unit,
           'Not allowed in any area — structural separation scraps the unit' AS note UNION ALL
    SELECT 'DELAMINATION','B',0,NULL,NULL,'Not allowed in any area — structural separation scraps the unit' UNION ALL
    SELECT 'DELAMINATION','C',0,NULL,NULL,'Not allowed in any area — structural separation scraps the unit' UNION ALL

    -- CRACKING / CRAZING / CLOUDING — structural defect, never allowed
    SELECT 'CRACKING','A',0,NULL,NULL,'Not allowed in any area — scraps the unit' UNION ALL
    SELECT 'CRACKING','B',0,NULL,NULL,'Not allowed in any area — scraps the unit' UNION ALL
    SELECT 'CRACKING','C',0,NULL,NULL,'Not allowed in any area — scraps the unit' UNION ALL

    -- CRACK — structural defect, never allowed
    SELECT 'CRACK','A',0,NULL,NULL,'Cracks not allowed in Area A, B, or C — scraps the unit' UNION ALL
    SELECT 'CRACK','B',0,NULL,NULL,'Cracks not allowed in Area A, B, or C — scraps the unit' UNION ALL
    SELECT 'CRACK','C',0,NULL,NULL,'Cracks not allowed in Area A, B, or C — scraps the unit' UNION ALL

    -- BUBBLE — VD QAC L1 Windshield "Interlayer Bubbles" sizes
    SELECT 'BUBBLE','A',1,0.7874,'mm','Max 0.79 mm in central viewing Area A — strictest visible-defect tolerance' UNION ALL
    SELECT 'BUBBLE','B',1,1.1430,'mm','Max 1.14 mm in 50mm border Area B — intermediate tolerance' UNION ALL
    SELECT 'BUBBLE','C',1,1.5748,'mm','Max 1.57 mm in obscuration / under-frame Area C — hidden by black ceramic band' UNION ALL

    -- OPEN_BUBBLE — VD QAC L1 Windshield "Cateyes (Open)" sizes
    SELECT 'OPEN_BUBBLE','A',1,0.7874,'mm','Max 0.79 mm in central viewing Area A (strike face open inclusion)' UNION ALL
    SELECT 'OPEN_BUBBLE','B',1,1.1430,'mm','Max 1.14 mm in 50mm border Area B (strike face)' UNION ALL
    SELECT 'OPEN_BUBBLE','C',1,1.5748,'mm','Max 1.57 mm in obscuration Area C (strike face)' UNION ALL

    -- STONE_KNOT — VD QAC L1 Windshield "Cateyes (Closed)" sizes
    SELECT 'STONE_KNOT','A',1,0.7874,'mm','Max 0.79 mm in central viewing Area A' UNION ALL
    SELECT 'STONE_KNOT','B',1,1.1430,'mm','Max 1.14 mm in 50mm border Area B' UNION ALL
    SELECT 'STONE_KNOT','C',1,1.5748,'mm','Max 1.57 mm in obscuration Area C' UNION ALL

    -- DIG — VD QAC L1 Windshield "Crush/Dig" (light) sizes
    SELECT 'DIG','A',1,0.7874,'mm','Max 0.79 mm in central viewing Area A — light dig only' UNION ALL
    SELECT 'DIG','B',1,1.1430,'mm','Max 1.14 mm in 50mm border Area B — light dig only' UNION ALL
    SELECT 'DIG','C',1,1.5748,'mm','Max 1.57 mm in obscuration Area C — light dig only' UNION ALL

    -- INSIDE_DIRT — VD QAC L1 Windshield "Dirt / Contamination" sizes
    SELECT 'INSIDE_DIRT','A',1,0.7874,'mm','Max 0.79 mm in central viewing Area A; not readily visible to landscape' UNION ALL
    SELECT 'INSIDE_DIRT','B',1,1.1430,'mm','Max 1.14 mm in 50mm border Area B' UNION ALL
    SELECT 'INSIDE_DIRT','C',1,1.5748,'mm','Max 1.57 mm in obscuration Area C' UNION ALL

    -- REAM_STRING — VD QAC: "not readily visible at angle > 45° to glass surface"
    --   (ATPD-2352R used 30°; VD QAC uses the more lenient 45° threshold, matching AGG's actual standard)
    SELECT 'REAM_STRING','A',1,45.000,'deg','Not readily visible at viewing angle > 45° to glass surface (VD QAC)' UNION ALL
    SELECT 'REAM_STRING','B',1,45.000,'deg','Not readily visible at viewing angle > 45° to glass surface (VD QAC)' UNION ALL
    SELECT 'REAM_STRING','C',1,45.000,'deg','Not readily visible at viewing angle > 45° to glass surface (VD QAC)' UNION ALL

    -- SCRATCH — VD QAC L1 Windshield sizes (light scratches)
    --   Area A: 50.8mm light; Area B: 76.2mm light; Area C: 76.2mm medium.
    SELECT 'SCRATCH','A',1,50.800,'mm','Max 50.8 mm (2 in) light scratches in central viewing Area A; ASTM F428 grade ≤ 6 acceptable' UNION ALL
    SELECT 'SCRATCH','B',1,76.200,'mm','Max 76.2 mm (3 in) light scratches in 50mm border Area B; ASTM F428 grade ≤ 6' UNION ALL
    SELECT 'SCRATCH','C',1,76.200,'mm','Max 76.2 mm (3 in) medium scratches in obscuration Area C — covered by black band' UNION ALL

    -- CRUSH — VD QAC L1 Windshield "Crush/Dig" (light) — paired with size limits
    SELECT 'CRUSH','A',1,0.7874,'mm','Max 0.79 mm light crush in central viewing Area A' UNION ALL
    SELECT 'CRUSH','B',1,1.1430,'mm','Max 1.14 mm light crush in 50mm border Area B' UNION ALL
    SELECT 'CRUSH','C',1,1.5748,'mm','Max 1.57 mm light crush in obscuration Area C' UNION ALL

    -- LINT_HAIR — VD QAC L1 Windshield length limits per area
    SELECT 'LINT_HAIR','A',1,12.700,'mm','Max 12.7 mm (0.5 in) in central viewing Area A' UNION ALL
    SELECT 'LINT_HAIR','B',1,19.050,'mm','Max 19.05 mm (0.75 in) in 50mm border Area B' UNION ALL
    SELECT 'LINT_HAIR','C',1,25.400,'mm','Max 25.4 mm (1 in) in obscuration Area C' UNION ALL

    -- ORGANIC (insects / organic matter) — visibility-distance based, same across areas
    SELECT 'ORGANIC','A',1,0.900,'m','Not detectable at distance > 0.9 m (3 ft), looking perpendicular' UNION ALL
    SELECT 'ORGANIC','B',1,0.900,'m','Not detectable at distance > 0.9 m (3 ft), looking perpendicular' UNION ALL
    SELECT 'ORGANIC','C',1,0.900,'m','Not detectable at distance > 0.9 m (3 ft), looking perpendicular' UNION ALL

    -- SURFACE (catch-all visible surface defect) — visibility-distance
    SELECT 'SURFACE','A',1,3.000,'m','Not detectable at distance > 3 m (10 ft) — central viewing Area A' UNION ALL
    SELECT 'SURFACE','B',1,3.000,'m','Not detectable at distance > 3 m (10 ft) — 50mm border Area B' UNION ALL
    SELECT 'SURFACE','C',1,3.000,'m','Not detectable at distance > 3 m (10 ft) — obscuration Area C' UNION ALL

    -- STRIAE (interlayer striae) — visibility-distance
    SELECT 'STRIAE','A',1,3.000,'m','Not detectable at distance > 3 m (10 ft), looking perpendicular' UNION ALL
    SELECT 'STRIAE','B',1,3.000,'m','Not detectable at distance > 3 m (10 ft), looking perpendicular' UNION ALL
    SELECT 'STRIAE','C',1,3.000,'m','Not detectable at distance > 3 m (10 ft), looking perpendicular'
) AS x ON x.code = t.code
ON DUPLICATE KEY UPDATE
    is_allowed = VALUES(is_allowed),
    limit_value = VALUES(limit_value),
    limit_unit = VALUES(limit_unit),
    note = VALUES(note);

-- ============================================================================
-- Verification
-- ============================================================================
--
--   SELECT t.code, l.area_code, l.is_allowed, l.limit_value, l.limit_unit, l.note
--     FROM ATPD_DEFECT_TYPES t
--     JOIN ATPD_DEFECT_LIMITS l ON l.defect_type_id = t.id
--    ORDER BY t.sort_order, l.area_code;
--
-- Expect 14 defect types × 3 areas = 42 rows. CRACK / CRACKING /
-- DELAMINATION carry is_allowed=0 across all three areas. Every other
-- defect carries is_allowed=1 with a size or distance limit; A is the
-- strictest, B intermediate, C the most permissive.
--
--   SELECT column_name, character_maximum_length
--     FROM INFORMATION_SCHEMA.COLUMNS
--    WHERE table_name='JOBFORM_QC' AND column_name='defect_code';
--
-- Expect character_maximum_length = 20.