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/

Upload File : current_dir [ Writeable ] document_root [ Writeable ]

<?php
/**
 * Stripe Purchase Reconciliation Script
 * 
 * This script compares Stripe payment intents with database purchases
 * to identify missing or mismatched purchases.
 * 
 * Usage: Run via browser (admin only) or cron job
 * 
 * Features:
 * - Fetches recent successful Stripe payments
 * - Compares with database purchases
 * - Identifies missing purchases
 * - Generates fix scripts
 * - Sends alerts for discrepancies
 */

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 "<h2>🔍 Stripe Purchase Reconciliation</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; }
    .missing { background: #5a1a1a; }
    .found { background: #2d5016; }
</style>";

// Get date range (last 30 days by default)
$days_back = intval($_GET['days'] ?? 30);
$start_date = date('Y-m-d', strtotime("-{$days_back} days"));

echo "<div class='section'>";
echo "<h3>📊 Reconciliation Parameters</h3>";
echo "<p>Checking Stripe payments from: <strong>{$start_date}</strong> to <strong>now</strong></p>";
echo "<p>Days to check: <strong>{$days_back}</strong></p>";
echo "</div>";

// Fetch recent successful payment intents from Stripe
echo "<div class='section'>";
echo "<h3>🔌 Fetching Stripe Payment Intents...</h3>";

$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, 'https://api.stripe.com/v1/payment_intents?limit=100&created[gte]=' . strtotime($start_date));
curl_setopt($ch, CURLOPT_HTTPHEADER, ['Authorization: Bearer ' . $stripe_secret]);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$stripe_response = curl_exec($ch);
$http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close($ch);

if ($http_code !== 200) {
    die("<p class='error'>Failed to fetch from Stripe API. HTTP Code: {$http_code}</p>");
}

$stripe_data = json_decode($stripe_response, true);
$payment_intents = $stripe_data['data'] ?? [];

// Filter only succeeded payments with metadata
$succeeded_payments = [];
foreach ($payment_intents as $pi) {
    if ($pi['status'] === 'succeeded' && !empty($pi['metadata'])) {
        $metadata = $pi['metadata'];
        // Only check payments with user_id and cart items or track purchases
        if (isset($metadata['user_id']) && 
            (isset($metadata['cart_items']) || isset($metadata['track_id']) || isset($metadata['payment_type']))) {
            $succeeded_payments[] = $pi;
        }
    }
}

echo "<p class='success'>✓ Found <strong>" . count($succeeded_payments) . "</strong> relevant successful payments</p>";
echo "</div>";

// Compare with database
echo "<div class='section'>";
echo "<h3>🔍 Comparing with Database...</h3>";

$discrepancies = [];
$matched = 0;

foreach ($succeeded_payments as $payment) {
    $payment_intent_id = $payment['id'];
    $metadata = $payment['metadata'];
    $user_id = $metadata['user_id'] ?? null;
    $amount = $payment['amount'] / 100; // Convert from cents
    
    if (!$user_id) {
        continue;
    }
    
    // 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 = [];
    }
    
    // Count expected track purchases
    $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' => $amount
        ];
    }
    
    // Check database for 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]);
    $db_purchases = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    $found_track_ids = array_column($db_purchases, 'track_id');
    $expected_track_ids = array_column($expected_tracks, 'track_id');
    
    // Check for discrepancies
    $missing_tracks = [];
    $extra_tracks = [];
    
    foreach ($expected_tracks as $expected) {
        if (!in_array($expected['track_id'], $found_track_ids)) {
            $missing_tracks[] = $expected;
        }
    }
    
    foreach ($db_purchases as $db_purchase) {
        if (!in_array($db_purchase['track_id'], $expected_track_ids)) {
            $extra_tracks[] = $db_purchase;
        }
    }
    
    // Check user_id mismatch
    $user_mismatch = false;
    foreach ($db_purchases as $db_purchase) {
        if ($db_purchase['user_id'] != $user_id) {
            $user_mismatch = true;
            break;
        }
    }
    
    if (!empty($missing_tracks) || !empty($extra_tracks) || $user_mismatch || count($expected_tracks) != count($db_purchases)) {
        $discrepancies[] = [
            'payment_intent_id' => $payment_intent_id,
            'user_id' => $user_id,
            'amount' => $amount,
            'created' => date('Y-m-d H:i:s', $payment['created']),
            'expected_tracks' => $expected_tracks,
            'found_tracks' => $db_purchases,
            'missing_tracks' => $missing_tracks,
            'extra_tracks' => $extra_tracks,
            'user_mismatch' => $user_mismatch,
            'metadata' => $metadata
        ];
    } else {
        $matched++;
    }
}

echo "<p class='info'>Matched: <strong>{$matched}</strong> payments</p>";
echo "<p class='" . (empty($discrepancies) ? 'success' : 'error') . "'>Discrepancies: <strong>" . count($discrepancies) . "</strong></p>";
echo "</div>";

// Display discrepancies
if (!empty($discrepancies)) {
    echo "<div class='section'>";
    echo "<h3>⚠️ DISCREPANCIES FOUND</h3>";
    
    foreach ($discrepancies as $disc) {
        echo "<div style='margin: 20px 0; padding: 15px; background: #3a1a1a; border-left: 4px solid #e53e3e;'>";
        echo "<h4>Payment Intent: <code>{$disc['payment_intent_id']}</code></h4>";
        echo "<p><strong>User ID:</strong> {$disc['user_id']} | <strong>Amount:</strong> \${$disc['amount']} | <strong>Date:</strong> {$disc['created']}</p>";
        
        if (!empty($disc['missing_tracks'])) {
            echo "<p class='error'><strong>Missing Tracks (" . count($disc['missing_tracks']) . "):</strong></p>";
            echo "<ul>";
            foreach ($disc['missing_tracks'] as $missing) {
                echo "<li>Track ID: {$missing['track_id']} - {$missing['title']} (\${$missing['price']})</li>";
            }
            echo "</ul>";
        }
        
        if (!empty($disc['extra_tracks'])) {
            echo "<p class='warning'><strong>Extra Tracks (not in Stripe metadata):</strong></p>";
            echo "<ul>";
            foreach ($disc['extra_tracks'] as $extra) {
                echo "<li>Track ID: {$extra['track_id']} - {$extra['track_title']} (\${$extra['price_paid']})</li>";
            }
            echo "</ul>";
        }
        
        if ($disc['user_mismatch']) {
            echo "<p class='error'><strong>⚠️ User ID Mismatch!</strong></p>";
        }
        
        // Add fix link
        echo "<p style='margin-top: 15px;'>";
        echo "<a href='/fix_purchase_discrepancies.php?payment_intent_id=" . urlencode($disc['payment_intent_id']) . "' style='padding: 10px 20px; background: #48bb78; color: white; text-decoration: none; border-radius: 5px; display: inline-block;'>🔧 Fix This Payment Intent</a>";
        echo "</p>";
        
        echo "</div>";
    }
    
    echo "</div>";
    
    // Generate fix script (for reference)
    echo "<div class='section'>";
    echo "<h3>🔧 Fix Options</h3>";
    echo "<p>To automatically fix discrepancies, use the <strong>Fix This Payment Intent</strong> links above, or:</p>";
    echo "<p><a href='/fix_purchase_discrepancies.php' style='color: #667eea;'>Go to Fix Purchase Discrepancies Tool</a></p>";
    echo "<p>Click below to generate a manual fix script (for reference):</p>";
    echo "<button onclick='generateFixScript()' style='padding: 10px 20px; background: #667eea; color: white; border: none; border-radius: 5px; cursor: pointer;'>Generate Fix Script (Reference)</button>";
    echo "<pre id='fixScript' style='display: none; background: #1a1a1a; padding: 15px; margin-top: 10px; overflow-x: auto;'></pre>";
    echo "</div>";
    
    // Log discrepancies
    $log_entry = [
        'timestamp' => date('Y-m-d H:i:s'),
        'action' => 'reconciliation_run',
        'days_checked' => $days_back,
        'total_payments' => count($succeeded_payments),
        'matched' => $matched,
        'discrepancies_count' => count($discrepancies),
        'discrepancies' => $discrepancies
    ];
    
    $log_file = __DIR__ . '/logs/reconciliation.log';
    if (!is_dir(__DIR__ . '/logs')) {
        mkdir(__DIR__ . '/logs', 0755, true);
    }
    file_put_contents($log_file, json_encode($log_entry) . "\n", FILE_APPEND | LOCK_EX);
    
} else {
    echo "<div class='section'>";
    echo "<p class='success'><strong>✅ No discrepancies found!</strong> All Stripe payments match database purchases.</p>";
    echo "</div>";
}

echo "<script>
function generateFixScript() {
    const discrepancies = " . json_encode($discrepancies) . ";
    let script = '<?php\\n';
    script += '// Auto-generated fix script\\n';
    script += '// Generated: ' + new Date().toISOString() + '\\n\\n';
    script += 'require_once \\'config/database.php\\';\\n';
    script += '\\$pdo = getDBConnection();\\n\\n';
    
    discrepancies.forEach((disc, idx) => {
        disc.missing_tracks.forEach((track, trackIdx) => {
            script += `// Fix for Payment Intent: ${disc.payment_intent_id}, Track: ${track.track_id}\\n`;
            script += `// User ID: ${disc.user_id}, Price: \$${track.price}\\n`;
            script += `// Add this to fix_missing_purchase.php or run manually\\n`;
            script += `// fix_missing_purchase.php?user_id=${disc.user_id}&track_id=${track.track_id}&price=${track.price}&payment_intent_id=${disc.payment_intent_id}&confirm=yes\\n\\n`;
        });
    });
    
    document.getElementById('fixScript').style.display = 'block';
    document.getElementById('fixScript').textContent = script;
}
</script>";

echo "<hr>";
echo "<p><a href='/admin.php' style='color: #667eea;'>← Back to Admin</a> | ";
echo "<a href='?days=7' style='color: #667eea;'>Last 7 days</a> | ";
echo "<a href='?days=30' style='color: #667eea;'>Last 30 days</a> | ";
echo "<a href='?days=90' style='color: #667eea;'>Last 90 days</a></p>";
?>