<?php

declare(strict_types=1);

namespace App\Infrastructure\Category;

use App\Domain\Category\Category;
use App\Domain\Reputation\DecayFunction;
use Doctrine\DBAL\Connection;

/**
 * DBAL gateway for the `categories` table.
 *
 * Ingest looks up by `slug`; the PairScorer needs the decay function and
 * parameter while computing a score; M10 adds CRUD on top so admins can
 * manage categories from the UI.
 */
final class CategoryRepository
{
    public function __construct(private readonly Connection $connection)
    {
    }

    public function findActiveBySlug(string $slug): ?Category
    {
        /** @var array<string, mixed>|false $row */
        $row = $this->connection->fetchAssociative(
            'SELECT id, slug, name, description, decay_function, decay_param, is_active '
            . 'FROM categories WHERE slug = :slug AND is_active = :active',
            ['slug' => $slug, 'active' => 1]
        );

        return $row === false ? null : self::hydrate($row);
    }

    public function findBySlug(string $slug): ?Category
    {
        /** @var array<string, mixed>|false $row */
        $row = $this->connection->fetchAssociative(
            'SELECT id, slug, name, description, decay_function, decay_param, is_active '
            . 'FROM categories WHERE slug = :slug',
            ['slug' => $slug]
        );

        return $row === false ? null : self::hydrate($row);
    }

    public function findById(int $id): ?Category
    {
        /** @var array<string, mixed>|false $row */
        $row = $this->connection->fetchAssociative(
            'SELECT id, slug, name, description, decay_function, decay_param, is_active '
            . 'FROM categories WHERE id = :id',
            ['id' => $id]
        );

        return $row === false ? null : self::hydrate($row);
    }

    /**
     * @return list<Category>
     */
    public function listAll(): array
    {
        /** @var list<array<string, mixed>> $rows */
        $rows = $this->connection->fetchAllAssociative(
            'SELECT id, slug, name, description, decay_function, decay_param, is_active '
            . 'FROM categories ORDER BY id ASC'
        );

        return array_map(static fn (array $r): Category => self::hydrate($r), $rows);
    }

    public function create(
        string $slug,
        string $name,
        ?string $description,
        DecayFunction $decayFunction,
        float $decayParam,
        bool $isActive,
    ): int {
        $this->connection->insert('categories', [
            'slug' => $slug,
            'name' => $name,
            'description' => $description,
            'decay_function' => $decayFunction->value,
            'decay_param' => number_format($decayParam, 4, '.', ''),
            'is_active' => $isActive ? 1 : 0,
        ]);

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

    /**
     * @param array<string, mixed> $fields
     */
    public function update(int $id, array $fields): void
    {
        if ($fields === []) {
            return;
        }
        $this->connection->update('categories', $fields, ['id' => $id]);
    }

    public function delete(int $id): void
    {
        $this->connection->delete('categories', ['id' => $id]);
    }

    /**
     * Returns the count of `policy_category_thresholds` rows referencing
     * this category. Non-zero ⇒ the category is "in use" from the
     * policy side.
     */
    public function policyReferenceCount(int $categoryId): int
    {
        return (int) $this->connection->fetchOne(
            'SELECT COUNT(*) FROM policy_category_thresholds WHERE category_id = :id',
            ['id' => $categoryId]
        );
    }

    /**
     * Returns the count of `reports` rows referencing this category.
     * Non-zero ⇒ the category is "in use" from the historical-reports
     * side.
     */
    public function reportReferenceCount(int $categoryId): int
    {
        return (int) $this->connection->fetchOne(
            'SELECT COUNT(*) FROM reports WHERE category_id = :id',
            ['id' => $categoryId]
        );
    }

    /**
     * @param array<string, mixed> $row
     */
    private static function hydrate(array $row): Category
    {
        $function = DecayFunction::tryFrom((string) $row['decay_function']) ?? DecayFunction::Exponential;

        return new Category(
            id: (int) $row['id'],
            slug: (string) $row['slug'],
            name: (string) $row['name'],
            description: $row['description'] !== null ? (string) $row['description'] : null,
            decayFunction: $function,
            decayParam: (float) $row['decay_param'],
            isActive: (bool) $row['is_active'],
        );
    }
}
