<?php

declare(strict_types=1);

namespace App\Repository;

use App\Entity\AnneeScolaire;
use App\Entity\CategorieDepense;
use App\Entity\Depense;
use App\Entity\Ecole;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;

/** @extends ServiceEntityRepository<Depense> */
final class DepenseRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Depense::class);
    }

    /** @return list<Depense> */
    public function searchForEcole(Ecole $ecole, array $filters = []): array
    {
        $qb = $this->createQueryBuilder('d')
            ->leftJoin('d.categorie', 'c')
            ->addSelect('c')
            ->andWhere('d.ecole = :ecole')
            ->setParameter('ecole', $ecole)
            ->orderBy('d.dateDepense', 'DESC')
            ->addOrderBy('d.id', 'DESC');

        if (!empty($filters['q'])) {
            $qb->andWhere('LOWER(d.description) LIKE :q OR LOWER(d.beneficiaire) LIKE :q OR LOWER(d.reference) LIKE :q')
                ->setParameter('q', '%'.mb_strtolower(trim((string) $filters['q'])).'%');
        }

        if (!empty($filters['statut'])) {
            $qb->andWhere('d.statut = :statut')->setParameter('statut', $filters['statut']);
        }

        if (($filters['categorie'] ?? null) instanceof CategorieDepense) {
            $qb->andWhere('d.categorie = :categorie')->setParameter('categorie', $filters['categorie']);
        }

        if (($filters['anneeScolaire'] ?? null) instanceof AnneeScolaire) {
            $qb->andWhere('d.anneeScolaire = :annee')->setParameter('annee', $filters['anneeScolaire']);
        }

        if (($filters['dateFrom'] ?? null) instanceof \DateTimeInterface) {
            $qb->andWhere('d.dateDepense >= :dateFrom')->setParameter('dateFrom', $filters['dateFrom']);
        }

        if (($filters['dateTo'] ?? null) instanceof \DateTimeInterface) {
            $qb->andWhere('d.dateDepense <= :dateTo')->setParameter('dateTo', $filters['dateTo']);
        }

        return $qb->getQuery()->getResult();
    }

    /** @return list<Depense> */
    public function findByEcoleAndYear(Ecole $ecole, ?AnneeScolaire $anneeScolaire): array
    {
        $qb = $this->createQueryBuilder('d')
            ->andWhere('d.ecole = :ecole')
            ->setParameter('ecole', $ecole)
            ->orderBy('d.dateDepense', 'DESC')
            ->addOrderBy('d.id', 'DESC');

        $anneeScolaire
            ? $qb->andWhere('d.anneeScolaire = :annee')->setParameter('annee', $anneeScolaire)
            : $qb->andWhere('d.anneeScolaire IS NULL');

        return $qb->getQuery()->getResult();
    }

    /** @return list<Depense> */
    public function findRecentByEcole(Ecole $ecole, int $limit = 10): array
    {
        return $this->createQueryBuilder('d')
            ->andWhere('d.ecole = :ecole')
            ->setParameter('ecole', $ecole)
            ->orderBy('d.dateDepense', 'DESC')
            ->addOrderBy('d.id', 'DESC')
            ->setMaxResults($limit)
            ->getQuery()
            ->getResult();
    }

    public function sumValidatedForEcole(Ecole $ecole): float
    {
        return $this->sumValidated($ecole, null, null);
    }

    public function sumValidatedForCurrentMonth(Ecole $ecole): float
    {
        return $this->sumValidated(
            $ecole,
            new \DateTimeImmutable('first day of this month 00:00:00'),
            new \DateTimeImmutable('last day of this month 23:59:59'),
        );
    }

    public function countForEcole(Ecole $ecole, ?string $statut = null): int
    {
        $qb = $this->createQueryBuilder('d')
            ->select('COUNT(d.id)')
            ->andWhere('d.ecole = :ecole')
            ->setParameter('ecole', $ecole);

        if ($statut !== null) {
            $qb->andWhere('d.statut = :statut')->setParameter('statut', $statut);
        }

        return (int) $qb->getQuery()->getSingleScalarResult();
    }

    private function sumValidated(Ecole $ecole, ?\DateTimeInterface $start, ?\DateTimeInterface $end): float
    {
        $qb = $this->createQueryBuilder('d')
            ->select('COALESCE(SUM(d.montant), 0)')
            ->andWhere('d.ecole = :ecole')
            ->andWhere('d.statut = :statut')
            ->setParameter('ecole', $ecole)
            ->setParameter('statut', Depense::STATUT_VALIDEE);

        if ($start instanceof \DateTimeInterface && $end instanceof \DateTimeInterface) {
            $qb->andWhere('d.dateDepense BETWEEN :start AND :end')
                ->setParameter('start', $start)
                ->setParameter('end', $end);
        }

        return (float) ($qb->getQuery()->getSingleScalarResult() ?? 0);
    }
}
