<?php

namespace App\Services;

use Illuminate\Http\UploadedFile;
use Illuminate\Support\Facades\File;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Symfony\Component\HttpFoundation\BinaryFileResponse;

class SpreadsheetService
{
    /** Read CSV, XLS or XLSX files into header-keyed rows. */
    public function readRows(UploadedFile $file): array
    {
        $reader = IOFactory::createReaderForFile($file->getRealPath());
        $reader->setReadDataOnly(true);
        $rawRows = $reader->load($file->getRealPath())->getActiveSheet()->toArray(null, true, true, false);

        if (!$rawRows) {
            return [];
        }

        $headers = array_map(fn ($value) => $this->normalizeHeader((string) $value), array_shift($rawRows));
        $rows = [];
        foreach ($rawRows as $rawRow) {
            if (!array_filter($rawRow, fn ($value) => $value !== null && trim((string) $value) !== '')) {
                continue;
            }
            $row = [];
            foreach ($headers as $index => $header) {
                if ($header !== '') {
                    $row[$header] = trim((string) ($rawRow[$index] ?? ''));
                }
            }
            $rows[] = $row;
        }

        return $rows;
    }

    /** Build a styled XLSX response without leaving permanent export files behind. */
    public function download(string $filename, array $headers, iterable $rows, string $sheetTitle = 'Export'): BinaryFileResponse
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle(mb_substr(preg_replace('/[\\\\\/?*\[\]:]/', '', $sheetTitle) ?: 'Export', 0, 31));
        $sheet->fromArray($headers, null, 'A1');

        $rowNumber = 2;
        foreach ($rows as $row) {
            $sheet->fromArray(array_values($row), null, 'A'.$rowNumber++);
        }

        $lastColumn = $sheet->getHighestColumn();
        $sheet->freezePane('A2');
        $sheet->setAutoFilter("A1:{$lastColumn}1");
        $sheet->getStyle("A1:{$lastColumn}1")->applyFromArray([
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '0F766E']],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER],
        ]);
        foreach (range('A', $lastColumn) as $column) {
            $sheet->getColumnDimension($column)->setAutoSize(true);
        }

        $directory = storage_path('app/private/exports');
        File::ensureDirectoryExists($directory);
        $path = $directory.'/'.uniqid('export_', true).'.xlsx';
        (new Xlsx($spreadsheet))->save($path);
        $spreadsheet->disconnectWorksheets();

        return response()->download($path, str_ends_with($filename, '.xlsx') ? $filename : $filename.'.xlsx', [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        ])->deleteFileAfterSend(true);
    }

    private function normalizeHeader(string $header): string
    {
        $header = preg_replace('/^\xEF\xBB\xBF/', '', trim($header));
        return strtolower(preg_replace('/\s+/', ' ', $header));
    }
}
