<?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 id, sku, shipping_label, lpn, fnsku, sellable, date_received FROM process WHERE fba_shipped='no' AND is_disposed='no'";

// Add search condition
if (!empty($searchValue)) {
    $sqlQuery .= " AND (id LIKE :search OR sku LIKE :search OR shipping_label LIKE :search OR fnsku LIKE :search)";
}

// Add sorting
$columns = ['id','location', 'sku', 'shipping_label', 'lpn', 'fnsku', 'sellable', 'date_received'];
$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 process WHERE fba_shipped='no' AND is_disposed='no'";
$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['id'] ?? '',
           
         
            
            $row['shipping_label'] ?? '',
            $row['lpn'] ?? '',
            $row['fnsku'] ?? '',
           
            $row['sellable'] == '1' ? 'Sellable' : 'Unsellable', // Handle boolean or tinyint
            $row['date_received'] ?? '' // Convert null date to an empty string
        ];
    }, $rows)
];

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