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
// Analytics Tab
// This file handles all analytics and reporting functionality

// Get comprehensive analytics data
$analytics_data = [];

// User analytics
$user_analytics = $pdo->query("
    SELECT 
        COUNT(*) as total_users,
        COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as new_users_7d,
        COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as new_users_30d,
        COUNT(CASE WHEN plan IN ('starter', 'pro') THEN 1 END) as premium_users,
        AVG(credits) as avg_credits
    FROM users
")->fetch();

// Track analytics
$track_analytics = $pdo->query("
    SELECT 
        COUNT(*) as total_tracks,
        COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END) as new_tracks_7d,
        COUNT(CASE WHEN created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 END) as new_tracks_30d,
        COUNT(CASE WHEN status = 'complete' THEN 1 END) as completed_tracks,
        COUNT(CASE WHEN status = 'processing' THEN 1 END) as processing_tracks,
        COUNT(CASE WHEN status = 'failed' THEN 1 END) as failed_tracks,
        AVG(duration) as avg_duration,
        SUM(duration) as total_duration
    FROM music_tracks
")->fetch();

// Daily activity for the last 30 days
$daily_activity = $pdo->query("
    SELECT 
        DATE(created_at) as date,
        COUNT(*) as total_created,
        COUNT(CASE WHEN status = 'complete' THEN 1 END) as completed,
        COUNT(CASE WHEN status = 'failed' THEN 1 END) as failed
    FROM music_tracks
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    GROUP BY DATE(created_at)
    ORDER BY date DESC
")->fetchAll();

// Top users by track creation
$top_users = $pdo->query("
    SELECT 
        u.name,
        u.email,
        u.plan,
        COUNT(mt.id) as total_tracks,
        COUNT(CASE WHEN mt.status = 'complete' THEN 1 END) as completed_tracks
    FROM users u
    LEFT JOIN music_tracks mt ON u.id = mt.user_id
    GROUP BY u.id, u.name, u.email, u.plan
    HAVING total_tracks > 0
    ORDER BY total_tracks DESC
    LIMIT 10
")->fetchAll();

$analytics_data = [
    'user_analytics' => $user_analytics,
    'track_analytics' => $track_analytics,
    'daily_activity' => $daily_activity,
    'top_users' => $top_users
];
?>

<!-- Analytics Dashboard -->
<div class="section-header">
    <h2><i class="fas fa-chart-line"></i> Analytics Dashboard</h2>
    <p>System performance and user activity metrics</p>
</div>

<!-- Key Metrics -->
<div class="stats-grid" style="margin-bottom: 3rem;">
    <div class="stat-card">
        <div class="stat-number"><?= number_format($user_analytics['total_users']) ?></div>
        <div class="stat-label">Total Users</div>
    </div>
    <div class="stat-card">
        <div class="stat-number"><?= number_format($track_analytics['total_tracks']) ?></div>
        <div class="stat-label">Total Tracks</div>
    </div>
    <div class="stat-card">
        <div class="stat-number"><?= round(($track_analytics['completed_tracks'] / $track_analytics['total_tracks']) * 100, 1) ?>%</div>
        <div class="stat-label">Success Rate</div>
    </div>
    <div class="stat-card">
        <div class="stat-number"><?= number_format($user_analytics['premium_users']) ?></div>
        <div class="stat-label">Premium Users</div>
    </div>
</div>

<!-- Recent Activity -->
<div class="section-header">
    <h3><i class="fas fa-clock"></i> Recent Activity (Last 30 Days)</h3>
</div>

<div class="stats-grid" style="margin-bottom: 3rem;">
    <div class="stat-card">
        <div class="stat-number"><?= number_format($user_analytics['new_users_7d']) ?></div>
        <div class="stat-label">New Users (7d)</div>
    </div>
    <div class="stat-card">
        <div class="stat-number"><?= number_format($user_analytics['new_users_30d']) ?></div>
        <div class="stat-label">New Users (30d)</div>
    </div>
    <div class="stat-card">
        <div class="stat-number"><?= number_format($track_analytics['new_tracks_7d']) ?></div>
        <div class="stat-label">New Tracks (7d)</div>
    </div>
    <div class="stat-card">
        <div class="stat-number"><?= number_format($track_analytics['new_tracks_30d']) ?></div>
        <div class="stat-label">New Tracks (30d)</div>
    </div>
</div>

<!-- Top Users -->
<div class="section-header">
    <h3><i class="fas fa-trophy"></i> Top Users by Track Creation</h3>
</div>

<table class="data-table" style="margin-bottom: 3rem;">
    <thead>
        <tr>
            <th>User</th>
            <th>Plan</th>
            <th>Total Tracks</th>
            <th>Completed</th>
            <th>Success Rate</th>
        </tr>
    </thead>
    <tbody>
        <?php foreach ($top_users as $user): ?>
        <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: 50%; display: flex; align-items: center; justify-content: center; font-weight: bold; color: white;">
                        <?= substr($user['name'], 0, 1) ?>
                    </div>
                    <div>
                        <div style="font-weight: 600; color: white;"><?= htmlspecialchars($user['name']) ?></div>
                        <div style="font-size: 1.2rem; color: #a0aec0;"><?= htmlspecialchars($user['email']) ?></div>
                    </div>
                </div>
            </td>
            <td>
                <span class="status-badge plan-badge plan-<?= strtolower($user['plan']) ?>">
                    <?= ucfirst($user['plan']) ?>
                </span>
            </td>
            <td><?= number_format($user['total_tracks']) ?></td>
            <td><?= number_format($user['completed_tracks']) ?></td>
            <td>
                <?php 
                $success_rate = $user['total_tracks'] > 0 ? ($user['completed_tracks'] / $user['total_tracks']) * 100 : 0;
                echo round($success_rate, 1) . '%';
                ?>
            </td>
        </tr>
        <?php endforeach; ?>
    </tbody>
</table>

<!-- Daily Activity Chart -->
<div class="section-header">
    <h3><i class="fas fa-calendar"></i> Daily 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>Date</th>
                <th>Total Created</th>
                <th>Completed</th>
                <th>Failed</th>
                <th>Success Rate</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach (array_slice($daily_activity, 0, 14) as $day): ?>
            <tr>
                <td><?= date('M j, Y', strtotime($day['date'])) ?></td>
                <td><?= number_format($day['total_created']) ?></td>
                <td style="color: #48bb78;"><?= number_format($day['completed']) ?></td>
                <td style="color: #f56565;"><?= number_format($day['failed']) ?></td>
                <td>
                    <?php 
                    $success_rate = $day['total_created'] > 0 ? ($day['completed'] / $day['total_created']) * 100 : 0;
                    echo round($success_rate, 1) . '%';
                    ?>
                </td>
            </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
</div>

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

<div class="stats-grid">
    <div class="stat-card">
        <div class="stat-number"><?= number_format($track_analytics['processing_tracks']) ?></div>
        <div class="stat-label">Currently Processing</div>
    </div>
    <div class="stat-card">
        <div class="stat-number"><?= number_format($track_analytics['failed_tracks']) ?></div>
        <div class="stat-label">Failed Tracks</div>
    </div>
    <div class="stat-card">
        <div class="stat-number"><?= $track_analytics['avg_duration'] ? gmdate('i:s', round($track_analytics['avg_duration'])) : 'N/A' ?></div>
        <div class="stat-label">Average Duration</div>
    </div>
    <div class="stat-card">
        <div class="stat-number"><?= $track_analytics['total_duration'] ? gmdate('H:i:s', round($track_analytics['total_duration'])) : 'N/A' ?></div>
        <div class="stat-label">Total Duration</div>
    </div>
</div>