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

use App\Core\Database;
use App\Core\Audit\AuditLogger;
use RuntimeException;

final class ProfessorPlanningEvent
{
    public static function meetingsForDirection(int $organisationId): array
    {
        $q=Database::connection()->prepare(
            "SELECT e.id,e.public_token,e.organisation_id,e.type_evenement,e.titre,e.description,
                    e.date_evenement,e.heure_debut,e.heure_fin,e.created_by,
                    e.created_at,e.updated_at,e.deleted_at,
                    GROUP_CONCAT(CONCAT(p.prenom,' ',p.nom) ORDER BY p.nom,p.prenom SEPARATOR ', ') invites,
                    GROUP_CONCAT(p.id ORDER BY p.id SEPARATOR ',') invite_ids
             FROM cda_professor_planning_events e
             INNER JOIN cda_professor_planning_event_guests g ON g.event_id=e.id
             INNER JOIN professeurs p ON p.id=g.professeur_id AND p.deleted_at IS NULL
             WHERE e.organisation_id=? AND e.type_evenement='reunion' AND e.deleted_at IS NULL
             GROUP BY e.id,e.public_token,e.organisation_id,e.type_evenement,e.titre,e.description,
                      e.date_evenement,e.heure_debut,e.heure_fin,e.created_by,
                      e.created_at,e.updated_at,e.deleted_at
             ORDER BY e.date_evenement DESC,e.heure_debut DESC"
        );
        $q->execute([$organisationId]);
        return $q->fetchAll()?:[];
    }

    public static function activeProfessors(int $organisationId): array
    {
        $q=Database::connection()->prepare(
            "SELECT id,nom,prenom FROM professeurs
             WHERE organisation_id=? AND actif=1 AND deleted_at IS NULL
             ORDER BY nom,prenom"
        );
        $q->execute([$organisationId]);
        return $q->fetchAll()?:[];
    }

    public static function ensureStorage(): void
    {
        $pdo=Database::connection();
        $pdo->exec(
            "CREATE TABLE IF NOT EXISTS cda_professor_planning_events (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                public_token CHAR(64) NOT NULL,
                organisation_id BIGINT UNSIGNED NOT NULL,
                type_evenement VARCHAR(30) NOT NULL DEFAULT 'reunion',
                titre VARCHAR(190) NOT NULL,
                description TEXT NULL,
                date_evenement DATE NOT NULL,
                heure_debut TIME NOT NULL,
                heure_fin TIME NOT NULL,
                created_by BIGINT UNSIGNED NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
                deleted_at TIMESTAMP NULL DEFAULT NULL,
                PRIMARY KEY(id),
                UNIQUE KEY uq_cda_planning_event_token(public_token),
                KEY idx_cda_planning_events_org_date(organisation_id,date_evenement)
             ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
        );
        $pdo->exec(
            "CREATE TABLE IF NOT EXISTS cda_professor_planning_event_guests (
                event_id BIGINT UNSIGNED NOT NULL,
                professeur_id BIGINT UNSIGNED NOT NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY(event_id,professeur_id),
                KEY idx_cda_planning_guest_prof(professeur_id)
             ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
        );
    }

    public static function all(int $organisationId,int $professorId): array
    {
        $pdo=Database::connection();
        $events=$pdo->prepare(
            "SELECT e.* FROM cda_professor_planning_events e
             INNER JOIN cda_professor_planning_event_guests g ON g.event_id=e.id
             WHERE e.organisation_id=? AND g.professeur_id=? AND e.deleted_at IS NULL
             ORDER BY e.date_evenement,e.heure_debut"
        );
        $events->execute([$organisationId,$professorId]);
        $requests=$pdo->prepare(
            "SELECT r.*,s.jour_semaine,s.heure_debut,s.heure_fin,d.libelle discipline_libelle
             FROM cda_professor_planning_move_requests r
             INNER JOIN cda_professor_slots s ON s.id=r.slot_id
             INNER JOIN disciplines d ON d.id=s.discipline_id
             WHERE r.organisation_id=? AND r.professeur_id=?
             ORDER BY FIELD(r.statut,'en_attente','refusee','validee'),r.created_at DESC"
        );
        $requests->execute([$organisationId,$professorId]);
        return ['events'=>$events->fetchAll()?:[],'requests'=>$requests->fetchAll()?:[]];
    }

    public static function createMeeting(int $organisationId,array $professorIds,array $data,int $userId): void
    {
        self::ensureStorage();
        if(!$professorIds)throw new RuntimeException('Invitez au moins un professeur.');
        $date=(string)($data['date_evenement']??'');$from=(string)($data['heure_debut']??'');$to=(string)($data['heure_fin']??'');
        if(!preg_match('/^\d{4}-\d{2}-\d{2}$/',$date)||!preg_match('/^\d{2}:\d{2}$/',$from)||!preg_match('/^\d{2}:\d{2}$/',$to)||$to<=$from)throw new RuntimeException('Renseignez une date et des horaires de réunion valides.');
        $title=trim((string)($data['titre']??''));if($title==='')throw new RuntimeException('Indiquez l’objet de la réunion.');
        $pdo=Database::connection();$pdo->beginTransaction();
        try{
            $token=bin2hex(random_bytes(32));
            $q=$pdo->prepare("INSERT INTO cda_professor_planning_events(public_token,organisation_id,type_evenement,titre,description,date_evenement,heure_debut,heure_fin,created_by) VALUES(? ,?,'reunion',?,?,?,?,?,?)");
            $q->execute([$token,$organisationId,$title,trim((string)($data['description']??''))?:null,$date,$from,$to,$userId?:null]);
            $eventId=(int)$pdo->lastInsertId();
            $check=$pdo->prepare("SELECT id FROM professeurs WHERE id=? AND organisation_id=? AND actif=1 AND deleted_at IS NULL");
            $guest=$pdo->prepare("INSERT INTO cda_professor_planning_event_guests(event_id,professeur_id) VALUES(?,?)");
            foreach(array_unique($professorIds) as $professorId){
                $professorId=(int)$professorId;
                $check->execute([$professorId,$organisationId]);
                if(!$check->fetchColumn())throw new RuntimeException('Un professeur invité est introuvable.');
                $guest->execute([$eventId,$professorId]);
                self::syncAnnualEvent($pdo,$organisationId,$eventId,$professorId,$data,$userId);
            }
            $pdo->commit();
            (new AuditLogger())->record('meeting.created','professor_planning','meeting',$token,null,[
                'date'=>$date,'professor_ids'=>array_values(array_unique(array_map('intval',$professorIds)))
            ]);
        }catch(\Throwable $e){$pdo->rollBack();throw $e;}
    }

    public static function addGuests(int $organisationId,string $token,array $professorIds,int $userId): int
    {
        if(!preg_match('/^[a-f0-9]{64}$/',$token))throw new RuntimeException('Réunion introuvable.');
        $professorIds=array_values(array_unique(array_filter(array_map('intval',$professorIds))));
        if(!$professorIds)throw new RuntimeException('Sélectionnez au moins un professeur à ajouter.');
        $pdo=Database::connection();$pdo->beginTransaction();
        try{
            $q=$pdo->prepare("SELECT * FROM cda_professor_planning_events WHERE public_token=? AND organisation_id=? AND type_evenement='reunion' AND deleted_at IS NULL FOR UPDATE");
            $q->execute([$token,$organisationId]);$event=$q->fetch();
            if(!$event)throw new RuntimeException('Réunion introuvable.');
            $check=$pdo->prepare("SELECT id FROM professeurs WHERE id=? AND organisation_id=? AND actif=1 AND deleted_at IS NULL");
            $exists=$pdo->prepare("SELECT 1 FROM cda_professor_planning_event_guests WHERE event_id=? AND professeur_id=?");
            $guest=$pdo->prepare("INSERT INTO cda_professor_planning_event_guests(event_id,professeur_id) VALUES(?,?)");
            $added=[];
            foreach($professorIds as $professorId){
                $check->execute([$professorId,$organisationId]);
                if(!$check->fetchColumn())throw new RuntimeException('Un professeur sélectionné est introuvable.');
                $exists->execute([$event['id'],$professorId]);
                if($exists->fetchColumn())continue;
                $guest->execute([$event['id'],$professorId]);
                self::syncAnnualEvent($pdo,$organisationId,(int)$event['id'],$professorId,$event,$userId);
                $added[]=$professorId;
            }
            $pdo->commit();
            if($added)(new AuditLogger())->record('meeting.guests_added','professor_planning','meeting',$token,null,['professor_ids'=>$added]);
            return count($added);
        }catch(\Throwable $e){if($pdo->inTransaction())$pdo->rollBack();throw $e;}
    }

    private static function syncAnnualEvent(\PDO $pdo,int $organisationId,int $eventId,int $professorId,array $event,int $userId): void
    {
        $date=(string)($event['date_evenement']??'');
        $year=$pdo->prepare(
            "SELECT id FROM annees_scolaires
             WHERE organisation_id=?
               AND (? BETWEEN date_debut AND date_fin
                    OR (date_debut>? AND date_debut<=DATE_ADD(?, INTERVAL 45 DAY)))
             ORDER BY CASE WHEN ? BETWEEN date_debut AND date_fin THEN 0 ELSE 1 END,
                      date_debut ASC
             LIMIT 1"
        );
        $year->execute([$organisationId,$date,$date,$date,$date]);$yearId=(int)$year->fetchColumn();
        if($yearId<1)throw new RuntimeException('Aucune année scolaire ne couvre cette réunion ni sa période de pré-rentrée.');
        $insert=$pdo->prepare(
            "INSERT INTO cda_professor_year_events
             (organisation_id,annee_scolaire_id,professeur_id,planning_event_id,date_evenement,
              heure_debut,heure_fin,type_evenement,statut,titre,commentaire,created_by)
             VALUES(?,?,?,?,?,?,?,'reunion','planifie',?,?,?)
             ON DUPLICATE KEY UPDATE
                date_evenement=VALUES(date_evenement),heure_debut=VALUES(heure_debut),
                heure_fin=VALUES(heure_fin),titre=VALUES(titre),commentaire=VALUES(commentaire),
                deleted_at=NULL,updated_at=NOW()"
        );
        $insert->execute([
            $organisationId,$yearId,$professorId,$eventId,$date,
            $event['heure_debut'],$event['heure_fin'],$event['titre'],
            trim((string)($event['description']??''))?:null,$userId?:null
        ]);
    }

    public static function requestMove(int $organisationId,int $professorId,array $data,int $userId): void
    {
        $slotId=(int)($data['slot_id']??0);$day=(string)($data['nouveau_jour']??'');$from=(string)($data['nouvelle_heure_debut']??'');$to=(string)($data['nouvelle_heure_fin']??'');$reason=trim((string)($data['motif']??''));
        if(!in_array($day,['lundi','mardi','mercredi','jeudi','vendredi','samedi','dimanche'],true)||!preg_match('/^\d{2}:\d{2}$/',$from)||!preg_match('/^\d{2}:\d{2}$/',$to)||$to<=$from||$reason==='')throw new RuntimeException('Renseignez le nouveau créneau et le motif de la demande.');
        $pdo=Database::connection();$check=$pdo->prepare("SELECT id FROM cda_professor_slots WHERE id=? AND organisation_id=? AND professeur_id=? AND deleted_at IS NULL");
        $check->execute([$slotId,$organisationId,$professorId]);if(!$check->fetchColumn())throw new RuntimeException('Ce créneau ne vous appartient pas.');
        $q=$pdo->prepare("INSERT INTO cda_professor_planning_move_requests(organisation_id,professeur_id,slot_id,nouveau_jour,nouvelle_heure_debut,nouvelle_heure_fin,motif,requested_by) VALUES(?,?,?,?,?,?,?,?)");
        $q->execute([$organisationId,$professorId,$slotId,$day,$from,$to,$reason,$userId?:null]);
    }

    public static function decideMove(int $organisationId,int $requestId,bool $approved,string $comment,int $userId): void
    {
        if(!$approved&&trim($comment)==='')throw new RuntimeException('Indiquez le motif du refus.');
        $pdo=Database::connection();$pdo->beginTransaction();
        try{
            $q=$pdo->prepare("SELECT * FROM cda_professor_planning_move_requests WHERE id=? AND organisation_id=? AND statut='en_attente' FOR UPDATE");
            $q->execute([$requestId,$organisationId]);$request=$q->fetch();if(!$request)throw new RuntimeException('Cette demande a déjà été traitée.');
            if($approved)$pdo->prepare("UPDATE cda_professor_slots SET jour_semaine=?,heure_debut=?,heure_fin=?,updated_at=NOW() WHERE id=? AND organisation_id=?")->execute([$request['nouveau_jour'],$request['nouvelle_heure_debut'],$request['nouvelle_heure_fin'],$request['slot_id'],$organisationId]);
            $pdo->prepare("UPDATE cda_professor_planning_move_requests SET statut=?,decision_comment=?,decided_by=?,decided_at=NOW() WHERE id=?")->execute([$approved?'validee':'refusee',trim($comment)?:null,$userId?:null,$requestId]);
            $pdo->commit();
        }catch(\Throwable $e){$pdo->rollBack();throw $e;}
    }
}
