<?php
$db = Database::getInstance();
$count = (int) $db->fetchColumn("SELECT COUNT(*) FROM member_subscriptions WHERE status = 'active' AND end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)");
$recent = $db->fetchAll(
    "SELECT ms.end_date, m.full_name_en, m.membership_number, DATEDIFF(ms.end_date, CURDATE()) AS days_left
     FROM member_subscriptions ms JOIN members m ON ms.member_id = m.id
     WHERE ms.status = 'active' AND ms.end_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)
     ORDER BY ms.end_date ASC LIMIT 5"
);
?>
<div class="card h-100">
    <div class="card-header d-flex justify-content-between align-items-center">
        <h6 class="mb-0"><i class="fas fa-exclamation-triangle text-warning me-2"></i>Expiring Subscriptions (30d)</h6>
        <span class="badge bg-warning text-dark"><?= $count ?></span>
    </div>
    <div class="card-body p-0">
        <?php if (empty($recent)): ?>
        <div class="text-center text-muted py-4"><i class="fas fa-check-circle fa-2x mb-2"></i><br>No expiring subscriptions</div>
        <?php else: ?>
        <ul class="list-group list-group-flush">
            <?php foreach ($recent as $r): ?>
            <li class="list-group-item d-flex justify-content-between align-items-center py-2">
                <div><small class="fw-bold"><?= e($r['membership_number']) ?></small><br><small><?= e($r['full_name_en']) ?></small></div>
                <span class="badge <?= $r['days_left'] <= 7 ? 'bg-danger' : 'bg-warning text-dark' ?>"><?= $r['days_left'] ?>d</span>
            </li>
            <?php endforeach; ?>
        </ul>
        <?php endif; ?>
    </div>
    <div class="card-footer text-center"><a href="/subscriptions/expiring" class="small">View All →</a></div>
</div>