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)
    foreach ($artists as &$artist) {
        $rank_sql = "
            SELECT COUNT(*) + 1
            FROM (
                SELECT 
                    u2.id,
                    (
                        COALESCE((
                            SELECT COUNT(*) 
                            FROM track_plays tp
                            JOIN music_tracks mt ON tp.track_id = mt.id
                            WHERE mt.user_id = u2.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 = u2.id AND mt.status = 'complete' AND mt.is_public = 1
                        ), 0) * 2.0 + 
                        COALESCE((SELECT AVG(rating) FROM artist_ratings WHERE artist_id = u2.id), 0) * 
                        COALESCE((SELECT COUNT(*) FROM artist_ratings WHERE artist_id = u2.id), 0) * 5.0
                    ) as total_score
                FROM users u2
                WHERE EXISTS (
                    SELECT 1 FROM music_tracks mt 
                    WHERE mt.user_id = u2.id AND mt.status = 'complete' AND mt.is_public = 1
                )
            ) artist_scores
            WHERE total_score > :total_score
               OR (total_score = :total_score AND artist_scores.id < :artist_id)
        ";
        
        $rank_stmt = $pdo->prepare($rank_sql);
        $rank_stmt->execute([
            ':total_score' => $artist['total_score'],
            ':artist_id' => $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()
    ]);
}
?>