<?php
/**
 * CustomerStatements — statement of account for a single customer.
 *
 * Ledger convention:
 *   Invoice increases the customer's balance (debit  side, "you owe us")
 *   Receipt decreases the customer's balance (credit side, "you paid us")
 * Rows are sorted by date, then by type so an invoice raised the same day
 * as a payment sits before its receipt in the running-balance column.
 *
 * Opening balance = all non-voided activity strictly before $from.
 * Closing balance = opening + net movement inside the window.
 * Aging is computed against CURDATE() from every open invoice's remaining
 * balance, using the same 5 buckets as AgedReceivables so the two agree.
 */
class CustomerStatements extends Model
{
    private int $tenantId;

    public function __construct(?int $tenantId = null)
    {
        $this->tenantId = $tenantId ?? (defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1);
    }

    public function statement(int $customerId, string $from, string $to): ?array
    {
        $db = new DB();
        $c  = $db->mySelect(
            "SELECT c0d, customer_no, customer_code,
                    TRIM(CONCAT(fname, ' ', lname)) AS customer_name,
                    email, phone_1, physical_address, city, country, po_box,
                    customer_tin
               FROM customers WHERE c0d = :id AND deleted = 0 LIMIT 1",
            [':id' => $customerId]
        );
        if (empty($c)) return null;

        // 1. Every non-void invoice + non-void receipt for the customer,
        //    from inception through $to, so we can compute opening + window
        //    activity in one pass.
        $invoices = $db->mySelect(
            "SELECT c0d, invoice_no, invoice_date, due_date, total_amount,
                    amount_paid, status
               FROM customer_invoices
              WHERE customer_id = :cid AND deleted = 0
                AND status IN ('posted','partially_paid','paid')
                AND invoice_date <= :to",
            [':cid' => $customerId, ':to' => $to]
        ) ?: [];

        $receipts = $db->mySelect(
            "SELECT c0d, receipt_no, receipt_date, amount, payment_method,
                    reference, status
               FROM customer_receipts
              WHERE customer_id = :cid AND deleted = 0
                AND status = 'recorded'
                AND receipt_date <= :to",
            [':cid' => $customerId, ':to' => $to]
        ) ?: [];

        // 2. Merge into one time-ordered ledger stream
        $ledger = [];
        foreach ($invoices as $i) {
            $ledger[] = [
                'date'    => $i['invoice_date'],
                'sort_k'  => 1,                       // invoices before receipts on same date
                'kind'    => 'invoice',
                'ref'     => $i['invoice_no'],
                'label'   => 'Invoice ' . $i['invoice_no'] . ($i['due_date'] ? ' · due ' . $i['due_date'] : ''),
                'debit'   => (float)$i['total_amount'],
                'credit'  => 0.0,
                'link_id' => (int)$i['c0d'],
            ];
        }
        foreach ($receipts as $r) {
            $ledger[] = [
                'date'    => $r['receipt_date'],
                'sort_k'  => 2,
                'kind'    => 'receipt',
                'ref'     => $r['receipt_no'],
                'label'   => 'Receipt ' . $r['receipt_no'] . ' · ' . str_replace('_', ' ', $r['payment_method'])
                             . ($r['reference'] ? ' · ' . $r['reference'] : ''),
                'debit'   => 0.0,
                'credit'  => (float)$r['amount'],
                'link_id' => (int)$r['c0d'],
            ];
        }
        usort($ledger, function ($a, $b) {
            $c = strcmp($a['date'], $b['date']);
            if ($c !== 0) return $c;
            return $a['sort_k'] <=> $b['sort_k'];
        });

        // 3. Split into opening + window
        $opening = 0.0;
        $rows    = [];
        foreach ($ledger as $l) {
            if ($l['date'] < $from) {
                $opening += ($l['debit'] - $l['credit']);
                continue;
            }
            $rows[] = $l;
        }
        $opening = round($opening, 2);

        // 4. Running balance through the window
        $running = $opening;
        $totalDebit = $totalCredit = 0.0;
        foreach ($rows as &$r) {
            $running    += ($r['debit'] - $r['credit']);
            $r['running'] = round($running, 2);
            $totalDebit  += $r['debit'];
            $totalCredit += $r['credit'];
        }
        unset($r);
        $closing = round($running, 2);

        // 5. Aging on OPEN invoices (any status but paid), using each
        //    invoice's remaining balance vs due date. Uses today's date so
        //    the aged column matches AgedReceivables one-to-one.
        $ageBuckets = ['current' => 0.0, 'd1_30' => 0.0, 'd31_60' => 0.0, 'd61_90' => 0.0, 'd90_plus' => 0.0];
        $openInvoices = $db->mySelect(
            "SELECT invoice_no, invoice_date, due_date, total_amount, amount_paid,
                    (total_amount - amount_paid) AS balance,
                    DATEDIFF(CURDATE(), COALESCE(due_date, invoice_date)) AS days_overdue
               FROM customer_invoices
              WHERE customer_id = :cid AND deleted = 0
                AND status IN ('posted','partially_paid')
                AND (total_amount - amount_paid) > 0.005",
            [':cid' => $customerId]
        ) ?: [];
        foreach ($openInvoices as $inv) {
            $d = (int)$inv['days_overdue'];
            $b = (float)$inv['balance'];
            $bucket = $d <= 0  ? 'current'
                    : ($d <= 30 ? 'd1_30'
                    : ($d <= 60 ? 'd31_60'
                    : ($d <= 90 ? 'd61_90' : 'd90_plus')));
            $ageBuckets[$bucket] += $b;
        }
        foreach ($ageBuckets as $k => $val) $ageBuckets[$k] = round($val, 2);
        $ageTotal = round(array_sum($ageBuckets), 2);

        return [
            'customer'      => $c[0],
            'from'          => $from,
            'to'            => $to,
            'opening'       => $opening,
            'closing'       => $closing,
            'total_debit'   => round($totalDebit, 2),
            'total_credit'  => round($totalCredit, 2),
            'rows'          => $rows,
            'age_buckets'   => $ageBuckets,
            'age_total'     => $ageTotal,
            'open_count'    => count($openInvoices),
        ];
    }

    /**
     * Fetch every non-deleted customer for the picker on the landing page.
     */
    public function allActive(): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT c0d, customer_no,
                    TRIM(CONCAT(fname, ' ', lname)) AS customer_name
               FROM customers
              WHERE deleted = 0 AND status = 'ACTIVE'
           ORDER BY fname, lname",
            []
        ) ?: [];
    }
}
