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

require 'dompdf/vendor/autoload.php';

use Dompdf\Dompdf;
use Dompdf\Options;

$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);

// ================= TOTALS =================
$total_issued = 0;
$total_used = 0;
$total_remaining = 0;

// ================= HTML =================
$html = '
<html>
<head>
<style>
body{
    font-family: Arial, sans-serif;
    font-size:12px;
}

h2{
    text-align:center;
    margin-bottom:5px;
}

.report-date{
    text-align:center;
    margin-bottom:15px;
    font-size:13px;
}

table{
    width:100%;
    border-collapse:collapse;
}

table th{
    background:#000;
    color:#fff;
    padding:8px;
    border:1px solid #ccc;
    font-size:11px;
}

table td{
    border:1px solid #ccc;
    padding:6px;
    font-size:10px;
}

.total-row{
    font-weight:bold;
    background:#f2f2f2;
}

.text-center{
    text-align:center;
}
</style>
</head>
<body>

<h2>Usage History Report</h2>

<div class="report-date">
From: <b>'.$from_date.'</b> 
&nbsp;&nbsp;&nbsp; 
To: <b>'.$to_date.'</b>
</div>

<table>
<thead>
<tr>
    <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</th>
</tr>
</thead>
<tbody>
';

$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 = "Partial";
    }else{
        $status = "Not Used";
    }

    $html .= '
    <tr>
        <td class="text-center">'.$i.'</td>
        <td>'.htmlspecialchars($row['created_by_name'] ?? 'System').'</td>
        <td>'.htmlspecialchars($row['name']).'</td>
        <td>'.htmlspecialchars($row['sku']).'</td>
        <td class="text-center">'.$issued.'</td>
        <td class="text-center">'.$used.'</td>
        <td class="text-center">'.$remaining.'</td>
        <td>'.$status.'</td>
        <td>'.substr($row['used_at'],0,16).'</td>
        <td>'.substr($row['updated_at'],0,16).'</td>
    </tr>
    ';

    $i++;
}

$html .= '
<tr class="total-row">
    <td colspan="4" class="text-center">TOTAL</td>
    <td class="text-center">'.$total_issued.'</td>
    <td class="text-center">'.$total_used.'</td>
    <td class="text-center">'.$total_remaining.'</td>
    <td colspan="3"></td>
</tr>
';

$html .= '
</tbody>
</table>

</body>
</html>
';

// ================= DOMPDF =================
$options = new Options();
$options->set('isRemoteEnabled', true);

$dompdf = new Dompdf($options);

$dompdf->loadHtml($html);

// A4 Landscape
$dompdf->setPaper('A4', 'landscape');

$dompdf->render();

// DOWNLOAD PDF
$dompdf->stream("usage_report.pdf", [
    "Attachment" => true
]);

exit;
?>