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
/**
 * Comprehensive Investigation Report for User ID 5's Purchases
 * Helps identify missing purchases and provides tools to add them
 */

session_start();
require_once 'config/database.php';

// Check if admin
if (!isset($_SESSION['is_admin']) || !$_SESSION['is_admin']) {
    die("Admin access required");
}

$pdo = getDBConnection();
$user_id = 5;

echo "<h2>🔍 Comprehensive Investigation: User ID 5 (Stephane Bergeron) Purchases</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; }
</style>";

// Get user info
$stmt = $pdo->prepare("SELECT id, name, email, created_at FROM users WHERE id = ?");
$stmt->execute([$user_id]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

echo "<div class='section'>";
echo "<h3>👤 User Information</h3>";
echo "<p><strong>ID:</strong> {$user['id']}</p>";
echo "<p><strong>Name:</strong> {$user['name']}</p>";
echo "<p><strong>Email:</strong> {$user['email']}</p>";
echo "<p><strong>Account Created:</strong> {$user['created_at']}</p>";
echo "</div>";

// Current purchases
echo "<div class='section'>";
echo "<h3>📦 Current Purchases in Database</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.user_id = ?
    ORDER BY tp.purchase_date DESC
");
$stmt->execute([$user_id]);
$purchases = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo "<p class='info'>Total purchases found: <strong>" . count($purchases) . "</strong></p>";

if (empty($purchases)) {
    echo "<p class='error'>No purchases found</p>";
} else {
    echo "<table>";
    echo "<tr><th>Purchase ID</th><th>Track ID</th><th>Track Title</th><th>Created By</th><th>Price</th><th>Payment Method</th><th>Stripe ID</th><th>Date</th></tr>";
    foreach ($purchases as $p) {
        $payment_method = $p['payment_method'] ?: 'N/A';
        $stripe_id = $p['stripe_payment_intent_id'] ?: 'N/A';
        $method_class = ($payment_method === 'credits') ? 'error' : 'success';
        echo "<tr>";
        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']} (ID: {$p['track_creator_id']})</td>";
        echo "<td>\${$p['price_paid']}</td>";
        echo "<td class='{$method_class}'>{$payment_method}</td>";
        echo "<td>{$stripe_id}</td>";
        echo "<td>{$p['purchase_date']}</td>";
        echo "</tr>";
    }
    echo "</table>";
}
echo "</div>";

// Check library
echo "<div class='section'>";
echo "<h3>📚 Tracks in User Library</h3>";
$stmt = $pdo->prepare("
    SELECT 
        ul.*,
        mt.title as track_title,
        mt.user_id as track_creator_id
    FROM user_library ul
    JOIN music_tracks mt ON ul.track_id = mt.id
    WHERE ul.user_id = ?
    ORDER BY ul.purchase_date DESC
");
$stmt->execute([$user_id]);
$library = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo "<p class='info'>Tracks in library: <strong>" . count($library) . "</strong></p>";

// Check for library entries without purchases
$missing_from_purchases = [];
foreach ($library as $l) {
    $check = $pdo->prepare("SELECT id FROM track_purchases WHERE user_id = ? AND track_id = ?");
    $check->execute([$user_id, $l['track_id']]);
    if (!$check->fetch()) {
        $missing_from_purchases[] = $l;
    }
}

if (!empty($missing_from_purchases)) {
    echo "<p class='warning'>⚠️ Found " . count($missing_from_purchases) . " track(s) in library without purchase records:</p>";
    echo "<ul>";
    foreach ($missing_from_purchases as $m) {
        echo "<li>Track ID: <a href='/track.php?id={$m['track_id']}' class='track-link'>{$m['track_id']}</a> | Title: " . htmlspecialchars($m['track_title'] ?: 'N/A') . " | Added: {$m['purchase_date']}</li>";
    }
    echo "</ul>";
} else {
    echo "<p class='success'>✓ All library entries have corresponding purchase records</p>";
}
echo "</div>";

// Check sales
echo "<div class='section'>";
echo "<h3>💰 Sales Records</h3>";
$stmt = $pdo->prepare("
    SELECT 
        s.*,
        mt.title as track_title
    FROM sales s
    JOIN music_tracks mt ON s.track_id = mt.id
    WHERE s.buyer_id = ?
    ORDER BY s.created_at DESC
");
$stmt->execute([$user_id]);
$sales = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo "<p class='info'>Sales records: <strong>" . count($sales) . "</strong></p>";

// Check for sales without purchases
$missing_from_sales = [];
foreach ($sales as $s) {
    $check = $pdo->prepare("SELECT id FROM track_purchases WHERE user_id = ? AND track_id = ?");
    $check->execute([$user_id, $s['track_id']]);
    if (!$check->fetch()) {
        $missing_from_sales[] = $s;
    }
}

if (!empty($missing_from_sales)) {
    echo "<p class='warning'>⚠️ Found " . count($missing_from_sales) . " sale(s) without purchase records:</p>";
    echo "<ul>";
    foreach ($missing_from_sales as $m) {
        echo "<li>Sale ID: {$m['id']} | Track ID: <a href='/track.php?id={$m['track_id']}' class='track-link'>{$m['track_id']}</a> | Title: " . htmlspecialchars($m['track_title'] ?: 'N/A') . " | Amount: \${$m['amount']} | Date: {$m['created_at']}</li>";
    }
    echo "</ul>";
} else {
    echo "<p class='success'>✓ All sales have corresponding purchase records</p>";
}
echo "</div>";

// Credit transactions
echo "<div class='section'>";
echo "<h3>💳 Credit Transactions Related to Purchases</h3>";
$stmt = $pdo->prepare("
    SELECT * FROM credit_transactions 
    WHERE user_id = ? 
    AND (description LIKE '%purchase%' OR description LIKE '%track%' OR description LIKE '%buy%')
    ORDER BY created_at DESC
");
$stmt->execute([$user_id]);
$credit_txns = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo "<p class='info'>Credit transactions: <strong>" . count($credit_txns) . "</strong></p>";

$purchase_credits = array_filter($credit_txns, function($t) {
    return stripos($t['description'], 'purchased track') !== false;
});

if (!empty($purchase_credits)) {
    echo "<p class='warning'>⚠️ Found " . count($purchase_credits) . " credit transaction(s) for purchases:</p>";
    echo "<table>";
    echo "<tr><th>ID</th><th>Amount</th><th>Description</th><th>Date</th></tr>";
    foreach ($purchase_credits as $c) {
        echo "<tr>";
        echo "<td>{$c['id']}</td>";
        echo "<td>{$c['amount']}</td>";
        echo "<td>" . htmlspecialchars($c['description']) . "</td>";
        echo "<td>{$c['created_at']}</td>";
        echo "</tr>";
    }
    echo "</table>";
}
echo "</div>";

// Tracks available around purchase dates
echo "<div class='section'>";
echo "<h3>🎵 Tracks Available Around Purchase Dates (Potential Missing Purchases)</h3>";
$purchase_dates = array_unique(array_column($purchases, 'purchase_date'));
$all_available_tracks = [];

foreach ($purchase_dates as $date) {
    $date_only = substr($date, 0, 10);
    $stmt = $pdo->prepare("
        SELECT id, title, user_id, created_at, price 
        FROM music_tracks 
        WHERE created_at LIKE ? 
        AND status = 'complete' 
        AND is_public = 1 
        AND user_id != ?
        ORDER BY created_at DESC
    ");
    $stmt->execute([$date_only . '%', $user_id]);
    $tracks = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    foreach ($tracks as $t) {
        $check = $pdo->prepare("SELECT id FROM track_purchases WHERE user_id = ? AND track_id = ?");
        $check->execute([$user_id, $t['id']]);
        if (!$check->fetch()) {
            $all_available_tracks[] = $t;
        }
    }
}

if (!empty($all_available_tracks)) {
    echo "<p class='info'>Found " . count($all_available_tracks) . " track(s) available around purchase dates that weren't purchased:</p>";
    echo "<table>";
    echo "<tr><th>Track ID</th><th>Title</th><th>Created By</th><th>Price</th><th>Created</th><th>Action</th></tr>";
    foreach ($all_available_tracks as $t) {
        echo "<tr>";
        echo "<td><a href='/track.php?id={$t['id']}' class='track-link'>{$t['id']}</a></td>";
        echo "<td>" . htmlspecialchars($t['title'] ?: 'N/A') . "</td>";
        echo "<td>User {$t['user_id']}</td>";
        echo "<td>\${$t['price']}</td>";
        echo "<td>{$t['created_at']}</td>";
        echo "<td><a href='/fix_missing_purchase.php?user_name=" . urlencode($user['name']) . "&track_id={$t['id']}&price={$t['price']}&confirm=yes' style='color: #48bb78;'>Add Purchase</a></td>";
        echo "</tr>";
    }
    echo "</table>";
} else {
    echo "<p class='info'>No additional tracks found around purchase dates</p>";
}
echo "</div>";

// Summary and recommendations
echo "<div class='section'>";
echo "<h3>📊 Summary & Recommendations</h3>";

$total_purchases = count($purchases);
$total_library = count($library);
$total_sales = count($sales);
$missing_purchases = count($missing_from_purchases) + count($missing_from_sales);

echo "<ul>";
echo "<li><strong>Total Purchases:</strong> {$total_purchases}</li>";
echo "<li><strong>Tracks in Library:</strong> {$total_library}</li>";
echo "<li><strong>Sales Records:</strong> {$total_sales}</li>";
echo "<li><strong>Missing Purchase Records:</strong> {$missing_purchases}</li>";
echo "</ul>";

if ($total_purchases == $total_library && $total_purchases == $total_sales && $missing_purchases == 0) {
    echo "<p class='success'><strong>✅ All records are consistent!</strong></p>";
    echo "<p>If the user claims 4 purchases but only 2 are recorded, possible reasons:</p>";
    echo "<ul>";
    echo "<li>The other 2 purchases were attempted but not completed</li>";
    echo "<li>The other 2 purchases were made but webhooks failed</li>";
    echo "<li>The user is counting something else (e.g., tracks they created)</li>";
    echo "<li>Need track IDs and dates to manually add them</li>";
    echo "</ul>";
} else {
    echo "<p class='warning'><strong>⚠️ Inconsistencies found!</strong></p>";
    echo "<p>Please review the missing records above and use the 'Add Purchase' links to fix them.</p>";
}

echo "</div>";

echo "<hr>";
echo "<p><a href='/admin.php?tab=tracks' style='color: #667eea;'>← Back to Admin</a> | ";
echo "<a href='/fix_user_5_purchases.php' style='color: #667eea;'>Run Fix Script</a> | ";
echo "<a href='/account_settings.php?tab=purchases' style='color: #667eea;'>View Purchases Page</a></p>";
?>