<?php 
ob_start();
session_start();
include("../inc/config.php");
include("../inc/session.php");

// XSS cleaning function
function xss_clean($data) {
    $data = trim($data);
    $data = str_replace(['&', '<', '>'], ['&amp;', '&lt;', '&gt;'], $data);
    $data = preg_replace('/(&#*\w+)[\x00-\x20]+;/u', '$1;', $data);
    $data = preg_replace('/(&#x*[0-9A-F]+);*/iu', '$1;', $data);
    $data = html_entity_decode($data, ENT_COMPAT, 'UTF-8');
    $data = preg_replace('#(<[^>]+?[\x00-\x20"\'])(?:on|xmlns)[^>]*+>#iu', '$1>', $data);
    $data = preg_replace('#([a-z]*)[\x00-\x20]*=[\x00-\x20]*([`\'"]*)[\x00-\x20]*javascript:#iu', '$1=$2nojavascript...', $data);
    $data = preg_replace('#([a-z]*)[\x00-\x20]*=([\'"]*)[\x00-\x20]*vbscript:#iu', '$1=$2novbscript...', $data);
    $data = preg_replace('#(<[^>]+?)style[\x00-\x20]*=[\x00-\x20]*[`\'"]*.*?expression\([^>]*+>#i', '$1>', $data);
    $data = preg_replace('#</*\w+:\w[^>]*+>#i', '', $data);
    do {
        $old_data = $data;
        $data = preg_replace('#</*(?:applet|b(?:ase|gsound|link)|embed|frame(?:set)?|i(?:frame|layer)|l(?:ayer|ink)|meta|object|s(?:cript|tyle)|title|xml)[^>]*+>#i', '', $data);
    } while ($old_data !== $data);
    return $data;
}

$requestData = $_REQUEST;
error_log(json_encode($requestData));
$from_date = $_POST['start_date'] ?? '2025-04-01';
$to_date = $_POST['end_date'] ?? date('Y-m-d');
$new_client = $_POST['new_client'] ?? 0;
$branch_id = $_POST['branch_id'] ?? 0;
$company_id = $_POST['company_id'] ?? 0;
$client_id = $_POST['client_id'] ?? 0;
$customer_id = $_POST['customer_id'] ?? 0;
$status = $_POST['status'] ?? 0;

$condition = " WHERE STR_TO_DATE(e.docDate, '%d/%m/%Y') BETWEEN :from_date AND :to_date";

if ($branch_id !== 'all' && $branch_id != '') {
    $condition .= " AND pickup_entry.branch_id = :branch_id";
}
if ($company_id !== 'all' && $company_id != '') {
    $condition .= " AND tbl_branch.company_id = :company_id";
}
if ($new_client !== 'all' && $new_client != '') {
    $condition .= " AND pickup_entry.new_client_id = :new_client";
}
if ($client_id !== 'all' && $client_id != '') {
    $condition .= " AND pickup_entry.client_id = :client_id";
}
if ($customer_id !== 'all' && $customer_id != '') {
    $condition .= " AND pickup_entry.customer_id = :customer_id";
}
if ($status !== 'all' && $status != 0 && $status != '') {
    $condition .= " AND pickup_entry.status_code = :status";
}
if (!empty($_SESSION['client_ids']) && $_SESSION['client_ids'] != 0) {
    $condition .= " AND tbl_client_new.id IN ({$_SESSION['client_ids']})";
}
if ($_SESSION['id'] != 1 && !empty($_SESSION['branch_ids']) && $_SESSION['branch_ids'] != '0') {
    $condition .= " AND tbl_branch.id IN ({$_SESSION['branch_ids']})";
}

$columns = [
    0 => 'p.id',
    3 => 'p.cscs_awb',
    4 => 'e.ewbNo',
    7 => 'p.ewbn_expiry',
    9 => 'p.ewayBillDate'
];

$sql = "SELECT e.*, 
               p.invoice_date, 
               p.invoice_no, 
               CASE 
                   WHEN p.invoice_no = e.docNo
                   THEN 'generated' ELSE 'not generated' 
               END AS rhd_ewaybill,
               CASE 
                   WHEN v.pid IS NOT NULL 
                   THEN 1 ELSE 0 
               END AS vehicle_updated 
        FROM ewaybills e
        LEFT JOIN pickup_entry p ON p.invoice_no = e.docNo
        LEFT JOIN vehicle_update_details v ON v.pid = e.ewbNo
        $condition  GROUP BY e.ewbNo";

// Prepare and bind parameters
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':from_date', $from_date);
$stmt->bindValue(':to_date', $to_date);
if (strpos($sql, ':branch_id') !== false) $stmt->bindValue(':branch_id', $branch_id);
if (strpos($sql, ':company_id') !== false) $stmt->bindValue(':company_id', $company_id);
if (strpos($sql, ':new_client') !== false) $stmt->bindValue(':new_client', $new_client);
if (strpos($sql, ':client_id') !== false) $stmt->bindValue(':client_id', $client_id);
if (strpos($sql, ':customer_id') !== false) $stmt->bindValue(':customer_id', $customer_id);
if (strpos($sql, ':status') !== false) $stmt->bindValue(':status', $status);

$stmt->execute();
$totalData = $stmt->rowCount();
$totalFiltered = $totalData;

// Search
if (!empty($requestData['search']['value'])) {
    $search = "%" . $requestData['search']['value'] . "%";
    $sql .= " AND (p.id LIKE :search 
              OR v.pid LIKE :search)";
}

$orderColIndex = $requestData['order'][0]['column'] ?? 0;
$orderDir = $requestData['order'][0]['dir'] ?? 'asc';
$orderColumn = $columns[$orderColIndex] ?? 'p.id';

$sql .= " ORDER BY $orderColumn $orderDir LIMIT :start, :length";

$stmt = $pdo->prepare($sql);
$stmt->bindValue(':from_date', $from_date);
$stmt->bindValue(':to_date', $to_date);
if (!empty($search)) $stmt->bindValue(':search', $search);
$stmt->bindValue(':start', (int)$requestData['start'], PDO::PARAM_INT);
$stmt->bindValue(':length', (int)$requestData['length'], PDO::PARAM_INT);
$stmt->execute();

$query = $stmt->fetchAll(PDO::FETCH_ASSOC);

$data = [];
$i = $requestData['start'] + 1;

// Initialize counters for rhd_ewaybill and vehicle_updated
$rhdGeneratedCount = 0;
$rhdNotGeneratedCount = 0;
$vehicleUpdatedCount = 0;
$vehicleNotUpdatedCount = 0;

foreach ($query as $row) {
    // Count generated rhd_ewaybill and not generated
    if ($row["rhd_ewaybill"] === 'generated') {
        $rhdGeneratedCount++;
    } else {
        $rhdNotGeneratedCount++;
    }

    // Count vehicle_updated (1 and 0)
    if ($row["vehicle_updated"] == 1) {
        $vehicleUpdatedCount++;
    } else {
        $vehicleNotUpdatedCount++;
    }

    $nestedData = [
        $i++,
        xss_clean($row["ewbNo"]),
        xss_clean($row["genGstin"]),
        xss_clean($row["docNo"]),
        xss_clean($row["docDate"]),
        xss_clean($row["ewbDate"]),
        xss_clean($row["validUpto"]),
        xss_clean($row["rhd_ewaybill"]),
        xss_clean($row["vehicle_updated"])
    ];

    $data[] = $nestedData;
}

echo json_encode([
    "draw" => intval($requestData['draw']),
    "recordsTotal" => $totalData,
    "recordsFiltered" => $totalFiltered,
    "data" => $data,
    "counts" => [
        "rhd_generated" => $rhdGeneratedCount,
        "rhd_not_generated" => $rhdNotGeneratedCount,
        "vehicle_updated" => $vehicleUpdatedCount,
        "vehicle_not_updated" => $vehicleNotUpdatedCount
    ]
]);
?>
