<?php
// Basic Offset-based Pagination Example
// This function simulates fetching data from a database
function fetchDataWithOffset(PDO $db, string $table, int $limit, int $offset): array
{
    $stmt = $db->prepare("SELECT * FROM {$table} LIMIT :limit OFFSET :offset");
    $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
    $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Basic Cursor-based (Keyset) Pagination Example
// Assumes 'id' is a unique, auto-incrementing, indexed column for ordering
function fetchDataWithCursor(PDO $db, string $table, int $limit, ?int $lastId): array
{
    $sql = "SELECT * FROM {$table}";
    $params = [];

    if ($lastId !== null) {
        $sql .= " WHERE id > :last_id"; // 'id' as the cursor
        $params[':last_id'] = $lastId;
    }

    $sql .= " ORDER BY id ASC LIMIT :limit";

    $stmt = $db->prepare($sql);
    foreach ($params as $key => $value) {
        $stmt->bindValue($key, $value, PDO::PARAM_INT);
    }
    $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Example usage in an API endpoint (conceptual)
// $db = new PDO(...);

// Offset-based:
// $page = $_GET['page'] ?? 1;
// $limit = $_GET['limit'] ?? 10;
// $offset = ($page - 1) * $limit;
// $data = fetchDataWithOffset($db, 'products', $limit, $offset);

// Cursor-based:
// $limit = $_GET['limit'] ?? 10;
// $lastId = $_GET['last_id'] ?? null; // Null for first page
// $data = fetchDataWithCursor($db, 'products', $limit, $lastId);

// For production, always sanitize and validate input parameters.
?>

<?php
// Example of building dynamic SQL queries for filtering
function fetchFilteredData(PDO $db, string $table, array $filters, int $limit, int $offset):
    array
{
    $sql = "SELECT * FROM {$table} ";
    $whereClauses = [];
    $params = [];

    // Define allowed filters to prevent SQL injection and unexpected behavior
    $allowedFilters = ['status', 'category_id', 'created_at_gt', 'price_lt'];

    foreach ($filters as $key => $value) {
        if (in_array($key, $allowedFilters)) {
            switch ($key) {
                case 'status':
                case 'category_id':
                    $whereClauses[] = "{$key} = :{$key}";
                    $params[":{$key}"] = $value;
                    break;
                case 'created_at_gt': // Greater than
                    $whereClauses[] = "created_at > :{$key}";
                    $params[":{$key}"] = $value;
                    break;
                case 'price_lt': // Less than
                    $whereClauses[] = "price < :{$key}";
                    $params[":{$key}"] = $value;
                    break;
                // Add more filter types as needed (e.g., LIKE, IN, IS NULL)
            }
        }
    }

    if (!empty($whereClauses)) {
        $sql .= " WHERE " . implode(' AND ', $whereClauses);
    }

    $sql .= " LIMIT :limit OFFSET :offset";

    $stmt = $db->prepare($sql);

    foreach ($params as $key => $value) {
        $stmt->bindValue($key, $value);
    }
    $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
    $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);

    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Example usage in an API endpoint (conceptual)
// $db = new PDO(...);
// $queryParams = $_GET; // Assuming GET parameters are the filters and pagination

// $page = $queryParams['page'] ?? 1;
// $limit = $queryParams['limit'] ?? 10;
// $offset = ($page - 1) * $limit;

// $filters = array_filter($queryParams, function($key) use ($allowedFilters) { 
//     return in_array($key, $allowedFilters); 
// }, ARRAY_FILTER_USE_KEY);

// $data = fetchFilteredData($db, 'products', $filters, $limit, $offset);

// Remember to validate and sanitize all user inputs.
?>

<?php
// Integrated Endpoint Logic (Conceptual)
// This example combines pagination and filtering into a single handler

interface UserRepositoryInterface
{
    public function findUsers(array $filters, int $limit, int $offset, ?string $orderBy = null, ?string $orderDir = null): array;
    public function countUsers(array $filters): int;
}

class MySQLUserRepository implements UserRepositoryInterface
{
    private PDO $db;

    public function __construct(PDO $db)
    {
        $this->db = $db;
    }

    public function findUsers(array $filters, int $limit, int $offset, ?string $orderBy = null, ?string $orderDir = null): array
    {
        $sql = "SELECT * FROM users";
        $whereClauses = [];
        $params = [];

        // Whitelist allowed filter fields and order fields
        $allowedFilterFields = ['status', 'role', 'created_at', 'email'];
        $allowedOrderFields = ['id', 'name', 'created_at'];

        foreach ($filters as $field => $value) {
            if (in_array($field, $allowedFilterFields)) {
                // Basic equality filter for demonstration
                $whereClauses[] = "{$field} = :{$field}";
                $params[":{$field}"] = $value;
            }
        }

        if (!empty($whereClauses)) {
            $sql .= " WHERE " . implode(' AND ', $whereClauses);
        }

        if ($orderBy && in_array($orderBy, $allowedOrderFields)) {
            $orderDir = strtoupper($orderDir) === 'DESC' ? 'DESC' : 'ASC';
            $sql .= " ORDER BY {$orderBy} {$orderDir}";
        } else {
            $sql .= " ORDER BY id ASC"; // Default order
        }

        $sql .= " LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($sql);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function countUsers(array $filters): int
    {
        $sql = "SELECT COUNT(*) FROM users";
        $whereClauses = [];
        $params = [];

        $allowedFilterFields = ['status', 'role', 'created_at', 'email'];

        foreach ($filters as $field => $value) {
            if (in_array($field, $allowedFilterFields)) {
                $whereClauses[] = "{$field} = :{$field}";
                $params[":{$field}"] = $value;
            }
        }

        if (!empty($whereClauses)) {
            $sql .= " WHERE " . implode(' AND ', $whereClauses);
        }

        $stmt = $this->db->prepare($sql);
        foreach ($params as $key => $value) {
            $stmt->bindValue($key, $value);
        }
        $stmt->execute();

        return (int) $stmt->fetchColumn();
    }
}

// --- API Endpoint Controller Logic (Simplified) ---
// Assumes you have a PDO connection $db and a UserRepository $userRepository

// function handleUserListRequest(UserRepositoryInterface $userRepository, array $queryParams): void
// {
//     $page = (int) ($queryParams['page'] ?? 1);
//     $limit = (int) ($queryParams['limit'] ?? 10);
//     $offset = ($page - 1) * $limit;

//     // Basic input validation for limit and page
//     if ($limit <= 0 || $limit > 100) { $limit = 10; }
//     if ($page <= 0) { $page = 1; }

//     // Extract filters (ensure these are whitelisted in the repository)
//     $filters = array_intersect_key($queryParams, array_flip(['status', 'role', 'email']));

//     $orderBy = $queryParams['sort_by'] ?? 'id';
//     $orderDir = $queryParams['sort_dir'] ?? 'asc';

//     $users = $userRepository->findUsers($filters, $limit, $offset, $orderBy, $orderDir);
//     $totalUsers = $userRepository->countUsers($filters);

//     $response = [
//         'data' => $users,
//         'pagination' => [
//             'total' => $totalUsers,
//             'count' => count($users),
//             'per_page' => $limit,
//             'current_page' => $page,
//             'total_pages' => ceil($totalUsers / $limit),
//             'next_page_url' => ($page * $limit < $totalUsers) ? "/api/users?page=" . ($page + 1) . "&limit={$limit}" : null,
//             'prev_page_url' => ($page > 1) ? "/api/users?page=" . ($page - 1) . "&limit={$limit}" : null,
//         ]
//     ];

//     header('Content-Type: application/json');
//     echo json_encode($response);
// }

// Example of how to get query parameters for the handler (simplified)
// $queryParams = $_GET;
// handleUserListRequest(new MySQLUserRepository($db), $queryParams);
?>