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/admin_includes/

Upload File : current_dir [ Writeable ] document_root [ Writeable ]

<?php
// Dashboard Tab
// This file handles the main dashboard overview

// Get basic statistics
$overall_stats = $pdo->query("
    SELECT 
        COUNT(*) as total_tracks,
        COUNT(CASE WHEN status = 'complete' THEN 1 END) as completed,
        COUNT(CASE WHEN status = 'processing' THEN 1 END) as processing,
        COUNT(CASE WHEN status = 'failed' THEN 1 END) as failed
    FROM music_tracks
")->fetch();

$user_stats = $pdo->query("
    SELECT 
        COUNT(*) as total_users,
        COUNT(CASE WHEN is_admin = 1 THEN 1 END) as admin_users,
        SUM(credits) as total_credits
    FROM users
")->fetch();

// Get recent activity
$recent_activity = $pdo->query("
    SELECT 
        mt.id,
        mt.title,
        mt.status,
        mt.created_at,
        u.name as user_name,
        u.plan
    FROM music_tracks mt
    LEFT JOIN users u ON mt.user_id = u.id
    ORDER BY mt.created_at DESC
    LIMIT 10
")->fetchAll();

// Get system health
$system_health = [
    'processing_tracks' => $overall_stats['processing'],
    'failed_tracks' => $overall_stats['failed'],
    'success_rate' => $overall_stats['total_tracks'] > 0 ? round(($overall_stats['completed'] / $overall_stats['total_tracks']) * 100, 1) : 0
];

// Get currently online users (logged in within last 15 minutes)
// Use multiple sources: users.last_login_at and user_login_history
try {
    // First, check if users table has last_login_at column
    $userColumns = $pdo->query("SHOW COLUMNS FROM users")->fetchAll(PDO::FETCH_COLUMN);
    $hasLastLoginAt = in_array('last_login_at', $userColumns);
    
    // Check which columns exist in user_login_history table
    $historyColumns = $pdo->query("SHOW COLUMNS FROM user_login_history")->fetchAll(PDO::FETCH_COLUMN);
    $hasLoginTime = in_array('login_time', $historyColumns);
    $hasSuccess = in_array('success', $historyColumns);
    $hasLoginSuccess = in_array('login_success', $historyColumns);
    $hasCountry = in_array('country', $historyColumns);
    
    // Determine which success column to use
    $hasSuccessColumn = $hasSuccess || $hasLoginSuccess;
    $successColumn = $hasSuccess ? 'success' : ($hasLoginSuccess ? 'login_success' : null);
    // Determine which time column to use
    $timeColumn = $hasLoginTime ? 'login_time' : 'created_at';
    
    // Use user_login_history as PRIMARY source (this is what gets updated on login)
    // The login process logs to user_login_history, so this is the reliable source
    // Handle both boolean (TRUE/FALSE) and integer (1/0) types for success column
    // Make success check permissive - include entries where success is true OR where success column doesn't exist/is null
    if ($hasSuccessColumn && $successColumn) {
        $successCondition = "((ulh.{$successColumn} = 1 OR ulh.{$successColumn} = TRUE OR ulh.{$successColumn} IS TRUE) OR ulh.{$successColumn} IS NULL) AND ";
    } else {
        $successCondition = "";
    }
    
    try {
        $online_users = $pdo->query("
            SELECT DISTINCT
                u.id,
                u.name,
                u.email,
                u.plan,
                u.is_admin,
                u.credits,
                MAX(ulh.{$timeColumn}) as last_login,
                MAX(ulh.ip_address) as last_ip,
                " . ($hasCountry ? "MAX(ulh.country) as country, MAX(ulh.city) as city," : "NULL as country, NULL as city,") . "
                COUNT(DISTINCT ulh.id) as login_count_today
            FROM users u
            INNER JOIN user_login_history ulh ON u.id = ulh.user_id
            WHERE {$successCondition}ulh.{$timeColumn} >= DATE_SUB(NOW(), INTERVAL 15 MINUTE)
            GROUP BY u.id, u.name, u.email, u.plan, u.is_admin, u.credits
            ORDER BY last_login DESC
        ")->fetchAll();
    } catch (Exception $queryError) {
        // If main query fails, try without success condition
        error_log("Main online users query failed: " . $queryError->getMessage());
        $online_users = $pdo->query("
            SELECT DISTINCT
                u.id,
                u.name,
                u.email,
                u.plan,
                u.is_admin,
                u.credits,
                MAX(ulh.{$timeColumn}) as last_login,
                MAX(ulh.ip_address) as last_ip,
                " . ($hasCountry ? "MAX(ulh.country) as country, MAX(ulh.city) as city," : "NULL as country, NULL as city,") . "
                COUNT(DISTINCT ulh.id) as login_count_today
            FROM users u
            INNER JOIN user_login_history ulh ON u.id = ulh.user_id
            WHERE ulh.{$timeColumn} >= DATE_SUB(NOW(), INTERVAL 15 MINUTE)
            GROUP BY u.id, u.name, u.email, u.plan, u.is_admin, u.credits
            ORDER BY last_login DESC
        ")->fetchAll();
    }
} catch (Exception $e) {
    $online_users = [];
    error_log("Error fetching online users: " . $e->getMessage());
    // Try a simpler fallback query that handles both column name variations
    try {
        $online_users = $pdo->query("
            SELECT DISTINCT
                u.id,
                u.name,
                u.email,
                u.plan,
                u.is_admin,
                u.credits,
                MAX(COALESCE(ulh.login_time, ulh.created_at)) as last_login,
                MAX(ulh.ip_address) as last_ip,
                NULL as country,
                NULL as city,
                0 as login_count_today
            FROM users u
            INNER JOIN user_login_history ulh ON u.id = ulh.user_id
            WHERE (ulh.success = 1 OR ulh.success = TRUE OR ulh.login_success = 1 OR ulh.login_success = TRUE OR ulh.success IS NULL)
            AND (ulh.login_time >= DATE_SUB(NOW(), INTERVAL 15 MINUTE) 
                 OR ulh.created_at >= DATE_SUB(NOW(), INTERVAL 15 MINUTE))
            GROUP BY u.id, u.name, u.email, u.plan, u.is_admin, u.credits
            ORDER BY last_login DESC
        ")->fetchAll();
    } catch (Exception $e2) {
        error_log("Fallback query also failed: " . $e2->getMessage());
        // Last resort: get ALL recent entries regardless of success (most permissive)
        try {
            $online_users = $pdo->query("
                SELECT DISTINCT
                    u.id,
                    u.name,
                    u.email,
                    u.plan,
                    u.is_admin,
                    u.credits,
                    MAX(COALESCE(ulh.login_time, ulh.created_at, NOW())) as last_login,
                    MAX(ulh.ip_address) as last_ip,
                    NULL as country,
                    NULL as city,
                    0 as login_count_today
                FROM users u
                INNER JOIN user_login_history ulh ON u.id = ulh.user_id
                WHERE (ulh.login_time >= DATE_SUB(NOW(), INTERVAL 15 MINUTE) 
                       OR ulh.created_at >= DATE_SUB(NOW(), INTERVAL 15 MINUTE))
                GROUP BY u.id, u.name, u.email, u.plan, u.is_admin, u.credits
                ORDER BY last_login DESC
            ")->fetchAll();
        } catch (Exception $e3) {
            error_log("Final fallback query also failed: " . $e3->getMessage());
            $online_users = [];
        }
    }
}

// Get total online count
$online_count = count($online_users);
?>

<!-- Dashboard Overview -->
<div class="section-header">
    <h2><i class="fas fa-tachometer-alt"></i> Dashboard Overview</h2>
    <p>Key system statistics and quick actions</p>
</div>

<!-- Main Statistics -->
<div class="stats-grid" style="margin-bottom: 3rem;">
    <div class="stat-card">
        <div class="stat-number"><?= number_format($overall_stats['total_tracks']) ?></div>
        <div class="stat-label">Total Tracks</div>
    </div>
    <div class="stat-card">
        <div class="stat-number"><?= number_format($user_stats['total_users']) ?></div>
        <div class="stat-label">Total Users</div>
    </div>
    <div class="stat-card" style="background: linear-gradient(135deg, rgba(72, 187, 120, 0.2), rgba(56, 161, 105, 0.2)); border: 1px solid rgba(72, 187, 120, 0.3);">
        <div class="stat-number" style="color: #48bb78;"><?= number_format($online_count) ?></div>
        <div class="stat-label">Online Now</div>
    </div>
    <div class="stat-card">
        <div class="stat-number"><?= $system_health['success_rate'] ?>%</div>
        <div class="stat-label">Success Rate</div>
    </div>
    <div class="stat-card">
        <div class="stat-number"><?= number_format($user_stats['total_credits']) ?></div>
        <div class="stat-label">Total Credits</div>
    </div>
</div>

<!-- Quick Actions -->
<div class="section-header">
    <h3><i class="fas fa-bolt"></i> Quick Actions</h3>
</div>

<div style="text-align: center; margin-bottom: 3rem;">
    <button class="btn btn-primary" onclick="location.href='?tab=users'">
        <i class="fas fa-users"></i> Manage Users
    </button>
    <button class="btn btn-secondary" onclick="location.href='?tab=user-accounts'">
        <i class="fas fa-list"></i> View All Accounts
    </button>
    <button class="btn btn-secondary" onclick="location.href='?tab=tracks'">
        <i class="fas fa-music"></i> Manage Tracks
    </button>
    <button class="btn btn-primary" onclick="location.href='?tab=analytics'">
        <i class="fas fa-chart-line"></i> View Analytics
    </button>
    <?php if ($system_health['failed_tracks'] > 0): ?>
    <button class="btn btn-success" onclick="retryFailedTracks()">
        <i class="fas fa-redo"></i> Retry Failed Tracks (<?= $system_health['failed_tracks'] ?>)
    </button>
    <?php endif; ?>
</div>

<!-- System Health Status -->
<div class="section-header">
    <h3><i class="fas fa-heartbeat"></i> System Health</h3>
</div>

<div class="stats-grid" style="margin-bottom: 3rem;">
    <div class="stat-card <?= $system_health['processing_tracks'] > 0 ? 'status-processing' : 'status-complete' ?>">
        <div class="stat-number"><?= number_format($system_health['processing_tracks']) ?></div>
        <div class="stat-label">Processing</div>
    </div>
    <div class="stat-card <?= $system_health['failed_tracks'] > 0 ? 'status-failed' : 'status-complete' ?>">
        <div class="stat-number"><?= number_format($system_health['failed_tracks']) ?></div>
        <div class="stat-label">Failed</div>
    </div>
    <div class="stat-card">
        <div class="stat-number"><?= number_format($user_stats['admin_users']) ?></div>
        <div class="stat-label">Admin Users</div>
    </div>
    <div class="stat-card">
        <div class="stat-number"><?= $system_health['success_rate'] ?>%</div>
        <div class="stat-label">Success Rate</div>
    </div>
</div>

<!-- Online Users -->
<div class="section-header">
    <h3><i class="fas fa-circle" style="color: #48bb78; font-size: 0.8rem;"></i> Currently Online Users</h3>
    <p style="color: #94a3b8; font-size: 1.2rem; margin-top: 0.5rem;">Users active within the last 15 minutes</p>
</div>

<div style="background: rgba(255, 255, 255, 0.05); border-radius: 16px; padding: 2rem; margin-bottom: 3rem;">
    <?php if (empty($online_users)): ?>
        <div style="text-align: center; padding: 3rem; color: #94a3b8;">
            <i class="fas fa-user-slash" style="font-size: 3rem; margin-bottom: 1rem; opacity: 0.5;"></i>
            <p style="font-size: 1.4rem;">No users currently online</p>
        </div>
    <?php else: ?>
        <div class="table-container">
        <table class="data-table">
            <thead>
                <tr>
                    <th>User</th>
                    <th>Email</th>
                    <th>Plan</th>
                    <th>Status</th>
                    <th>Location</th>
                    <th>Last Login</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($online_users as $user): ?>
                <tr>
                    <td>
                        <div style="display: flex; align-items: center; gap: 1rem;">
                            <div style="width: 40px; height: 40px; background: linear-gradient(135deg, #48bb78, #38a169); border-radius: 50%; display: flex; align-items: center; justify-content: center; font-weight: bold; color: white; position: relative;">
                                <?= substr($user['name'], 0, 1) ?>
                                <div style="position: absolute; bottom: -2px; right: -2px; width: 12px; height: 12px; background: #48bb78; border: 2px solid #0f172a; border-radius: 50%;"></div>
                            </div>
                            <div>
                                <div style="font-weight: 600; color: white;"><?= htmlspecialchars($user['name']) ?></div>
                                <div style="font-size: 1.2rem; color: #a0a3b0;">ID: <?= $user['id'] ?></div>
                            </div>
                        </div>
                    </td>
                    <td>
                        <div style="color: #60a5fa;"><?= htmlspecialchars($user['email']) ?></div>
                    </td>
                    <td>
                        <span class="status-badge plan-badge plan-<?= strtolower($user['plan']) ?>">
                            <?= ucfirst($user['plan']) ?>
                        </span>
                    </td>
                    <td>
                        <?php if ($user['is_admin']): ?>
                            <span class="status-badge status-admin">Admin</span>
                        <?php else: ?>
                            <span class="status-badge status-user">User</span>
                        <?php endif; ?>
                    </td>
                    <td>
                        <?php if ($user['city'] || $user['country']): ?>
                            <div style="font-size: 1.2rem; color: #a0a3b0;">
                                <?= htmlspecialchars($user['city'] ?? '') ?><?= ($user['city'] && $user['country']) ? ', ' : '' ?><?= htmlspecialchars($user['country'] ?? '') ?>
                            </div>
                            <div style="font-size: 1.1rem; color: #64748b;"><?= htmlspecialchars($user['last_ip'] ?? '') ?></div>
                        <?php else: ?>
                            <span style="color: #64748b;">-</span>
                        <?php endif; ?>
                    </td>
                    <td>
                        <div style="color: #48bb78; font-weight: 600;">
                            <?= date('H:i:s', strtotime($user['last_login'])) ?>
                        </div>
                        <div style="font-size: 1.2rem; color: #a0a3b0;">
                            <?= date('M j', strtotime($user['last_login'])) ?>
                        </div>
                    </td>
                    <td>
                        <div class="action-buttons">
                            <button class="btn btn-primary btn-sm" onclick="loginAsUser(<?= $user['id'] ?>)">
                                <i class="fas fa-sign-in-alt"></i>
                            </button>
                            <button class="btn btn-secondary btn-sm" onclick="location.href='?tab=users'">
                                <i class="fas fa-user"></i>
                            </button>
                        </div>
                    </td>
                </tr>
                <?php endforeach; ?>
            </tbody>
        </table>
        </div>
    <?php endif; ?>
</div>

<!-- Recent Activity -->
<div class="section-header">
    <h3><i class="fas fa-clock"></i> Recent Activity</h3>
</div>

<div style="background: rgba(255, 255, 255, 0.05); border-radius: 16px; padding: 2rem; margin-bottom: 3rem;">
    <table class="data-table">
        <thead>
            <tr>
                <th>Track</th>
                <th>User</th>
                <th>Status</th>
                <th>Created</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($recent_activity as $activity): ?>
            <tr>
                <td>
                    <div style="display: flex; align-items: center; gap: 1rem;">
                        <div style="width: 40px; height: 40px; background: linear-gradient(135deg, #667eea, #764ba2); border-radius: 8px; display: flex; align-items: center; justify-content: center; color: white;">
                            <i class="fas fa-music"></i>
                        </div>
                        <div>
                            <div style="font-weight: 600; color: white;"><?= htmlspecialchars($activity['title'] ?? 'Untitled') ?></div>
                            <div style="font-size: 1.2rem; color: #a0aec0;">ID: <?= $activity['id'] ?></div>
                        </div>
                    </div>
                </td>
                <td>
                    <?php if ($activity['user_name']): ?>
                    <div>
                        <div style="font-weight: 600; color: white;"><?= htmlspecialchars($activity['user_name']) ?></div>
                        <div style="font-size: 1.2rem; color: #a0aec0;"><?= ucfirst($activity['plan']) ?></div>
                    </div>
                    <?php else: ?>
                    <span style="color: #f56565;">Orphaned</span>
                    <?php endif; ?>
                </td>
                <td>
                    <span class="status-badge status-<?= $activity['status'] ?>">
                        <?= ucfirst($activity['status']) ?>
                    </span>
                </td>
                <td><?= date('M j, H:i', strtotime($activity['created_at'])) ?></td>
            </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
    </div>
</div>

<script>
// Retry failed tracks function
function retryFailedTracks() {
    if (confirm('Are you sure you want to retry all failed tracks?')) {
        fetch('admin_api.php?action=retry_failed')
            .then(response => response.json())
            .then(data => {
                if (data.success) {
                    alert('Retried ' + data.retried + ' failed tracks!');
                    location.reload();
                } else {
                    alert('Error retrying tracks: ' + data.error);
                }
            })
            .catch(error => {
                alert('Error retrying tracks: ' + error);
            });
    }
}
</script>