<?php

namespace App\Repository;

use App\Entity\Commentaire;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;

/**
 * @method Commentaire|null find($id, $lockMode = null, $lockVersion = null)
 * @method Commentaire|null findOneBy(array $criteria, array $orderBy = null)
 * @method Commentaire[]    findAll()
 * @method Commentaire[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class CommentaireRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Commentaire::class);
    }

    public function findLast ($limit = 5)
    {
        return $qb = $this->createQueryBuilder('c')
            ->innerJoin('c.film', 'flm')->addSelect('flm')
            ->innerJoin('c.user', 'usr')->addSelect('usr')
            ->setMaxResults($limit)
            ->orderBy('c.id', 'DESC')
            ->getQuery()
            ->getResult();
    }

    public function countCommentairesParMois(): array
    {
        return $this->createQueryBuilder('c')
            ->select("DATE_FORMAT(c.dateSubmitted, '%Y-%m') as month, COUNT(c.id) as entryCount")
            ->where('c.dateSubmitted IS NOT NULL')
            ->groupBy('month')
            ->orderBy('month', 'ASC')
            ->getQuery()
            ->getResult()
        ;
    }

    public function findFirstDateAdded()
    {
        // Construire une requête pour obtenir la première date d'ajout
        return $this->createQueryBuilder('c')
            ->select('c.dateSubmitted')
            ->orderBy('c.dateSubmitted', 'ASC')  // Trier par date croissante
            ->setMaxResults(1)  // Limiter à 1 résultat (la première date)
            ->getQuery()
            ->getOneOrNullResult();  // Récupérer le premier résultat ou null
    }

}