<?php

namespace App\Repository;

use App\Entity\ContratPlage;
use App\Entity\ContratTarif;
use App\Entity\Equipement;
use App\Entity\EquipementTypePuissance;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;

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

    // /**
    //  * @return ContratTarif[] Returns an array of ContratTarif objects
    //  */

    public function deleteByPeriode($periode)
    {
        $qb = $this->createQueryBuilder('ct')
            ->delete();

        if (isset($periode['date_debut'])) {
            $qb->andWhere('ct.date_debut = :date_debut')->setParameter(':date_debut', $periode['date_debut']);
        }
        if (isset($periode['date_fin'])) {
            $qb->andWhere('ct.date_fin = :date_fin')->setParameter(':date_fin', $periode['date_fin']);
        }
        return $qb->getQuery()->getResult();
    }

    public function updateLastPeriode($periode) {
        return $this->createQueryBuilder('ct')
            ->update()
            ->set('ct.date_fin', 'null')
            ->andWhere('ct.date_fin = :date_debut')
            ->setParameter('date_debut', $periode['date_debut_1_day'])
            ->getQuery()
            ->getSingleScalarResult()
            ;
    }

    public function addDateFin($date_fin)
    {
        return $this->createQueryBuilder('ct')
            ->update()
            ->set('ct.date_fin', ':date_fin')
            ->setParameter('date_fin', $date_fin)
            ->where('ct.date_fin is null')
            ->getQuery()
            ->getSingleScalarResult()
            ;
    }

    public function majField($field, $old_value, $new_value) {
        return $this->createQueryBuilder('ct')
            ->update()
            ->set('ct.' . $field, ':new_value')
            ->setParameter('new_value', $new_value)
            ->where('ct.' . $field . ' = :old_value')
            ->setParameter('old_value', $old_value)
            ->getQuery()
            ->getSingleScalarResult()
            ;
    }

    public function findForOneEquipement(EquipementTypePuissance $equipementTypePuissance, ContratPlage $contratPlage)
    {
        $queryBuilder = $this->createQueryBuilder('ct');
        $expr = $queryBuilder->expr();
        $request = $queryBuilder
            ->where($expr->in('ct.equipement_type_puissance',  ':equipementTypePuissance'))
            ->andWhere('ct.date_fin IS NULL')
            ->andWhere("((ct.contrat_type IN ('3','4') AND ct.contrat_plage IS NULL) OR (ct.contrat_type NOT IN ('3','4') AND ct.contrat_plage = :contratPlage))")
            ->setParameter('equipementTypePuissance', $equipementTypePuissance)
            ->setParameter('contratPlage', $contratPlage)
            ->getQuery();

        return $request->getResult() ;
    }

    public function getOrderedContratTarif ($periode = null) {
        $qb = $this->createQueryBuilder('ct')
            ->join('ct.equipement_type_puissance', 'etp')
            ->join('etp.equipement_type', 'et')
            ->join('etp.equipement_puissance', 'ep')
            ->join('et.equipementTypeFamilles', 'etf')
            ->join('etf.equipement_famille', 'ef');
        if ($periode) {
            $qb->where('ct.date_debut = :date_debut')->setParameter('date_debut', $periode);
        }

       return $qb->orderBy('ct.date_debut', 'DESC') // Ensuite, trie par date_debut DESC
            ->addOrderBy('ef.code', 'ASC')  // Trie par l'ID de l'EquipementTypeFamille
            ->addOrderBy('et.type', 'ASC')  // Trie par l'ID de l'EquipementTypeFamille
            ->addOrderBy('ep.libelle', 'ASC')  // Trie par l'ID de l'EquipementTypeFamille
            ->getQuery()
            ->getResult();
    }

}