CREATE OR REPLACE FUNCTION public.hard_delete_journal_entry(_entry_id uuid)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF _entry_id IS NULL THEN RETURN; 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;
END; $$;
REVOKE ALL ON FUNCTION public.hard_delete_journal_entry(uuid) FROM PUBLIC;
REVOKE ALL ON FUNCTION public.hard_delete_journal_entry(uuid) FROM anon, authenticated;

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;

  -- Payments: only removable when the receipt applies solely to this invoice
  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;

  -- Credit notes and refunds tied to the invoice
  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;

  -- Unlink source documents, then remove the invoice and its ledger entry
  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 (table_name, record_id, action, changed_by, old_data)
  VALUES ('invoices', _invoice_id, 'delete', auth.uid(), to_jsonb(_inv));
END; $$;

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