-- ============================================================================
-- Phase M6-p2c-c — per-serial bend cycle linkage table
-- ============================================================================
--
-- Background (CLAUDE.md invariant 47):
--
--   AGG's glass bender holds 2 moulds per cycle (one windshield's full
--   L1+L2+L3+L4+Support stack per mould). The matched-set semantic is
--   per-mould = per-JOBFORM_GLASS, but a furnace cycle can carry up to
--   2 moulds drawn from different jobs / glasses. M4-p7c-h1 shipped
--   JOBFORM_BEND_CYCLE_LOG (append-only, keyed on task_id + cycle_no +
--   qty) which captures "this cycle bent qty=N units of layer Lk for
--   THIS task" but doesn't surface "which physical serials shared
--   cycle BC-145?" — a question the QC engineer asks routinely when
--   investigating a defect-cluster on a specific bend cycle.
--
--   M6-p2c-c adds the missing per-serial linkage. One row per
--   (cycle_no, serial_id) pair. Auto-written by task_complete.php on
--   Glass Bending Mark Complete: when cycle_no is supplied AND parent
--   stage is 'Glass Bending', INSERT IGNORE one row per serial of the
--   parent glass that doesn't already have a JOBFORM_BEND_CYCLE_SERIAL
--   row (first N un-linked serials, where N = the Mark Complete batch
--   qty). UNIQUE constraint on (cycle_no, serial_id) makes the auto-
--   link idempotent + race-safe: a duplicate INSERT IGNORE is a no-op.
--
-- One row per (cycle_no, serial_id) pair. Cross-cycle queries become
-- one-SELECT:
--
--   SELECT s.temp_serial, s.permanent_serial
--     FROM JOBFORM_BEND_CYCLE_SERIAL b
--     JOIN JOBFORM_SERIAL s ON s.id = b.serial_id
--    WHERE b.cycle_no = 'BC-145';
--
-- M6-p2c-c also makes cycle_no MANDATORY at Glass Bending Mark Complete
-- (server gate in task_complete.php + mobile gate in the dialog).
-- Pre-p2c-c Mark Completes were allowed to skip cycle_no (M4-p7c-h1
-- shipped it as optional); going forward the floor must always tag
-- which physical cycle the bend was in, so the audit trail stays
-- complete from p2c-c onward. Pre-p2c-c historical bends without
-- cycle_no stay in JOBFORM_BEND_CYCLE_LOG (no backfill needed — the
-- log carries cycle_no for every batch that had it; missing entries
-- mean "pre-p2c-c, cycle unknown").
--
-- The existing JOBFORM_BEND_CYCLE_LOG table (migration 31) is the
-- append-only HISTORY of every Mark Complete batch + cycle_no; it
-- STAYS in the schema as the authoritative log. The new
-- JOBFORM_BEND_CYCLE_SERIAL is a derived linkage table optimised for
-- the cross-cycle query — populated server-side from the same
-- Mark Complete event that writes the log row.
--
-- ============================================================================

CREATE TABLE IF NOT EXISTS JOBFORM_BEND_CYCLE_SERIAL (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    cycle_no    VARCHAR(50)  NOT NULL,
    serial_id   INT UNSIGNED NOT NULL,
    bound_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    bound_by    VARCHAR(50)  NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_cycle_serial (cycle_no, serial_id),
    KEY idx_bcs_serial (serial_id),
    KEY idx_bcs_cycle  (cycle_no),
    CONSTRAINT fk_bcs_serial FOREIGN KEY (serial_id)
        REFERENCES JOBFORM_SERIAL(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================================
-- Verification
-- ============================================================================
--
--   -- Table shape
--   SHOW CREATE TABLE JOBFORM_BEND_CYCLE_SERIAL;
--
--   -- After deploy + first Glass Bending Mark Complete with cycle_no,
--   -- expect one row per serial bent in the batch:
--   SELECT b.cycle_no, b.serial_id, s.temp_serial, b.bound_at, b.bound_by
--     FROM JOBFORM_BEND_CYCLE_SERIAL b
--     JOIN JOBFORM_SERIAL s ON s.id = b.serial_id
--    ORDER BY b.bound_at DESC
--    LIMIT 20;
--
--   -- Cross-cycle audit: which serials shared cycle BC-145?
--   SELECT s.temp_serial, s.permanent_serial, g.id AS glass_id
--     FROM JOBFORM_BEND_CYCLE_SERIAL b
--     JOIN JOBFORM_SERIAL s    ON s.id = b.serial_id
--     JOIN JOBFORM_GLASS  g    ON g.id = s.jobform_glass_id
--    WHERE b.cycle_no = 'BC-145';
--
-- ============================================================================