CREATE OR REPLACE FUNCTION public.post_opening_balances(
  _entry_date date,
  _equity_account_id uuid,
  _lines jsonb,
  _memo text DEFAULT 'Opening balances'
)
RETURNS uuid
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
DECLARE
  _je_id uuid; _total numeric(18,2) := 0; _line jsonb; _i int := 0; _amt numeric(18,2);
  _closed boolean;
BEGIN
  PERFORM public.require_access('accounting');
  IF NOT public.is_admin() THEN
    RAISE EXCEPTION 'Only administrators can post opening balances';
  END IF;
  IF _equity_account_id IS NULL THEN
    RAISE EXCEPTION 'Choose the equity account the opening balances are funded from';
  END IF;
  IF _lines IS NULL OR jsonb_array_length(_lines) < 1 THEN
    RAISE EXCEPTION 'Enter at least one opening balance';
  END IF;

  SELECT true INTO _closed FROM public.fiscal_periods
   WHERE status = 'closed' AND _entry_date BETWEEN start_date AND end_date LIMIT 1;
  IF _closed THEN RAISE EXCEPTION 'The fiscal period for % is closed', _entry_date; END IF;

  FOR _line IN SELECT * FROM jsonb_array_elements(_lines) LOOP
    _amt := round(COALESCE((_line->>'amount')::numeric, 0), 2);
    IF _amt < 0 THEN RAISE EXCEPTION 'Opening balances cannot be negative'; END IF;
    _total := _total + _amt;
  END LOOP;
  IF _total <= 0 THEN RAISE EXCEPTION 'Total opening balance must be greater than zero'; END IF;

  INSERT INTO public.journal_entries (entry_number, entry_date, memo, source_type, source_id, status, total_debit, total_credit, created_by)
  VALUES (public.next_number('journal'), _entry_date, COALESCE(NULLIF(btrim(_memo), ''), 'Opening balances'),
          'opening', NULL, 'posted', _total, _total, auth.uid())
  RETURNING id INTO _je_id;

  FOR _line IN SELECT * FROM jsonb_array_elements(_lines) LOOP
    _amt := round(COALESCE((_line->>'amount')::numeric, 0), 2);
    CONTINUE WHEN _amt = 0;
    _i := _i + 1;
    INSERT INTO public.journal_lines (journal_entry_id, account_id, debit, credit, description, line_no)
    VALUES (_je_id, (_line->>'account_id')::uuid, _amt, 0,
            COALESCE(_line->>'description', 'Opening balance'), _i);
  END LOOP;

  _i := _i + 1;
  INSERT INTO public.journal_lines (journal_entry_id, account_id, debit, credit, description, line_no)
  VALUES (_je_id, _equity_account_id, 0, _total, 'Opening balance equity', _i);

  RETURN _je_id;
END; $function$;

REVOKE ALL ON FUNCTION public.post_opening_balances(date, uuid, jsonb, text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.post_opening_balances(date, uuid, jsonb, text) TO authenticated;
GRANT EXECUTE ON FUNCTION public.post_opening_balances(date, uuid, jsonb, text) TO service_role;