<?php
include 'includes/auth.php';
include 'db.php';

$department_id = 5;

// ================= FILTERS =================
$search = $_GET['search'] ?? '';
$from_date = $_GET['from_date'] ?? date('Y-m-01');
$to_date   = $_GET['to_date'] ?? date('Y-m-d');

$date_start = $from_date . " 00:00:00";
$date_end   = $to_date . " 23:59:59";

// ================= QUERY =================
$sql = "
SELECT 
    bri.id,
    p.name,
    p.sku,
    bri.quantity,
    bri.quantity_used,
    bri.used_at,
    bri.updated_at,
    u.username AS created_by_name,
    (bri.quantity - bri.quantity_used) AS remaining

FROM k_requisition_items bri
JOIN k_requisitions r ON r.id = bri.requisition_id
LEFT JOIN users u ON u.id = r.requested_by
JOIN products p ON p.id = bri.product_id
JOIN product_stock ps ON ps.product_id = p.id

WHERE ps.department_id = $department_id
AND (bri.used_at BETWEEN '$date_start' AND '$date_end' OR bri.used_at IS NULL)
";

if($search){
    $search_safe = $conn->real_escape_string($search);
    $sql .= " AND (p.name LIKE '%$search_safe%' OR p.sku LIKE '%$search_safe%')";
}

$sql .= " ORDER BY bri.updated_at DESC";

$result = $conn->query($sql);

// ================= EXPORT EXCEL =================
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=usage_report.xls");

$total_issued = 0;
$total_used = 0;
$total_remaining = 0;

echo "
<table border='1'>
<tr style='background:#000;color:#fff;'>
    <th>#</th>
    <th>Created By</th>
    <th>Product</th>
    <th>SKU</th>
    <th>Issued</th>
    <th>Used</th>
    <th>Remaining</th>
    <th>Status</th>
    <th>Used At</th>
    <th>Updated At</th>
</tr>
";

$i = 1;

while($row = $result->fetch_assoc()){

    $issued = intval($row['quantity']);
    $used = intval($row['quantity_used']);
    $remaining = max($issued - $used, 0);

    $total_issued += $issued;
    $total_used += $used;
    $total_remaining += $remaining;

    if($remaining == 0){
        $status = "Fully Used";
    }elseif($used > 0){
        $status = "Partially Used";
    }else{
        $status = "Not Used";
    }

    echo "
    <tr>
        <td>$i</td>
        <td>".htmlspecialchars($row['created_by_name'] ?? 'System')."</td>
        <td>{$row['name']}</td>
        <td>{$row['sku']}</td>
        <td>$issued</td>
        <td>$used</td>
        <td>$remaining</td>
        <td>$status</td>
        <td>{$row['used_at']}</td>
        <td>{$row['updated_at']}</td>
    </tr>
    ";

    $i++;
}

echo "
<tr style='font-weight:bold;background:#f2f2f2;'>
    <td colspan='4'>TOTAL</td>
    <td>$total_issued</td>
    <td>$total_used</td>
    <td>$total_remaining</td>
    <td colspan='3'></td>
</tr>
";

echo "</table>";
?>