<?php
$pageTitle = 'Dashboard';
include_once 'includes/header.php';

// Fetch stats
$db = new Database();
$db->query("SELECT COUNT(*) as count FROM users");
$user_count = $db->single()->count;
$db->query("SELECT COUNT(*) as count FROM leagues");
$league_count = $db->single()->count;
$db->query("SELECT COUNT(*) as count FROM clubs");
$club_count = $db->single()->count;
$db->query("SELECT COUNT(*) as count FROM fixtures WHERE status = 'live'");
$live_fixtures_count = $db->single()->count;
$db->query("SELECT COUNT(*) as count FROM players");
$player_count = $db->single()->count;
$db->query("SELECT COUNT(*) as count FROM teams");
$team_count = $db->single()->count;
$db->query("SELECT COUNT(*) as count FROM transfers WHERE status = 'pending'");
$pending_transfers_count = $db->single()->count;

// More stats
$db->query("SELECT COUNT(*) as count, SUM(amount) as total_value FROM fines");
$fines_stats = $db->single();
$total_fines_count = $fines_stats->count ?? 0;
$total_fines_value = $fines_stats->total_value ?? 0;

$db->query("SELECT COUNT(*) as count FROM memberships WHERE expiry_date >= CURDATE()");
$active_memberships_count = $db->single()->count ?? 0;

$db->query("SELECT COUNT(*) as count FROM fantasy_teams");
$fantasy_teams_count = $db->single()->count ?? 0;

// Fetch Board Commission (Total Revenue)
$db->query("SELECT balance FROM wallets WHERE entity_type = 'board' AND entity_id = 0");
$board_wallet = $db->single();
$total_board_commission = $board_wallet->balance ?? 0;

// Fetch Registration Fee Stats
$settingModel = new Setting();
$settings = $settingModel->getAll();

$db->query("SELECT SUM(l.reg_fee_total) as total FROM clubs c JOIN leagues l ON c.league_id = l.id");
$total_expected_reg = $db->single()->total ?? 0;

$db->query("SELECT SUM(amount) as total FROM wallet_transactions WHERE type = 'registration_fee' AND (status = 'success' OR status = 'completed')");
$total_collected_reg = $db->single()->total ?? 0;

$currency_symbol = '$';
if (($settings['system_currency'] ?? 'USD') === 'ZMW') $currency_symbol = 'K';
elseif (($settings['system_currency'] ?? 'USD') === 'EUR') $currency_symbol = '€';
elseif (($settings['system_currency'] ?? 'USD') === 'GBP') $currency_symbol = '£';

// Fetch Next Deadline
$next_deadline = null;
try {
    $db->query("SELECT gameweek, deadline_time FROM gameweek_deadlines WHERE deadline_time > NOW() ORDER BY deadline_time ASC LIMIT 1");
    $next_deadline = $db->single();
} catch (Exception $e) {
    // Table might not exist yet, ignore error
}


// Fetch User Growth Data (Last 6 Months)
$db->query("
    SELECT DATE_FORMAT(created_at, '%Y-%m') as month, COUNT(*) as count 
    FROM users 
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 6 MONTH) 
    GROUP BY DATE_FORMAT(created_at, '%Y-%m') 
    ORDER BY month ASC
");
$user_growth_data = $db->resultSet();

// Fetch Recent Payments (Last 5)
$db->query("SELECT p.*, u.first_name, u.last_name 
            FROM payments p 
            LEFT JOIN users u ON p.user_id = u.id 
            ORDER BY p.created_at DESC LIMIT 5");
$recent_payments = $db->resultSet();

// Fetch Upcoming Fixtures (Next 5)
$db->query("SELECT f.*, t1.name as home_team, t2.name as away_team 
            FROM fixtures f 
            JOIN teams t1 ON f.home_team_id = t1.id 
            JOIN teams t2 ON f.away_team_id = t2.id 
            WHERE f.match_datetime >= NOW() 
            ORDER BY f.match_datetime ASC LIMIT 5");
$upcoming_fixtures = $db->resultSet();

// Fetch Recent User Registrations
$db->query("SELECT id, first_name, last_name, email, created_at FROM users ORDER BY created_at DESC LIMIT 5");
$recent_users = $db->resultSet();

// Fetch Recent Fines
$db->query("SELECT f.*, 
            CASE 
                WHEN f.entity_type = 'player' THEN CONCAT(u.first_name, ' ', u.last_name)
                WHEN f.entity_type = 'club' THEN c.name
            END as entity_name
            FROM fines f
            LEFT JOIN users u ON f.entity_id = u.id AND f.entity_type = 'player'
            LEFT JOIN clubs c ON f.entity_id = c.id AND f.entity_type = 'club'
            ORDER BY f.issued_date DESC LIMIT 5");
$recent_fines = $db->resultSet();

include_once 'includes/sidebar.php';
?>

<h1>Admin Dashboard</h1>
<p>Welcome, <strong><?php echo htmlspecialchars($_SESSION['admin_user_name']); ?></strong>! Here's a snapshot of your application.</p>

<?php if (isset($_SESSION['flash_message'])): ?>
    <div class="message success" style="background-color: #d4edda; color: #155724; padding: 15px; border-radius: 5px; margin-bottom: 20px; border: 1px solid #c3e6cb;">
        <?php echo $_SESSION['flash_message']; unset($_SESSION['flash_message']); ?>
    </div>
<?php endif; ?>
<?php if (isset($_SESSION['flash_error'])): ?>
    <div class="message error" style="background-color: #f8d7da; color: #721c24; padding: 15px; border-radius: 5px; margin-bottom: 20px; border: 1px solid #f5c6cb;">
        <?php echo $_SESSION['flash_error']; unset($_SESSION['flash_error']); ?>
    </div>
<?php endif; ?>

<div class="dashboard-stats">
    <div class="stat-card">
        <div class="stat-value"><?php echo $user_count; ?></div>
        <div class="stat-label">Total Users</div>
    </div>
    <div class="stat-card">
        <div class="stat-value"><?php echo $league_count; ?></div>
        <div class="stat-label">Leagues</div>
    </div>
    <div class="stat-card">
        <div class="stat-value"><?php echo $club_count; ?></div>
        <div class="stat-label">Clubs</div>
    </div>
    <div class="stat-card">
        <div class="stat-value"><?php echo $live_fixtures_count; ?></div>
        <div class="stat-label">Live Fixtures</div>
    </div>
    <div class="stat-card">
        <div class="stat-value"><?php echo $player_count; ?></div>
        <div class="stat-label">Total Players</div>
    </div>
    <div class="stat-card">
        <div class="stat-value"><?php echo $team_count; ?></div>
        <div class="stat-label">Total Teams</div>
    </div>
    <div class="stat-card">
        <div class="stat-value"><?php echo $currency_symbol; ?><?php echo number_format($total_board_commission, 2); ?></div>
        <div class="stat-label">Board Commission</div>
    </div>
    <div class="stat-card">
        <div class="stat-value"><?php echo $pending_transfers_count; ?></div>
        <div class="stat-label">Pending Transfers</div>
    </div>
    <div class="stat-card">
        <div class="stat-value"><?php echo $total_fines_count; ?></div>
        <div class="stat-label">Fines Issued</div>
    </div>
    <div class="stat-card">
        <div class="stat-value"><?php echo $active_memberships_count; ?></div>
        <div class="stat-label">Active Memberships</div>
    </div>
    <div class="stat-card">
        <div class="stat-value"><?php echo $fantasy_teams_count; ?></div>
        <div class="stat-label">Fantasy Teams</div>
    </div>
    <?php if ($total_expected_reg > 0): ?>
    <div class="stat-card">
        <div class="stat-value" style="color: #2ecc71;">
            <?php echo $currency_symbol . number_format($total_collected_reg, 0); ?>
            <span style="font-size: 0.5em; color: #999;">/ <?php echo number_format($total_expected_reg, 0); ?></span>
        </div>
        <div class="stat-label">Reg. Fees Collected</div>
        <div style="width: 100%; background: #eee; height: 5px; border-radius: 5px; margin-top: 5px;">
            <div style="width: <?php echo ($total_expected_reg > 0) ? min(100, ($total_collected_reg / $total_expected_reg) * 100) : 0; ?>%; background: #2ecc71; height: 100%; border-radius: 5px;"></div>
        </div>
    </div>
    <?php endif; ?>
    <?php if ($next_deadline): ?>
    <div class="stat-card" style="background: #e74c3c; color: white;">
        <div class="stat-value" style="font-size: 1.5rem;">
            <?php echo date('d M, H:i', strtotime($next_deadline->deadline_time)); ?>
        </div>
        <div class="stat-label" style="color: rgba(255,255,255,0.8);">GW <?php echo $next_deadline->gameweek; ?> Deadline</div>
    </div>
    <?php endif; ?>
</div>

<!-- User Growth Chart -->
<div class="card shadow mb-4" style="margin-top: 2rem;">
    <div class="card-header py-3">
        <h6 class="m-0 font-weight-bold text-primary">User Growth (Last 6 Months)</h6>
    </div>
    <div class="card-body">
        <div class="chart-area">
            <canvas id="userGrowthChart"></canvas>
        </div>
    </div>
</div>

<!-- Recent Activity Row -->
<div class="dashboard-row" style="grid-template-columns: 1fr 1fr;">
    <!-- Recent Payments -->
    <div class="card shadow mb-4">
        <div class="card-header py-3">
            <h6 class="m-0 font-weight-bold text-primary">Recent Payments</h6>
        </div>
        <div class="card-body">
            <div class="table-responsive">
                <table class="table table-bordered" width="100%" cellspacing="0">
                    <thead>
                        <tr>
                            <th>User</th>
                            <th>Amount</th>
                            <th>Status</th>
                            <th>Date</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($recent_payments as $payment): ?>
                        <tr>
                            <td><?php echo htmlspecialchars($payment->first_name . ' ' . $payment->last_name); ?></td>
                            <td><?php echo number_format($payment->amount, 2); ?></td>
                            <td><span class="badge badge-<?php echo $payment->status === 'successful' ? 'success' : 'warning'; ?>"><?php echo ucfirst($payment->status); ?></span></td>
                            <td><?php echo date('d M Y', strtotime($payment->created_at)); ?></td>
                        </tr>
                        <?php endforeach; ?>
                        <?php if(empty($recent_payments)): ?><tr><td colspan="4">No recent payments.</td></tr><?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <!-- Upcoming Fixtures -->
    <div class="card shadow mb-4">
        <div class="card-header py-3">
            <h6 class="m-0 font-weight-bold text-primary">Upcoming Fixtures</h6>
        </div>
        <div class="card-body">
            <div class="table-responsive">
                <table class="table table-bordered" width="100%" cellspacing="0">
                    <thead>
                        <tr>
                            <th>Match</th>
                            <th>Date</th>
                            <th>Time</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($upcoming_fixtures as $fixture): ?>
                        <tr>
                            <td><?php echo htmlspecialchars($fixture->home_team . ' vs ' . $fixture->away_team); ?></td>
                            <td><?php echo date('d M Y', strtotime($fixture->match_datetime)); ?></td>
                            <td><?php echo date('H:i', strtotime($fixture->match_datetime)); ?></td>
                        </tr>
                        <?php endforeach; ?>
                        <?php if(empty($upcoming_fixtures)): ?><tr><td colspan="3">No upcoming fixtures.</td></tr><?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<!-- New Activity Row -->
<div class="dashboard-row">
    <!-- Recent User Registrations -->
    <div class="card shadow mb-4">
        <div class="card-header py-3">
            <h6 class="m-0 font-weight-bold text-primary">Recent User Registrations</h6>
        </div>
        <div class="card-body">
            <div class="table-responsive">
                <table class="table table-bordered" width="100%" cellspacing="0">
                    <thead>
                        <tr>
                            <th>Name</th>
                            <th>Email</th>
                            <th>Date</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($recent_users as $user): ?>
                        <tr>
                            <td><?php echo htmlspecialchars($user->first_name . ' ' . $user->last_name); ?></td>
                            <td><?php echo htmlspecialchars($user->email); ?></td>
                            <td><?php echo date('d M Y', strtotime($user->created_at)); ?></td>
                        </tr>
                        <?php endforeach; ?>
                        <?php if(empty($recent_users)): ?><tr><td colspan="3">No recent registrations.</td></tr><?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <!-- Recent Fines -->
    <div class="card shadow mb-4">
        <div class="card-header py-3">
            <h6 class="m-0 font-weight-bold text-primary">Recent Fines Issued</h6>
        </div>
        <div class="card-body">
            <div class="table-responsive">
                <table class="table table-bordered" width="100%" cellspacing="0">
                    <thead>
                        <tr>
                            <th>Entity</th>
                            <th>Amount</th>
                            <th>Reason</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($recent_fines as $fine): ?>
                        <tr>
                            <td><?php echo htmlspecialchars($fine->entity_name); ?></td>
                            <td><?php echo $currency_symbol . number_format($fine->amount, 2); ?></td>
                            <td><?php echo htmlspecialchars($fine->reason); ?></td>
                        </tr>
                        <?php endforeach; ?>
                        <?php if(empty($recent_fines)): ?><tr><td colspan="3">No recent fines.</td></tr><?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<!-- Include Chart.js -->
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
document.addEventListener('DOMContentLoaded', function() {
    const ctx = document.getElementById('userGrowthChart').getContext('2d');
    const growthData = <?php echo json_encode($user_growth_data); ?>;
    
    const labels = growthData.map(item => {
        const date = new Date(item.month + '-01');
        return date.toLocaleString('default', { month: 'short', year: 'numeric' });
    });
    const data = growthData.map(item => item.count);

    new Chart(ctx, {
        type: 'line',
        data: {
            labels: labels,
            datasets: [{
                label: 'New Users',
                data: data,
                borderColor: '#4e73df',
                backgroundColor: 'rgba(78, 115, 223, 0.05)',
                pointRadius: 3,
                pointBackgroundColor: '#4e73df',
                pointBorderColor: '#4e73df',
                pointHoverRadius: 3,
                pointHoverBackgroundColor: '#4e73df',
                pointHoverBorderColor: '#4e73df',
                pointHitRadius: 10,
                pointBorderWidth: 2,
                tension: 0.3 // Smooth curves
            }]
        },
        options: {
            maintainAspectRatio: false,
            layout: { padding: { left: 10, right: 25, top: 25, bottom: 0 } },
            scales: {
                x: { grid: { display: false, drawBorder: false }, ticks: { maxTicksLimit: 7 } },
                y: { ticks: { maxTicksLimit: 5, padding: 10, callback: function(value) { return value; } }, grid: { color: "rgb(234, 236, 244)", zeroLineColor: "rgb(234, 236, 244)", drawBorder: false, borderDash: [2], zeroLineBorderDash: [2] } }
            },
            plugins: {
                legend: { display: false },
                tooltip: {
                    backgroundColor: "rgb(255,255,255)",
                    bodyColor: "#858796",
                    titleMarginBottom: 10,
                    titleColor: '#6e707e',
                    titleFont: { size: 14 },
                    borderColor: '#dddfeb',
                    borderWidth: 1,
                    xPadding: 15,
                    yPadding: 15,
                    displayColors: false,
                    intersect: false,
                    mode: 'index',
                    caretPadding: 10,
                }
            }
        }
    });
});
</script>

<style>
.chart-area {
    position: relative;
    height: 20rem;
    width: 100%;
}
.dashboard-row {
    display: grid;
    grid-template-columns: 1fr 1fr;
    gap: 20px;
}
@media (max-width: 992px) {
    .dashboard-row { grid-template-columns: 1fr; }
}
.card {
    background: #fff;
    border-radius: 8px;
    box-shadow: 0 0.15rem 1.75rem 0 rgba(58, 59, 69, 0.15);
    border: 1px solid #e3e6f0;
}
.card-header {
    padding: 0.75rem 1.25rem;
    background-color: #f8f9fc;
    border-bottom: 1px solid #e3e6f0;
}
.card-body { padding: 1.25rem; }
.table { width: 100%; border-collapse: collapse; }
.table th, .table td { padding: 0.75rem; border-top: 1px solid #e3e6f0; text-align: left; }
.badge { padding: 0.25em 0.4em; font-size: 75%; font-weight: 700; border-radius: 0.25rem; color: #fff; }
.badge-success { background-color: #1cc88a; }
.badge-warning { background-color: #f6c23e; }
.dashboard-stats {
    display: grid;
    grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
    gap: 20px;
    margin-bottom: 2rem;
}
</style>

<?php include_once 'includes/footer.php'; ?>