<?php
namespace Modules\Projects\Models;

use Core\Model;
use PhpOffice\PhpSpreadsheet\IOFactory;

class FinancialAnalysisModel extends Model
{
    protected $table = 'financial_analyses';

    public function parseExcelDocument(string $filePath, int $projectId, int $userId): bool
    {
        try {
            $spreadsheet = IOFactory::load($filePath);
            $worksheet = $spreadsheet->getActiveSheet();
            $rows = $worksheet->toArray();

            $project = $this->queryOne("SELECT name, project_value FROM projects WHERE id = ?", [$projectId]);

            $existing = $this->findBy('project_id', $projectId);
            if ($existing) {
                $this->execute("DELETE FROM financial_analysis_items WHERE analysis_id = ?", [$existing['id']]);
                $analysisId = (int)$existing['id'];
            } else {
                $analysisId = $this->create([
                    'project_id' => $projectId,
                    'analysis_name' => 'Financial Analysis - ' . ($project['name'] ?? ('Project #' . $projectId)),
                    'total_revenue' => (float)($project['project_value'] ?? 0),
                    'total_budget' => 0,
                    'created_by' => $userId,
                ]);
            }

            $totalBudget = 0;
            $sortOrder = 0;
            foreach ($rows as $index => $row) {
                if ($index === 0) {
                    continue; // skip header
                }
                if (empty($row[1])) {
                    continue; // empty item name
                }

                $quantity = floatval($row[3] ?? 0);
                $unitRate = floatval($row[4] ?? 0);
                $budget = $quantity * $unitRate;
                $totalBudget += $budget;

                $this->addItem($analysisId, [
                    'category' => $row[0] ?? 'General',
                    'item_name' => $row[1],
                    'unit' => $row[2] ?? '',
                    'quantity' => $quantity,
                    'unit_rate' => $unitRate,
                    'total_budget' => $budget,
                    'sort_order' => $sortOrder++,
                ]);
            }

            $this->update($analysisId, [
                'total_budget' => $totalBudget,
                'total_revenue' => (float)($project['project_value'] ?? 0),
            ]);
            return true;
        } catch (\Throwable $e) {
            error_log('Financial analysis parse failed: ' . $e->getMessage());
            return false;
        }
    }

    public function getAnalysis(int $projectId): ?array
    {
        $analysis = $this->findBy('project_id', $projectId);
        if (!$analysis) {
            return null;
        }
        $analysis['items'] = $this->query(
            "SELECT * FROM financial_analysis_items WHERE analysis_id = ? ORDER BY sort_order ASC",
            [$analysis['id']]
        );
        return $analysis;
    }

    public function updateItem(int $itemId, array $data): bool
    {
        $allowed = array_intersect_key($data, array_flip(['category', 'item_name', 'unit', 'quantity', 'unit_rate', 'total_budget', 'total_actual', 'notes']));
        if (empty($allowed)) {
            return false;
        }
        $set = implode(', ', array_map(fn($k) => "$k = ?", array_keys($allowed)));
        return $this->execute("UPDATE financial_analysis_items SET {$set} WHERE id = ?", [...array_values($allowed), $itemId]);
    }

    public function addItem(int $analysisId, array $data): int
    {
        return $this->execute(
            "INSERT INTO financial_analysis_items (analysis_id, category, item_name, unit, quantity, unit_rate, total_budget, sort_order) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
            [$analysisId, $data['category'], $data['item_name'], $data['unit'], $data['quantity'], $data['unit_rate'], $data['total_budget'], $data['sort_order'] ?? 0]
        ) ? (int)$this->db->lastInsertId() : 0;
    }

    public function deleteItem(int $itemId): bool
    {
        return $this->execute("DELETE FROM financial_analysis_items WHERE id = ?", [$itemId]);
    }

    public function calculateProfitability(int $projectId): array
    {
        $analysis = $this->findBy('project_id', $projectId);
        $revenue = (float)($analysis['total_revenue'] ?? 0);
        $budget = (float)($analysis['total_budget'] ?? 0);

        $actualRow = $this->queryOne("SELECT COALESCE(SUM(amount), 0) as total FROM expenses WHERE project_id = ?", [$projectId]);
        $actual = (float)($actualRow['total'] ?? 0);

        $margin = $revenue > 0 ? round((($revenue - $actual) / $revenue) * 100, 2) : 0.0;

        return [
            'revenue' => $revenue,
            'budget' => $budget,
            'actual' => $actual,
            'margin' => $margin,
            'is_profitable' => ($revenue - $actual) > 0,
        ];
    }
}
