<?php
$page_title = 'Members';
require __DIR__ . '/_guard.php';

$q      = trim($_GET['q'] ?? '');
$status = $_GET['status'] ?? '';
$tier   = $_GET['tier']   ?? '';
$sort   = $_GET['sort']   ?? 'newest';

$where  = [];
$params = [];

if ($q !== '') {
    $where[] = "(m.email LIKE :q1 OR m.business_name LIKE :q2 OR m.first_name LIKE :q3 OR m.last_name LIKE :q4)";
    $params['q1'] = '%'.$q.'%';
    $params['q2'] = '%'.$q.'%';
    $params['q3'] = '%'.$q.'%';
    $params['q4'] = '%'.$q.'%';
}
if ($status && in_array($status, ['pending','active','suspended','cancelled'], true)) {
    $where[] = "m.status=:st"; $params['st'] = $status;
}
if ($tier && in_array($tier, ['Bronze','Silver','Gold','Platinum','Diamond'], true)) {
    $where[] = "m.tier=:tier"; $params['tier'] = $tier;
}

$order = $sort==='alpha' ? 'm.business_name ASC' : 'm.created_at DESC';
$where_sql = empty($where) ? '' : ('WHERE '.implode(' AND ',$where));

$members = db_all(
    "SELECT m.*,
            (SELECT COUNT(*) FROM invoices WHERE member_id=m.id AND status IN ('unpaid','overdue')) AS unpaid_count,
            (SELECT COUNT(*) FROM payment_tokens WHERE member_id=m.id AND status='active') AS has_sub
       FROM members m
       $where_sql
       ORDER BY $order
       LIMIT 500",
    $params
);

$status_counts = [];
foreach (db_all("SELECT status, COUNT(*) AS c FROM members GROUP BY status") as $r) {
    $status_counts[$r['status']] = (int)$r['c'];
}
$total = array_sum($status_counts);
?>

<style>
.atbl{width:100%;border-collapse:collapse;font-size:.875rem;}
.atbl th{padding:.55rem .9rem;background:var(--surface-alt);font-size:.72rem;text-transform:uppercase;letter-spacing:.05em;color:var(--ink-muted);border-bottom:2px solid var(--line);white-space:nowrap;text-align:left;}
.atbl td{padding:.6rem .9rem;border-bottom:1px solid var(--line);vertical-align:middle;}
.atbl tr:last-child td{border-bottom:none;}
.atbl tbody tr:hover td{background:var(--surface-alt);}
.ftab{display:inline-flex;align-items:center;gap:.4rem;padding:.3rem .85rem;border-radius:999px;font-size:.82rem;border:1px solid var(--line);text-decoration:none;color:var(--ink);}
.ftab.on{background:var(--brand-primary);color:#fff;border-color:var(--brand-primary);}
.ftab .cnt{background:rgba(0,0,0,.12);border-radius:999px;padding:.05em .45em;font-size:.78em;font-weight:700;}
.tb{display:inline-block;padding:.15em .55em;border-radius:3px;font-size:.7rem;font-weight:700;text-transform:uppercase;letter-spacing:.04em;}
.tb-bronze{background:#cd7f32;color:#fff;}.tb-silver{background:#a0a0ad;color:#fff;}
.tb-gold{background:#c9a227;color:#fff;}.tb-platinum{background:#6a5acd;color:#fff;}
.tb-diamond{background:#1a1a2e;color:#fff;}
</style>

<section class="section">
<div class="container">

<div style="display:flex;justify-content:space-between;align-items:center;margin-bottom:1.25rem;">
    <h1 style="margin:0;">Members <span class="muted" style="font-size:1rem;font-weight:400;">(<?= $total ?>)</span></h1>
</div>

<!-- Filter bar -->
<div style="display:flex;gap:.5rem;flex-wrap:wrap;margin-bottom:.75rem;align-items:center;">
    <a href="?status=" class="ftab <?= $status===''?'on':'' ?>">All <span class="cnt"><?= $total ?></span></a>
    <a href="?status=active" class="ftab <?= $status==='active'?'on':'' ?>">
        Active <span class="cnt"><?= $status_counts['active']??0 ?></span>
    </a>
    <a href="?status=pending" class="ftab <?= $status==='pending'?'on':'' ?>">
        Pending <span class="cnt"><?= $status_counts['pending']??0 ?></span>
    </a>
    <a href="?status=suspended" class="ftab <?= $status==='suspended'?'on':'' ?>">
        Suspended <span class="cnt"><?= $status_counts['suspended']??0 ?></span>
    </a>
    <a href="?status=cancelled" class="ftab <?= $status==='cancelled'?'on':'' ?>">
        Cancelled <span class="cnt"><?= $status_counts['cancelled']??0 ?></span>
    </a>
</div>

<!-- Search + tier + sort -->
<form method="get" style="display:flex;gap:.5rem;margin-bottom:1.1rem;flex-wrap:wrap;">
    <input type="hidden" name="status" value="<?= htmlspecialchars($status) ?>">
    <input type="search" name="q" value="<?= htmlspecialchars($q) ?>"
           placeholder="Search name, email, business…" style="flex:1;min-width:200px;">
    <select name="tier" style="width:130px;">
        <option value="">Any tier</option>
        <?php foreach (['Bronze','Silver','Gold','Platinum','Diamond'] as $t): ?>
            <option value="<?= $t ?>" <?= $tier===$t?'selected':'' ?>><?= $t ?></option>
        <?php endforeach; ?>
    </select>
    <select name="sort" style="width:140px;">
        <option value="newest" <?= $sort==='newest'?'selected':'' ?>>Newest first</option>
        <option value="alpha"  <?= $sort==='alpha'?'selected':'' ?>>A–Z by business</option>
    </select>
    <button type="submit" class="btn">Filter</button>
    <?php if ($q||$tier||$status): ?>
        <a href="members.php" class="btn btn-outline">Clear</a>
    <?php endif; ?>
</form>

<?php if (empty($members)): ?>
    <div class="card"><p class="muted" style="margin:0;">No members match your filters.</p></div>
<?php else: ?>
<div class="card" style="padding:0;overflow:auto;">
    <table class="atbl">
        <thead>
            <tr>
                <th>#</th>
                <th>Business</th>
                <th>Contact</th>
                <th>Tier</th>
                <th>Status</th>
                <th>Subscribed</th>
                <th>Joined</th>
                <th>Renewal</th>
                <th>Unpaid</th>
                <th></th>
            </tr>
        </thead>
        <tbody>
        <?php foreach ($members as $m):
            $ms = $m['status'];
            if ($ms==='active')         $mc='tag-ok';
            elseif ($ms==='cancelled'||$ms==='suspended') $mc='tag-err';
            else                        $mc='muted';
            $tier_lc = strtolower($m['tier']);
        ?>
            <tr>
                <td class="muted" style="font-size:.78rem;"><?= $m['id'] ?></td>
                <td><strong><?= htmlspecialchars($m['business_name']) ?></strong></td>
                <td>
                    <?= htmlspecialchars($m['first_name'].' '.$m['last_name']) ?><br>
                    <small class="muted"><?= htmlspecialchars($m['email']) ?></small>
                </td>
                <td><span class="tb tb-<?= $tier_lc ?>"><?= htmlspecialchars($m['tier']) ?></span></td>
                <td><span class="tag <?= $mc ?>"><?= htmlspecialchars($ms) ?></span>
                    <?php if (!empty($m['cancel_effective_at']) && $m['status']==='active'): ?>
                        <br><small class="muted" style="font-size:.72rem;">
                            ⏱ Ends <?= htmlspecialchars(date('j M', strtotime($m['cancel_effective_at']))) ?>
                        </small>
                    <?php endif; ?>
                </td>
                <td style="text-align:center;">
                    <?= $m['has_sub'] ? '<span class="tag tag-ok">Yes</span>' : '<span class="muted">—</span>' ?>
                </td>
                <td class="muted"><?= $m['join_date'] ? date('j M Y', strtotime($m['join_date'])) : '—' ?></td>
                <td class="muted"><?= $m['renewal_date'] ? date('j M Y', strtotime($m['renewal_date'])) : '—' ?></td>
                <td>
                    <?php if ((int)$m['unpaid_count']>0): ?>
                        <span class="tag tag-err"><?= $m['unpaid_count'] ?></span>
                    <?php else: ?>
                        <span class="muted">—</span>
                    <?php endif; ?>
                </td>
                <td><a href="member-edit.php?id=<?= $m['id'] ?>">Edit →</a></td>
            </tr>
        <?php endforeach; ?>
        </tbody>
    </table>
</div>
<p class="muted" style="font-size:.8rem;margin:.5rem 0 0;">
    Showing <?= count($members) ?> of <?= $total ?> members.
</p>
<?php endif; ?>
</div>
</section>
<?php require __DIR__ . '/_footer.php'; ?>