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

// Improved stock summary using inventory_movements for accuracy
$query = "
    SELECT 
        i.name AS item_name,
        COALESCE(c.name, 'N/A') AS category,
        COALESCE(SUM(CASE WHEN im.movement_type = 'in' THEN im.quantity ELSE 0 END), 0) AS total_in,
        COALESCE(SUM(CASE WHEN im.movement_type = 'out' THEN im.quantity ELSE 0 END), 0) AS total_out,
        COALESCE(SUM(CASE WHEN im.movement_type = 'adjustment' THEN im.quantity ELSE 0 END), 0) AS total_adj,
        COALESCE(SUM(CASE WHEN im.movement_type = 'in' THEN im.quantity ELSE 0 END), 0) -
        COALESCE(SUM(CASE WHEN im.movement_type = 'out' THEN im.quantity ELSE 0 END), 0) +
        COALESCE(SUM(CASE WHEN im.movement_type = 'adjustment' THEN im.quantity ELSE 0 END), 0) AS balance_qty
    FROM items i
    LEFT JOIN categories c ON i.category_id = c.id
    LEFT JOIN inventory_movements im ON i.id = im.item_id
    GROUP BY i.id
    ORDER BY i.name ASC
";

$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>Total In</th>
                                <th>Total Out</th>
                                <th>Adjustments</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><?= number_format($row['total_in'], 2) ?></td>
                                    <td><?= number_format($row['total_out'], 2) ?></td>
                                    <td><?= number_format($row['total_adj'], 2) ?></td>
                                    <td class="fw-bold <?= $row['balance_qty'] <= 0 ? 'text-danger' : '' ?>">
                                        <?= number_format($row['balance_qty'], 2) ?>
                                    </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>