<?php
include 'includes/auth.php';
include 'db.php';
include 'includes/header.php';
$is_admin = ($_SESSION['role'] ?? '') === 'admin';

$department_id = 5;


// ================= DELETE RECORD (ADMIN ONLY) =================
if (isset($_GET['delete_id']) && $is_admin) {

    $delete_id = intval($_GET['delete_id']);

    $conn->query("DELETE FROM k_requisition_items WHERE id = $delete_id");

   echo "<script>
    alert('Record deleted successfully!');
    window.location.href = '".$_SERVER['PHP_SELF']."';
</script>";
exit;
    
}

// ================= 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";

// ================= WHERE =================
$where = "ps.department_id = $department_id";

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

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

    r.created_at,
    r.requested_by,
    u.username AS created_by_name

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)

ORDER BY bri.updated_at DESC
";

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

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

<style>
.card-box{
    border-radius:12px;
    padding:15px;
    color:#fff;
    margin-bottom:15px;
}
.bg-blue{background:#0d6efd;}
.bg-green{background:#198754;}
.bg-orange{background:#fd7e14;}
.bg-red{background:#dc3545;}

.table td, .table th{vertical-align:middle;font-size:14px;}
.badge-status{padding:6px 10px;border-radius:20px;}
</style>

<div class="container mt-4">

<h3>📊 Usage History Report</h3>
<p>From <b><?= $from_date ?></b> To <b><?= $to_date ?></b></p>


<!-- EXPORT BUTTONS -->
<div class="mb-3">
       <button class="btn btn-secondary mb-3" onclick="history.back()">
        <i class="fa fa-arrow-left"></i> Back
    </button>
    <a href="export_usage_excel.php?from_date=<?= $from_date ?>&to_date=<?= $to_date ?>&search=<?= $search ?>" class="btn btn-success">
        📤 Export Excel
    </a>

    <a href="export_usage_pdf.php?from_date=<?= $from_date ?>&to_date=<?= $to_date ?>&search=<?= $search ?>" class="btn btn-danger">
        📄 Export PDF
    </a>
    <a href="kitchen_analytics_report.php" class="btn btn-primary mb-3">
    <i class="fa fa-chart-line"></i>Kitchen Analytics Report
</a>
</div>

<!-- SUMMARY CARDS -->
<div class="row mb-3">
    <div class="col-md-4">
        <div class="card-box bg-blue">
            <h5>Total Issued</h5>
            <h3 id="totalIssued">0</h3>
        </div>
    </div>

    <div class="col-md-4">
        <div class="card-box bg-green">
            <h5>Total Used</h5>
            <h3 id="totalUsed">0</h3>
        </div>
    </div>

    <div class="col-md-4">
        <div class="card-box bg-orange">
            <h5>Total Remaining</h5>
            <h3 id="totalRemaining">0</h3>
        </div>
    </div>
</div>

<!-- FILTERS -->
<form method="GET" class="row g-2 mb-3">
    <div class="col-md-3">
        <input type="date" name="from_date" value="<?= $from_date ?>" class="form-control">
    </div>
    <div class="col-md-3">
        <input type="date" name="to_date" value="<?= $to_date ?>" class="form-control">
    </div>
    <div class="col-md-4">
        <input type="text" name="search" value="<?= $search ?>" class="form-control" placeholder="Search product">
    </div>
    <div class="col-md-2">
        <button class="btn btn-primary w-100">Filter</button>
    </div>
</form>

<!-- TABLE -->
<div class="table-responsive">
<table class="table table-bordered table-striped" id="reportTable">
<thead class="bg-dark text-white">
<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>
    <th>Actions</th>
</tr>
</thead>

<tbody>

<?php
$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;

    // status
    if($remaining == 0){
        $status = "<span class='badge bg-danger'>Fully Used</span>";
    } elseif($used > 0){
        $status = "<span class='badge bg-warning text-dark'>Partially Used</span>";
    } else {
        $status = "<span class='badge bg-secondary'>Not Used</span>";
    }

    echo "<tr>
        <td>$i</td> "?>
        <td><?= htmlspecialchars($row['created_by_name'] ?? 'System') ?></td>

        <?php echo"
        <td>{$row['name']}</td>
        <td><b>({$row['id']})</b>{$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> "?>


<td>
<?php if ($is_admin): ?>
    <a href="?delete_id=<?= $row['id'] ?>" 
       onclick="return confirm('Are you sure you want to delete this record?')"
       class="btn btn-sm btn-danger">
     <i class="fa fa-trash"></i>
    </a>
<?php else: ?>
   <span class="text-muted">
    <i class="fa fa-lock"></i>
</span>
<?php endif; ?>
</td>



   <?php echo" </tr>";

    $i++;
}
?>

</tbody>

<tfoot class="bg-light fw-bold">
<tr><td></td>
    <td colspan="3">TOTAL</td>
    <td><?= $total_issued ?></td>
    <td><?= $total_used ?></td>
    <td><?= $total_remaining ?></td>
    <td colspan="3"></td>
</tr>
</tfoot>

</table>
</div>

</div>

<script>
// animate summary cards
document.getElementById("totalIssued").innerText = <?= $total_issued ?>;
document.getElementById("totalUsed").innerText = <?= $total_used ?>;
document.getElementById("totalRemaining").innerText = <?= $total_remaining ?>;
</script>

<?php include 'includes/footer.php'; ?>