 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();
// OPTIMIZED: Using JOINs instead of correlated subqueries for better performance
// Get all tracks with their rankings and scores (query-safe using prepared statements)
if ($votes_table_exists) {
$sql = "
SELECT
mt.id,
mt.title,
mt.created_at,
u.name as artist_name,
COALESCE(play_stats.play_count, 0) as play_count,
COALESCE(like_stats.like_count, 0) as like_count,
COALESCE(vote_stats.vote_count, 0) as vote_count,
COALESCE(rating_stats.avg_rating, 0) as avg_rating,
COALESCE(rating_stats.rating_count, 0) as rating_count,
-- Calculate total score using JOIN aliases
(
COALESCE(play_stats.play_count, 0) * 1.0 +
COALESCE(like_stats.like_count, 0) * 2.0 +
COALESCE(vote_stats.vote_count, 0) * 3.0 +
COALESCE(rating_stats.avg_rating, 0) * COALESCE(rating_stats.rating_count, 0) * 5.0
) as total_score
FROM music_tracks mt
INNER JOIN users u ON mt.user_id = u.id
LEFT JOIN (SELECT track_id, COUNT(*) as play_count FROM track_plays GROUP BY track_id) play_stats ON mt.id = play_stats.track_id
LEFT JOIN (SELECT track_id, COUNT(*) as like_count FROM track_likes GROUP BY track_id) like_stats ON mt.id = like_stats.track_id
LEFT JOIN (SELECT track_id, SUM(CASE WHEN vote_type = 'up' THEN 1 ELSE -1 END) as vote_count FROM track_votes GROUP BY track_id) vote_stats ON mt.id = vote_stats.track_id
LEFT JOIN (SELECT track_id, AVG(rating) as avg_rating, COUNT(*) as rating_count FROM track_ratings GROUP BY track_id) rating_stats ON mt.id = rating_stats.track_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,
mt.created_at,
u.name as artist_name,
COALESCE(play_stats.play_count, 0) as play_count,
COALESCE(like_stats.like_count, 0) as like_count,
0 as vote_count,
COALESCE(rating_stats.avg_rating, 0) as avg_rating,
COALESCE(rating_stats.rating_count, 0) as rating_count,
-- Calculate total score using JOIN aliases
(
COALESCE(play_stats.play_count, 0) * 1.0 +
COALESCE(like_stats.like_count, 0) * 2.0 +
COALESCE(rating_stats.avg_rating, 0) * COALESCE(rating_stats.rating_count, 0) * 5.0
) as total_score
FROM music_tracks mt
INNER JOIN users u ON mt.user_id = u.id
LEFT JOIN (SELECT track_id, COUNT(*) as play_count FROM track_plays GROUP BY track_id) play_stats ON mt.id = play_stats.track_id
LEFT JOIN (SELECT track_id, COUNT(*) as like_count FROM track_likes GROUP BY track_id) like_stats ON mt.id = like_stats.track_id
LEFT JOIN (SELECT track_id, AVG(rating) as avg_rating, COUNT(*) as rating_count FROM track_ratings GROUP BY track_id) rating_stats ON mt.id = rating_stats.track_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 using the SAME logic as individual track ranking
// Individual ranking uses: COUNT(*) + 1 where COUNT is tracks with BETTER scores
// This means: rank = (number of tracks with better scores) + 1
// Tied tracks naturally get the same rank because they have the same number of better tracks
// Check if track_votes table exists for rank calculation
$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;
}
$ranked_tracks = [];
$tied_groups = []; // Track tied groups for logging
foreach ($tracks as $track) {
$score = (float)$track['total_score'];
$play_count = (int)$track['play_count'];
$like_count = (int)$track['like_count'];
$vote_count = (int)($track['vote_count'] ?? 0);
$avg_rating = (float)$track['avg_rating'];
$rating_count = (int)$track['rating_count'];
// Calculate rank using the EXACT same logic as individual track ranking
// Count how many tracks have BETTER scores OR same score but newer (tiebreaker)
// Tiebreaker: When scores are equal, newer tracks (created_at) rank higher
$track_created_at = isset($track['created_at']) ? $track['created_at'] : date('Y-m-d H:i:s');
if ($votes_table_exists) {
$rank_query = "
SELECT COUNT(*) + 1 as rank
FROM music_tracks mt2
LEFT JOIN (SELECT track_id, COUNT(*) as play_count FROM track_plays GROUP BY track_id) tp2 ON mt2.id = tp2.track_id
LEFT JOIN (SELECT track_id, COUNT(*) as like_count FROM track_likes GROUP BY track_id) tl2 ON mt2.id = tl2.track_id
LEFT JOIN (
SELECT
track_id,
SUM(CASE WHEN vote_type = 'up' THEN 1 ELSE -1 END) as vote_count
FROM track_votes
GROUP BY track_id
) tv2 ON mt2.id = tv2.track_id
LEFT JOIN (SELECT track_id, AVG(rating) as avg_rating, COUNT(*) as rating_count FROM track_ratings GROUP BY track_id) tr2 ON mt2.id = tr2.track_id
WHERE mt2.status = 'complete'
AND (
(COALESCE(tp2.play_count, 0) * 1.0 +
COALESCE(tl2.like_count, 0) * 2.0 +
COALESCE(tv2.vote_count, 0) * 3.0 +
COALESCE(tr2.avg_rating, 0) * COALESCE(tr2.rating_count, 0) * 5.0) >
(COALESCE(?, 0) * 1.0 +
COALESCE(?, 0) * 2.0 +
COALESCE(?, 0) * 3.0 +
COALESCE(?, 0) * COALESCE(?, 0) * 5.0)
OR (
(COALESCE(tp2.play_count, 0) * 1.0 +
COALESCE(tl2.like_count, 0) * 2.0 +
COALESCE(tv2.vote_count, 0) * 3.0 +
COALESCE(tr2.avg_rating, 0) * COALESCE(tr2.rating_count, 0) * 5.0) =
(COALESCE(?, 0) * 1.0 +
COALESCE(?, 0) * 2.0 +
COALESCE(?, 0) * 3.0 +
COALESCE(?, 0) * COALESCE(?, 0) * 5.0)
AND mt2.created_at > ?
)
)
";
$rank_stmt = $pdo->prepare($rank_query);
$rank_stmt->execute([
$play_count,
$like_count,
$vote_count,
$avg_rating,
$rating_count,
$play_count,
$like_count,
$vote_count,
$avg_rating,
$rating_count,
$track_created_at
]);
} else {
$rank_query = "
SELECT COUNT(*) + 1 as rank
FROM music_tracks mt2
LEFT JOIN (SELECT track_id, COUNT(*) as play_count FROM track_plays GROUP BY track_id) tp2 ON mt2.id = tp2.track_id
LEFT JOIN (SELECT track_id, COUNT(*) as like_count FROM track_likes GROUP BY track_id) tl2 ON mt2.id = tl2.track_id
LEFT JOIN (SELECT track_id, AVG(rating) as avg_rating, COUNT(*) as rating_count FROM track_ratings GROUP BY track_id) tr2 ON mt2.id = tr2.track_id
WHERE mt2.status = 'complete'
AND (
(COALESCE(tp2.play_count, 0) * 1.0 +
COALESCE(tl2.like_count, 0) * 2.0 +
COALESCE(tr2.avg_rating, 0) * COALESCE(tr2.rating_count, 0) * 5.0) >
(COALESCE(?, 0) * 1.0 +
COALESCE(?, 0) * 2.0 +
COALESCE(?, 0) * COALESCE(?, 0) * 5.0)
OR (
(COALESCE(tp2.play_count, 0) * 1.0 +
COALESCE(tl2.like_count, 0) * 2.0 +
COALESCE(tr2.avg_rating, 0) * COALESCE(tr2.rating_count, 0) * 5.0) =
(COALESCE(?, 0) * 1.0 +
COALESCE(?, 0) * 2.0 +
COALESCE(?, 0) * COALESCE(?, 0) * 5.0)
AND mt2.created_at > ?
)
)
";
$rank_stmt = $pdo->prepare($rank_query);
$rank_stmt->execute([
$play_count,
$like_count,
$avg_rating,
$rating_count,
$play_count,
$like_count,
$avg_rating,
$rating_count,
$track_created_at
]);
}
$rank = (int)$rank_stmt->fetchColumn();
// Track tied groups for logging
$score_key = round($score, 2);
if (!isset($tied_groups[$score_key])) {
$tied_groups[$score_key] = [];
}
$tied_groups[$score_key][] = [
'id' => (int)$track['id'],
'title' => $track['title'] ?: 'Untitled Track',
'rank' => $rank
];
$ranked_tracks[] = [
'rank' => $rank,
'id' => (int)$track['id'],
'title' => $track['title'] ?: 'Untitled Track',
'artist_name' => $track['artist_name'] ?: 'Unknown Artist',
'play_count' => $play_count,
'like_count' => $like_count,
'vote_count' => $vote_count,
'avg_rating' => round($avg_rating, 1),
'rating_count' => $rating_count,
'total_score' => round($score, 2)
];
}
// Log tied groups
foreach ($tied_groups as $score => $tracks_in_tie) {
if (count($tracks_in_tie) > 1) {
$track_ids = array_column($tracks_in_tie, 'id');
$titles = array_column($tracks_in_tie, 'title');
$rank = $tracks_in_tie[0]['rank'];
error_log("📊 RANKING TIE: " . count($tracks_in_tie) . " tracks tied at rank #{$rank} with score {$score} (Track IDs: " . implode(', ', $track_ids) . " - Titles: " . implode(', ', array_map(function($t) { return "'{$t}'"; }, $titles)) . ")");
}
}
// Sort by rank, then by score (descending) for consistent display
usort($ranked_tracks, function($a, $b) {
if ($a['rank'] != $b['rank']) {
return $a['rank'] - $b['rank']; // Sort by rank ascending
}
return $b['total_score'] - $a['total_score']; // For same rank, sort by score descending
});
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'
]);
}
?>