-- Fix subscriptions table to allow proper upsert operations
-- This adds a unique constraint on user_id to prevent multiple subscriptions per user
-- and allows the webhook upsert operations to work correctly

-- First, clean up any potential duplicate subscriptions by keeping only the most recent one per user
WITH ranked_subs AS (
  SELECT id,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as rn
  FROM subscriptions
)
DELETE FROM subscriptions 
WHERE id IN (
  SELECT id FROM ranked_subs WHERE rn > 1
);

-- Add unique constraint on user_id to prevent multiple active subscriptions
-- This allows proper upsert behavior when a user gets a new subscription
CREATE UNIQUE INDEX IF NOT EXISTS idx_subscriptions_user_id_unique ON subscriptions(user_id);

-- Add comment to explain the constraint
COMMENT ON INDEX idx_subscriptions_user_id_unique IS 'Ensures one subscription per user for proper upsert operations';