-- 0015: sales tax — rates, taxable invoices, liability tracking.
-- Spec: docs/superpowers/specs/2026-08-02-sales-tax-design.md. Forward-only;
-- every earlier migration is already applied everywhere (including the dev
-- DB, which has real data), so column additions use safe DEFAULTs and every
-- function change is CREATE OR REPLACE — no data rewrites.
--
-- Design in one paragraph: sales tax is a per-company toggle, OFF by
-- default. A tax_rates row is a single percent rate (e.g. "CA Sales Tax
-- 7.25%") optionally pointing at the vendor you remit to. An invoice may
-- carry one rate; its tax_amount is a DOCUMENT FACT (like line amounts):
-- computed server-side at integer-cents HALF-UP, stored on the document,
-- re-verified independently by issue_invoice() below (belt and braces — a
-- buggy client cannot post wrong tax), and immutable after issue. Issuing a
-- taxed invoice posts the three-way entry debit AR (grand total) / credit
-- income (net, per line account) / credit Sales Tax Payable (tax). The
-- payable ACCOUNT BALANCE still derives from journal_lines at query time —
-- CLAUDE.md §3.4 untouched; nothing here stores a ledger balance.

-- ---------------------------------------------------------------------------
-- tax_rates
-- ---------------------------------------------------------------------------

CREATE TABLE tax_rates (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  name text NOT NULL,
  -- percent, not fraction: 7.25 means 7.25%. numeric(7,4) holds any real
  -- US combined rate with headroom; CHECK forbids nonsense.
  rate numeric(7,4) NOT NULL CHECK (rate >= 0 AND rate <= 100),
  agency_vendor_id bigint,               -- who you remit to (optional)
  active boolean NOT NULL DEFAULT true,  -- no deletes, ever — deactivate
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (id),
  UNIQUE (company_id, id),               -- composite-FK target (§4)
  UNIQUE (company_id, name),
  -- §4: a rate can only ever point at this company's own vendor.
  FOREIGN KEY (company_id, agency_vendor_id) REFERENCES vendors (company_id, id)
);
CREATE INDEX tax_rates_by_company ON tax_rates (company_id);

ALTER TABLE tax_rates ENABLE ROW LEVEL SECURITY;
ALTER TABLE tax_rates FORCE ROW LEVEL SECURITY;
CREATE POLICY tax_rates_isolation ON tax_rates
  USING (company_id = current_company_id());

CREATE TRIGGER tax_rates_no_truncate
  BEFORE TRUNCATE ON tax_rates FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate();

-- Grants mirror vendors/customers: baseline SELECT+INSERT, column-hardened
-- UPDATE (no DELETE — rates referenced by issued invoices are history).
GRANT SELECT, INSERT ON tax_rates TO app;
GRANT UPDATE (name, rate, agency_vendor_id, active) ON tax_rates TO app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app;

-- A rate's PERCENT freezes once any ISSUED invoice references it: issued
-- invoices store their tax_amount forever, and sales_tax_summary() shows the
-- rate's percent beside historically collected tax — silently editing the
-- percent would make that report row self-inconsistent. Name/agency/active
-- stay editable (they don't participate in any stored math), and a rate
-- referenced only by DRAFTS can still be corrected freely — drafts recompute
-- against the current percent at issue.
CREATE FUNCTION tax_rates_rate_frozen_when_used() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.rate IS DISTINCT FROM OLD.rate
     AND EXISTS (
       SELECT 1 FROM invoices
        WHERE company_id = OLD.company_id AND tax_rate_id = OLD.id
          AND status <> 'draft'
     )
  THEN
    RAISE EXCEPTION
      'tax rate ''%'' has been used on issued invoices — deactivate it and create a new rate instead of changing the percent',
      OLD.name;
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER tax_rates_rate_frozen_when_used
  BEFORE UPDATE ON tax_rates
  FOR EACH ROW EXECUTE FUNCTION tax_rates_rate_frozen_when_used();

-- ---------------------------------------------------------------------------
-- Column additions (pure DDL; DEFAULTs backfill existing rows safely)
-- ---------------------------------------------------------------------------

ALTER TABLE company_settings ADD COLUMN sales_tax_enabled boolean NOT NULL DEFAULT false;
ALTER TABLE company_settings ADD COLUMN sales_tax_account_id bigint;
ALTER TABLE company_settings
  ADD CONSTRAINT company_settings_sales_tax_account_fk
  FOREIGN KEY (company_id, sales_tax_account_id) REFERENCES accounts (company_id, id);

ALTER TABLE customers ADD COLUMN tax_exempt boolean NOT NULL DEFAULT false;
ALTER TABLE customers ADD COLUMN exemption_note text;  -- certificate number etc.

ALTER TABLE invoices ADD COLUMN tax_rate_id bigint;
ALTER TABLE invoices ADD COLUMN tax_amount numeric(19,4) NOT NULL DEFAULT 0
  CHECK (tax_amount >= 0);
ALTER TABLE invoices
  ADD CONSTRAINT invoices_tax_rate_fk
  FOREIGN KEY (company_id, tax_rate_id) REFERENCES tax_rates (company_id, id);
-- Consistency at rest, enforced by the table itself (not just by
-- issue_invoice): an invoice can never carry a tax amount without a rate.
ALTER TABLE invoices
  ADD CONSTRAINT invoices_tax_amount_requires_rate
  CHECK (tax_rate_id IS NOT NULL OR tax_amount = 0);

ALTER TABLE invoice_lines ADD COLUMN taxable boolean NOT NULL DEFAULT false;

-- Column-grant extensions (column-level grants are additive; the 0005/0009
-- lists stay in force). invoices/customers INSERT are table-wide from 0005,
-- so the new columns are already insertable on drafts; UPDATE needs the
-- explicit extension. sales_tax_enabled is deliberately NOT app-updatable:
-- enable_sales_tax()/disable_sales_tax() below are the only paths, so the
-- draft-invoice guard on disable can never be bypassed by a bare UPDATE.
GRANT UPDATE (tax_rate_id, tax_amount) ON invoices TO app;
GRANT UPDATE (tax_exempt, exemption_note) ON customers TO app;
GRANT UPDATE (sales_tax_account_id) ON company_settings TO app;

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

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

-- ---------------------------------------------------------------------------
-- Issued-invoice immutability now covers the tax columns
-- ---------------------------------------------------------------------------

-- invoices_immutable_when_issued: 0005's version plus tax_rate_id/tax_amount
-- in the "must stay unchanged" list for the one legal open -> void
-- transition. (invoice_lines_immutable_when_issued needs NO change: it
-- already refuses EVERY insert/update/delete on a non-draft invoice's lines,
-- which freezes the new `taxable` column along with everything else.)
CREATE OR REPLACE 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
     AND NEW.tax_rate_id     IS NOT DISTINCT FROM OLD.tax_rate_id
     AND NEW.tax_amount      IS NOT DISTINCT FROM OLD.tax_amount
  THEN
    RETURN NEW;
  END IF;

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

-- ---------------------------------------------------------------------------
-- enable / disable
-- ---------------------------------------------------------------------------

-- Turn sales tax on. If no payable account is configured yet: adopt the one
-- active liability account named 'Sales Tax Payable' (exactly-one-active
-- semantics, mirroring ensure_ar_account/ensure_ap_account); if none exists,
-- CREATE it (code '2200' per the small-business template, or the next free
-- 22xx when that code is taken); if several exist, refuse loudly. Idempotent:
-- enabling an already-enabled company just re-verifies the account.
CREATE FUNCTION enable_sales_tax(p_company_id uuid, p_user_id uuid) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_account_id bigint;
  v_count int;
  v_code text;
  v_n int;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  -- Lock the settings row FIRST (0010's counter-row pattern): INSERT ... ON
  -- CONFLICT DO NOTHING, then SELECT ... FOR UPDATE — two concurrent enables
  -- on a company with no settings row yet serialize cleanly instead of the
  -- second one hitting a raw duplicate-key error.
  INSERT INTO company_settings (company_id) VALUES (p_company_id)
    ON CONFLICT (company_id) DO NOTHING;
  SELECT sales_tax_account_id INTO v_account_id FROM company_settings
   WHERE company_id = p_company_id FOR UPDATE;

  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 = 'Sales Tax Payable';
    IF v_count > 1 THEN
      RAISE EXCEPTION
        'More than one active account is named ''Sales Tax Payable'' — set the one to use in Settings';
    ELSIF v_count = 0 THEN
      -- Create it. Template code is 2200; if this chart already uses 2200
      -- (or the next few), walk forward to the first free 22xx code.
      v_n := 2200;
      v_code := v_n::text;
      WHILE EXISTS (
        SELECT 1 FROM accounts WHERE company_id = p_company_id AND code = v_code
      ) LOOP
        v_n := v_n + 1;
        IF v_n > 2299 THEN
          RAISE EXCEPTION
            'no free account code between 2200 and 2299 — create a ''Sales Tax Payable'' liability account yourself';
        END IF;
        v_code := v_n::text;
      END LOOP;
      INSERT INTO accounts (company_id, code, name, type)
      VALUES (p_company_id, v_code, 'Sales Tax Payable', 'liability')
      RETURNING id INTO v_account_id;
    END IF;
    UPDATE company_settings SET sales_tax_account_id = v_account_id
     WHERE company_id = p_company_id;
  END IF;

  UPDATE company_settings SET sales_tax_enabled = true
   WHERE company_id = p_company_id;

  INSERT INTO audit_log (company_id, user_id, action, entity, entity_id, detail)
  VALUES (p_company_id, p_user_id, 'enable_sales_tax', 'company_settings',
          p_company_id::text,
          jsonb_build_object('sales_tax_account_id', v_account_id));

  RETURN v_account_id;
END $$;

-- Turn sales tax off. History (issued invoices, rates, the payable account)
-- stays fully intact — disabling only hides the feature going forward. The
-- one refusal: a DRAFT invoice still carrying a tax rate would strand
-- invisible tax state in the UI, so it must be cleaned up first.
CREATE FUNCTION disable_sales_tax(p_company_id uuid, p_user_id uuid) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_draft_id bigint;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  -- Same lock-first ordering as enable_sales_tax above (0010's pattern).
  INSERT INTO company_settings (company_id) VALUES (p_company_id)
    ON CONFLICT (company_id) DO NOTHING;
  PERFORM 1 FROM company_settings WHERE company_id = p_company_id FOR UPDATE;

  SELECT id INTO v_draft_id FROM invoices
   WHERE company_id = p_company_id AND status = 'draft' AND tax_rate_id IS NOT NULL
   ORDER BY id LIMIT 1;
  IF v_draft_id IS NOT NULL THEN
    RAISE EXCEPTION
      'draft invoice % still has sales tax on it — remove its tax rate first',
      v_draft_id;
  END IF;

  UPDATE company_settings SET sales_tax_enabled = false
   WHERE company_id = p_company_id;

  INSERT INTO audit_log (company_id, user_id, action, entity, entity_id, detail)
  VALUES (p_company_id, p_user_id, 'disable_sales_tax', 'company_settings',
          p_company_id::text, NULL);
END $$;

-- ---------------------------------------------------------------------------
-- issue_invoice — 0008's version (the newest full text) plus tax handling
-- ---------------------------------------------------------------------------

-- Base is 0008's issue_invoice verbatim (document_only lock check, counter
-- ordering, grouped income credits — all unchanged; period-lock /
-- reconciled-through enforcement for posted entries lives in post_entry()
-- and is inherited untouched). Additions, per the spec:
--   - tax consistency: a tax amount without a rate refuses (also a table
--     CHECK now); with a rate, sales tax must be ENABLED for the company
--     and the rate must be ACTIVE at issue, and the
--     function RECOMPUTES tax = round(taxable_line_total * rate / 100, 2)
--     — Postgres numeric round() is half-away-from-zero, which for these
--     non-negative amounts is exactly the server's integer-cents HALF-UP —
--     and refuses on any mismatch, so a buggy client can't post wrong tax.
--   - posting: AR debit = line total + tax_amount; income credits per line
--     account unchanged; plus ONE credit of tax_amount to the company's
--     sales tax payable account (refuses if tax is present but no account
--     is configured). No tax line is written when tax_amount = 0 (§3.1
--     forbids zero-amount journal lines).
--   - document_only: validated and stored identically, posts nothing.
CREATE OR REPLACE 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;
  v_lock date;
  v_rate tax_rates%ROWTYPE;
  v_enabled boolean;
  v_taxable_total numeric(19,4);
  v_expected_tax numeric(19,4);
  v_tax_account 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;

  -- Tax validation (both document kinds — a document_only invoice's tax
  -- fields are document facts too and must be just as coherent).
  IF v_invoice.tax_rate_id IS NULL THEN
    IF v_invoice.tax_amount <> 0 THEN
      RAISE EXCEPTION 'invoice % has a tax amount but no tax rate', p_invoice_id;
    END IF;
  ELSE
    -- Sales tax must actually be ON for this company: without this check,
    -- a disabled company could still be handed a taxed invoice (rates and
    -- drafts are plain app-writable rows). void_invoice() is deliberately
    -- NOT gated the same way — voiding a previously taxed invoice reverses
    -- its stored entry and must keep working after a disable.
    SELECT sales_tax_enabled INTO v_enabled FROM company_settings
     WHERE company_id = p_company_id;
    IF v_enabled IS DISTINCT FROM true THEN
      RAISE EXCEPTION
        'sales tax is turned off for this company — turn it on in Settings before issuing a taxed invoice';
    END IF;

    SELECT * INTO v_rate FROM tax_rates
     WHERE company_id = p_company_id AND id = v_invoice.tax_rate_id;
    IF NOT FOUND THEN
      RAISE EXCEPTION 'tax rate % not found', v_invoice.tax_rate_id;
    END IF;
    IF NOT v_rate.active THEN
      RAISE EXCEPTION
        'tax rate ''%'' is inactive — reactivate it or pick another rate', v_rate.name;
    END IF;

    SELECT coalesce(sum(amount), 0) INTO v_taxable_total
      FROM invoice_lines
     WHERE company_id = p_company_id AND invoice_id = p_invoice_id AND taxable;
    -- Integer-cents HALF-UP: numeric round() is half-away-from-zero, which
    -- for non-negative amounts is HALF-UP — identical to the server's math.
    v_expected_tax := round(v_taxable_total * v_rate.rate / 100, 2);
    IF v_invoice.tax_amount <> v_expected_tax THEN
      RAISE EXCEPTION
        'invoice % tax amount % does not match the % rate on its taxable lines (should be %)',
        p_invoice_id, v_invoice.tax_amount, v_rate.rate, v_expected_tax;
    END IF;
  END IF;

  -- DECISION 1 (0008): document_only never reaches post_entry() below, so
  -- its own lock check has to run here instead — same text post_entry
  -- raises, so errors.ts maps it exactly the same way for both kinds.
  IF v_invoice.document_only THEN
    v_lock := effective_locked_through(p_company_id);
    IF v_lock IS NOT NULL AND v_invoice.invoice_date <= v_lock THEN
      RAISE EXCEPTION 'cannot post into locked period (locked through %)', v_lock;
    END IF;
  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);

    IF v_invoice.tax_amount > 0 THEN
      SELECT sales_tax_account_id INTO v_tax_account FROM company_settings
       WHERE company_id = p_company_id;
      IF v_tax_account IS NULL THEN
        RAISE EXCEPTION
          'no Sales Tax Payable account is configured — turn sales tax on in Settings first';
      END IF;
    END IF;

    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;

    -- AR debit is the GRAND total: lines plus tax.
    INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit)
    VALUES (p_company_id, v_entry_id, v_ar_account, v_total + v_invoice.tax_amount, 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;

    IF v_invoice.tax_amount > 0 THEN
      INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit)
      VALUES (p_company_id, v_entry_id, v_tax_account, 0, v_invoice.tax_amount);
    END IF;

    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 $$;

-- ---------------------------------------------------------------------------
-- Grand total = line total + tax_amount, everywhere a line total was the total
-- ---------------------------------------------------------------------------

-- invoice_open_balance (0005): + tax_amount. All existing documents carry
-- tax_amount = 0, so every pre-tax balance is bit-for-bit unchanged.
CREATE OR REPLACE 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 tax_amount FROM invoices
                 WHERE company_id = p_company_id AND 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)
$$;

-- invoice_open_balance_asof (0007): + tax_amount, same point-in-time
-- semantics otherwise. (tax_amount is frozen at issue, so it needs no as-of
-- gating of its own — it is the same number on every date the invoice
-- exists, exactly like the line amounts this already sums.)
CREATE OR REPLACE 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 tax_amount FROM invoices
                 WHERE company_id = p_company_id AND 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)
$$;
-- ar_aging (0007) needs no change: it derives every open amount through
-- invoice_open_balance_asof above, so its buckets are tax-inclusive now.

-- check_payment_application (0005): the "invoice is never applied for more
-- than its own total" guard summed invoice_lines alone — a fully-paid taxed
-- invoice would have spuriously counted as over-applied. Total is now lines
-- + tax_amount. Everything else is 0005's text verbatim.
CREATE OR REPLACE 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;
  v_inv_total := v_inv_total + coalesce((SELECT tax_amount FROM invoices
    WHERE company_id = NEW.company_id AND id = NEW.invoice_id), 0);
  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 $$;

-- ---------------------------------------------------------------------------
-- sales_tax_summary — the remittance report
-- ---------------------------------------------------------------------------

-- Per rate over the period: taxable sales, non-taxable sales (other lines on
-- those same taxed invoices), and tax collected. Invoice-date basis, over
-- ISSUED invoices that are not CURRENTLY void (a remittance report is about
-- what you owe the agency NOW, so current state — not the aging reports'
-- as-of reconstruction — is the correct lens; a voided invoice's tax is not
-- owed, whenever the void happened). document_only invoices are EXCLUDED:
-- they are migrated history whose tax was collected and remitted in the old
-- system — their tax fields are display facts, never a liability here.
-- Only invoices that carry a rate appear; a fully untaxed invoice belongs to
-- no rate's row.
CREATE FUNCTION sales_tax_summary(p_company_id uuid, p_from date, p_to date)
RETURNS TABLE (
  tax_rate_id bigint,
  rate_name text,
  rate numeric(7,4),
  agency_vendor_id bigint,
  agency_name text,
  taxable_sales numeric(19,4),
  non_taxable_sales numeric(19,4),
  tax_collected numeric(19,4)
)
LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  RETURN QUERY
  WITH taxed_invoices AS (
    SELECT i.id, i.tax_rate_id AS rate_id, i.tax_amount
      FROM invoices i
     WHERE i.company_id = p_company_id
       AND i.status = 'open'            -- issued, not currently void
       AND i.document_only = false     -- migrated history: tax handled in the old system
       AND i.tax_rate_id IS NOT NULL
       AND i.invoice_date BETWEEN p_from AND p_to
  ),
  line_sums AS (
    SELECT ti.rate_id,
           sum(CASE WHEN il.taxable THEN il.amount ELSE 0 END) AS taxable_sales,
           sum(CASE WHEN NOT il.taxable THEN il.amount ELSE 0 END) AS non_taxable_sales
      FROM taxed_invoices ti
      JOIN invoice_lines il
        ON il.company_id = p_company_id AND il.invoice_id = ti.id
     GROUP BY ti.rate_id
  ),
  tax_sums AS (
    SELECT ti.rate_id, sum(ti.tax_amount) AS tax_collected
      FROM taxed_invoices ti
     GROUP BY ti.rate_id
  )
  SELECT tr.id, tr.name, tr.rate, tr.agency_vendor_id, v.name,
         coalesce(ls.taxable_sales, 0)::numeric(19,4),
         coalesce(ls.non_taxable_sales, 0)::numeric(19,4),
         ts.tax_collected::numeric(19,4)
    FROM tax_sums ts
    JOIN tax_rates tr ON tr.company_id = p_company_id AND tr.id = ts.rate_id
    LEFT JOIN line_sums ls ON ls.rate_id = ts.rate_id
    LEFT JOIN vendors v ON v.company_id = p_company_id AND v.id = tr.agency_vendor_id
   ORDER BY tr.name;
END $$;
