<?php
// Assuming you have the database connection established already
require("../mainconfig.php");

// Function to calculate the total number of pages based on the number of records per page
function getTotalPages($totalRecords, $recordsPerPage) {
    return ceil($totalRecords / $recordsPerPage);
}

// Function to search for services based on the search query and category
function searchServices($searchQuery, $category, $offset, $recordsPerPage, &$totalRecords) {
    global $db;

    // Add SQL injection protection
    $searchQuery = mysqli_real_escape_string($db, $searchQuery);
    $category = mysqli_real_escape_string($db, $category);

    $categoryFilter = "";
    if (!empty($category)) {
        $categoryFilter = " AND category = '$category'";
    }

    $query = "SELECT * FROM services WHERE service LIKE '%$searchQuery%' $categoryFilter ORDER BY id DESC LIMIT $recordsPerPage OFFSET $offset";
    $result = mysqli_query($db, $query);

    $data = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $data[] = array(
            "sid" => $row['sid'],
            "category" => $row['category'],
            "service" => $row['service'],
            "price" => $row['price'],
            "note" => $row['note'],
            "average" => $row['average'],
            "min" => $row['min'],
            "max" => $row['max'],
            "status" => $row['status'],
        );
    }

    // Get the total number of records that match the search query and category filter
    $totalRecordsQuery = "SELECT COUNT(*) AS total FROM services WHERE service LIKE '%$searchQuery%' $categoryFilter";
    $totalRecordsResult = mysqli_query($db, $totalRecordsQuery);
    $totalRecordsRow = mysqli_fetch_assoc($totalRecordsResult);
    $totalRecords = $totalRecordsRow['total'];

    return $data;
}

$recordsPerPage = isset($_GET['recordsPerPage']) ? intval($_GET['recordsPerPage']) : 10;
$page = isset($_GET['page']) ? intval($_GET['page']) : 1;
$searchQuery = isset($_GET['search']) ? $_GET['search'] : '';
$category = isset($_GET['category']) ? $_GET['category'] : '';

// Validate the search query to prevent SQL injection
$searchQuery = preg_replace("/[^\w\s]/", "", $searchQuery);

// Calculate the OFFSET for the current page
$offset = ($page - 1) * $recordsPerPage;

if (!empty($searchQuery) || !empty($category)) {
    // Perform search and get data for the current page
    $data = searchServices($searchQuery, $category, $offset, $recordsPerPage, $totalRecords);
} else {
    // Fetch data from the 'services' table for the current page
    $query = "SELECT * FROM services ORDER BY id LIMIT ? OFFSET ?";
    $stmt = mysqli_prepare($db, $query);
    mysqli_stmt_bind_param($stmt, "ii", $recordsPerPage, $offset);
    mysqli_stmt_execute($stmt);

    $result = mysqli_stmt_get_result($stmt);

    $data = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $data[] = array(
            "sid" => $row['sid'],
            "category" => $row['category'],
            "service" => $row['service'],
            "price" => $row['price'],
            "average" => $row['average'],
            "min" => $row['min'],
            "max" => $row['max']
        );
    }

    // Get the total number of records in the 'services' table
    $totalRecordsQuery = "SELECT COUNT(*) AS total FROM services";
    $totalRecordsResult = mysqli_query($db, $totalRecordsQuery);
    $totalRecordsRow = mysqli_fetch_assoc($totalRecordsResult);
    $totalRecords = $totalRecordsRow['total'];
}

// Calculate total pages
$totalPages = getTotalPages($totalRecords, $recordsPerPage);

// Return data as JSON
echo json_encode(array(
    "services" => $data,
    "totalPages" => $totalPages
));
?>
