 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
/**
* Investigate Stephane's Credits Issue
*
* This script checks:
* 1. User's current credits balance
* 2. Subscription status and plan
* 3. Monthly track usage (subscription tracks)
* 4. Credit transactions history
* 5. Subscription period information
*/
require_once __DIR__ . '/config/database.php';
require_once __DIR__ . '/utils/subscription_helpers.php';
$pdo = getDBConnection();
// Find Stephane's user ID
$stmt = $pdo->prepare("SELECT id, name, email, plan, credits, created_at FROM users WHERE email LIKE '%stephane%' OR email LIKE '%bergron%' OR name LIKE '%stephane%' OR name LIKE '%taz%'");
$stmt->execute();
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (empty($users)) {
die("No users found matching Stephane/Taz/Bergeron");
}
echo "<h1>Stephane's Credits Investigation</h1>";
echo "<style>
body { font-family: Arial, sans-serif; margin: 20px; }
table { border-collapse: collapse; width: 100%; margin: 20px 0; }
th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
th { background-color: #667eea; color: white; }
.warning { background-color: #fff3cd; }
.error { background-color: #f8d7da; }
.success { background-color: #d4edda; }
pre { background: #f4f4f4; padding: 10px; overflow-x: auto; }
</style>";
foreach ($users as $user) {
$user_id = $user['id'];
echo "<h2>User: {$user['name']} (ID: {$user_id}, Email: {$user['email']})</h2>";
// 1. Current Credits and Plan
echo "<h3>1. Current Account Status</h3>";
echo "<table>";
echo "<tr><th>Field</th><th>Value</th></tr>";
echo "<tr><td>Plan</td><td><strong>{$user['plan']}</strong></td></tr>";
echo "<tr><td>Credits Balance</td><td><strong>{$user['credits']}</strong></td></tr>";
echo "<tr><td>Account Created</td><td>{$user['created_at']}</td></tr>";
echo "</table>";
// 2. Subscription Status
echo "<h3>2. Subscription Status</h3>";
$subscription = hasActiveSubscription($user_id);
if ($subscription) {
echo "<table class='success'>";
echo "<tr><th>Field</th><th>Value</th></tr>";
echo "<tr><td>Status</td><td><strong>{$subscription['status']}</strong></td></tr>";
echo "<tr><td>Plan Name</td><td><strong>{$subscription['plan_name']}</strong></td></tr>";
echo "<tr><td>Period Start</td><td>" . ($subscription['current_period_start'] ?? 'N/A') . "</td></tr>";
echo "<tr><td>Period End</td><td>" . ($subscription['current_period_end'] ?? 'N/A') . "</td></tr>";
echo "</table>";
} else {
echo "<p class='warning'>No active subscription found via hasActiveSubscription()</p>";
// Check user_subscriptions table directly
echo "<h4>Checking user_subscriptions table directly:</h4>";
$sub_check = $pdo->prepare("
SELECT * FROM user_subscriptions
WHERE user_id = ?
ORDER BY created_at DESC
");
$sub_check->execute([$user_id]);
$all_subs = $sub_check->fetchAll(PDO::FETCH_ASSOC);
if ($all_subs) {
echo "<table class='error'>";
echo "<tr><th>ID</th><th>Stripe ID</th><th>Plan</th><th>Status</th><th>Period Start</th><th>Period End</th><th>Created</th></tr>";
foreach ($all_subs as $sub) {
$status_class = in_array($sub['status'], ['active', 'trialing']) ? 'success' : 'error';
echo "<tr class='{$status_class}'>";
echo "<td>{$sub['id']}</td>";
echo "<td>{$sub['stripe_subscription_id']}</td>";
echo "<td><strong>{$sub['plan_name']}</strong></td>";
echo "<td><strong>{$sub['status']}</strong></td>";
echo "<td>{$sub['current_period_start']}</td>";
echo "<td>{$sub['current_period_end']}</td>";
echo "<td>{$sub['created_at']}</td>";
echo "</tr>";
}
echo "</table>";
// Check why hasActiveSubscription() might be failing
$active_check = $pdo->prepare("
SELECT * FROM user_subscriptions
WHERE user_id = ?
AND status IN ('active', 'trialing')
AND current_period_end > NOW()
ORDER BY created_at DESC
LIMIT 1
");
$active_check->execute([$user_id]);
$active_sub = $active_check->fetch(PDO::FETCH_ASSOC);
if ($active_sub) {
echo "<p class='error'><strong>⚠️ ISSUE FOUND:</strong> There IS an active subscription in the database, but hasActiveSubscription() returned false!</p>";
echo "<p>This means subscription tracks should be used first, but the system is using credits instead.</p>";
}
} else {
echo "<p>No subscription records found in user_subscriptions table</p>";
}
}
// 3. Monthly Track Usage (Subscription Tracks)
echo "<h3>3. Monthly Track Usage (Subscription Tracks)</h3>";
if ($subscription) {
$usage = getMonthlyTrackUsage($user_id, $subscription['plan_name']);
if ($usage) {
echo "<table>";
echo "<tr><th>Field</th><th>Value</th></tr>";
echo "<tr><td>Tracks Created</td><td><strong>{$usage['tracks_created']}</strong></td></tr>";
echo "<tr><td>Track Limit</td><td><strong>{$usage['track_limit']}</strong></td></tr>";
echo "<tr><td>Tracks Remaining</td><td><strong>" . ($usage['track_limit'] - $usage['tracks_created']) . "</strong></td></tr>";
echo "<tr><td>Period Start</td><td>{$usage['subscription_period_start']}</td></tr>";
echo "<tr><td>Reset At</td><td>{$usage['reset_at']}</td></tr>";
echo "<tr><td>Last Updated</td><td>{$usage['updated_at']}</td></tr>";
echo "</table>";
// Check all usage records
$all_usage_stmt = $pdo->prepare("SELECT * FROM monthly_track_usage WHERE user_id = ? ORDER BY subscription_period_start DESC LIMIT 10");
$all_usage_stmt->execute([$user_id]);
$all_usage = $all_usage_stmt->fetchAll(PDO::FETCH_ASSOC);
if (count($all_usage) > 1) {
echo "<h4>All Usage Records (Last 10):</h4>";
echo "<table>";
echo "<tr><th>Period Start</th><th>Tracks Created</th><th>Track Limit</th><th>Reset At</th><th>Updated At</th></tr>";
foreach ($all_usage as $u) {
echo "<tr>";
echo "<td>{$u['subscription_period_start']}</td>";
echo "<td>{$u['tracks_created']}</td>";
echo "<td>{$u['track_limit']}</td>";
echo "<td>{$u['reset_at']}</td>";
echo "<td>{$u['updated_at']}</td>";
echo "</tr>";
}
echo "</table>";
}
} else {
echo "<p class='warning'>No usage record found (should be created automatically)</p>";
}
} else {
echo "<p>No subscription, so no monthly track usage</p>";
}
// 4. Credit Transactions (Recent)
echo "<h3>4. Recent Credit Transactions (Last 20)</h3>";
$trans_stmt = $pdo->prepare("
SELECT * FROM credit_transactions
WHERE user_id = ?
ORDER BY created_at DESC
LIMIT 20
");
$trans_stmt->execute([$user_id]);
$transactions = $trans_stmt->fetchAll(PDO::FETCH_ASSOC);
if ($transactions) {
echo "<table>";
echo "<tr><th>Date</th><th>Amount</th><th>Type</th><th>Description</th></tr>";
foreach ($transactions as $trans) {
$color = $trans['amount'] < 0 ? 'error' : 'success';
echo "<tr class='{$color}'>";
echo "<td>{$trans['created_at']}</td>";
echo "<td><strong>{$trans['amount']}</strong></td>";
echo "<td>{$trans['type']}</td>";
echo "<td>{$trans['description']}</td>";
echo "</tr>";
}
echo "</table>";
} else {
echo "<p>No credit transactions found</p>";
}
// 5. Credit Purchases
echo "<h3>5. Credit Purchases</h3>";
$purchase_stmt = $pdo->prepare("
SELECT * FROM credit_purchases
WHERE user_id = ?
ORDER BY created_at DESC
LIMIT 10
");
$purchase_stmt->execute([$user_id]);
$purchases = $purchase_stmt->fetchAll(PDO::FETCH_ASSOC);
if ($purchases) {
echo "<table>";
echo "<tr><th>Date</th><th>Package</th><th>Credits</th><th>Amount</th><th>Expires At</th></tr>";
foreach ($purchases as $purchase) {
echo "<tr>";
echo "<td>{$purchase['created_at']}</td>";
echo "<td>{$purchase['package']}</td>";
echo "<td><strong>{$purchase['credits']}</strong></td>";
echo "<td>\${$purchase['amount']}</td>";
echo "<td>{$purchase['expires_at']}</td>";
echo "</tr>";
}
echo "</table>";
} else {
echo "<p>No credit purchases found</p>";
}
// 6. Recent Tracks Created
echo "<h3>6. Recent Tracks Created (Last 10)</h3>";
$tracks_stmt = $pdo->prepare("
SELECT id, title, created_at, status
FROM music_tracks
WHERE user_id = ?
ORDER BY created_at DESC
LIMIT 10
");
$tracks_stmt->execute([$user_id]);
$tracks = $tracks_stmt->fetchAll(PDO::FETCH_ASSOC);
if ($tracks) {
echo "<table>";
echo "<tr><th>ID</th><th>Title</th><th>Created At</th><th>Status</th></tr>";
foreach ($tracks as $track) {
echo "<tr>";
echo "<td>{$track['id']}</td>";
echo "<td>{$track['title']}</td>";
echo "<td>{$track['created_at']}</td>";
echo "<td>{$track['status']}</td>";
echo "</tr>";
}
echo "</table>";
} else {
echo "<p>No tracks found</p>";
}
// 7. Check for issues
echo "<h3>7. Issue Analysis</h3>";
$issues = [];
if ($subscription && $usage) {
// Check if subscription tracks are being reset incorrectly
if ($usage['tracks_created'] == 0 && $user['credits'] > 0) {
$issues[] = "⚠️ Subscription tracks are at 0, but user has credits. This is normal if they just used subscription tracks.";
}
// Check if there are multiple usage records for the same period
$duplicate_stmt = $pdo->prepare("
SELECT subscription_period_start, COUNT(*) as count
FROM monthly_track_usage
WHERE user_id = ?
GROUP BY subscription_period_start
HAVING count > 1
");
$duplicate_stmt->execute([$user_id]);
$duplicates = $duplicate_stmt->fetchAll(PDO::FETCH_ASSOC);
if ($duplicates) {
$issues[] = "❌ ERROR: Multiple usage records for the same period! This could cause resets.";
echo "<pre>" . print_r($duplicates, true) . "</pre>";
}
// Check if period_start changed recently
$period_changes_stmt = $pdo->prepare("
SELECT subscription_period_start, tracks_created, reset_at
FROM monthly_track_usage
WHERE user_id = ?
ORDER BY reset_at DESC
LIMIT 5
");
$period_changes_stmt->execute([$user_id]);
$period_changes = $period_changes_stmt->fetchAll(PDO::FETCH_ASSOC);
if (count($period_changes) > 1) {
$recent_resets = 0;
foreach ($period_changes as $i => $pc) {
if ($i > 0 && $pc['tracks_created'] == 0) {
$recent_resets++;
}
}
if ($recent_resets > 0) {
$issues[] = "⚠️ Found {$recent_resets} recent resets. Check if subscription period is changing incorrectly.";
}
}
}
if (empty($issues)) {
echo "<p class='success'>✅ No obvious issues detected</p>";
} else {
echo "<ul>";
foreach ($issues as $issue) {
echo "<li>{$issue}</li>";
}
echo "</ul>";
}
echo "<hr>";
}
echo "<p><em>Generated: " . date('Y-m-d H:i:s') . "</em></p>";
?>