<?php
namespace App\Models;

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

final class CourseGroupV6
{
    private static array $columnCache = [];
    private static array $tableCache = [];

    public static function all(): array
    {
        if (!self::tableExists('groupes_cours')) {
            return [];
        }

        $where = ['1=1'];

        if (self::hasColumn('groupes_cours', 'deleted_at')) {
            $where[] = 'g.deleted_at IS NULL';
        }

        if (self::hasColumn('groupes_cours', 'actif')) {
            $where[] = 'g.actif=1';
        }

        $planningJoin = self::tableExists('groupe_planning_versions')
            ? "LEFT JOIN groupe_planning_versions v
                    ON v.groupe_id=g.id"
              . (self::hasColumn('groupe_planning_versions', 'date_fin_effet')
                    ? " AND v.date_fin_effet IS NULL"
                    : "")
            : "";

        $roomJoin = self::tableExists('salles')
            ? "LEFT JOIN salles s ON s.id=v.salle_id"
            : "";

        $profJoin = self::tableExists('professeurs')
            ? "LEFT JOIN professeurs p
                    ON p.id=v.professeur_principal_id"
            : "";

        $participantCount = self::tableExists('groupe_participants')
            ? "(
                    SELECT COUNT(*)
                    FROM groupe_participants gp
                    WHERE gp.groupe_id=g.id"
              . (self::hasColumn('groupe_participants', 'deleted_at')
                    ? " AND gp.deleted_at IS NULL"
                    : "")
              . (self::hasColumn('groupe_participants', 'statut')
                    ? " AND gp.statut='actif'"
                    : "")
              . "
                )"
            : "0";

        $sql = "SELECT
                    g.*,
                    a.code AS atelier_code,
                    a.libelle AS atelier_libelle,
                    d.libelle AS discipline_libelle,
                    COALESCE(i.valeur_icone,'✨') AS discipline_icone,
                    COALESCE(
                        d.couleur_accent,
                        i.couleur_defaut,
                        '#64748B'
                    ) AS discipline_couleur,"
              . (self::tableExists('groupe_planning_versions')
                    ? "v.jour_semaine,
                       v.heure_debut,
                       v.heure_fin,"
                    : "NULL AS jour_semaine,
                       NULL AS heure_debut,
                       NULL AS heure_fin,")
              . (self::tableExists('salles')
                    ? "s.libelle AS salle_libelle,"
                    : "NULL AS salle_libelle,")
              . (self::tableExists('professeurs')
                    ? "CONCAT(p.prenom,' ',p.nom) AS professeur_nom,"
                    : "NULL AS professeur_nom,")
              . "{$participantCount} AS nb_participants
                FROM groupes_cours g
                INNER JOIN ateliers a ON a.id=g.atelier_id
                LEFT JOIN disciplines d ON d.id=a.discipline_id
                LEFT JOIN discipline_icons i ON i.id=d.icon_id
                {$planningJoin}
                {$roomJoin}
                {$profJoin}
                WHERE " . implode(' AND ', $where) . "
                ORDER BY
                    jour_semaine,
                    heure_debut,
                    a.libelle,
                    g.libelle";

        return Database::connection()->query($sql)->fetchAll();
    }

    public static function find(int $id): ?array
    {
        if (!self::tableExists('groupes_cours')) {
            return null;
        }

        $where = ['g.id=:id'];

        if (self::hasColumn('groupes_cours', 'deleted_at')) {
            $where[] = 'g.deleted_at IS NULL';
        }

        $planningJoin = self::tableExists('groupe_planning_versions')
            ? "LEFT JOIN groupe_planning_versions v
                    ON v.groupe_id=g.id"
              . (self::hasColumn('groupe_planning_versions', 'date_fin_effet')
                    ? " AND v.date_fin_effet IS NULL"
                    : "")
            : "";

        $roomJoin = self::tableExists('salles')
            ? "LEFT JOIN salles s ON s.id=v.salle_id"
            : "";

        $profJoin = self::tableExists('professeurs')
            ? "LEFT JOIN professeurs p
                    ON p.id=v.professeur_principal_id"
            : "";

        $sql = "SELECT
                    g.*,
                    a.code AS atelier_code,
                    a.libelle AS atelier_libelle,
                    d.libelle AS discipline_libelle,
                    COALESCE(i.valeur_icone,'✨') AS discipline_icone,
                    COALESCE(
                        d.couleur_accent,
                        i.couleur_defaut,
                        '#64748B'
                    ) AS discipline_couleur,"
              . (self::tableExists('groupe_planning_versions')
                    ? "v.id AS planning_version_id,
                       v.jour_semaine,
                       v.heure_debut,
                       v.heure_fin,
                       v.salle_id,
                       v.professeur_principal_id,
                       v.date_debut_effet,"
                    : "NULL AS planning_version_id,
                       NULL AS jour_semaine,
                       NULL AS heure_debut,
                       NULL AS heure_fin,
                       NULL AS salle_id,
                       NULL AS professeur_principal_id,
                       NULL AS date_debut_effet,")
              . (self::tableExists('salles')
                    ? "s.libelle AS salle_libelle,"
                    : "NULL AS salle_libelle,")
              . (self::tableExists('professeurs')
                    ? "CONCAT(p.prenom,' ',p.nom) AS professeur_nom"
                    : "NULL AS professeur_nom")
              . "
                FROM groupes_cours g
                INNER JOIN ateliers a ON a.id=g.atelier_id
                LEFT JOIN disciplines d ON d.id=a.discipline_id
                LEFT JOIN discipline_icons i ON i.id=d.icon_id
                {$planningJoin}
                {$roomJoin}
                {$profJoin}
                WHERE " . implode(' AND ', $where) . "
                LIMIT 1";

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

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

    public static function workshops(): array
    {
        if (!self::tableExists('ateliers')) {
            return [];
        }

        $where = ['1=1'];

        if (self::hasColumn('ateliers', 'deleted_at')) {
            $where[] = 'a.deleted_at IS NULL';
        }

        if (self::hasColumn('ateliers', 'actif')) {
            $where[] = 'a.actif=1';
        }

        if (self::hasColumn('ateliers', 'statut')) {
            $where[] = "a.statut NOT IN ('ferme','archive')";
        }

        // Seuls les cours rattachés à une discipline active pour l'année
        // courante peuvent servir de référence à un nouveau créneau.
        $where[] = 'a.discipline_id IS NOT NULL';
        $where[] = "EXISTS(
            SELECT 1
            FROM cda_discipline_year_status dys
            INNER JOIN annees_scolaires ay
                    ON ay.id=dys.annee_scolaire_id
                   AND ay.organisation_id=a.organisation_id
                   AND ay.active=1
            WHERE dys.organisation_id=a.organisation_id
              AND dys.discipline_id=a.discipline_id
              AND dys.statut='active'
        )";

        $sql = "SELECT
                    a.id,
                    a.code,
                    a.libelle,"
              . (self::hasColumn('ateliers', 'type_atelier')
                    ? "a.type_atelier,"
                    : "'individuel' AS type_atelier,")
              . "d.libelle AS discipline_libelle,
                    COALESCE(i.valeur_icone,'✨') AS discipline_icone
                FROM ateliers a
                LEFT JOIN disciplines d ON d.id=a.discipline_id
                LEFT JOIN discipline_icons i ON i.id=d.icon_id
                WHERE " . implode(' AND ', $where) . "
                ORDER BY d.libelle,a.libelle";

        return Database::connection()->query($sql)->fetchAll();
    }

    public static function professors(): array
    {
        if (!self::tableExists('professeurs')) {
            return [];
        }

        $where = ['1=1'];

        if (self::hasColumn('professeurs', 'deleted_at')) {
            $where[] = 'deleted_at IS NULL';
        }

        if (self::hasColumn('professeurs', 'actif')) {
            $where[] = 'actif=1';
        }

        return Database::connection()->query(
            "SELECT id,code_professeur,nom,prenom
             FROM professeurs
             WHERE " . implode(' AND ', $where) . "
             ORDER BY nom,prenom"
        )->fetchAll();
    }

    public static function rooms(): array
    {
        if (!self::tableExists('salles')) {
            return [];
        }

        $where = ['1=1'];

        if (self::hasColumn('salles', 'deleted_at')) {
            $where[] = 'deleted_at IS NULL';
        }

        if (self::hasColumn('salles', 'actif')) {
            $where[] = 'actif=1';
        }

        return Database::connection()->query(
            "SELECT id,libelle
             FROM salles
             WHERE " . implode(' AND ', $where) . "
             ORDER BY libelle"
        )->fetchAll();
    }

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

        try {
            $code = self::generateCode(
                (int)$data['atelier_id'],
                (string)$data['type_groupe'],
                (string)$data['jour_semaine'],
                (string)$data['heure_debut']
            );

            self::assertNoPlanningConflict($data);

            $columns = [
                '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'",
            ];

            if (self::hasColumn('groupes_cours', 'actif')) {
                $columns[] = 'actif';
                $values[] = '1';
            }

            $stmt = $pdo->prepare(
                "INSERT INTO groupes_cours("
                . implode(',', $columns)
                . ") VALUES("
                . implode(',', $values)
                . ")"
            );

            $stmt->execute([
                'atelier_id' => $data['atelier_id'],
                'code' => $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'] ?: ((new \DateTimeImmutable((string)$data['date_debut']))->format('m-d') < '09-01' ? (new \DateTimeImmutable((string)$data['date_debut']))->format('Y').'-08-31' : ((int)(new \DateTimeImmutable((string)$data['date_debut']))->format('Y')+1).'-08-31'),
            ]);

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

            if (self::tableExists('groupe_planning_versions')) {
                $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_id,
                        :jour_semaine,
                        :heure_debut,
                        :heure_fin,
                        :duree,
                        :date_debut_effet,
                        'Création du groupe',
                        :created_by
                     )"
                )->execute([
                    'groupe_id' => $groupId,
                    'salle_id' => $data['salle_id'] ?: null,
                    'professeur_id' => $data['professeur_id'] ?: null,
                    'jour_semaine' => $data['jour_semaine'],
                    'heure_debut' => $data['heure_debut'],
                    'heure_fin' => $data['heure_fin'],
                    'duree' => $data['duree_creneau_minutes'] ?: null,
                    'date_debut_effet' => $data['date_debut'],
                    'created_by' => $userId ?: null,
                ]);
            }

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

            $pdo->commit();

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

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

        try {
            self::assertNoPlanningConflict($data,$id);

            $pdo->prepare(
                "UPDATE groupes_cours
                 SET libelle=:libelle,
                     type_groupe=:type_groupe,
                     capacite_max=:capacite,
                     duree_creneau_minutes=:duree,
                     date_fin=:date_fin,
                     updated_at=CURRENT_TIMESTAMP
                 WHERE id=:id"
            )->execute([
                'libelle' => $data['libelle'],
                'type_groupe' => $data['type_groupe'],
                'capacite' => $data['capacite_max'] ?: null,
                'duree' => $data['duree_creneau_minutes'] ?: null,
                'date_fin' => $data['date_fin'] ?: ((new \DateTimeImmutable((string)$data['date_debut']))->format('m-d') < '09-01' ? (new \DateTimeImmutable((string)$data['date_debut']))->format('Y').'-08-31' : ((int)(new \DateTimeImmutable((string)$data['date_debut']))->format('Y')+1).'-08-31'),
                'id' => $id,
            ]);

            if (self::tableExists('groupe_planning_versions')) {
                if (self::hasColumn(
                    'groupe_planning_versions',
                    'date_fin_effet'
                )) {
                    $pdo->prepare(
                        "UPDATE groupe_planning_versions
                         SET date_fin_effet=DATE_SUB(
                             :date_effet,
                             INTERVAL 1 DAY
                         )
                         WHERE groupe_id=:id
                           AND date_fin_effet IS NULL"
                    )->execute([
                        'date_effet' => $data['date_effet'],
                        'id' => $id,
                    ]);
                }

                $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_id,
                        :jour_semaine,
                        :heure_debut,
                        :heure_fin,
                        :duree,
                        :date_effet,
                        :motif,
                        :created_by
                     )"
                )->execute([
                    'groupe_id' => $id,
                    'salle_id' => $data['salle_id'] ?: null,
                    'professeur_id' => $data['professeur_id'] ?: null,
                    'jour_semaine' => $data['jour_semaine'],
                    'heure_debut' => $data['heure_debut'],
                    'heure_fin' => $data['heure_fin'],
                    'duree' => $data['duree_creneau_minutes'] ?: null,
                    'date_effet' => $data['date_effet'],
                    'motif' => $data['motif'],
                    'created_by' => $userId ?: null,
                ]);
            }

            if(self::tableExists('groupe_professeurs')){
                $pdo->prepare("UPDATE groupe_professeurs SET actif=0,date_fin=DATE_SUB(?,INTERVAL 1 DAY) WHERE groupe_id=? AND role_professeur='principal' AND actif=1")
                    ->execute([$data['date_effet'],$id]);
                if(!empty($data['professeur_id'])){
                    $pdo->prepare("INSERT INTO groupe_professeurs(groupe_id,professeur_id,role_professeur,date_debut,date_fin,actif) VALUES(?,?,'principal',?,NULL,1) ON DUPLICATE KEY UPDATE date_debut=VALUES(date_debut),date_fin=NULL,actif=1")
                        ->execute([$id,(int)$data['professeur_id'],$data['date_effet']]);
                }
            }

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

    public static function archive(int $id): void
    {
        $sets = ["statut='archive'"];

        if (self::hasColumn('groupes_cours', 'actif')) {
            $sets[] = 'actif=0';
        }

        if (self::hasColumn('groupes_cours', 'deleted_at')) {
            $sets[] = 'deleted_at=CURRENT_TIMESTAMP';
        }

        if (self::hasColumn('groupes_cours', 'updated_at')) {
            $sets[] = 'updated_at=CURRENT_TIMESTAMP';
        }

        Database::connection()->prepare(
            "UPDATE groupes_cours
             SET " . implode(',', $sets) . "
             WHERE id=:id"
        )->execute(['id' => $id]);
    }

    private static function generateCode(
        int $atelierId,
        string $groupType,
        string $day,
        string $start
    ): string {
        $stmt = Database::connection()->prepare(
            "SELECT code
             FROM ateliers
             WHERE id=:id
             LIMIT 1"
        );
        $stmt->execute(['id' => $atelierId]);

        $courseCode = (string)$stmt->fetchColumn();
        if ($courseCode === '') {
            throw new \RuntimeException(
                'Le créneau doit être rattaché à un cours valide.'
            );
        }
        $nature = $groupType === 'collectif' ? 'COL' : 'IND';
        $base = $nature
            . '-'
            . $courseCode
            . '-'
            . strtoupper(substr($day, 0, 3))
            . '-'
            . str_replace(':', '', substr($start, 0, 5));

        $candidate = $base;
        $counter = 1;

        $check = Database::connection()->prepare(
            "SELECT 1
             FROM groupes_cours
             WHERE code=:code
             LIMIT 1"
        );

        while (true) {
            $check->execute(['code' => $candidate]);

            if (!$check->fetchColumn()) {
                return $candidate;
            }

            $counter++;
            $candidate = $base . '-' . $counter;
        }
    }

    private static function assertNoPlanningConflict(
        array $data,
        ?int $excludedGroupId=null
    ): void {
        if (!self::tableExists('groupe_planning_versions')) {
            return;
        }

        $professorId=(int)($data['professeur_id'] ?? 0);
        $roomId=(int)($data['salle_id'] ?? 0);
        if($professorId<=0 && $roomId<=0){
            return;
        }

        $sql="SELECT g.code,g.libelle,v.heure_debut,v.heure_fin,
                     CASE WHEN v.professeur_principal_id=:professor_match
                          THEN 'professeur' ELSE 'salle' END AS conflit
              FROM groupe_planning_versions v
              INNER JOIN groupes_cours g ON g.id=v.groupe_id
              WHERE v.date_fin_effet IS NULL
                AND g.deleted_at IS NULL
                AND g.statut<>'archive'
                AND v.jour_semaine=:jour
                AND v.heure_debut < :heure_fin
                AND v.heure_fin > :heure_debut
                AND (:excluded_id=0 OR g.id<>:excluded_match)
                AND (
                    (:professor_enabled=1 AND v.professeur_principal_id=:professor_id)
                    OR (:room_enabled=1 AND v.salle_id=:room_id)
                )
              LIMIT 1";
        $stmt=Database::connection()->prepare($sql);
        $stmt->execute([
            'professor_match'=>$professorId ?: -1,
            'jour'=>$data['jour_semaine'],'heure_fin'=>$data['heure_fin'],
            'heure_debut'=>$data['heure_debut'],'excluded_id'=>$excludedGroupId?:0,
            'excluded_match'=>$excludedGroupId?:0,
            'professor_enabled'=>$professorId>0?1:0,'professor_id'=>$professorId?:-1,
            'room_enabled'=>$roomId>0?1:0,'room_id'=>$roomId?:-1,
        ]);
        $conflict=$stmt->fetch();
        if($conflict){
            throw new \RuntimeException(
                'Chevauchement de '.$conflict['conflit'].' avec le créneau '
                .$conflict['code'].' ('.substr((string)$conflict['heure_debut'],0,5)
                .'–'.substr((string)$conflict['heure_fin'],0,5).').'
            );
        }
    }

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

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

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