CREATE OR REPLACE FUNCTION public.consume_credits(_amount integer)
 RETURNS TABLE(balance integer, monthly_allowance integer, total_used integer, period_start timestamp with time zone)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
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 uc
     SET balance = uc.monthly_allowance, period_start = current_period, total_used = 0
   WHERE uc.user_id = uid AND uc.period_start < current_period;

  UPDATE public.user_credits uc
     SET balance = uc.balance - GREATEST(_amount, 0),
         total_used = uc.total_used + GREATEST(_amount, 0)
   WHERE uc.user_id = uid AND uc.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;
$function$;

CREATE OR REPLACE FUNCTION public.ensure_user_credits()
 RETURNS TABLE(balance integer, monthly_allowance integer, total_used integer, period_start timestamp with time zone)
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
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 uc
     SET balance = uc.monthly_allowance, period_start = current_period, total_used = 0
   WHERE uc.user_id = uid AND uc.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;
$function$;