-- ============================================================================
-- Phase M4-p7b-r3 Round 3B — JOBFORM_TASK_WORKER autoclave cycle metadata
-- ============================================================================
--
-- The Autoclave supervisor's Assign sheet captures two pieces of metadata
-- about the physical autoclave cycle each batch went into:
--
--   - cycle_no   VARCHAR(50) — alphanumeric supervisor-entered tag
--                              (e.g. 'AC-145', '2026-051-PVB', etc.).
--                              Free-text, max 50 chars. Required when
--                              the worker row is assigned to an
--                              Autoclave-stage task; NULL otherwise.
--   - cycle_type ENUM('PVB','PU') — the autoclave recipe family.
--                              Required when the worker row is assigned
--                              to an Autoclave-stage task; NULL otherwise.
--
-- Per-worker-row (not per-task) keying — under the M4-p6-h1 additive
-- assign semantic, a single Autoclave task can have multiple worker
-- rows assigned across DIFFERENT physical cycles (re-runs, partial
-- batches, supervisor splitting a glass across two cycles for some
-- production reason). Storing the cycle per worker row preserves that
-- audit detail; aggregating to "all cycles this glass passed through"
-- on the future Cycle Chart viewer is a one-line GROUP BY.
--
-- The PHP file `agg-api/v1/jobforms/task_assign.php` is updated in the
-- same M4-p7b-r2 ship to validate `cycle_no` (non-empty, ≤50 chars) +
-- `cycle_type` ('PVB' | 'PU') when `stage == 'Autoclave'`, and to
-- silently drop them to NULL on every other stage. The mobile Assign
-- sheet (`TaskAssignmentSheet`) renders the two new inputs only when
-- the stage arg is 'Autoclave'.
--
-- Both columns nullable — keeps the migration safe under the
-- pre-deploy window when r2 servers may receive r1-mobile Autoclave
-- assigns (which won't include cycle metadata; the server will 422
-- those instead of dropping them, but defensive nullability is cheap).
-- Existing worker rows (most of them on non-Autoclave tasks) stay NULL
-- forever which is the correct semantic.
--
-- Idempotent — uses information_schema to skip each ALTER if the
-- column already exists. No innodb_strict_mode dance —
-- JOBFORM_TASK_WORKER is a slim table well below the row-size budget.
-- ============================================================================

-- cycle_no column
SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME = 'JOBFORM_TASK_WORKER'
       AND COLUMN_NAME = 'cycle_no'
);
SET @ddl = IF(@col_exists = 0,
    'ALTER TABLE JOBFORM_TASK_WORKER ADD COLUMN cycle_no VARCHAR(50) NULL',
    'SELECT "JOBFORM_TASK_WORKER.cycle_no already exists — skipped"'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- cycle_type column
SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME = 'JOBFORM_TASK_WORKER'
       AND COLUMN_NAME = 'cycle_type'
);
SET @ddl = IF(@col_exists = 0,
    'ALTER TABLE JOBFORM_TASK_WORKER ADD COLUMN cycle_type ENUM(''PVB'',''PU'') NULL',
    'SELECT "JOBFORM_TASK_WORKER.cycle_type already exists — skipped"'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================================================
-- Verification:
--   DESCRIBE JOBFORM_TASK_WORKER;
--     expect cycle_no VARCHAR(50) NULL + cycle_type ENUM('PVB','PU') NULL.
--   SELECT COUNT(*) FROM JOBFORM_TASK_WORKER WHERE cycle_no IS NOT NULL;
--     should be 0 immediately after migration (no Autoclave assigns yet).
-- ============================================================================