<?php
/**
 * SupplierStatement — Accounts Payable ageing per vendor.
 *
 * Mirror of CustomerStatement, but on the payables side. Every SUPPLIER lpo
 * with cash_out_status ∈ (APPROVED, ISSUED, PAID) contributes its
 * outstanding balance (amount − Σ payments allocated to it) into ONE ageing
 * bucket based on days past its effective due date:
 *   Current: due date not yet reached (or today)
 *   1-30 / 31-60 / 61-90 / 90+: days past due
 *
 * Effective due = requested_on + 30 days (default supplier terms).
 * Only vendor payees count — employee/customer LPOs (petty cash, imprest,
 * disposal refund) are excluded here.
 */
class SupplierStatement extends Model
{
    /** Portfolio overview — one row per vendor with committed LPOs. */
    public function overviewAllVendors(string $asAt = ''): array
    {
        $asAt = $asAt ?: date('Y-m-d');
        $db = new DB();
        $sql = "
            SELECT v.c0d AS vendor_id, v.vendor_no, v.vendor_name, v.vendor_tin,
                   v.vendor_email, v.vendor_phone_1, v.vendor_category,
                   COUNT(l.c0d) AS lpo_count,
                   IFNULL(SUM(l.amount), 0) AS total_committed,
                   IFNULL(SUM(paid_agg.paid), 0) AS total_paid,
                   IFNULL(SUM(GREATEST(l.amount - IFNULL(paid_agg.paid, 0), 0)), 0) AS total_outstanding,
                   IFNULL(SUM(CASE WHEN DATEDIFF(:d1, DATE_ADD(DATE(IFNULL(l.requested_on, l.created_on)), INTERVAL 30 DAY)) <= 0
                                    THEN GREATEST(l.amount - IFNULL(paid_agg.paid, 0), 0) ELSE 0 END), 0) AS bucket_current,
                   IFNULL(SUM(CASE WHEN DATEDIFF(:d2, DATE_ADD(DATE(IFNULL(l.requested_on, l.created_on)), INTERVAL 30 DAY)) BETWEEN 1  AND 30
                                    THEN GREATEST(l.amount - IFNULL(paid_agg.paid, 0), 0) ELSE 0 END), 0) AS bucket_1_30,
                   IFNULL(SUM(CASE WHEN DATEDIFF(:d3, DATE_ADD(DATE(IFNULL(l.requested_on, l.created_on)), INTERVAL 30 DAY)) 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(:d4, DATE_ADD(DATE(IFNULL(l.requested_on, l.created_on)), INTERVAL 30 DAY)) 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(:d5, DATE_ADD(DATE(IFNULL(l.requested_on, l.created_on)), INTERVAL 30 DAY)) > 90
                                    THEN GREATEST(l.amount - IFNULL(paid_agg.paid, 0), 0) ELSE 0 END), 0) AS bucket_90_plus,
                   MAX(CASE WHEN l.amount > IFNULL(paid_agg.paid, 0)
                             THEN DATEDIFF(:d6, DATE_ADD(DATE(IFNULL(l.requested_on, l.created_on)), INTERVAL 30 DAY)) END) AS oldest_days_past_due
              FROM vendors v
         LEFT JOIN lpos l ON l.payee_type = 'vendor' AND l.payee_id = v.c0d
                          AND l.deleted = 0
                          AND l.cash_out_status IN ('APPROVED','ISSUED','PAID')
         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 v.deleted = 0
             GROUP BY v.c0d, v.vendor_no, v.vendor_name, v.vendor_tin, v.vendor_email, v.vendor_phone_1, v.vendor_category
            HAVING lpo_count > 0
             ORDER BY total_outstanding DESC, total_committed DESC
        ";
        return $db->mySelect($sql, [':d1'=>$asAt,':d2'=>$asAt,':d3'=>$asAt,':d4'=>$asAt,':d5'=>$asAt,':d6'=>$asAt]) ?: [];
    }

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

        $vRs = $db->mySelect(
            "SELECT c0d, vendor_no, vendor_name, vendor_tin, vendor_vrn,
                    vendor_address, vendor_phone_1, vendor_phone_2, vendor_email,
                    vendor_category, vendor_poc, vendor_poc_email, vendor_poc_phone,
                    bank_name, bank_account_no
               FROM vendors WHERE c0d = :id AND deleted = 0 LIMIT 1",
            [':id' => $vendor_id]
        );
        if (empty($vRs)) return [];
        $v = $vRs[0];

        // Per-LPO detail with ageing
        $lpos = $db->mySelect(
            "SELECT l.c0d, l.lpo_no, l.lpo_type, l.source_module, l.currency,
                    l.amount, l.purpose, l.cash_out_status, l.receipt_status,
                    IFNULL(paid_agg.paid, 0) AS paid,
                    GREATEST(l.amount - IFNULL(paid_agg.paid, 0), 0) AS outstanding,
                    DATE(IFNULL(l.requested_on, l.created_on)) AS requested_date,
                    DATE_ADD(DATE(IFNULL(l.requested_on, l.created_on)), INTERVAL 30 DAY) AS effective_due,
                    DATEDIFF(:asAt, DATE_ADD(DATE(IFNULL(l.requested_on, l.created_on)), INTERVAL 30 DAY)) AS days_past_due,
                    p.project_no
               FROM lpos l
          LEFT JOIN c_projects p ON p.c0d = l.project_id
          LEFT JOIN (
                SELECT pa.lpo_id, SUM(pa.amount) AS paid
                  FROM payment_allocations pa
                  JOIN payments p2 ON p2.c0d = pa.payment_id AND p2.deleted = 0
                 GROUP BY pa.lpo_id
              ) paid_agg ON paid_agg.lpo_id = l.c0d
              WHERE l.payee_type = 'vendor' AND l.payee_id = :vid
                AND l.deleted = 0
                AND l.cash_out_status IN ('APPROVED','ISSUED','PAID')
              ORDER BY effective_due ASC, l.c0d ASC",
            [':asAt' => $asAt, ':vid' => $vendor_id]
        ) ?: [];

        // Buckets + totals
        $buckets = ['current' => 0.0, '1_30' => 0.0, '31_60' => 0.0, '61_90' => 0.0, '90_plus' => 0.0];
        $totCommitted = 0.0; $totPaid = 0.0; $totOutstanding = 0.0; $oldest = 0;
        foreach ($lpos as &$l) {
            $totCommitted   += (float)$l['amount'];
            $totPaid        += (float)$l['paid'];
            $totOutstanding += (float)$l['outstanding'];
            $days = (int)$l['days_past_due'];
            if ((float)$l['outstanding'] > 0 && $days > $oldest) $oldest = $days;
            $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';
            $l['bucket'] = $bucket;
            if ((float)$l['outstanding'] > 0) $buckets[$bucket] += (float)$l['outstanding'];
        }
        unset($l);

        // Recent payments made to this vendor
        $pmts = $db->mySelect(
            "SELECT p.c0d, p.payment_no, p.payment_date, p.amount, p.currency,
                    p.payment_method, p.reference_no,
                    pv.pv_no, pv.lpo_id,
                    l.lpo_no
               FROM payments p
               JOIN payment_vouchers pv ON pv.c0d = p.payment_voucher_id
               JOIN lpos l              ON l.c0d = pv.lpo_id
              WHERE l.payee_type = 'vendor' AND l.payee_id = :vid
                AND p.deleted = 0
              ORDER BY p.payment_date DESC, p.c0d DESC
              LIMIT 20",
            [':vid' => $vendor_id]
        ) ?: [];

        return [
            'vendor'            => $v,
            'as_at'             => $asAt,
            'lpos'              => $lpos,
            'buckets'           => $buckets,
            'total_committed'   => $totCommitted,
            'total_paid'        => $totPaid,
            'total_outstanding' => $totOutstanding,
            'oldest_days'       => $oldest,
            'payments'          => $pmts,
        ];
    }
}
