<?php
require_once __DIR__ . '/../config/auth.php';
require_once __DIR__ . '/../includes/functions.php';
requirePermission('report_view');

$pdo = getDB();
$customers = $pdo->query("
    SELECT c.*, COALESCE(SUM(si.total_amount), 0) AS total_purchases
    FROM customers c
    LEFT JOIN sales_invoices si ON c.id = si.customer_id AND si.invoice_status != 'Cancelled'
    WHERE c.current_balance > 0
    GROUP BY c.id
    ORDER BY c.current_balance DESC
")->fetchAll();
$total_due = array_sum(array_column($customers, 'current_balance'));

include __DIR__ . '/../includes/header.php';
include __DIR__ . '/../includes/sidebar.php';
?>
<div class="content-wrapper">
    <div class="container-fluid">
        <h4 class="page-title"><i class="fas fa-users me-2"></i>Customer Due Report</h4>
        <div class="row mb-3">
            <div class="col-md-4"><div class="stat-card bg-danger-gradient"><div class="stat-value"><?= formatCurrency($total_due) ?></div><div class="stat-label">Total Due Amount</div></div></div>
            <div class="col-md-4"><div class="stat-card bg-warning-gradient"><div class="stat-value"><?= count($customers) ?></div><div class="stat-label">Customers with Dues</div></div></div>
        </div>
        <div class="card">
            <div class="card-body">
                <div class="table-responsive">
                    <table id="customerDueTable" class="table table-hover datatable">
                        <thead><tr><th>Customer</th><th>Phone</th><th>Due Balance</th><th>Total Purchases</th></tr></thead>
                        <tbody>
                            <?php foreach ($customers as $c): ?>
                            <tr>
                                <td><?= htmlspecialchars($c['name']) ?></td>
                                <td><?= htmlspecialchars($c['phone']) ?></td>
                                <td class="text-danger fw-bold"><?= formatCurrency($c['current_balance']) ?></td>
                                <td><?= formatCurrency($c['total_purchases']) ?></td>
                            </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</div>
<?php include __DIR__ . '/../includes/footer.php'; ?>
