-- ============================================================
-- AGG-UAE ERP — Migration 50 — Phase M7-p6b
--   JOBFORM_SCHEDULE_PIN + JOBFORM.is_held + JOBFORM.last_forecast_check_at
-- ============================================================
--
-- Two new mechanics + one bookkeeping column for the M7-p6b workflow
-- refresh:
--
-- 1. JOBFORM_SCHEDULE_PIN — hard pins of (JF, glass, stage, day, qty)
--    set by the supervisor's "Pull Forward → Allocate Today" action.
--    Each pin survives recomputes; the scheduler honours them FIRST
--    when laying out the day's allocations, then routes remaining
--    demand around the pinned slots. The UNIQUE constraint enforces
--    one pin per (JF, glass, stage, day) — re-pinning the same cell
--    updates the qty in-place via INSERT ... ON DUPLICATE KEY UPDATE.
--
-- 2. JOBFORM.is_held — set TRUE when the supervisor drags an
--    On-Process JF down the priority list and picks "Hold" on the
--    prompt. Per CLAUDE.md §15 (JOBFORM_SCHEDULE_PIN section), held
--    JFs keep their in-flight stages running but do NOT start new
--    stages. The scheduler reads this flag and skips held JFs' new-
--    stage allocations during the daily fill.
--
-- 3. JOBFORM.last_forecast_check_at — per-JF marker for the
--    forecast-changed snackbar. The mobile's "since last check"
--    pivot is stored client-side (DataStore lastSeenRunId) so this
--    column isn't strictly required for the snackbar flow, but it
--    surfaces "when did the supervisor last acknowledge this JF's
--    forecast" in the JF Detail header for audit + future use
--    (e.g. badging JFs whose forecast moved since last acknowledge).
--    Optional read; default NULL = never acknowledged.
--
-- Idempotent — re-runs find the column/table/index already present
-- and no-op. JOBFORM is slim (no row-size pressure) so the two
-- column adds don't need the innodb_strict_mode dance.
--
-- Depends on: 17-phase-m1-jobforms.sql (JOBFORM, JOBFORM_GLASS),
--             44-phase-m7-p2-schedule-day.sql (SCHEDULE_RUN).

-- ─────────────────────────────────────────────────────────────
-- (1) JOBFORM_SCHEDULE_PIN
-- ─────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS JOBFORM_SCHEDULE_PIN (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    jobform_id          INT NOT NULL,
    jobform_glass_id    INT NOT NULL,
    stage               ENUM(
                            'Glass Cutting','Film Cutting','PC Cutting',
                            'Glass Bending','Ceramic Printing','Primer Printing',
                            'Printing','Lamination','Autoclave','Final Inspection'
                        ) NOT NULL,
    section             VARCHAR(50) NULL,
    day_date            DATE NOT NULL,
    qty                 INT NOT NULL DEFAULT 1,
    pinned_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    pinned_by           VARCHAR(100) NULL,
    notes               VARCHAR(255) NULL,
    UNIQUE KEY uq_pin   (jobform_id, jobform_glass_id, stage, day_date),
    INDEX idx_pin_day   (day_date),
    INDEX idx_pin_jf    (jobform_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────
-- (2) JOBFORM.is_held
-- ─────────────────────────────────────────────────────────────

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

SET @sql := IF(
    @col_exists = 0,
    "ALTER TABLE JOBFORM
       ADD COLUMN is_held TINYINT(1) NOT NULL DEFAULT 0
           AFTER priority_bracket,
       ADD COLUMN held_at DATETIME NULL DEFAULT NULL
           AFTER is_held,
       ADD COLUMN held_by VARCHAR(100) NULL DEFAULT NULL
           AFTER held_at",
    "SELECT 'is_held column already present — skipping ALTER' AS note"
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Index for the scheduler's "WHERE is_held = 0" walk during the pool
-- build. Cheap on a slim table, but helps the query optimizer pick
-- the index when JF count grows past a few hundred.
SET @idx_exists := (
    SELECT COUNT(*)
      FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'JOBFORM'
       AND INDEX_NAME   = 'idx_jobform_is_held'
);

SET @sql := IF(
    @idx_exists = 0,
    "CREATE INDEX idx_jobform_is_held ON JOBFORM (is_held)",
    "SELECT 'idx_jobform_is_held already present — skipping CREATE' AS note"
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ─────────────────────────────────────────────────────────────
-- (3) JOBFORM.last_forecast_check_at
-- ─────────────────────────────────────────────────────────────

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

SET @sql := IF(
    @col_exists = 0,
    "ALTER TABLE JOBFORM
       ADD COLUMN last_forecast_check_at DATETIME NULL DEFAULT NULL
           AFTER held_by,
       ADD COLUMN last_forecast_check_by VARCHAR(100) NULL DEFAULT NULL
           AFTER last_forecast_check_at",
    "SELECT 'last_forecast_check_at column already present — skipping ALTER' AS note"
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ─────────────────────────────────────────────────────────────
-- Verification
-- ─────────────────────────────────────────────────────────────

SELECT 'JOBFORM_SCHEDULE_PIN' AS object,
       (SELECT COUNT(*) FROM JOBFORM_SCHEDULE_PIN) AS row_count;

SELECT is_held, COUNT(*) AS jf_count
  FROM JOBFORM
 GROUP BY is_held;

SELECT 'last_forecast_check_at' AS column_name,
       (SELECT COUNT(*) FROM JOBFORM WHERE last_forecast_check_at IS NOT NULL) AS acknowledged_jfs;