<?php
// ------------------------------------------------------------
// 1. Load DB + helpers + auth BEFORE any HTML output
// ------------------------------------------------------------
require_once '../config/database.php';
require_once '../includes/functions.php';
require_once '../includes/auth.php';
requireLogin();

// ---------- Handle status update ----------
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['update_status'])) {
    $subId   = (int)($_POST['subscription_id'] ?? 0);
    $newStat = $_POST['new_status'] ?? '';
    $allowed = ['active', 'expired', 'cancelled', 'pending', 'paid_return'];
    if ($subId > 0 && in_array($newStat, $allowed)) {
        $upd = $pdo->prepare("UPDATE user_subscriptions SET status = :status WHERE id = :id");
        $upd->execute([':status' => $newStat, ':id' => $subId]);
        setFlash('success', 'Subscription #' . $subId . ' updated to ' . ucfirst($newStat) . '.');
    } else {
        setFlash('danger', 'Invalid status change.');
    }
    // Rebuild the current query string to preserve filters
    $queryString = $_GET ? http_build_query($_GET) : '';
    header('Location: subscriptions.php?' . $queryString);
    exit;
}

// ---------- Filters & Pagination ----------
$page = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
$perPage = 15;
$offset = ($page - 1) * $perPage;

$statusFilter = $_GET['status'] ?? '';
$search = trim($_GET['search'] ?? '');

$where = "WHERE 1=1";
$params = [];

if ($statusFilter !== '') {
    $where .= " AND us.status = :status";
    $params[':status'] = $statusFilter;
}
if ($search !== '') {
    // Escape special LIKE characters
    $escapedSearch = addcslashes($search, '%_');
    $likePattern = "%$escapedSearch%";
    // ✅ Unique parameter names for each field (fixes search)
    $where .= " AND (u.reseller_name LIKE :search_name OR u.phone LIKE :search_phone OR u.email LIKE :search_email)";
    $params[':search_name']  = $likePattern;
    $params[':search_phone'] = $likePattern;
    $params[':search_email'] = $likePattern;
}

// Total count
$countStmt = $pdo->prepare("
    SELECT COUNT(*) FROM user_subscriptions us
    JOIN users u ON us.user_id = u.id
    JOIN subscription_plans sp ON us.plan_id = sp.id
    $where
");
$countStmt->execute($params);
$total = $countStmt->fetchColumn();
$totalPages = ceil($total / $perPage);

// Fetch subscriptions
$subsStmt = $pdo->prepare("
    SELECT us.*, u.reseller_name, u.shop_name, u.phone, sp.plan_name
    FROM user_subscriptions us
    JOIN users u ON us.user_id = u.id
    JOIN subscription_plans sp ON us.plan_id = sp.id
    $where
    ORDER BY us.started_at DESC
    LIMIT $perPage OFFSET $offset
");
$subsStmt->execute($params);
$subscriptions = $subsStmt->fetchAll();

$statuses = ['active', 'expired', 'cancelled', 'pending', 'paid_return'];

// Colour mapping
$statusColors = [
    'active'      => ['bg' => 'success',  'text' => 'white'],
    'expired'     => ['bg' => 'warning',  'text' => 'dark'],
    'cancelled'   => ['bg' => 'danger',   'text' => 'white'],
    'pending'     => ['bg' => 'secondary','text' => 'white'],
    'paid_return' => ['bg' => 'info',     'text' => 'dark'],
];
?>

<?php
// 2. Now include the full layout (header/sidebar)
require_once '../includes/header.php';
?>

<h1 class="h2 mb-4">Subscriptions</h1>

<?php $flash = getFlash(); if ($flash): ?>
<div class="alert alert-<?= $flash['type'] ?> alert-dismissible fade show" role="alert">
    <?= h($flash['message']) ?>
    <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
</div>
<?php endif; ?>

<div class="card shadow-sm mb-4">
    <div class="card-body">
        <form method="GET" class="row g-2 align-items-end">
            <div class="col-md-4">
                <label class="form-label">Search Reseller</label>
                <input type="text" name="search" class="form-control" placeholder="Name / Phone / Email" value="<?= h($search) ?>">
            </div>
            <div class="col-md-3">
                <label class="form-label">Status</label>
                <select name="status" class="form-select">
                    <option value="">All</option>
                    <?php foreach ($statuses as $s): ?>
                        <option value="<?= $s ?>" <?= $statusFilter === $s ? 'selected' : '' ?>><?= ucfirst($s) ?></option>
                    <?php endforeach; ?>
                </select>
            </div>
            <div class="col-md-2">
                <button type="submit" class="btn btn-primary w-100">Filter</button>
            </div>
            <div class="col-md-3">
                <a href="subscriptions.php" class="btn btn-outline-secondary w-100">Reset</a>
            </div>
        </form>
    </div>
</div>

<div class="card shadow-sm">
    <div class="card-body p-0">
        <div class="table-responsive">
            <table class="table table-hover mb-0">
                <thead class="table-light">
                    <tr>
                        <th>ID</th>
                        <th>Reseller</th>
                        <th>Plan</th>
                        <th>Started</th>
                        <th>Expires</th>
                        <th>Status</th>
                        <th>Payment</th>
                        <th class="text-end">Action</th>
                    </tr>
                </thead>
                <tbody>
                <?php if (empty($subscriptions)): ?>
                    <tr><td colspan="8" class="text-center py-3">No subscriptions found.</td></tr>
                <?php else: foreach ($subscriptions as $sub): 
                    $curColor = $statusColors[$sub['status']] ?? ['bg'=>'secondary','text'=>'white'];
                ?>
                    <tr>
                        <td><?= $sub['id'] ?></td>
                        <td>
                            <?= h($sub['reseller_name']) ?><br>
                            <small class="text-muted"><?= h($sub['phone']) ?></small>
                        </td>
                        <td><?= h($sub['plan_name']) ?></td>
                        <td><?= date('d M Y', strtotime($sub['started_at'])) ?></td>
                        <td><?= date('d M Y', strtotime($sub['expires_at'])) ?></td>
                        <td>
                            <form method="POST" style="display:inline;">
                                <input type="hidden" name="subscription_id" value="<?= $sub['id'] ?>">
                                <select name="new_status" 
                                        class="form-select form-select-sm d-inline-block w-auto 
                                               bg-<?= $curColor['bg'] ?> text-<?= $curColor['text'] ?>"
                                        style="min-width: 110px;"
                                        onchange="this.form.submit()">
                                    <?php foreach ($statuses as $s): ?>
                                        <option value="<?= $s ?>" <?= $sub['status'] === $s ? 'selected' : '' ?>><?= ucfirst($s) ?></option>
                                    <?php endforeach; ?>
                                </select>
                                <input type="hidden" name="update_status" value="1">
                            </form>
                        </td>
                        <td>
                            <?= h($sub['payment_method']) ?>
                            <?php if ($sub['payment_ref']): ?>
                                <br><small><?= h($sub['payment_ref']) ?></small>
                            <?php endif; ?>
                        </td>
                        <td class="text-end"></td>
                    </tr>
                <?php endforeach; endif; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

<?php if ($totalPages > 1): ?>
<nav class="mt-4">
    <ul class="pagination justify-content-center">
        <li class="page-item <?= $page == 1 ? 'disabled' : '' ?>">
            <a class="page-link" href="?page=<?= $page-1 ?>&status=<?= urlencode($statusFilter) ?>&search=<?= urlencode($search) ?>">&laquo;</a>
        </li>
        <?php for ($i = 1; $i <= $totalPages; $i++): ?>
            <li class="page-item <?= $i == $page ? 'active' : '' ?>">
                <a class="page-link" href="?page=<?= $i ?>&status=<?= urlencode($statusFilter) ?>&search=<?= urlencode($search) ?>"><?= $i ?></a>
            </li>
        <?php endfor; ?>
        <li class="page-item <?= $page == $totalPages ? 'disabled' : '' ?>">
            <a class="page-link" href="?page=<?= $page+1 ?>&status=<?= urlencode($statusFilter) ?>&search=<?= urlencode($search) ?>">&raquo;</a>
        </li>
    </ul>
</nav>
<?php endif; ?>

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