ALTER TABLE public.invoices
  ADD COLUMN IF NOT EXISTS approval_status text NOT NULL DEFAULT 'not_required',
  ADD COLUMN IF NOT EXISTS submitted_by uuid,
  ADD COLUMN IF NOT EXISTS submitted_at timestamptz,
  ADD COLUMN IF NOT EXISTS approved_by uuid,
  ADD COLUMN IF NOT EXISTS approved_at timestamptz,
  ADD COLUMN IF NOT EXISTS rejection_reason text;

CREATE TABLE IF NOT EXISTS public.invoice_approvals (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  invoice_id uuid NOT NULL REFERENCES public.invoices(id) ON DELETE CASCADE,
  action text NOT NULL,
  actor_id uuid,
  note text,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS invoice_approvals_invoice_idx ON public.invoice_approvals(invoice_id);

GRANT SELECT ON public.invoice_approvals TO authenticated;
GRANT ALL ON public.invoice_approvals TO service_role;
ALTER TABLE public.invoice_approvals ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "invoice_approvals_read" ON public.invoice_approvals;
CREATE POLICY "invoice_approvals_read" ON public.invoice_approvals
  FOR SELECT TO authenticated USING (public.can_access('invoices'));

-- Submit a draft invoice for admin approval.
CREATE OR REPLACE FUNCTION public.submit_invoice_for_approval(_invoice_id uuid, _note text DEFAULT NULL)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _inv public.invoices;
BEGIN
  IF NOT public.can_edit('invoices') THEN RAISE EXCEPTION 'You do not have permission to submit invoices'; END IF;
  SELECT * INTO _inv FROM public.invoices WHERE id = _invoice_id FOR UPDATE;
  IF _inv.id IS NULL THEN RAISE EXCEPTION 'Invoice not found'; END IF;
  IF _inv.status <> 'draft' THEN RAISE EXCEPTION 'Only draft invoices can be submitted for approval'; END IF;
  IF _inv.approval_status = 'pending' THEN RAISE EXCEPTION 'Invoice is already awaiting approval'; END IF;
  IF _inv.total <= 0 THEN RAISE EXCEPTION 'Invoice total must be greater than zero'; END IF;

  UPDATE public.invoices SET approval_status = 'pending', submitted_by = auth.uid(), submitted_at = now(),
    approved_by = NULL, approved_at = NULL, rejection_reason = NULL
  WHERE id = _invoice_id;

  INSERT INTO public.invoice_approvals (invoice_id, action, actor_id, note)
  VALUES (_invoice_id, 'submitted', auth.uid(), _note);
  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'submit', 'invoice', _invoice_id, 'Submitted invoice ' || _inv.invoice_number || ' for approval');
END;
$$;

-- Admin approves (and optionally posts) a pending invoice.
CREATE OR REPLACE FUNCTION public.approve_invoice(_invoice_id uuid, _note text DEFAULT NULL, _post boolean DEFAULT true)
RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _inv public.invoices; _je uuid;
BEGIN
  IF NOT public.is_admin() THEN RAISE EXCEPTION 'Only an administrator can approve invoices'; END IF;
  SELECT * INTO _inv FROM public.invoices WHERE id = _invoice_id FOR UPDATE;
  IF _inv.id IS NULL THEN RAISE EXCEPTION 'Invoice not found'; END IF;
  IF _inv.status <> 'draft' THEN RAISE EXCEPTION 'Only draft invoices can be approved'; END IF;
  IF _inv.approval_status NOT IN ('pending','rejected') THEN RAISE EXCEPTION 'Invoice is not awaiting approval'; END IF;

  UPDATE public.invoices SET approval_status = 'approved', approved_by = auth.uid(), approved_at = now(),
    rejection_reason = NULL WHERE id = _invoice_id;
  INSERT INTO public.invoice_approvals (invoice_id, action, actor_id, note)
  VALUES (_invoice_id, 'approved', auth.uid(), _note);
  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'approve', 'invoice', _invoice_id, 'Approved invoice ' || _inv.invoice_number);

  IF _post THEN _je := public.post_invoice(_invoice_id); END IF;
  RETURN _je;
END;
$$;

-- Admin rejects a pending invoice, sending it back to the submitter.
CREATE OR REPLACE FUNCTION public.reject_invoice(_invoice_id uuid, _reason text)
RETURNS void LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _inv public.invoices;
BEGIN
  IF NOT public.is_admin() THEN RAISE EXCEPTION 'Only an administrator can reject invoices'; END IF;
  IF coalesce(btrim(_reason),'') = '' THEN RAISE EXCEPTION 'A rejection reason is required'; END IF;
  SELECT * INTO _inv FROM public.invoices WHERE id = _invoice_id FOR UPDATE;
  IF _inv.id IS NULL THEN RAISE EXCEPTION 'Invoice not found'; END IF;
  IF _inv.approval_status <> 'pending' THEN RAISE EXCEPTION 'Invoice is not awaiting approval'; END IF;

  UPDATE public.invoices SET approval_status = 'rejected', rejection_reason = _reason,
    approved_by = NULL, approved_at = NULL WHERE id = _invoice_id;
  INSERT INTO public.invoice_approvals (invoice_id, action, actor_id, note)
  VALUES (_invoice_id, 'rejected', auth.uid(), _reason);
  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'reject', 'invoice', _invoice_id, 'Rejected invoice ' || _inv.invoice_number);
END;
$$;

-- Posting now requires admin approval for non-admin users.
CREATE OR REPLACE FUNCTION public.post_invoice(_invoice_id uuid)
RETURNS uuid
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE _inv public.invoices; _lines jsonb := '[]'::jsonb; _je uuid; _ar uuid; _vat uuid; _rev record; _fallback uuid;
BEGIN
  IF NOT public.can_edit('invoices') THEN RAISE EXCEPTION 'You do not have permission to post invoices'; END IF;
  SELECT * INTO _inv FROM public.invoices WHERE id = _invoice_id FOR UPDATE;
  IF _inv.id IS NULL THEN RAISE EXCEPTION 'Invoice not found'; END IF;
  IF _inv.status <> 'draft' THEN RAISE EXCEPTION 'Only draft invoices can be posted'; END IF;
  IF _inv.total <= 0 THEN RAISE EXCEPTION 'Invoice total must be greater than zero'; END IF;
  IF NOT public.is_admin() AND _inv.approval_status <> 'approved' THEN
    RAISE EXCEPTION 'This invoice must be approved by an administrator before it can be posted';
  END IF;

  _ar := public.account_id_by_code('1200');
  _vat := public.account_id_by_code('2100');
  _fallback := COALESCE(public.account_id_by_code('4300'), public.account_id_by_code('4100'));

  UPDATE public.invoice_items SET revenue_account_id = _fallback
  WHERE invoice_id = _invoice_id AND revenue_account_id IS NULL;

  _lines := _lines || jsonb_build_object('account_id', _ar, 'debit', _inv.total, 'credit', 0,
      'description', 'Invoice ' || _inv.invoice_number, 'customer_id', _inv.customer_id);

  FOR _rev IN
    SELECT COALESCE(i.revenue_account_id, _fallback) AS acc,
           SUM(i.line_total - i.tax_amount) AS net
    FROM public.invoice_items i WHERE i.invoice_id = _invoice_id
    GROUP BY COALESCE(i.revenue_account_id, _fallback)
  LOOP
    IF round(_rev.net,2) <> 0 THEN
      _lines := _lines || jsonb_build_object('account_id', _rev.acc, 'debit', 0, 'credit', round(_rev.net,2),
        'description', 'Revenue - ' || _inv.invoice_number, 'customer_id', _inv.customer_id);
    END IF;
  END LOOP;

  IF _inv.tax_total > 0 THEN
    _lines := _lines || jsonb_build_object('account_id', _vat, 'debit', 0, 'credit', _inv.tax_total,
      'description', 'VAT on ' || _inv.invoice_number, 'customer_id', _inv.customer_id);
  END IF;

  _je := public.post_journal_entry(_inv.invoice_date, 'Invoice ' || _inv.invoice_number, 'invoice', _invoice_id, _lines);

  UPDATE public.invoices SET status = 'posted', journal_entry_id = _je, posted_at = now(),
    balance = total - amount_paid WHERE id = _invoice_id;

  INSERT INTO public.invoice_approvals (invoice_id, action, actor_id, note)
  VALUES (_invoice_id, 'posted', auth.uid(), 'Posted to the ledger');
  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'post', 'invoice', _invoice_id, 'Posted invoice ' || _inv.invoice_number);
  RETURN _je;
END;
$$;