 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
/**
* Fix Stephane Bergeron's Missing Purchases
*
* This script fixes the missing purchases from payment intent:
* pi_35M5sbDBzXLMB4gH86HMEFic... (4 tracks, $7.96, succeeded but no DB records)
*
* Usage: Run via browser as admin
* Example: fix_stephane_missing_purchases.php?payment_intent_id=pi_35M5sbDBzXLMB4gH86HMEFic[FULL]
*/
// Enable error reporting for debugging
error_reporting(E_ALL);
ini_set('display_errors', 0); // Don't display errors, log them instead
ini_set('log_errors', 1);
session_start();
// Check if admin first (before any output)
if (!isset($_SESSION['is_admin']) || !$_SESSION['is_admin']) {
http_response_code(403);
die("Admin access required");
}
// Load database config
if (!file_exists(__DIR__ . '/config/database.php')) {
http_response_code(500);
error_log("fix_stephane_missing_purchases.php: config/database.php not found");
die("Configuration error. Please contact support.");
}
require_once __DIR__ . '/config/database.php';
// Verify getDBConnection function exists
if (!function_exists('getDBConnection')) {
http_response_code(500);
error_log("fix_stephane_missing_purchases.php: getDBConnection function not found");
die("Configuration error. Please contact support.");
}
// Get database connection
try {
$pdo = getDBConnection();
if (!$pdo) {
throw new Exception('Database connection failed - getDBConnection returned null');
}
} catch (Exception $e) {
http_response_code(500);
error_log("fix_stephane_missing_purchases.php: Database connection error: " . $e->getMessage());
die("Database connection error. Please try again later.");
} catch (Error $e) {
http_response_code(500);
error_log("fix_stephane_missing_purchases.php: Database connection fatal error: " . $e->getMessage());
die("Database connection fatal error. Please contact support.");
}
$stripe_secret = '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';
// Get payment intent ID
$payment_intent_id = $_GET['payment_intent_id'] ?? '';
$action = $_GET['action'] ?? 'analyze';
// Start HTML output
echo "<!DOCTYPE html><html><head><meta charset='UTF-8'><title>Fix Stephane's Missing Purchases</title>";
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; }
.btn { display: inline-block; padding: 10px 20px; background: #667eea; color: white; text-decoration: none; border-radius: 5px; margin: 10px 5px; }
.btn:hover { background: #5568d3; }
.btn-danger { background: #e53e3e; }
.btn-danger:hover { background: #c53030; }
input[type='text'] { padding: 8px; width: 400px; background: #2a2a2a; border: 1px solid #444; color: white; border-radius: 5px; }
button.btn { border: none; cursor: pointer; }
</style></head><body>";
echo "<h2>🔧 Fix Stephane Bergeron's Missing Purchases</h2>";
// If no payment intent ID, show form
if (empty($payment_intent_id)) {
echo "<div class='section'>";
echo "<h3>Enter Payment Intent ID</h3>";
echo "<p class='info'>From the investigation, the payment intent ID was: <code>pi_35M5sbDBzXLMB4gH86HMEFic...</code></p>";
echo "<p>Please enter the <strong>complete</strong> payment intent ID:</p>";
echo "<form method='GET'>";
echo "<input type='text' name='payment_intent_id' placeholder='pi_35M5sbDBzXLMB4gH86HMEFic...' required style='font-family: monospace;'>";
echo "<button type='submit' class='btn'>Analyze</button>";
echo "</form>";
echo "<p><a href='investigate_stephane_10_tracks.php' style='color: #667eea;'>← Back to Investigation</a></p>";
echo "</div>";
exit;
}
if ($action === 'analyze') {
echo "<div class='section'>";
echo "<h3>📊 Analyzing Payment Intent</h3>";
// Validate payment intent ID format
if (empty($payment_intent_id) || !preg_match('/^pi_[a-zA-Z0-9_]+$/', $payment_intent_id)) {
echo "<p class='error'>✗ Invalid payment intent ID format. Must start with 'pi_' followed by alphanumeric characters.</p>";
echo "</div></body></html>";
exit;
}
// Fetch payment intent from Stripe
try {
$ch = curl_init();
if (!$ch) {
throw new Exception('Failed to initialize cURL');
}
curl_setopt($ch, CURLOPT_URL, 'https://api.stripe.com/v1/payment_intents/' . urlencode($payment_intent_id));
curl_setopt($ch, CURLOPT_HTTPHEADER, ['Authorization: Bearer ' . $stripe_secret]);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_TIMEOUT, 30);
curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 10);
$response = curl_exec($ch);
$curl_error = curl_error($ch);
$http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close($ch);
if ($curl_error) {
throw new Exception('cURL Error: ' . $curl_error);
}
} catch (Exception $e) {
echo "<p class='error'>✗ Error fetching payment intent: " . htmlspecialchars($e->getMessage()) . "</p>";
echo "</div></body></html>";
exit;
}
if ($http_code !== 200) {
echo "<p class='error'>✗ Failed to fetch payment intent. HTTP Code: {$http_code}</p>";
if ($http_code === 404) {
echo "<p class='warning'>⚠ Payment intent not found. Please check the payment intent ID is correct.</p>";
} else {
$error_data = json_decode($response, true);
if ($error_data && isset($error_data['error']['message'])) {
echo "<p class='error'>Error: " . htmlspecialchars($error_data['error']['message']) . "</p>";
}
}
echo "</div></body></html>";
exit;
}
$payment = json_decode($response, true);
if (!$payment) {
echo "<p class='error'>✗ Failed to parse payment intent response</p>";
echo "<pre>" . htmlspecialchars($response) . "</pre>";
echo "</div></body></html>";
exit;
}
if ($payment['status'] !== 'succeeded') {
echo "<p class='error'>✗ Payment intent not succeeded. Status: " . htmlspecialchars($payment['status'] ?? 'unknown') . "</p>";
echo "</div></body></html>";
exit;
}
$metadata = $payment['metadata'] ?? [];
$user_id = $metadata['user_id'] ?? null;
$amount = $payment['amount'] / 100;
echo "<p class='success'>✓ Payment Intent Found</p>";
echo "<table>";
echo "<tr><th>Payment Intent ID</th><td>{$payment_intent_id}</td></tr>";
echo "<tr><th>Status</th><td class='success'>{$payment['status']}</td></tr>";
echo "<tr><th>Amount</th><td>\${$amount}</td></tr>";
echo "<tr><th>User ID</th><td>{$user_id}</td></tr>";
echo "</table>";
// Get user info
if ($user_id) {
$stmt = $pdo->prepare("SELECT id, name, email FROM users WHERE id = ?");
$stmt->execute([$user_id]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);
if ($user) {
echo "<p class='info'>User: <strong>{$user['name']}</strong> ({$user['email']})</p>";
}
}
// Parse cart items
$cart_items_json = $metadata['cart_items'] ?? '[]';
$cart_items = is_string($cart_items_json) ? json_decode($cart_items_json, true) : $cart_items_json;
if (!is_array($cart_items)) {
echo "<p class='error'>✗ Invalid cart items</p>";
echo "<p class='info'>Cart items data: " . htmlspecialchars($cart_items_json) . "</p>";
echo "</div></body></html>";
exit;
}
// Extract track items
$track_items = [];
foreach ($cart_items as $item) {
if (isset($item['type']) && $item['type'] === 'track' && isset($item['track_id'])) {
$track_items[] = [
'track_id' => (int)$item['track_id'],
'title' => $item['title'] ?? 'Unknown',
'price' => ($item['amount'] ?? 0) / 100
];
}
}
echo "<h3>Cart Items:</h3>";
echo "<p class='info'>Found <strong>" . count($track_items) . "</strong> track(s)</p>";
if (empty($track_items)) {
echo "<p class='warning'>⚠ No track items found in cart</p>";
echo "<p class='info'>Cart items: <pre>" . htmlspecialchars(json_encode($cart_items, JSON_PRETTY_PRINT)) . "</pre></p>";
echo "</div></body></html>";
exit;
}
echo "<table>";
echo "<tr><th>Track ID</th><th>Title</th><th>Price</th></tr>";
foreach ($track_items as $item) {
echo "<tr><td>{$item['track_id']}</td><td>{$item['title']}</td><td>\${$item['price']}</td></tr>";
}
echo "</table>";
// Check existing purchases
$missing_tracks = [];
$existing_tracks = [];
foreach ($track_items as $item) {
$track_id = $item['track_id'];
// Check if track exists
$stmt = $pdo->prepare("SELECT id, title, price, status, user_id as artist_id FROM music_tracks WHERE id = ?");
$stmt->execute([$track_id]);
$track = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$track) {
$missing_tracks[] = ['track_id' => $track_id, 'title' => $item['title'], 'reason' => 'Track not found'];
continue;
}
if ($track['status'] !== 'complete') {
$missing_tracks[] = ['track_id' => $track_id, 'title' => $track['title'], 'reason' => "Status: {$track['status']}"];
continue;
}
// Check if already purchased
$stmt = $pdo->prepare("SELECT id FROM track_purchases WHERE user_id = ? AND track_id = ? AND stripe_payment_intent_id = ?");
$stmt->execute([$user_id, $track_id, $payment_intent_id]);
if ($stmt->fetch()) {
$existing_tracks[] = ['track_id' => $track_id, 'title' => $track['title']];
} else {
// Check if purchased with different payment intent
$stmt = $pdo->prepare("SELECT id FROM track_purchases WHERE user_id = ? AND track_id = ?");
$stmt->execute([$user_id, $track_id]);
if ($stmt->fetch()) {
$existing_tracks[] = ['track_id' => $track_id, 'title' => $track['title'], 'note' => 'Different payment intent'];
} else {
$missing_tracks[] = [
'track_id' => $track_id,
'title' => $track['title'],
'price' => $track['price'],
'artist_id' => $track['artist_id'],
'reason' => 'Not purchased'
];
}
}
}
echo "<h3>Status:</h3>";
echo "<p class='info'>Existing: <strong>" . count($existing_tracks) . "</strong> | Missing: <strong>" . count($missing_tracks) . "</strong></p>";
if (!empty($existing_tracks)) {
echo "<table>";
echo "<tr><th>Track ID</th><th>Title</th><th>Status</th></tr>";
foreach ($existing_tracks as $t) {
echo "<tr><td>{$t['track_id']}</td><td>{$t['title']}</td><td>" . ($t['note'] ?? 'Already purchased') . "</td></tr>";
}
echo "</table>";
}
if (!empty($missing_tracks)) {
$fixable = array_filter($missing_tracks, function($t) { return isset($t['price']) && isset($t['artist_id']); });
if (!empty($fixable)) {
echo "<table>";
echo "<tr><th>Track ID</th><th>Title</th><th>Price</th><th>Reason</th></tr>";
foreach ($missing_tracks as $t) {
echo "<tr><td>{$t['track_id']}</td><td>{$t['title']}</td><td>\${$t['price'] ?? 'N/A'}</td><td>{$t['reason']}</td></tr>";
}
echo "</table>";
echo "<div class='section'>";
echo "<h3>🔧 Fix Missing Purchases</h3>";
echo "<p class='warning'>This will add " . count($fixable) . " missing purchase(s).</p>";
echo "<p><a href='?action=fix&payment_intent_id=" . urlencode($payment_intent_id) . "' class='btn btn-danger'>Fix Now</a></p>";
echo "</div>";
}
} else {
echo "<p class='success'>✓ All purchases are recorded</p>";
}
echo "</div>";
} elseif ($action === 'fix') {
echo "<div class='section'>";
echo "<h3>🔧 Fixing Missing Purchases</h3>";
// Fetch payment intent
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, 'https://api.stripe.com/v1/payment_intents/' . urlencode($payment_intent_id));
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) {
echo "<p class='error'>✗ Failed to fetch payment intent. HTTP Code: {$http_code}</p>";
if ($http_code === 404) {
echo "<p class='warning'>⚠ Payment intent not found. Please check the payment intent ID is correct.</p>";
}
echo "</div></body></html>";
exit;
}
$payment = json_decode($response, true);
$metadata = $payment['metadata'] ?? [];
$user_id = $metadata['user_id'] ?? null;
if (!$user_id) {
echo "<p class='error'>✗ No user_id in metadata</p>";
echo "<p class='info'>Metadata: <pre>" . htmlspecialchars(json_encode($metadata, JSON_PRETTY_PRINT)) . "</pre></p>";
echo "</div></body></html>";
exit;
}
// Parse cart items
$cart_items_json = $metadata['cart_items'] ?? '[]';
$cart_items = is_string($cart_items_json) ? json_decode($cart_items_json, true) : $cart_items_json;
if (!is_array($cart_items)) {
echo "<p class='error'>✗ Invalid cart items</p>";
echo "<p class='info'>Cart items data: " . htmlspecialchars($cart_items_json) . "</p>";
echo "</div></body></html>";
exit;
}
// Extract track items
$track_items = [];
foreach ($cart_items as $item) {
if (isset($item['type']) && $item['type'] === 'track' && isset($item['track_id'])) {
$track_items[] = ['track_id' => (int)$item['track_id']];
}
}
$added = [];
$failed = [];
foreach ($track_items as $item) {
$track_id = $item['track_id'];
// Check if already purchased
$stmt = $pdo->prepare("SELECT id FROM track_purchases WHERE user_id = ? AND track_id = ? AND stripe_payment_intent_id = ?");
$stmt->execute([$user_id, $track_id, $payment_intent_id]);
if ($stmt->fetch()) {
continue;
}
// Get track info
$stmt = $pdo->prepare("SELECT id, title, price, user_id as artist_id, status FROM music_tracks WHERE id = ?");
$stmt->execute([$track_id]);
$track = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$track || $track['status'] !== 'complete') {
$failed[] = ['track_id' => $track_id, 'error' => $track ? "Status: {$track['status']}" : 'Not found'];
continue;
}
// Check if purchased with different payment intent
$stmt = $pdo->prepare("SELECT id FROM track_purchases WHERE user_id = ? AND track_id = ?");
$stmt->execute([$user_id, $track_id]);
if ($stmt->fetch()) {
continue;
}
// Determine revenue recipient
$artist_stmt = $pdo->prepare("SELECT plan FROM users WHERE id = ?");
$artist_stmt->execute([$track['artist_id']]);
$artist = $artist_stmt->fetch(PDO::FETCH_ASSOC);
$is_free_user_track = ($artist && strtolower($artist['plan']) === 'free');
$revenue_recipient = $is_free_user_track ? 'platform' : 'artist';
$recipient_id = $is_free_user_track ? 1 : $track['artist_id'];
try {
$pdo->beginTransaction();
// Record sale
$stmt = $pdo->prepare("INSERT INTO sales (track_id, buyer_id, artist_id, amount, quantity, revenue_recipient, recipient_id, is_free_user_track, created_at) VALUES (?, ?, ?, ?, 1, ?, ?, ?, NOW())");
if (!$stmt) {
throw new Exception('Failed to prepare sales INSERT statement: ' . implode(', ', $pdo->errorInfo()));
}
$stmt->execute([$track_id, $user_id, $track['artist_id'], $track['price'], $revenue_recipient, $recipient_id, $is_free_user_track ? 1 : 0]);
// Record purchase
$stmt = $pdo->prepare("INSERT INTO track_purchases (user_id, track_id, price_paid, credits_used, payment_method, stripe_payment_intent_id, purchase_date) VALUES (?, ?, ?, 0, 'stripe', ?, NOW())");
if (!$stmt) {
throw new Exception('Failed to prepare track_purchases INSERT statement: ' . implode(', ', $pdo->errorInfo()));
}
$stmt->execute([$user_id, $track_id, $track['price'], $payment_intent_id]);
$purchase_id = $pdo->lastInsertId();
// Add to library
try {
$stmt = $pdo->prepare("INSERT IGNORE INTO user_library (user_id, track_id, purchase_date) VALUES (?, ?, NOW())");
if ($stmt) {
$stmt->execute([$user_id, $track_id]);
}
} catch (Exception $e) {
// Table might not exist - log but don't fail
error_log("fix_stephane_missing_purchases: Could not add to user_library: " . $e->getMessage());
}
$pdo->commit();
$added[] = ['track_id' => $track_id, 'title' => $track['title'], 'purchase_id' => $purchase_id];
} catch (Exception $e) {
if ($pdo->inTransaction()) {
$pdo->rollBack();
}
$error_msg = $e->getMessage();
error_log("fix_stephane_missing_purchases: Error processing track {$track_id}: " . $error_msg);
$failed[] = ['track_id' => $track_id, 'error' => $error_msg];
} catch (Error $e) {
if ($pdo->inTransaction()) {
$pdo->rollBack();
}
$error_msg = $e->getMessage();
error_log("fix_stephane_missing_purchases: Fatal error processing track {$track_id}: " . $error_msg);
$failed[] = ['track_id' => $track_id, 'error' => 'Fatal error: ' . $error_msg];
}
}
echo "<h3>Results:</h3>";
if (!empty($added)) {
echo "<p class='success'>✓ Added " . count($added) . " purchase(s):</p>";
echo "<table>";
echo "<tr><th>Purchase ID</th><th>Track ID</th><th>Title</th></tr>";
foreach ($added as $item) {
echo "<tr><td>{$item['purchase_id']}</td><td>{$item['track_id']}</td><td>{$item['title']}</td></tr>";
}
echo "</table>";
}
if (!empty($failed)) {
echo "<p class='error'>✗ Failed " . count($failed) . " purchase(s):</p>";
echo "<table>";
echo "<tr><th>Track ID</th><th>Error</th></tr>";
foreach ($failed as $item) {
echo "<tr><td>{$item['track_id']}</td><td>{$item['error']}</td></tr>";
}
echo "</table>";
}
echo "</div>";
}
echo "<p><a href='investigate_stephane_10_tracks.php' style='color: #667eea;'>← Back to Investigation</a></p>";
echo "</body></html>";
?>