<?php

namespace App\Exports;

use App\Enums\WorkInternalStatus;
use App\Enums\WorkStatus;
use App\Models\Work;
use App\Support\Works\WorkOperationalEfficiencySnapshot;
use Illuminate\Database\Eloquent\Builder;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

final class WorksOperationalEfficiencyExport
{
    private const HEADERS = [
        'OT',
        'Código sitio',
        'Nombre sitio',
        'Proyecto',
        'Tipo de trabajo',
        'Técnico',
        'Estado externo',
        'Estado interno',
        'Fecha programada',
        'Fecha escala',
        'SLA (h contratadas)',
        'Límite SLA (escala + SLA)',
        'Fecha clareo',
        'Duración ejecución (h)',
        'Etiqueta ejecución',
        'Cumple SLA',
        'Margen vs límite SLA (h)',
        'Efectividad SLA (%)',
        'Verdicto SLA',
    ];

    /** @param  Builder<Work>  $worksQuery */
    public function __construct(
        private readonly Builder $worksQuery,
    ) {}

    public function saveTo(string $absolutePath): void
    {
        $spreadsheet = new Spreadsheet;
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->fromArray(self::HEADERS, null, 'A1');

        $rowIndex = 2;
        $this->worksQuery->clone()->lazy(500)->each(function (Work $work) use ($sheet, &$rowIndex) {
            $sheet->fromArray([$this->rowValues($work)], null, 'A'.$rowIndex);
            $rowIndex++;
        });

        $lastCol = Coordinate::stringFromColumnIndex(count(self::HEADERS));
        $lastRow = max(1, $rowIndex - 1);
        $headerRange = 'A1:'.$lastCol.'1';
        $filterRange = 'A1:'.$lastCol.$lastRow;

        $sheet->getStyle($headerRange)->applyFromArray([
            'font' => [
                'bold' => true,
                'color' => ['argb' => Color::COLOR_WHITE],
            ],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['argb' => 'FF0D9488'],
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
        ]);

        $sheet->setAutoFilter($filterRange);
        $sheet->freezePane('A2');

        (new Xlsx($spreadsheet))->save($absolutePath);

        $spreadsheet->disconnectWorksheets();
    }

    /** @return list<int|float|string|null> */
    private function rowValues(Work $work): array
    {
        $snap = WorkOperationalEfficiencySnapshot::forWork($work);

        $cumple = '';
        $margen = '';
        $efectividad = '';
        $verdicto = '';
        $durH = '';
        $durLabel = '';
        $limite = '';

        if ($snap !== null) {
            $durH = $snap->executionHours !== null ? $snap->executionHours : '';
            $durLabel = $snap->executionLabel ?? '';
            $limite = $snap->slaDeadline?->format('Y-m-d H:i:s') ?? '';
            if ($snap->slaMet === true) {
                $cumple = 'Sí';
            } elseif ($snap->slaMet === false) {
                $cumple = 'No';
            } else {
                $cumple = 'N/D';
            }
            $margen = $snap->slaMarginHours !== null ? $snap->slaMarginHours : '';
            $efectividad = $snap->slaEffectivenessPercent !== null ? $snap->slaEffectivenessPercent : '';
            $verdicto = $snap->slaVerdictLabel;
        }

        return [
            $work->ot,
            $work->site?->code,
            $work->site?->name,
            $work->site?->project?->name,
            $work->workType?->name,
            $work->technician?->fullName() ?? '',
            WorkStatus::tryFrom((string) $work->work_status)?->label() ?? $work->work_status,
            WorkInternalStatus::tryFrom((string) $work->internal_status)?->label() ?? $work->internal_status,
            $work->scheduled_date?->format('Y-m-d H:i:s'),
            $work->scale_datetime?->format('Y-m-d H:i:s'),
            $work->sla_hours,
            $limite,
            $work->clearance_datetime?->format('Y-m-d H:i:s'),
            $durH,
            $durLabel,
            $cumple,
            $margen,
            $efectividad,
            $verdicto,
        ];
    }
}
