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

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

final class EnrollmentRequest
{
    private static array $columns=[];

    public static function activeAnnualFile(int $organisationId,int $clientId): ?array
    {
        $q=Database::connection()->prepare("SELECT d.id,a.id annee_scolaire_id,a.libelle,a.date_debut,a.date_fin FROM cda_dossiers_adherents_annuels d INNER JOIN annees_scolaires a ON a.id=d.annee_scolaire_id AND a.active=1 WHERE d.organisation_id=? AND d.client_id=? AND d.statut='actif' AND d.deleted_at IS NULL LIMIT 1");
        $q->execute([$organisationId,$clientId]);return $q->fetch()?:null;
    }

    public static function workshops(int $organisationId): array
    {
        $q=Database::connection()->prepare("SELECT a.id,a.code,a.libelle FROM ateliers a INNER JOIN annees_scolaires y ON y.id=a.annee_scolaire_id AND y.active=1 WHERE a.organisation_id=? AND a.deleted_at IS NULL AND a.actif=1 ORDER BY a.libelle");
        $q->execute([$organisationId]);return $q->fetchAll()?:[];
    }

    public static function create(array $data): int
    {
        $annual=self::activeAnnualFile((int)$data['organisation_id'],(int)$data['adherent_id']);
        if(!$annual)throw new RuntimeException('Activez d’abord le dossier de l’année scolaire en cours.');
        $check=Database::connection()->prepare("SELECT COUNT(*) FROM ateliers WHERE id=? AND organisation_id=? AND annee_scolaire_id=? AND actif=1 AND deleted_at IS NULL");
        $check->execute([(int)$data['atelier_id'],(int)$data['organisation_id'],(int)$annual['annee_scolaire_id']]);
        if(!(int)$check->fetchColumn())throw new RuntimeException('Cet atelier n’est pas ouvert pour l’année scolaire active.');
        $duplicate=Database::connection()->prepare(
            "SELECT COUNT(*)
             FROM demandes_inscription d
             INNER JOIN ateliers a ON a.id=d.atelier_id
             WHERE d.organisation_id=?
               AND d.membre_id=?
               AND d.atelier_id=?
               AND a.annee_scolaire_id=?
               AND d.statut IN('file_attente','a_etudier','creneau_propose','validee')
               AND d.deleted_at IS NULL"
        );
        $duplicate->execute([
            (int)$data['organisation_id'],
            (int)$data['membre_id'],
            (int)$data['atelier_id'],
            (int)$annual['annee_scolaire_id'],
        ]);
        if((int)$duplicate->fetchColumn()>0)throw new RuntimeException('Une demande ou une inscription existe déjà pour cet élève et cet atelier cette année.');
        $stmt=Database::connection()->prepare("INSERT INTO demandes_inscription(organisation_id,membre_id,atelier_id,adherent_id,disponibilites,commentaire_famille,statut,created_by) VALUES(:organisation_id,:membre_id,:atelier_id,:adherent_id,:disponibilites,:commentaire_famille,'file_attente',:created_by)");
        unset($data['professeur_souhaite_id']);$stmt->execute($data);return (int)Database::connection()->lastInsertId();
    }

    public static function createDiscipline(array $data): int
    {
        $year=Database::connection()->prepare(
            "SELECT id,libelle
             FROM annees_scolaires
             WHERE organisation_id=?
               AND active=1
             ORDER BY id DESC
             LIMIT 1"
        );
        $year->execute([(int)$data['organisation_id']]);
        $annual=$year->fetch();
        if(!$annual)throw new RuntimeException('Aucune année scolaire active ne permet d’enregistrer la demande.');
        $q=Database::connection()->prepare(
            "SELECT COUNT(*)
             FROM disciplines d
             WHERE d.id=?
               AND d.organisation_id=?
               AND d.actif=1
               AND d.deleted_at IS NULL"
        );
        $q->execute([(int)$data['discipline_id'],(int)$data['organisation_id']]);
        if(!(int)$q->fetchColumn())throw new RuntimeException('Cette discipline est inactive ou archivée.');
        $dup=Database::connection()->prepare("SELECT COUNT(*) FROM demandes_inscription WHERE organisation_id=? AND membre_id=? AND discipline_id=? AND annee_scolaire_id=? AND statut IN('file_attente','a_etudier','creneau_propose','validee') AND deleted_at IS NULL");
        $dup->execute([(int)$data['organisation_id'],(int)$data['membre_id'],(int)$data['discipline_id'],(int)$annual['annee_scolaire_id']]);
        if((int)$dup->fetchColumn())throw new RuntimeException('Une demande ou une inscription existe déjà pour cette discipline cette année.');
        $stmt=Database::connection()->prepare("INSERT INTO demandes_inscription(organisation_id,membre_id,atelier_id,discipline_id,annee_scolaire_id,adherent_id,disponibilites,commentaire_famille,instrument_loan_type,statut,created_by) VALUES(?,?,NULL,?,?,?,?,?,?,'file_attente',?)");
        $stmt->execute([(int)$data['organisation_id'],(int)$data['membre_id'],(int)$data['discipline_id'],(int)$annual['annee_scolaire_id'],(int)$data['adherent_id'],$data['disponibilites'],$data['commentaire_famille'],$data['instrument_loan_type']??'none',$data['created_by']]);
        return (int)Database::connection()->lastInsertId();
    }

    public static function portalMembers(int $organisationId,int $clientId): array
    {
        $q=Database::connection()->prepare(
            "SELECT m.id,m.nom,m.prenom,m.date_naissance
             FROM membres_famille m
             INNER JOIN familles f ON f.id=m.famille_id
             WHERE f.organisation_id=? AND f.client_id=?
               AND f.deleted_at IS NULL AND m.deleted_at IS NULL
             ORDER BY m.prenom,m.nom,m.id"
        );
        $q->execute([$organisationId,$clientId]);
        return $q->fetchAll()?:[];
    }

    public static function portalMemberOwnedBy(int $organisationId,int $clientId,int $memberId): bool
    {
        $q=Database::connection()->prepare(
            "SELECT COUNT(*) FROM membres_famille m
             INNER JOIN familles f ON f.id=m.famille_id
             WHERE m.id=? AND f.organisation_id=? AND f.client_id=?
               AND f.deleted_at IS NULL AND m.deleted_at IS NULL"
        );
        $q->execute([$memberId,$organisationId,$clientId]);
        return (int)$q->fetchColumn()===1;
    }

    public static function cancelFromPortal(int $id,int $organisationId,int $clientId,int $userId): void
    {
        $pdo=Database::connection();$pdo->beginTransaction();
        try{
            $q=$pdo->prepare(
                "SELECT d.statut FROM demandes_inscription d
                 INNER JOIN membres_famille m ON m.id=d.membre_id
                 INNER JOIN familles f ON f.id=m.famille_id
                 WHERE d.id=? AND d.organisation_id=? AND f.client_id=?
                   AND d.deleted_at IS NULL LIMIT 1 FOR UPDATE"
            );
            $q->execute([$id,$organisationId,$clientId]);
            $status=$q->fetchColumn();
            if($status===false)throw new RuntimeException('Demande introuvable.');
            if(!in_array((string)$status,['file_attente','a_etudier','creneau_propose'],true))
                throw new RuntimeException('Cette demande ne peut plus être annulée depuis le portail Famille.');
            $pdo->prepare("UPDATE demandes_inscription SET statut='abandonnee',updated_at=NOW() WHERE id=?")
                ->execute([$id]);
            $pdo->commit();
        }catch(Throwable $e){if($pdo->inTransaction())$pdo->rollBack();throw $e;}
    }

    public static function forClient(int $clientId): array
    {
        $q=Database::connection()->prepare(
            "SELECT d.*,
                    COALESCE(x.libelle,a.libelle,'Discipline ou cours archivé') atelier_libelle,
                    COALESCE(x.code,a.code,'—') atelier_code,
                    m.nom membre_nom,
                    m.prenom membre_prenom
             FROM demandes_inscription d
             INNER JOIN membres_famille m ON m.id=d.membre_id
             INNER JOIN familles f ON f.id=m.famille_id
             LEFT JOIN ateliers a ON a.id=d.atelier_id
             LEFT JOIN disciplines x ON x.id=COALESCE(NULLIF(d.discipline_id,0),a.discipline_id)
             WHERE f.client_id=?
               AND d.deleted_at IS NULL
             ORDER BY d.created_at DESC,d.id DESC"
        );
        $q->execute([$clientId]);
        return $q->fetchAll() ?: [];
    }

    public static function waitingList(int $organisationId,string $status=''): array
    {
        $args=[$organisationId];
        $filter='';
        if($status!==''){
            $filter=' AND d.statut=?';
            $args[]=$status;
        }
        $q=Database::connection()->prepare(
            "SELECT d.*,
                    COALESCE(x.libelle,a.libelle,'Discipline ou cours archivé') atelier_libelle,
                    COALESCE(x.code,a.code,'—') atelier_code,
                    m.nom membre_nom,
                    m.prenom membre_prenom,
                    c.id client_id,
                    c.code_client,
                    c.nom adherent_nom,
                    c.prenom adherent_prenom
             FROM demandes_inscription d
             INNER JOIN membres_famille m ON m.id=d.membre_id
             INNER JOIN familles f ON f.id=m.famille_id
             INNER JOIN clients c ON c.id=f.client_id
             LEFT JOIN ateliers a ON a.id=d.atelier_id
             LEFT JOIN disciplines x ON x.id=COALESCE(NULLIF(d.discipline_id,0),a.discipline_id)
             WHERE d.organisation_id=?
               AND d.deleted_at IS NULL"
             .$filter.
            " ORDER BY
                CASE d.statut
                    WHEN 'file_attente' THEN 1
                    WHEN 'a_etudier' THEN 2
                    WHEN 'creneau_propose' THEN 3
                    ELSE 4
                END,
                d.created_at,
                d.id"
        );
        $q->execute($args);
        return $q->fetchAll() ?: [];
    }

    public static function pending(int $organisationId): array
    {
        $q=Database::connection()->prepare("SELECT d.*,COALESCE(x.code,a.code) atelier_code,COALESCE(x.libelle,a.libelle) atelier_libelle,COALESCE(NULLIF(d.discipline_id,0),a.discipline_id) discipline_id,CONCAT(m.prenom,' ',m.nom) membre_nom FROM demandes_inscription d LEFT JOIN ateliers a ON a.id=d.atelier_id LEFT JOIN disciplines x ON x.id=COALESCE(NULLIF(d.discipline_id,0),a.discipline_id) INNER JOIN membres_famille m ON m.id=d.membre_id WHERE d.organisation_id=? AND d.deleted_at IS NULL AND d.statut IN('file_attente','a_etudier','creneau_propose') ORDER BY d.created_at,d.id");
        $q->execute([$organisationId]);return $q->fetchAll()?:[];
    }

    public static function find(int $id,int $organisationId): ?array
    {
        $q=Database::connection()->prepare("SELECT d.*,COALESCE(x.code,a.code) atelier_code,COALESCE(x.libelle,a.libelle) atelier_libelle,COALESCE(NULLIF(d.discipline_id,0),a.discipline_id) discipline_id,COALESCE(NULLIF(d.annee_scolaire_id,0),a.annee_scolaire_id) annee_scolaire_id,CONCAT(m.prenom,' ',m.nom) membre_nom FROM demandes_inscription d LEFT JOIN ateliers a ON a.id=d.atelier_id LEFT JOIN disciplines x ON x.id=COALESCE(NULLIF(d.discipline_id,0),a.discipline_id) INNER JOIN membres_famille m ON m.id=d.membre_id WHERE d.id=? AND d.organisation_id=? AND d.deleted_at IS NULL LIMIT 1");
        $q->execute([$id,$organisationId]);return $q->fetch()?:null;
    }

    public static function validateSlot(int $id,int $organisationId,int $slotId,int $userId): array
    {
        $pdo=Database::connection();$pdo->beginTransaction();
        try{
            // Verrouille d'abord la demande : deux validations simultanées ne peuvent
            // plus créer deux inscriptions ou deux lignes de facturation.
            $lock=$pdo->prepare("SELECT statut FROM demandes_inscription WHERE id=? AND organisation_id=? AND deleted_at IS NULL LIMIT 1 FOR UPDATE");
            $lock->execute([$id,$organisationId]);
            $lockedStatus=$lock->fetchColumn();
            if($lockedStatus===false||!in_array((string)$lockedStatus,['file_attente','a_etudier','creneau_propose'],true))throw new RuntimeException('Cette demande a déjà été traitée.');
            $request=self::find($id,$organisationId);
            if(!$request)throw new RuntimeException('Demande introuvable.');
            $request['adherent_id']=self::resolveClientId(
                $organisationId,
                (int)$request['membre_id'],
                (int)($request['adherent_id']??0)
            );
            $q=$pdo->prepare("SELECT ps.*,LEAST(ps.capacite_max,COALESCE(NULLIF(s.capacite,0),ps.capacite_max)) capacite_effective,CONCAT(p.prenom,' ',p.nom) professeur_nom 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 INNER JOIN professeur_disciplines pd ON pd.professeur_id=ps.professeur_id AND pd.discipline_id=ps.discipline_id AND pd.actif=1 AND pd.deleted_at IS NULL WHERE ps.id=? AND ps.organisation_id=? AND ps.discipline_id=? AND ps.statut='valide' AND ps.deleted_at IS NULL LIMIT 1 FOR UPDATE");
            $q->execute([$slotId,$organisationId,(int)$request['discipline_id']]);$slot=$q->fetch();
            if(!$slot)throw new RuntimeException('Le créneau n’est plus disponible ou le professeur n’est pas compétent pour cette discipline.');
            $yearId=self::resolveValidatedSlotYear(
                $organisationId,
                (int)($slot['annee_scolaire_id']??0),
                (int)($request['annee_scolaire_id']??0),
                (string)($slot['date_debut']??''),
                (string)($slot['date_fin']??'')
            );
            $request['annee_scolaire_id']=$yearId;
            $cap=$pdo->prepare("SELECT COUNT(*) FROM cda_slot_enrollments WHERE slot_id=? AND statut='active' AND deleted_at IS NULL FOR UPDATE");$cap->execute([$slotId]);
            if((int)$cap->fetchColumn()>=(int)($slot['capacite_effective']??$slot['capacite_max']))throw new RuntimeException('Ce créneau est complet. Utilisez la mise en file d’attente ciblée.');
            $workshop=self::ensureWorkshopForValidatedSlot(
                $organisationId,
                (int)$request['discipline_id'],
                $yearId,
                $slot,
                $userId
            );

            // Applique les règles métier (dont CE1 minimum en musique) au moment
            // décisif, avant toute occupation de place ou création comptable.
            $eligibility=EnrollmentEligibility::check(
                (int)$request['membre_id'],
                (int)$workshop['id'],
                $yearId
            );
            if(!$eligibility['eligible']){
                throw new RuntimeException(implode(' ', $eligibility['messages']));
            }

            // Défense supplémentaire contre un doublon inter-créneaux pour la même
            // discipline et la même année scolaire.
            $duplicate=$pdo->prepare(
                "SELECT COUNT(*)
                 FROM cda_slot_enrollments se
                 INNER JOIN demandes_inscription di ON di.id=se.demande_inscription_id
                 WHERE se.organisation_id=?
                   AND se.membre_id=?
                   AND se.statut='active'
                   AND se.deleted_at IS NULL
                   AND di.annee_scolaire_id=?
                   AND di.discipline_id=?
                   AND di.id<>?
                   AND di.deleted_at IS NULL
                 FOR UPDATE"
            );
            $duplicate->execute([
                $organisationId,
                (int)$request['membre_id'],
                $yearId,
                (int)$request['discipline_id'],
                $id,
            ]);
            if((int)$duplicate->fetchColumn()>0){
                throw new RuntimeException('Cet élève possède déjà une inscription active pour cette discipline cette année.');
            }

            $tariff=self::resolveTariff($organisationId,(int)$workshop['id'],$yearId,(int)$request['adherent_id']);
            if(!$tariff||!(float)$tariff['montant'])throw new RuntimeException('Aucun tarif chiffré ne correspond à cette discipline.');
            $member=MemberProfile::find((int)$request['membre_id']);if(!$member)throw new RuntimeException('Élève introuvable.');
            $pdo->prepare("INSERT INTO inscriptions_cours(organisation_id,atelier_id,participant_source,participant_id,date_inscription,date_debut,date_fin,statut,eligibility_ce1,observations,created_by) VALUES(?,?,?,?,CURRENT_DATE,?,?,'validee',NULL,?,?)")->execute([$organisationId,(int)$workshop['id'],$member['source_table'],(int)$request['membre_id'],$slot['date_debut'],$slot['date_fin'],$request['commentaire_famille'],$userId?:null]);
            $enrollmentId=(int)$pdo->lastInsertId();
            $pdo->prepare("INSERT INTO cda_slot_enrollments(organisation_id,slot_id,demande_inscription_id,inscription_id,membre_id,statut,date_entree,validated_by) VALUES(?,?,?,?,?,'active',?,?)")->execute([$organisationId,$slotId,$id,$enrollmentId,(int)$request['membre_id'],$slot['date_debut'],$userId?:null]);
            $request['atelier_id']=(int)$workshop['id'];$request['atelier_libelle']=$request['atelier_libelle']?:$workshop['libelle'];
            self::createBillingLine($request,$tariff,$enrollmentId,['date_debut_effet'=>$slot['date_debut'],'date_fin_effet'=>$slot['date_fin']],$userId);
            self::createInstrumentLoanBillingLine($request,$enrollmentId,['date_debut_effet'=>$slot['date_debut'],'date_fin_effet'=>$slot['date_fin']],$userId);
            $pdo->prepare("UPDATE demandes_inscription SET atelier_id=?,cda_slot_id=?,statut='validee',heure_debut_proposee=?,heure_fin_proposee=?,accord_professeur_at=NOW(),validation_direction_at=NOW(),validee_par=?,updated_at=NOW() WHERE id=?")->execute([(int)$workshop['id'],$slotId,$slot['heure_debut'],$slot['heure_fin'],$userId?:null,$id]);
            $pdo->commit();
            return ['request_id'=>$id,'inscription_id'=>$enrollmentId,'email'=>(string)($member['email']??''),'membre_nom'=>$request['membre_nom'],'atelier_libelle'=>$request['atelier_libelle'],'jour_semaine'=>$slot['jour_semaine'],'heure_debut'=>$slot['heure_debut'],'heure_fin'=>$slot['heure_fin'],'date_debut'=>$slot['date_debut']];
        }catch(Throwable $e){if($pdo->inTransaction())$pdo->rollBack();throw $e;}
    }


    public static function queueForSlot(int $id,int $organisationId,int $slotId,int $userId): void
    {
        $pdo=Database::connection();$pdo->beginTransaction();
        try{
            $lock=$pdo->prepare("SELECT statut,discipline_id FROM demandes_inscription WHERE id=? AND organisation_id=? AND deleted_at IS NULL LIMIT 1 FOR UPDATE");
            $lock->execute([$id,$organisationId]);$request=$lock->fetch();
            if(!$request||!in_array((string)$request['statut'],['file_attente','a_etudier','creneau_propose'],true))throw new RuntimeException('Cette demande a déjà été traitée.');
            $slotQ=$pdo->prepare("SELECT ps.*,LEAST(ps.capacite_max,COALESCE(NULLIF(s.capacite,0),ps.capacite_max)) capacite_effective,(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 LEFT JOIN salles s ON s.id=ps.salle_id WHERE ps.id=? AND ps.organisation_id=? AND ps.discipline_id=? AND ps.statut='valide' AND ps.deleted_at IS NULL LIMIT 1 FOR UPDATE");
            $slotQ->execute([$slotId,$organisationId,(int)$request['discipline_id']]);$slot=$slotQ->fetch();
            if(!$slot)throw new RuntimeException('Créneau indisponible pour cette discipline.');
            if((int)$slot['inscrits']<(int)$slot['capacite_effective'])throw new RuntimeException('Une place est disponible : validez directement l’inscription.');
            $pdo->prepare("UPDATE demandes_inscription SET cda_slot_id=?,statut='file_attente',heure_debut_proposee=?,heure_fin_proposee=?,updated_at=NOW() WHERE id=? AND organisation_id=?")->execute([$slotId,$slot['heure_debut'],$slot['heure_fin'],$id,$organisationId]);
            $pdo->commit();
        }catch(Throwable $e){if($pdo->inTransaction())$pdo->rollBack();throw $e;}
    }

    public static function promoteNextForSlot(int $organisationId,int $slotId,int $userId): ?array
    {
        $pdo=Database::connection();
        $q=$pdo->prepare("SELECT d.id FROM demandes_inscription d INNER JOIN cda_professor_slots ps ON ps.id=d.cda_slot_id AND ps.discipline_id=d.discipline_id WHERE d.organisation_id=? AND d.cda_slot_id=? AND d.statut='file_attente' AND d.deleted_at IS NULL AND ps.statut='valide' AND ps.deleted_at IS NULL ORDER BY d.created_at,d.id LIMIT 1");
        $q->execute([$organisationId,$slotId]);$id=(int)$q->fetchColumn();
        if(!$id)return null;
        try{return self::validateSlot($id,$organisationId,$slotId,$userId);}catch(Throwable $e){return null;}
    }

    public static function groups(int $atelierId): array
    {
        $q=Database::connection()->prepare("SELECT g.id,g.libelle,g.capacite_max,v.jour_semaine,v.heure_debut,v.heure_fin,v.date_debut_effet,v.date_fin_effet,v.professeur_principal_id,CONCAT(p.prenom,' ',p.nom) professeur_nom,s.libelle salle_libelle,(SELECT COUNT(*) FROM groupe_participants x WHERE x.groupe_id=g.id AND x.deleted_at IS NULL AND x.statut='actif') participants FROM groupes_cours g INNER JOIN groupe_planning_versions v ON v.groupe_id=g.id AND v.date_fin_effet IS NULL LEFT JOIN professeurs p ON p.id=v.professeur_principal_id LEFT JOIN salles s ON s.id=v.salle_id WHERE g.atelier_id=? AND g.statut='actif' AND g.deleted_at IS NULL ORDER BY FIELD(v.jour_semaine,'lundi','mardi','mercredi','jeudi','vendredi','samedi','dimanche'),v.heure_debut");
        $q->execute([$atelierId]);return $q->fetchAll()?:[];
    }

    public static function validate(int $id,int $organisationId,int $groupId,int $userId): array
    {
        $pdo=Database::connection();$pdo->beginTransaction();
        try{
            $request=self::find($id,$organisationId);
            if(!$request||!in_array($request['statut'],['file_attente','a_etudier','creneau_propose'],true))throw new RuntimeException('Cette demande a déjà été traitée.');
            $q=$pdo->prepare("SELECT g.*,v.jour_semaine,v.heure_debut,v.heure_fin,v.date_debut_effet,v.date_fin_effet,v.professeur_principal_id FROM groupes_cours g INNER JOIN groupe_planning_versions v ON v.groupe_id=g.id AND v.date_fin_effet IS NULL WHERE g.id=? AND g.atelier_id=? AND g.statut='actif' AND g.deleted_at IS NULL LIMIT 1 FOR UPDATE");
            $q->execute([$groupId,(int)$request['atelier_id']]);$group=$q->fetch();
            if(!$group)throw new RuntimeException('Le créneau sélectionné est introuvable.');
            if(!$group['professeur_principal_id'])throw new RuntimeException('Aucun professeur n’est affecté à ce créneau.');
            $conflict=$pdo->prepare("SELECT CONCAT(a.libelle,' — ',g.libelle) FROM groupe_planning_versions v INNER JOIN groupes_cours g ON g.id=v.groupe_id AND g.deleted_at IS NULL INNER JOIN ateliers a ON a.id=g.atelier_id WHERE (v.professeur_principal_id=? OR EXISTS(SELECT 1 FROM groupe_professeurs gp WHERE gp.groupe_id=g.id AND gp.professeur_id=? AND gp.actif=1)) AND g.id<>? AND v.jour_semaine=? AND v.date_debut_effet<=COALESCE(?, '9999-12-31') AND (v.date_fin_effet IS NULL OR v.date_fin_effet>=?) AND v.heure_debut<? AND v.heure_fin>? LIMIT 1");
            $conflict->execute([(int)$group['professeur_principal_id'],(int)$group['professeur_principal_id'],$groupId,$group['jour_semaine'],$group['date_fin_effet'],$group['date_debut_effet'],$group['heure_fin'],$group['heure_debut']]);
            if($label=$conflict->fetchColumn())throw new RuntimeException('Conflit dans l’agenda du professeur avec « '.$label.' ».');
            if((int)($group['capacite_max']??0)>0){
                $cap=$pdo->prepare("SELECT COUNT(*) FROM groupe_participants WHERE groupe_id=? AND deleted_at IS NULL AND statut='actif' FOR UPDATE");
                $cap->execute([$groupId]);if((int)$cap->fetchColumn()>=(int)$group['capacite_max'])throw new RuntimeException('Ce créneau est complet.');
            }
            $tariff=self::resolveTariff($organisationId,(int)$request['atelier_id'],(int)$request['annee_scolaire_id'],(int)$request['adherent_id']);
            if(!$tariff||!(float)$tariff['montant'])throw new RuntimeException('Aucun tarif chiffré ne correspond à cet atelier. Configurez le tarif avant de valider.');
            $member=MemberProfile::find((int)$request['membre_id']);if(!$member)throw new RuntimeException('Élève introuvable.');
            $pdo->prepare("INSERT INTO inscriptions_cours(organisation_id,atelier_id,participant_source,participant_id,date_inscription,date_debut,date_fin,statut,eligibility_ce1,observations,created_by) VALUES(?,?,?,?,CURRENT_DATE,?,?,'validee',NULL,?,?)")->execute([$organisationId,(int)$request['atelier_id'],$member['source_table'],(int)$request['membre_id'],$group['date_debut_effet'],$group['date_fin_effet'],$request['commentaire_famille'],$userId?:null]);
            $enrollmentId=(int)$pdo->lastInsertId();
            $pdo->prepare("INSERT IGNORE INTO groupe_professeurs(groupe_id,professeur_id,role_professeur,date_debut,date_fin,actif) VALUES(?,?,'principal',?,?,1)")->execute([$groupId,(int)$group['professeur_principal_id'],$group['date_debut_effet'],$group['date_fin_effet']]);
            $pdo->prepare("INSERT INTO groupe_participants(groupe_id,inscription_id,date_entree,date_sortie,statut,heure_debut_individuelle,heure_fin_individuelle,observations,created_by) VALUES(?,?,?,NULL,'actif',?,?,?,?)")->execute([$groupId,$enrollmentId,$group['date_debut_effet'],$group['heure_debut'],$group['heure_fin'],$request['disponibilites'],$userId?:null]);
            self::createBillingLine($request,$tariff,$enrollmentId,$group,$userId);
            self::createInstrumentLoanBillingLine($request,$enrollmentId,$group,$userId);
            $pdo->prepare("UPDATE demandes_inscription SET statut='validee',groupe_propose_id=?,heure_debut_proposee=?,heure_fin_proposee=?,accord_professeur_at=NOW(),validation_direction_at=NOW(),validee_par=?,updated_at=NOW() WHERE id=?")->execute([$groupId,$group['heure_debut'],$group['heure_fin'],$userId?:null,$id]);
            $pdo->commit();
            return ['request_id'=>$id,'inscription_id'=>$enrollmentId,'email'=>(string)($member['email']??''),'membre_nom'=>$request['membre_nom'],'atelier_libelle'=>$request['atelier_libelle'],'jour_semaine'=>$group['jour_semaine'],'heure_debut'=>$group['heure_debut'],'heure_fin'=>$group['heure_fin'],'date_debut'=>$group['date_debut_effet']];
        }catch(Throwable $e){if($pdo->inTransaction())$pdo->rollBack();throw $e;}
    }

    /**
     * Résout uniquement une année réellement présente dans annees_scolaires.
     * Le créneau fait foi, puis la demande, puis la période et enfin l'année active.
     */
    private static function resolveValidatedSlotYear(
        int $organisationId,
        int $slotYearId,
        int $requestYearId,
        string $startDate,
        string $endDate
    ): int {
        $pdo=Database::connection();
        $byId=$pdo->prepare(
            "SELECT id
             FROM annees_scolaires
             WHERE id=?
               AND organisation_id=?
             LIMIT 1"
        );
        foreach(array_unique([$slotYearId,$requestYearId]) as $candidate){
            if($candidate<=0)continue;
            $byId->execute([$candidate,$organisationId]);
            if($yearId=$byId->fetchColumn())return (int)$yearId;
        }

        $referenceDate=$startDate!==''?$startDate:$endDate;
        if($referenceDate!==''){
            $byDate=$pdo->prepare(
                "SELECT id
                 FROM annees_scolaires
                 WHERE organisation_id=?
                   AND date_debut<=?
                   AND date_fin>=?
                 ORDER BY active DESC,date_debut DESC,id DESC
                 LIMIT 1"
            );
            $byDate->execute([$organisationId,$referenceDate,$referenceDate]);
            if($yearId=$byDate->fetchColumn())return (int)$yearId;
        }

        $active=$pdo->prepare(
            "SELECT id
             FROM annees_scolaires
             WHERE organisation_id=?
               AND active=1
             ORDER BY date_debut DESC,id DESC
             LIMIT 1"
        );
        $active->execute([$organisationId]);
        if($yearId=$active->fetchColumn())return (int)$yearId;

        throw new RuntimeException('Aucune année scolaire valide ne correspond à ce cours. Activez l’année scolaire avant de valider.');
    }

    private static function resolveTariff(int $organisationId,int $atelierId,int $yearId,int $clientId): ?array
    {
        $pdo=Database::connection();$atelierCols=self::columns('ateliers');$catalogCols=self::columns('cda_prestation_catalog');
        $q=$pdo->prepare("SELECT * FROM ateliers WHERE id=? AND organisation_id=? LIMIT 1");$q->execute([$atelierId,$organisationId]);$atelier=$q->fetch()?:[];
        $disciplineId=(int)($atelier['discipline_id']??0);
        if($disciplineId>0){
            $d=$pdo->prepare("SELECT d.*,p.code pole_code,p.libelle pole_libelle FROM disciplines d LEFT JOIN poles p ON p.id=d.pole_id WHERE d.id=? AND d.organisation_id=? LIMIT 1");
            $d->execute([$disciplineId,$organisationId]);$discipline=$d->fetch()?:[];
            if(self::isMusicInstrument($discipline)){
                $zone=self::isBrayDunois($organisationId,$clientId)?'BRD':'NBRD';
                $fixed=$pdo->prepare("SELECT * FROM cda_prestation_catalog WHERE organisation_id=? AND actif=1 AND montant>0 AND code=? LIMIT 1");
                $fixed->execute([$organisationId,'TAR-MUS-INSTRUMENT-'.$zone]);
                if($row=$fixed->fetch())return $row;
                throw new RuntimeException('Le tarif fixe « Instrument seul — '.($zone==='BRD'?'Bray-Dunois':'Hors Bray-Dunois').' » est introuvable ou sans montant.');
            }
        }
        foreach(['prestation_catalog_id','catalogue_prestation_id','tarif_id','prestation_id'] as $link){
            if(isset($atelierCols[$link])&&!empty($atelier[$link])){
                $s=$pdo->prepare("SELECT * FROM cda_prestation_catalog WHERE id=? AND organisation_id=? AND actif=1 LIMIT 1");$s->execute([(int)$atelier[$link],$organisationId]);if($row=$s->fetch())return $row;
            }
        }
        $where=["organisation_id=?","actif=1","montant>0"];$args=[$organisationId];$score=[];
        if(isset($catalogCols['annee_scolaire_id'])){$where[]="(annee_scolaire_id=? OR annee_scolaire_id IS NULL)";$args[]=$yearId;$score[]="(annee_scolaire_id=".$yearId.") DESC";}
        foreach(['discipline_id','pole_id','public_cible_id'] as $col){
            if(isset($catalogCols[$col],$atelierCols[$col])&&!empty($atelier[$col])){$where[]="($col=? OR $col IS NULL)";$args[]=(int)$atelier[$col];$score[]="($col=".(int)$atelier[$col].") DESC";}
        }
        if(isset($catalogCols['categorie']))$where[]="categorie<>'adhesion'";
        $sql="SELECT * FROM cda_prestation_catalog WHERE ".implode(' AND ',$where)." ORDER BY ".($score?implode(',',$score).',':'')."id DESC LIMIT 1";
        $s=$pdo->prepare($sql);$s->execute($args);return $s->fetch()?:null;
    }

    /**
     * Le planning professeur est désormais la source de vérité des cours.
     * L'ancien modèle d'inscription exige encore un atelier_id : lorsqu'aucun
     * atelier actif n'existe, on crée donc automatiquement un support métier
     * pour la discipline et l'année du créneau déjà validé.
     */
    private static function ensureWorkshopForValidatedSlot(
        int $organisationId,
        int $disciplineId,
        int $yearId,
        array $slot,
        int $userId
    ): array {
        $pdo=Database::connection();
        $find=$pdo->prepare(
            "SELECT *
             FROM ateliers
             WHERE organisation_id=?
               AND discipline_id=?
               AND annee_scolaire_id=?
               AND actif=1
               AND deleted_at IS NULL
             ORDER BY id
             LIMIT 1
             FOR UPDATE"
        );
        $find->execute([$organisationId,$disciplineId,$yearId]);
        if($workshop=$find->fetch())return $workshop;

        $discipline=$pdo->prepare(
            "SELECT d.code,d.libelle,d.pole_id
             FROM disciplines d
             WHERE d.id=?
               AND d.organisation_id=?
               AND d.actif=1
               AND d.deleted_at IS NULL
             LIMIT 1"
        );
        $discipline->execute([$disciplineId,$organisationId]);
        $row=$discipline->fetch();
        if(!$row)throw new RuntimeException('La discipline de ce cours est inactive ou archivée.');

        $columns=self::columns('ateliers');
        $baseCode='PLAN-'.$yearId.'-'.$disciplineId;
        $code=$baseCode;
        $suffix=1;
        $codeExists=$pdo->prepare("SELECT 1 FROM ateliers WHERE code=? LIMIT 1");
        while(true){
            $codeExists->execute([$code]);
            if(!$codeExists->fetchColumn())break;
            $code=$baseCode.'-'.(++$suffix);
        }

        $values=[
            'organisation_id'=>$organisationId,
            'annee_scolaire_id'=>$yearId,
            'discipline_id'=>$disciplineId,
            'pole_id'=>(int)($row['pole_id']??0)?:null,
            'salle_id'=>(int)($slot['salle_id']??0)?:null,
            'code'=>$code,
            'libelle'=>(string)$row['libelle'],
            'jour_semaine'=>$slot['jour_semaine']??null,
            'heure_debut'=>$slot['heure_debut']??null,
            'heure_fin'=>$slot['heure_fin']??null,
            'type_atelier'=>($slot['type_cours']??'individuel')==='individuel'?'individuel':'collectif',
            'duree_standard_minutes'=>max(1,(int)round((strtotime((string)$slot['heure_fin'])-strtotime((string)$slot['heure_debut']))/60)),
            'capacite_max'=>max(1,(int)($slot['capacite_max']??1)),
            'actif'=>1,
            'statut'=>'ouvert',
            'created_by'=>$userId?:null,
            'updated_by'=>$userId?:null,
        ];
        $values=array_filter(
            $values,
            static fn($value,$column)=>isset($columns[$column])&&$value!==null,
            ARRAY_FILTER_USE_BOTH
        );
        $names=array_keys($values);
        $pdo->prepare(
            "INSERT INTO ateliers(".implode(',',$names).")
             VALUES(".implode(',',array_fill(0,count($names),'?')).")"
        )->execute(array_values($values));

        $id=(int)$pdo->lastInsertId();
        $created=$pdo->prepare("SELECT * FROM ateliers WHERE id=? AND organisation_id=? LIMIT 1");
        $created->execute([$id,$organisationId]);
        $workshop=$created->fetch();
        if(!$workshop)throw new RuntimeException('Le support d’inscription du cours n’a pas pu être créé.');
        return $workshop;
    }

    private static function isMusicInstrument(array $discipline): bool
    {
        $pole=strtoupper((string)($discipline['pole_code']??''));
        $poleLabel=mb_strtoupper((string)($discipline['pole_libelle']??''),'UTF-8');
        $code=strtoupper((string)($discipline['code']??''));
        $label=mb_strtolower((string)($discipline['libelle']??''),'UTF-8');
        $nonInstrument=['formation musicale','éveil musical','eveil musical','mao','chant','chorale','soutien harmonie'];
        if(!str_contains($pole,'MUS')&&!str_contains($poleLabel,'MUSIQUE')&&!str_starts_with($code,'MUS'))return false;
        foreach($nonInstrument as $excluded)if(str_contains($label,$excluded))return false;
        return true;
    }

    private static function isBrayDunois(int $organisationId,int $clientId): bool
    {
        $q=Database::connection()->prepare("SELECT code_postal,ville FROM clients WHERE id=? AND organisation_id=? LIMIT 1");
        $q->execute([$clientId,$organisationId]);$client=$q->fetch()?:[];
        $postal=preg_replace('/\s+/','',(string)($client['code_postal']??''))??'';
        $city=mb_strtoupper(trim((string)($client['ville']??'')),'UTF-8');
        $city=str_replace(['-',' '],'',$city);
        return $postal==='59123'&&in_array($city,['BRAYDUNES','BRAYDUNE'],true);
    }

    private static function createBillingLine(array $request,array $tariff,int $enrollmentId,array $group,int $userId): void
    {
        $pdo=Database::connection();$cols=self::columns('cda_facturation_a_preparer');
        $annual=self::ensureAnnualFile((int)$request['organisation_id'],(int)$request['adherent_id'],(int)$request['annee_scolaire_id'],$userId);
        $amount=(float)$tariff['montant'];$sourceKey='inscription_cours:'.$enrollmentId;
        $exists=$pdo->prepare("SELECT id FROM cda_facturation_a_preparer WHERE organisation_id=? AND source_key=? AND invoice_id IS NULL LIMIT 1");
        $exists->execute([(int)$request['organisation_id'],$sourceKey]);if($exists->fetchColumn())return;
        $values=[
            'organisation_id'=>(int)$request['organisation_id'],'client_id'=>(int)$request['adherent_id'],
            'dossier_annuel_id'=>(int)($annual['id']??0),'annee_scolaire_id'=>(int)$request['annee_scolaire_id'],
            'type_element'=>'inscription_cours','libelle'=>'Inscription '.$request['atelier_libelle'],
            'montant_ttc'=>$amount,'statut'=>'a_facturer','source_module'=>'inscriptions','source_key'=>$sourceKey,
            'pole_code'=>$tariff['pole_code']??null,'service_start'=>$group['date_debut_effet'],'service_end'=>$group['date_fin_effet'],
            'gross_amount'=>$amount,'discount_amount'=>0,'net_amount'=>$amount,'created_by'=>$userId?:null,'updated_by'=>$userId?:null
        ];
        $values=array_filter($values,static fn($v,$k)=>isset($cols[$k])&&($k!=='dossier_annuel_id'||(int)$v>0),ARRAY_FILTER_USE_BOTH);
        $names=array_keys($values);$sql="INSERT INTO cda_facturation_a_preparer(".implode(',',$names).") VALUES(".implode(',',array_fill(0,count($names),'?')).")";
        $pdo->prepare($sql)->execute(array_values($values));
    }

    private static function createInstrumentLoanBillingLine(array $request,int $enrollmentId,array $period,int $userId): void
    {
        $type=(string)($request['instrument_loan_type']??'none');
        if(!in_array($type,['individual','collective'],true))return;
        $pdo=Database::connection();$zone=self::isBrayDunois((int)$request['organisation_id'],(int)$request['adherent_id'])?'BRD':'NBRD';
        // Les codes du catalogue utilisent COL (et non COLL) pour le prêt collectif.
        $kind=$type==='individual'?'IND':'COL';
        $codes=[
            'TAR-MUS-PRET-'.$kind.'-'.$zone,
            'TAR-MUS-PRET-INST-'.$kind.'-'.$zone,
            'TAR-MUS-INSTRUMENT-PRET-'.$kind.'-'.$zone,
        ];
        $marks=implode(',',array_fill(0,count($codes),'?'));
        $q=$pdo->prepare("SELECT * FROM cda_prestation_catalog WHERE organisation_id=? AND actif=1 AND montant>0 AND code IN($marks) ORDER BY FIELD(code,$marks) LIMIT 1");
        $args=array_merge([(int)$request['organisation_id']],$codes,$codes);$q->execute($args);$tariff=$q->fetch();
        if(!$tariff){
            $category=$type==='individual'?'pret_individuel':'pret_collectif';
            $q=$pdo->prepare(
                "SELECT * FROM cda_prestation_catalog
                 WHERE organisation_id=? AND actif=1 AND montant>0
                   AND (categorie=? OR (UPPER(code) LIKE '%PRET%' AND UPPER(code) LIKE ?))
                   AND UPPER(code) LIKE ?
                 ORDER BY (categorie=?) DESC,id DESC LIMIT 1"
            );
            $q->execute([
                (int)$request['organisation_id'],
                $category,
                '%'.$kind.'%',
                '%'.$zone.'%',
                $category
            ]);$tariff=$q->fetch();
        }
        if(!$tariff)throw new RuntimeException('Le tarif du prêt d’instrument '.($type==='individual'?'individuel':'collectif').' ('.($zone==='BRD'?'Bray-Dunois':'hors Bray-Dunois').') est introuvable.');
        $copy=$request;
        $copy['atelier_libelle']='Prêt d’instrument '.($type==='individual'?'individuel':'collectif');
        self::insertBillingLine($copy,$tariff,'pret_instrument:'.$enrollmentId,'pret_instrument',$copy['atelier_libelle'],$period,$userId);
    }

    private static function insertBillingLine(array $request,array $tariff,string $sourceKey,string $type,string $label,array $period,int $userId): void
    {
        $pdo=Database::connection();$cols=self::columns('cda_facturation_a_preparer');
        $annual=self::ensureAnnualFile((int)$request['organisation_id'],(int)$request['adherent_id'],(int)$request['annee_scolaire_id'],$userId);
        $exists=$pdo->prepare("SELECT id FROM cda_facturation_a_preparer WHERE organisation_id=? AND source_key=? AND invoice_id IS NULL LIMIT 1");
        $exists->execute([(int)$request['organisation_id'],$sourceKey]);if($exists->fetchColumn())return;
        $amount=(float)$tariff['montant'];
        $values=['organisation_id'=>(int)$request['organisation_id'],'client_id'=>(int)$request['adherent_id'],'dossier_annuel_id'=>(int)$annual['id'],'annee_scolaire_id'=>(int)$request['annee_scolaire_id'],'type_element'=>$type,'libelle'=>$label,'montant_ttc'=>$amount,'statut'=>'a_facturer','source_module'=>'inscriptions','source_key'=>$sourceKey,'pole_code'=>$tariff['pole_code']??'MUS','service_start'=>$period['date_debut_effet']??null,'service_end'=>$period['date_fin_effet']??null,'gross_amount'=>$amount,'discount_amount'=>0,'net_amount'=>$amount,'created_by'=>$userId?:null,'updated_by'=>$userId?:null];
        $values=array_filter($values,static fn($v,$k)=>isset($cols[$k]),ARRAY_FILTER_USE_BOTH);
        $names=array_keys($values);$pdo->prepare("INSERT INTO cda_facturation_a_preparer(".implode(',',$names).") VALUES(".implode(',',array_fill(0,count($names),'?')).")")->execute(array_values($values));
    }

    private static function ensureAnnualFile(int $organisationId,int $clientId,int $yearId,int $userId): array
    {
        $pdo=Database::connection();
        $valid=$pdo->prepare("SELECT id FROM clients WHERE id=? AND organisation_id=? AND deleted_at IS NULL LIMIT 1");
        $valid->execute([$clientId,$organisationId]);
        if(!(int)$valid->fetchColumn())throw new RuntimeException('Le compte client de la famille est introuvable. Corrigez le rattachement de l’adhérent avant de valider l’affectation.');
        $q=$pdo->prepare("SELECT * FROM cda_dossiers_adherents_annuels WHERE organisation_id=? AND client_id=? AND annee_scolaire_id=? AND deleted_at IS NULL LIMIT 1 FOR UPDATE");
        $q->execute([$organisationId,$clientId,$yearId]);
        if($row=$q->fetch())return $row;
        $pdo->prepare("INSERT INTO cda_dossiers_adherents_annuels(organisation_id,client_id,annee_scolaire_id,statut,activated_at,created_by,updated_by) VALUES(?,?,?,'actif',NOW(),?,?)")->execute([$organisationId,$clientId,$yearId,$userId?:null,$userId?:null]);
        return ['id'=>(int)$pdo->lastInsertId(),'annee_scolaire_id'=>$yearId,'statut'=>'actif'];
    }

    private static function resolveClientId(int $organisationId,int $memberId,int $legacyClientId=0): int
    {
        $pdo=Database::connection();
        $q=$pdo->prepare(
            "SELECT c.id
             FROM membres_famille m
             INNER JOIN familles f
                     ON f.id=m.famille_id
                    AND f.deleted_at IS NULL
             INNER JOIN clients c
                     ON c.id=f.client_id
                    AND c.organisation_id=?
                    AND c.deleted_at IS NULL
             WHERE m.id=?
               AND m.deleted_at IS NULL
             LIMIT 1"
        );
        $q->execute([$organisationId,$memberId]);
        $clientId=(int)$q->fetchColumn();
        if($clientId>0)return $clientId;
        if($legacyClientId>0){
            $fallback=$pdo->prepare("SELECT id FROM clients WHERE id=? AND organisation_id=? AND deleted_at IS NULL LIMIT 1");
            $fallback->execute([$legacyClientId,$organisationId]);
            if($resolved=(int)$fallback->fetchColumn())return $resolved;
        }
        throw new RuntimeException('Impossible de retrouver le compte client lié à cet élève. Vérifiez son rattachement à la famille.');
    }

    public static function logMail(int $requestId,string $email,string $status,?string $error,int $userId): void
    {
        $q=Database::connection()->prepare("INSERT INTO cda_enrollment_mail_log(demande_inscription_id,destinataire,statut,erreur,created_by) VALUES(?,?,?,?,?)");
        $q->execute([$requestId,$email,$status,$error,$userId?:null]);
    }

    private static function columns(string $table): array
    {
        if(isset(self::$columns[$table]))return self::$columns[$table];
        $rows=Database::connection()->query("SHOW COLUMNS FROM `$table`")->fetchAll();$out=[];
        foreach($rows as $r)$out[(string)$r['Field']]=true;
        return self::$columns[$table]=$out;
    }
}
