<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use App\Helpers\InvoiceHelper;
use App\Helpers\GSTHelper;
use Exception;
use PDO;

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

    public function getAll(int $tenantId, array $filters = [], int $page = 1, int $perPage = 20): array
    {
        $offset = ($page - 1) * $perPage;
        
        $sql = "SELECT e.*, v.name as vendor_name 
                FROM expenses e
                LEFT JOIN vendors v ON e.vendor_id = v.id
                WHERE e.tenant_id = :tenant_id";
        
        $params = [':tenant_id' => $tenantId];

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

        $sql .= " ORDER BY e.expense_date DESC LIMIT $perPage OFFSET $offset";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function getById(int $id, int $tenantId): ?array
    {
        $sql = "SELECT e.*, v.name as vendor_name, c.name as customer_name
                FROM expenses e
                LEFT JOIN vendors v ON e.vendor_id = v.id
                LEFT JOIN customers c ON e.customer_id = c.id
                WHERE e.id = ? AND e.tenant_id = ?";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$id, $tenantId]);
        return $stmt->fetch() ?: null;
    }

    public function createExpense(int $tenantId, array $data, int $userId): array
    {
        $this->db->beginTransaction();

        try {
            $expenseNum = $data['expense_number'] ?? InvoiceHelper::generateAutoNumber('expenses', 'EXP', $tenantId);
            
            $amount = (float)$data['amount'];
            $taxRate = (float)($data['tax_rate'] ?? 0);
            $isTaxInclusive = isset($data['is_tax_inclusive']) ? (bool)$data['is_tax_inclusive'] : false;
            
            $taxAmount = 0;
            $totalAmount = $amount;

            if ($taxRate > 0) {
                if ($isTaxInclusive) {
                    $taxAmount = $amount - ($amount / (1 + ($taxRate / 100)));
                    $amount = $amount - $taxAmount; 
                } else {
                    $taxAmount = ($amount * $taxRate) / 100;
                    $totalAmount = $amount + $taxAmount;
                }
            }

            $sql = "INSERT INTO expenses (
                tenant_id, expense_number, expense_category, vendor_id,
                expense_date, description, amount, tax_amount, total_amount,
                is_tax_inclusive, tax_rate, payment_method, reference_number,
                is_billable, customer_id, notes, status, created_by
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                $tenantId, $expenseNum, $data['expense_category'], $data['vendor_id'] ?? null,
                $data['expense_date'] ?? date('Y-m-d'), $data['description'] ?? null,
                $amount, $taxAmount, $totalAmount,
                $isTaxInclusive ? 1 : 0, $taxRate,
                $data['payment_method'] ?? 'cash', $data['reference_number'] ?? null,
                isset($data['is_billable']) && $data['is_billable'] ? 1 : 0,
                $data['customer_id'] ?? null, $data['notes'] ?? null,
                $data['status'] ?? 'submitted', $userId
            ]);
            
            $expenseId = $this->db->lastInsertId();
            $this->db->commit();
            return ['id' => $expenseId, 'expense_number' => $expenseNum];
        } catch (Exception $e) {
            $this->db->rollBack();
            throw new Exception("Error creating expense: " . $e->getMessage());
        }
    }

    public function getCategories(int $tenantId): array
    {
        $stmt = $this->db->prepare("SELECT DISTINCT expense_category FROM expenses WHERE tenant_id = ? AND expense_category IS NOT NULL ORDER BY expense_category");
        $stmt->execute([$tenantId]);
        return $stmt->fetchAll(PDO::FETCH_COLUMN);
    }
}
