CREATE OR REPLACE FUNCTION public.delete_journal_entry(_entry_id uuid)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE _e public.journal_entries;
BEGIN
  IF NOT public.is_admin() THEN
    RAISE EXCEPTION 'Only administrators can delete journal entries.';
  END IF;

  SELECT * INTO _e FROM public.journal_entries WHERE id = _entry_id;
  IF _e.id IS NULL THEN
    RAISE EXCEPTION 'Journal entry not found.';
  END IF;

  IF _e.source_type IS DISTINCT FROM 'manual' THEN
    RAISE EXCEPTION 'Only manual journal entries can be deleted. Use Reverse for entries created by invoices, payments, expenses and payroll.';
  END IF;

  IF EXISTS (
    SELECT 1 FROM public.fiscal_periods p
    WHERE p.status = 'closed' AND _e.entry_date BETWEEN p.start_date AND p.end_date
  ) THEN
    RAISE EXCEPTION 'That entry falls in a closed fiscal period and cannot be deleted.';
  END IF;

  IF EXISTS (SELECT 1 FROM public.journal_entries x WHERE x.reverses_entry_id = _entry_id) THEN
    RAISE EXCEPTION 'That entry has already been reversed, so it cannot be deleted.';
  END IF;

  UPDATE public.journal_entries SET reversed_by = NULL WHERE reversed_by = _entry_id;
  UPDATE public.journal_entries SET status = 'draft', reverses_entry_id = NULL, reversed_by = NULL WHERE id = _entry_id;
  DELETE FROM public.journal_lines WHERE journal_entry_id = _entry_id;
  DELETE FROM public.journal_entries WHERE id = _entry_id;

  INSERT INTO public.audit_logs (table_name, record_id, action, changed_by, old_data)
  VALUES ('journal_entries', _entry_id, 'delete', auth.uid(), to_jsonb(_e));
END;
$$;