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/28ea8325/

Upload File : current_dir [ Writeable ] document_root [ Writeable ]

<?php
session_start();
header('Content-Type: application/json');

require_once '../config/database.php';

try {
    $pdo = getDBConnection();
    
    // Get pagination parameters
    $page = max(1, intval($_GET['page'] ?? 1));
    $per_page = intval($_GET['per_page'] ?? 20);
    $offset = ($page - 1) * $per_page;
    
    // Get all public playlists with track counts and artist info
    // OPTIMIZED: Using LEFT JOINs instead of correlated subqueries for better performance
    $stmt = $pdo->prepare("
        SELECT 
            ap.id as playlist_id,
            ap.name as playlist_name,
            ap.description as playlist_description,
            ap.created_at as playlist_created,
            ap.updated_at as playlist_updated,
            u.id as artist_id,
            u.name as artist_name,
            u.profile_image,
            COUNT(DISTINCT pt.track_id) as track_count,
            COALESCE(SUM(mt.duration), 0) as total_duration,
            COALESCE(like_stats.total_likes, 0) as total_likes,
            COALESCE(play_stats.total_plays, 0) as total_plays
        FROM artist_playlists ap
        JOIN users u ON ap.user_id = u.id
        LEFT JOIN playlist_tracks pt ON ap.id = pt.playlist_id
        LEFT JOIN music_tracks mt ON pt.track_id = mt.id AND mt.status = 'complete'
        LEFT JOIN (
            SELECT pt2.playlist_id, COUNT(DISTINCT tl.track_id) as total_likes
            FROM playlist_tracks pt2
            INNER JOIN track_likes tl ON pt2.track_id = tl.track_id
            GROUP BY pt2.playlist_id
        ) like_stats ON like_stats.playlist_id = ap.id
        LEFT JOIN (
            SELECT pt3.playlist_id, COUNT(DISTINCT tp.track_id) as total_plays
            FROM playlist_tracks pt3
            INNER JOIN track_plays tp ON pt3.track_id = tp.track_id
            GROUP BY pt3.playlist_id
        ) play_stats ON play_stats.playlist_id = ap.id
        WHERE ap.is_public = TRUE
        GROUP BY ap.id, ap.name, ap.description, ap.created_at, ap.updated_at, u.id, u.name, u.profile_image, like_stats.total_likes, play_stats.total_plays
        HAVING track_count > 0
        ORDER BY ap.updated_at DESC
        LIMIT ? OFFSET ?
    ");
    
    $stmt->execute([$per_page, $offset]);
    $playlists = $stmt->fetchAll();
    
    // Get total count for pagination
    $countStmt = $pdo->prepare("
        SELECT COUNT(DISTINCT ap.id) as total
        FROM artist_playlists ap
        LEFT JOIN playlist_tracks pt ON ap.id = pt.playlist_id
        LEFT JOIN music_tracks mt ON pt.track_id = mt.id AND mt.status = 'complete'
        WHERE ap.is_public = TRUE
        GROUP BY ap.id
        HAVING COUNT(pt.track_id) > 0
    ");
    $countStmt->execute();
    $totalPlaylists = $countStmt->rowCount();
    
    // Format playlists for response
    $formattedPlaylists = [];
    foreach ($playlists as $playlist) {
        $formattedPlaylists[] = [
            'id' => $playlist['playlist_id'],
            'name' => $playlist['playlist_name'],
            'description' => $playlist['playlist_description'],
            'created_at' => $playlist['playlist_created'],
            'updated_at' => $playlist['playlist_updated'],
            'artist' => [
                'id' => $playlist['artist_id'],
                'name' => $playlist['artist_name'],
                'profile_image' => $playlist['profile_image']
            ],
            'stats' => [
                'track_count' => $playlist['track_count'],
                'total_duration' => $playlist['total_duration'],
                'total_likes' => $playlist['total_likes'],
                'total_plays' => $playlist['total_plays']
            ]
        ];
    }
    
    echo json_encode([
        'success' => true,
        'playlists' => $formattedPlaylists,
        'pagination' => [
            'page' => $page,
            'per_page' => $per_page,
            'total' => $totalPlaylists,
            'total_pages' => ceil($totalPlaylists / $per_page)
        ]
    ]);
    
} catch (Exception $e) {
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'error' => $e->getMessage()
    ]);
}
?>