<?php

declare(strict_types=1);

namespace Infrastructure\Persistence;

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

use Domain\Entity\EntityTaxonomyMapping;
use Domain\Repository\EntityTaxonomyRepositoryInterface;
use PDO;

final class EntityTaxonomyRepository implements EntityTaxonomyRepositoryInterface
{
    private PDO $db;

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

    /**
     * {@inheritDoc}
     */
    public function findByEntityId(int $entityId): array
    {
        $stmt = $this->db->prepare(
            'SELECT * FROM entity_taxonomy_mapping WHERE entity_id = :entity_id ORDER BY relevance DESC'
        );
        $stmt->execute(['entity_id' => $entityId]);

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

        return $mappings;
    }

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

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

    /**
     * {@inheritDoc}
     */
    public function save(EntityTaxonomyMapping $mapping): int
    {
        if ($mapping->getId() !== null) {
            // Update
            $stmt = $this->db->prepare(
                'UPDATE entity_taxonomy_mapping
                 SET entity_id = :entity_id,
                     taxonomy_term_id = :term_id,
                     relevance = :relevance,
                     validated = :validated,
                     updated_at = NOW()
                 WHERE id = :id'
            );
            $stmt->execute([
                'id' => $mapping->getId(),
                'entity_id' => $mapping->getEntityId(),
                'term_id' => $mapping->getTaxonomyTermId(),
                'relevance' => $mapping->getRelevance()->value(),
                'validated' => $mapping->isValidated() ? 1 : 0,
            ]);

            return $mapping->getId();
        }

        // Insert
        $stmt = $this->db->prepare(
            'INSERT INTO entity_taxonomy_mapping (entity_id, taxonomy_term_id, relevance, validated, created_at)
             VALUES (:entity_id, :term_id, :relevance, :validated, :created_at)'
        );
        $stmt->execute([
            'entity_id' => $mapping->getEntityId(),
            'term_id' => $mapping->getTaxonomyTermId(),
            'relevance' => $mapping->getRelevance()->value(),
            'validated' => $mapping->isValidated() ? 1 : 0,
            '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 entity_taxonomy_mapping WHERE id = :id');

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

    /**
     * {@inheritDoc}
     */
    public function deleteByEntityId(int $entityId): int
    {
        $stmt = $this->db->prepare('DELETE FROM entity_taxonomy_mapping WHERE entity_id = :entity_id');
        $stmt->execute(['entity_id' => $entityId]);

        return $stmt->rowCount();
    }

    /**
     * {@inheritDoc}
     */
    public function getUnvalidatedMappings(int $limit = 100): array
    {
        $stmt = $this->db->prepare(
            'SELECT * FROM entity_taxonomy_mapping
             WHERE validated = 0
             ORDER BY relevance DESC
             LIMIT :limit'
        );
        $stmt->bindValue('limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

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

        return $mappings;
    }

    /**
     * {@inheritDoc}
     */
    public function markAsValidated(int $id): bool
    {
        $stmt = $this->db->prepare(
            'UPDATE entity_taxonomy_mapping SET validated = 1, updated_at = NOW() WHERE id = :id'
        );

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

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

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

    /**
     * {@inheritDoc}
     */
    public function getValidationStats(): array
    {
        $result = $this->db->query(
            'SELECT
                COUNT(*) as total,
                SUM(CASE WHEN validated = 1 THEN 1 ELSE 0 END) as validated,
                SUM(CASE WHEN validated = 0 THEN 1 ELSE 0 END) as pending
             FROM entity_taxonomy_mapping'
        )->fetch(PDO::FETCH_ASSOC);

        return $result !== false ? [
            'total' => (int) $result['total'],
            'validated' => (int) $result['validated'],
            'pending' => (int) $result['pending'],
        ] : [
            'total' => 0,
            'validated' => 0,
            'pending' => 0,
        ];
    }
}