-- 0009: accounts payable — vendors, bills, bill payments, check printing,
-- aging. Mirrors 0005 (AR) deliberately (docs/superpowers/specs/
-- 2026-08-01-ap-design.md) but bakes in EVERY hardening lesson AR needed
-- three follow-up migrations (0006/0007/0008) to learn, in this ONE file:
--   - FOR UPDATE bill locks inside pay_bills()/apply_vendor_credit() before
--     inserting any application row (0006 lesson).
--   - point-in-time aging with voided_on columns from the start, plus both
--     the current-state and _asof derived-balance helpers (0007 lesson).
--   - period locks (and reversal-ordering) cover document_only issue/void
--     from the start (0008 DECISION 1), and a settable, greater-than-
--     highest-used counter for check numbers (0008 DECISION 2's pattern).
--   - ensure_ap_account() requires exactly one ACTIVE match from the start
--     (0006 lesson), never the "any match, no ambiguity check" version 0005
--     shipped with for AR.
--   - payments (bill_payments) get SELECT+UPDATE+DELETE from the start (not
--     SELECT-only) so the immutability trigger — not a bare permission
--     error — is what an app UPDATE attempt hits (0006 lesson).
--
-- Mirror-mapping deviations from AR, per the design spec:
--   - bills carry the VENDOR'S OWN reference (ref_no, optional, unique per
--     (company, vendor, ref_no) when set) instead of a per-company assigned
--     number — there is no bill_counters table. Since aging must still be
--     able to ask "had this bill ever been issued, as of date X" (the same
--     job invoice_no's NOT NULL-forever gate did for AR — see 0007's
--     header), bills carry issued_on date instead: stamped once by
--     issue_bill(), never cleared, used ONLY as a permanent "was this ever
--     issued" marker (the actual date arithmetic for point-in-time
--     inclusion is still bill_date <= as_of, exactly like AR's invoice_date).
--   - postings are the mirror image of AR's: issuing a bill CREDITS the A/P
--     account and DEBITS each line's category account (any account except
--     the company's own A/P account — bill_lines_category_not_ap below);
--     paying bills DEBITS A/P and CREDITS the money account.
--   - check printing is new machinery with no AR analogue: check_counters
--     (per company+money-account, mirrors invoice_counters' FOR UPDATE
--     pattern), bill_payments.method/check_no/check_status/printed_on, and
--     assign_check_numbers()/set_next_check_no().

-- company_settings (created in 0005) gains AP + check-printing settings.
ALTER TABLE company_settings ADD COLUMN ap_account_id bigint;
ALTER TABLE company_settings ADD COLUMN check_format text NOT NULL DEFAULT 'three_per_page'
  CHECK (check_format IN ('three_per_page', 'voucher'));
ALTER TABLE company_settings ADD COLUMN check_offset_x numeric(6,2) NOT NULL DEFAULT 0;
ALTER TABLE company_settings ADD COLUMN check_offset_y numeric(6,2) NOT NULL DEFAULT 0;
ALTER TABLE company_settings
  ADD CONSTRAINT company_settings_ap_account_fk
  FOREIGN KEY (company_id, ap_account_id) REFERENCES accounts (company_id, id);

CREATE TABLE vendors (
  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),
  track_1099 boolean NOT NULL DEFAULT false,
  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, money account) check numbering via SELECT ... FOR
-- UPDATE, mirrors invoice_counters/entry_counters. No default id column —
-- the key IS (company_id, money_account_id), one counter per bank account.
CREATE TABLE check_counters (
  company_id uuid NOT NULL REFERENCES companies (id),
  money_account_id bigint NOT NULL,
  next_check_no bigint NOT NULL DEFAULT 1,
  PRIMARY KEY (company_id, money_account_id),
  FOREIGN KEY (company_id, money_account_id) REFERENCES accounts (company_id, id)
);

CREATE TABLE bills (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  vendor_id bigint NOT NULL,
  ref_no text,                     -- the VENDOR's own invoice/reference number
  -- 'paid'/'partial' are DERIVED (total minus applications), never stored.
  status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'open', 'void')),
  bill_date date NOT NULL,
  due_date date NOT NULL,
  memo text,
  -- migrated history: a place in aging, but no ledger effect — the
  -- accounting impact already happened via QuickBooks history import.
  document_only boolean NOT NULL DEFAULT false,
  -- stamped once by issue_bill(), never cleared afterward — the permanent
  -- "this bill was, at some point, actually issued" marker that ap_aging()
  -- needs for point-in-time reporting (mirrors invoice_no's IS NOT NULL
  -- role for AR, without being a number anyone needs to assign or see).
  issued_on date,
  posted_entry_id bigint,          -- NULL for draft, and for document_only
  void_entry_id bigint,            -- set by void_bill() for non-document_only voids
  voided_on date,                  -- point-in-time aging (0007 lesson, from the start)
  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, vendor_id) REFERENCES vendors (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 bills_by_vendor ON bills (company_id, vendor_id);
CREATE INDEX bills_by_status ON bills (company_id, status);
-- the vendor's own reference need only be unique when actually set — many
-- vendors never supply one, and multiple NULLs must coexist freely.
CREATE UNIQUE INDEX bills_vendor_ref_no_unique
  ON bills (company_id, vendor_id, ref_no) WHERE ref_no IS NOT NULL;

CREATE TABLE bill_lines (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  bill_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 numeric(19,4) NOT NULL CHECK (amount > 0),
  -- any account EXCEPT the company's own A/P account (expense is typical,
  -- but asset purchases are legitimate too) — enforced by trigger below,
  -- since a composite FK alone can't express "not THIS specific account".
  category_account_id bigint NOT NULL,
  PRIMARY KEY (id),
  UNIQUE (company_id, id),
  FOREIGN KEY (company_id, bill_id) REFERENCES bills (company_id, id),
  FOREIGN KEY (company_id, category_account_id) REFERENCES accounts (company_id, id)
);
CREATE INDEX bill_lines_by_bill ON bill_lines (company_id, bill_id);

CREATE TABLE bill_payments (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  vendor_id bigint NOT NULL,
  payment_date date NOT NULL,
  amount numeric(19,4) NOT NULL CHECK (amount > 0),
  money_account_id bigint NOT NULL, -- must be type 'asset'; checked in pay_bills()
  method text NOT NULL CHECK (method IN ('check', 'electronic')),
  check_no bigint,                  -- set at creation (manual) or by assign_check_numbers (to_print)
  check_status text CHECK (check_status IN ('to_print', 'manual', 'printed', 'void')),
  printed_on date,
  posted_entry_id bigint NOT NULL,
  void_entry_id bigint,
  voided_on date,                   -- point-in-time aging (0007 lesson, from the start)
  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, vendor_id) REFERENCES vendors (company_id, id),
  FOREIGN KEY (company_id, money_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),
  -- a check payment always carries a check_status; an electronic one never does
  CHECK ((method = 'check') = (check_status IS NOT NULL)),
  -- only a check payment can ever carry a check number
  CHECK (method = 'check' OR check_no IS NULL)
);
CREATE INDEX bill_payments_by_vendor ON bill_payments (company_id, vendor_id);
-- check numbers are a number line per (company, money account) — never
-- reused, including by a voided check (its check_no stays put, just
-- flagged check_status = 'void').
CREATE UNIQUE INDEX bill_payments_check_no_unique
  ON bill_payments (company_id, money_account_id, check_no) WHERE check_no IS NOT NULL;

CREATE TABLE bill_payment_applications (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  payment_id bigint NOT NULL,
  bill_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 bill_payments (company_id, id),
  FOREIGN KEY (company_id, bill_id) REFERENCES bills (company_id, id)
);
CREATE INDEX bill_payment_applications_by_payment ON bill_payment_applications (company_id, payment_id);
CREATE INDEX bill_payment_applications_by_bill ON bill_payment_applications (company_id, bill_id);

-- Row-level security: ENABLE + FORCE everywhere, keyed to current_company_id().
-- (company_settings already has RLS from 0005 — new columns inherit it.)
DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY[
    'vendors', 'check_counters', 'bills', 'bill_lines',
    'bill_payments', 'bill_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 AP table
-- (forbid_truncate() already exists, from db/migrations/0002_ledger_core.sql).
CREATE TRIGGER vendors_no_truncate
  BEFORE TRUNCATE ON vendors FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate();
CREATE TRIGGER check_counters_no_truncate
  BEFORE TRUNCATE ON check_counters FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate();
CREATE TRIGGER bills_no_truncate
  BEFORE TRUNCATE ON bills FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate();
CREATE TRIGGER bill_lines_no_truncate
  BEFORE TRUNCATE ON bill_lines FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate();
CREATE TRIGGER bill_payments_no_truncate
  BEFORE TRUNCATE ON bill_payments FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate();
CREATE TRIGGER bill_payment_applications_no_truncate
  BEFORE TRUNCATE ON bill_payment_applications FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate();

-- company_settings.ap_account_id must point at a liability account, whether
-- set by ensure_ap_account()'s auto-detect or edited directly later.
CREATE FUNCTION company_settings_ap_account_is_liability() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE v_type text;
BEGIN
  IF NEW.ap_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.ap_account_id;
  IF v_type IS NULL THEN
    RAISE EXCEPTION 'account % not found', NEW.ap_account_id;
  END IF;
  IF v_type <> 'liability' THEN
    RAISE EXCEPTION
      'accounts payable account must be a liability account (account % is %)',
      NEW.ap_account_id, v_type;
  END IF;
  RETURN NEW;
END $$;

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

-- bill_lines.category_account_id must NOT be the company's own A/P account —
-- checks BOTH the resolved company_settings.ap_account_id (the common case)
-- AND, when that hasn't been resolved yet (a brand-new company that has
-- never issued a bill), any account literally named 'Accounts Payable' —
-- otherwise this guard could be bypassed simply by posting a bill line
-- before ensure_ap_account() has ever run once.
CREATE FUNCTION bill_lines_category_not_ap() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_ap_account bigint;
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM accounts WHERE company_id = NEW.company_id AND id = NEW.category_account_id
  ) THEN
    RAISE EXCEPTION 'account % not found', NEW.category_account_id;
  END IF;

  SELECT ap_account_id INTO v_ap_account FROM company_settings
   WHERE company_id = NEW.company_id;
  IF v_ap_account IS NULL THEN
    SELECT id INTO v_ap_account FROM accounts
     WHERE company_id = NEW.company_id AND type = 'liability' AND active
       AND name = 'Accounts Payable';
  END IF;

  IF v_ap_account IS NOT NULL AND NEW.category_account_id = v_ap_account THEN
    RAISE EXCEPTION
      'bill lines cannot post to the Accounts Payable account itself (account %)',
      NEW.category_account_id;
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER bill_lines_category_not_ap
  BEFORE INSERT OR UPDATE ON bill_lines
  FOR EACH ROW EXECUTE FUNCTION bill_lines_category_not_ap();

-- Bills are born drafts; issue_bill()/void_bill() are the only paths to
-- issued_on, a posting, or a void (mirrors invoices_born_draft in 0005).
CREATE FUNCTION bills_born_draft() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.status <> 'draft' OR NEW.issued_on IS NOT NULL
     OR NEW.posted_entry_id IS NOT NULL OR NEW.void_entry_id IS NOT NULL THEN
    RAISE EXCEPTION 'bills are created as drafts; use issue_bill() to issue';
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER bills_born_draft
  BEFORE INSERT ON bills
  FOR EACH ROW EXECUTE FUNCTION bills_born_draft();

-- Issued-bill header immutability: once out of draft, only the exact
-- open -> void transition (status + void_entry_id + voided_on, nothing
-- else) is allowed — that's void_bill()'s own UPDATE.
CREATE FUNCTION bills_immutable_when_issued() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    IF OLD.status <> 'draft' THEN
      RAISE EXCEPTION 'issued bill % 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.vendor_id       IS NOT DISTINCT FROM OLD.vendor_id
     AND NEW.ref_no          IS NOT DISTINCT FROM OLD.ref_no
     AND NEW.bill_date       IS NOT DISTINCT FROM OLD.bill_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.issued_on       IS NOT DISTINCT FROM OLD.issued_on
     AND NEW.posted_entry_id IS NOT DISTINCT FROM OLD.posted_entry_id
  THEN
    RETURN NEW;
  END IF;

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

CREATE TRIGGER bills_immutable_when_issued
  BEFORE UPDATE OR DELETE ON bills
  FOR EACH ROW EXECUTE FUNCTION bills_immutable_when_issued();

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

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

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

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

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

-- Payments are born fully-formed and posted (pay_bills() creates the payment
-- row and its entry atomically — there is no payment "draft" state). Two
-- further legal transitions, and nothing else:
--   (1) void_bill_payment()'s active -> void (+ check_status -> 'void' when
--       the payment method is 'check' — the number is consumed, never reused)
--   (2) assign_check_numbers()'s to_print -> printed (+ check_no/printed_on
--       assigned) for a still-active check payment.
CREATE FUNCTION bill_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.vendor_id         IS NOT DISTINCT FROM OLD.vendor_id
     AND NEW.payment_date      IS NOT DISTINCT FROM OLD.payment_date
     AND NEW.amount            IS NOT DISTINCT FROM OLD.amount
     AND NEW.money_account_id  IS NOT DISTINCT FROM OLD.money_account_id
     AND NEW.method            IS NOT DISTINCT FROM OLD.method
     AND NEW.check_no          IS NOT DISTINCT FROM OLD.check_no
     AND NEW.printed_on        IS NOT DISTINCT FROM OLD.printed_on
     AND NEW.posted_entry_id   IS NOT DISTINCT FROM OLD.posted_entry_id
     AND NEW.memo              IS NOT DISTINCT FROM OLD.memo
     AND (NEW.check_status IS NOT DISTINCT FROM OLD.check_status
          OR (OLD.method = 'check' AND NEW.check_status = 'void'))
  THEN
    RETURN NEW;
  END IF;

  IF OLD.status = 'active' AND NEW.status = 'active'
     AND OLD.method = 'check' AND NEW.method = 'check'
     AND OLD.check_status = 'to_print' AND NEW.check_status = 'printed'
     AND OLD.check_no IS NULL AND NEW.check_no IS NOT NULL
     AND NEW.printed_on IS NOT NULL
     AND NEW.vendor_id         IS NOT DISTINCT FROM OLD.vendor_id
     AND NEW.payment_date      IS NOT DISTINCT FROM OLD.payment_date
     AND NEW.amount            IS NOT DISTINCT FROM OLD.amount
     AND NEW.money_account_id  IS NOT DISTINCT FROM OLD.money_account_id
     AND NEW.posted_entry_id   IS NOT DISTINCT FROM OLD.posted_entry_id
     AND NEW.void_entry_id     IS NOT DISTINCT FROM OLD.void_entry_id
     AND NEW.voided_on         IS NOT DISTINCT FROM OLD.voided_on
     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 bill_payments_immutable
  BEFORE UPDATE OR DELETE ON bill_payments
  FOR EACH ROW EXECUTE FUNCTION bill_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 bill_payment_applications_immutable() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'payment applications are immutable; void the payment instead';
END $$;

CREATE TRIGGER bill_payment_applications_immutable
  BEFORE UPDATE OR DELETE ON bill_payment_applications
  FOR EACH ROW EXECUTE FUNCTION bill_payment_applications_immutable();

-- Deferred commit-time checks (fire once per queued row, after every
-- statement in the transaction has run — mirrors check_payment_application
-- in 0005/0006):
--  (a) a payment is never applied for more than its own amount
--  (b) a bill is never applied for more than its own line total (a voided
--      payment's applications no longer count)
--  (c) an application's payment and bill belong to the same vendor
CREATE FUNCTION check_bill_payment_application() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_pay_amount numeric(19,4);
  v_applied numeric(19,4);
  v_bill_total numeric(19,4);
  v_bill_applied numeric(19,4);
  v_pay_vendor bigint;
  v_bill_vendor bigint;
BEGIN
  SELECT amount INTO v_pay_amount FROM bill_payments
   WHERE company_id = NEW.company_id AND id = NEW.payment_id;
  SELECT coalesce(sum(amount), 0) INTO v_applied FROM bill_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_bill_total FROM bill_lines
   WHERE company_id = NEW.company_id AND bill_id = NEW.bill_id;
  SELECT coalesce(sum(bpa.amount), 0) INTO v_bill_applied FROM bill_payment_applications bpa
    JOIN bill_payments p ON p.company_id = bpa.company_id AND p.id = bpa.payment_id
   WHERE bpa.company_id = NEW.company_id AND bpa.bill_id = NEW.bill_id
     AND p.status = 'active';
  IF v_bill_applied > v_bill_total THEN
    RAISE EXCEPTION 'bill % is over-applied: % applied against a % bill',
      NEW.bill_id, v_bill_applied, v_bill_total;
  END IF;

  SELECT vendor_id INTO v_pay_vendor FROM bill_payments
   WHERE company_id = NEW.company_id AND id = NEW.payment_id;
  SELECT vendor_id INTO v_bill_vendor FROM bills
   WHERE company_id = NEW.company_id AND id = NEW.bill_id;
  IF v_pay_vendor IS DISTINCT FROM v_bill_vendor THEN
    RAISE EXCEPTION 'payment % and bill % belong to different vendors',
      NEW.payment_id, NEW.bill_id;
  END IF;

  RETURN NULL;
END $$;

-- bill_payment_applications_immutable already forbids UPDATE/DELETE
-- unconditionally, so only INSERT can ever reach here in practice.
CREATE CONSTRAINT TRIGGER bill_payment_application_checks
  AFTER INSERT ON bill_payment_applications
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION check_bill_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 AR's
-- (already-fixed) hardening exactly: check_counters and bill_payments get
-- SELECT-plus-(UPDATE/DELETE-so-the-trigger-speaks) but no app INSERT —
-- every write to them happens inside a SECURITY DEFINER function.
GRANT UPDATE (ap_account_id, check_format, check_offset_x, check_offset_y)
  ON company_settings TO app;

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

GRANT SELECT ON check_counters TO app;

GRANT SELECT, INSERT, DELETE ON bills TO app;
GRANT UPDATE (vendor_id, ref_no, bill_date, due_date, memo) ON bills TO app;

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

-- no INSERT: every payment is created atomically alongside its posting
-- entry by pay_bills(). UPDATE/DELETE are granted so the immutability
-- trigger (not a bare permission error) is what an app attempt hits —
-- baking in AR's 0006 FIX 3 from the start.
GRANT SELECT, UPDATE, DELETE ON bill_payments TO app;
GRANT SELECT, UPDATE, DELETE ON bill_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). Bakes in AR's 0006 FIX 2 from
-- the start: exactly one ACTIVE 'Accounts Payable' liability account, loud
-- English otherwise.
CREATE FUNCTION ensure_ap_account(p_company_id uuid) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_account_id bigint;
  v_count int;
BEGIN
  SELECT ap_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 count(*), min(id) INTO v_count, v_account_id FROM accounts
     WHERE company_id = p_company_id AND type = 'liability' AND active
       AND name = 'Accounts Payable';
    IF v_count = 0 THEN
      RAISE EXCEPTION
        'No account named ''Accounts Payable'' exists — create one or set it in Settings';
    ELSIF v_count > 1 THEN
      RAISE EXCEPTION
        'More than one active account is named ''Accounts Payable'' — set the one to use in Settings';
    END IF;
    UPDATE company_settings SET ap_account_id = v_account_id
     WHERE company_id = p_company_id;
  END IF;

  RETURN v_account_id;
END $$;

REVOKE EXECUTE ON FUNCTION ensure_ap_account(uuid) FROM PUBLIC;

-- Derived-only balances (CLAUDE.md §3.4): never stored, always computed from
-- bill_lines / bill_payment_applications at query time. A voided payment's
-- applications are excluded. Current-state versions (used by triggers and
-- live UI) plus _asof versions (used only by ap_aging — 0007 lesson, from
-- the start rather than as a later fix).
CREATE FUNCTION bill_open_balance(p_company_id uuid, p_bill_id bigint)
RETURNS numeric(19,4)
LANGUAGE sql STABLE AS $$
  SELECT (
    coalesce((SELECT sum(amount) FROM bill_lines
                WHERE company_id = p_company_id AND bill_id = p_bill_id), 0)
    - coalesce((SELECT sum(bpa.amount) FROM bill_payment_applications bpa
                  JOIN bill_payments p ON p.company_id = bpa.company_id AND p.id = bpa.payment_id
                 WHERE bpa.company_id = p_company_id AND bpa.bill_id = p_bill_id
                   AND p.status = 'active'), 0)
  )::numeric(19,4)
$$;

CREATE FUNCTION bill_payment_unapplied_balance(p_company_id uuid, p_payment_id bigint)
RETURNS numeric(19,4)
LANGUAGE sql STABLE AS $$
  SELECT CASE WHEN p.status = 'active' THEN
    (p.amount - coalesce((SELECT sum(amount) FROM bill_payment_applications
                            WHERE company_id = p_company_id AND payment_id = p_payment_id), 0))
  ELSE 0 END::numeric(19,4)
    FROM bill_payments p WHERE p.company_id = p_company_id AND p.id = p_payment_id
$$;

CREATE FUNCTION bill_open_balance_asof(p_company_id uuid, p_bill_id bigint, p_as_of date)
RETURNS numeric(19,4)
LANGUAGE sql STABLE AS $$
  SELECT (
    coalesce((SELECT sum(amount) FROM bill_lines
                WHERE company_id = p_company_id AND bill_id = p_bill_id), 0)
    - coalesce((SELECT sum(bpa.amount) FROM bill_payment_applications bpa
                  JOIN bill_payments p ON p.company_id = bpa.company_id AND p.id = bpa.payment_id
                 WHERE bpa.company_id = p_company_id AND bpa.bill_id = p_bill_id
                   AND p.payment_date <= p_as_of
                   AND (p.voided_on IS NULL OR p.voided_on > p_as_of)), 0)
  )::numeric(19,4)
$$;

CREATE FUNCTION bill_payment_unapplied_balance_asof(p_company_id uuid, p_payment_id bigint, p_as_of date)
RETURNS numeric(19,4)
LANGUAGE sql STABLE AS $$
  SELECT CASE
    WHEN p.payment_date > p_as_of THEN 0
    WHEN p.voided_on IS NOT NULL AND p.voided_on <= p_as_of THEN 0
    ELSE (p.amount - coalesce((SELECT sum(amount) FROM bill_payment_applications
                                 WHERE company_id = p_company_id AND payment_id = p_payment_id), 0))
  END::numeric(19,4)
    FROM bill_payments p WHERE p.company_id = p_company_id AND p.id = p_payment_id
$$;

-- Issue: unlike issue_invoice(), assigns NO number (bills carry the
-- vendor's own ref_no instead) — just stamps issued_on and, unless the bill
-- is document_only, posts DEBIT each line's category account / CREDIT the
-- A/P account (grouped by account into one line per account). Bakes in
-- AR's 0008 DECISION 1 from the start: document_only never reaches
-- post_entry() below, so its own period-lock check has to run here too,
-- using the SAME RAISE text post_entry uses so errors.ts's mapping (built
-- once, in Task 2) fires identically for both kinds of bill.
CREATE FUNCTION issue_bill(p_company_id uuid, p_bill_id bigint, p_user_id uuid)
RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_bill bills%ROWTYPE;
  v_line_count int;
  v_total numeric(19,4);
  v_ap_account bigint;
  v_entry_id bigint;
  v_lock date;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match bill company';
  END IF;

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

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

  IF v_bill.document_only THEN
    v_lock := effective_locked_through(p_company_id);
    IF v_lock IS NOT NULL AND v_bill.bill_date <= v_lock THEN
      RAISE EXCEPTION 'cannot post into locked period (locked through %)', v_lock;
    END IF;

    UPDATE bills SET status = 'open', issued_on = CURRENT_DATE
     WHERE company_id = p_company_id AND id = p_bill_id;
  ELSE
    v_ap_account := ensure_ap_account(p_company_id);

    INSERT INTO journal_entries (company_id, entry_date, memo, created_by)
    VALUES (p_company_id, v_bill.bill_date,
            coalesce('Bill ' || v_bill.ref_no, 'Bill'), p_user_id)
    RETURNING id INTO v_entry_id;

    INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit)
    SELECT p_company_id, v_entry_id, category_account_id, sum(amount), 0
      FROM bill_lines
     WHERE company_id = p_company_id AND bill_id = p_bill_id
     GROUP BY category_account_id;

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

    PERFORM post_entry(p_company_id, v_entry_id);

    UPDATE bills SET status = 'open', issued_on = CURRENT_DATE, posted_entry_id = v_entry_id
     WHERE company_id = p_company_id AND id = p_bill_id;
  END IF;
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 status + voided_on — 0008 DECISION 1's two guards
-- baked in here from the start, same RAISE texts as their ledger-side
-- counterparts).
CREATE FUNCTION void_bill(
  p_company_id uuid, p_bill_id bigint, p_void_date date, p_user_id uuid
) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_bill bills%ROWTYPE;
  v_app_count int;
  v_rev_id bigint;
  v_lock date;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match bill company';
  END IF;

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

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

  IF v_bill.document_only THEN
    v_lock := effective_locked_through(p_company_id);
    IF v_lock IS NOT NULL AND p_void_date <= v_lock THEN
      RAISE EXCEPTION 'cannot post into locked period (locked through %)', v_lock;
    END IF;
    IF p_void_date < v_bill.bill_date THEN
      RAISE EXCEPTION 'reversal date cannot precede the entry it reverses';
    END IF;

    UPDATE bills SET status = 'void', voided_on = p_void_date
     WHERE company_id = p_company_id AND id = p_bill_id;
  ELSE
    v_rev_id := reverse_entry(p_company_id, v_bill.posted_entry_id, p_void_date, p_user_id);
    UPDATE bills SET status = 'void', void_entry_id = v_rev_id, voided_on = p_void_date
     WHERE company_id = p_company_id AND id = p_bill_id;
  END IF;
END $$;

-- Pay bills: posts DEBIT A/P / CREDIT money account, creates the payment row
-- and any applications atomically. Applications may be empty (a pure,
-- fully-unapplied vendor credit). p_spec: {vendor_id, payment_date, amount,
-- money_account_id, method, check_no (optional), memo,
-- applications: [{bill_id, amount}]}. Bakes in AR's 0006 FIX 1 from the
-- start: every targeted bill is locked (FOR UPDATE) before any application
-- row is inserted, so a concurrent pay_bills()/apply_vendor_credit() on the
-- same bill serializes behind this transaction instead of racing the
-- deferred over-application check.
CREATE FUNCTION pay_bills(p_company_id uuid, p_spec jsonb, p_user_id uuid)
RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_vendor_id bigint;
  v_date date;
  v_amount numeric(19,4);
  v_money_account bigint;
  v_money_type text;
  v_method text;
  v_check_no bigint;
  v_check_status text;
  v_memo text;
  v_apps jsonb;
  v_app jsonb;
  v_ap_account bigint;
  v_entry_id bigint;
  v_payment_id bigint;
  v_bill_id bigint;
  v_app_amount numeric(19,4);
  v_bill_vendor bigint;
  v_bill_status text;
  v_bill_ids bigint[];
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  v_vendor_id := (p_spec->>'vendor_id')::bigint;
  v_date := (p_spec->>'payment_date')::date;
  v_amount := (p_spec->>'amount')::numeric(19,4);
  v_money_account := (p_spec->>'money_account_id')::bigint;
  v_method := p_spec->>'method';
  v_check_no := nullif(p_spec->>'check_no', '')::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 vendors WHERE company_id = p_company_id AND id = v_vendor_id
  ) THEN
    RAISE EXCEPTION 'vendor % not found', v_vendor_id;
  END IF;
  IF v_method NOT IN ('check', 'electronic') THEN
    RAISE EXCEPTION 'payment method must be ''check'' or ''electronic''';
  END IF;

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

  IF v_method = 'check' THEN
    v_check_status := CASE WHEN v_check_no IS NOT NULL THEN 'manual' ELSE 'to_print' END;
  ELSE
    IF v_check_no IS NOT NULL THEN
      RAISE EXCEPTION 'a check number can only be set for check payments';
    END IF;
    v_check_status := NULL;
  END IF;

  v_ap_account := ensure_ap_account(p_company_id);

  INSERT INTO journal_entries (company_id, entry_date, memo, created_by)
  VALUES (p_company_id, v_date, coalesce(v_memo, 'Vendor 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_ap_account, v_amount, 0),
         (p_company_id, v_entry_id, v_money_account, 0, v_amount);

  PERFORM post_entry(p_company_id, v_entry_id);

  INSERT INTO bill_payments
    (company_id, vendor_id, payment_date, amount, money_account_id, method,
     check_no, check_status, posted_entry_id, memo, created_by)
  VALUES
    (p_company_id, v_vendor_id, v_date, v_amount, v_money_account, v_method,
     v_check_no, v_check_status, v_entry_id, v_memo, p_user_id)
  RETURNING id INTO v_payment_id;

  SELECT array_agg(DISTINCT (elem->>'bill_id')::bigint) INTO v_bill_ids
    FROM jsonb_array_elements(v_apps) elem;
  IF v_bill_ids IS NOT NULL THEN
    PERFORM 1 FROM bills
     WHERE company_id = p_company_id AND id = ANY (v_bill_ids)
     FOR UPDATE;
  END IF;

  FOR v_app IN SELECT * FROM jsonb_array_elements(v_apps)
  LOOP
    v_bill_id := (v_app->>'bill_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 vendor_id, status INTO v_bill_vendor, v_bill_status FROM bills
     WHERE company_id = p_company_id AND id = v_bill_id;
    IF NOT FOUND THEN
      RAISE EXCEPTION 'bill % not found', v_bill_id;
    END IF;
    IF v_bill_status <> 'open' THEN
      RAISE EXCEPTION 'bill % is % — only open bills accept payment',
        v_bill_id, v_bill_status;
    END IF;
    IF v_bill_vendor <> v_vendor_id THEN
      RAISE EXCEPTION 'bill % does not belong to this vendor', v_bill_id;
    END IF;

    INSERT INTO bill_payment_applications (company_id, payment_id, bill_id, amount)
    VALUES (p_company_id, v_payment_id, v_bill_id, v_app_amount);
  END LOOP;

  RETURN v_payment_id;
END $$;

-- Apply an existing payment's unapplied remainder to more bills. No new
-- entry — the cash already went out when the payment was made. Same
-- invoice-locking discipline as pay_bills() above (0006 lesson).
CREATE FUNCTION apply_vendor_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 bill_payments%ROWTYPE;
  v_apps jsonb;
  v_app jsonb;
  v_bill_id bigint;
  v_amount numeric(19,4);
  v_bill_vendor bigint;
  v_bill_status text;
  v_bill_ids 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 bill_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;

  v_apps := coalesce(p_applications, '[]'::jsonb);

  SELECT array_agg(DISTINCT (elem->>'bill_id')::bigint) INTO v_bill_ids
    FROM jsonb_array_elements(v_apps) elem;
  IF v_bill_ids IS NOT NULL THEN
    PERFORM 1 FROM bills
     WHERE company_id = p_company_id AND id = ANY (v_bill_ids)
     FOR UPDATE;
  END IF;

  FOR v_app IN SELECT * FROM jsonb_array_elements(v_apps)
  LOOP
    v_bill_id := (v_app->>'bill_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 vendor_id, status INTO v_bill_vendor, v_bill_status FROM bills
     WHERE company_id = p_company_id AND id = v_bill_id;
    IF NOT FOUND THEN
      RAISE EXCEPTION 'bill % not found', v_bill_id;
    END IF;
    IF v_bill_status <> 'open' THEN
      RAISE EXCEPTION 'bill % is % — only open bills accept payment',
        v_bill_id, v_bill_status;
    END IF;
    IF v_bill_vendor <> v_payment.vendor_id THEN
      RAISE EXCEPTION 'bill % does not belong to this vendor', v_bill_id;
    END IF;

    INSERT INTO bill_payment_applications (company_id, payment_id, bill_id, amount)
    VALUES (p_company_id, p_payment_id, v_bill_id, v_amount);
  END LOOP;
END $$;

-- Void a payment: reverses its posting entry. Its applications are
-- untouched (immutable) but no longer count toward any bill's open balance.
-- If the payment was a check, check_status becomes 'void' too — the number
-- is consumed and never reused (reprint = a new payment with a new number).
CREATE FUNCTION void_bill_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 bill_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 bill_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);

  IF v_payment.method = 'check' THEN
    UPDATE bill_payments
       SET status = 'void', void_entry_id = v_rev_id, voided_on = p_void_date, check_status = 'void'
     WHERE company_id = p_company_id AND id = p_payment_id;
  ELSE
    UPDATE bill_payments
       SET status = 'void', void_entry_id = v_rev_id, voided_on = p_void_date
     WHERE company_id = p_company_id AND id = p_payment_id;
  END IF;
END $$;

-- Settable next check number, per (company, money account) — fast-forward
-- only, never rewind (mirrors 0008 DECISION 2's set_next_invoice_no exactly,
-- baked in here from the start rather than as a later migration).
CREATE FUNCTION set_next_check_no(
  p_company uuid, p_account bigint, p_next bigint, p_user uuid
) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_max bigint;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  SELECT coalesce(max(check_no), 0) INTO v_max FROM bill_payments
   WHERE company_id = p_company AND money_account_id = p_account;

  IF p_next <= v_max THEN
    RAISE EXCEPTION 'next check number must be greater than the highest used (%)', v_max;
  END IF;

  INSERT INTO check_counters (company_id, money_account_id, next_check_no)
    VALUES (p_company, p_account, p_next)
    ON CONFLICT (company_id, money_account_id) DO UPDATE SET next_check_no = excluded.next_check_no;

  INSERT INTO audit_log (company_id, user_id, action, entity, entity_id, detail)
  VALUES (p_company, p_user, 'set_next_check_no', 'check_counters', p_account::text,
          jsonb_build_object('next_check_no', p_next));
END $$;

-- Assign sequential check numbers to a print run: only payments that are
-- method='check', check_status='to_print', and on the requested money
-- account qualify; numbers are handed out in (payment_date, id) order from
-- the per-account counter, atomically. The counter row is locked FIRST
-- (before locking/validating any individual payment row) specifically so
-- that two concurrent calls for the SAME account always serialize on this
-- one lock, no matter which payments each call names — this is what makes
-- "two concurrent print runs on one account never produce duplicate check
-- numbers" true, not merely likely.
CREATE FUNCTION assign_check_numbers(
  p_company_id uuid, p_account_id bigint, p_payment_ids bigint[], p_user_id uuid
) RETURNS TABLE (payment_id bigint, check_no bigint)
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_next bigint;
  v_id bigint;
  v_method text;
  v_status text;
  v_account bigint;
  rec RECORD;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;
  IF p_payment_ids IS NULL OR array_length(p_payment_ids, 1) IS NULL THEN
    RAISE EXCEPTION 'no payments selected for check printing';
  END IF;

  INSERT INTO check_counters (company_id, money_account_id) VALUES (p_company_id, p_account_id)
    ON CONFLICT (company_id, money_account_id) DO NOTHING;
  SELECT next_check_no INTO v_next FROM check_counters
   WHERE company_id = p_company_id AND money_account_id = p_account_id
   FOR UPDATE;

  FOREACH v_id IN ARRAY p_payment_ids LOOP
    SELECT method, check_status, money_account_id INTO v_method, v_status, v_account
      FROM bill_payments
     WHERE company_id = p_company_id AND id = v_id
     FOR UPDATE;
    IF NOT FOUND THEN
      RAISE EXCEPTION 'payment % not found', v_id;
    END IF;
    IF v_method <> 'check' OR v_status <> 'to_print' OR v_account <> p_account_id THEN
      RAISE EXCEPTION 'payment % is not a check payment queued to print on this account', v_id;
    END IF;
  END LOOP;

  FOR rec IN
    SELECT id FROM bill_payments
     WHERE company_id = p_company_id AND id = ANY (p_payment_ids)
     ORDER BY payment_date, id
  LOOP
    UPDATE bill_payments
       SET check_no = v_next, check_status = 'printed', printed_on = CURRENT_DATE
     WHERE company_id = p_company_id AND id = rec.id;
    payment_id := rec.id;
    check_no := v_next;
    RETURN NEXT;
    v_next := v_next + 1;
  END LOOP;

  UPDATE check_counters SET next_check_no = v_next
   WHERE company_id = p_company_id AND money_account_id = p_account_id;

  RETURN;
END $$;

-- Aging: per-bill open balances bucketed by days past due_date, including
-- document_only bills, excluding void; plus a per-vendor row for each
-- unapplied payment credit. Point-in-time from the start (0007 lesson):
-- a bill counts as of p_as_of iff it was EVER issued (issued_on IS NOT
-- NULL — a draft never gets one, so this alone excludes drafts forever,
-- regardless of today's `status`) AND its bill_date had arrived by p_as_of
-- AND, if it's since been voided, that void hadn't happened yet as of
-- p_as_of. A payment counts on the same voided-on-relative-to-as_of basis,
-- AND only once its own payment_date has arrived.
CREATE FUNCTION ap_aging(p_company_id uuid, p_as_of date DEFAULT CURRENT_DATE)
RETURNS TABLE (
  row_type text,
  vendor_id bigint,
  vendor_name text,
  bill_id bigint,
  ref_no text,
  bill_date date,
  due_date date,
  document_only boolean,
  open_amount numeric(19,4),
  bucket text
)
LANGUAGE sql STABLE AS $$
  WITH bill_open AS (
    SELECT b.id AS bill_id, b.vendor_id, b.ref_no, b.bill_date,
           b.due_date, b.document_only,
           bill_open_balance_asof(p_company_id, b.id, p_as_of) AS open_amount
      FROM bills b
     WHERE b.company_id = p_company_id
       AND b.issued_on IS NOT NULL
       AND b.bill_date <= p_as_of
       AND (b.voided_on IS NULL OR b.voided_on > p_as_of)
  ),
  payment_credits AS (
    SELECT p.vendor_id,
           sum(bill_payment_unapplied_balance_asof(p_company_id, p.id, p_as_of)) AS credit_amount
      FROM bill_payments p
     WHERE p.company_id = p_company_id
       AND p.payment_date <= p_as_of
       AND (p.voided_on IS NULL OR p.voided_on > p_as_of)
     GROUP BY p.vendor_id
  )
  SELECT 'bill'::text, bo.vendor_id, v.name, bo.bill_id, bo.ref_no,
         bo.bill_date, bo.due_date, bo.document_only, bo.open_amount,
         CASE
           WHEN (p_as_of - bo.due_date) <= 0 THEN 'current'
           WHEN (p_as_of - bo.due_date) BETWEEN 1 AND 30 THEN '1-30'
           WHEN (p_as_of - bo.due_date) BETWEEN 31 AND 60 THEN '31-60'
           WHEN (p_as_of - bo.due_date) BETWEEN 61 AND 90 THEN '61-90'
           ELSE '90+'
         END
    FROM bill_open bo
    JOIN vendors v ON v.company_id = p_company_id AND v.id = bo.vendor_id
   WHERE bo.open_amount > 0

  UNION ALL

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