<!DOCTYPE html>
<?php
include("layout.php");
include("db.php");

// Stock summary with accurate quantity aggregation using subqueries
$query = "
    SELECT 
        i.name AS item_name,
        COALESCE(c.name, 'N/A') AS category,
        COALESCE(pur.total_purchased, 0) AS purchased_qty,
        COALESCE(sal.total_sold, 0) AS sold_qty,
        COALESCE(pur.total_purchased, 0) - COALESCE(sal.total_sold, 0) AS balance_qty
    FROM items i
    LEFT JOIN categories c ON i.category_id = c.id
    LEFT JOIN (
        SELECT 
            item_id,
            SUM(quantity) AS total_purchased
        FROM purchase_items
        GROUP BY item_id
    ) pur ON i.id = pur.item_id
    LEFT JOIN (
        SELECT 
            item_id,
            SUM(quantity) AS total_sold
        FROM sale_items
        GROUP BY item_id
    ) sal ON i.id = sal.item_id
    ORDER BY i.name ASC
";

// Run the query
$result = mysqli_query($conn, $query);
if (!$result) {
    die("Query failed: " . mysqli_error($conn));
}
?>

<div class="page-wrapper">
    <div class="content">
        <div class="page-header">
            <div class="add-item d-flex">
                <div class="page-title">
                    <h4>Stock Balance Report</h4>
                    <h6>View current stock levels by item</h6>
                </div>
            </div>
        </div>

        <!-- Stock Report Table -->
        <div class="card table-list-card">
            <div class="card-body">
                <div class="table-responsive">
                    <table class="table datanew" id="stockReportTable">
                        <thead>
                            <tr>
                                <th>Item Name</th>
                                <th>Category</th>
                                <th>Purchased Qty</th>
                                <th>Sold Qty</th>
                                <th>Balance Qty</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php while ($row = mysqli_fetch_assoc($result)) : ?>
                                <tr>
                                    <td><?= htmlspecialchars($row['item_name']) ?></td>
                                    <td><?= htmlspecialchars($row['category']) ?></td>
                                    <td><?= $row['purchased_qty'] ?></td>
                                    <td><?= $row['sold_qty'] ?></td>
                                    <td><?= $row['balance_qty'] ?></td>
                                </tr>
                            <?php endwhile; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- DataTables JS -->
<script>
    $(document).ready(function () {
        if (!$.fn.DataTable.isDataTable('#stockReportTable')) {
            $('#stockReportTable').DataTable({
                stateSave: true
            });
        }
    });
</script>
