<?php
declare(strict_types=1);
namespace App\Models;

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

final class DisciplinePlanning
{
    public static function dashboard(int $organisationId): array
    {
        $q=Database::connection()->prepare(
            "SELECT d.id,d.code,d.libelle,p.libelle pole_libelle,
                    COALESCE(s.statut,'inactive') statut,
                    COUNT(DISTINCT CASE WHEN r.statut IN('file_attente','a_etudier','creneau_propose') THEN r.id END) attente,
                    GROUP_CONCAT(DISTINCT CONCAT(pr.prenom,' ',pr.nom) ORDER BY pr.nom SEPARATOR ', ') professeurs,
                    COUNT(DISTINCT ps.id) creneaux,
                    COALESCE(SUM(DISTINCT ps.capacite_max),0) capacite
             FROM disciplines d
             LEFT JOIN poles p ON p.id=d.pole_id
             LEFT JOIN annees_scolaires y ON y.organisation_id=? AND y.active=1
             LEFT JOIN cda_discipline_year_status s ON s.discipline_id=d.id AND s.annee_scolaire_id=y.id
             LEFT JOIN demandes_inscription r
                    ON r.organisation_id=d.organisation_id
                   AND r.deleted_at IS NULL
                   AND (r.discipline_id=d.id OR r.atelier_id IN(
                        SELECT ar.id FROM ateliers ar
                        WHERE ar.discipline_id=d.id AND ar.deleted_at IS NULL
                   ))
                   AND COALESCE(NULLIF(r.annee_scolaire_id,0),(
                        SELECT ay.annee_scolaire_id FROM ateliers ay
                        WHERE ay.id=r.atelier_id LIMIT 1
                   ))=y.id
             LEFT JOIN professeur_disciplines pd ON pd.discipline_id=d.id AND pd.actif=1 AND pd.deleted_at IS NULL
             LEFT JOIN professeurs pr ON pr.id=pd.professeur_id AND pr.actif=1 AND pr.deleted_at IS NULL
             LEFT JOIN cda_professor_slots ps ON ps.discipline_id=d.id AND ps.annee_scolaire_id=y.id AND ps.statut='valide' AND ps.deleted_at IS NULL
             WHERE d.deleted_at IS NULL
             GROUP BY d.id,d.code,d.libelle,p.libelle,s.statut
             ORDER BY p.libelle,d.libelle"
        );
        $q->execute([$organisationId]);
        return $q->fetchAll()?:[];
    }

    public static function disciplinesForRequest(int $organisationId): array
    {
        $q=Database::connection()->prepare(
            "SELECT d.id,d.code,d.libelle,'active' statut,
                    COUNT(DISTINCT CASE WHEN r.statut IN('file_attente','a_etudier','creneau_propose') THEN r.id END) attente
             FROM disciplines d
             INNER JOIN annees_scolaires y
                     ON y.organisation_id=d.organisation_id
                    AND y.active=1
             LEFT JOIN demandes_inscription r
                    ON r.organisation_id=d.organisation_id
                   AND r.deleted_at IS NULL
                   AND (r.discipline_id=d.id OR r.atelier_id IN(
                        SELECT ar.id FROM ateliers ar
                        WHERE ar.discipline_id=d.id AND ar.deleted_at IS NULL
                   ))
                   AND COALESCE(NULLIF(r.annee_scolaire_id,0),(
                        SELECT ay.annee_scolaire_id FROM ateliers ay
                        WHERE ay.id=r.atelier_id LIMIT 1
                   ))=y.id
             WHERE d.organisation_id=?
               AND d.actif=1
               AND d.deleted_at IS NULL
             GROUP BY d.id,d.code,d.libelle
             ORDER BY d.libelle"
        );
        $q->execute([$organisationId]);return $q->fetchAll()?:[];
    }

    public static function saveStatuses(int $organisationId,array $statuses,int $userId): void
    {
        $pdo=Database::connection();
        $year=(int)$pdo->query("SELECT id FROM annees_scolaires WHERE active=1 ORDER BY id DESC LIMIT 1")->fetchColumn();
        if(!$year)throw new RuntimeException('Aucune année scolaire active.');
        $stmt=$pdo->prepare("INSERT INTO cda_discipline_year_status(organisation_id,annee_scolaire_id,discipline_id,statut,updated_by) VALUES(?,?,?,?,?) ON DUPLICATE KEY UPDATE statut=VALUES(statut),updated_by=VALUES(updated_by),updated_at=NOW()");
        foreach($statuses as $disciplineId=>$status){
            if(!in_array($status,['active','inactive','reflexion'],true))continue;
            $stmt->execute([$organisationId,$year,(int)$disciplineId,$status,$userId?:null]);
        }
    }

    public static function professorData(int $organisationId,int $professorId): array
    {
        $pdo=Database::connection();
        $q=$pdo->prepare("SELECT p.id,p.public_token,p.nom,p.prenom,
                                COALESCE(p.teletravail_autorise,0) teletravail_autorise,
                                COALESCE(p.reunions_autorisees,0) reunions_autorisees
                         FROM professeurs p
                         WHERE p.id=? AND p.organisation_id=? AND p.deleted_at IS NULL");
        $q->execute([$professorId,$organisationId]);$professor=$q->fetch();
        if(!$professor)throw new RuntimeException('Professeur introuvable.');
        $d=$pdo->prepare("SELECT d.id,d.libelle,d.duree_individuelle_minutes,d.duree_collective_minutes FROM professeur_disciplines pd INNER JOIN disciplines d ON d.id=pd.discipline_id WHERE pd.professeur_id=? AND pd.actif=1 AND pd.deleted_at IS NULL ORDER BY d.libelle");
        $d->execute([$professorId]);
        $rooms=$pdo->prepare("SELECT id,code,libelle FROM salles WHERE organisation_id=? AND actif=1 ORDER BY libelle");
        $rooms->execute([$organisationId]);
        $yearQuery=$pdo->prepare("SELECT id,date_debut,date_fin FROM annees_scolaires WHERE organisation_id=? AND active=1 ORDER BY date_debut DESC,id DESC LIMIT 1");
        $yearQuery->execute([$organisationId]);
        $activeYear=$yearQuery->fetch()?:['id'=>0,'date_debut'=>date('Y-m-d'),'date_fin'=>''];
        $s=$pdo->prepare("SELECT ps.*,d.libelle discipline_libelle,s.libelle salle_libelle,s.capacite salle_capacite,
                    ((SELECT COUNT(*) FROM cda_slot_enrollments e
                     WHERE e.slot_id=ps.id AND e.statut='active' AND e.deleted_at IS NULL)
                    +(SELECT COUNT(*) FROM cda_slot_assignment_proposals ap
                      WHERE ap.slot_id=ps.id AND ap.statut='en_attente')) inscrits
             FROM cda_professor_slots ps
             INNER JOIN disciplines d ON d.id=ps.discipline_id
             LEFT JOIN salles s ON s.id=ps.salle_id
             INNER JOIN annees_scolaires y ON y.id=ps.annee_scolaire_id AND y.organisation_id=? AND y.active=1
             WHERE ps.professeur_id=? AND ps.deleted_at IS NULL
             ORDER BY FIELD(ps.jour_semaine,'lundi','mardi','mercredi','jeudi','vendredi','samedi','dimanche'),ps.heure_debut");
        $s->execute([$organisationId,$professorId]);
        $c=$pdo->prepare("SELECT COALESCE(SUM(volume_heures),0) FROM contrats_professeurs WHERE professeur_id=? AND statut='actif' AND deleted_at IS NULL");
        $c->execute([$professorId]);
        $slots=$s->fetchAll()?:[];$minutes=0;foreach($slots as $slot)$minutes+=(int)round((strtotime($slot['heure_fin'])-strtotime($slot['heure_debut']))/60);
        $courseHours=$minutes/60;
        $remoteHoursQuery=$pdo->prepare(
            "SELECT COALESCE(SUM(x.duration_hours),0)
             FROM (
                SELECT
                    CASE
                        WHEN rw.mode_teletravail='annualise' AND rw.serie_uuid IS NOT NULL
                            THEN CONCAT('serie-',rw.serie_uuid)
                        ELSE CONCAT('occurrence-',rw.id)
                    END charge_key,
                    MAX(TIME_TO_SEC(TIMEDIFF(rw.heure_fin,rw.heure_debut))/3600) duration_hours
                FROM cda_professor_remote_work rw
                WHERE rw.organisation_id=?
                  AND rw.professeur_id=?
                  AND rw.deleted_at IS NULL
                  AND rw.date_teletravail BETWEEN ? AND ?
                GROUP BY charge_key
             ) x"
        );
        $remoteHoursQuery->execute([
            $organisationId,
            $professorId,
            (string)$activeYear['date_debut'],
            (string)$activeYear['date_fin']
        ]);
        $remoteWorkHours=(float)$remoteHoursQuery->fetchColumn();
        $waiting=$pdo->prepare(
            "SELECT r.id,COALESCE(NULLIF(r.discipline_id,0),a.discipline_id) discipline_id,
                    COALESCE(NULLIF(r.annee_scolaire_id,0),a.annee_scolaire_id) annee_scolaire_id,
                    CONCAT(m.prenom,' ',m.nom) eleve_nom,
                    d.libelle discipline_libelle,
                    COALESCE(r.disponibilites,'') disponibilites
             FROM demandes_inscription r
             LEFT JOIN ateliers a ON a.id=r.atelier_id AND a.deleted_at IS NULL
             LEFT JOIN disciplines d ON d.id=COALESCE(NULLIF(r.discipline_id,0),a.discipline_id)
             INNER JOIN membres_famille m ON m.id=r.membre_id
             WHERE r.organisation_id=?
               AND r.statut IN('file_attente','a_etudier','creneau_propose')
               AND COALESCE(
                    NULLIF(r.annee_scolaire_id,0),
                    NULLIF(a.annee_scolaire_id,0),
                    (
                        SELECT y0.id FROM annees_scolaires y0
                        WHERE y0.organisation_id=? AND y0.active=1
                        ORDER BY y0.date_debut DESC,y0.id DESC LIMIT 1
                    )
               )=(
                    SELECT y.id FROM annees_scolaires y
                    WHERE y.organisation_id=? AND y.active=1
                    ORDER BY y.date_debut DESC,y.id DESC LIMIT 1
               )
               AND COALESCE(NULLIF(r.discipline_id,0),a.discipline_id) IN(
                    SELECT pd.discipline_id
                    FROM professeur_disciplines pd
                    WHERE pd.professeur_id=?
                      AND pd.actif=1
                      AND pd.deleted_at IS NULL
               )
               AND r.deleted_at IS NULL
               AND NOT EXISTS(
                    SELECT 1 FROM cda_slot_assignment_proposals ap
                    WHERE ap.demande_inscription_id=r.id AND ap.statut='en_attente'
               )
             ORDER BY m.nom,m.prenom"
        );
        $waiting->execute([$organisationId,$organisationId,$organisationId,$professorId]);
        $proposals=$pdo->prepare(
            "SELECT a.*,CONCAT(m.prenom,' ',m.nom) eleve_nom,
                    CONCAT(COALESCE(u.prenom,''),' ',COALESCE(u.nom,'')) propose_par_nom
             FROM cda_slot_assignment_proposals a
             INNER JOIN demandes_inscription r ON r.id=a.demande_inscription_id
             INNER JOIN membres_famille m ON m.id=r.membre_id
             LEFT JOIN utilisateurs u ON u.id=a.proposed_by
             WHERE a.organisation_id=? AND a.professeur_id=?
             ORDER BY FIELD(a.statut,'en_attente','refusee','validee'),a.created_at DESC"
        );
        $proposals->execute([$organisationId,$professorId]);
        $validated=count($slots)>0;
        foreach($slots as $slot){
            if(($slot['statut']??'')!=='valide'){$validated=false;break;}
        }
        return [
            'professor'=>$professor,'disciplines'=>$d->fetchAll()?:[],'rooms'=>$rooms->fetchAll()?:[],
            'active_year'=>$activeYear,
            'slots'=>$slots,'contract_hours'=>(float)$c->fetchColumn(),
            'course_hours'=>$courseHours,
            'remote_work_hours'=>$remoteWorkHours,
            'all_professors'=>(function()use($pdo,$organisationId){$q=$pdo->prepare("SELECT id,nom,prenom FROM professeurs WHERE organisation_id=? AND deleted_at IS NULL AND actif=1 ORDER BY nom,prenom");$q->execute([$organisationId]);return $q->fetchAll()?:[];})(),
            'planned_hours'=>$courseHours+$remoteWorkHours,'planning_validated'=>$validated,
            'waiting_students'=>$waiting->fetchAll()?:[],
            'assignment_proposals'=>$proposals->fetchAll()?:[]
        ];
    }

    public static function saveSlot(int $organisationId,int $professorId,array $data,int $userId): void
    {
        $pdo=Database::connection();$pdo->beginTransaction();
        try{
            $discipline=(int)$data['discipline_id'];
            $check=$pdo->prepare("SELECT d.duree_individuelle_minutes,d.duree_collective_minutes FROM professeur_disciplines pd INNER JOIN disciplines d ON d.id=pd.discipline_id WHERE pd.professeur_id=? AND pd.discipline_id=? AND pd.actif=1 AND pd.deleted_at IS NULL AND d.deleted_at IS NULL");
            $check->execute([$professorId,$discipline]);$durationRow=$check->fetch();if(!$durationRow)throw new RuntimeException('Ce professeur n’est pas affecté à cette discipline.');
            $roomId=(int)($data['salle_id']??0);
            if($roomId<1)throw new RuntimeException('La salle est obligatoire pour chaque créneau.');
            self::assertRoom($pdo,$organisationId,$roomId);
            if(($data['heure_fin']??'')<=($data['heure_debut']??''))throw new RuntimeException('L’heure de fin doit être postérieure à l’heure de début.');
            $year=(int)$pdo->query("SELECT id FROM annees_scolaires WHERE active=1 ORDER BY id DESC LIMIT 1")->fetchColumn();
            $conflict=$pdo->prepare("SELECT COUNT(*) FROM cda_professor_slots WHERE professeur_id=? AND annee_scolaire_id=? AND jour_semaine=? AND deleted_at IS NULL AND statut<>'archive' AND heure_debut<? AND heure_fin>?");
            $conflict->execute([$professorId,$year,$data['jour_semaine'],$data['heure_fin'],$data['heure_debut']]);
            if((int)$conflict->fetchColumn())throw new RuntimeException('Ce créneau chevauche déjà un autre créneau du professeur.');
            self::assertRoomAvailable($pdo,$organisationId,$year,$roomId,(string)$data['jour_semaine'],(string)$data['heure_debut'],(string)$data['heure_fin'],(string)$data['date_debut'],($data['date_fin']??'')?:null);
            $insert=$pdo->prepare("INSERT INTO cda_professor_slots(organisation_id,annee_scolaire_id,professeur_id,discipline_id,salle_id,jour_semaine,heure_debut,heure_fin,type_cours,capacite_max,date_debut,date_fin,statut,created_by) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            $type=in_array(($data['type_cours']??''),['individuel','collectif'],true)?$data['type_cours']:'individuel';
            $start=strtotime((string)$data['heure_debut']);$end=strtotime((string)$data['heure_fin']);
            $duration=$type==='individuel'?(int)$durationRow['duree_individuelle_minutes']:(int)$durationRow['duree_collective_minutes'];
            if($duration<5)throw new RuntimeException('La durée prévisionnelle doit être paramétrée dans la discipline.');
            if($type==='individuel'&&(($end-$start)%($duration*60))!==0)throw new RuntimeException('La plage doit contenir un nombre entier de cours de '.$duration.' minutes.');
            $ranges=[];if($type==='individuel'){for($cursor=$start;$cursor<$end;$cursor+=$duration*60)$ranges[]=[$cursor,$cursor+$duration*60];}else{$ranges[]=[$start,$end];}
            foreach($ranges as [$from,$to]){$insert->execute([$organisationId,$year,$professorId,$discipline,$roomId,$data['jour_semaine'],date('H:i:s',$from),date('H:i:s',$to),$type,$type==='individuel'?1:max(1,(int)$data['capacite_max']),$data['date_debut'],$data['date_fin']?:null,'brouillon',$userId?:null]);}
            $pdo->commit();
        }catch(Throwable $e){if($pdo->inTransaction())$pdo->rollBack();throw $e;}
    }

    public static function updateSlot(int $organisationId,int $professorId,int $slotId,array $data,int $userId): void
    {
        if($slotId<1)throw new RuntimeException('Plage introuvable.');
        $pdo=Database::connection();$pdo->beginTransaction();
        try{
            $slot=$pdo->prepare("SELECT * FROM cda_professor_slots WHERE id=? AND organisation_id=? AND professeur_id=? AND deleted_at IS NULL FOR UPDATE");
            $slot->execute([$slotId,$organisationId,$professorId]);$existing=$slot->fetch();
            if(!$existing)throw new RuntimeException('Cette plage n’existe plus.');
            $discipline=(int)($data['discipline_id']??0);
            $check=$pdo->prepare("SELECT 1 FROM professeur_disciplines pd INNER JOIN disciplines d ON d.id=pd.discipline_id AND d.deleted_at IS NULL WHERE pd.professeur_id=? AND pd.discipline_id=? AND pd.actif=1 AND pd.deleted_at IS NULL");
            $check->execute([$professorId,$discipline]);if(!$check->fetchColumn())throw new RuntimeException('Ce professeur n’est pas affecté à cette discipline.');
            $roomId=(int)($data['salle_id']??0);
            if($roomId<1)throw new RuntimeException('La salle est obligatoire pour chaque créneau.');
            self::assertRoom($pdo,$organisationId,$roomId);
            $day=(string)($data['jour_semaine']??'');$from=(string)($data['heure_debut']??'');$to=(string)($data['heure_fin']??'');
            if(!in_array($day,['lundi','mardi','mercredi','jeudi','vendredi','samedi','dimanche'],true)||$to<=$from)throw new RuntimeException('Les horaires de la plage sont invalides.');
            $conflict=$pdo->prepare("SELECT COUNT(*) FROM cda_professor_slots WHERE professeur_id=? AND annee_scolaire_id=? AND jour_semaine=? AND id<>? AND deleted_at IS NULL AND statut<>'archive' AND heure_debut<? AND heure_fin>?");
            $conflict->execute([$professorId,(int)$existing['annee_scolaire_id'],$day,$slotId,$to,$from]);
            if((int)$conflict->fetchColumn())throw new RuntimeException('Le déplacement chevauche une autre plage du professeur.');
            self::assertRoomAvailable($pdo,$organisationId,(int)$existing['annee_scolaire_id'],$roomId,$day,$from,$to,(string)($data['date_debut']?:$existing['date_debut']),($data['date_fin']??'')?:null,$slotId);
            $type=in_array(($data['type_cours']??''),['individuel','collectif'],true)?$data['type_cours']:'individuel';
            $enrolled=$pdo->prepare("SELECT COUNT(*) FROM cda_slot_enrollments WHERE slot_id=? AND statut='active' AND deleted_at IS NULL");
            $enrolled->execute([$slotId]);
            if((int)$enrolled->fetchColumn()>0&&($discipline!==(int)$existing['discipline_id']||$type!==$existing['type_cours']))throw new RuntimeException('La discipline ou le format ne peut pas changer car des élèves sont déjà affectés.');
            $update=$pdo->prepare("UPDATE cda_professor_slots SET discipline_id=?,salle_id=?,jour_semaine=?,heure_debut=?,heure_fin=?,type_cours=?,capacite_max=?,date_debut=?,date_fin=?,statut='brouillon',validated_by=NULL,validated_at=NULL WHERE id=? AND organisation_id=? AND professeur_id=?");
            $update->execute([$discipline,$roomId,$day,$from,$to,$type,$type==='individuel'?1:max(1,(int)($data['capacite_max']??1)),$data['date_debut']?:$existing['date_debut'],($data['date_fin']??'')?:null,$slotId,$organisationId,$professorId]);
            $pdo->commit();
        }catch(Throwable $e){if($pdo->inTransaction())$pdo->rollBack();throw $e;}
    }

    public static function validatePlanning(int $organisationId,int $professorId,int $userId): void
    {
        $pdo=Database::connection();
        $year=(int)$pdo->query("SELECT id FROM annees_scolaires WHERE active=1 ORDER BY id DESC LIMIT 1")->fetchColumn();
        $already=$pdo->prepare("SELECT COUNT(*) FROM cda_professor_slots WHERE organisation_id=? AND professeur_id=? AND annee_scolaire_id=? AND statut='valide' AND deleted_at IS NULL");
        $already->execute([$organisationId,$professorId,$year]);
        if((int)$already->fetchColumn()>0)throw new RuntimeException('Ce planning est déjà validé. Il ne peut pas être validé une seconde fois.');
        $q=$pdo->prepare("SELECT COALESCE(SUM(TIME_TO_SEC(TIMEDIFF(heure_fin,heure_debut))/3600),0) FROM cda_professor_slots WHERE professeur_id=? AND annee_scolaire_id=? AND deleted_at IS NULL AND statut<>'archive'");
        $q->execute([$professorId,$year]);$courseHours=(float)$q->fetchColumn();
        $remote=$pdo->prepare(
            "SELECT COALESCE(SUM(x.duration_hours),0)
             FROM (
                SELECT
                    CASE
                        WHEN rw.mode_teletravail='annualise' AND rw.serie_uuid IS NOT NULL
                            THEN CONCAT('serie-',rw.serie_uuid)
                        ELSE CONCAT('occurrence-',rw.id)
                    END charge_key,
                    MAX(TIME_TO_SEC(TIMEDIFF(rw.heure_fin,rw.heure_debut))/3600) duration_hours
                FROM cda_professor_remote_work rw
                INNER JOIN annees_scolaires y
                    ON y.id=?
                   AND rw.date_teletravail BETWEEN y.date_debut AND y.date_fin
                WHERE rw.organisation_id=?
                  AND rw.professeur_id=?
                  AND rw.deleted_at IS NULL
                GROUP BY charge_key
             ) x"
        );
        $remote->execute([$year,$organisationId,$professorId]);
        $remoteWorkHours=(float)$remote->fetchColumn();
        $planned=$courseHours+$remoteWorkHours;
        $c=$pdo->prepare("SELECT COALESCE(SUM(volume_heures),0) FROM contrats_professeurs WHERE professeur_id=? AND statut='actif' AND deleted_at IS NULL");
        $c->execute([$professorId]);$contract=(float)$c->fetchColumn();
        if($contract<=0)throw new RuntimeException('Validation impossible : aucun contrat actif avec un volume horaire n’est renseigné pour ce professeur.');
        if($planned<=0)throw new RuntimeException('Validation impossible : aucun cours ni télétravail n’est planifié pour ce professeur.');
        $rooms=$pdo->prepare("SELECT COUNT(*) FROM cda_professor_slots WHERE organisation_id=? AND professeur_id=? AND annee_scolaire_id=? AND deleted_at IS NULL AND statut<>'archive' AND salle_id IS NULL");
        $rooms->execute([$organisationId,$professorId,$year]);
        if((int)$rooms->fetchColumn()>0)throw new RuntimeException('Validation impossible : une salle doit être affectée à chaque créneau.');
        if(abs($planned-$contract)>0.01)throw new RuntimeException(
            'Validation impossible : '.$courseHours.' h de cours + '.$remoteWorkHours.
            ' h de télétravail = '.$planned.' h planifiées pour '.$contract.
            ' h contractuelles. Ajoutez ou corrigez les plages avant de valider.'
        );
        $pdo->prepare("UPDATE cda_professor_slots SET statut='valide',validated_by=?,validated_at=NOW() WHERE organisation_id=? AND professeur_id=? AND annee_scolaire_id=? AND deleted_at IS NULL")->execute([$userId?:null,$organisationId,$professorId,$year]);
    }

    public static function activeYearId(int $organisationId): int
    {
        $q=Database::connection()->prepare("SELECT id FROM annees_scolaires WHERE organisation_id=? AND active=1 ORDER BY date_debut DESC,id DESC LIMIT 1");
        $q->execute([$organisationId]);return (int)$q->fetchColumn();
    }

    public static function proposeStudent(int $organisationId,int $professorId,int $slotId,int $requestId,int $userId,string $comment=''): void
    {
        $pdo=Database::connection();$pdo->beginTransaction();
        try{
            $slot=$pdo->prepare("SELECT id,discipline_id,annee_scolaire_id,capacite_max FROM cda_professor_slots WHERE id=? AND organisation_id=? AND professeur_id=? AND statut='valide' AND deleted_at IS NULL FOR UPDATE");
            $slot->execute([$slotId,$organisationId,$professorId]);$slotRow=$slot->fetch();
            if(!$slotRow)throw new RuntimeException('Le planning doit être validé par la Direction avant toute affectation.');
            $occupied=$pdo->prepare("SELECT
                (SELECT COUNT(*) FROM cda_slot_enrollments WHERE slot_id=? AND statut='active' AND deleted_at IS NULL)
                +(SELECT COUNT(*) FROM cda_slot_assignment_proposals WHERE slot_id=? AND statut='en_attente')");
            $occupied->execute([$slotId,$slotId]);
            if((int)$occupied->fetchColumn()>=(int)$slotRow['capacite_max'])throw new RuntimeException('Cette place est déjà occupée ou réservée par une proposition en attente.');
            $request=$pdo->prepare("SELECT r.id,COALESCE(NULLIF(r.discipline_id,0),a.discipline_id) discipline_id,COALESCE(NULLIF(r.annee_scolaire_id,0),a.annee_scolaire_id) annee_scolaire_id FROM demandes_inscription r LEFT JOIN ateliers a ON a.id=r.atelier_id AND a.deleted_at IS NULL WHERE r.id=? AND r.organisation_id=? AND r.statut IN('file_attente','a_etudier','creneau_propose') AND r.deleted_at IS NULL FOR UPDATE");
            $request->execute([$requestId,$organisationId]);$requestRow=$request->fetch();
            if(!$requestRow)throw new RuntimeException('Cet élève n’est plus disponible dans la file d’attente.');
            if((int)$requestRow['discipline_id']!==(int)$slotRow['discipline_id'])throw new RuntimeException('L’élève doit être en attente pour la même discipline que le créneau.');
            if((int)$requestRow['annee_scolaire_id']<=0)$requestRow['annee_scolaire_id']=(int)$slotRow['annee_scolaire_id'];
            if((int)$requestRow['annee_scolaire_id']!==(int)$slotRow['annee_scolaire_id'])throw new RuntimeException('La demande et le créneau doivent appartenir à la même année scolaire.');
            $duplicate=$pdo->prepare("SELECT COUNT(*) FROM cda_slot_assignment_proposals WHERE demande_inscription_id=? AND statut='en_attente'");
            $duplicate->execute([$requestId]);
            if((int)$duplicate->fetchColumn()>0)throw new RuntimeException('Une proposition est déjà en attente pour cet élève.');
            $pdo->prepare("INSERT INTO cda_slot_assignment_proposals(organisation_id,professeur_id,slot_id,demande_inscription_id,statut,proposed_by,proposal_comment) VALUES(?,?,?,?,'en_attente',?,?)")
                ->execute([$organisationId,$professorId,$slotId,$requestId,$userId?:null,trim($comment)?:null]);
            $pdo->prepare("UPDATE demandes_inscription SET statut='creneau_propose',accord_professeur_at=NOW(),updated_at=NOW() WHERE id=?")
                ->execute([$requestId]);
            $pdo->commit();
        }catch(Throwable $e){if($pdo->inTransaction())$pdo->rollBack();throw $e;}
    }

    public static function proposal(int $organisationId,int $proposalId): ?array
    {
        $q=Database::connection()->prepare("SELECT * FROM cda_slot_assignment_proposals WHERE id=? AND organisation_id=? AND statut='en_attente' LIMIT 1");
        $q->execute([$proposalId,$organisationId]);return $q->fetch()?:null;
    }

    public static function pendingProposalCount(int $organisationId): int
    {
        $q=Database::connection()->prepare("SELECT COUNT(*) FROM cda_slot_assignment_proposals WHERE organisation_id=? AND statut='en_attente'");
        $q->execute([$organisationId]);return (int)$q->fetchColumn();
    }

    public static function proposalsForDirection(int $organisationId): array
    {
        $q=Database::connection()->prepare(
            "SELECT ap.id,ap.statut,ap.proposal_comment,ap.decision_comment,ap.created_at,ap.decided_at,
                    CONCAT(m.prenom,' ',m.nom) eleve_nom,d.libelle discipline_libelle,
                    CONCAT(p.prenom,' ',p.nom) professeur_nom,
                    ps.jour_semaine,ps.heure_debut,ps.heure_fin,ps.capacite_max,
                    s.libelle salle_libelle,s.capacite salle_capacite,
                    (SELECT COUNT(*) FROM cda_slot_enrollments e WHERE e.slot_id=ps.id AND e.statut='active' AND e.deleted_at IS NULL) inscrits
             FROM cda_slot_assignment_proposals ap
             INNER JOIN demandes_inscription r ON r.id=ap.demande_inscription_id AND r.deleted_at IS NULL
             INNER JOIN membres_famille m ON m.id=r.membre_id
             INNER JOIN cda_professor_slots ps ON ps.id=ap.slot_id AND ps.deleted_at IS NULL
             INNER JOIN disciplines d ON d.id=ps.discipline_id
             INNER JOIN professeurs p ON p.id=ap.professeur_id AND p.deleted_at IS NULL
             LEFT JOIN salles s ON s.id=ps.salle_id
             WHERE ap.organisation_id=?
             ORDER BY FIELD(ap.statut,'en_attente','validee','refusee'),ap.created_at DESC"
        );
        $q->execute([$organisationId]);return $q->fetchAll()?:[];
    }

    public static function finishProposal(int $organisationId,int $proposalId,bool $approved,int $userId,string $comment=''): void
    {
        $pdo=Database::connection();
        $q=$pdo->prepare("UPDATE cda_slot_assignment_proposals SET statut=?,decided_by=?,decided_at=NOW(),decision_comment=? WHERE id=? AND organisation_id=? AND statut='en_attente'");
        $q->execute([$approved?'validee':'refusee',$userId?:null,trim($comment)?:null,$proposalId,$organisationId]);
        if(!$approved){
            $p=$pdo->prepare("SELECT demande_inscription_id FROM cda_slot_assignment_proposals WHERE id=? AND organisation_id=?");
            $p->execute([$proposalId,$organisationId]);$requestId=(int)$p->fetchColumn();
            if($requestId)$pdo->prepare("UPDATE demandes_inscription SET statut='file_attente',accord_professeur_at=NULL,updated_at=NOW() WHERE id=? AND statut='creneau_propose'")->execute([$requestId]);
        }
    }

    public static function slotsForDiscipline(int $organisationId,int $disciplineId): array
    {
        $q=Database::connection()->prepare(
            "SELECT ps.*,CONCAT(p.prenom,' ',p.nom) professeur_nom,s.libelle salle_libelle,s.capacite salle_capacite,
                    (SELECT COUNT(*) FROM cda_slot_enrollments e WHERE e.slot_id=ps.id AND e.statut='active' AND e.deleted_at IS NULL) inscrits
             FROM cda_professor_slots ps INNER JOIN professeurs p ON p.id=ps.professeur_id
             LEFT JOIN salles s ON s.id=ps.salle_id
             WHERE ps.organisation_id=? AND ps.discipline_id=? AND ps.statut='valide' AND ps.deleted_at IS NULL
             ORDER BY FIELD(ps.jour_semaine,'lundi','mardi','mercredi','jeudi','vendredi','samedi','dimanche'),ps.heure_debut"
        );
        $q->execute([$organisationId,$disciplineId]);return $q->fetchAll()?:[];
    }

    private static function assertRoom(\PDO $pdo,int $organisationId,int $roomId): void
    {
        $q=$pdo->prepare("SELECT COUNT(*) FROM salles WHERE id=? AND organisation_id=? AND actif=1");
        $q->execute([$roomId,$organisationId]);
        if(!(int)$q->fetchColumn())throw new RuntimeException('La salle sélectionnée est inactive ou introuvable.');
    }

    private static function assertRoomAvailable(\PDO $pdo,int $organisationId,int $yearId,int $roomId,string $day,string $from,string $to,string $dateStart,?string $dateEnd,int $excludedSlotId=0): void
    {
        $q=$pdo->prepare(
            "SELECT CONCAT(p.prenom,' ',p.nom,' — ',TIME_FORMAT(ps.heure_debut,'%H:%i'),'–',TIME_FORMAT(ps.heure_fin,'%H:%i'))
             FROM cda_professor_slots ps
             INNER JOIN professeurs p ON p.id=ps.professeur_id
             WHERE ps.organisation_id=?
               AND ps.annee_scolaire_id=?
               AND ps.salle_id=?
               AND ps.jour_semaine=?
               AND ps.id<>?
               AND ps.deleted_at IS NULL
               AND ps.statut<>'archive'
               AND ps.heure_debut<?
               AND ps.heure_fin>?
               AND COALESCE(ps.date_debut,'1000-01-01')<=COALESCE(?,'9999-12-31')
               AND COALESCE(ps.date_fin,'9999-12-31')>=COALESCE(?,'1000-01-01')
             LIMIT 1"
        );
        $q->execute([$organisationId,$yearId,$roomId,$day,$excludedSlotId,$to,$from,$dateEnd,$dateStart]);
        if($label=$q->fetchColumn())throw new RuntimeException('Cette salle est déjà occupée sur cette période par '.$label.'.');
    }
}
