<?php

declare(strict_types=1);

namespace Infrastructure\Persistence;

// @responsibility: CRUD-Operationen für Entitäten (Personen, Orte, Konzepte etc.)

use Domain\Constants;
use Domain\Repository\EntityRepositoryInterface;
use PDO;

final class EntityRepository implements EntityRepositoryInterface
{
    private PDO $db;

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

    /**
     * {@inheritDoc}
     */
    public function findFiltered(string $type = '', string $search = '', int $limit = Constants::DEFAULT_LIMIT): array
    {
        $sql = 'SELECT e.*,
                       COUNT(DISTINCT ce.chunk_id) as chunk_count,
                       COUNT(DISTINCT er.id) as relation_count
                FROM entities e
                LEFT JOIN chunk_entities ce ON e.id = ce.entity_id
                LEFT JOIN entity_relations er ON e.id = er.source_entity_id OR e.id = er.target_entity_id
                WHERE 1=1';

        $params = [];

        if ($type !== '') {
            $sql .= ' AND e.type = :type';
            $params['type'] = $type;
        }

        if ($search !== '') {
            $sql .= ' AND (e.name LIKE :search OR e.description LIKE :search2)';
            $params['search'] = '%' . $search . '%';
            $params['search2'] = '%' . $search . '%';
        }

        $sql .= ' GROUP BY e.id ORDER BY chunk_count DESC, e.name LIMIT ' . $limit;

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

        return $stmt->fetchAll();
    }

    /**
     * {@inheritDoc}
     */
    public function getStats(): array
    {
        return $this->db->query(
            'SELECT type, COUNT(*) as count FROM entities GROUP BY type'
        )->fetchAll();
    }

    /**
     * {@inheritDoc}
     */
    public function find(int $id): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM entities WHERE id = :id');
        $stmt->execute(['id' => $id]);
        $result = $stmt->fetch();

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

    /**
     * {@inheritDoc}
     */
    public function findSynonyms(int $entityId): array
    {
        $stmt = $this->db->prepare('SELECT * FROM entity_synonyms WHERE entity_id = :id');
        $stmt->execute(['id' => $entityId]);

        return $stmt->fetchAll();
    }

    /**
     * {@inheritDoc}
     */
    public function getOutgoingRelations(int $entityId): array
    {
        $stmt = $this->db->prepare(
            'SELECT er.*, e.name as target_name, e.type as target_type
             FROM entity_relations er
             JOIN entities e ON er.target_entity_id = e.id
             WHERE er.source_entity_id = :id
             ORDER BY er.strength DESC'
        );
        $stmt->execute(['id' => $entityId]);

        return $stmt->fetchAll();
    }

    /**
     * {@inheritDoc}
     */
    public function getIncomingRelations(int $entityId): array
    {
        $stmt = $this->db->prepare(
            'SELECT er.*, e.name as source_name, e.type as source_type
             FROM entity_relations er
             JOIN entities e ON er.source_entity_id = e.id
             WHERE er.target_entity_id = :id
             ORDER BY er.strength DESC'
        );
        $stmt->execute(['id' => $entityId]);

        return $stmt->fetchAll();
    }

    /**
     * {@inheritDoc}
     */
    public function findChunks(int $entityId, int $limit = 20): array
    {
        $stmt = $this->db->prepare(
            'SELECT c.id, c.content, c.token_count, d.filename, ce.relevance_score
             FROM chunk_entities ce
             JOIN chunks c ON ce.chunk_id = c.id
             JOIN documents d ON c.document_id = d.id
             WHERE ce.entity_id = :id
             ORDER BY ce.relevance_score DESC
             LIMIT :limit'
        );
        $stmt->bindValue(':id', $entityId, PDO::PARAM_INT);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    /**
     * {@inheritDoc}
     */
    public function findClassifications(int $entityId): array
    {
        $stmt = $this->db->prepare(
            'SELECT oc.*, ec.confidence
             FROM entity_classifications ec
             JOIN ontology_classes oc ON ec.ontology_class_id = oc.id
             WHERE ec.entity_id = :id'
        );
        $stmt->execute(['id' => $entityId]);

        return $stmt->fetchAll();
    }

    /**
     * {@inheritDoc}
     */
    public function create(string $name, string $type, ?string $description = null): int
    {
        $stmt = $this->db->prepare(
            'INSERT INTO entities (name, canonical_name, type, description, created_at, updated_at)
             VALUES (:name, :canonical, :type, :description, NOW(), NOW())'
        );
        $stmt->execute([
            'name' => $name,
            'canonical' => strtolower(trim($name)),
            'type' => $type,
            'description' => $description,
        ]);

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

    /**
     * {@inheritDoc}
     */
    public function update(int $id, string $name, string $type, ?string $description = null): bool
    {
        $stmt = $this->db->prepare(
            'UPDATE entities SET name = :name, canonical_name = :canonical, type = :type,
             description = :description, updated_at = NOW() WHERE id = :id'
        );

        return $stmt->execute([
            'id' => $id,
            'name' => $name,
            'canonical' => strtolower(trim($name)),
            'type' => $type,
            'description' => $description,
        ]);
    }

    /**
     * {@inheritDoc}
     */
    public function delete(int $id): bool
    {
        // Check for relations
        $stmt = $this->db->prepare(
            'SELECT COUNT(*) FROM entity_relations
             WHERE source_entity_id = :id OR target_entity_id = :id2'
        );
        $stmt->execute(['id' => $id, 'id2' => $id]);

        if ((int) $stmt->fetchColumn() > 0) {
            return false; // Has relations, cannot delete
        }

        // Delete synonyms first
        $stmt = $this->db->prepare('DELETE FROM entity_synonyms WHERE entity_id = :id');
        $stmt->execute(['id' => $id]);

        // Delete chunk associations
        $stmt = $this->db->prepare('DELETE FROM chunk_entities WHERE entity_id = :id');
        $stmt->execute(['id' => $id]);

        // Delete entity
        $stmt = $this->db->prepare('DELETE FROM entities WHERE id = :id');

        return $stmt->execute(['id' => $id]);
    }

    /**
     * {@inheritDoc}
     */
    public function getTypes(): array
    {
        return ['person', 'organization', 'location', 'concept', 'method', 'tool', 'event', 'other'];
    }

    /**
     * {@inheritDoc}
     */
    public function findAllSimple(): array
    {
        return $this->db->query('SELECT id, name, type FROM entities ORDER BY name')->fetchAll();
    }

    /**
     * {@inheritDoc}
     */
    public function getKnowledgeSemantics(int $entityId): ?array
    {
        $stmt = $this->db->prepare(
            'SELECT semantic_role, functional_category, context_meaning, properties, model_used
             FROM entity_knowledge_semantics
             WHERE entity_id = :entity_id
             LIMIT 1'
        );
        $stmt->execute(['entity_id' => $entityId]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);

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