CREATE OR REPLACE FUNCTION public.delete_invoice(_invoice_id uuid)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  _inv public.invoices;
  _r record;
BEGIN
  PERFORM public.require_access('invoices');

  SELECT * INTO _inv FROM public.invoices WHERE id = _invoice_id;
  IF _inv.id IS NULL THEN
    RAISE EXCEPTION 'Invoice not found.';
  END IF;

  IF _inv.status <> 'draft' AND NOT public.is_admin() THEN
    RAISE EXCEPTION 'Only administrators can delete a posted invoice.';
  END IF;

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

  FOR _r IN
    SELECT p.id AS payment_id, p.journal_entry_id
    FROM public.payments p
    WHERE EXISTS (SELECT 1 FROM public.payment_allocations a WHERE a.payment_id = p.id AND a.invoice_id = _invoice_id)
  LOOP
    IF EXISTS (
      SELECT 1 FROM public.payment_allocations a
      WHERE a.payment_id = _r.payment_id AND a.invoice_id IS DISTINCT FROM _invoice_id
    ) THEN
      RAISE EXCEPTION 'A receipt on this invoice also settles other invoices. Remove that allocation first.';
    END IF;
    DELETE FROM public.payment_allocations WHERE payment_id = _r.payment_id;
    DELETE FROM public.payments WHERE id = _r.payment_id;
    PERFORM public.hard_delete_journal_entry(_r.journal_entry_id);
  END LOOP;

  FOR _r IN SELECT id, journal_entry_id FROM public.credit_notes WHERE invoice_id = _invoice_id LOOP
    DELETE FROM public.credit_notes WHERE id = _r.id;
    PERFORM public.hard_delete_journal_entry(_r.journal_entry_id);
  END LOOP;

  FOR _r IN SELECT id, journal_entry_id FROM public.refunds WHERE invoice_id = _invoice_id LOOP
    DELETE FROM public.refunds WHERE id = _r.id;
    PERFORM public.hard_delete_journal_entry(_r.journal_entry_id);
  END LOOP;

  UPDATE public.bookings SET invoice_id = NULL WHERE invoice_id = _invoice_id;
  UPDATE public.quotes SET invoice_id = NULL WHERE invoice_id = _invoice_id;

  DELETE FROM public.invoices WHERE id = _invoice_id;
  PERFORM public.hard_delete_journal_entry(_inv.journal_entry_id);

  FOR _r IN SELECT id FROM public.journal_entries WHERE source_type = 'invoice' AND source_id = _invoice_id LOOP
    PERFORM public.hard_delete_journal_entry(_r.id);
  END LOOP;

  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description, metadata)
  VALUES (auth.uid(), 'delete', 'invoices', _invoice_id,
          'Invoice ' || COALESCE(_inv.invoice_number, '') || ' permanently deleted',
          to_jsonb(_inv));
END; $$;

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