-- AGG-UAE — Phase M7-p6a-h2-p9 — unit_seqs + vehicle short name
--
-- Two columns added to support h2-p9's grid cell rendering rewrite per
-- CLAUDE.md §3 invariants 64-66:
--
--   1. JOBFORM_SCHEDULE_DAY.unit_seqs VARCHAR(100) NULL
--      Comma-separated list of qty_required unit indices scheduled in
--      this row (e.g. "1,2" means units 1 + 2 of qty_required=10).
--      Scheduler tracks running unit_seq per (jobform_glass_id) during
--      the day-by-day simulation; writes the per-row slice here so the
--      mobile cell can render `FDL 1&2(10)` instead of `FDL×2`.
--      Pre-h2-p9 rows stay NULL — the mobile falls back to the legacy
--      `Wx2` rendering shape when the field is absent, so deploying the
--      column alone (without a recompute) is non-breaking.
--
--   2. CARMODEL.CARMODEL_SHORTNAME VARCHAR(20) NULL
--      Optional manual override for the vehicle short name pattern. The
--      h2-p9 API derives the short name from CARMODEL_NAME by pattern:
--        "Land Cruiser 300"          → "LC300"
--        "Land Cruiser 78 Series"    → "L78"
--        "Hilux"                     → "Hilux" (passthrough)
--      When the pattern doesn't fit (custom APC vehicle names, OEM
--      variants with unique abbreviations the floor uses), the GM can
--      set CARMODEL_SHORTNAME explicitly and the API surfaces that
--      verbatim. NULL means "use the pattern".
--
-- Both columns added via information_schema column probes (idempotent
-- re-runs find the column already present and no-op). Both tables are
-- slim — no innodb_strict_mode dance required for either ALTER (CARMODEL
-- is a metadata table; JOBFORM_SCHEDULE_DAY was created fresh in
-- migration 44 with no row-size pressure).

-- ---------------------------------------------------------------
-- 1. JOBFORM_SCHEDULE_DAY.unit_seqs
-- ---------------------------------------------------------------

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

SET @sql := IF(
    @col_exists = 0,
    "ALTER TABLE JOBFORM_SCHEDULE_DAY
       ADD COLUMN unit_seqs VARCHAR(100) NULL
           AFTER qty_allocated",
    "SELECT 'JOBFORM_SCHEDULE_DAY.unit_seqs column already present — skipping ALTER' AS note"
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ---------------------------------------------------------------
-- 2. CARMODEL.CARMODEL_SHORTNAME
-- ---------------------------------------------------------------

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

SET @sql := IF(
    @col_exists = 0,
    "ALTER TABLE CARMODEL
       ADD COLUMN CARMODEL_SHORTNAME VARCHAR(20) NULL",
    "SELECT 'CARMODEL.CARMODEL_SHORTNAME column already present — skipping ALTER' AS note"
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Verify
SELECT 'JOBFORM_SCHEDULE_DAY' AS tbl,
       SUM(unit_seqs IS NOT NULL) AS rows_with_unit_seqs,
       COUNT(*) AS total_rows
  FROM JOBFORM_SCHEDULE_DAY;

SELECT 'CARMODEL' AS tbl,
       SUM(CARMODEL_SHORTNAME IS NOT NULL) AS rows_with_shortname,
       COUNT(*) AS total_rows
  FROM CARMODEL;