<?php
require_once __DIR__ . '/../config.php';
requireLogin();

$startDate = $_GET['start_date'] ?? null;
$endDate = $_GET['end_date'] ?? null;
$supplier = $_GET['supplier'] ?? null;
$status = $_GET['status'] ?? null;

$pdo = getDBConnection();
$sql = "SELECT o.*, g.Quantity_Received, g.GRV_Date,
              CASE WHEN o.PO_DateApproved IS NULL THEN 'Pending'
                   WHEN g.Quantity_Received IS NULL THEN 'Approved'
                   WHEN g.Quantity_Received >= o.Quantity THEN 'Received'
                   ELSE 'Partial' END as Status
       FROM orders o
       LEFT JOIN grv g ON o.Reference = g.Order_No
       WHERE 1=1";
$params = [];

if ($startDate) {
    $sql .= " AND o.PO_Date >= ?";
    $params[] = $startDate;
}
if ($endDate) {
    $sql .= " AND o.PO_Date <= ?";
    $params[] = $endDate;
}
if ($supplier) {
    $sql .= " AND o.Supplier = ?";
    $params[] = $supplier;
}
if ($status) {
    $sql .= " AND CASE WHEN o.PO_DateApproved IS NULL THEN 'Pending'
                   WHEN g.Quantity_Received IS NULL THEN 'Approved'
                   WHEN g.Quantity_Received >= o.Quantity THEN 'Received'
                   ELSE 'Partial' END = ?";
    $params[] = $status;
}

$sql .= " ORDER BY o.PO_Date DESC";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$data = $stmt->fetchAll();

$kpis = [
    'total_orders' => count($data),
    'pending' => array_filter($data, fn($o) => $o['Status'] === 'Pending'),
    'received' => array_filter($data, fn($o) => $o['Status'] === 'Received'),
    'partial' => array_filter($data, fn($o) => $o['Status'] === 'Partial')
];

jsonResponse(['data' => $data, 'kpis' => $kpis]);
?>