<?php

declare(strict_types=1);

namespace App\Modules\Guest;

/**
 * GuestImporter — Zero-dependency CSV and XLSX file parsing engine.
 *
 * Utilizes built-in PHP ZipArchive and SimpleXML to parse XLSX spreadsheets.
 * Detects delimiters for CSV parsing.
 *
 * @package Inveetaire
 * @ref     PILOT-002
 */
class GuestImporter
{
    /**
     * Parse the uploaded file path into a structured raw array of rows.
     *
     * @param string $filePath Path to the file.
     * @param string $ext      File extension ('csv' or 'xlsx').
     * @return array Raw rows.
     */
    public function parseFile(string $filePath, string $ext): array
    {
        if ($ext === 'csv') {
            return $this->parseCsv($filePath);
        } elseif ($ext === 'xlsx') {
            return $this->parseXlsx($filePath);
        }
        throw new \InvalidArgumentException("Unsupported file format: '{$ext}'.");
    }

    /**
     * Parse CSV file with automatic delimiter detection.
     *
     * @param string $filePath
     * @return array
     */
    private function parseCsv(string $filePath): array
    {
        $delimiter = $this->detectDelimiter($filePath);
        $rows = [];

        if (($handle = fopen($filePath, 'r')) !== false) {
            while (($data = fgetcsv($handle, 4096, $delimiter)) !== false) {
                // Clean input encoding (UTF-8 conversion if necessary)
                $cleanedRow = array_map(function ($val) {
                    if ($val === null) {
                        return '';
                    }
                    // Handle UTF-8 encoding checks
                    $val = (string) $val;
                    if (!mb_check_encoding($val, 'UTF-8')) {
                        $val = mb_convert_encoding($val, 'UTF-8', 'ISO-8859-1');
                    }
                    return trim($val);
                }, $data);
                
                $rows[] = $cleanedRow;
            }
            fclose($handle);
        }
        return $rows;
    }

    /**
     * Detect CSV delimiter based on count of comma, semicolon, or tab.
     *
     * @param string $filePath
     * @return string
     */
    private function detectDelimiter(string $filePath): string
    {
        $handle = fopen($filePath, 'r');
        if (!$handle) {
            return ',';
        }
        $firstLine = fgets($handle);
        fclose($handle);

        if ($firstLine === false) {
            return ',';
        }

        $comma = substr_count($firstLine, ',');
        $semicolon = substr_count($firstLine, ';');
        $tab = substr_count($firstLine, "\t");

        if ($semicolon > $comma && $semicolon > $tab) {
            return ';';
        }
        if ($tab > $comma && $tab > $semicolon) {
            return "\t";
        }
        return ',';
    }

    /**
     * Zero-dependency XLSX parser leveraging ZipArchive and SimpleXML.
     *
     * @param string $filePath
     * @return array
     */
    private function parseXlsx(string $filePath): array
    {
        if (!class_exists('\ZipArchive')) {
            throw new \RuntimeException("XLSX parsing requires the PHP Zip extension. Please enable the 'zip' extension in your PHP configuration, or upload a CSV file instead.");
        }

        $zip = new \ZipArchive();
        if ($zip->open($filePath) !== true) {
            throw new \RuntimeException("Unable to open XLSX file zip archive.");
        }

        // 1. Read sharedStrings.xml if present
        $sharedStrings = [];
        $stringsRaw = $zip->getFromName('xl/sharedStrings.xml');
        if ($stringsRaw !== false) {
            $stringsXml = new \SimpleXMLElement($stringsRaw);
            foreach ($stringsXml->si as $si) {
                if (isset($si->t)) {
                    $sharedStrings[] = (string) $si->t;
                } else {
                    $text = '';
                    if (isset($si->r)) {
                        foreach ($si->r as $r) {
                            if (isset($r->t)) {
                                $text .= (string) $r->t;
                            }
                        }
                    }
                    $sharedStrings[] = $text;
                }
            }
        }

        // 2. Read sheet1.xml
        $sheetRaw = $zip->getFromName('xl/worksheets/sheet1.xml');
        if ($sheetRaw === false) {
            $zip->close();
            throw new \RuntimeException("Unable to read worksheet data in XLSX (missing sheet1.xml).");
        }

        $sheetXml = new \SimpleXMLElement($sheetRaw);
        $rows = [];

        // Parse sheetData rows
        foreach ($sheetXml->sheetData->row as $rowNode) {
            $rowNum = (int) $rowNode['r'];
            $rowData = [];
            
            foreach ($rowNode->c as $cellNode) {
                $cellRef = (string) $cellNode['r'];
                preg_match('/^[A-Z]+/', $cellRef, $matches);
                $colLetter = $matches[0] ?? 'A';
                
                $colIndex = $this->colLetterToNumber($colLetter) - 1;
                
                $type = (string) $cellNode['t'];
                $valNode = $cellNode->v;
                $value = '';
                
                if ($valNode !== null) {
                    $value = (string) $valNode;
                    if ($type === 's') {
                        $idx = (int) $value;
                        $value = $sharedStrings[$idx] ?? '';
                    }
                }
                
                $rowData[$colIndex] = trim($value);
            }
            
            // Fill any column gaps in between with empty strings
            if (!empty($rowData)) {
                $maxIndex = max(array_keys($rowData));
                for ($i = 0; $i <= $maxIndex; $i++) {
                    if (!isset($rowData[$i])) {
                        $rowData[$i] = '';
                    }
                }
                ksort($rowData);
                $rows[$rowNum] = $rowData;
            }
        }

        $zip->close();
        
        ksort($rows);
        return array_values($rows);
    }

    /**
     * Convert Excel column letters (A, B, AA) into 1-based number index.
     *
     * @param string $col
     * @return int
     */
    private function colLetterToNumber(string $col): int
    {
        $number = 0;
        $len = strlen($col);
        for ($i = 0; $i < $len; $i++) {
            $number = $number * 26 + (ord($col[$i]) - 64);
        }
        return $number;
    }
}
