<?php
/**
 * AgedPayables — mirror of AgedReceivables.
 *
 * Buckets an open bill's balance by days-past-due:
 *   current    (not yet due)
 *   1-30       (up to a month overdue)
 *   31-60
 *   61-90
 *   over 90
 *
 * Everything reads live — no snapshot table. If you want an as-of-date
 * view later, add an $asOf param and filter both bills and their
 * allocations by created_on / entry_date.
 */
class AgedPayables extends Model
{
    public function summary(): array
    {
        $db = new DB();
        $rows = $db->mySelect(
            "SELECT sb.c0d AS bill_id, sb.bill_no, sb.supplier_ref, sb.bill_date, sb.due_date,
                    sb.total_amount, sb.amount_paid,
                    (sb.total_amount - sb.amount_paid) AS balance_due,
                    DATEDIFF(CURDATE(), COALESCE(sb.due_date, sb.bill_date)) AS days_overdue,
                    v.c0d AS vendor_id, v.vendor_name, v.vendor_tin
               FROM supplier_bills sb
          LEFT JOIN vendors v ON v.c0d = sb.vendor_id
              WHERE sb.deleted = 0
                AND sb.status IN ('posted','paid')
                AND (sb.total_amount - sb.amount_paid) > 0.005
                AND sb.tenant_id = :t
           ORDER BY v.vendor_name, sb.bill_date",
            [':t' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1]
        ) ?: [];

        $vendors = [];   // vendor_id => bucket totals + bills[]
        $totals  = ['current' => 0.0, 'd1_30' => 0.0, 'd31_60' => 0.0, 'd61_90' => 0.0, 'd90_plus' => 0.0, 'grand' => 0.0];

        foreach ($rows as $r) {
            $d = (int)$r['days_overdue'];
            $bal = (float)$r['balance_due'];
            $bucket = $d <= 0  ? 'current'
                    : ($d <= 30 ? 'd1_30'
                    : ($d <= 60 ? 'd31_60'
                    : ($d <= 90 ? 'd61_90' : 'd90_plus')));

            $vid = (int)$r['vendor_id'];
            if (!isset($vendors[$vid])) {
                $vendors[$vid] = [
                    'vendor_id'   => $vid,
                    'vendor_name' => $r['vendor_name'] ?: '(unknown)',
                    'vendor_tin'  => $r['vendor_tin'],
                    'current'     => 0.0, 'd1_30' => 0.0, 'd31_60' => 0.0, 'd61_90' => 0.0, 'd90_plus' => 0.0,
                    'grand'       => 0.0,
                    'bills'       => [],
                ];
            }
            $vendors[$vid][$bucket] += $bal;
            $vendors[$vid]['grand'] += $bal;
            $vendors[$vid]['bills'][] = [
                'bill_id'      => (int)$r['bill_id'],
                'bill_no'      => $r['bill_no'],
                'supplier_ref' => $r['supplier_ref'],
                'bill_date'    => $r['bill_date'],
                'due_date'     => $r['due_date'],
                'balance_due'  => $bal,
                'days_overdue' => $d,
                'bucket'       => $bucket,
            ];

            $totals[$bucket] += $bal;
            $totals['grand'] += $bal;
        }

        // Round + sort
        foreach ($vendors as &$v) {
            foreach (['current','d1_30','d31_60','d61_90','d90_plus','grand'] as $b) $v[$b] = round($v[$b], 2);
        }
        unset($v);
        foreach ($totals as $k => $t) $totals[$k] = round($t, 2);

        // Vendors with the biggest overdue exposure first
        uasort($vendors, fn($a, $b) => ($b['d90_plus'] + $b['d61_90']) <=> ($a['d90_plus'] + $a['d61_90'])
                                     ?: $b['grand'] <=> $a['grand']);

        return ['vendors' => array_values($vendors), 'totals' => $totals];
    }
}
