<?php
include 'db.php';
include 'layout.php';
?>

<div class="main-wrapper">
    <div class="page-wrapper">
        <div class="content">

            <div class="page-header">
                <div class="page-title">
                    <h4>Outstanding Supplier Statement</h4>
                    <h6>Only suppliers with unpaid or partially paid purchases</h6>
                </div>
            </div>

            <div class="card">
                <div class="card-body">
                    <table class="table table-bordered table-striped">
                        <thead class="table-light">
                            <tr>
                                <th>Supplier Name</th>
                                <th class="text-end">Total Outstanding</th>
                                <th>Status</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $query = "
                                SELECT 
                                    v.id AS vendor_id, 
                                    v.name AS vendor_name, 
                                    SUM(so.amount) AS total_outstanding,
                                    MAX(so.status) AS status
                                FROM sup_outstanding so
                                JOIN vendors v ON v.id = so.vendor_id
                                WHERE so.status IN ('unpaid', 'partial')
                                GROUP BY so.vendor_id
                                ORDER BY vendor_name ASC
                            ";

                            $result = $conn->query($query);
                            $grand_total = 0;

                            while ($row = $result->fetch_assoc()):
                                $grand_total += $row['total_outstanding'];
                            ?>
                            <tr>
                                <td><?= htmlspecialchars($row['vendor_name']) ?></td>
                                <td class="text-end"><?= number_format($row['total_outstanding'], 2) ?></td>
                                <td><?= ucfirst($row['status']) ?></td>
                            </tr>
                            <?php endwhile; ?>
                        </tbody>
                        <tfoot>
                            <tr class="table-light fw-bold">
                                <td>Total Outstanding</td>
                                <td class="text-end"><?= number_format($grand_total, 2) ?></td>
                                <td></td>
                            </tr>
                        </tfoot>
                    </table>
                </div>
            </div>

        </div>
    </div>
</div>
