-- Atomic per-branch monthly invoice numbering.
-- Run on existing DBs that already have schema up to invoices.

SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS invoice_sequences (
    branch_id BIGINT UNSIGNED NOT NULL,
    period_key CHAR(6) NOT NULL COMMENT 'YYYYMM from issue_date',
    last_number INT UNSIGNED NOT NULL DEFAULT 0,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (branch_id, period_key),
    CONSTRAINT fk_invseq_branch FOREIGN KEY (branch_id) REFERENCES branches(id)
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;

-- Optional: enforce unique numbers per branch (skip if duplicate legacy rows exist)
-- ALTER TABLE invoices ADD UNIQUE KEY uq_invoices_branch_number (branch_id, invoice_number);