<?php

namespace App\Repository;

use App\Entity\Secteur;
use App\Entity\SectorisationVersion;
use App\Entity\EquipementFamilleContrat;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\OptimisticLockException;
use Doctrine\ORM\ORMException;
use Doctrine\Persistence\ManagerRegistry;
use Jsor\Doctrine\PostGIS\Functions\ST_GeomFromGeoJSON;
use Jsor\Doctrine\PostGIS\Functions\ST_Point;
use Doctrine\ORM\EntityManagerInterface;

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


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

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

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

    public function getSecteursFromCoordinates($longitude, $latitude) {
        return $this->createQueryBuilder('s')
            ->andWhere('ST_Contains(ST_GeomFromGeoJSON(s.coordonnees), ST_Point(:longitude, :latitude,4326)) = true')
            ->setParameter('longitude', $longitude)
            ->setParameter('latitude', $latitude)
            ->getQuery()->getResult();
    }

    public function getSecteurs(SectorisationVersion $version){
        return $this->createQueryBuilder('s')
            ->andWhere('s.sectorisation_version = :version')
            ->setParameter('version', $version)
            ->getQuery()->getResult();
    }

    public function getNbEquipement(){
        $qb = $this->createQueryBuilder('s');
        $qb
            ->select('count(se.equipement) as nb, efc.id as id_famille, s.id as id_secteur')
            ->innerJoin('s.secteurEquipements', 'se')
            ->innerJoin('se.equipement', 'e')
            ->innerJoin('e.equipement_famille_contrat', 'efc')
            ->groupBy('s.id', 'efc.id')
        ;
        $query = $qb->getQuery();
        return $query->getArrayResult();
    }

}