<?php

namespace App\Repository;

use App\Entity\CategoriePlanification;
use App\Entity\Intervention;
use App\Entity\Utilisateur;
use App\Entity\Equipement;
use App\Entity\UtilisateurProfil;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\OptimisticLockException;
use Doctrine\ORM\ORMException;
use Doctrine\Persistence\ManagerRegistry;

/**
 * @method Intervention|null find($id, $lockMode = null, $lockVersion = null)
 * @method Intervention|null findOneBy(array $criteria, array $orderBy = null)
 * @method Intervention[]    findAll()
 * @method Intervention[]    findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
 */
class InterventionRepository extends ServiceEntityRepository
{
    private $em;

    private $selectIntervention = "SELECT 
    i.id,
    c.numero_client as c_numero_client,
    CONCAT(c.nom, ' ', c.prenom) as nom_client,
    CONCAT(a.adresse, ' ', a.code_postal, ' ', a.ville) as adresse_client,
	i.id as i_id,
	p.date as p_date,
    p.heure_debut as p_heure_debut,
    p.heure_fin as p_heure_fin,
	agentint.nom as agentint_nom,
	agentplan.nom as agentplan_nom,
	CASE
		WHEN i.avis_passage= true and i.avis_passage_mail = true THEN 'mail'::text
		WHEN i.avis_passage= true and i.avis_passage_mail = false THEN 'courrier'::text
		ELSE 'non'::text
	END AS i_avis_passage,
	moment.libelle as moment_libelle,
	i.observations as i_observations,
	ima.libelle as ima_libelle,
    im.libelle as im_libelle,
    tpinit.code_court as tpinit_code_court,
    tpreal.code_court as tpreal_code_court
   FROM sav.intervention i
    INNER JOIN sav.planning p on p.id = i.idplanning
	INNER JOIN sav.moment_passage moment on moment.id = p.idmoment_passage
	LEFT JOIN sav.equipement e on e.id = i.idequipement and e.actif = true
    LEFT JOIN sav.adresse a on a.id = e.idadresse
	LEFT JOIN sav.client c on c.id = e.idclient_souscripteur
	LEFT JOIN sav.type_planification tpinit on tpinit.id = p.idtype_planification_init
	LEFT JOIN sav.utilisateur agentint on agentint.id = p.idutilisateur_agent
    LEFT JOIN sav.utilisateur agentplan on agentplan.id = p.idutilisateur_planning
	LEFT JOIN sav.intervention_annulation ia on ia.idintervention = i.id
	LEFT JOIN sav.intervention_motif_absence ima on ima.id = ia.idintervention_motif_absence
    LEFT JOIN sav.intervention_motif im on im.id = i.idintervention_motif
    LEFT JOIN sav.type_planification tpreal on tpreal.id = i.idtype_planification
    WHERE 1=1
  ";

    private $orderByIntervention = "
    ORDER BY p.date ASC,p.heure_debut ASC, i.id
    ";

    private $selectCountInterventionLight = "
    SELECT count(*) FROM
  (
  SELECT 
       i.id
        FROM sav.intervention i
  ";

    private $selectCountIntervention= "
    SELECT count(*) FROM
  (
  SELECT 
       i.id
    FROM sav.intervention i
    INNER JOIN sav.planning p on p.id = i.idplanning
	INNER JOIN sav.moment_passage moment on moment.id = p.idmoment_passage
	LEFT JOIN sav.equipement e on e.id = i.idequipement
	LEFT JOIN sav.client c on c.id = e.idclient_souscripteur
	LEFT JOIN sav.type_planification tpinit on tpinit.id = p.idtype_planification_init
	LEFT JOIN sav.utilisateur agentint on agentint.id = p.idutilisateur_agent
    LEFT JOIN sav.utilisateur agentplan on agentplan.id = p.idutilisateur_planning
	LEFT JOIN sav.intervention_annulation ia on ia.idintervention = i.id
	LEFT JOIN sav.intervention_motif_absence ima on ima.id = ia.idintervention_motif_absence
    LEFT JOIN sav.intervention_motif im on im.id = i.idintervention_motif
    LEFT JOIN sav.type_planification tpreal on tpreal.id = i.idtype_planification
    where true

  ";

    private $tabSearchLeft =
        [
            "p.date" => "INNER JOIN sav.planning p ON p.id = id.idplanning"
        ];

    private $selectCountEnd = ") t;";

    private function hasDateFilter(?array $filters): bool
    {
        if (!$filters || empty($filters['AND'])) {
            return false;
        }
        foreach ($filters['AND'] as $filter) {
            $plain = $filter['filtersPlain'] ?? '';
            if (is_array($plain)) {
                $plain = implode(' ', $plain);
            }
            if (stripos($plain, 'p.date') !== false || stripos($plain, 'p_date') !== false) {
                return true;
            }
        }
        return false;
    }

    public function __construct(ManagerRegistry $registry, EntityManagerInterface $em)
    {
        parent::__construct($registry, Intervention::class);
        $this->em = $em;
    }

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

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

    public function getInterventionByAgent(Utilisateur $utilisateur)
    {
        //J et J+1
        $queryBuilder = $this->createQueryBuilder('i');
        $expr = $queryBuilder->expr();
        $dateJour = date('d/m/Y');
        $dateDemain = date('d/m/Y', strtotime("+1 day"));
        $request = $queryBuilder
            ->innerJoin('i.planning', 'p')
            ->where($expr->in('p.utilisateur_agent',  ':ids'))
            ->andWhere('p.date >= :dateJour')
            ->andWhere('p.date <= :dateDemain')
            ->orderBy('p.date', 'ASC')
            ->setParameter('ids', $utilisateur->getId())
            //->setParameter('dateJour', $dateJour)
            ->setParameter('dateJour', '16/09/2022')
            ->setParameter('dateDemain', '17/09/2022')
           // ->setParameter('dateDemain', $dateDemain)
           //->indexBy('p', 'p.date')
            ->getQuery();
        //var_dump($request->getSQL());

        return $request->getResult() ;
    }

    public function getInterventionByEquipement(Equipement $equipement)
    {
        $queryBuilder = $this->createQueryBuilder('i');
        $expr = $queryBuilder->expr();
        $request = $queryBuilder
            ->innerJoin('i.planning', 'p')
            ->where($expr->in('p.equipement',  ':ids'))
            ->andWhere($expr->in('p.statut',  '3'))
            ->orderBy('p.date', 'ASC')
            ->setParameter('ids', $equipement->getId())
            ->getQuery();

        return $request->getResult() ;
    }

    public function getLastInterventionByEquipement(Equipement $equipement)
    {
        $queryBuilder = $this->createQueryBuilder('i');
        $expr = $queryBuilder->expr();
        $request = $queryBuilder
            ->innerJoin('i.planning', 'p')
            ->where($expr->in('p.equipement',  ':ids'))
            ->andWhere($expr->in('p.statut',  '3'))
            ->orderBy('p.date', 'DESC')
            ->setParameter('ids', $equipement->getId())
            ->setMaxResults(1)
            ->getQuery();
        return $request->getOneOrNullResult() ;
    }

    public function getLastEntretien(Equipement $equipement)
    {
        $queryBuilder = $this->createQueryBuilder('i');
        $expr = $queryBuilder->expr();
        $request = $queryBuilder
            ->innerJoin('i.planning', 'p')
            ->where($expr->in('p.equipement',  ':ids'))
            ->andWhere($expr->in('p.statut',  '3'))
            ->andWhere($expr->in('i.entretien',  'true'))
            ->orderBy('p.date', 'DESC')
            ->setParameter('ids', $equipement->getId())
            ->setMaxResults(1)
            ->getQuery();
        return $request->getOneOrNullResult() ;
    }

    public function getInterventionsView($skip = null, $take = null, $filters = null, $sort = null,$sites = null) {
        $tabAddLeft=[];
        $conn = $this->em
            ->getConnection();

        $sql_contrat = '';
        $sql = $this->selectIntervention;

        if ($filters && $filters['AND']) {
            foreach ($filters['AND'] as $key => $filter) {
                foreach($this->tabSearchLeft as $keySearch => $left) {
                    if (stripos($filter['filtersPlain'], $keySearch) !== false) {
                        if (is_array($left)) {
                            foreach ($left as $v) {
                                if (!in_array($v,$tabAddLeft)) $tabAddLeft[] = $v;
                            }
                        }else {
                            if (!in_array($left,$tabAddLeft)) $tabAddLeft[] = $left;
                        }
                    }
                }

                if (stripos($filter['filtersPlain'],'i.avis_passage') !== false){
                    $filterValue = $filter['parameters'][0]['value'] ?? '';
                    switch($filterValue) {
                        case 'mail':
                            $sql .= " and (i.avis_passage = true and i.avis_passage_mail = true)";
                            break;
                        case 'courrier':
                            $sql .= " and (i.avis_passage = true and i.avis_passage_mail = false)";
                            break;
                        case 'non':
                            $sql .= " and (i.avis_passage = false or i.avis_passage is null)";
                            break;
                        default:
                            $sql .= " and (" .(str_replace('ea.adresse', "concat(ea.adresse, ' ', ea.adresse_complement)",$filter['filtersPlain'])) .')';
                            break;
                    }
                } else {
                    $sql .= " and (" .(str_replace('ea.adresse', "concat(ea.adresse, ' ', ea.adresse_complement)",$filter['filtersPlain'])) .')';
                }
            }
        }

        if (!$this->hasDateFilter($filters)) {
            $today = date('Y-m-d');
            $dayAfterTomorrow = date('Y-m-d', strtotime('+2 days'));
            $sql .= " and (p.date >= '$today') and (p.date < '$dayAfterTomorrow')";
        }

        $sql .= $sort ? " ORDER BY p.date " . $sort : $this->orderByIntervention;
        if ($take) {
            $sql .= " LIMIT $take OFFSET $skip";
        }

        $sql = str_replace('[replace_left_join]', implode(' ', $tabAddLeft), $sql );
        $sql = str_replace('[replace_contrat]', $sql_contrat, $sql );

        $stmt = $conn->prepare($sql);
        return $stmt->executeQuery()->fetchAllAssociative();
    }

    public function countInterventions($filters = null)
    {
        $sql_contrat = '';
        $tabAddLeft = [];
        $conn = $this->em
            ->getConnection();


        if ($filters && $filters['AND']) {
            $sql = $this->selectCountIntervention;
            foreach ($filters['AND'] as $key => $filter) {
                foreach($this->tabSearchLeft as $keySearch => $left) {
                    if (stripos($filter['filtersPlain'], $keySearch) !== false) {
                        if (is_array($left)) {
                            foreach ($left as $v) {
                                if (!in_array($v,$tabAddLeft)) $tabAddLeft[] = $v;
                            }
                        }else {
                            if (!in_array($left,$tabAddLeft)) $tabAddLeft[] = $left;
                        }
                    }
                }

                if (stripos($filter['filtersPlain'],'i.avis_passage') !== false){
                    $filterValue = $filter['parameters'][0]['value'] ?? '';
                    switch($filterValue) {
                        case 'mail':
                            $sql .= " and (i.avis_passage = true and i.avis_passage_mail = true)";
                            break;
                        case 'courrier':
                            $sql .= " and (i.avis_passage = true and i.avis_passage_mail = false)";
                            break;
                        case 'non':
                            $sql .= " and (i.avis_passage = false or i.avis_passage is null)";
                            break;
                        default:
                            $sql .= " and (" .(str_replace('ea.adresse', "concat(ea.adresse, ' ', ea.adresse_complement)",$filter['filtersPlain'])) .')';
                            break;
                    }
                } else {
                    $sql .= " and (" .(str_replace('ea.adresse', "concat(ea.adresse, ' ', ea.adresse_complement)",$filter['filtersPlain'])) .')';
                }
            }
            if (stripos($sql, 'e.actif = false') === false) {
                $sql.= ' AND e.actif = true';
            }
        } else {
            $sql = $this->selectCountIntervention;
        }
        if (!$this->hasDateFilter($filters)) {
            $today = date('Y-m-d');
            $dayAfterTomorrow = date('Y-m-d', strtotime('+2 days'));
            $sql .= " and (p.date >= '$today') and (p.date < '$dayAfterTomorrow')";
        }
        $sql .= $this->selectCountEnd;
        $sql = str_replace('[replace_left_join]', implode(' ', $tabAddLeft), $sql );
        $sql = str_replace('[replace_contrat]', $sql_contrat, $sql );

        $stmt = $conn->prepare($sql);

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

    public function getInterventionsCarto($agents, $date)
    {
        $queryBuilder = $this->createQueryBuilder('i');
        $expr = $queryBuilder->expr();
        $request = $queryBuilder
            ->innerJoin('i.planning', 'p')
            ->innerJoin('p.type_planification_init', 'tp')
            ->innerJoin('tp.categorie_planification', 'cp')
            ->where($expr->in('p.utilisateur_agent',  ':agents'))
            ->andWhere('p.date = :date')
            ->andWhere('cp.id = :categClient')
            ->setParameter('agents', $agents)
            ->setParameter('date', $date)
            ->setParameter('categClient', CategoriePlanification::ID_CATEG_CLIENT)
            ->getQuery();
        return $request->getResult() ;
    }

    /**
     * Récupère la date de la première intervention planifiée pour un agent.
     * EXISTS + ORDER BY date LIMIT 1 permet d'arrêter à la première ligne trouvée
     * si l'index (idutilisateur_agent, date) existe sur sav.planning.
     *
     * @param Utilisateur $agent
     * @return string|null Date au format Y-m-d
     */
    public function getFirstInterventionRealiseeAgent($agent)
    {
        $conn = $this->em->getConnection();
        $sql = "
            SELECT p.date
            FROM sav.planning p
            WHERE p.idutilisateur_agent = ?
              AND EXISTS (SELECT 1 FROM sav.intervention i WHERE i.idplanning = p.id)
            ORDER BY p.date ASC
            LIMIT 1
        ";
        $result = $conn->executeQuery($sql, [$agent->getId()])->fetchOne();
        return $result !== false ? $result : null;
    }

    /**
     * Date de la première intervention non validée (technique ou adm) pour un agent.
     * Utilise MIN() pour permettre à l'optimiseur d'utiliser les index (idutilisateur_agent, date) et (idplanning, date_validation_*).
     */
    public function getFirstInterventionAdmAgent($agent, $type) {
        $validationColumn = $type === 'technique' ? 'date_validation_technique' : 'date_validation_adm';
        $conn = $this->em->getConnection();
        $sql = "
            SELECT MIN(p.date) AS date
            FROM sav.planning p
            INNER JOIN sav.intervention i ON i.idplanning = p.id AND i.{$validationColumn} IS NULL
            INNER JOIN sav.type_planification tp ON tp.id = i.idtype_planification AND tp.idcategorie_planification IN (?, ?)
            WHERE p.idutilisateur_agent = ?
        ";
        $result = $conn->executeQuery($sql, [
            CategoriePlanification::ID_CATEG_CLIENT,
            CategoriePlanification::ID_CATEG_INTERNE,
            $agent->getId(),
        ])->fetchOne();
        return $result !== false && $result !== null ? $result : null;
    }

    /**
     * Interventions d'un agent pour la liste de validation.
     * Plage limitée à 400 jours max à partir de $date pour éviter les requêtes sur plusieurs années.
     */
    public function getInterventionsAgent($agent, $date)
    {
        $dateMax = new \DateTime('tomorrow');
        if ($date) {
            $cap = (clone $date)->modify('+400 days');
            if ($cap < $dateMax) {
                $dateMax = $cap;
            }
        }

        $qb = $this->createQueryBuilder('i')
            ->addSelect(['p', 'e', 'cs', 'cu', 'tp', 'eqp'])
            ->innerJoin('i.planning', 'p')
            ->innerJoin('p.type_planification_init', 'tp')
            ->innerJoin('i.equipement', 'e')
            ->innerJoin('e.client_souscripteur', 'cs')
            ->leftJoin('e.client_utilisateur', 'cu')
            ->leftJoin('e.equipementPreferencesPlanification', 'eqp')
            ->andWhere('p.utilisateur_agent = :agent')
            ->andWhere('p.date < :date_max')
            ->setParameter('agent', $agent)
            ->setParameter('date_max', $dateMax)
            ->orderBy('p.date', 'ASC');

        if ($date) {
            $qb->andWhere('p.date >= :date')
                ->setParameter('date', $date->format('Y-m-d'));
        }

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

    /**
     * Compte les interventions non validées techniquement par agent et catégorie de planification
     *
     * @param array $agentIds Liste des IDs des agents
     * @param array $categorieIds Liste des IDs des catégories (1=CLIENT, 2=INTERNE)
     * @return array Résultat avec [id_agent] = count
     */
    public function getCountInterventionValidationByAgents(array $agentIds, string $type)
    {
        $conn = $this->em->getConnection();

        // Créer les placeholders pour les IN clauses
        $agentPlaceholders = implode(',', array_fill(0, count($agentIds), '?'));
        $categoriePlaceholders = implode(',', array_fill(0, 2, '?')); // Toujours 1 et 2

        $sql = "
            SELECT count(i.*), p.idutilisateur_agent
            FROM sav.intervention i 
            INNER JOIN sav.planning p ON p.id = i.idplanning 
            INNER JOIN sav.type_planification tp ON tp.id = i.idtype_planification 
            INNER JOIN sav.categorie_planification cp ON cp.id = tp.idcategorie_planification 
            INNER JOIN sav.equipement e on e.id = i.idequipement
            WHERE i.date_validation_".$type." IS NULL
              AND p.idutilisateur_agent IN ($agentPlaceholders)
              AND cp.id IN ($categoriePlaceholders)
            GROUP BY p.idutilisateur_agent
        ";


        $stmt = $conn->prepare($sql);

        // Ajouter les paramètres dans l'ordre
        $params = array_merge($agentIds, [CategoriePlanification::ID_CATEG_CLIENT, CategoriePlanification::ID_CATEG_INTERNE]);
        $stmt->executeQuery($params);

        $results = $stmt->fetchAllAssociative();

        // Transformer en tableau indexé par idutilisateur_agent
        $stats = [];
        foreach ($results as $result) {
            $stats[$result['idutilisateur_agent']] = (int)$result['count'];
        }

        return $stats;
    }
}