-- wave-12: invoice-receipt-document (spec 179)
-- Creates receipts, receipt_payment_lines, receipt_sequences tables
-- and wires the FK from invoice_payments.receipt_id → receipts.id.

--> statement-breakpoint
CREATE TABLE receipts (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id         UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  customer_id       UUID NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  doc_type          TEXT NOT NULL CHECK (doc_type IN ('receipt','invoice_receipt')),
  receipt_number    TEXT,
  status            TEXT NOT NULL DEFAULT 'DRAFT' CHECK (status IN ('DRAFT','ISSUED','VOIDED')),
  invoice_id        UUID REFERENCES invoices(id) ON DELETE RESTRICT,
  currency          TEXT NOT NULL DEFAULT 'ILS',
  amount            NUMERIC(12,2) NOT NULL,
  ils_exchange_rate NUMERIC(10,4),
  amount_ils        NUMERIC(12,2),
  issued_at         TIMESTAMPTZ,
  issued_by         UUID REFERENCES users(id) ON DELETE SET NULL,
  pdf_r2_key        TEXT,
  void_reason       TEXT,
  voided_at         TIMESTAMPTZ,
  voided_by         UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

--> statement-breakpoint
CREATE INDEX idx_receipts_tenant ON receipts(tenant_id, status, issued_at DESC);

--> statement-breakpoint
CREATE INDEX idx_receipts_invoice ON receipts(invoice_id);

--> statement-breakpoint
CREATE TABLE receipt_payment_lines (
  id                   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  receipt_id           UUID NOT NULL REFERENCES receipts(id) ON DELETE CASCADE,
  method               TEXT NOT NULL CHECK (method IN ('cash','bank_transfer','cheque','credit_card','other')),
  amount               NUMERIC(12,2) NOT NULL,
  cheque_number        TEXT,
  cheque_bank          TEXT,
  cheque_branch        TEXT,
  cheque_account       TEXT,
  cheque_due_date      DATE,
  card_last_four       TEXT,
  card_brand           TEXT,
  reference            TEXT,
  invoice_payment_id   UUID REFERENCES invoice_payments(id) ON DELETE SET NULL
);

--> statement-breakpoint
CREATE TABLE receipt_sequences (
  tenant_id   UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  doc_type    TEXT NOT NULL CHECK (doc_type IN ('receipt','invoice_receipt')),
  prefix      TEXT NOT NULL DEFAULT '',
  next_number INTEGER NOT NULL DEFAULT 1,
  PRIMARY KEY (tenant_id, doc_type)
);

--> statement-breakpoint
ALTER TABLE invoice_payments
  ADD CONSTRAINT invoice_payments_receipt_id_fkey
  FOREIGN KEY (receipt_id) REFERENCES receipts(id) ON DELETE SET NULL;
