<?php
declare(strict_types=1);

namespace App\Support;

use ZipArchive;

final class QuestionBank
{
    private ?array $questions = null;

    public function all(): array
    {
        if ($this->questions !== null) {
            return $this->questions;
        }

        $workbookPath = (string) config('source_files.questions');

        if (!is_file($workbookPath)) {
            return $this->questions = [];
        }

        $archive = new ZipArchive();

        if ($archive->open($workbookPath) !== true) {
            return $this->questions = [];
        }

        $sharedStrings = $this->readSharedStrings($archive);
        $cells = $this->readSheetCells($archive, 'xl/worksheets/sheet1.xml', $sharedStrings);
        $archive->close();

        $blocks = [
            ['rows' => range(3, 9), 'left' => 'R', 'right' => 'I'],
            ['rows' => range(12, 18), 'left' => 'A', 'right' => 'S'],
            ['rows' => range(21, 27), 'left' => 'E', 'right' => 'C'],
        ];

        $questions = [];
        $counters = ['R' => 0, 'I' => 0, 'A' => 0, 'S' => 0, 'E' => 0, 'C' => 0];

        foreach ($blocks as $block) {
            foreach ($block['rows'] as $rowNumber) {
                $leftCell = trim((string) ($cells['A' . $rowNumber] ?? ''));
                $rightCell = trim((string) ($cells['C' . $rowNumber] ?? ''));

                if ($leftCell !== '') {
                    $areaCode = $block['left'];
                    $counters[$areaCode]++;
                    $questions[] = [
                        'id' => $areaCode . $counters[$areaCode],
                        'area_code' => $areaCode,
                        'statement' => $leftCell,
                    ];
                }

                if ($rightCell !== '') {
                    $areaCode = $block['right'];
                    $counters[$areaCode]++;
                    $questions[] = [
                        'id' => $areaCode . $counters[$areaCode],
                        'area_code' => $areaCode,
                        'statement' => $rightCell,
                    ];
                }
            }
        }

        return $this->questions = $questions;
    }

    private function readSharedStrings(ZipArchive $archive): array
    {
        $sharedStringsXml = $archive->getFromName('xl/sharedStrings.xml');

        if ($sharedStringsXml === false) {
            return [];
        }

        $xml = simplexml_load_string($sharedStringsXml);
        $sharedStrings = [];

        if ($xml === false) {
            return [];
        }

        foreach ($xml->si as $item) {
            $text = '';

            if (isset($item->t)) {
                $text = (string) $item->t;
            } else {
                foreach ($item->r as $run) {
                    $text .= (string) $run->t;
                }
            }

            $sharedStrings[] = trim($text);
        }

        return $sharedStrings;
    }

    private function readSheetCells(ZipArchive $archive, string $sheetPath, array $sharedStrings): array
    {
        $sheetXml = $archive->getFromName($sheetPath);

        if ($sheetXml === false) {
            return [];
        }

        $xml = simplexml_load_string($sheetXml);

        if ($xml === false || !isset($xml->sheetData)) {
            return [];
        }

        $cells = [];

        foreach ($xml->sheetData->row as $row) {
            foreach ($row->c as $cell) {
                $reference = (string) $cell['r'];
                $type = (string) $cell['t'];
                $value = isset($cell->v) ? (string) $cell->v : '';

                if ($type === 's') {
                    $value = $sharedStrings[(int) $value] ?? '';
                }

                $cells[$reference] = trim($value);
            }
        }

        return $cells;
    }
}