<?php
declare(strict_types=1);

function load_questions_from_file(string $path): array {
    if (!file_exists($path) && file_exists(dirname($path) . '/questions.csv')) {
        $path = dirname($path) . '/questions.csv';
    }

    if (!file_exists($path)) {
        throw new Exception("Questions file not found: $path");
    }

    $ext = strtolower(pathinfo($path, PATHINFO_EXTENSION));

    if ($ext === 'xlsx') {
        $rows = read_xlsx_rows($path);
    } elseif ($ext === 'csv') {
        $rows = read_csv_rows($path);
    } else {
        throw new Exception("Unsupported file type: .$ext (use .xlsx or .csv)");
    }

    if (count($rows) < 2) {
        throw new Exception("No data rows found in questions file.");
    }

    $headers   = array_map(fn($h) => strtolower(trim((string)$h)), $rows[0]);
    $dataRows  = array_slice($rows, 1);

    global $REQUIRED_COLUMNS;
    $missing = array_values(array_diff($REQUIRED_COLUMNS, $headers));

    if (!empty($missing)) {
        throw new Exception("Missing required columns: " . implode(', ', $missing));
    }

    $has_correct = in_array('correct', $headers, true);
    $has_image   = in_array('image_path', $headers, true);
    $idx         = array_flip($headers);
    $questions   = [];

    foreach ($dataRows as $r) {
        $q = [
            'id'       => (string)($r[$idx['id']] ?? ''),
            'question' => (string)($r[$idx['question']] ?? ''),
            'options'  => [
                'A' => (string)($r[$idx['option_a']] ?? ''),
                'B' => (string)($r[$idx['option_b']] ?? ''),
                'C' => (string)($r[$idx['option_c']] ?? ''),
                'D' => (string)($r[$idx['option_d']] ?? '')
            ]
        ];

        if ($has_correct) {
            $q['correct'] = strtoupper(trim((string)($r[$idx['correct']] ?? '')));
        }

        if ($has_image) {
            $ip = (string)($r[$idx['image_path']] ?? '');
            if (strlen(trim($ip)) > 0) {
                $q['image_path'] = trim($ip);
            }
        }

        $questions[] = $q;
    }

    return [$questions, $has_correct];
}

function read_csv_rows(string $path): array {
    $rows = [];

    if (($h = fopen($path, 'r')) !== false) {
        while (($d = fgetcsv($h)) !== false) {
            $rows[] = $d;
        }
        fclose($h);
    }

    return $rows;
}

function read_xlsx_rows(string $path): array {
    $zip = new ZipArchive();
    if ($zip->open($path) !== true) {
        throw new Exception("Failed to open XLSX zip: $path");
    }

    $shared = [];
    $ssi    = $zip->locateName('xl/sharedStrings.xml');

    if ($ssi !== false) {
        $xml = simplexml_load_string($zip->getFromIndex($ssi));
        if ($xml && isset($xml->si)) {
            foreach ($xml->si as $si) {
                $texts = [];
                if (isset($si->t)) {
                    $texts[] = (string)$si->t;
                }
                if (isset($si->r)) {
                    foreach ($si->r as $r) {
                        if (isset($r->t)) {
                            $texts[] = (string)$r->t;
                        }
                    }
                }
                $shared[] = implode('', $texts);
            }
        }
    }

    $sheetIndex = $zip->locateName('xl/worksheets/sheet1.xml');

    if ($sheetIndex === false) {
        for ($i = 0; $i < $zip->numFiles; $i++) {
            $n = $zip->getNameIndex($i);
            if (preg_match('#^xl/worksheets/sheet\d+\.xml$#', $n)) {
                $sheetIndex = $i;
                break;
            }
        }
    }

    if ($sheetIndex === false) {
        $zip->close();
        throw new Exception("Worksheet XML not found in XLSX.");
    }

    $sheetXml = simplexml_load_string($zip->getFromIndex($sheetIndex));
    $zip->close();

    $rows = [];
    foreach ($sheetXml->sheetData->row as $row) {
        $cells = [];
        foreach ($row->c as $c) {
            $r   = (string)$c['r'];
            $col = xlsx_col_index($r);
            $val = '';

            $t = (string)$c['t'];
            if ($t === 's') {
                $idx = (int)$c->v;
                $val = $shared[$idx] ?? '';
            } elseif ($t === 'inlineStr') {
                if (isset($c->is->t)) {
                    $val = (string)$c->is->t;
                }
            } else {
                $val = isset($c->v) ? (string)$c->v : '';
            }

            $cells[$col] = $val;
        }

        if (!empty($cells)) {
            $max    = max(array_keys($cells));
            $rowArr = array_fill(0, $max + 1, '');
            foreach ($cells as $i => $v) {
                $rowArr[$i] = $v;
            }
            $rows[] = $rowArr;
        } else {
            $rows[] = [];
        }
    }

    return $rows;
}

function xlsx_col_index(string $cellRef): int {
    if (preg_match('/^([A-Z]+)/', $cellRef, $m)) {
        $letters = $m[1];
        $num     = 0;
        for ($i = 0; $i < strlen($letters); $i++) {
            $num = $num * 26 + (ord($letters[$i]) - 64);
        }
        return $num - 1;
    }
    return 0;
}
