<?php

namespace App\Modules\Finance\Models;

class BudgetModel
{
    private \PDO $db;

    public function __construct()
    {
        $this->db = \Database::getInstance();
    }

    public function getForProject(int $projectId): array
    {
        $stmt = $this->db->prepare(
            "SELECT b.*, COALESCE((SELECT SUM(amount) FROM expenses e WHERE e.budget_item_id = b.id), 0) as total_actual
             FROM budget_items b WHERE b.project_id = :project_id ORDER BY b.category, b.item_name"
        );
        $stmt->execute([':project_id' => $projectId]);
        return $stmt->fetchAll();
    }

    public function getBudgetSummary(int $projectId): array
    {
        $items = $this->getForProject($projectId);
        $totalBudget = array_sum(array_column($items, 'total_budget'));
        $totalActual = array_sum(array_column($items, 'total_actual'));

        $stmt = $this->db->prepare("SELECT total_revenue FROM financial_analyses WHERE project_id = :project_id");
        $stmt->execute([':project_id' => $projectId]);
        $revenue = (float)($stmt->fetchColumn() ?: 0);

        $variance = $totalBudget - $totalActual;
        $margin = $revenue > 0 ? round((($revenue - $totalActual) / $revenue) * 100, 2) : 0;

        return [
            'total_budget' => $totalBudget,
            'total_actual' => $totalActual,
            'variance' => $variance,
            'margin_percent' => $margin,
            'revenue' => $revenue,
        ];
    }

    public function getCategoryBreakdown(int $projectId): array
    {
        $breakdown = [];
        foreach ($this->getForProject($projectId) as $item) {
            $breakdown[$item['category']] = ($breakdown[$item['category']] ?? 0) + (float)$item['total_budget'];
        }
        return $breakdown;
    }

    public function addBudgetItem(array $data): int
    {
        $stmt = $this->db->prepare(
            "INSERT INTO budget_items (project_id, category, item_name, unit, quantity, unit_rate, total_budget, notes, created_by, created_at, updated_at)
             VALUES (:project_id, :category, :item_name, :unit, :quantity, :unit_rate, :total_budget, :notes, :created_by, NOW(), NOW())"
        );
        $stmt->execute([
            ':project_id' => $data['project_id'],
            ':category' => $data['category'],
            ':item_name' => $data['item_name'],
            ':unit' => $data['unit'] ?? null,
            ':quantity' => $data['quantity'] ?? 0,
            ':unit_rate' => $data['unit_rate'] ?? 0,
            ':total_budget' => $data['total_budget'] ?? 0,
            ':notes' => $data['notes'] ?? null,
            ':created_by' => $data['created_by'],
        ]);
        return (int)$this->db->lastInsertId();
    }

    public function updateBudgetItem(int $id, array $data): bool
    {
        $stmt = $this->db->prepare(
            "UPDATE budget_items SET category=:category, item_name=:item_name, unit=:unit, quantity=:quantity, unit_rate=:unit_rate, total_budget=:total_budget, notes=:notes, updated_at=NOW() WHERE id=:id"
        );
        return $stmt->execute([
            ':category' => $data['category'],
            ':item_name' => $data['item_name'],
            ':unit' => $data['unit'] ?? null,
            ':quantity' => $data['quantity'] ?? 0,
            ':unit_rate' => $data['unit_rate'] ?? 0,
            ':total_budget' => $data['total_budget'] ?? 0,
            ':notes' => $data['notes'] ?? null,
            ':id' => $id,
        ]);
    }

    public function deleteBudgetItem(int $id): bool
    {
        $stmt = $this->db->prepare("DELETE FROM budget_items WHERE id = :id");
        return $stmt->execute([':id' => $id]);
    }
}
