<?php

declare(strict_types=1);

namespace Infrastructure\Persistence;

// @responsibility: CRUD-Operationen für Chunk-Taxonomie-Mappings

use Domain\Constants;
use Domain\Entity\ChunkTaxonomyMapping;
use Domain\Repository\ChunkTaxonomyRepositoryInterface;
use PDO;

final class ChunkTaxonomyRepository implements ChunkTaxonomyRepositoryInterface
{
    private PDO $db;

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

    /**
     * {@inheritDoc}
     */
    public function findByChunkId(int $chunkId): array
    {
        $stmt = $this->db->prepare(
            'SELECT * FROM chunk_taxonomy WHERE chunk_id = :chunk_id ORDER BY confidence DESC'
        );
        $stmt->execute(['chunk_id' => $chunkId]);

        $mappings = [];
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $mappings[] = ChunkTaxonomyMapping::fromArray($row);
        }

        return $mappings;
    }

    /**
     * {@inheritDoc}
     */
    public function findByTaxonomyTermId(int $termId): array
    {
        $stmt = $this->db->prepare(
            'SELECT chunk_id, confidence, source
             FROM chunk_taxonomy
             WHERE taxonomy_term_id = :term_id
             ORDER BY confidence DESC'
        );
        $stmt->execute(['term_id' => $termId]);

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

    /**
     * {@inheritDoc}
     */
    public function save(ChunkTaxonomyMapping $mapping): int
    {
        if ($mapping->getId() !== null) {
            // Update
            $stmt = $this->db->prepare(
                'UPDATE chunk_taxonomy
                 SET chunk_id = :chunk_id,
                     taxonomy_term_id = :term_id,
                     confidence = :confidence,
                     source = :source
                 WHERE id = :id'
            );
            $stmt->execute([
                'id' => $mapping->getId(),
                'chunk_id' => $mapping->getChunkId(),
                'term_id' => $mapping->getTaxonomyTermId(),
                'confidence' => $mapping->getConfidence()->value(),
                'source' => $mapping->getSource()->value,
            ]);

            return $mapping->getId();
        }

        // Insert
        $stmt = $this->db->prepare(
            'INSERT INTO chunk_taxonomy (chunk_id, taxonomy_term_id, confidence, source, created_at)
             VALUES (:chunk_id, :term_id, :confidence, :source, :created_at)'
        );
        $stmt->execute([
            'chunk_id' => $mapping->getChunkId(),
            'term_id' => $mapping->getTaxonomyTermId(),
            'confidence' => $mapping->getConfidence()->value(),
            'source' => $mapping->getSource()->value,
            'created_at' => $mapping->getCreatedAt()->format('Y-m-d H:i:s'),
        ]);

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

    /**
     * {@inheritDoc}
     */
    public function delete(int $id): bool
    {
        $stmt = $this->db->prepare('DELETE FROM chunk_taxonomy WHERE id = :id');

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

    /**
     * {@inheritDoc}
     */
    public function deleteByChunkId(int $chunkId): int
    {
        $stmt = $this->db->prepare('DELETE FROM chunk_taxonomy WHERE chunk_id = :chunk_id');
        $stmt->execute(['chunk_id' => $chunkId]);

        return $stmt->rowCount();
    }

    /**
     * {@inheritDoc}
     */
    public function getUnmappedChunks(int $limit = 100): array
    {
        $stmt = $this->db->prepare(
            'SELECT c.id, c.document_id, SUBSTRING(c.content, 1, 500) as content
             FROM chunks c
             LEFT JOIN chunk_taxonomy ct ON c.id = ct.chunk_id
             WHERE ct.id IS NULL
               AND c.status = "embedded"
             LIMIT :limit'
        );
        $stmt->bindValue('limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

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

    /**
     * {@inheritDoc}
     */
    public function exists(int $chunkId, int $taxonomyTermId): bool
    {
        $stmt = $this->db->prepare(
            'SELECT COUNT(*) FROM chunk_taxonomy
             WHERE chunk_id = :chunk_id AND taxonomy_term_id = :term_id'
        );
        $stmt->execute([
            'chunk_id' => $chunkId,
            'term_id' => $taxonomyTermId,
        ]);

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

    /**
     * {@inheritDoc}
     */
    public function countByTaxonomyTerm(): array
    {
        $stmt = $this->db->query(
            'SELECT taxonomy_term_id, COUNT(*) as count
             FROM chunk_taxonomy
             GROUP BY taxonomy_term_id'
        );

        $counts = [];
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $counts[(int) $row['taxonomy_term_id']] = (int) $row['count'];
        }

        return $counts;
    }
}