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

$department_id = 5; // Housekeeping department (example ID, adjust as needed)

// ==================== Filters ====================
$search = $_GET['search'] ?? '';
$status_filter = $_GET['status'] ?? '';
$from_date = $_GET['from_date'] ?? date('Y-m-d'); 
$to_date = $_GET['to_date'] ?? date('Y-m-d');

$where_inventory = "ps.department_id = $department_id"; // assuming you have product_stock table with department_id

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

if ($status_filter == 'low') 
    $where_inventory .= " AND ps.quantity <= IFNULL(ps.reorder_level,5) AND ps.quantity > 0";
elseif ($status_filter == 'out') 
    $where_inventory .= " AND ps.quantity = 0";

// ==================== Inventory Query ====================
$sql_inventory = "
SELECT 
    p.id,
    p.category_id,
    p.name,
    p.sku,
    p.selling_price,
    ps.product_id,
    IFNULL(ps.reorder_level,5) as reorder_level,
    p.vat_rate,

    /* FIX: bring issued + used */
    IFNULL(SUM(hki.quantity),0) AS quantity,
    IFNULL(SUM(hki.quantity_used),0) AS quantity_used,

    IFNULL(SUM(hki.quantity),0) - IFNULL(SUM(hki.quantity_used),0) AS remaining_qty

FROM products p
LEFT JOIN hk_requisition_items hki 
    ON p.id = hki.product_id
INNER JOIN product_stock ps 
    ON p.id = ps.product_id 
    AND ps.department_id = $department_id
WHERE p.category_id NOT IN (14,13,16,17,18,19)
GROUP BY p.id
ORDER BY p.name ASC
";

$inventory = $conn->query($sql_inventory);

// ==================== Usage / Issued Query ====================
$date_start = $conn->real_escape_string($from_date . ' 00:00:00');
$date_end = $conn->real_escape_string($to_date . ' 23:59:59');

$sql_usage = "
SELECT hki.product_id, 
       SUM(hki.quantity) as issued_qty,
       GROUP_CONCAT(DISTINCT hr.id SEPARATOR ', ') as requisitions,
       SUM(CASE WHEN hki.status = 'used' THEN hki.quantity ELSE 0 END) as once_used,
       GROUP_CONCAT(DISTINCT hr.status SEPARATOR ', ') as requisition_status
FROM hk_requisition_items hki
JOIN hk_requisitions hr ON hki.requisition_id = hr.id
WHERE hr.created_at BETWEEN '$date_start' AND '$date_end'
GROUP BY hki.product_id
";

$usage_result = $conn->query($sql_usage);

$usage_map = [];
while($row = $usage_result->fetch_assoc()){
    $usage_map[$row['product_id']] = $row;
}

// ==================== Summary Cards ====================
$total_products = $conn->query("
   SELECT COUNT(DISTINCT ps.product_id) AS cnt
FROM product_stock ps
JOIN products p 
    ON p.id = ps.product_id
WHERE ps.department_id = $department_id
  AND p.category_id NOT IN (11,14,13,16,17,18,19);
")->fetch_assoc()['cnt'];

// Low stock: products where remaining_qty <= reorder_level
$low_stock = $conn->query("
  SELECT COUNT(*) AS cnt
FROM product_stock ps
LEFT JOIN hk_requisition_items hki 
    ON ps.product_id = hki.product_id
JOIN products p 
    ON p.id = ps.product_id
WHERE ps.department_id = $department_id
  AND IFNULL(hki.quantity,0) > 0
  AND IFNULL(hki.quantity,0) <= IFNULL(ps.reorder_level,5)
  AND p.category_id NOT IN (11,14,13,16,17,18,19);
")->fetch_assoc()['cnt'];

// Out of stock: products where remaining_qty = 0
$out_stock = $conn->query("
  SELECT COUNT(*) AS cnt
FROM product_stock ps
LEFT JOIN hk_requisition_items hki 
    ON ps.product_id = hki.product_id
JOIN products p 
    ON p.id = ps.product_id
WHERE ps.department_id = $department_id
  AND (hki.quantity IS NULL OR hki.quantity = 0)
  AND p.category_id NOT IN (11,14,13,16,17,18,19);
")->fetch_assoc()['cnt'];

// Initialize totals
$total_inventory_value = 0;
$total_issued_value = 0;
?>

<div class="container mt-4">
       <button class="btn btn-secondary mb-3" onclick="history.back()">
        <i class="fa fa-arrow-left"></i> Back
    </button>
    <a href="export_inventory_excelp.php?search=<?= $search ?>&status=<?= $status_filter ?>&from_date=<?= $from_date ?>&to_date=<?= $to_date ?>" class="btn btn-success mb-3">Export to Excel</a>
    <a href="export_inventory_pdfp.php?search=<?= $search ?>&status=<?= $status_filter ?>" class="btn btn-danger mb-3">Export to PDF</a>
<a href="housekeeping_report.php" class="btn btn-primary mb-3">
    <i class="fa fa-chart-line"></i> Housekeeping Analytics Report
</a>
<h4>🧹 Housekeeping Inventory & Usage Report</h4>
<p>From: <strong><?= htmlspecialchars($from_date) ?></strong> 
To: <strong><?= htmlspecialchars($to_date) ?></strong></p>

<!-- Filter Form -->
<form method="GET" class="row g-2 mb-3" id="filterForm">
    <div class="col-md-2"><input type="date" name="from_date" value="<?= htmlspecialchars($from_date) ?>" class="form-control"></div>
    <div class="col-md-2"><input type="date" name="to_date" value="<?= htmlspecialchars($to_date) ?>" class="form-control"></div>
    <div class="col-md-3"><input type="text" id="liveSearch" name="search" placeholder="Search Product/Item" value="<?= htmlspecialchars($search) ?>" class="form-control"></div>
    <div class="col-md-2">
        <select name="status" class="form-control">
            <option value="">All Stock</option>
            <option value="low" <?= $status_filter=='low'?'selected':'' ?>>Low Stock</option>
            <option value="out" <?= $status_filter=='out'?'selected':'' ?>>Out of Stock</option>
        </select>
    </div>
    <div class="col-md-3"><button class="btn btn-primary w-100">Apply Filters</button></div>
</form>

<!-- Summary Cards -->
<div class="row mb-3">
    <div class="col-md-3"><div class="card text-white bg-primary"><div class="card-body"><h5>Total Products</h5><h4><?= $total_products ?></h4></div></div></div>
    <div class="col-md-3"><div class="card text-white bg-warning"><div class="card-body"><h4>Low Stock</h4><h4><?= $low_stock ?></h4></div></div></div>
    <div class="col-md-3"><div class="card text-white bg-danger"><div class="card-body"><h5>Out of Stock</h5><h3><?= $out_stock ?></h3></div></div></div>
</div>

<!-- Inventory Table -->
<form method="POST" action="bulk_reorderh.php">
<style>
    table.table { font-size:0.85rem; }
    table.table th, table.table td { vertical-align: middle; }
</style>
<center><b><font color="red">Please tick the box at the left side before reorder on the right side</font></b></center>

<table class="table table-bordered table-striped" id="inventoryTable">
<thead class="bg-dark text-white">
<tr>
    <th><input type="checkbox" onclick="toggleAll(this)"></th>
    <th>#</th>
    <th>Pid</th>
    <th>Product</th>
    <th>Price (MK)</th>
 
    <th>Stock Breakdown</th>
    <th>Used Status</th>
    <th>Reorder Level</th>
    <th>Value (MK)</th>
   
    <th>Status</th>
    <th>Reorder</th>
</tr>
</thead>

<tbody>
<?php
$i=1;
while($row = $inventory->fetch_assoc()) {

    $product_id = $row['id'];
    $qty = intval($row['remaining_qty']); // use requisition quantity instead of stock
    $reorder_level = intval($row['reorder_level']);
    $value = $qty * $row['selling_price'];
    $total_inventory_value += $value;

    $issued_qty = $usage_map[$product_id]['issued_qty'] ?? 0;
    $once_used = $usage_map[$product_id]['once_used'] ?? 0;
    $issued_value = $issued_qty * $row['selling_price'];
    $total_issued_value += $issued_value;

    if($qty == 0){
        $status_icon = "<span class='text-danger fw-bold'>Out</span>";
        $row_class = "table-danger";
    } elseif($qty <= $reorder_level){
        $status_icon = "<span class='text-warning fw-bold'>Reorder</span>";
        $row_class = "table-warning";
    } else{
        $status_icon = "<span class='text-success'>OK</span>";
        $row_class = "";
    }

    echo "<tr class='$row_class'>
        <td>";
    if($qty <= $reorder_level){
        echo "<input type='checkbox' name='products[]' value='$product_id'>";
    } else {
        echo "-";
    }
    echo "</td>
        <td>$i</td>
        <td>{$row['product_id']}</td>
        <td>{$row['name']}</td>
        <td>".number_format($row['selling_price'],2)."</td>"?>
        <td style="min-width:180px;">

<?php
    $issued = intval($row['quantity']);   // from table row
    $used = intval($row['quantity_used']); // from table row

    $remaining = $issued - $used;

    if($remaining < 0) $remaining = 0;
    if($issued <= 0) $issued = 1;

    $used_percent = ($used / $issued) * 100;
    $remain_percent = ($remaining / $issued) * 100;
?>

<!-- Progress Bar -->
<div style="background:#e9ecef; border-radius:12px; overflow:hidden; height:20px; display:flex;">

    <div style="width:<?= $used_percent ?>%; background:#198754; height:20px;"></div>
    <div style="width:<?= $remain_percent ?>%; background:#ffc107; height:20px;"></div>

</div>

<!-- Labels -->
<small style="display:flex; justify-content:space-between; margin-top:4px;">
    <span style="color:#198754; font-weight:bold;">Used: <?= $used ?></span>
    <span style="color:#dc3545;"><b>Issued: <?= $issued ?></b></span>
    <span style="color:#000000; font-weight:bold;">Remain: <?= $remaining ?></span>
</small>

</td>


<td>
    <?php if($once_used > 0): ?>
        <span class="badge bg-success">
            Used: <?= $once_used ?>
        </span>
        <a href="update_used.php?product_id=<?= $product_id ?>" 
           class="btn btn-sm btn-primary mt-1">
           Update
        </a>
    <?php else: ?>
        <span class="badge bg-secondary">Not Used</span>
        <a href="update_used.php?product_id=<?= $product_id ?>" 
           class="btn btn-sm btn-warning mt-1">
           Mark Used
        </a>
    <?php endif; ?>
</td>


        <?php echo"
        <td>$reorder_level</td>
        <td>".number_format($value,2)."</td>
       
        <td>$status_icon</td>
        <td>";?>

   <?php
if($qty <= $reorder_level){
    // Reorder input + button
    $default_qty = ($reorder_level - $qty > 0) ? ($reorder_level - $qty) : 1;
    echo "<input type='number' min='1' name='quantities[{$product_id}]' value='{$default_qty}' class='form-control form-control-sm' style='width:80px; display:inline-block; margin-right:5px;'>";
    echo "<button type='submit' name='single_product' value='{$product_id}' class='btn btn-sm btn-danger mt-1'>Reorder</button>";
    
    // Purchase Request button
    echo " ";
} else {
    echo "-";
}
?>
<?php 
    echo "</td></tr>";

    $i++;
}
?>
</tbody>

<tfoot class="bg-light fw-bold">
<tr>
    <td colspan="6" class="text-end">Total Inventory Value:</td>
    <td colspan="2">MK <?= number_format($total_inventory_value,2) ?></td>
    <td class="text-end">Total Issued Value:</td>
    <td colspan="2">MK <?= number_format($total_issued_value,2) ?></td>
</tr>
</tfoot>
</table>

<button type="submit" class="btn btn-danger mb-3">Reorder Selected Items</button>
</form>

<button class="btn btn-secondary" onclick="window.print()">Print Report</button>

</div>

<script>
function toggleAll(source){
    let checkboxes = document.getElementsByName('products[]');
    for(let i=0;i<checkboxes.length;i++){
        checkboxes[i].checked = source.checked;
    }
}

// Live Search for Inventory Table
document.getElementById('liveSearch').addEventListener('keyup', function() {
    let filter = this.value.toLowerCase();
    let table = document.getElementById('inventoryTable');
    let trs = table.getElementsByTagName('tbody')[0].getElementsByTagName('tr');

    for (let i = 0; i < trs.length; i++) {
        let tds = trs[i].getElementsByTagName('td');
        let show = false;

        for (let j = 0; j < tds.length; j++) {
            if(tds[j].textContent.toLowerCase().indexOf(filter) > -1){
                show = true;
                break;
            }
        }

        trs[i].style.display = show ? '' : 'none';
    }
});
</script>

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