<?php

declare(strict_types=1);

namespace Infrastructure\Persistence;

// @responsibility: Persistenz für Content-Konfiguration (Profile, Contracts, Strukturen)

use Domain\Repository\ContentConfigRepositoryInterface;

class ContentConfigRepository implements ContentConfigRepositoryInterface
{
    private \PDO $pdo;

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

    public function findById(int $id): ?array
    {
        $stmt = $this->pdo->prepare(
            'SELECT c.*, p.name as parent_name
             FROM content_config c
             LEFT JOIN content_config p ON c.parent_id = p.id
             WHERE c.id = ?'
        );
        $stmt->execute([$id]);
        $result = $stmt->fetch(\PDO::FETCH_ASSOC);

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

    public function findByIdAndType(int $id, string $type): ?array
    {
        $stmt = $this->pdo->prepare(
            "SELECT id, name, slug, content, type, status
             FROM content_config
             WHERE id = ? AND type = ? AND status = 'active'"
        );
        $stmt->execute([$id, $type]);
        $result = $stmt->fetch(\PDO::FETCH_ASSOC);

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

    public function findAll(?string $typeFilter = null, ?string $statusFilter = null): array
    {
        $sql = 'SELECT c.*, p.name as parent_name
                FROM content_config c
                LEFT JOIN content_config p ON c.parent_id = p.id
                WHERE 1=1';
        $params = [];

        if ($typeFilter !== null && $typeFilter !== '') {
            $sql .= ' AND c.type = ?';
            $params[] = $typeFilter;
        }

        if ($statusFilter !== null && $statusFilter !== '') {
            $sql .= ' AND c.status = ?';
            $params[] = $statusFilter;
        }

        $sql .= ' ORDER BY c.type, c.name';

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);

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

    public function findAllByType(string $type): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT id, name, slug, content, type, status
             FROM content_config
             WHERE type = ? AND status = 'active'
             ORDER BY name"
        );
        $stmt->execute([$type]);

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

    public function create(
        string $type,
        string $name,
        string $slug,
        ?string $description,
        string $content,
        string $version,
        string $status,
        ?int $parentId,
        ?int $promptId = null,
        int $sortOrder = 0
    ): int {
        $stmt = $this->pdo->prepare(
            'INSERT INTO content_config (type, name, slug, description, content, version, status, parent_id, prompt_id, sort_order)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
        );
        $stmt->execute([$type, $name, $slug, $description, $content, $version, $status, $parentId, $promptId, $sortOrder]);

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

    public function update(
        int $id,
        string $name,
        string $slug,
        ?string $description,
        string $content,
        string $version,
        string $status,
        ?int $parentId
    ): void {
        $stmt = $this->pdo->prepare(
            'UPDATE content_config
             SET name = ?, slug = ?, description = ?, content = ?, version = ?, status = ?, parent_id = ?
             WHERE id = ?'
        );
        $stmt->execute([$name, $slug, $description, $content, $version, $status, $parentId, $id]);
    }

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

    public function saveHistory(int $configId, string $content, string $version, string $changedBy, string $changeDescription): void
    {
        $stmt = $this->pdo->prepare(
            'INSERT INTO content_config_history (config_id, content, version, changed_by, change_description)
             VALUES (?, ?, ?, ?, ?)'
        );
        $stmt->execute([$configId, $content, $version, $changedBy, $changeDescription]);
    }

    public function deleteHistory(int $configId): void
    {
        $stmt = $this->pdo->prepare('DELETE FROM content_config_history WHERE config_id = ?');
        $stmt->execute([$configId]);
    }

    public function countChildren(int $parentId): int
    {
        $stmt = $this->pdo->prepare('SELECT COUNT(*) FROM content_config WHERE parent_id = ?');
        $stmt->execute([$parentId]);

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

    public function getChildren(int $parentId): array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM content_config WHERE parent_id = ? ORDER BY name');
        $stmt->execute([$parentId]);

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

    public function getHistory(int $configId, int $limit = 10): array
    {
        $stmt = $this->pdo->prepare(
            'SELECT * FROM content_config_history WHERE config_id = ? ORDER BY created_at DESC LIMIT ?'
        );
        $stmt->bindValue(1, $configId, \PDO::PARAM_INT);
        $stmt->bindValue(2, $limit, \PDO::PARAM_INT);
        $stmt->execute();

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

    public function getParentOptions(?int $excludeId = null): array
    {
        $stmt = $this->pdo->query(
            'SELECT id, type, name FROM content_config WHERE type IN ("contract", "organization") ORDER BY type, name'
        );
        $parents = [];
        foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) as $row) {
            if ($excludeId !== null && (int) $row['id'] === $excludeId) {
                continue;
            }
            $parents[] = $row;
        }

        return $parents;
    }

    public function getStatistics(): array
    {
        $stats = ['total' => 0, 'by_type' => [], 'by_status' => []];

        $stmt = $this->pdo->query('SELECT type, COUNT(*) as cnt FROM content_config GROUP BY type');
        foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) as $row) {
            $stats['by_type'][$row['type']] = (int) $row['cnt'];
            $stats['total'] += (int) $row['cnt'];
        }

        $stmt = $this->pdo->query('SELECT status, COUNT(*) as cnt FROM content_config GROUP BY status');
        foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) as $row) {
            $stats['by_status'][$row['status']] = (int) $row['cnt'];
        }

        return $stats;
    }

    public function getAuthorProfiles(): array
    {
        return $this->findAllByType('author_profile');
    }

    public function getSystemPrompts(): array
    {
        return $this->findAllByType('system_prompt');
    }

    public function getAuthorProfile(int $id): ?array
    {
        return $this->findByIdAndType($id, 'author_profile');
    }

    public function getSystemPrompt(int $id): ?array
    {
        return $this->findByIdAndType($id, 'system_prompt');
    }

    public function getStructures(): array
    {
        return $this->findAllByType('structure');
    }

    public function getStructure(int $id): ?array
    {
        return $this->findByIdAndType($id, 'structure');
    }
}