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

$department_ids = [2, 5];
$dept_list = implode(',', $department_ids);

// ==================== 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 = $where_inventory = "ps.department_id IN ($dept_list)";

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,
    IFNULL(SUM(bri.quantity),0) AS remaining_qty,
      IFNULL(SUM(bri.quantity),0) AS quantity,
       IFNULL(SUM(bri.quantity_used),0) AS quantity_used
FROM products p
LEFT JOIN k_requisition_items bri 
    ON p.id = bri.product_id
INNER JOIN product_stock ps 
    ON p.id = ps.product_id 
    AND ps.department_id IN ($dept_list)
WHERE p.category_id NOT IN (13,15,16,17,18,19,20)
GROUP BY p.id
ORDER BY p.name ASC;
";


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

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

$sql_sales = "
SELECT si.product_id, 
       SUM(si.qty) as sold_qty, 
       SUM(si.qty*si.price) as sold_total,
       GROUP_CONCAT(DISTINCT s.created_by SEPARATOR ', ') as sold_by,
       GROUP_CONCAT(DISTINCT s.payment_method SEPARATOR ', ') as payment_methods
FROM sale_items si
JOIN sales s ON si.sale_id = s.id
WHERE s.sale_date BETWEEN '$date_start' AND '$date_end'
GROUP BY si.product_id
";

$sales_result = $conn->query($sql_sales);

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

// ==================== Summary Cards ====================
// ==================== Summary Cards ====================

// Total products in Kitchen department (only those that exist in product_stock for department 1)
$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 IN ($dept_list)
  AND p.category_id NOT IN (13,15,16,17,18,19,20);
")->fetch_assoc()['cnt'];

// Low stock: products where kithen_requisition_items quantity is <= reorder level
$low_stock = $conn->query("
  SELECT COUNT(*) AS cnt
FROM product_stock ps
JOIN k_requisition_items bri 
    ON ps.product_id = bri.product_id
JOIN products p 
    ON p.id = ps.product_id
WHERE ps.department_id IN ($dept_list)
  AND bri.quantity > 0
  AND bri.quantity <= IFNULL(ps.reorder_level, 5)
  AND p.category_id NOT IN (13,15,16,17,18,19,20);
")->fetch_assoc()['cnt'];

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

// Initialize totals
$total_inventory_value = 0;
$total_sold_value = 0;
?>
<head>
<style>
body {
    background: #f4f6f9;
}

/* Page header */
.page-title {
    background: linear-gradient(135deg, #0d6efd, #6610f2);
    color: white;
    padding: 15px;
    border-radius: 10px;
    margin-bottom: 15px;
    box-shadow: 0 4px 12px rgba(0,0,0,0.1);
}

/* Cards enhancement */
.card {
    border: none;
    border-radius: 12px;
    box-shadow: 0 4px 12px rgba(0,0,0,0.08);
    transition: 0.3s;
}
.card:hover {
    transform: translateY(-3px);
}

/* Table improvements */
table.table {
    border-radius: 12px;
    overflow: hidden;
    background: white;
    box-shadow: 0 4px 12px rgba(0,0,0,0.08);
}

table.table thead {
    position: sticky;
    top: 0;
    z-index: 10;
}

table.table tbody tr:hover {
    background: #f1f7ff;
}

/* Buttons */
.btn {
    border-radius: 8px;
    font-weight: 500;
}

/* Status badges */
.badge-status {
    padding: 5px 10px;
    border-radius: 20px;
    font-size: 12px;
}

/* Floating summary bar */
.summary-bar {
    position: sticky;
    top: 0;
    z-index: 1000;
    background: white;
    padding: 10px;
    border-radius: 10px;
    box-shadow: 0 2px 10px rgba(0,0,0,0.08);
    margin-bottom: 10px;
}

/* Loader overlay (future use) */
.loader {
    display: none;
    position: fixed;
    top: 0; left: 0;
    width: 100%;
    height: 100%;
    background: rgba(255,255,255,0.7);
    z-index: 9999;
}
</style>
</head>
<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_excel.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_pdf.php?search=<?= $search ?>&status=<?= $status_filter ?>" class="btn btn-danger mb-3">Export to PDF</a>
<div class="page-title">
    <h5>🍽️ Kitchen Inventory & Sales Dashboard</h5>

</div>
<p>From: <strong><?= htmlspecialchars($from_date) ?></strong> 
To: <strong><?= htmlspecialchars($to_date) ?></strong></p>

<!-- Filter Form -->
<!-- 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><h3><?= $total_products ?></h3></div></div></div>
    <div class="col-md-3"><div class="card text-white bg-warning"><div class="card-body"><h5>Low Stock</h5><h3><?= $low_stock ?></h3></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_reorderk.php">
<style>
    table.table {
        font-size: 0.85rem; /* smaller font */
    }
    table.table th, table.table td {
        vertical-align: middle; /* better alignment */
    }
</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'];
$issued = intval($row['quantity']);
$used = intval($row['quantity_used']);

$qty = $issued - $used;

if($qty < 0){
    $qty = 0;
}// use requisition quantity instead of stock
    $reorder_level = intval($row['reorder_level']);
    $once_used = intval($row['quantity_used'] ?? 0);
    $value = $qty * $row['selling_price'];
    $total_inventory_value += $value;

    $sold_qty = $sales_map[$product_id]['sold_qty'] ?? 0;
    $sold_value = $sales_map[$product_id]['sold_total'] ?? 0;
    $total_sold_value += $sold_value;

    if($qty == 0){
        $status_icon = "<span class='badge bg-danger'>OUT OF STOCK</span>";
        $row_class = "table-danger";
    } elseif($qty <= $reorder_level){
        $status_icon = "<span class='badge bg-warning text-dark'>LOW STOCK</span>";
        $row_class = "table-warning";
    } else{
    $status_icon = "";
    $row_class = "";
}

    echo "<tr class='$row_class'>
   

<td><input type='checkbox' name='products[]' value='$product_id'></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($used > 0): ?>
        <span class="badge bg-success">
            Used: <?= $once_used ?>
        </span>
        <a href="update_k_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_k_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
$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>";
?>

<?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 Sold Value:</td>
    <td colspan="2">MK <?= number_format($total_sold_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;
    }
}
</script>
<script>
// 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'; ?>