<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\WithStyles;
use Maatwebsite\Excel\Concerns\WithCharts;
use Maatwebsite\Excel\Concerns\WithColumnWidths;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Chart\Chart;
use PhpOffice\PhpSpreadsheet\Chart\DataSeries;
use PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues;
use PhpOffice\PhpSpreadsheet\Chart\Legend;
use PhpOffice\PhpSpreadsheet\Chart\PlotArea;
use PhpOffice\PhpSpreadsheet\Chart\Title;
use PhpOffice\PhpSpreadsheet\Chart\Layout;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

class SolicitudesChartsSheet implements FromArray, WithTitle, WithStyles, WithCharts, WithColumnWidths
{
    protected $solicitudes;
    protected $stats;

    // Posiciones reales de cada tabla (se calculan en array())
    protected $estadoDataStart;
    protected $estadoDataEnd;
    protected $deptoDataStart;
    protected $deptoDataEnd;
    protected $califDataStart;
    protected $califDataEnd;

    // Mapa de estados legibles
    private const ESTADO_LABELS = [
        'I'  => 'Ingresada',
        'A'  => 'Asignada',
        'EE' => 'En Espera',
        'CO' => 'Ejecutado/Por Conformar',
        'EJ' => 'En Ejecución',
        'CF' => 'Conformada',
        'CE' => 'Cerrada',
    ];

    // Colores para gráfica de estados (sin #)
    private const ESTADO_COLORS = [
        'Ingresada'              => '10B981', // emerald-500
        'Asignada'               => 'F59E0B', // amber-500
        'En Espera'              => 'F97316', // orange-500
        'En Ejecución'           => '10B981', // emerald-500
        'Ejecutado/Por Conformar'=> '3B82F6', // blue-500
        'Conformada'             => '6366F1', // indigo-500
        'Cerrada'                => '64748B', // slate-500
    ];

    // Colores para calificaciones
    private const CALIFICACION_COLORS = [
        '5' => '10B981',  // verde
        '4' => '22D3EE',  // cyan
        '3' => 'F59E0B',  // amber
        '2' => 'F97316',  // naranja
        '1' => 'EF4444',  // rojo
        '0' => 'CBD5E1',  // gris (Sin Calificar)
    ];

    public function __construct($solicitudes)
    {
        $this->solicitudes = $solicitudes;
        $this->stats = $this->calculateStats();
    }

    public function title(): string
    {
        return 'Estadísticas';
    }

    /**
     * Pre-calcula todas las estadísticas a partir de la colección.
     */
    private function calculateStats(): array
    {
        $stats = [];

        // --- Por Estado ---
        $porEstado = [];
        foreach ($this->solicitudes as $s) {
            $label = self::ESTADO_LABELS[$s->estado] ?? $s->estado;
            $porEstado[$label] = ($porEstado[$label] ?? 0) + 1;
        }
        $stats['estados'] = $porEstado;

        // --- Por Departamento ---
        $porDepto = [];
        foreach ($this->solicitudes as $s) {
            $depto = optional($s->departamento)->nombre ?? 'Sin Departamento';
            $porDepto[$depto] = ($porDepto[$depto] ?? 0) + 1;
        }
        arsort($porDepto);
        $stats['departamentos'] = $porDepto;

        // --- Por Calificación ---
        $porCalif = [];
        foreach ($this->solicitudes as $s) {
            if ($s->calificacion) {
                $stars = str_repeat('★', $s->calificacion) . str_repeat('☆', 5 - $s->calificacion);
                $label = $stars . ' (' . $s->calificacion . ')';
                $key = (string) $s->calificacion;
            } else {
                $label = 'Sin Calificar';
                $key = '0';
            }
            if (!isset($porCalif[$key])) {
                $porCalif[$key] = ['label' => $label, 'count' => 0];
            }
            $porCalif[$key]['count']++;
        }
        // Ordenar por calificación descendente (5, 4, 3, 2, 1, Sin Calificar)
        krsort($porCalif);
        $stats['calificaciones'] = $porCalif;

        return $stats;
    }

    /**
     * Genera los datos tabulares que se escribirán en la hoja.
     * Usa filas con cadenas vacías (no arrays vacíos) para garantizar que
     * las filas separadoras se creen realmente en Excel.
     * Registra las posiciones exactas de cada tabla para las gráficas.
     */
    public function array(): array
    {
        $rows = [];
        $total = $this->solicitudes->count();
        $currentRow = 0; // Tracker de fila (0-indexed, Excel será 1-indexed)

        // === ENCABEZADO (filas 1-3) ===
        $rows[] = ['RESUMEN ESTADÍSTICO DEL REPORTE', '', '', ''];
        $currentRow++;
        $rows[] = ['Sistema SISER v2 — Generado el ' . date('d/m/Y H:i A'), '', '', ''];
        $currentRow++;
        $rows[] = ['Total de solicitudes en este reporte: ' . $total, '', '', ''];
        $currentRow++;

        // Fila separadora (con espacios vacíos para que se cree la fila)
        $rows[] = ['', '', '', ''];
        $currentRow++;

        // === TABLA 1: Por Estado ===
        $rows[] = ['DISTRIBUCIÓN POR ESTADO', '', '', ''];
        $currentRow++;
        $rows[] = ['Estado', 'Cantidad', 'Porcentaje', ''];
        $currentRow++;

        $this->estadoDataStart = $currentRow + 1; // Fila Excel (1-indexed)
        foreach ($this->stats['estados'] as $estado => $cantidad) {
            $pct = $total > 0 ? round(($cantidad / $total) * 100, 1) . '%' : '0%';
            $rows[] = [$estado, $cantidad, $pct, ''];
            $currentRow++;
        }
        $this->estadoDataEnd = $currentRow; // Última fila de datos de estado

        // Fila TOTAL
        $rows[] = ['TOTAL', $total, '100%', ''];
        $currentRow++;

        // Separador (2 filas con contenido vacío)
        $rows[] = ['', '', '', ''];
        $currentRow++;
        $rows[] = ['', '', '', ''];
        $currentRow++;

        // === TABLA 2: Por Departamento ===
        $rows[] = ['SOLICITUDES POR DEPARTAMENTO', '', '', ''];
        $currentRow++;
        $rows[] = ['Departamento', 'Cantidad', 'Porcentaje', ''];
        $currentRow++;

        $this->deptoDataStart = $currentRow + 1;
        foreach ($this->stats['departamentos'] as $depto => $cantidad) {
            $pct = $total > 0 ? round(($cantidad / $total) * 100, 1) . '%' : '0%';
            $rows[] = [$depto, $cantidad, $pct, ''];
            $currentRow++;
        }
        $this->deptoDataEnd = $currentRow;

        // Separador
        $rows[] = ['', '', '', ''];
        $currentRow++;
        $rows[] = ['', '', '', ''];
        $currentRow++;

        // === TABLA 3: Por Calificación ===
        $rows[] = ['DISTRIBUCIÓN DE CALIFICACIONES', '', '', ''];
        $currentRow++;
        $rows[] = ['Calificación', 'Cantidad', 'Porcentaje', ''];
        $currentRow++;

        $this->califDataStart = $currentRow + 1;
        foreach ($this->stats['calificaciones'] as $key => $data) {
            $pct = $total > 0 ? round(($data['count'] / $total) * 100, 1) . '%' : '0%';
            $rows[] = [$data['label'], $data['count'], $pct, ''];
            $currentRow++;
        }
        $this->califDataEnd = $currentRow;

        return $rows;
    }

    public function columnWidths(): array
    {
        return [
            'A' => 35,
            'B' => 15,
            'C' => 15,
            'D' => 5,
        ];
    }

    public function styles(Worksheet $sheet)
    {
        // Título principal
        $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(16)->getColor()->setARGB('0C782B');
        $sheet->getStyle('A2')->getFont()->setSize(11)->getColor()->setARGB('64748B');
        $sheet->getStyle('A3')->getFont()->setBold(true)->setSize(11);

        // Localizar y estilizar cabeceras de tablas
        $tableHeaders = ['DISTRIBUCIÓN POR ESTADO', 'SOLICITUDES POR DEPARTAMENTO', 'DISTRIBUCIÓN DE CALIFICACIONES'];
        $highestRow = $sheet->getHighestRow();

        for ($row = 1; $row <= $highestRow; $row++) {
            $cellValue = $sheet->getCell('A' . $row)->getValue();

            // Estilar títulos de sección
            if (in_array($cellValue, $tableHeaders)) {
                $sheet->getStyle('A' . $row)->getFont()->setBold(true)->setSize(12)->getColor()->setARGB('0C782B');
                $sheet->getStyle('A' . $row . ':C' . $row)->applyFromArray([
                    'fill' => [
                        'fillType' => Fill::FILL_SOLID,
                        'startColor' => ['argb' => 'F0FDF4'], // green-50
                    ],
                ]);
            }

            // Estilar filas de cabecera de tabla (Estado/Cantidad/Porcentaje, etc.)
            $headerLabels = ['Estado', 'Departamento', 'Calificación'];
            if (in_array($cellValue, $headerLabels)) {
                $sheet->getStyle('A' . $row . ':C' . $row)->applyFromArray([
                    'font' => [
                        'bold' => true,
                        'color' => ['argb' => 'FFFFFF'],
                    ],
                    'fill' => [
                        'fillType' => Fill::FILL_SOLID,
                        'startColor' => ['argb' => '0C782B'],
                    ],
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => Border::BORDER_THIN,
                            'color' => ['argb' => 'FFFFFF'],
                        ],
                    ],
                ]);
            }

            // Estilar fila TOTAL
            if ($cellValue === 'TOTAL') {
                $sheet->getStyle('A' . $row . ':C' . $row)->applyFromArray([
                    'font' => ['bold' => true],
                    'fill' => [
                        'fillType' => Fill::FILL_SOLID,
                        'startColor' => ['argb' => 'DCFCE7'], // green-100
                    ],
                    'borders' => [
                        'top' => [
                            'borderStyle' => Border::BORDER_MEDIUM,
                            'color' => ['argb' => '0C782B'],
                        ],
                    ],
                ]);
            }
        }

        // Alinear columnas de números al centro
        $sheet->getStyle('B1:C' . $highestRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        return [];
    }

    /**
     * Genera las 3 gráficas nativas de Excel.
     * Las posiciones de datos se obtienen de las propiedades calculadas en array().
     */
    public function charts()
    {
        $charts = [];
        $sheetName = $this->title(); // Usar el mismo nombre que la hoja para garantizar coincidencia

        $estadoCount = count($this->stats['estados']);
        $deptoCount = count($this->stats['departamentos']);
        $califCount = count($this->stats['calificaciones']);

        // ====================================
        // GRÁFICA 1: Pie - Por Estado
        // ====================================
        if ($estadoCount > 0 && $this->estadoDataStart && $this->estadoDataEnd) {
            $categories1 = [
                new DataSeriesValues(
                    DataSeriesValues::DATASERIES_TYPE_STRING,
                    "'" . $sheetName . "'" . '!$A$' . $this->estadoDataStart . ':$A$' . $this->estadoDataEnd,
                    null,
                    $estadoCount
                )
            ];
            $values1 = [
                new DataSeriesValues(
                    DataSeriesValues::DATASERIES_TYPE_NUMBER,
                    "'" . $sheetName . "'" . '!$B$' . $this->estadoDataStart . ':$B$' . $this->estadoDataEnd,
                    null,
                    $estadoCount
                )
            ];

            // Asignar colores por estado
            $estadoFillColors = [];
            foreach ($this->stats['estados'] as $estado => $qty) {
                $estadoFillColors[] = self::ESTADO_COLORS[$estado] ?? '94A3B8';
            }
            $values1[0]->setFillColor($estadoFillColors);

            $series1 = new DataSeries(
                DataSeries::TYPE_PIECHART,
                null,
                range(0, count($values1) - 1),
                [],
                $categories1,
                $values1
            );

            // Layout para mostrar porcentajes
            $layout1 = new Layout();
            $layout1->setShowPercent(true);
            $layout1->setShowCatName(true);

            $plotArea1 = new PlotArea($layout1, [$series1]);
            $legend1 = new Legend(Legend::POSITION_RIGHT, null, false);
            $title1 = new Title('Distribución por Estado');

            $chart1 = new Chart(
                'chart_estado',
                $title1,
                $legend1,
                $plotArea1
            );
            $chart1->setTopLeftPosition('E2');
            $chart1->setBottomRightPosition('N18');

            $charts[] = $chart1;
        }

        // ====================================
        // GRÁFICA 2: Barras - Por Departamento
        // ====================================
        if ($deptoCount > 0 && $this->deptoDataStart && $this->deptoDataEnd) {
            $categories2 = [
                new DataSeriesValues(
                    DataSeriesValues::DATASERIES_TYPE_STRING,
                    "'" . $sheetName . "'" . '!$A$' . $this->deptoDataStart . ':$A$' . $this->deptoDataEnd,
                    null,
                    $deptoCount
                )
            ];
            $values2 = [
                new DataSeriesValues(
                    DataSeriesValues::DATASERIES_TYPE_NUMBER,
                    "'" . $sheetName . "'" . '!$B$' . $this->deptoDataStart . ':$B$' . $this->deptoDataEnd,
                    null,
                    $deptoCount
                )
            ];
            $values2[0]->setFillColor('0C782B');

            $labels2 = [
                new DataSeriesValues(
                    DataSeriesValues::DATASERIES_TYPE_STRING,
                    null,
                    null,
                    1,
                    ['Solicitudes']
                )
            ];

            $series2 = new DataSeries(
                DataSeries::TYPE_BARCHART,
                DataSeries::GROUPING_CLUSTERED,
                range(0, count($values2) - 1),
                $labels2,
                $categories2,
                $values2,
                DataSeries::DIRECTION_BAR
            );

            $plotArea2 = new PlotArea(null, [$series2]);
            $legend2 = new Legend(Legend::POSITION_BOTTOM, null, false);
            $title2 = new Title('Solicitudes por Departamento');

            $chart2 = new Chart(
                'chart_departamento',
                $title2,
                $legend2,
                $plotArea2
            );
            $chart2->setTopLeftPosition('E20');
            $chart2->setBottomRightPosition('N38');

            $charts[] = $chart2;
        }

        // ====================================
        // GRÁFICA 3: Pie - Por Calificación
        // ====================================
        if ($califCount > 0 && $this->califDataStart && $this->califDataEnd) {
            $categories3 = [
                new DataSeriesValues(
                    DataSeriesValues::DATASERIES_TYPE_STRING,
                    "'" . $sheetName . "'" . '!$A$' . $this->califDataStart . ':$A$' . $this->califDataEnd,
                    null,
                    $califCount
                )
            ];
            $values3 = [
                new DataSeriesValues(
                    DataSeriesValues::DATASERIES_TYPE_NUMBER,
                    "'" . $sheetName . "'" . '!$B$' . $this->califDataStart . ':$B$' . $this->califDataEnd,
                    null,
                    $califCount
                )
            ];

            // Asignar colores por calificación
            $califFillColors = [];
            foreach ($this->stats['calificaciones'] as $key => $data) {
                $califFillColors[] = self::CALIFICACION_COLORS[$key] ?? '94A3B8';
            }
            $values3[0]->setFillColor($califFillColors);

            $series3 = new DataSeries(
                DataSeries::TYPE_PIECHART,
                null,
                range(0, count($values3) - 1),
                [],
                $categories3,
                $values3
            );

            $layout3 = new Layout();
            $layout3->setShowPercent(true);
            $layout3->setShowCatName(true);

            $plotArea3 = new PlotArea($layout3, [$series3]);
            $legend3 = new Legend(Legend::POSITION_RIGHT, null, false);
            $title3 = new Title('Distribución de Calificaciones');

            $chart3 = new Chart(
                'chart_calificacion',
                $title3,
                $legend3,
                $plotArea3
            );
            $chart3->setTopLeftPosition('E40');
            $chart3->setBottomRightPosition('N56');

            $charts[] = $chart3;
        }

        return $charts;
    }
}
