<?php

declare(strict_types=1);

namespace Infrastructure\Persistence;

// @responsibility: Persistenz für Prompt-Templates

class PromptsRepository
{
    private \PDO $pdo;

    public function __construct(\PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    public function findById(int $id): ?array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM prompts WHERE id = ?');
        $stmt->execute([$id]);
        $result = $stmt->fetch(\PDO::FETCH_ASSOC);

        return $result !== false ? $result : null;
    }

    public function findAll(): array
    {
        $stmt = $this->pdo->query('
            SELECT p.*,
                   (SELECT COUNT(*) FROM critics c WHERE c.prompt_id = p.id) as critic_count
            FROM prompts p
            ORDER BY p.name
        ');

        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    public function findActivePrompts(): array
    {
        $stmt = $this->pdo->query('
            SELECT id, name, version
            FROM prompts
            WHERE is_active = 1
            ORDER BY name
        ');

        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    public function create(string $name, string $version, string $content, int $isActive): int
    {
        $stmt = $this->pdo->prepare('
            INSERT INTO prompts (name, version, content, is_active)
            VALUES (?, ?, ?, ?)
        ');
        $stmt->execute([$name, $version, $content, $isActive]);

        return (int) $this->pdo->lastInsertId();
    }

    public function update(int $id, string $name, string $version, string $content, int $isActive): void
    {
        $stmt = $this->pdo->prepare('
            UPDATE prompts
            SET name = ?, version = ?, content = ?, is_active = ?
            WHERE id = ?
        ');
        $stmt->execute([$name, $version, $content, $isActive, $id]);
    }

    public function delete(int $id): void
    {
        $stmt = $this->pdo->prepare('DELETE FROM prompts WHERE id = ?');
        $stmt->execute([$id]);
    }

    public function duplicate(int $id, string $newName, string $newVersion): int
    {
        $original = $this->findById($id);
        if ($original === null) {
            throw new \RuntimeException("Prompt {$id} not found");
        }

        $stmt = $this->pdo->prepare('
            INSERT INTO prompts (name, version, content, is_active)
            VALUES (?, ?, ?, 0)
        ');
        $stmt->execute([$newName, $newVersion, $original['content']]);

        return (int) $this->pdo->lastInsertId();
    }

    public function findLinkedCritics(int $promptId): array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM critics WHERE prompt_id = ? ORDER BY name');
        $stmt->execute([$promptId]);

        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }

    public function countLinkedCritics(int $promptId): int
    {
        $stmt = $this->pdo->prepare('SELECT COUNT(*) FROM critics WHERE prompt_id = ?');
        $stmt->execute([$promptId]);

        return (int) $stmt->fetchColumn();
    }

    public function getStatistics(): array
    {
        $stats = ['total' => 0, 'active' => 0, 'inactive' => 0, 'linked_to_critics' => 0];

        $stmt = $this->pdo->query('SELECT COUNT(*) as cnt, SUM(is_active) as active FROM prompts');
        $row = $stmt->fetch(\PDO::FETCH_ASSOC);
        $stats['total'] = (int) $row['cnt'];
        $stats['active'] = (int) $row['active'];
        $stats['inactive'] = $stats['total'] - $stats['active'];

        $stmt = $this->pdo->query('SELECT COUNT(DISTINCT prompt_id) FROM critics WHERE prompt_id IS NOT NULL');
        $stats['linked_to_critics'] = (int) $stmt->fetchColumn();

        return $stats;
    }
}