REVOKE ALL ON FUNCTION public.consume_credits(UUID, INTEGER) FROM PUBLIC, anon, authenticated;
REVOKE ALL ON FUNCTION public.ensure_user_credits(UUID) FROM PUBLIC, anon, authenticated;

-- Tighten function bodies to only operate on the calling user's row.
CREATE OR REPLACE FUNCTION public.consume_credits(_amount INTEGER)
RETURNS TABLE (balance INTEGER, monthly_allowance INTEGER, total_used INTEGER, period_start TIMESTAMPTZ)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  uid UUID := auth.uid();
  current_period TIMESTAMPTZ := date_trunc('month', now());
BEGIN
  IF uid IS NULL THEN RAISE EXCEPTION 'Not authenticated' USING ERRCODE = '42501'; END IF;

  INSERT INTO public.user_credits (user_id) VALUES (uid)
  ON CONFLICT (user_id) DO NOTHING;

  UPDATE public.user_credits
     SET balance = monthly_allowance, period_start = current_period, total_used = 0
   WHERE user_id = uid AND period_start < current_period;

  UPDATE public.user_credits
     SET balance = balance - GREATEST(_amount, 0),
         total_used = total_used + GREATEST(_amount, 0)
   WHERE user_id = uid AND balance >= GREATEST(_amount, 0);

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Out of credits' USING ERRCODE = 'P0001';
  END IF;

  RETURN QUERY
  SELECT uc.balance, uc.monthly_allowance, uc.total_used, uc.period_start
    FROM public.user_credits uc WHERE uc.user_id = uid;
END;
$$;

CREATE OR REPLACE FUNCTION public.ensure_user_credits()
RETURNS TABLE (balance INTEGER, monthly_allowance INTEGER, total_used INTEGER, period_start TIMESTAMPTZ)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  uid UUID := auth.uid();
  current_period TIMESTAMPTZ := date_trunc('month', now());
BEGIN
  IF uid IS NULL THEN RAISE EXCEPTION 'Not authenticated' USING ERRCODE = '42501'; END IF;

  INSERT INTO public.user_credits (user_id) VALUES (uid)
  ON CONFLICT (user_id) DO NOTHING;

  UPDATE public.user_credits
     SET balance = monthly_allowance, period_start = current_period, total_used = 0
   WHERE user_id = uid AND period_start < current_period;

  RETURN QUERY
  SELECT uc.balance, uc.monthly_allowance, uc.total_used, uc.period_start
    FROM public.user_credits uc WHERE uc.user_id = uid;
END;
$$;

-- Drop the old (uid, amount) variants.
DROP FUNCTION IF EXISTS public.consume_credits(UUID, INTEGER);
DROP FUNCTION IF EXISTS public.ensure_user_credits(UUID);

GRANT EXECUTE ON FUNCTION public.consume_credits(INTEGER) TO authenticated;
GRANT EXECUTE ON FUNCTION public.ensure_user_credits() TO authenticated;