<?php
$db = getDB();

// ---------- Filters ----------
$searchUser   = $_GET['search_user'] ?? '';
$statusFilter = $_GET['status'] ?? '';
$pageNum      = isset($_GET['p']) ? max(1, intval($_GET['p'])) : 1;
$limit        = 50;
$offset       = ($pageNum - 1) * $limit;

// ---------- Build WHERE conditions ----------
$where = [];
$params = [];
$types = '';

if (!empty($searchUser)) {
    $where[] = "(u.name LIKE ? OR u.email LIKE ?)";
    $param = "%$searchUser%";
    $params[] = $param;
    $params[] = $param;
    $types .= 'ss';
}
if (!empty($statusFilter)) {
    $where[] = "s.status = ?";
    $params[] = $statusFilter;
    $types .= 's';
}

$whereSQL = '';
if (!empty($where)) {
    $whereSQL = 'WHERE ' . implode(' AND ', $where);
}

// Count
$countSQL = "SELECT COUNT(*) FROM subscriptions s JOIN users u ON s.user_id = u.id $whereSQL";
$stmt = $db->prepare($countSQL);
if ($params) $stmt->bind_param($types, ...$params);
$stmt->execute();
$total = $stmt->get_result()->fetch_row()[0];
$totalPages = ceil($total / $limit);

// Fetch data
$sql = "SELECT s.*, u.name AS user_name, u.email AS user_email 
        FROM subscriptions s 
        JOIN users u ON s.user_id = u.id 
        $whereSQL 
        ORDER BY s.created_at DESC 
        LIMIT ? OFFSET ?";
$types .= 'ii';
$params[] = $limit;
$params[] = $offset;
$stmt = $db->prepare($sql);
$stmt->bind_param($types, ...$params);
$stmt->execute();
$subs = $stmt->get_result();
?>

<h3>Subscriptions</h3>

<form class="row g-2 mb-3" method="get">
    <input type="hidden" name="page" value="subscriptions">
    <div class="col-auto">
        <input type="text" name="search_user" class="form-control" placeholder="Search by user name/email"
               value="<?= htmlspecialchars($searchUser) ?>">
    </div>
    <div class="col-auto">
        <select name="status" class="form-select">
            <option value="">All statuses</option>
            <option value="pending" <?= $statusFilter === 'pending' ? 'selected' : '' ?>>Pending</option>
            <option value="active" <?= $statusFilter === 'active' ? 'selected' : '' ?>>Active</option>
            <option value="expired" <?= $statusFilter === 'expired' ? 'selected' : '' ?>>Expired</option>
            <option value="cancelled" <?= $statusFilter === 'cancelled' ? 'selected' : '' ?>>Cancelled</option>
        </select>
    </div>
    <div class="col-auto">
        <button type="submit" class="btn btn-outline-secondary"><i class="fa fa-filter"></i> Filter</button>
    </div>
</form>

<div class="table-responsive">
    <table class="table table-striped table-hover">
        <thead class="table-dark">
            <tr>
                <th>ID</th>
                <th>User</th>
                <th>Plan</th>
                <th>Amount</th>
                <th>Status</th>
                <th>Start</th>
                <th>End</th>
                <th>Created</th>
            </tr>
        </thead>
        <tbody>
        <?php if ($subs->num_rows > 0): ?>
            <?php while($s = $subs->fetch_assoc()): ?>
            <tr>
                <td><?= $s['id'] ?></td>
                <td>
                    <?= htmlspecialchars($s['user_name']) ?> (<?= htmlspecialchars($s['user_email']) ?>)
                    <a href="?page=users&action=view&id=<?= $s['user_id'] ?>" class="text-decoration-none ms-1" title="View user"><i class="fa fa-external-link-alt"></i></a>
                </td>
                <td><?= $s['plan_type'] ?></td>
                <td><?= number_format($s['amount'], 2) ?> BDT</td>
                <td>
                    <?php
                    $badge = 'secondary';
                    if ($s['status'] === 'active') $badge = 'success';
                    elseif ($s['status'] === 'expired') $badge = 'warning';
                    elseif ($s['status'] === 'cancelled') $badge = 'danger';
                    ?>
                    <span class="badge bg-<?= $badge ?>"><?= $s['status'] ?></span>
                </td>
                <td><?= date('Y-m-d', strtotime($s['starts_at'])) ?></td>
                <td><?= $s['ends_at'] ? date('Y-m-d', strtotime($s['ends_at'])) : 'N/A' ?></td>
                <td><?= date('Y-m-d', strtotime($s['created_at'])) ?></td>
            </tr>
            <?php endwhile; ?>
        <?php else: ?>
            <tr><td colspan="8" class="text-center">No subscriptions found.</td></tr>
        <?php endif; ?>
        </tbody>
    </table>
</div>

<!-- Pagination -->
<?php if ($totalPages > 1): ?>
<nav>
    <ul class="pagination">
        <?php for ($i = 1; $i <= $totalPages; $i++): ?>
            <li class="page-item <?= $i == $pageNum ? 'active' : '' ?>">
                <a class="page-link" href="?page=subscriptions&p=<?= $i ?>&search_user=<?= urlencode($searchUser) ?>&status=<?= urlencode($statusFilter) ?>">
                    <?= $i ?>
                </a>
            </li>
        <?php endfor; ?>
    </ul>
</nav>
<?php endif; ?>