 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
$ranked_tracks = [];
$current_rank = $offset + 1;
$previous_score = null;
$tied_tracks = []; // Track tied tracks for logging
foreach ($tracks as $track) {
$score = (float)$track['total_score'];
// Handle ties - if score matches previous (within 0.01), use same rank
// Otherwise, move to next rank
if ($previous_score !== null && abs($score - $previous_score) < 0.01) {
// This is a tie - use the same rank as previous track
// Don't increment current_rank
$tied_tracks[] = [
'track_id' => (int)$track['id'],
'title' => $track['title'] ?: 'Untitled Track',
'score' => round($score, 2),
'rank' => $current_rank
];
// Log tie for audit
error_log("🔗 RANKING TIE DETECTED: Track ID {$track['id']} ('{$track['title']}') tied at rank #{$current_rank} with score " . round($score, 2));
} else {
// New score (not a tie) - move to next rank
// If we had ties before, we need to skip ranks
if ($previous_score !== null) {
$current_rank++;
}
// Log when we move past ties
if (count($tied_tracks) > 1) {
$tied_ids = array_column($tied_tracks, 'track_id');
error_log("📊 RANKING: " . count($tied_tracks) . " tracks tied at rank #" . ($current_rank - 1) . " (Track IDs: " . implode(', ', $tied_ids) . ")");
$tied_tracks = [];
}
}
$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)
];
// Track this for tie detection
if ($previous_score === null || abs($score - $previous_score) < 0.01) {
$tied_tracks[] = [
'track_id' => (int)$track['id'],
'title' => $track['title'] ?: 'Untitled Track',
'score' => round($score, 2),
'rank' => $current_rank
];
}
$previous_score = $score;
}
// Log final ties if any
if (count($tied_tracks) > 1) {
$tied_ids = array_column($tied_tracks, 'track_id');
error_log("📊 RANKING: " . count($tied_tracks) . " tracks tied at rank #" . $current_rank . " (Track IDs: " . implode(', ', $tied_ids) . ")");
}
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'
]);
}
?>