<?php
require_once '../../db/Config.php';

$db = new Config();
$conn = $db->getConnection();

// Pagination parameters
$start = $_POST['start'];
$length = $_POST['length'];
$draw = $_POST['draw'];
$searchValue = $_POST['search']['value'];
$columnIndex = $_POST['order'][0]['column']; // Index of the column to sort by
$sortDirection = $_POST['order'][0]['dir']; // ASC or DESC
$currentMonth = isset($_POST['currentmonth']) ? $_POST['currentmonth'] : date('Y-m'); // Get current month from POST data
error_log("Current Month in fetch_data.php: " . $currentMonth);
error_log($_POST['currentmonth']);

// Map the columns for sorting
$columns = [
  0 => 'fnsku',
  1 => 'total', // Sellable Quantity
  2 => 'total_received', // Total Received
  3 => 'sellable_percent' // This is calculated, so we need to sort by derived value
];

// Base SQL query
$sqlQuery = "SELECT fnsku, 
                SUM(CASE WHEN sellable = '1' THEN 1 ELSE 0 END) as total, 
                SUM(CASE WHEN sellable = '2' THEN 1 ELSE 0 END) as total_notsell,
                (SUM(CASE WHEN sellable = '1' THEN 1 ELSE 0 END) / (SUM(CASE WHEN sellable IN ('1', '2') THEN 1 ELSE 0 END))) * 100 AS sellable_percent
            FROM process 
            WHERE fba_shipped = 'no' AND is_disposed = 'no' AND DATE_FORMAT(`date_received`, '%Y-%m') = :currentmonth"; 

// Apply search condition if provided
if (!empty($searchValue)) {
    $sqlQuery .= " AND fnsku LIKE :searchValue";
}

// Handle Sorting
$orderColumn = $columns[$columnIndex];
$sqlQuery .= " GROUP BY fnsku ORDER BY " . $orderColumn . " " . strtoupper($sortDirection);

// Apply pagination
$sqlQuery .= " LIMIT :start, :length";

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

$data = [];

// Fetch the results and process the data
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $countsellable = $row['total'];
    $total_notsellable = $row['total_notsell'];
    $total_received = $countsellable + $total_notsellable;
    $sellable_percent = $total_received > 0 ? ($countsellable / $total_received) * 100 : 0;

    // Push the row data to the array
    $data[] = [
        'fnsku' => $row['fnsku'],
        'total' => $countsellable,
        'total_received' => number_format($total_received),
        'sellable_percent' => number_format($sellable_percent, 2) . '%'
    ];
}

// Calculate total records in the database
$totalRecordsStmt = $conn->query("SELECT COUNT(DISTINCT fnsku) AS total FROM process WHERE fba_shipped='no' AND is_disposed='no' and date_received like '%$currentMonth%'");
$totalRecords = $totalRecordsStmt->fetchColumn();

// Calculate total filtered records
$totalFilteredRecordsStmt = $conn->query("SELECT COUNT(DISTINCT fnsku) AS total FROM process WHERE fba_shipped='no' AND is_disposed='no' and date_received like '%$currentMonth%'");
if (!empty($searchValue)) {
    $totalFilteredRecordsStmt = $conn->query("SELECT COUNT(DISTINCT fnsku) AS total FROM process WHERE fba_shipped='no' AND is_disposed='no' and date_received like '%$currentMonth%' AND fnsku LIKE '%" . $searchValue . "%'");
}
$totalFilteredRecords = $totalFilteredRecordsStmt->fetchColumn();

// Return the response in JSON format
$response = [
    "draw" => intval($draw),
    "recordsTotal" => intval($totalRecords),
    "recordsFiltered" => intval($totalFilteredRecords),
    "data" => $data
];

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