-- ============================================================
-- AGG-UAE ERP — Migration 55 — Phase M7-p4-h19
--   GLASS_THICKNESS_LEVEL band table (replaces hardcoded
--   scheduler_thickness_factor() linear interpolation)
-- ============================================================
--
-- Pre-h19 the scheduler computed per-unit "capacity cost" by linear
-- interpolation between four hardcoded reference thicknesses
-- (43→1.00, 63→1.20, 80→1.75, 100→2.00). User feedback (2026-05-26):
--
--   "the caviat is sometimes there are glasses with 45mm 42mm 50mm
--    72mm which are in between of those categorized hardcoded ones.
--    What we need is level of glasses so anything up to 45mm comes
--    in level 1 and 45-65mm comes in level 2 and 65-90mm comes in
--    level 3 and 90+ comes in level 4 and we need to pre-set the
--    capacity ratio for each."
--
-- The level model is a STEP function: every glass falls into exactly
-- one band by its thickness_mm, and the band's capacity_ratio
-- multiplies its per-unit cost. Bands are sorted by level_no;
-- thickness_max_mm is the inclusive UPPER bound for that band
-- (NULL = unbounded top band).
--
-- Settings architecture per CLAUDE.md §15 + h17 pattern: each row is
-- a (selector, behavior) tuple. Selector = thickness_max_mm range;
-- behavior = capacity_ratio. Adding a 5th level is one INSERT;
-- tuning a ratio is one UPDATE; no scheduler code change required.
--
-- Idempotent: gated on CREATE TABLE IF NOT EXISTS + INSERT ON
-- DUPLICATE KEY UPDATE so re-runs refresh the seed values without
-- duplicating rows.

CREATE TABLE IF NOT EXISTS GLASS_THICKNESS_LEVEL (
    id                INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
    level_no          INT          NOT NULL,
    level_name        VARCHAR(60)  NOT NULL,
    thickness_max_mm  DECIMAL(5,2) NULL
        COMMENT 'Inclusive upper bound for this band; NULL = unbounded (top level)',
    capacity_ratio    DECIMAL(5,3) NOT NULL DEFAULT 1.000
        COMMENT 'Multiplier on per-unit capacity cost (1.0 = Level 1 = baseline)',
    notes             VARCHAR(500) NULL,
    updated_at        DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updated_by        VARCHAR(50)  NULL,
    UNIQUE KEY uq_level_no (level_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed the 4 default levels per user spec. capacity_ratio values match
-- the pre-h19 hardcoded reference points (43mm=1.00, 63mm=1.20,
-- 80mm=1.75, 100mm=2.00) snapped to the level upper bounds.
INSERT INTO GLASS_THICKNESS_LEVEL
    (level_no, level_name, thickness_max_mm, capacity_ratio, notes)
VALUES
    (1, 'Level 1', 45.00, 1.000, 'Thin glasses up to 45 mm (43mm windshields, 42mm doors, etc.)'),
    (2, 'Level 2', 65.00, 1.200, 'Medium 45-65 mm (50mm, 63mm common builds)'),
    (3, 'Level 3', 90.00, 1.750, 'Heavy 65-90 mm (72mm, 80mm common builds)'),
    (4, 'Level 4', NULL,  2.000, '90+ mm heaviest tier (100mm+ commonly armored)')
ON DUPLICATE KEY UPDATE
    level_name       = VALUES(level_name),
    thickness_max_mm = VALUES(thickness_max_mm),
    capacity_ratio   = VALUES(capacity_ratio),
    notes            = VALUES(notes);

-- Audit query post-migration.
SELECT level_no, level_name, thickness_max_mm, capacity_ratio, notes
  FROM GLASS_THICKNESS_LEVEL
 ORDER BY level_no;