<?php

namespace App\Repository;

use App\Entity\Contrat;
use DateTime;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\OptimisticLockException;
use Doctrine\ORM\ORMException;
use Doctrine\Persistence\ManagerRegistry;

/**
 * @method Contrat|null find($id, $lockMode = null, $lockVersion = null)
 * @method Contrat|null findOneBy(array $criteria, array $orderBy = null)
 * @method Contrat[]    findAll()
 * @method Contrat[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class ContratRepository extends ServiceEntityRepository
{
    private $em;
    public function __construct(ManagerRegistry $registry, EntityManagerInterface $em)
    {
        parent::__construct($registry, Contrat::class);
        $this->em = $em;
    }

    /**
     * @throws ORMException
     * @throws OptimisticLockException
     */
    public function add(Contrat $entity, bool $flush = true): void
    {
        $this->_em->persist($entity);
        if ($flush) {
            $this->_em->flush();
        }
    }

    /**
     * @throws ORMException
     * @throws OptimisticLockException
     */
    public function remove(Contrat $entity, bool $flush = true): void
    {
        $this->_em->remove($entity);
        if ($flush) {
            $this->_em->flush();
        }
    }

    public function getContratsByGroupe($groupe) {
        $qb = $this->createQueryBuilder('c')
            ->select()
            ->join('c.equipement', 'e', 'WITH', 'e.actif = true' )
            ->andWhere('c.date_fin_contrat >= :now')
            ->andWhere('c.groupe = :groupe')
            ->setParameter('now', new DateTime())
            ->setParameter('groupe', $groupe);

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

    public function getAllGroupeIdNom()
    {
        $qb = $this->createQueryBuilder('s')->select('s.groupe as value,s.groupe as text')->distinct();
        return $qb->getQuery()->getArrayResult();
    }

    public function searchContrats($datas, $groupe) {
        $qb = $this->createQueryBuilder('c')
            ->select()
            ->innerJoin('c.client', 'cc')
            ->innerJoin('c.equipement', 'e', 'WITH', 'e.actif = true')
            ->innerJoin('e.adresse', 'a')
            ->andWhere('c.date_fin_contrat >= :now')
            ->setParameter('now', new DateTime());

        if ($groupe) {
            $qb->andWhere('c.groupe <> :groupe_actuel')->setParameter('groupe_actuel', $groupe);
        }

        if (!empty($datas['nom']))
            $qb->andWhere('LOWER(cc.nom) LIKE LOWER(:nom)')->setParameter('nom','%'.$datas['nom'].'%');
        if (!empty($datas['prenom']))
            $qb->andWhere('LOWER(cc.prenom) LIKE LOWER(:prenom)')->setParameter('prenom','%'.$datas['prenom'].'%');
        if (!empty($datas['equipement']))
            $qb->andWhere('LOWER(e.num_serie_principal) LIKE LOWER(:equipement)')->setParameter('equipement','%'.$datas['equipement'].'%');
        if (!empty($datas['adresse']))
            $qb->andWhere('LOWER(a.adresse) LIKE LOWER(:adresse)')->setParameter('adresse','%'.$datas['adresse'].'%');
        if (!empty($datas['code_postal']))
            $qb->andWhere('LOWER(a.code_postal) LIKE LOWER(:code_postal)')->setParameter('code_postal','%'.$datas['code_postal'].'%');
        if (!empty($datas['ville']))
            $qb->andWhere('LOWER(a.ville) LIKE LOWER(:ville)')->setParameter('ville','%'.$datas['ville'].'%');
        if (!empty($datas['groupe']))
            $qb->andWhere('c.groupe =:groupe')->setParameter('groupe',$datas['groupe']);


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

    public function getNewGroupe() {
        return $this->createQueryBuilder('c')
            ->select('MAX(c.groupe)')
            ->getQuery()->getSingleColumnResult();
    }

    public function getGapGroupe() {
        $conn = $this->em
            ->getConnection();

        $sql = "WITH all_idgroups AS (
          SELECT generate_series(1, (SELECT MAX(groupe) FROM sav.contrat)) AS idgroupe
        )
        SELECT all_idgroups.idgroupe
        from all_idgroups
        LEFT JOIN sav.contrat ON all_idgroups.idgroupe = sav.contrat.groupe
        WHERE sav.contrat.groupe IS NULL
        LIMIT 1";
        $stmt = $conn->prepare($sql);

        return $stmt->executeQuery()->fetchAllAssociative();
    }

    public function disableOldContrats()
    {
        $qb = $this->createQueryBuilder('c')
            ->update()
            ->set('c.dernier_contrat', ':dernier_contrat')
            ->where('c.date_fin_contrat < :today')
            ->setParameter('today', new \DateTimeImmutable())
            ->setParameter('dernier_contrat', false)
        ;

        $qb->getQuery()->execute();
    }
}