-- ============================================================================
-- Phase M6-p2c-e — Autoclave per-cycle run history table
-- ============================================================================
--
-- Background (CLAUDE.md invariant 42 + M6-p1-h11 known limitation):
--
--   Migration 30 (M4-p7b-r3) added cycle_no + cycle_type columns to
--   JOBFORM_TASK_WORKER so the supervisor's Autoclave Assign sheet
--   could capture which physical furnace cycle each worker × serial
--   row went into. The UPSERT on JOBFORM_TASK_WORKER is keyed on the
--   PK (task_id, emp_no, serial_id) — fine for "one cycle per serial",
--   but M6-p1-h11 surfaced the edge case the bender's real workflow
--   actually hits: a serial that gets FI-Reprocessed (PC Removal)
--   goes BACK through Autoclave, sometimes with the SAME worker on
--   the redo and a NEW cycle_no. Under the UPSERT semantics, the
--   second Assign call overwrites the original (cycle_no, cycle_type)
--   on that row → the original cycle's metadata is LOST.
--
--   JOBFORM_BEND_CYCLE_LOG (migration 31) and JOBFORM_BEND_CYCLE_SERIAL
--   (migration 40) already solved the analogous problem for Glass
--   Bending — append-only history + per-serial linkage, never
--   overwriting. M6-p2c-e brings the Autoclave side up to parity by
--   introducing JOBFORM_AUTOCLAVE_RUN, an append-only audit table
--   that captures every cycle a (task, serial) pair has been
--   committed to.
--
-- Schema rationale:
--
--   * (task_id, serial_id, cycle_no) UNIQUE — idempotent re-asserts
--     of the SAME cycle (e.g. supervisor re-saves the Assign sheet
--     without changing cycle metadata) are no-ops via INSERT IGNORE.
--     A NEW cycle_no after Reprocess writes a new row instead.
--   * cycle_type ENUM('PVB','PU') matches migration 30's enum verbatim.
--   * FK to JOBFORM_SERIAL ON DELETE CASCADE — when a serial gets
--     wiped (rare; admin-only cleanup), its run history goes with it.
--   * started_at / started_by capture the moment the Assign call
--     committed the cycle. The worker who triggered the Assign is
--     informational; the canonical "who's currently assigned" still
--     lives in JOBFORM_TASK_WORKER per stage worker rules.
--   * emp_no NULL-able for backwards compat — if the API ever writes
--     a row without resolving the worker (e.g. a server-side cycle
--     re-tag flow), the row is still legal.
--
-- Server changes that depend on this migration:
--
--   * agg-api/v1/jobforms/task_assign.php — when stage='Autoclave',
--     INSERT IGNORE one JOBFORM_AUTOCLAVE_RUN row per worker × serial
--     pair alongside the existing JOBFORM_TASK_WORKER UPSERT. The
--     existing UPSERT stays (it's the source of truth for "who's
--     assigned RIGHT NOW"); the new table is the append-only audit
--     of every (task, serial, cycle) tuple ever asserted.
--   * agg-api/v1/glasses/show.php — surfaces autoclave_runs[] per
--     serial in the M5-p2 per-unit detail response.
--
-- Mobile consumers (M6-p2c-e):
--
--   * GlassUnitDetail DTO grows autoclaveRuns: List<GlassUnitAutoclaveRun>.
--   * GlassUnitDetailFragment renders the new metadata in the History
--     card as a per-serial "Autoclave: AC-201 (PVB) → AC-205 (PVB,
--     post-reprocess)" mini-timeline.
--
-- Idempotency:
--
--   * `CREATE TABLE IF NOT EXISTS` makes the migration safe to re-run.
--   * No backfill — pre-p2c-e Autoclave Assigns wrote cycle metadata
--     to JOBFORM_TASK_WORKER only; recreating those rows here would
--     misrepresent the timestamp ("now" vs. "when the assign actually
--     ran") and risks polluting a clean audit log. Pre-p2c-e cycles
--     stay queryable via JOBFORM_TASK_WORKER.cycle_no on serials that
--     haven't been reprocessed (where the original is still in place).
--
-- ============================================================================

CREATE TABLE IF NOT EXISTS JOBFORM_AUTOCLAVE_RUN (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    task_id     INT          NOT NULL,
    serial_id   INT UNSIGNED NOT NULL,
    cycle_no    VARCHAR(50)  NOT NULL,
    cycle_type  ENUM('PVB','PU') NOT NULL,
    emp_no      INT          NULL,
    started_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    started_by  VARCHAR(50)  NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_run         (task_id, serial_id, cycle_no),
    KEY idx_ar_serial_started (serial_id, started_at),
    KEY idx_ar_cycle          (cycle_no),
    KEY idx_ar_task           (task_id),
    CONSTRAINT fk_ar_serial FOREIGN KEY (serial_id)
        REFERENCES JOBFORM_SERIAL(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================================
-- Verification
-- ============================================================================
--
--   -- Table shape
--   SHOW CREATE TABLE JOBFORM_AUTOCLAVE_RUN;
--
--   -- After deploy + an Autoclave Assign on a per-unit task, expect
--   -- one row per (task, serial, cycle) triple. A second Assign on
--   -- the SAME triple is a no-op (UNIQUE + INSERT IGNORE). A second
--   -- Assign on the SAME (task, serial) with a NEW cycle (post-
--   -- reprocess) writes a fresh row:
--   SELECT r.id, r.task_id, r.serial_id, s.temp_serial,
--          r.cycle_no, r.cycle_type, r.started_at, r.started_by
--     FROM JOBFORM_AUTOCLAVE_RUN r
--     JOIN JOBFORM_SERIAL s ON s.id = r.serial_id
--    ORDER BY r.started_at DESC
--    LIMIT 20;
--
--   -- Cross-cycle audit: which serials went through cycle AC-201?
--   SELECT s.temp_serial, s.permanent_serial, r.cycle_type, r.started_at
--     FROM JOBFORM_AUTOCLAVE_RUN r
--     JOIN JOBFORM_SERIAL s ON s.id = r.serial_id
--    WHERE r.cycle_no = 'AC-201'
--    ORDER BY r.started_at ASC;
--
--   -- Per-serial history: every cycle this serial was committed to,
--   -- ordered chronologically. Two rows for a reprocessed serial
--   -- (original + redo); one row for a clean-pass serial:
--   SELECT cycle_no, cycle_type, started_at, started_by
--     FROM JOBFORM_AUTOCLAVE_RUN
--    WHERE serial_id = ?
--    ORDER BY started_at ASC;
--
-- ============================================================================