<?php

namespace App\Repository;

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

/**
 * @extends ServiceEntityRepository<Rpps>
 */
class RppsPsRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Rpps::class);
    }

    public function listDepartments()
    {
        return $this->createQueryBuilder('r') // Création d'un query builder pour l'entité Rpps.
            ->select('DISTINCT(r.department)') // Sélectionne uniquement les départements distincts.
            ->where('r.department != :emptyDepartment') // Exclut les départements vides.
            ->setParameter(':emptyDepartment', "") // Paramètre de comparaison pour les départements vides.
            ->getQuery() // Génère la requête.
            ->getSingleColumnResult(); // Exécute la requête et retourne les résultats sous forme d'un tableau.
    }
    
    // src/Repository/ProfessionalRepository.php

    public function fetchPsList(?array $params): array
    {
        try {
            $conn = $this->getEntityManager()->getConnection();

            $sql = 'SELECT 
            ps_fullname AS psFullname,
            office_cedex_cs AS officeCedexCs,
            pp_national_identification AS ppNationalIdentification,
            addr AS addr,
            id AS id,
            phone_number_cs AS phoneNumberCs,
            exercise_name AS exerciseName,
            firstname_exercise AS firstnameExercise,
            department AS department,
            know_how_code AS speciality,
            professional_category_code AS professionalCategoryCode
        FROM rpps_ps
        WHERE 1=1';

            $bindParams = [];
            $conditions = [];

            // Filtres conditionnels
            if (!empty($params['psFullname'])) {
                $conditions[] = 'UPPER(ps_fullname) LIKE :psFullname';
                $bindParams['psFullname'] = '%' . strtoupper(trim($params['psFullname'])) . '%';
            }

            if (!empty($params['ppNationalIdentification'])) {
                $conditions[] = 'UPPER(pp_national_identification) LIKE :ppNationalIdentification';
                $bindParams['ppNationalIdentification'] = '%' . strtoupper(trim($params['ppNationalIdentification'])) . '%';
            }

            if (!empty($params['department'])) {
                $conditions[] = 'UPPER(department) LIKE :department';
                $bindParams['department'] = '%' . strtoupper(trim($params['department'])) . '%';
            }

            if (!empty($params['firstnameExercise'])) {
                $conditions[] = 'UPPER(firstname_exercise) LIKE :firstnameExercise';
                $bindParams['firstnameExercise'] = '%' . strtoupper(trim($params['firstnameExercise'])) . '%';
            }

            if (!empty($params['phoneNumberCs'])) {
                $conditions[] = 'UPPER(phone_number_cs) LIKE :phoneNumberCs';
                $bindParams['phoneNumberCs'] = '%' . strtoupper(trim($params['phoneNumberCs'])) . '%';
            }

            if (!empty($params['exerciseName'])) {
                $conditions[] = 'UPPER(exercise_name) LIKE :exerciseName';
                $bindParams['exerciseName'] = '%' . strtoupper(trim($params['exerciseName'])) . '%';
            }

            if (!empty($params['specialities']) && is_array($params['specialities'])) {
                $i = 0;
                $orConditions = [];

                foreach ($params['specialities'] as $spec) {
                    $key = ':spec' . $i;
                    $bindParams[$key] = '%' . strtoupper(trim($spec)) . '%';
                    $orConditions[] = "UPPER(know_how_code) LIKE $key";
                    $i++;
                }

                if (!empty($orConditions)) {
                    $conditions[] = '(' . implode(' OR ', $orConditions) . ')';
                }
            }

            if (!empty($params['addr'])) {
                $conditions[] = 'UPPER(addr) LIKE :addr';
                $bindParams['addr'] = '%' . strtoupper(trim($params['addr'])) . '%';
            }

            if (!empty($conditions)) {
                $sql .= ' AND ' . implode(' AND ', $conditions);
            }

            // Order By - Champs alignés avec le provider
            if (!empty($params['orderBy']) && !empty($params['orderDirection'])) {
                $allowedFields = ['ps_fullname', 'department', 'pp_national_identification', 'professional_category_code'];
                $allowedDirections = ['ASC', 'DESC'];

                $orderBy = in_array($params['orderBy'], $allowedFields) ? $params['orderBy'] : 'ps_fullname';
                $direction = in_array(strtoupper($params['orderDirection']), $allowedDirections) ? strtoupper($params['orderDirection']) : 'ASC';

                $sql .= " ORDER BY $orderBy $direction";
            } else {
                // Tri par défaut ajouté pour une cohérence
                $sql .= " ORDER BY ps_fullname ASC";
            }

            // Pagination 
            $page = isset($params['page']) ? max(1, (int) $params['page']) : 1;
            $itemsPerPage = isset($params['itemsPerPage']) ? min(max(1, (int) $params['itemsPerPage']), 100) : 10; // ✅ Limite min et max
            $offset = ($page - 1) * $itemsPerPage;

            $sql .= ' LIMIT :limit OFFSET :offset';
            $bindParams['limit'] = $itemsPerPage;
            $bindParams['offset'] = $offset;

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

            // Binding des paramètres de pagination
            $stmt->bindValue('limit', $itemsPerPage, \PDO::PARAM_INT);
            $stmt->bindValue('offset', $offset, \PDO::PARAM_INT);

            // Binding des autres paramètres
            foreach ($bindParams as $key => $value) {
                if (!in_array($key, ['limit', 'offset'])) {
                    $stmt->bindValue($key, $value, \PDO::PARAM_STR);
                }
            }

            return $stmt->executeQuery()->fetchAllAssociative();
        } catch (\Exception $e) {
            throw new \RuntimeException('Erreur lors de l\'exécution de la requête RPPS : ' . $e->getMessage());
        }
    }

}
