<?php
/**
 * AgedReceivables — outstanding customer invoices bucketed by days since due.
 *
 * The report is a single SELECT: read from customer_invoices where status is
 * still open (issued / partially_paid), compute days-since-due, then bucket:
 *   current (not yet due)  ·  0-30  ·  31-60  ·  61-90  ·  90+
 *
 * Two views:
 *   summary()  — per-customer aggregate (5 buckets + total)
 *   detail()   — per-invoice list for one customer (drill-through from summary)
 */
class AgedReceivables extends Model
{
    public function summary(?string $asOf = null): array
    {
        $asOf = $asOf ?: date('Y-m-d');
        return (new DB())->mySelect(
            "SELECT
                c.c0d AS customer_id,
                TRIM(CONCAT(c.fname, ' ', c.lname)) AS customer_name,
                c.phone_1 AS customer_phone,
                COUNT(i.c0d) AS invoice_count,
                SUM(i.total_amount - i.amount_paid) AS total_outstanding,
                SUM(CASE WHEN DATEDIFF(:asof, i.due_date) <= 0                                   THEN i.total_amount - i.amount_paid ELSE 0 END) AS bucket_current,
                SUM(CASE WHEN DATEDIFF(:asof, i.due_date) BETWEEN 1  AND 30                      THEN i.total_amount - i.amount_paid ELSE 0 END) AS bucket_0_30,
                SUM(CASE WHEN DATEDIFF(:asof, i.due_date) BETWEEN 31 AND 60                      THEN i.total_amount - i.amount_paid ELSE 0 END) AS bucket_31_60,
                SUM(CASE WHEN DATEDIFF(:asof, i.due_date) BETWEEN 61 AND 90                      THEN i.total_amount - i.amount_paid ELSE 0 END) AS bucket_61_90,
                SUM(CASE WHEN DATEDIFF(:asof, i.due_date) > 90                                    THEN i.total_amount - i.amount_paid ELSE 0 END) AS bucket_90_plus,
                MAX(DATEDIFF(:asof, i.due_date)) AS oldest_overdue_days
              FROM customer_invoices i
              JOIN customers c ON c.c0d = i.customer_id
             WHERE i.deleted = 0
               AND i.status IN ('issued','partially_paid')
               AND i.tenant_id = :tenant
          GROUP BY c.c0d
            HAVING total_outstanding > 0
          ORDER BY bucket_90_plus DESC, bucket_61_90 DESC, total_outstanding DESC",
            [':asof' => $asOf, ':tenant' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1]
        ) ?: [];
    }

    public function totals(?string $asOf = null): array
    {
        $rows = $this->summary($asOf);
        $t = ['invoice_count'=>0,'total_outstanding'=>0,'bucket_current'=>0,'bucket_0_30'=>0,'bucket_31_60'=>0,'bucket_61_90'=>0,'bucket_90_plus'=>0];
        foreach ($rows as $r) {
            $t['invoice_count']     += (int)$r['invoice_count'];
            $t['total_outstanding'] += (float)$r['total_outstanding'];
            $t['bucket_current']    += (float)$r['bucket_current'];
            $t['bucket_0_30']       += (float)$r['bucket_0_30'];
            $t['bucket_31_60']      += (float)$r['bucket_31_60'];
            $t['bucket_61_90']      += (float)$r['bucket_61_90'];
            $t['bucket_90_plus']    += (float)$r['bucket_90_plus'];
        }
        return $t;
    }
}
