acmcc/supabase/migrations/20260602140000_accounting_expense_classification.sql

-- A5 (expense classification) + A/P overpayment fix.
--
-- 1. post_transaction_gl counter priority was customer→AR, vendor→AP, coa→coa.
--    But direct vendor payments carry an expense category (coa_account_id); mapping
--    them to A/P drove A/P negative AND hid the real expense account. New priority:
--    customer→A/R (a receipt always settles A/R), then coa→that account (direct
--    categorized expense/income), then vendor with no category→A/P (bill settlement).
--
-- 2. Synced bills created a single bill_item with no account, so post_bill_gl lumped
--    every bill into the default "Administrative" expense. Map the public bill's
--    expense_account_id (public.chart_of_accounts) to the matching accounting expense
--    account by name and set it on the bill_item.

create or replace function accounting.post_transaction_gl(_txn_id uuid) returns void
language plpgsql security definer set search_path to 'public','accounting' as $$
declare t accounting.transactions%rowtype; _counter uuid; _je uuid; _amt numeric;
begin
  select * into t from accounting.transactions where id=_txn_id;
  if not found then return; end if;
  perform accounting._gl_clear(t.company_id, 'acmacc_txn', t.id::text);
  if not accounting.gl_managed(t.company_id) then return; end if;
  if t.transfer_id is not null or t.deposit_id is not null then return; end if;
  if t.account_id is null then return; end if;
  _amt := coalesce(t.amount,0);
  if _amt = 0 then return; end if;
  _counter := case
    when t.customer_id is not null then accounting.coa_ar(t.company_id)   -- receipt settles A/R
    when t.coa_account_id is not null then t.coa_account_id               -- direct categorized expense/income
    when t.vendor_id is not null then accounting.coa_ap(t.company_id)     -- uncategorized vendor payment = bill settlement
    else null end;
  if _counter is null then return; end if;

  insert into accounting.journal_entries (company_id, date, description, reference, external_source, external_id)
  values (t.company_id, t.date, coalesce(nullif(t.description,''), 'Bank transaction'), t.reference, 'acmacc_txn', t.id::text)
  returning id into _je;
  if t.type = 'credit' then -- money in
    insert into accounting.journal_entry_lines (journal_entry_id, account_id, debit, credit, description) values
      (_je, t.account_id, _amt, 0, t.description),
      (_je, _counter,     0, _amt, t.description);
  else -- money out
    insert into accounting.journal_entry_lines (journal_entry_id, account_id, debit, credit, description) values
      (_je, _counter,     _amt, 0, t.description),
      (_je, t.account_id, 0, _amt, t.description);
  end if;
end$$;

create or replace function accounting.sync_public_bill(_bill_id uuid)
returns void
language plpgsql security definer set search_path to 'public','accounting'
as $$
declare
  b public.bills%rowtype;
  _company_id uuid; _vendor_id uuid; _status accounting.bill_status;
  _paid numeric; _tot numeric; _acct_bill_id uuid; _exp_acct uuid; _exp_name text;
begin
  select * into b from public.bills where id=_bill_id;
  if not found then return; end if;
  select id into _company_id from accounting.companies where association_id=b.association_id;
  if _company_id is null then return; end if;

  if not accounting.bill_should_mirror(b.status) then
    delete from accounting.bills where company_id=_company_id and external_source='acmacc_bill' and external_id=b.id::text;
    return;
  end if;

  _vendor_id := accounting.ensure_vendor_for_public(_company_id, b.vendor_id);
  _paid := coalesce(b.amount_paid, 0);
  _tot  := coalesce(b.amount, 0);
  _status := (case when _paid >= _tot and _tot > 0 then 'paid'
                   when _paid > 0 then 'partially_paid'
                   else 'open' end)::accounting.bill_status;

  insert into accounting.bills
    (company_id, vendor_id, number, issue_date, due_date, status, subtotal, tax, total,
     notes, paid_amount, attachment_url, external_source, external_id)
  values
    (_company_id, _vendor_id,
     coalesce(nullif(b.invoice_number,''), 'BILL-' || left(replace(b.id::text,'-',''),8)),
     b.bill_date, b.due_date, _status, _tot, 0, _tot,
     b.description, _paid, b.attachment_url, 'acmacc_bill', b.id::text)
  on conflict (company_id, external_source, external_id) where external_id is not null
  do update set vendor_id=excluded.vendor_id, number=excluded.number, issue_date=excluded.issue_date,
     due_date=excluded.due_date, status=excluded.status, subtotal=excluded.subtotal,
     total=excluded.total, notes=excluded.notes, paid_amount=excluded.paid_amount,
     attachment_url=excluded.attachment_url, updated_at=now()
  returning id into _acct_bill_id;

  if _acct_bill_id is null then
    select id into _acct_bill_id from accounting.bills
    where company_id=_company_id and external_source='acmacc_bill' and external_id=b.id::text;
  end if;

  -- Map the bill's expense account (public COA name -> accounting expense account).
  if b.expense_account_id is not null then
    select account_name into _exp_name from public.chart_of_accounts where id=b.expense_account_id;
    if _exp_name is not null then
      _exp_acct := accounting.coa_get_or_create(_company_id, array[_exp_name], null, _exp_name, 'expense'::accounting.account_type);
    end if;
  end if;

  delete from accounting.bill_items where bill_id=_acct_bill_id;
  insert into accounting.bill_items (bill_id, description, quantity, rate, amount, account_id)
  values (_acct_bill_id, coalesce(nullif(b.description,''), 'Bill ' || coalesce(b.invoice_number,'')), 1, _tot, _tot, _exp_acct);

  -- Re-post the bill to the GL now that the line item's expense account is set.
  perform accounting.post_bill_gl(_acct_bill_id);
end;
$$;

-- Backfill managed companies
do $$
declare r record;
begin
  for r in select b.id from public.bills b
           join accounting.companies c on c.association_id=b.association_id
           where accounting.gl_managed(c.id) loop
    perform accounting.sync_public_bill(r.id);
  end loop;
  for r in select t.id from accounting.transactions t
           join accounting.companies c on c.id=t.company_id
           where accounting.gl_managed(c.id) and t.transfer_id is null and t.deposit_id is null loop
    perform accounting.post_transaction_gl(r.id);
  end loop;
end$$;