Server : Apache/2 System : Linux server-15-235-50-60 5.15.0-164-generic #174-Ubuntu SMP Fri Nov 14 20:25:16 UTC 2025 x86_64 User : gositeme ( 1004) PHP Version : 8.2.29 Disable Function : exec,system,passthru,shell_exec,proc_close,proc_open,dl,popen,show_source,posix_kill,posix_mkfifo,posix_getpwuid,posix_setpgid,posix_setsid,posix_setuid,posix_setgid,posix_seteuid,posix_setegid,posix_uname Directory :  /home/gositeme/.cursor-server/data/User/History/-5e139c8f/

Upload File : current_dir [ Writeable ] document_root [ Writeable ]

<?php
header('Content-Type: application/json');
require_once __DIR__ . '/../config/database.php';

// Start session
session_start();

$per_page = isset($_GET['per_page']) ? (int)$_GET['per_page'] : 50;
$page = isset($_GET['page']) ? (int)$_GET['page'] : 1;
$offset = ($page - 1) * $per_page;

try {
    $pdo = getDBConnection();
    
    // Get all artists with their rankings and scores
    $sql = "
        SELECT 
            u.id,
            u.name as username,
            u.created_at,
            COALESCE((
                SELECT COUNT(*) 
                FROM track_plays tp
                JOIN music_tracks mt ON tp.track_id = mt.id
                WHERE mt.user_id = u.id AND mt.status = 'complete' AND mt.is_public = 1
            ), 0) as total_plays,
            COALESCE((
                SELECT COUNT(*) 
                FROM track_likes tl
                JOIN music_tracks mt ON tl.track_id = mt.id
                WHERE mt.user_id = u.id AND mt.status = 'complete' AND mt.is_public = 1
            ), 0) as total_likes,
            COALESCE((SELECT AVG(rating) FROM artist_ratings WHERE artist_id = u.id), 0) as average_rating,
            COALESCE((SELECT COUNT(*) FROM artist_ratings WHERE artist_id = u.id), 0) as rating_count,
            -- Calculate total score
            (
                COALESCE((
                    SELECT COUNT(*) 
                    FROM track_plays tp
                    JOIN music_tracks mt ON tp.track_id = mt.id
                    WHERE mt.user_id = u.id AND mt.status = 'complete' AND mt.is_public = 1
                ), 0) * 1.0 + 
                COALESCE((
                    SELECT COUNT(*) 
                    FROM track_likes tl
                    JOIN music_tracks mt ON tl.track_id = mt.id
                    WHERE mt.user_id = u.id AND mt.status = 'complete' AND mt.is_public = 1
                ), 0) * 2.0 + 
                COALESCE((SELECT AVG(rating) FROM artist_ratings WHERE artist_id = u.id), 0) * 
                COALESCE((SELECT COUNT(*) FROM artist_ratings WHERE artist_id = u.id), 0) * 5.0
            ) as total_score
        FROM users u
        WHERE EXISTS (
            SELECT 1 FROM music_tracks mt 
            WHERE mt.user_id = u.id AND mt.status = 'complete' AND mt.is_public = 1
        )
        ORDER BY total_score DESC, u.created_at DESC
        LIMIT :per_page OFFSET :offset
    ";
    
    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':per_page', $per_page, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();
    $artists = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Get total count
    $count_sql = "
        SELECT COUNT(DISTINCT u.id)
        FROM users u
        WHERE EXISTS (
            SELECT 1 FROM music_tracks mt 
            WHERE mt.user_id = u.id AND mt.status = 'complete' AND mt.is_public = 1
        )
    ";
    $count_stmt = $pdo->query($count_sql);
    $total_artists = (int)$count_stmt->fetchColumn();
    
    // Calculate rank for each artist (based on total_score)
    // Use the same logic as calculateArtistRankings function
    foreach ($artists as &$artist) {
        $total_plays = (int)($artist['total_plays'] ?? 0);
        $total_likes = (int)($artist['total_likes'] ?? 0);
        $avg_rating = (float)($artist['average_rating'] ?? 0);
        $rating_count = (int)($artist['rating_count'] ?? 0);
        $artist_id = (int)$artist['id'];
        
        $rank_sql = "
            SELECT COUNT(*) + 1 as rank
            FROM (
                SELECT 
                    u.id as artist_id,
                    COALESCE((
                        SELECT COUNT(*) 
                        FROM track_plays tp
                        JOIN music_tracks mt ON tp.track_id = mt.id
                        WHERE mt.user_id = u.id AND mt.status = 'complete' AND mt.is_public = 1
                    ), 0) as total_plays,
                    COALESCE((
                        SELECT COUNT(*) 
                        FROM track_likes tl
                        JOIN music_tracks mt ON tl.track_id = mt.id
                        WHERE mt.user_id = u.id AND mt.status = 'complete' AND mt.is_public = 1
                    ), 0) as total_likes,
                    COALESCE((
                        SELECT AVG(rating) 
                        FROM artist_ratings 
                        WHERE artist_id = u.id
                    ), 0) as avg_rating,
                    COALESCE((
                        SELECT COUNT(*) 
                        FROM artist_ratings 
                        WHERE artist_id = u.id
                    ), 0) as rating_count
                FROM users u
                WHERE EXISTS (
                    SELECT 1 FROM music_tracks mt 
                    WHERE mt.user_id = u.id AND mt.status = 'complete' AND mt.is_public = 1
                )
            ) artist_stats
            WHERE (
                (total_plays * 1.0 + total_likes * 2.0 + avg_rating * rating_count * 5.0) >
                (? * 1.0 + ? * 2.0 + ? * ? * 5.0)
                OR (
                    (total_plays * 1.0 + total_likes * 2.0 + avg_rating * rating_count * 5.0) =
                    (? * 1.0 + ? * 2.0 + ? * ? * 5.0)
                    AND artist_id < ?
                )
            )
        ";
        
        $rank_stmt = $pdo->prepare($rank_sql);
        $rank_stmt->execute([
            $total_plays, $total_likes, $avg_rating, $rating_count,
            $total_plays, $total_likes, $avg_rating, $rating_count, $artist_id
        ]);
        $artist['rank'] = (int)$rank_stmt->fetchColumn();
    }
    unset($artist);
    
    echo json_encode([
        'success' => true,
        'artists' => $artists,
        'pagination' => [
            'page' => $page,
            'per_page' => $per_page,
            'total_artists' => $total_artists,
            'total_pages' => ceil($total_artists / $per_page)
        ]
    ]);
    
} catch (Exception $e) {
    error_log("Error fetching artist rankings: " . $e->getMessage());
    error_log("Stack trace: " . $e->getTraceAsString());
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'error' => 'Failed to fetch artist rankings: ' . $e->getMessage()
    ]);
}
?>