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

$department_id = 5; // Bar department

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

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
FROM products p
-- LEFT JOIN bar_requisition_items to get remaining quantities
LEFT JOIN bar_requisition_items bri 
    ON p.id = bri.product_id
-- INNER JOIN product_stock to ensure only products for department_id = 1
INNER JOIN product_stock ps 
    ON p.id = ps.product_id 
    AND ps.department_id = $department_id
WHERE    p.category_id NOT IN (11,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 Bar 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 = $department_id
  AND p.category_id NOT IN (11,20);
")->fetch_assoc()['cnt'];

// Low stock: products where bar_requisition_items quantity is <= reorder level
$low_stock = $conn->query("
    SELECT COUNT(*) as cnt
    FROM product_stock ps
    JOIN bar_requisition_items bri ON ps.product_id = bri.product_id
    WHERE ps.department_id = $department_id
      AND bri.quantity > 0
      AND bri.quantity <= IFNULL(ps.reorder_level, 5)
")->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 bar_requisition_items bri 
    ON ps.product_id = bri.product_id
JOIN products p 
    ON p.id = ps.product_id
WHERE ps.department_id = $department_id
  AND (bri.quantity IS NULL OR bri.quantity = 0)
  AND p.category_id NOT IN (11,20);
")->fetch_assoc()['cnt'];

// Initialize totals
$total_inventory_value = 0;
$total_sold_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_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>
<h4>🍺 Bar Inventory & Sales Report</h4>
<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_reorder.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>Remaining Qty</th>
    <th>Reorder Level</th>
    <th>Value (MK)</th>
    <th>Sold Qty</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;

    $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='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>$qty</td>
        <td>$reorder_level</td>
        <td>".number_format($value,2)."</td>
        <td>$sold_qty</td>
        <td>$status_icon</td>
        <td>";?>

  
<?php
if($qty <= $reorder_level){
    // Manual quantity input
    $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;'>";
    
    // Single Reorder button
    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 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'; ?>