<?php
require_once __DIR__ . '/../config/Database.php';

class Expense {
    private $db;
    private $conn;
    
    public function __construct() {
        $this->db = new Database();
        $this->conn = $this->db->getConnection();
    }
    
    public function getAllExpenses($filters = []) {
        $query = "SELECT * FROM expenses WHERE 1=1";
        
        if (isset($filters['start_date'])) {
            $query .= " AND DATE(expense_date) >= '" . $this->db->escape($filters['start_date']) . "'";
        }
        if (isset($filters['end_date'])) {
            $query .= " AND DATE(expense_date) <= '" . $this->db->escape($filters['end_date']) . "'";
        }
        if (isset($filters['category'])) {
            $query .= " AND category = '" . $this->db->escape($filters['category']) . "'";
        }
        
        $query .= " ORDER BY expense_date DESC";
        
        $result = $this->conn->query($query);
        return $result->fetch_all(MYSQLI_ASSOC);
    }
    
    public function createExpense($data) {
        $query = "INSERT INTO expenses (id, category, amount, expense_date, description, payed_by, receipt_file) 
                  VALUES (?, ?, ?, ?, ?, ?, ?)";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bind_param("ssdssss", $data['id'], $data['category'], $data['amount'], 
                          $data['expense_date'], $data['description'], $data['payed_by'], $data['receipt_file']);
        
        return $stmt->execute();
    }
    
    public function updateExpense($expenseId, $data) {
        $query = "UPDATE expenses SET category=?, amount=?, expense_date=?, description=?, status=? WHERE id=?";
        $stmt = $this->conn->prepare($query);
        $stmt->bind_param("sdsss", $data['category'], $data['amount'], $data['expense_date'], 
                          $data['description'], $data['status'], $expenseId);
        return $stmt->execute();
    }
    
    public function getExpenseStats($startDate = null, $endDate = null) {
        if (!$startDate) $startDate = date('Y-m-01');
        if (!$endDate) $endDate = date('Y-m-d');
        
        $query = "SELECT 
                    category,
                    COUNT(*) as count,
                    SUM(amount) as total
                  FROM expenses 
                  WHERE DATE(expense_date) BETWEEN ? AND ? AND status = 'approved'
                  GROUP BY category
                  ORDER BY total DESC";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bind_param("ss", $startDate, $endDate);
        $stmt->execute();
        return $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
    }
    
    public function getTotalExpenses($startDate = null, $endDate = null) {
        if (!$startDate) $startDate = date('Y-m-01');
        if (!$endDate) $endDate = date('Y-m-d');
        
        $query = "SELECT SUM(amount) as total FROM expenses 
                  WHERE DATE(expense_date) BETWEEN ? AND ? AND status = 'approved'";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bind_param("ss", $startDate, $endDate);
        $stmt->execute();
        return $stmt->get_result()->fetch_assoc()['total'] ?? 0;
    }
}
?>
