<?php

namespace ChurchCRM\Service;

use ChurchCRM\Utils\DateTimeUtils;
use Propel\Runtime\Propel;

/**
 * ExpenseService - Manages church expenses and withdrawals
 */
class ExpenseService
{
    private $pdo;

    public function __construct()
    {
        $this->pdo = Propel::getConnection();
    }

    /**
     * Create a new expense
     */
    public function createExpense($data)
    {
        $sql = "INSERT INTO expense_exp (
            exp_Date, exp_Amount, exp_Category, exp_Fund, exp_Vendor,
            exp_Description, exp_Reference, exp_Status, exp_PaymentMethod,
            exp_Notes, exp_EnteredBy
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([
            $data['date'],
            $data['amount'],
            $data['category'],
            $data['fund'] ?? null,
            $data['vendor'] ?? null,
            $data['description'] ?? null,
            $data['reference'] ?? null,
            $data['status'] ?? 'Pending',
            $data['payment_method'] ?? 'Check',
            $data['notes'] ?? null,
            $data['entered_by']
        ]);
    }

    /**
     * Get all expenses with optional filters
     */
    public function getExpenses($filters = [])
    {
        $sql = "SELECT e.*, c.cat_Name, f.fun_Name
                FROM expense_exp e
                LEFT JOIN expense_category_cat c ON e.exp_Category = c.cat_ID
                LEFT JOIN donationfund_fun f ON e.exp_Fund = f.fun_ID
                WHERE 1=1";

        $params = [];

        if (!empty($filters['category'])) {
            $sql .= " AND e.exp_Category = ?";
            $params[] = $filters['category'];
        }

        if (!empty($filters['status'])) {
            $sql .= " AND e.exp_Status = ?";
            $params[] = $filters['status'];
        }

        if (!empty($filters['fund'])) {
            $sql .= " AND e.exp_Fund = ?";
            $params[] = $filters['fund'];
        }

        if (!empty($filters['start_date'])) {
            $sql .= " AND e.exp_Date >= ?";
            $params[] = $filters['start_date'];
        }

        if (!empty($filters['end_date'])) {
            $sql .= " AND e.exp_Date <= ?";
            $params[] = $filters['end_date'];
        }

        $sql .= " ORDER BY e.exp_Date DESC";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    /**
     * Get expense by ID
     */
    public function getExpenseById($id)
    {
        $sql = "SELECT e.*, c.cat_Name, f.fun_Name
                FROM expense_exp e
                LEFT JOIN expense_category_cat c ON e.exp_Category = c.cat_ID
                LEFT JOIN donationfund_fun f ON e.exp_Fund = f.fun_ID
                WHERE e.exp_ID = ?";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([$id]);
        return $stmt->fetch();
    }

    /**
     * Update expense
     */
    public function updateExpense($id, $data)
    {
        $fields = [];
        $params = [];

        foreach ($data as $key => $value) {
            $fields[] = "exp_$key = ?";
            $params[] = $value;
        }

        $fields[] = "exp_DateLastEdited = NOW()";
        $params[] = $id;

        $sql = "UPDATE expense_exp SET " . implode(', ', $fields) . " WHERE exp_ID = ?";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute($params);
    }

    /**
     * Delete expense
     */
    public function deleteExpense($id)
    {
        $sql = "DELETE FROM expense_exp WHERE exp_ID = ?";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([$id]);
    }

    /**
     * Approve expense
     */
    public function approveExpense($id, $userId, $notes = '')
    {
        $this->updateExpense($id, ['Status' => 'Approved', 'ApprovedBy' => $userId]);
        $this->logApproval($id, 'Approved', $userId, $notes);
        return true;
    }

    /**
     * Reject expense
     */
    public function rejectExpense($id, $userId, $notes = '')
    {
        $this->updateExpense($id, ['Status' => 'Rejected', 'ApprovedBy' => $userId]);
        $this->logApproval($id, 'Rejected', $userId, $notes);
        return true;
    }

    /**
     * Mark as paid
     */
    public function markAsPaid($id, $userId, $notes = '')
    {
        $this->updateExpense($id, ['Status' => 'Paid']);
        $this->logApproval($id, 'Paid', $userId, $notes);
        return true;
    }

    /**
     * Log approval action
     */
    private function logApproval($expenseId, $action, $userId, $notes)
    {
        $sql = "INSERT INTO expense_approval_log_eal (eal_ExpenseID, eal_Action, eal_UserId, eal_Notes)
                VALUES (?, ?, ?, ?)";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([$expenseId, $action, $userId, $notes]);
    }

    /**
     * Get total expenses by category
     */
    public function getTotalByCategory($categoryId, $startDate = null, $endDate = null)
    {
        $sql = "SELECT SUM(exp_Amount) as total FROM expense_exp
                WHERE exp_Category = ?";
        $params = [$categoryId];

        if ($startDate) {
            $sql .= " AND exp_Date >= ?";
            $params[] = $startDate;
        }
        if ($endDate) {
            $sql .= " AND exp_Date <= ?";
            $params[] = $endDate;
        }

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $result = $stmt->fetch();
        return $result['total'] ?? 0;
    }

    /**
     * Get total expenses by fund
     */
    public function getTotalByFund($fundId, $startDate = null, $endDate = null)
    {
        $sql = "SELECT SUM(exp_Amount) as total FROM expense_exp
                WHERE exp_Fund = ?";
        $params = [$fundId];

        if ($startDate) {
            $sql .= " AND exp_Date >= ?";
            $params[] = $startDate;
        }
        if ($endDate) {
            $sql .= " AND exp_Date <= ?";
            $params[] = $endDate;
        }

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $result = $stmt->fetch();
        return $result['total'] ?? 0;
    }

    /**
     * Get budget information
     */
    public function getBudget($year, $categoryId = null, $fundId = null)
    {
        $sql = "SELECT b.*, c.cat_Name, f.fun_Name FROM expense_budget_bud b
                LEFT JOIN expense_category_cat c ON b.bud_Category = c.cat_ID
                LEFT JOIN donationfund_fun f ON b.bud_Fund = f.fun_ID
                WHERE b.bud_Year = ?";
        $params = [$year];

        if ($categoryId) {
            $sql .= " AND b.bud_Category = ?";
            $params[] = $categoryId;
        }
        if ($fundId) {
            $sql .= " AND b.bud_Fund = ?";
            $params[] = $fundId;
        }

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    /**
     * Save budget
     */
    public function saveBudget($year, $categoryId, $fundId, $amount)
    {
        $sql = "INSERT INTO expense_budget_bud (bud_Year, bud_Category, bud_Fund, bud_Amount)
                VALUES (?, ?, ?, ?)
                ON DUPLICATE KEY UPDATE bud_Amount = ?";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute([$year, $categoryId, $fundId, $amount, $amount]);
    }

    /**
     * Get all expense categories
     */
    public function getCategories()
    {
        $sql = "SELECT * FROM expense_category_cat WHERE cat_Active = 'true' ORDER BY cat_Order";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    /**
     * Get dashboard summary (total income vs expenses)
     */
    public function getDashboardSummary($startDate = null, $endDate = null)
    {
        $filters = [];
        if ($startDate) {
            $filters['start_date'] = $startDate;
        }
        if ($endDate) {
            $filters['end_date'] = $endDate;
        }

        $totalExpenses = 0;
        $expenses = $this->getExpenses($filters);
        foreach ($expenses as $expense) {
            if ($expense['exp_Status'] !== 'Rejected') {
                $totalExpenses += $expense['exp_Amount'];
            }
        }

        return [
            'total_expenses' => $totalExpenses,
            'expense_count' => count($expenses),
            'by_category' => $this->getExpensesByCategory($startDate, $endDate),
            'pending_approval' => $this->getPendingApproval()
        ];
    }

    /**
     * Get expenses grouped by category
     */
    private function getExpensesByCategory($startDate = null, $endDate = null)
    {
        $sql = "SELECT c.cat_ID, c.cat_Name, SUM(e.exp_Amount) as total, COUNT(*) as count
                FROM expense_exp e
                JOIN expense_category_cat c ON e.exp_Category = c.cat_ID
                WHERE e.exp_Status != 'Rejected'";
        $params = [];

        if ($startDate) {
            $sql .= " AND e.exp_Date >= ?";
            $params[] = $startDate;
        }
        if ($endDate) {
            $sql .= " AND e.exp_Date <= ?";
            $params[] = $endDate;
        }

        $sql .= " GROUP BY c.cat_ID ORDER BY total DESC";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    /**
     * Get pending approval expenses
     */
    public function getPendingApproval()
    {
        $sql = "SELECT COUNT(*) as count FROM expense_exp WHERE exp_Status = 'Pending'";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        $result = $stmt->fetch();
        return $result['count'] ?? 0;
    }
}
