-- 0005: accounts receivable — customers, invoices, payments, applications.
-- Documents sit ON TOP of the ledger (docs/superpowers/specs/2026-07-31-ar-design.md):
-- issuing an invoice posts (receivable up / income up); receiving a payment
-- posts (cash up / receivable down) and applies to invoices atomically. Every
-- posted document links to its journal entry. Invoice/customer/payment
-- balances are ALWAYS derived — never stored (CLAUDE.md §3.4). Same armor as
-- the ledger core: composite FKs, forced RLS, hardened grants, TRUNCATE guards.

CREATE TABLE company_settings (
  company_id uuid PRIMARY KEY REFERENCES companies (id),
  ar_account_id bigint,
  FOREIGN KEY (company_id, ar_account_id) REFERENCES accounts (company_id, id)
);

CREATE TABLE customers (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  name text NOT NULL,
  email text,
  phone text,
  address text,
  terms_days int NOT NULL DEFAULT 30 CHECK (terms_days >= 0),
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (id),
  UNIQUE (company_id, id),
  UNIQUE (company_id, name)
);

-- §4: per-company invoice numbering via SELECT ... FOR UPDATE, mirrors entry_counters.
CREATE TABLE invoice_counters (
  company_id uuid PRIMARY KEY REFERENCES companies (id),
  next_invoice_no bigint NOT NULL DEFAULT 1
);

CREATE TABLE invoices (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  customer_id bigint NOT NULL,
  invoice_no bigint,               -- assigned at issue; NULL while draft
  -- 'paid' is DERIVED (total minus applications), never stored.
  status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'open', 'void')),
  invoice_date date NOT NULL,
  due_date date NOT NULL,
  memo text,
  -- migrated history: a number and a place in aging, but no ledger effect —
  -- the accounting impact already happened via QuickBooks history import.
  document_only boolean NOT NULL DEFAULT false,
  posted_entry_id bigint,          -- NULL for draft, and for document_only
  void_entry_id bigint,            -- set by void_invoice() for non-document_only voids
  created_by uuid NOT NULL REFERENCES users (id),
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (id),
  UNIQUE (company_id, id),
  UNIQUE (company_id, invoice_no), -- multiple NULLs (drafts) coexist fine
  FOREIGN KEY (company_id, customer_id) REFERENCES customers (company_id, id),
  FOREIGN KEY (company_id, posted_entry_id) REFERENCES journal_entries (company_id, id),
  FOREIGN KEY (company_id, void_entry_id) REFERENCES journal_entries (company_id, id),
  CHECK (status = 'draft' OR invoice_no IS NOT NULL)
);
CREATE INDEX invoices_by_customer ON invoices (company_id, customer_id);
CREATE INDEX invoices_by_status ON invoices (company_id, status);

CREATE TABLE invoice_lines (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  invoice_id bigint NOT NULL,
  description text,
  quantity numeric(12,4) NOT NULL DEFAULT 1 CHECK (quantity > 0),
  unit_price numeric(19,4) NOT NULL,
  -- amount = quantity * unit_price is enforced app-side (rounding happens once,
  -- at the boundary); the DB only guards the invariant that actually matters
  -- here: a line can never post a non-positive amount to the ledger.
  amount numeric(19,4) NOT NULL CHECK (amount > 0),
  income_account_id bigint NOT NULL, -- must be type 'income'; enforced by trigger below
  PRIMARY KEY (id),
  UNIQUE (company_id, id),
  FOREIGN KEY (company_id, invoice_id) REFERENCES invoices (company_id, id),
  FOREIGN KEY (company_id, income_account_id) REFERENCES accounts (company_id, id)
);
CREATE INDEX invoice_lines_by_invoice ON invoice_lines (company_id, invoice_id);

CREATE TABLE payments (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  customer_id bigint NOT NULL,
  payment_date date NOT NULL,
  amount numeric(19,4) NOT NULL CHECK (amount > 0),
  deposit_account_id bigint NOT NULL, -- must be type 'asset'; checked in receive_payment()
  posted_entry_id bigint NOT NULL,
  void_entry_id bigint,
  status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'void')),
  memo text,
  created_by uuid NOT NULL REFERENCES users (id),
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (id),
  UNIQUE (company_id, id),
  FOREIGN KEY (company_id, customer_id) REFERENCES customers (company_id, id),
  FOREIGN KEY (company_id, deposit_account_id) REFERENCES accounts (company_id, id),
  FOREIGN KEY (company_id, posted_entry_id) REFERENCES journal_entries (company_id, id),
  FOREIGN KEY (company_id, void_entry_id) REFERENCES journal_entries (company_id, id)
);
CREATE INDEX payments_by_customer ON payments (company_id, customer_id);

CREATE TABLE payment_applications (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  payment_id bigint NOT NULL,
  invoice_id bigint NOT NULL,
  amount numeric(19,4) NOT NULL CHECK (amount > 0),
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (id),
  UNIQUE (company_id, id),
  -- §4: cross-company applications are physically impossible.
  FOREIGN KEY (company_id, payment_id) REFERENCES payments (company_id, id),
  FOREIGN KEY (company_id, invoice_id) REFERENCES invoices (company_id, id)
);
CREATE INDEX payment_applications_by_payment ON payment_applications (company_id, payment_id);
CREATE INDEX payment_applications_by_invoice ON payment_applications (company_id, invoice_id);

-- Row-level security: ENABLE + FORCE everywhere, keyed to current_company_id().
DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY[
    'company_settings', 'customers', 'invoice_counters',
    'invoices', 'invoice_lines', 'payments', 'payment_applications'
  ] LOOP
    EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', t);
    EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY', t);
    EXECUTE format(
      'CREATE POLICY %I_isolation ON %I USING (company_id = current_company_id())',
      t, t);
  END LOOP;
END $$;

-- TRUNCATE never fires row triggers; forbidden outright on every AR table
-- (forbid_truncate() already exists, from db/migrations/0002_ledger_core.sql).
CREATE TRIGGER company_settings_no_truncate
  BEFORE TRUNCATE ON company_settings FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate();
CREATE TRIGGER customers_no_truncate
  BEFORE TRUNCATE ON customers FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate();
CREATE TRIGGER invoice_counters_no_truncate
  BEFORE TRUNCATE ON invoice_counters FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate();
CREATE TRIGGER invoices_no_truncate
  BEFORE TRUNCATE ON invoices FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate();
CREATE TRIGGER invoice_lines_no_truncate
  BEFORE TRUNCATE ON invoice_lines FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate();
CREATE TRIGGER payments_no_truncate
  BEFORE TRUNCATE ON payments FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate();
CREATE TRIGGER payment_applications_no_truncate
  BEFORE TRUNCATE ON payment_applications FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate();

-- company_settings.ar_account_id must point at an asset account, whether set
-- by ensure_ar_account()'s auto-detect or edited directly later.
CREATE FUNCTION company_settings_ar_account_is_asset() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE v_type text;
BEGIN
  IF NEW.ar_account_id IS NULL THEN RETURN NEW; END IF;
  SELECT type INTO v_type FROM accounts
   WHERE company_id = NEW.company_id AND id = NEW.ar_account_id;
  IF v_type IS NULL THEN
    RAISE EXCEPTION 'account % not found', NEW.ar_account_id;
  END IF;
  IF v_type <> 'asset' THEN
    RAISE EXCEPTION
      'accounts receivable account must be an asset account (account % is %)',
      NEW.ar_account_id, v_type;
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER company_settings_ar_account_is_asset
  BEFORE INSERT OR UPDATE ON company_settings
  FOR EACH ROW EXECUTE FUNCTION company_settings_ar_account_is_asset();

-- invoice_lines.income_account_id must be type 'income' (a composite FK alone
-- cannot express "the right kind of account", only "an account of this company").
CREATE FUNCTION invoice_lines_income_account_only() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE v_type text;
BEGIN
  SELECT type INTO v_type FROM accounts
   WHERE company_id = NEW.company_id AND id = NEW.income_account_id;
  IF v_type IS NULL THEN
    RAISE EXCEPTION 'account % not found', NEW.income_account_id;
  END IF;
  IF v_type <> 'income' THEN
    RAISE EXCEPTION
      'invoice lines must post to an income account (account % is %)',
      NEW.income_account_id, v_type;
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER invoice_lines_income_account_only
  BEFORE INSERT OR UPDATE ON invoice_lines
  FOR EACH ROW EXECUTE FUNCTION invoice_lines_income_account_only();

-- Invoices are born drafts; issue_invoice()/void_invoice() are the only paths
-- to a number, a posting, or a void (mirrors entries_born_draft in 0002).
CREATE FUNCTION invoices_born_draft() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.status <> 'draft' OR NEW.invoice_no IS NOT NULL
     OR NEW.posted_entry_id IS NOT NULL OR NEW.void_entry_id IS NOT NULL THEN
    RAISE EXCEPTION 'invoices are created as drafts; use issue_invoice() to issue';
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER invoices_born_draft
  BEFORE INSERT ON invoices
  FOR EACH ROW EXECUTE FUNCTION invoices_born_draft();

-- Issued-invoice header immutability: once out of draft, only the exact
-- open -> void transition (status + void_entry_id, nothing else) is allowed —
-- that's void_invoice()'s own UPDATE. Everything else, including issue_invoice's
-- draft -> open transition, is unaffected because it starts from OLD.status='draft'.
CREATE FUNCTION invoices_immutable_when_issued() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    IF OLD.status <> 'draft' THEN
      RAISE EXCEPTION 'issued invoice % is immutable; void it instead of deleting', OLD.id;
    END IF;
    RETURN OLD;
  END IF;

  IF OLD.status = 'draft' THEN
    RETURN NEW;
  END IF;

  IF OLD.status = 'open' AND NEW.status = 'void'
     AND NEW.customer_id     IS NOT DISTINCT FROM OLD.customer_id
     AND NEW.invoice_no      IS NOT DISTINCT FROM OLD.invoice_no
     AND NEW.invoice_date    IS NOT DISTINCT FROM OLD.invoice_date
     AND NEW.due_date        IS NOT DISTINCT FROM OLD.due_date
     AND NEW.memo            IS NOT DISTINCT FROM OLD.memo
     AND NEW.document_only   IS NOT DISTINCT FROM OLD.document_only
     AND NEW.posted_entry_id IS NOT DISTINCT FROM OLD.posted_entry_id
  THEN
    RETURN NEW;
  END IF;

  RAISE EXCEPTION 'issued invoice % is immutable; void it instead of editing', OLD.id;
END $$;

CREATE TRIGGER invoices_immutable_when_issued
  BEFORE UPDATE OR DELETE ON invoices
  FOR EACH ROW EXECUTE FUNCTION invoices_immutable_when_issued();

-- Invoice lines freeze the moment their invoice leaves draft — mirrors
-- forbid_posted_line_change's FOR SHARE race-safe pattern from 0002.
CREATE FUNCTION invoice_lines_immutable_when_issued() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_company uuid;
  v_invoice bigint;
  v_status text;
BEGIN
  IF TG_OP = 'INSERT' THEN
    v_company := NEW.company_id; v_invoice := NEW.invoice_id;
  ELSE
    v_company := OLD.company_id; v_invoice := OLD.invoice_id;
  END IF;

  SELECT status INTO v_status FROM invoices i
   WHERE i.company_id = v_company AND i.id = v_invoice
   FOR SHARE;
  IF v_status <> 'draft' THEN
    RAISE EXCEPTION
      'lines of an issued invoice are immutable; void the invoice instead';
  END IF;

  IF TG_OP = 'UPDATE' AND (NEW.invoice_id <> OLD.invoice_id
      OR NEW.company_id <> OLD.company_id) THEN
    SELECT status INTO v_status FROM invoices i
     WHERE i.company_id = NEW.company_id AND i.id = NEW.invoice_id
     FOR SHARE;
    IF v_status <> 'draft' THEN
      RAISE EXCEPTION
        'lines of an issued invoice are immutable; void the invoice instead';
    END IF;
  END IF;

  IF TG_OP = 'DELETE' THEN RETURN OLD; END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER invoice_lines_immutable_when_issued
  BEFORE INSERT OR UPDATE OR DELETE ON invoice_lines
  FOR EACH ROW EXECUTE FUNCTION invoice_lines_immutable_when_issued();

-- Payments are born fully-formed and posted (receive_payment() creates the
-- payment row and its entry atomically — there is no payment "draft" state);
-- the only further transition is void_payment()'s active -> void.
CREATE FUNCTION payments_immutable() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    RAISE EXCEPTION 'payments are immutable; void payment % instead', OLD.id;
  END IF;

  IF OLD.status = 'active' AND NEW.status = 'void'
     AND NEW.customer_id         IS NOT DISTINCT FROM OLD.customer_id
     AND NEW.payment_date        IS NOT DISTINCT FROM OLD.payment_date
     AND NEW.amount               IS NOT DISTINCT FROM OLD.amount
     AND NEW.deposit_account_id  IS NOT DISTINCT FROM OLD.deposit_account_id
     AND NEW.posted_entry_id     IS NOT DISTINCT FROM OLD.posted_entry_id
     AND NEW.memo                IS NOT DISTINCT FROM OLD.memo
  THEN
    RETURN NEW;
  END IF;

  RAISE EXCEPTION 'payments are immutable; void payment % instead', OLD.id;
END $$;

CREATE TRIGGER payments_immutable
  BEFORE UPDATE OR DELETE ON payments
  FOR EACH ROW EXECUTE FUNCTION payments_immutable();

-- Applications are append-only for good: the correction path is voiding the
-- payment (a reversal), never editing or deleting an application row.
CREATE FUNCTION payment_applications_immutable() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'payment applications are immutable; void the payment instead';
END $$;

CREATE TRIGGER payment_applications_immutable
  BEFORE UPDATE OR DELETE ON payment_applications
  FOR EACH ROW EXECUTE FUNCTION payment_applications_immutable();

-- Deferred commit-time checks (fire once per queued row, after every
-- statement in the transaction has run — see check_entry_balanced in 0002
-- for the same pattern):
--  (a) a payment is never applied for more than its own amount
--  (b) an invoice is never applied for more than its own line total
--      (a voided payment's applications no longer count — CLAUDE.md-style
--      derived balances must reflect reality, not history)
--  (c) an application's payment and invoice belong to the same customer
CREATE FUNCTION check_payment_application() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_pay_amount numeric(19,4);
  v_applied numeric(19,4);
  v_inv_total numeric(19,4);
  v_inv_applied numeric(19,4);
  v_pay_customer bigint;
  v_inv_customer bigint;
BEGIN
  SELECT amount INTO v_pay_amount FROM payments
   WHERE company_id = NEW.company_id AND id = NEW.payment_id;
  SELECT coalesce(sum(amount), 0) INTO v_applied FROM payment_applications
   WHERE company_id = NEW.company_id AND payment_id = NEW.payment_id;
  IF v_applied > v_pay_amount THEN
    RAISE EXCEPTION 'payment % is over-applied: % applied against a % payment',
      NEW.payment_id, v_applied, v_pay_amount;
  END IF;

  SELECT coalesce(sum(amount), 0) INTO v_inv_total FROM invoice_lines
   WHERE company_id = NEW.company_id AND invoice_id = NEW.invoice_id;
  SELECT coalesce(sum(pa.amount), 0) INTO v_inv_applied FROM payment_applications pa
    JOIN payments p ON p.company_id = pa.company_id AND p.id = pa.payment_id
   WHERE pa.company_id = NEW.company_id AND pa.invoice_id = NEW.invoice_id
     AND p.status = 'active';
  IF v_inv_applied > v_inv_total THEN
    RAISE EXCEPTION 'invoice % is over-applied: % applied against a % invoice',
      NEW.invoice_id, v_inv_applied, v_inv_total;
  END IF;

  SELECT customer_id INTO v_pay_customer FROM payments
   WHERE company_id = NEW.company_id AND id = NEW.payment_id;
  SELECT customer_id INTO v_inv_customer FROM invoices
   WHERE company_id = NEW.company_id AND id = NEW.invoice_id;
  IF v_pay_customer IS DISTINCT FROM v_inv_customer THEN
    RAISE EXCEPTION 'payment % and invoice % belong to different customers',
      NEW.payment_id, NEW.invoice_id;
  END IF;

  RETURN NULL;
END $$;

-- payment_applications_immutable already forbids UPDATE/DELETE unconditionally,
-- so only INSERT can ever reach here in practice; declared as such.
CREATE CONSTRAINT TRIGGER payment_application_checks
  AFTER INSERT ON payment_applications
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION check_payment_application();

-- Grants. Baseline SELECT+INSERT, then column-hardened UPDATE where the
-- table has any state app must not be able to set by hand — mirrors
-- journal_entries' status/entry_no/posted_at hardening in 0002. Tables whose
-- entire row is only ever legitimately written atomically alongside a ledger
-- posting (invoice_counters, payments, payment_applications) get SELECT only:
-- every write to them happens inside a SECURITY DEFINER function.
GRANT SELECT, INSERT ON company_settings TO app;
GRANT UPDATE (ar_account_id) ON company_settings TO app;

GRANT SELECT, INSERT ON customers TO app;
GRANT UPDATE (name, email, phone, address, terms_days, active) ON customers TO app;

GRANT SELECT ON invoice_counters TO app;

GRANT SELECT, INSERT, DELETE ON invoices TO app;
GRANT UPDATE (customer_id, invoice_date, due_date, memo) ON invoices TO app;

GRANT SELECT, INSERT, UPDATE, DELETE ON invoice_lines TO app;

GRANT SELECT ON payments TO app;
-- no INSERT: every payment is created atomically alongside its posting entry
-- by receive_payment(). UPDATE/DELETE are granted so the immutability trigger
-- (not a bare permission error) is what an app attempt actually hits.
GRANT SELECT, UPDATE, DELETE ON payment_applications TO app;

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app;

-- Internal helper: not a public entry point (no session-company guard of its
-- own — callers establish/verify that). Mirrors assert_entry_balanced/
-- reverse_entry_internal's EXECUTE-revocation pattern from 0002.
CREATE FUNCTION ensure_ar_account(p_company_id uuid) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_account_id bigint;
BEGIN
  SELECT ar_account_id INTO v_account_id FROM company_settings
   WHERE company_id = p_company_id FOR UPDATE;
  IF NOT FOUND THEN
    INSERT INTO company_settings (company_id) VALUES (p_company_id);
    v_account_id := NULL;
  END IF;

  IF v_account_id IS NULL THEN
    SELECT id INTO v_account_id FROM accounts
     WHERE company_id = p_company_id AND type = 'asset' AND name = 'Accounts Receivable'
     ORDER BY id LIMIT 1;
    IF v_account_id IS NULL THEN
      RAISE EXCEPTION
        'no Accounts Receivable account found for this company — set one in company settings';
    END IF;
    UPDATE company_settings SET ar_account_id = v_account_id
     WHERE company_id = p_company_id;
  END IF;

  RETURN v_account_id;
END $$;

REVOKE EXECUTE ON FUNCTION ensure_ar_account(uuid) FROM PUBLIC;

-- Derived-only balances (CLAUDE.md §3.4): never stored, always computed from
-- invoice_lines / payment_applications at query time. A voided payment's
-- applications are excluded — the cash never actually stayed applied.
CREATE FUNCTION invoice_open_balance(p_company_id uuid, p_invoice_id bigint)
RETURNS numeric(19,4)
LANGUAGE sql STABLE AS $$
  SELECT (
    coalesce((SELECT sum(amount) FROM invoice_lines
                WHERE company_id = p_company_id AND invoice_id = p_invoice_id), 0)
    - coalesce((SELECT sum(pa.amount) FROM payment_applications pa
                  JOIN payments p ON p.company_id = pa.company_id AND p.id = pa.payment_id
                 WHERE pa.company_id = p_company_id AND pa.invoice_id = p_invoice_id
                   AND p.status = 'active'), 0)
  )::numeric(19,4)
$$;

CREATE FUNCTION payment_unapplied_balance(p_company_id uuid, p_payment_id bigint)
RETURNS numeric(19,4)
LANGUAGE sql STABLE AS $$
  SELECT (
    p.amount - coalesce((SELECT sum(amount) FROM payment_applications
                           WHERE company_id = p_company_id AND payment_id = p_payment_id), 0)
  )::numeric(19,4)
    FROM payments p WHERE p.company_id = p_company_id AND p.id = p_payment_id
$$;

-- Issue: assigns the invoice number (FOR UPDATE counter, mirrors post_entry),
-- and — unless the invoice is document_only — posts debit AR / credit each
-- line's income account (grouped by account into one line per account).
CREATE FUNCTION issue_invoice(p_company_id uuid, p_invoice_id bigint, p_user_id uuid)
RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_invoice invoices%ROWTYPE;
  v_line_count int;
  v_total numeric(19,4);
  v_ar_account bigint;
  v_no bigint;
  v_entry_id bigint;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match invoice company';
  END IF;

  SELECT * INTO v_invoice FROM invoices
   WHERE company_id = p_company_id AND id = p_invoice_id
   FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'invoice % not found', p_invoice_id;
  END IF;
  IF v_invoice.status <> 'draft' THEN
    RAISE EXCEPTION 'invoice % is already %', p_invoice_id, v_invoice.status;
  END IF;

  SELECT count(*), coalesce(sum(amount), 0) INTO v_line_count, v_total
    FROM invoice_lines WHERE company_id = p_company_id AND invoice_id = p_invoice_id;
  IF v_line_count < 1 THEN
    RAISE EXCEPTION 'invoice % has no lines', p_invoice_id;
  END IF;

  INSERT INTO invoice_counters (company_id) VALUES (p_company_id)
    ON CONFLICT (company_id) DO NOTHING;
  SELECT next_invoice_no INTO v_no FROM invoice_counters
   WHERE company_id = p_company_id FOR UPDATE;
  UPDATE invoice_counters SET next_invoice_no = v_no + 1
   WHERE company_id = p_company_id;

  IF v_invoice.document_only THEN
    UPDATE invoices SET status = 'open', invoice_no = v_no
     WHERE company_id = p_company_id AND id = p_invoice_id;
  ELSE
    v_ar_account := ensure_ar_account(p_company_id);

    INSERT INTO journal_entries (company_id, entry_date, memo, created_by)
    VALUES (p_company_id, v_invoice.invoice_date, 'Invoice #' || v_no, p_user_id)
    RETURNING id INTO v_entry_id;

    INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit)
    VALUES (p_company_id, v_entry_id, v_ar_account, v_total, 0);

    INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit)
    SELECT p_company_id, v_entry_id, income_account_id, 0, sum(amount)
      FROM invoice_lines
     WHERE company_id = p_company_id AND invoice_id = p_invoice_id
     GROUP BY income_account_id;

    PERFORM post_entry(p_company_id, v_entry_id);

    UPDATE invoices SET status = 'open', invoice_no = v_no, posted_entry_id = v_entry_id
     WHERE company_id = p_company_id AND id = p_invoice_id;
  END IF;

  RETURN v_no;
END $$;

-- Void: refuses if any payment has been applied (unapply first = void the
-- payments); otherwise reverses the posting entry (document_only: no entry
-- to reverse, just flip the status — the accounting impact was never here).
CREATE FUNCTION void_invoice(
  p_company_id uuid, p_invoice_id bigint, p_void_date date, p_user_id uuid
) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_invoice invoices%ROWTYPE;
  v_app_count int;
  v_rev_id bigint;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match invoice company';
  END IF;

  SELECT * INTO v_invoice FROM invoices
   WHERE company_id = p_company_id AND id = p_invoice_id
   FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'invoice % not found', p_invoice_id;
  END IF;
  IF v_invoice.status <> 'open' THEN
    RAISE EXCEPTION 'invoice % is % — only open invoices can be voided',
      p_invoice_id, v_invoice.status;
  END IF;

  SELECT count(*) INTO v_app_count FROM payment_applications
   WHERE company_id = p_company_id AND invoice_id = p_invoice_id;
  IF v_app_count > 0 THEN
    RAISE EXCEPTION
      'invoice % has payments applied — void the payments applied to it first',
      p_invoice_id;
  END IF;

  IF v_invoice.document_only THEN
    UPDATE invoices SET status = 'void' WHERE company_id = p_company_id AND id = p_invoice_id;
  ELSE
    v_rev_id := reverse_entry(p_company_id, v_invoice.posted_entry_id, p_void_date, p_user_id);
    UPDATE invoices SET status = 'void', void_entry_id = v_rev_id
     WHERE company_id = p_company_id AND id = p_invoice_id;
  END IF;
END $$;

-- Receive a payment: posts debit deposit account / credit AR, creates the
-- payment row and any applications atomically. Applications may be empty
-- (a pure, fully-unapplied credit). p_spec: {customer_id, payment_date,
-- amount, deposit_account_id, memo, applications: [{invoice_id, amount}]}.
CREATE FUNCTION receive_payment(p_company_id uuid, p_spec jsonb, p_user_id uuid)
RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_customer_id bigint;
  v_date date;
  v_amount numeric(19,4);
  v_deposit_account bigint;
  v_deposit_type text;
  v_memo text;
  v_apps jsonb;
  v_app jsonb;
  v_ar_account bigint;
  v_entry_id bigint;
  v_payment_id bigint;
  v_invoice_id bigint;
  v_app_amount numeric(19,4);
  v_inv_customer bigint;
  v_inv_status text;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  v_customer_id := (p_spec->>'customer_id')::bigint;
  v_date := (p_spec->>'payment_date')::date;
  v_amount := (p_spec->>'amount')::numeric(19,4);
  v_deposit_account := (p_spec->>'deposit_account_id')::bigint;
  v_memo := p_spec->>'memo';
  v_apps := coalesce(p_spec->'applications', '[]'::jsonb);

  IF v_amount <= 0 THEN
    RAISE EXCEPTION 'payment amount must be positive';
  END IF;
  IF NOT EXISTS (
    SELECT 1 FROM customers WHERE company_id = p_company_id AND id = v_customer_id
  ) THEN
    RAISE EXCEPTION 'customer % not found', v_customer_id;
  END IF;

  SELECT type INTO v_deposit_type FROM accounts
   WHERE company_id = p_company_id AND id = v_deposit_account;
  IF v_deposit_type IS NULL THEN
    RAISE EXCEPTION 'account % not found', v_deposit_account;
  END IF;
  IF v_deposit_type <> 'asset' THEN
    RAISE EXCEPTION 'deposit account must be an asset account (account % is %)',
      v_deposit_account, v_deposit_type;
  END IF;

  v_ar_account := ensure_ar_account(p_company_id);

  INSERT INTO journal_entries (company_id, entry_date, memo, created_by)
  VALUES (p_company_id, v_date, coalesce(v_memo, 'Customer payment'), p_user_id)
  RETURNING id INTO v_entry_id;

  INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit)
  VALUES (p_company_id, v_entry_id, v_deposit_account, v_amount, 0),
         (p_company_id, v_entry_id, v_ar_account, 0, v_amount);

  PERFORM post_entry(p_company_id, v_entry_id);

  INSERT INTO payments
    (company_id, customer_id, payment_date, amount, deposit_account_id,
     posted_entry_id, memo, created_by)
  VALUES
    (p_company_id, v_customer_id, v_date, v_amount, v_deposit_account,
     v_entry_id, v_memo, p_user_id)
  RETURNING id INTO v_payment_id;

  FOR v_app IN SELECT * FROM jsonb_array_elements(v_apps)
  LOOP
    v_invoice_id := (v_app->>'invoice_id')::bigint;
    v_app_amount := (v_app->>'amount')::numeric(19,4);
    IF v_app_amount <= 0 THEN
      RAISE EXCEPTION 'application amount must be positive';
    END IF;

    SELECT customer_id, status INTO v_inv_customer, v_inv_status FROM invoices
     WHERE company_id = p_company_id AND id = v_invoice_id;
    IF NOT FOUND THEN
      RAISE EXCEPTION 'invoice % not found', v_invoice_id;
    END IF;
    IF v_inv_status <> 'open' THEN
      RAISE EXCEPTION 'invoice % is % — only open invoices accept payment',
        v_invoice_id, v_inv_status;
    END IF;
    IF v_inv_customer <> v_customer_id THEN
      RAISE EXCEPTION 'invoice % does not belong to this customer', v_invoice_id;
    END IF;

    INSERT INTO payment_applications (company_id, payment_id, invoice_id, amount)
    VALUES (p_company_id, v_payment_id, v_invoice_id, v_app_amount);
  END LOOP;

  RETURN v_payment_id;
END $$;

-- Apply an existing payment's unapplied remainder to more invoices. No new
-- entry — the cash already arrived when the payment was received.
CREATE FUNCTION apply_credit(
  p_company_id uuid, p_payment_id bigint, p_applications jsonb, p_user_id uuid
) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_payment payments%ROWTYPE;
  v_app jsonb;
  v_invoice_id bigint;
  v_amount numeric(19,4);
  v_inv_customer bigint;
  v_inv_status text;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  SELECT * INTO v_payment FROM payments
   WHERE company_id = p_company_id AND id = p_payment_id
   FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'payment % not found', p_payment_id;
  END IF;
  IF v_payment.status <> 'active' THEN
    RAISE EXCEPTION 'payment % is void', p_payment_id;
  END IF;

  FOR v_app IN SELECT * FROM jsonb_array_elements(coalesce(p_applications, '[]'::jsonb))
  LOOP
    v_invoice_id := (v_app->>'invoice_id')::bigint;
    v_amount := (v_app->>'amount')::numeric(19,4);
    IF v_amount <= 0 THEN
      RAISE EXCEPTION 'application amount must be positive';
    END IF;

    SELECT customer_id, status INTO v_inv_customer, v_inv_status FROM invoices
     WHERE company_id = p_company_id AND id = v_invoice_id;
    IF NOT FOUND THEN
      RAISE EXCEPTION 'invoice % not found', v_invoice_id;
    END IF;
    IF v_inv_status <> 'open' THEN
      RAISE EXCEPTION 'invoice % is % — only open invoices accept payment',
        v_invoice_id, v_inv_status;
    END IF;
    IF v_inv_customer <> v_payment.customer_id THEN
      RAISE EXCEPTION 'invoice % does not belong to this customer', v_invoice_id;
    END IF;

    INSERT INTO payment_applications (company_id, payment_id, invoice_id, amount)
    VALUES (p_company_id, p_payment_id, v_invoice_id, v_amount);
  END LOOP;
END $$;

-- Void a payment: reverses its posting entry. Its applications are untouched
-- (immutable) but no longer count toward any invoice's open balance, because
-- invoice_open_balance()/check_payment_application() filter on payments.status.
CREATE FUNCTION void_payment(
  p_company_id uuid, p_payment_id bigint, p_void_date date, p_user_id uuid
) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_payment payments%ROWTYPE;
  v_rev_id bigint;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  SELECT * INTO v_payment FROM payments
   WHERE company_id = p_company_id AND id = p_payment_id
   FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'payment % not found', p_payment_id;
  END IF;
  IF v_payment.status <> 'active' THEN
    RAISE EXCEPTION 'payment % is already void', p_payment_id;
  END IF;

  v_rev_id := reverse_entry(p_company_id, v_payment.posted_entry_id, p_void_date, p_user_id);
  UPDATE payments SET status = 'void', void_entry_id = v_rev_id
   WHERE company_id = p_company_id AND id = p_payment_id;
END $$;

-- Aging: per-invoice open balances bucketed by days past due_date, including
-- document_only invoices, excluding void; plus a per-customer row for each
-- unapplied payment credit. row_type distinguishes the two shapes.
CREATE FUNCTION ar_aging(p_company_id uuid, p_as_of date DEFAULT CURRENT_DATE)
RETURNS TABLE (
  row_type text,
  customer_id bigint,
  customer_name text,
  invoice_id bigint,
  invoice_no bigint,
  invoice_date date,
  due_date date,
  document_only boolean,
  open_amount numeric(19,4),
  bucket text
)
LANGUAGE sql STABLE AS $$
  WITH invoice_open AS (
    SELECT i.id AS invoice_id, i.customer_id, i.invoice_no, i.invoice_date,
           i.due_date, i.document_only,
           invoice_open_balance(p_company_id, i.id) AS open_amount
      FROM invoices i
     WHERE i.company_id = p_company_id AND i.status = 'open'
  ),
  payment_credits AS (
    SELECT p.customer_id, sum(payment_unapplied_balance(p_company_id, p.id)) AS credit_amount
      FROM payments p
     WHERE p.company_id = p_company_id AND p.status = 'active'
     GROUP BY p.customer_id
  )
  SELECT 'invoice'::text, io.customer_id, c.name, io.invoice_id, io.invoice_no,
         io.invoice_date, io.due_date, io.document_only, io.open_amount,
         CASE
           WHEN (p_as_of - io.due_date) <= 0 THEN 'current'
           WHEN (p_as_of - io.due_date) BETWEEN 1 AND 30 THEN '1-30'
           WHEN (p_as_of - io.due_date) BETWEEN 31 AND 60 THEN '31-60'
           WHEN (p_as_of - io.due_date) BETWEEN 61 AND 90 THEN '61-90'
           ELSE '90+'
         END
    FROM invoice_open io
    JOIN customers c ON c.company_id = p_company_id AND c.id = io.customer_id
   WHERE io.open_amount > 0

  UNION ALL

  SELECT 'credit'::text, pc.customer_id, c.name,
         NULL::bigint, NULL::bigint, NULL::date, NULL::date, NULL::boolean,
         pc.credit_amount::numeric(19,4), 'credit'::text
    FROM payment_credits pc
    JOIN customers c ON c.company_id = p_company_id AND c.id = pc.customer_id
   WHERE pc.credit_amount > 0
$$;
