<?php
require_once '../src/Database.php';
require_once '../src/Auth.php';
$auth = new Auth();
$auth->requireLogin();

$db = new Database();
$conn = $db->getConnection();

// Pagination settings
$items_per_page = 20;
$page = isset($_GET['page']) && is_numeric($_GET['page']) ? intval($_GET['page']) : 1;
if ($page < 1) $page = 1;

// Get filter parameters
$status_filter = isset($_GET['status']) ? trim($_GET['status']) : '';
$search_query = isset($_GET['search']) ? trim($_GET['search']) : '';

// Build query based on filters
$where_clauses = ['checkout_id IS NOT NULL'];
$params = [];

if ($status_filter) {
    if ($status_filter === 'completed') {
        $where_clauses[] = 'is_paid = ?';
        $params[] = 'yes';
    } elseif ($status_filter === 'pending') {
        $where_clauses[] = 'is_paid = ?';
        $params[] = 'no';
    }
}

if ($search_query) {
    $where_clauses[] = '(phone LIKE ? OR checkout_id LIKE ?)';
    $params[] = '%' . $search_query . '%';
    $params[] = '%' . $search_query . '%';
}

$where_sql = 'WHERE ' . implode(' AND ', $where_clauses);

// Get total count
$total_count = 0;
try {
    $stmt = $conn->prepare("SELECT COUNT(*) as count FROM mpesa_stk_queue $where_sql");
    $stmt->execute($params);
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    $total_count = $result['count'] ?? 0;
} catch (Exception $e) {
    $total_count = 0;
}

// Calculate pagination
$total_pages = ceil($total_count / $items_per_page);
if ($page > $total_pages && $total_pages > 0) {
    $page = $total_pages;
}

$offset = ($page - 1) * $items_per_page;

// Get paginated STK requests
$stk_requests = [];
try {
    $sql = "SELECT id, phone, amount, checkout_id, is_paid, created_at FROM mpesa_stk_queue $where_sql ORDER BY created_at DESC LIMIT :limit OFFSET :offset";
    $stmt = $conn->prepare($sql);
    
    // Bind filter parameters
    $paramIndex = 1;
    foreach ($params as $param) {
        $stmt->bindValue($paramIndex++, $param, PDO::PARAM_STR);
    }
    
    // Bind pagination parameters
    $stmt->bindValue(':limit', $items_per_page, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    
    $stmt->execute();
    $stk_requests = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (Exception $e) {
    $stk_requests = [];
    error_log('Error fetching STK requests: ' . $e->getMessage());
}

// Get status counts
$completed_count = 0;
$pending_count = 0;
try {
    $stmt = $conn->query('SELECT COUNT(*) as count FROM mpesa_stk_queue WHERE checkout_id IS NOT NULL AND is_paid = "yes"');
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    $completed_count = $result['count'] ?? 0;

    $stmt = $conn->query('SELECT COUNT(*) as count FROM mpesa_stk_queue WHERE checkout_id IS NOT NULL AND is_paid = "no"');
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    $pending_count = $result['count'] ?? 0;
} catch (Exception $e) {
    // Ignore
}

// Get total amount
$total_amount = 0;
try {
    $stmt = $conn->query('SELECT SUM(amount) as total FROM mpesa_stk_queue WHERE checkout_id IS NOT NULL AND is_paid = "yes"');
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    $total_amount = $result['total'] ?? 0;
} catch (Exception $e) {
    // Ignore
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>STK Push Requests - USSD System</title>
    <script src="https://cdn.tailwindcss.com"></script>
</head>
<body class="bg-gray-100">
    <?php include 'sidebar.php'; ?>
    <div class="md:ml-64 min-h-screen">
        <!-- Header -->
        <div class="bg-white shadow-md p-4 md:p-6 border-b border-gray-200">
            <div>
                <h1 class="text-3xl font-bold text-gray-800">STK Push Requests</h1>
                <p class="text-gray-600 text-sm mt-1">View and manage M-Pesa STK push payment requests</p>
            </div>
        </div>

        <!-- Content -->
        <div class="p-4 md:p-8">
            <!-- Statistics Cards -->
            <div class="grid grid-cols-1 md:grid-cols-4 gap-4 mb-6">
                <div class="bg-white rounded-lg shadow p-6">
                    <div class="flex items-center justify-between">
                        <div>
                            <p class="text-gray-600 text-sm font-medium">Total STK Requests</p>
                            <p class="text-3xl font-bold text-blue-600 mt-2"><?php echo number_format($total_count); ?></p>
                        </div>
                        <svg class="w-12 h-12 text-blue-100" fill="currentColor" viewBox="0 0 20 20">
                            <path d="M4 3a2 2 0 00-2 2v10a2 2 0 002 2h12a2 2 0 002-2V5a2 2 0 00-2-2H4zm12 12H4V5h12v10z"></path>
                        </svg>
                    </div>
                </div>

                <div class="bg-white rounded-lg shadow p-6">
                    <div class="flex items-center justify-between">
                        <div>
                            <p class="text-gray-600 text-sm font-medium">Completed</p>
                            <p class="text-3xl font-bold text-green-600 mt-2"><?php echo number_format($completed_count); ?></p>
                        </div>
                        <svg class="w-12 h-12 text-green-100" fill="currentColor" viewBox="0 0 20 20">
                            <path fill-rule="evenodd" d="M10 18a8 8 0 100-16 8 8 0 000 16zm3.707-9.293a1 1 0 00-1.414-1.414L9 10.586 7.707 9.293a1 1 0 00-1.414 1.414l2 2a1 1 0 001.414 0l4-4z" clip-rule="evenodd"></path>
                        </svg>
                    </div>
                </div>

                <div class="bg-white rounded-lg shadow p-6">
                    <div class="flex items-center justify-between">
                        <div>
                            <p class="text-gray-600 text-sm font-medium">Pending</p>
                            <p class="text-3xl font-bold text-yellow-600 mt-2"><?php echo number_format($pending_count); ?></p>
                        </div>
                        <svg class="w-12 h-12 text-yellow-100" fill="currentColor" viewBox="0 0 20 20">
                            <path fill-rule="evenodd" d="M10 18a8 8 0 100-16 8 8 0 000 16zm1-11a1 1 0 10-2 0v3.5H7a1 1 0 100 2h4a1 1 0 001-1V7z" clip-rule="evenodd"></path>
                        </svg>
                    </div>
                </div>

                <div class="bg-white rounded-lg shadow p-6">
                    <div class="flex items-center justify-between">
                        <div>
                            <p class="text-gray-600 text-sm font-medium">Total Amount</p>
                            <p class="text-3xl font-bold text-purple-600 mt-2">KES <?php echo number_format($total_amount, 0); ?></p>
                        </div>
                        <svg class="w-12 h-12 text-purple-100" fill="currentColor" viewBox="0 0 20 20">
                            <path d="M8.16 2.75a.75.75 0 00-.328 1.428c.896.359 1.57 1.181 1.664 2.286h1.932a.75.75 0 00.75-.75V4.75h2a.75.75 0 010 1.5h-2v1h1.996a2.75 2.75 0 010 5.5H12.9c-.096 1.105-.768 1.927-1.664 2.286a.75.75 0 10.328 1.428c1.34-.538 2.355-1.779 2.505-3.214h1.831a.75.75 0 010 1.5h-1.838v1.25a.75.75 0 01-1.5 0V15h-1a.75.75 0 010-1.5h1v-1H9.996a2.75 2.75 0 010-5.5h2.1c.096-1.105.768-1.927 1.664-2.286a.75.75 0 00-.328-1.428c-1.34.538-2.355 1.779-2.505 3.214H9.196V4.75a.75.75 0 00-.75-.75H6.5v1h1.66z"></path>
                        </svg>
                    </div>
                </div>
            </div>

            <!-- Filters Section -->
            <div class="bg-white rounded-lg shadow-md p-6 mb-6">
                <h2 class="text-lg font-bold text-gray-800 mb-4">Filters</h2>
                <form method="GET" class="space-y-4">
                    <div class="grid grid-cols-1 md:grid-cols-3 gap-4">
                        <!-- Search -->
                        <div>
                            <label class="block text-sm font-medium text-gray-700 mb-1">Search (Phone or Checkout ID)</label>
                            <input type="text" name="search" placeholder="Search by phone or checkout ID..." value="<?php echo htmlspecialchars($search_query); ?>" class="w-full px-4 py-2 border border-gray-300 rounded-lg focus:outline-none focus:ring-2 focus:ring-blue-500">
                        </div>

                        <!-- Status Filter -->
                        <div>
                            <label class="block text-sm font-medium text-gray-700 mb-1">Status</label>
                            <select name="status" class="w-full px-4 py-2 border border-gray-300 rounded-lg focus:outline-none focus:ring-2 focus:ring-blue-500">
                                <option value="">All Statuses</option>
                                <option value="completed" <?php echo $status_filter === 'completed' ? 'selected' : ''; ?>>Completed</option>
                                <option value="pending" <?php echo $status_filter === 'pending' ? 'selected' : ''; ?>>Pending</option>
                            </select>
                        </div>

                        <!-- Action Buttons -->
                        <div class="flex items-end gap-2">
                            <button type="submit" class="flex-1 bg-blue-600 hover:bg-blue-700 text-white px-6 py-2 rounded-lg font-semibold transition">Apply Filters</button>
                            <a href="stk_requests.php" class="flex-1 bg-gray-600 hover:bg-gray-700 text-white px-6 py-2 rounded-lg font-semibold transition text-center">Clear</a>
                        </div>
                    </div>
                </form>
            </div>

            <!-- STK Requests Table -->
            <div class="bg-white rounded-lg shadow-md overflow-hidden">
                <div class="overflow-x-auto">
                    <table class="w-full">
                        <thead class="bg-gray-50 border-b border-gray-200">
                            <tr>
                                <th class="px-6 py-3 text-left text-sm font-semibold text-gray-700">Phone Number</th>
                                <th class="px-6 py-3 text-left text-sm font-semibold text-gray-700">Amount</th>
                                <th class="px-6 py-3 text-left text-sm font-semibold text-gray-700">Checkout ID</th>
                                <th class="px-6 py-3 text-left text-sm font-semibold text-gray-700">Reference</th>
                                <th class="px-6 py-3 text-left text-sm font-semibold text-gray-700">Status</th>
                                <th class="px-6 py-3 text-left text-sm font-semibold text-gray-700">Date</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($stk_requests as $request): ?>
                            <tr class="border-b border-gray-200 hover:bg-gray-50 transition">
                                <td class="px-6 py-3 text-sm font-mono text-gray-900"><?php echo htmlspecialchars($request['phone']); ?></td>
                                <td class="px-6 py-3 text-sm font-semibold text-gray-900">
                                    KES <?php echo number_format($request['amount'], 2); ?>
                                </td>
                                <td class="px-6 py-3 text-sm font-mono text-gray-700 break-all"><?php echo htmlspecialchars($request['checkout_id']); ?></td>
                                <td class="px-6 py-3 text-sm font-mono text-gray-700"><?php echo htmlspecialchars($request['paybill_ref'] ?? '-'); ?></td>
                                <td class="px-6 py-3 text-sm">
                                    <?php if ($request['is_paid'] === 'yes'): ?>
                                        <span class="px-3 py-1 bg-green-100 text-green-800 rounded-full text-xs font-semibold">Completed</span>
                                    <?php else: ?>
                                        <span class="px-3 py-1 bg-yellow-100 text-yellow-800 rounded-full text-xs font-semibold">Pending</span>
                                    <?php endif; ?>
                                </td>
                                <td class="px-6 py-3 text-sm text-gray-600">
                                    <?php echo date('M d, Y H:i', strtotime($request['created_at'])); ?>
                                </td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                    <?php if (empty($stk_requests)): ?>
                    <div class="text-center text-gray-500 py-12">
                        <svg class="w-12 h-12 mx-auto mb-3 text-gray-400" fill="none" stroke="currentColor" viewBox="0 0 24 24">
                            <path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M4 3a2 2 0 00-2 2v10a2 2 0 002 2h12a2 2 0 002-2V5a2 2 0 00-2-2H4z"></path>
                        </svg>
                        <p class="text-lg">No STK push requests found</p>
                        <p class="text-sm">Requests will appear here</p>
                    </div>
                    <?php endif; ?>
                </div>
            </div>

            <!-- Pagination -->
            <?php if ($total_pages > 1): ?>
            <div class="mt-6 flex justify-between items-center flex-wrap gap-4">
                <div class="text-sm text-gray-600">
                    Showing <strong><?php echo (($page - 1) * $items_per_page) + 1; ?></strong> to <strong><?php echo min($page * $items_per_page, $total_count); ?></strong> of <strong><?php echo number_format($total_count); ?></strong> requests
                </div>
                <nav class="flex gap-2 flex-wrap">
                    <?php if ($page > 1): ?>
                    <a href="?page=1<?php echo $status_filter ? '&status=' . urlencode($status_filter) : ''; ?><?php echo $search_query ? '&search=' . urlencode($search_query) : ''; ?>" class="px-3 py-2 border border-gray-300 rounded-lg text-gray-700 hover:bg-gray-50 transition">First</a>
                    <a href="?page=<?php echo $page - 1; ?><?php echo $status_filter ? '&status=' . urlencode($status_filter) : ''; ?><?php echo $search_query ? '&search=' . urlencode($search_query) : ''; ?>" class="px-3 py-2 border border-gray-300 rounded-lg text-gray-700 hover:bg-gray-50 transition">Previous</a>
                    <?php endif; ?>

                    <?php
                    $start_page = max(1, $page - 2);
                    $end_page = min($total_pages, $page + 2);
                    
                    if ($start_page > 1): ?>
                    <span class="px-3 py-2">...</span>
                    <?php endif; ?>

                    <?php for ($i = $start_page; $i <= $end_page; $i++): ?>
                    <?php if ($i === $page): ?>
                    <button class="px-3 py-2 bg-blue-600 text-white rounded-lg font-semibold"><?php echo $i; ?></button>
                    <?php else: ?>
                    <a href="?page=<?php echo $i; ?><?php echo $status_filter ? '&status=' . urlencode($status_filter) : ''; ?><?php echo $search_query ? '&search=' . urlencode($search_query) : ''; ?>" class="px-3 py-2 border border-gray-300 rounded-lg text-gray-700 hover:bg-gray-50 transition"><?php echo $i; ?></a>
                    <?php endif; ?>
                    <?php endfor; ?>

                    <?php if ($end_page < $total_pages): ?>
                    <span class="px-3 py-2">...</span>
                    <?php endif; ?>

                    <?php if ($page < $total_pages): ?>
                    <a href="?page=<?php echo $page + 1; ?><?php echo $status_filter ? '&status=' . urlencode($status_filter) : ''; ?><?php echo $search_query ? '&search=' . urlencode($search_query) : ''; ?>" class="px-3 py-2 border border-gray-300 rounded-lg text-gray-700 hover:bg-gray-50 transition">Next</a>
                    <a href="?page=<?php echo $total_pages; ?><?php echo $status_filter ? '&status=' . urlencode($status_filter) : ''; ?><?php echo $search_query ? '&search=' . urlencode($search_query) : ''; ?>" class="px-3 py-2 border border-gray-300 rounded-lg text-gray-700 hover:bg-gray-50 transition">Last</a>
                    <?php endif; ?>
                </nav>
            </div>
            <?php endif; ?>
        </div>
    </div>
</body>
</html>
