<?php
$db = getDB();

// ---------- Fetch leaderboard ----------
// We join with users to display names.
// Order by rank ascending; if rank is 0 (default), fallback to points descending.
$sql = "SELECT l.*, u.name, u.email, u.avatar 
        FROM leaderboard l 
        JOIN users u ON l.user_id = u.id 
        ORDER BY l.rank ASC, l.points DESC, l.lessons_done DESC 
        LIMIT 200";   // top 200, adjust if needed
$result = $db->query($sql);

$rank = 0;
?>

<h3>Leaderboard</h3>

<div class="table-responsive">
    <table class="table table-striped table-hover">
        <thead class="table-dark">
            <tr>
                <th>#</th>
                <th>User</th>
                <th>Points</th>
                <th>Streak</th>
                <th>Lessons Done</th>
            </tr>
        </thead>
        <tbody>
        <?php if ($result->num_rows > 0): ?>
            <?php while($row = $result->fetch_assoc()): 
                $rank++;
            ?>
            <tr>
                <td><?= $rank ?></td>
                <td>
                    <?= htmlspecialchars($row['name']) ?>
                    <small class="text-muted d-block"><?= htmlspecialchars($row['email']) ?></small>
                </td>
                <td><strong><?= number_format($row['points']) ?></strong></td>
                <td><?= $row['streak_days'] ?> days</td>
                <td><?= $row['lessons_done'] ?></td>
            </tr>
            <?php endwhile; ?>
        <?php else: ?>
            <tr><td colspan="5" class="text-center">No leaderboard data yet.</td></tr>
        <?php endif; ?>
        </tbody>
    </table>
</div>