 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/public_html/ |
<?php
/**
* Diagnostic script to check Kat Zen's purchases and transaction traces
*/
session_start();
require_once 'config/database.php';
// Check if admin
if (!isset($_SESSION['is_admin']) || !$_SESSION['is_admin']) {
die("Admin access required");
}
$pdo = getDBConnection();
echo "<h2>Kat Zen Purchase Diagnostic</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; }
</style>";
// 1. Find Kat Zen user
echo "<h3>1. Finding Kat Zen User</h3>";
$kat_zen = $pdo->prepare("
SELECT id, name, email, created_at, credits, plan
FROM users
WHERE name LIKE '%Kat%Zen%' OR name LIKE '%katzen%' OR name LIKE '%kat zen%' OR email LIKE '%kat%zen%'
ORDER BY id
");
$kat_zen->execute();
$users = $kat_zen->fetchAll(PDO::FETCH_ASSOC);
if (empty($users)) {
echo "<p class='error'>✗ No user found matching 'Kat Zen'</p>";
echo "<p>Searching all users with 'kat' or 'zen' in name...</p>";
$all_users = $pdo->query("SELECT id, name, email FROM users WHERE name LIKE '%kat%' OR name LIKE '%zen%' LIMIT 20")->fetchAll();
if (!empty($all_users)) {
echo "<table>";
echo "<tr><th>ID</th><th>Name</th><th>Email</th></tr>";
foreach ($all_users as $u) {
echo "<tr><td>{$u['id']}</td><td>{$u['name']}</td><td>{$u['email']}</td></tr>";
}
echo "</table>";
}
exit;
}
foreach ($users as $user) {
$user_id = $user['id'];
echo "<p class='success'>✓ Found user: <strong>{$user['name']}</strong> (ID: {$user_id}, Email: {$user['email']})</p>";
echo "<p>Created: {$user['created_at']} | Credits: {$user['credits']} | Plan: {$user['plan']}</p>";
// 2. Check track_purchases table
echo "<h3>2. Purchases in track_purchases Table</h3>";
$purchases = $pdo->prepare("
SELECT
tp.*,
mt.title as track_title,
mt.price as track_price,
artist.name as artist_name
FROM track_purchases tp
LEFT JOIN music_tracks mt ON tp.track_id = mt.id
LEFT JOIN users artist ON mt.user_id = artist.id
WHERE tp.user_id = ?
ORDER BY tp.purchase_date DESC
");
$purchases->execute([$user_id]);
$user_purchases = $purchases->fetchAll(PDO::FETCH_ASSOC);
echo "<p><strong>Total purchases found:</strong> <span class='info' style='font-size: 24px;'>{$purchases->rowCount()}</span></p>";
if (empty($user_purchases)) {
echo "<p class='error'>✗ No purchases found in track_purchases table</p>";
} else {
echo "<table>";
echo "<tr><th>Purchase ID</th><th>Track ID</th><th>Track Title</th><th>Artist</th><th>Price Paid</th><th>Purchase Date</th><th>Payment Method</th></tr>";
foreach ($user_purchases as $p) {
$payment_method = isset($p['payment_method']) ? $p['payment_method'] : 'N/A';
echo "<tr>";
echo "<td>{$p['id']}</td>";
echo "<td>{$p['track_id']}</td>";
echo "<td>" . htmlspecialchars($p['track_title'] ?? 'Unknown') . "</td>";
echo "<td>" . htmlspecialchars($p['artist_name'] ?? 'Unknown') . "</td>";
echo "<td>\${$p['price_paid']}</td>";
echo "<td>{$p['purchase_date']}</td>";
echo "<td>{$payment_method}</td>";
echo "</tr>";
}
echo "</table>";
}
// 3. Check sales table
echo "<h3>3. Sales in sales Table</h3>";
$sales = $pdo->prepare("
SELECT
s.*,
mt.title as track_title,
artist.name as artist_name
FROM sales s
LEFT JOIN music_tracks mt ON s.track_id = mt.id
LEFT JOIN users artist ON s.artist_id = artist.id
WHERE s.buyer_id = ?
ORDER BY s.created_at DESC
");
$sales->execute([$user_id]);
$user_sales = $sales->fetchAll(PDO::FETCH_ASSOC);
echo "<p><strong>Total sales found:</strong> <span class='info' style='font-size: 24px;'>{$sales->rowCount()}</span></p>";
if (!empty($user_sales)) {
echo "<table>";
echo "<tr><th>Sale ID</th><th>Track ID</th><th>Track Title</th><th>Artist</th><th>Amount</th><th>Date</th><th>Revenue To</th></tr>";
foreach ($user_sales as $s) {
echo "<tr>";
echo "<td>{$s['id']}</td>";
echo "<td>{$s['track_id']}</td>";
echo "<td>" . htmlspecialchars($s['track_title'] ?? 'Unknown') . "</td>";
echo "<td>" . htmlspecialchars($s['artist_name'] ?? 'Unknown') . "</td>";
echo "<td>\${$s['amount']}</td>";
echo "<td>{$s['created_at']}</td>";
echo "<td>{$s['revenue_recipient']}</td>";
echo "</tr>";
}
echo "</table>";
}
// 4. Check track 182 specifically
echo "<h3>4. Track 182 (WHISPER THROUGH THE BONES) Details</h3>";
$track_182 = $pdo->prepare("
SELECT
mt.*,
artist.name as artist_name,
COUNT(tp.id) as purchase_count
FROM music_tracks mt
LEFT JOIN users artist ON mt.user_id = artist.id
LEFT JOIN track_purchases tp ON mt.id = tp.track_id
WHERE mt.id = 182
GROUP BY mt.id
");
$track_182->execute();
$track = $track_182->fetch(PDO::FETCH_ASSOC);
if ($track) {
echo "<p class='success'>✓ Track 182 exists</p>";
echo "<p><strong>Title:</strong> " . htmlspecialchars($track['title']) . "</p>";
echo "<p><strong>Artist:</strong> " . htmlspecialchars($track['artist_name']) . "</p>";
echo "<p><strong>Price:</strong> \${$track['price']}</p>";
echo "<p><strong>Total purchases of this track:</strong> {$track['purchase_count']}</p>";
// Check if Kat Zen purchased it
$kat_purchase = $pdo->prepare("
SELECT * FROM track_purchases
WHERE user_id = ? AND track_id = 182
");
$kat_purchase->execute([$user_id]);
$purchase = $kat_purchase->fetch(PDO::FETCH_ASSOC);
if ($purchase) {
echo "<p class='success'>✓ Kat Zen HAS purchased track 182</p>";
echo "<table>";
echo "<tr><th>Field</th><th>Value</th></tr>";
foreach ($purchase as $key => $value) {
echo "<tr><td>{$key}</td><td>" . htmlspecialchars($value ?? 'NULL') . "</td></tr>";
}
echo "</table>";
} else {
echo "<p class='error'>✗ Kat Zen has NOT purchased track 182 in track_purchases table</p>";
}
} else {
echo "<p class='error'>✗ Track 182 not found</p>";
}
// 5. Test the count query
echo "<h3>5. Testing Purchase Count Query</h3>";
$count_test = $pdo->prepare("
SELECT COUNT(*) as total_purchases
FROM track_purchases
WHERE user_id = ?
");
$count_test->execute([$user_id]);
$count_result = $count_test->fetch(PDO::FETCH_ASSOC);
echo "<p><strong>COUNT query result:</strong> <span class='info' style='font-size: 24px;'>{$count_result['total_purchases']}</span></p>";
// 6. Check user_library
echo "<h3>6. User Library Entries</h3>";
$library = $pdo->prepare("
SELECT
ul.*,
mt.title as track_title
FROM user_library ul
LEFT JOIN music_tracks mt ON ul.track_id = mt.id
WHERE ul.user_id = ?
ORDER BY ul.purchase_date DESC
");
$library->execute([$user_id]);
$library_items = $library->fetchAll(PDO::FETCH_ASSOC);
echo "<p><strong>Library items:</strong> " . count($library_items) . "</p>";
if (!empty($library_items)) {
echo "<table>";
echo "<tr><th>Track ID</th><th>Track Title</th><th>Purchase Date</th></tr>";
foreach ($library_items as $item) {
echo "<tr>";
echo "<td>{$item['track_id']}</td>";
echo "<td>" . htmlspecialchars($item['track_title'] ?? 'Unknown') . "</td>";
echo "<td>{$item['purchase_date']}</td>";
echo "</tr>";
}
echo "</table>";
}
// 7. Check Stripe logs for transaction
echo "<h3>7. Stripe Transaction Logs</h3>";
$log_files = [
__DIR__ . '/logs/stripe_success.log',
__DIR__ . '/logs/stripe_actions.log',
__DIR__ . '/logs/track_purchases.log',
__DIR__ . '/logs/mixed_cart_payments.log'
];
foreach ($log_files as $log_file) {
if (file_exists($log_file)) {
echo "<h4>" . basename($log_file) . "</h4>";
$lines = file($log_file, FILE_IGNORE_NEW_LINES);
$relevant_lines = [];
foreach (array_slice($lines, -50) as $line) { // Last 50 lines
$data = json_decode($line, true);
if ($data) {
// Check if this log entry is related to this user or track 182
if (
(isset($data['user_id']) && $data['user_id'] == $user_id) ||
(isset($data['track_id']) && $data['track_id'] == 182) ||
(isset($data['user_name']) && stripos($data['user_name'], 'kat') !== false) ||
(isset($data['artist_name']) && stripos($data['artist_name'], 'kat') !== false)
) {
$relevant_lines[] = $data;
}
}
}
if (!empty($relevant_lines)) {
echo "<p class='success'>Found " . count($relevant_lines) . " relevant log entries</p>";
echo "<pre style='background: #2a2a2a; padding: 10px; border-radius: 5px; overflow-x: auto;'>";
foreach ($relevant_lines as $entry) {
echo htmlspecialchars(json_encode($entry, JSON_PRETTY_PRINT)) . "\n\n";
}
echo "</pre>";
} else {
echo "<p class='warning'>No relevant entries found in last 50 lines</p>";
}
} else {
echo "<p class='warning'>Log file not found: " . basename($log_file) . "</p>";
}
}
// 8. Summary and recommendation
echo "<h3>8. Summary & Recommendation</h3>";
$purchase_count = count($user_purchases);
$count_query_result = $count_result['total_purchases'];
echo "<div style='background: #2a2a2a; padding: 20px; border-radius: 10px; margin: 20px 0;'>";
echo "<p><strong>Purchase Count from Array:</strong> <span class='info'>{$purchase_count}</span></p>";
echo "<p><strong>Purchase Count from Query:</strong> <span class='info'>{$count_query_result}</span></p>";
if ($purchase_count > 0 && $count_query_result == 0) {
echo "<p class='error'><strong>ISSUE FOUND:</strong> Purchases exist but COUNT query returns 0!</p>";
echo "<p>This suggests a query issue. The fix should use the array count as fallback.</p>";
} elseif ($purchase_count == 0 && $count_query_result == 0) {
echo "<p class='error'><strong>ISSUE FOUND:</strong> No purchases found in database!</p>";
echo "<p>The purchase may not have been recorded. Check the webhook logs and consider using fix_missing_purchase.php</p>";
} elseif ($purchase_count > 0 && $count_query_result > 0) {
echo "<p class='success'><strong>✓ Purchases exist and count query works!</strong></p>";
echo "<p>The issue might be in how the count is displayed. Check the account_settings.php template.</p>";
}
echo "</div>";
// 9. Quick fix button
if ($purchase_count > 0) {
echo "<h3>9. Quick Fix</h3>";
echo "<p>If the count is still showing 0, try:</p>";
echo "<ol>";
echo "<li>Clear browser cache and refresh</li>";
echo "<li>Check if the count is being cached somewhere</li>";
echo "<li>Verify the account_settings.php is using the correct variable</li>";
echo "</ol>";
}
}
echo "<hr>";
echo "<p><a href='/admin.php?tab=purchases' style='color: #667eea;'>← Back to Purchases Admin</a> | ";
echo "<a href='/account_settings.php?tab=purchases' style='color: #667eea;'>View Kat Zen's Account</a></p>";
?>