 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/admin_includes/ |
<?php
// Variations Diagnostic Tool - Deep Database Analysis
// This tool checks the actual database state for a specific track
$track_id = $_GET['track_id'] ?? 313; // Default to track 313 (Feu Sacré)
$action = $_GET['action'] ?? 'analyze';
$diagnostic_data = [];
if ($action === 'analyze' && $track_id) {
try {
// Get track info
$track_stmt = $pdo->prepare("
SELECT
id,
title,
task_id,
variations_count,
status,
created_at,
audio_url
FROM music_tracks
WHERE id = ?
");
$track_stmt->execute([$track_id]);
$track = $track_stmt->fetch(PDO::FETCH_ASSOC);
if (!$track) {
$diagnostic_data['error'] = "Track #$track_id not found in database";
} else {
$diagnostic_data['track'] = $track;
// Get ALL variations from database
$var_stmt = $pdo->prepare("
SELECT
id,
variation_index,
audio_url,
duration,
title,
tags,
image_url,
source_audio_url,
stream_audio_url,
created_at,
metadata
FROM audio_variations
WHERE track_id = ?
ORDER BY variation_index ASC
");
$var_stmt->execute([$track_id]);
$variations = $var_stmt->fetchAll(PDO::FETCH_ASSOC);
$diagnostic_data['variations_in_db'] = $variations;
$diagnostic_data['variations_count_in_db'] = count($variations);
// Check if variations_count matches
$diagnostic_data['count_match'] = $track['variations_count'] == count($variations);
// If we have task_id, try to fetch from api.box
if ($track['task_id']) {
$api_key = '63edba40620216c5aa2c04240ac41dbd';
$api_url = "https://api.api.box/api/v1/status/{$track['task_id']}";
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $api_url);
curl_setopt($ch, CURLOPT_HTTPGET, true);
curl_setopt($ch, CURLOPT_HTTPHEADER, [
'Authorization: Bearer ' . $api_key,
'Content-Type: application/json',
'User-Agent: SoundStudioPro-Diagnostic/1.0'
]);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_TIMEOUT, 30);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
$response = curl_exec($ch);
$http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close($ch);
if ($http_code === 200) {
$api_data = json_decode($response, true);
// Extract variations from api.box response
$api_variations = [];
if (isset($api_data['data']['data']) && is_array($api_data['data']['data'])) {
$api_variations = $api_data['data']['data'];
} elseif (isset($api_data['data']) && is_array($api_data['data'])) {
$api_variations = $api_data['data'];
} elseif (isset($api_data['variations']) && is_array($api_data['variations'])) {
$api_variations = $api_data['variations'];
}
$diagnostic_data['api_box'] = [
'success' => true,
'variations_count' => count($api_variations),
'variations' => $api_variations,
'raw_response' => $api_data
];
} else {
$diagnostic_data['api_box'] = [
'success' => false,
'error' => "HTTP $http_code",
'response' => substr($response, 0, 500)
];
}
}
// Check callback logs if available
$log_file = "callback_log.txt";
if (file_exists($log_file)) {
$log_content = file_get_contents($log_file);
$task_id_pattern = preg_quote($track['task_id'] ?? '', '/');
if ($task_id_pattern) {
// Find relevant log entries
preg_match_all("/.*task.*{$task_id_pattern}.*variation.*/i", $log_content, $matches);
$diagnostic_data['callback_logs'] = array_slice($matches[0], -20); // Last 20 matches
}
}
}
} catch (Exception $e) {
$diagnostic_data['error'] = "Database error: " . $e->getMessage();
}
}
?>
<!-- Variations Diagnostic Tool -->
<div class="section-header">
<h2><i class="fas fa-microscope"></i> Variations Diagnostic Tool</h2>
<p>Deep analysis of track variations in database vs API.Box source</p>
</div>
<!-- Track Selection -->
<div style="background: rgba(255, 255, 255, 0.05); border-radius: 12px; padding: 1.5rem; margin-bottom: 2rem;">
<form method="get" style="display: flex; gap: 1rem; align-items: center;">
<input type="hidden" name="tab" value="variations-diagnostic">
<input type="hidden" name="action" value="analyze">
<label style="color: white; font-weight: 600;">Track ID:</label>
<input type="number" name="track_id" value="<?= htmlspecialchars($track_id) ?>" style="padding: 0.5rem; border-radius: 8px; border: 1px solid rgba(255, 255, 255, 0.2); background: rgba(255, 255, 255, 0.1); color: white; width: 100px;">
<button type="submit" class="btn btn-primary">
<i class="fas fa-search"></i> Analyze
</button>
</form>
</div>
<?php if (!empty($diagnostic_data)): ?>
<?php if (isset($diagnostic_data['error'])): ?>
<div style="background: rgba(229, 62, 62, 0.2); border: 1px solid rgba(229, 62, 62, 0.3); border-radius: 12px; padding: 1.5rem; color: #fc8181;">
<h3 style="margin-top: 0; color: #e53e3e;"><i class="fas fa-exclamation-triangle"></i> Error</h3>
<p><?= htmlspecialchars($diagnostic_data['error']) ?></p>
</div>
<?php else: ?>
<!-- Track Info -->
<div style="background: rgba(102, 126, 234, 0.2); border: 1px solid rgba(102, 126, 234, 0.3); border-radius: 12px; padding: 1.5rem; margin-bottom: 2rem;">
<h3 style="margin-top: 0; color: white;"><i class="fas fa-music"></i> Track Information</h3>
<table style="width: 100%; color: white; border-collapse: collapse;">
<tr>
<td style="padding: 0.5rem; border-bottom: 1px solid rgba(255, 255, 255, 0.1);"><strong>ID:</strong></td>
<td style="padding: 0.5rem; border-bottom: 1px solid rgba(255, 255, 255, 0.1);">#<?= $diagnostic_data['track']['id'] ?></td>
</tr>
<tr>
<td style="padding: 0.5rem; border-bottom: 1px solid rgba(255, 255, 255, 0.1);"><strong>Title:</strong></td>
<td style="padding: 0.5rem; border-bottom: 1px solid rgba(255, 255, 255, 0.1);"><?= htmlspecialchars($diagnostic_data['track']['title']) ?></td>
</tr>
<tr>
<td style="padding: 0.5rem; border-bottom: 1px solid rgba(255, 255, 255, 0.1);"><strong>Task ID:</strong></td>
<td style="padding: 0.5rem; border-bottom: 1px solid rgba(255, 255, 255, 0.1);"><?= htmlspecialchars($diagnostic_data['track']['task_id'] ?? 'N/A') ?></td>
</tr>
<tr>
<td style="padding: 0.5rem; border-bottom: 1px solid rgba(255, 255, 255, 0.1);"><strong>variations_count (DB):</strong></td>
<td style="padding: 0.5rem; border-bottom: 1px solid rgba(255, 255, 255, 0.1);">
<span style="color: <?= $diagnostic_data['count_match'] ? '#48bb78' : '#f59e0b' ?>; font-weight: 600;">
<?= $diagnostic_data['track']['variations_count'] ?>
<?php if (!$diagnostic_data['count_match']): ?>
<span style="color: #f59e0b;">⚠️ Mismatch!</span>
<?php endif; ?>
</span>
</td>
</tr>
<tr>
<td style="padding: 0.5rem;"><strong>Actual Variations in DB:</strong></td>
<td style="padding: 0.5rem;">
<span style="color: <?= $diagnostic_data['count_match'] ? '#48bb78' : '#f59e0b' ?>; font-weight: 600;">
<?= $diagnostic_data['variations_count_in_db'] ?>
</span>
</td>
</tr>
</table>
</div>
<!-- Variations in Database -->
<div style="background: rgba(255, 255, 255, 0.05); border-radius: 12px; padding: 1.5rem; margin-bottom: 2rem;">
<h3 style="margin-top: 0; color: white;">
<i class="fas fa-database"></i> Variations Stored in Database (<?= $diagnostic_data['variations_count_in_db'] ?>)
</h3>
<?php if (empty($diagnostic_data['variations_in_db'])): ?>
<p style="color: #f59e0b;">⚠️ No variations found in audio_variations table!</p>
<?php else: ?>
<div style="overflow-x: auto;">
<table style="width: 100%; border-collapse: collapse; color: white; margin-top: 1rem;">
<thead>
<tr style="border-bottom: 2px solid rgba(255, 255, 255, 0.1);">
<th style="padding: 1rem; text-align: left;">Index</th>
<th style="padding: 1rem; text-align: left;">Audio URL</th>
<th style="padding: 1rem; text-align: left;">Duration</th>
<th style="padding: 1rem; text-align: left;">Title</th>
<th style="padding: 1rem; text-align: left;">Has Audio?</th>
</tr>
</thead>
<tbody>
<?php foreach ($diagnostic_data['variations_in_db'] as $var): ?>
<tr style="border-bottom: 1px solid rgba(255, 255, 255, 0.05);">
<td style="padding: 1rem;"><?= $var['variation_index'] ?></td>
<td style="padding: 1rem; font-size: 0.85rem; word-break: break-all;">
<?= htmlspecialchars($var['audio_url'] ?: ($var['source_audio_url'] ?: ($var['stream_audio_url'] ?: 'NO URL'))) ?>
</td>
<td style="padding: 1rem;"><?= $var['duration'] ? gmdate('i:s', $var['duration']) : 'N/A' ?></td>
<td style="padding: 1rem;"><?= htmlspecialchars($var['title'] ?: 'N/A') ?></td>
<td style="padding: 1rem;">
<?php
$has_audio = !empty($var['audio_url']) || !empty($var['source_audio_url']) || !empty($var['stream_audio_url']);
?>
<span style="color: <?= $has_audio ? '#48bb78' : '#e53e3e' ?>;">
<?= $has_audio ? '✅ Yes' : '❌ No' ?>
</span>
</td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</div>
<?php endif; ?>
</div>
<!-- API.Box Comparison -->
<?php if (isset($diagnostic_data['api_box'])): ?>
<div style="background: rgba(139, 92, 246, 0.2); border: 1px solid rgba(139, 92, 246, 0.3); border-radius: 12px; padding: 1.5rem; margin-bottom: 2rem;">
<h3 style="margin-top: 0; color: white;">
<i class="fas fa-cloud"></i> API.Box Source Data
</h3>
<?php if ($diagnostic_data['api_box']['success']): ?>
<p style="color: #a0aec0;">
<strong>Variations in API.Box:</strong>
<span style="color: white; font-weight: 600;"><?= $diagnostic_data['api_box']['variations_count'] ?></span>
</p>
<?php if ($diagnostic_data['api_box']['variations_count'] != $diagnostic_data['variations_count_in_db']): ?>
<div style="background: rgba(245, 158, 11, 0.2); border: 1px solid rgba(245, 158, 11, 0.3); border-radius: 8px; padding: 1rem; margin-top: 1rem;">
<p style="color: #f59e0b; margin: 0;">
<strong>⚠️ MISMATCH DETECTED!</strong><br>
API.Box has <?= $diagnostic_data['api_box']['variations_count'] ?> variations, but database only has <?= $diagnostic_data['variations_count_in_db'] ?>.
</p>
</div>
<?php endif; ?>
<?php if (!empty($diagnostic_data['api_box']['variations'])): ?>
<div style="margin-top: 1rem;">
<h4 style="color: white;">API.Box Variations:</h4>
<div style="background: rgba(0, 0, 0, 0.3); border-radius: 8px; padding: 1rem; margin-top: 0.5rem; max-height: 400px; overflow-y: auto;">
<pre style="color: #a0aec0; font-size: 0.85rem; margin: 0; white-space: pre-wrap;"><?= htmlspecialchars(json_encode($diagnostic_data['api_box']['variations'], JSON_PRETTY_PRINT)) ?></pre>
</div>
</div>
<?php endif; ?>
<?php else: ?>
<p style="color: #e53e3e;">
<strong>❌ Failed to fetch from API.Box:</strong> <?= htmlspecialchars($diagnostic_data['api_box']['error']) ?>
</p>
<?php endif; ?>
</div>
<?php endif; ?>
<!-- Analysis & Explanation -->
<div style="background: rgba(255, 255, 255, 0.05); border-radius: 12px; padding: 1.5rem; margin-bottom: 2rem;">
<h3 style="margin-top: 0; color: white;"><i class="fas fa-lightbulb"></i> Analysis & Explanation</h3>
<?php
$db_count = $diagnostic_data['variations_count_in_db'];
$expected_count = $diagnostic_data['track']['variations_count'];
$api_count = $diagnostic_data['api_box']['variations_count'] ?? null;
$issues = [];
if ($db_count != $expected_count) {
$issues[] = "Database count mismatch: variations_count field says $expected_count but audio_variations table has $db_count";
}
if ($api_count !== null && $api_count != $db_count) {
$issues[] = "API.Box mismatch: Source has $api_count variations but database only has $db_count";
}
if ($db_count == 0) {
$issues[] = "No variations stored in database - callback may have failed or variations were skipped";
}
if ($db_count == 1 && ($api_count > 1 || $expected_count > 1)) {
$issues[] = "Only 1 variation stored when there should be more - second variation may have been skipped during callback";
}
?>
<?php if (empty($issues)): ?>
<p style="color: #48bb78;">✅ Everything looks correct! All counts match.</p>
<?php else: ?>
<div style="background: rgba(245, 158, 11, 0.2); border-left: 4px solid #f59e0b; padding: 1rem; margin-top: 1rem;">
<h4 style="color: #f59e0b; margin-top: 0;">🔍 Issues Found:</h4>
<ul style="color: #fbbf24; line-height: 1.8;">
<?php foreach ($issues as $issue): ?>
<li><?= htmlspecialchars($issue) ?></li>
<?php endforeach; ?>
</ul>
</div>
<div style="background: rgba(102, 126, 234, 0.2); border-left: 4px solid #667eea; padding: 1rem; margin-top: 1rem;">
<h4 style="color: #667eea; margin-top: 0;">💡 Possible Causes:</h4>
<ul style="color: #a0aec0; line-height: 1.8;">
<li><strong>Callback Issue:</strong> The second variation may have been skipped if it didn't have an audio_url, source_audio_url, or stream_audio_url in the callback response</li>
<li><strong>Database Insert Failed:</strong> The INSERT statement may have failed silently for the second variation (check callback logs)</li>
<li><strong>Partial Callback:</strong> Only the first variation callback was processed, second one may have failed or not been sent</li>
<li><strong>Duplicate Variation Index:</strong> Both variations may have been stored with the same variation_index (0), causing one to overwrite the other</li>
</ul>
</div>
<div style="background: rgba(72, 187, 120, 0.2); border-left: 4px solid #48bb78; padding: 1rem; margin-top: 1rem;">
<h4 style="color: #48bb78; margin-top: 0;">🔧 Recommended Fix:</h4>
<ol style="color: #a0aec0; line-height: 1.8;">
<li>Check callback logs for this track's task_id to see what happened during variation storage</li>
<li>Use "Re-sync from API.Box" in the Variations Fix tool to download all variations from source</li>
<li>Verify that both variations have valid audio URLs in the API.Box response</li>
</ol>
</div>
<?php endif; ?>
</div>
<!-- Callback Logs -->
<?php if (!empty($diagnostic_data['callback_logs'])): ?>
<div style="background: rgba(255, 255, 255, 0.05); border-radius: 12px; padding: 1.5rem;">
<h3 style="margin-top: 0; color: white;"><i class="fas fa-file-alt"></i> Relevant Callback Logs</h3>
<div style="background: rgba(0, 0, 0, 0.3); border-radius: 8px; padding: 1rem; max-height: 400px; overflow-y: auto;">
<pre style="color: #a0aec0; font-size: 0.8rem; margin: 0; white-space: pre-wrap;"><?= htmlspecialchars(implode("\n", $diagnostic_data['callback_logs'])) ?></pre>
</div>
</div>
<?php endif; ?>
<?php endif; ?>
<?php endif; ?>