<?php

namespace App\Exports;

use App\Enums\WorkAssignmentStatus;
use App\Models\WorkAssignment;
use App\Services\Works\WorkObservedIncidentsService;
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\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

final class WorkObservedIncidentsExport
{
    private const DETAIL_HEADERS = [
        'Fecha y hora',
        'Día',
        'OT',
        'Código sitio',
        'Sitio',
        'Proyecto',
        'Técnico (asignación)',
        'Coordinador',
    ];

    private const SUMMARY_HEADERS = [
        'Día',
        'Incidencias (eventos observado)',
    ];

    public function __construct(
        private readonly WorkObservedIncidentsService $service,
        private readonly ?string $dateFrom,
        private readonly ?string $dateTo,
    ) {}

    public function saveTo(string $absolutePath): void
    {
        $spreadsheet = new Spreadsheet;
        $detailSheet = $spreadsheet->getActiveSheet();
        $detailSheet->setTitle('Detalle');
        $detailSheet->fromArray(self::DETAIL_HEADERS, null, 'A1');
        $this->styleHeaderRow($detailSheet, count(self::DETAIL_HEADERS));

        $rowIndex = 2;
        $this->detailQuery()->clone()->lazy(500)->each(function (WorkAssignment $a) use ($detailSheet, &$rowIndex) {
            $w = $a->work;
            $at = $a->recorded_at;
            $detailSheet->fromArray([[
                $at?->format('Y-m-d H:i:s') ?? '',
                $at?->format('Y-m-d') ?? '',
                $w?->ot ?? '',
                $w?->site?->code ?? '',
                $w?->site?->name ?? '',
                $w?->site?->project?->name ?? '',
                $a->technician?->fullName() ?? '',
                $a->coordinator?->fullName() ?? '',
            ]], null, 'A'.$rowIndex);
            $rowIndex++;
        });

        $summarySheet = $spreadsheet->createSheet(1);
        $summarySheet->setTitle('Por día');
        $summarySheet->fromArray(self::SUMMARY_HEADERS, null, 'A1');
        $this->styleHeaderRow($summarySheet, count(self::SUMMARY_HEADERS));

        $dayExpr = $this->service->sqlDayExpression('recorded_at');
        $agg = $this->detailQuery()
            ->clone()
            ->selectRaw($dayExpr.' as day, COUNT(*) as c')
            ->groupByRaw($dayExpr)
            ->orderBy('day')
            ->get();

        $sRow = 2;
        foreach ($agg as $row) {
            $summarySheet->fromArray([[(string) $row->day, (int) $row->c]], null, 'A'.$sRow);
            $sRow++;
        }

        $spreadsheet->setActiveSheetIndex(0);

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

        $spreadsheet->disconnectWorksheets();
    }

    private function styleHeaderRow(Worksheet $sheet, int $colCount): void
    {
        $lastCol = Coordinate::stringFromColumnIndex($colCount);
        $sheet->getStyle('A1:'.$lastCol.'1')->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->freezePane('A2');
    }

    /** @return Builder<WorkAssignment> */
    private function detailQuery(): Builder
    {
        $bounds = $this->service->resolveExportBounds($this->dateFrom, $this->dateTo);
        if ($bounds === false) {
            throw new \InvalidArgumentException('Rango de fechas inválido.');
        }
        [$from, $to] = $bounds;

        return WorkAssignment::query()
            ->where('status', WorkAssignmentStatus::OBSERVADO->value)
            ->when($from !== null, fn ($q) => $q->where('recorded_at', '>=', $from))
            ->when($to !== null, fn ($q) => $q->where('recorded_at', '<=', $to))
            ->with([
                'work:id,ot,site_id',
                'work.site:id,code,name,project_id',
                'work.site.project:id,name',
                'technician:id,name,paternal_surname,maternal_surname',
                'coordinator:id,name,paternal_surname,maternal_surname',
            ])
            ->orderByDesc('recorded_at')
            ->orderByDesc('id');
    }
}
