-- ============================================================================
-- Phase M6-p1 — JOBFORM_SERIAL retry lineage columns
-- ============================================================================
--
-- When FI Reject fires on a per-unit serial, M6-p1 allocates a new
-- JOBFORM_SERIAL row to track the retry — temp_serial gets a `-R{n}`
-- suffix (e.g. `1003-26 FDL2` → `1003-26 FDL2-R1` → `-R2` etc.). Two
-- new columns track the retry lineage:
--
--   retry_of_serial_id  — FK to the rejected JOBFORM_SERIAL row
--                         (the IMMEDIATE parent, not the root). Null
--                         on every serial that was never rejected.
--   retry_count         — 0 for original serials; 1 for the first
--                         retry; 2 for the retry of the first retry;
--                         etc. Tracks the depth in the retry chain.
--
-- The root serial (`retry_of_serial_id IS NULL`) holds the canonical
-- temp_serial base; each retry's temp_serial is computed as
-- `root.temp_serial || '-R' || retry_count`. Tracing back to the root
-- is a recursive lookup; the qc_create.php cascade resolves it inside
-- the same db_tx as the FI Reject.
--
-- Idempotent via information_schema column probes — re-running this
-- migration on a database that already has the columns is a no-op.
--
-- ============================================================================

-- Add retry_of_serial_id if missing.
SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME = 'JOBFORM_SERIAL'
       AND COLUMN_NAME = 'retry_of_serial_id'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE JOBFORM_SERIAL ADD COLUMN retry_of_serial_id INT UNSIGNED NULL AFTER permanent_serial',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Add retry_count if missing.
SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME = 'JOBFORM_SERIAL'
       AND COLUMN_NAME = 'retry_count'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE JOBFORM_SERIAL ADD COLUMN retry_count INT NOT NULL DEFAULT 0 AFTER retry_of_serial_id',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Add an index on retry_of_serial_id so the "find children of this
-- rejected serial" lookup is cheap on the rare audit query.
SET @idx_exists = (
    SELECT COUNT(*) FROM information_schema.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME = 'JOBFORM_SERIAL'
       AND INDEX_NAME = 'idx_retry_of_serial_id'
);
SET @sql = IF(@idx_exists = 0,
    'ALTER TABLE JOBFORM_SERIAL ADD INDEX idx_retry_of_serial_id (retry_of_serial_id)',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Optional FK constraint — wraps in TRY/CATCH-style probe since
-- adding an FK on a column with existing data needs the data to be
-- consistent (it is — every existing row has retry_of_serial_id=NULL).
-- The FK targets the same table (self-reference); CASCADE behavior is
-- intentionally RESTRICT so a careless DELETE on the parent doesn't
-- silently null out the children's lineage.
SET @fk_exists = (
    SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME = 'JOBFORM_SERIAL'
       AND CONSTRAINT_NAME = 'fk_jobform_serial_retry_of'
);
SET @sql = IF(@fk_exists = 0,
    'ALTER TABLE JOBFORM_SERIAL ADD CONSTRAINT fk_jobform_serial_retry_of FOREIGN KEY (retry_of_serial_id) REFERENCES JOBFORM_SERIAL(id) ON DELETE RESTRICT ON UPDATE RESTRICT',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ============================================================================
-- Verification:
--   SHOW COLUMNS FROM JOBFORM_SERIAL LIKE 'retry%';
--   Expect two columns: retry_of_serial_id (int unsigned NULL) and
--   retry_count (int NOT NULL DEFAULT 0).
--
--   SHOW INDEX FROM JOBFORM_SERIAL WHERE Key_name='idx_retry_of_serial_id';
--   Expect 1 row.
--
--   SHOW CREATE TABLE JOBFORM_SERIAL;
--   Expect CONSTRAINT `fk_jobform_serial_retry_of` FOREIGN KEY...
-- ============================================================================