<!DOCTYPE html>
<?php
include("layout.php");
include("db.php");
include 'auth.php';
require_right('Purchase Report');

// Fetch purchase report data
$query = "
    SELECT 
        pi.item_name,
        SUM(pi.quantity) AS total_qty,
        pi.unit_cost,
        pi.tax_rate,
        SUM(pi.total_cost) AS total_cost
    FROM 
        purchase_items pi
    JOIN 
        purchases p ON pi.purchase_id = p.id
    GROUP BY 
        pi.item_id, pi.unit_cost, pi.tax_rate, pi.item_name
    ORDER BY 
        pi.item_name ASC
";

$result = mysqli_query($conn, $query);
?>

<div class="page-wrapper">
    <div class="content">
        <div class="page-header">
            <div class="add-item d-flex">
                <div class="page-title">
                    <h4>Purchase Report</h4>
                    <h6>Overview of Purchased Items</h6>
                </div>
            </div>
        </div>

        <div class="card table-list-card">
            <div class="card-body">
                <div class="table-responsive">
                    <table class="table datanew" id="purchaseReportTable">
                        <thead>
                            <tr>
                                <th>Item Name</th>
                                <th>Quantity</th>
                                <th>Unit Cost</th>
                                <th>Tax Rate</th>
                                <th>Total Cost</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php while ($row = mysqli_fetch_assoc($result)) : ?>
                                <tr>
                                    <td><?php echo htmlspecialchars($row['item_name']); ?></td>
                                    <td><?php echo $row['total_qty']; ?></td>
                                    <td><?php echo number_format($row['unit_cost'], 2); ?></td>
                                    <td><?php echo number_format($row['tax_rate'], 2); ?>%</td>
                                    <td><?php echo number_format($row['total_cost'], 2); ?></td>
                                </tr>
                            <?php endwhile; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- DataTable Init -->
<script>
  $(document).ready(function () {
    if (!$.fn.DataTable.isDataTable('#purchaseReportTable')) {
      $('#purchaseReportTable').DataTable();
    }
  });
</script>
</html>
