-- ============================================================
-- Fix: Quotation Number Race Condition
-- Creates a dedicated atomic counter table.
-- Run once on your database.
-- ============================================================

CREATE TABLE IF NOT EXISTS quotation_counters (
    counter_year  SMALLINT UNSIGNED NOT NULL,
    next_seq      INT UNSIGNED      NOT NULL DEFAULT 1,
    PRIMARY KEY (counter_year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Atomic quotation number sequence per year';

-- Pre-populate from existing data so the sequence continues from where you left off.
INSERT INTO quotation_counters (counter_year, next_seq)
SELECT
    CAST(SUBSTRING(quotation_no, 5, 4) AS UNSIGNED)                        AS counter_year,
    MAX(CAST(SUBSTRING(quotation_no, 10) AS UNSIGNED)) + 1                 AS next_seq
FROM quotations
WHERE quotation_no REGEXP '^FQS-[0-9]{4}-[0-9]+$'
GROUP BY CAST(SUBSTRING(quotation_no, 5, 4) AS UNSIGNED)
ON DUPLICATE KEY UPDATE next_seq = VALUES(next_seq);

-- Confirm the UNIQUE constraint already exists (schema.sql has it, but just in case).
-- ALTER TABLE quotations ADD UNIQUE IF NOT EXISTS (quotation_no);
