<?php
namespace App\Models;

use App\Core\Database;
use Throwable;

final class Professor
{
    public static function all(string $query=''): array
    {
        $params=[];
        $where='p.deleted_at IS NULL';

        if($query!==''){
            $where.=' AND (
                p.nom LIKE :q OR p.prenom LIKE :q
                OR CONCAT(p.nom," ",p.prenom) LIKE :q
                OR p.email LIKE :q
                OR p.telephone_principal LIKE :q
                OR p.code_professeur LIKE :q
            )';
            $params['q']='%'.$query.'%';
        }

        $stmt=Database::connection()->prepare(
            "SELECT p.*,
                (SELECT COUNT(*) FROM groupe_professeurs gp
                 WHERE gp.professeur_id=p.id AND gp.actif=1) AS nb_groupes
             FROM professeurs p
             WHERE {$where}
             ORDER BY p.nom,p.prenom"
        );
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public static function active(): array
    {
        return Database::connection()->query(
            "SELECT id,nom,prenom
             FROM professeurs
             WHERE deleted_at IS NULL AND actif=1
             ORDER BY nom,prenom"
        )->fetchAll();
    }

    public static function find(int $id): ?array
    {
        $stmt=Database::connection()->prepare(
            "SELECT * FROM professeurs
             WHERE id=:id AND deleted_at IS NULL
             LIMIT 1"
        );
        $stmt->execute(['id'=>$id]);
        return $stmt->fetch() ?: null;
    }

    public static function groups(int $id): array
    {
        $stmt=Database::connection()->prepare(
            "SELECT g.*,a.libelle AS atelier_libelle,
                    v.jour_semaine,v.heure_debut,v.heure_fin
             FROM groupe_professeurs gp
             INNER JOIN groupes_cours g ON g.id=gp.groupe_id
             INNER JOIN ateliers a ON a.id=g.atelier_id
             LEFT JOIN groupe_planning_versions v
                    ON v.groupe_id=g.id AND v.date_fin_effet IS NULL
             WHERE gp.professeur_id=:id
               AND gp.actif=1
               AND g.deleted_at IS NULL
             ORDER BY FIELD(v.jour_semaine,
                 'lundi','mardi','mercredi','jeudi','vendredi','samedi','dimanche'),
                 v.heure_debut"
        );
        $stmt->execute(['id'=>$id]);
        return $stmt->fetchAll();
    }

    public static function create(array $data,array $account=[],?array $contract=null): int
    {
        $pdo=Database::connection();
        $pdo->beginTransaction();

        try{
            $userId=null;
            if(!empty($account['enabled'])){
                $find=$pdo->prepare("SELECT id FROM utilisateurs WHERE organisation_id=? AND email=? AND deleted_at IS NULL LIMIT 1");
                $find->execute([(int)$data['organisation_id'],$data['email']]);
                $userId=(int)$find->fetchColumn()?:null;

                if(!$userId){
                    $password=(string)($account['password']??'');
                    if(mb_strlen($password)<10)throw new \RuntimeException('Le mot de passe initial du compte professeur doit contenir au moins 10 caractères.');
                    $createUser=$pdo->prepare("INSERT INTO utilisateurs(organisation_id,nom,prenom,email,mot_de_passe,statut,actif,created_at,updated_at) VALUES(?,?,?,?,?,'actif',1,NOW(),NOW())");
                    $createUser->execute([(int)$data['organisation_id'],$data['nom'],$data['prenom'],$data['email'],password_hash($password,PASSWORD_DEFAULT)]);
                    $userId=(int)$pdo->lastInsertId();
                }

                $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)$data['organisation_id']]);$roleId=(int)$role->fetchColumn();
                if(!$roleId)throw new \RuntimeException('Le rôle Professeur / Animateur est introuvable. Installez d’abord la migration des rôles.');
                $pdo->prepare('INSERT IGNORE INTO utilisateur_roles(utilisateur_id,role_id) VALUES(?,?)')->execute([$userId,$roleId]);
            }

            $code=self::generateUniqueCode(
                (int)$data['organisation_id'],
                $data['nom'],
                $data['prenom']
            );

            $stmt=$pdo->prepare(
                "INSERT INTO professeurs(
                    organisation_id,utilisateur_id,code_professeur,civilite,nom,prenom,
                    date_naissance,email,telephone_principal,telephone_secondaire,
                    adresse_ligne1,adresse_ligne2,code_postal,ville,
                    statut_intervenant,type_contrat,date_debut_contrat,
                    date_fin_contrat,droit_image,rgpd_accepte,
                    dispense_mutuelle,dispense_mutuelle_date,teletravail_autorise,reunions_autorisees,notes,actif,public_token
                 ) VALUES(
                    :organisation_id,:utilisateur_id,:code_professeur,:civilite,:nom,:prenom,
                    :date_naissance,:email,:telephone_principal,:telephone_secondaire,
                    :adresse_ligne1,:adresse_ligne2,:code_postal,:ville,
                    :statut_intervenant,:type_contrat,:date_debut_contrat,
                    :date_fin_contrat,:droit_image,:rgpd_accepte,
                    :dispense_mutuelle,:dispense_mutuelle_date,:teletravail_autorise,:reunions_autorisees,:notes,1,:public_token
                 )"
            );

            $params=self::params($data,$code);$params['utilisateur_id']=$userId;
            $params['public_token']=bin2hex(random_bytes(32));
            $stmt->execute($params);
            $id=(int)$pdo->lastInsertId();
            if($contract){
                $pdo->prepare("INSERT INTO contrats_professeurs(professeur_id,type_contrat_id,date_debut,date_fin,fonction,unite_heures,contrat_principal,statut,created_by) VALUES(?,?,?,?,?,'periode',1,'actif',?)")
                    ->execute([$id,$contract['type_contrat_id'],$contract['date_debut'],$contract['date_fin'],'Professeur / Animateur',(int)($account['created_by']??0)?:null]);
            }
            $pdo->commit();
            return $id;
        }catch(Throwable $e){
            $pdo->rollBack();
            throw $e;
        }
    }

    public static function update(int $id,array $data): void
    {
        $stmt=Database::connection()->prepare(
            "UPDATE professeurs SET
                civilite=:civilite,
                nom=:nom,
                prenom=:prenom,
                date_naissance=:date_naissance,
                email=:email,
                telephone_principal=:telephone_principal,
                telephone_secondaire=:telephone_secondaire,
                adresse_ligne1=:adresse_ligne1,
                adresse_ligne2=:adresse_ligne2,
                code_postal=:code_postal,
                ville=:ville,
                statut_intervenant=:statut_intervenant,
                type_contrat=:type_contrat,
                date_debut_contrat=:date_debut_contrat,
                date_fin_contrat=:date_fin_contrat,
                droit_image=:droit_image,
                rgpd_accepte=:rgpd_accepte,
                dispense_mutuelle=:dispense_mutuelle,
                dispense_mutuelle_date=:dispense_mutuelle_date,
                teletravail_autorise=:teletravail_autorise,
                reunions_autorisees=:reunions_autorisees,
                notes=:notes,
                updated_at=CURRENT_TIMESTAMP
             WHERE id=:id AND deleted_at IS NULL"
        );

        $params=self::params($data,'');
        unset($params['organisation_id'],$params['code_professeur']);
        $params['id']=$id;
        $stmt->execute($params);
    }

    public static function archive(int $id): void
    {
        $stmt=Database::connection()->prepare(
            "UPDATE professeurs
             SET actif=0,deleted_at=CURRENT_TIMESTAMP,updated_at=CURRENT_TIMESTAMP
             WHERE id=:id AND deleted_at IS NULL"
        );
        $stmt->execute(['id'=>$id]);
    }

    public static function savePrimaryContract(int $professorId,array $contract,int $userId): void
    {
        $pdo=Database::connection();
        $q=$pdo->prepare("SELECT id FROM contrats_professeurs WHERE professeur_id=? AND contrat_principal=1 AND deleted_at IS NULL ORDER BY date_debut DESC,id DESC LIMIT 1");$q->execute([$professorId]);$id=(int)$q->fetchColumn();
        if($id){
            $pdo->prepare("UPDATE contrats_professeurs SET type_contrat_id=?,date_debut=?,date_fin=?,statut='actif' WHERE id=?")
                ->execute([$contract['type_contrat_id'],$contract['date_debut'],$contract['date_fin'],$id]);
            return;
        }
        $pdo->prepare("INSERT INTO contrats_professeurs(professeur_id,type_contrat_id,date_debut,date_fin,fonction,unite_heures,contrat_principal,statut,created_by) VALUES(?,?,?,?,?,'periode',1,'actif',?)")
            ->execute([$professorId,$contract['type_contrat_id'],$contract['date_debut'],$contract['date_fin'],'Professeur / Animateur',$userId?:null]);
    }

    private static function params(array $data,string $code): array
    {
        return [
            'organisation_id'=>$data['organisation_id'] ?? null,
            'code_professeur'=>$code,
            'civilite'=>$data['civilite'],
            'nom'=>$data['nom'],
            'prenom'=>$data['prenom'],
            'date_naissance'=>$data['date_naissance'] ?: null,
            'email'=>$data['email'] ?: null,
            'telephone_principal'=>$data['telephone_principal'] ?: null,
            'telephone_secondaire'=>$data['telephone_secondaire'] ?: null,
            'adresse_ligne1'=>$data['adresse_ligne1'] ?: null,
            'adresse_ligne2'=>$data['adresse_ligne2'] ?: null,
            'code_postal'=>$data['code_postal'] ?: null,
            'ville'=>$data['ville'] ?: null,
            'statut_intervenant'=>$data['statut_intervenant'],
            'type_contrat'=>$data['type_contrat'] ?: null,
            'date_debut_contrat'=>$data['date_debut_contrat'] ?: null,
            'date_fin_contrat'=>$data['date_fin_contrat'] ?: null,
            'droit_image'=>$data['droit_image'],
            'rgpd_accepte'=>$data['rgpd_accepte'],
            'dispense_mutuelle'=>$data['dispense_mutuelle'],
            'dispense_mutuelle_date'=>$data['dispense_mutuelle']
                ? ($data['dispense_mutuelle_date'] ?: null)
                : null,
            'teletravail_autorise'=>(int)($data['teletravail_autorise']??0),
            'reunions_autorisees'=>(int)($data['reunions_autorisees']??0),
            'notes'=>$data['notes'] ?: null,
        ];
    }

    private static function generateUniqueCode(
        int $organisationId,
        string $nom,
        string $prenom
    ): string {
        $normalize=static function(string $value): string{
            $value=iconv('UTF-8','ASCII//TRANSLIT//IGNORE',$value) ?: $value;
            $value=strtoupper($value);
            return preg_replace('/[^A-Z0-9]/','',$value) ?: '';
        };

        $nomN=$normalize($nom);
        $prenomN=$normalize($prenom);

        $base='P'.substr($nomN,0,8).substr($prenomN,0,3);
        $base=substr($base,0,12);
        $candidate=$base;
        $counter=1;

        $pdo=Database::connection();
        $stmt=$pdo->prepare(
            "SELECT 1 FROM professeurs
             WHERE organisation_id=:org
               AND code_professeur=:code
             LIMIT 1"
        );

        while(true){
            $stmt->execute(['org'=>$organisationId,'code'=>$candidate]);
            if(!$stmt->fetchColumn()) return $candidate;

            $counter++;
            $suffix=(string)$counter;
            $candidate=substr($base,0,12-strlen($suffix)).$suffix;
        }
    }
}
