-- CreateTable
CREATE TABLE "invoice_counters" (
    "year"        INTEGER NOT NULL PRIMARY KEY,
    "last_number" INTEGER NOT NULL DEFAULT 0,
    "updated_at"  TIMESTAMP(3) NOT NULL
);

-- Backfill counters from existing invoices so subsequent numbers don't collide
INSERT INTO "invoice_counters" ("year", "last_number", "updated_at")
SELECT
    EXTRACT(YEAR FROM issued_at)::int AS year,
    COUNT(*)::int AS last_number,
    NOW() AS updated_at
FROM "invoices"
GROUP BY EXTRACT(YEAR FROM issued_at);
