<?php
declare(strict_types=1);

namespace App\Taxonomy;

use App\Support\TranslatableException;
use InvalidArgumentException;
use PDO;
use RuntimeException;
use Throwable;

final class TaxonomyService
{
    public function __construct(private readonly PDO $pdo) {}

    public function findCategoryBySlug(string $slug): ?array
    {
        return $this->findOne('categories', $slug);
    }

    public function findTagBySlug(string $slug): ?array
    {
        return $this->findOne('tags', $slug);
    }

    /** @return list<array<string,mixed>> */
    public function tags(): array
    {
        $statement = $this->pdo->query('SELECT id,name,slug,content_locale FROM tags ORDER BY name ASC,id ASC');
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }

    /** @return list<array<string,mixed>> */
    public function questionsFor(string $type, int $targetId, int $limit = 50): array
    {
        $limit = max(1, min(100, $limit));
        if ($targetId <= 0) return [];
        $type = strtolower(trim($type));
        if (!in_array($type, ['category', 'tag'], true)) throw new TranslatableException('taxonomy.type_invalid');

        $where = $type === 'category'
            ? 'q.category_id = :target_id'
            : 'EXISTS (SELECT 1 FROM question_tags qt WHERE qt.question_id=q.id AND qt.tag_id=:target_id)';
        $statement = $this->pdo->prepare(
            'SELECT q.id,q.slug,q.title,q.body,q.votes_count,q.answers_count,q.comments_count,q.created_at,'
            . " COALESCE(c.name,'') AS category_name,COALESCE(c.slug,'') AS category_slug,"
            . " CASE WHEN q.is_anonymous=1 OR u.id IS NULL THEN '' ELSE COALESCE(u.name,'') END AS author_name,"
            . " CASE WHEN q.is_anonymous=1 OR u.id IS NULL THEN '' ELSE COALESCE(u.username,'') END AS author_username"
            . ' FROM questions q LEFT JOIN categories c ON c.id=q.category_id LEFT JOIN users u ON u.id=q.user_id'
            . " WHERE q.status='published' AND {$where}"
            . ' ORDER BY COALESCE(q.published_at,q.created_at) DESC,q.id DESC LIMIT ' . $limit
        );
        $statement->execute(['target_id' => $targetId]);
        return $statement->fetchAll(PDO::FETCH_ASSOC);
    }

    /** @return array{items:list<array<string,mixed>>,total:int,page:int,per_page:int,total_pages:int} */
    public function topicsFor(int $categoryId, int $page, int $perPage): array
    {
        $page = max(1, $page);
        $perPage = max(1, min(100, $perPage));
        if ($categoryId <= 0) {
            return ['items' => [], 'total' => 0, 'page' => 1, 'per_page' => $perPage, 'total_pages' => 1];
        }

        $params = ['category_id' => $categoryId];
        $count = $this->pdo->prepare("SELECT COUNT(*) FROM topics t WHERE t.status='published' AND t.category_id=:category_id");
        $count->execute($params);
        $total = (int) $count->fetchColumn();
        $totalPages = max(1, (int) ceil($total / $perPage));
        $page = min($page, $totalPages);
        $offset = ($page - 1) * $perPage;
        $query = $this->pdo->prepare(
            "SELECT t.*,u.name AS author_name,u.username AS author_username,u.avatar_url AS author_avatar_url,c.name AS category_name,c.slug AS category_slug
             FROM topics t LEFT JOIN users u ON u.id=t.user_id LEFT JOIN categories c ON c.id=t.category_id
             WHERE t.status='published' AND t.category_id=:category_id
             ORDER BY t.is_pinned DESC,(t.kind='announcement') DESC,t.last_activity_at DESC,t.id DESC
             LIMIT {$perPage} OFFSET {$offset}"
        );
        $query->execute($params);
        return ['items' => $query->fetchAll(PDO::FETCH_ASSOC), 'total' => $total, 'page' => $page, 'per_page' => $perPage, 'total_pages' => $totalPages];
    }

    /** @param list<int|string> $tagIds */
    public function attachTags(int $questionId, array $tagIds, int $maximum = 5): void
    {
        if ($questionId <= 0) throw new TranslatableException('taxonomy.question_invalid');
        $ids = array_values(array_unique(array_filter(array_map('intval', $tagIds), static fn(int $id): bool => $id > 0)));
        if (count($ids) > $maximum) throw new \App\Support\TranslatableException('questions.tags_max', ['count' => $maximum], 'Too many tags selected.');
        if ($ids === []) return;

        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        $check = $this->pdo->prepare("SELECT id FROM tags WHERE id IN ({$placeholders})");
        $check->execute($ids);
        $valid = array_fill_keys(array_map('intval', $check->fetchAll(PDO::FETCH_COLUMN)), true);
        if (count($valid) !== count($ids)) throw new TranslatableException('taxonomy.tags_not_found');

        $this->pdo->beginTransaction();
        try {
            $insert = $this->pdo->prepare('INSERT IGNORE INTO question_tags (question_id,tag_id,created_at) VALUES (:question_id,:tag_id,NOW())');
            foreach ($ids as $id) $insert->execute(['question_id' => $questionId, 'tag_id' => $id]);
            $this->pdo->commit();
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) $this->pdo->rollBack();
            throw $e;
        }
    }

    private function findOne(string $table, string $slug): ?array
    {
        $slug = trim($slug);
        if ($slug === '') return null;
        $statement = $this->pdo->prepare("SELECT * FROM {$table} WHERE slug=:slug LIMIT 1");
        $statement->execute(['slug' => $slug]);
        $row = $statement->fetch(PDO::FETCH_ASSOC);
        return is_array($row) ? $row : null;
    }
}
