-- Migration: 00015_referral_count_rpc
-- Description: Atomic RPC to increment referral_count_verified for a referrer
-- Date: 2025-12-21

CREATE OR REPLACE FUNCTION increment_referral_count_verified(referrer_id UUID)
RETURNS TABLE (
  referral_count_verified INTEGER,
  priority_score INTEGER
) AS $$
BEGIN
  UPDATE users
  SET referral_count_verified = referral_count_verified + 1
  WHERE id = referrer_id;

  RETURN QUERY
  SELECT u.referral_count_verified, u.priority_score
  FROM users u
  WHERE u.id = referrer_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, auth, extensions;

GRANT EXECUTE ON FUNCTION increment_referral_count_verified(UUID) TO service_role;


