-- ───────────────────────────────────────────────────────────────────────
-- Phase M4 — Per-qty + per-unit worker assignment + temp-serial uniqueness.
--
-- M4 folds in the deferred per-qty/per-unit work flagged in M3.5e-p4.
-- Two columns added to JOBFORM_TASK_WORKER, one unique index added to
-- JOBFORM_SERIAL.
--
-- 1. JOBFORM_TASK_WORKER.qty (INT NOT NULL DEFAULT 1)
--    For PRE-LAMINATION tasks (Glass Cutting / Film Cutting / PC Cutting /
--    Glass Bending / Printing), `qty` is the number of units this worker
--    is responsible for. The sum of `qty` across worker rows for a given
--    task MUST equal task.qty_required (server-side validated by
--    task_assign.php). Default 1 keeps existing M3 rows correct: those
--    were created against single-unit tasks where "1 worker on the whole
--    task" means qty=1.
--
-- 2. JOBFORM_TASK_WORKER.serial_id (INT UNSIGNED NOT NULL DEFAULT 0)
--    For LAMINATION+ tasks (Lamination / Autoclave / Final Inspection),
--    `serial_id` references the specific JOBFORM_SERIAL row (= the
--    individual glass unit) this worker is assigned to. For pre-lamination
--    tasks, `serial_id = 0` (the sentinel "no specific unit" — the
--    assignment applies to the whole task qty).
--
--    Sentinel value (NOT NULL DEFAULT 0) instead of NULL because the PK
--    needs to include this column and MariaDB doesn't allow NULL in PK.
--    A FOREIGN KEY constraint pointing at JOBFORM_SERIAL.id cannot use
--    sentinel 0; we keep the column schema-only and rely on application
--    code to validate the lookup. (Adding a sentinel row to JOBFORM_SERIAL
--    would muddy reads + reports — the trade-off favours app-level FK
--    validation here, same posture as JOBFORM.priority_order = 0.)
--
-- 3. PK rebuilt as (task_id, emp_no, serial_id) so the same worker can
--    be assigned to multiple units of the same task (Lamination scenario).
--
-- 4. JOBFORM_SERIAL.temp_serial gets a UNIQUE index. The convert handler
--    pre-allocates these as `{NNNN}-{YY} {Initials}{seq}` per CLAUDE.md
--    §15 + invariant 40 (e.g. "1001-26 W1", "1001-26 FDL2"). The unique
--    constraint catches any allocation race or developer typo before it
--    lands on a sticker on the floor.
--
-- BACKFILL POLICY: skipped. Existing JOBFORM_TASK_WORKER rows get
-- qty=1 + serial_id=0 by column default — that's correct for M3-era
-- pre-lamination assignments. JOBFORM_SERIAL rows for existing
-- JOBFORM_GLASS rows are NOT backfilled by this migration; the M4
-- design pre-allocates serials at SO→JF convert time, so existing
-- JFs (test data) will not have serials. Manual backfill via a
-- one-shot PHP script is the recommended fix for any pre-M4 JF that
-- needs to enter Lamination after the deploy.
-- ───────────────────────────────────────────────────────────────────────

-- ── 1. JOBFORM_TASK_WORKER ──────────────────────────────────────────

-- Idempotent: skip if columns already exist.
SET @col_qty := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'JOBFORM_TASK_WORKER'
       AND COLUMN_NAME  = 'qty'
);
SET @col_serial := (
    SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'JOBFORM_TASK_WORKER'
       AND COLUMN_NAME  = 'serial_id'
);

SET @sql := IF(@col_qty = 0,
    'ALTER TABLE JOBFORM_TASK_WORKER ADD COLUMN qty INT NOT NULL DEFAULT 1 AFTER emp_no',
    'SELECT ''column qty already present — skipping add''');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := IF(@col_serial = 0,
    'ALTER TABLE JOBFORM_TASK_WORKER ADD COLUMN serial_id INT UNSIGNED NOT NULL DEFAULT 0 AFTER qty',
    'SELECT ''column serial_id already present — skipping add''');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Rebuild PK to include serial_id. Idempotent: only if current PK is the
-- 2-column M3 shape (task_id, emp_no).
SET @pk_cols := (
    SELECT GROUP_CONCAT(COLUMN_NAME ORDER BY ORDINAL_POSITION)
      FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'JOBFORM_TASK_WORKER'
       AND INDEX_NAME   = 'PRIMARY'
);
SET @sql := IF(@pk_cols = 'task_id,emp_no',
    'ALTER TABLE JOBFORM_TASK_WORKER DROP PRIMARY KEY, ADD PRIMARY KEY (task_id, emp_no, serial_id)',
    'SELECT ''PK already includes serial_id — skipping rebuild''');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Index on serial_id for the per-unit reverse-lookup (used by GET
-- /v1/jobforms/{id}/glasses/{glass_id}/serials when rendering the
-- per-unit assignment cards on Lamination/Autoclave/FI).
SET @idx_serial := (
    SELECT COUNT(*) FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'JOBFORM_TASK_WORKER'
       AND INDEX_NAME   = 'idx_jftw_serial'
);
SET @sql := IF(@idx_serial = 0,
    'ALTER TABLE JOBFORM_TASK_WORKER ADD KEY idx_jftw_serial (serial_id)',
    'SELECT ''idx_jftw_serial already present — skipping add''');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── 2. JOBFORM_SERIAL.temp_serial UNIQUE ────────────────────────────

SET @uq_temp := (
    SELECT COUNT(*) FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'JOBFORM_SERIAL'
       AND INDEX_NAME   = 'uq_temp_serial'
);
SET @sql := IF(@uq_temp = 0,
    'ALTER TABLE JOBFORM_SERIAL ADD UNIQUE KEY uq_temp_serial (temp_serial)',
    'SELECT ''uq_temp_serial already present — skipping add''');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;