VITEC-website/packages/vitec/Classes/Import/ProductXlsxReader.php

<?php

declare(strict_types=1);

namespace Evomedien\Vitec\Import;

use PhpOffice\PhpSpreadsheet\IOFactory;

/**
 * Reads one agency-delivered "Product Page Content" workbook into a structured
 * array the import module can work with.
 *
 * The workbook format (verified against the Aligo, Arqa and AV-over-IP&KVM
 * deliveries of 2026-08): a meta block in the top rows (URL, primary keyword,
 * intent, SEO approach), a component table whose header row carries "Component"
 * in column B, and a second sheet "Page SEO Check" with one header and one
 * value row.
 *
 * The component column is a controlled vocabulary and doubles as the page-type
 * detector: product files carry "Product Introduction", category files carry
 * "Category Introduction". Category files use the same file format but a
 * different vocabulary and target a page rather than a record - they are
 * recognised here and rejected by the import flow, never guessed at.
 *
 * Source selectors (what the persisted mapping stores) are stable strings:
 *
 *   c:<Component>#<n>:<cell>   one cell of the n-th occurrence of a component
 *                              (cell = title | body | cta)
 *   m:<key>                    meta block (url, primaryKeyword, intent, seoApproach)
 *   s:<Header>                 one column of the "Page SEO Check" value row
 *
 * They survive across deliveries because the vocabulary is stable - that is
 * what makes the saved mapping reusable for every next product.
 */
final class ProductXlsxReader
{
    /**
     * Explicitly marked "Internal note - not website copy" in the deliveries;
     * never offered as an import source.
     */
    private const HIDDEN_COMPONENTS = ['Technical Gap / Validation Note'];

    /** Meta-block labels (column A, lowercased) => meta keys. */
    private const META_LABELS = [
        'url' => 'url',
        'primary keyword' => 'primaryKeyword',
        'intent' => 'intent',
        'seo approach' => 'seoApproach',
    ];

    /**
     * @return array{
     *   pageType: string,
     *   meta: array<string,string>,
     *   components: array<int,array<string,mixed>>,
     *   seo: array<string,string>
     * }
     */
    public function parse(string $filePath): array
    {
        $reader = IOFactory::createReaderForFile($filePath);
        $reader->setReadDataOnly(true);
        $spreadsheet = $reader->load($filePath);

        $sheet = $spreadsheet->getSheet(0);
        // Formatting flags off: raw values, no calculated formatting - the
        // deliveries are pure text.
        $rows = $sheet->toArray(null, false, false, false);

        // The header row is FOUND, not assumed at row 9: the one whose column B
        // says "Component".
        $headerIndex = null;
        foreach ($rows as $i => $row) {
            if (trim((string)($row[1] ?? '')) === 'Component') {
                $headerIndex = $i;
                break;
            }
        }

        $meta = [];
        $components = [];

        if ($headerIndex !== null) {
            foreach (array_slice($rows, 0, $headerIndex) as $row) {
                $label = strtolower(trim((string)($row[0] ?? '')));
                $value = trim((string)($row[1] ?? ''));
                if ($label !== '' && $value !== '' && isset(self::META_LABELS[$label])) {
                    $meta[self::META_LABELS[$label]] = $value;
                }
            }

            $occurrences = [];
            foreach (array_slice($rows, $headerIndex + 1) as $row) {
                $component = trim((string)($row[1] ?? ''));
                if ($component === '') {
                    continue;
                }
                $occurrences[$component] = ($occurrences[$component] ?? 0) + 1;
                $components[] = [
                    'order' => (int)($row[0] ?? 0),
                    'component' => $component,
                    'occurrence' => $occurrences[$component],
                    'title' => trim((string)($row[2] ?? '')),
                    'body' => trim((string)($row[3] ?? '')),
                    'cta' => trim((string)($row[4] ?? '')),
                    'lengthNote' => trim((string)($row[5] ?? '')),
                    'seoNote' => trim((string)($row[6] ?? '')),
                ];
            }
        }

        $seo = [];
        $seoSheet = $spreadsheet->getSheetByName('Page SEO Check')
            ?? ($spreadsheet->getSheetCount() > 1 ? $spreadsheet->getSheet(1) : null);
        if ($seoSheet !== null) {
            $seoRows = $seoSheet->toArray(null, false, false, false);
            $headers = $seoRows[0] ?? [];
            $values = $seoRows[1] ?? [];
            foreach ($headers as $i => $header) {
                $header = trim((string)$header);
                $value = trim((string)($values[$i] ?? ''));
                if ($header !== '' && $value !== '') {
                    $seo[$header] = $value;
                }
            }
        }

        $spreadsheet->disconnectWorksheets();

        return [
            'pageType' => $this->detectPageType($components),
            'meta' => $meta,
            'components' => $components,
            'seo' => $seo,
        ];
    }

    /**
     * The selectable sources of a parsed workbook, grouped for the dropdown.
     * Every entry: selector, label, value (full) and preview (shortened).
     *
     * @param array<string,mixed> $parsed
     * @return array<string,array<int,array{selector:string,label:string,value:string,preview:string}>>
     */
    public function sources(array $parsed): array
    {
        $groups = ['Meta' => [], 'Components' => [], 'Composed' => [], 'Page SEO Check' => []];

        foreach ($parsed['meta'] ?? [] as $key => $value) {
            $groups['Meta'][] = $this->entry('m:' . $key, 'Meta · ' . $key, (string)$value);
        }

        foreach ($parsed['components'] ?? [] as $component) {
            $name = (string)$component['component'];
            if (in_array($name, self::HIDDEN_COMPONENTS, true)) {
                continue;
            }
            $suffix = ((int)$component['occurrence']) > 1 || $this->occursMultipleTimes($parsed, $name)
                ? ' #' . $component['occurrence']
                : '';
            foreach (['title' => 'Title', 'body' => 'Body', 'cta' => 'CTA'] as $cell => $cellLabel) {
                $value = (string)($component[$cell] ?? '');
                if ($value === '') {
                    continue;
                }
                $groups['Components'][] = $this->entry(
                    'c:' . $name . '#' . $component['occurrence'] . ':' . $cell,
                    $name . $suffix . ' · ' . $cellLabel,
                    $value
                );
            }

            // Drift-proof copy selector: the agency fills text sometimes into
            // "Body Copy" (D), sometimes into "CTA / Card Copy" (E) - Aligo's
            // hero one-liner sits in D, Arqa's in E. `copy` reads body and
            // falls back to cta, so one stored mapping fits every delivery.
            $copy = trim((string)($component['body'] ?? ''));
            if ($copy === '') {
                $copy = trim((string)($component['cta'] ?? ''));
            }
            if ($copy !== '') {
                $groups['Components'][] = $this->entry(
                    'c:' . $name . '#' . $component['occurrence'] . ':copy',
                    $name . $suffix . ' · Copy (Body, sonst CTA)',
                    $copy
                );
            }

            // Section-intro rows ("... — H2") additionally as ONE ready RTE
            // value: heading plus paragraph in the exact markup the finished
            // MGW-Diamond product stores in `textrelatedproducts` - imported
            // and hand-written intros then render identically.
            if (str_ends_with($name, '— H2')) {
                $pair = $this->composePair($component);
                if ($pair !== null) {
                    $groups['Composed'][] = $this->entry(
                        'p:' . $name . '#' . $component['occurrence'],
                        $name . $suffix . ' · Title+Body as HTML',
                        $pair
                    );
                }
            }
        }

        // Composed sources: a component occurring several times can be pulled
        // as ONE value - every occurrence as <h3> + bullets/paragraph, prefixed
        // with the section heading. Built for the Key Capability Groups, whose
        // three rows land in the single `capabilities` field; that is the shape
        // the finished product pages already use.
        $counts = [];
        foreach ($parsed['components'] ?? [] as $component) {
            $counts[(string)$component['component']] = ((int)($component['occurrence'] ?? 1));
        }
        foreach ($counts as $name => $count) {
            if ($count < 2 || in_array($name, self::HIDDEN_COMPONENTS, true)) {
                continue;
            }
            $html = $this->composeGroup($parsed, $name);
            if ($html !== null) {
                $groups['Composed'][] = $this->entry('g:' . $name, $name . ' · all ' . $count . ' as HTML', $html);
            }
        }

        // Cross-component composite for the SEO title: hero title, em dash,
        // introduction heading - "Aligo — High-performance AV over IP and KVM".
        $seoTitle = $this->composeSeoTitle($parsed);
        if ($seoTitle !== null) {
            $groups['Composed'][] = $this->entry('x:seotitle', 'Hero title — Intro heading (SEO title)', $seoTitle);
        }

        foreach ($parsed['seo'] ?? [] as $header => $value) {
            $groups['Page SEO Check'][] = $this->entry('s:' . $header, 'SEO · ' . $header, (string)$value);
        }

        return array_filter($groups, static fn(array $g): bool => $g !== []);
    }

    /**
     * Resolves one stored selector against a parsed workbook. null = the
     * selector points at nothing in THIS file (component missing or cell
     * empty) - the caller shows that instead of silently writing ''.
     *
     * @param array<string,mixed> $parsed
     */
    public function valueFor(array $parsed, string $selector): ?string
    {
        if (str_starts_with($selector, 'm:')) {
            $value = $parsed['meta'][substr($selector, 2)] ?? null;
            return is_string($value) && $value !== '' ? $value : null;
        }
        if (str_starts_with($selector, 'g:')) {
            return $this->composeGroup($parsed, substr($selector, 2));
        }
        if (str_starts_with($selector, 'p:') && preg_match('/^p:(.+)#(\d+)$/', $selector, $m)) {
            foreach ($parsed['components'] ?? [] as $component) {
                if ((string)$component['component'] === $m[1] && (int)$component['occurrence'] === (int)$m[2]) {
                    return $this->composePair($component);
                }
            }
            return null;
        }
        if ($selector === 'x:seotitle') {
            return $this->composeSeoTitle($parsed);
        }
        if (str_starts_with($selector, 's:')) {
            $value = $parsed['seo'][substr($selector, 2)] ?? null;
            return is_string($value) && $value !== '' ? $value : null;
        }
        if (str_starts_with($selector, 'c:') && preg_match('/^c:(.+)#(\d+):(title|body|cta|copy)$/', $selector, $m)) {
            foreach ($parsed['components'] ?? [] as $component) {
                if ((string)$component['component'] === $m[1] && (int)$component['occurrence'] === (int)$m[2]) {
                    if ($m[3] === 'copy') {
                        $value = trim((string)($component['body'] ?? ''));
                        if ($value === '') {
                            $value = trim((string)($component['cta'] ?? ''));
                        }
                        $value = trim((string)preg_replace('/\|\s*CTA:.*$/su', '', $value));
                    } else {
                        $value = (string)($component[$m[3]] ?? '');
                    }
                    return $value !== '' ? $value : null;
                }
            }
        }
        return null;
    }

    /**
     * SEO title composite: the hero title (the product name), an em dash, and
     * the introduction heading. Plain text - `seotitle` is a plain input, no
     * escaping wanted here.
     *
     * @param array<string,mixed> $parsed
     */
    private function composeSeoTitle(array $parsed): ?string
    {
        $hero = null;
        $intro = null;
        foreach ($parsed['components'] ?? [] as $component) {
            $name = (string)($component['component'] ?? '');
            if ($name === 'Hero — H1' && (int)($component['occurrence'] ?? 1) === 1) {
                $hero = trim((string)($component['title'] ?? ''));
            } elseif ($name === 'Product Introduction' && (int)($component['occurrence'] ?? 1) === 1) {
                $intro = trim((string)($component['title'] ?? ''));
            }
        }
        if ($hero === null || $hero === '' || $intro === null || $intro === '') {
            return null;
        }
        return $hero . ' — ' . $intro;
    }

    /**
     * One component row as a heading/paragraph pair. The markup - centred h3
     * with an inner span, centred p - is copied verbatim from what the
     * finished MGW-Diamond product carries in `textrelatedproducts`, so the
     * front end renders imported intros exactly like the hand-made one.
     *
     * @param array<string,mixed> $component
     */
    private function composePair(array $component): ?string
    {
        $title = trim((string)($component['title'] ?? ''));
        $body = trim((string)($component['body'] ?? ''));
        if ($body === '') {
            $body = trim((string)($component['cta'] ?? ''));
        }
        $body = trim((string)preg_replace('/\|\s*CTA:.*$/su', '', $body));
        if ($title === '' || $body === '') {
            return null;
        }
        return '<h3 class="text-center"><span>' . htmlspecialchars($title, ENT_QUOTES) . '</span></h3>' . "\n"
            . '<p class="text-center">' . htmlspecialchars($body, ENT_QUOTES) . '</p>';
    }

    /**
     * All occurrences of one component, composed into a single HTML block in
     * the exact markup the hand-made MGW-Diamond `capabilities` field uses
     * (the front end styles only that shape): the immediately preceding
     * "... — H2" row (found by ORDER, not by name - "Key Capability Group" vs
     * "Key Capabilities — H2" makes name matching fragile) becomes
     * <h3 class="text-center"><span>, each occurrence's title a
     * <p><strong> group heading, and a body whose text uses "•" bullets
     * becomes a <ul>. Plain text otherwise.
     *
     * @param array<string,mixed> $parsed
     */
    private function composeGroup(array $parsed, string $name): ?string
    {
        $components = $parsed['components'] ?? [];
        $members = array_values(array_filter(
            $components,
            static fn(array $c): bool => (string)($c['component'] ?? '') === $name
        ));
        if ($members === []) {
            return null;
        }

        $html = '';
        $firstOrder = (int)($members[0]['order'] ?? 0);
        foreach ($components as $component) {
            if ((int)($component['order'] ?? 0) === $firstOrder - 1
                && str_ends_with((string)($component['component'] ?? ''), '— H2')
                && trim((string)($component['title'] ?? '')) !== ''
            ) {
                $html .= '<h3 class="text-center"><span>'
                    . htmlspecialchars(trim((string)$component['title']), ENT_QUOTES)
                    . '</span></h3>' . "\n";
                break;
            }
        }

        foreach ($members as $member) {
            $title = trim((string)($member['title'] ?? ''));
            $body = trim((string)($member['body'] ?? ''));
            if ($body === '') {
                $body = trim((string)($member['cta'] ?? ''));
            }
            // Card copy carries a "| CTA: <label>" suffix; the label is
            // display-only everywhere else and would be junk inside a <p>.
            $body = trim((string)preg_replace('/\|\s*CTA:.*$/su', '', $body));
            if ($title !== '') {
                $html .= '<p><strong>' . htmlspecialchars($title, ENT_QUOTES) . '</strong></p>';
            }
            if ($body === '') {
                continue;
            }
            if (str_contains($body, '•')) {
                $items = array_filter(array_map('trim', explode('•', $body)));
                $html .= '<ul>';
                foreach ($items as $item) {
                    $html .= '<li>' . htmlspecialchars($item, ENT_QUOTES) . '</li>';
                }
                $html .= '</ul>';
            } else {
                $html .= '<p>' . htmlspecialchars($body, ENT_QUOTES) . '</p>';
            }
        }

        return $html !== '' ? $html : null;
    }

    /** @param array<int,array<string,mixed>> $components */
    private function detectPageType(array $components): string
    {
        $names = array_column($components, 'component');
        if (in_array('Product Introduction', $names, true)) {
            return 'product';
        }
        if (in_array('Category Introduction', $names, true)) {
            return 'category';
        }
        return 'unknown';
    }

    /** @param array<string,mixed> $parsed */
    private function occursMultipleTimes(array $parsed, string $name): bool
    {
        $count = 0;
        foreach ($parsed['components'] ?? [] as $component) {
            if ((string)$component['component'] === $name && ++$count > 1) {
                return true;
            }
        }
        return false;
    }

    /** @return array{selector:string,label:string,value:string,preview:string} */
    private function entry(string $selector, string $label, string $value): array
    {
        $flat = preg_replace('/\s+/u', ' ', $value) ?? $value;
        return [
            'selector' => $selector,
            'label' => $label,
            'value' => $value,
            'preview' => mb_strlen($flat) > 60 ? mb_substr($flat, 0, 57) . '…' : $flat,
        ];
    }
}