 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/ |
<?php
/**
* Audit Track Flow - A to Z
* Complete audit of the track creation to completion flow
*/
require_once 'config/database.php';
$pdo = getDBConnection();
if (!$pdo) {
die("Database connection failed");
}
echo "<h1>🔍 Complete Track Flow Audit</h1>";
echo "<style>
.section { border: 2px solid #667eea; padding: 20px; margin: 20px 0; background: #1a1a2e; }
.good { color: #10b981; }
.bad { color: #ef4444; }
.warning { color: #f59e0b; }
table { width: 100%; border-collapse: collapse; margin: 10px 0; }
th, td { padding: 10px; border: 1px solid #333; text-align: left; }
th { background: #667eea; color: white; }
.flow-step { margin: 15px 0; padding: 15px; background: #2d2d44; border-left: 4px solid #667eea; }
</style>";
// Step 1: Check recent processing tracks
echo "<div class='section'>";
echo "<h2>Step 1: Recent Processing Tracks</h2>";
$stmt = $pdo->prepare("
SELECT id, task_id, title, status, created_at, updated_at,
TIMESTAMPDIFF(MINUTE, created_at, NOW()) as minutes_old
FROM music_tracks
WHERE status = 'processing'
ORDER BY created_at DESC
LIMIT 20
");
$stmt->execute();
$processingTracks = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "<p>Found " . count($processingTracks) . " tracks in 'processing' status</p>";
if (!empty($processingTracks)) {
echo "<table>";
echo "<tr><th>ID</th><th>Task ID</th><th>Title</th><th>Created</th><th>Age (min)</th><th>Has Valid Task ID</th></tr>";
foreach ($processingTracks as $track) {
$hasValidTaskId = !empty($track['task_id']) &&
$track['task_id'] != 'unknown' &&
!str_starts_with($track['task_id'], 'temp_') &&
!str_starts_with($track['task_id'], 'retry_');
echo "<tr>";
echo "<td>{$track['id']}</td>";
echo "<td>" . htmlspecialchars($track['task_id'] ?? 'NULL') . "</td>";
echo "<td>" . htmlspecialchars($track['title'] ?? 'Untitled') . "</td>";
echo "<td>{$track['created_at']}</td>";
echo "<td>{$track['minutes_old']}</td>";
echo "<td class='" . ($hasValidTaskId ? 'good' : 'bad') . "'>" . ($hasValidTaskId ? '✅ YES' : '❌ NO') . "</td>";
echo "</tr>";
}
echo "</table>";
}
echo "</div>";
// Step 2: Check callback logs for recent activity
echo "<div class='section'>";
echo "<h2>Step 2: Recent Callback Activity</h2>";
$logFile = 'callback_log.txt';
if (file_exists($logFile)) {
$logContent = file_get_contents($logFile);
$logLines = explode("\n", $logContent);
$recentLogs = array_slice($logLines, -50); // Last 50 lines
echo "<p>Last 50 lines from callback_log.txt:</p>";
echo "<pre style='background: #1a1a2e; padding: 15px; overflow-x: auto; max-height: 400px;'>";
echo htmlspecialchars(implode("\n", $recentLogs));
echo "</pre>";
// Count recent callbacks
$callbackCount = 0;
$completeCount = 0;
$failedCount = 0;
foreach ($recentLogs as $line) {
if (strpos($line, 'Callback received') !== false) $callbackCount++;
if (strpos($line, 'status=complete') !== false || strpos($line, 'status: complete') !== false) $completeCount++;
if (strpos($line, 'status=failed') !== false || strpos($line, 'status: failed') !== false) $failedCount++;
}
echo "<p><strong>Recent Activity:</strong></p>";
echo "<ul>";
echo "<li>Callbacks received: {$callbackCount}</li>";
echo "<li>Completed: {$completeCount}</li>";
echo "<li>Failed: {$failedCount}</li>";
echo "</ul>";
} else {
echo "<p class='bad'>❌ callback_log.txt not found</p>";
}
echo "</div>";
// Step 3: Check tracks that should have completed
echo "<div class='section'>";
echo "<h2>Step 3: Tracks That Should Have Completed</h2>";
$stmt = $pdo->prepare("
SELECT mt.id, mt.task_id, mt.title, mt.created_at,
TIMESTAMPDIFF(MINUTE, mt.created_at, NOW()) as minutes_old,
CASE
WHEN fr.id IS NOT NULL THEN 'YES'
ELSE 'NO'
END as has_task_result
FROM music_tracks mt
LEFT JOIN (
SELECT DISTINCT
SUBSTRING_INDEX(SUBSTRING_INDEX(value, '\"task_id\":\"', -1), '\"', 1) as task_id
FROM (
SELECT CONCAT('{\"task_id\":\"', task_id, '\"}') as value
FROM music_tracks
WHERE task_id IS NOT NULL
) t
) tr ON mt.task_id = tr.task_id
LEFT JOIN (
SELECT DISTINCT
REPLACE(REPLACE(name, 'task_results/', ''), '.json', '') as task_id
FROM (
SELECT 'task_results/' || name as name
FROM (
SELECT '65721f17febedc0b5f3e1b89b9099444.json' as name
) files
) f
) fr ON mt.task_id = fr.task_id
WHERE mt.status = 'processing'
AND mt.task_id IS NOT NULL
AND mt.task_id != ''
AND mt.task_id != 'unknown'
AND mt.task_id NOT LIKE 'temp_%'
AND mt.task_id NOT LIKE 'retry_%'
AND TIMESTAMPDIFF(MINUTE, mt.created_at, NOW()) > 5
ORDER BY mt.created_at DESC
LIMIT 20
");
// Better query - check if task_results file exists
$stmt = $pdo->prepare("
SELECT id, task_id, title, created_at,
TIMESTAMPDIFF(MINUTE, created_at, NOW()) as minutes_old
FROM music_tracks
WHERE status = 'processing'
AND task_id IS NOT NULL
AND task_id != ''
AND task_id != 'unknown'
AND task_id NOT LIKE 'temp_%'
AND task_id NOT LIKE 'retry_%'
AND TIMESTAMPDIFF(MINUTE, created_at, NOW()) > 5
ORDER BY created_at DESC
LIMIT 20
");
$stmt->execute();
$oldProcessingTracks = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "<p>Found " . count($oldProcessingTracks) . " tracks processing for > 5 minutes</p>";
if (!empty($oldProcessingTracks)) {
echo "<table>";
echo "<tr><th>ID</th><th>Task ID</th><th>Title</th><th>Age (min)</th><th>Task Results File</th><th>Local Audio</th><th>API Status</th></tr>";
foreach ($oldProcessingTracks as $track) {
$taskId = $track['task_id'];
// Check task_results
$taskResultFile = "task_results/{$taskId}.json";
$hasTaskResult = file_exists($taskResultFile);
// Check local audio
$audioExtensions = ['mp3', 'wav', 'm4a', 'ogg'];
$hasLocalAudio = false;
foreach ($audioExtensions as $ext) {
if (file_exists("audio_files/{$taskId}.{$ext}")) {
$hasLocalAudio = true;
break;
}
}
// Check API status
$apiStatus = 'Not Checked';
$apiKey = '63edba40620216c5aa2c04240ac41dbd';
$apiUrl = "https://api.api.box/api/v1/status/{$taskId}";
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $apiUrl);
curl_setopt($ch, CURLOPT_HTTPGET, true);
curl_setopt($ch, CURLOPT_HTTPHEADER, [
'Authorization: Bearer ' . $apiKey,
'Content-Type: application/json'
]);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_TIMEOUT, 5);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
$response = curl_exec($ch);
$httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close($ch);
if ($httpCode === 200 && $response) {
$apiData = json_decode($response, true);
if ($apiData) {
$apiStatus = $apiData['status'] ?? ($apiData['data']['status'] ?? 'unknown');
} else {
$apiStatus = "HTTP {$httpCode} (Invalid JSON)";
}
} else {
$apiStatus = "HTTP {$httpCode}";
}
echo "<tr>";
echo "<td>{$track['id']}</td>";
echo "<td>" . htmlspecialchars($taskId) . "</td>";
echo "<td>" . htmlspecialchars($track['title'] ?? 'Untitled') . "</td>";
echo "<td>{$track['minutes_old']}</td>";
echo "<td class='" . ($hasTaskResult ? 'good' : 'bad') . "'>" . ($hasTaskResult ? '✅ YES' : '❌ NO') . "</td>";
echo "<td class='" . ($hasLocalAudio ? 'good' : 'bad') . "'>" . ($hasLocalAudio ? '✅ YES' : '❌ NO') . "</td>";
echo "<td>{$apiStatus}</td>";
echo "</tr>";
}
echo "</table>";
}
echo "</div>";
// Step 4: Check callback.php functionality
echo "<div class='section'>";
echo "<h2>Step 4: Callback Processing Flow</h2>";
echo "<div class='flow-step'>";
echo "<h3>4.1: Callback File Check</h3>";
if (file_exists('callback.php')) {
echo "<p class='good'>✅ callback.php exists</p>";
// Check if callback has the fix for empty strings
$callbackContent = file_get_contents('callback.php');
if (strpos($callbackContent, '!empty($audio[\'audio_url\'])') !== false) {
echo "<p class='good'>✅ Callback has empty string check fix</p>";
} else {
echo "<p class='bad'>❌ Callback missing empty string check fix</p>";
}
if (strpos($callbackContent, 'callbackType === \'complete\'') !== false) {
echo "<p class='good'>✅ Callback handles complete callbacks</p>";
} else {
echo "<p class='bad'>❌ Callback may not handle complete callbacks properly</p>";
}
} else {
echo "<p class='bad'>❌ callback.php not found</p>";
}
echo "</div>";
echo "<div class='flow-step'>";
echo "<h3>4.2: updateMusicTrack Function</h3>";
if (function_exists('updateMusicTrack')) {
echo "<p class='good'>✅ updateMusicTrack function exists</p>";
} else {
echo "<p class='bad'>❌ updateMusicTrack function not found</p>";
}
echo "</div>";
echo "<div class='flow-step'>";
echo "<h3>4.3: Callback URL Configuration</h3>";
// Check where callbacks are configured
$createFiles = glob('create_*.php');
$studioFiles = glob('studio/create_*.php');
$allCreateFiles = array_merge($createFiles, $studioFiles);
$callbackUrl = 'https://soundstudiopro.com/callback.php';
$foundCallbackUrl = false;
foreach ($allCreateFiles as $file) {
$content = file_get_contents($file);
if (strpos($content, $callbackUrl) !== false || strpos($content, 'callback.php') !== false) {
$foundCallbackUrl = true;
echo "<p class='good'>✅ Found callback URL in: " . basename($file) . "</p>";
break;
}
}
if (!$foundCallbackUrl) {
echo "<p class='warning'>⚠️ Could not verify callback URL configuration</p>";
}
echo "</div>";
echo "</div>";
// Step 5: Check for common issues
echo "<div class='section'>";
echo "<h2>Step 5: Common Issues Check</h2>";
echo "<div class='flow-step'>";
echo "<h3>5.1: Tracks with task_id but no callback received</h3>";
$stmt = $pdo->prepare("
SELECT COUNT(*) as count
FROM music_tracks
WHERE status = 'processing'
AND task_id IS NOT NULL
AND task_id != ''
AND task_id != 'unknown'
AND task_id NOT LIKE 'temp_%'
AND TIMESTAMPDIFF(HOUR, created_at, NOW()) > 1
");
$stmt->execute();
$stuckCount = $stmt->fetchColumn();
echo "<p>Stuck tracks (>1 hour): <strong>{$stuckCount}</strong></p>";
echo "</div>";
echo "<div class='flow-step'>";
echo "<h3>5.2: Tracks with task_results but still processing</h3>";
$stmt = $pdo->prepare("
SELECT id, task_id, title
FROM music_tracks
WHERE status = 'processing'
AND task_id IS NOT NULL
LIMIT 10
");
$stmt->execute();
$tracksWithTaskId = $stmt->fetchAll(PDO::FETCH_ASSOC);
$hasTaskResultsButProcessing = 0;
foreach ($tracksWithTaskId as $track) {
$taskResultFile = "task_results/{$track['task_id']}.json";
if (file_exists($taskResultFile)) {
$hasTaskResultsButProcessing++;
}
}
echo "<p>Tracks with task_results but still processing: <strong>{$hasTaskResultsButProcessing}</strong></p>";
if ($hasTaskResultsButProcessing > 0) {
echo "<p class='bad'>❌ These tracks have callback data but weren't updated to complete!</p>";
}
echo "</div>";
echo "<div class='flow-step'>";
echo "<h3>5.3: Database Connection Issues</h3>";
if ($pdo) {
echo "<p class='good'>✅ Database connection working</p>";
// Test updateMusicTrack
$testStmt = $pdo->prepare("SELECT COUNT(*) FROM music_tracks WHERE status = 'processing'");
$testStmt->execute();
$testResult = $testStmt->fetchColumn();
echo "<p>Test query successful: Found {$testResult} processing tracks</p>";
} else {
echo "<p class='bad'>❌ Database connection failed</p>";
}
echo "</div>";
echo "</div>";
// Step 6: Test updateMusicTrack function
echo "<div class='section'>";
echo "<h2>Step 6: Test updateMusicTrack Function</h2>";
// Test with a real processing track
$testStmt = $pdo->prepare("
SELECT id, task_id, title, status
FROM music_tracks
WHERE status = 'processing'
AND task_id IS NOT NULL
AND task_id != ''
AND task_id != 'unknown'
LIMIT 1
");
$testStmt->execute();
$testTrack = $testStmt->fetch(PDO::FETCH_ASSOC);
if ($testTrack) {
echo "<p>Testing updateMusicTrack with track #{$testTrack['id']} (task_id: {$testTrack['task_id']})</p>";
// Try to update status (dry run - don't actually change it)
$testTaskId = $testTrack['task_id'];
$testStatus = 'processing'; // Keep same status for test
// Check if track exists
$checkStmt = $pdo->prepare("SELECT id, status FROM music_tracks WHERE task_id = ?");
$checkStmt->execute([$testTaskId]);
$exists = $checkStmt->fetch(PDO::FETCH_ASSOC);
if ($exists) {
echo "<p class='good'>✅ Track exists with task_id: {$testTaskId}</p>";
// Test SQL query construction
$testUpdates = ['status = ?'];
$testParams = [$testStatus];
$testParams[] = $testTaskId;
$testSql = "UPDATE music_tracks SET " . implode(', ', $testUpdates) . " WHERE task_id = ?";
echo "<p>Test SQL: <code>" . htmlspecialchars($testSql) . "</code></p>";
echo "<p>Test Params: " . htmlspecialchars(json_encode($testParams)) . "</p>";
// Try to prepare the statement
try {
$prepStmt = $pdo->prepare($testSql);
echo "<p class='good'>✅ SQL statement prepared successfully</p>";
// Don't actually execute - just test
echo "<p class='warning'>⚠️ Not executing (dry run test)</p>";
} catch (Exception $e) {
echo "<p class='bad'>❌ SQL preparation failed: " . htmlspecialchars($e->getMessage()) . "</p>";
}
} else {
echo "<p class='bad'>❌ Track not found with task_id: {$testTaskId}</p>";
}
} else {
echo "<p>No processing tracks found to test with</p>";
}
echo "</div>";
// Step 7: Check callback processing errors
echo "<div class='section'>";
echo "<h2>Step 7: Recent Callback Processing Errors</h2>";
$errorPatterns = [
'Failed to update track status' => 'updateMusicTrack failures',
'Track not found with task_id' => 'task_id mismatches',
'updateMusicTrack returned false' => 'updateMusicTrack returning false',
'CRITICAL' => 'Critical errors'
];
$logFile = 'callback_log.txt';
if (file_exists($logFile)) {
$logContent = file_get_contents($logFile);
$logLines = explode("\n", $logContent);
echo "<table>";
echo "<tr><th>Error Type</th><th>Count (Last 1000 lines)</th></tr>";
$recentLines = array_slice($logLines, -1000);
foreach ($errorPatterns as $pattern => $label) {
$count = 0;
foreach ($recentLines as $line) {
if (stripos($line, $pattern) !== false) {
$count++;
}
}
$color = $count > 0 ? 'bad' : 'good';
echo "<tr><td>{$label}</td><td class='{$color}'>" . ($count > 0 ? "❌ {$count}" : "✅ 0") . "</td></tr>";
}
echo "</table>";
// Show recent critical errors
echo "<h3>Recent Critical Errors (Last 20):</h3>";
$criticalErrors = [];
foreach (array_reverse($recentLines) as $line) {
if (stripos($line, 'CRITICAL') !== false || stripos($line, 'Failed to update') !== false) {
$criticalErrors[] = $line;
if (count($criticalErrors) >= 20) break;
}
}
if (!empty($criticalErrors)) {
echo "<pre style='background: #1a1a2e; padding: 15px; overflow-x: auto; max-height: 300px; color: #ef4444;'>";
echo htmlspecialchars(implode("\n", array_reverse($criticalErrors)));
echo "</pre>";
} else {
echo "<p class='good'>✅ No critical errors in recent logs</p>";
}
}
echo "</div>";
// Step 8: Complete Flow Diagram
echo "<div class='section'>";
echo "<h2>Step 8: Complete Flow Analysis</h2>";
echo "<div class='flow-step'>";
echo "<h3>Flow Step 1: Track Creation</h3>";
echo "<p>✅ Track created with status='processing' and temp_task_id</p>";
echo "<p>✅ Real task_id updated after API call</p>";
echo "<p>✅ Callback URL set to: <code>https://soundstudiopro.com/callback.php</code></p>";
echo "</div>";
echo "<div class='flow-step'>";
echo "<h3>Flow Step 2: API Processing</h3>";
echo "<p>API processes the track and sends callbacks to callback.php</p>";
echo "</div>";
echo "<div class='flow-step'>";
echo "<h3>Flow Step 3: Callback Reception</h3>";
$callbackAccessible = @file_get_contents('https://soundstudiopro.com/callback.php', false, stream_context_create([
'http' => ['timeout' => 5, 'method' => 'GET']
]));
if ($callbackAccessible !== false) {
echo "<p class='good'>✅ callback.php is accessible</p>";
} else {
echo "<p class='bad'>❌ callback.php may not be accessible</p>";
}
echo "</div>";
echo "<div class='flow-step'>";
echo "<h3>Flow Step 4: Callback Processing</h3>";
echo "<p>Callback should:</p>";
echo "<ul>";
echo "<li>Parse JSON data</li>";
echo "<li>Extract audio URLs, metadata, lyrics</li>";
echo "<li>Download files locally</li>";
echo "<li>Call updateMusicTrack() to update status to 'complete'</li>";
echo "</ul>";
echo "</div>";
echo "<div class='flow-step'>";
echo "<h3>Flow Step 5: Database Update</h3>";
echo "<p>updateMusicTrack() should update:</p>";
echo "<ul>";
echo "<li>status = 'complete'</li>";
echo "<li>audio_url</li>";
echo "<li>metadata</li>";
echo "<li>variations_count</li>";
echo "</ul>";
echo "</div>";
echo "</div>";
// Step 9: Recommendations
echo "<div class='section'>";
echo "<h2>Step 9: Recommendations & Fixes</h2>";
echo "<ul>";
echo "<li><strong>Run Fix Script:</strong> <a href='fix_stuck_processing_tracks.php'>fix_stuck_processing_tracks.php</a> to process stuck tracks</li>";
echo "<li><strong>Check Callback Logs:</strong> Review callback_log.txt for specific errors</li>";
echo "<li><strong>Verify Callback URL:</strong> Ensure callback.php is accessible externally</li>";
echo "<li><strong>Monitor Failed Updates:</strong> Check why updateMusicTrack is failing</li>";
echo "<li><strong>Test Callback:</strong> Manually test callback.php with sample data</li>";
echo "</ul>";
echo "</div>";
echo "<hr>";
echo "<p><a href='fix_stuck_processing_tracks.php'>Fix Stuck Tracks</a> | <a href='library.php'>Library</a> | <a href='admin.php'>Admin</a></p>";