<?php
require_once '../../db/Config.php';
$db = new Config();
$conn = $db->getConnection();

// Required for DataTables to work with server-side processing
$draw = isset($_POST['draw']) ? intval($_POST['draw']) : 1;
$start = isset($_POST['start']) ? intval($_POST['start']) : 0;
$length = isset($_POST['length']) ? intval($_POST['length']) : 10;

// Get search filter
$searchValue = isset($_POST['search']['value']) ? $_POST['search']['value'] : '';

// Get sorting
$orderColumn = $_POST['order'][0]['column'];
$orderDirection = $_POST['order'][0]['dir'];

// Define the base query
$sqlQuery = "SELECT * FROM reimbursement WHERE status=1";

// Add search condition
if (!empty($searchValue)) {
    $sqlQuery .= " AND (fnsku LIKE :search OR lpn LIKE :search OR tracking LIKE :search OR case_id LIKE :search)";
}

// Add sorting
$columns = ['fnsku', 'lpn', 'tracking', 'notes', 'case_id', 'complete_date', 'reimburse_amount', 'reimbursement_id'];
$sqlQuery .= " ORDER BY " . $columns[$orderColumn] . " " . $orderDirection;

// Add limit and offset for pagination
$sqlQuery .= " LIMIT :start, :length";

// Prepare and execute the query
$stmt = $conn->prepare($sqlQuery);
if (!empty($searchValue)) {
    $stmt->bindValue(':search', '%' . $searchValue . '%', PDO::PARAM_STR);
}
$stmt->bindValue(':start', $start, PDO::PARAM_INT);
$stmt->bindValue(':length', $length, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Get total records count
$totalRecordsQuery = "SELECT COUNT(*) AS count FROM reimbursement WHERE status=1";
$totalRecordsStmt = $conn->prepare($totalRecordsQuery);
$totalRecordsStmt->execute();
$totalRecords = $totalRecordsStmt->fetchColumn();

// Prepare data for DataTables
$response = [
    "draw" => $draw,
    "recordsTotal" => $totalRecords,
    "recordsFiltered" => $totalRecords,
    "data" => array_map(function ($row) {
        return [
            $row['fnsku'] ?? '', // If 'location' is null, return an empty string
            $row['lpn'] ?? '',
            $row['tracking'] ?? '',
            $row['notes'] ?? '',
            $row['case_id'] ?? '' // Assuming 'abv' is the nickname
            
        ];
    }, $rows)
];

// Return the response as JSON
header('Content-Type: application/json');
echo json_encode($response);
