<?php
namespace App\Services;

use App\Core\Database;

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

    public function resolve(
        string $type,
        ?int $targetId,
        array $selection=[]
    ): array {
        return match($type){
            'individuel' => $this->individual($selection),
            'tous_adherents' => $this->allMembers(),
            'atelier' => $this->forWorkshop((int)$targetId),
            'groupe','classe' => $this->forGroup((int)$targetId),
            'selection' => $this->individual($selection),
            default => [],
        };
    }

    private function individual(array $selection): array
    {
        $result=[];

        foreach($selection as $item){
            if(empty($item['email'])){
                continue;
            }

            $result[]=[
                'source_type'=>$item['source_type'] ?? 'manuel',
                'source_id'=>(int)($item['source_id'] ?? 0) ?: null,
                'nom'=>$item['nom'] ?? null,
                'email'=>$item['email']
            ];
        }

        return $result;
    }

    private function allMembers(): array
    {
        foreach([
            ['clients','id','prenom','nom','email'],
            ['adherents','id','prenom','nom','email'],
        ] as [$table,$id,$first,$last,$email]){
            if(
                !$this->tableExists($table)
                ||
                !$this->hasColumn($table,$email)
            ){
                continue;
            }

            $sql="SELECT
                    {$id} AS source_id,
                    CONCAT({$first},' ',{$last}) AS nom,
                    {$email} AS email
                 FROM {$table}
                 WHERE {$email} IS NOT NULL
                   AND {$email}<>''";

            if($this->hasColumn($table,'deleted_at')){
                $sql.=" AND deleted_at IS NULL";
            }

            if($this->hasColumn($table,'actif')){
                $sql.=" AND actif=1";
            }

            $rows=Database::connection()->query($sql)->fetchAll();

            return array_map(
                fn(array $row): array => [
                    'source_type'=>$table,
                    'source_id'=>(int)$row['source_id'],
                    'nom'=>$row['nom'],
                    'email'=>$row['email'],
                ],
                $rows
            );
        }

        return [];
    }

    private function forWorkshop(int $workshopId): array
    {
        if(!$this->tableExists('demandes_inscription')){
            return [];
        }

        $source=$this->memberSource();

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

        $sql="SELECT DISTINCT
                m.`{$source['id']}` AS source_id,
                CONCAT(
                    m.`{$source['first']}`,
                    ' ',
                    m.`{$source['last']}`
                ) AS nom,
                m.`{$source['email']}` AS email
             FROM demandes_inscription d
             INNER JOIN `{$source['table']}` m
                     ON m.`{$source['id']}`=d.membre_id
             WHERE d.atelier_id=:atelier_id
               AND d.deleted_at IS NULL
               AND d.statut NOT IN(
                   'refusee',
                   'annulee',
                   'archive'
               )
               AND m.`{$source['email']}` IS NOT NULL
               AND m.`{$source['email']}`<>''";

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

        return array_map(
            fn(array $row): array => [
                'source_type'=>$source['table'],
                'source_id'=>(int)$row['source_id'],
                'nom'=>$row['nom'],
                'email'=>$row['email'],
            ],
            $stmt->fetchAll()
        );
    }

    private function forGroup(int $groupId): array
    {
        if(
            !$this->tableExists('groupe_participants')
            ||
            !$this->tableExists('inscriptions_cours')
        ){
            return [];
        }

        $source=$this->memberSource();

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

        $sql="SELECT DISTINCT
                m.`{$source['id']}` AS source_id,
                CONCAT(
                    m.`{$source['first']}`,
                    ' ',
                    m.`{$source['last']}`
                ) AS nom,
                m.`{$source['email']}` AS email
             FROM groupe_participants gp
             INNER JOIN inscriptions_cours ic
                     ON ic.id=gp.inscription_id
             INNER JOIN `{$source['table']}` m
                     ON m.`{$source['id']}`=ic.participant_id
             WHERE gp.groupe_id=:groupe_id
               AND gp.deleted_at IS NULL
               AND gp.statut='actif'
               AND m.`{$source['email']}` IS NOT NULL
               AND m.`{$source['email']}`<>''";

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

        return array_map(
            fn(array $row): array => [
                'source_type'=>$source['table'],
                'source_id'=>(int)$row['source_id'],
                'nom'=>$row['nom'],
                'email'=>$row['email'],
            ],
            $stmt->fetchAll()
        );
    }

    private function memberSource(): ?array
    {
        $candidates=[
            [
                'table'=>'membres',
                'id'=>'id',
                'first'=>'prenom',
                'last'=>'nom',
                'email'=>'email'
            ],
            [
                'table'=>'membres_famille',
                'id'=>'id',
                'first'=>'prenom',
                'last'=>'nom',
                'email'=>'email'
            ],
            [
                'table'=>'family_members',
                'id'=>'id',
                'first'=>'prenom',
                'last'=>'nom',
                'email'=>'email'
            ],
        ];

        foreach($candidates as $candidate){
            if(!$this->tableExists($candidate['table'])){
                continue;
            }

            foreach(['id','first','last','email'] as $field){
                if(!$this->hasColumn(
                    $candidate['table'],
                    $candidate[$field]
                )){
                    continue 2;
                }
            }

            return $candidate;
        }

        return null;
    }

    private function tableExists(string $table): bool
    {
        if(array_key_exists($table,$this->tables)){
            return $this->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 $this->tables[$table]=(int)$stmt->fetchColumn()>0;
    }

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

        if(array_key_exists($key,$this->columns)){
            return $this->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 $this->columns[$key]=(int)$stmt->fetchColumn()>0;
    }
}
