Investidor-V2/app/Services/CartographyWorkbookAudit.php

<?php

namespace App\Services;

use RuntimeException;
use SimpleXMLElement;
use ZipArchive;

/** Inspects an authorised local workbook without importing or transforming data. */
final class CartographyWorkbookAudit
{
    /** @param array<string, mixed> $manifest
     * @return array<string, mixed>
     */
    public function inspect(string $path, array $manifest): array
    {
        if (! is_file($path) || filesize($path) > 20_000_000) {
            throw new RuntimeException('Workbook missing or larger than 20 MB.');
        }
        $checksum = hash_file('sha256', $path);
        if ($checksum === false) {
            throw new RuntimeException('Unable to read workbook checksum.');
        }
        if (! isset($manifest['sha256']) || ! hash_equals(strtolower((string) $manifest['sha256']), $checksum)) {
            throw new RuntimeException('Manifest checksum does not match the workbook.');
        }
        foreach (['inspection_authorization'] as $key) {
            if (! is_string($manifest[$key] ?? null) || trim($manifest[$key]) === '') {
                throw new RuntimeException('Missing manifest field: '.$key);
            }
        }
        if (isset($manifest['classification']) && ! in_array($manifest['classification'], ['public', 'internal', 'confidential', 'restricted'], true)) {
            throw new RuntimeException('Invalid classification.');
        }
        $zip = new ZipArchive;
        if ($zip->open($path) !== true) {
            throw new RuntimeException('Invalid XLSX container.');
        }
        try {
            $bytes = 0;
            if ($zip->numFiles > 1000) {
                throw new RuntimeException('Too many archive entries.');
            }
            for ($i = 0; $i < $zip->numFiles; $i++) {
                $entry = $zip->statIndex($i);
                if ($entry === false) {
                    throw new RuntimeException('Unreadable archive entry.');
                }
                $bytes += $entry['size'];
                if ($bytes > 50_000_000 || str_contains($entry['name'], '../')) {
                    throw new RuntimeException('Unsafe or oversized archive.');
                }
            }
            $strings = [];
            if ($zip->locateName('xl/sharedStrings.xml') !== false) {
                foreach ($this->xml($zip, 'xl/sharedStrings.xml')->xpath('//*[local-name()="si"]') ?: [] as $item) {
                    $strings[] = implode('', array_map(fn ($t) => (string) $t, $item->xpath('.//*[local-name()="t"]') ?: []));
                }
            }
            $relationships = [];
            foreach ($this->xml($zip, 'xl/_rels/workbook.xml.rels')->children() as $rel) {
                $target = (string) $rel['Target'];
                if ((string) $rel['TargetMode'] !== 'External' && ! str_contains($target, '..')) {
                    $relationships[(string) $rel['Id']] = str_starts_with($target, '/') ? ltrim($target, '/') : 'xl/'.$target;
                }
            }
            $sheets = [];
            $rowCount = 0;
            foreach ($this->xml($zip, 'xl/workbook.xml')->xpath('//*[local-name()="sheet"]') ?: [] as $sheet) {
                $id = (string) $sheet->attributes('http://schemas.openxmlformats.org/officeDocument/2006/relationships')['id'];
                if (! isset($relationships[$id])) {
                    throw new RuntimeException('Unresolved worksheet relationship.');
                }
                $rows = [];
                $seen = [];
                foreach ($this->xml($zip, $relationships[$id])->xpath('//*[local-name()="sheetData"]/*[local-name()="row"]') ?: [] as $row) {
                    if (++$rowCount > 50000) {
                        throw new RuntimeException('Audit limit of 50000 rows exceeded.');
                    }
                    $values = [];
                    $issues = [];
                    foreach ($row->children() as $cell) {
                        $address = (string) $cell['r'];
                        $value = (string) $cell->v;
                        if ((string) $cell['t'] === 's') {
                            $value = $strings[(int) $value] ?? '';
                        } elseif ((string) $cell['t'] === 'inlineStr') {
                            $value = implode('', array_map(fn ($t) => (string) $t, $cell->xpath('.//*[local-name()="t"]') ?: []));
                        }
                        $values[preg_replace('/[0-9]+$/', '', $address)] = $value;
                        if ($value === '') {
                            $issues[] = ['field' => $address, 'code' => 'empty_cell'];
                        }
                        if (isset($cell->f)) {
                            $issues[] = ['field' => $address, 'code' => 'formula_not_evaluated'];
                        }
                        if ((string) $cell['t'] === 'e') {
                            $issues[] = ['field' => $address, 'code' => 'spreadsheet_error'];
                        }
                    }
                    $hash = hash('sha256', json_encode($values, JSON_THROW_ON_ERROR));
                    $duplicate = isset($seen[$hash]);
                    $seen[$hash] = true;
                    $issues[] = ['field' => '*', 'code' => 'semantic_mapping_and_institutional_validation_pending'];
                    if (empty($manifest['original_crs'])) {
                        $issues[] = ['field' => '*', 'code' => 'original_crs_unknown'];
                    }
                    $rows[] = ['row' => (int) $row['r'], 'status' => $duplicate ? 'duplicate' : 'pending', 'populated_columns' => array_keys($values), 'issues' => $issues];
                }
                $sheets[] = ['name' => (string) $sheet['name'], 'state' => (string) $sheet['state'] ?: 'visible', 'rows' => $rows];
            }

            return ['sha256' => $checksum, 'version' => $manifest['version'] ?? null, 'classification' => $manifest['classification'] ?? null,
                'pending_metadata' => array_values(array_filter(['owner', 'version', 'received_at', 'classification', 'original_crs'], fn ($key) => empty($manifest[$key]))),
                'original_crs' => $manifest['original_crs'] ?? null, 'publication_allowed' => false, 'transformations' => [],
                'status' => 'quarantined', 'sheets' => $sheets, 'rows_inspected' => $rowCount,
                'limitations' => ['No semantic header mapping, omitted-cell inference, coordinate validation or external reference resolution.', 'No source values, coordinates or formulas copied to the report.']];
        } finally {
            $zip->close();
        }
    }

    private function xml(ZipArchive $zip, string $name): SimpleXMLElement
    {
        $content = $zip->getFromName($name);
        if ($content === false || preg_match('/<!DOCTYPE|<!ENTITY/i', $content)) {
            throw new RuntimeException('Missing or unsafe workbook XML.');
        }
        $previous = libxml_use_internal_errors(true);
        try {
            $xml = simplexml_load_string($content, SimpleXMLElement::class, LIBXML_NONET);
            if ($xml === false) {
                throw new RuntimeException('Malformed workbook XML.');
            }

            return $xml;
        } finally {
            libxml_clear_errors();
            libxml_use_internal_errors($previous);
        }
    }
}