<?php

namespace App\Repository;

use App\Entity\Address;
use Doctrine\DBAL\Connection;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\Persistence\ManagerRegistry;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;

/**
 * @extends ServiceEntityRepository<Address>
 */
class AddressRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry, private EntityManagerInterface $em)
    {
        parent::__construct($registry, Address::class);
    }
    public function getPaginatedAddresses(
        int $page,                     // Numéro de la page actuelle pour la pagination.
        int $itemsPerPage,             // Nombre d'éléments par page.
        ?string $orderBy,              // Champ par lequel trier les résultats (optionnel).
        ?string $direction,            // Direction du tri (ASC ou DESC).
        ?string $address         // Recherche par nom de department.
    ): array {
        // Création du QueryBuilder avec les IDs ajoutés
        $queryBuilder = $this->createQueryBuilder('a')
            ->select('CONCAT(a.numero, \' \', a.nomVoie, \', \', a.codePostal, \' \') AS address'); // Concatenate fields correctly

        if ($address !== null) {
            $sanitizedPatName = trim($address);
            $nameParts = explode(' ', $sanitizedPatName);
            foreach ($nameParts as $part) {
                $queryBuilder->andWhere(
                    $queryBuilder->expr()->orX(
                        $queryBuilder->expr()->like('a.nomVoie', ':part'),
                        $queryBuilder->expr()->like('a.codePostal', ':part'),
                    )
                )
                    ->setParameter('part', '%' . $part . '%');
            }
        }
        // Tri
        if ($orderBy == 'address' && $direction) {
            $queryBuilder->orderBy('address', $direction);
        }

        if ($itemsPerPage && $page) {
            // Compter le nombre total d'éléments
            $totalItems = count($queryBuilder->getQuery()->getArrayResult());
            // Calculer le nombre total de pages
            $totalPages = (int) ceil($totalItems / $itemsPerPage);
            // Pagination
            $queryBuilder
                ->setFirstResult(($page - 1) * $itemsPerPage)
                ->setMaxResults($itemsPerPage);
        }

        // Exécution de la requête et récupération des résultats sous forme d'un tableau associatif
        $results = $queryBuilder->getQuery()->getArrayResult();

        // Retourner les informations des patients sous forme de réponse JSON
        if ($itemsPerPage && $page) {
            $response = [
                'addresses' => array($results),
                'pagination' => [
                    'totalItems' => $totalItems,
                    'itemsPerPage' => $itemsPerPage,
                    'currentPage' => $page,
                    'totalPages' => $totalPages,
                ],
            ];
        } else {
            $response = [
                'addresses' => array($results),
            ];
        }

        return $response;
    }


    public function findLatAndLong(?array $address)
    {
        $queryBuilder = $this->createQueryBuilder('a')
            ->select('a.lat', 'a.lon');
        // Filtre sur le nom de department

        if ($address['nomVoie'] !== null) {
            $sanitizedNomVoie = trim($address['nomVoie']);
            $nameParts = explode(' ', $sanitizedNomVoie);
            foreach ($nameParts as $part) {
                $queryBuilder->andWhere(
                    $queryBuilder->expr()->orX(
                        $queryBuilder->expr()->like('a.nomVoie', ':part')
                    )
                )->setParameter('part', '%' . $part . '%');
            }
        }
        if ($address['codePostal'] !== null) {
            $queryBuilder->andWhere('a.codePostal = :codePostal')
                ->setParameter('codePostal', $address['codePostal']);
        }
        if ($address['numero'] !== null) {
            $queryBuilder->andWhere('a.numero = :numero')
                ->setParameter('numero', $address['numero']);
        }
        $queryBuilder->setMaxResults(1);
        $results = $queryBuilder->getQuery()->getOneOrNullResult();
        // Vérification si des résultats ont été trouvés
        return [
            'lat' => $results['lat'] ?? null,
            'lon' => $results['lon'] ?? null,
        ];
    }
    public function fetchMunicipalities2($postalCode = null)
    {
        $qb = $this->createQueryBuilder('a')
            ->select('a.codeInsee, a.nomCommune , a.codePostal');

        if ($postalCode !== null) {
            $sanitizedCodePostal = trim($postalCode);
            $qb->andWhere(
                $qb->expr()->like('a.codePostal', ':postalCode')
            )->setParameter('postalCode', '%' . $sanitizedCodePostal . '%');
        }
        $qb->groupBy('a.nomCommune');
        return $qb->getQuery()->getArrayResult();
    }
    public function fetchMunicipalities(?string $postalCode = null): array
    {
        $conn = $this->em->getConnection();

        $sql = 'SELECT DISTINCT nom_commune AS nomCommune, code_insee AS codeInsee, code_postal AS codePostal
                FROM address';

        $params = [];
        if ($postalCode !== null) {
            $sql .= ' WHERE code_postal LIKE :postalCode';
            $params['postalCode'] = $postalCode . '%';
        }

        $sql .= ' LIMIT 10'; // Optional: limit for performance

        return $conn->executeQuery($sql, $params)->fetchAllAssociative();
    }


    // Cette méthode récupère des adresses basées sur une recherche textuelle, un code postal et un code de ville.
    public function fetchAddresses2(
        ?string $q = null,
        ?string $postalCode = null,
        ?string $cityCode = null,
        int     $limit = 10,
        ?int $trackNumber
    ): array {
        $qb = $this->createQueryBuilder('a')
            ->select('a.nomVoie', 'a.codePostal', 'a.codeInsee', 'a.numero', 'a.lat', 'a.lon', 'a.nomCommune');
        // Filtre sur le nom de department
        if ($q !== null) {
            $sanitizedAddress = trim($q);
            $nameParts = explode(' ', $sanitizedAddress);
            foreach ($nameParts as $part) {
                $qb->andWhere(
                    $qb->expr()->like('a.nomVoie', ':part')
                )->setParameter('part', '%' . $part . '%');
            }
        }
        if ($postalCode !== null) {
            $sanitizedCodePostal = trim($postalCode);
            $qb->andWhere(
                $qb->expr()->like('a.codePostal', ':postalCode')
            )->setParameter('postalCode', '%' . $sanitizedCodePostal . '%');
        }
        if ($cityCode !== null) {
            $sanitizedCodeInsee = trim($cityCode);
            $qb->andWhere(
                $qb->expr()->like('a.codeInsee', ':codeInsee')
            )->setParameter('codeInsee', '%' . $sanitizedCodeInsee . '%');
        }
        if ($trackNumber !== null) {
            $qb->andWhere('a.numero = :numero')
                ->setParameter('numero', $trackNumber);
        }
        $qb->setMaxResults($limit);
        return $qb->getQuery()->getArrayResult();
    }
    public function fetchAddresses(
        ?string $q = null,
        ?string $postalCode = null,
        ?string $cityCode = null,
        int     $limit = 10,
        ?int    $trackNumber = null
    ): array {
        $conn = $this->em->getConnection();

        $sql = 'SELECT nom_voie AS nomVoie , lat , lon
                FROM address
                WHERE 1=1';

        $params = [];
        $conditions = [];

        if ($q !== null) {
            $sanitized = trim($q);
            $parts = explode(' ', $sanitized);
            foreach ($parts as $i => $part) {
                $key = ":q_$i";
                $conditions[] = "nom_voie LIKE $key";
                $params[$key] = '%' . $part . '%';
            }
        }

        if ($postalCode !== null) {
            $params[':postalCode'] = $postalCode . '%';
            $conditions[] = 'code_postal LIKE :postalCode';
        }

        if ($cityCode !== null) {
            $params[':cityCode'] = $cityCode . '%';
            $conditions[] = 'code_insee LIKE :cityCode';
        }

        if ($trackNumber !== null) {
            $params[':numero'] = $trackNumber;
            $conditions[] = 'numero = :numero';
        }

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

        $sql .= ' GROUP BY nomVoie';
        $sql .= ' LIMIT ' . (int)$limit;

        return $conn->prepare($sql)->executeQuery($params)->fetchAllAssociative();
    }
    public function fetchTrackNumber(
        ?string $postalCode,
        ?string $cityCode,
        int     $limit = 10,
        ?string    $trackName
    ): array {
        $conn = $this->em->getConnection();

        $sql = 'SELECT numero , lat , lon
                FROM address
                WHERE 1=1';

        $params = [];
        $conditions = [];

        if ($trackName !== null) {
            $sanitized = trim($trackName);
            $parts = explode(' ', $sanitized);
            foreach ($parts as $i => $part) {
                $key = ":trackName_$i";
                $conditions[] = "nom_voie LIKE $key";
                $params[$key] = '%' . $part . '%';
            }
        }

        if ($postalCode !== null) {
            $params[':postalCode'] = $postalCode . '%';
            $conditions[] = 'code_postal LIKE :postalCode';
        }

        if ($cityCode !== null) {
            $params[':cityCode'] = $cityCode . '%';
            $conditions[] = 'code_insee LIKE :cityCode';
        }

        if (!empty($conditions)) {
            $sql .= ' AND ' . implode(' AND ', $conditions);
        }
        $sql .= ' GROUP BY numero';
        $sql .= ' LIMIT ' . (int)$limit;

        return $conn->prepare($sql)->executeQuery($params)->fetchAllAssociative();
    }
    // public function fetchFullAddresses(
    //     ?string $address = null,
    //     int     $limit = 10,
    // ): array {
    //     $conn = $this->em->getConnection();

    //     $sql = 'SELECT nom_voie as nomVoie , lat , lon , numero , nom_commune as nomCommune , code_insee as codeInsee , code_postal as codePostal
    //             FROM address
    //             WHERE 1=1';

    //     $params = [];
    //     $conditions = [];

    //     if ($address !== null) {
    //         $sanitized = trim($address);
    //         $parts = explode(' ', $sanitized);

    //         foreach ($parts as $i => $part) {
    //             $key = ":nom_voie_$i";
    //             $conditions[] = "nom_voie LIKE $key";
    //             $params[$key] = '%' . $part . '%';
    //         }

    //         $params += [
    //             ':postalCode' => $sanitized . '%',
    //             ':cityCode'   => $sanitized . '%',
    //             ':numero'     => $sanitized,
    //         ];

    //         $conditions[] = 'code_postal LIKE :postalCode';
    //         $conditions[] = 'code_insee LIKE :cityCode';
    //         $conditions[] = 'numero = :numero';
    //     }

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

    //     $sql .= ' GROUP BY nomVoie';
    //     $sql .= ' LIMIT ' . (int)$limit;
    //     return $conn->prepare($sql)->executeQuery($params)->fetchAllAssociative();
    // }

    public function fetchFullAddresses(?string $address = null, int $limit = 10): array
    {
        if ($address === null || ($address = trim($address)) === '') {
            return [];
        }

        $conn = $this->em->getConnection();

        // Heuristic: if input is digits only (likely postal), search postal FIRST.
        // This prevents slow commune/voie scans and avoids idle timeouts.
        $isDigits = ctype_digit($address);

        if ($isDigits) {
            $rows = $this->searchByPostal($conn, $address, $limit);
            if (!empty($rows)) {
                return $rows;
            }
            // If no postal match, no need to try contains on 26M rows; bail fast.
            return [];
        }

        // Non-numeric: try communes (fast prefix), then voies (fast prefix), then fallbacks.
        $rows = $this->searchByNomCommune($conn, $address, $limit);
        if (!empty($rows)) {
            return $rows;
        }

        $rows = $this->searchByNomVoie($conn, $address, $limit);
        if (!empty($rows)) {
            return $rows;
        }

        // Last chance: postal if the user mixed letters+digits (e.g., "Paris 75")
        return $this->searchByPostal($conn, $address, $limit);
    }

    private function baseSelectSql(string $mode): string
    {
        return match ($mode) {
            'commune' => <<<SQL
            SELECT DISTINCT
                nom_commune AS nomCommune,
                code_postal AS codePostal,
                code_insee  AS codeInsee
            FROM address
        SQL,
            'voie' => <<<SQL
            SELECT DISTINCT
                nom_voie AS nomVoie,
                nom_commune AS nomCommune,
                code_postal AS codePostal
            FROM address
        SQL,
            'postal' => <<<SQL
            SELECT DISTINCT
                code_postal AS codePostal,
                nom_commune AS nomCommune
            FROM address
        SQL,
            default => 'SELECT * FROM address',
        };
    }

    /**
     * Commune: try index-friendly prefix first, then (only if needed) contains.
     */
    private function searchByNomCommune(Connection $conn, string $input, int $limit): array
    {
        $first = preg_split('/\s+/', $input)[0] ?? '';
        if ($first !== '') {
            $sqlFast = $this->baseSelectSql('commune')
                . ' WHERE nom_commune LIKE :p COLLATE utf8mb4_general_ci'
                . ' ORDER BY nom_commune ASC LIMIT ' . (int)$limit;

            return $conn->prepare($sqlFast)->executeQuery([
                'p' => $first . '%',
            ])->fetchAllAssociative();

            // if (!empty($rows) || mb_strlen($input) < 3) {
            //     return $rows;
            // }
        } else {
            return [];
        }

        // Fallback (can be slow; still capped by LIMIT)
        // $sqlSlow = $this->baseSelectSql('commune')
        //     . ' WHERE nom_commune LIKE :c COLLATE utf8mb4_general_ci'
        //     . ' ORDER BY nom_commune ASC LIMIT ' . (int)$limit;

        // return $conn->prepare($sqlSlow)->executeQuery([
        //     'c' => '%' . $input . '%',
        // ])->fetchAllAssociative();
    }

    /**
     * Voie: fast prefix on voie; optionally narrow by a commune hint or postal digits found in the input.
     */
    private function searchByNomVoie(Connection $conn, string $input, int $limit): array
    {
        $tokens = preg_split('/\s+/', trim($input));
        $first  = $tokens[0] ?? '';
        $commHint = null;
        foreach ($tokens as $t) {
            if (preg_match('/[A-Za-z]/', $t)) {
                $commHint = $t;
                break;
            }
        }

        $maybePostal = preg_replace('/\D+/', '', $input);
        $hasPostal = $maybePostal !== '';

        $where = ' WHERE nom_voie LIKE :p COLLATE utf8mb4_general_ci';
        $params = ['p' => ($first !== '' ? $first . '%' : '%')];

        if ($commHint && mb_strlen($commHint) >= 3) {
            $where .= ' AND nom_commune LIKE :ch COLLATE utf8mb4_general_ci';
            $params['ch'] = $commHint . '%';
        }

        if ($hasPostal) {
            // Range filter keeps code_postal BTREE usable
            $low  = $maybePostal;
            $high = (string)((int)$maybePostal + 1);
            $where .= ' AND code_postal >= :plow AND code_postal < :phigh';
            $params['plow']  = $low;
            $params['phigh'] = $high;
        }

        $sqlFast = $this->baseSelectSql('voie') . $where
            . ' ORDER BY nom_commune, nom_voie LIMIT ' . (int)$limit;

        return $conn->prepare($sqlFast)->executeQuery($params)->fetchAllAssociative();
        // if (!empty($rows) || mb_strlen($input) < 3) {
        //     return $rows;
        // }

        // Contains fallback (last resort)
        // $sqlSlow = $this->baseSelectSql('voie')
        //     . ' WHERE nom_voie LIKE :v COLLATE utf8mb4_general_ci'
        //     . ' ORDER BY nom_commune, nom_voie LIMIT ' . (int)$limit;

        // return $conn->prepare($sqlSlow)->executeQuery([
        //     'v' => '%' . $input . '%',
        // ])->fetchAllAssociative();
    }

    /**
     * Postal: always use an index-friendly range (never '%...%').
     * Works for '55555' and also for mixed inputs like 'Paris 75'.
     */
    private function searchByPostal(Connection $conn, string $input, int $limit): array
    {
        $prefix = preg_replace('/\D+/', '', $input);
        if ($prefix === '') {
            return [];
        }

        $low  = $prefix;
        $high = (string)((int)$prefix + 1);

        $sql = $this->baseSelectSql('postal')
            . ' WHERE code_postal >= :low AND code_postal < :high'
            . ' ORDER BY code_postal, nom_commune LIMIT ' . (int)$limit;

        return $conn->prepare($sql)->executeQuery([
            'low'  => $low,
            'high' => $high,
        ])->fetchAllAssociative();
    }
}
