-- Migration: 00032_referral_code_rate_limits
-- Description: Add referral code rate limiting table and RPC
-- Author: Auto-generated
-- Date: 2025-12-15

CREATE TABLE IF NOT EXISTS referral_code_rate_limits (
  user_id UUID PRIMARY KEY,
  attempt_count INT NOT NULL DEFAULT 0,
  window_start TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION enforce_referral_code_rate_limit(p_user_id UUID)
RETURNS BOOLEAN AS $$
DECLARE
  v_attempt_count INT;
BEGIN
  INSERT INTO referral_code_rate_limits (user_id, attempt_count, window_start)
  VALUES (p_user_id, 1, NOW())
  ON CONFLICT (user_id) DO UPDATE
    SET attempt_count = CASE
      WHEN referral_code_rate_limits.window_start < NOW() - INTERVAL '1 hour'
        THEN 1
      ELSE referral_code_rate_limits.attempt_count + 1
    END,
    window_start = CASE
      WHEN referral_code_rate_limits.window_start < NOW() - INTERVAL '1 hour'
        THEN NOW()
      ELSE referral_code_rate_limits.window_start
    END
  RETURNING attempt_count INTO v_attempt_count;

  RETURN v_attempt_count <= 5;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, auth, extensions;
