<?php
/**
 * SupplierStatements — Pacific's ledger view of one supplier account.
 *
 * Same shape as CustomerStatements but with the sign flipped for how
 * balances read to a human reviewer:
 *   Bill increases what we owe the supplier    (debit column in the view)
 *   Payment decreases what we owe               (credit column in the view)
 * Closing balance > 0 = we owe them; < 0 = advance / credit on account.
 *
 * Used to reconcile Pacific's payables ledger against the supplier's own
 * statement when the two disagree.
 */
class SupplierStatements 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 $vendorId, string $from, string $to): ?array
    {
        $db = new DB();

        $v = $db->mySelect(
            "SELECT c0d, vendor_no, vendor_name, vendor_tin, vendor_vrn,
                    vendor_phone_1, vendor_email, vendor_address,
                    vendor_city, vendor_country, vendor_po_box, vendor_category
               FROM vendors WHERE c0d = :id AND deleted = 0 LIMIT 1",
            [':id' => $vendorId]
        );
        if (empty($v)) return null;

        // Bills that ever hit A/P for this supplier (draft bills stay out)
        $bills = $db->mySelect(
            "SELECT c0d, bill_no, supplier_ref, bill_date, due_date, total_amount,
                    amount_paid, status
               FROM supplier_bills
              WHERE vendor_id = :vid AND deleted = 0
                AND status IN ('posted','paid')
                AND bill_date <= :to",
            [':vid' => $vendorId, ':to' => $to]
        ) ?: [];

        // Non-void payments made
        $payments = $db->mySelect(
            "SELECT c0d, payment_no, payment_date, amount, payment_method,
                    reference, status
               FROM supplier_payments
              WHERE vendor_id = :vid AND deleted = 0
                AND status = 'recorded'
                AND payment_date <= :to",
            [':vid' => $vendorId, ':to' => $to]
        ) ?: [];

        // Merge into a time-ordered ledger. Bills sort before payments on
        // the same day so the running balance moves in the right direction.
        $ledger = [];
        foreach ($bills as $b) {
            $ledger[] = [
                'date'    => $b['bill_date'],
                'sort_k'  => 1,
                'kind'    => 'bill',
                'ref'     => $b['bill_no'],
                'label'   => 'Bill ' . $b['bill_no']
                             . ($b['supplier_ref'] ? ' · ' . $b['supplier_ref'] : '')
                             . ($b['due_date'] ? ' · due ' . $b['due_date'] : ''),
                'debit'   => (float)$b['total_amount'],   // increases owed
                'credit'  => 0.0,
                'link_id' => (int)$b['c0d'],
            ];
        }
        foreach ($payments as $p) {
            $ledger[] = [
                'date'    => $p['payment_date'],
                'sort_k'  => 2,
                'kind'    => 'payment',
                'ref'     => $p['payment_no'],
                'label'   => 'Payment ' . $p['payment_no']
                             . ' · ' . str_replace('_', ' ', $p['payment_method'])
                             . ($p['reference'] ? ' · ' . $p['reference'] : ''),
                'debit'   => 0.0,
                'credit'  => (float)$p['amount'],         // reduces owed
                'link_id' => (int)$p['c0d'],
            ];
        }
        usort($ledger, function ($a, $b) {
            $c = strcmp($a['date'], $b['date']);
            return $c !== 0 ? $c : $a['sort_k'] <=> $b['sort_k'];
        });

        // 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);

        // 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);

        // Aging on OPEN bills (posted, not fully paid), using each bill's
        // remaining balance vs its due date. Same buckets as AgedPayables.
        $ageBuckets = ['current' => 0.0, 'd1_30' => 0.0, 'd31_60' => 0.0, 'd61_90' => 0.0, 'd90_plus' => 0.0];
        $openBills = $db->mySelect(
            "SELECT (total_amount - amount_paid) AS balance,
                    DATEDIFF(CURDATE(), COALESCE(due_date, bill_date)) AS days_overdue
               FROM supplier_bills
              WHERE vendor_id = :vid AND deleted = 0
                AND status IN ('posted','paid')
                AND (total_amount - amount_paid) > 0.005",
            [':vid' => $vendorId]
        ) ?: [];
        foreach ($openBills 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 [
            'vendor'        => $v[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($openBills),
        ];
    }

    public function allActive(): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT c0d, vendor_no, vendor_name
               FROM vendors
              WHERE deleted = 0 AND status = 'ACTIVE'
           ORDER BY vendor_name",
            []
        ) ?: [];
    }
}
