<?php
namespace App\Services;

use App\Core\Database;
use PDO;
use Throwable;

final class CommunicationDirectoryService
{
    private PDO $pdo;

    public function __construct()
    {
        $this->pdo=Database::connection();
    }

    public function searchPeople(string $query): array
    {
        $query=trim($query);

        if(mb_strlen($query)<2){
            return [];
        }

        $userId=$this->currentUserId();
        $scope=$this->resolveScope($userId);

        if($scope==='none'){
            return [];
        }

        $results=[];

        foreach($this->searchClients($query,$scope,$userId) as $row){
            $results[]=$row;
        }

        foreach($this->searchFamilyMembers($query,$scope,$userId) as $row){
            $results[]=$row;
        }

        foreach($this->searchProfessors($query,$scope,$userId) as $row){
            $results[]=$row;
        }

        return array_slice($this->deduplicate($results),0,50);
    }

    public function searchActivities(string $query): array
    {
        $query=trim($query);
        $userId=$this->currentUserId();
        $scope=$this->resolveScope($userId);

        if($scope==='none'){
            return [];
        }

        $results=[];

        foreach($this->searchWorkshops($query,$scope,$userId) as $row){
            $results[]=$row;
        }

        foreach($this->searchGroups($query,$scope,$userId) as $row){
            $results[]=$row;
        }

        return array_slice($results,0,50);
    }

    private function searchClients(
        string $query,
        string $scope,
        int $userId
    ): array {
        $where=[
            "c.deleted_at IS NULL",
            "c.statut='actif'",
            "(
                c.nom LIKE :q1
                OR c.prenom LIKE :q2
                OR CONCAT(c.prenom,' ',c.nom) LIKE :q3
                OR CONCAT(c.nom,' ',c.prenom) LIKE :q4
                OR c.code_client LIKE :q5
                OR c.email LIKE :q6
            )"
        ];
        $like='%'.$query.'%';
        $params=[
            'q1'=>$like,'q2'=>$like,'q3'=>$like,
            'q4'=>$like,'q5'=>$like,'q6'=>$like
        ];

        if($scope==='own'){
            $professorId=$this->professorIdForUser($userId);

            if($professorId<=0){
                return [];
            }

            $where[]="EXISTS(
                SELECT 1
                FROM familles f
                INNER JOIN membres_famille mf
                        ON mf.famille_id=f.id
                       AND mf.deleted_at IS NULL
                INNER JOIN inscriptions i
                        ON i.membre_id=mf.id
                       AND i.deleted_at IS NULL
                       AND i.statut NOT IN('annulee','terminee')
                INNER JOIN groupes_cours gc
                        ON gc.atelier_id=i.atelier_id
                       AND gc.deleted_at IS NULL
                INNER JOIN groupe_professeurs gp
                        ON gp.groupe_id=gc.id
                       AND gp.professeur_id=:professor_id
                       AND gp.actif=1
                WHERE f.client_id=c.id
                  AND f.deleted_at IS NULL
                  AND f.actif=1
            )";

            $params['professor_id']=$professorId;
        }

        $stmt=$this->pdo->prepare(
            "SELECT
                c.id,
                c.code_client,
                c.civilite,
                c.nom,
                c.prenom,
                c.email
             FROM clients c
             WHERE ".implode(' AND ',$where)."
             ORDER BY c.nom,c.prenom
             LIMIT 30"
        );
        $stmt->execute($params);

        $results=[];

        foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $row){
            $email=$this->validEmail((string)$row['email']);

            $results[]=[
                'source_table'=>'clients',
                'source_id'=>(int)$row['id'],
                'type'=>'client',
                'type_label'=>'Client titulaire',
                'icon'=>'👤',
                'name'=>trim((string)$row['prenom'].' '.(string)$row['nom']),
                'email'=>$email,
                'code'=>(string)$row['code_client'],
                'subtitle'=>'Client titulaire · '.(string)$row['code_client']
                    .($email!=='' ? ' · '.$email : ' · aucun e-mail enregistré')
            ];
        }

        return $results;
    }

    private function searchFamilyMembers(
        string $query,
        string $scope,
        int $userId
    ): array {
        $like='%'.$query.'%';
        $params=[
            'q1'=>$like,'q2'=>$like,'q3'=>$like,
            'q4'=>$like,'q5'=>$like
        ];
        $where=[
            "mf.deleted_at IS NULL",
            "(
                mf.nom LIKE :q1
                OR mf.prenom LIKE :q2
                OR CONCAT(mf.prenom,' ',mf.nom) LIKE :q3
                OR CONCAT(mf.nom,' ',mf.prenom) LIKE :q4
                OR mf.email LIKE :q5
            )"
        ];

        if($scope==='own'){
            $professorId=$this->professorIdForUser($userId);

            if($professorId<=0){
                return [];
            }

            $where[]="EXISTS(
                SELECT 1
                FROM inscriptions i
                INNER JOIN groupes_cours gc
                        ON gc.atelier_id=i.atelier_id
                       AND gc.deleted_at IS NULL
                INNER JOIN groupe_professeurs gp
                        ON gp.groupe_id=gc.id
                       AND gp.professeur_id=:professor_id
                       AND gp.actif=1
                WHERE i.membre_id=mf.id
                  AND i.deleted_at IS NULL
                  AND i.statut NOT IN('annulee','terminee')
            )";

            $params['professor_id']=$professorId;
        }

        $stmt=$this->pdo->prepare(
            "SELECT
                mf.id,
                mf.famille_id,
                mf.civilite,
                mf.nom,
                mf.prenom,
                mf.email,
                mf.lien_familial,
                mf.participant_ateliers,
                mf.responsable_legal,
                mf.recoit_emails,
                f.reference_famille,
                f.nom_affichage,
                c.id AS client_id,
                c.nom AS client_nom,
                c.prenom AS client_prenom,
                c.email AS client_email
             FROM membres_famille mf
             INNER JOIN familles f
                     ON f.id=mf.famille_id
                    AND f.deleted_at IS NULL
                    AND f.actif=1
             INNER JOIN clients c
                     ON c.id=f.client_id
                    AND c.deleted_at IS NULL
                    AND c.statut='actif'
             WHERE ".implode(' AND ',$where)."
             ORDER BY mf.nom,mf.prenom
             LIMIT 30"
        );
        $stmt->execute($params);

        $results=[];

        foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $row){
            $email='';

            if((int)$row['recoit_emails']===1){
                $email=$this->validEmail((string)$row['email']);
            }

            if($email===''){
                $email=$this->validEmail((string)$row['client_email']);
            }

            $labels=[];

            if((int)$row['participant_ateliers']===1){
                $labels[]='Participant';
            }

            if((int)$row['responsable_legal']===1){
                $labels[]='Responsable légal';
            }

            if(!$labels){
                $labels[]='Membre de famille';
            }

            $results[]=[
                'source_table'=>'membres_famille',
                'source_id'=>(int)$row['id'],
                'type'=>'membre',
                'type_label'=>implode(' / ',$labels),
                'icon'=>'👥',
                'name'=>trim((string)$row['prenom'].' '.(string)$row['nom']),
                'email'=>$email,
                'code'=>(string)$row['reference_famille'],
                'subtitle'=>implode(' / ',$labels)
                    .' · Famille '.(string)$row['nom_affichage']
                    .($email!=='' ? ' · '.$email : ' · aucun e-mail autorisé')
            ];
        }

        return $results;
    }

    private function searchProfessors(
        string $query,
        string $scope,
        int $userId
    ): array {
        $like='%'.$query.'%';
        $params=[
            'q1'=>$like,'q2'=>$like,'q3'=>$like,
            'q4'=>$like,'q5'=>$like,'q6'=>$like
        ];
        $where=[
            "p.deleted_at IS NULL",
            "p.actif=1",
            "(
                p.nom LIKE :q1
                OR p.prenom LIKE :q2
                OR CONCAT(p.prenom,' ',p.nom) LIKE :q3
                OR CONCAT(p.nom,' ',p.prenom) LIKE :q4
                OR p.code_professeur LIKE :q5
                OR p.email LIKE :q6
            )"
        ];

        if($scope==='own'){
            $professorId=$this->professorIdForUser($userId);

            if($professorId<=0){
                return [];
            }

            $where[]="p.id=:professor_id";
            $params['professor_id']=$professorId;
        }

        $stmt=$this->pdo->prepare(
            "SELECT
                p.id,
                p.code_professeur,
                p.civilite,
                p.nom,
                p.prenom,
                p.email,
                p.statut_intervenant
             FROM professeurs p
             WHERE ".implode(' AND ',$where)."
             ORDER BY p.nom,p.prenom
             LIMIT 20"
        );
        $stmt->execute($params);

        $results=[];

        foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $row){
            $email=$this->validEmail((string)$row['email']);

            $results[]=[
                'source_table'=>'professeurs',
                'source_id'=>(int)$row['id'],
                'type'=>'professeur',
                'type_label'=>'Professeur',
                'icon'=>'👨‍🏫',
                'name'=>trim((string)$row['prenom'].' '.(string)$row['nom']),
                'email'=>$email,
                'code'=>(string)($row['code_professeur'] ?? ''),
                'subtitle'=>'Professeur · '.(string)$row['statut_intervenant']
                    .($email!=='' ? ' · '.$email : ' · aucun e-mail enregistré')
            ];
        }

        return $results;
    }

    private function searchWorkshops(
        string $query,
        string $scope,
        int $userId
    ): array {
        $params=[];
        $where=[
            "a.deleted_at IS NULL",
            "a.actif=1"
        ];

        if($query!==''){
            $where[]="(
                a.libelle LIKE :aq1
                OR a.code LIKE :aq2
            )";
            $like='%'.$query.'%';
            $params['aq1']=$like;
            $params['aq2']=$like;
        }

        if($scope==='own'){
            $professorId=$this->professorIdForUser($userId);

            if($professorId<=0){
                return [];
            }

            $where[]="EXISTS(
                SELECT 1
                FROM groupes_cours gc
                INNER JOIN groupe_professeurs gp
                        ON gp.groupe_id=gc.id
                       AND gp.professeur_id=:professor_id
                       AND gp.actif=1
                WHERE gc.atelier_id=a.id
                  AND gc.deleted_at IS NULL
                  AND gc.actif=1
            )";

            $params['professor_id']=$professorId;
        }

        $stmt=$this->pdo->prepare(
            "SELECT
                a.id,
                a.code,
                a.libelle,
                a.jour_semaine,
                a.heure_debut,
                a.heure_fin,
                a.capacite_max
             FROM ateliers a
             WHERE ".implode(' AND ',$where)."
             ORDER BY a.libelle
             LIMIT 30"
        );
        $stmt->execute($params);

        $results=[];

        foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $row){
            $schedule=trim(
                (string)($row['jour_semaine'] ?? '')
                .' '
                .substr((string)($row['heure_debut'] ?? ''),0,5)
            );

            $results[]=[
                'source_table'=>'ateliers',
                'source_id'=>(int)$row['id'],
                'type'=>'atelier',
                'type_label'=>'Atelier',
                'icon'=>'🎨',
                'label'=>(string)$row['libelle'],
                'code'=>(string)$row['code'],
                'value'=>(string)$row['id'],
                'composite_value'=>'atelier:'.(int)$row['id'],
                'subtitle'=>'Atelier · '.(string)$row['code']
                    .($schedule!=='' ? ' · '.$schedule : '')
            ];
        }

        return $results;
    }

    private function searchGroups(
        string $query,
        string $scope,
        int $userId
    ): array {
        $params=[];
        $where=[
            "gc.deleted_at IS NULL",
            "gc.actif=1"
        ];

        if($query!==''){
            $where[]="(
                gc.libelle LIKE :gq1
                OR gc.code LIKE :gq2
                OR a.libelle LIKE :gq3
                OR a.code LIKE :gq4
            )";
            $like='%'.$query.'%';
            $params['gq1']=$like;
            $params['gq2']=$like;
            $params['gq3']=$like;
            $params['gq4']=$like;
        }

        if($scope==='own'){
            $professorId=$this->professorIdForUser($userId);

            if($professorId<=0){
                return [];
            }

            $where[]="EXISTS(
                SELECT 1
                FROM groupe_professeurs gp
                WHERE gp.groupe_id=gc.id
                  AND gp.professeur_id=:professor_id
                  AND gp.actif=1
            )";

            $params['professor_id']=$professorId;
        }

        $stmt=$this->pdo->prepare(
            "SELECT
                gc.id,
                gc.code,
                gc.libelle,
                gc.type_groupe,
                gc.capacite_max,
                a.code AS atelier_code,
                a.libelle AS atelier_libelle
             FROM groupes_cours gc
             INNER JOIN ateliers a
                     ON a.id=gc.atelier_id
                    AND a.deleted_at IS NULL
             WHERE ".implode(' AND ',$where)."
             ORDER BY a.libelle,gc.libelle
             LIMIT 30"
        );
        $stmt->execute($params);

        $results=[];

        foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $row){
            $results[]=[
                'source_table'=>'groupes_cours',
                'source_id'=>(int)$row['id'],
                'type'=>'groupe',
                'type_label'=>'Groupe',
                'icon'=>'👥',
                'label'=>(string)$row['libelle'],
                'code'=>(string)$row['code'],
                'value'=>(string)$row['id'],
                'composite_value'=>'groupe:'.(int)$row['id'],
                'subtitle'=>'Groupe · '.(string)$row['atelier_libelle']
                    .' · '.(string)$row['type_groupe']
            ];
        }

        return $results;
    }

    private function resolveScope(int $userId): string
    {
        if($userId<=0){
            return 'none';
        }

        $roles=$this->roleCodes($userId);

        if(array_intersect(
            $roles,
            ['super_admin','direction','administration','accueil']
        )){
            return 'all';
        }

        if(in_array('professeur',$roles,true)){
            return 'own';
        }

        if($this->permissionGranted($userId,'communications.directory.all')){
            return 'all';
        }

        if($this->permissionGranted($userId,'communications.directory.own')){
            return 'own';
        }

        return 'none';
    }

    private function roleCodes(int $userId): array
    {
        $stmt=$this->pdo->prepare(
            "SELECT r.code
             FROM utilisateur_roles ur
             INNER JOIN roles r
                     ON r.id=ur.role_id
                    AND r.actif=1
                    AND r.deleted_at IS NULL
             WHERE ur.utilisateur_id=:user_id"
        );
        $stmt->execute(['user_id'=>$userId]);

        return array_map('strval',$stmt->fetchAll(PDO::FETCH_COLUMN));
    }

    private function permissionGranted(int $userId,string $code): bool
    {
        $stmt=$this->pdo->prepare(
            "SELECT
                COALESCE(
                    (
                        SELECT up.accordee
                        FROM utilisateur_permissions up
                        INNER JOIN permissions p1 ON p1.id=up.permission_id
                        WHERE up.utilisateur_id=:user_id_1
                          AND p1.code=:code_1
                        LIMIT 1
                    ),
                    (
                        SELECT 1
                        FROM utilisateur_roles ur
                        INNER JOIN role_permissions rp ON rp.role_id=ur.role_id
                        INNER JOIN permissions p2 ON p2.id=rp.permission_id
                        WHERE ur.utilisateur_id=:user_id_2
                          AND p2.code=:code_2
                          AND p2.actif=1
                        LIMIT 1
                    ),
                    0
                )"
        );
        $stmt->execute([
            'user_id_1'=>$userId,
            'code_1'=>$code,
            'user_id_2'=>$userId,
            'code_2'=>$code
        ]);

        return (int)$stmt->fetchColumn()===1;
    }

    private function professorIdForUser(int $userId): int
    {
        $stmt=$this->pdo->prepare(
            "SELECT id
             FROM professeurs
             WHERE utilisateur_id=:user_id
               AND actif=1
               AND deleted_at IS NULL
             LIMIT 1"
        );
        $stmt->execute(['user_id'=>$userId]);

        return (int)$stmt->fetchColumn();
    }

    private function currentUserId(): int
    {
        foreach([
            $_SESSION['utilisateur_id'] ?? null,
            $_SESSION['user_id'] ?? null,
            $_SESSION['auth_user_id'] ?? null,
            $_SESSION['utilisateur']['id'] ?? null,
            $_SESSION['user']['id'] ?? null
        ] as $candidate){
            if(is_numeric($candidate) && (int)$candidate>0){
                return (int)$candidate;
            }
        }

        if(class_exists('\\App\\Core\\Auth')){
            try{
                foreach(['id','userId','currentUserId'] as $method){
                    if(method_exists('\\App\\Core\\Auth',$method)){
                        $value=\App\Core\Auth::$method();

                        if(is_numeric($value) && (int)$value>0){
                            return (int)$value;
                        }
                    }
                }
            }catch(Throwable){
            }
        }

        return 0;
    }

    private function validEmail(string $email): string
    {
        $email=trim($email);

        return filter_var($email,FILTER_VALIDATE_EMAIL)
            ? $email
            : '';
    }

    private function deduplicate(array $rows): array
    {
        $seen=[];
        $result=[];

        foreach($rows as $row){
            $key=(string)$row['source_table'].':'.(int)$row['source_id'];

            if(isset($seen[$key])){
                continue;
            }

            $seen[$key]=true;
            $result[]=$row;
        }

        return $result;
    }
}
