DROP POLICY IF EXISTS invoices_all ON public.invoices;
DROP POLICY IF EXISTS invoice_items_all ON public.invoice_items;

CREATE POLICY invoices_select ON public.invoices FOR SELECT TO authenticated USING (public.can_access('invoices'));
CREATE POLICY invoices_insert ON public.invoices FOR INSERT TO authenticated WITH CHECK (public.can_edit('invoices'));
CREATE POLICY invoices_update ON public.invoices FOR UPDATE TO authenticated USING (public.can_edit('invoices')) WITH CHECK (public.can_edit('invoices'));
CREATE POLICY invoices_delete ON public.invoices FOR DELETE TO authenticated USING (public.can_edit('invoices'));

CREATE POLICY invoice_items_select ON public.invoice_items FOR SELECT TO authenticated USING (public.can_access('invoices'));
CREATE POLICY invoice_items_insert ON public.invoice_items FOR INSERT TO authenticated WITH CHECK (public.can_edit('invoices'));
CREATE POLICY invoice_items_update ON public.invoice_items FOR UPDATE TO authenticated USING (public.can_edit('invoices')) WITH CHECK (public.can_edit('invoices'));
CREATE POLICY invoice_items_delete ON public.invoice_items FOR DELETE TO authenticated USING (public.can_edit('invoices'));

CREATE OR REPLACE FUNCTION public.post_invoice(_invoice_id uuid)
RETURNS uuid
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE _inv public.invoices; _lines jsonb := '[]'::jsonb; _je uuid; _ar uuid; _vat uuid; _rev record;
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;

  _ar := public.account_id_by_code('1200');
  _vat := public.account_id_by_code('2100');

  _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, public.account_id_by_code('4100')) 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, public.account_id_by_code('4100'))
  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.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; $$;

CREATE OR REPLACE FUNCTION public.void_invoice(_invoice_id uuid)
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 void 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.amount_paid > 0 THEN RAISE EXCEPTION 'Cannot void an invoice with payments applied'; END IF;
  IF _inv.journal_entry_id IS NOT NULL THEN
    PERFORM public.reverse_journal_entry(_inv.journal_entry_id, CURRENT_DATE, 'Void invoice ' || _inv.invoice_number);
  END IF;
  UPDATE public.invoices SET status = 'void', balance = 0 WHERE id = _invoice_id;
  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'void', 'invoice', _invoice_id, 'Voided invoice ' || _inv.invoice_number);
END; $$;