<?php

namespace ChurchCRM\model\ChurchCRM;

use PDO;

class ExpenseQuery
{
    private array $filters = [];
    private ?PDO $connection = null;
    private static ?self $instance = null;

    public function __construct()
    {
        global $database;
        $this->connection = $database;
    }

    public static function create(): self
    {
        return new self();
    }

    public function filterByCategory(int $categoryId): self
    {
        $this->filters['exp_Category'] = $categoryId;
        return $this;
    }

    public function filterByFund(int $fundId): self
    {
        $this->filters['exp_Fund'] = $fundId;
        return $this;
    }

    public function filterByStatus(string $status): self
    {
        $this->filters['exp_Status'] = $status;
        return $this;
    }

    public function filterByDate(array $dateRange): self
    {
        $this->filters['exp_Date'] = $dateRange;
        return $this;
    }

    public function find(): array
    {
        $sql = "SELECT * FROM expense_exp WHERE 1=1";
        $params = [];

        foreach ($this->filters as $field => $value) {
            if ($field === 'exp_Date' && is_array($value)) {
                if (isset($value['min'])) {
                    $sql .= " AND exp_Date >= ?";
                    $params[] = $value['min'];
                }
                if (isset($value['max'])) {
                    $sql .= " AND exp_Date <= ?";
                    $params[] = $value['max'];
                }
            } else {
                $sql .= " AND $field = ?";
                $params[] = $value;
            }
        }

        $stmt = $this->connection->prepare($sql);
        $stmt->execute($params);

        $expenses = [];
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $expense = $this->hydrateExpense($row);
            $expenses[] = $expense;
        }

        return $expenses;
    }

    private function hydrateExpense(array $data): Expense
    {
        $expense = new Expense();
        $expense->setId($data['exp_ID'] ?? null);
        $expense->setDate($data['exp_Date'] ?? null);
        $expense->setAmount($data['exp_Amount'] ?? null);
        $expense->setCategory($data['exp_Category'] ?? null);
        $expense->setFund($data['exp_Fund'] ?? null);
        $expense->setVendor($data['exp_Vendor'] ?? null);
        $expense->setDescription($data['exp_Description'] ?? null);
        $expense->setReference($data['exp_Reference'] ?? null);
        $expense->setStatus($data['exp_Status'] ?? 'Pending');
        $expense->setPaymentMethod($data['exp_PaymentMethod'] ?? 'Check');
        $expense->setNotes($data['exp_Notes'] ?? null);
        $expense->setEnteredBy($data['exp_EnteredBy'] ?? null);
        $expense->setApprovedBy($data['exp_ApprovedBy'] ?? null);

        if (!empty($data['exp_CreatedDate'])) {
            $expense->setCreatedDate(new \DateTime($data['exp_CreatedDate']));
        }
        if (!empty($data['exp_DateLastEdited'])) {
            $expense->setDateLastEdited(new \DateTime($data['exp_DateLastEdited']));
        }

        return $expense;
    }
}
