-- 0007: AR aging must be a true POINT-IN-TIME report (owner review fix,
-- forward-only — 0005/0006 are already applied to the dev DB, so every
-- change here is ALTER TABLE / CREATE OR REPLACE / a new function, never an
-- edit to 0005 or 0006).
--
-- THE BUG: ar_aging()/invoice_open_balance() only ever reflected CURRENT
-- state (today's `status` column), never history as it stood on the
-- requested as_of date. Two concrete symptoms the review caught:
--   (a) an invoice created/issued AFTER as_of still showed up in a report
--       dated before it existed.
--   (b) a payment received TODAY retroactively erased an invoice from a
--       report dated in the past, and voiding a payment TODAY retroactively
--       changed what a past-dated report showed.
-- QuickBooks' own "aging as of <date>" reconstructs history as it looked on
-- that date; this system must too (CLAUDE.md is silent on this specifically,
-- but §3's "every balance... derives from journal_lines/invoice_lines at
-- query time" spirit extends to "at query time, for the point in time asked
-- about" — not just "right now").
--
-- THE FIX: record WHEN a void happened (voided_on), so any as-of query can
-- ask "had this been voided BY that date, or not yet" instead of only ever
-- seeing today's void status. Then ar_aging() (and its two helpers) gate
-- inclusion on invoice_date/payment_date <= as_of and voided_on (IS NULL OR
-- > as_of), never on the `status` column, which only ever reflects now.
--
-- No backfill needed for voided_on: nothing has been voided in any real
-- database yet (0005/0006 are the only migrations so far to touch AR, and
-- this is a brand-new v1 system with no production data — see CLAUDE.md's
-- "nothing is in production" precedent for 0002's own clean-rewrite call).
--
-- Applications themselves carry no date of their own (payment_applications
-- has only created_at, which this deliberately does NOT use, since "when was
-- this application literally inserted" is not the same question as "does it
-- belong to history as of this date" for a report) — an application counts
-- once its PAYMENT counts, exactly per the owner's spec below. This is a
-- known, deliberate simplification: applying an existing credit to an
-- invoice via apply_credit() long after the payment was received still
-- reads, in a report dated between the payment and the apply, as if the
-- application had already happened (there is nowhere else to hang that
-- distinction without adding a date column to payment_applications, which
-- the review did not ask for).

ALTER TABLE invoices ADD COLUMN voided_on date;
ALTER TABLE payments ADD COLUMN voided_on date;

-- void_invoice: identical to 0005's version except it now stamps voided_on
-- with the void date alongside status/void_entry_id. voided_on is
-- deliberately NOT in invoices_immutable_when_issued's list of "must stay
-- unchanged" columns (same as void_entry_id already wasn't) — see 0005 — so
-- this UPDATE is still a legal open -> void transition under that trigger
-- with no trigger changes needed. voided_on is also not in the app role's
-- grantable column list for invoices (0005's `GRANT UPDATE (customer_id,
-- invoice_date, due_date, memo)`), so it can only ever be set here, inside
-- this SECURITY DEFINER function — never by a bare UPDATE from the app.
CREATE OR REPLACE 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', voided_on = p_void_date
     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, voided_on = p_void_date
     WHERE company_id = p_company_id AND id = p_invoice_id;
  END IF;
END $$;

-- void_payment: identical to 0006's version except it now stamps voided_on
-- too — same rationale/immutability-trigger note as void_invoice above
-- (payments_immutable's own "must stay unchanged" list never included
-- void_entry_id either, so voided_on joining it needs no trigger change).
CREATE OR REPLACE 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, voided_on = p_void_date
   WHERE company_id = p_company_id AND id = p_payment_id;
END $$;

-- Point-in-time invoice balance: invoice line total minus applications whose
-- PAYMENT counts as of p_as_of (payment_date <= p_as_of, and if that payment
-- has since been voided, not yet as of p_as_of). Note this deliberately does
-- NOT gate on the invoice's own invoice_date/voided_on — that's ar_aging()'s
-- job at the invoice-selection level below, so this helper stays usable on
-- its own for "what did THIS invoice's balance look like on date X" without
-- also silently hiding invoices that didn't exist yet.
--
-- Not REVOKEd from PUBLIC — same convention as invoice_open_balance() in
-- 0005, which also never revoked EXECUTE: it's a harmless read with no
-- side effects and no bypass of anything RLS/grants already protect.
CREATE FUNCTION invoice_open_balance_asof(p_company_id uuid, p_invoice_id bigint, p_as_of date)
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.payment_date <= p_as_of
                   AND (p.voided_on IS NULL OR p.voided_on > p_as_of)), 0)
  )::numeric(19,4)
$$;

-- Point-in-time payment credit: how much of this payment was still unapplied
-- as of p_as_of. A payment dated after p_as_of contributes nothing yet
-- (0 — it hadn't happened as of that date); one voided by (on or before)
-- p_as_of contributes nothing either (0 — its credit no longer existed as of
-- that date). Applications themselves have no date of their own (see the
-- file header) so, same as the current-state payment_unapplied_balance(),
-- ALL of a still-counted payment's applications are subtracted regardless of
-- when they were recorded.
CREATE FUNCTION 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 payment_applications
                                 WHERE company_id = p_company_id AND payment_id = p_payment_id), 0))
  END::numeric(19,4)
    FROM payments p WHERE p.company_id = p_company_id AND p.id = p_payment_id
$$;

-- ar_aging: same shape/signature as 0005's version (row_type/bucket columns,
-- default p_as_of = CURRENT_DATE) but now genuinely point-in-time:
--  - an invoice counts as of p_as_of iff it was EVER issued (invoice_no IS
--    NOT NULL — a draft never gets one, so this alone excludes drafts
--    forever, regardless of today's `status`) AND its invoice_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. This replaces the old `status = 'open'`
--    filter, which only ever reflected today.
--  - a payment counts as of p_as_of on the same voided-on-relative-to-as_of
--    basis, AND only once its own payment_date has arrived — a payment
--    dated in the future relative to the report can't retroactively reduce
--    a past balance or contribute a past credit.
-- Bucketing (current/1-30/31-60/61-90/90+, by days past due_date relative to
-- p_as_of) is unchanged from 0005.
--
-- Equivalence with the pre-0007 (current-state-only) behavior: for any
-- as_of that is today, on data where nothing is void and nothing is
-- future-dated, invoice_no IS NOT NULL <=> status IN ('open') for an invoice
-- that's never been voided, and voided_on IS NULL <=> status <> 'void' —
-- so this reproduces the old output exactly in that (the common) case. See
-- tests/server/ar-payments.test.ts's dedicated equivalence test.
CREATE OR REPLACE 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_asof(p_company_id, i.id, p_as_of) AS open_amount
      FROM invoices i
     WHERE i.company_id = p_company_id
       AND i.invoice_no IS NOT NULL
       AND i.invoice_date <= p_as_of
       AND (i.voided_on IS NULL OR i.voided_on > p_as_of)
  ),
  payment_credits AS (
    SELECT p.customer_id,
           sum(payment_unapplied_balance_asof(p_company_id, p.id, p_as_of)) AS credit_amount
      FROM 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.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
$$;
