<?php
namespace App\Models;

use App\Core\Database;

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

    public static function pedagogySummary(int $professorId): array
    {
        $pdo=Database::connection();
        $year=(int)$pdo->query("SELECT id FROM annees_scolaires WHERE active=1 ORDER BY date_debut DESC LIMIT 1")->fetchColumn();
        $days=$pdo->prepare("SELECT jour_semaine,MIN(heure_debut) heure_debut,MAX(heure_fin) heure_fin,COUNT(*) creneaux FROM cda_professor_slots WHERE professeur_id=? AND annee_scolaire_id=? AND statut='valide' AND deleted_at IS NULL GROUP BY jour_semaine ORDER BY FIELD(jour_semaine,'lundi','mardi','mercredi','jeudi','vendredi','samedi','dimanche')");
        $days->execute([$professorId,$year]);
        $waiting=$pdo->prepare(
            "SELECT d.libelle discipline,COUNT(DISTINCT r.id) total
             FROM professeur_disciplines pd
             JOIN disciplines d ON d.id=pd.discipline_id
             LEFT JOIN demandes_inscription r
               ON r.deleted_at IS NULL
              AND r.statut IN('file_attente','a_etudier','creneau_propose')
              AND COALESCE(NULLIF(r.discipline_id,0),(
                    SELECT a.discipline_id FROM ateliers a
                    WHERE a.id=r.atelier_id LIMIT 1
                  ))=d.id
              AND COALESCE(NULLIF(r.annee_scolaire_id,0),(
                    SELECT a.annee_scolaire_id FROM ateliers a
                    WHERE a.id=r.atelier_id LIMIT 1
                  ),?)=?
             WHERE pd.professeur_id=? AND pd.actif=1 AND pd.deleted_at IS NULL
             GROUP BY d.id,d.libelle ORDER BY d.libelle"
        );
        $waiting->execute([$year,$year,$professorId]);
        $students=$pdo->prepare("SELECT ps.type_cours,d.libelle discipline,COUNT(e.id) total FROM cda_professor_slots ps JOIN disciplines d ON d.id=ps.discipline_id LEFT JOIN cda_slot_enrollments e ON e.slot_id=ps.id AND e.statut='active' AND e.deleted_at IS NULL WHERE ps.professeur_id=? AND ps.annee_scolaire_id=? AND ps.statut='valide' AND ps.deleted_at IS NULL GROUP BY ps.type_cours,d.id,d.libelle");
        $students->execute([$professorId,$year]);
        $hours=$pdo->prepare(
            "SELECT d.libelle discipline,
                    ROUND(SUM(TIMESTAMPDIFF(MINUTE,ps.heure_debut,ps.heure_fin))/60,2) heures
             FROM cda_professor_slots ps
             JOIN disciplines d ON d.id=ps.discipline_id
             WHERE ps.professeur_id=? AND ps.annee_scolaire_id=?
               AND ps.statut='valide' AND ps.deleted_at IS NULL
             GROUP BY d.id,d.libelle ORDER BY d.libelle"
        );
        $hours->execute([$professorId,$year]);
        $hoursByDiscipline=$hours->fetchAll()?:[];
        return [
            'year_id'=>$year,'days'=>$days->fetchAll()?:[],
            'waiting'=>$waiting->fetchAll()?:[],'students'=>$students->fetchAll()?:[],
            'hours_by_discipline'=>$hoursByDiscipline,
            'weekly_hours'=>array_sum(array_map(static fn(array $row): float=>(float)$row['heures'],$hoursByDiscipline)),
        ];
    }

    public static function all(): array
    {
        return Database::connection()->query(
            "SELECT
                p.id,
                p.code_professeur,
                p.nom,
                p.prenom,
                p.email,
                p.telephone_principal AS telephone,
                p.telephone_secondaire,
                p.actif,
                COUNT(DISTINCT pd.discipline_id) AS nb_disciplines,
                COUNT(DISTINCT cp.id) AS nb_contrats,
                SUM(
                    CASE
                        WHEN cp.statut='actif'
                         AND cp.deleted_at IS NULL
                        THEN 1 ELSE 0
                    END
                ) AS nb_contrats_actifs
             FROM professeurs p
             LEFT JOIN professeur_disciplines pd
                    ON pd.professeur_id=p.id
                   AND pd.actif=1
                   AND pd.deleted_at IS NULL
             LEFT JOIN contrats_professeurs cp
                    ON cp.professeur_id=p.id
                   AND cp.deleted_at IS NULL
             WHERE p.deleted_at IS NULL
             GROUP BY
                p.id,p.code_professeur,p.nom,p.prenom,
                p.email,p.telephone_principal,
                p.telephone_secondaire,p.actif
             ORDER BY p.nom,p.prenom"
        )->fetchAll();
    }

    public static function find(int $id): ?array
    {
        $stmt=Database::connection()->prepare(
            "SELECT
                p.*,
                p.telephone_principal AS telephone,
                u.email AS user_email,u.statut AS user_status,u.actif AS user_active,
                u.derniere_connexion_at AS user_last_login
             FROM professeurs p
             LEFT JOIN utilisateurs u ON u.id=p.utilisateur_id AND u.deleted_at IS NULL
             WHERE p.id=:id
               AND p.deleted_at IS NULL
             LIMIT 1"
        );
        $stmt->execute(['id'=>$id]);

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

    public static function createOrLinkAccount(int $professorId,string $password): array
    {
        $pdo=Database::connection();$pdo->beginTransaction();
        try{
            $q=$pdo->prepare("SELECT * FROM professeurs WHERE id=? AND deleted_at IS NULL FOR UPDATE");$q->execute([$professorId]);$professor=$q->fetch();
            if(!$professor)throw new \RuntimeException('Professeur introuvable.');
            if((int)($professor['utilisateur_id']??0)>0){$pdo->commit();return ['created'=>false,'user_id'=>(int)$professor['utilisateur_id']];}
            $email=filter_var((string)($professor['email']??''),FILTER_VALIDATE_EMAIL);if(!$email)throw new \RuntimeException('Renseignez une adresse e-mail valide dans les informations du professeur.');
            $find=$pdo->prepare("SELECT id FROM utilisateurs WHERE organisation_id=? AND email=? AND deleted_at IS NULL LIMIT 1");$find->execute([(int)$professor['organisation_id'],$email]);$userId=(int)$find->fetchColumn();$created=false;
            if(!$userId){
                if(mb_strlen($password)<10)throw new \RuntimeException('Le mot de passe initial doit contenir au moins 10 caractères.');
                $pdo->prepare("INSERT INTO utilisateurs(organisation_id,nom,prenom,email,mot_de_passe,statut,actif,created_at,updated_at) VALUES(?,?,?,?,?,'actif',1,NOW(),NOW())")
                    ->execute([(int)$professor['organisation_id'],$professor['nom'],$professor['prenom'],$email,password_hash($password,PASSWORD_DEFAULT)]);
                $userId=(int)$pdo->lastInsertId();$created=true;
            }
            $role=$pdo->prepare("SELECT id FROM roles WHERE organisation_id=? AND code='professeur_animateur' AND actif=1 AND deleted_at IS NULL LIMIT 1");$role->execute([(int)$professor['organisation_id']]);$roleId=(int)$role->fetchColumn();
            if(!$roleId)throw new \RuntimeException('Le rôle Professeur / Animateur est introuvable.');
            $pdo->prepare('INSERT IGNORE INTO utilisateur_roles(utilisateur_id,role_id) VALUES(?,?)')->execute([$userId,$roleId]);
            $pdo->prepare('UPDATE professeurs SET utilisateur_id=?,updated_at=NOW() WHERE id=?')->execute([$userId,$professorId]);
            $pdo->commit();return ['created'=>$created,'user_id'=>$userId];
        }catch(\Throwable $e){if($pdo->inTransaction())$pdo->rollBack();throw $e;}
    }

    public static function disciplines(int $professorId): array
    {
        $stmt=Database::connection()->prepare(
            "SELECT
                d.id,
                d.code,
                d.libelle,
                pd.principale,
                pd.date_debut,
                pd.date_fin,
                COALESCE(i.valeur_icone,'✨') AS icone,
                COALESCE(
                    d.couleur_accent,
                    i.couleur_defaut,
                    '#64748B'
                ) AS couleur
             FROM professeur_disciplines pd
             INNER JOIN disciplines d
                     ON d.id=pd.discipline_id
             LEFT JOIN discipline_icons i
                    ON i.id=d.icon_id
             WHERE pd.professeur_id=:professeur_id
               AND pd.actif=1
               AND pd.deleted_at IS NULL
             ORDER BY pd.principale DESC,d.libelle"
        );
        $stmt->execute(['professeur_id'=>$professorId]);

        return $stmt->fetchAll();
    }

    public static function availableDisciplines(): array
    {
        return Database::connection()->query(
            "SELECT
                d.id,
                d.code,
                d.libelle,
                p.libelle AS pole_libelle,
                COALESCE(i.valeur_icone,'✨') AS icone,
                COALESCE(
                    d.couleur_accent,
                    i.couleur_defaut,
                    '#64748B'
                ) AS couleur
             FROM disciplines d
             LEFT JOIN poles p ON p.id=d.pole_id
             LEFT JOIN discipline_icons i ON i.id=d.icon_id
             WHERE d.actif=1
               AND d.deleted_at IS NULL
             ORDER BY p.libelle,d.libelle"
        )->fetchAll();
    }

    public static function contracts(int $professorId): array
    {
        return ProfessorContract::forProfessor($professorId);
    }

    public static function alerts(int $professorId): array
    {
        $stmt=Database::connection()->prepare(
            "SELECT *
             FROM professeur_alertes
             WHERE professeur_id=:professeur_id
               AND deleted_at IS NULL
             ORDER BY
                FIELD(priorite,'urgente','haute','normale','basse'),
                date_declenchement,
                id DESC"
        );
        $stmt->execute(['professeur_id'=>$professorId]);

        return $stmt->fetchAll();
    }

    public static function consents(int $professorId): array
    {
        return ProfessorConsent::all($professorId);
    }

    public static function replaceDisciplines(
        int $professorId,
        array $disciplineIds,
        ?int $mainDisciplineId
    ): void {
        $pdo=Database::connection();
        $pdo->beginTransaction();

        try {
            $pdo->prepare(
                "UPDATE professeur_disciplines
                 SET actif=0,
                     deleted_at=CURRENT_TIMESTAMP
                 WHERE professeur_id=:professeur_id
                   AND actif=1
                   AND deleted_at IS NULL"
            )->execute(['professeur_id'=>$professorId]);

            $insert=$pdo->prepare(
                "INSERT INTO professeur_disciplines(
                    professeur_id,discipline_id,principale,date_debut,date_fin,actif,deleted_at
                 ) VALUES(
                    :professeur_id,:discipline_id,:principale,CURRENT_DATE,NULL,1,NULL
                 )
                 ON DUPLICATE KEY UPDATE
                    principale=VALUES(principale),
                    date_fin=NULL,
                    actif=1,
                    deleted_at=NULL"
            );

            foreach(array_unique(array_map('intval',$disciplineIds)) as $disciplineId){
                if($disciplineId<=0){
                    continue;
                }

                $insert->execute([
                    'professeur_id'=>$professorId,
                    'discipline_id'=>$disciplineId,
                    'principale'=>
                        $mainDisciplineId===$disciplineId ? 1 : 0,
                ]);
            }

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