<?php
namespace App\Models;

use App\Core\Database;
use Throwable;

final class CourseGroup
{
    public static function all(): array
    {
        return Database::connection()->query(
            "SELECT g.*, a.libelle AS atelier_libelle,
                    v.jour_semaine, v.heure_debut, v.heure_fin,
                    s.libelle AS salle_libelle,
                    CONCAT(p.prenom,' ',p.nom) AS professeur_nom,
                    (SELECT COUNT(*) FROM groupe_participants gp
                     WHERE gp.groupe_id = g.id
                       AND gp.deleted_at IS NULL
                       AND gp.statut = 'actif') AS nb_participants
             FROM groupes_cours g
             INNER JOIN ateliers a ON a.id = g.atelier_id
             LEFT JOIN groupe_planning_versions v
                    ON v.groupe_id = g.id
                   AND v.date_fin_effet IS NULL
             LEFT JOIN salles s ON s.id = v.salle_id
             LEFT JOIN professeurs p ON p.id = v.professeur_principal_id
             WHERE g.deleted_at IS NULL
             ORDER BY a.libelle, g.libelle"
        )->fetchAll();
    }

    public static function find(int $id): ?array
    {
        $stmt = Database::connection()->prepare(
            "SELECT g.*, a.libelle AS atelier_libelle,
                    v.id AS planning_version_id,
                    v.jour_semaine, v.heure_debut, v.heure_fin,
                    v.date_debut_effet, v.date_fin_effet,
                    s.libelle AS salle_libelle,
                    CONCAT(p.prenom,' ',p.nom) AS professeur_nom
             FROM groupes_cours g
             INNER JOIN ateliers a ON a.id = g.atelier_id
             LEFT JOIN groupe_planning_versions v
                    ON v.groupe_id = g.id
                   AND v.date_fin_effet IS NULL
             LEFT JOIN salles s ON s.id = v.salle_id
             LEFT JOIN professeurs p ON p.id = v.professeur_principal_id
             WHERE g.id = :id AND g.deleted_at IS NULL
             LIMIT 1"
        );
        $stmt->execute(['id' => $id]);
        return $stmt->fetch() ?: null;
    }

    public static function participants(int $id): array
    {
        $stmt = Database::connection()->prepare(
            "SELECT gp.*, m.nom, m.prenom, m.date_naissance
             FROM groupe_participants gp
             INNER JOIN membres_famille m ON m.id = gp.membre_id
             WHERE gp.groupe_id = :id
               AND gp.deleted_at IS NULL
             ORDER BY gp.heure_individuelle_debut, m.nom, m.prenom"
        );
        $stmt->execute(['id' => $id]);
        return $stmt->fetchAll();
    }

    public static function rooms(): array
    {
        return Database::connection()->query(
            "SELECT id, libelle FROM salles
             WHERE deleted_at IS NULL AND actif = 1
             ORDER BY libelle"
        )->fetchAll();
    }

    public static function create(array $data, int $userId): int
    {
        $pdo = Database::connection();
        $pdo->beginTransaction();

        try {
            $stmt = $pdo->prepare(
                "INSERT INTO groupes_cours(
                    atelier_id, code, libelle, type_groupe,
                    capacite_max, duree_creneau_minutes,
                    date_debut, date_fin, statut
                 ) VALUES(
                    :atelier_id, :code, :libelle, :type_groupe,
                    :capacite_max, :duree_creneau_minutes,
                    :date_debut, :date_fin, 'actif'
                 )"
            );

            $stmt->execute([
                'atelier_id' => $data['atelier_id'],
                'code' => $data['code'],
                'libelle' => $data['libelle'],
                'type_groupe' => $data['type_groupe'],
                'capacite_max' => $data['capacite_max'] ?: null,
                'duree_creneau_minutes' => $data['duree_creneau_minutes'] ?: null,
                'date_debut' => $data['date_debut'],
                'date_fin' => $data['date_fin'],
            ]);

            $groupId = (int)$pdo->lastInsertId();

            $stmt = $pdo->prepare(
                "INSERT INTO groupe_planning_versions(
                    groupe_id, salle_id, professeur_principal_id,
                    jour_semaine, heure_debut, heure_fin,
                    duree_creneau_minutes, date_debut_effet,
                    motif_modification, created_by
                 ) VALUES(
                    :groupe_id, :salle_id, :professeur_principal_id,
                    :jour_semaine, :heure_debut, :heure_fin,
                    :duree_creneau_minutes, :date_debut_effet,
                    'Création initiale', :created_by
                 )"
            );

            $stmt->execute([
                'groupe_id' => $groupId,
                'salle_id' => $data['salle_id'] ?: null,
                'professeur_principal_id' => $data['professeur_id'] ?: null,
                'jour_semaine' => $data['jour_semaine'],
                'heure_debut' => $data['heure_debut'],
                'heure_fin' => $data['heure_fin'],
                'duree_creneau_minutes' => $data['duree_creneau_minutes'] ?: null,
                'date_debut_effet' => $data['date_debut'],
                'created_by' => $userId,
            ]);

            if (!empty($data['professeur_id'])) {
                $stmt = $pdo->prepare(
                    "INSERT INTO groupe_professeurs(
                        groupe_id, professeur_id, role_professeur,
                        date_debut, date_fin, actif
                     ) VALUES(
                        :groupe_id, :professeur_id, 'principal',
                        :date_debut, :date_fin, 1
                     )"
                );
                $stmt->execute([
                    'groupe_id' => $groupId,
                    'professeur_id' => $data['professeur_id'],
                    'date_debut' => $data['date_debut'],
                    'date_fin' => $data['date_fin'],
                ]);
            }

            $pdo->commit();
            return $groupId;
        } catch (Throwable $e) {
            $pdo->rollBack();
            throw $e;
        }
    }
}
