<?php
/**
 * SupplierPayments — the AP mirror of CustomerReceipts.
 *
 * On record():
 *   Dr 4009 Trade Payables     amount
 *   Cr Bank/Cash/MoMo          amount   (per payment_method map below)
 *
 * The payment can be allocated to one or more bills via the same-shape
 * supplier_payment_allocations table. Bills refresh their amount_paid/
 * status via SupplierBills::refreshPaidState().
 */
class SupplierPayments extends Model
{
    private const AP_ACCOUNT = '4009';   // Trade Payables

    /** Which COA credits get hit when we pay a supplier. Mirror of expenses. */
    public const PAYMENT_METHOD_TO_CREDIT = [
        'bank_transfer' => '3001',   // Bank - CRDB
        'cheque'        => '3001',   // Bank - CRDB
        'mobile_money'  => '3006',   // Mobile Money Float
        'cash'          => '3003',   // Cash
        'fuel_card'     => '3009',   // Fuel Card
    ];

    private function s($v): string { return trim((string)$v); }
    private function m($v): float  { return round((float)preg_replace('/[^\d\.\-]/', '', (string)$v), 2); }

    /* ═══════════════ reads ═══════════════ */

    public function listPaginated(int $page, int $perPage, array $filters = []): array
    {
        $where  = ['sp.deleted = 0', 'sp.tenant_id = :tenant'];
        $params = [':tenant' => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1];

        if (!empty($filters['q'])) {
            $where[] = '(sp.payment_no LIKE :q OR sp.reference LIKE :q OR v.vendor_name LIKE :q)';
            $params[':q'] = '%' . $filters['q'] . '%';
        }
        if (!empty($filters['status']))    { $where[] = 'sp.status = :st'; $params[':st'] = $this->s($filters['status']); }
        if (!empty($filters['vendor_id'])) { $where[] = 'sp.vendor_id = :vid'; $params[':vid'] = (int)$filters['vendor_id']; }
        if (!empty($filters['from_date'])) { $where[] = 'sp.payment_date >= :fd'; $params[':fd'] = $filters['from_date']; }
        if (!empty($filters['to_date']))   { $where[] = 'sp.payment_date <= :td'; $params[':td'] = $filters['to_date']; }
        if (!empty($filters['method']))    { $where[] = 'sp.payment_method = :pm'; $params[':pm'] = $this->s($filters['method']); }

        $whereSql = implode(' AND ', $where);
        $db = new DB();

        $countRs = $db->mySelect("SELECT COUNT(*) AS n FROM supplier_payments sp LEFT JOIN vendors v ON v.c0d = sp.vendor_id WHERE $whereSql", $params);
        $total = (int)($countRs[0]['n'] ?? 0);

        $page    = max(1, $page);
        $perPage = max(1, min(200, $perPage));
        $offset  = ($page - 1) * $perPage;

        $rows = $db->mySelect(
            "SELECT sp.c0d, sp.payment_no, sp.payment_date, sp.amount, sp.amount_allocated,
                    sp.currency, sp.payment_method, sp.reference, sp.status,
                    sp.vendor_id, v.vendor_name
               FROM supplier_payments sp
          LEFT JOIN vendors v ON v.c0d = sp.vendor_id
              WHERE $whereSql
           ORDER BY sp.payment_date DESC, sp.c0d DESC
              LIMIT $perPage OFFSET $offset",
            $params
        ) ?: [];

        return ['rows' => $rows, 'total' => $total];
    }

    public function byId(int $id): array
    {
        $db = new DB();
        $rs = $db->mySelect(
            "SELECT sp.*, v.vendor_name, v.vendor_phone_1, v.vendor_tin
               FROM supplier_payments sp
          LEFT JOIN vendors v ON v.c0d = sp.vendor_id
              WHERE sp.c0d = :id AND sp.deleted = 0 LIMIT 1",
            [':id' => $id]
        );
        return $rs[0] ?? [];
    }

    public function allocationsFor(int $paymentId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT spa.c0d, spa.bill_id, spa.amount_applied,
                    sb.bill_no, sb.supplier_ref, sb.bill_date, sb.total_amount
               FROM supplier_payment_allocations spa
               JOIN supplier_bills sb ON sb.c0d = spa.bill_id
              WHERE spa.payment_id = :pid
           ORDER BY sb.bill_date",
            [':pid' => $paymentId]
        ) ?: [];
    }

    /* ═══════════════ record + allocate ═══════════════ */

    /**
     * Record a payment against a vendor, optionally allocating to specific
     * bills.
     *
     *   $d = [
     *     vendor_id, payment_date, amount, payment_method, reference, notes,
     *     allocations => [[bill_id, amount_applied], ...]   // optional
     *   ]
     */
    public function record(array $d, int $uid, string $uname): array
    {
        $vendorId = (int)($d['vendor_id'] ?? 0);
        if ($vendorId <= 0) return ['ok' => false, 'msg' => 'Pick a supplier.'];

        $amount = $this->m($d['amount'] ?? 0);
        if ($amount <= 0) return ['ok' => false, 'msg' => 'Amount must be positive.'];

        $method = $this->s($d['payment_method'] ?? 'bank_transfer');
        if (!isset(self::PAYMENT_METHOD_TO_CREDIT[$method])) return ['ok' => false, 'msg' => 'Unknown payment method.'];

        $payDate = $this->s($d['payment_date'] ?? '') ?: date('Y-m-d');

        // Validate allocations up front — they must not overpay any bill and
        // must sum to ≤ amount.
        $allocs = [];
        $sumAlloc = 0.0;
        foreach (($d['allocations'] ?? []) as $a) {
            $billId = (int)($a['bill_id'] ?? 0);
            $applied = $this->m($a['amount_applied'] ?? 0);
            if ($billId <= 0 || $applied <= 0) continue;
            $bill = (new SupplierBills())->byId($billId);
            if (!$bill) return ['ok' => false, 'msg' => "Bill $billId not found."];
            $balance = (float)$bill['total_amount'] - (float)$bill['amount_paid'];
            if ($applied > $balance + 0.005) {
                return ['ok' => false, 'msg' => "Bill {$bill['bill_no']} would be overpaid (" .
                    number_format($applied, 2) . ' vs balance ' . number_format($balance, 2) . ').'];
            }
            $allocs[] = ['bill_id' => $billId, 'amount_applied' => $applied];
            $sumAlloc += $applied;
        }
        if ($sumAlloc > $amount + 0.005) return ['ok' => false, 'msg' => 'Allocations exceed payment amount.'];

        // 1. Post the payment JE
        $creditAcc = self::PAYMENT_METHOD_TO_CREDIT[$method];
        $post = (new JournalPosting())->post([
            'tenant_id'   => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1,
            'entry_date'  => $payDate,
            'source_type' => 'SUPPLIER_PAYMENT',
            'source_ref'  => $this->s($d['reference'] ?? ''),
            'narrative'   => 'Supplier payment · ' . $method,
            'posted_by'      => $uid,
            'posted_by_name' => $uname,
            'lines' => [
                ['account_code' => self::AP_ACCOUNT, 'debit' => $amount, 'credit' => 0.0,
                 'narrative' => 'Vendor payment', 'vendor_id' => $vendorId],
                ['account_code' => $creditAcc,       'debit' => 0.0,    'credit' => $amount,
                 'narrative' => 'Paid via ' . $method,'vendor_id' => $vendorId],
            ],
        ]);
        if (!$post['ok']) return ['ok' => false, 'msg' => 'JE post failed: ' . $post['msg']];

        // 2. Persist the payment header
        $db = new DB();
        $paymentNo = $this->nextPaymentNo();
        $ins = $db->insertUpdate(
            "INSERT INTO supplier_payments
                (tenant_id, payment_no, vendor_id, payment_date, amount, amount_allocated,
                 currency, payment_method, reference, status, journal_entry_id,
                 notes, created_by, created_on)
             VALUES
                (:t, :pn, :vid, :pd, :amt, :alloc, 'TZS', :pm, :ref, 'recorded', :je,
                 :notes, :uid, NOW())",
            [
                ':t'     => defined('DEFAULT_TENANT_ID') ? DEFAULT_TENANT_ID : 1,
                ':pn'    => $paymentNo,
                ':vid'   => $vendorId,
                ':pd'    => $payDate,
                ':amt'   => $amount,
                ':alloc' => round($sumAlloc, 2),
                ':pm'    => $method,
                ':ref'   => $this->s($d['reference'] ?? ''),
                ':je'    => (int)$post['id'],
                ':notes' => $this->s($d['notes'] ?? ''),
                ':uid'   => $uid,
            ]
        );
        $paymentId = (int)$ins['last_id'];
        if ($paymentId <= 0) return ['ok' => false, 'msg' => 'Could not persist payment.'];

        // 3. Persist allocations + refresh each bill's paid state
        $bills = new SupplierBills();
        foreach ($allocs as $a) {
            $db->insertUpdate(
                "INSERT INTO supplier_payment_allocations (payment_id, bill_id, amount_applied, created_on)
                 VALUES (:p, :b, :a, NOW())",
                [':p' => $paymentId, ':b' => $a['bill_id'], ':a' => $a['amount_applied']]
            );
            $bills->refreshPaidState($a['bill_id']);
        }

        return ['ok' => true, 'msg' => "Payment recorded ($paymentNo).", 'id' => $paymentId];
    }

    /**
     * Void a payment — post reversing JE, void the payment, refresh the
     * bills it was allocated to.
     */
    public function voidPayment(int $id, string $reason, int $uid, string $uname): array
    {
        $p = $this->byId($id);
        if (!$p) return ['ok' => false, 'msg' => 'Payment not found.'];
        if ($p['status'] === 'void') return ['ok' => false, 'msg' => 'Already void.'];

        $creditAcc = self::PAYMENT_METHOD_TO_CREDIT[$p['payment_method']] ?? '3001';
        $post = (new JournalPosting())->post([
            'tenant_id'   => (int)$p['tenant_id'],
            'entry_date'  => date('Y-m-d'),
            'source_type' => 'SUPPLIER_PAYMENT_VOID',
            'source_id'   => $id,
            'source_ref'  => $p['payment_no'],
            'narrative'   => 'Void payment ' . $p['payment_no'] . ' · ' . $reason,
            'posted_by'      => $uid,
            'posted_by_name' => $uname,
            'lines' => [
                ['account_code' => $creditAcc,       'debit' => (float)$p['amount'], 'credit' => 0.0,
                 'narrative' => 'Reversal ' . $p['payment_no'], 'vendor_id' => (int)$p['vendor_id']],
                ['account_code' => self::AP_ACCOUNT, 'debit' => 0.0,                 'credit' => (float)$p['amount'],
                 'narrative' => 'Reversal ' . $p['payment_no'], 'vendor_id' => (int)$p['vendor_id']],
            ],
        ]);
        if (!$post['ok']) return ['ok' => false, 'msg' => 'Reversal JE failed: ' . $post['msg']];

        $db = new DB();
        $db->insertUpdate(
            "UPDATE supplier_payments SET
                status = 'void', void_by = :uid, void_at = NOW(),
                void_reason = :r
              WHERE c0d = :id",
            [':uid' => $uid, ':r' => $reason, ':id' => $id]
        );

        // Refresh every bill this payment touched — the allocations still
        // exist as history but the joined `sp.status != 'void'` filter in
        // refreshPaidState now excludes them.
        $affected = $db->mySelect(
            "SELECT DISTINCT bill_id FROM supplier_payment_allocations WHERE payment_id = :p",
            [':p' => $id]
        );
        $bills = new SupplierBills();
        foreach ($affected as $row) $bills->refreshPaidState((int)$row['bill_id']);

        return ['ok' => true, 'msg' => 'Payment voided.'];
    }

    /* ═══════════════ helpers ═══════════════ */

    private function nextPaymentNo(): string
    {
        $db = new DB();
        $yr = date('Y');
        $rs = $db->mySelect(
            "SELECT payment_no FROM supplier_payments
              WHERE payment_no LIKE :p ORDER BY payment_no DESC LIMIT 1",
            [':p' => "PAY-$yr-%"]
        );
        $n = 1;
        if (!empty($rs) && preg_match('/PAY-\d{4}-(\d+)/', $rs[0]['payment_no'], $m)) {
            $n = (int)$m[1] + 1;
        }
        return sprintf('PAY-%s-%04d', $yr, $n);
    }
}
