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/70db24c9/

Upload File : current_dir [ Writeable ] document_root [ Writeable ]

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

// Start session
session_start();

// Get pagination parameters (optional, for large lists)
$page = max(1, intval($_GET['page'] ?? 1));
$per_page = intval($_GET['per_page'] ?? 100);
$offset = ($page - 1) * $per_page;

try {
    $pdo = getDBConnection();
    
    // Get total count of complete tracks
    $count_stmt = $pdo->query("SELECT COUNT(*) FROM music_tracks WHERE status = 'complete'");
    $total_tracks = $count_stmt->fetchColumn();
    
    // Get all tracks with their rankings and scores (query-safe using prepared statements)
    $sql = "
        SELECT 
            mt.id,
            mt.title,
            u.name as artist_name,
            COALESCE((SELECT COUNT(*) FROM track_plays WHERE track_id = mt.id), 0) as play_count,
            COALESCE((SELECT COUNT(*) FROM track_likes WHERE track_id = mt.id), 0) as like_count,
            COALESCE((SELECT AVG(rating) FROM track_ratings WHERE track_id = mt.id), 0) as avg_rating,
            COALESCE((SELECT COUNT(*) FROM track_ratings WHERE track_id = mt.id), 0) as rating_count,
            -- Calculate total score
            (
                COALESCE((SELECT COUNT(*) FROM track_plays WHERE track_id = mt.id), 0) * 1.0 + 
                COALESCE((SELECT COUNT(*) FROM track_likes WHERE track_id = mt.id), 0) * 2.0 + 
                COALESCE((SELECT AVG(rating) FROM track_ratings WHERE track_id = mt.id), 0) * 
                COALESCE((SELECT COUNT(*) FROM track_ratings WHERE track_id = mt.id), 0) * 5.0
            ) as total_score
        FROM music_tracks mt
        INNER JOIN users u ON mt.user_id = u.id
        WHERE mt.status = 'complete'
        AND mt.user_id IS NOT NULL
        AND u.id IS NOT NULL
        AND (mt.is_public = 1 OR mt.is_public IS NULL)
        ORDER BY total_score DESC, mt.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();
    $tracks = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Calculate rankings for each track
    $ranked_tracks = [];
    $current_rank = $offset + 1;
    $previous_score = null;
    $rank_skip = 0;
    
    foreach ($tracks as $track) {
        $score = (float)$track['total_score'];
        
        // Handle ties - if score matches previous, use same rank
        if ($previous_score !== null && abs($score - $previous_score) < 0.01) {
            $rank_skip++;
        } else {
            $current_rank += $rank_skip;
            $rank_skip = 0;
        }
        
        $ranked_tracks[] = [
            'rank' => $current_rank,
            'id' => (int)$track['id'],
            'title' => $track['title'] ?: 'Untitled Track',
            'artist_name' => $track['artist_name'] ?: 'Unknown Artist',
            'play_count' => (int)$track['play_count'],
            'like_count' => (int)$track['like_count'],
            'avg_rating' => round((float)$track['avg_rating'], 1),
            'rating_count' => (int)$track['rating_count'],
            'total_score' => round($score, 2)
        ];
        
        $previous_score = $score;
        $current_rank++;
    }
    
    echo json_encode([
        'success' => true,
        'tracks' => $ranked_tracks,
        'pagination' => [
            'page' => $page,
            'per_page' => $per_page,
            'total_tracks' => (int)$total_tracks,
            'total_pages' => (int)ceil($total_tracks / $per_page),
            'has_more' => ($page * $per_page) < $total_tracks
        ]
    ]);
    
} catch (PDOException $e) {
    error_log('Database error in get_all_track_rankings.php: ' . $e->getMessage());
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'error' => 'Database error occurred while loading rankings'
    ]);
} catch (Exception $e) {
    error_log('Error in get_all_track_rankings.php: ' . $e->getMessage());
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'error' => 'An error occurred while loading rankings'
    ]);
}
?>