 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/public_html/ |
<?php
session_start();
require_once 'config/database.php';
header('Content-Type: application/json');
$pdo = getDBConnection();
if (!$pdo) {
echo json_encode(['success' => false, 'message' => 'Database connection failed']);
exit;
}
// Get parameters
$page = max(1, intval($_GET['page'] ?? 1));
$per_page = 20;
$offset = ($page - 1) * $per_page;
$sort_filter = $_GET['sort'] ?? 'latest';
$time_filter = $_GET['time'] ?? 'all';
$genre_filter = $_GET['genre'] ?? '';
$user_id = $_SESSION['user_id'] ?? null;
// Build time filter condition
$time_condition = '';
switch ($time_filter) {
case 'today':
$time_condition = 'AND mt.created_at >= CURDATE()';
break;
case 'week':
$time_condition = 'AND mt.created_at >= DATE_SUB(NOW(), INTERVAL 1 WEEK)';
break;
case 'month':
$time_condition = 'AND mt.created_at >= DATE_SUB(NOW(), INTERVAL 1 MONTH)';
break;
}
// Build genre filter condition
$genre_condition = '';
$genre_params = [];
if (!empty($genre_filter)) {
$genre_condition = "AND (JSON_UNQUOTE(JSON_EXTRACT(mt.metadata, '$.genre')) = ? OR JSON_CONTAINS(mt.metadata, JSON_QUOTE(?), '$.genres'))";
$genre_params = [$genre_filter, $genre_filter];
}
// Simple sort order function
function getSortOrder($sort_filter) {
switch ($sort_filter) {
case 'latest':
return "mt.created_at DESC";
case 'popular':
// OPTIMIZED: Use JOIN alias instead of subquery
return "COALESCE(like_stats.like_count, 0) DESC, mt.created_at DESC";
default:
return "mt.created_at DESC";
}
}
try {
// Get tracks with social data
// Parameters: is_following user_id, user_like_stats.user_id (2 total if user_id exists)
$query_params = [];
if ($user_id) {
$query_params[] = $user_id; // is_following
$query_params[] = $user_id; // user_like_stats
}
if (!empty($genre_filter)) {
$query_params = array_merge($query_params, $genre_params);
}
// OPTIMIZED: Using JOINs instead of correlated subqueries for better performance
$stmt = $pdo->prepare("
SELECT
mt.id,
mt.title,
mt.prompt,
mt.audio_url,
mt.duration,
mt.created_at,
mt.user_id,
mt.price,
mt.metadata,
u.name as artist_name,
u.profile_image,
u.plan,
COALESCE(like_stats.like_count, 0) as like_count,
COALESCE(comment_stats.comment_count, 0) as comment_count,
COALESCE(share_stats.share_count, 0) as share_count,
COALESCE(play_stats.play_count, 0) as play_count,
COALESCE(view_stats.view_count, 0) as view_count,
" . ($user_id ? "COALESCE((SELECT COUNT(*) FROM user_follows WHERE follower_id = ? AND following_id = mt.user_id), 0) as is_following," : "0 as is_following,") . "
" . ($user_id ? "CASE WHEN user_like_stats.track_id IS NOT NULL THEN 1 ELSE 0 END as user_liked," : "0 as user_liked,") . "
COALESCE(artist_stats.total_tracks, 0) as artist_total_tracks
FROM music_tracks mt
JOIN users u ON mt.user_id = u.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, COUNT(*) as comment_count FROM track_comments GROUP BY track_id) comment_stats ON mt.id = comment_stats.track_id
LEFT JOIN (SELECT track_id, COUNT(*) as share_count FROM track_shares GROUP BY track_id) share_stats ON mt.id = share_stats.track_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 view_count FROM track_views GROUP BY track_id) view_stats ON mt.id = view_stats.track_id
LEFT JOIN (SELECT user_id, COUNT(*) as total_tracks FROM music_tracks WHERE status = 'complete' GROUP BY user_id) artist_stats ON mt.user_id = artist_stats.user_id" .
($user_id ? "\n LEFT JOIN (SELECT track_id FROM track_likes WHERE user_id = ?) user_like_stats ON mt.id = user_like_stats.track_id" : "") . "
WHERE mt.status = 'complete'
AND mt.audio_url IS NOT NULL
AND mt.audio_url != ''
AND mt.is_public = TRUE
$time_condition
$genre_condition
ORDER BY " . getSortOrder($sort_filter) . "
LIMIT ? OFFSET ?
");
// Add LIMIT and OFFSET parameters
$query_params[] = $per_page;
$query_params[] = $offset;
$stmt->execute($query_params);
$tracks = $stmt->fetchAll();
// Process tracks for JSON response
$processed_tracks = [];
foreach ($tracks as $track) {
// Parse metadata
$metadata = json_decode($track['metadata'] ?? '{}', true) ?: [];
// Extract genre
$genre = $metadata['genre'] ?? 'Unknown';
if (is_array($genre)) {
$genre = $genre[0] ?? 'Unknown';
}
// Extract BPM, key, mood, energy
$bpm = $metadata['bpm'] ?? '120';
$key = $metadata['key'] ?? 'C major';
$mood = $metadata['mood'] ?? 'Neutral';
$energy = $metadata['energy'] ?? 'Medium';
// Format duration
$duration = $track['duration'] ?? 0;
$minutes = floor($duration / 60);
$seconds = $duration % 60;
$formatted_duration = sprintf('%d:%02d', $minutes, $seconds);
// Format price
$price = $track['price'] ? '$' . number_format($track['price'], 2) : 'Free';
// Get artist initial
$artist_initial = strtoupper(substr($track['artist_name'], 0, 1));
$processed_tracks[] = [
'id' => $track['id'],
'title' => $track['title'],
'artist_name' => $track['artist_name'],
'artist_initial' => $artist_initial,
'audio_url' => $track['audio_url'],
'duration' => $formatted_duration,
'price' => $price,
'genre' => $genre,
'bpm' => $bpm,
'key' => $key,
'mood' => $mood,
'energy' => $energy,
'like_count' => $track['like_count'],
'comment_count' => $track['comment_count'],
'share_count' => $track['share_count'],
'play_count' => $track['play_count'],
'view_count' => $track['view_count'],
'user_liked' => $track['user_liked'],
'is_following' => $track['is_following'],
'artist_total_tracks' => $track['artist_total_tracks'],
'created_at' => $track['created_at']
];
}
echo json_encode([
'success' => true,
'tracks' => $processed_tracks,
'page' => $page,
'has_more' => count($processed_tracks) === $per_page
]);
} catch (Exception $e) {
error_log("API Load Tracks Error: " . $e->getMessage());
echo json_encode([
'success' => false,
'message' => 'Failed to load tracks: ' . $e->getMessage()
]);
}
?>