-- ─────────────────────────────────────────────────────────────────────────────
-- 054 — Tax invoices and credit notes
--
-- An invoice is not a view over an order. It is a legal document that must say
-- the same thing in three years' time as it did on the day it was issued, even
-- though the merchant will have changed their address, their GSTIN, their
-- prices and their product names in between. So everything it prints is
-- snapshotted here at issue time, and nothing in this table is ever updated
-- once `issuedAt` is set.
--
-- What is deliberately NOT copied: the per-line GST breakdown. sales.order_items
-- already snapshots hsnCode, the three rates, taxableValue and the three tax
-- amounts at the moment a checkout became an order (see schema.prisma), and
-- those rows are themselves immutable. Copying them again would create a second
-- version of the same truth that could drift from the first.
-- ─────────────────────────────────────────────────────────────────────────────

BEGIN;

-- ── The number series ────────────────────────────────────────────────────────
--
-- Rule 46 of the CGST Rules wants a *consecutive* serial number, unique within
-- a financial year. A Postgres sequence cannot do this: nextval() is exempt
-- from rollback by design, so every failed transaction silently burns a number
-- and leaves a hole an auditor will ask about.
--
-- A counter row can, because UPDATE ... RETURNING participates in the
-- transaction: if the invoice insert fails, the number goes back. The row lock
-- serialises concurrent issuers for the same store, which is exactly the
-- behaviour wanted — two invoices must never share a number, and waiting a few
-- milliseconds for the lock is cheaper than any reconciliation.
CREATE TABLE IF NOT EXISTS "sales"."invoice_counters" (
  "storeId"       TEXT NOT NULL,
  -- Indian financial years run April–March. Stored as the starting year, so
  -- 2026 means 2026-04-01 … 2027-03-31 and prints as "2026-27".
  "financialYear" INT  NOT NULL,
  -- Separate series per document kind: a credit note may not continue the
  -- invoice numbering.
  "kind"          TEXT NOT NULL,
  "nextNumber"    INT  NOT NULL DEFAULT 1,

  "createdOn"     TIMESTAMPTZ NOT NULL DEFAULT now(),
  "updatedOn"     TIMESTAMPTZ NOT NULL DEFAULT now(),

  CONSTRAINT "invoice_counters_pkey" PRIMARY KEY ("storeId", "financialYear", "kind"),
  CONSTRAINT "invoice_counters_kind_check"
    CHECK ("kind" IN ('tax_invoice', 'credit_note'))
);

-- ── The documents ────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS "sales"."invoices" (
  "id"             TEXT NOT NULL,
  "storeId"        TEXT NOT NULL,
  "organizationId" TEXT,
  "orderId"        TEXT NOT NULL,

  "kind"           TEXT NOT NULL DEFAULT 'tax_invoice',
  -- A credit note points at the invoice it reverses.
  "relatedInvoiceId" TEXT,

  -- The printed identity. `invoiceNumber` is the full human string
  -- ("INV/2026-27/00001234"); `serial` is the raw counter value it came from,
  -- kept so a gap in the series can be proven absent without parsing strings.
  "invoiceNumber"  TEXT NOT NULL,
  "financialYear"  INT  NOT NULL,
  "serial"         INT  NOT NULL,
  "issuedAt"       TIMESTAMPTZ NOT NULL DEFAULT now(),

  -- ── Snapshots ──────────────────────────────────────────────────────────
  -- The seller as they were on the day. This is the one that bites: a merchant
  -- who moves premises or re-registers for GST would otherwise silently rewrite
  -- every invoice they have ever issued.
  "sellerSnapshot" JSONB NOT NULL,
  "buyerSnapshot"  JSONB NOT NULL,
  "billingAddress" JSONB,
  "shippingAddress" JSONB,
  -- Place of supply decides CGST+SGST versus IGST, so the invoice has to state
  -- which it was judged on rather than leaving it inferable from a store row
  -- that can change.
  "placeOfSupply"  TEXT,
  "isInterState"   BOOLEAN NOT NULL DEFAULT false,

  -- ── Money, in paise, exactly as printed ────────────────────────────────
  "currency"       TEXT NOT NULL DEFAULT 'INR',
  "subtotal"       INT  NOT NULL DEFAULT 0,
  "discountTotal"  INT  NOT NULL DEFAULT 0,
  "taxableAmount"  INT  NOT NULL DEFAULT 0,
  "cgstTotal"      INT  NOT NULL DEFAULT 0,
  "sgstTotal"      INT  NOT NULL DEFAULT 0,
  "igstTotal"      INT  NOT NULL DEFAULT 0,
  "tcsTotal"       INT  NOT NULL DEFAULT 0,
  "shippingTotal"  INT  NOT NULL DEFAULT 0,
  "grandTotal"     INT  NOT NULL DEFAULT 0,

  -- ── The rendered PDF ───────────────────────────────────────────────────
  -- Null until first requested: the PDF is generated lazily and then reused
  -- for ever. `pdfSha256` is what makes "reused for ever" checkable — a
  -- re-render that produces different bytes is a bug worth catching, not a
  -- silent overwrite.
  "pdfStorageKey"  TEXT,
  "pdfBytes"       INT,
  "pdfSha256"      TEXT,
  "pdfGeneratedAt" TIMESTAMPTZ,

  "createdBy"      TEXT NOT NULL DEFAULT 'system',
  "createdOn"      TIMESTAMPTZ NOT NULL DEFAULT now(),
  "updatedBy"      TEXT NOT NULL DEFAULT 'system',
  "updatedOn"      TIMESTAMPTZ NOT NULL DEFAULT now(),

  CONSTRAINT "invoices_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "invoices_kind_check" CHECK ("kind" IN ('tax_invoice', 'credit_note'))
);

-- One tax invoice per order. This is what makes lazy generation safe: two
-- requests racing to produce the first PDF both try to insert, one wins, the
-- loser reads the winner's row instead of rendering a second document with a
-- second number.
CREATE UNIQUE INDEX IF NOT EXISTS "invoices_one_per_order"
  ON "sales"."invoices" ("orderId")
  WHERE "kind" = 'tax_invoice';

-- The series itself must be unique, per store, per year, per kind.
CREATE UNIQUE INDEX IF NOT EXISTS "invoices_series_unique"
  ON "sales"."invoices" ("storeId", "financialYear", "kind", "serial");

CREATE UNIQUE INDEX IF NOT EXISTS "invoices_number_unique"
  ON "sales"."invoices" ("storeId", "invoiceNumber");

CREATE INDEX IF NOT EXISTS "invoices_store_issued_idx"
  ON "sales"."invoices" ("storeId", "issuedAt" DESC);

CREATE INDEX IF NOT EXISTS "invoices_related_idx"
  ON "sales"."invoices" ("relatedInvoiceId")
  WHERE "relatedInvoiceId" IS NOT NULL;

-- ── Immutability ─────────────────────────────────────────────────────────────
--
-- Enforced in the database rather than in the service, because "we always go
-- through the service" is exactly the assumption that fails at 2am during an
-- incident. The PDF columns stay writable so the lazy render can fill them in
-- once; everything a customer or an auditor reads is frozen at insert.
CREATE OR REPLACE FUNCTION "sales"."fn_invoice_is_immutable"()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW."invoiceNumber"  IS DISTINCT FROM OLD."invoiceNumber"
  OR NEW."serial"         IS DISTINCT FROM OLD."serial"
  OR NEW."financialYear"  IS DISTINCT FROM OLD."financialYear"
  OR NEW."orderId"        IS DISTINCT FROM OLD."orderId"
  OR NEW."issuedAt"       IS DISTINCT FROM OLD."issuedAt"
  OR NEW."sellerSnapshot" IS DISTINCT FROM OLD."sellerSnapshot"
  OR NEW."buyerSnapshot"  IS DISTINCT FROM OLD."buyerSnapshot"
  OR NEW."grandTotal"     IS DISTINCT FROM OLD."grandTotal"
  OR NEW."cgstTotal"      IS DISTINCT FROM OLD."cgstTotal"
  OR NEW."sgstTotal"      IS DISTINCT FROM OLD."sgstTotal"
  OR NEW."igstTotal"      IS DISTINCT FROM OLD."igstTotal"
  THEN
    RAISE EXCEPTION
      'invoice % is issued and cannot be altered — cancel it with a credit note instead',
      OLD."invoiceNumber"
      USING ERRCODE = 'restrict_violation';
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS "trg_invoices_immutable" ON "sales"."invoices";
CREATE TRIGGER "trg_invoices_immutable"
  BEFORE UPDATE ON "sales"."invoices"
  FOR EACH ROW
  EXECUTE FUNCTION "sales"."fn_invoice_is_immutable"();

-- Deleting one is not a correction either; it is the destruction of a record
-- the merchant is legally required to retain.
CREATE OR REPLACE FUNCTION "sales"."fn_invoice_no_delete"()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE EXCEPTION
    'invoice % cannot be deleted — issue a credit note instead',
    OLD."invoiceNumber"
    USING ERRCODE = 'restrict_violation';
END;
$$;

DROP TRIGGER IF EXISTS "trg_invoices_no_delete" ON "sales"."invoices";
CREATE TRIGGER "trg_invoices_no_delete"
  BEFORE DELETE ON "sales"."invoices"
  FOR EACH ROW
  EXECUTE FUNCTION "sales"."fn_invoice_no_delete"();

COMMIT;