<?php

declare(strict_types=1);

final class ProductMetaDb
{
    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);
    }

    public function ensureSchema(): void
    {
        $this->pdo->exec(
            'CREATE TABLE IF NOT EXISTS product_meta (
                content_id TEXT PRIMARY KEY,
                supplement_title TEXT,
                description TEXT,
                manual_actresses TEXT,
                operator_review TEXT,
                source_url TEXT,
                play_count INTEGER,
                fav_count INTEGER,
                updated_at TEXT
            )'
        );
        $this->ensureColumnExists('product_meta', 'manual_actresses', 'TEXT');
        $this->ensureColumnExists('product_meta', 'operator_review', 'TEXT');
        $this->pdo->exec('CREATE INDEX IF NOT EXISTS idx_product_meta_updated_at ON product_meta(updated_at)');
        $this->pdo->exec('CREATE INDEX IF NOT EXISTS idx_product_meta_play_count ON product_meta(play_count)');
        $this->pdo->exec('CREATE INDEX IF NOT EXISTS idx_product_meta_fav_count ON product_meta(fav_count)');
    }

    /**
     * @return array<string,mixed>|null
     */
    public function findByCid(string $cid): ?array
    {
        $cid = trim($cid);
        if ($cid === '') {
            return null;
        }
        $stmt = $this->pdo->prepare(
            'SELECT content_id, supplement_title, description, manual_actresses, operator_review, source_url, play_count, fav_count, updated_at
             FROM product_meta
             WHERE content_id = :cid
             LIMIT 1'
        );
        $stmt->bindValue(':cid', $cid);
        $stmt->execute();
        $row = $stmt->fetch();
        return is_array($row) ? $row : null;
    }

    /**
     * @param array<int,string> $cids
     * @return array<string,array<string,mixed>>
     */
    public function findByCids(array $cids): array
    {
        $normalized = [];
        foreach ($cids as $cid) {
            if (!is_string($cid)) {
                continue;
            }
            $cid = trim($cid);
            if ($cid === '') {
                continue;
            }
            $normalized[$cid] = true;
        }
        if (count($normalized) === 0) {
            return [];
        }

        $placeholders = [];
        $params = [];
        $idx = 0;
        foreach (array_keys($normalized) as $cid) {
            $ph = ':cid_' . $idx;
            $placeholders[] = $ph;
            $params[$ph] = $cid;
            $idx++;
        }

        $stmt = $this->pdo->prepare(
            'SELECT content_id, supplement_title, description, manual_actresses, operator_review, source_url, play_count, fav_count, updated_at
             FROM product_meta
             WHERE content_id IN (' . implode(', ', $placeholders) . ')'
        );
        foreach ($params as $ph => $cid) {
            $stmt->bindValue($ph, $cid);
        }
        $stmt->execute();
        $rows = $stmt->fetchAll();

        $result = [];
        if (!is_array($rows)) {
            return $result;
        }
        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;
            }
            $result[$cid] = $row;
        }

        return $result;
    }

    /**
     * @param array<int,array<string,mixed>> $rows
     */
    public function upsertMany(array $rows): int
    {
        if (count($rows) === 0) {
            return 0;
        }
        $stmt = $this->pdo->prepare(
            'INSERT INTO product_meta (content_id, supplement_title, description, manual_actresses, operator_review, source_url, play_count, fav_count, updated_at)
             VALUES (:content_id, :supplement_title, :description, :manual_actresses, :operator_review, :source_url, :play_count, :fav_count, :updated_at)
             ON CONFLICT(content_id) DO UPDATE SET
               supplement_title = excluded.supplement_title,
               description = excluded.description,
               manual_actresses = excluded.manual_actresses,
               operator_review = excluded.operator_review,
               source_url = excluded.source_url,
               play_count = excluded.play_count,
               fav_count = excluded.fav_count,
               updated_at = excluded.updated_at'
        );

        $count = 0;
        $now = date('c');
        $this->pdo->beginTransaction();
        try {
            foreach ($rows as $row) {
                $cid = $this->toString($row['content_id'] ?? null);
                if ($cid === null) {
                    continue;
                }
                $stmt->execute([
                    ':content_id' => $cid,
                    ':supplement_title' => $this->toString($row['supplement_title'] ?? null),
                    ':description' => $this->toString($row['description'] ?? null),
                    ':manual_actresses' => $this->toString($row['manual_actresses'] ?? null),
                    ':operator_review' => $this->toString($row['operator_review'] ?? null),
                    ':source_url' => $this->toString($row['source_url'] ?? null),
                    ':play_count' => $this->toIntOrNull($row['play_count'] ?? null),
                    ':fav_count' => $this->toIntOrNull($row['fav_count'] ?? null),
                    ':updated_at' => $now,
                ]);
                $count++;
            }
            $this->pdo->commit();
        } catch (Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
        return $count;
    }

    public function upsertOne(
        string $contentId,
        ?string $supplementTitle,
        ?string $description,
        ?string $manualActresses = null,
        ?string $sourceUrl = null,
        ?int $playCount = null,
        ?int $favCount = null,
        ?string $operatorReview = null
    ): void {
        $cid = trim($contentId);
        if ($cid === '') {
            return;
        }
        $stmt = $this->pdo->prepare(
            'INSERT INTO product_meta (content_id, supplement_title, description, manual_actresses, operator_review, source_url, play_count, fav_count, updated_at)
             VALUES (:content_id, :supplement_title, :description, :manual_actresses, :operator_review, :source_url, :play_count, :fav_count, :updated_at)
             ON CONFLICT(content_id) DO UPDATE SET
               supplement_title = excluded.supplement_title,
               description = excluded.description,
               manual_actresses = excluded.manual_actresses,
               operator_review = excluded.operator_review,
               source_url = excluded.source_url,
               play_count = excluded.play_count,
               fav_count = excluded.fav_count,
               updated_at = excluded.updated_at'
        );
        $stmt->execute([
            ':content_id' => $cid,
            ':supplement_title' => $this->toString($supplementTitle),
            ':description' => $this->toString($description),
            ':manual_actresses' => $this->toString($manualActresses),
            ':operator_review' => $this->toString($operatorReview),
            ':source_url' => $this->toString($sourceUrl),
            ':play_count' => $playCount,
            ':fav_count' => $favCount,
            ':updated_at' => date('c'),
        ]);
    }

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

    private function toIntOrNull($value): ?int
    {
        if ($value === null || $value === '') {
            return null;
        }
        if (is_int($value)) {
            return $value;
        }
        if (is_string($value)) {
            $value = trim($value);
            if ($value === '') {
                return null;
            }
            if (!preg_match('/^-?\d+$/', $value)) {
                return null;
            }
            return (int) $value;
        }
        if (is_numeric($value)) {
            return (int) $value;
        }
        return null;
    }

    private function ensureColumnExists(string $table, string $column, string $type): void
    {
        $stmt = $this->pdo->query('PRAGMA table_info(' . $table . ')');
        $rows = $stmt !== false ? $stmt->fetchAll() : [];
        if (!is_array($rows)) {
            $rows = [];
        }
        foreach ($rows as $row) {
            if (is_array($row) && isset($row['name']) && is_string($row['name']) && $row['name'] === $column) {
                return;
            }
        }
        $this->pdo->exec('ALTER TABLE ' . $table . ' ADD COLUMN ' . $column . ' ' . $type);
    }
}