<?php
declare(strict_types=1);

/**
 * Excel writer — generates the result XLSX using PhpSpreadsheet 3.x
 */
class ExcelWriter
{
    /**
     * @param array  $results  Array of DB result rows (ordered by row_number ASC)
     * @param string $outPath  Full path to write the XLSX
     */
    public static function write(array $results, string $outPath): void
    {
        require_once APP_ROOT . '/vendor/autoload.php';

        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet       = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Image Results');

        // Helper: get cell by 1-based column + row (PhpSpreadsheet 3.x compatible)
        $cell = static function (int $col, int $row) use ($sheet): \PhpOffice\PhpSpreadsheet\Cell\Cell {
            $coord = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col) . $row;
            return $sheet->getCell($coord);
        };
        $style = static function (int $col, int $row) use ($sheet): \PhpOffice\PhpSpreadsheet\Style\Style {
            $coord = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col) . $row;
            return $sheet->getStyle($coord);
        };

        // Header row
        $headers = ['#', 'Product URL', 'Extracted Image URL', 'Status', 'Error', 'Fallback Level'];
        foreach ($headers as $c => $header) {
            $colIdx = $c + 1;
            $cell($colIdx, 1)->setValue($header);
            $style($colIdx, 1)->getFont()->setBold(true);
            $style($colIdx, 1)->getFill()
                ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                ->getStartColor()->setARGB('FF1E3A5F');
            $style($colIdx, 1)->getFont()->getColor()->setARGB('FFFFFFFF');
        }

        // Data rows
        foreach ($results as $i => $row) {
            $r = $i + 2;
            $cell(1, $r)->setValue($row['row_number']);
            $cell(2, $r)->setValue($row['product_url']);
            $cell(3, $r)->setValue($row['image_url'] ?? '');
            $cell(4, $r)->setValue(ucfirst($row['status']));
            $cell(5, $r)->setValue($row['error_message'] ?? '');
            $cell(6, $r)->setValue($row['fallback_level'] ?? '');

            // Color-code status column
            $statusColor = match ($row['status']) {
                'success' => 'FFD4EDDA',
                'failed'  => 'FFF8D7DA',
                default   => 'FFFEF3CD',
            };
            $style(4, $r)->getFill()
                ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                ->getStartColor()->setARGB($statusColor);
        }

        // Auto-size columns A–F
        foreach (range(1, 6) as $col) {
            $letter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($col);
            $sheet->getColumnDimension($letter)->setAutoSize(true);
        }

        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save($outPath);
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
    }
}
