<?php

namespace App\Controllers;

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

/**
 * Controlador de Reportes y Métricas
 * Genera reportes y KPIs del área de TI
 */
class ReportsController extends BaseController
{
    protected $db;
    protected $authMiddleware;

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

    /**
     * Vista principal de reportes
     */
    public function index(): void
    {
        $this->authMiddleware->requireRole();

        // Obtener rangos de fecha (últimos 30 días por defecto)
        $endDate = $_GET['end_date'] ?? date('Y-m-d');
        $startDate = $_GET['start_date'] ?? date('Y-m-d', strtotime('-30 days'));

        $data = [
            'start_date' => $startDate,
            'end_date' => $endDate,
            'csrf_token' => $_SESSION['csrf_token'] ?? ''
        ];

        require __DIR__ . '/../../views/reports/index.php';
    }

    /**
     * Obtener métricas generales del área TI
     */
    public function getMetrics(): void
    {
        $this->authMiddleware->requireRole();
        header('Content-Type: application/json');

        try {
            $startDate = $_GET['start_date'] ?? date('Y-m-d', strtotime('-30 days'));
            $endDate = $_GET['end_date'] ?? date('Y-m-d');

            $metrics = [
                'requirements' => $this->getRequirementsMetrics($startDate, $endDate),
                'projects' => $this->getProjectsMetrics($startDate, $endDate),
                'sprints' => $this->getSprintsMetrics($startDate, $endDate),
                'team' => $this->getTeamMetrics($startDate, $endDate),
            ];

            echo json_encode(['success' => true, 'metrics' => $metrics]);

        } catch (\Exception $e) {
            error_log("Error getting metrics: " . $e->getMessage());
            http_response_code(500);
            echo json_encode(['success' => false, 'message' => 'Error al obtener métricas']);
        }
    }

    /**
     * Métricas de requerimientos
     */
    private function getRequirementsMetrics($startDate, $endDate): array
    {
        // Total de requerimientos
        $total = $this->db->fetchOne(
            "SELECT COUNT(*) as count FROM requirements 
             WHERE created_at BETWEEN ? AND ?",
            [$startDate, $endDate]
        )['count'] ?? 0;

        // Por estado
        $byStatus = $this->db->fetchAll(
            "SELECT status, COUNT(*) as count 
             FROM requirements 
             WHERE created_at BETWEEN ? AND ?
             GROUP BY status",
            [$startDate, $endDate]
        );

        // Por tipo
        $byType = $this->db->fetchAll(
            "SELECT type, COUNT(*) as count 
             FROM requirements 
             WHERE created_at BETWEEN ? AND ?
             GROUP BY type 
             ORDER BY count DESC",
            [$startDate, $endDate]
        );

        // Tiempo promedio por tipo (desde creación hasta completado)
        $avgTime = $this->db->fetchAll(
            "SELECT 
                type,
                AVG(DATEDIFF(updated_at, created_at)) as avg_days
             FROM requirements 
             WHERE status = 'completed'
             AND created_at BETWEEN ? AND ?
             GROUP BY type",
            [$startDate, $endDate]
        );

        // Por equipo
        $byTeam = $this->db->fetchAll(
            "SELECT 
                assigned_team,
                COUNT(*) as total,
                SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed
             FROM requirements 
             WHERE created_at BETWEEN ? AND ?
             GROUP BY assigned_team",
            [$startDate, $endDate]
        );

        return [
            'total' => $total,
            'by_status' => $byStatus,
            'by_type' => $byType,
            'avg_time' => $avgTime,
            'by_team' => $byTeam
        ];
    }

    /**
     * Métricas de proyectos
     */
    private function getProjectsMetrics($startDate, $endDate): array
    {
        // Total de proyectos
        $total = $this->db->fetchOne(
            "SELECT COUNT(*) as count FROM projects 
             WHERE created_at BETWEEN ? AND ?",
            [$startDate, $endDate]
        )['count'] ?? 0;

        // Por estado
        $byStatus = $this->db->fetchAll(
            "SELECT status, COUNT(*) as count 
             FROM projects 
             WHERE created_at BETWEEN ? AND ?
             GROUP BY status",
            [$startDate, $endDate]
        );

        // Progreso promedio
        $avgProgress = $this->db->fetchOne(
            "SELECT AVG(progress_percentage) as avg 
             FROM projects 
             WHERE status NOT IN ('cancelled', 'completed')
             AND created_at BETWEEN ? AND ?",
            [$startDate, $endDate]
        )['avg'] ?? 0;

        // Proyectos activos
        $active = $this->db->fetchAll(
            "SELECT 
                p.code,
                p.name,
                p.status,
                p.progress_percentage,
                p.start_date,
                p.target_date as end_date,
                CONCAT(u.first_name, ' ', u.last_name) as manager
             FROM projects p
             LEFT JOIN users u ON p.created_by = u.id
             WHERE p.status IN ('planning', 'in_progress')
             ORDER BY p.created_at DESC
             LIMIT 10",
            []
        );

        return [
            'total' => $total,
            'by_status' => $byStatus,
            'avg_progress' => round($avgProgress, 2),
            'active_projects' => $active
        ];
    }

    /**
     * Métricas de sprints y velocidad
     */
    private function getSprintsMetrics($startDate, $endDate): array
    {
        // Sprints completados
        $completed = $this->db->fetchAll(
            "SELECT 
                s.id,
                s.name,
                s.start_date,
                s.end_date,
                p.name as project_name,
                SUM(pbi.story_points) as total_points,
                SUM(CASE WHEN pbi.status = 'done' THEN pbi.story_points ELSE 0 END) as completed_points
             FROM sprints s
             JOIN projects p ON s.project_id = p.id
             LEFT JOIN sprint_backlog sb ON s.id = sb.sprint_id
             LEFT JOIN product_backlog_items pbi ON sb.backlog_item_id = pbi.id
             WHERE s.status = 'completed'
             AND s.end_date BETWEEN ? AND ?
             GROUP BY s.id
             ORDER BY s.end_date DESC
             LIMIT 5",
            [$startDate, $endDate]
        );

        // Calcular velocidad promedio
        $velocities = [];
        foreach ($completed as $sprint) {
            $start = new \DateTime($sprint['start_date']);
            $end = new \DateTime($sprint['end_date']);
            $days = $start->diff($end)->days + 1;
            
            if ($days > 0) {
                $velocities[] = ($sprint['completed_points'] ?? 0) / $days;
            }
        }

        $avgVelocity = count($velocities) > 0 ? array_sum($velocities) / count($velocities) : 0;

        // Sprint actual
        $currentSprint = $this->db->fetchOne(
            "SELECT 
                s.*,
                p.name as project_name,
                SUM(pbi.story_points) as total_points,
                SUM(CASE WHEN pbi.status = 'done' THEN pbi.story_points ELSE 0 END) as completed_points
             FROM sprints s
             JOIN projects p ON s.project_id = p.id
             LEFT JOIN sprint_backlog sb ON s.id = sb.sprint_id
             LEFT JOIN product_backlog_items pbi ON sb.backlog_item_id = pbi.id
             WHERE s.status = 'active'
             GROUP BY s.id
             LIMIT 1",
            []
        );

        return [
            'completed_sprints' => $completed,
            'avg_velocity' => round($avgVelocity, 2),
            'current_sprint' => $currentSprint
        ];
    }

    /**
     * Métricas del equipo
     */
    private function getTeamMetrics($startDate, $endDate): array
    {
        // Carga de trabajo por usuario
        $workload = $this->db->fetchAll(
            "SELECT 
                u.id,
                CONCAT(u.first_name, ' ', u.last_name) as name,
                u.team,
                COUNT(DISTINCT r.id) as requirements_assigned,
                COUNT(DISTINCT pbi.id) as backlog_items_assigned,
                SUM(t.estimated_hours) as estimated_hours,
                SUM(t.actual_hours) as actual_hours
             FROM users u
             LEFT JOIN requirements r ON u.id = r.assigned_user_id AND r.status NOT IN ('completed', 'rejected')
             LEFT JOIN product_backlog_items pbi ON u.id = pbi.assigned_to AND pbi.status != 'done'
             LEFT JOIN tasks t ON u.id = t.assigned_to AND t.status != 'done'
             WHERE u.is_active = TRUE AND u.role != 'user'
             GROUP BY u.id
             ORDER BY u.team, u.first_name",
            []
        );

        // Productividad (tareas completadas)
        $productivity = $this->db->fetchAll(
            "SELECT 
                u.id,
                CONCAT(u.first_name, ' ', u.last_name) as name,
                COUNT(t.id) as tasks_completed,
                SUM(t.actual_hours) as hours_worked
             FROM users u
             JOIN tasks t ON u.id = t.assigned_to
             WHERE t.status = 'done'
             AND t.updated_at BETWEEN ? AND ?
             GROUP BY u.id
             ORDER BY tasks_completed DESC
             LIMIT 10",
            [$startDate, $endDate]
        );

        return [
            'workload' => $workload,
            'productivity' => $productivity
        ];
    }

    /**
     * Obtener datos para gráficos
     */
    public function getChartData(): void
    {
        $this->authMiddleware->requireRole();
        header('Content-Type: application/json');

        try {
            $type = $_GET['type'] ?? 'requirements_timeline';
            $startDate = $_GET['start_date'] ?? date('Y-m-d', strtotime('-30 days'));
            $endDate = $_GET['end_date'] ?? date('Y-m-d');

            $data = [];

            switch ($type) {
                case 'requirements_timeline':
                    $data = $this->getRequirementsTimelineData($startDate, $endDate);
                    break;

                case 'projects_status':
                    $data = $this->getProjectsStatusData();
                    break;

                case 'team_workload':
                    $data = $this->getTeamWorkloadData();
                    break;

                case 'velocity_trend':
                    $data = $this->getVelocityTrendData();
                    break;

                default:
                    throw new \Exception('Tipo de gráfico no válido');
            }

            echo json_encode(['success' => true, 'data' => $data]);

        } catch (\Exception $e) {
            error_log("Error getting chart data: " . $e->getMessage());
            http_response_code(500);
            echo json_encode(['success' => false, 'message' => $e->getMessage()]);
        }
    }

    private function getRequirementsTimelineData($startDate, $endDate): array
    {
        $data = $this->db->fetchAll(
            "SELECT 
                DATE(created_at) as date,
                COUNT(*) as count
             FROM requirements
             WHERE created_at BETWEEN ? AND ?
             GROUP BY DATE(created_at)
             ORDER BY date ASC",
            [$startDate, $endDate]
        );

        return $data;
    }

    private function getProjectsStatusData(): array
    {
        $data = $this->db->fetchAll(
            "SELECT status, COUNT(*) as count
             FROM projects
             GROUP BY status",
            []
        );

        return $data;
    }

    private function getTeamWorkloadData(): array
    {
        $data = $this->db->fetchAll(
            "SELECT 
                assigned_team as team,
                COUNT(*) as count
             FROM requirements
             WHERE status NOT IN ('completed', 'rejected')
             GROUP BY assigned_team",
            []
        );

        return $data;
    }

    private function getVelocityTrendData(): array
    {
        $sprints = $this->db->fetchAll(
            "SELECT 
                s.name,
                s.end_date,
                SUM(CASE WHEN pbi.status = 'done' THEN pbi.story_points ELSE 0 END) as velocity
             FROM sprints s
             LEFT JOIN sprint_backlog sb ON s.id = sb.sprint_id
             LEFT JOIN product_backlog_items pbi ON sb.backlog_item_id = pbi.id
             WHERE s.status = 'completed'
             GROUP BY s.id
             ORDER BY s.end_date ASC
             LIMIT 10",
            []
        );

        return $sprints;
    }
}
