thrilltrack-explorer/supabase/migrations/20251006143508_13e7404e-c399-47f5-af33-e1713a02f469.sql

-- Function to calculate dynamic submission priority
CREATE OR REPLACE FUNCTION calculate_submission_priority(
  submission_id UUID
) RETURNS INTEGER 
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  submission_age INTERVAL;
  submission_type TEXT;
  escalated BOOLEAN;
  calculated_priority INTEGER := 3;
BEGIN
  SELECT 
    NOW() - submitted_at,
    content_submissions.submission_type,
    content_submissions.escalated
  INTO submission_age, submission_type, escalated
  FROM content_submissions
  WHERE id = submission_id;
  
  -- Base priority by type
  IF submission_type = 'photo' THEN
    calculated_priority := 2;
  ELSIF submission_type IN ('park', 'ride') THEN
    calculated_priority := 4;
  ELSIF submission_type IN ('manufacturer', 'operator', 'designer', 'property_owner') THEN
    calculated_priority := 3;
  END IF;
  
  -- Boost for age (1 point per 24 hours waiting)
  calculated_priority := calculated_priority + (EXTRACT(EPOCH FROM submission_age) / 86400)::INTEGER;
  
  -- Escalated items get max priority
  IF escalated THEN
    calculated_priority := 10;
  END IF;
  
  -- Cap at 10
  RETURN LEAST(calculated_priority, 10);
END;
$$;

-- Function to claim next submission from queue with proper locking
CREATE OR REPLACE FUNCTION 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 id FROM content_submissions
    WHERE status IN ('pending', 'partially_approved')
      AND (assigned_to IS NULL OR locked_until < NOW())
    ORDER BY 
      priority DESC,
      submitted_at ASC
    LIMIT 1
    FOR UPDATE SKIP LOCKED
  )
  RETURNING 
    id,
    content_submissions.submission_type,
    content_submissions.priority,
    NOW() - submitted_at;
END;
$$;