-- ============================================================
-- AGG-UAE ERP — Migration 54 — Phase M7-p4-h17
--   STAGE_TRANSITION_LEAD_TIME opportunistic-same-day flag
-- ============================================================
--
-- Adds `allow_same_day_when_downstream_idle TINYINT(1) NOT NULL
-- DEFAULT 0` to STAGE_TRANSITION_LEAD_TIME. When set to 1 on a row,
-- the scheduler treats the configured lead_hours as the DEFAULT
-- cure / cool-down but allows the unit to fill the downstream stage
-- on the SAME day when that stage would otherwise be under-utilised.
--
-- User feedback (2026-05-26): Day 3 Lamination consistently
-- under-utilises because bended glasses finish Primer Printing on
-- day N but the 8h cool-down (= next working day) pushes them to
-- Lam on day N+1, leaving day N's Lam slots empty. The 24h cure is
-- the SAFE default; when Lam would otherwise sit idle, the cure-
-- collapse is worth it for the productivity gain.
--
-- Settings architecture principle: each STAGE_TRANSITION_LEAD_TIME
-- row is a (selector, behavior) tuple. Selectors identify WHICH
-- transition the row covers (from_stage / to_stage / size_class /
-- is_bended_filter). Behaviors are the scheduler actions (lead_mode
-- / lead_hours / NEW allow_same_day_when_downstream_idle). Adding
-- new tuning knobs = adding columns; no new tables, no scheduler
-- architecture changes.
--
-- Idempotent: gated on information_schema column probe; safe to
-- re-run. Default 0 preserves the pre-h17 behaviour on every
-- existing row.

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

SET @sql := IF(@col_exists = 0,
    'ALTER TABLE STAGE_TRANSITION_LEAD_TIME
       ADD COLUMN allow_same_day_when_downstream_idle TINYINT(1)
         NOT NULL DEFAULT 0
         COMMENT ''When 1, allow downstream stage to absorb the unit on the cure-start day if the downstream cap would otherwise be under-utilised''',
    'SELECT ''column already exists, no-op''');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Audit query post-migration.
SELECT from_stage, to_stage, size_class, is_bended_filter,
       lead_mode, lead_hours, allow_same_day_when_downstream_idle
  FROM STAGE_TRANSITION_LEAD_TIME
 ORDER BY from_stage, to_stage, size_class, is_bended_filter;