<?php

namespace App\Support\MedicalReport;

use Illuminate\Support\Arr;
use RuntimeException;
use SimpleXMLElement;
use ZipArchive;

class TemplateWorkbook
{
    /**
     * @var array{path: string, departments: list<array<string, mixed>>, sheets: array<string, array<string, mixed>>, order: list<string>}|null
     */
    private ?array $definition = null;

    public function __construct(
        private readonly DepartmentSlugger $departmentSlugger,
        private readonly DepartmentCatalog $departmentCatalog,
    ) {}

    /**
     * @return array{path: string, departments: list<array<string, mixed>>, sheets: array<string, array<string, mixed>>, order: list<string>}
     */
    public function definition(): array
    {
        if ($this->definition !== null) {
            return $this->definition;
        }

        $path = storage_path('app/private/templates/medical-report-template.xlsx');

        if (! file_exists($path)) {
            throw new RuntimeException('Medical report template is missing.');
        }

        return $this->definition = $this->parseWorkbook($path);
    }

    /**
     * @return list<string>
     */
    public function sheetKeys(): array
    {
        return $this->definition()['order'];
    }

    /**
     * @return array<string, mixed>
     */
    public function sheet(string $sheetKey): array
    {
        return $this->definition()['sheets'][$sheetKey];
    }

    /**
     * @return list<array<string, mixed>>
     */
    public function departments(): array
    {
        return $this->definition()['departments'];
    }

    /**
     * @return array<string, mixed>|null
     */
    public function department(string $departmentKey): ?array
    {
        return collect($this->departments())->firstWhere('key', $departmentKey);
    }

    public function firstSheetKey(): ?string
    {
        return $this->definition()['order'][0] ?? null;
    }

    public function keyForName(string $sheetName): ?string
    {
        foreach ($this->definition()['sheets'] as $sheetKey => $sheet) {
            if ($sheet['name'] === $sheetName) {
                return $sheetKey;
            }
        }

        return null;
    }

    /**
     * @return array{path: string, departments: list<array<string, mixed>>, sheets: array<string, array<string, mixed>>, order: list<string>}
     */
    private function parseWorkbook(string $path): array
    {
        $archive = new ZipArchive;

        if ($archive->open($path) !== true) {
            throw new RuntimeException('Unable to open medical report template.');
        }

        $sharedStrings = $this->sharedStrings($archive);
        $relations = $this->workbookRelations($archive);
        $workbookXml = $this->loadXml($archive, 'xl/workbook.xml');

        $sheets = [];
        $order = [];
        $departments = [];
        $sheetIndex = 1;

        foreach ($workbookXml->sheets->sheet as $sheetNode) {
            $sheetName = (string) $sheetNode['name'];
            $relationId = (string) $sheetNode->attributes('r', true)->id;
            $sheetPath = Arr::get($relations, $relationId);

            if ($sheetPath === null) {
                continue;
            }

            $sheetKey = $this->sheetKey($sheetIndex, $sheetName);
            $sheet = $this->parseSheet(
                $this->loadXml($archive, $sheetPath),
                $sheetKey,
                $sheetName,
                $sharedStrings,
            );

            unset($sheet['departments']);

            $sheets[$sheetKey] = $sheet;
            $order[] = $sheetKey;
            $sheetIndex++;
        }

        $archive->close();

        foreach ($this->departmentCatalog->departments() as $department) {
            $departments[] = $department;
        }

        return [
            'path' => $path,
            'departments' => array_values($departments),
            'sheets' => $sheets,
            'order' => $order,
        ];
    }

    /**
     * @return array<string, string>
     */
    private function workbookRelations(ZipArchive $archive): array
    {
        $relationsXml = $this->loadXml($archive, 'xl/_rels/workbook.xml.rels');
        $relations = [];

        foreach ($relationsXml->Relationship as $relation) {
            $target = (string) $relation['Target'];
            $relations[(string) $relation['Id']] = str_starts_with($target, 'xl/')
                ? $target
                : 'xl/'.$target;
        }

        return $relations;
    }

    /**
     * @return list<string>
     */
    private function sharedStrings(ZipArchive $archive): array
    {
        if ($archive->locateName('xl/sharedStrings.xml') === false) {
            return [];
        }

        $sharedStringsXml = $this->loadXml($archive, 'xl/sharedStrings.xml');
        $sharedStrings = [];

        foreach ($sharedStringsXml->si as $stringNode) {
            $sharedStrings[] = $this->nodeText($stringNode);
        }

        return $sharedStrings;
    }

    /**
     * @param  list<string>  $sharedStrings
     * @return array<string, mixed>
     */
    private function parseSheet(
        SimpleXMLElement $sheetXml,
        string $sheetKey,
        string $sheetName,
        array $sharedStrings,
    ): array {
        $formulaCount = 0;
        $editableCount = 0;
        $fieldsByDepartment = [];
        $departments = [];
        $columnLabels = [];

        foreach ($sheetXml->sheetData->row as $rowNode) {
            $rowLabel = null;
            $editableCells = [];

            foreach ($rowNode->c as $cellNode) {
                $coordinate = strtoupper((string) $cellNode['r']);
                $parts = Coordinates::split($coordinate);
                $formula = isset($cellNode->f) ? trim((string) $cellNode->f) : null;
                $value = $this->cellValue($cellNode, $sharedStrings);
                $normalizedValue = trim((string) $value);

                if ($formula !== null && $formula !== '') {
                    $formulaCount++;

                    continue;
                }

                if ($this->isTextLabel($normalizedValue)) {
                    $columnLabels[$parts['column']] = $normalizedValue;
                    $rowLabel ??= $parts['column'] <= 3 ? $normalizedValue : $rowLabel;

                    continue;
                }

                if (! $this->isEditableValue($normalizedValue)) {
                    continue;
                }

                $editableCells[] = [
                    'coordinate' => $coordinate,
                    'default' => $normalizedValue,
                    'row' => $parts['row'],
                    'column' => $parts['column'],
                ];
            }

            if ($editableCells === [] || $rowLabel === null) {
                continue;
            }

            $departmentKey = $this->departmentSlugger->slug($rowLabel);

            if ($departmentKey === null) {
                continue;
            }

            $departments[$departmentKey] ??= $rowLabel;

            foreach ($editableCells as $editableCell) {
                $columnLabel = $columnLabels[$editableCell['column']]
                    ?? Coordinates::numberToColumn($editableCell['column']);

                $fieldsByDepartment[$departmentKey][] = [
                    'coordinate' => $editableCell['coordinate'],
                    'default' => $editableCell['default'],
                    'row' => $editableCell['row'],
                    'column' => $editableCell['column'],
                    'row_label' => $rowLabel,
                    'column_label' => $columnLabel,
                ];
                $editableCount++;
            }
        }

        return [
            'key' => $sheetKey,
            'name' => $sheetName,
            'editable_count' => $editableCount,
            'formula_count' => $formulaCount,
            'fields_by_department' => $fieldsByDepartment,
            'departments' => $departments,
        ];
    }

    /**
     * @param  list<string>  $sharedStrings
     */
    private function cellValue(SimpleXMLElement $cellNode, array $sharedStrings): ?string
    {
        $type = (string) $cellNode['t'];
        $rawValue = isset($cellNode->v) ? (string) $cellNode->v : null;

        if ($type === 's' && $rawValue !== null) {
            return $sharedStrings[(int) $rawValue] ?? null;
        }

        if ($type === 'inlineStr') {
            return $this->nodeText($cellNode->is);
        }

        return $rawValue;
    }

    private function nodeText(SimpleXMLElement $node): string
    {
        $xml = $node->asXML();

        if ($xml === false) {
            return '';
        }

        preg_match_all('/<(?:\w+:)?t[^>]*>(.*?)<\/(?:\w+:)?t>/u', $xml, $matches);

        return html_entity_decode(implode('', $matches[1] ?? []), ENT_QUOTES | ENT_XML1, 'UTF-8');
    }

    private function isTextLabel(string $value): bool
    {
        return $value !== '' && preg_match('/\p{L}/u', $value) === 1;
    }

    private function isEditableValue(string $value): bool
    {
        if ($value === '' || $value === '%') {
            return false;
        }

        if (preg_match('/^\d+\.$/', $value) === 1) {
            return false;
        }

        return preg_match('/\p{L}/u', $value) !== 1;
    }

    private function sheetKey(int $sheetIndex, string $sheetName): string
    {
        $slug = str($sheetName)->ascii()->slug()->value();

        return $slug !== ''
            ? sprintf('%d-%s', $sheetIndex, $slug)
            : sprintf('sheet-%d', $sheetIndex);
    }

    private function loadXml(ZipArchive $archive, string $path): SimpleXMLElement
    {
        $contents = $archive->getFromName($path);

        if ($contents === false) {
            throw new RuntimeException(sprintf('Unable to read [%s] from workbook.', $path));
        }

        $xml = simplexml_load_string($contents);

        if (! $xml instanceof SimpleXMLElement) {
            throw new RuntimeException(sprintf('Unable to parse XML [%s].', $path));
        }

        return $xml;
    }
}