acmcc/supabase/migrations/20260601140000_accounting_sync_bills.sql

-- Bidirectional bill sync between the main app and the Accounting platform.
--   Forward: an approved/paid public.bills row (created by the bill-approvals
--            flow) is mirrored into accounting.bills (Payables), creating the
--            accounting vendor + a single line item.
--   Back:    when an accounting.bills row is marked paid (paid_amount >= total),
--            the linked public.bills is set status='paid' (+ paid_date,
--            amount_paid) and its bill_approvals are marked paid.
-- Scoped to associations with an accounting.companies row. Keyed by
-- external_source='acmacc_bill' / 'acmacc_vendor', external_id = public row id.

-- ---------------------------------------------------------------------------
-- Find-or-create the accounting vendor mirroring a public vendor.
-- Adopts an existing same-name accounting vendor before creating a new one.
-- ---------------------------------------------------------------------------
create or replace function accounting.ensure_vendor_for_public(_company_id uuid, _public_vendor_id uuid)
returns uuid
language plpgsql security definer set search_path to 'public','accounting'
as $$
declare _vid uuid; _name text; _email text; _phone text; _addr text;
begin
  if _public_vendor_id is null then return null; end if;

  select id into _vid from accounting.vendors
  where company_id=_company_id and external_source='acmacc_vendor' and external_id=_public_vendor_id::text;
  if _vid is not null then return _vid; end if;

  select name, email, phone, address into _name, _email, _phone, _addr
  from public.vendors where id=_public_vendor_id;
  if _name is null then _name := 'Vendor'; end if;

  -- adopt an existing same-name vendor (avoid duplicates), else create
  select id into _vid from accounting.vendors
  where company_id=_company_id and lower(trim(name))=lower(trim(_name)) limit 1;
  if _vid is not null then
    update accounting.vendors
      set external_source='acmacc_vendor', external_id=_public_vendor_id::text,
          email=coalesce(email,_email), phone=coalesce(phone,_phone), address=coalesce(address,_addr),
          updated_at=now()
      where id=_vid;
    return _vid;
  end if;

  insert into accounting.vendors (company_id, name, email, phone, address, external_source, external_id)
  values (_company_id, _name, _email, _phone, _addr, 'acmacc_vendor', _public_vendor_id::text)
  returning id into _vid;
  return _vid;
end;
$$;

-- Should a public bill of this status appear in accounting Payables?
create or replace function accounting.bill_should_mirror(_status text)
returns boolean language sql immutable as $$
  select coalesce(lower(_status), '') not in
    ('draft','pending','pending_approval','rejected','void','voided','cancelled','denied');
$$;

-- ---------------------------------------------------------------------------
-- Forward: mirror one public.bills row into accounting.bills (idempotent).
-- ---------------------------------------------------------------------------
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;
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;

  -- Single line item mirroring the bill amount (refresh on each sync).
  delete from accounting.bill_items where bill_id=_acct_bill_id;
  insert into accounting.bill_items (bill_id, description, quantity, rate, amount)
  values (_acct_bill_id, coalesce(nullif(b.description,''), 'Bill ' || coalesce(b.invoice_number,'')), 1, _tot, _tot);
end;
$$;

-- ---------------------------------------------------------------------------
-- Back: when an accounting bill is fully paid, reflect it on the public bill.
-- ---------------------------------------------------------------------------
create or replace function accounting.sync_accounting_bill_paid(_acct_bill_id uuid)
returns void
language plpgsql security definer set search_path to 'public','accounting'
as $$
declare ab accounting.bills%rowtype; _public_id uuid;
begin
  select * into ab from accounting.bills where id=_acct_bill_id;
  if not found then return; end if;
  if ab.external_source <> 'acmacc_bill' or ab.external_id is null then return; end if;
  if not (coalesce(ab.paid_amount,0) >= coalesce(ab.total,0) and coalesce(ab.total,0) > 0) then return; end if;

  _public_id := ab.external_id::uuid;
  update public.bills
    set status='paid', amount_paid=ab.total,
        paid_date=coalesce(paid_date, current_date), updated_at=now()
    where id=_public_id and status is distinct from 'paid';

  update public.bill_approvals
    set status='paid', updated_at=now()
    where bill_id=_public_id and status is distinct from 'paid';
end;
$$;

-- ---------------------------------------------------------------------------
-- Triggers (error-swallowed).
-- ---------------------------------------------------------------------------
create or replace function accounting.tg_public_bill_sync()
returns trigger
language plpgsql security definer set search_path to 'public','accounting'
as $$
declare _company_id uuid;
begin
  begin
    if tg_op='DELETE' then
      select id into _company_id from accounting.companies where association_id=old.association_id;
      if _company_id is not null then
        delete from accounting.bills where company_id=_company_id and external_source='acmacc_bill' and external_id=old.id::text;
      end if;
      return old;
    end if;
    perform accounting.sync_public_bill(new.id);
    return new;
  exception when others then
    raise warning 'accounting: public bill sync failed for %: %', coalesce(new.id, old.id), sqlerrm;
    return coalesce(new, old);
  end;
end;
$$;

create or replace function accounting.tg_accounting_bill_paid_sync()
returns trigger
language plpgsql security definer set search_path to 'public','accounting'
as $$
begin
  begin
    -- only act when the paid position actually changed, to avoid loops
    if old.paid_amount is distinct from new.paid_amount or old.total is distinct from new.total then
      perform accounting.sync_accounting_bill_paid(new.id);
    end if;
  exception when others then
    raise warning 'accounting: accounting bill paid sync failed for %: %', new.id, sqlerrm;
  end;
  return new;
end;
$$;

drop trigger if exists trg_acct_sync_public_bill on public.bills;
create trigger trg_acct_sync_public_bill
  after insert or update or delete on public.bills
  for each row execute function accounting.tg_public_bill_sync();

drop trigger if exists trg_acct_bill_paid_back on accounting.bills;
create trigger trg_acct_bill_paid_back
  after update on accounting.bills
  for each row execute function accounting.tg_accounting_bill_paid_sync();