<?php
/**
 * CashflowForecast — 30/60/90-day liquidity projection.
 *
 * Inflows: outstanding sales invoices bucketed by expected receipt date
 *   Overdue (< today) → treat as expected in the 0-30 bucket (chase now)
 *   otherwise bucket by DATEDIFF(effective_due, today)
 *
 * Outflows: outstanding supplier LPOs bucketed by expected payment date
 *   effective_due = requested_on + 30 days (same convention as AP ageing)
 *
 * Current bank balance: sum of all bank-flagged COA account balances
 *   (chart_of_accounts.is_bank = 1) from posted journal_lines.
 *
 * Buckets: 0-30 / 31-60 / 61-90 / 90+ days from today.
 */
class CashflowForecast extends Model
{
    /** Current cash on hand — sum of Dr − Cr on all bank-flagged accounts. */
    public function currentBankBalance(): float
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT IFNULL(SUM(jl.debit) - SUM(jl.credit), 0) AS balance
               FROM journal_lines jl
               JOIN journal_entries je   ON je.c0d = jl.journal_entry_id AND je.deleted = 0 AND je.status = 'POSTED'
               JOIN chart_of_accounts coa ON coa.c0d = jl.account_id
              WHERE coa.is_bank = 1",
            []
        );
        return (float)($rs[0]['balance'] ?? 0);
    }

    /**
     * Breakdown of expected inflows (open customer invoices) by bucket.
     * Overdue balances collapse into the 0-30 bucket (chase now).
     */
    public function expectedInflowsByBucket(string $asAt = ''): array
    {
        $asAt = $asAt ?: date('Y-m-d');
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT
                IFNULL(SUM(CASE WHEN DATEDIFF(IFNULL(s.due_date, DATE_ADD(s.invoice_date, INTERVAL 30 DAY)), :d1) <= 30 THEN GREATEST(s.total - s.amount_paid, 0) ELSE 0 END), 0) AS bucket_0_30,
                IFNULL(SUM(CASE WHEN DATEDIFF(IFNULL(s.due_date, DATE_ADD(s.invoice_date, INTERVAL 30 DAY)), :d2) 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(IFNULL(s.due_date, DATE_ADD(s.invoice_date, INTERVAL 30 DAY)), :d3) 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(IFNULL(s.due_date, DATE_ADD(s.invoice_date, INTERVAL 30 DAY)), :d4) > 90 THEN GREATEST(s.total - s.amount_paid, 0) ELSE 0 END), 0) AS bucket_90_plus,
                IFNULL(SUM(GREATEST(s.total - s.amount_paid, 0)), 0) AS total_open
              FROM sales s
             WHERE s.deleted = 0 AND s.invoice_workflow_status = 'ISSUED' AND s.total > s.amount_paid",
            [':d1' => $asAt, ':d2' => $asAt, ':d3' => $asAt, ':d4' => $asAt]
        );
        return $rs[0] ?? ['bucket_0_30' => 0, 'bucket_31_60' => 0, 'bucket_61_90' => 0, 'bucket_90_plus' => 0, 'total_open' => 0];
    }

    /** Breakdown of expected outflows (open supplier LPOs) by bucket. */
    public function expectedOutflowsByBucket(string $asAt = ''): array
    {
        $asAt = $asAt ?: date('Y-m-d');
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT
                IFNULL(SUM(CASE WHEN DATEDIFF(DATE_ADD(DATE(IFNULL(l.requested_on, l.created_on)), INTERVAL 30 DAY), :d1) <= 30 THEN GREATEST(l.amount - IFNULL(paid_agg.paid, 0), 0) ELSE 0 END), 0) AS bucket_0_30,
                IFNULL(SUM(CASE WHEN DATEDIFF(DATE_ADD(DATE(IFNULL(l.requested_on, l.created_on)), INTERVAL 30 DAY), :d2) BETWEEN 31 AND 60 THEN GREATEST(l.amount - IFNULL(paid_agg.paid, 0), 0) ELSE 0 END), 0) AS bucket_31_60,
                IFNULL(SUM(CASE WHEN DATEDIFF(DATE_ADD(DATE(IFNULL(l.requested_on, l.created_on)), INTERVAL 30 DAY), :d3) BETWEEN 61 AND 90 THEN GREATEST(l.amount - IFNULL(paid_agg.paid, 0), 0) ELSE 0 END), 0) AS bucket_61_90,
                IFNULL(SUM(CASE WHEN DATEDIFF(DATE_ADD(DATE(IFNULL(l.requested_on, l.created_on)), INTERVAL 30 DAY), :d4) > 90 THEN GREATEST(l.amount - IFNULL(paid_agg.paid, 0), 0) ELSE 0 END), 0) AS bucket_90_plus,
                IFNULL(SUM(GREATEST(l.amount - IFNULL(paid_agg.paid, 0), 0)), 0) AS total_open
              FROM lpos l
         LEFT JOIN (
                SELECT pa.lpo_id, SUM(pa.amount) AS paid
                  FROM payment_allocations pa
                  JOIN payments p ON p.c0d = pa.payment_id AND p.deleted = 0
                 GROUP BY pa.lpo_id
              ) paid_agg ON paid_agg.lpo_id = l.c0d
             WHERE l.deleted = 0 AND l.cash_out_status IN ('APPROVED','ISSUED')
               AND l.amount > IFNULL(paid_agg.paid, 0)",
            [':d1' => $asAt, ':d2' => $asAt, ':d3' => $asAt, ':d4' => $asAt]
        );
        return $rs[0] ?? ['bucket_0_30' => 0, 'bucket_31_60' => 0, 'bucket_61_90' => 0, 'bucket_90_plus' => 0, 'total_open' => 0];
    }

    /** Per-invoice detail for a bucket (used in drill-down table). */
    public function inflowLines(string $asAt = ''): array
    {
        $asAt = $asAt ?: date('Y-m-d');
        $db = new DB();
        return $db->mySelect(
            "SELECT s.c0d, s.sale_no, s.invoice_ref, s.invoice_date,
                    IFNULL(s.due_date, DATE_ADD(s.invoice_date, INTERVAL 30 DAY)) AS effective_due,
                    DATEDIFF(IFNULL(s.due_date, DATE_ADD(s.invoice_date, INTERVAL 30 DAY)), :asAt) AS days_until_due,
                    s.customer_name, s.currency, s.total, s.amount_paid,
                    (s.total - s.amount_paid) AS outstanding
               FROM sales s
              WHERE s.deleted = 0 AND s.invoice_workflow_status = 'ISSUED' AND s.total > s.amount_paid
              ORDER BY effective_due ASC",
            [':asAt' => $asAt]
        ) ?: [];
    }

    public function outflowLines(string $asAt = ''): array
    {
        $asAt = $asAt ?: date('Y-m-d');
        $db = new DB();
        return $db->mySelect(
            "SELECT l.c0d, l.lpo_no, l.lpo_type, l.payee_name, l.currency, l.amount,
                    IFNULL(paid_agg.paid, 0) AS paid,
                    GREATEST(l.amount - IFNULL(paid_agg.paid, 0), 0) AS outstanding,
                    DATE_ADD(DATE(IFNULL(l.requested_on, l.created_on)), INTERVAL 30 DAY) AS effective_due,
                    DATEDIFF(DATE_ADD(DATE(IFNULL(l.requested_on, l.created_on)), INTERVAL 30 DAY), :asAt) AS days_until_due
               FROM lpos l
          LEFT JOIN (SELECT pa.lpo_id, SUM(pa.amount) AS paid FROM payment_allocations pa
                     JOIN payments p ON p.c0d = pa.payment_id AND p.deleted = 0 GROUP BY pa.lpo_id) paid_agg ON paid_agg.lpo_id = l.c0d
              WHERE l.deleted = 0 AND l.cash_out_status IN ('APPROVED','ISSUED')
                AND l.amount > IFNULL(paid_agg.paid, 0)
              ORDER BY effective_due ASC",
            [':asAt' => $asAt]
        ) ?: [];
    }
}
