<?php

declare(strict_types=1);

namespace Evomedien\Vitec\Service;

use TYPO3\CMS\Core\Database\Connection;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Resource\ResourceFactory;
use TYPO3\CMS\Core\Service\FlexFormService;
use TYPO3\CMS\Core\Utility\GeneralUtility;
use TYPO3\CMS\Extbase\Service\ImageService;

/**
 * Page content audit for the market and solution trees (sibling of
 * ProductCheckService, decision Harry 2026-11-13): which market / sub-market /
 * solution-group / sub-solution PAGE carries which content, and the full
 * content of one page for the detail pane.
 *
 * Unlike products these are pages built from content elements, so the audit
 * looks at the elements in page order (hero, intro, cards, testimonials,
 * products, insights, logos, CTA banner), at the page properties (meta
 * description, OG image, page media) and at the market / solution record that
 * points to the page via detail_page.
 *
 * Scopes: 'markets'   = children of /markets   (11 markets, 24 sub-markets)
 *         'solutions' = children of /solutions (8 groups,  39 sub-solutions)
 *
 * Columns carry an `applies` list - a column a page type does not have (e.g.
 * logos on a sub-market, insights on a main market) is delivered as
 * {ok: null, count: 0} and counted neither as filled nor as applicable.
 *
 * Placeholders (Harry 2026-11-13): files below fileadmin/placeholders/ (or
 * named "...placeholder...") are grey dummies that must not pass as finished.
 * Image cells carry `placeholder` = number of such files when there is at
 * least one; summary.filled counts only cells without placeholders, and
 * summary.placeholder counts the cells that have one. Detail images carry
 * `placeholder: true|false`. `ok` itself is unchanged (something is attached).
 *
 * Read fresh on every call, nothing cached (USER_INT in the renderer).
 */
final class PageCheckService
{
    /** SQL condition on sys_file f that marks a placeholder file. */
    public const PLACEHOLDER_SQL = "(f.identifier LIKE '/placeholders/%' OR LOWER(f.name) LIKE '%placeholder%')";

    public static function isPlaceholder(string $identifier, string $name): bool
    {
        return str_starts_with($identifier, '/placeholders/') || str_contains(strtolower($name), 'placeholder');
    }

    public const SCOPES = [
        'markets' => [
            'label' => 'Markets',
            'rootSlug' => '/markets',
            'types' => ['market', 'submarket'],
            'recordTable' => 'tx_vitec_domain_model_market',
        ],
        'solutions' => [
            'label' => 'Solutions',
            'rootSlug' => '/solutions',
            'types' => ['solution', 'subsolution'],
            'recordTable' => 'tx_vitec_domain_model_solution',
        ],
    ];

    private const ALL = ['market', 'submarket', 'solution', 'subsolution'];

    /**
     * Columns in display order. kind as in the product check (images / video /
     * text / features / list), applies = page types that have the column.
     */
    private const FIELDS = [
        ['key' => 'hero', 'label' => 'Hero image', 'kind' => 'images', 'applies' => self::ALL],
        ['key' => 'subline', 'label' => 'Hero subline', 'kind' => 'text', 'applies' => self::ALL],
        ['key' => 'lead', 'label' => 'Lead', 'kind' => 'text', 'applies' => ['solution', 'subsolution']],
        ['key' => 'intro', 'label' => 'Intro text', 'kind' => 'text', 'applies' => self::ALL],
        ['key' => 'introImage', 'label' => 'Intro image', 'kind' => 'images', 'applies' => self::ALL],
        ['key' => 'introCta', 'label' => 'Intro CTA', 'kind' => 'text', 'applies' => self::ALL],
        ['key' => 'cards', 'label' => 'Cards', 'kind' => 'features', 'applies' => self::ALL],
        ['key' => 'stories', 'label' => 'Testimonials', 'kind' => 'list', 'applies' => self::ALL],
        ['key' => 'products', 'label' => 'Products', 'kind' => 'list', 'applies' => ['submarket', 'subsolution']],
        ['key' => 'insights', 'label' => 'Insights text', 'kind' => 'text', 'applies' => ['submarket', 'solution', 'subsolution']],
        ['key' => 'insightsCover', 'label' => 'Insights cover', 'kind' => 'images', 'applies' => ['submarket', 'solution', 'subsolution']],
        ['key' => 'insightsCta', 'label' => 'Insights CTA', 'kind' => 'text', 'applies' => ['submarket', 'solution', 'subsolution']],
        ['key' => 'logos', 'label' => 'Logos', 'kind' => 'list', 'applies' => ['market']],
        ['key' => 'cta', 'label' => 'CTA banner', 'kind' => 'text', 'applies' => ['market', 'solution']],
        ['key' => 'seo', 'label' => 'Meta description', 'kind' => 'text', 'applies' => self::ALL],
        ['key' => 'og', 'label' => 'OG image', 'kind' => 'images', 'applies' => self::ALL],
        ['key' => 'media', 'label' => 'Page media', 'kind' => 'images', 'applies' => self::ALL],
        ['key' => 'record', 'label' => 'Record', 'kind' => 'list', 'applies' => self::ALL],
    ];

    private const HERO_COLPOS = [1, 110];
    private const CONTAINER_PREFIXES = ['vitec_cols_', 'vitec_container', 'vitec_cards_carousel'];

    /** Text fields per element type for the detail pane: column => label. */
    private const ELEMENT_TEXTS = [
        'vitec_markethero' => ['vitec_subline' => 'Subline', 'vitec_cta_label' => 'CTA label', 'vitec_video_title' => 'Video title'],
        'vitec_herosection' => ['header' => 'Header', 'subheader' => 'Subheader', 'bodytext' => 'Text', 'vitec_cta_label' => 'CTA label'],
        'vitec_introparagraph' => ['header' => 'Header', 'subheader' => 'Subheader', 'bodytext' => 'Text', 'vitec_button_text' => 'Button'],
        'vitec_marketintro' => ['vitec_intro_headline' => 'Headline', 'vitec_intro_text' => 'Text', 'vitec_learnmore_label' => 'Button'],
        'vitec_card' => ['vitec_eyebrow' => 'Eyebrow', 'header' => 'Header', 'subheader' => 'Subheader', 'bodytext' => 'Text', 'vitec_back_headline' => 'Back headline', 'vitec_back_text' => 'Back text', 'vitec_primary_cta_label' => 'Button'],
        'vitec_usecaselist' => ['header' => 'Header'],
        'vitec_customerlogos' => ['header' => 'Header'],
        'vitec_marketinsights' => ['vitec_text1' => 'Text 1', 'vitec_text2' => 'Text 2', 'vitec_cta_label' => 'CTA label', 'vitec_video_person' => 'Video person', 'vitec_video_role' => 'Video role', 'vitec_video_title' => 'Video title'],
        'vitec_ctabanner' => ['header' => 'Header', 'bodytext' => 'Text', 'vitec_primary_cta_label' => 'Primary button', 'vitec_secondary_cta_label' => 'Secondary button'],
        'vitec_productfeature' => ['header' => 'Header'],
        'vitec_productlist' => ['header' => 'Header'],
    ];

    /** Link fields per element type: column => label. */
    private const ELEMENT_LINKS = [
        'vitec_markethero' => ['vitec_cta_link' => 'CTA link', 'vitec_video_url' => 'Video URL'],
        'vitec_herosection' => ['vitec_cta' => 'CTA link'],
        'vitec_introparagraph' => ['vitec_button_link' => 'Button link'],
        'vitec_marketintro' => ['vitec_learnmore_target' => 'Button target'],
        'vitec_card' => ['vitec_primary_cta' => 'Button link', 'vitec_card_link' => 'Card link'],
        'vitec_marketinsights' => ['vitec_cta_target' => 'CTA target', 'vitec_video_url' => 'Video URL'],
        'vitec_ctabanner' => ['vitec_primary_cta' => 'Primary link', 'vitec_secondary_cta' => 'Secondary link'],
    ];

    private const ELEMENT_LABELS = [
        'vitec_markethero' => 'Hero',
        'vitec_herosection' => 'Hero section',
        'vitec_introparagraph' => 'Intro paragraph',
        'vitec_marketintro' => 'Intro (text | image)',
        'vitec_card' => 'Card',
        'vitec_usecaselist' => 'Testimonials',
        'vitec_customerlogos' => 'Customer logos',
        'vitec_marketinsights' => 'Insights',
        'vitec_ctabanner' => 'CTA banner',
        'vitec_productfeature' => 'Featured product',
        'vitec_productlist' => 'Product list',
    ];

    public function __construct(
        private readonly ConnectionPool $connectionPool,
    ) {}

    /** @return array<string, mixed> */
    public function overview(string $scope): array
    {
        $cfg = self::SCOPES[$scope] ?? throw new \InvalidArgumentException('Unknown scope ' . $scope);
        $pages = $this->pages($cfg);
        $pageUids = array_map(static fn(array $p): int => (int)$p['uid'], $pages);
        $elementsByPage = $this->elementsByPage($pageUids);
        $allElementUids = [];
        foreach ($elementsByPage as $els) {
            foreach ($els as $el) {
                $allElementUids[] = (int)$el['uid'];
            }
        }
        $elementFiles = $this->referenceCounts('tt_content', $allElementUids);
        $elementPlaceholders = $this->referenceCounts('tt_content', $allElementUids, true);
        $pageFiles = $this->referenceCounts('pages', $pageUids);
        $pagePlaceholders = $this->referenceCounts('pages', $pageUids, true);
        $records = $this->records($cfg['recordTable'], $pageUids);
        $recordUids = array_map(static fn(array $r): int => (int)$r['uid'], $records);
        $recordFiles = $this->referenceCounts($cfg['recordTable'], $recordUids);
        $recordPlaceholders = $this->referenceCounts($cfg['recordTable'], $recordUids, true);
        // Empty selection = what the testimonial band shows: quoted stories,
        // at most twelve (UsecaseListJsonRenderer::testimonialRows).
        $allStories = min(12, $this->countAll('tx_vitec_domain_model_usecase', "deleted = 0 AND hidden = 0 AND hideonwebsite = 0 AND TRIM(COALESCE(quote, '')) <> ''"));
        $allCustomers = $this->countAll('tx_vitec_domain_model_customer', 'deleted = 0 AND hidden = 0');

        $keys = array_column(self::FIELDS, 'key');
        $filled = array_fill_keys($keys, 0);
        $placeholder = array_fill_keys($keys, 0);
        $applicable = array_fill_keys($keys, 0);
        $byType = [];
        $rows = [];
        foreach ($pages as $p) {
            $uid = (int)$p['uid'];
            $type = (string)$p['_type'];
            $byType[$type] = ($byType[$type] ?? 0) + 1;
            $els = $elementsByPage[$uid] ?? [];
            $visible = array_values(array_filter($els, static fn(array $e): bool => (int)$e['hidden'] === 0));
            $hiddenCount = count($els) - count($visible);
            $record = $records[$uid] ?? null;
            $recordUid = $record ? (int)$record['uid'] : 0;
            $raw = $this->pageChecks(
                $type,
                $visible,
                ['n' => $elementFiles, 'ph' => $elementPlaceholders],
                ['n' => $pageFiles[$uid] ?? [], 'ph' => $pagePlaceholders[$uid] ?? []],
                $p,
                $record,
                ['n' => $recordFiles[$recordUid] ?? [], 'ph' => $recordPlaceholders[$recordUid] ?? []],
                $allStories,
                $allCustomers
            );

            $checks = [];
            foreach (self::FIELDS as $field) {
                $key = $field['key'];
                if (!in_array($type, $field['applies'], true)) {
                    $checks[$key] = ['ok' => null, 'count' => 0];
                    continue;
                }
                $checks[$key] = $raw[$key];
                $applicable[$key]++;
                if (($raw[$key]['placeholder'] ?? 0) > 0) {
                    $placeholder[$key]++;
                } elseif ($raw[$key]['ok'] === true) {
                    $filled[$key]++;
                }
            }
            $rows[] = $this->base($p, $type, $record, $hiddenCount) + ['checks' => $checks];
        }

        return [
            'scope' => $scope,
            'scopeLabel' => $cfg['label'],
            'generatedAt' => date(\DATE_ATOM),
            'fields' => self::FIELDS,
            'summary' => ['pages' => count($rows), 'byType' => $byType, 'filled' => $filled, 'placeholder' => $placeholder, 'applicable' => $applicable],
            'pages' => $rows,
        ];
    }

    /** @return array<string, mixed>|null null when the page is not part of any scope */
    public function detail(int $uid): ?array
    {
        foreach (self::SCOPES as $scope => $cfg) {
            foreach ($this->pages($cfg) as $p) {
                if ((int)$p['uid'] !== $uid) {
                    continue;
                }
                $type = (string)$p['_type'];
                $records = $this->records($cfg['recordTable'], [$uid]);
                $record = $records[$uid] ?? null;
                $els = $this->elementsByPage([$uid])[$uid] ?? [];
                $hiddenCount = count(array_filter($els, static fn(array $e): bool => (int)$e['hidden'] === 1));
                $flexFormService = GeneralUtility::makeInstance(FlexFormService::class);

                $elements = [];
                foreach ($els as $el) {
                    $cType = (string)$el['CType'];
                    $texts = [];
                    foreach (self::ELEMENT_TEXTS[$cType] ?? ['header' => 'Header', 'bodytext' => 'Text'] as $column => $label) {
                        $value = (string)($el[$column] ?? '');
                        $texts[] = ['key' => $column, 'label' => $label, 'ok' => $this->filled($value), 'html' => $value];
                    }
                    $links = [];
                    foreach (self::ELEMENT_LINKS[$cType] ?? [] as $column => $label) {
                        $value = trim((string)($el[$column] ?? ''));
                        if ($value !== '') {
                            $links[] = ['key' => $column, 'label' => $label, 'target' => $value];
                        }
                    }
                    $settings = [];
                    if (trim((string)($el['pi_flexform'] ?? '')) !== '') {
                        $settings = $flexFormService->convertFlexFormContentToArray((string)$el['pi_flexform'])['settings'] ?? [];
                        $settings = array_filter($settings, static fn($v): bool => is_scalar($v) && (string)$v !== '' && (string)$v !== '0');
                        $settings = array_map(static fn($v): string => (string)$v, $settings);
                    }
                    $elements[] = [
                        'uid' => (int)$el['uid'],
                        'type' => $cType,
                        'label' => self::ELEMENT_LABELS[$cType] ?? $cType,
                        'colPos' => (int)$el['colPos'],
                        'slot' => in_array((int)$el['colPos'], self::HERO_COLPOS, true) ? 'hero' : 'main',
                        'hidden' => (int)$el['hidden'] === 1,
                        'header' => (string)($el['header'] ?? ''),
                        'layoutVariant' => (string)($el['vitec_layout_variant'] ?? ''),
                        'texts' => $texts,
                        'links' => $links,
                        'images' => $this->files('tt_content', (int)$el['uid']),
                        'settings' => $settings,
                    ];
                }

                $children = [];
                if ((int)$p['_level'] === 1) {
                    foreach ($this->pages($cfg) as $c) {
                        if ((int)$c['pid'] === $uid) {
                            $children[] = ['uid' => (int)$c['uid'], 'title' => (string)$c['title'], 'url' => (string)$c['slug'], 'hidden' => (int)$c['hidden'] === 1];
                        }
                    }
                }

                return $this->base($p, $type, $record, $hiddenCount) + [
                    'seo' => [
                        'title' => (string)$p['title'],
                        'seoTitle' => (string)($p['seo_title'] ?? ''),
                        'description' => (string)($p['description'] ?? ''),
                        'og' => $this->files('pages', $uid, ['og_image']),
                    ],
                    'media' => $this->files('pages', $uid, ['media']),
                    'record' => $record === null ? null : [
                        'uid' => (int)$record['uid'],
                        'title' => (string)$record['title'],
                        'subtitle' => (string)($record['subtitle'] ?? ''),
                        'teaser' => (string)($record['teaser'] ?? ''),
                        'description' => (string)($record['description'] ?? ''),
                        'image' => $this->files($cfg['recordTable'], (int)$record['uid'], ['image']),
                    ],
                    'elements' => $elements,
                    'children' => $children,
                ];
            }
        }
        return null;
    }

    // ---- checks -------------------------------------------------------------

    /**
     * @param array<int, array<string, mixed>> $visible visible elements in page order
     * @param array{n: array<int, array<string, int>>, ph: array<int, array<string, int>>} $elementFiles n/ph: [elementUid][fieldname] = count of all / of placeholder files
     * @param array{n: array<string, int>, ph: array<string, int>} $pageFiles [fieldname] = count
     * @param array<string, mixed> $page
     * @param array<string, mixed>|null $record
     * @param array{n: array<string, int>, ph: array<string, int>} $recordFiles
     * @return array<string, array{ok: bool, count: int, icons?: int, placeholder?: int}>
     */
    private function pageChecks(string $type, array $visible, array $elementFiles, array $pageFiles, array $page, ?array $record, array $recordFiles, int $allStories, int $allCustomers): array
    {
        $hero = $this->firstOf($visible, 'vitec_markethero') ?? $this->firstOf($visible, 'vitec_herosection');
        $heroFiles = $hero ? ($elementFiles['n'][(int)$hero['uid']] ?? []) : [];
        $heroPh = $hero ? ($elementFiles['ph'][(int)$hero['uid']] ?? []) : [];
        $heroFields = ['vitec_bgimage', 'vitec_hero_bgimage', 'vitec_hero_image'];
        $lead = null;
        foreach ($visible as $el) {
            if ($el['CType'] === 'vitec_introparagraph' && in_array((int)$el['colPos'], self::HERO_COLPOS, true)) {
                $lead = $el;
                break;
            }
        }
        $intro = $this->firstOf($visible, 'vitec_marketintro');
        $introFiles = $intro ? ($elementFiles['n'][(int)$intro['uid']] ?? []) : [];
        $introPh = $intro ? ($elementFiles['ph'][(int)$intro['uid']] ?? []) : [];

        $cards = array_values(array_filter($visible, static fn(array $e): bool => $e['CType'] === 'vitec_card'));
        $cardVisuals = 0;
        $cardPlaceholders = 0;
        foreach ($cards as $c) {
            $f = $elementFiles['n'][(int)$c['uid']] ?? [];
            $ph = $elementFiles['ph'][(int)$c['uid']] ?? [];
            if (($f['vitec_icon'] ?? 0) > 0 || ($f['vitec_image'] ?? 0) > 0) {
                $cardVisuals++;
            }
            if (($ph['vitec_icon'] ?? 0) > 0 || ($ph['vitec_image'] ?? 0) > 0) {
                $cardPlaceholders++;
            }
        }

        $stories = $this->firstOf($visible, 'vitec_usecaselist');
        $storyCount = 0;
        if ($stories) {
            $sel = $this->flexSettings((string)$stories['pi_flexform']);
            $selected = GeneralUtility::intExplode(',', (string)($sel['usecases'] ?? ''), true);
            $storyCount = $selected === [] ? $allStories : count($selected);
        }

        $productCount = 0;
        $hasProducts = false;
        foreach ($visible as $el) {
            if ($el['CType'] === 'vitec_productfeature') {
                $hasProducts = true;
                $productCount += (int)(($this->flexSettings((string)$el['pi_flexform'])['product'] ?? 0) > 0);
            } elseif ($el['CType'] === 'vitec_productlist') {
                $hasProducts = true;
                $sel = $this->flexSettings((string)$el['pi_flexform']);
                $productCount += count(GeneralUtility::intExplode(',', (string)($sel['selectedproducts'] ?? ''), true));
            }
        }

        $insights = $this->firstOf($visible, 'vitec_marketinsights');
        $insightsFiles = $insights ? ($elementFiles['n'][(int)$insights['uid']] ?? []) : [];
        $insightsPh = $insights ? ($elementFiles['ph'][(int)$insights['uid']] ?? []) : [];

        $logos = $this->firstOf($visible, 'vitec_customerlogos');
        $logoCount = 0;
        if ($logos) {
            $sel = $this->flexSettings((string)$logos['pi_flexform']);
            $selected = GeneralUtility::intExplode(',', (string)($sel['customers'] ?? ''), true);
            $logoCount = $selected === [] || ($sel['onlySelected'] ?? '0') !== '1' ? $allCustomers : count($selected);
        }

        $cta = $this->firstOf($visible, 'vitec_ctabanner');

        $recordFilled = 0;
        if ($record !== null) {
            $recordFilled += (($recordFiles['n']['image'] ?? 0) > 0) ? 1 : 0;
            $recordFilled += $this->filled($record['teaser'] ?? '') ? 1 : 0;
            $recordFilled += $this->filled($record['description'] ?? '') ? 1 : 0;
        }

        $sum = static fn(array $counts, array $fields): int => array_sum(array_map(static fn(string $f): int => (int)($counts[$f] ?? 0), $fields));

        return [
            'hero' => $this->check($sum($heroFiles, $heroFields), $sum($heroPh, $heroFields)),
            'subline' => $this->check($hero && ($this->filled($hero['vitec_subline'] ?? '') || $this->filled($hero['subheader'] ?? '')) ? 1 : 0),
            'lead' => $this->check($lead && $this->filled($lead['bodytext'] ?? '') ? 1 : 0),
            'intro' => $this->check($intro && $this->filled($intro['vitec_intro_text'] ?? '') ? 1 : 0),
            'introImage' => $this->check($introFiles['vitec_image'] ?? 0, $introPh['vitec_image'] ?? 0),
            'introCta' => $this->check($intro && $this->filled($intro['vitec_learnmore_label'] ?? '') && $this->filled($intro['vitec_learnmore_target'] ?? '') ? 1 : 0),
            'cards' => $this->check(count($cards), $cardPlaceholders) + ['icons' => $cardVisuals],
            'stories' => ['ok' => $stories !== null, 'count' => $storyCount],
            'products' => ['ok' => $hasProducts, 'count' => $productCount],
            'insights' => $this->check($insights && $this->filled($insights['vitec_text1'] ?? '') ? 1 : 0),
            'insightsCover' => $this->check($insightsFiles['vitec_video_cover'] ?? 0, $insightsPh['vitec_video_cover'] ?? 0),
            'insightsCta' => $this->check($insights && $this->filled($insights['vitec_cta_label'] ?? '') && $this->filled($insights['vitec_cta_target'] ?? '') ? 1 : 0),
            'logos' => ['ok' => $logos !== null, 'count' => $logoCount],
            'cta' => $this->check($cta && $this->filled($cta['vitec_primary_cta_label'] ?? '') ? 1 : 0),
            'seo' => $this->check($this->filled($page['description'] ?? '') ? 1 : 0),
            'og' => $this->check($pageFiles['n']['og_image'] ?? 0, $pageFiles['ph']['og_image'] ?? 0),
            'media' => $this->check($pageFiles['n']['media'] ?? 0, $pageFiles['ph']['media'] ?? 0),
            'record' => ['ok' => $record !== null && ($recordFiles['n']['image'] ?? 0) > 0 && $this->filled($record['teaser'] ?? ''), 'count' => $recordFilled]
                + ((($recordFiles['ph']['image'] ?? 0) > 0) ? ['placeholder' => (int)$recordFiles['ph']['image']] : []),
        ];
    }

    // ---- building blocks ----------------------------------------------------

    /**
     * @param array<string, mixed> $p
     * @param array<string, mixed>|null $record
     */
    private function base(array $p, string $type, ?array $record, int $hiddenElements): array
    {
        $flags = [];
        if ((int)$p['hidden'] === 1) {
            $flags[] = 'hidden';
        }
        if ((int)$p['nav_hide'] === 1) {
            $flags[] = 'navHide';
        }
        if ((int)$p['no_index'] === 1) {
            $flags[] = 'noindex';
        }
        if ($record === null) {
            $flags[] = 'noRecord';
        }
        if ($hiddenElements > 0) {
            $flags[] = 'hiddenElements:' . $hiddenElements;
        }
        return [
            'uid' => (int)$p['uid'],
            'title' => (string)$p['title'],
            'slug' => trim((string)$p['slug'], '/'),
            'url' => (string)$p['slug'],
            'type' => $type,
            'level' => (int)$p['_level'],
            'parent' => (int)$p['pid'],
            'flags' => $flags,
        ];
    }

    /**
     * @param int $placeholder number of placeholder files among $count; emitted only when > 0
     * @return array{ok: bool, count: int, placeholder?: int}
     */
    private function check(int $count, int $placeholder = 0): array
    {
        return ['ok' => $count > 0, 'count' => $count] + ($placeholder > 0 ? ['placeholder' => $placeholder] : []);
    }

    private function filled(mixed $value): bool
    {
        return trim(strip_tags((string)$value)) !== '';
    }

    /** @param array<int, array<string, mixed>> $elements */
    private function firstOf(array $elements, string $cType): ?array
    {
        foreach ($elements as $el) {
            if ($el['CType'] === $cType) {
                return $el;
            }
        }
        return null;
    }

    /** @return array<string, string> */
    private function flexSettings(string $xml): array
    {
        if (trim($xml) === '') {
            return [];
        }
        $settings = GeneralUtility::makeInstance(FlexFormService::class)->convertFlexFormContentToArray($xml)['settings'] ?? [];
        return is_array($settings) ? array_map(static fn($v): string => is_scalar($v) ? (string)$v : '', $settings) : [];
    }

    /**
     * The scope's pages: level 1 = children of the root page, level 2 = their
     * children. Standard pages only, in tree order; a page carrying the check
     * element itself is left out.
     *
     * @param array{rootSlug: string, types: array<int, string>} $cfg
     * @return array<int, array<string, mixed>> rows with _level and _type
     */
    private function pages(array $cfg): array
    {
        $conn = $this->connection('pages');
        $root = $conn->fetchAssociative('SELECT uid FROM pages WHERE deleted = 0 AND sys_language_uid = 0 AND slug = ?', [$cfg['rootSlug']]);
        if ($root === false) {
            return [];
        }
        $select = 'SELECT uid, pid, title, slug, hidden, nav_hide, no_index, description, seo_title, sorting FROM pages
                   WHERE deleted = 0 AND sys_language_uid = 0 AND doktype = 1 AND pid IN (%s) ORDER BY pid, sorting';
        $level1 = $conn->fetchAllAssociative(sprintf($select, (int)$root['uid']));
        $checkPages = $this->pagesWithCheckElement();
        $out = [];
        foreach ($level1 as $p1) {
            if (isset($checkPages[(int)$p1['uid']])) {
                continue;
            }
            $out[] = $p1 + ['_level' => 1, '_type' => $cfg['types'][0]];
            $level2 = $conn->fetchAllAssociative(sprintf($select, (int)$p1['uid']));
            foreach ($level2 as $p2) {
                if (isset($checkPages[(int)$p2['uid']])) {
                    continue;
                }
                $out[] = $p2 + ['_level' => 2, '_type' => $cfg['types'][1]];
            }
        }
        return $out;
    }

    /** @return array<int, true> page uid => true for pages that hold a check element */
    private function pagesWithCheckElement(): array
    {
        $out = [];
        foreach ($this->connection('tt_content')->fetchFirstColumn(
            "SELECT DISTINCT pid FROM tt_content WHERE deleted = 0 AND CType IN ('vitec_pagecheck', 'vitec_productcheck')"
        ) as $pid) {
            $out[(int)$pid] = true;
        }
        return $out;
    }

    /**
     * All non-deleted default-language elements of the pages, page order,
     * containers themselves left out (their children count as page content).
     *
     * @param int[] $pageUids
     * @return array<int, array<int, array<string, mixed>>> [pageUid] = rows
     */
    private function elementsByPage(array $pageUids): array
    {
        if ($pageUids === []) {
            return [];
        }
        $rows = $this->connection('tt_content')->fetchAllAssociative(
            'SELECT * FROM tt_content WHERE deleted = 0 AND sys_language_uid = 0 AND pid IN ('
            . implode(',', array_map('intval', $pageUids)) . ') ORDER BY pid, colPos, sorting, uid'
        );
        // page order: hero slot first, then the main column(s) by colPos and sorting
        usort($rows, static function (array $a, array $b): int {
            $heroA = in_array((int)$a['colPos'], self::HERO_COLPOS, true) ? 0 : 1;
            $heroB = in_array((int)$b['colPos'], self::HERO_COLPOS, true) ? 0 : 1;
            return [(int)$a['pid'], $heroA, (int)$a['colPos'], (int)$a['sorting'], (int)$a['uid']]
                <=> [(int)$b['pid'], $heroB, (int)$b['colPos'], (int)$b['sorting'], (int)$b['uid']];
        });
        $out = [];
        foreach ($rows as $row) {
            $cType = (string)$row['CType'];
            foreach (self::CONTAINER_PREFIXES as $prefix) {
                if (str_starts_with($cType, $prefix)) {
                    continue 2;
                }
            }
            $out[(int)$row['pid']][] = $row;
        }
        return $out;
    }

    /**
     * Visible file references per record and field.
     *
     * @param int[] $uids
     * @return array<int, array<string, int>> [uid][fieldname] = count
     */
    private function referenceCounts(string $table, array $uids, bool $placeholdersOnly = false): array
    {
        if ($uids === []) {
            return [];
        }
        $in = implode(',', array_map('intval', $uids));
        $sql = $placeholdersOnly
            ? 'SELECT r.uid_foreign, r.fieldname, COUNT(*) AS n FROM sys_file_reference r JOIN sys_file f ON f.uid = r.uid_local
               WHERE r.deleted = 0 AND r.hidden = 0 AND r.tablenames = ? AND r.uid_foreign IN (' . $in . ') AND ' . self::PLACEHOLDER_SQL . '
               GROUP BY r.uid_foreign, r.fieldname'
            : 'SELECT uid_foreign, fieldname, COUNT(*) AS n FROM sys_file_reference
               WHERE deleted = 0 AND hidden = 0 AND tablenames = ? AND uid_foreign IN (' . $in . ')
               GROUP BY uid_foreign, fieldname';
        $rows = $this->connection('sys_file_reference')->fetchAllAssociative($sql, [$table]);
        $out = [];
        foreach ($rows as $r) {
            $out[(int)$r['uid_foreign']][(string)$r['fieldname']] = (int)$r['n'];
        }
        return $out;
    }

    /**
     * Files of one record with original URL and a ~480 px thumbnail.
     *
     * @param string[]|null $fieldnames null = all fields
     * @return array<int, array{field: string, url: string, thumb: string, name: string, mime: string, placeholder: bool}>
     */
    private function files(string $table, int $uid, ?array $fieldnames = null): array
    {
        $sql = "SELECT r.fieldname, r.uid_local, f.identifier, f.name, f.mime_type
                FROM sys_file_reference r JOIN sys_file f ON f.uid = r.uid_local
                WHERE r.deleted = 0 AND r.hidden = 0 AND r.tablenames = ? AND r.uid_foreign = ?";
        $params = [$table, $uid];
        if ($fieldnames !== null) {
            $sql .= ' AND r.fieldname IN (' . implode(',', array_fill(0, count($fieldnames), '?')) . ')';
            $params = array_merge($params, $fieldnames);
        }
        $sql .= ' ORDER BY r.fieldname, r.sorting_foreign';
        $out = [];
        foreach ($this->connection('sys_file_reference')->fetchAllAssociative($sql, $params) as $r) {
            $url = '/fileadmin' . $r['identifier'];
            $out[] = [
                'field' => (string)preg_replace('/^vitec_/', '', (string)$r['fieldname']),
                'url' => $url,
                'thumb' => $this->thumbnail((int)$r['uid_local'], (string)$r['mime_type'], $url),
                'name' => (string)$r['name'],
                'mime' => (string)$r['mime_type'],
                'placeholder' => self::isPlaceholder((string)$r['identifier'], (string)$r['name']),
            ];
        }
        return $out;
    }

    private function thumbnail(int $fileUid, string $mime, string $fallback): string
    {
        if (!str_starts_with($mime, 'image/') || $mime === 'image/svg+xml') {
            return $fallback;
        }
        try {
            $file = GeneralUtility::makeInstance(ResourceFactory::class)->getFileObject($fileUid);
            $imageService = GeneralUtility::makeInstance(ImageService::class);
            $processed = $imageService->applyProcessingInstructions($file, ['maxWidth' => 480, 'maxHeight' => 480]);
            return $imageService->getImageUri($processed);
        } catch (\Throwable) {
            return $fallback;
        }
    }

    /**
     * Records pointing to the pages via detail_page (first live record per page).
     *
     * @param int[] $pageUids
     * @return array<int, array<string, mixed>> [pageUid] = record row
     */
    private function records(string $table, array $pageUids): array
    {
        if ($pageUids === []) {
            return [];
        }
        $rows = $this->connection($table)->fetchAllAssociative(
            'SELECT uid, title, subtitle, teaser, description, detail_page FROM ' . $table . '
             WHERE deleted = 0 AND detail_page IN (' . implode(',', array_map('intval', $pageUids)) . ') ORDER BY hidden, uid'
        );
        $out = [];
        foreach ($rows as $r) {
            $out[(int)$r['detail_page']] ??= $r;
        }
        return $out;
    }

    private function countAll(string $table, string $where): int
    {
        return (int)$this->connection($table)->fetchOne("SELECT COUNT(*) FROM $table WHERE $where");
    }

    private function connection(string $table): Connection
    {
        return $this->connectionPool->getConnectionForTable($table);
    }
}