 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/ |
<?php
/**
* Investigation Script for Stephan Bergeron's Purchase Issue
* Payment Intent: pm_1SW5taD0zXLMB4gHVB9lJWVz
* Expected: 4 tracks (Raw DRUMAHON, Wild And Free DRUMAHON, DrumAhon DRUMAHON, Prime Orchestral DRUMAHON)
* Actual: Only 2 tracks showing (wrong ones)
*/
session_start();
require_once 'config/database.php';
// Check if admin
if (!isset($_SESSION['is_admin']) || !$_SESSION['is_admin']) {
die("Admin access required");
}
$pdo = getDBConnection();
$payment_intent_id = 'pm_1SW5taD0zXLMB4gHVB9lJWVz';
echo "<h2>🔍 Investigation: Stephan Bergeron Purchase Issue</h2>";
echo "<style>
body { font-family: Arial; padding: 20px; background: #1a1a1a; color: white; }
table { border-collapse: collapse; width: 100%; margin: 20px 0; background: #2a2a2a; }
th, td { border: 1px solid #444; padding: 10px; text-align: left; }
th { background: #667eea; color: white; }
.success { color: #48bb78; }
.error { color: #e53e3e; }
.warning { color: #ffc107; }
.info { color: #667eea; }
.section { margin: 30px 0; padding: 20px; background: #2a2a2a; border-radius: 8px; }
.track-link { color: #667eea; text-decoration: none; }
.track-link:hover { text-decoration: underline; }
.expected { background: #2d5016; }
.wrong { background: #5a1a1a; }
</style>";
// 1. Find user
echo "<div class='section'>";
echo "<h3>👤 Step 1: Find User</h3>";
$stmt = $pdo->prepare("SELECT id, name, email FROM users WHERE name LIKE ? OR name LIKE ?");
$stmt->execute(['%Stephan%', '%Stephane%']);
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (empty($users)) {
die("<p class='error'>User 'Stephan bergeron' not found</p>");
}
$user = $users[0];
$user_id = $user['id'];
echo "<p class='success'>✓ Found user: <strong>{$user['name']}</strong> (ID: {$user_id}, Email: {$user['email']})</p>";
echo "</div>";
// 2. Check purchases with this payment intent
echo "<div class='section'>";
echo "<h3>📦 Step 2: Purchases with Payment Intent {$payment_intent_id}</h3>";
$stmt = $pdo->prepare("
SELECT
tp.*,
mt.title as track_title,
mt.user_id as track_creator_id,
u.name as track_creator_name
FROM track_purchases tp
JOIN music_tracks mt ON tp.track_id = mt.id
JOIN users u ON mt.user_id = u.id
WHERE tp.stripe_payment_intent_id = ?
ORDER BY tp.purchase_date DESC
");
$stmt->execute([$payment_intent_id]);
$purchases = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "<p class='info'>Found <strong>" . count($purchases) . "</strong> purchase(s) with this payment intent</p>";
if (empty($purchases)) {
echo "<p class='error'>⚠️ No purchases found with this payment intent ID!</p>";
} else {
echo "<table>";
echo "<tr><th>Purchase ID</th><th>User ID</th><th>Track ID</th><th>Track Title</th><th>Artist</th><th>Price</th><th>Date</th></tr>";
foreach ($purchases as $p) {
$is_wrong_user = ($p['user_id'] != $user_id);
$row_class = $is_wrong_user ? 'wrong' : '';
echo "<tr class='{$row_class}'>";
echo "<td>{$p['id']}</td>";
echo "<td>{$p['user_id']}" . ($is_wrong_user ? " <span class='error'>(WRONG USER!)</span>" : "") . "</td>";
echo "<td><a href='/track.php?id={$p['track_id']}' class='track-link'>{$p['track_id']}</a></td>";
echo "<td>" . htmlspecialchars($p['track_title'] ?: 'N/A') . "</td>";
echo "<td>{$p['track_creator_name']} (ID: {$p['track_creator_id']})</td>";
echo "<td>\${$p['price_paid']}</td>";
echo "<td>{$p['purchase_date']}</td>";
echo "</tr>";
}
echo "</table>";
}
echo "</div>";
// 3. Find the 4 tracks he actually bought
echo "<div class='section'>";
echo "<h3>🎵 Step 3: Find Expected Tracks (DRUMAHON)</h3>";
$expected_tracks = [
'Raw DRUMAHON',
'Wild And Free DRUMAHON',
'DrumAhon DRUMAHON',
'Prime Orchestral DRUMAHON'
];
// First, find DRUMAHON artist
$stmt = $pdo->prepare("SELECT id, name FROM users WHERE name LIKE ? OR name LIKE ? LIMIT 1");
$stmt->execute(['%DRUMAHON%', '%Drumahon%']);
$drumahon = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$drumahon) {
echo "<p class='error'>⚠️ DRUMAHON artist not found. Searching by track titles...</p>";
$drumahon_id = null;
} else {
$drumahon_id = $drumahon['id'];
echo "<p class='success'>✓ Found artist: <strong>{$drumahon['name']}</strong> (ID: {$drumahon_id})</p>";
}
$found_tracks = [];
foreach ($expected_tracks as $expected_title) {
// Search for tracks with this title
$search_pattern = '%' . str_replace(' ', '%', $expected_title) . '%';
$stmt = $pdo->prepare("
SELECT
mt.id,
mt.title,
mt.user_id,
mt.price,
u.name as artist_name
FROM music_tracks mt
JOIN users u ON mt.user_id = u.id
WHERE (mt.title LIKE ? OR mt.title LIKE ?)
AND mt.status = 'complete'
" . ($drumahon_id ? "AND mt.user_id = {$drumahon_id}" : "") . "
ORDER BY mt.created_at DESC
LIMIT 5
");
// Try different variations
$variations = [
$expected_title,
str_replace('DRUMAHON', 'DRUMAHON', $expected_title),
str_replace('DRUMAHON', 'Drumahon', $expected_title),
str_replace('DRUMAHON', 'drumahon', $expected_title),
];
$found = false;
foreach ($variations as $variation) {
$search = '%' . str_replace(' ', '%', $variation) . '%';
$stmt->execute([$search, $search]);
$tracks = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (!empty($tracks)) {
$found_tracks[$expected_title] = $tracks[0];
$found = true;
break;
}
}
if (!$found) {
// Try searching just by key words
$keywords = explode(' ', $expected_title);
$keyword = $keywords[0]; // "Raw", "Wild", "DrumAhon", "Prime"
$stmt = $pdo->prepare("
SELECT
mt.id,
mt.title,
mt.user_id,
mt.price,
u.name as artist_name
FROM music_tracks mt
JOIN users u ON mt.user_id = u.id
WHERE mt.title LIKE ?
AND (u.name LIKE '%DRUMAHON%' OR u.name LIKE '%Drumahon%')
AND mt.status = 'complete'
ORDER BY mt.created_at DESC
LIMIT 5
");
$stmt->execute(['%' . $keyword . '%']);
$tracks = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (!empty($tracks)) {
$found_tracks[$expected_title] = $tracks[0];
}
}
}
echo "<p class='info'>Found <strong>" . count($found_tracks) . "</strong> of 4 expected tracks</p>";
if (empty($found_tracks)) {
echo "<p class='error'>⚠️ Could not find any of the expected tracks. They may have different titles in the database.</p>";
} else {
echo "<table class='expected'>";
echo "<tr><th>Expected Title</th><th>Found Track ID</th><th>Actual Title</th><th>Artist</th><th>Price</th><th>Status</th></tr>";
foreach ($expected_tracks as $expected) {
if (isset($found_tracks[$expected])) {
$track = $found_tracks[$expected];
// Check if this track is in purchases
$check_stmt = $pdo->prepare("
SELECT id FROM track_purchases
WHERE user_id = ? AND track_id = ? AND stripe_payment_intent_id = ?
");
$check_stmt->execute([$user_id, $track['id'], $payment_intent_id]);
$is_purchased = $check_stmt->fetch();
$status = $is_purchased ? "<span class='success'>✓ Purchased</span>" : "<span class='error'>✗ MISSING</span>";
echo "<tr>";
echo "<td><strong>{$expected}</strong></td>";
echo "<td><a href='/track.php?id={$track['id']}' class='track-link'>{$track['id']}</a></td>";
echo "<td>" . htmlspecialchars($track['title']) . "</td>";
echo "<td>{$track['artist_name']}</td>";
echo "<td>\${$track['price']}</td>";
echo "<td>{$status}</td>";
echo "</tr>";
} else {
echo "<tr>";
echo "<td><strong>{$expected}</strong></td>";
echo "<td colspan='5' class='error'>✗ Track not found in database</td>";
echo "</tr>";
}
}
echo "</table>";
}
echo "</div>";
// 4. Check all purchases for this user
echo "<div class='section'>";
echo "<h3>📋 Step 4: All Purchases for User {$user['name']}</h3>";
$stmt = $pdo->prepare("
SELECT
tp.*,
mt.title as track_title,
u.name as track_creator_name
FROM track_purchases tp
JOIN music_tracks mt ON tp.track_id = mt.id
JOIN users u ON mt.user_id = u.id
WHERE tp.user_id = ?
ORDER BY tp.purchase_date DESC
LIMIT 20
");
$stmt->execute([$user_id]);
$all_purchases = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "<p class='info'>Total purchases: <strong>" . count($all_purchases) . "</strong></p>";
if (!empty($all_purchases)) {
echo "<table>";
echo "<tr><th>Purchase ID</th><th>Track ID</th><th>Track Title</th><th>Artist</th><th>Price</th><th>Payment Intent</th><th>Date</th></tr>";
foreach ($all_purchases as $p) {
$is_target_payment = ($p['stripe_payment_intent_id'] === $payment_intent_id);
$row_class = $is_target_payment ? 'expected' : '';
echo "<tr class='{$row_class}'>";
echo "<td>{$p['id']}</td>";
echo "<td><a href='/track.php?id={$p['track_id']}' class='track-link'>{$p['track_id']}</a></td>";
echo "<td>" . htmlspecialchars($p['track_title'] ?: 'N/A') . "</td>";
echo "<td>{$p['track_creator_name']}</td>";
echo "<td>\${$p['price_paid']}</td>";
echo "<td>" . ($p['stripe_payment_intent_id'] ? substr($p['stripe_payment_intent_id'], 0, 20) . '...' : 'N/A') . "</td>";
echo "<td>{$p['purchase_date']}</td>";
echo "</tr>";
}
echo "</table>";
}
echo "</div>";
// 5. Check Stripe webhook logs
echo "<div class='section'>";
echo "<h3>📝 Step 5: Check Webhook Logs</h3>";
$log_file = __DIR__ . '/logs/mixed_cart_payments.log';
if (file_exists($log_file)) {
$lines = file($log_file);
$relevant_logs = [];
foreach ($lines as $line) {
$log = json_decode($line, true);
if ($log && isset($log['payment_intent_id']) && $log['payment_intent_id'] === $payment_intent_id) {
$relevant_logs[] = $log;
}
}
if (!empty($relevant_logs)) {
echo "<p class='success'>✓ Found " . count($relevant_logs) . " log entry(ies) for this payment intent</p>";
echo "<pre style='background: #1a1a1a; padding: 15px; border-radius: 5px; overflow-x: auto;'>";
echo htmlspecialchars(json_encode($relevant_logs, JSON_PRETTY_PRINT));
echo "</pre>";
} else {
echo "<p class='warning'>⚠️ No log entries found for this payment intent</p>";
}
} else {
echo "<p class='warning'>⚠️ Log file not found: {$log_file}</p>";
}
echo "</div>";
// 6. Summary and recommendations
echo "<div class='section'>";
echo "<h3>📊 Summary & Recommendations</h3>";
$purchases_with_intent = count($purchases);
$expected_count = 4;
$found_tracks_count = count($found_tracks);
echo "<ul>";
echo "<li><strong>Expected purchases:</strong> {$expected_count}</li>";
echo "<li><strong>Found purchases with payment intent:</strong> {$purchases_with_intent}</li>";
echo "<li><strong>Expected tracks found in DB:</strong> {$found_tracks_count}</li>";
echo "</ul>";
if ($purchases_with_intent < $expected_count) {
echo "<p class='error'><strong>⚠️ ISSUE CONFIRMED:</strong> Only {$purchases_with_intent} purchase(s) recorded, but {$expected_count} were expected.</p>";
echo "<p><strong>Possible causes:</strong></p>";
echo "<ul>";
echo "<li>Webhook failed to process all cart items</li>";
echo "<li>Cart items metadata was corrupted or missing</li>";
echo "<li>Some tracks failed validation during purchase processing</li>";
echo "<li>Wrong tracks were associated with this payment intent</li>";
echo "</ul>";
echo "<p><strong>Next steps:</strong></p>";
echo "<ol>";
echo "<li>Verify the 4 expected tracks exist in the database</li>";
echo "<li>Check if wrong purchases need to be deleted</li>";
echo "<li>Manually add missing purchases using fix_missing_purchase.php</li>";
echo "</ol>";
} else {
echo "<p class='warning'>⚠️ Same number of purchases found, but they may be the wrong tracks. Check the track titles above.</p>";
}
echo "</div>";
echo "<hr>";
echo "<p><a href='/admin.php?tab=tracks' style='color: #667eea;'>← Back to Admin</a> | ";
echo "<a href='/fix_missing_purchase.php' style='color: #667eea;'>Fix Missing Purchase Tool</a></p>";
?>