-- Payments module permissions: allow any permitted user (not just admins/owner) to
-- record and apply payments, while blocking view-only users.
CREATE OR REPLACE FUNCTION public.allocate_payment(_payment_id uuid, _allocations jsonb)
 RETURNS void
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
DECLARE _pmt public.payments; _alloc jsonb; _amt numeric; _inv public.invoices; _sum numeric := 0;
BEGIN
  PERFORM public.require_access('payments');
  SELECT * INTO _pmt FROM public.payments WHERE id = _payment_id FOR UPDATE;
  IF _pmt.id IS NULL THEN RAISE EXCEPTION 'Payment not found'; END IF;
  SELECT COALESCE(SUM((a->>'amount')::numeric),0) INTO _sum FROM jsonb_array_elements(_allocations) a;
  IF round(_sum,2) > round(_pmt.unapplied_amount,2) + 0.001 THEN RAISE EXCEPTION 'Allocation exceeds unapplied amount'; END IF;

  FOR _alloc IN SELECT * FROM jsonb_array_elements(_allocations) LOOP
    _amt := round((_alloc->>'amount')::numeric,2);
    IF _amt <= 0 THEN CONTINUE; END IF;
    SELECT * INTO _inv FROM public.invoices WHERE id = (_alloc->>'invoice_id')::uuid FOR UPDATE;
    IF _inv.id IS NULL THEN RAISE EXCEPTION 'Invoice not found for allocation'; END IF;
    IF _inv.status NOT IN ('posted','partial') THEN RAISE EXCEPTION 'Invoice % is not open for payment', _inv.invoice_number; END IF;
    IF _amt > _inv.balance + 0.001 THEN RAISE EXCEPTION 'Allocation exceeds balance of invoice %', _inv.invoice_number; END IF;
    INSERT INTO public.payment_allocations (payment_id, invoice_id, amount) VALUES (_payment_id, _inv.id, _amt);
    UPDATE public.invoices SET amount_paid = amount_paid + _amt, balance = total - (amount_paid + _amt),
      status = CASE WHEN total - (amount_paid + _amt) <= 0.001 THEN 'paid' ELSE 'partial' END
    WHERE id = _inv.id;
  END LOOP;
  UPDATE public.payments SET unapplied_amount = unapplied_amount - round(_sum,2) WHERE id = _payment_id;

  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'allocate', 'payment', _payment_id, 'Applied payment ' || _pmt.payment_number || ' to invoices');
END; $function$;

CREATE OR REPLACE FUNCTION public.record_customer_payment(_customer_id uuid, _payment_date date, _amount numeric, _deposit_account_id uuid, _method text, _reference text, _notes text, _allocations jsonb)
 RETURNS uuid
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
DECLARE _pmt_id uuid; _num text; _je uuid; _alloc jsonb; _sum numeric := 0; _inv public.invoices; _amt numeric;
BEGIN
  PERFORM public.require_access('payments');
  IF _amount <= 0 THEN RAISE EXCEPTION 'Payment amount must be greater than zero'; END IF;
  _num := public.next_number('payment');

  SELECT COALESCE(SUM((a->>'amount')::numeric),0) INTO _sum
  FROM jsonb_array_elements(COALESCE(_allocations,'[]'::jsonb)) a;
  IF round(_sum,2) > round(_amount,2) THEN RAISE EXCEPTION 'Allocated amount exceeds payment amount'; END IF;

  INSERT INTO public.payments (payment_number, customer_id, payment_date, amount, unapplied_amount,
    method, deposit_account_id, reference, notes)
  VALUES (_num, _customer_id, _payment_date, _amount, round(_amount - _sum,2), COALESCE(_method,'cash'),
    _deposit_account_id, _reference, _notes)
  RETURNING id INTO _pmt_id;

  FOR _alloc IN SELECT * FROM jsonb_array_elements(COALESCE(_allocations,'[]'::jsonb)) LOOP
    _amt := round((_alloc->>'amount')::numeric, 2);
    IF _amt <= 0 THEN CONTINUE; END IF;
    SELECT * INTO _inv FROM public.invoices WHERE id = (_alloc->>'invoice_id')::uuid FOR UPDATE;
    IF _inv.id IS NULL THEN RAISE EXCEPTION 'Invoice not found for allocation'; END IF;
    IF _inv.status NOT IN ('posted','partial') THEN RAISE EXCEPTION 'Invoice % is not open for payment', _inv.invoice_number; END IF;
    IF _amt > _inv.balance + 0.001 THEN RAISE EXCEPTION 'Allocation exceeds balance of invoice %', _inv.invoice_number; END IF;

    INSERT INTO public.payment_allocations (payment_id, invoice_id, amount) VALUES (_pmt_id, _inv.id, _amt);
    UPDATE public.invoices SET amount_paid = amount_paid + _amt, balance = total - (amount_paid + _amt),
      status = CASE WHEN total - (amount_paid + _amt) <= 0.001 THEN 'paid' ELSE 'partial' END
    WHERE id = _inv.id;
  END LOOP;

  _je := public.post_journal_entry(_payment_date, 'Customer payment ' || _num, 'payment', _pmt_id,
    jsonb_build_array(
      jsonb_build_object('account_id', _deposit_account_id, 'debit', _amount, 'credit', 0, 'description', 'Payment ' || _num, 'customer_id', _customer_id),
      jsonb_build_object('account_id', public.account_id_by_code('1200'), 'debit', 0, 'credit', _amount, 'description', 'Payment ' || _num, 'customer_id', _customer_id)
    ));
  UPDATE public.payments SET journal_entry_id = _je WHERE id = _pmt_id;
  INSERT INTO public.audit_logs (user_id, action, entity_type, entity_id, description)
  VALUES (auth.uid(), 'create', 'payment', _pmt_id, 'Recorded payment ' || _num);
  RETURN _pmt_id;
END; $function$;

-- Row level security: view with the payments module, change only with edit rights.
DROP POLICY IF EXISTS payments_all ON public.payments;
CREATE POLICY payments_view ON public.payments FOR SELECT TO authenticated USING (public.can_access('payments'));
CREATE POLICY payments_write ON public.payments FOR ALL TO authenticated
  USING (public.can_edit('payments')) WITH CHECK (public.can_edit('payments'));

DROP POLICY IF EXISTS payment_allocations_all ON public.payment_allocations;
CREATE POLICY payment_allocations_view ON public.payment_allocations FOR SELECT TO authenticated USING (public.can_access('payments'));
CREATE POLICY payment_allocations_write ON public.payment_allocations FOR ALL TO authenticated
  USING (public.can_edit('payments')) WITH CHECK (public.can_edit('payments'));

GRANT SELECT, INSERT, UPDATE, DELETE ON public.payments TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.payment_allocations TO authenticated;
GRANT ALL ON public.payments TO service_role;
GRANT ALL ON public.payment_allocations TO service_role;