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/

Upload File : current_dir [ Writeable ] document_root [ Writeable ]

<?php
header('Content-Type: application/json');
require_once __DIR__ . '/../config/database.php';

// Get pagination parameters
$page = max(1, intval($_GET['page'] ?? 1));
$per_page = intval($_GET['per_page'] ?? 20);
$offset = ($page - 1) * $per_page;

try {
    $pdo = getDBConnection();
    
    // Get featured tracks ordered by playlist_order
    // Optimized: Using LEFT JOINs instead of correlated subqueries for better performance
    $stmt = $pdo->prepare("
        SELECT 
            mt.id,
            mt.task_id,
            mt.title,
            mt.audio_url,
            mt.video_url,
            mt.prompt,
            mt.created_at,
            mt.playlist_order,
            u.name as artist_name,
            u.id as user_id,
            COALESCE(play_stats.play_count, 0) as play_count,
            COALESCE(like_stats.like_count, 0) as like_count
        FROM music_tracks mt
        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 play_stats.track_id = mt.id
        LEFT JOIN (
            SELECT track_id, COUNT(*) as like_count 
            FROM track_likes 
            GROUP BY track_id
        ) like_stats ON like_stats.track_id = mt.id
        WHERE mt.status = 'complete' 
        AND mt.audio_url IS NOT NULL 
        AND mt.is_featured = 1
        ORDER BY mt.playlist_order ASC, mt.created_at DESC
        LIMIT ? OFFSET ?
    ");
    
    $stmt->execute([$per_page, $offset]);
    $tracks = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Convert external audio URLs to proxy endpoint and decode HTML entities in titles
    foreach ($tracks as &$track) {
        // Decode HTML entities in title (e.g., &#039; → ')
        if (!empty($track['title'])) {
            $track['title'] = html_entity_decode($track['title'], ENT_QUOTES | ENT_HTML5, 'UTF-8');
        }
        if (!empty($track['artist_name'])) {
            $track['artist_name'] = html_entity_decode($track['artist_name'], ENT_QUOTES | ENT_HTML5, 'UTF-8');
        }
        
        $audioUrl = $track['audio_url'] ?? '';
        $taskId = $track['task_id'] ?? '';
        
        // If URL is external (API.box, http/https), convert to proxy
        if (!empty($audioUrl)) {
            if (strpos($audioUrl, 'http') === 0 || 
                strpos($audioUrl, 'api.box') !== false || 
                strpos($audioUrl, 'apiboxfiles.erweima.ai') !== false) {
                // Only convert if we have task_id
                if (!empty($taskId)) {
                    // Convert to proxy endpoint
                    $track['audio_url'] = '/utils/audiofiles.php?id=' . urlencode($taskId);
                } else {
                    // Try to extract task_id from URL or use track ID as fallback
                    // For now, keep original URL but log the issue
                    error_log("Track {$track['id']} ({$track['title']}) has external URL but no task_id");
                }
            }
            // If it's already a local path or proxy endpoint, keep it as is
        }
    }
    unset($track); // Break reference
    
    // Get total count for pagination
    $count_stmt = $pdo->prepare("
        SELECT COUNT(*) as total
        FROM music_tracks mt
        WHERE mt.status = 'complete' 
        AND mt.audio_url IS NOT NULL 
        AND mt.is_featured = 1
    ");
    $count_stmt->execute();
    $total_count = $count_stmt->fetch(PDO::FETCH_ASSOC)['total'];
    
    // Format the response
    $response = [
        'success' => true,
        'tracks' => $tracks,
        'pagination' => [
            'page' => $page,
            'per_page' => $per_page,
            'total' => $total_count,
            'total_pages' => ceil($total_count / $per_page)
        ],
        'playlist_info' => [
            'name' => 'Featured Tracks',
            'description' => 'Curated showcase tracks from the SoundStudioPro community',
            'type' => 'featured'
        ]
    ];
    
    echo json_encode($response);

} catch (Exception $e) {
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'error' => 'Failed to load featured tracks',
        'message' => $e->getMessage()
    ]);
}
?>