<?php

declare(strict_types=1);

namespace App\Administration\Infrastructure\Persistence\Doctrine;

use App\Administration\Domain\Exception\AffectationDejaExistanteException;
use App\Administration\Domain\Exception\AffectationNotFoundException;
use App\Administration\Domain\Model\SchoolClass\AcademicYearId;
use App\Administration\Domain\Model\SchoolClass\ClassId;
use App\Administration\Domain\Model\Subject\SubjectId;
use App\Administration\Domain\Model\TeacherAssignment\AssignmentStatus;
use App\Administration\Domain\Model\TeacherAssignment\TeacherAssignment;
use App\Administration\Domain\Model\TeacherAssignment\TeacherAssignmentId;
use App\Administration\Domain\Model\User\UserId;
use App\Administration\Domain\Repository\TeacherAssignmentRepository;
use App\Shared\Domain\Tenant\TenantId;
use DateTimeImmutable;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Exception\UniqueConstraintViolationException;
use Override;

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

    #[Override]
    public function save(TeacherAssignment $assignment): void
    {
        try {
            $this->connection->executeStatement(
                'INSERT INTO teacher_assignments (id, tenant_id, teacher_id, school_class_id, subject_id, academic_year_id, start_date, end_date, status, created_at, updated_at)
                 VALUES (:id, :tenant_id, :teacher_id, :school_class_id, :subject_id, :academic_year_id, :start_date, :end_date, :status, :created_at, :updated_at)
                 ON CONFLICT (id) DO UPDATE SET
                     start_date = EXCLUDED.start_date,
                     end_date = EXCLUDED.end_date,
                     status = EXCLUDED.status,
                     updated_at = EXCLUDED.updated_at',
                [
                    'id' => (string) $assignment->id,
                    'tenant_id' => (string) $assignment->tenantId,
                    'teacher_id' => (string) $assignment->teacherId,
                    'school_class_id' => (string) $assignment->classId,
                    'subject_id' => (string) $assignment->subjectId,
                    'academic_year_id' => (string) $assignment->academicYearId,
                    'start_date' => $assignment->startDate->format(DateTimeImmutable::ATOM),
                    'end_date' => $assignment->endDate?->format(DateTimeImmutable::ATOM),
                    'status' => $assignment->status->value,
                    'created_at' => $assignment->createdAt->format(DateTimeImmutable::ATOM),
                    'updated_at' => $assignment->updatedAt->format(DateTimeImmutable::ATOM),
                ],
            );
        } catch (UniqueConstraintViolationException) {
            throw AffectationDejaExistanteException::pourTriple(
                $assignment->teacherId,
                $assignment->classId,
                $assignment->subjectId,
            );
        }
    }

    #[Override]
    public function get(TeacherAssignmentId $id, TenantId $tenantId): TeacherAssignment
    {
        $assignment = $this->findById($id, $tenantId);

        if ($assignment === null) {
            throw AffectationNotFoundException::withId($id);
        }

        return $assignment;
    }

    #[Override]
    public function findById(TeacherAssignmentId $id, TenantId $tenantId): ?TeacherAssignment
    {
        $row = $this->connection->fetchAssociative(
            'SELECT * FROM teacher_assignments WHERE id = :id AND tenant_id = :tenant_id',
            ['id' => (string) $id, 'tenant_id' => (string) $tenantId],
        );

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

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

    #[Override]
    public function findByTeacherClassSubject(
        UserId $teacherId,
        ClassId $classId,
        SubjectId $subjectId,
        AcademicYearId $academicYearId,
        TenantId $tenantId,
    ): ?TeacherAssignment {
        $row = $this->connection->fetchAssociative(
            'SELECT * FROM teacher_assignments
             WHERE tenant_id = :tenant_id
             AND teacher_id = :teacher_id
             AND school_class_id = :school_class_id
             AND subject_id = :subject_id
             AND academic_year_id = :academic_year_id
             AND status = :status',
            [
                'tenant_id' => (string) $tenantId,
                'teacher_id' => (string) $teacherId,
                'school_class_id' => (string) $classId,
                'subject_id' => (string) $subjectId,
                'academic_year_id' => (string) $academicYearId,
                'status' => AssignmentStatus::ACTIVE->value,
            ],
        );

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

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

    #[Override]
    public function findRemovedByTeacherClassSubject(
        UserId $teacherId,
        ClassId $classId,
        SubjectId $subjectId,
        AcademicYearId $academicYearId,
        TenantId $tenantId,
    ): ?TeacherAssignment {
        $row = $this->connection->fetchAssociative(
            'SELECT * FROM teacher_assignments
             WHERE tenant_id = :tenant_id
             AND teacher_id = :teacher_id
             AND school_class_id = :school_class_id
             AND subject_id = :subject_id
             AND academic_year_id = :academic_year_id
             AND status = :status',
            [
                'tenant_id' => (string) $tenantId,
                'teacher_id' => (string) $teacherId,
                'school_class_id' => (string) $classId,
                'subject_id' => (string) $subjectId,
                'academic_year_id' => (string) $academicYearId,
                'status' => AssignmentStatus::REMOVED->value,
            ],
        );

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

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

    #[Override]
    public function findActiveByTeacher(
        UserId $teacherId,
        TenantId $tenantId,
    ): array {
        $rows = $this->connection->fetchAllAssociative(
            'SELECT * FROM teacher_assignments
             WHERE teacher_id = :teacher_id
             AND tenant_id = :tenant_id
             AND status = :status
             ORDER BY created_at ASC',
            [
                'teacher_id' => (string) $teacherId,
                'tenant_id' => (string) $tenantId,
                'status' => AssignmentStatus::ACTIVE->value,
            ],
        );

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

    #[Override]
    public function findActiveByClass(
        ClassId $classId,
        TenantId $tenantId,
    ): array {
        $rows = $this->connection->fetchAllAssociative(
            'SELECT * FROM teacher_assignments
             WHERE school_class_id = :school_class_id
             AND tenant_id = :tenant_id
             AND status = :status
             ORDER BY created_at ASC',
            [
                'school_class_id' => (string) $classId,
                'tenant_id' => (string) $tenantId,
                'status' => AssignmentStatus::ACTIVE->value,
            ],
        );

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

    #[Override]
    public function findAllActiveByTenant(TenantId $tenantId): array
    {
        $rows = $this->connection->fetchAllAssociative(
            'SELECT * FROM teacher_assignments
             WHERE tenant_id = :tenant_id
             AND status = :status
             ORDER BY created_at ASC',
            [
                'tenant_id' => (string) $tenantId,
                'status' => AssignmentStatus::ACTIVE->value,
            ],
        );

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

    /**
     * @param array<string, mixed> $row
     */
    private function hydrate(array $row): TeacherAssignment
    {
        /** @var string $id */
        $id = $row['id'];
        /** @var string $tenantId */
        $tenantId = $row['tenant_id'];
        /** @var string $teacherId */
        $teacherId = $row['teacher_id'];
        /** @var string $classId */
        $classId = $row['school_class_id'];
        /** @var string $subjectId */
        $subjectId = $row['subject_id'];
        /** @var string $academicYearId */
        $academicYearId = $row['academic_year_id'];
        /** @var string $startDate */
        $startDate = $row['start_date'];
        /** @var string|null $endDate */
        $endDate = $row['end_date'];
        /** @var string $status */
        $status = $row['status'];
        /** @var string $createdAt */
        $createdAt = $row['created_at'];
        /** @var string $updatedAt */
        $updatedAt = $row['updated_at'];

        return TeacherAssignment::reconstitute(
            id: TeacherAssignmentId::fromString($id),
            tenantId: TenantId::fromString($tenantId),
            teacherId: UserId::fromString($teacherId),
            classId: ClassId::fromString($classId),
            subjectId: SubjectId::fromString($subjectId),
            academicYearId: AcademicYearId::fromString($academicYearId),
            startDate: new DateTimeImmutable($startDate),
            endDate: $endDate !== null ? new DateTimeImmutable($endDate) : null,
            status: AssignmentStatus::from($status),
            createdAt: new DateTimeImmutable($createdAt),
            updatedAt: new DateTimeImmutable($updatedAt),
        );
    }
}
