 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
/**
* Investigation Script: Stephane Bergeron's 10 Track Purchase
*
* This script:
* 1. Finds Stephane Bergeron's user account
* 2. Checks his recent purchases (looking for 10 tracks)
* 3. Finds all incomplete and failed transactions
* 4. Cross-references Stripe payment intents with database purchases
*/
session_start();
require_once 'config/database.php';
// Check if admin
if (!isset($_SESSION['is_admin']) || !$_SESSION['is_admin']) {
die("Admin access required");
}
$pdo = getDBConnection();
$stripe_secret = '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';
echo "<!DOCTYPE html><html><head><meta charset='UTF-8'><title>Stephane Bergeron Investigation</title>";
echo "<style>
body { font-family: Arial, sans-serif; padding: 20px; background: #1a1a1a; color: #e0e0e0; line-height: 1.6; }
h1, h2, h3 { color: #667eea; }
table { border-collapse: collapse; width: 100%; margin: 20px 0; background: #2a2a2a; }
th, td { border: 1px solid #444; padding: 12px; text-align: left; }
th { background: #667eea; color: white; font-weight: bold; }
tr:hover { background: #333; }
.success { color: #48bb78; font-weight: bold; }
.error { color: #e53e3e; font-weight: bold; }
.warning { color: #ffc107; font-weight: bold; }
.info { color: #667eea; }
.section { margin: 30px 0; padding: 25px; background: #2a2a2a; border-radius: 8px; border-left: 4px solid #667eea; }
.missing { background: rgba(229, 62, 62, 0.1); }
.found { background: rgba(72, 187, 120, 0.1); }
.incomplete { background: rgba(255, 193, 7, 0.1); }
pre { background: #1a1a1a; padding: 15px; border-radius: 5px; overflow-x: auto; border: 1px solid #444; }
.badge { display: inline-block; padding: 4px 8px; border-radius: 4px; font-size: 12px; font-weight: bold; }
.badge-success { background: #48bb78; color: white; }
.badge-error { background: #e53e3e; color: white; }
.badge-warning { background: #ffc107; color: #1a1a1a; }
.badge-info { background: #667eea; color: white; }
a { color: #667eea; text-decoration: none; }
a:hover { text-decoration: underline; }
</style></head><body>";
echo "<h1>🔍 Investigation: Stephane Bergeron's 10 Track Purchase</h1>";
// ============================================
// STEP 1: Find Stephane Bergeron
// ============================================
echo "<div class='section'>";
echo "<h2>👤 Step 1: Finding Stephane Bergeron</h2>";
$stmt = $pdo->prepare("
SELECT id, name, email, stripe_customer_id, created_at
FROM users
WHERE name LIKE ? OR name LIKE ? OR email LIKE ?
ORDER BY id DESC
");
$stmt->execute(['%Stephane%', '%Stephan%', '%stevenberg450%']);
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (empty($users)) {
die("<p class='error'>✗ User 'Stephane Bergeron' not found</p>");
}
$stephane = $users[0];
$user_id = $stephane['id'];
echo "<p class='success'>✓ Found user: <strong>{$stephane['name']}</strong></p>";
echo "<table>";
echo "<tr><th>User ID</th><th>Name</th><th>Email</th><th>Stripe Customer ID</th><th>Account Created</th></tr>";
echo "<tr>";
echo "<td>{$stephane['id']}</td>";
echo "<td>{$stephane['name']}</td>";
echo "<td>{$stephane['email']}</td>";
echo "<td>" . ($stephane['stripe_customer_id'] ?: 'None') . "</td>";
echo "<td>{$stephane['created_at']}</td>";
echo "</tr>";
echo "</table>";
echo "</div>";
// ============================================
// STEP 2: Check Recent Purchases (Last 30 days)
// ============================================
echo "<div class='section'>";
echo "<h2>📦 Step 2: Recent Purchases (Last 30 Days)</h2>";
$stmt = $pdo->prepare("
SELECT
tp.id as purchase_id,
tp.track_id,
tp.price_paid,
tp.payment_method,
tp.stripe_payment_intent_id,
tp.purchase_date,
mt.title as track_title,
mt.price as track_price,
artist.name as artist_name
FROM track_purchases tp
JOIN music_tracks mt ON tp.track_id = mt.id
JOIN users artist ON mt.user_id = artist.id
WHERE tp.user_id = ?
AND tp.purchase_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY tp.purchase_date DESC
");
$stmt->execute([$user_id]);
$recent_purchases = $stmt->fetchAll(PDO::FETCH_ASSOC);
$purchase_count = count($recent_purchases);
echo "<p class='info'>Found <strong>{$purchase_count}</strong> purchase(s) in the last 30 days</p>";
if (!empty($recent_purchases)) {
echo "<table>";
echo "<tr><th>Purchase ID</th><th>Track ID</th><th>Track Title</th><th>Artist</th><th>Price Paid</th><th>Payment Method</th><th>Payment Intent</th><th>Date</th></tr>";
foreach ($recent_purchases as $p) {
$payment_intent_short = $p['stripe_payment_intent_id']
? substr($p['stripe_payment_intent_id'], 0, 20) . '...'
: 'N/A';
echo "<tr>";
echo "<td>{$p['purchase_id']}</td>";
echo "<td><a href='/track.php?id={$p['track_id']}' target='_blank'>{$p['track_id']}</a></td>";
echo "<td>" . htmlspecialchars($p['track_title'] ?: 'N/A') . "</td>";
echo "<td>{$p['artist_name']}</td>";
echo "<td>\${$p['price_paid']}</td>";
echo "<td>{$p['payment_method']}</td>";
echo "<td><code>{$payment_intent_short}</code></td>";
echo "<td>{$p['purchase_date']}</td>";
echo "</tr>";
}
echo "</table>";
} else {
echo "<p class='warning'>⚠️ No recent purchases found</p>";
}
// Check all-time purchases
$stmt = $pdo->prepare("SELECT COUNT(*) as total FROM track_purchases WHERE user_id = ?");
$stmt->execute([$user_id]);
$total_purchases = $stmt->fetch(PDO::FETCH_ASSOC)['total'];
echo "<p class='info'>Total all-time purchases: <strong>{$total_purchases}</strong></p>";
echo "</div>";
// ============================================
// STEP 3: Check Stripe Payment Intents for Stephane
// ============================================
echo "<div class='section'>";
echo "<h2>💳 Step 3: Stripe Payment Intents (Last 60 Days)</h2>";
$customer_id = $stephane['stripe_customer_id'];
if (!$customer_id) {
echo "<p class='warning'>⚠️ No Stripe customer ID found. Searching by email...</p>";
// Try to find customer by email
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, 'https://api.stripe.com/v1/customers?email=' . urlencode($stephane['email']) . '&limit=1');
curl_setopt($ch, CURLOPT_HTTPHEADER, ['Authorization: Bearer ' . $stripe_secret]);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$response = curl_exec($ch);
$http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close($ch);
if ($http_code === 200) {
$data = json_decode($response, true);
if (!empty($data['data'])) {
$customer_id = $data['data'][0]['id'];
echo "<p class='success'>✓ Found Stripe customer: <code>{$customer_id}</code></p>";
}
}
}
if ($customer_id) {
// Fetch payment intents for this customer
$ch = curl_init();
$created_after = strtotime('-60 days');
curl_setopt($ch, CURLOPT_URL, "https://api.stripe.com/v1/payment_intents?customer={$customer_id}&limit=100&created[gte]={$created_after}");
curl_setopt($ch, CURLOPT_HTTPHEADER, ['Authorization: Bearer ' . $stripe_secret]);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$response = curl_exec($ch);
$http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close($ch);
if ($http_code === 200) {
$data = json_decode($response, true);
$payment_intents = $data['data'] ?? [];
echo "<p class='info'>Found <strong>" . count($payment_intents) . "</strong> payment intent(s) for this customer</p>";
if (!empty($payment_intents)) {
echo "<table>";
echo "<tr><th>Payment Intent ID</th><th>Status</th><th>Amount</th><th>Currency</th><th>Created</th><th>Metadata</th><th>DB Purchase Count</th></tr>";
foreach ($payment_intents as $pi) {
$pi_id = $pi['id'];
$status = $pi['status'];
$amount = $pi['amount'] / 100;
$created = date('Y-m-d H:i:s', $pi['created']);
$metadata = $pi['metadata'] ?? [];
// Check database for purchases with this payment intent
$stmt = $pdo->prepare("SELECT COUNT(*) as count FROM track_purchases WHERE stripe_payment_intent_id = ?");
$stmt->execute([$pi_id]);
$db_count = $stmt->fetch(PDO::FETCH_ASSOC)['count'];
// Determine row class based on status
$row_class = '';
if ($status === 'succeeded' && $db_count == 0) {
$row_class = 'missing';
} elseif (in_array($status, ['requires_payment_method', 'requires_action', 'requires_capture', 'requires_confirmation'])) {
$row_class = 'incomplete';
} elseif (in_array($status, ['canceled', 'payment_failed'])) {
$row_class = 'error';
}
// Parse metadata for track info
$metadata_summary = '';
if (!empty($metadata)) {
if (isset($metadata['cart_items'])) {
$cart_items = json_decode($metadata['cart_items'], true);
if (is_array($cart_items)) {
$track_count = 0;
foreach ($cart_items as $item) {
if (isset($item['type']) && $item['type'] === 'track') {
$track_count++;
}
}
$metadata_summary = "Cart: {$track_count} track(s)";
}
} elseif (isset($metadata['track_id'])) {
$metadata_summary = "Track ID: " . $metadata['track_id'];
} else {
$metadata_summary = json_encode($metadata);
}
}
echo "<tr class='{$row_class}'>";
echo "<td><code>" . substr($pi_id, 0, 30) . "...</code></td>";
echo "<td><span class='badge badge-" . ($status === 'succeeded' ? 'success' : ($status === 'canceled' || $status === 'payment_failed' ? 'error' : 'warning')) . "'>{$status}</span></td>";
echo "<td>\${$amount}</td>";
echo "<td>{$pi['currency']}</td>";
echo "<td>{$created}</td>";
echo "<td><small>{$metadata_summary}</small></td>";
echo "<td><strong>{$db_count}</strong></td>";
echo "</tr>";
}
echo "</table>";
}
} else {
echo "<p class='error'>✗ Failed to fetch from Stripe API. HTTP Code: {$http_code}</p>";
}
} else {
echo "<p class='warning'>⚠️ Could not find Stripe customer ID</p>";
}
echo "</div>";
// ============================================
// STEP 4: Find All Incomplete/Failed Transactions
// ============================================
echo "<div class='section'>";
echo "<h2>⚠️ Step 4: All Incomplete & Failed Transactions (Last 60 Days)</h2>";
// Fetch all payment intents with problematic statuses
$ch = curl_init();
$created_after = strtotime('-60 days');
curl_setopt($ch, CURLOPT_URL, "https://api.stripe.com/v1/payment_intents?limit=100&created[gte]={$created_after}");
curl_setopt($ch, CURLOPT_HTTPHEADER, ['Authorization: Bearer ' . $stripe_secret]);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$response = curl_exec($ch);
$http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close($ch);
if ($http_code === 200) {
$data = json_decode($response, true);
$all_payment_intents = $data['data'] ?? [];
// Filter for incomplete/failed statuses
$problematic_statuses = [
'requires_payment_method',
'requires_action',
'requires_capture',
'requires_confirmation',
'canceled',
'payment_failed'
];
$problematic_payments = [];
foreach ($all_payment_intents as $pi) {
if (in_array($pi['status'], $problematic_statuses)) {
// Only include if it has metadata suggesting it's a track purchase
$metadata = $pi['metadata'] ?? [];
if (isset($metadata['user_id']) &&
(isset($metadata['cart_items']) || isset($metadata['track_id']) || isset($metadata['payment_type']))) {
$problematic_payments[] = $pi;
}
}
}
echo "<p class='info'>Found <strong>" . count($problematic_payments) . "</strong> incomplete/failed transaction(s) with track purchase metadata</p>";
if (!empty($problematic_payments)) {
echo "<table>";
echo "<tr><th>Payment Intent ID</th><th>Status</th><th>User ID</th><th>Amount</th><th>Created</th><th>Metadata</th><th>Error</th></tr>";
foreach ($problematic_payments as $pi) {
$pi_id = $pi['id'];
$status = $pi['status'];
$amount = $pi['amount'] / 100;
$created = date('Y-m-d H:i:s', $pi['created']);
$metadata = $pi['metadata'] ?? [];
$user_id_from_meta = $metadata['user_id'] ?? 'N/A';
// Get user name if possible
$user_name = 'N/A';
if ($user_id_from_meta !== 'N/A') {
$stmt = $pdo->prepare("SELECT name FROM users WHERE id = ?");
$stmt->execute([$user_id_from_meta]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);
if ($user) {
$user_name = $user['name'];
}
}
// Parse metadata
$metadata_summary = '';
if (isset($metadata['cart_items'])) {
$cart_items = json_decode($metadata['cart_items'], true);
if (is_array($cart_items)) {
$track_count = 0;
$tracks = [];
foreach ($cart_items as $item) {
if (isset($item['type']) && $item['type'] === 'track') {
$track_count++;
$tracks[] = $item['title'] ?? 'Unknown';
}
}
$metadata_summary = "Cart: {$track_count} track(s) - " . implode(', ', array_slice($tracks, 0, 3));
}
} elseif (isset($metadata['track_id'])) {
$metadata_summary = "Track ID: " . $metadata['track_id'];
}
// Get error message if available
$error_msg = 'N/A';
if (isset($pi['last_payment_error'])) {
$error_msg = $pi['last_payment_error']['message'] ?? 'Unknown error';
}
$row_class = in_array($status, ['canceled', 'payment_failed']) ? 'error' : 'incomplete';
echo "<tr class='{$row_class}'>";
echo "<td><code>" . substr($pi_id, 0, 30) . "...</code></td>";
echo "<td><span class='badge badge-" . ($status === 'canceled' || $status === 'payment_failed' ? 'error' : 'warning') . "'>{$status}</span></td>";
echo "<td>{$user_id_from_meta} ({$user_name})</td>";
echo "<td>\${$amount}</td>";
echo "<td>{$created}</td>";
echo "<td><small>{$metadata_summary}</small></td>";
echo "<td><small>{$error_msg}</small></td>";
echo "</tr>";
}
echo "</table>";
} else {
echo "<p class='success'>✓ No incomplete/failed transactions found</p>";
}
// Also check for succeeded payments without database records
echo "<h3>🔍 Succeeded Payments Without Database Records</h3>";
$succeeded_without_db = [];
foreach ($all_payment_intents as $pi) {
if ($pi['status'] === 'succeeded') {
$metadata = $pi['metadata'] ?? [];
if (isset($metadata['user_id']) &&
(isset($metadata['cart_items']) || isset($metadata['track_id']))) {
$pi_id = $pi['id'];
$stmt = $pdo->prepare("SELECT COUNT(*) as count FROM track_purchases WHERE stripe_payment_intent_id = ?");
$stmt->execute([$pi_id]);
$db_count = $stmt->fetch(PDO::FETCH_ASSOC)['count'];
if ($db_count == 0) {
$succeeded_without_db[] = $pi;
}
}
}
}
if (!empty($succeeded_without_db)) {
echo "<p class='error'>⚠️ Found <strong>" . count($succeeded_without_db) . "</strong> succeeded payment(s) without database records!</p>";
echo "<table class='missing'>";
echo "<tr><th>Payment Intent ID</th><th>User ID</th><th>Amount</th><th>Created</th><th>Metadata</th></tr>";
foreach ($succeeded_without_db as $pi) {
$pi_id = $pi['id'];
$amount = $pi['amount'] / 100;
$created = date('Y-m-d H:i:s', $pi['created']);
$metadata = $pi['metadata'] ?? [];
$user_id_from_meta = $metadata['user_id'] ?? 'N/A';
// Parse metadata
$metadata_summary = '';
if (isset($metadata['cart_items'])) {
$cart_items = json_decode($metadata['cart_items'], true);
if (is_array($cart_items)) {
$track_count = 0;
$tracks = [];
foreach ($cart_items as $item) {
if (isset($item['type']) && $item['type'] === 'track') {
$track_count++;
$tracks[] = ($item['title'] ?? 'Unknown') . " (ID: " . ($item['track_id'] ?? 'N/A') . ")";
}
}
$metadata_summary = "Cart: {$track_count} track(s) - " . implode(', ', array_slice($tracks, 0, 3));
}
}
echo "<tr>";
echo "<td><code>" . substr($pi_id, 0, 30) . "...</code></td>";
echo "<td>{$user_id_from_meta}</td>";
echo "<td>\${$amount}</td>";
echo "<td>{$created}</td>";
echo "<td><small>{$metadata_summary}</small></td>";
echo "</tr>";
}
echo "</table>";
} else {
echo "<p class='success'>✓ All succeeded payments have database records</p>";
}
} else {
echo "<p class='error'>✗ Failed to fetch from Stripe API. HTTP Code: {$http_code}</p>";
}
echo "</div>";
// ============================================
// STEP 5: Summary
// ============================================
echo "<div class='section'>";
echo "<h2>📊 Summary</h2>";
echo "<ul>";
echo "<li><strong>User Found:</strong> {$stephane['name']} (ID: {$stephane['id']})</li>";
echo "<li><strong>Recent Purchases (30 days):</strong> {$purchase_count}</li>";
echo "<li><strong>Total All-Time Purchases:</strong> {$total_purchases}</li>";
if ($purchase_count < 10) {
echo "<li class='warning'><strong>⚠️ Only {$purchase_count} purchase(s) found in last 30 days. Expected 10 tracks.</strong></li>";
} else {
echo "<li class='success'><strong>✓ Found {$purchase_count} purchase(s) in last 30 days</strong></li>";
}
echo "</ul>";
echo "<h3>Next Steps:</h3>";
echo "<ol>";
echo "<li>If purchases are missing, check the 'Succeeded Payments Without Database Records' section above</li>";
echo "<li>Review incomplete/failed transactions to identify payment issues</li>";
echo "<li>Use <a href='fix_missing_purchase.php'>fix_missing_purchase.php</a> to manually add missing purchases</li>";
echo "<li>Use <a href='reconcile_stripe_purchases.php'>reconcile_stripe_purchases.php</a> for bulk reconciliation</li>";
echo "</ol>";
echo "</div>";
echo "<hr>";
echo "<p><a href='/admin.php?tab=purchases' style='color: #667eea;'>← Back to Admin</a></p>";
echo "</body></html>";
?>