<?php

namespace App\Models;

use CodeIgniter\Model;

class PaymentTransModel extends Model
{
    protected $table = 'payment_trans';
    protected $primaryKey = 'id';
    protected $useAutoIncrement = true;
    protected $returnType = 'array';
    protected $useSoftDeletes = false;
    protected $allowedFields = [
        'payment_annual_id', 'member_id', 'transaction_no', 'amount',
        'payment_method', 'bank_name', 'bank_ref', 'payment_date',
        'status', 'notes'
    ];
    protected $useTimestamps = true;
    protected $createdField = 'created_at';
    protected $updatedField = 'updated_at';

    protected $validationRules = [
        'member_id' => 'required|numeric',
        'transaction_no' => 'required|is_unique[payment_trans.transaction_no]',
        'amount' => 'required|numeric|greater_than[0]',
        'payment_date' => 'required|valid_date[Y-m-d]',
    ];

    public function getTransactionByMember($memberId, $limit = 20)
    {
        return $this->where('member_id', $memberId)
            ->orderBy('payment_date', 'DESC')
            ->limit($limit)
            ->findAll();
    }

    public function getUnmatchedTransactions()
    {
        return $this->where('status', 'pending')
            ->orWhere('status', 'confirmed')
            ->findAll();
    }

    public function getTotalPaidByMember($memberId)
    {
        $result = $this->selectSum('amount')
            ->where('member_id', $memberId)
            ->where('status', 'matched')
            ->first();
        
        return $result['amount'] ?? 0;
    }

    public function matchWithInvoice($transactionId, $invoiceId)
    {
        return $this->update($transactionId, [
            'payment_annual_id' => $invoiceId,
            'status' => 'matched'
        ]);
    }

    public function getTransactionsByBankRef($bankRef)
    {
        return $this->where('bank_ref', $bankRef)->findAll();
    }

    public function getRecentTransactions($days = 30)
    {
        $date = date('Y-m-d', strtotime("-$days days"));
        return $this->where('payment_date >=', $date)
            ->orderBy('payment_date', 'DESC')
            ->findAll();
    }
}
