<?php

namespace App\Models;

class FinanceTransactionModel extends BaseModel
{
    protected $table      = 'finance_transactions';
    protected $primaryKey = 'id';

    protected $allowedFields = [
        'uuid', 'church_id', 'category_id', 'recorded_by', 'type',
        'amount', 'description', 'reference', 'transaction_date',
    ];

    public function getSummary(int $churchId, ?string $from, ?string $to): array
    {
        $db   = \Config\Database::connect();
        $base = $db->table('finance_transactions')
                   ->where('church_id', $churchId)
                   ->where('deleted_at IS NULL');

        if ($from) {
            $base->where('transaction_date >=', $from);
        }
        if ($to) {
            $base->where('transaction_date <=', $to);
        }

        $income  = (clone $base)->where('type', 'income')->selectSum('amount', 'total')->get()->getRowArray()['total'] ?? 0;
        $expense = (clone $base)->where('type', 'expense')->selectSum('amount', 'total')->get()->getRowArray()['total'] ?? 0;

        return [
            'income'  => (float) $income,
            'expense' => (float) $expense,
            'net'     => (float) $income - (float) $expense,
        ];
    }
}
