<?php
namespace App\Models;

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

final class CommunicationDirectory
{
    private static array $tables=[];
    private static array $columns=[];

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

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

        $like='%'.$query.'%';
        $results=[];

        foreach([
            [
                'tables'=>['adherents','clients'],
                'type'=>'adherent',
                'label'=>'Adhérent',
                'icon'=>'👤'
            ],
            [
                'tables'=>['membres','membres_famille','family_members'],
                'type'=>'membre',
                'label'=>'Membre',
                'icon'=>'👥'
            ],
            [
                'tables'=>['professeurs','intervenants'],
                'type'=>'professeur',
                'label'=>'Professeur / intervenant',
                'icon'=>'👨‍🏫'
            ],
        ] as $family){
            foreach($family['tables'] as $table){
                if(!self::tableExists($table)){
                    continue;
                }

                if(
                    !self::columnExists($table,'nom')
                    ||
                    !self::columnExists($table,'prenom')
                ){
                    continue;
                }

                $select=['id','nom','prenom'];

                foreach(self::emailColumns($table) as $column){
                    $select[]=$column;
                }

                foreach([
                    'code_adherent',
                    'code_client',
                    'code_professeur',
                    'code_intervenant'
                ] as $column){
                    if(self::columnExists($table,$column)){
                        $select[]=$column;
                    }
                }

                $where=[
                    "nom LIKE :q",
                    "prenom LIKE :q",
                    "CONCAT(prenom,' ',nom) LIKE :q",
                    "CONCAT(nom,' ',prenom) LIKE :q",
                ];

                foreach([
                    'code_adherent',
                    'code_client',
                    'code_professeur',
                    'code_intervenant'
                ] as $column){
                    if(self::columnExists($table,$column)){
                        $where[]="`{$column}` LIKE :q";
                    }
                }

                $sql="SELECT ".implode(',',$select)."
                      FROM `{$table}`
                      WHERE (".implode(' OR ',$where).")";

                if(self::columnExists($table,'deleted_at')){
                    $sql.=" AND deleted_at IS NULL";
                }

                if(self::columnExists($table,'actif')){
                    $sql.=" AND actif=1";
                }

                $sql.=" ORDER BY nom,prenom LIMIT 20";

                try{
                    $stmt=Database::connection()->prepare($sql);
                    $stmt->execute(['q'=>$like]);

                    foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $row){
                        $email=self::firstValidEmail($row);
                        $code=self::firstValue(
                            $row,
                            [
                                'code_adherent',
                                'code_client',
                                'code_professeur',
                                'code_intervenant'
                            ]
                        );

                        $results[]=[
                            'source_table'=>$table,
                            'source_id'=>(int)$row['id'],
                            'type'=>$family['type'],
                            'type_label'=>$family['label'],
                            'icon'=>$family['icon'],
                            'name'=>trim(
                                (string)$row['prenom'].' '.(string)$row['nom']
                            ),
                            'email'=>$email,
                            'code'=>$code,
                            'subtitle'=>trim(
                                $family['label']
                                .($code!=='' ? ' · '.$code : '')
                                .($email!=='' ? ' · '.$email : ' · e-mail à compléter')
                            ),
                        ];
                    }
                }catch(Throwable){
                    // Une table ancienne ou atypique ne bloque pas les autres résultats.
                }
            }
        }

        return self::deduplicate($results,'person');
    }

    public static function searchActivities(string $query): array
    {
        $query=trim($query);
        $like='%'.$query.'%';
        $results=[];

        $definitions=[
            [
                'tables'=>['ateliers','cours','activities','workshops'],
                'type'=>'atelier',
                'label'=>'Atelier',
                'icon'=>'🎨',
            ],
            [
                'tables'=>['groupes','atelier_groupes','groupes_ateliers','classes'],
                'type'=>'groupe',
                'label'=>'Groupe',
                'icon'=>'👥',
            ],
        ];

        foreach($definitions as $definition){
            foreach($definition['tables'] as $table){
                if(!self::tableExists($table)){
                    continue;
                }

                $labelColumn=self::firstExistingColumn(
                    $table,
                    [
                        'libelle',
                        'nom',
                        'titre',
                        'designation',
                        'name'
                    ]
                );

                if($labelColumn===null){
                    continue;
                }

                $codeColumn=self::firstExistingColumn(
                    $table,
                    [
                        'code',
                        'code_atelier',
                        'code_cours',
                        'code_groupe',
                        'reference'
                    ]
                );

                $capacityColumn=self::firstExistingColumn(
                    $table,
                    ['capacite','capacity','places_max','effectif_max']
                );

                $select=[
                    'id',
                    "`{$labelColumn}` AS item_label"
                ];

                $select[]=$codeColumn
                    ? "`{$codeColumn}` AS item_code"
                    : "NULL AS item_code";

                $select[]=$capacityColumn
                    ? "`{$capacityColumn}` AS item_capacity"
                    : "NULL AS item_capacity";

                $where=["`{$labelColumn}` LIKE :q"];

                if($codeColumn){
                    $where[]="`{$codeColumn}` LIKE :q";
                }

                $sql="SELECT ".implode(',',$select)."
                      FROM `{$table}`";

                if($query!==''){
                    $sql.=" WHERE (".implode(' OR ',$where).")";
                }else{
                    $conditions=[];

                    if(self::columnExists($table,'deleted_at')){
                        $conditions[]="deleted_at IS NULL";
                    }

                    if(self::columnExists($table,'actif')){
                        $conditions[]="actif=1";
                    }

                    if($conditions){
                        $sql.=" WHERE ".implode(' AND ',$conditions);
                    }
                }

                if($query!==''){
                    if(self::columnExists($table,'deleted_at')){
                        $sql.=" AND deleted_at IS NULL";
                    }

                    if(self::columnExists($table,'actif')){
                        $sql.=" AND actif=1";
                    }
                }

                $sql.=" ORDER BY item_label LIMIT 40";

                try{
                    $stmt=Database::connection()->prepare($sql);
                    $stmt->execute($query!=='' ? ['q'=>$like] : []);

                    foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $row){
                        $label=trim((string)$row['item_label']);
                        $code=trim((string)($row['item_code'] ?? ''));
                        $capacity=$row['item_capacity'] ?? null;

                        $results[]=[
                            'source_table'=>$table,
                            'source_id'=>(int)$row['id'],
                            'type'=>$definition['type'],
                            'type_label'=>$definition['label'],
                            'icon'=>$definition['icon'],
                            'label'=>$label,
                            'code'=>$code,
                            'value'=>(string)$row['id'],
                            'composite_value'=>$definition['type'].':'.(int)$row['id'],
                            'subtitle'=>trim(
                                $definition['label']
                                .($code!=='' ? ' · '.$code : '')
                                .($capacity!==null && $capacity!=='' ? ' · capacité '.$capacity : '')
                            ),
                        ];
                    }
                }catch(Throwable){
                }
            }
        }

        return self::deduplicate($results,'activity');
    }

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

        foreach($rows as $row){
            $key=$mode==='person'
                ? mb_strtolower(
                    ($row['name'] ?? '').'|'.($row['email'] ?? '').'|'.($row['type'] ?? '')
                )
                : mb_strtolower(
                    ($row['type'] ?? '').'|'.($row['source_id'] ?? '').'|'.($row['label'] ?? '')
                );

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

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

        return array_slice($result,0,40);
    }

    private static function emailColumns(string $table): array
    {
        $result=[];

        foreach([
            'email',
            'email_principal',
            'email_secondaire',
            'email_professionnel',
            'mail',
            'mail_secondaire'
        ] as $column){
            if(self::columnExists($table,$column)){
                $result[]=$column;
            }
        }

        return $result;
    }

    private static function firstValidEmail(array $row): string
    {
        foreach([
            'email',
            'email_principal',
            'email_secondaire',
            'email_professionnel',
            'mail',
            'mail_secondaire'
        ] as $column){
            $email=trim((string)($row[$column] ?? ''));

            if($email!=='' && filter_var($email,FILTER_VALIDATE_EMAIL)){
                return $email;
            }
        }

        return '';
    }

    private static function firstValue(array $row,array $columns): string
    {
        foreach($columns as $column){
            $value=trim((string)($row[$column] ?? ''));

            if($value!==''){
                return $value;
            }
        }

        return '';
    }

    private static function firstExistingColumn(
        string $table,
        array $columns
    ): ?string {
        foreach($columns as $column){
            if(self::columnExists($table,$column)){
                return $column;
            }
        }

        return null;
    }

    private static function tableExists(string $table): bool
    {
        if(array_key_exists($table,self::$tables)){
            return self::$tables[$table];
        }

        $stmt=Database::connection()->prepare(
            "SELECT COUNT(*)
             FROM information_schema.TABLES
             WHERE TABLE_SCHEMA=DATABASE()
               AND TABLE_NAME=:table"
        );
        $stmt->execute(['table'=>$table]);

        return self::$tables[$table]=(int)$stmt->fetchColumn()>0;
    }

    private static function columnExists(string $table,string $column): bool
    {
        $key=$table.'.'.$column;

        if(array_key_exists($key,self::$columns)){
            return self::$columns[$key];
        }

        $stmt=Database::connection()->prepare(
            "SELECT COUNT(*)
             FROM information_schema.COLUMNS
             WHERE TABLE_SCHEMA=DATABASE()
               AND TABLE_NAME=:table
               AND COLUMN_NAME=:column"
        );
        $stmt->execute([
            'table'=>$table,
            'column'=>$column
        ]);

        return self::$columns[$key]=(int)$stmt->fetchColumn()>0;
    }
}
