<?php

namespace App\Repository;

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

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

    public function getEquipementsForUpdatePrivileges($site, $equipementFamillesContrat) {
        $queryBuilder = $this->createQueryBuilder('e');
        $expr = $queryBuilder->expr();
        $queryBuilder
            ->andWhere('e.site = :site' )
            ->andWhere('e.actif = true')
            ->andWhere('e.privilege = false or e.privilege is null')
            ->andWhere($expr->in('e.equipement_famille_contrat',  ':equipementFamillesContrat'))
            ->setParameter('site', $site)
            ->setParameter('equipementFamillesContrat', $equipementFamillesContrat);

        return $queryBuilder->getQuery()->getResult();

    }

    public function findByAdresse($datas) {
        $queryBuilder = $this->createQueryBuilder('e');
        $queryBuilder
            ->innerJoin('e.adresse', 'ea')
            // ->andWhere('e.actif = true')
            ->andWhere('e.client_souscripteur <> :client')->setParameter('client', $datas['id_souscripteur_search'])
            ->andWhere('LOWER(ea.adresse) LIKE LOWER(:adresse)')->setParameter('adresse','%'.$datas['adresse_equipement_search'].'%');

        if (isset($datas['code_postal_equipement_search']) && !empty($datas['code_postal_equipement_search']))
            $queryBuilder->andWhere('ea.code_postal = :code_postal')->setParameter('code_postal',$datas['code_postal_equipement_search']);
        if (isset($datas['ville_equipement_search']) && !empty($datas['ville_equipement_search']))
            $queryBuilder->andWhere('LOWER(ea.ville) LIKE LOWER(:ville)')->setParameter('ville','%'.$datas['ville_equipement_search'].'%');

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

    public function getEquipementAServir($latitude = null, $longitude = null, $distanceMaxKm = null, $tri = null, $page = 1, $limit = 10, $familleIds = null){
        $queryBuilder = $this->createQueryBuilder('e');

        $queryBuilder
            ->select('e')
            ->innerJoin('e.adresse', 'a')
            ->where('e.a_servir = true');

        // Filtre par famille d'équipement (qualifications)
        if ($familleIds !== null) {
            if (empty($familleIds)) {
                // Si la liste est vide, on ne doit rien retourner
                $queryBuilder->andWhere('1 = 0');
            } else {
                $queryBuilder->andWhere('e.equipement_famille IN (:familleIds)')
                    ->setParameter('familleIds', $familleIds);
            }
        }
        // Calcul de la distance si les coordonnées sont fournies
        $hasCoords = $latitude !== null && $longitude !== null;
        $haversineFormula = null;

        if ($hasCoords) {
            $latitude = (float) $latitude;
            $longitude = (float) $longitude;

            $cosExpression = "
                (cos(radians(:latitude)) *
                 cos(radians(a.latitude)) *
                 cos(radians(a.longitude) - radians(:longitude)))
                +
                (sin(radians(:latitude)) *
                 sin(radians(a.latitude)))
            ";

            // Clamp version compatible DQL
            $clampedExpression = "
                CASE
                    WHEN $cosExpression > 1 THEN 1
                    WHEN $cosExpression < -1 THEN -1
                    ELSE $cosExpression
                END
            ";

            $haversineFormula = "(6371 * acos($clampedExpression))";

            $queryBuilder->setParameter('latitude', $latitude);
            $queryBuilder->setParameter('longitude', $longitude);
            $queryBuilder->addSelect($haversineFormula . ' AS distance');

            // Filtre par distance si demandé
            if ($distanceMaxKm !== null) {
                $queryBuilder
                    ->andWhere('a.latitude IS NOT NULL')
                    ->andWhere('a.longitude IS NOT NULL')
                    ->andWhere($haversineFormula . ' <= :distanceMax')
                    ->setParameter('distanceMax', (float) $distanceMaxKm);
            }
        }

        // Gestion du tri
        if ($tri === 'distance' && $hasCoords) {
            $queryBuilder->orderBy('distance', 'ASC');
        } elseif ($tri === 'fin_contrat') {
            $queryBuilder
                ->leftJoin('e.contrats', 'c', 'WITH', 'c.dernier_contrat = true')
                ->orderBy('c.date_fin_contrat', 'ASC');
        }

        $offset = ($page - 1) * $limit;
        $query = $queryBuilder->getQuery()
            ->setFirstResult($offset)
            ->setMaxResults($limit);

        $results = $query->getResult();

        if ($hasCoords) {
            $equipements = [];
            foreach ($results as $result) {
                $equipement = $result[0];
                $equipement->setDistance($result['distance']);
                $equipements[] = $equipement;
            }
            return $equipements;
        }
        return $results;
    }

    public function countEquipementAServir($latitude = null, $longitude = null, $distanceMaxKm = null, $familleIds = null)
    {
        $queryBuilder = $this->createQueryBuilder('e');
        $queryBuilder
            ->select('count(e.id)')
            ->innerJoin('e.adresse', 'a')
            ->where('e.a_servir = true');

        // Filtre par famille d'équipement (qualifications)
        if ($familleIds !== null) {
            if (empty($familleIds)) {
                return 0;
            } else {
                $queryBuilder->andWhere('e.equipement_famille IN (:familleIds)')
                    ->setParameter('familleIds', $familleIds);
            }
        }

        if ($latitude !== null && $longitude !== null && $distanceMaxKm !== null) {
            $latitude = (float) $latitude;
            $longitude = (float) $longitude;

            $cosExpression = "
                (cos(radians(:latitude)) *
                 cos(radians(a.latitude)) *
                 cos(radians(a.longitude) - radians(:longitude)))
                +
                (sin(radians(:latitude)) *
                 sin(radians(a.latitude)))
            ";

            // Clamp version compatible DQL
            $clampedExpression = "
                CASE
                    WHEN $cosExpression > 1 THEN 1
                    WHEN $cosExpression < -1 THEN -1
                    ELSE $cosExpression
                END
            ";

            $haversineFormula = "(6371 * acos($clampedExpression))";

            $queryBuilder
                ->andWhere('a.latitude IS NOT NULL')
                ->andWhere('a.longitude IS NOT NULL')
                ->andWhere($haversineFormula . ' <= :distanceMax')
                ->setParameter('latitude', $latitude)
                ->setParameter('longitude', $longitude)
                ->setParameter('distanceMax', $distanceMaxKm);
        }

        return $queryBuilder->getQuery()->getSingleScalarResult();
    }
}