 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 Purchase Discrepancies Script
*
* Actually fixes missing purchases identified by reconciliation
* Can fix specific payment intent or all discrepancies
*/
// Enable error reporting for debugging
error_reporting(E_ALL);
ini_set('display_errors', 1);
ini_set('log_errors', 1);
// Set error handler to catch fatal errors
register_shutdown_function(function() {
$error = error_get_last();
if ($error !== NULL && in_array($error['type'], [E_ERROR, E_PARSE, E_CORE_ERROR, E_COMPILE_ERROR])) {
http_response_code(500);
echo "<!DOCTYPE html><html><head><meta charset='UTF-8'><title>Error</title></head><body>";
echo "<h1 style='color: red;'>Fatal Error</h1>";
echo "<p><strong>File:</strong> " . htmlspecialchars($error['file']) . "</p>";
echo "<p><strong>Line:</strong> " . htmlspecialchars($error['line']) . "</p>";
echo "<p><strong>Message:</strong> " . htmlspecialchars($error['message']) . "</p>";
echo "</body></html>";
}
});
try {
session_start();
} catch (Exception $e) {
die("Session error: " . htmlspecialchars($e->getMessage()));
}
try {
require_once 'config/database.php';
} catch (Exception $e) {
die("Database config error: " . htmlspecialchars($e->getMessage()));
}
// Check if admin
if (!isset($_SESSION['is_admin']) || !$_SESSION['is_admin']) {
die("Admin access required");
}
try {
$pdo = getDBConnection();
} catch (Exception $e) {
die("Database connection error: " . htmlspecialchars($e->getMessage()));
}
$payment_intent_id = $_GET['payment_intent_id'] ?? null;
$user_name = $_GET['user_name'] ?? null;
$auto_fix = $_GET['auto_fix'] ?? 'false';
echo "<!DOCTYPE html><html><head><meta charset='UTF-8'><title>Fix Purchase Discrepancies</title></head><body>";
echo "<h2>🔧 Fix Purchase Discrepancies</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; }
button { padding: 10px 20px; background: #667eea; color: white; border: none; border-radius: 5px; cursor: pointer; margin: 5px; }
button:hover { background: #5568d3; }
.fixed { background: #2d5016; }
.failed { background: #5a1a1a; }
</style>";
// If specific payment intent provided, fix it
if ($payment_intent_id) {
echo "<div class='section'>";
echo "<h3>Fixing Payment Intent: {$payment_intent_id}</h3>";
// Get Stripe payment intent data
try {
$stripe_secret = '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';
$ch = curl_init();
if ($ch === false) {
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_SSL_VERIFYPEER, true);
$stripe_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);
}
if ($http_code !== 200) {
$error_data = json_decode($stripe_response, true);
$error_msg = $error_data['error']['message'] ?? "HTTP Error: {$http_code}";
throw new Exception("Stripe API error: {$error_msg}");
}
if (empty($stripe_response)) {
throw new Exception("Empty response from Stripe API");
}
$payment = json_decode($stripe_response, true);
if (json_last_error() !== JSON_ERROR_NONE) {
throw new Exception("JSON decode error: " . json_last_error_msg());
}
if (!isset($payment['id'])) {
throw new Exception("Invalid payment intent response from Stripe");
}
$metadata = $payment['metadata'] ?? [];
$user_id = $metadata['user_id'] ?? null;
} catch (Exception $e) {
die("<p class='error'>Error fetching payment intent: " . htmlspecialchars($e->getMessage()) . "</p>");
}
if (!$user_id) {
die("<p class='error'>No user_id found in payment intent metadata. Metadata: " . htmlspecialchars(json_encode($metadata)) . "</p>");
}
// Get user info
try {
$stmt = $pdo->prepare("SELECT id, name, email FROM users WHERE id = ?");
$stmt->execute([$user_id]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$user) {
die("<p class='error'>User not found with ID: {$user_id}</p>");
}
} catch (Exception $e) {
die("<p class='error'>Database error: " . htmlspecialchars($e->getMessage()) . "</p>");
}
echo "<p><strong>User:</strong> {$user['name']} (ID: {$user_id})</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)) {
$cart_items = [];
}
// Get expected tracks
$expected_tracks = [];
foreach ($cart_items as $item) {
if (isset($item['type']) && $item['type'] === 'track' && isset($item['track_id'])) {
$expected_tracks[] = [
'track_id' => $item['track_id'],
'title' => $item['title'] ?? 'Unknown',
'price' => ($item['amount'] ?? 0) / 100
];
}
}
// Also check for single track purchase
if (isset($metadata['track_id']) && !empty($metadata['track_id'])) {
$expected_tracks[] = [
'track_id' => $metadata['track_id'],
'title' => $metadata['track_title'] ?? 'Unknown',
'price' => ($payment['amount'] ?? 0) / 100
];
}
echo "<p><strong>Expected Tracks:</strong> " . count($expected_tracks) . "</p>";
// Get current purchases with this payment intent
$stmt = $pdo->prepare("
SELECT
tp.id,
tp.track_id,
tp.user_id,
tp.price_paid,
mt.title as track_title
FROM track_purchases tp
JOIN music_tracks mt ON tp.track_id = mt.id
WHERE tp.stripe_payment_intent_id = ?
");
$stmt->execute([$payment_intent_id]);
$current_purchases = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "<p><strong>Current Purchases:</strong> " . count($current_purchases) . "</p>";
// Find missing tracks
$current_track_ids = array_column($current_purchases, 'track_id');
$expected_track_ids = array_column($expected_tracks, 'track_id');
$missing_tracks = [];
foreach ($expected_tracks as $expected) {
if (!in_array($expected['track_id'], $current_track_ids)) {
$missing_tracks[] = $expected;
}
}
// Find wrong tracks (in DB but not in expected)
$wrong_tracks = [];
foreach ($current_purchases as $purchase) {
if (!in_array($purchase['track_id'], $expected_track_ids)) {
$wrong_tracks[] = $purchase;
}
}
if (empty($missing_tracks) && empty($wrong_tracks)) {
echo "<p class='success'>✅ No discrepancies found. All purchases are correct!</p>";
echo "</div>";
} else {
echo "<div class='section'>";
echo "<h3>Discrepancies Found</h3>";
if (!empty($missing_tracks)) {
echo "<p class='error'><strong>Missing Tracks (" . count($missing_tracks) . "):</strong></p>";
echo "<ul>";
foreach ($missing_tracks as $missing) {
echo "<li>Track ID: {$missing['track_id']} - {$missing['title']} (\${$missing['price']})</li>";
}
echo "</ul>";
}
if (!empty($wrong_tracks)) {
echo "<p class='warning'><strong>Wrong Tracks (" . count($wrong_tracks) . "):</strong></p>";
echo "<ul>";
foreach ($wrong_tracks as $wrong) {
echo "<li>Purchase ID: {$wrong['id']} - Track ID: {$wrong['track_id']} - {$wrong['track_title']} (\${$wrong['price_paid']})</li>";
}
echo "</ul>";
}
// Auto-fix if requested
if ($auto_fix === 'yes' || isset($_POST['fix'])) {
echo "<div class='section'>";
echo "<h3>🔧 Applying Fixes...</h3>";
$fixed = [];
$failed = [];
// Remove wrong purchases
foreach ($wrong_tracks as $wrong) {
try {
$pdo->beginTransaction();
// Delete from track_purchases
$stmt = $pdo->prepare("DELETE FROM track_purchases WHERE id = ?");
$stmt->execute([$wrong['id']]);
// Delete from user_library
$stmt = $pdo->prepare("DELETE FROM user_library WHERE user_id = ? AND track_id = ?");
$stmt->execute([$user_id, $wrong['track_id']]);
// Delete from sales
$stmt = $pdo->prepare("DELETE FROM sales WHERE buyer_id = ? AND track_id = ?");
$stmt->execute([$user_id, $wrong['track_id']]);
$pdo->commit();
$fixed[] = [
'action' => 'removed',
'purchase_id' => $wrong['id'],
'track_id' => $wrong['track_id'],
'track_title' => $wrong['track_title']
];
} catch (Exception $e) {
$pdo->rollBack();
$failed[] = [
'action' => 'remove_failed',
'purchase_id' => $wrong['id'],
'error' => $e->getMessage()
];
}
}
// Add missing purchases
foreach ($missing_tracks as $missing) {
try {
// Check if track exists
$stmt = $pdo->prepare("SELECT id, title, price, user_id as artist_id, status FROM music_tracks WHERE id = ?");
$stmt->execute([$missing['track_id']]);
$track = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$track) {
$failed[] = [
'action' => 'add_failed',
'track_id' => $missing['track_id'],
'error' => 'Track not found in database'
];
continue;
}
if ($track['status'] !== 'complete') {
$failed[] = [
'action' => 'add_failed',
'track_id' => $missing['track_id'],
'error' => 'Track status is not complete'
];
continue;
}
$pdo->beginTransaction();
// Check if already purchased (shouldn't be, but check anyway)
$stmt = $pdo->prepare("SELECT id FROM track_purchases WHERE user_id = ? AND track_id = ?");
$stmt->execute([$user_id, $missing['track_id']]);
if ($stmt->fetch()) {
$pdo->rollBack();
$failed[] = [
'action' => 'add_failed',
'track_id' => $missing['track_id'],
'error' => 'Track already purchased'
];
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'];
// 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())
");
$stmt->execute([
$missing['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())
");
$stmt->execute([
$user_id,
$missing['track_id'],
$track['price'],
$payment_intent_id
]);
// Add to library
$stmt = $pdo->prepare("
INSERT IGNORE INTO user_library (user_id, track_id, purchase_date)
VALUES (?, ?, NOW())
");
$stmt->execute([$user_id, $missing['track_id']]);
$pdo->commit();
$fixed[] = [
'action' => 'added',
'track_id' => $missing['track_id'],
'track_title' => $track['title'],
'price' => $track['price']
];
} catch (Exception $e) {
if ($pdo->inTransaction()) {
$pdo->rollBack();
}
$failed[] = [
'action' => 'add_failed',
'track_id' => $missing['track_id'],
'error' => $e->getMessage()
];
}
}
// Display results
if (!empty($fixed)) {
echo "<p class='success'><strong>✅ Fixed (" . count($fixed) . "):</strong></p>";
echo "<table class='fixed'>";
echo "<tr><th>Action</th><th>Track ID</th><th>Details</th></tr>";
foreach ($fixed as $fix) {
$track_title_or_id = isset($fix['track_title']) ? $fix['track_title'] : (isset($fix['purchase_id']) ? $fix['purchase_id'] : 'N/A');
echo "<tr>";
echo "<td>{$fix['action']}</td>";
echo "<td>{$fix['track_id']}</td>";
echo "<td>" . htmlspecialchars($track_title_or_id) . "</td>";
echo "</tr>";
}
echo "</table>";
}
if (!empty($failed)) {
echo "<p class='error'><strong>❌ Failed (" . count($failed) . "):</strong></p>";
echo "<table class='failed'>";
echo "<tr><th>Action</th><th>Track ID</th><th>Error</th></tr>";
foreach ($failed as $fail) {
$track_or_purchase_id = isset($fail['track_id']) ? $fail['track_id'] : (isset($fail['purchase_id']) ? $fail['purchase_id'] : 'N/A');
echo "<tr>";
echo "<td>{$fail['action']}</td>";
echo "<td>{$track_or_purchase_id}</td>";
echo "<td>" . htmlspecialchars($fail['error']) . "</td>";
echo "</tr>";
}
echo "</table>";
}
echo "</div>";
} else {
// Show fix button
echo "<form method='POST' style='margin-top: 20px;'>";
echo "<button type='submit' name='fix' value='1' style='background: #48bb78;'>🔧 Fix All Discrepancies</button>";
echo "</form>";
echo "<p class='warning'>⚠️ This will remove wrong purchases and add missing ones. Make sure you've reviewed the discrepancies above.</p>";
}
echo "</div>";
}
echo "</div>";
} else {
// Show form to search
echo "<div class='section'>";
echo "<h3>Search for Payment Intent</h3>";
echo "<form method='GET'>";
echo "<p><label>Payment Intent ID: <input type='text' name='payment_intent_id' style='padding: 8px; width: 400px;' placeholder='pm_1SW5taD0zXLMB4gHVB9lJWVz'></label></p>";
echo "<p><label>User Name (optional): <input type='text' name='user_name' style='padding: 8px; width: 300px;' placeholder='Stephan Bergeron'></label></p>";
echo "<p><button type='submit'>Search</button></p>";
echo "</form>";
echo "</div>";
// If user name provided, find their payment intents
if ($user_name) {
$stmt = $pdo->prepare("SELECT id, name FROM users WHERE name LIKE ? LIMIT 5");
$stmt->execute(['%' . $user_name . '%']);
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (!empty($users)) {
echo "<div class='section'>";
echo "<h3>Found Users:</h3>";
foreach ($users as $user) {
echo "<p><a href='?user_id={$user['id']}' style='color: #667eea;'>{$user['name']} (ID: {$user['id']})</a></p>";
}
echo "</div>";
}
}
}
echo "<hr>";
echo "<p><a href='/admin.php' style='color: #667eea;'>← Back to Admin</a> | ";
echo "<a href='/reconcile_stripe_purchases.php' style='color: #667eea;'>Run Reconciliation</a></p>";
echo "</body></html>";
?>