thrilltrack-explorer/supabase/migrations/20251111174845_5fa6aecb-2b86-4e69-b886-1f2d55bf5105.sql

-- Create function to get contributor leaderboard
CREATE OR REPLACE FUNCTION public.get_contributor_leaderboard(
  limit_count integer DEFAULT 50,
  time_period text DEFAULT 'all_time' -- 'all_time', 'month', 'week'
)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  result jsonb;
  date_filter timestamp;
BEGIN
  -- Determine date filter based on time period
  CASE time_period
    WHEN 'week' THEN date_filter := NOW() - INTERVAL '7 days';
    WHEN 'month' THEN date_filter := NOW() - INTERVAL '30 days';
    ELSE date_filter := '1970-01-01'::timestamp;
  END CASE;

  WITH contributor_stats AS (
    SELECT 
      p.user_id,
      p.username,
      p.display_name,
      p.avatar_url,
      p.created_at as join_date,
      
      -- Count approved submissions by type
      COALESCE(SUM(CASE WHEN es.entity_type = 'park' AND es.status = 'approved' AND es.created_at >= date_filter THEN 1 ELSE 0 END), 0) as parks_added,
      COALESCE(SUM(CASE WHEN es.entity_type = 'ride' AND es.status = 'approved' AND es.created_at >= date_filter THEN 1 ELSE 0 END), 0) as rides_added,
      COALESCE(SUM(CASE WHEN es.entity_type = 'company' AND es.status = 'approved' AND es.created_at >= date_filter THEN 1 ELSE 0 END), 0) as companies_added,
      COALESCE(SUM(CASE WHEN es.entity_type = 'ride_model' AND es.status = 'approved' AND es.created_at >= date_filter THEN 1 ELSE 0 END), 0) as models_added,
      
      -- Count photos
      COALESCE((
        SELECT COUNT(*) 
        FROM entity_photos ep 
        WHERE ep.uploaded_by = p.user_id 
        AND ep.status = 'approved'
        AND ep.created_at >= date_filter
      ), 0) as photos_added,
      
      -- Count reviews
      COALESCE((
        SELECT COUNT(*) 
        FROM reviews r 
        WHERE r.user_id = p.user_id 
        AND r.status = 'approved'
        AND r.created_at >= date_filter
      ), 0) as reviews_added,
      
      -- Count edits (from entity_history)
      COALESCE((
        SELECT COUNT(*) 
        FROM entity_history eh 
        WHERE eh.changed_by = p.user_id
        AND eh.created_at >= date_filter
      ), 0) as edits_made,
      
      -- All-time stats for achievements
      COALESCE((SELECT COUNT(*) FROM entity_submissions WHERE user_id = p.user_id AND status = 'approved' AND entity_type = 'park'), 0) as total_parks,
      COALESCE((SELECT COUNT(*) FROM entity_submissions WHERE user_id = p.user_id AND status = 'approved' AND entity_type = 'ride'), 0) as total_rides,
      COALESCE((SELECT COUNT(*) FROM entity_photos WHERE uploaded_by = p.user_id AND status = 'approved'), 0) as total_photos,
      COALESCE((SELECT COUNT(*) FROM reviews WHERE user_id = p.user_id AND status = 'approved'), 0) as total_reviews,
      COALESCE((SELECT COUNT(*) FROM entity_history WHERE changed_by = p.user_id), 0) as total_edits
      
    FROM profiles p
    LEFT JOIN entity_submissions es ON es.user_id = p.user_id
    WHERE p.user_id IS NOT NULL
    GROUP BY p.user_id, p.username, p.display_name, p.avatar_url, p.created_at
  ),
  
  scored_contributors AS (
    SELECT 
      *,
      -- Calculate contribution score (weighted)
      (parks_added * 10) + 
      (rides_added * 8) + 
      (companies_added * 5) + 
      (models_added * 5) + 
      (photos_added * 2) + 
      (reviews_added * 3) + 
      (edits_made * 1) as contribution_score,
      
      (total_parks * 10) + 
      (total_rides * 8) + 
      (total_photos * 2) + 
      (total_reviews * 3) + 
      (total_edits * 1) as total_score
      
    FROM contributor_stats
  ),
  
  ranked_contributors AS (
    SELECT 
      *,
      ROW_NUMBER() OVER (ORDER BY contribution_score DESC, total_score DESC, join_date ASC) as rank,
      
      -- Determine achievement level
      CASE 
        WHEN total_score >= 5000 THEN 'legend'
        WHEN total_score >= 1000 THEN 'platinum'
        WHEN total_score >= 500 THEN 'gold'
        WHEN total_score >= 100 THEN 'silver'
        WHEN total_score >= 10 THEN 'bronze'
        ELSE 'newcomer'
      END as achievement_level,
      
      -- Calculate special badges
      jsonb_build_array(
        CASE WHEN total_parks >= 100 THEN 'park_explorer' END,
        CASE WHEN total_rides >= 200 THEN 'ride_master' END,
        CASE WHEN total_photos >= 500 THEN 'photographer' END,
        CASE WHEN total_reviews >= 100 THEN 'critic' END,
        CASE WHEN total_edits >= 500 THEN 'editor' END,
        CASE WHEN total_parks >= 10 AND total_rides >= 50 AND total_photos >= 100 THEN 'completionist' END,
        CASE WHEN EXTRACT(days FROM (NOW() - join_date)) >= 365 THEN 'veteran' END,
        CASE WHEN rank = 1 THEN 'top_contributor' END
      ) - 'null'::jsonb as special_badges
      
    FROM scored_contributors
    WHERE contribution_score > 0 OR total_score > 0
  )
  
  SELECT jsonb_build_object(
    'contributors', (
      SELECT jsonb_agg(
        jsonb_build_object(
          'rank', rank,
          'user_id', user_id,
          'username', username,
          'display_name', display_name,
          'avatar_url', avatar_url,
          'join_date', join_date,
          'contribution_score', contribution_score,
          'total_score', total_score,
          'achievement_level', achievement_level,
          'special_badges', special_badges,
          'stats', jsonb_build_object(
            'parks_added', parks_added,
            'rides_added', rides_added,
            'companies_added', companies_added,
            'models_added', models_added,
            'photos_added', photos_added,
            'reviews_added', reviews_added,
            'edits_made', edits_made
          ),
          'total_stats', jsonb_build_object(
            'total_parks', total_parks,
            'total_rides', total_rides,
            'total_photos', total_photos,
            'total_reviews', total_reviews,
            'total_edits', total_edits
          )
        )
        ORDER BY rank
      )
      FROM ranked_contributors
      LIMIT limit_count
    ),
    'total_contributors', (SELECT COUNT(*) FROM ranked_contributors),
    'generated_at', NOW()
  ) INTO result;
  
  RETURN result;
END;
$$;

-- Grant execute permission to authenticated users
GRANT EXECUTE ON FUNCTION public.get_contributor_leaderboard(integer, text) TO authenticated;