<?php

namespace App\Controller;

use App\Entity\Goal;
use App\Entity\Target;
use App\Entity\Action;
use App\Entity\Activity;
use App\Entity\Notation;
 use App\Entity\NotationIaTemp;
use App\Entity\Nature;
use App\Entity\Ministry;
use App\Entity\Commune;
use App\Entity\Department;
use App\Entity\Appreciation;
use App\Form\ActionType;
use App\Form\ActivityType;
use App\Form\ValidateType;
use App\Form\EvaluationType;
use App\Form\MinistryOnlySelType;
use App\Form\MinistryCommuneSelType;
use App\Repository\ActivitySuggestionRepository; 
use App\Form\NotationType;
use App\Form\NotationModelType;
use App\Form\NotationCollectionModelType;
use App\Form\Model\NotationIaTempModel;
use App\Form\Model\NotationModel;
use App\Form\Model\NotationCollectionModel;
use App\Form\Model\ActivityCollectionModel;
use App\Service\SettingsManager;
use App\Utils\PlanUtils;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Component\HttpFoundation\Request;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\IsGranted;
use Symfony\Component\HttpFoundation\JsonResponse;
use App\Entity\Indicator;
use App\Entity\ActivitySuggestion;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\ORM\EntityManagerInterface;
use Symfony\Component\HttpFoundation\Response;
use Doctrine\DBAL\Connection;
use Psr\Log\LoggerInterface;
use Doctrine\Persistence\ManagerRegistry;


class PlanController extends AbstractController
{

  /**
     * @Route("/app/notation_ia/{id}", name="app_notations_list", defaults={"id"=0})
     */
    //* @IsGranted({"ROLE_ADMIN", "ROLE_MANAGER", "ROLE_DDD"})
public function activitieslist2($id = 0, Request $request, SettingsManager $settingsManager)
{
    $show = !($this->isGranted('ROLE_MANAGER') or $this->isGranted('ROLE_DDD'));

    $collection = $request->request->get('notation_collection_model');
    $postId = isset($collection['action']) ? $collection['action'] : null;
    if ($postId && ($postId != $id)) {
        return $this->redirectToRoute('app_notations_list', ['id' => $postId]);
    }

    $ministry   = null;
    $commune    = null;
    $activities = [];
    $activityIds = [];

    $action = $this->getDoctrine()
        ->getRepository(Action::class)
        ->findOneBy(['id' => (int) $id]);

    $notationCollection = new NotationCollectionModel($action);

    if ($action) {
        // Charger seulement 200 activités max avec Doctrine
        $activities = $this->getDoctrine()
            ->getRepository(Activity::class)
            ->createQueryBuilder('a')
            ->where('a.action = :action')
            ->andWhere('a.validated = true')
            ->andWhere('a.treated = false')
            ->setParameter('action', $action)
            ->setMaxResults(200) // ✅ limite réelle côté SQL
            ->getQuery()
            ->getResult();

        foreach ($activities as $activity) {
            $activityIds[] = $activity->getId();
            $model = new NotationModel();
            $model->activity = $activity;
            $notationCollection->addNotationModel($model);
        }

        $ministry = $action->getProgramm()->getMinistry();
        $commune  = $action->getProgramm()->getCommune();
    }

    // Création du formulaire (inchangé)
    if ($commune) {
        $form = $this->createForm(NotationCollectionModelType::class, $notationCollection, [
            'sessionYear'       => $request->getSession()->get('_year'),
            'sessionMinistry'   => $request->getSession()->get('_ministry'),
            'sessionCommune'    => $request->getSession()->get('_commune'),
            'sessionDepartement'=> $request->getSession()->get('_departement'),
            'commune'           => $commune,
            'grouping'          => 'arrondissement',
            'minRate'           => $this->getRates('minRate'),
            'maxRate'           => $this->getRates('maxRate'),
            'allRate'           => $this->getRates('allRate'),
            'weighting'         => $this->getWeighting($ministry, $settingsManager),
            'show'              => $show,
        ]);
    } else {
        $form = $this->createForm(NotationCollectionModelType::class, $notationCollection, [
            'sessionYear'       => $request->getSession()->get('_year'),
            'sessionMinistry'   => $request->getSession()->get('_ministry'),
            'sessionCommune'    => $request->getSession()->get('_commune'),
            'sessionDepartement'=> $request->getSession()->get('_departement'),
            'commune'           => null,
            'enabledPoste'      => $ministry ? $ministry->getPoste() : false,
            'grouping'          => $ministry ? $ministry->getGrouping() : 'department',
            'minRate'           => $this->getRates('minRate'),
            'maxRate'           => $this->getRates('maxRate'),
            'allRate'           => $this->getRates('allRate'),
            'weighting'         => $this->getWeighting($ministry, $settingsManager),
            'show'              => $show,
        ]);
    }

    $form->handleRequest($request);

    if ($form->isSubmitted() && $form->isValid()) {
        $notationModels = $form->get('notationModels')->getData();

        foreach ($notationModels as $model) {
            if ($model->treated == true) {
                $notation = new Notation();
                $notation->setActivity($model->activity);
                $notation->setNature($model->nature);
                $notation->setTarget($model->target);

                if ($model->communes) {
                    $notation->setNbOfLocation(count($model->communes));
                    foreach ($model->communes as $commune) {
                        $notation->addCommune($commune);
                    }
                    foreach ($model->postes as $poste) {
                        $notation->addPoste($poste);
                    }
                } elseif ($model->arrondissements) {
                    $notation->setNbOfLocation(count($model->arrondissements));
                    foreach ($model->arrondissements as $arrondissement) {
                        $notation->addArrondissement($arrondissement);
                    }
                }

                $notation->setTargetRate($model->targetRate);
                $notation->setLocationRate($model->locationRate);
                $notation->setSensitivity($model->sensitivity);

                $appreciation = $this->getDoctrine()
                    ->getRepository(Appreciation::class)
                    ->findOneByValue($model->sensitivity);
                $notation->setAppreciation($appreciation);

                foreach ($model->indicators as $indicator) {
                    $notation->addIndicator($indicator);
                }

                $this->getDoctrine()->getManagerForClass(Notation::class)->persist($notation);

                $activity = $model->activity;
                $activity->setTreated(true);
            }
        }

        $action->setTreated(PlanUtils::checkActionTreated($action));
        $this->getDoctrine()->getManagerForClass(Notation::class)->flush();

        return $this->redirectToRoute('app_notations_list', ['id' => $action->getId()]);
    }

    return $this->render('plan/notation_form_ia.html.twig', [
        'activities'  => $activities,
        'activityIds' => $activityIds,
        'form'        => $form->createView(),
        'actionId'    => $action ? $action->getId() : 0,
        'grouping'    => $commune ? 'arrondissement' : 'department',
    ]);
}

 #[Route('/save-ia-results', name: 'save_ia_results', methods: ['POST'])]
public function saveIaResults(
    Request $request,
    EntityManagerInterface $entityManager,
    LoggerInterface $logger
): JsonResponse {
    $logger->info('saveIaResults appelé');

    try {
        $session = $request->getSession();
        if ($session && !$session->isStarted()) {
            $session->start();
        }
    } catch (\Throwable $e) {
        $logger->debug('Impossible de démarrer la session : ' . $e->getMessage());
    }

    $content = $request->getContent();
    $data = json_decode($content, true);

    if (!is_array($data) || !isset($data['results']) || !is_array($data['results'])) {
        $logger->warning('Données invalides reçues', ['data' => $data]);
        return new JsonResponse(['status' => 'error', 'message' => 'Données invalides'], 400);
    }

    $saved = 0;
    $skipped = 0;
    $errors = 0;
    $chunkSize = 50;
    $processedInBatch = 0;

    $repoActivity   = $entityManager->getRepository(Activity::class);
    $repoNature     = $entityManager->getRepository(Nature::class);
    $repoGoal       = $entityManager->getRepository(Goal::class);
    $repoIndicator  = $entityManager->getRepository(Indicator::class);
    $repoApprec     = $entityManager->getRepository(Appreciation::class);
    $repoNotation   = $entityManager->getRepository(Notation::class);
    $repoTarget     = $entityManager->getRepository(Target::class);

    foreach ($data['results'] as $index => $result) {
        try {
            // Debug des données brutes
            $logger->debug("Traitement de l'index #$index", [
                'activity_id' => $result['activity_id'] ?? null,
                'activity_id_type' => gettype($result['activity_id'] ?? null),
                'cible' => $result['cible'] ?? null,
                'indicateur' => $result['indicateur'] ?? null
            ]);

            // Validation de activity_id
            $activityId = $result['activity_id'] ?? null;
            if (!is_int($activityId) && !(is_string($activityId) && ctype_digit($activityId))) {
                $logger->warning("activity_id invalide", [
                    'value' => $activityId,
                    'type' => gettype($activityId),
                    'data' => $result
                ]);
                $skipped++;
                continue;
            }
            $activityId = (int)$activityId;

            // Validation de nature.id
            $natureId = $result['nature']['id'] ?? null;
            if (!is_int($natureId) && !(is_string($natureId) && ctype_digit($natureId))) {
                $logger->warning("nature.id invalide", [
                    'value' => $natureId,
                    'type' => gettype($natureId),
                    'data' => $result
                ]);
                $skipped++;
                continue;
            }
            $natureId = (int)$natureId;

            // Validation de cible.id (goalId)
            $goalId = $result['cible']['id'] ?? null;
            if ($goalId !== null && !is_int($goalId) && !(is_string($goalId) && ctype_digit($goalId))) {
                $logger->warning("cible.id invalide", [
                    'value' => $goalId,
                    'type' => gettype($goalId),
                    'data' => $result
                ]);
                $skipped++;
                continue;
            }
            $goalId = $goalId !== null ? (int)$goalId : null;

            // Validation de indicateur
            $indicatorId = null;
            if (isset($result['indicateur'])) {
                if (is_array($result['indicateur']) && isset($result['indicateur']['id'])) {
                    $indicatorId = $result['indicateur']['id'];
                    if (!is_int($indicatorId) && !(is_string($indicatorId) && ctype_digit($indicatorId))) {
                        $logger->warning("indicateur.id invalide", ['indicator' => $result['indicateur']]);
                    } else {
                        $indicatorId = (int)$indicatorId;
                    }
                } elseif (is_int($result['indicateur']) || (is_string($result['indicateur']) && ctype_digit($result['indicateur']))) {
                    $indicatorId = (int)$result['indicateur'];
                } else {
                    $logger->warning("indicateur invalide", ['indicator' => $result['indicateur']]);
                }
            }

            // Récupération des entités
            $activity  = $repoActivity->find($activityId);
            $nature    = $repoNature->find($natureId);
            $target      = $goalId ? $repoTarget->find($goalId) : null;
            $indicator = $indicatorId ? $repoIndicator->find($indicatorId) : null;

            if (!$activity || !$nature) {
                $logger->warning("Activity ou Nature introuvable", [
                    'activity_id' => $activityId,
                    'nature_id' => $natureId
                ]);
                $skipped++;
                continue;
            }

            // TRAITEMENT SPÉCIAL POUR LES ACTIVITÉS DE SOUTIEN (nature_id = 4)
            if ($natureId === 4) {
                $logger->info("Activité de soutien détectée - traitement simplifié", [
                    'activity_id' => $activityId,
                    'activity_name' => $activity->getName(),
                    'nature' => 'soutien'
                ]);

                // Gestion de la Notation pour le soutien
                $notation = $repoNotation->findOneBy(['activity' => $activity]);
                if (!$notation) {
                    $notation = new Notation();
                    $notation->setActivity($activity);
                }

                // Configuration spécifique pour les activités de soutien
                $notation->setNature($nature);
                $notation->setTarget(null);
                
                // Pas d'indicateurs pour le soutien - on nettoie les indicateurs existants
                foreach ($notation->getIndicators() as $existingIndicator) {
                    $notation->removeIndicator($existingIndicator);
                }
                
                // ========================================
                //  MODIFICATION - Calcul spécifique pour le soutien
                // ========================================
                $notation->setSensitivity(20);
                $notation->setTargetRate(0);
                $notation->setLocationRate(0);
                $notation->setNbOfLocation(0);
                // ========================================
                
                // Appreciation pour le soutien (ID 1)
                $appreciation = $repoApprec->find(1);
                $notation->setAppreciation($appreciation);
                $notation->setDuplicated(false);

            } else {
                // 🔹 TRAITEMENT NORMAL POUR LES AUTRES NATURES
                
                // Récupérer la notation existante pour cette activité
                $existingNotation = $repoNotation->findOneBy(['activity' => $activity]);
                
                $nbCommunes = 0;
                $communesIds = [];
                $nbTotalCommunes = 0;
                
                if ($existingNotation) {
                    // Récupération des communes via la table notation_commune
                    $connection = $entityManager->getConnection();
                    $sql = 'SELECT commune_id FROM notation_commune WHERE notation_id = :notation_id';
                    $stmt = $connection->prepare($sql);
                    $stmt->bindValue('notation_id', $existingNotation->getId());
                    $resultSet = $stmt->executeQuery();
                    $communesIds = $resultSet->fetchFirstColumn();
                    $nbCommunes = count($communesIds);
                    
                    // Récupération du nombre total de communes disponibles
                    $sqlTotal = 'SELECT COUNT(*) FROM commune';
                    $stmtTotal = $connection->prepare($sqlTotal);
                    $resultTotal = $stmtTotal->executeQuery();
                    $nbTotalCommunes = $resultTotal->fetchOne();
                    
                    $logger->info("Localisation récupérée depuis notation_commune pour l'activité #$activityId", [
                        'activity_id' => $activityId,
                        'activity_name' => $activity->getName(),
                        'notation_id' => $existingNotation->getId(),
                        'communes_ids' => $communesIds,
                        'nb_communes_selectionnees' => $nbCommunes,
                        'nb_total_communes' => $nbTotalCommunes
                    ]);
                } else {
                    $logger->warning("Aucune notation existante trouvée pour l'activité #$activityId - nb_communes = 0", [
                        'activity_id' => $activityId,
                        'activity_name' => $activity->getName()
                    ]);
                }

                // ========================================
                //  MODIFICATION - Calcul des rates et de la sensibilité
                // ========================================
                
                // 1. Calcul du natureRate selon la nature
                $natureRate = $this->getNatureRate($natureId);
                
                // 2. Calcul du targetRate
                $hasIndicator = ($indicator !== null);
                $targetRate = $this->calculateTargetRate($natureRate, $hasIndicator);
                
                // 3. Calcul du locationRate
                $locationRate = $this->calculateLocationRate($nbCommunes, $nbTotalCommunes);
                
                // 4. Calcul de la sensibilité finale
                $sensitivity = $this->calculateSensitivity($natureRate, $targetRate, $locationRate);
                
                // 5. Détermination de l'appreciation selon la sensibilité
                $appreciation = $this->getAppreciationBySensitivity($sensitivity, $repoApprec);
                
                $logger->info("Calculs effectués pour l'activité #$activityId", [
                    'activity_id' => $activityId,
                    'nature_id' => $natureId,
                    'natureRate' => $natureRate,
                    'hasIndicator' => $hasIndicator,
                    'targetRate' => $targetRate,
                    'nbCommunes' => $nbCommunes,
                    'nbTotalCommunes' => $nbTotalCommunes,
                    'locationRate' => $locationRate,
                    'sensitivity' => $sensitivity,
                    'appreciation_id' => $appreciation ? $appreciation->getId() : null
                ]);
                
                // ========================================

                // Gestion de la Notation
                $notation = $repoNotation->findOneBy(['activity' => $activity]);
                if (!$notation) {
                    $notation = new Notation();
                    $notation->setActivity($activity);
                }

                $notation->setNature($nature);
                $notation->setTarget($target);

                if ($indicator) {
                    $already = false;
                    foreach ($notation->getIndicators() as $ind) {
                        if ($ind->getId() === $indicator->getId()) {
                            $already = true;
                            break;
                        }
                    }
                    if (!$already) {
                        $notation->addIndicator($indicator);
                    }
                }

                // ========================================
                //  MODIFICATION - Application des valeurs calculées
                // ========================================
                $notation->setSensitivity($sensitivity);
                $notation->setTargetRate($targetRate);
                $notation->setLocationRate($locationRate);
                $notation->setNbOfLocation($nbCommunes);
                $notation->setAppreciation($appreciation);
                // ========================================
                
                $notation->setDuplicated(false);
            }

            //  Marquer l'activité comme traitée
            $activity->setTreated(true);

            // Vérifier si l'action parente est entièrement traitée
            $action = $activity->getAction();
            if ($action) {
                $action->setTreated(PlanUtils::checkActionTreated($action));
            }

            $entityManager->persist($notation);
            
            $saved++;
            $processedInBatch++;

        } catch (\Throwable $e) {
            $errors++;
            $logger->error("Erreur résultat #$index : " . $e->getMessage(), [
                'index' => $index,
                'activity_id' => $activityId ?? 'null',
                'trace' => $e->getTraceAsString()
            ]);
        }

        // Flush par lots
        if ($processedInBatch >= $chunkSize) {
            try {
                $entityManager->flush();
                $entityManager->clear();
            } catch (\Throwable $e) {
                $logger->error("Erreur flush/clear : " . $e->getMessage());
            }
            $processedInBatch = 0;
        }
    }

    // Flush final
    try {
        $entityManager->flush();
        $entityManager->clear();
    } catch (\Throwable $e) {
        $logger->error("Erreur flush final : " . $e->getMessage());
    }

    $logger->info("Traitement terminé", [
        'saved' => $saved,
        'skipped' => $skipped,
        'errors' => $errors
    ]);

    return new JsonResponse([
        'status' => 'success',
        'message' => 'Traitement terminé',
        'saved' => $saved,
        'skipped' => $skipped,
        'errors' => $errors
    ]);
}

// ========================================
//  FONCTIONS DE CALCUL
// ========================================

/**
 * Retourne le natureRate selon l'ID de la nature
 * Consommation (1) = 90, Production (2) = 70, Habilitante (3) = 50, Soutien (4) = 20
 */
private function getNatureRate(int $natureId): float
{
    $natureRates = [
        1 => 90.0,  // Consommation
        2 => 70.0,  // Production
        3 => 50.0,  // Habilitante
        4 => 20.0   // Soutien
    ];
    
    return $natureRates[$natureId] ?? 0.0;
}

/**
 * Calcule le targetRate
 * Si un indicateur est présent, retourne maxRate (90), sinon retourne natureRate
 */
private function calculateTargetRate(float $natureRate, bool $hasIndicator): float
{
    $maxRate = 90.0;
    $minRate = 20.0;
    
    // Si natureRate est valide (différent de minRate)
    if ($natureRate && ($natureRate - $minRate) != 0) {
        if ($hasIndicator) {
            return $maxRate;
        }
    }
    
    return $natureRate;
}

/**
 * Calcule le locationRate
 * locationRate = (nbCommunes sélectionnées / nbTotal communes) * 100
 */
private function calculateLocationRate(int $nbSelectedCommunes, int $nbTotalCommunes): float
{
    if ($nbTotalCommunes == 0) {
        return 0.0;
    }
    
    return ($nbSelectedCommunes / $nbTotalCommunes) * 100;
}

/**
 * Calcule la sensibilité finale
 * sensitivity = (targetRate - ((100 - locationRate) / 10)) * weighting / 100
 * weighting = 100 (constante)
 */
private function calculateSensitivity(float $natureRate, float $targetRate, float $locationRate): float
{
    $minRate = 20.0;
    $weighting = 100.0;
    
    $sensitivity = $targetRate;
    
    // Si natureRate est valide (différent de minRate)
    if ($natureRate && ($natureRate - $minRate) != 0) {
        $sensitivity = ($targetRate - ((100 - $locationRate) / 10)) * $weighting / 100;
    }
    
    return round($sensitivity, 2);
}

/**
 * Détermine l'appreciation selon la sensibilité
 * Basé sur les valeurs de sensibilité calculées
 */
private function getAppreciationBySensitivity(float $sensitivity, $appreciationRepository)
{
    // Logique de mapping selon la sensibilité
    if ($sensitivity >= 70) {
        return $appreciationRepository->find(4); // Très sensible
    } elseif ($sensitivity >= 50) {
        return $appreciationRepository->find(3); // Sensible
    } elseif ($sensitivity >= 30) {
        return $appreciationRepository->find(2); // Peu sensible
    } else {
        return $appreciationRepository->find(1); // Pas sensible
    }
}

/**
 * Cette fonction est appelée au clic du bouton "Suggestion IA"
 * 
 * @Route("/api/generate-activity-suggestions", name="generate_activity_suggestions", methods={"POST"})
 */
public function generateActivitySuggestions(
    Request $request,
    EntityManagerInterface $entityManager,
    LoggerInterface $logger
): JsonResponse {
    $logger->info('generateActivitySuggestions appelé');
    
    $successCount = 0;
    $errorCount = 0;
    $skippedCount = 0;
    $totalProcessed = 0;
    
    try {
        // Récupération des données envoyées depuis l'interface
        $content = $request->getContent();
        $data = json_decode($content, true);
        
        $activitiesData = $data['activities'] ?? [];

        
        if (empty($activitiesData)) {
            // Si aucune donnée n'est envoyée, on récupère toutes les notations
            $logger->info("Aucune activité spécifique fournie, traitement de toutes les notations");
            $repoNotation = $entityManager->getRepository(Notation::class);
            $notations = $repoNotation->findAll();
        } else {
            // Sinon, on récupère uniquement les notations demandées
            $logger->info("Traitement de " . count($activitiesData) . " activités spécifiques");
            $notationIds = array_column($activitiesData, 'notation_id');
            $repoNotation = $entityManager->getRepository(Notation::class);
            $notations = $repoNotation->findBy(['id' => $notationIds]);
        }
        
        // Récupérer en une seule requête toutes les activités déjà traitées
        $connection = $entityManager->getConnection();
        $existingIds = $connection->fetchFirstColumn(
            'SELECT activity_id FROM activity_suggestion'
        );
        $existingIds = array_flip($existingIds);

        // Filtrer les notations dont l'activité n'a pas encore de suggestion
        $notations = array_filter($notations, function($notation) use ($existingIds) {
            $activity = $notation->getActivity();
            return $activity && !isset($existingIds[$activity->getId()]);
        });

        // Prendre uniquement les 10 premières non encore traitées
        $notations = array_slice(array_values($notations), 0, 10);

        $totalProcessed = count($notations);

        $logger->info("Traitement de {$totalProcessed} notations pour génération de suggestions");
        
        foreach ($notations as $notation) {
            try {
                // Récupération de l'activité liée à la notation
                $activity = $notation->getActivity();
                
                if (!$activity) {
                    $logger->warning("Notation #{$notation->getId()} sans activité associée");
                    $skippedCount++;
                    continue;
                }
                
                $activityId = $activity->getId();
                
                // Récupération des communes depuis notation_commune
                $communesIds = $this->getCommunesForNotation($notation->getId(), $entityManager);
                
                // Récupération de la sensibilité
                // Récupération de la sensibilité
                $sensitivity = $notation->getSensitivity() ?? 20;

                // On ne génère une suggestion que si la sensibilité est <= 50%
                if ($sensitivity > 50) {
                    $logger->info("Activité #{$activityId} ignorée (sensibilité {$sensitivity}% > 50%)", [
                        'activity_name' => $activity->getName()
                    ]);
                    $skippedCount++;
                    continue;
                }

                $logger->info("Traitement de l'activité #{$activityId}: {$activity->getName()}");
                
                // Appel à l'API Mistral pour obtenir la suggestion                $suggestion = $this->getMistralSuggestion(
                    $activity,
                    $notation,
                    $communesIds,
                    $sensitivity,
                    $logger
                );
                
                $logger->info("Suggestion pour l'activité ayant l'id {$activityId} et la description: {$activity->getName()} : {$suggestion}");
                
                // Enregistrement de la suggestion en base de données
                $this->saveActivitySuggestionToDatabase(
                    $activityId,
                    $suggestion,
                    $entityManager,
                    $logger
                );
                
                $successCount++;
                
                // Petit délai pour éviter de surcharger l'API Mistral
                usleep(500000); // 0.5 seconde
                
            } catch (\Throwable $e) {
                $errorCount++;
                $logger->error("Erreur lors de la génération de suggestion pour la notation #{$notation->getId()}: " . $e->getMessage(), [
                    'trace' => $e->getTraceAsString()
                ]);
            }
        }
        
    } catch (\Throwable $e) {
        $logger->error("Erreur globale lors de la génération des suggestions: " . $e->getMessage());
        return new JsonResponse([
            'status' => 'error',
            'message' => 'Erreur lors de la génération des suggestions: ' . $e->getMessage()
        ], 500);
    }
    
    $logger->info("Génération des suggestions terminée", [
        'total' => $totalProcessed,
        'success' => $successCount,
        'errors' => $errorCount,
        'skipped' => $skippedCount
    ]);
    
    return new JsonResponse([
        'status' => 'success',
        'message' => 'Génération des suggestions terminée',
        'total' => $totalProcessed,
        'success' => $successCount,
        'errors' => $errorCount,
        'skipped' => $skippedCount
    ]);
}

/**
 * Récupère les IDs des communes liées à une notation
 */
private function getCommunesForNotation(int $notationId, EntityManagerInterface $entityManager): array
{
    try {
        $connection = $entityManager->getConnection();
        $sql = 'SELECT commune_id FROM notation_commune WHERE notation_id = :notation_id';
        $stmt = $connection->prepare($sql);
        $stmt->bindValue('notation_id', $notationId);
        $resultSet = $stmt->executeQuery();
        return $resultSet->fetchFirstColumn();
    } catch (\Throwable $e) {
        return [];
    }
}

/**
 * Appelle l'API Mistral pour obtenir des suggestions d'amélioration de la sensibilité
 */
private function getMistralSuggestion(
    $activity,
    $notation,
    array $communesIds,
    float $sensitivity,
    LoggerInterface $logger
): string {
    // Récupération des informations nécessaires
    $activityName = $activity->getName();
    $goalName = $notation->getTarget() ? $notation->getTarget()->getGoal()->getName() : 'Non défini';
    $nbCommunes = count($communesIds);
    $natureName = $notation->getNature() ? $notation->getNature()->getName() : 'Non définie';
    $direction= $activity->getDirection();
    $ministry= $direction->getMinistry()->getName();
    
    // Construction du prompt pour Mistral
    $prompt = "Tu es un spécialiste de l'analyse de la sensibilité des activités gouvernementales au Bénin aux Objectifs du Développement Durable (ODD) prévus pour 2030. Ton rôle est d'analyser les activités et de proposer des suggestions pertinentes qui permettront d'améliorer la sensibilité plus tard. Voici une activité de projet :\n\n";
    $prompt .= "- Intitulé : {$activityName}\n";
    $prompt .= "- Ministère en charge : {$ministry}\n";
    $prompt .= "- Nature : {$natureName}\n";
    $prompt .= "- ODD (Objectif de Développement Durable) actuel : {$goalName}\n";
    $prompt .= "- Nombre de communes ciblées : {$nbCommunes} sur 77 communes du Bénin au total\n";
    $prompt .= "- Sensibilité actuelle : {$sensitivity}%\n\n";
    $prompt .= "Question : Comment améliorer la sensibilité de cette activité aux ODD pour maximiser son impact ? ";
    $prompt .= "Donne 2 recommandations concrètes et actionnables en 3-4 phrases maximum.";
    $logger->info("Prompt final envoyé #{$prompt}");
    // Préparation de la requête API
    $apiKey = $_ENV['MISTRAL_API_KEY'] ?? null;
    
    if (!$apiKey) {
        throw new \Exception('MISTRAL_API_KEY non configurée dans les variables d\'environnement');
    }
    
    $requestData = [
        'model' => 'mistral-small-latest',
        'temperature' => 0.7,
        'top_p' => 1,
        'max_tokens' => 500,
        'stream' => false,
        'messages' => [
            [
                'role' => 'user',
                'content' => $prompt
            ]
        ],
        'safe_prompt' => false
    ];
    
    // Appel à l'API Mistral
    $ch = curl_init('https://api.mistral.ai/v1/chat/completions');
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_POST, true);
    curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($requestData));
    curl_setopt($ch, CURLOPT_HTTPHEADER, [
        'Content-Type: application/json',
        'Authorization: Bearer ' . $apiKey
    ]);
    curl_setopt($ch, CURLOPT_TIMEOUT, 30);
    
    $response = curl_exec($ch);
    $httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
    $curlError = curl_error($ch);
    curl_close($ch);
    
    if ($curlError) {
        throw new \Exception("Erreur cURL : {$curlError}");
    }
    
    if ($httpCode !== 200) {
        $logger->error("Erreur API Mistral", [
            'http_code' => $httpCode,
            'response' => $response
        ]);
        throw new \Exception("Erreur API Mistral (HTTP {$httpCode})");
    }
    
    $responseData = json_decode($response, true);
    
    if (!isset($responseData['choices'][0]['message']['content'])) {
        throw new \Exception('Réponse API Mistral invalide');
    }
    
    return trim($responseData['choices'][0]['message']['content']);
}

/**
 * Enregistre ou met à jour la suggestion d'activité en base de données
 */
private function saveActivitySuggestionToDatabase(
    int $activityId,
    string $suggestion,
    EntityManagerInterface $entityManager,
    LoggerInterface $logger
): void {
    try {
        $connection = $entityManager->getConnection();
        
        // Vérifier si une suggestion existe déjà pour cette activité
        $sql = 'SELECT id FROM activity_suggestion WHERE activity_id = :activity_id';
        $stmt = $connection->prepare($sql);
        $stmt->bindValue('activity_id', $activityId);
        $result = $stmt->executeQuery();
        $existingId = $result->fetchOne();
        
        if ($existingId) {
            // Mise à jour de la suggestion existante
            $updateSql = 'UPDATE activity_suggestion 
                         SET suggestion = :suggestion, 
                             updated_at = NOW() 
                         WHERE activity_id = :activity_id';
            $updateStmt = $connection->prepare($updateSql);
            $updateStmt->bindValue('suggestion', $suggestion);
            $updateStmt->bindValue('activity_id', $activityId);
            $updateStmt->executeStatement();
            
            $logger->info("Suggestion mise à jour en base de données pour l'activité #{$activityId}");
        } else {
            // Insertion d'une nouvelle suggestion
            $insertSql = 'INSERT INTO activity_suggestion (activity_id, suggestion, created_at, updated_at) 
                         VALUES (:activity_id, :suggestion, NOW(), NOW())';
            $insertStmt = $connection->prepare($insertSql);
            $insertStmt->bindValue('activity_id', $activityId);
            $insertStmt->bindValue('suggestion', $suggestion);
            $insertStmt->executeStatement();
            
            $logger->info("Nouvelle suggestion enregistrée en base de données pour l'activité #{$activityId}");
        }
        
    } catch (\Throwable $e) {
        $logger->error("Erreur lors de l'enregistrement de la suggestion pour l'activité #{$activityId}: " . $e->getMessage());
        // On ne throw pas l'exception pour ne pas bloquer le traitement principal
    }
}
/**
 * Appelle l'API Mistral pour obtenir des suggestions d'amélioration de la sensibilité
 */

/**
 * Enregistre ou met à jour la suggestion d'activité en base de données
 */

public function getActivityLocation($activityId)
{
    // Récupération de l'activité par son ID
    $activity = $this->getDoctrine()
        ->getRepository(Activity::class)
        ->findOneBy(['id' => (int)$activityId]);

    if (!$activity) {
        return [
            'communes' => [],
            'departements' => [],
            'poles' => [],
            'arrondissements' => [],
            'all_communes' => [],
            'all_arrondissements' => [],
            'message' => 'Activité non trouvée'
        ];
    }

    // Récupération dynamique des départements, communes et arrondissements depuis la base de données
    $departementsCommunes = $this->getDepartementsCommunes();
    
    // Récupération des pôles et leurs communes
    $polesCommunes = $this->getPolesCommunes();

    // Récupération du nom de l'activité
    $activityName = $activity->getName() ? strtolower($activity->getName()) : '';
    
    // DEBUG : Affichage du nom de l'activité et des pôles disponibles
    error_log("ACTIVITE: '$activityName'");
    error_log("POLES DISPONIBLES: " . json_encode(array_keys($polesCommunes)));
    
    $localisations = [
        'communes' => [],
        'departements' => [],
        'poles' => [],
        'arrondissements' => [],
        'all_communes' => [], // Toutes les communes trouvées (directement ou via départements/pôles)
        'all_arrondissements' => [] // Tous les arrondissements trouvés (directement, via communes/départements/pôles)
    ];

    //  RECHERCHE DES PÔLES dans le nom de l'activité (mots complets uniquement)
    foreach ($polesCommunes as $pole => $communes) {
        $poleLower = strtolower($pole);
        
        // Utilisation de preg_match avec des limites de mots (\b)
        $pattern = '/\b' . preg_quote($poleLower, '/') . '\b/i';
        
        if (preg_match($pattern, $activityName)) {
            $localisations['poles'][] = $pole;
            
            //  DEBUG : Log pour comprendre ce qui se passe
            error_log("POLE DETECTE: '$pole' dans '$activityName' - Nombre de communes: " . count($communes));
            
            // Ajout de toutes les communes du pôle
            foreach ($communes as $commune) {
                if (!in_array($commune, $localisations['all_communes'])) {
                    $localisations['all_communes'][] = $commune;
                }
                
                // Récupération des arrondissements de cette commune depuis $departementsCommunes
                foreach ($departementsCommunes as $departement => $communesArrond) {
                    if (isset($communesArrond[$commune])) {
                        foreach ($communesArrond[$commune] as $arrondissement) {
                            if (!in_array($arrondissement, $localisations['all_arrondissements'])) {
                                $localisations['all_arrondissements'][] = $arrondissement;
                            }
                        }
                        break; // Commune trouvée, pas besoin de continuer
                    }
                }
            }
        }
    }

    //  RECHERCHE DES DÉPARTEMENTS dans le nom de l'activité (mots complets uniquement)
    foreach ($departementsCommunes as $departement => $communes) {
        $departementLower = strtolower($departement);
        
        // Utilisation de preg_match avec des limites de mots (\b)
        $pattern = '/\b' . preg_quote($departementLower, '/') . '\b/i';
        
        if (preg_match($pattern, $activityName)) {
            $localisations['departements'][] = $departement;
            
            // Ajout de toutes les communes du département
            foreach ($communes as $commune => $arrondissements) {
                if (!in_array($commune, $localisations['all_communes'])) {
                    $localisations['all_communes'][] = $commune;
                }
                
                // Ajout de tous les arrondissements de toutes les communes du département
                foreach ($arrondissements as $arrondissement) {
                    if (!in_array($arrondissement, $localisations['all_arrondissements'])) {
                        $localisations['all_arrondissements'][] = $arrondissement;
                    }
                }
            }
        }
    }

    //  RECHERCHE DES COMMUNES spécifiques dans le nom de l'activité (mots complets uniquement)
    foreach ($departementsCommunes as $departement => $communes) {
        foreach ($communes as $commune => $arrondissements) {
            $communeLower = strtolower($commune);
            
            // Gestion spéciale pour les noms avec tirets (comme Abomey-Calavi)
            $communeForPattern = str_replace('-', '\-', $communeLower);
            $pattern = '/\b' . preg_quote($communeForPattern, '/') . '\b/i';
            
            if (preg_match($pattern, $activityName)) {
                if (!in_array($commune, $localisations['communes'])) {
                    $localisations['communes'][] = $commune;
                }
                if (!in_array($commune, $localisations['all_communes'])) {
                    $localisations['all_communes'][] = $commune;
                }
                
                // Ajout de tous les arrondissements de cette commune
                foreach ($arrondissements as $arrondissement) {
                    if (!in_array($arrondissement, $localisations['all_arrondissements'])) {
                        $localisations['all_arrondissements'][] = $arrondissement;
                    }
                }
            }
        }
    }

    //  RECHERCHE DES ARRONDISSEMENTS spécifiques dans le nom de l'activité (mots complets uniquement)
    foreach ($departementsCommunes as $departement => $communes) {
        foreach ($communes as $commune => $arrondissements) {
            foreach ($arrondissements as $arrondissement) {
                $arrondissementLower = strtolower($arrondissement);
                
                // Gestion spéciale pour les noms avec tirets, apostrophes, espaces, etc.
                $arrondissementForPattern = preg_quote($arrondissementLower, '/');
                $pattern = '/\b' . $arrondissementForPattern . '\b/i';
                
                if (preg_match($pattern, $activityName)) {
                    if (!in_array($arrondissement, $localisations['arrondissements'])) {
                        $localisations['arrondissements'][] = $arrondissement;
                    }
                    if (!in_array($arrondissement, $localisations['all_arrondissements'])) {
                        $localisations['all_arrondissements'][] = $arrondissement;
                    }
                    
                    // Ajout de la commune parent si elle n'est pas déjà présente
                    if (!in_array($commune, $localisations['all_communes'])) {
                        $localisations['all_communes'][] = $commune;
                    }
                }
            }
        }
    }

    // Tri des résultats
    sort($localisations['communes']);
    sort($localisations['departements']);
    sort($localisations['poles']);
    sort($localisations['arrondissements']);
    sort($localisations['all_communes']);
    sort($localisations['all_arrondissements']);

    return $localisations;
}

private function getPolesCommunes()
{
    $connection = $this->getDoctrine()->getConnection();
    
    $sql = "
        SELECT 
            CONCAT('pole ', c.pole_id) AS pole_name,
            'pole' AS pole_word,
            c.name AS commune_name
        FROM commune c
        WHERE c.pole_id IS NOT NULL
        ORDER BY c.pole_id, c.name
    ";

    $rows = $connection->fetchAllAssociative($sql);

    $data = [];
    foreach ($rows as $row) {
        $pole = $row['pole_name']; // pole 1, pole 2, etc.
        $commune = $row['commune_name'];

        // Initialisation du pôle s'il n'existe pas
        if (!isset($data[$pole])) {
            $data[$pole] = [];
        }

        // Ajout commune au pôle
        $data[$pole][] = $commune;
        
        // Ajout aussi du mot "pole" seul pour la recherche générale
        if (!isset($data['pole'])) {
            $data['pole'] = [];
        }
        if (!in_array($commune, $data['pole'])) {
            $data['pole'][] = $commune;
        }
    }

    return $data;
}

/**
 * Récupère la structure départements -> communes -> arrondissements depuis la base de données
 */
private function getDepartementsCommunes()
{
    $connection = $this->getDoctrine()->getConnection();
    
    $sql = "
        SELECT 
            d.name AS department,
            c.name AS commune,
            a.name AS arrondissement
        FROM department d
        INNER JOIN commune c ON c.department_id = d.id
        INNER JOIN arrondissement a ON a.commune_id = c.id
        ORDER BY d.name, c.name, a.name
    ";

    $rows = $connection->fetchAllAssociative($sql);

    $data = [];
    foreach ($rows as $row) {
        $departement = $row['department'];
        $commune = $row['commune'];
        $arrondissement = $row['arrondissement'];

        // Initialisation
        if (!isset($data[$departement])) {
            $data[$departement] = [];
        }
        if (!isset($data[$departement][$commune])) {
            $data[$departement][$commune] = [];
        }

        // Ajout arrondissement
        $data[$departement][$commune][] = $arrondissement;
    }

    return $data;
}
/**
     * @Route("/app/validation_ia/{id}", name="app_validations_list", defaults={"id"=0})
     */
    //* @IsGranted({"ROLE_ADMIN", "ROLE_MANAGER", "ROLE_DDD"}, "ROLE_FOCAL"})
public function validationia($id, Request $request)
    {
        $notations = [];
        $appreciation = '---';
        $sensitivity = 0;
        $amount = 0;

        $evaluation = $request->request->get('evaluation');
        $postId = isset($evaluation['action']) ? $evaluation['action'] : null;
        if ($postId and ($postId != $id)) {
            return $this->redirectToRoute('app_validations_list', ['id' => $postId]);
        }

        $action = $this->getDoctrine()
            ->getRepository(Action::class)
            ->findOneBy(['id' => (int)$id]);

        
        $commune = $action ? $action->getProgramm()->getCommune() : null;

        $notationCollection = New NotationCollectionModel($action);

        if ($action) {
            foreach ($action->getActivities() as $activity) {
                if ($activity->getValidated() == true) {
                    foreach ($activity->getNotations() as $notation) {
                        $notations[] = $notation;
                    }
                }
            }
        }

        $form = $this->createForm(EvaluationType::class, $notationCollection, [
            'sessionYear' => $request->getSession()->get('_year'),
            'sessionMinistry' => $request->getSession()->get('_ministry'),
            'sessionCommune' => $request->getSession()->get('_commune'),
            'sessionDepartement' => $request->getSession()->get('_departement'),
            'defaultEntite' => $commune
        ]);

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            //$ministry = $form->get('ministry')->getData();
            //$programm = $form->get('programm')->getData();
            //$action = $form->get('action')->getData();
            
        }

        return $this->render('plan/evaluation_list_ia.html.twig', [
            'notations' => $notations,
            'form' => $form->createView(),
            'actionId' => $action ? $action->getId() : 0
        ]);
    }
 
 
    function getWeighting($ministry, $settingsManager) {
        $weighting = 100;
        if ($ministry) {
            $ministryType = $ministry->getMtype();
            if ( $ministryType == 'strategie' ) {
                $weighting += $settingsManager->get('ministry_weight'); 
            }
        }
        return $weighting;
    }

    function getRates($type) {
        $natureRepository = $this->getDoctrine()->getRepository(Nature::class);
        if ($type == 'minRate')
            return $natureRepository->findMinRate();
        if ($type == 'maxRate')
            return $natureRepository->findMaxRate();
        if ($type == 'allRate')
            return json_encode($natureRepository->findAllRate());
    }

    function getSensitivity($notation, $minRate, $weighting) {
        $sensitivity = $notation->getTargetRate();
        if ($notation->getNature()->getRate() > $minRate) {
            $sensitivity = ($notation->getTargetRate() - ((100 - $notation->getLocationRate()) / 10)) * ($weighting / 100);
        }
        return $sensitivity;
    }

    /**
     * @Route("/app/actions", name="app_actions")
     */
    public function actionsAction(Request $request)
    {
        $actions = [];
        $entite=null;

        if($request->getSession()->get('_ministry')){
            $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $request->getSession()->get('_ministry')]);
            $entite=$ministry;
            $actions = $this->getDoctrine()
                ->getRepository(Action::class)
                ->createQueryBuilder('a')
                ->select('a')
                ->join('a.programm' ,'p')
                ->andWhere('a.year = :year')
                ->andWhere('p.ministry = :ministry')
                ->andWhere('a.validated = :validated')
                ->setParameter('year', $request->getSession()->get('_year'))
                ->setParameter('ministry',$ministry)
                ->setParameter('validated', true)
                ->getQuery()->getResult();
        }

        if($request->getSession()->get('_commune')){
            $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $request->getSession()->get('_commune')]);
            $entite=$commune;
            $actions = $this->getDoctrine()
                ->getRepository(Action::class)
                ->createQueryBuilder('a')
                ->select('a')
                ->join('a.programm' ,'p')
                ->andWhere('a.year = :year')
                ->andWhere('p.commune = :commune')
                ->andWhere('a.validated = :validated')
                ->setParameter('year', $request->getSession()->get('_year'))
                ->setParameter('commune',$commune)
                ->setParameter('validated', true)
                ->getQuery()->getResult();
        }

        $form = $this->createForm(MinistryCommuneSelType::class, null, [
            'sessionYear' => $request->getSession()->get('_year'),
            'sessionMinistry' => $request->getSession()->get('_ministry'),
            'sessionCommune' => $request->getSession()->get('_commune'),
            'sessionDepartement' => $request->getSession()->get('_departement'),
        ]);

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            if($request->getSession()->get('_departement')){
                $type='commune';
            }else{
                $type = $form->get('type')->getData();
            }
            $entite = $form->get('entite')->getData();
            if($type=='ministere' && $entite){
                $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $entite->getId()]);
                $actions = $this->getDoctrine()
                ->getRepository(Action::class)
                ->createQueryBuilder('a')
                ->select('a')
                ->join('a.programm' ,'p')
                ->andWhere('a.year = :year')
                ->andWhere('p.ministry = :ministry')
                ->andWhere('a.validated = :validated')
                ->setParameter('year', $request->getSession()->get('_year'))
                ->setParameter('ministry',$ministry)
                ->setParameter('validated', true)
                ->getQuery()->getResult();
            }else if($type=='commune' && $entite){
                $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $entite->getId()]);
                $actions = $this->getDoctrine()
                ->getRepository(Action::class)
                ->createQueryBuilder('a')
                ->select('a')
                ->join('a.programm' ,'p')
                ->andWhere('a.year = :year')
                ->andWhere('p.commune = :commune')
                ->andWhere('a.validated = :validated')
                ->setParameter('year', $request->getSession()->get('_year'))
                ->setParameter('commune',$commune)
                ->setParameter('validated', true)
                ->getQuery()->getResult();
            }
        }

        return $this->render('plan/action_list.html.twig', [
            'actions' => $actions,
            'form' => $form->createView(),
            'entite' => $entite
        ]);
    }

    /**
     * @Route("/app/actions/new", name="app_action_new")
     * 
     */
	 //@IsGranted({"ROLE_ADMIN", "ROLE_MANAGER", "ROLE_DPP", "ROLE_FOCAL"})
    public function newActionAction(Request $request)
    {
        $action = new Action();

        $form = $this->createForm(ActionType::class, $action, [
            'sessionYear' => $request->getSession()->get('_year'),
            'sessionMinistry' => $request->getSession()->get('_ministry'),
            'sessionCommune' => $request->getSession()->get('_commune'),
            'defaultType' => null,
            'defaultEntite' => null
        ]);

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $action = $form->getData();
            $action->setYear($request->getSession()->get('_year'));
            $action->setCreatedBy($this->getUser());
            $action->setValidatedBy($this->getUser());
            $action->setValidated(true);

            $this->getDoctrine()->getManagerForClass(Action::class)->persist($action);
            $this->getDoctrine()->getManagerForClass(Action::class)->flush();


            $this->addFlash('success', 'Action ajoutée avec succès');

            return $this->redirectToRoute('app_actions');
        }

        return $this->render('plan/action_form.html.twig', [
            'action' => $action,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/actions/{id}", name="app_action")
     * 
     */
	 //@IsGranted({"ROLE_ADMIN", "ROLE_MANAGER", "ROLE_DPP", "ROLE_FOCAL"})
    public function actionAction($id, Request $request)
    {
        $action = $this->getDoctrine()
            ->getRepository(Action::class)
            ->findOneBy(['id' => $id]);
        
        if($action->getProgramm()->getCommune()){
            $form = $this->createForm(ActionType::class, $action, [
                'sessionYear' => $request->getSession()->get('_year'),
                'sessionMinistry' => $request->getSession()->get('_ministry'),
                'sessionCommune' => $request->getSession()->get('_commune'),
                'defaultType' => 'commune',
                'defaultEntite' => $action->getProgramm()->getCommune(),
            ]);
        }
        if($action->getProgramm()->getMinistry()){
            $form = $this->createForm(ActionType::class, $action, [
                'sessionYear' => $request->getSession()->get('_year'),
                'sessionMinistry' => $request->getSession()->get('_ministry'),
                'sessionCommune' => $request->getSession()->get('_commune'),
                'defaultType' => 'ministere',
                'defaultEntite' => $action->getProgramm()->getMinistry(),
            ]);
        }

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $action = $form->getData();
            $action->setUpdatedBy($this->getUser());

            $this->getDoctrine()->getManagerForClass(Action::class)->flush();

            $this->addFlash('success', 'Action modifiée avec succès');

            return $this->redirectToRoute('app_actions');
        }

        return $this->render('plan/action_form.html.twig', [
            'action' => $action,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/activities", name="app_activities")
     */
    public function activitiesAction(Request $request)
    {
        $activities = [];
        $entite=null;

        if($request->getSession()->get('_ministry')){
            $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $request->getSession()->get('_ministry')]);
            $entite=$ministry;
            $activities = $this->getDoctrine()
                ->getRepository(Activity::class)
                ->createQueryBuilder('y')
                ->select('y')
                ->join('y.action' ,'a')
                ->join('a.programm' ,'p')
                ->andWhere('a.year = :year')
                ->andWhere('p.ministry = :ministry')
                ->andWhere('a.validated = :validated')
                ->setParameter('year', $request->getSession()->get('_year'))
                ->setParameter('ministry',$ministry)
                ->setParameter('validated', true)
                ->getQuery()->getResult();
        }

        if($request->getSession()->get('_commune')){
            $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $request->getSession()->get('_commune')]);
            $entite=$commune;
            $activities = $this->getDoctrine()
                ->getRepository(Activity::class)
                ->createQueryBuilder('y')
                ->select('y')
                ->join('y.action' ,'a')
                ->join('a.programm' ,'p')
                ->andWhere('a.year = :year')
                ->andWhere('p.commune = :commune')
                ->andWhere('a.validated = :validated')
                ->setParameter('year', $request->getSession()->get('_year'))
                ->setParameter('commune',$commune)
                ->setParameter('validated', true)
                ->getQuery()->getResult();
        }

        $form = $this->createForm(MinistryCommuneSelType::class, null, [
            'sessionYear' => $request->getSession()->get('_year'),
            'sessionMinistry' => $request->getSession()->get('_ministry'),
            'sessionCommune' => $request->getSession()->get('_commune'),
            'sessionDepartement' => $request->getSession()->get('_departement'),
        ]);

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            if($request->getSession()->get('_departement')){
                $type='commune';
            }else{
                $type = $form->get('type')->getData();
            }
            $entite = $form->get('entite')->getData();
            if($type=='ministere' && $entite){
                $ministry = $this->getDoctrine()->getRepository(Ministry::class)->findOneBy(['id' => $entite->getId()]);
                $activities = $this->getDoctrine()
                ->getRepository(Activity::class)
                ->createQueryBuilder('y')
                ->select('y')
                ->join('y.action' ,'a')
                ->join('a.programm' ,'p')
                ->andWhere('a.year = :year')
                ->andWhere('p.ministry = :ministry')
                ->andWhere('a.validated = :validated')
                ->setParameter('year', $request->getSession()->get('_year'))
                ->setParameter('ministry',$ministry)
                ->setParameter('validated', true)
                ->getQuery()->getResult();
            }
            if($type=='commune' && $entite){
                $commune = $this->getDoctrine()->getRepository(Commune::class)->findOneBy(['id' => $entite->getId()]);
                $activities = $this->getDoctrine()
                ->getRepository(Activity::class)
                ->createQueryBuilder('y')
                ->select('y')
                ->join('y.action' ,'a')
                ->join('a.programm' ,'p')
                ->andWhere('a.year = :year')
                ->andWhere('p.commune = :commune')
                ->andWhere('a.validated = :validated')
                ->setParameter('year', $request->getSession()->get('_year'))
                ->setParameter('commune',$commune)
                ->setParameter('validated', true)
                ->getQuery()->getResult();
            }
            
        }

        return $this->render('plan/activity_list.html.twig', [
            'activities' => $activities,
            'form' => $form->createView(),
            'entite' => $entite
        ]);
    }

    /**
     * @Route("/app/activities/new", name="app_activity_new")
     * 
     */
	 //@IsGranted({"ROLE_ADMIN", "ROLE_MANAGER", "ROLE_DPP", "ROLE_FOCAL"})
    public function newActivityAction(Request $request)
    {
        $activity = new Activity();

        $form = $this->createForm(ActivityType::class, $activity, [
            'sessionYear' => $request->getSession()->get('_year'),
            'sessionMinistry' => $request->getSession()->get('_ministry'),
            'sessionCommune' => $request->getSession()->get('_commune'),
        ]);

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $activity = $form->getData();
            $activity->setYear($request->getSession()->get('_year'));
            $activity->setCreatedBy($this->getUser());
            $activity->setValidatedBy($this->getUser());
            $activity->setValidated(true);

            $activity->getAction()->setTreated(PlanUtils::checkActionTreated($activity->getAction()));

            $this->getDoctrine()->getManagerForClass(Activity::class)->persist($activity);
            $this->getDoctrine()->getManagerForClass(Activity::class)->flush();

            $this->addFlash('success', 'Activité ajoutée avec succès');
            
            return $this->redirectToRoute('app_activity_new');
        }

        return $this->render('plan/activity_form.html.twig', [
            'activity' => $activity,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/activities/{id}", name="app_activity")
     * 
     */
	 //@IsGranted({"ROLE_ADMIN", "ROLE_MANAGER", "ROLE_DPP", "ROLE_FOCAL"})
    public function activityAction($id, Request $request)
    {
        $activity = $this->getDoctrine()
            ->getRepository(Activity::class)
            ->findOneBy(['id' => $id]);
        $programm = $activity->getAction()->getProgramm();
        
        if($activity && $activity->getDirection()->getCommune()){
            $form = $this->createForm(ActivityType::class, $activity, [
                'sessionYear' => $request->getSession()->get('_year'),
                'sessionMinistry' => $request->getSession()->get('_ministry'),
                'sessionCommune' => $request->getSession()->get('_commune'),
                'defaultType' => 'commune',
                'defaultEntite' => $activity->getDirection()->getCommune(),
                'defaultProgramm' => $programm,
            ]);
        }else if($activity &&  $activity->getDirection()->getMinistry()){
            $form = $this->createForm(ActivityType::class, $activity, [
                'sessionYear' => $request->getSession()->get('_year'),
                'sessionMinistry' => $request->getSession()->get('_ministry'),
                'sessionCommune' => $request->getSession()->get('_commune'),
                'defaultType' =>'ministere',
                'defaultEntite' => $activity->getDirection()->getMinistry(),
                'defaultProgramm' => $programm,
            ]);
        }

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $activity = $form->getData();
            $activity->setUpdatedBy($this->getUser());

            $this->getDoctrine()->getManagerForClass(Activity::class)->flush();

            $this->addFlash('success', 'Activité modifiée avec succès');

            return $this->redirectToRoute('app_activities');
        }

        return $this->render('plan/activity_form.html.twig', [
            'activity' => $activity,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/notation/{id}", name="app_notations", defaults={"id"=0})
     */
    //* @IsGranted({"ROLE_ADMIN", "ROLE_MANAGER", "ROLE_DDD"})
    public function notationAction($id=0, Request $request, SettingsManager $settingsManager)
    {

      
        /*if (!($this->isGranted('ROLE_MANAGER') or $this->isGranted('ROLE_DDD'))) {
            return $this->redirectToRoute('app_evaluation', ['id' => 0]);
        }*/

        $show = !($this->isGranted('ROLE_MANAGER') or $this->isGranted('ROLE_DDD'));

        $collection = $request->request->get('notation_collection_model');
        $postId = isset($collection['action']) ? $collection['action'] : null;
        if ($postId and ($postId != $id)) {
            return $this->redirectToRoute('app_notations', ['id' => $postId]);
        }

        $ministry = null;
        $commune = null;
        $activities = [];
        $activityIds = [];
        $action = $this->getDoctrine()
            ->getRepository(Action::class)
            ->findOneBy(['id' => (int)$id]);

        $notationCollection = New NotationCollectionModel($action);

        if ($action) {
            $limit = 0;
            foreach ($action->getActivities() as $activity) {
                if (($activity->getValidated() == true) and ($activity->getTreated() == false)) {
                    if ($limit < 200) {
                        $model = New NotationModel();
                        $model->activity = $activity;
                        $activityIds[] = $activity->getId();
                        $notationCollection->addNotationModel($model);

                        $activities[] = $activity;
                    }
                    $limit += 1;
                }
                
            }
            $ministry = $action->getProgramm()->getMinistry();
            $commune = $action->getProgramm()->getCommune();
        }

        if($commune){
            $form = $this->createForm(NotationCollectionModelType::class, $notationCollection, [
                'sessionYear' => $request->getSession()->get('_year'),
                'sessionMinistry' => $request->getSession()->get('_ministry'),
                'sessionCommune' => $request->getSession()->get('_commune'),
                'sessionDepartement' => $request->getSession()->get('_departement'),
                'commune' => $commune,
                'grouping' => 'arrondissement',
                'minRate' => $this->getRates('minRate'),
                'maxRate' => $this->getRates('maxRate'),
                'allRate' => $this->getRates('allRate'),  
                'weighting' => $this->getWeighting($ministry, $settingsManager),
                'show' => $show,
            ]);
        }else{
            $form = $this->createForm(NotationCollectionModelType::class, $notationCollection, [
                'sessionYear' => $request->getSession()->get('_year'),
                'sessionMinistry' => $request->getSession()->get('_ministry'),
                'sessionCommune' => $request->getSession()->get('_commune'),
                'sessionDepartement' => $request->getSession()->get('_departement'),
                'commune' => null,
                'enabledPoste' => $ministry ? $ministry->getPoste() : false,
                'grouping' => $ministry ? $ministry->getGrouping() : 'department',
                'minRate' => $this->getRates('minRate'),
                'maxRate' => $this->getRates('maxRate'),
                'allRate' => $this->getRates('allRate'),  
                'weighting' => $this->getWeighting($ministry, $settingsManager),
                'show' => $show,
            ]);
        }
        
        
        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            
            $notationModels = $form->get('notationModels')->getData();
            //   dd($notationModels);
            foreach ($notationModels as $model) {
;                
                if ($model->treated == true) {
                    $notation = New Notation();
                    $notation->setActivity($model->activity);
                    
                    $notation->setNature($model->nature);
                    $notation->setTarget($model->target);
                    if($model->communes){
                        $notation->setNbOfLocation(count($model->communes));
                        foreach ($model->communes as $commune) {
                            $notation->addCommune($commune);
                        }
                        foreach ($model->postes as $poste) {
                            $notation->addPoste($poste);
                        }
                    }else if($model->arrondissements){
                        $notation->setNbOfLocation(count($model->arrondissements));
                        foreach ($model->arrondissements as $arrondissement) {
                            $notation->addArrondissement($arrondissement);
                        }
                    }
                    $notation->setTargetRate($model->targetRate);
                    $notation->setLocationRate($model->locationRate);
                    $notation->setSensitivity($model->sensitivity);
                    $appreciation = $this->getDoctrine()
                        ->getRepository(Appreciation::class)
                        ->findOneByValue($model->sensitivity);
                    $notation->setAppreciation($appreciation);

                    foreach ($model->indicators as $indicator) {
                        $notation->addIndicator($indicator);
                    }
                    
                    $this->getDoctrine()->getManagerForClass(Notation::class)->persist($notation);

                    $activity = $model->activity;
                    $activity->setTreated(true);
                }
            }
            $action->setTreated(PlanUtils::checkActionTreated($action));

            $this->getDoctrine()->getManagerForClass(Notation::class)->flush();
            
            return $this->redirectToRoute('app_notations', ['id'=>$action->getId()]);
        }
        
        return $this->render('plan/notation_form_ia.html.twig', [
            'activities' => $activities,
            'activityIds' => $activityIds,
            'form' => $form->createView(),
            'actionId' => $action ? $action->getId() : 0,
            'grouping' => $commune ? 'arrondissement' : 'department',
        ]);
    }

    /**
     * @Route("/app/evaluation/{id}", name="app_evaluation")
     */
    //* @IsGranted({"ROLE_ADMIN", "ROLE_MANAGER", "ROLE_FOCAL"})
    public function planEvaluationAction($id, Request $request)
    {
        $notations = [];
        $appreciation = '---';
        $sensitivity = 0;
        $amount = 0;

        $evaluation = $request->request->get('evaluation');
        $postId = isset($evaluation['action']) ? $evaluation['action'] : null;
        if ($postId and ($postId != $id)) {
            return $this->redirectToRoute('app_evaluation', ['id' => $postId]);
        }

        $action = $this->getDoctrine()
            ->getRepository(Action::class)
            ->findOneBy(['id' => (int)$id]);

        
        $commune = $action ? $action->getProgramm()->getCommune() : null;

        $notationCollection = New NotationCollectionModel($action);

        if ($action) {
            foreach ($action->getActivities() as $activity) {
                if ($activity->getValidated() == true) {
                    foreach ($activity->getNotations() as $notation) {
                        $notations[] = $notation;
                    }
                }
            }
        }

        $form = $this->createForm(EvaluationType::class, $notationCollection, [
            'sessionYear' => $request->getSession()->get('_year'),
            'sessionMinistry' => $request->getSession()->get('_ministry'),
            'sessionCommune' => $request->getSession()->get('_commune'),
            'sessionDepartement' => $request->getSession()->get('_departement'),
            'defaultEntite' => $commune
        ]);

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            //$ministry = $form->get('ministry')->getData();
            //$programm = $form->get('programm')->getData();
            //$action = $form->get('action')->getData();
            
        }

        return $this->render('plan/evaluation_list.html.twig', [
            'notations' => $notations,
            'form' => $form->createView(),
            'actionId' => $action ? $action->getId() : 0
        ]);
    }

    /**
     * @Route("/app/recommandation/{id}", name="app_recommandation")
     */
    //* @IsGranted({"ROLE_ADMIN", "ROLE_MANAGER", "ROLE_FOCAL"})
   public function recommandationia($id, Request $request, ActivitySuggestionRepository $activitySuggestionRepository)
{
    $notations = [];
    $appreciation = '---';
    $sensitivity = 0;
    $amount = 0;

    $evaluation = $request->request->get('evaluation');
    $postId = isset($evaluation['action']) ? $evaluation['action'] : null;
    if ($postId and ($postId != $id)) {
        return $this->redirectToRoute('app_validations_list', ['id' => $postId]);
    }

    $action = $this->getDoctrine()
        ->getRepository(Action::class)
        ->findOneBy(['id' => (int)$id]);

    $commune = $action ? $action->getProgramm()->getCommune() : null;

    $notationCollection = new NotationCollectionModel($action);

    if ($action) {
        foreach ($action->getActivities() as $activity) {
            if ($activity->getValidated() == true) {
                foreach ($activity->getNotations() as $notation) {
                    $notations[] = $notation;
                }
            }
        }
    }

    // --- récupération des suggestions ---
    $suggestions = [];
    foreach ($action->getActivities() as $activity) {
        $suggestion = $activitySuggestionRepository->findOneBy(['activity' => $activity->getId()]);
        $suggestions[$activity->getId()] = $suggestion ? $suggestion->getSuggestion() : null;
    }

    $form = $this->createForm(EvaluationType::class, $notationCollection, [
        'sessionYear' => $request->getSession()->get('_year'),
        'sessionMinistry' => $request->getSession()->get('_ministry'),
        'sessionCommune' => $request->getSession()->get('_commune'),
        'sessionDepartement' => $request->getSession()->get('_departement'),
        'defaultEntite' => $commune
    ]);

    $form->handleRequest($request);
    if ($form->isSubmitted() && $form->isValid()) {
        // traitement éventuel du formulaire
    }

    return $this->render('plan/suggestion_list.html.twig', [
        'notations' => $notations,
        'form' => $form->createView(),
        'actionId' => $action ? $action->getId() : 0,
        'suggestions' => $suggestions, 
    ]);
}

    
    /**
     * @Route("/app/notation/duplicate/{id}", name="app_notation_duplicate")
     * 
     */
	 //@IsGranted({"ROLE_ADMIN", "ROLE_MANAGER"})
    public function duplicateNotationAction($id, Request $request, SettingsManager $settingsManager)
    {
        $activity = $this->getDoctrine()
            ->getRepository(Notation::class)
            ->findOneBy(['id' => $id]) 
            ->getActivity();

        $notation = new Notation();
        $notation->setActivity($activity);

        $ministry = $notation->getActivity()->getAction()->getProgramm()->getMinistry();
        $commune = $notation->getActivity()->getAction()->getProgramm()->getCommune();

        if($commune){
            $form = $this->createForm(NotationType::class, $notation, [
                'enabledPoste' => false,
                'grouping' => 'arrondissement',
                'commune' => $commune,
                'minRate' => $this->getRates('minRate'),
                'maxRate' => $this->getRates('maxRate'),
                'allRate' => $this->getRates('allRate'),  
                'weighting' => $this->getWeighting($ministry, $settingsManager),
            ]);
        }else{
            $form = $this->createForm(NotationType::class, $notation, [
                'enabledPoste' => $ministry ? $ministry->getPoste() : false,
                'grouping' => $ministry ? $ministry->getGrouping() : 'department',
                'commune' => null,
                'minRate' => $this->getRates('minRate'),
                'maxRate' => $this->getRates('maxRate'),
                'allRate' => $this->getRates('allRate'),  
                'weighting' => $this->getWeighting($ministry, $settingsManager),
            ]);
        }

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $notation = $form->getData();
            $treated = $form->get('treated')->getData();
            
            if ($treated == true) {
                $appreciation = $this->getDoctrine()
                    ->getRepository(Appreciation::class)
                    ->findOneByValue($notation->getSensitivity());
                $notation->setAppreciation($appreciation);
                $notation->setNbOfLocation(count($notation->getCommunes()));
                $notation->setDuplicated(true);

                $this->getDoctrine()->getManagerForClass(Notation::class)->persist($notation);
            }

            $this->getDoctrine()->getManagerForClass(Notation::class)->flush();

            $actionId = $notation->getActivity()->getAction()->getId();
            return $this->redirectToRoute('app_evaluation', ['id' => $actionId]);
        }

        return $this->render('plan/evaluation_form.html.twig', [
            'notation' => $notation,
            'grouping' => $commune ? 'arrondissement' : 'department',
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/notation/edit/{id}", name="app_notation_edit")
     * 
     */
	 //@IsGranted({"ROLE_ADMIN", "ROLE_MANAGER", "ROLE_FOCAL"})
	 
    public function editNotationAction($id, Request $request, SettingsManager $settingsManager)
    {
        $notation = $this->getDoctrine()
            ->getRepository(Notation::class)
            ->findOneBy(['id' => $id]);

        $ministry = $notation->getActivity()->getAction()->getProgramm()->getMinistry();
        $commune = $notation->getActivity()->getAction()->getProgramm()->getCommune();

        if($commune){
            $grouping='arrondissement';
            $form = $this->createForm(NotationType::class, $notation, [
                'enabledPoste' => false,
                'grouping' => 'arrondissement',
                'commune' => $commune,
                'minRate' => $this->getRates('minRate'),
                'maxRate' => $this->getRates('maxRate'),
                'allRate' => $this->getRates('allRate'),  
                'weighting' => $this->getWeighting($ministry, $settingsManager),
            ]);
        }else{
            $grouping=$ministry ? $ministry->getGrouping() : 'department';
            $form = $this->createForm(NotationType::class, $notation, [
                'enabledPoste' => $ministry ? $ministry->getPoste() : false,
                'grouping' => $ministry ? $ministry->getGrouping() : 'department',
                'commune' => null,
                'minRate' => $this->getRates('minRate'),
                'maxRate' => $this->getRates('maxRate'),
                'allRate' => $this->getRates('allRate'),  
                'weighting' => $this->getWeighting($ministry, $settingsManager),
            ]);
        }

        /*$form = $this->createForm(NotationType::class, $notation, [
            'enabledPoste' => $ministry ? $ministry->getPoste() : false,
            'grouping' => $ministry ? $ministry->getGrouping() : 'department',
            'minRate' => $this->getRates('minRate'),
            'maxRate' => $this->getRates('maxRate'),
            'allRate' => $this->getRates('allRate'),  
            'weighting' => $this->getWeighting($ministry, $settingsManager),
        ]);*/

        $form->handleRequest($request);
        if ($form->isSubmitted() && $form->isValid()) {
            $notation = $form->getData();
            $treated = $form->get('treated')->getData();
            
            if ($treated == true) {
                $appreciation = $this->getDoctrine()
                    ->getRepository(Appreciation::class)
                    ->findOneByValue($notation->getSensitivity());
                $notation->setAppreciation($appreciation);
                $notation->setNbOfLocation(count($notation->getCommunes()));

                $this->getDoctrine()->getManagerForClass(Notation::class)->persist($notation);
            }

            $this->getDoctrine()->getManagerForClass(Notation::class)->flush();

            $actionId = $notation->getActivity()->getAction()->getId();
            return $this->redirectToRoute('app_evaluation', ['id' => $actionId]);
        }

        return $this->render('plan/evaluation_form.html.twig', [
            'notation' => $notation,
            'grouping' =>$grouping,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/app/notation/delete/{id}", name="app_notation_delete")
     */
    public function deleteNotationtionAction($id, Request $request)
    {
        $notation = $this->getDoctrine()
            ->getRepository(Notation::class)
            ->findOneBy(['id' => $id]);

        $this->getDoctrine()->getManagerForClass(Notation::class)->remove($notation);
        $this->getDoctrine()->getManagerForClass(Notation::class)->flush();

        $actionId = $notation->getActivity()->getAction()->getId();
        return $this->redirectToRoute('app_evaluation', ['id' => $actionId]);
    }
}