<?php

namespace App\Exports;

use Carbon\Carbon;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithColumnWidths;
use Maatwebsite\Excel\Events\AfterSheet;
use App\Repositories\Hrm\Payroll\SalaryRepository;

class PlanillaSueldosExport implements FromArray, WithTitle, WithEvents, WithColumnWidths, WithStyles
{
    protected string $date;
    protected ?int $departmentId;
    protected SalaryRepository $salaryRepository;
    protected array $planillaData;

    // Columnas fijas y dinámicas
    protected array $columns = [];
    protected array $incomeTypes = [];
    protected array $deductionTypes = [];

    // Meses en español
    private array $mesesEs = [
        1 => 'Enero', 2 => 'Febrero', 3 => 'Marzo', 4 => 'Abril',
        5 => 'Mayo', 6 => 'Junio', 7 => 'Julio', 8 => 'Agosto',
        9 => 'Septiembre', 10 => 'Octubre', 11 => 'Noviembre', 12 => 'Diciembre',
    ];

    public function __construct(string $date, ?int $departmentId, SalaryRepository $salaryRepository)
    {
        $this->date = $date;
        $this->departmentId = $departmentId;
        $this->salaryRepository = $salaryRepository;
        $this->planillaData = $this->salaryRepository->getPlanillaData($date, $departmentId);
        $this->incomeTypes = $this->planillaData['income_types'] ?? [];
        $this->deductionTypes = $this->planillaData['deduction_types'] ?? [];
        $this->columns = $this->buildColumnMap();
    }

    /**
     * Construir mapa de columnas dinámico.
     * Estructura: [key => ['header' => 'Título', 'width' => N, 'type' => 'money|center|text']]
     */
    private function buildColumnMap(): array
    {
        $cols = [];

        // Columnas fijas de inicio
        $cols[] = ['key' => 'emp_code', 'header' => 'Emp.', 'width' => 7, 'type' => 'center'];
        $cols[] = ['key' => 'nombre', 'header' => 'Empleado', 'width' => 32, 'type' => 'text'];
        $cols[] = ['key' => 'salario_base', 'header' => 'Salario', 'width' => 12, 'type' => 'money'];
        $cols[] = ['key' => 'dias_trabajados', 'header' => 'Días Trab.', 'width' => 7, 'type' => 'center'];
        $cols[] = ['key' => 'salario_devengado', 'header' => 'Salario Devengado', 'width' => 14, 'type' => 'money'];
        $cols[] = ['key' => 'h_extra', 'header' => 'H. Extra', 'width' => 9, 'type' => 'money'];

        // Columnas dinámicas de ingresos
        foreach ($this->incomeTypes as $name) {
            $cols[] = ['key' => 'income_' . $name, 'header' => $name, 'width' => 13, 'type' => 'money'];
        }

        $cols[] = ['key' => 'total_ingresos', 'header' => 'Total Ingresos', 'width' => 14, 'type' => 'money'];

        // Columnas fijas de descuentos de ley
        $cols[] = ['key' => 'isss', 'header' => 'ISSS', 'width' => 10, 'type' => 'money'];
        $cols[] = ['key' => 'afp', 'header' => 'AFP', 'width' => 10, 'type' => 'money'];
        $cols[] = ['key' => 'renta', 'header' => 'Renta', 'width' => 10, 'type' => 'money'];

        // Columnas dinámicas de descuentos
        foreach ($this->deductionTypes as $name) {
            $cols[] = ['key' => 'deduction_' . $name, 'header' => $name, 'width' => 13, 'type' => 'money'];
        }

        $cols[] = ['key' => 'total_descs', 'header' => 'Total Descs.', 'width' => 13, 'type' => 'money'];
        $cols[] = ['key' => 'liquido_recibir', 'header' => 'Líquido a Recibir', 'width' => 14, 'type' => 'money'];

        return $cols;
    }

    /**
     * Obtener la letra de columna Excel para un índice (0=A, 1=B, ..., 26=AA)
     */
    private function colLetter(int $index): string
    {
        $letter = '';
        $index++;
        while ($index > 0) {
            $index--;
            $letter = chr(65 + ($index % 26)) . $letter;
            $index = intdiv($index, 26);
        }
        return $letter;
    }

    private function lastColLetter(): string
    {
        return $this->colLetter(count($this->columns) - 1);
    }

    /**
     * Obtener valor de empleado para una columna específica.
     */
    private function getEmpValue(array $emp, array $col): mixed
    {
        $key = $col['key'];

        if (str_starts_with($key, 'income_')) {
            $name = substr($key, 7);
            return $emp['ingresos_by_type'][$name] ?? 0;
        }
        if (str_starts_with($key, 'deduction_')) {
            $name = substr($key, 10);
            return $emp['descuentos_by_type'][$name] ?? 0;
        }

        return $emp[$key] ?? 0;
    }

    public function array(): array
    {
        return [];
    }

    public function title(): string
    {
        return 'Planilla de Sueldos';
    }

    public function columnWidths(): array
    {
        $widths = [];
        foreach ($this->columns as $i => $col) {
            $widths[$this->colLetter($i)] = $col['width'];
        }
        return $widths;
    }

    public function styles(Worksheet $sheet)
    {
        return [];
    }

    private function buildSubtitle(): string
    {
        $range = $this->planillaData['range'] ?? [];
        $startDate = Carbon::parse($range['start'] ?? $this->date);
        $endDate = Carbon::parse($range['end'] ?? $this->date);

        $day1 = $startDate->day;
        $day2 = $endDate->day;
        $month = $this->mesesEs[(int) $startDate->month] ?? $startDate->format('F');
        $year = $startDate->year;

        return "PLANILLA DE SUELDOS, del " . str_pad($day1, 2, '0', STR_PAD_LEFT)
            . " al " . str_pad($day2, 2, '0', STR_PAD_LEFT)
            . " de " . $month . " de " . $year;
    }

    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function (AfterSheet $event) {
                $sheet = $event->sheet->getDelegate();
                $departments = $this->planillaData['departments'] ?? collect();
                $companyName = base_settings('company_name') ?? 'EMPRESA';
                $lastCol = $this->lastColLetter();
                $totalColumns = count($this->columns);

                $currentRow = 1;

                // ── FILA 1: Nombre de la empresa ──
                $sheet->mergeCells("A{$currentRow}:{$lastCol}{$currentRow}");
                $sheet->setCellValue("A{$currentRow}", strtoupper($companyName));
                $sheet->getStyle("A{$currentRow}")->applyFromArray([
                    'font' => ['bold' => true, 'size' => 14],
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                    ],
                ]);
                $sheet->getRowDimension($currentRow)->setRowHeight(28);
                $currentRow++;

                // ── FILA 2: Subtítulo ──
                $sheet->mergeCells("A{$currentRow}:{$lastCol}{$currentRow}");
                $sheet->setCellValue("A{$currentRow}", $this->buildSubtitle());
                $sheet->getStyle("A{$currentRow}")->applyFromArray([
                    'font' => ['bold' => true, 'size' => 11],
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical' => Alignment::VERTICAL_CENTER,
                    ],
                ]);
                $sheet->getRowDimension($currentRow)->setRowHeight(22);
                $currentRow++;

                // ── FILA 3: "Personal Permanente" ──
                $sheet->mergeCells("A{$currentRow}:{$lastCol}{$currentRow}");
                $sheet->setCellValue("A{$currentRow}", 'Personal Permanente');
                $sheet->getStyle("A{$currentRow}")->applyFromArray([
                    'font' => ['bold' => true, 'size' => 10],
                    'alignment' => ['horizontal' => Alignment::HORIZONTAL_LEFT, 'vertical' => Alignment::VERTICAL_CENTER],
                ]);
                $currentRow++;

                // Fila 4: vacía
                $currentRow++;

                // ── Totales generales ──
                $grandTotals = array_fill(0, $totalColumns, 0);

                $dataStartRow = $currentRow;

                // ── POR CADA DEPARTAMENTO ──
                foreach ($departments as $deptName => $employees) {
                    // Fila de departamento
                    $sheet->mergeCells("A{$currentRow}:{$lastCol}{$currentRow}");
                    $sheet->setCellValue("A{$currentRow}", strtoupper($deptName));
                    $sheet->getStyle("A{$currentRow}")->applyFromArray([
                        'font' => ['bold' => true, 'size' => 10],
                        'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'D9E1F2']],
                        'alignment' => ['horizontal' => Alignment::HORIZONTAL_LEFT, 'vertical' => Alignment::VERTICAL_CENTER],
                    ]);
                    $currentRow++;

                    // Fila de encabezados
                    foreach ($this->columns as $i => $col) {
                        $sheet->setCellValue("{$this->colLetter($i)}{$currentRow}", $col['header']);
                    }
                    $sheet->getStyle("A{$currentRow}:{$lastCol}{$currentRow}")->applyFromArray([
                        'font' => ['bold' => true, 'size' => 9],
                        'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER, 'wrapText' => true],
                        'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'E2EFDA']],
                        'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
                    ]);
                    $sheet->getRowDimension($currentRow)->setRowHeight(30);
                    $currentRow++;

                    // Subtotales por departamento
                    $deptTotals = array_fill(0, $totalColumns, 0);

                    // Filas de empleados
                    foreach ($employees as $emp) {
                        foreach ($this->columns as $i => $col) {
                            $val = $this->getEmpValue($emp, $col);
                            $sheet->setCellValue("{$this->colLetter($i)}{$currentRow}", $val);

                            // Acumular totales (solo numéricos)
                            if ($col['type'] !== 'text') {
                                $numVal = is_numeric($val) ? (float) $val : 0;
                                $deptTotals[$i] += $numVal;
                            }
                        }

                        // Estilos de fila
                        $sheet->getStyle("A{$currentRow}:{$lastCol}{$currentRow}")->applyFromArray([
                            'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
                            'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
                        ]);

                        // Nombre a la izquierda
                        $sheet->getStyle("B{$currentRow}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
                        // Código centrado
                        $sheet->getStyle("A{$currentRow}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

                        $currentRow++;
                    }

                    // Fila SUBTOTAL
                    $sheet->setCellValue("A{$currentRow}", '');
                    $sheet->setCellValue("B{$currentRow}", 'SUBTOTAL ' . strtoupper($deptName));
                    for ($i = 2; $i < $totalColumns; $i++) {
                        $col = $this->columns[$i];
                        if ($col['type'] === 'money') {
                            $sheet->setCellValue("{$this->colLetter($i)}{$currentRow}", $deptTotals[$i]);
                        } elseif ($col['type'] === 'center') {
                            $sheet->setCellValue("{$this->colLetter($i)}{$currentRow}", '');
                        }
                    }

                    $sheet->getStyle("A{$currentRow}:{$lastCol}{$currentRow}")->applyFromArray([
                        'font' => ['bold' => true, 'size' => 9],
                        'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'FCE4D6']],
                        'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
                        'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
                    ]);

                    // Acumular gran total
                    for ($i = 0; $i < $totalColumns; $i++) {
                        $grandTotals[$i] += $deptTotals[$i];
                    }

                    $currentRow++;
                    $currentRow++; // separador
                }

                // ── FILA TOTAL GENERAL ──
                $sheet->setCellValue("A{$currentRow}", '');
                $sheet->setCellValue("B{$currentRow}", 'TOTAL GENERAL');
                for ($i = 2; $i < $totalColumns; $i++) {
                    $col = $this->columns[$i];
                    if ($col['type'] === 'money') {
                        $sheet->setCellValue("{$this->colLetter($i)}{$currentRow}", $grandTotals[$i]);
                    } elseif ($col['type'] === 'center') {
                        $sheet->setCellValue("{$this->colLetter($i)}{$currentRow}", '');
                    }
                }

                $sheet->getStyle("A{$currentRow}:{$lastCol}{$currentRow}")->applyFromArray([
                    'font' => ['bold' => true, 'size' => 10],
                    'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'DAEEF3']],
                    'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_MEDIUM]],
                    'alignment' => ['vertical' => Alignment::VERTICAL_CENTER],
                ]);

                // ── Formato numérico para columnas monetarias ──
                foreach ($this->columns as $i => $col) {
                    if ($col['type'] === 'money') {
                        $letter = $this->colLetter($i);
                        $sheet->getStyle("{$letter}{$dataStartRow}:{$letter}{$currentRow}")
                            ->getNumberFormat()->setFormatCode('#,##0.00');
                        $sheet->getStyle("{$letter}{$dataStartRow}:{$letter}{$currentRow}")
                            ->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
                    }
                }

                // ── Fecha de generación ──
                $currentRow += 2;
                $sheet->setCellValue("A{$currentRow}", 'Generado: ' . now()->format('d/m/Y H:i:s'));
                $sheet->getStyle("A{$currentRow}")->applyFromArray([
                    'font' => ['italic' => true, 'size' => 8, 'color' => ['rgb' => '808080']],
                ]);

                // ── Configuración de impresión ──
                $sheet->getPageSetup()->setOrientation(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::ORIENTATION_LANDSCAPE);
                $sheet->getPageSetup()->setPaperSize(\PhpOffice\PhpSpreadsheet\Worksheet\PageSetup::PAPERSIZE_LEGAL);
                $sheet->getPageSetup()->setFitToWidth(1);
                $sheet->getPageSetup()->setFitToHeight(0);
                $sheet->getPageMargins()->setTop(0.5);
                $sheet->getPageMargins()->setBottom(0.5);
                $sheet->getPageMargins()->setLeft(0.3);
                $sheet->getPageMargins()->setRight(0.3);
                $sheet->getPageSetup()->setRowsToRepeatAtTopByStartAndEnd(1, 3);
            },
        ];
    }
}