<?php
namespace App\Models;

use App\Core\Database;
use RuntimeException;
use Throwable;

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

    public static function group(int $id): ?array
    {
        $stmt=Database::connection()->prepare(
            "SELECT
                g.*,
                a.id AS atelier_id,
                a.code AS atelier_code,
                a.libelle AS atelier_libelle
             FROM groupes_cours g
             INNER JOIN ateliers a ON a.id=g.atelier_id
             WHERE g.id=:id
             LIMIT 1"
        );
        $stmt->execute(['id'=>$id]);

        return $stmt->fetch() ?: null;
    }

    public static function participants(int $groupId): array
    {
        $source=self::memberSource();
        $memberJoin='';
        $nameExpr="'Participant #',ic.participant_id";
        $birthExpr='NULL';
        $emailExpr='NULL';
        $phoneExpr='NULL';

        if($source!==null){
            $table=$source['table'];
            $id=$source['id'];
            $first=$source['first'];
            $last=$source['last'];

            $memberJoin="LEFT JOIN `{$table}` m
                        ON m.`{$id}`=ic.participant_id";

            $nameExpr="CONCAT(
                COALESCE(m.`{$first}`,''),
                ' ',
                COALESCE(m.`{$last}`,'')
            )";

            if($source['birth']){
                $birthExpr="m.`{$source['birth']}`";
            }

            if($source['email']){
                $emailExpr="m.`{$source['email']}`";
            }

            if($source['phone']){
                $phoneExpr="m.`{$source['phone']}`";
            }
        }

        $stmt=Database::connection()->prepare(
            "SELECT
                gp.*,
                ic.participant_id,
                ic.participant_source,
                ic.statut AS inscription_statut,
                {$nameExpr} AS participant_nom,
                {$birthExpr} AS date_naissance,
                {$emailExpr} AS email,
                {$phoneExpr} AS telephone
             FROM groupe_participants gp
             INNER JOIN inscriptions_cours ic
                     ON ic.id=gp.inscription_id
             {$memberJoin}
             WHERE gp.groupe_id=:groupe_id
               AND gp.deleted_at IS NULL
             ORDER BY
                FIELD(
                    gp.statut,
                    'actif',
                    'liste_attente',
                    'suspendu',
                    'sorti',
                    'archive'
                ),
                gp.heure_debut_individuelle,
                participant_nom"
        );
        $stmt->execute(['groupe_id'=>$groupId]);

        return $stmt->fetchAll();
    }

    public static function eligibleRegistrations(int $groupId): array
    {
        $group=self::group($groupId);

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

        $source=self::memberSource();
        $memberJoin='';
        $nameExpr="CONCAT('Participant #',ic.participant_id)";
        $birthExpr='NULL';

        if($source!==null){
            $table=$source['table'];
            $id=$source['id'];
            $first=$source['first'];
            $last=$source['last'];

            $memberJoin="LEFT JOIN `{$table}` m
                        ON m.`{$id}`=ic.participant_id";

            $nameExpr="CONCAT(
                COALESCE(m.`{$first}`,''),
                ' ',
                COALESCE(m.`{$last}`,'')
            )";

            if($source['birth']){
                $birthExpr="m.`{$source['birth']}`";
            }
        }

        $stmt=Database::connection()->prepare(
            "SELECT
                ic.*,
                {$nameExpr} AS participant_nom,
                {$birthExpr} AS date_naissance
             FROM inscriptions_cours ic
             {$memberJoin}
             WHERE ic.atelier_id=:atelier_id
               AND ic.deleted_at IS NULL
               AND ic.statut IN (
                   'validee',
                   'liste_attente'
               )
               AND NOT EXISTS(
                   SELECT 1
                   FROM groupe_participants gp
                   WHERE gp.inscription_id=ic.id
                     AND gp.deleted_at IS NULL
                     AND gp.statut IN (
                         'actif',
                         'liste_attente',
                         'suspendu'
                     )
               )
             ORDER BY participant_nom"
        );
        $stmt->execute([
            'atelier_id'=>(int)$group['atelier_id']
        ]);

        return $stmt->fetchAll();
    }

    public static function metrics(int $groupId): array
    {
        $group=self::group($groupId);

        if(!$group){
            return [
                'capacity'=>0,
                'active'=>0,
                'waiting'=>0,
                'remaining'=>0,
            ];
        }

        $stmt=Database::connection()->prepare(
            "SELECT
                SUM(
                    CASE WHEN statut='actif'
                    THEN 1 ELSE 0 END
                ) AS active_count,
                SUM(
                    CASE WHEN statut='liste_attente'
                    THEN 1 ELSE 0 END
                ) AS waiting_count
             FROM groupe_participants
             WHERE groupe_id=:groupe_id
               AND deleted_at IS NULL"
        );
        $stmt->execute(['groupe_id'=>$groupId]);
        $counts=$stmt->fetch() ?: [];

        $capacity=(int)($group['capacite_max'] ?? 0);
        $active=(int)($counts['active_count'] ?? 0);
        $waiting=(int)($counts['waiting_count'] ?? 0);

        return [
            'capacity'=>$capacity,
            'active'=>$active,
            'waiting'=>$waiting,
            'remaining'=>$capacity>0
                ? max(0,$capacity-$active)
                : null,
        ];
    }

    public static function assign(
        int $groupId,
        int $registrationId,
        array $data,
        int $userId,
        bool $allowOverCapacity
    ): int {
        $pdo=Database::connection();
        $pdo->beginTransaction();

        try{
            $group=self::group($groupId);

            if(!$group){
                throw new RuntimeException(
                    'Groupe introuvable.'
                );
            }

            $registration=self::registration($registrationId);

            if(!$registration){
                throw new RuntimeException(
                    'Inscription introuvable.'
                );
            }

            if(
                (int)$registration['atelier_id']
                !==
                (int)$group['atelier_id']
            ){
                throw new RuntimeException(
                    'Cette inscription ne correspond pas au cours du groupe.'
                );
            }

            self::validateMusicEligibility(
                $group,
                $registration
            );

            $status=(string)($data['statut'] ?? 'actif');
            $metrics=self::metrics($groupId);

            if(
                $status==='actif' &&
                $metrics['capacity']>0 &&
                $metrics['active'] >= $metrics['capacity'] &&
                !$allowOverCapacity
            ){
                throw new RuntimeException(
                    'Le groupe est complet. Ajoutez la personne en liste d’attente ou utilisez la permission de dépassement.'
                );
            }

            if(
                $status==='actif' &&
                !empty($data['heure_debut']) &&
                !empty($data['heure_fin'])
            ){
                self::validateSlot(
                    $groupId,
                    (string)$data['heure_debut'],
                    (string)$data['heure_fin']
                );
            }

            $position=null;

            if($status==='liste_attente'){
                $stmt=$pdo->prepare(
                    "SELECT COALESCE(MAX(position_attente),0)+1
                     FROM groupe_participants
                     WHERE groupe_id=:groupe_id
                       AND statut='liste_attente'
                       AND deleted_at IS NULL"
                );
                $stmt->execute(['groupe_id'=>$groupId]);
                $position=(int)$stmt->fetchColumn();
            }

            $stmt=$pdo->prepare(
                "INSERT INTO groupe_participants(
                    groupe_id,
                    inscription_id,
                    date_entree,
                    statut,
                    heure_debut_individuelle,
                    heure_fin_individuelle,
                    position_attente,
                    observations,
                    created_by
                 ) VALUES(
                    :groupe_id,
                    :inscription_id,
                    :date_entree,
                    :statut,
                    :heure_debut,
                    :heure_fin,
                    :position_attente,
                    :observations,
                    :created_by
                 )"
            );

            $stmt->execute([
                'groupe_id'=>$groupId,
                'inscription_id'=>$registrationId,
                'date_entree'=>$data['date_entree']
                    ?: date('Y-m-d'),
                'statut'=>$status,
                'heure_debut'=>$data['heure_debut']
                    ?: null,
                'heure_fin'=>$data['heure_fin']
                    ?: null,
                'position_attente'=>$position,
                'observations'=>trim(
                    (string)($data['observations'] ?? '')
                ) ?: null,
                'created_by'=>$userId ?: null,
            ]);

            $id=(int)$pdo->lastInsertId();
            $pdo->commit();

            return $id;
        }catch(Throwable $error){
            $pdo->rollBack();
            throw $error;
        }
    }

    public static function remove(
        int $id,
        string $reason
    ): void {
        Database::connection()->prepare(
            "UPDATE groupe_participants
             SET statut='sorti',
                 date_sortie=CURRENT_DATE,
                 motif_sortie=:motif,
                 updated_at=CURRENT_TIMESTAMP
             WHERE id=:id"
        )->execute([
            'motif'=>trim($reason) ?: null,
            'id'=>$id,
        ]);
    }

    public static function archive(int $id): void
    {
        Database::connection()->prepare(
            "UPDATE groupe_participants
             SET statut='archive',
                 deleted_at=CURRENT_TIMESTAMP,
                 updated_at=CURRENT_TIMESTAMP
             WHERE id=:id"
        )->execute(['id'=>$id]);
    }

    private static function registration(
        int $id
    ): ?array {
        $stmt=Database::connection()->prepare(
            "SELECT *
             FROM inscriptions_cours
             WHERE id=:id
               AND deleted_at IS NULL
             LIMIT 1"
        );
        $stmt->execute(['id'=>$id]);

        return $stmt->fetch() ?: null;
    }

    private static function validateSlot(
        int $groupId,
        string $start,
        string $end
    ): void {
        if($end <= $start){
            throw new RuntimeException(
                'L’heure de fin doit être postérieure à l’heure de début.'
            );
        }

        $stmt=Database::connection()->prepare(
            "SELECT COUNT(*)
             FROM groupe_participants
             WHERE groupe_id=:groupe_id
               AND statut='actif'
               AND deleted_at IS NULL
               AND heure_debut_individuelle IS NOT NULL
               AND heure_fin_individuelle IS NOT NULL
               AND (
                   heure_debut_individuelle < :heure_fin
                   AND heure_fin_individuelle > :heure_debut
               )"
        );
        $stmt->execute([
            'groupe_id'=>$groupId,
            'heure_debut'=>$start,
            'heure_fin'=>$end,
        ]);

        if((int)$stmt->fetchColumn()>0){
            throw new RuntimeException(
                'Ce sous-créneau chevauche déjà celui d’un autre participant.'
            );
        }
    }

    private static function validateMusicEligibility(
        array $group,
        array $registration
    ): void {
        $stmt=Database::connection()->prepare(
            "SELECT p.code
             FROM ateliers a
             LEFT JOIN disciplines d ON d.id=a.discipline_id
             LEFT JOIN poles p ON p.id=d.pole_id
             WHERE a.id=:atelier_id
             LIMIT 1"
        );
        $stmt->execute([
            'atelier_id'=>(int)$group['atelier_id']
        ]);

        $poleCode=strtoupper(
            (string)$stmt->fetchColumn()
        );

        if(
            in_array($poleCode,['MUS','MUSIQUE'],true) &&
            $registration['eligibility_ce1'] !== null &&
            (int)$registration['eligibility_ce1'] !== 1
        ){
            throw new RuntimeException(
                'L’inscription à un cours de musique nécessite la validation du niveau CE1 minimum.'
            );
        }
    }

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

        foreach($candidates as $candidate){
            if(!self::tableExists($candidate['table'])){
                continue;
            }

            if(
                !self::hasColumn(
                    $candidate['table'],
                    $candidate['id']
                ) ||
                !self::hasColumn(
                    $candidate['table'],
                    $candidate['first']
                ) ||
                !self::hasColumn(
                    $candidate['table'],
                    $candidate['last']
                )
            ){
                continue;
            }

            foreach(['birth','email','phone'] as $key){
                if(
                    !$candidate[$key] ||
                    !self::hasColumn(
                        $candidate['table'],
                        $candidate[$key]
                    )
                ){
                    $candidate[$key]=null;
                }
            }

            return $candidate;
        }

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