<?php

declare(strict_types=1);

namespace App\Administration\Infrastructure\Persistence\Doctrine;

use App\Administration\Domain\Exception\ClasseNotFoundException;
use App\Administration\Domain\Model\SchoolClass\AcademicYearId;
use App\Administration\Domain\Model\SchoolClass\ClassId;
use App\Administration\Domain\Model\SchoolClass\ClassName;
use App\Administration\Domain\Model\SchoolClass\ClassStatus;
use App\Administration\Domain\Model\SchoolClass\SchoolClass;
use App\Administration\Domain\Model\SchoolClass\SchoolId;
use App\Administration\Domain\Model\SchoolClass\SchoolLevel;
use App\Administration\Domain\Repository\ClassRepository;
use App\Shared\Domain\Tenant\TenantId;
use DateTimeImmutable;
use Doctrine\DBAL\Connection;
use Override;

final readonly class DoctrineClassRepository implements ClassRepository
{
    public function __construct(
        private Connection $connection,
    ) {
    }

    #[Override]
    public function save(SchoolClass $class): void
    {
        $this->connection->executeStatement(
            'INSERT INTO school_classes (id, tenant_id, school_id, academic_year_id, name, level, capacity, status, description, created_at, updated_at, deleted_at)
             VALUES (:id, :tenant_id, :school_id, :academic_year_id, :name, :level, :capacity, :status, :description, :created_at, :updated_at, :deleted_at)
             ON CONFLICT (id) DO UPDATE SET
                 name = EXCLUDED.name,
                 level = EXCLUDED.level,
                 capacity = EXCLUDED.capacity,
                 status = EXCLUDED.status,
                 description = EXCLUDED.description,
                 updated_at = EXCLUDED.updated_at,
                 deleted_at = EXCLUDED.deleted_at',
            [
                'id' => (string) $class->id,
                'tenant_id' => (string) $class->tenantId,
                'school_id' => (string) $class->schoolId,
                'academic_year_id' => (string) $class->academicYearId,
                'name' => (string) $class->name,
                'level' => $class->level?->value,
                'capacity' => $class->capacity,
                'status' => $class->status->value,
                'description' => $class->description,
                'created_at' => $class->createdAt->format(DateTimeImmutable::ATOM),
                'updated_at' => $class->updatedAt->format(DateTimeImmutable::ATOM),
                'deleted_at' => $class->deletedAt?->format(DateTimeImmutable::ATOM),
            ],
        );
    }

    #[Override]
    public function get(ClassId $id): SchoolClass
    {
        $class = $this->findById($id);

        if ($class === null) {
            throw ClasseNotFoundException::withId($id);
        }

        return $class;
    }

    #[Override]
    public function findById(ClassId $id): ?SchoolClass
    {
        $row = $this->connection->fetchAssociative(
            'SELECT * FROM school_classes WHERE id = :id',
            ['id' => (string) $id],
        );

        if ($row === false) {
            return null;
        }

        return $this->hydrate($row);
    }

    #[Override]
    public function findByName(
        ClassName $name,
        TenantId $tenantId,
        AcademicYearId $academicYearId,
    ): ?SchoolClass {
        $row = $this->connection->fetchAssociative(
            'SELECT * FROM school_classes
             WHERE tenant_id = :tenant_id
             AND academic_year_id = :academic_year_id
             AND name = :name
             AND deleted_at IS NULL',
            [
                'tenant_id' => (string) $tenantId,
                'academic_year_id' => (string) $academicYearId,
                'name' => (string) $name,
            ],
        );

        if ($row === false) {
            return null;
        }

        return $this->hydrate($row);
    }

    #[Override]
    public function findActiveByTenantAndYear(
        TenantId $tenantId,
        AcademicYearId $academicYearId,
    ): array {
        $rows = $this->connection->fetchAllAssociative(
            'SELECT * FROM school_classes
             WHERE tenant_id = :tenant_id
             AND academic_year_id = :academic_year_id
             AND status = :status
             ORDER BY name ASC',
            [
                'tenant_id' => (string) $tenantId,
                'academic_year_id' => (string) $academicYearId,
                'status' => ClassStatus::ACTIVE->value,
            ],
        );

        return array_map(fn ($row) => $this->hydrate($row), $rows);
    }

    #[Override]
    public function delete(ClassId $id): void
    {
        $this->connection->delete('school_classes', ['id' => (string) $id]);
    }

    /**
     * @param array<string, mixed> $row
     */
    private function hydrate(array $row): SchoolClass
    {
        /** @var string $id */
        $id = $row['id'];
        /** @var string $tenantId */
        $tenantId = $row['tenant_id'];
        /** @var string $schoolId */
        $schoolId = $row['school_id'];
        /** @var string $academicYearId */
        $academicYearId = $row['academic_year_id'];
        /** @var string $name */
        $name = $row['name'];
        /** @var string|null $level */
        $level = $row['level'];
        /** @var int|string|null $capacity */
        $capacity = $row['capacity'];
        /** @var string $status */
        $status = $row['status'];
        /** @var string|null $description */
        $description = $row['description'];
        /** @var string $createdAt */
        $createdAt = $row['created_at'];
        /** @var string $updatedAt */
        $updatedAt = $row['updated_at'];
        /** @var string|null $deletedAt */
        $deletedAt = $row['deleted_at'];

        return SchoolClass::reconstitute(
            id: ClassId::fromString($id),
            tenantId: TenantId::fromString($tenantId),
            schoolId: SchoolId::fromString($schoolId),
            academicYearId: AcademicYearId::fromString($academicYearId),
            name: new ClassName($name),
            level: $level !== null ? SchoolLevel::from($level) : null,
            capacity: $capacity !== null ? (int) $capacity : null,
            status: ClassStatus::from($status),
            description: $description,
            createdAt: new DateTimeImmutable($createdAt),
            updatedAt: new DateTimeImmutable($updatedAt),
            deletedAt: $deletedAt !== null ? new DateTimeImmutable($deletedAt) : null,
        );
    }
}
