-- ============================================================
-- AGG-UAE ERP — Migration 56 — Phase M7-p4-h20
--   Flat/bended capacity split + 5-mode Lamination capacity model
-- ============================================================
--
-- Splits PRODUCTION_SECTION_CAPACITY's single-pair Doors/WS cap into
-- a 4-kind × 2-mode = 8-cap matrix per the user's locked spec:
--
--   Kinds:       bended_doors / bended_ws / flat_small / flat_large
--   Modes:       'only' (single-kind day) + 'mix' (multi-kind day)
--
-- Lamination uses both modes — its setup overhead per kind switch means
-- single-kind days run faster (use *_only caps); multi-kind days lose
-- that focus and use *_mix caps for each kind in the pool.
--
-- Other sections (Glass Cutting / FC / PC / CP / PP / Glass Bending /
-- Autoclave / FI) use only the 4 *_mix caps; the *_only caps stay NULL
-- on those rows (scheduler falls back to mix).
--
-- Per CLAUDE.md never-drop rule, the legacy base_capacity_doors +
-- base_capacity_ws columns stay in the schema as fallback. Scheduler
-- prefers the new columns when they're > 0; falls back to legacy when
-- new columns are 0 (preserves behaviour on databases that haven't run
-- the backfill yet).
--
-- Also extends GLASS_THICKNESS_LEVEL (h19) with `flat_category` so the
-- scheduler can classify a flat glass as small or large via its level.
-- Default seed: levels 1+2 → small; levels 3+4 → large.
--
-- Idempotent: ADD COLUMN IF NOT EXISTS (MariaDB 10.6+) on every ALTER;
-- backfill UPDATE guarded by `WHERE new_column = 0` so a re-run finds
-- nothing to update.

-- ---- PRODUCTION_SECTION_CAPACITY — 8 new caps ----------------------
ALTER TABLE PRODUCTION_SECTION_CAPACITY
  ADD COLUMN IF NOT EXISTS cap_bended_doors_mix  INT NOT NULL DEFAULT 0
    COMMENT 'Bended doors cap when 2+ kinds share the section day',
  ADD COLUMN IF NOT EXISTS cap_bended_ws_mix     INT NOT NULL DEFAULT 0
    COMMENT 'Bended windshields cap when 2+ kinds share the day',
  ADD COLUMN IF NOT EXISTS cap_flat_small_mix    INT NOT NULL DEFAULT 0
    COMMENT 'Flat small (Levels 1+2) cap when 2+ kinds share the day',
  ADD COLUMN IF NOT EXISTS cap_flat_large_mix    INT NOT NULL DEFAULT 0
    COMMENT 'Flat large (Levels 3+4) cap when 2+ kinds share the day',
  ADD COLUMN IF NOT EXISTS cap_bended_doors_only INT NULL
    COMMENT 'Bended-doors-only-day cap (Lam-only; NULL on other sections)',
  ADD COLUMN IF NOT EXISTS cap_bended_ws_only    INT NULL
    COMMENT 'Bended-WS-only-day cap (Lam-only)',
  ADD COLUMN IF NOT EXISTS cap_flat_small_only   INT NULL
    COMMENT 'Flat-small-only-day cap (Lam-only)',
  ADD COLUMN IF NOT EXISTS cap_flat_large_only   INT NULL
    COMMENT 'Flat-large-only-day cap (Lam-only)';

-- Backfill: existing base_capacity_doors → cap_bended_doors_mix.
-- Pre-h20 the seed mixed flat + bended into the doors cap; floor team
-- can refine per-kind from the Settings page once h20 is live.
UPDATE PRODUCTION_SECTION_CAPACITY
   SET cap_bended_doors_mix = COALESCE(base_capacity_doors, 0)
 WHERE cap_bended_doors_mix = 0;

UPDATE PRODUCTION_SECTION_CAPACITY
   SET cap_bended_ws_mix    = COALESCE(base_capacity_ws, 0)
 WHERE cap_bended_ws_mix    = 0;

-- Flat caps default 0 — GM populates from Settings page based on actual
-- floor throughput data. Pre-population would make assumptions about
-- the existing capacity split that the user prefers to set deliberately.

-- ---- GLASS_THICKNESS_LEVEL — flat_category column ------------------
ALTER TABLE GLASS_THICKNESS_LEVEL
  ADD COLUMN IF NOT EXISTS flat_category ENUM('small', 'large')
    NOT NULL DEFAULT 'small'
    COMMENT 'Lam capacity bucket: small = flat_small_*, large = flat_large_* cap columns';

-- Default seed: thicker levels are LARGE flats. Levels 1+2 (≤65mm)
-- map to small_flat caps; levels 3+4 (>65mm) map to large_flat caps.
-- Idempotent via the WHERE clause — re-runs find nothing to update.
UPDATE GLASS_THICKNESS_LEVEL
   SET flat_category = 'large'
 WHERE level_no >= 3
   AND flat_category = 'small';

-- ---- Audit ---------------------------------------------------------
SELECT section,
       cap_bended_doors_mix, cap_bended_ws_mix,
       cap_flat_small_mix,   cap_flat_large_mix,
       cap_bended_doors_only, cap_bended_ws_only,
       cap_flat_small_only,   cap_flat_large_only
  FROM PRODUCTION_SECTION_CAPACITY
 ORDER BY section;

SELECT level_no, level_name, thickness_max_mm, capacity_ratio, flat_category
  FROM GLASS_THICKNESS_LEVEL
 ORDER BY level_no;