<?php
namespace App\Models;

use App\Core\Database;
use Throwable;

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

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

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

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

        self::searchClients($like,$results);
        self::searchMembers($like,$results);
        self::searchProfessors($like,$results);

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

    public static function addresses(
        string $sourceType,
        int $sourceId
    ): array {
        $result=[];

        $stmt=Database::connection()->prepare(
            "SELECT
                id,
                libelle,
                email,
                type_adresse,
                priorite
             FROM communication_adresses
             WHERE source_type=:source_type
               AND source_id=:source_id
               AND actif=1
               AND deleted_at IS NULL
             ORDER BY priorite,type_adresse,email"
        );
        $stmt->execute([
            'source_type'=>$sourceType,
            'source_id'=>$sourceId,
        ]);

        foreach($stmt->fetchAll() as $row){
            $result[]=$row;
        }

        $native=self::nativeAddresses(
            $sourceType,
            $sourceId
        );

        foreach($native as $row){
            $duplicate=false;

            foreach($result as $existing){
                if(
                    mb_strtolower($existing['email'])
                    ===
                    mb_strtolower($row['email'])
                ){
                    $duplicate=true;
                    break;
                }
            }

            if(!$duplicate){
                $result[]=$row;
            }
        }

        usort(
            $result,
            static fn(array $a,array $b): int =>
                ((int)$a['priorite'])
                <=>
                ((int)$b['priorite'])
        );

        return $result;
    }

    public static function context(
        string $sourceType,
        int $sourceId
    ): array {
        return match($sourceType){
            'client','clients','adherent','adherents' =>
                self::clientContext($sourceType,$sourceId),
            'membre','membres','membres_famille','family_members' =>
                self::memberContext($sourceType,$sourceId),
            'professeur','professeurs' =>
                self::professorContext($sourceId),
            default => [],
        };
    }

    private static function searchClients(
        string $like,
        array &$results
    ): void {
        foreach([
            ['clients','code_client'],
            ['adherents','code_adherent'],
        ] as [$table,$code]){
            if(!self::tableExists($table)){
                continue;
            }

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

            $selectCode=self::hasColumn($table,$code)
                ? $code
                : "NULL";

            $emailFields=self::emailColumns($table);

            $sql="SELECT
                    id,
                    prenom,
                    nom,
                    {$selectCode} AS code"
                 .($emailFields
                    ? ",".implode(',',$emailFields)
                    : "")
                 ."
                 FROM {$table}
                 WHERE (
                    prenom LIKE :q
                    OR nom LIKE :q"
                 .(self::hasColumn($table,$code)
                    ? " OR {$code} LIKE :q"
                    : "")
                 .")"
                 .(self::hasColumn($table,'deleted_at')
                    ? " AND deleted_at IS NULL"
                    : "")
                 ." ORDER BY nom,prenom
                    LIMIT 12";

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

                foreach($stmt->fetchAll() as $row){
                    $results[]=[
                        'source_type'=>$table,
                        'source_id'=>(int)$row['id'],
                        'type_label'=>'Adhérent',
                        'name'=>trim(
                            (string)$row['prenom']
                            .' '
                            .(string)$row['nom']
                        ),
                        'subtitle'=>
                            (string)($row['code'] ?? ''),
                        'has_email'=>
                            self::rowHasEmail($row),
                        'icon'=>'👤',
                    ];
                }
            }catch(Throwable){
            }
        }
    }

    private static function searchMembers(
        string $like,
        array &$results
    ): void {
        foreach([
            'membres',
            'membres_famille',
            'family_members',
        ] as $table){
            if(!self::tableExists($table)){
                continue;
            }

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

            $emailFields=self::emailColumns($table);

            $sql="SELECT
                    id,
                    prenom,
                    nom"
                 .($emailFields
                    ? ",".implode(',',$emailFields)
                    : "")
                 ."
                 FROM {$table}
                 WHERE (
                    prenom LIKE :q
                    OR nom LIKE :q
                 )"
                 .(self::hasColumn($table,'deleted_at')
                    ? " AND deleted_at IS NULL"
                    : "")
                 ." ORDER BY nom,prenom
                    LIMIT 12";

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

                foreach($stmt->fetchAll() as $row){
                    $results[]=[
                        'source_type'=>$table,
                        'source_id'=>(int)$row['id'],
                        'type_label'=>'Membre',
                        'name'=>trim(
                            (string)$row['prenom']
                            .' '
                            .(string)$row['nom']
                        ),
                        'subtitle'=>self::rowHasEmail($row)
                            ? 'Adresse disponible'
                            : 'Adresse du représentant légal proposée',
                        'has_email'=>self::rowHasEmail($row),
                        'icon'=>'👥',
                    ];
                }
            }catch(Throwable){
            }
        }
    }

    private static function searchProfessors(
        string $like,
        array &$results
    ): void {
        if(!self::tableExists('professeurs')){
            return;
        }

        $emails=self::emailColumns('professeurs');

        $sql="SELECT
                id,
                prenom,
                nom,
                code_professeur"
             .($emails ? ",".implode(',',$emails) : "")
             ."
             FROM professeurs
             WHERE (
                prenom LIKE :q
                OR nom LIKE :q
                OR code_professeur LIKE :q
             )"
             .(self::hasColumn('professeurs','deleted_at')
                ? " AND deleted_at IS NULL"
                : "")
             ." ORDER BY nom,prenom
                LIMIT 12";

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

        foreach($stmt->fetchAll() as $row){
            $results[]=[
                'source_type'=>'professeurs',
                'source_id'=>(int)$row['id'],
                'type_label'=>'Professeur',
                'name'=>trim(
                    (string)$row['prenom']
                    .' '
                    .(string)$row['nom']
                ),
                'subtitle'=>(string)$row['code_professeur'],
                'has_email'=>self::rowHasEmail($row),
                'icon'=>'👨‍🏫',
            ];
        }
    }

    private static function nativeAddresses(
        string $sourceType,
        int $sourceId
    ): array {
        $table=self::normalizeTable($sourceType);

        if(!$table || !self::tableExists($table)){
            return [];
        }

        $emailColumns=self::emailColumnNames($table);

        if(!$emailColumns){
            return self::representativeAddresses(
                $table,
                $sourceId
            );
        }

        $select=array_map(
            static fn(string $column): string =>
                "`{$column}`",
            $emailColumns
        );

        $stmt=Database::connection()->prepare(
            "SELECT ".implode(',',$select)."
             FROM `{$table}`
             WHERE id=:id
             LIMIT 1"
        );
        $stmt->execute(['id'=>$sourceId]);
        $row=$stmt->fetch() ?: [];

        $result=[];
        $priority=10;

        foreach($emailColumns as $column){
            $email=trim((string)($row[$column] ?? ''));

            if($email==='' || !filter_var($email,FILTER_VALIDATE_EMAIL)){
                continue;
            }

            $result[]=[
                'id'=>null,
                'libelle'=>self::emailLabel($column),
                'email'=>$email,
                'type_adresse'=>self::emailType($column),
                'priorite'=>$priority,
            ];

            $priority+=10;
        }

        if(!$result){
            return self::representativeAddresses(
                $table,
                $sourceId
            );
        }

        return $result;
    }

    private static function representativeAddresses(
        string $table,
        int $sourceId
    ): array {
        $familyColumn=null;
        $clientColumn=null;

        foreach(['famille_id','family_id'] as $candidate){
            if(self::hasColumn($table,$candidate)){
                $familyColumn=$candidate;
                break;
            }
        }

        foreach([
            'client_id',
            'adherent_id',
            'responsable_id'
        ] as $candidate){
            if(self::hasColumn($table,$candidate)){
                $clientColumn=$candidate;
                break;
            }
        }

        if(!$familyColumn && !$clientColumn){
            return [];
        }

        $select=[];

        if($familyColumn){
            $select[]="`{$familyColumn}` AS famille_id";
        }

        if($clientColumn){
            $select[]="`{$clientColumn}` AS client_id";
        }

        $stmt=Database::connection()->prepare(
            "SELECT ".implode(',',$select)."
             FROM `{$table}`
             WHERE id=:id
             LIMIT 1"
        );
        $stmt->execute(['id'=>$sourceId]);
        $link=$stmt->fetch() ?: [];

        $clientId=(int)($link['client_id'] ?? 0);

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

        foreach(['clients','adherents'] as $clientTable){
            if(!self::tableExists($clientTable)){
                continue;
            }

            $emailColumns=self::emailColumnNames($clientTable);

            if(!$emailColumns){
                continue;
            }

            $select=array_merge(
                ['prenom','nom'],
                $emailColumns
            );

            $stmt=Database::connection()->prepare(
                "SELECT ".implode(',',$select)."
                 FROM {$clientTable}
                 WHERE id=:id
                 LIMIT 1"
            );
            $stmt->execute(['id'=>$clientId]);
            $client=$stmt->fetch();

            if(!$client){
                continue;
            }

            $result=[];
            $priority=5;

            foreach($emailColumns as $column){
                $email=trim(
                    (string)($client[$column] ?? '')
                );

                if(
                    $email===''
                    ||
                    !filter_var(
                        $email,
                        FILTER_VALIDATE_EMAIL
                    )
                ){
                    continue;
                }

                $result[]=[
                    'id'=>null,
                    'libelle'=>'Représentant légal — '
                        .trim(
                            (string)$client['prenom']
                            .' '
                            .(string)$client['nom']
                        ),
                    'email'=>$email,
                    'type_adresse'=>'representant_legal',
                    'priorite'=>$priority,
                ];

                $priority+=10;
            }

            return $result;
        }

        return [];
    }

    private static function clientContext(
        string $sourceType,
        int $sourceId
    ): array {
        $table=self::normalizeTable($sourceType);

        if(!$table){
            return [];
        }

        $civilite=self::hasColumn($table,'civilite')
            ? 'civilite'
            : null;

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

        if($civilite){
            $select[]=$civilite;
        }

        $stmt=Database::connection()->prepare(
            "SELECT ".implode(',',$select)."
             FROM {$table}
             WHERE id=:id
             LIMIT 1"
        );
        $stmt->execute(['id'=>$sourceId]);
        $row=$stmt->fetch() ?: [];

        return self::buildContext(
            $row,
            'Adhérent titulaire'
        );
    }

    private static function memberContext(
        string $sourceType,
        int $sourceId
    ): array {
        $table=self::normalizeTable($sourceType);

        if(!$table){
            return [];
        }

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

        if(self::hasColumn($table,'civilite')){
            $select[]='civilite';
        }

        $stmt=Database::connection()->prepare(
            "SELECT ".implode(',',$select)."
             FROM {$table}
             WHERE id=:id
             LIMIT 1"
        );
        $stmt->execute(['id'=>$sourceId]);
        $row=$stmt->fetch() ?: [];

        return self::buildContext(
            $row,
            'Membre'
        );
    }

    private static function professorContext(
        int $sourceId
    ): array {
        $select=['prenom','nom'];

        if(self::hasColumn('professeurs','civilite')){
            $select[]='civilite';
        }

        $stmt=Database::connection()->prepare(
            "SELECT ".implode(',',$select)."
             FROM professeurs
             WHERE id=:id
             LIMIT 1"
        );
        $stmt->execute(['id'=>$sourceId]);
        $row=$stmt->fetch() ?: [];

        return self::buildContext(
            $row,
            'Professeur'
        );
    }

    private static function buildContext(
        array $row,
        string $quality
    ): array {
        $first=trim((string)($row['prenom'] ?? ''));
        $last=trim((string)($row['nom'] ?? ''));
        $civilite=trim((string)($row['civilite'] ?? ''));

        $formal=trim(
            $civilite
            .' '
            .$last
        );

        if($formal===''){
            $formal=trim($first.' '.$last);
        }

        return [
            'prenom'=>$first,
            'nom'=>$last,
            'civilite'=>$civilite,
            'nom_complet'=>trim($first.' '.$last),
            'qualite'=>$quality,
            'formule_appel'=>
                $formal!=='' ? 'Bonjour '.$formal.',' : 'Bonjour,',
        ];
    }

    private static function normalizeTable(
        string $sourceType
    ): ?string {
        return match($sourceType){
            'client','clients'=>'clients',
            'adherent','adherents'=>'adherents',
            'membre','membres'=>'membres',
            'membres_famille'=>'membres_famille',
            'family_members'=>'family_members',
            'professeur','professeurs'=>'professeurs',
            default=>null,
        };
    }

    private static function emailColumns(
        string $table
    ): array {
        return array_map(
            static fn(string $column): string =>
                "`{$column}` AS `{$column}`",
            self::emailColumnNames($table)
        );
    }

    private static function emailColumnNames(
        string $table
    ): array {
        $candidates=[
            'email',
            'email_principal',
            'email_secondaire',
            'email_professionnel',
            'mail',
            'mail_secondaire',
        ];

        return array_values(
            array_filter(
                $candidates,
                static fn(string $column): bool =>
                    self::hasColumn($table,$column)
            )
        );
    }

    private static function rowHasEmail(
        array $row
    ): bool {
        foreach($row as $key=>$value){
            if(
                str_contains(
                    mb_strtolower((string)$key),
                    'mail'
                )
                &&
                filter_var(
                    (string)$value,
                    FILTER_VALIDATE_EMAIL
                )
            ){
                return true;
            }
        }

        return false;
    }

    private static function emailLabel(
        string $column
    ): string {
        return match($column){
            'email','email_principal','mail'=>
                'Adresse principale',
            'email_secondaire','mail_secondaire'=>
                'Adresse secondaire',
            'email_professionnel'=>
                'Adresse professionnelle',
            default=>'Adresse e-mail',
        };
    }

    private static function emailType(
        string $column
    ): string {
        return match($column){
            'email_secondaire','mail_secondaire'=>
                'secondaire',
            'email_professionnel'=>
                'professionnelle',
            default=>'principale',
        };
    }

    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 hasColumn(
        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;
    }
}
