<?php

class Expense extends Model
{
    protected string $table = 'expenses';

    public function listWithAccount(string $dateFrom = '', string $dateTo = ''): array
    {
        $sql = "SELECT e.*, fa.account_name, u.full_name as created_by_name
                FROM expenses e
                JOIN finance_accounts fa ON fa.id = e.finance_account_id
                LEFT JOIN users u ON u.id = e.created_by
                WHERE 1=1";
        $params = [];
        if ($dateFrom !== '') {
            $sql .= " AND e.expense_date >= :date_from";
            $params['date_from'] = $dateFrom;
        }
        if ($dateTo !== '') {
            $sql .= " AND e.expense_date <= :date_to";
            $params['date_to'] = $dateTo;
        }
        $sql .= " ORDER BY e.expense_date DESC, e.id DESC LIMIT 200";

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

    public function monthTotal(): float
    {
        $stmt = $this->db->query(
            "SELECT COALESCE(SUM(amount),0) as total FROM expenses
             WHERE MONTH(expense_date) = MONTH(CURDATE()) AND YEAR(expense_date) = YEAR(CURDATE())"
        );
        return (float) $stmt->fetch()['total'];
    }

    public function createExpense(string $category, float $amount, int $accountId, string $date, string $notes, ?int $userId): int
    {
        $this->db->beginTransaction();
        try {
            $expenseId = $this->insert([
                'expense_category'   => $category,
                'amount'             => $amount,
                'finance_account_id' => $accountId,
                'expense_date'       => $date,
                'notes'              => $notes ?: null,
                'created_by'         => $userId,
            ]);

            $this->db->prepare("UPDATE finance_accounts SET current_balance = current_balance - :amt WHERE id = :id")
                ->execute(['amt' => $amount, 'id' => $accountId]);

            $this->db->prepare(
                "INSERT INTO finance_transactions (finance_account_id, transaction_type, amount, category, reference_type, reference_id, description, created_by)
                 VALUES (:account_id, 'DEBIT', :amount, 'EXPENSE', 'EXPENSE', :expense_id, :description, :user_id)"
            )->execute([
                'account_id'  => $accountId,
                'amount'      => $amount,
                'expense_id'  => $expenseId,
                'description' => "{$category}" . ($notes ? " — {$notes}" : ''),
                'user_id'     => $userId,
            ]);

            $this->db->commit();
            return $expenseId;
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function deleteExpense(int $id): void
    {
        $this->db->beginTransaction();
        try {
            $expense = $this->find($id);
            if ($expense) {
                // Reverse the debit before deleting, so account balance stays accurate
                $this->db->prepare("UPDATE finance_accounts SET current_balance = current_balance + :amt WHERE id = :id")
                    ->execute(['amt' => $expense['amount'], 'id' => $expense['finance_account_id']]);

                $this->db->prepare(
                    "INSERT INTO finance_transactions (finance_account_id, transaction_type, amount, category, reference_type, reference_id, description, created_by)
                     VALUES (:account_id, 'CREDIT', :amount, 'EXPENSE', 'EXPENSE', :expense_id, :description, :user_id)"
                )->execute([
                    'account_id'  => $expense['finance_account_id'],
                    'amount'      => $expense['amount'],
                    'expense_id'  => $id,
                    'description' => "Reversed: {$expense['expense_category']}",
                    'user_id'     => null,
                ]);
            }
            $this->delete($id);
            $this->db->commit();
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }
}
