CREATE OR REPLACE FUNCTION public.account_balances_as_of(_as_of date)
RETURNS TABLE (
  account_id uuid,
  code text,
  name text,
  category text,
  normal_balance text,
  total_debit numeric,
  total_credit numeric,
  balance numeric
)
LANGUAGE sql
STABLE
SECURITY DEFINER
AS $$
  SELECT a.id,
         a.code,
         a.name,
         t.category,
         t.normal_balance,
         COALESCE(SUM(l.debit), 0) AS total_debit,
         COALESCE(SUM(l.credit), 0) AS total_credit,
         CASE WHEN t.normal_balance = 'debit'
              THEN COALESCE(SUM(l.debit), 0) - COALESCE(SUM(l.credit), 0)
              ELSE COALESCE(SUM(l.credit), 0) - COALESCE(SUM(l.debit), 0) END AS balance
    FROM public.accounts a
    JOIN public.account_types t ON t.id = a.account_type_id
    LEFT JOIN public.journal_lines l ON l.account_id = a.id
     AND EXISTS (
       SELECT 1 FROM public.journal_entries e
        WHERE e.id = l.journal_entry_id
          AND e.status <> 'draft'
          AND e.entry_date <= COALESCE(_as_of, CURRENT_DATE)
     )
   WHERE public.can_access('accounting')
   GROUP BY a.id, a.code, a.name, t.category, t.normal_balance;
$$;

REVOKE ALL ON FUNCTION public.account_balances_as_of(date) FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.account_balances_as_of(date) TO authenticated, service_role;