-- AGG-UAE — Phase M7-p6a-h2-p13 — STAGE_TRANSITION_LEAD_TIME bended-filter
--
-- Adds a fourth identity column to STAGE_TRANSITION_LEAD_TIME so the GM
-- can configure DIFFERENT lead times for flat vs bended glasses on the
-- same (from_stage, to_stage, size_class) triple.
--
-- Motivation (user report 2026-05-22 after h2-p12 deploy):
--   "Lamination need to wait for the complete glass units to get to them
--   before they Print. Just like how Glass bending waited for a day to
--   get glasses so from next day onwards Glass Cutting is always ahead
--   of what bending needs. Similarly Primer Printing (especially for
--   bended) can be planned that way."
--
-- The fix: split Primer Printing → Lamination into TWO rows — flat keeps
-- same-day flow (0h lead), bended gets a 24h lead so Primer stays a day
-- ahead of Lam.
--
-- Schema change
-- -------------
-- 1. ADD COLUMN is_bended_filter ENUM('any','flat','bended') NOT NULL DEFAULT 'any'
--    AFTER size_class. Pre-h2-p13 rows default to 'any' (= no
--    differentiation), preserving backward compatibility.
-- 2. DROP existing UNIQUE uq_edge (from_stage, to_stage, size_class).
-- 3. ADD UNIQUE uq_edge (from_stage, to_stage, size_class, is_bended_filter).
--
-- Lookup strategy (scheduler.php scheduler_lead_time):
--   1st try: (from, to, size_class, is_bended-matching-this-glass)
--   2nd try: (from, to, size_class, 'any')
--   3rd try: defaults to 0h
--
-- So existing rows with is_bended_filter='any' keep working unchanged.
-- New differentiated rows take precedence when the glass's is_bended
-- flag matches the row's filter.
--
-- Re-seed
-- -------
-- DELETE existing Primer Printing → Lamination rows.
-- INSERT bended-aware split:
--   normal flat     → 0h  (same-day, current behavior)
--   normal bended   → 24h (next-day buffer)
--   big flat        → 0h
--   big bended      → 24h
--   windshield bended → 24h  (WS is always bended)
--
-- Idempotent via information_schema probes + UNIQUE-aware UPSERT.
-- Pre-existing non-Primer-to-Lam rows (Ceramic→Bending 6h, Bending→Primer 1h,
-- etc.) are untouched.

-- ---------------------------------------------------------------
-- 1. Add is_bended_filter column
-- ---------------------------------------------------------------

SET @col_exists := (
    SELECT COUNT(*)
      FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'STAGE_TRANSITION_LEAD_TIME'
       AND COLUMN_NAME  = 'is_bended_filter'
);

SET @sql := IF(
    @col_exists = 0,
    "ALTER TABLE STAGE_TRANSITION_LEAD_TIME
       ADD COLUMN is_bended_filter ENUM('any','flat','bended') NOT NULL DEFAULT 'any'
           AFTER size_class",
    "SELECT 'STAGE_TRANSITION_LEAD_TIME.is_bended_filter already present — skipping ALTER' AS note"
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ---------------------------------------------------------------
-- 2. Drop + re-create UNIQUE with is_bended_filter included
-- ---------------------------------------------------------------

SET @idx_exists := (
    SELECT COUNT(*)
      FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'STAGE_TRANSITION_LEAD_TIME'
       AND INDEX_NAME   = 'uq_edge'
);

SET @sql := IF(
    @idx_exists > 0,
    "ALTER TABLE STAGE_TRANSITION_LEAD_TIME DROP INDEX uq_edge",
    "SELECT 'uq_edge index not present — skipping DROP' AS note"
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Verify it's gone (or never was) before adding the new one
SET @idx_exists := (
    SELECT COUNT(*)
      FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'STAGE_TRANSITION_LEAD_TIME'
       AND INDEX_NAME   = 'uq_edge'
);

SET @sql := IF(
    @idx_exists = 0,
    "ALTER TABLE STAGE_TRANSITION_LEAD_TIME
       ADD UNIQUE KEY uq_edge (from_stage, to_stage, size_class, is_bended_filter)",
    "SELECT 'uq_edge already present after re-create — skipping' AS note"
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ---------------------------------------------------------------
-- 3. Re-seed Primer Printing → Lamination rows with bended split
-- ---------------------------------------------------------------

DELETE FROM STAGE_TRANSITION_LEAD_TIME
 WHERE from_stage = 'Primer Printing'
   AND to_stage   = 'Lamination';

INSERT INTO STAGE_TRANSITION_LEAD_TIME
    (from_stage, to_stage, size_class, is_bended_filter, lead_mode, lead_hours, notes, updated_by)
VALUES
    ('Primer Printing', 'Lamination', 'normal',     'flat',   'fixed_hours',  0,
     'Flat normal: same-day Primer → Lam (no buffer needed).', NULL),
    ('Primer Printing', 'Lamination', 'normal',     'bended', 'fixed_hours', 24,
     'Bended normal: 24h buffer so Primer stays a day ahead of Lam.', NULL),
    ('Primer Printing', 'Lamination', 'big',        'flat',   'fixed_hours',  0,
     'Flat big: same-day Primer → Lam.', NULL),
    ('Primer Printing', 'Lamination', 'big',        'bended', 'fixed_hours', 24,
     'Bended big: 24h buffer.', NULL),
    ('Primer Printing', 'Lamination', 'windshield', 'bended', 'fixed_hours', 24,
     'Bended WS: 24h buffer (WS is always bended).', NULL);

-- ---------------------------------------------------------------
-- Verify
-- ---------------------------------------------------------------

SELECT 'STAGE_TRANSITION_LEAD_TIME' AS tbl,
       COUNT(*) AS total_rows,
       SUM(is_bended_filter = 'flat')   AS flat_rows,
       SUM(is_bended_filter = 'bended') AS bended_rows,
       SUM(is_bended_filter = 'any')    AS any_rows
  FROM STAGE_TRANSITION_LEAD_TIME;

SELECT from_stage, to_stage, size_class, is_bended_filter, lead_mode, lead_hours, notes
  FROM STAGE_TRANSITION_LEAD_TIME
 WHERE from_stage = 'Primer Printing' AND to_stage = 'Lamination'
 ORDER BY size_class, is_bended_filter;