thrilltrack-explorer/supabase/migrations/20251006185633_5aea811b-4e27-4338-8872-5f6a5a79de0c.sql

-- Fix ambiguous column reference in claim_next_submission
CREATE OR REPLACE FUNCTION public.claim_next_submission(
  moderator_id UUID,
  lock_duration INTERVAL DEFAULT '15 minutes'
)
RETURNS TABLE (
  submission_id UUID,
  submission_type TEXT,
  priority INTEGER,
  waiting_time INTERVAL
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  -- Update priority for all pending submissions first
  UPDATE content_submissions
  SET priority = calculate_submission_priority(id)
  WHERE status IN ('pending', 'partially_approved')
    AND (assigned_to IS NULL OR locked_until < NOW());
  
  -- Claim next item with row-level lock
  RETURN QUERY
  UPDATE content_submissions
  SET 
    assigned_to = moderator_id,
    assigned_at = NOW(),
    locked_until = NOW() + lock_duration,
    first_reviewed_at = COALESCE(first_reviewed_at, NOW())
  WHERE id = (
    SELECT cs.id FROM content_submissions cs
    WHERE cs.status IN ('pending', 'partially_approved')
      AND (cs.assigned_to IS NULL OR cs.locked_until < NOW())
    ORDER BY 
      cs.priority DESC,
      cs.submitted_at ASC
    LIMIT 1
    FOR UPDATE SKIP LOCKED
  )
  RETURNING 
    content_submissions.id,
    content_submissions.submission_type,
    content_submissions.priority,
    NOW() - content_submissions.submitted_at;
END;
$$;