<?php
$db = getDB();

// ---------- Filters ----------
$searchUser   = $_GET['search_user'] ?? '';
$statusFilter = $_GET['status'] ?? '';
$dateFrom     = $_GET['date_from'] ?? '';
$dateTo       = $_GET['date_to'] ?? '';
$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[] = "t.status = ?";
    $params[] = $statusFilter;
    $types .= 's';
}
if (!empty($dateFrom)) {
    $where[] = "DATE(t.transaction_date) >= ?";
    $params[] = $dateFrom;
    $types .= 's';
}
if (!empty($dateTo)) {
    $where[] = "DATE(t.transaction_date) <= ?";
    $params[] = $dateTo;
    $types .= 's';
}

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

// Count
$countSQL = "SELECT COUNT(*) FROM payment_transactions t JOIN users u ON t.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 t.*, u.name AS user_name, u.email AS user_email, s.plan_type 
        FROM payment_transactions t 
        JOIN users u ON t.user_id = u.id 
        LEFT JOIN subscriptions s ON t.subscription_id = s.id 
        $whereSQL 
        ORDER BY t.transaction_date DESC 
        LIMIT ? OFFSET ?";
$types .= 'ii';
$params[] = $limit;
$params[] = $offset;
$stmt = $db->prepare($sql);
$stmt->bind_param($types, ...$params);
$stmt->execute();
$txns = $stmt->get_result();
?>

<h3>Payment Transactions</h3>

<form class="row g-2 mb-3" method="get">
    <input type="hidden" name="page" value="transactions">
    <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="initiated" <?= $statusFilter === 'initiated' ? 'selected' : '' ?>>Initiated</option>
            <option value="success" <?= $statusFilter === 'success' ? 'selected' : '' ?>>Success</option>
            <option value="failed" <?= $statusFilter === 'failed' ? 'selected' : '' ?>>Failed</option>
            <option value="cancelled" <?= $statusFilter === 'cancelled' ? 'selected' : '' ?>>Cancelled</option>
            <option value="verification_failed" <?= $statusFilter === 'verification_failed' ? 'selected' : '' ?>>Verification Failed</option>
        </select>
    </div>
    <div class="col-auto">
        <input type="date" name="date_from" class="form-control" value="<?= htmlspecialchars($dateFrom) ?>" title="From date">
    </div>
    <div class="col-auto">
        <input type="date" name="date_to" class="form-control" value="<?= htmlspecialchars($dateTo) ?>" title="To date">
    </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>Order ID</th>
                <th>Amount</th>
                <th>Status</th>
                <th>Method</th>
                <th>Date</th>
            </tr>
        </thead>
        <tbody>
        <?php if ($txns->num_rows > 0): ?>
            <?php while($t = $txns->fetch_assoc()): ?>
            <tr>
                <td><?= $t['id'] ?></td>
                <td>
                    <?= htmlspecialchars($t['user_name']) ?> (<?= htmlspecialchars($t['user_email']) ?>)
                    <a href="?page=users&action=view&id=<?= $t['user_id'] ?>" class="text-decoration-none ms-1" title="View user"><i class="fa fa-external-link-alt"></i></a>
                </td>
                <td><?= htmlspecialchars($t['plan_type'] ?? '') ?></td>
                <td><small><?= htmlspecialchars($t['customer_order_id']) ?></small></td>
                <td><?= number_format($t['amount'], 2) ?> BDT</td>
                <td>
                    <?php
                    $badge = 'secondary';
                    if ($t['status'] === 'success') $badge = 'success';
                    elseif ($t['status'] === 'failed' || $t['status'] === 'verification_failed') $badge = 'danger';
                    elseif ($t['status'] === 'cancelled') $badge = 'warning';
                    ?>
                    <span class="badge bg-<?= $badge ?>"><?= $t['status'] ?></span>
                </td>
                <td><?= htmlspecialchars($t['payment_method'] ?? '') ?></td>
                <td><?= date('Y-m-d H:i', strtotime($t['transaction_date'])) ?></td>
            </tr>
            <?php endwhile; ?>
        <?php else: ?>
            <tr><td colspan="8" class="text-center">No transactions 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=transactions&p=<?= $i ?>&search_user=<?= urlencode($searchUser) ?>&status=<?= urlencode($statusFilter) ?>&date_from=<?= urlencode($dateFrom) ?>&date_to=<?= urlencode($dateTo) ?>">
                    <?= $i ?>
                </a>
            </li>
        <?php endfor; ?>
    </ul>
</nav>
<?php endif; ?>