<?php

namespace App\Modules\Finance\Controllers;

use Core\Controller;
use Core\Auth;
use App\Modules\Finance\Models\BudgetModel;

class BudgetController extends Controller
{
    private BudgetModel $budgetModel;

    public function __construct()
    {
        parent::__construct();
        $this->requireAuth();
        $this->budgetModel = new BudgetModel();
    }

    public function index(int $projectId)
    {
        $project = $this->dbQueryOne("SELECT * FROM projects WHERE id = ? AND org_id = ?", [$projectId, Auth::orgId()]);
        if (!$project) {
            $this->setFlash('error', 'Project not found.');
            $this->redirect(APP_URL . '/projects');
        }

        $budgetItems = $this->dbQuery(
            "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 = ? ORDER BY b.category, b.item_name",
            [$projectId]
        );

        $revenue = (float)($this->dbQueryOne("SELECT total_revenue FROM financial_analyses WHERE project_id = ?", [$projectId])['total_revenue'] ?? $project['project_value'] ?? 0);

        $summary = [
            'revenue' => $revenue,
            'total_budget' => array_sum(array_column($budgetItems, 'total_budget')),
            'total_actual' => array_sum(array_column($budgetItems, 'total_actual')),
        ];
        $summary['variance'] = $summary['total_budget'] - $summary['total_actual'];
        $summary['margin_percent'] = $revenue > 0 ? round((($revenue - $summary['total_actual']) / $revenue) * 100, 2) : 0;

        $categoryActuals = [];
        foreach ($budgetItems as $item) {
            $categoryActuals[$item['category']] = ($categoryActuals[$item['category']] ?? 0) + (float)$item['total_actual'];
        }

        $categoryBreakdown = [];
        foreach ($budgetItems as $item) {
            $categoryBreakdown[$item['category']] = ($categoryBreakdown[$item['category']] ?? 0) + (float)$item['total_budget'];
        }

        $this->render('finance/budget/index', [
            'pageTitle'   => 'Budget — ' . $project['name'],
            'project'     => $project,
            'project_id'  => $projectId,
            'budgetItems' => $budgetItems,
            'summary'     => $summary,
            'categoryBreakdown' => $categoryBreakdown,
            'categoryActuals'   => $categoryActuals,
            'breadcrumb'  => [
                ['label' => 'Projects', 'url' => APP_URL . '/projects'],
                ['label' => $project['name'], 'url' => APP_URL . '/projects/' . $projectId],
                ['label' => 'Budget'],
            ],
        ]);
    }

    public function storeItem(int $projectId)
    {
        if (!$this->validateCsrf()) {
            $this->setFlash('error', 'Invalid security token.');
            $this->redirect(APP_URL . '/finance/budgets/' . $projectId);
        }

        $project = $this->dbQueryOne("SELECT id FROM projects WHERE id = ? AND org_id = ?", [$projectId, Auth::orgId()]);
        if (!$project) {
            $this->setFlash('error', 'Project not found.');
            $this->redirect(APP_URL . '/projects');
        }

        $qty      = (float) $this->getPost('quantity', 0);
        $rate     = (float) $this->getPost('unit_rate', 0);
        $total    = $qty * $rate;

        $this->dbExecute(
            "INSERT INTO budget_items (project_id, category, item_name, unit, quantity, unit_rate, total_budget, notes, created_by, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())",
            [
                $projectId,
                $this->getPost('category', 'General'),
                $this->getPost('item_name', ''),
                $this->getPost('unit', 'lump sum'),
                $qty,
                $rate,
                $total,
                $this->getPost('notes', ''),
                Auth::id(),
            ]
        );

        $this->setFlash('success', 'Budget item added.');
        $this->redirect(APP_URL . '/finance/budgets/' . $projectId);
    }

    public function updateItem(int $id)
    {
        if (!$this->validateCsrf()) {
            $this->setFlash('error', 'Invalid security token.');
            $this->redirect(APP_URL . '/projects');
        }

        $item = $this->dbQueryOne(
            "SELECT b.project_id FROM budget_items b INNER JOIN projects p ON b.project_id = p.id WHERE b.id = ? AND p.org_id = ?",
            [$id, Auth::orgId()]
        );
        if (!$item) {
            $this->setFlash('error', 'Budget item not found.');
            $this->redirect(APP_URL . '/projects');
        }

        $qty   = (float) $this->getPost('quantity', 0);
        $rate  = (float) $this->getPost('unit_rate', 0);
        $total = $qty * $rate;

        $this->dbExecute(
            "UPDATE budget_items SET category=?, item_name=?, unit=?, quantity=?, unit_rate=?, total_budget=?, notes=?, updated_at=NOW() WHERE id=?",
            [
                $this->getPost('category'),
                $this->getPost('item_name'),
                $this->getPost('unit'),
                $qty, $rate, $total,
                $this->getPost('notes'),
                $id,
            ]
        );

        $this->setFlash('success', 'Budget item updated.');
        $this->redirect(APP_URL . '/finance/budgets/' . $item['project_id']);
    }

    public function deleteItem(int $id)
    {
        if (!$this->validateCsrf()) {
            $this->setFlash('error', 'Invalid security token.');
            $this->redirect(APP_URL . '/projects');
        }

        $item = $this->dbQueryOne(
            "SELECT b.project_id FROM budget_items b INNER JOIN projects p ON b.project_id = p.id WHERE b.id = ? AND p.org_id = ?",
            [$id, Auth::orgId()]
        );
        if (!$item) {
            $this->setFlash('error', 'Budget item not found.');
            $this->redirect(APP_URL . '/projects');
        }
        $this->dbExecute("DELETE FROM budget_items WHERE id = ?", [$id]);
        $this->setFlash('success', 'Budget item removed.');
        $this->redirect(APP_URL . '/finance/budgets/' . ($item['project_id'] ?? 0));
    }
}
