<?php

namespace App\Models;

use App\Core\Model;
use PDO;

class Expense extends Model {
    public function search($params = []) {
        $sql = "SELECT e.*, ec.name as category_name 
                FROM expenses e 
                JOIN expense_categories ec ON e.expense_category_id = ec.id 
                WHERE 1=1";
        $binds = [];

        if (!empty($params['search'])) {
            $sql .= " AND (e.title LIKE :search OR e.description LIKE :search)";
            $binds['search'] = '%' . $params['search'] . '%';
        }

        if (!empty($params['category_id'])) {
            $sql .= " AND e.expense_category_id = :category_id";
            $binds['category_id'] = $params['category_id'];
        }

        $sql .= " ORDER BY e.date DESC, e.id DESC";
        
        $stmt = $this->db->prepare($sql);
        if (!empty($binds)) {
            $stmt->execute($binds);
        } else {
            $stmt->execute();
        }
        return $stmt->fetchAll();
    }

    public function find($id) {
        $stmt = $this->db->prepare("SELECT * FROM expenses WHERE id = :id LIMIT 1");
        $stmt->execute(['id' => $id]);
        return $stmt->fetch();
    }

    public function save($data) {
        if (!empty($data['id'])) {
            $sql = "UPDATE expenses SET 
                    expense_category_id = :expense_category_id, title = :title, 
                    amount = :amount, date = :date, description = :description, 
                    attachment = :attachment, payment_method = :payment_method 
                    WHERE id = :id";
            $stmt = $this->db->prepare($sql);
            return $stmt->execute([
                'expense_category_id' => $data['expense_category_id'],
                'title' => $data['title'],
                'amount' => $data['amount'] ?? 0,
                'date' => $data['date'],
                'description' => $data['description'] ?? null,
                'attachment' => $data['attachment'] ?? null,
                'payment_method' => $data['payment_method'] ?? 'Cash',
                'id' => $data['id']
            ]);
        } else {
            $sql = "INSERT INTO expenses (expense_category_id, title, amount, date, description, attachment, payment_method) 
                    VALUES (:expense_category_id, :title, :amount, :date, :description, :attachment, :payment_method)";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                'expense_category_id' => $data['expense_category_id'],
                'title' => $data['title'],
                'amount' => $data['amount'] ?? 0,
                'date' => $data['date'],
                'description' => $data['description'] ?? null,
                'attachment' => $data['attachment'] ?? null,
                'payment_method' => $data['payment_method'] ?? 'Cash'
            ]);
            return $this->db->lastInsertId();
        }
    }

    public function delete($id) {
        $expense = $this->find($id);
        if ($expense && $expense['attachment']) {
            $filePath = __DIR__ . '/../../public' . $expense['attachment'];
            if (file_exists($filePath)) unlink($filePath);
        }
        $stmt = $this->db->prepare("DELETE FROM expenses WHERE id = :id");
        return $stmt->execute(['id' => $id]);
    }
}
