<?php

declare(strict_types=1);

final class ProductDb
{
    private PDO $pdo;

    public function __construct(string $dbPath)
    {
        $dir = dirname($dbPath);
        if (!is_dir($dir)) {
            @mkdir($dir, 0775, true);
        }
        $this->pdo = new PDO('sqlite:' . $dbPath);
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
        $this->pdo->exec('PRAGMA busy_timeout = 5000');
        // Read-heavy search workload tuning.
        $this->pdo->exec('PRAGMA synchronous = NORMAL');
        $this->pdo->exec('PRAGMA temp_store = MEMORY');
        $this->pdo->exec('PRAGMA cache_size = -20000');
    }

    public function ensureSchema(): void
    {
        $this->pdo->exec(
            'CREATE TABLE IF NOT EXISTS products (
                content_id TEXT PRIMARY KEY,
                product_id TEXT,
                title TEXT,
                date TEXT,
                date_ymd TEXT,
                affiliate_url TEXT,
                url TEXT,
                image_small TEXT,
                image_large TEXT,
                maker_names TEXT,
                series_names TEXT,
                actress_names TEXT,
                genre_names TEXT,
                source_flags TEXT,
                raw_json TEXT,
                updated_at TEXT
            )'
        );
        $this->pdo->exec(
            'CREATE TABLE IF NOT EXISTS product_sources (
                content_id TEXT NOT NULL,
                source TEXT NOT NULL,
                PRIMARY KEY(content_id, source)
            )'
        );
        if (!$this->columnExists('products', 'date_ymd')) {
            $this->pdo->exec('ALTER TABLE products ADD COLUMN date_ymd TEXT');
        }
        $this->pdo->exec('UPDATE products SET date_ymd = substr(date,1,10) WHERE (date_ymd IS NULL OR date_ymd = "") AND date IS NOT NULL');
        $this->pdo->exec('CREATE INDEX IF NOT EXISTS idx_products_date ON products(date)');
        $this->pdo->exec('CREATE INDEX IF NOT EXISTS idx_products_date_ymd ON products(date_ymd)');
        $this->pdo->exec('CREATE INDEX IF NOT EXISTS idx_products_date_content ON products(date DESC, content_id DESC)');
        $this->pdo->exec('CREATE INDEX IF NOT EXISTS idx_products_title ON products(title)');
        $this->pdo->exec('CREATE INDEX IF NOT EXISTS idx_products_updated_at ON products(updated_at)');
        $this->pdo->exec('CREATE INDEX IF NOT EXISTS idx_product_sources_source ON product_sources(source)');
        $this->pdo->exec(
            'CREATE TABLE IF NOT EXISTS product_actress_map (
                content_id TEXT NOT NULL,
                actress_name TEXT NOT NULL,
                actress_name_norm TEXT NOT NULL,
                PRIMARY KEY(content_id, actress_name_norm)
            )'
        );
        $this->pdo->exec(
            'CREATE TABLE IF NOT EXISTS product_genre_map (
                content_id TEXT NOT NULL,
                genre_name TEXT NOT NULL,
                genre_name_norm TEXT NOT NULL,
                PRIMARY KEY(content_id, genre_name_norm)
            )'
        );
        $this->pdo->exec('CREATE INDEX IF NOT EXISTS idx_product_actress_map_name ON product_actress_map(actress_name_norm, content_id)');
        $this->pdo->exec('CREATE INDEX IF NOT EXISTS idx_product_genre_map_name ON product_genre_map(genre_name_norm, content_id)');
    }

    /**
     * @param array<int,array<string,mixed>> $items
     */
    public function upsertItems(array $items, string $source): int
    {
        if (count($items) === 0) {
            return 0;
        }

        $itemSql = 'INSERT INTO products (
            content_id,product_id,title,date,date_ymd,affiliate_url,url,image_small,image_large,
            maker_names,series_names,actress_names,genre_names,source_flags,raw_json,updated_at
        ) VALUES (
            :content_id,:product_id,:title,:date,:date_ymd,:affiliate_url,:url,:image_small,:image_large,
            :maker_names,:series_names,:actress_names,:genre_names,:source_flags,:raw_json,:updated_at
        )
        ON CONFLICT(content_id) DO UPDATE SET
            product_id=excluded.product_id,
            title=excluded.title,
            date=excluded.date,
            date_ymd=excluded.date_ymd,
            affiliate_url=excluded.affiliate_url,
            url=excluded.url,
            image_small=excluded.image_small,
            image_large=excluded.image_large,
            maker_names=excluded.maker_names,
            series_names=excluded.series_names,
            actress_names=excluded.actress_names,
            genre_names=excluded.genre_names,
            source_flags=excluded.source_flags,
            raw_json=excluded.raw_json,
            updated_at=excluded.updated_at';

        $sourceSql = 'INSERT OR IGNORE INTO product_sources (content_id, source) VALUES (:content_id, :source)';

        $itemStmt = $this->pdo->prepare($itemSql);
        $srcStmt = $this->pdo->prepare($sourceSql);
        $deleteActressMapStmt = $this->pdo->prepare('DELETE FROM product_actress_map WHERE content_id = :content_id');
        $insertActressMapStmt = $this->pdo->prepare(
            'INSERT OR REPLACE INTO product_actress_map (content_id, actress_name, actress_name_norm)
             VALUES (:content_id, :actress_name, :actress_name_norm)'
        );
        $deleteGenreMapStmt = $this->pdo->prepare('DELETE FROM product_genre_map WHERE content_id = :content_id');
        $insertGenreMapStmt = $this->pdo->prepare(
            'INSERT OR REPLACE INTO product_genre_map (content_id, genre_name, genre_name_norm)
             VALUES (:content_id, :genre_name, :genre_name_norm)'
        );

        $count = 0;
        $now = date('c');

        $this->pdo->beginTransaction();
        try {
            foreach ($items as $item) {
                $contentId = $this->str($item['content_id'] ?? null);
                if ($contentId === null) {
                    continue;
                }

                $makerNames = $this->joinNames($item['iteminfo']['maker'] ?? null);
                $seriesNames = $this->joinNames($item['iteminfo']['series'] ?? null);
                $actressNames = $this->joinNames($item['iteminfo']['actress'] ?? null);
                $genreNames = $this->joinNames($item['iteminfo']['genre'] ?? null);

                $itemStmt->execute([
                    ':content_id' => $contentId,
                    ':product_id' => $this->str($item['product_id'] ?? null),
                    ':title' => $this->str($item['title'] ?? null),
                    ':date' => $this->str($item['date'] ?? null),
                    ':date_ymd' => $this->dateYmd($item['date'] ?? null),
                    ':affiliate_url' => $this->str($item['affiliateURL'] ?? null),
                    ':url' => $this->str($item['URL'] ?? null),
                    ':image_small' => $this->str($item['imageURL']['small'] ?? null),
                    ':image_large' => $this->str($item['imageURL']['large'] ?? null),
                    ':maker_names' => $makerNames,
                    ':series_names' => $seriesNames,
                    ':actress_names' => $actressNames,
                    ':genre_names' => $genreNames,
                    ':source_flags' => $source,
                    ':raw_json' => json_encode($item, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
                    ':updated_at' => $now,
                ]);

                $srcStmt->execute([
                    ':content_id' => $contentId,
                    ':source' => $source,
                ]);
                $this->syncLookupMaps(
                    $contentId,
                    $item['iteminfo']['actress'] ?? null,
                    $item['iteminfo']['genre'] ?? null,
                    $deleteActressMapStmt,
                    $insertActressMapStmt,
                    $deleteGenreMapStmt,
                    $insertGenreMapStmt
                );

                $count++;
            }
            $this->pdo->commit();
        } catch (Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }

        return $count;
    }

    public function countAll(): int
    {
        return (int) $this->pdo->query('SELECT COUNT(*) FROM products')->fetchColumn();
    }

    public function hasRows(): bool
    {
        return $this->countAll() > 0;
    }

    /**
     * @param array{
     *   keyword?:string,
     *   product_keyword?:string,
     *   director_keyword?:string,
     *   cid_keyword?:string,
     *   product_actress_keyword?:string,
     *   maker_keyword?:string,
     *   series_keyword?:string,
     *   genre_keyword?:string,
     *   maker_exact?:string,
     *   series_exact?:string,
     *   genre_exact_list?:array<int,string>,
     *   release_date_from?:string,
     *   release_date_to?:string
     * } $filters
     * @return array{items:array<int,array<string,mixed>>,total_count:int}
     */
    public function searchItems(array $filters, int $limit, int $offset): array
    {
        // list表示は通常 20/40/60/100 件だが、集計用途では 1000+ 件を読むため上限を広げる
        $limit = max(1, min(5000, $limit));
        $offset = max(1, $offset);
        ['where' => $where, 'bind' => $bind] = $this->buildSearchWhere($filters);
        $countSql = 'SELECT COUNT(*) FROM products' . $where;
        $countStmt = $this->pdo->prepare($countSql);
        foreach ($bind as $k => $v) {
            $countStmt->bindValue($k, $v, PDO::PARAM_STR);
        }
        $countStmt->execute();
        $total = (int) $countStmt->fetchColumn();

        $rowOffset = max(0, $offset - 1);
        $querySql = 'SELECT content_id, raw_json, product_id, title, date, affiliate_url, url, image_small, image_large,
                            maker_names, series_names, actress_names, genre_names
                     FROM products' . $where . '
                     ORDER BY date DESC, content_id DESC
                     LIMIT :limit OFFSET :offset';
        $stmt = $this->pdo->prepare($querySql);
        foreach ($bind as $k => $v) {
            $stmt->bindValue($k, $v, PDO::PARAM_STR);
        }
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $rowOffset, PDO::PARAM_INT);
        $stmt->execute();
        $rows = $stmt->fetchAll();

        $items = [];
        if (is_array($rows)) {
            foreach ($rows as $row) {
                if (!is_array($row)) {
                    continue;
                }
                $items[] = $this->rowToItem($row);
            }
        }
        return ['items' => $items, 'total_count' => $total];
    }

    public function optimize(bool $full = false): void
    {
        $this->bestEffortExec('PRAGMA optimize');
        $this->bestEffortExec('ANALYZE');
        if ($full) {
            $this->bestEffortExec('REINDEX');
            $this->bestEffortExec('VACUUM');
        }
    }

    /**
     * @param array{
     *   keyword?:string,
     *   product_keyword?:string,
     *   director_keyword?:string,
     *   cid_keyword?:string,
     *   product_actress_keyword?:string,
     *   maker_keyword?:string,
     *   series_keyword?:string,
     *   genre_keyword?:string,
     *   maker_exact?:string,
     *   series_exact?:string,
     *   genre_exact_list?:array<int,string>,
     *   release_date_from?:string,
     *   release_date_to?:string
     * } $filters
     * @return array<int,array<string,mixed>>
     */
    public function searchItemsForStats(array $filters, int $limit = 1500): array
    {
        $limit = max(1, min(5000, $limit));
        ['where' => $where, 'bind' => $bind] = $this->buildSearchWhere($filters);
        $sql = 'SELECT content_id, date, maker_names, series_names, actress_names, genre_names
                FROM products' . $where . '
                ORDER BY date DESC, content_id DESC
                LIMIT :limit';
        $stmt = $this->pdo->prepare($sql);
        foreach ($bind as $k => $v) {
            $stmt->bindValue($k, $v, PDO::PARAM_STR);
        }
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        $rows = $stmt->fetchAll();

        $items = [];
        if (is_array($rows)) {
            foreach ($rows as $row) {
                if (!is_array($row)) {
                    continue;
                }
                $items[] = $this->statsRowToItem($row);
            }
        }

        return $items;
    }

    /**
     * @param array<string,mixed> $filters
     * @return array<int,array{cid:string,title:string,date:string}>
     */
    public function findCardSummaries(array $filters, int $limit = 5, string $sort = 'date', ?string $excludeCid = null): array
    {
        $limit = max(1, min(100, $limit));
        ['where' => $where, 'bind' => $bind] = $this->buildSearchWhere($filters);

        $clauses = [];
        if ($where !== '') {
            $clauses[] = substr($where, 7);
        }
        $excludeCid = is_string($excludeCid) ? trim($excludeCid) : '';
        if ($excludeCid !== '') {
            $clauses[] = 'content_id <> :exclude_cid';
            $bind[':exclude_cid'] = $excludeCid;
        }
        $whereSql = count($clauses) > 0 ? (' WHERE ' . implode(' AND ', $clauses)) : '';

        $orderBy = 'date DESC, content_id DESC';
        if ($sort === 'popularity') {
            $orderBy = '(
                COALESCE(CAST(json_extract(raw_json, \'$.review.count\') AS REAL), 0) * 10.0 +
                COALESCE(CAST(json_extract(raw_json, \'$.review.average\') AS REAL), 0)
            ) DESC, date DESC, content_id DESC';
        }

        $stmt = $this->pdo->prepare(
            'SELECT content_id, title, date
             FROM products' . $whereSql . '
             ORDER BY ' . $orderBy . '
             LIMIT :limit'
        );
        foreach ($bind as $k => $v) {
            $stmt->bindValue($k, $v, PDO::PARAM_STR);
        }
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        $rows = $stmt->fetchAll();

        $items = [];
        if (!is_array($rows)) {
            return $items;
        }
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $cid = isset($row['content_id']) && is_string($row['content_id']) ? trim($row['content_id']) : '';
            if ($cid === '') {
                continue;
            }
            $items[] = [
                'cid' => $cid,
                'title' => isset($row['title']) && is_string($row['title']) ? $row['title'] : '',
                'date' => isset($row['date']) && is_string($row['date']) ? $row['date'] : '',
            ];
        }

        return $items;
    }

    /**
     * @param array<string,mixed> $filters
     * @return array{items:array<int,array<string,mixed>>,has_more:bool}
     */
    public function searchItemsPageNoCount(array $filters, int $limit, int $offset): array
    {
        $limit = max(1, min(5000, $limit));
        $offset = max(1, $offset);
        ['where' => $where, 'bind' => $bind] = $this->buildSearchWhere($filters);

        $fetchLimit = min(5000, $limit + 1);
        $rowOffset = max(0, $offset - 1);
        $querySql = 'SELECT content_id, raw_json, product_id, title, date, affiliate_url, url, image_small, image_large,
                            maker_names, series_names, actress_names, genre_names
                     FROM products' . $where . '
                     ORDER BY date DESC, content_id DESC
                     LIMIT :limit OFFSET :offset';
        $stmt = $this->pdo->prepare($querySql);
        foreach ($bind as $k => $v) {
            $stmt->bindValue($k, $v, PDO::PARAM_STR);
        }
        $stmt->bindValue(':limit', $fetchLimit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $rowOffset, PDO::PARAM_INT);
        $stmt->execute();
        $rows = $stmt->fetchAll();

        $hasMore = is_array($rows) && count($rows) > $limit;
        if ($hasMore && is_array($rows)) {
            $rows = array_slice($rows, 0, $limit);
        }

        $items = [];
        if (is_array($rows)) {
            foreach ($rows as $row) {
                if (!is_array($row)) {
                    continue;
                }
                $items[] = $this->rowToItem($row);
            }
        }

        return ['items' => $items, 'has_more' => $hasMore];
    }

    /**
     * DMM ItemList互換の最小検索（DBフォールバック用）
     *
     * @param array{
     *   keyword?:string,
     *   cid?:string,
     *   article?:string,
     *   article_names?:array<int,string>,
     *   sort?:string
     * } $filters
     * @return array{items:array<int,array<string,mixed>>,total_count:int}
     */
    public function searchItemsDmmLike(array $filters, int $limit, int $offset): array
    {
        $limit = max(1, min(5000, $limit));
        $offset = max(1, $offset);
        $sqlParts = [];
        $bind = [];

        $keyword = isset($filters['keyword']) && is_string($filters['keyword']) ? trim($filters['keyword']) : '';
        if ($keyword !== '') {
            $sqlParts[] = 'title LIKE :keyword';
            $bind[':keyword'] = '%' . $this->escapeLike($keyword) . '%';
        }

        $cid = isset($filters['cid']) && is_string($filters['cid']) ? trim($filters['cid']) : '';
        if ($cid !== '') {
            $sqlParts[] = 'content_id = :cid COLLATE NOCASE';
            $bind[':cid'] = $cid;
        }

        $article = isset($filters['article']) && is_string($filters['article']) ? trim($filters['article']) : '';
        $articleNames = isset($filters['article_names']) && is_array($filters['article_names']) ? $filters['article_names'] : [];
        $articleColumn = '';
        if ($article === 'genre') {
            $articleColumn = 'genre_names';
        } elseif ($article === 'maker') {
            $articleColumn = 'maker_names';
        } elseif ($article === 'series') {
            $articleColumn = 'series_names';
        } elseif ($article === 'actress') {
            $articleColumn = 'actress_names';
        }
        if ($articleColumn !== '' && count($articleNames) > 0) {
            $orParts = [];
            $idx = 0;
            foreach ($articleNames as $name) {
                if (!is_string($name)) {
                    continue;
                }
                $name = trim($name);
                if ($name === '') {
                    continue;
                }
                $ph = ':article_name_' . $idx;
                $orParts[] = $articleColumn . ' LIKE ' . $ph;
                $bind[$ph] = '%' . $this->escapeLike($name) . '%';
                $idx++;
            }
            if (count($orParts) > 0) {
                $sqlParts[] = '(' . implode(' OR ', $orParts) . ')';
            }
        }

        $sort = isset($filters['sort']) && is_string($filters['sort']) ? strtolower(trim($filters['sort'])) : 'date';
        $dateTo = isset($filters['release_date_to']) && is_string($filters['release_date_to']) ? trim($filters['release_date_to']) : '';
        if ($dateTo === '') {
            // 既定は「今日+3日」までを表示対象にし、遠い未来作の先出しを抑制する。
            $sqlParts[] = 'date_ymd <= :date_to_default';
            $bind[':date_to_default'] = date('Y-m-d', strtotime('+3 days'));
        }
        $where = count($sqlParts) > 0 ? (' WHERE ' . implode(' AND ', $sqlParts)) : '';
        $countSql = 'SELECT COUNT(*) FROM products' . $where;
        $countStmt = $this->pdo->prepare($countSql);
        foreach ($bind as $k => $v) {
            $countStmt->bindValue($k, $v, PDO::PARAM_STR);
        }
        $countStmt->execute();
        $total = (int) $countStmt->fetchColumn();
        // DBフォールバックでは rank も日付順で代替する。
        $orderBy = ' ORDER BY date DESC, content_id DESC';
        if ($sort === 'date' || $sort === 'rank') {
            $orderBy = ' ORDER BY date DESC, content_id DESC';
        }

        $rowOffset = max(0, $offset - 1);
        $querySql = 'SELECT content_id, raw_json, product_id, title, date, affiliate_url, url, image_small, image_large,
                            maker_names, series_names, actress_names, genre_names
                     FROM products' . $where . $orderBy . '
                     LIMIT :limit OFFSET :offset';
        $stmt = $this->pdo->prepare($querySql);
        foreach ($bind as $k => $v) {
            $stmt->bindValue($k, $v, PDO::PARAM_STR);
        }
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $rowOffset, PDO::PARAM_INT);
        $stmt->execute();
        $rows = $stmt->fetchAll();

        $items = [];
        if (is_array($rows)) {
            foreach ($rows as $row) {
                if (!is_array($row)) {
                    continue;
                }
                $items[] = $this->rowToItem($row);
            }
        }

        return ['items' => $items, 'total_count' => $total];
    }

    /**
     * @return array<int,array{source:string,cnt:int}>
     */
    public function sourceStats(): array
    {
        $rows = $this->pdo->query('SELECT source, COUNT(*) AS cnt FROM product_sources GROUP BY source ORDER BY cnt DESC')->fetchAll();
        return is_array($rows) ? $rows : [];
    }

    private function str($v): ?string
    {
        if (!is_string($v)) {
            return null;
        }
        $v = trim($v);
        return $v === '' ? null : $v;
    }

    private function dateYmd($v): ?string
    {
        if (!is_string($v)) {
            return null;
        }
        $v = trim($v);
        if ($v === '') {
            return null;
        }
        if (preg_match('/^(\d{4}-\d{2}-\d{2})/', $v, $m) === 1) {
            return $m[1];
        }
        return null;
    }

    private function columnExists(string $table, string $column): bool
    {
        $stmt = $this->pdo->query('PRAGMA table_info(' . $table . ')');
        $rows = $stmt->fetchAll();
        if (!is_array($rows)) {
            return false;
        }
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $name = isset($row['name']) && is_string($row['name']) ? $row['name'] : '';
            if ($name === $column) {
                return true;
            }
        }
        return false;
    }

    private function bestEffortExec(string $sql): void
    {
        try {
            $this->pdo->exec($sql);
        } catch (Throwable $e) {
            // Optimize is non-critical; keep app functional on shared hosting FS limitations.
        }
    }

    private function escapeLike(string $value): string
    {
        return str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], $value);
    }

    /**
     * @param mixed $actressRows
     * @param mixed $genreRows
     */
    private function syncLookupMaps(
        string $contentId,
        $actressRows,
        $genreRows,
        PDOStatement $deleteActressMapStmt,
        PDOStatement $insertActressMapStmt,
        PDOStatement $deleteGenreMapStmt,
        PDOStatement $insertGenreMapStmt
    ): void {
        $deleteActressMapStmt->execute([':content_id' => $contentId]);
        foreach ($this->collectLookupRows($actressRows, 'actress') as $row) {
            $insertActressMapStmt->execute([
                ':content_id' => $contentId,
                ':actress_name' => $row['name'],
                ':actress_name_norm' => $row['norm'],
            ]);
        }

        $deleteGenreMapStmt->execute([':content_id' => $contentId]);
        foreach ($this->collectLookupRows($genreRows, 'genre') as $row) {
            $insertGenreMapStmt->execute([
                ':content_id' => $contentId,
                ':genre_name' => $row['name'],
                ':genre_name_norm' => $row['norm'],
            ]);
        }
    }

    /**
     * @param mixed $rows
     * @return array<int,array{name:string,norm:string}>
     */
    private function collectLookupRows($rows, string $type): array
    {
        if (!is_array($rows)) {
            return [];
        }
        $items = [];
        $seen = [];
        foreach ($rows as $row) {
            if (is_array($row) && isset($row['name']) && is_string($row['name'])) {
                $name = trim($row['name']);
            } elseif (is_string($row)) {
                $name = trim($row);
            } else {
                $name = '';
            }
            if ($name === '') {
                continue;
            }
            $norm = $type === 'actress'
                ? $this->normalizeActressLookupName($name)
                : $this->normalizeGenreLookupName($name);
            if ($norm === '' || isset($seen[$norm])) {
                continue;
            }
            $seen[$norm] = true;
            $items[] = ['name' => $name, 'norm' => $norm];
        }
        return $items;
    }

    private function normalizeActressLookupName(string $name): string
    {
        $name = trim($name);
        if ($name === '') {
            return '';
        }
        $normalized = preg_replace('/[（(].*?[）)]/u', '', $name);
        if (!is_string($normalized)) {
            $normalized = $name;
        }
        $normalized = trim($normalized);
        if ($normalized === '') {
            $normalized = $name;
        }
        return $this->lowerText($normalized);
    }

    private function normalizeGenreLookupName(string $name): string
    {
        return $this->lowerText(trim($name));
    }

    private function lowerText(string $value): string
    {
        return function_exists('mb_strtolower') ? mb_strtolower($value, 'UTF-8') : strtolower($value);
    }

    /**
     * Existing DB向け: products から対応表を再構築する。
     *
     * @return array{products:int,actress_rows:int,genre_rows:int}
     */
    public function rebuildLookupMaps(int $batchSize = 2000): array
    {
        $batchSize = max(100, min(10000, $batchSize));
        $this->ensureSchema();
        $this->pdo->exec('DELETE FROM product_actress_map');
        $this->pdo->exec('DELETE FROM product_genre_map');

        $selectStmt = $this->pdo->query('SELECT content_id, actress_names, genre_names FROM products ORDER BY content_id ASC');
        $insertActressMapStmt = $this->pdo->prepare(
            'INSERT OR REPLACE INTO product_actress_map (content_id, actress_name, actress_name_norm)
             VALUES (:content_id, :actress_name, :actress_name_norm)'
        );
        $insertGenreMapStmt = $this->pdo->prepare(
            'INSERT OR REPLACE INTO product_genre_map (content_id, genre_name, genre_name_norm)
             VALUES (:content_id, :genre_name, :genre_name_norm)'
        );

        $productCount = 0;
        $actressRows = 0;
        $genreRows = 0;
        $opsInTx = 0;
        $this->pdo->beginTransaction();
        try {
            while (is_array($row = $selectStmt->fetch(PDO::FETCH_ASSOC))) {
                $contentId = isset($row['content_id']) && is_string($row['content_id']) ? trim($row['content_id']) : '';
                if ($contentId === '') {
                    continue;
                }
                foreach ($this->splitLookupNamesText(isset($row['actress_names']) && is_string($row['actress_names']) ? $row['actress_names'] : '') as $name) {
                    $norm = $this->normalizeActressLookupName($name);
                    if ($norm === '') {
                        continue;
                    }
                    $insertActressMapStmt->execute([
                        ':content_id' => $contentId,
                        ':actress_name' => $name,
                        ':actress_name_norm' => $norm,
                    ]);
                    $actressRows++;
                }
                foreach ($this->splitLookupNamesText(isset($row['genre_names']) && is_string($row['genre_names']) ? $row['genre_names'] : '') as $name) {
                    $norm = $this->normalizeGenreLookupName($name);
                    if ($norm === '') {
                        continue;
                    }
                    $insertGenreMapStmt->execute([
                        ':content_id' => $contentId,
                        ':genre_name' => $name,
                        ':genre_name_norm' => $norm,
                    ]);
                    $genreRows++;
                }
                $productCount++;
                $opsInTx++;
                if ($opsInTx >= $batchSize) {
                    $this->pdo->commit();
                    $this->pdo->beginTransaction();
                    $opsInTx = 0;
                }
            }
            $this->pdo->commit();
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $e;
        }

        return [
            'products' => $productCount,
            'actress_rows' => $actressRows,
            'genre_rows' => $genreRows,
        ];
    }

    /**
     * @return array<int,string>
     */
    private function splitLookupNamesText(string $value): array
    {
        $value = trim($value);
        if ($value === '') {
            return [];
        }
        $parts = preg_split('/\s*\/\s*/u', $value) ?: [];
        $items = [];
        $seen = [];
        foreach ($parts as $part) {
            if (!is_string($part)) {
                continue;
            }
            $part = trim($part);
            if ($part === '' || isset($seen[$part])) {
                continue;
            }
            $seen[$part] = true;
            $items[] = $part;
        }
        return $items;
    }

    /**
     * @param array<string,mixed> $row
     * @return array<string,mixed>
     */
    private function rowToItem(array $row): array
    {
        $raw = isset($row['raw_json']) && is_string($row['raw_json']) ? trim($row['raw_json']) : '';
        if ($raw !== '') {
            $decoded = json_decode($raw, true);
            if (is_array($decoded)) {
                return $decoded;
            }
        }
        // Fallback minimal item shape for rendering when raw_json is not available.
        return [
            'content_id' => (string) ($row['content_id'] ?? ''),
            'product_id' => (string) ($row['product_id'] ?? ''),
            'title' => (string) ($row['title'] ?? ''),
            'date' => (string) ($row['date'] ?? ''),
            'affiliateURL' => (string) ($row['affiliate_url'] ?? ''),
            'URL' => (string) ($row['url'] ?? ''),
            'imageURL' => [
                'small' => (string) ($row['image_small'] ?? ''),
                'large' => (string) ($row['image_large'] ?? ''),
            ],
            'iteminfo' => [
                'maker' => $this->splitNames((string) ($row['maker_names'] ?? '')),
                'series' => $this->splitNames((string) ($row['series_names'] ?? '')),
                'actress' => $this->splitNames((string) ($row['actress_names'] ?? '')),
                'genre' => $this->splitNames((string) ($row['genre_names'] ?? '')),
            ],
        ];
    }

    /**
     * @param array<string,mixed> $filters
     * @return array{where:string,bind:array<string,string>}
     */
    private function buildSearchWhere(array $filters): array
    {
        $sqlParts = [];
        $bind = [];

        $productActressExact = isset($filters['product_actress_exact']) && is_string($filters['product_actress_exact'])
            ? trim($filters['product_actress_exact'])
            : '';
        if ($productActressExact !== '') {
            $productActressExactNorm = $this->normalizeActressLookupName($productActressExact);
            if ($productActressExactNorm !== '') {
                $sqlParts[] = 'content_id IN (SELECT content_id FROM product_actress_map WHERE actress_name_norm = :product_actress_exact_norm)';
                $bind[':product_actress_exact_norm'] = $productActressExactNorm;
            }
        }

        $genreExactName = isset($filters['genre_exact_name']) && is_string($filters['genre_exact_name'])
            ? trim($filters['genre_exact_name'])
            : '';
        if ($genreExactName !== '') {
            $genreExactNameNorm = $this->normalizeGenreLookupName($genreExactName);
            if ($genreExactNameNorm !== '') {
                $sqlParts[] = 'content_id IN (SELECT content_id FROM product_genre_map WHERE genre_name_norm = :genre_exact_name_norm)';
                $bind[':genre_exact_name_norm'] = $genreExactNameNorm;
            }
        }

        $likeMap = [
            'keyword' => 'title',
            'product_keyword' => 'title',
            'product_actress_keyword' => 'actress_names',
            'maker_keyword' => 'maker_names',
            'series_keyword' => 'series_names',
            'genre_keyword' => 'genre_names',
            'maker_exact' => 'maker_names',
            'series_exact' => 'series_names',
        ];
        foreach ($likeMap as $filterKey => $column) {
            if ($filterKey === 'product_actress_keyword' && $productActressExact !== '') {
                continue;
            }
            if ($filterKey === 'genre_keyword' && $genreExactName !== '') {
                continue;
            }
            $val = isset($filters[$filterKey]) && is_string($filters[$filterKey]) ? trim($filters[$filterKey]) : '';
            if ($val === '') {
                continue;
            }
            $ph = ':' . $filterKey;
            $sqlParts[] = "{$column} LIKE {$ph}";
            $bind[$ph] = '%' . $this->escapeLike($val) . '%';
        }

        $directorKeyword = isset($filters['director_keyword']) && is_string($filters['director_keyword'])
            ? trim($filters['director_keyword'])
            : '';
        if ($directorKeyword !== '') {
            $sqlParts[] = 'raw_json LIKE :director_keyword';
            $bind[':director_keyword'] = '%"director"%' . $this->escapeLike($directorKeyword) . '%';
        }

        $cidKeyword = isset($filters['cid_keyword']) && is_string($filters['cid_keyword']) ? trim($filters['cid_keyword']) : '';
        if ($cidKeyword !== '') {
            $sqlParts[] = 'content_id = :cid_keyword COLLATE NOCASE';
            $bind[':cid_keyword'] = $cidKeyword;
        }

        $genreExactList = isset($filters['genre_exact_list']) && is_array($filters['genre_exact_list']) ? $filters['genre_exact_list'] : [];
        foreach ($genreExactList as $idx => $name) {
            if (!is_string($name)) {
                continue;
            }
            $name = trim($name);
            if ($name === '') {
                continue;
            }
            $ph = ':genre_exact_' . $idx;
            $sqlParts[] = "genre_names LIKE {$ph}";
            $bind[$ph] = '%' . $this->escapeLike($name) . '%';
        }

        $dateFrom = isset($filters['release_date_from']) && is_string($filters['release_date_from']) ? trim($filters['release_date_from']) : '';
        $dateTo = isset($filters['release_date_to']) && is_string($filters['release_date_to']) ? trim($filters['release_date_to']) : '';
        if ($dateFrom !== '') {
            $sqlParts[] = 'date_ymd >= :date_from';
            $bind[':date_from'] = $dateFrom;
        }
        if ($dateTo !== '') {
            $sqlParts[] = 'date_ymd <= :date_to';
            $bind[':date_to'] = $dateTo;
        } else {
            $sqlParts[] = 'date_ymd <= :date_to_default';
            $bind[':date_to_default'] = date('Y-m-d', strtotime('+3 days'));
        }

        return [
            'where' => count($sqlParts) > 0 ? (' WHERE ' . implode(' AND ', $sqlParts)) : '',
            'bind' => $bind,
        ];
    }

    private function statsRowToItem(array $row): array
    {
        return [
            'content_id' => (string) ($row['content_id'] ?? ''),
            'date' => (string) ($row['date'] ?? ''),
            'iteminfo' => [
                'maker' => $this->splitNames((string) ($row['maker_names'] ?? '')),
                'series' => $this->splitNames((string) ($row['series_names'] ?? '')),
                'actress' => $this->splitNames((string) ($row['actress_names'] ?? '')),
                'genre' => $this->splitNames((string) ($row['genre_names'] ?? '')),
            ],
        ];
    }

    /**
     * @return array<int,array{name:string}>
     */
    private function splitNames(string $value): array
    {
        $value = trim($value);
        if ($value === '') {
            return [];
        }
        $parts = preg_split('/\s*\/\s*/u', $value) ?: [];
        $rows = [];
        foreach ($parts as $name) {
            if (!is_string($name)) {
                continue;
            }
            $name = trim($name);
            if ($name === '') {
                continue;
            }
            $rows[] = ['name' => $name];
        }
        return $rows;
    }

    /**
     * @param mixed $rows
     */
    private function joinNames($rows): ?string
    {
        if (!is_array($rows)) {
            return null;
        }
        $names = [];
        foreach ($rows as $row) {
            if (is_array($row) && isset($row['name']) && is_string($row['name'])) {
                $name = trim($row['name']);
                if ($name !== '') {
                    $names[] = $name;
                }
            }
        }
        if (count($names) === 0) {
            return null;
        }
        return implode(' / ', $names);
    }
}