<?php
require_once __DIR__.'/../../../includes/auth.php';
requireLogin();
require_once __DIR__.'/../../../config/database.php';

$bucket = $_GET['bucket'] ?? '';

$sql = "
SELECT 
    i.invoice_number,
    i.invoice_date,
    i.total,
    COALESCE(SUM(p.amount),0) as paid
FROM invoices i
LEFT JOIN invoice_payments p ON p.invoice_id = i.id
WHERE i.status <> 'CANCELLED'
GROUP BY i.id
";

$stmt = $pdo->query($sql);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

$filtered = [];

foreach($rows as $r){

    $balance = $r['total'] - $r['paid'];
    if($balance <= 0) continue;

    $days = (time() - strtotime($r['invoice_date'])) / 86400;

    if($bucket == '0_30' && $days <= 30) $filtered[] = $r;
    elseif($bucket == '31_60' && $days > 30 && $days <= 60) $filtered[] = $r;
    elseif($bucket == '61_90' && $days > 60 && $days <= 90) $filtered[] = $r;
    elseif($bucket == '90_plus' && $days > 90) $filtered[] = $r;
}
?>

<h2>Aging Details (<?= $bucket ?>)</h2>

<table border="1" cellpadding="8">
<tr>
<th>Invoice</th>
<th>Date</th>
<th>Total</th>
<th>Paid</th>
<th>Balance</th>
</tr>

<?php foreach($filtered as $r): ?>
<tr>
<td><?= $r['invoice_number'] ?></td>
<td><?= $r['invoice_date'] ?></td>
<td>R <?= number_format($r['total'],2) ?></td>
<td>R <?= number_format($r['paid'],2) ?></td>
<td>R <?= number_format($r['total'] - $r['paid'],2) ?></td>
</tr>
<?php endforeach; ?>

</table>