<?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 Customer Statement</h4>
                    <h6>Only customers with unpaid or partially paid invoices</h6>
                </div>
            </div>

            <div class="card">
                <div class="card-body">
                    <table class="table table-bordered table-striped">
                        <thead class="table-light">
                            <tr>
                                <th>Customer Name</th>
                                <th class="text-end">Total Outstanding</th>
                                <th>Status</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php
                            $query = "
                                SELECT 
                                    c.id AS customer_id, 
                                    c.name AS customer_name, 
                                    SUM(co.amount) AS total_outstanding,
                                    MAX(co.status) AS status
                                FROM cus_outstanding co
                                JOIN customers c ON c.id = co.customer_id
                                WHERE co.status IN ('unpaid', 'partial')
                                GROUP BY co.customer_id
                                ORDER BY customer_name ASC
                            ";

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

                            while ($row = $result->fetch_assoc()):
                                $grand_total += $row['total_outstanding'];
                            ?>
                            <tr>
                                <td><?= htmlspecialchars($row['customer_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>
