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
/**
 * Diagnostic: Check if credits were used when subscription should have been used
 * For Stephane Bergeron (user_id = 5)
 */

session_start();
require_once 'config/database.php';
require_once __DIR__ . '/utils/subscription_helpers.php';

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

$pdo = getDBConnection();
$user_id = 5; // Stephane Bergeron

echo "<h2>🔍 Subscription vs Credits Usage Analysis</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; }
    .highlight { background: #3a3a3a; }
    .issue { background: rgba(229, 62, 62, 0.2); border-left: 4px solid #e53e3e; }
</style>";

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

echo "<div class='section'>";
echo "<h3>👤 User Information</h3>";
echo "<table>";
echo "<tr><th>ID</th><td>{$user['id']}</td></tr>";
echo "<tr><th>Name</th><td>{$user['name']}</td></tr>";
echo "<tr><th>Email</th><td>{$user['email']}</td></tr>";
echo "<tr><th>Current Credits</th><td><strong>{$user['credits']}</strong></td></tr>";
echo "<tr><th>Current Plan</th><td>{$user['plan']}</td></tr>";
echo "</table>";
echo "</div>";

// Get subscription history
echo "<div class='section'>";
echo "<h3>📅 Subscription History</h3>";
$stmt = $pdo->prepare("
    SELECT 
        us.*,
        DATE_FORMAT(us.created_at, '%Y-%m-%d %H:%i:%s') as sub_created,
        DATE_FORMAT(us.current_period_start, '%Y-%m-%d %H:%i:%s') as period_start,
        DATE_FORMAT(us.current_period_end, '%Y-%m-%d %H:%i:%s') as period_end
    FROM user_subscriptions us
    WHERE us.user_id = ?
    ORDER BY us.created_at DESC
");
$stmt->execute([$user_id]);
$subscriptions = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (empty($subscriptions)) {
    echo "<p class='error'>❌ No subscriptions found!</p>";
    echo "<p class='info'>This explains why credits were used - user had no active subscription.</p>";
} else {
    echo "<table>";
    echo "<tr>
        <th>Plan</th>
        <th>Status</th>
        <th>Created At</th>
        <th>Period Start</th>
        <th>Period End</th>
        <th>Stripe Subscription ID</th>
    </tr>";
    
    foreach ($subscriptions as $sub) {
        $status_class = ($sub['status'] === 'active') ? 'success' : 'warning';
        echo "<tr>";
        echo "<td><strong>" . ucfirst($sub['plan_name']) . "</strong></td>";
        echo "<td class='{$status_class}'>{$sub['status']}</td>";
        echo "<td>{$sub['sub_created']}</td>";
        echo "<td>{$sub['period_start']}</td>";
        echo "<td>{$sub['period_end']}</td>";
        echo "<td>{$sub['stripe_subscription_id']}</td>";
        echo "</tr>";
    }
    echo "</table>";
}
echo "</div>";

// Get monthly track usage
echo "<div class='section'>";
echo "<h3>📊 Monthly Track Usage History</h3>";
$stmt = $pdo->prepare("
    SELECT 
        mtu.*,
        DATE_FORMAT(mtu.subscription_period_start, '%Y-%m-%d %H:%i:%s') as period_start,
        DATE_FORMAT(mtu.reset_at, '%Y-%m-%d %H:%i:%s') as reset_date,
        DATE_FORMAT(mtu.created_at, '%Y-%m-%d %H:%i:%s') as created_date
    FROM monthly_track_usage mtu
    WHERE mtu.user_id = ?
    ORDER BY mtu.subscription_period_start DESC
");
$stmt->execute([$user_id]);
$usage_records = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (empty($usage_records)) {
    echo "<p class='warning'>⚠️ No monthly track usage records found!</p>";
    echo "<p class='info'>This means subscription tracks were never used (or subscription was created after credit usage).</p>";
} else {
    echo "<table>";
    echo "<tr>
        <th>Period Start</th>
        <th>Tracks Created</th>
        <th>Track Limit</th>
        <th>Remaining</th>
        <th>Created At</th>
    </tr>";
    
    foreach ($usage_records as $usage) {
        $remaining = $usage['track_limit'] - $usage['tracks_created'];
        $remaining_class = ($remaining > 0) ? 'success' : 'error';
        echo "<tr>";
        echo "<td>{$usage['period_start']}</td>";
        echo "<td><strong>{$usage['tracks_created']}</strong></td>";
        echo "<td>{$usage['track_limit']}</td>";
        echo "<td class='{$remaining_class}'><strong>{$remaining}</strong></td>";
        echo "<td>{$usage['created_date']}</td>";
        echo "</tr>";
    }
    echo "</table>";
}
echo "</div>";

// Get actual tracks created
echo "<div class='section'>";
echo "<h3>🎵 Tracks Created (from music_tracks table)</h3>";
$stmt = $pdo->prepare("
    SELECT 
        mt.id,
        mt.title,
        mt.status,
        DATE_FORMAT(mt.created_at, '%Y-%m-%d %H:%i:%s') as track_created,
        mt.created_at as track_created_raw
    FROM music_tracks mt
    WHERE mt.user_id = ?
    ORDER BY mt.created_at DESC
    LIMIT 50
");
$stmt->execute([$user_id]);
$tracks = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (empty($tracks)) {
    echo "<p class='info'>ℹ️ No tracks found</p>";
} else {
    echo "<table>";
    echo "<tr>
        <th>Track ID</th>
        <th>Title</th>
        <th>Status</th>
        <th>Created At</th>
        <th>Should Use Subscription?</th>
    </tr>";
    
    foreach ($tracks as $track) {
        $track_timestamp = strtotime($track['track_created_raw']);
        $should_use_sub = false;
        $issue_msg = '';
        
        // Check if subscription was active at this time
        foreach ($subscriptions as $sub) {
            if ($sub['status'] === 'active') {
                $period_start = strtotime($sub['current_period_start']);
                $period_end = strtotime($sub['current_period_end']);
                
                if ($track_timestamp >= $period_start && $track_timestamp <= $period_end) {
                    $should_use_sub = true;
                    
                    // Check if subscription limit was reached at this time
                    foreach ($usage_records as $usage) {
                        if ($usage['subscription_period_start'] == $sub['current_period_start']) {
                            // Need to check usage BEFORE this track was created
                            // This is approximate - we'd need to check usage incrementally
                            $issue_msg = "Subscription active - check usage below";
                            break;
                        }
                    }
                    
                    if (!$issue_msg) {
                        $issue_msg = "✅ Subscription was active - should have used subscription";
                    }
                    break;
                }
            }
        }
        
        if (!$should_use_sub) {
            $issue_msg = "✅ No active subscription - credits used correctly";
        }
        
        $row_class = (strpos($issue_msg, 'should have used') !== false) ? 'issue' : '';
        echo "<tr class='{$row_class}'>";
        echo "<td>{$track['id']}</td>";
        echo "<td>" . htmlspecialchars($track['title'] ?: 'Untitled') . "</td>";
        echo "<td>{$track['status']}</td>";
        echo "<td>{$track['track_created']}</td>";
        echo "<td>{$issue_msg}</td>";
        echo "</tr>";
    }
    echo "</table>";
}
echo "</div>";

// Get credit transactions (track creation only)
echo "<div class='section'>";
echo "<h3>💳 Credit Transactions (Track Creation)</h3>";
$stmt = $pdo->prepare("
    SELECT 
        ct.*,
        DATE_FORMAT(ct.created_at, '%Y-%m-%d %H:%i:%s') as tx_date
    FROM credit_transactions ct
    WHERE ct.user_id = ?
    AND ct.type = 'usage'
    AND ct.description LIKE 'Music track creation%'
    ORDER BY ct.created_at DESC
");
$stmt->execute([$user_id]);
$credit_txs = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (empty($credit_txs)) {
    echo "<p class='info'>ℹ️ No credit transactions for track creation found</p>";
} else {
    echo "<table>";
    echo "<tr>
        <th>Date</th>
        <th>Amount</th>
        <th>Description</th>
        <th>Matching Track</th>
        <th>Should Use Subscription?</th>
    </tr>";
    
    // Match credit transactions with tracks
    $issues = [];
    foreach ($credit_txs as $tx) {
        $tx_timestamp = strtotime($tx['created_at']);
        $should_use_sub = false;
        $issue_msg = '';
        
        // Find matching track by timestamp (within 5 seconds)
        $matching_track = null;
        foreach ($tracks as $track) {
            $track_timestamp = strtotime($track['track_created_raw']);
            if (abs($tx_timestamp - $track_timestamp) <= 5) {
                $matching_track = $track;
                break;
            }
        }
        
        // Check if subscription was active at this time
        foreach ($subscriptions as $sub) {
            if ($sub['status'] === 'active') {
                $period_start = strtotime($sub['current_period_start']);
                $period_end = strtotime($sub['current_period_end']);
                
                if ($tx_timestamp >= $period_start && $tx_timestamp <= $period_end) {
                    $should_use_sub = true;
                    
                    // Check if subscription limit was reached at the time of this transaction
                    // We need to count how many tracks were created BEFORE this transaction
                    $tracks_before_this = 0;
                    foreach ($tracks as $track) {
                        $track_ts = strtotime($track['track_created_raw']);
                        if ($track_ts < $tx_timestamp && $track_ts >= $period_start && $track_ts <= $period_end) {
                            $tracks_before_this++;
                        }
                    }
                    
                    // Find usage record for this period
                    foreach ($usage_records as $usage) {
                        $usage_period_start = strtotime($usage['subscription_period_start']);
                        if (abs($usage_period_start - $period_start) < 86400) { // Within 1 day
                            $track_limit = $usage['track_limit'];
                            
                            if ($tracks_before_this >= $track_limit) {
                                $issue_msg = "✅ OK - Subscription limit reached ({$tracks_before_this}/{$track_limit}), credits used correctly";
                            } else {
                                $issue_msg = "❌ ISSUE - Only {$tracks_before_this}/{$track_limit} subscription tracks used, should have used subscription!";
                                $issues[] = [
                                    'date' => $tx['tx_date'],
                                    'description' => $tx['description'],
                                    'subscription_used' => $tracks_before_this,
                                    'subscription_limit' => $track_limit,
                                    'track_id' => $matching_track ? $matching_track['id'] : 'N/A'
                                ];
                            }
                            break;
                        }
                    }
                    
                    if (!$issue_msg) {
                        $issue_msg = "❌ ISSUE - Subscription was active but no usage record found!";
                        $issues[] = [
                            'date' => $tx['tx_date'],
                            'description' => $tx['description'],
                            'subscription_used' => $tracks_before_this,
                            'subscription_limit' => 'N/A',
                            'track_id' => $matching_track ? $matching_track['id'] : 'N/A'
                        ];
                    }
                    break;
                }
            }
        }
        
        if (!$should_use_sub) {
            $issue_msg = "✅ OK - No active subscription at this time, credits used correctly";
        }
        
        $row_class = (strpos($issue_msg, '❌') !== false) ? 'issue' : '';
        echo "<tr class='{$row_class}'>";
        echo "<td>{$tx['tx_date']}</td>";
        echo "<td class='error'>{$tx['amount']}</td>";
        echo "<td>" . htmlspecialchars($tx['description']) . "</td>";
        echo "<td>" . ($matching_track ? "Track #{$matching_track['id']}" : "No matching track") . "</td>";
        echo "<td>{$issue_msg}</td>";
        echo "</tr>";
    }
    echo "</table>";
    
    if (!empty($issues)) {
        echo "<div class='section issue'>";
        echo "<h3>🚨 Issues Found</h3>";
        echo "<p class='error'><strong>" . count($issues) . " credit transaction(s) should have used subscription instead!</strong></p>";
        echo "<ul>";
        foreach ($issues as $issue) {
            echo "<li>";
            echo "<strong>{$issue['date']}</strong>: {$issue['description']}<br>";
            echo "Subscription usage: {$issue['subscription_used']}/{$issue['subscription_limit']}";
            echo "</li>";
        }
        echo "</ul>";
        echo "</div>";
    }
}
echo "</div>";

// Summary
echo "<div class='section'>";
echo "<h3>📋 Summary</h3>";

$active_sub = hasActiveSubscription($user_id);
if ($active_sub) {
    echo "<p class='success'>✅ User has active subscription: <strong>" . ucfirst($active_sub['plan_name']) . "</strong></p>";
    
    $current_usage = getMonthlyTrackUsage($user_id, $active_sub['plan_name']);
    if ($current_usage) {
        echo "<p class='info'>Current usage: <strong>{$current_usage['tracks_created']}/{$current_usage['track_limit']}</strong></p>";
    }
} else {
    echo "<p class='warning'>⚠️ User does NOT have an active subscription currently</p>";
}

if (!empty($subscriptions)) {
    $first_sub = $subscriptions[count($subscriptions) - 1]; // Oldest
    echo "<p class='info'>First subscription created: <strong>{$first_sub['sub_created']}</strong></p>";
    
    if (!empty($credit_txs)) {
        $oldest_tx = $credit_txs[count($credit_txs) - 1]; // Oldest
        $oldest_tx_date = strtotime($oldest_tx['created_at']);
        $first_sub_date = strtotime($first_sub['created_at']);
        
        if ($oldest_tx_date < $first_sub_date) {
            echo "<p class='success'>✅ Oldest credit transaction ({$oldest_tx['tx_date']}) was BEFORE subscription creation - credits used correctly</p>";
        } else {
            echo "<p class='warning'>⚠️ Oldest credit transaction ({$oldest_tx['tx_date']}) was AFTER subscription creation - may need investigation</p>";
        }
    }
}

echo "</div>";

?>