<?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 = isset($_POST['order'][0]['column']) ? intval($_POST['order'][0]['column']) : 0;
$orderDirection = isset($_POST['order'][0]['dir']) && in_array(strtoupper($_POST['order'][0]['dir']), ['ASC', 'DESC']) ? $_POST['order'][0]['dir'] : 'ASC';

// Define columns for ordering (ensure the column exists)
$columns = ['id', 'msku', 'shipping_label', 'lpn', 'fnsku', 'abv', 'sellable', 'date_received'];
$orderByColumn = isset($columns[$orderColumn]) ? $columns[$orderColumn] : $columns[0];

// Define the base query
$sqlQuery = "SELECT msku, shipping_label, lpn, fnsku, abv, DATE(date_received) AS date_received, disposal_date 
             FROM process 
             WHERE fba_shipped='no' AND is_disposed='yes'";

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

// Add sorting
$sqlQuery .= " ORDER BY $orderByColumn $orderDirection";

// Add limit and offset for pagination (no binding for LIMIT)
$sqlQuery .= " LIMIT $start, $length";

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

// Get total records count (without filter)
$totalRecordsQuery = "SELECT COUNT(*) FROM process WHERE fba_shipped='no' AND is_disposed='yes'";
$totalRecordsStmt = $conn->prepare($totalRecordsQuery);
$totalRecordsStmt->execute();
$totalRecords = $totalRecordsStmt->fetchColumn();

// Get total filtered records count (with search filter)
$totalFilteredQuery = "SELECT COUNT(*) FROM process WHERE fba_shipped='no' AND is_disposed='yes'";
if (!empty($searchValue)) {
    $totalFilteredQuery .= " AND (id LIKE :search OR msku LIKE :search OR shipping_label LIKE :search OR fnsku LIKE :search OR disposal_date LIKE :search)";
}
$totalFilteredStmt = $conn->prepare($totalFilteredQuery);
if (!empty($searchValue)) {
    $totalFilteredStmt->bindValue(':search', '%' . $searchValue . '%', PDO::PARAM_STR);
}
$totalFilteredStmt->execute();
$totalFiltered = $totalFilteredStmt->fetchColumn();

// Prepare data for DataTables
$response = [
    "draw" => $draw,
    "recordsTotal" => intval($totalRecords),      // Total records without filtering
    "recordsFiltered" => intval($totalFiltered),  // Total records with filtering
    "data" => array_map(function ($row) {
        return [
            $row['date_received'] ?? '',
            $row['msku'] ?? '',
            $row['shipping_label'] ?? '',
            $row['lpn'] ?? '',
            $row['fnsku'] ?? '',
            $row['abv'] ?? '',
            $row['disposal_date'] ?? ''
        ];
    }, $rows)
];

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