<?php
declare(strict_types=1);

/**
 * Excel / CSV reader — wraps PhpSpreadsheet
 */
class ExcelReader
{
    private string $filePath;
    private string $extension;

    public function __construct(string $filePath)
    {
        $this->filePath  = $filePath;
        $this->extension = strtolower(pathinfo($filePath, PATHINFO_EXTENSION));
    }

    /**
     * Detect which column holds URLs by scanning header row and first few data rows.
     * Returns column letter (A, B, C ...) or the explicitly requested column.
     */
    public function detectUrlColumn(string $hintColumn = ''): string
    {
        if ($hintColumn !== '') {
            return strtoupper($hintColumn);
        }

        $rows = $this->readRaw(5);
        if (empty($rows)) return 'A';

        // Look for a column whose header or first value looks like a URL
        $colCount = max(array_map('count', $rows));
        for ($col = 0; $col < $colCount; $col++) {
            foreach ($rows as $row) {
                $val = trim((string)($row[$col] ?? ''));
                if (str_starts_with($val, 'http') && filter_var($val, FILTER_VALIDATE_URL)) {
                    return $this->numToLetter($col);
                }
            }
        }
        return 'A';
    }

    /**
     * Read all URLs from the given column.
     * Returns array of ['row_number' => int, 'product_url' => string]
     */
    public function readUrls(string $column, int $maxRows = 0): array
    {
        $colIndex = $this->letterToNum($column);
        $raw      = $this->readRaw();
        $results  = [];
        $rowNum   = 0;

        foreach ($raw as $index => $row) {
            $val = trim((string)($row[$colIndex] ?? ''));
            // Skip header rows and empty cells
            if (empty($val) || (!str_starts_with($val, 'http') && !str_starts_with($val, '//'))) {
                continue;
            }
            if (!filter_var($val, FILTER_VALIDATE_URL)) {
                continue;
            }
            $rowNum++;
            $results[] = ['row_number' => $rowNum, 'product_url' => $val];
            if ($maxRows > 0 && $rowNum >= $maxRows) break;
        }

        return $results;
    }

    // --- Internal ---

    private function readRaw(int $limit = 0): array
    {
        if ($this->extension === 'csv') {
            return $this->readCsv($limit);
        }
        return $this->readSpreadsheet($limit);
    }

    private function readSpreadsheet(int $limit = 0): array
    {
        require_once APP_ROOT . '/vendor/autoload.php';
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReaderForFile($this->filePath);
        $reader->setReadDataOnly(true);
        $spreadsheet = $reader->load($this->filePath);
        $sheet       = $spreadsheet->getActiveSheet();

        $rows  = [];
        $count = 0;
        foreach ($sheet->getRowIterator() as $row) {
            $cellIter = $row->getCellIterator();
            $cellIter->setIterateOnlyExistingCells(false);
            $rowData = [];
            foreach ($cellIter as $cell) {
                $rowData[] = $cell->getValue();
            }
            $rows[] = $rowData;
            $count++;
            if ($limit > 0 && $count >= $limit) break;
        }
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        return $rows;
    }

    private function readCsv(int $limit = 0): array
    {
        $rows  = [];
        $count = 0;
        $fp    = fopen($this->filePath, 'r');
        if (!$fp) return [];
        while (($row = fgetcsv($fp)) !== false) {
            $rows[] = $row;
            $count++;
            if ($limit > 0 && $count >= $limit) break;
        }
        fclose($fp);
        return $rows;
    }

    private function letterToNum(string $letter): int
    {
        $letter = strtoupper($letter);
        $num    = 0;
        $len    = strlen($letter);
        for ($i = 0; $i < $len; $i++) {
            $num = $num * 26 + (ord($letter[$i]) - ord('A') + 1);
        }
        return $num - 1;
    }

    private function numToLetter(int $num): string
    {
        $letter = '';
        $num++;
        while ($num > 0) {
            $num--;
            $letter = chr($num % 26 + ord('A')) . $letter;
            $num    = intdiv($num, 26);
        }
        return $letter;
    }
}
