-- AGG-UAE — Phase M7-p6a — JOBFORM.priority_bracket
--
-- Adds a 5-value bracket column alongside the existing numeric
-- priority_order. Brackets drive the PRIMARY scheduler sort:
--   URGENT > HIGH > MEDIUM > LOW > BACKLOG
-- priority_order is the tiebreaker WITHIN a bracket — drag-reorder
-- on the mobile priority list still bumps priority_order, but the
-- bracket selection acts as the "this is urgent" gross signal that
-- doesn't require manually moving the JF past every same-bracket
-- sibling.
--
-- Default MEDIUM matches the implicit pre-p6a behaviour (every JF
-- treated equally; only target_date + priority_order influenced the
-- sort). Existing JFs inherit MEDIUM automatically via the column
-- default; no explicit backfill needed.
--
-- Idempotent — re-runs find the column already present and no-op.
-- JOBFORM is a slim table (no MEDIUMTEXT row-size pressure), no
-- innodb_strict_mode dance required.

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

SET @sql := IF(
    @col_exists = 0,
    "ALTER TABLE JOBFORM
       ADD COLUMN priority_bracket
           ENUM('URGENT','HIGH','MEDIUM','LOW','BACKLOG')
           NOT NULL DEFAULT 'MEDIUM'
           AFTER priority_order",
    "SELECT 'priority_bracket column already present — skipping ALTER' AS note"
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Index for the scheduler's sort. Composite covers the canonical
-- order: bracket first, then date, then numeric tiebreaker.
SET @idx_exists := (
    SELECT COUNT(*)
      FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'JOBFORM'
       AND INDEX_NAME   = 'idx_jobform_priority_sort'
);

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

-- Verify
SELECT priority_bracket, COUNT(*) AS jf_count
  FROM JOBFORM
 GROUP BY priority_bracket
 ORDER BY FIELD(priority_bracket, 'URGENT','HIGH','MEDIUM','LOW','BACKLOG');