<?php
/**
 * CustomerStatement — Accounts Receivable ageing per customer.
 *
 * Every ISSUED sales invoice contributes its outstanding balance
 * (total − amount_paid) into ONE ageing bucket based on days past due:
 *   Current: due_date not yet reached (or today)
 *   1-30:    1-30 days past due
 *   31-60:   31-60 days past due
 *   61-90:   61-90 days past due
 *   90+:     over 90 days past due (this is where CFOs panic)
 *
 * If an invoice has no due_date, default is invoice_date + 30 days.
 * Overpayments (amount_paid > total) show as negative outstanding — surfaced
 * as a "credit" chip, not merged with the buckets.
 */
class CustomerStatement extends Model
{
    /** Portfolio overview — one row per customer with ISSUED invoices. */
    public function overviewAllCustomers(string $asAt = ''): array
    {
        $asAt = $asAt ?: date('Y-m-d');
        $db = new DB();
        $sql = "
            SELECT c.c0d AS customer_id, c.customer_no, c.fname, c.lname, c.email, c.phone_1,
                   c.customer_tier, c.customer_sector,
                   COUNT(s.c0d) AS invoice_count,
                   IFNULL(SUM(s.total), 0) AS total_invoiced,
                   IFNULL(SUM(s.amount_paid), 0) AS total_paid,
                   IFNULL(SUM(GREATEST(s.total - s.amount_paid, 0)), 0) AS total_outstanding,
                   IFNULL(SUM(CASE WHEN DATEDIFF(:d1, IFNULL(s.due_date, DATE_ADD(s.invoice_date, INTERVAL 30 DAY))) <= 0
                                    THEN GREATEST(s.total - s.amount_paid, 0) ELSE 0 END), 0) AS bucket_current,
                   IFNULL(SUM(CASE WHEN DATEDIFF(:d2, IFNULL(s.due_date, DATE_ADD(s.invoice_date, INTERVAL 30 DAY))) BETWEEN 1  AND 30
                                    THEN GREATEST(s.total - s.amount_paid, 0) ELSE 0 END), 0) AS bucket_1_30,
                   IFNULL(SUM(CASE WHEN DATEDIFF(:d3, IFNULL(s.due_date, DATE_ADD(s.invoice_date, INTERVAL 30 DAY))) BETWEEN 31 AND 60
                                    THEN GREATEST(s.total - s.amount_paid, 0) ELSE 0 END), 0) AS bucket_31_60,
                   IFNULL(SUM(CASE WHEN DATEDIFF(:d4, IFNULL(s.due_date, DATE_ADD(s.invoice_date, INTERVAL 30 DAY))) BETWEEN 61 AND 90
                                    THEN GREATEST(s.total - s.amount_paid, 0) ELSE 0 END), 0) AS bucket_61_90,
                   IFNULL(SUM(CASE WHEN DATEDIFF(:d5, IFNULL(s.due_date, DATE_ADD(s.invoice_date, INTERVAL 30 DAY))) > 90
                                    THEN GREATEST(s.total - s.amount_paid, 0) ELSE 0 END), 0) AS bucket_90_plus,
                   MAX(CASE WHEN s.total > s.amount_paid
                             THEN DATEDIFF(:d6, IFNULL(s.due_date, DATE_ADD(s.invoice_date, INTERVAL 30 DAY))) END) AS oldest_days_past_due
              FROM customers c
         LEFT JOIN sales s ON s.customer_id = c.c0d AND s.deleted = 0 AND s.invoice_workflow_status = 'ISSUED'
             WHERE c.deleted = 0
             GROUP BY c.c0d, c.customer_no, c.fname, c.lname, c.email, c.phone_1, c.customer_tier, c.customer_sector
            HAVING invoice_count > 0
             ORDER BY total_outstanding DESC, total_invoiced DESC
        ";
        return $db->mySelect($sql, [':d1'=>$asAt,':d2'=>$asAt,':d3'=>$asAt,':d4'=>$asAt,':d5'=>$asAt,':d6'=>$asAt]) ?: [];
    }

    public function forCustomer(int $customer_id, string $asAt = ''): array
    {
        $asAt = $asAt ?: date('Y-m-d');
        $db = new DB();

        $custRs = $db->mySelect(
            "SELECT c0d, customer_no, fname, lname, email, phone_1, phone_2, physical_address, city, country,
                    customer_tin, customer_vrn, customer_tier, customer_sector, customer_poc,
                    customer_poc_email, customer_poc_phone
               FROM customers WHERE c0d = :id AND deleted = 0 LIMIT 1",
            [':id' => $customer_id]
        );
        if (empty($custRs)) return [];
        $c = $custRs[0];

        // All ISSUED invoices with ageing
        $inv = $db->mySelect(
            "SELECT s.c0d, s.sale_no, s.invoice_ref, s.invoice_date, s.due_date, s.currency,
                    s.subtotal, s.vat_amount, s.total, s.amount_paid,
                    GREATEST(s.total - s.amount_paid, 0)                        AS outstanding,
                    (s.amount_paid - s.total)                                    AS overpayment,
                    IFNULL(s.due_date, DATE_ADD(s.invoice_date, INTERVAL 30 DAY)) AS effective_due,
                    DATEDIFF(:asAt, IFNULL(s.due_date, DATE_ADD(s.invoice_date, INTERVAL 30 DAY))) AS days_past_due,
                    p.project_no
               FROM sales s
          LEFT JOIN c_projects p ON p.c0d = s.project_id
              WHERE s.customer_id = :cid AND s.deleted = 0 AND s.invoice_workflow_status = 'ISSUED'
              ORDER BY effective_due ASC, s.c0d ASC",
            [':asAt' => $asAt, ':cid' => $customer_id]
        ) ?: [];

        // Buckets + totals
        $buckets = ['current' => 0.0, '1_30' => 0.0, '31_60' => 0.0, '61_90' => 0.0, '90_plus' => 0.0];
        $totInvoiced = 0.0; $totPaid = 0.0; $totOutstanding = 0.0; $totOverpayment = 0.0;
        $oldest = 0;
        foreach ($inv as &$i) {
            $totInvoiced   += (float)$i['total'];
            $totPaid       += (float)$i['amount_paid'];
            $totOutstanding+= (float)$i['outstanding'];
            $days = (int)$i['days_past_due'];
            if ((float)$i['outstanding'] > 0 && $days > $oldest) $oldest = $days;
            if ((float)$i['overpayment'] > 0) $totOverpayment += (float)$i['overpayment'];

            $bucket = 'current';
            if     ($days > 90)              $bucket = '90_plus';
            elseif ($days > 60)              $bucket = '61_90';
            elseif ($days > 30)              $bucket = '31_60';
            elseif ($days > 0)               $bucket = '1_30';
            $i['bucket'] = $bucket;
            if ((float)$i['outstanding'] > 0) $buckets[$bucket] += (float)$i['outstanding'];
        }
        unset($i);

        // Recent receipts
        $rcp = $db->mySelect(
            "SELECT r.c0d, r.receipt_no, r.receipt_type, r.receipt_date, r.amount, r.currency,
                    r.payment_method, r.bank_reference, r.efd_receipt_number, r.sale_id,
                    s.sale_no, s.invoice_ref
               FROM receipts r
          LEFT JOIN sales s ON s.c0d = r.sale_id
              WHERE r.customer_id = :cid AND r.deleted = 0
              ORDER BY r.receipt_date DESC, r.c0d DESC
              LIMIT 20",
            [':cid' => $customer_id]
        ) ?: [];

        return [
            'customer'          => $c,
            'as_at'             => $asAt,
            'invoices'          => $inv,
            'buckets'           => $buckets,
            'total_invoiced'    => $totInvoiced,
            'total_paid'        => $totPaid,
            'total_outstanding' => $totOutstanding,
            'total_overpayment' => $totOverpayment,
            'oldest_days'       => $oldest,
            'receipts'          => $rcp,
        ];
    }
}
