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

use App\Core\Database;

final class ProfessorYear
{
    public static function professorForUser(int $userId): ?int
    {
        if (\App\Core\Permission::activeSpace() === 'professeur'
            && \App\Core\Permission::isSuperAdmin()) {
            return \App\Core\PortalSimulation::professorId();
        }
        $q=Database::connection()->prepare("SELECT id FROM professeurs WHERE utilisateur_id=? AND deleted_at IS NULL LIMIT 1");
        $q->execute([$userId]);
        return ($id=(int)$q->fetchColumn())>0?$id:null;
    }

    public static function data(int $organisationId,int $professorId,int $yearId): array
    {
        $pdo=Database::connection();
        $p=$pdo->prepare("SELECT id,nom,prenom,code_professeur FROM professeurs WHERE id=? AND organisation_id=? AND deleted_at IS NULL");
        $p->execute([$professorId,$organisationId]);
        $professor=$p->fetch();
        if(!$professor)throw new \RuntimeException('Professeur introuvable.');
        $y=$pdo->prepare("SELECT * FROM annees_scolaires WHERE id=? AND organisation_id=?");
        $y->execute([$yearId,$organisationId]);
        $year=$y->fetch();
        if(!$year)throw new \RuntimeException('Année scolaire introuvable.');
        $d=$pdo->prepare("SELECT d.id,d.libelle,COALESCE(d.couleur_accent,'#315bd4') couleur FROM professeur_disciplines pd 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]);
        $e=$pdo->prepare("SELECT e.*,d.libelle discipline_libelle,s.libelle salle_libelle,COALESCE(d.couleur_accent,'#315bd4') couleur FROM cda_professor_year_events e LEFT JOIN disciplines d ON d.id=e.discipline_id LEFT JOIN salles s ON s.id=e.salle_id WHERE e.professeur_id=? AND e.annee_scolaire_id=? AND e.deleted_at IS NULL AND NOT(e.type_evenement='cours' AND (e.date_evenement BETWEEN '2026-07-04' AND '2026-08-31' OR e.date_evenement BETWEEN '2026-10-17' AND '2026-11-01' OR e.date_evenement BETWEEN '2026-12-19' AND '2027-01-03' OR e.date_evenement BETWEEN '2027-02-20' AND '2027-03-07' OR e.date_evenement BETWEEN '2027-04-17' AND '2027-05-02' OR e.date_evenement BETWEEN '2027-07-03' AND '2027-08-31')) ORDER BY e.date_evenement,e.heure_debut");
        $e->execute([$professorId,$yearId]);
        $r=$pdo->prepare("SELECT r.*,u.nom demandeur_nom,u.prenom demandeur_prenom FROM cda_professor_change_requests r LEFT JOIN utilisateurs u ON u.id=r.requested_by WHERE r.professeur_id=? AND r.annee_scolaire_id=? ORDER BY FIELD(r.statut,'en_attente','refusee','validee'),r.created_at DESC");
        $r->execute([$professorId,$yearId]);
        $hours=$pdo->prepare("SELECT COALESCE(SUM(TIMESTAMPDIFF(MINUTE,heure_debut,heure_fin))/60,0) FROM cda_professor_year_events WHERE professeur_id=? AND annee_scolaire_id=? AND deleted_at IS NULL AND type_evenement IN('cours','reunion','evenement','rattrapage') AND statut<>'annule'");
        $hours->execute([$professorId,$yearId]);
        $plannedHours=(float)$hours->fetchColumn();
        $real=$pdo->prepare("SELECT COALESCE(SUM(duree_prevue_minutes)/60,0) FROM seances WHERE professeur_id=? AND date_seance BETWEEN ? AND ? AND statut='realisee' AND deleted_at IS NULL");
        $real->execute([$professorId,$year['date_debut'],$year['date_fin']]);
        $workedHours=(float)$real->fetchColumn();
        $events=$e->fetchAll()?:[];
        // Le télétravail possède son propre circuit de compte rendu, mais il
        // fait partie du planning annuel de travail du professeur.
        $rw=$pdo->prepare(
            "SELECT id,date_teletravail,heure_debut,heure_fin,consignes_direction,
                    taches_effectuees,statut,mode_teletravail
               FROM cda_professor_remote_work
              WHERE organisation_id=? AND professeur_id=?
                AND date_teletravail BETWEEN ? AND ?
                AND deleted_at IS NULL
              ORDER BY date_teletravail,heure_debut"
        );
        $rw->execute([$organisationId,$professorId,$year['date_debut'],$year['date_fin']]);
        $closures=SchoolCalendar::closures($organisationId,$year['date_debut'],$year['date_fin']);
        foreach($rw->fetchAll()?:[] as $remote){
            if(($remote['mode_teletravail']??'ponctuel')==='annualise'
                && SchoolCalendar::isClosed((string)$remote['date_teletravail'],$closures)){
                continue;
            }
            $duration=max(0,(strtotime((string)$remote['heure_fin'])-strtotime((string)$remote['heure_debut']))/3600);
            $plannedHours+=$duration;
            if($remote['statut']==='valide')$workedHours+=$duration;
            $events[]=[
                'id'=>'teletravail-'.(int)$remote['id'],
                'remote_work_id'=>(int)$remote['id'],
                'organisation_id'=>$organisationId,
                'annee_scolaire_id'=>$yearId,
                'professeur_id'=>$professorId,
                'date_evenement'=>$remote['date_teletravail'],
                'heure_debut'=>$remote['heure_debut'],
                'heure_fin'=>$remote['heure_fin'],
                'type_evenement'=>'teletravail',
                'statut'=>$remote['statut'],
                'discipline_libelle'=>'Télétravail',
                'salle_libelle'=>null,
                'couleur'=>'#7955c7',
                'students'=>[],
                'consignes_direction'=>$remote['consignes_direction']??null,
                'taches_effectuees'=>$remote['taches_effectuees']??null,
                'mode_teletravail'=>$remote['mode_teletravail']??'ponctuel',
            ];
        }
        usort($events,static fn(array $a,array $b):int=>
            [$a['date_evenement'],$a['heure_debut']]<=>[$b['date_evenement'],$b['heure_debut']]
        );
        $enrollments=$pdo->prepare("SELECT e.slot_id,CONCAT(m.prenom,' ',m.nom) eleve_nom FROM cda_slot_enrollments e JOIN membres_famille m ON m.id=e.membre_id WHERE e.statut='active' AND e.deleted_at IS NULL AND e.slot_id IN (SELECT id FROM cda_professor_slots WHERE professeur_id=? AND annee_scolaire_id=? AND deleted_at IS NULL) ORDER BY m.nom,m.prenom");
        $enrollments->execute([$professorId,$yearId]);$students=[];
        foreach($enrollments->fetchAll()?:[] as $student)$students[(int)$student['slot_id']][]=$student['eleve_nom'];
        foreach($events as &$event)$event['students']=$students[(int)($event['slot_id']??0)]??[];
        unset($event);
        return ['professor'=>$professor,'year'=>$year,'disciplines'=>$d->fetchAll()?:[],'events'=>$events,'requests'=>$r->fetchAll()?:[],'plannedHours'=>$plannedHours,'workedHours'=>$workedHours];
    }

    public static function years(int $organisationId): array
    {
        // Certaines bases CDA utilisent `active`, d'autres n'ont aucun indicateur.
        // La liste annuelle ne doit jamais dépendre d'une colonne optionnelle.
        $q=Database::connection()->prepare("SELECT id,libelle,date_debut,date_fin FROM annees_scolaires WHERE organisation_id=? ORDER BY date_debut DESC");
        $q->execute([$organisationId]);return $q->fetchAll()?:[];
    }

    public static function generate(int $organisationId,int $professorId,int $yearId,int $userId): int
    {
        $pdo=Database::connection();
        $y=$pdo->prepare("SELECT date_debut,date_fin FROM annees_scolaires WHERE id=? AND organisation_id=?");
        $y->execute([$yearId,$organisationId]);$year=$y->fetch();
        if(!$year)throw new \RuntimeException('Année scolaire introuvable.');
        $slots=$pdo->prepare("SELECT * FROM cda_professor_slots WHERE professeur_id=? AND annee_scolaire_id=? AND statut='valide' AND deleted_at IS NULL");
        $slots->execute([$professorId,$yearId]);$slots=$slots->fetchAll()?:[];
        if(!$slots)throw new \RuntimeException('Le planning prévisionnel doit être validé avant de générer l’année.');
        // Zone B 2026-2027, vacances d'été comprises.
        $holidays=[
            ['2026-07-04','2026-08-31'],
            ['2026-10-17','2026-11-01'],
            ['2026-12-19','2027-01-03'],
            ['2027-02-20','2027-03-07'],
            ['2027-04-17','2027-05-02'],
            ['2027-07-03','2027-08-31'],
        ];
        $days=['dimanche'=>0,'lundi'=>1,'mardi'=>2,'mercredi'=>3,'jeudi'=>4,'vendredi'=>5,'samedi'=>6];
        $insert=$pdo->prepare("INSERT IGNORE INTO cda_professor_year_events(organisation_id,annee_scolaire_id,professeur_id,slot_id,discipline_id,salle_id,date_evenement,heure_debut,heure_fin,type_evenement,statut,created_by) VALUES(?,?,?,?,?,?,?,?,?,?,'planifie',?)");
        $count=0;$pdo->beginTransaction();
        try{
            $pdo->prepare("DELETE FROM cda_professor_year_events
                           WHERE professeur_id=? AND annee_scolaire_id=?
                             AND type_evenement='cours' AND statut='planifie'
                             AND (date_evenement BETWEEN '2026-07-04' AND '2026-08-31'
                               OR date_evenement BETWEEN '2026-10-17' AND '2026-11-01'
                               OR date_evenement BETWEEN '2026-12-19' AND '2027-01-03'
                               OR date_evenement BETWEEN '2027-02-20' AND '2027-03-07'
                               OR date_evenement BETWEEN '2027-04-17' AND '2027-05-02'
                               OR date_evenement BETWEEN '2027-07-03' AND '2027-08-31')")
                ->execute([$professorId,$yearId]);
            // Les cours passés restent historiques. Seuls les cours futurs planifiés
            // sont reconstruits lors d'un changement de contrat ou de planning.
            $pdo->prepare("DELETE e FROM cda_professor_year_events e WHERE e.professeur_id=? AND e.annee_scolaire_id=? AND e.type_evenement='cours' AND e.statut='planifie' AND e.date_evenement>=CURRENT_DATE AND NOT EXISTS(SELECT 1 FROM cda_professor_change_requests r WHERE r.event_id=e.id AND r.statut='validee')")
                ->execute([$professorId,$yearId]);
            $from=strtotime($year['date_debut']);$to=strtotime($year['date_fin']);
            for($ts=$from;$ts<=$to;$ts+=86400){
                $date=date('Y-m-d',$ts);
                if($date<date('Y-m-d'))continue;
                $holiday=false;foreach($holidays as[$a,$b])if($date>=$a&&$date<=$b){$holiday=true;break;}
                if($holiday)continue;
                $applicable=[];
                foreach($slots as$slot){
                    if((int)date('w',$ts)!==($days[$slot['jour_semaine']]??-1))continue;
                    $slotFrom=$slot['date_debut']?:$year['date_debut'];$slotTo=$slot['date_fin']?:$year['date_fin'];
                    if($date>=$slotFrom&&$date<=$slotTo)$applicable[]=$slot;
                }
                // Une plage bornée à une période est un avenant temporaire : elle
                // remplace les plages d'origine ce jour-là, puis l'origine reprend.
                $temporary=array_values(array_filter($applicable,static fn($slot)=>
                    (!empty($slot['date_debut'])&&$slot['date_debut']>$year['date_debut'])
                    ||(!empty($slot['date_fin'])&&$slot['date_fin']<$year['date_fin'])
                ));
                if($temporary)$applicable=$temporary;
                foreach($applicable as$slot){
                    $insert->execute([$organisationId,$yearId,$professorId,$slot['id'],$slot['discipline_id'],$slot['salle_id'],$date,$slot['heure_debut'],$slot['heure_fin'],'cours',$userId?:null]);
                    $count+=$insert->rowCount();
                }
            }
            $pdo->commit();return $count;
        }catch(\Throwable $e){$pdo->rollBack();throw $e;}
    }

    public static function requestChange(int $organisationId,int $professorId,array $data,int $userId): void
    {
        $event=Database::connection()->prepare("SELECT date_evenement FROM cda_professor_year_events WHERE id=? AND organisation_id=? AND professeur_id=? AND deleted_at IS NULL");
        $event->execute([(int)$data['event_id'],$organisationId,$professorId]);
        $eventDate=$event->fetchColumn();
        if(!$eventDate)throw new \RuntimeException('Cours introuvable.');
        if($eventDate<date('Y-m-d'))throw new \RuntimeException('Une séance passée ne peut plus être déplacée ou annulée.');
        $q=Database::connection()->prepare("INSERT INTO cda_professor_change_requests(organisation_id,annee_scolaire_id,professeur_id,event_id,type_demande,date_proposee,heure_debut_proposee,heure_fin_proposee,motif,statut,requested_by) VALUES(?,?,?,?,?,?,?,?,?,'en_attente',?)");
        $q->execute([$organisationId,(int)$data['annee_scolaire_id'],$professorId,(int)$data['event_id'],$data['type_demande'],$data['date_proposee']?:null,$data['heure_debut_proposee']?:null,$data['heure_fin_proposee']?:null,trim((string)$data['motif']),$userId]);
    }

    public static function decide(int $requestId,bool $approve,int $userId): void
    {
        $pdo=Database::connection();$pdo->beginTransaction();
        try{
            $q=$pdo->prepare("SELECT * FROM cda_professor_change_requests WHERE id=? AND statut='en_attente' FOR UPDATE");$q->execute([$requestId]);$r=$q->fetch();
            if(!$r)throw new \RuntimeException('Demande déjà traitée ou introuvable.');
            if($approve){
                if($r['type_demande']==='annulation'){
                    $pdo->prepare("UPDATE cda_professor_year_events SET statut='annule',updated_at=NOW() WHERE id=?")->execute([$r['event_id']]);
                }else{
                    $pdo->prepare("UPDATE cda_professor_year_events SET date_evenement=COALESCE(?,date_evenement),heure_debut=COALESCE(?,heure_debut),heure_fin=COALESCE(?,heure_fin),type_evenement=IF(?='rattrapage','rattrapage',type_evenement),updated_at=NOW() WHERE id=?")->execute([$r['date_proposee'],$r['heure_debut_proposee'],$r['heure_fin_proposee'],$r['type_demande'],$r['event_id']]);
                }
            }
            $pdo->prepare("UPDATE cda_professor_change_requests SET statut=?,decided_by=?,decided_at=NOW() WHERE id=?")->execute([$approve?'validee':'refusee',$userId,$requestId]);
            $pdo->commit();
        }catch(\Throwable $e){$pdo->rollBack();throw $e;}
    }
}
