<?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)');
    }

    /**
     * @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);

        $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,
                ]);

                $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,
     *   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);
        $sqlParts = [];
        $bind = [];

        $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) {
            $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) . '%';
        }

        $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 {
            // 既定は「今日+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();

        $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,
     *   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));
        $res = $this->searchItems($filters, $limit, 1);
        return $res['items'];
    }

    /**
     * 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 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'] ?? '')),
            ],
        ];
    }

    /**
     * @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);
    }
}