-- ============================================================
-- AGG-UAE ERP — Migration 57 — Phase M7-p4-h20-rev2
--   Per-part size_class + bended cap rename to size semantics
--   + universal only/mix mode (drops Lam-only restriction)
-- ============================================================
--
-- User refinement (2026-05-27): the previous h20 model conflated
-- glass KIND (doors vs WS via SubItem name) with SIZE (small vs
-- large physical glass). They're orthogonal:
--
--   "Mostly doors are classed as Small size glass (Bended doors) and
--    items with Windshield item names in them are mostly large Sized
--    glasses and for flat glasses it depends on the size of the Flat
--    glass... add a radio button option of Large/Small in part create/
--    part edit module on erp."
--
-- So:
--   - SIZE (small / large) is a per-PART attribute on CRTPARTS, set
--     manually by the GM on the Parts form. Doors usually = small;
--     WS usually = large; flat picks per individual part.
--   - The 4 scheduler capacity KINDS become:
--       bended + small  → cap_bended_small_*
--       bended + large  → cap_bended_large_*
--       flat   + small  → cap_flat_small_*
--       flat   + large  → cap_flat_large_*
--
-- This migration:
--   (1) Adds CRTPARTS.CRTSITEM_SIZE_CLASS ENUM('small','large') NULL
--       DEFAULT 'small' for the per-part flag
--   (2) Renames the bended caps semantically by ADDING new columns
--       cap_bended_small_* and cap_bended_large_* alongside the old
--       cap_bended_doors_* and cap_bended_ws_*; legacy columns stay
--       per CLAUDE.md never-drop rule
--   (3) Backfills new caps from the old caps (one-time copy)
--
-- Per the same refinement, GLASS_THICKNESS_LEVEL.flat_category is NO
-- LONGER used by the scheduler — thickness and size are independent
-- dimensions. The column stays in schema (never-drop) but the UI
-- hides it and the scheduler stops reading it.
--
-- Idempotent: ADD COLUMN IF NOT EXISTS (MariaDB 10.6+); backfill
-- UPDATEs gated on "new column is 0/NULL" so re-runs find nothing.

-- ---- CRTPARTS — per-part size_class --------------------------------
-- CRTPARTS is a wide legacy table; needs innodb_strict_mode=0 dance
-- per CLAUDE.md invariant 15 + migration-15/21 precedent.
SET @innodb_strict_orig := @@SESSION.innodb_strict_mode;
SET SESSION innodb_strict_mode = 0;

ALTER TABLE CRTPARTS
  ADD COLUMN IF NOT EXISTS CRTSITEM_SIZE_CLASS ENUM('small', 'large')
    NULL DEFAULT 'small'
    COMMENT 'h20-rev2 size discriminator for ballistic glass parts; drives the small/large capacity bucket. Default small.';

SET SESSION innodb_strict_mode = @innodb_strict_orig;

-- ---- PRODUCTION_SECTION_CAPACITY — semantic rename -----------------
ALTER TABLE PRODUCTION_SECTION_CAPACITY
  ADD COLUMN IF NOT EXISTS cap_bended_small_mix  INT NOT NULL DEFAULT 0
    COMMENT 'Bended small-size cap (formerly cap_bended_doors_mix; semantic rename per h20-rev2)',
  ADD COLUMN IF NOT EXISTS cap_bended_large_mix  INT NOT NULL DEFAULT 0
    COMMENT 'Bended large-size cap (formerly cap_bended_ws_mix; semantic rename per h20-rev2)',
  ADD COLUMN IF NOT EXISTS cap_bended_small_only INT NULL
    COMMENT 'Bended-small-only-day cap (h20-rev2 semantic rename; now applies to all sections, not just Lam)',
  ADD COLUMN IF NOT EXISTS cap_bended_large_only INT NULL
    COMMENT 'Bended-large-only-day cap (h20-rev2; universal)';

-- One-shot backfill: copy values from the legacy "doors/ws" columns.
-- Idempotent — re-runs find nothing to copy (already non-zero / non-null).
UPDATE PRODUCTION_SECTION_CAPACITY
   SET cap_bended_small_mix = cap_bended_doors_mix
 WHERE cap_bended_small_mix = 0 AND cap_bended_doors_mix > 0;

UPDATE PRODUCTION_SECTION_CAPACITY
   SET cap_bended_large_mix = cap_bended_ws_mix
 WHERE cap_bended_large_mix = 0 AND cap_bended_ws_mix > 0;

UPDATE PRODUCTION_SECTION_CAPACITY
   SET cap_bended_small_only = cap_bended_doors_only
 WHERE cap_bended_small_only IS NULL AND cap_bended_doors_only IS NOT NULL;

UPDATE PRODUCTION_SECTION_CAPACITY
   SET cap_bended_large_only = cap_bended_ws_only
 WHERE cap_bended_large_only IS NULL AND cap_bended_ws_only IS NOT NULL;

-- ---- Audit ---------------------------------------------------------
SELECT section,
       cap_bended_small_mix,   cap_bended_large_mix,
       cap_flat_small_mix,     cap_flat_large_mix,
       cap_bended_small_only,  cap_bended_large_only,
       cap_flat_small_only,    cap_flat_large_only
  FROM PRODUCTION_SECTION_CAPACITY
 ORDER BY section;

SELECT COUNT(*) AS parts_with_size_class
  FROM CRTPARTS
 WHERE CRTSITEM_SIZE_CLASS IS NOT NULL;