<?php

namespace Zab\ZabSite\ViewHelpers;

use TYPO3\CMS\Core\Database\Connection;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;
use TYPO3\CMS\Extbase\Utility\LocalizationUtility;
use TYPO3Fluid\Fluid\Core\ViewHelper\AbstractViewHelper;

/**
 * Finds which content block, info-hub record or download file matches the
 * search word and returns the pathUri with #hash appended or a direct file URL.
 *
 * Usage: <zab:searchResultAnchors pathUri="{row.pathUri}" pageId="{row.page_id}" sword="{searchParams.sword}" />
 */
class SearchResultAnchorsViewHelper extends AbstractViewHelper
{
    protected $escapeOutput = false;

    private static ?array $blockConfig = null;

    public function initializeArguments(): void
    {
        $this->registerArgument('pathUri', 'string', 'The original path URI from the search result', true);
        $this->registerArgument('pageId', 'int', 'The page ID of the search result', true);
        $this->registerArgument('sword', 'string', 'The search word(s)', true);
        $this->registerArgument('as', 'string', 'Variable name for the matched anchors array', false, 'searchAnchors');
    }

    public function render(): string
    {
        $pathUri = (string)$this->arguments['pathUri'];
        $pageId = (int)$this->arguments['pageId'];
        $sword = trim((string)$this->arguments['sword']);
        $as = $this->arguments['as'];

        $anchors = [];

        if ($sword !== '') {
            // 1. Check info-hub records only if the page has an info-hub-list block
            if ($this->pageHasInfoHubList($pageId)) {
                $infoHubMatches = $this->findAllMatchingInfoHubSlugs($sword);
                foreach ($infoHubMatches as $match) {
                    $slug = ltrim($match['slug'], '/');
                    $anchors[] = [
                        'uri' => $pathUri . '?infoHub=' . $slug,
                        'title' => $match['title'],
                    ];
                }
            }

            // 2. Check tt_content blocks on this page
            /* if (empty($anchors)) {
                $blockMatches = $this->findAllMatchingBlockUuids($pageId, $sword);
                foreach ($blockMatches as $match) {
                    $anchors[] = [
                        'uri' => $pathUri . '#' . $match['uuid'],
                        'title' => $match['title'],
                    ];
                }
            } */
        }

        // If no matches, return single entry with plain pathUri
        if (empty($anchors)) {
            $uri = $pathUri;
            $title = '';
            if ($sword !== '' && $this->pageHasFaqsList($pageId)) {
                $uri .= '?tx_zabfaqs%5Bsearch%5D=' . rawurlencode($sword);
                $title = LocalizationUtility::translate('search.viewResults', 'zab_site') ?? 'View results';
            }
            $anchors[] = [
                'uri' => $uri,
                'title' => $title,
            ];
        }

        $this->renderingContext->getVariableProvider()->add($as, $anchors);
        $content = $this->renderChildren();
        $this->renderingContext->getVariableProvider()->remove($as);

        return (string)$content;
    }

    /**
     * Dynamically discovers content block columns from tt_content schema.
     * Finds zab_*_uuid columns and their associated text fields.
     */
    protected function getBlockConfig(): array
    {
        if (self::$blockConfig !== null) {
            return self::$blockConfig;
        }

        $connection = GeneralUtility::makeInstance(ConnectionPool::class)
            ->getConnectionForTable('tt_content');

        $columns = $connection->executeQuery('SHOW COLUMNS FROM tt_content WHERE Field LIKE \'zab_%\'')
            ->fetchAllAssociative();

        // Group columns by block prefix (e.g. "zab_text", "zab_banner")
        $uuidColumns = [];
        $textColumns = [];

        foreach ($columns as $col) {
            $field = $col['Field'];
            $type = strtolower($col['Type']);

            // Detect UUID columns: zab_{blockname}_uuid
            if (str_ends_with($field, '_uuid') && str_contains($type, 'varchar')) {
                // Extract block prefix: "zab_text_uuid" -> "zab_text"
                $prefix = substr($field, 0, -5); // remove "_uuid"
                $uuidColumns[$prefix] = $field;
            }
            // Detect text columns (varchar, text, longtext) - skip int, enum, opt fields, etc.
            elseif ((str_contains($type, 'text') || str_contains($type, 'varchar')) && !str_contains($field, '_opt_')) {
                // Extract block prefix: "zab_text_title" -> "zab_text"
                // Find the second underscore to get the prefix
                $parts = explode('_', $field, 3); // ["zab", "text", "title"]
                if (count($parts) >= 3) {
                    $prefix = $parts[0] . '_' . $parts[1];
                    $textColumns[$prefix][] = $field;
                }
            }
        }

        self::$blockConfig = [];
        foreach ($uuidColumns as $prefix => $uuidCol) {
            self::$blockConfig[$prefix] = [
                'uuid' => $uuidCol,
                'fields' => $textColumns[$prefix] ?? [],
            ];
        }

        return self::$blockConfig;
    }

    protected function findAllMatchingInfoHubSlugs(string $sword): array
    {
        $connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);
        $connection = $connectionPool->getConnectionForTable('tx_zab_info_hub');

        // Dynamically discover all text/varchar columns with zab_recordinfohub_ prefix
        $columns = $connection->executeQuery("SHOW COLUMNS FROM tx_zab_info_hub WHERE Field LIKE 'zab_recordinfohub_%'")
            ->fetchAllAssociative();

        $textFields = [];
        $slugField = null;
        $titleField = null;
        $downloadRecordsField = null;

        foreach ($columns as $col) {
            $field = $col['Field'];
            $type = strtolower($col['Type']);

            if (str_ends_with($field, '_slug')) {
                $slugField = $field;
            } elseif (str_ends_with($field, '_title') && $titleField === null) {
                $titleField = $field;
            }

            if (str_ends_with($field, '_downloads_records')) {
                $downloadRecordsField = $field;
                continue;
            }

            // Only index text/varchar fields, skip option/color/line fields
            if (str_contains($type, 'text') || str_contains($type, 'varchar')) {
                if (!str_contains($field, '_opt_') && !str_contains($field, '_slug')) {
                    $textFields[] = $field;
                }
            }
        }

        if ($slugField === null || $titleField === null) {
            return [];
        }

        // Fetch all info-hub rows
        $selectFields = array_unique(array_merge([$slugField, $titleField], $textFields));
        if ($downloadRecordsField !== null) {
            $selectFields[] = $downloadRecordsField;
        }

        $queryBuilder = $connectionPool->getQueryBuilderForTable('tx_zab_info_hub');
        $rows = $queryBuilder
            ->select(...$selectFields)
            ->from('tx_zab_info_hub')
            ->executeQuery()
            ->fetchAllAssociative();

        // Pre-fetch all referenced download records
        $allDownloads = [];
        if ($downloadRecordsField !== null) {
            $downloadUids = [];
            foreach ($rows as $row) {
                $uids = GeneralUtility::intExplode(',', (string)($row[$downloadRecordsField] ?? ''), true);
                $downloadUids = array_merge($downloadUids, $uids);
            }
            $downloadUids = array_unique($downloadUids);

            if (!empty($downloadUids)) {
                $allDownloads = $this->fetchDownloadTexts($connectionPool, $downloadUids);
            }
        }

        $matches = [];
        foreach ($rows as $row) {
            // Concatenate all text fields
            $content = '';
            foreach ($textFields as $field) {
                $content .= ' ' . ($row[$field] ?? '');
            }
            $content = strip_tags($content);

            // Append download content
            if ($downloadRecordsField !== null) {
                $dlUids = GeneralUtility::intExplode(',', (string)($row[$downloadRecordsField] ?? ''), true);
                foreach ($dlUids as $dlUid) {
                    if (isset($allDownloads[$dlUid])) {
                        $content .= ' ' . $allDownloads[$dlUid];
                    }
                }
            }

            if (mb_stripos($content, $sword) !== false) {
                $matches[] = [
                    'slug' => $row[$slugField] ?? '',
                    'title' => $row[$titleField] ?? '',
                ];
            }
        }

        return $matches;
    }

    /**
     * Fetch all text content from download records for search matching.
     * Includes text fields from tx_zab_downloads AND link labels/filenames
     * from the collection child table (zab_recorddownloads_download_links).
     */
    protected function fetchDownloadTexts(ConnectionPool $connectionPool, array $uids): array
    {
        $connection = $connectionPool->getConnectionForTable('tx_zab_downloads');
        $columns = $connection->executeQuery("SHOW COLUMNS FROM tx_zab_downloads WHERE Field LIKE 'zab_recorddownloads_%'")
            ->fetchAllAssociative();

        $textFields = ['uid'];
        foreach ($columns as $col) {
            $field = $col['Field'];
            $type = strtolower($col['Type']);
            if ((str_contains($type, 'text') || str_contains($type, 'varchar'))
                && !str_contains($field, '_opt_') && !str_contains($field, '_slug') && !str_contains($field, 'link')) {
                $textFields[] = $field;
            }
        }

        $qb = $connectionPool->getQueryBuilderForTable('tx_zab_downloads');
        $dlRows = $qb
            ->select(...$textFields)
            ->from('tx_zab_downloads')
            ->where($qb->expr()->in('uid', $uids))
            ->executeQuery()
            ->fetchAllAssociative();

        $result = [];
        foreach ($dlRows as $dl) {
            $text = '';
            foreach ($textFields as $field) {
                if ($field === 'uid') continue;
                $text .= ' ' . ($dl[$field] ?? '');
            }
            $result[(int)$dl['uid']] = strip_tags($text);
        }

        // Append link labels and filenames from Collection child table
        $linkTexts = $this->fetchDownloadLinkTexts($connectionPool, $uids);
        foreach ($linkTexts as $dlUid => $linkText) {
            $result[$dlUid] = ($result[$dlUid] ?? '') . ' ' . $linkText;
        }

        return $result;
    }

    /**
     * Fetch link titles, filenames, and generated labels from the download_links
     * Collection child table, matching what the DownloadCard Partial renders.
     */
    protected function fetchDownloadLinkTexts(ConnectionPool $connectionPool, array $downloadUids): array
    {
        $childTable = 'zab_recorddownloads_download_links';

        // Check if table exists
        try {
            $connection = $connectionPool->getConnectionForTable($childTable);
            $connection->executeQuery("SELECT 1 FROM `$childTable` LIMIT 0");
        } catch (\Exception $e) {
            return [];
        }

        $qb = $connectionPool->getQueryBuilderForTable($childTable);
        $rows = $qb
            ->select('foreign_table_parent_uid', 'link')
            ->from($childTable)
            ->where(
                $qb->expr()->in('foreign_table_parent_uid', $downloadUids),
                $qb->expr()->eq('deleted', 0),
                $qb->expr()->eq('hidden', 0)
            )
            ->executeQuery()
            ->fetchAllAssociative();

        // Collect all file UIDs referenced in links to batch-resolve filenames
        $fileUids = [];
        foreach ($rows as $row) {
            $linkValue = (string)($row['link'] ?? '');
            if (preg_match('/t3:\/\/file\?uid=(\d+)/', $linkValue, $m)) {
                $fileUids[] = (int)$m[1];
            }
        }

        // Batch fetch file names from sys_file
        $fileNames = [];
        if (!empty($fileUids)) {
            $fileQb = $connectionPool->getQueryBuilderForTable('sys_file');
            $fileRows = $fileQb
                ->select('uid', 'name', 'extension')
                ->from('sys_file')
                ->where($fileQb->expr()->in('uid', array_unique($fileUids)))
                ->executeQuery()
                ->fetchAllAssociative();

            foreach ($fileRows as $f) {
                $fileNames[(int)$f['uid']] = [
                    'name' => $f['name'],
                    'extension' => strtolower($f['extension']),
                ];
            }
        }

        // Build text per download UID
        $result = [];
        foreach ($rows as $row) {
            $parentUid = (int)$row['foreign_table_parent_uid'];
            $linkValue = (string)($row['link'] ?? '');
            $parts = [];

            // Extract link title (stored as JSON or TypoLink parts)
            $linkTitle = $this->extractLinkTitle($linkValue);
            if ($linkTitle !== '') {
                $parts[] = $linkTitle;
            }

            // Generate the same label the template renders
            if (preg_match('/t3:\/\/file\?uid=(\d+)/', $linkValue, $m)) {
                $fileUid = (int)$m[1];
                $file = $fileNames[$fileUid] ?? null;
                if ($file !== null) {
                    $parts[] = $file['name']; // filename
                    // Same logic as DownloadCard.html
                    if ($file['extension'] === 'pdf') {
                        $parts[] = LocalizationUtility::translate('download.pdf', 'zab_site') ?? 'Download PDF';
                    } else {
                        $parts[] = LocalizationUtility::translate('download.file', 'zab_site') ?? 'Download File';
                    }
                }
            } else {
                // External URL
                $parts[] = LocalizationUtility::translate('download.link', 'zab_site') ?? 'External Link';
            }

            if (!empty($parts)) {
                $result[$parentUid] = ($result[$parentUid] ?? '') . ' ' . implode(' ', $parts);
            }
        }

        return $result;
    }

    /**
     * Extract title from a TYPO3 link field value.
     * Handles both JSON format and classic TypoLink format.
     */
    protected function extractLinkTitle(string $linkValue): string
    {
        // Try JSON format first (TYPO3 v12+)
        if (str_starts_with($linkValue, '{')) {
            $data = json_decode($linkValue, true);
            if (is_array($data) && !empty($data['title'])) {
                return (string)$data['title'];
            }
        }

        // Classic TypoLink: "url _target css-class "title""
        if (preg_match('/"([^"]+)"/', $linkValue, $m)) {
            return $m[1];
        }

        return '';
    }


    protected function pageHasFaqsList(int $pageId): bool
    {
        $connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);
        $queryBuilder = $connectionPool->getQueryBuilderForTable('tt_content');

        $count = $queryBuilder
            ->count('uid')
            ->from('tt_content')
            ->where(
                $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter($pageId, Connection::PARAM_INT)),
                $queryBuilder->expr()->eq('CType', $queryBuilder->createNamedParameter('zab_faqslist')),
                $queryBuilder->expr()->eq('deleted', 0),
                $queryBuilder->expr()->eq('hidden', 0)
            )
            ->executeQuery()
            ->fetchOne();

        return (int)$count > 0;
    }

    protected function pageHasInfoHubList(int $pageId): bool
    {
        $connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);
        $queryBuilder = $connectionPool->getQueryBuilderForTable('tt_content');

        $count = $queryBuilder
            ->count('uid')
            ->from('tt_content')
            ->where(
                $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter($pageId, Connection::PARAM_INT)),
                $queryBuilder->expr()->eq('CType', $queryBuilder->createNamedParameter('zab_infohublist')),
                $queryBuilder->expr()->eq('deleted', 0),
                $queryBuilder->expr()->eq('hidden', 0)
            )
            ->executeQuery()
            ->fetchOne();

        return (int)$count > 0;
    }

    private static ?array $collectionConfig = null;

    /**
     * Dynamically discovers collection child tables and their text fields.
     * Tables follow the pattern zab_{blockname}_{suffix} with foreign_table_parent_uid.
     */
    protected function getCollectionConfig(): array
    {
        if (self::$collectionConfig !== null) {
            return self::$collectionConfig;
        }

        $connection = GeneralUtility::makeInstance(ConnectionPool::class)
            ->getConnectionForTable('tt_content');

        // Find all zab_* tables (excluding tx_zab_* which are record types)
        $tables = $connection->executeQuery(
            "SHOW TABLES LIKE 'zab_%'"
        )->fetchAllAssociative();

        self::$collectionConfig = [];

        foreach ($tables as $tableRow) {
            $tableName = reset($tableRow);

            // Check if it has foreign_table_parent_uid (confirms it's a collection child)
            $hasForeignKey = $connection->executeQuery(
                "SHOW COLUMNS FROM `$tableName` LIKE 'foreign_table_parent_uid'"
            )->fetchOne();

            if (!$hasForeignKey) {
                continue;
            }

            // Get text columns (skip system fields)
            $columns = $connection->executeQuery(
                "SHOW COLUMNS FROM `$tableName`"
            )->fetchAllAssociative();

            $skipFields = ['uid', 'pid', 'tstamp', 'crdate', 'deleted', 'hidden', 'sorting',
                'sys_language_uid', 'l10n_parent', 'l10n_state', 'l10n_diffsource', 't3ver_oid',
                't3ver_wsid', 't3ver_state', 'fe_group', 'foreign_table_parent_uid',
                'tablenames', 'fieldname', 'table_local'];

            $textFields = [];
            foreach ($columns as $col) {
                $field = $col['Field'];
                $type = strtolower($col['Type']);

                if (in_array($field, $skipFields, true)) {
                    continue;
                }
                // Skip non-text fields (int, images, links, option/select fields)
                if (!str_contains($type, 'text') && !str_contains($type, 'varchar')) {
                    continue;
                }
                if (str_contains($field, '_opt_') || str_contains($field, 'opt_')) {
                    continue;
                }
                if (str_contains($field, 'link')) {
                    continue;
                }
                $textFields[] = $field;
            }

            if (!empty($textFields)) {
                // Derive CType prefix: "zab_cardsscroller_cards" -> "zab_cardsscroller"
                $prefix = substr($tableName, 0, (int)strrpos($tableName, '_'));
                self::$collectionConfig[$tableName] = [
                    'ctype' => $prefix,
                    'fields' => $textFields,
                ];
            }
        }

        return self::$collectionConfig;
    }

    protected function findAllMatchingBlockUuids(int $pageId, string $sword): array
    {
        $connectionPool = GeneralUtility::makeInstance(ConnectionPool::class);
        $blockConfig = $this->getBlockConfig();
        $matches = [];
        $foundUuids = [];

        $queryBuilder = $connectionPool->getQueryBuilderForTable('tt_content');
        $rows = $queryBuilder
            ->select('uid', 'CType')
            ->from('tt_content')
            ->where(
                $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter($pageId, Connection::PARAM_INT)),
                $queryBuilder->expr()->eq('deleted', 0),
                $queryBuilder->expr()->eq('hidden', 0)
            )
            ->orderBy('sorting')
            ->executeQuery()
            ->fetchAllAssociative();

        foreach ($rows as $row) {
            $ctype = $row['CType'] ?? '';
            $config = $blockConfig[$ctype] ?? null;

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

            // 1. Check direct text fields on tt_content
            if (!empty($config['fields'])) {
                $selectFields = array_merge([$config['uuid']], $config['fields']);
                $detailBuilder = $connectionPool->getQueryBuilderForTable('tt_content');
                $detail = $detailBuilder
                    ->select(...$selectFields)
                    ->from('tt_content')
                    ->where(
                        $detailBuilder->expr()->eq('uid', $detailBuilder->createNamedParameter($row['uid'], Connection::PARAM_INT))
                    )
                    ->executeQuery()
                    ->fetchAssociative();

                if ($detail) {
                    $content = '';
                    foreach ($config['fields'] as $field) {
                        $content .= ' ' . ($detail[$field] ?? '');
                    }
                    $uuid = $detail[$config['uuid']] ?? '';
                    if ($uuid !== '' && !isset($foundUuids[$uuid]) && mb_stripos(strip_tags($content), $sword) !== false) {
                        $foundUuids[$uuid] = true;
                        // Use first text field as title
                        $title = '';
                        foreach ($config['fields'] as $field) {
                            $val = trim(strip_tags($detail[$field] ?? ''));
                            if ($val !== '') {
                                $title = $val;
                                break;
                            }
                        }
                        $matches[] = ['uuid' => $uuid, 'title' => $title];
                    }
                }
            }

            // 2. Check collection child tables
            $collectionConfig = $this->getCollectionConfig();
            foreach ($collectionConfig as $childTable => $childConfig) {
                if ($childConfig['ctype'] !== $ctype) {
                    continue;
                }

                $childFields = $childConfig['fields'];
                $childBuilder = $connectionPool->getQueryBuilderForTable($childTable);
                $children = $childBuilder
                    ->select(...$childFields)
                    ->from($childTable)
                    ->where(
                        $childBuilder->expr()->eq('foreign_table_parent_uid', $childBuilder->createNamedParameter($row['uid'], Connection::PARAM_INT)),
                        $childBuilder->expr()->eq('deleted', 0),
                        $childBuilder->expr()->eq('hidden', 0)
                    )
                    ->executeQuery()
                    ->fetchAllAssociative();

                foreach ($children as $child) {
                    $content = '';
                    foreach ($childFields as $field) {
                        $content .= ' ' . ($child[$field] ?? '');
                    }
                    if (mb_stripos(strip_tags($content), $sword) !== false) {
                        $uuidBuilder = $connectionPool->getQueryBuilderForTable('tt_content');
                        $uuid = $uuidBuilder
                            ->select($config['uuid'])
                            ->from('tt_content')
                            ->where(
                                $uuidBuilder->expr()->eq('uid', $uuidBuilder->createNamedParameter($row['uid'], Connection::PARAM_INT))
                            )
                            ->executeQuery()
                            ->fetchOne();

                        if ($uuid && !isset($foundUuids[$uuid])) {
                            $foundUuids[$uuid] = true;
                            $title = '';
                            foreach ($childFields as $field) {
                                $val = trim(strip_tags($child[$field] ?? ''));
                                if ($val !== '') {
                                    $title = $val;
                                    break;
                                }
                            }
                            $matches[] = ['uuid' => $uuid, 'title' => $title];
                        }
                    }
                }
            }
        }

        return $matches;
    }
}