-- Ledger impact summary for a custom account (drives the delete/inactivate workflow).
CREATE OR REPLACE FUNCTION public.account_ledger_impact(_account_id uuid)
RETURNS jsonb
LANGUAGE plpgsql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  _acct record;
  _lines int := 0;
  _entries int := 0;
  _debit numeric := 0;
  _credit numeric := 0;
  _first date;
  _last date;
  _children int := 0;
BEGIN
  IF auth.uid() IS NULL THEN RAISE EXCEPTION 'Not authenticated'; END IF;
  PERFORM public.require_access('accounting');

  SELECT a.id, a.code, a.name, a.is_system, a.balance_locked, a.is_active,
         at.normal_balance
    INTO _acct
  FROM public.accounts a
  LEFT JOIN public.account_types at ON at.id = a.account_type_id
  WHERE a.id = _account_id;

  IF _acct.id IS NULL THEN RAISE EXCEPTION 'Account not found'; END IF;

  SELECT count(*), count(DISTINCT jl.journal_entry_id),
         coalesce(sum(jl.debit), 0), coalesce(sum(jl.credit), 0),
         min(je.entry_date), max(je.entry_date)
    INTO _lines, _entries, _debit, _credit, _first, _last
  FROM public.journal_lines jl
  JOIN public.journal_entries je ON je.id = jl.journal_entry_id
  WHERE jl.account_id = _account_id;

  SELECT count(*) INTO _children FROM public.accounts WHERE parent_id = _account_id;

  RETURN jsonb_build_object(
    'account_id', _acct.id,
    'code', _acct.code,
    'name', _acct.name,
    'is_system', _acct.is_system,
    'balance_locked', _acct.balance_locked,
    'is_active', _acct.is_active,
    'line_count', _lines,
    'entry_count', _entries,
    'total_debit', _debit,
    'total_credit', _credit,
    'balance', CASE WHEN coalesce(_acct.normal_balance, 'debit') = 'credit'
                    THEN _credit - _debit ELSE _debit - _credit END,
    'first_date', _first,
    'last_date', _last,
    'child_count', _children,
    'can_delete', (_lines = 0 AND _children = 0 AND NOT _acct.is_system AND NOT _acct.balance_locked)
  );
END;
$$;

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

-- Individual General Ledger lines behind an account balance up to a date.
CREATE OR REPLACE FUNCTION public.account_ledger_lines(_account_id uuid, _as_of date)
RETURNS TABLE (
  line_id uuid,
  journal_entry_id uuid,
  entry_number text,
  entry_date date,
  source_type text,
  status text,
  memo text,
  description text,
  debit numeric,
  credit numeric
)
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT jl.id, je.id, je.entry_number, je.entry_date, je.source_type, je.status,
         je.memo, jl.description, jl.debit, jl.credit
  FROM public.journal_lines jl
  JOIN public.journal_entries je ON je.id = jl.journal_entry_id
  WHERE jl.account_id = _account_id
    AND je.status <> 'draft'
    AND je.entry_date <= coalesce(_as_of, current_date)
    AND auth.uid() IS NOT NULL
    AND public.can_access('accounting')
  ORDER BY je.entry_date, je.entry_number, jl.created_at
  LIMIT 500;
$$;

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