-- ============================================================================
-- Phase M6 — ATPD-2352 defect catalogue + per-area tolerance limits
-- ============================================================================
--
-- Adapts the essence of ATPD-2352R Section 4.2.3 + Table VII + Glossary
-- (Appendix A) for AGG's QC Final Inspection picker. The full military
-- spec covers ballistic protection classes, optical tests, environmental
-- chambers, and packaging requirements — we adopt only the visual-defect
-- catalogue + the 3-zone (Area A/B/C) spatial tolerance model.
--
-- Two new tables:
--
--   ATPD_DEFECT_TYPES — the 14-row catalogue from Table VII + Appendix A
--     Glossary. Each row carries an `assessment_type` enum describing HOW
--     the defect is measured: size_mm (gaseous inclusions, digs, stones),
--     length_mm (scratches), visibility_distance (crush, lint, surface
--     defects, interlayer striae), angle (ream and string), not_allowed
--     (delamination, cracking, crazing, clouding, cracks — zero tolerance
--     everywhere). `requires_size_input` flags whether the picker UI
--     should prompt for a measured size; visibility/angle/not_allowed
--     types don't need a numeric input.
--
--   ATPD_DEFECT_LIMITS — per-(defect, area) tolerance row. Three rows per
--     defect type (one per Area A/B/C) so the picker can resolve the
--     limit in one lookup. `limit_value` is the numeric threshold in
--     `limit_unit`; `is_allowed=0` rows skip the threshold (Area A on
--     every defect, plus the always-disallowed types).
--
-- Per CLAUDE.md §15 + invariant 39, neither table uses AUTO_INCREMENT on
-- legacy decimal PKs. These are NEW tables so the standard InnoDB AI is
-- fine — the legacy row-size budget issue from CRTSALEGOODS / EMPLOYEE
-- doesn't apply.
--
-- Per CLAUDE.md §11 deploy convention, the migration is idempotent:
--   - CREATE TABLE IF NOT EXISTS for both tables.
--   - Seed via INSERT ... ON DUPLICATE KEY UPDATE so a re-run refreshes
--     the catalogue values without colliding (the `code` column on
--     ATPD_DEFECT_TYPES is UNIQUE; the (defect_type_id, area_code) pair
--     on ATPD_DEFECT_LIMITS is UNIQUE).
--
-- ============================================================================

CREATE TABLE IF NOT EXISTS ATPD_DEFECT_TYPES (
    id                    INT UNSIGNED NOT NULL AUTO_INCREMENT,
    code                  VARCHAR(20)  NOT NULL,
    name                  VARCHAR(100) NOT NULL,
    description           VARCHAR(500) NULL,
    assessment_type       ENUM('size_mm','length_mm','visibility_distance',
                               'angle','not_allowed','subjective') NOT NULL,
    requires_size_input   TINYINT(1)   NOT NULL DEFAULT 0,
    sort_order            INT          NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE KEY uq_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ATPD_DEFECT_LIMITS (
    id                INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    defect_type_id    INT UNSIGNED  NOT NULL,
    area_code         ENUM('A','B','C') NOT NULL,
    is_allowed        TINYINT(1)    NOT NULL DEFAULT 1,
    limit_value       DECIMAL(7,3)  NULL,
    limit_unit        VARCHAR(10)   NULL,
    note              VARCHAR(500)  NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_defect_area (defect_type_id, area_code),
    KEY idx_defect_type (defect_type_id),
    CONSTRAINT fk_atpd_limits_type FOREIGN KEY (defect_type_id)
        REFERENCES ATPD_DEFECT_TYPES(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- Seed: 14 defect types from ATPD-2352R Table VII + Appendix A Glossary
-- ----------------------------------------------------------------------------
INSERT INTO ATPD_DEFECT_TYPES (code, name, description, assessment_type, requires_size_input, sort_order) VALUES
    ('DELAMINATION',   'Delamination or bond separation',
        'Separation of bonded transparent armor into visibly separated layers. Often suppressed by fluid pulled into the space — appears or disappears with humidity. Bond failure indicator.',
        'not_allowed', 0, 10),
    ('CRACKING',       'Cracking, crazing, or clouding',
        'Network of microscopic surface cracks visible as rainbow glint; crazing of the interlayer producing smoky / foggy appearance.',
        'not_allowed', 0, 20),
    ('CRACK',          'Crack',
        'Break in the glass, ceramic, or plastic oriented in the thickness direction of any layer of the laminate. Reject in all areas.',
        'not_allowed', 0, 30),
    ('BUBBLE',         'Gaseous inclusion (bubble)',
        'A gas pocket in the interlayer or between glass and interlayer; or an elongated bubble in the glass. Per ASTM C1036-06 / C1172-09. Measure longest dimension.',
        'size_mm', 1, 40),
    ('OPEN_BUBBLE',    'Open gaseous inclusion (strike face only)',
        'A gaseous inclusion that is open to the strike-face surface. Stricter tolerance than a closed bubble because moisture / contaminants can enter.',
        'size_mm', 1, 50),
    ('STONE_KNOT',     'Stones and knots',
        'Stone: crystalline inclusion in the glass. Knot: inhomogeneity in the form of a vitreous lump. Measure longest dimension.',
        'size_mm', 1, 60),
    ('DIG',            'Dig',
        'Per ASTM C1036-06: deep, short scratch. Measure longest dimension.',
        'size_mm', 1, 70),
    ('INSIDE_DIRT',    'Inside dirt',
        'Foreign matter trapped inside the laminate (between layers). Per ASTM C1172-09. Measure longest dimension.',
        'size_mm', 1, 80),
    ('REAM_STRING',    'Ream and string',
        'Linear distortion as a result of non-homogeneous layers of flat glass (ream); straight or curled line from slow solution of sand / foreign matter (string). Assessed by viewing angle.',
        'angle', 0, 90),
    ('SCRATCH',        'Scratches and rubs',
        'Per ASTM C1036-06: damage in line form caused by an object moved across the glass. Rub: frosted-appearance abrasion. ASTM F428 (glass) / F548 (plastics) grading. Measure length.',
        'length_mm', 1, 100),
    ('CRUSH',          'Crush',
        'Per ASTM C1036-06: lightly pitted condition with a dull gray appearance. Visibility-based assessment.',
        'visibility_distance', 0, 110),
    ('LINT_HAIR',      'Lint and hair',
        'Lint: short fibers of yarn or fabric trapped within the laminate. Hair: slender pigmented filament. Per ASTM C1172-09. Visibility-based assessment.',
        'visibility_distance', 0, 120),
    ('ORGANIC',        'Insects or other organic matter',
        'Organic detritus trapped between layers. Visibility-based assessment.',
        'visibility_distance', 0, 130),
    ('SURFACE',        'Surface defects',
        'Defects other than the named inclusion types — anything that hinders vision through the system but doesn''t fit another category. Visibility-based.',
        'visibility_distance', 0, 140),
    ('STRIAE',         'Interlayer striae',
        'A cord of low intensity in the interlayer per MIL-HDBK-722. Visibility-based at perpendicular viewing.',
        'visibility_distance', 0, 150)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    description = VALUES(description),
    assessment_type = VALUES(assessment_type),
    requires_size_input = VALUES(requires_size_input),
    sort_order = VALUES(sort_order);

-- ----------------------------------------------------------------------------
-- Seed: per-(defect, area) limits from Table VII
-- Area A is concealed by frame/gasket — zero defects allowed.
-- Area B is within 50mm of the concealed edge — intermediate tolerance.
-- Area C is the remaining central area — strictest tolerance.
-- ----------------------------------------------------------------------------

-- Helper: re-seed via subquery against the catalogue we just inserted.
-- Each row carries (defect_code, area, is_allowed, limit_value, limit_unit, note).
-- The subquery resolves defect_type_id by code so the migration stays
-- idempotent across re-runs (no hardcoded auto-increment ids).

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 — not allowed anywhere
    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' AS note UNION ALL
    SELECT 'DELAMINATION','B',0,NULL,NULL,'Not allowed in any area' UNION ALL
    SELECT 'DELAMINATION','C',0,NULL,NULL,'Not allowed in any area' UNION ALL

    -- CRACKING / CRAZING / CLOUDING — not allowed anywhere
    SELECT 'CRACKING','A',0,NULL,NULL,'Not allowed in any area' UNION ALL
    SELECT 'CRACKING','B',0,NULL,NULL,'Not allowed in any area' UNION ALL
    SELECT 'CRACKING','C',0,NULL,NULL,'Not allowed in any area' UNION ALL

    -- CRACK — not allowed anywhere
    SELECT 'CRACK','A',0,NULL,NULL,'Cracks not allowed in Area A, B, or C' UNION ALL
    SELECT 'CRACK','B',0,NULL,NULL,'Cracks not allowed in Area A, B, or C' UNION ALL
    SELECT 'CRACK','C',0,NULL,NULL,'Cracks not allowed in Area A, B, or C' UNION ALL

    -- BUBBLE — size limit
    SELECT 'BUBBLE','A',0,NULL,NULL,'Not allowed in concealed edge area' UNION ALL
    SELECT 'BUBBLE','B',1,2.400,'mm','Max 2.4 mm (0.093 in) in Area B' UNION ALL
    SELECT 'BUBBLE','C',1,1.600,'mm','Max 1.6 mm (0.062 in) in central viewing Area C' UNION ALL

    -- OPEN BUBBLE — strike face only
    SELECT 'OPEN_BUBBLE','A',0,NULL,NULL,'Not allowed in concealed edge area' UNION ALL
    SELECT 'OPEN_BUBBLE','B',1,1.600,'mm','Max 1.6 mm (0.062 in) in Area B (strike face only)' UNION ALL
    SELECT 'OPEN_BUBBLE','C',1,1.200,'mm','Max 1.2 mm (0.045 in) in Area C (strike face only)' UNION ALL

    -- STONE_KNOT — tightest size tolerance
    SELECT 'STONE_KNOT','A',0,NULL,NULL,'Not allowed in concealed edge area' UNION ALL
    SELECT 'STONE_KNOT','B',1,1.200,'mm','Max 1.2 mm (0.045 in) in Area B' UNION ALL
    SELECT 'STONE_KNOT','C',1,0.800,'mm','Max 0.8 mm (0.031 in) in Area C' UNION ALL

    -- DIG — size limit
    SELECT 'DIG','A',0,NULL,NULL,'Not allowed in concealed edge area' UNION ALL
    SELECT 'DIG','B',1,2.400,'mm','Max 2.4 mm (0.093 in) in Area B' UNION ALL
    SELECT 'DIG','C',1,1.600,'mm','Max 1.6 mm (0.062 in) in Area C' UNION ALL

    -- INSIDE_DIRT — same limit both areas
    SELECT 'INSIDE_DIRT','A',0,NULL,NULL,'Not allowed in concealed edge area' UNION ALL
    SELECT 'INSIDE_DIRT','B',1,2.400,'mm','Max 2.4 mm (0.093 in)' UNION ALL
    SELECT 'INSIDE_DIRT','C',1,2.400,'mm','Max 2.4 mm (0.093 in)' UNION ALL

    -- REAM_STRING — angle-based
    SELECT 'REAM_STRING','A',0,NULL,NULL,'Not allowed in concealed edge area' UNION ALL
    SELECT 'REAM_STRING','B',1,30.000,'deg','Not evident at viewing angle > 30 degrees to glass surface, indirect daylight' UNION ALL
    SELECT 'REAM_STRING','C',1,30.000,'deg','Not evident at viewing angle > 30 degrees to glass surface, indirect daylight' UNION ALL

    -- SCRATCH — length limit (ASTM F428 grade 6 acceptable, 7 unacceptable)
    SELECT 'SCRATCH','A',0,NULL,NULL,'Scratches not allowed in concealed edge area; ASTM F428 grade > 6 unacceptable' UNION ALL
    SELECT 'SCRATCH','B',1,50.800,'mm','Max 2 inches (50.8 mm) in Area B; ASTM F428 grade 6 acceptable' UNION ALL
    SELECT 'SCRATCH','C',1,25.400,'mm','Max 1 inch (25.4 mm) in Area C; inside-surface scratches NOT permitted; ASTM F428 grade 6 acceptable' UNION ALL

    -- CRUSH — visibility distance
    SELECT 'CRUSH','A',0,NULL,NULL,'Not allowed in concealed edge area' UNION ALL
    SELECT 'CRUSH','B',1,3.000,'m','Shall not be detectable at distances > 3 m (10 ft) with indirect daylight' UNION ALL
    SELECT 'CRUSH','C',1,3.000,'m','Shall not be detectable at distances > 3 m (10 ft) with indirect daylight' UNION ALL

    -- LINT_HAIR — visibility distance (different per area)
    SELECT 'LINT_HAIR','A',0,NULL,NULL,'Not allowed in concealed edge area' UNION ALL
    SELECT 'LINT_HAIR','B',1,0.900,'m','May be noticeable at 0.9 m (3 ft) through patterned background — borderline acceptable' UNION ALL
    SELECT 'LINT_HAIR','C',1,0.900,'m','Barely noticeable at 0.9 m (3 ft) looking through patterned background, indirect daylight' UNION ALL

    -- ORGANIC — visibility distance
    SELECT 'ORGANIC','A',0,NULL,NULL,'Not allowed in concealed edge area' UNION ALL
    SELECT 'ORGANIC','B',1,0.900,'m','Shall not be detectable at distances > 0.9 m (3 ft) looking through perpendicular, indirect daylight' UNION ALL
    SELECT 'ORGANIC','C',1,0.900,'m','Shall not be detectable at distances > 0.9 m (3 ft) looking through perpendicular, indirect daylight' UNION ALL

    -- SURFACE (catch-all) — visibility distance
    SELECT 'SURFACE','A',0,NULL,NULL,'Not allowed in concealed edge area' UNION ALL
    SELECT 'SURFACE','B',1,3.000,'m','Shall not be detectable at distances > 3 m (10 ft)' UNION ALL
    SELECT 'SURFACE','C',1,3.000,'m','Shall not be detectable at distances > 3 m (10 ft)' UNION ALL

    -- STRIAE — visibility distance
    SELECT 'STRIAE','A',0,NULL,NULL,'Not allowed in concealed edge area' UNION ALL
    SELECT 'STRIAE','B',1,3.000,'m','Shall not be detectable at distances > 3 m (10 ft) looking perpendicular, indirect daylight' UNION ALL
    SELECT 'STRIAE','C',1,3.000,'m','Shall not be detectable at distances > 3 m (10 ft) looking perpendicular, indirect daylight'
) 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, t.name, t.assessment_type, t.requires_size_input,
--          l.area_code, l.is_allowed, l.limit_value, l.limit_unit
--     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. Every defect has
--   `is_allowed=0` for Area A. Sized defects (BUBBLE, OPEN_BUBBLE,
--   STONE_KNOT, DIG, INSIDE_DIRT, SCRATCH) carry numeric limit_value;
--   visibility / angle / not_allowed types carry NULL or contextual
--   limit_value (the picker UI consumes assessment_type to decide
--   whether to render the size input + tolerance hint).
--
-- ============================================================================
