CREATE OR REPLACE FUNCTION public.post_invoice(_invoice_id uuid)
RETURNS uuid
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE _inv public.invoices; _lines jsonb := '[]'::jsonb; _je uuid; _ar uuid; _vat uuid; _rev record; _fallback uuid;
BEGIN
  IF NOT public.can_edit('invoices') THEN RAISE EXCEPTION 'You do not have permission to post invoices'; END IF;
  SELECT * INTO _inv FROM public.invoices WHERE id = _invoice_id FOR UPDATE;
  IF _inv.id IS NULL THEN RAISE EXCEPTION 'Invoice not found'; END IF;
  IF _inv.status <> 'draft' THEN RAISE EXCEPTION 'Only draft invoices can be posted'; END IF;
  IF _inv.total <= 0 THEN RAISE EXCEPTION 'Invoice total must be greater than zero'; END IF;

  _ar := public.account_id_by_code('1200');
  _vat := public.account_id_by_code('2100');
  -- Detail (leaf) account for lines with no revenue account of their own.
  _fallback := COALESCE(public.account_id_by_code('4300'), public.account_id_by_code('4100'));

  _lines := _lines || jsonb_build_object('account_id', _ar, 'debit', _inv.total, 'credit', 0,
      'description', 'Invoice ' || _inv.invoice_number, 'customer_id', _inv.customer_id);

  FOR _rev IN
    SELECT COALESCE(i.revenue_account_id, _fallback) AS acc,
           SUM(i.line_total - i.tax_amount) AS net
    FROM public.invoice_items i WHERE i.invoice_id = _invoice_id
    GROUP BY COALESCE(i.revenue_account_id, _fallback)
  LOOP
    IF round(_rev.net,2) <> 0 THEN
      _lines := _lines || jsonb_build_object('account_id', _rev.acc, 'debit', 0, 'credit', round(_rev.net,2),
        'description', 'Revenue - ' || _inv.invoice_number, 'customer_id', _inv.customer_id);
    END IF;
  END LOOP;

  IF _inv.tax_total > 0 THEN
    _lines := _lines || jsonb_build_object('account_id', _vat, 'debit', 0, 'credit', _inv.tax_total,
      'description', 'VAT on ' || _inv.invoice_number, 'customer_id', _inv.customer_id);
  END IF;

  _je := public.post_journal_entry(_inv.invoice_date, 'Invoice ' || _inv.invoice_number, 'invoice', _invoice_id, _lines);

  UPDATE public.invoices SET status = 'posted', journal_entry_id = _je, posted_at = now(),
    balance = total - amount_paid WHERE id = _invoice_id;

  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'post', 'invoice', _invoice_id, 'Posted invoice ' || _inv.invoice_number);
  RETURN _je;
END;
$$;