<?php

namespace App\Controllers;

use App\Services\Database;
use App\Middleware\AuthMiddleware;

/**
 * Controlador de Proyectos
 * Gestiona proyectos que agrupan múltiples requerimientos
 */
class ProjectsController extends BaseController
{
    protected $db;
    protected $authMiddleware;

    public function __construct()
    {
        parent::__construct();
        $this->db = Database::getInstance();
        $this->authMiddleware = new AuthMiddleware();
    }

    /**
     * Listar todos los proyectos
     */
    public function index(): void
    {
        $this->authMiddleware->requireRole();

        // Filtros
        $status = $_GET['status'] ?? '';
        $priority = $_GET['priority'] ?? '';
        $team = $_GET['team'] ?? '';
        $search = $_GET['search'] ?? '';

        $sql = "SELECT p.*, 
                       t.name as team_name, t.code as team_code,
                       u.first_name as creator_first_name, u.last_name as creator_last_name,
                       (SELECT COUNT(*) FROM requirements WHERE project_id = p.id) as requirements_count
                FROM projects p
                LEFT JOIN teams t ON p.responsible_team_id = t.id
                LEFT JOIN users u ON p.created_by = u.id
                WHERE 1=1";

        $params = [];

        if (!empty($status)) {
            $sql .= " AND p.status = ?";
            $params[] = $status;
        }

        if (!empty($priority)) {
            $sql .= " AND p.priority = ?";
            $params[] = $priority;
        }

        if (!empty($team)) {
            $sql .= " AND p.responsible_team_id = ?";
            $params[] = $team;
        }

        if (!empty($search)) {
            $sql .= " AND (p.code LIKE ? OR p.name LIKE ? OR p.description LIKE ?)";
            $searchTerm = "%{$search}%";
            $params[] = $searchTerm;
            $params[] = $searchTerm;
            $params[] = $searchTerm;
        }

        $sql .= " ORDER BY p.created_at DESC";

        $projects = $this->db->fetchAll($sql, $params);

        // Cargar equipos para filtro
        $teams = $this->db->fetchAll("SELECT id, name, code FROM teams WHERE is_active = 1 ORDER BY name");

        // Estadísticas generales
        $stats = $this->getStats();

        require_once __DIR__ . '/../../views/projects/index.php';
    }

    /**
     * Ver detalle de un proyecto
     */
    public function view(int $id): void
    {
        $this->authMiddleware->requireRole();

        $project = $this->db->fetchOne(
            "SELECT p.*, 
                    t.name as team_name, t.code as team_code,
                    u.first_name as creator_first_name, u.last_name as creator_last_name
             FROM projects p
             LEFT JOIN teams t ON p.responsible_team_id = t.id
             LEFT JOIN users u ON p.created_by = u.id
             WHERE p.id = ?",
            [$id]
        );

        if (!$project) {
            $_SESSION['error'] = 'Proyecto no encontrado';
            header('Location: /projects');
            exit;
        }

        // Cargar requerimientos asociados
        $requirements = $this->db->fetchAll(
            "SELECT r.*, 
                    u1.first_name as requester_first_name, u1.last_name as requester_last_name,
                    u2.first_name as assigned_first_name, u2.last_name as assigned_last_name
             FROM requirements r
             LEFT JOIN users u1 ON r.requester_user_id = u1.id
             LEFT JOIN users u2 ON r.assigned_user_id = u2.id
             WHERE r.project_id = ?
             ORDER BY r.created_at DESC",
            [$id]
        );

        // Cargar solo requerimientos APROBADOS disponibles (sin proyecto asignado)
        // Según el flujo institucional: solo requerimientos aprobados pueden asignarse a proyectos
        $availableRequirements = $this->db->fetchAll(
            "SELECT id, code, title, status,
                    CONCAT(requester_first_name, ' ', requester_last_name) as requester_name
             FROM requirements r
             LEFT JOIN users u ON r.requester_user_id = u.id
             WHERE r.project_id IS NULL 
               AND r.status = 'approved'
             ORDER BY r.created_at DESC 
             LIMIT 100"
        );

        // Calcular progreso automático basado en requerimientos
        $requirementsStats = $this->getProjectRequirementsStats($id);

        // Obtener trazabilidad completa: Backlog Items relacionados a cada requerimiento
        $traceability = $this->db->fetchAll(
            "SELECT 
                pbi.id as backlog_item_id,
                pbi.title as backlog_item_title,
                pbi.status as backlog_item_status,
                pbi.priority as backlog_item_priority,
                pbi.requirement_id,
                r.code as requirement_code,
                r.title as requirement_title,
                s.id as sprint_id,
                s.name as sprint_name,
                s.status as sprint_status,
                (SELECT COUNT(*) FROM tasks WHERE backlog_item_id = pbi.id) as tasks_count,
                (SELECT COUNT(*) FROM tasks WHERE backlog_item_id = pbi.id AND status = 'done') as tasks_completed
             FROM product_backlog_items pbi
             LEFT JOIN requirements r ON pbi.requirement_id = r.id
             LEFT JOIN sprint_backlog sb ON pbi.id = sb.backlog_item_id
             LEFT JOIN sprints s ON sb.sprint_id = s.id
             WHERE pbi.project_id = ?
             ORDER BY r.code, pbi.created_at",
            [$id]
        );

        // Agrupar backlog items por requerimiento
        $traceabilityByRequirement = [];
        foreach ($traceability as $item) {
            $reqId = $item['requirement_id'] ?? 'technical';
            if (!isset($traceabilityByRequirement[$reqId])) {
                $traceabilityByRequirement[$reqId] = [
                    'requirement_code' => $item['requirement_code'] ?? 'Técnico',
                    'requirement_title' => $item['requirement_title'] ?? 'Item técnico sin requerimiento',
                    'items' => []
                ];
            }
            $traceabilityByRequirement[$reqId]['items'][] = $item;
        }

        // Info del usuario actual
        $user = $_SESSION['user'] ?? [];
        $userRole = $user['role'] ?? 'user';
        $isAdmin = ($userRole === 'admin');

        require_once __DIR__ . '/../../views/projects/view.php';
    }

    /**
     * Mostrar formulario de creación
     */
    public function create(): void
    {
        $this->authMiddleware->requireRole();

        // Cargar equipos
        $teams = $this->db->fetchAll("SELECT id, name, code FROM teams WHERE is_active = 1 ORDER BY name");

        require_once __DIR__ . '/../../views/projects/create.php';
    }

    /**
     * Guardar nuevo proyecto
     */
    public function store(): void
    {
        header('Content-Type: application/json');
        $this->authMiddleware->requireRole();

        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            http_response_code(405);
            echo json_encode(['success' => false, 'message' => 'Método no permitido']);
            return;
        }

        try {
            // Validación
            $name = trim($_POST['name'] ?? '');
            $description = trim($_POST['description'] ?? '');
            $requestingDepartment = trim($_POST['requesting_department'] ?? '');
            $priority = $_POST['priority'] ?? 'medium';
            $responsibleTeamId = $_POST['responsible_team_id'] ?? null;
            $startDate = $_POST['start_date'] ?? null;
            $targetDate = $_POST['target_date'] ?? null;
            $budget = $_POST['budget'] ?? null;

            if (empty($name)) {
                throw new \Exception('El nombre del proyecto es requerido');
            }

            // Generar código único del proyecto
            $code = $this->generateProjectCode();

            // Crear proyecto
            $projectId = $this->db->insert(
                "INSERT INTO projects (code, name, description, requesting_department, status, priority, start_date, target_date, progress_percentage, responsible_team_id, budget, created_by) 
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                [
                    $code,
                    $name,
                    $description,
                    $requestingDepartment,
                    'planning', // status por defecto
                    $priority,
                    $startDate ?: null,
                    $targetDate ?: null,
                    0, // progress_percentage inicial
                    $responsibleTeamId ?: null,
                    $budget ?: null,
                    $_SESSION['user']['id']
                ]
            );

            echo json_encode([
                'success' => true,
                'message' => 'Proyecto creado correctamente',
                'project_id' => $projectId,
                'code' => $code
            ]);

        } catch (\Exception $e) {
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => $e->getMessage()]);
        }
    }

    /**
     * Actualizar proyecto
     */
    public function update(int $id): void
    {
        header('Content-Type: application/json');
        $this->authMiddleware->requireRole();

        if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
            http_response_code(405);
            echo json_encode(['success' => false, 'message' => 'Método no permitido']);
            return;
        }

        try {
            $action = $_POST['action'] ?? '';

            switch ($action) {
                case 'update_info':
                    $this->updateProjectInfo($id);
                    break;

                case 'update_status':
                    $this->updateProjectStatus($id);
                    break;

                case 'update_progress':
                    $this->updateProjectProgress($id);
                    break;

                case 'assign_requirement':
                    $this->assignRequirementToProject($id);
                    break;

                case 'remove_requirement':
                    $this->removeRequirementFromProject($id);
                    break;

                default:
                    throw new \Exception('Acción no válida');
            }

        } catch (\Exception $e) {
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => $e->getMessage()]);
        }
    }

    /**
     * Actualizar información del proyecto
     */
    private function updateProjectInfo(int $id): void
    {
        $name = trim($_POST['name'] ?? '');
        $description = trim($_POST['description'] ?? '');
        $requestingDepartment = trim($_POST['requesting_department'] ?? '');
        $priority = $_POST['priority'] ?? 'medium';
        $responsibleTeamId = $_POST['responsible_team_id'] ?? null;
        $startDate = $_POST['start_date'] ?? null;
        $targetDate = $_POST['target_date'] ?? null;
        $budget = $_POST['budget'] ?? null;

        if (empty($name)) {
            throw new \Exception('El nombre del proyecto es requerido');
        }

        $this->db->query(
            "UPDATE projects 
             SET name = ?, description = ?, requesting_department = ?, priority = ?, 
                 start_date = ?, target_date = ?, responsible_team_id = ?, budget = ?
             WHERE id = ?",
            [
                $name,
                $description,
                $requestingDepartment,
                $priority,
                $startDate ?: null,
                $targetDate ?: null,
                $responsibleTeamId ?: null,
                $budget ?: null,
                $id
            ]
        );

        echo json_encode(['success' => true, 'message' => 'Proyecto actualizado correctamente']);
    }

    /**
     * Actualizar estado del proyecto
     */
    private function updateProjectStatus(int $id): void
    {
        $status = $_POST['status'] ?? '';
        $validStatuses = ['planning', 'in_progress', 'testing', 'deployed', 'closed'];

        if (!in_array($status, $validStatuses)) {
            throw new \Exception('Estado no válido');
        }

        $completionDate = ($status === 'closed') ? date('Y-m-d') : null;

        $this->db->query(
            "UPDATE projects SET status = ?, completion_date = ? WHERE id = ?",
            [$status, $completionDate, $id]
        );

        echo json_encode(['success' => true, 'message' => 'Estado actualizado correctamente']);
    }

    /**
     * Actualizar progreso del proyecto
     */
    private function updateProjectProgress(int $id): void
    {
        $progress = (int)($_POST['progress'] ?? 0);

        if ($progress < 0 || $progress > 100) {
            throw new \Exception('El progreso debe estar entre 0 y 100');
        }

        $this->db->query(
            "UPDATE projects SET progress_percentage = ? WHERE id = ?",
            [$progress, $id]
        );

        echo json_encode(['success' => true, 'message' => 'Progreso actualizado correctamente']);
    }

    /**
     * Asignar requerimiento a proyecto
     */
    private function assignRequirementToProject(int $projectId): void
    {
        $requirementId = (int)($_POST['requirement_id'] ?? 0);

        if (empty($requirementId)) {
            throw new \Exception('ID de requerimiento requerido');
        }

        // Validar que el requerimiento existe y está APROBADO
        $requirement = $this->db->fetchOne(
            "SELECT id, code, status, project_id FROM requirements WHERE id = ?",
            [$requirementId]
        );

        if (!$requirement) {
            throw new \Exception('Requerimiento no encontrado');
        }

        if ($requirement['status'] !== 'approved') {
            throw new \Exception('Solo se pueden asignar requerimientos aprobados. Estado actual: ' . $requirement['status']);
        }

        if (!empty($requirement['project_id'])) {
            throw new \Exception('El requerimiento ya está asignado a otro proyecto');
        }

        // Asignar requerimiento al proyecto
        $this->db->query(
            "UPDATE requirements SET project_id = ? WHERE id = ?",
            [$projectId, $requirementId]
        );

        echo json_encode([
            'success' => true, 
            'message' => 'Requerimiento ' . $requirement['code'] . ' asignado al proyecto exitosamente'
        ]);
    }

    /**
     * Remover requerimiento de proyecto
     */
    private function removeRequirementFromProject(int $projectId): void
    {
        $requirementId = (int)($_POST['requirement_id'] ?? 0);

        if (empty($requirementId)) {
            throw new \Exception('ID de requerimiento requerido');
        }

        $this->db->query(
            "UPDATE requirements SET project_id = NULL WHERE id = ? AND project_id = ?",
            [$requirementId, $projectId]
        );

        echo json_encode(['success' => true, 'message' => 'Requerimiento removido del proyecto']);
    }

    /**
     * Eliminar proyecto
     */
    public function delete(int $id): void
    {
        header('Content-Type: application/json');
        $this->authMiddleware->requireAdmin();

        try {
            // Verificar que no tenga requerimientos asociados
            $requirementsCount = $this->db->fetchOne(
                "SELECT COUNT(*) as count FROM requirements WHERE project_id = ?",
                [$id]
            )['count'];

            if ($requirementsCount > 0) {
                throw new \Exception('No se puede eliminar un proyecto con requerimientos asociados. Primero debe removerlos.');
            }

            $this->db->query("DELETE FROM projects WHERE id = ?", [$id]);

            echo json_encode(['success' => true, 'message' => 'Proyecto eliminado correctamente']);

        } catch (\Exception $e) {
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => $e->getMessage()]);
        }
    }

    /**
     * Generar código único de proyecto
     */
    private function generateProjectCode(): string
    {
        $year = date('Y');
        $prefix = "PROJ-{$year}-";

        $lastProject = $this->db->fetchOne(
            "SELECT code FROM projects WHERE code LIKE ? ORDER BY id DESC LIMIT 1",
            ["{$prefix}%"]
        );

        if ($lastProject) {
            $lastNumber = (int)substr($lastProject['code'], -4);
            $newNumber = $lastNumber + 1;
        } else {
            $newNumber = 1;
        }

        return $prefix . str_pad($newNumber, 4, '0', STR_PAD_LEFT);
    }

    /**
     * Obtener estadísticas generales de proyectos
     */
    public function getStats(): array
    {
        $stats = [
            'total' => 0,
            'by_status' => [],
            'by_priority' => [],
            'average_progress' => 0,
            'on_time' => 0,
            'delayed' => 0
        ];

        // Total de proyectos
        $total = $this->db->fetchOne("SELECT COUNT(*) as count FROM projects");
        $stats['total'] = $total['count'] ?? 0;

        // Por estado
        $byStatus = $this->db->fetchAll(
            "SELECT status, COUNT(*) as count FROM projects GROUP BY status"
        );
        foreach ($byStatus as $row) {
            $stats['by_status'][$row['status']] = (int)$row['count'];
        }

        // Por prioridad
        $byPriority = $this->db->fetchAll(
            "SELECT priority, COUNT(*) as count FROM projects GROUP BY priority"
        );
        foreach ($byPriority as $row) {
            $stats['by_priority'][$row['priority']] = (int)$row['count'];
        }

        // Progreso promedio
        $avgProgress = $this->db->fetchOne(
            "SELECT AVG(progress_percentage) as avg FROM projects WHERE status IN ('in_progress', 'testing')"
        );
        $stats['average_progress'] = round($avgProgress['avg'] ?? 0);

        // Proyectos a tiempo vs atrasados
        $today = date('Y-m-d');
        $onTime = $this->db->fetchOne(
            "SELECT COUNT(*) as count FROM projects 
             WHERE status NOT IN ('closed', 'cancelled') 
             AND (target_date IS NULL OR target_date >= ?)",
            [$today]
        );
        $stats['on_time'] = $onTime['count'] ?? 0;

        $delayed = $this->db->fetchOne(
            "SELECT COUNT(*) as count FROM projects 
             WHERE status NOT IN ('closed', 'cancelled') 
             AND target_date < ?",
            [$today]
        );
        $stats['delayed'] = $delayed['count'] ?? 0;

        return $stats;
    }

    /**
     * Obtener estadísticas de requerimientos de un proyecto
     */
    private function getProjectRequirementsStats(int $projectId): array
    {
        $stats = [
            'total' => 0,
            'by_status' => [],
            'completed_percentage' => 0
        ];

        // Total de requerimientos
        $total = $this->db->fetchOne(
            "SELECT COUNT(*) as count FROM requirements WHERE project_id = ?",
            [$projectId]
        );
        $stats['total'] = $total['count'] ?? 0;

        // Por estado
        $byStatus = $this->db->fetchAll(
            "SELECT status, COUNT(*) as count FROM requirements WHERE project_id = ? GROUP BY status",
            [$projectId]
        );
        foreach ($byStatus as $row) {
            $stats['by_status'][$row['status']] = (int)$row['count'];
        }

        // Porcentaje completado
        if ($stats['total'] > 0) {
            $completed = $stats['by_status']['completed'] ?? 0;
            $stats['completed_percentage'] = round(($completed / $stats['total']) * 100);
        }

        return $stats;
    }
}
