<?php

namespace App\Models;

use CodeIgniter\Model;

class PaymentAnnualModel extends Model
{
    public const VISIBLE_FEE_TYPE = 'AWTF';

    protected $table = 'payment_annual';
    protected $primaryKey = 'id';
    protected $useAutoIncrement = true;
    protected $returnType = 'array';
    protected $useSoftDeletes = false;
    protected $allowedFields = [
        'member_id', 'fee_year', 'reference_no', 'fee_type', 'account',
        'fee', 'paystatus', 'created_at', 'duedate', 'updated_at',
        'invoice_group', 'installment',
    ];
    protected $useTimestamps = true;
    protected $createdField = 'created_at';
    protected $updatedField = 'updated_at';

    protected $validationRules = [
        'member_id' => 'required|numeric',
        'fee_year' => 'required|numeric|exact_length[4]',
        'fee' => 'required|numeric|greater_than[0]',
    ];

    public function generateReferenceNo()
    {
        $timestamp = time();
        $random = mt_rand(1000, 9999);
        return "INV" . $timestamp . $random;
    }

    public function getInvoiceByMember($memberId, $year = null)
    {
        $query = $this->where('member_id', $memberId)
            ->where('fee_type', self::VISIBLE_FEE_TYPE);
        if ($year) {
            $query = $query->where('fee_year', $year);
        }
        return $query->orderBy('fee_year', 'DESC')
            ->orderBy('id', 'DESC')
            ->findAll();
    }

    public function getPaidInvoicesByMember($memberId)
    {
        return $this->where('member_id', $memberId)
            ->where('fee_type', self::VISIBLE_FEE_TYPE)
            ->where('paystatus', 'PAID')
            ->findAll();
    }

    public function getPendingInvoicesByMember($memberId)
    {
        return $this->where('member_id', $memberId)
            ->where('fee_type', self::VISIBLE_FEE_TYPE)
            ->groupStart()
            ->where('paystatus !=', 'PAID')
            ->orWhere('paystatus', null)
            ->groupEnd()
            ->findAll();
    }

    public function getTotalPaidByMember($memberId)
    {
        $result = $this->selectSum('fee')
            ->where('member_id', $memberId)
            ->where('fee_type', self::VISIBLE_FEE_TYPE)
            ->where('paystatus', 'PAID')
            ->first();
        
        return $result['fee'] ?? 0;
    }

    public function getTotalOutstandingByMember($memberId)
    {
        $result = $this->selectSum('fee')
            ->where('member_id', $memberId)
            ->where('fee_type', self::VISIBLE_FEE_TYPE)
            ->groupStart()
            ->where('paystatus !=', 'PAID')
            ->orWhere('paystatus', null)
            ->groupEnd()
            ->first();
        
        return $result['fee'] ?? 0;
    }

    public function getOutstandingInvoices()
    {
        return $this->groupStart()
            ->where('paystatus !=', 'PAID')
            ->orWhere('paystatus', null)
            ->groupEnd()
            ->where('fee_type', self::VISIBLE_FEE_TYPE)
            ->findAll();
    }
}
