 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/ |
<?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();
// Check if track_votes table exists
$votes_table_exists = false;
try {
$check_stmt = $pdo->query("SHOW TABLES LIKE 'track_votes'");
$votes_table_exists = $check_stmt->rowCount() > 0;
} catch (Exception $e) {
$votes_table_exists = false;
}
// 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)
if ($votes_table_exists) {
$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 SUM(CASE WHEN vote_type = 'up' THEN 1 ELSE -1 END)
FROM track_votes
WHERE track_id = mt.id
), 0) as vote_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 SUM(CASE WHEN vote_type = 'up' THEN 1 ELSE -1 END)
FROM track_votes
WHERE track_id = mt.id
), 0) * 3.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
";
} else {
$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,
0 as vote_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
// Standard ranking: Tied tracks get same rank, next rank skips tied positions
// Example: [100, 100, 99] = ranks [1, 1, 3] not [1, 1, 2]
$ranked_tracks = [];
$current_rank = $offset + 1;
$previous_score = null;
$previous_tied_count = 0; // How many tracks were tied at previous rank
$tied_track_ids = []; // Track IDs in current tie group for logging
foreach ($tracks as $track) {
$score = (float)$track['total_score'];
// Check if this score ties with previous (within 0.01 tolerance)
$is_tie = ($previous_score !== null && abs($score - $previous_score) < 0.01);
if ($is_tie) {
// This is a tie - use same rank as previous track
$tied_track_ids[] = (int)$track['id'];
} else {
// New score (not a tie) - calculate new rank
// If previous had ties, skip those positions
if ($previous_score !== null) {
$current_rank += $previous_tied_count;
}
// Log previous tie group if it had multiple tracks
if (count($tied_track_ids) > 1) {
$tied_rank = $current_rank - $previous_tied_count;
error_log("📊 RANKING TIE: " . count($tied_track_ids) . " tracks tied at rank #{$tied_rank} (Track IDs: " . implode(', ', $tied_track_ids) . ", Score: " . round($previous_score, 2) . ")");
}
// Start new tie group
$tied_track_ids = [(int)$track['id']];
$previous_tied_count = 1;
}
$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'],
'vote_count' => (int)($track['vote_count'] ?? 0),
'avg_rating' => round((float)$track['avg_rating'], 1),
'rating_count' => (int)$track['rating_count'],
'total_score' => round($score, 2)
];
// Update tied count if this is part of a tie
if ($is_tie) {
$previous_tied_count++;
}
$previous_score = $score;
}
// Log final tie group if any
if (count($tied_track_ids) > 1) {
$tied_rank = $current_rank;
error_log("📊 RANKING TIE: " . count($tied_track_ids) . " tracks tied at rank #{$tied_rank} (Track IDs: " . implode(', ', $tied_track_ids) . ", Score: " . round($previous_score, 2) . ")");
}
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'
]);
}
?>