 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/domains/soundstudiopro.com/private_html/api/ |
<?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();
// OPTIMIZED: Using JOINs instead of correlated subqueries for better performance
// Get all artists with their rankings and scores
$sql = "
SELECT
u.id,
u.name as username,
u.created_at,
COALESCE(play_stats.total_plays, 0) as total_plays,
COALESCE(like_stats.total_likes, 0) as total_likes,
COALESCE(rating_stats.average_rating, 0) as average_rating,
COALESCE(rating_stats.rating_count, 0) as rating_count,
-- Calculate total score using JOIN aliases
(
COALESCE(play_stats.total_plays, 0) * 1.0 +
COALESCE(like_stats.total_likes, 0) * 2.0 +
COALESCE(rating_stats.average_rating, 0) * COALESCE(rating_stats.rating_count, 0) * 5.0
) as total_score
FROM users u
LEFT JOIN (
SELECT mt.user_id, COUNT(*) as total_plays
FROM track_plays tp
JOIN music_tracks mt ON tp.track_id = mt.id
WHERE mt.status = 'complete' AND mt.is_public = 1
GROUP BY mt.user_id
) play_stats ON u.id = play_stats.user_id
LEFT JOIN (
SELECT mt.user_id, COUNT(*) as total_likes
FROM track_likes tl
JOIN music_tracks mt ON tl.track_id = mt.id
WHERE mt.status = 'complete' AND mt.is_public = 1
GROUP BY mt.user_id
) like_stats ON u.id = like_stats.user_id
LEFT JOIN (
SELECT artist_id, AVG(rating) as average_rating, COUNT(*) as rating_count
FROM artist_ratings
GROUP BY artist_id
) rating_stats ON u.id = rating_stats.artist_id
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 - MUST match the WHERE clause above exactly
$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();
// Debug logging
error_log("get_all_artist_rankings.php: Total artists counted = " . $total_artists);
// Calculate rank for each artist (based on total_score)
// Use the same logic as calculateArtistRankings function
// IMPORTANT: Tie-breaker must match the ORDER BY clause (created_at DESC)
foreach ($artists as &$artist) {
$total_plays = (int)($artist['total_plays'] ?? 0);
$total_likes = (int)($artist['total_likes'] ?? 0);
$avg_rating = (float)($artist['average_rating'] ?? 0);
$rating_count = (int)($artist['rating_count'] ?? 0);
$artist_id = (int)$artist['id'];
$created_at = $artist['created_at'] ?? null;
$rank_sql = "
SELECT COUNT(*) + 1 as rank
FROM (
SELECT
u.id as artist_id,
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 avg_rating,
COALESCE((
SELECT COUNT(*)
FROM artist_ratings
WHERE artist_id = u.id
), 0) as rating_count
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
)
) artist_stats
WHERE (
(total_plays * 1.0 + total_likes * 2.0 + avg_rating * rating_count * 5.0) >
(? * 1.0 + ? * 2.0 + ? * ? * 5.0)
OR (
(total_plays * 1.0 + total_likes * 2.0 + avg_rating * rating_count * 5.0) =
(? * 1.0 + ? * 2.0 + ? * ? * 5.0)
AND created_at > ?
)
)
";
$rank_stmt = $pdo->prepare($rank_sql);
$rank_stmt->execute([
$total_plays, $total_likes, $avg_rating, $rating_count,
$total_plays, $total_likes, $avg_rating, $rating_count, $created_at
]);
$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()
]);
}
?>