<br />
<b>Warning</b>:  Undefined variable $auth in <b>/home/pevo0181/public_html/pia-soft.com/cleania/routes/index.php</b> on line <b>542</b><br />
<br />
<b>Warning</b>:  Trying to access array offset on value of type null in <b>/home/pevo0181/public_html/pia-soft.com/cleania/routes/index.php</b> on line <b>542</b><br />
<?php

use Carbon\Carbon;
use App\Models\Note;
use App\Models\User;
use App\Models\Year;
use App\Models\Admin;
use App\Models\Brand;
use App\Models\Pension;
use App\Models\CashDesk;
use App\Models\Customer;
use App\Models\CarFolder;
use App\Models\ClassSchool;
use App\Models\StudentRank;
use App\Models\Subscription;
use App\Models\CustomerAffair;
use Illuminate\Support\Facades\DB;
use App\Models\SchoolConfiguration;
use Illuminate\Support\Facades\Auth;


if (!function_exists('getSchoolConfiguration')) {
    function getSchoolConfiguration()
    {
        return SchoolConfiguration::getActiveSchool();
    }
}

if (!function_exists('getSchoolHeaderData')) {
    function getSchoolHeaderData()
    {
        $school = getSchoolConfiguration();
        
        // Valeurs par défaut
        $defaultData = [
            'school_name_fr' => 'APEX BILINGUAL ACADEMY',
            'school_name_en' => 'APEX BILINGUAL ACADEMY',
            'school_name' => 'APEX BILINGUAL ACADEMY',
            'abbreviation'  => 'APEX',
            'motto_fr' => 'Engagement - Travail - Succès',
            'motto_en' => 'Commitment - HardWork - Success',
            'ministry_fr' => 'MINISTÈRE DES ENSEIGNEMENTS DE BASE',
            'ministry_en' => 'MINISTRY OF BASIC EDUCATION',
            'phone' => '(+237) 676 987 081 / 686 412 171',
            'email' => 'apexbilingualacademy@gmail.com',
            'opening_order' => '982/J/17/MINEDUB/SG/DSEPB/SDAAP DU JUIN 2024',
            'logo' => getDefaultLogo(),
            'address' => 'Nyom II, Yaoundé - Cameroun'
        ];
        
        if (!$school) {
            return $defaultData;
        }
        
        // Fusionner avec les données de l'école
        return array_merge($defaultData, [
            'school_name_fr' => $school->school_name ?? $defaultData['school_name_fr'],
            'school_name_en' => $school->school_name_en ?? $defaultData['school_name_en'],
            'school_name' => $school->school_name ?? $defaultData['school_name'],
            'abbreviation'  => $school->abbreviation ?? $defaultData['abbreviation'],
            'motto_fr' => $school->motto_fr ?? $defaultData['motto_fr'],
            'motto_en' => $school->motto_en ?? $defaultData['motto_en'],
            'ministry_fr' => $school->ministry_fr ?? $defaultData['ministry_fr'],
            'ministry_en' => $school->ministry_en ?? $defaultData['ministry_en'],
            'address' => $school->address ?? $defaultData['address'],
            'phone' => $school->phone ?? $defaultData['phone'],
            'email' => $school->email ?? $defaultData['email'],
            'website' => $school->website ?? '',
            'opening_order' => $school->getFormattedOpeningDate() ?? $defaultData['opening_order'],
            'logo' => $school->logo_path ? asset($school->logo_path) : $defaultData['logo'],
            'card_recto' => $school->card_recto ?? 'card/recto.png',
            'card_verso' => $school->card_verso ?? 'card/verso.png',
            'badge_parent' => $school->badge_parent ?? 'card/badge_parent.png',
            'card_color' => $school->card_color ?? '#000',
            'card_sign' => $school->card_sign ?? '',
        ]);
    }
}


if (! function_exists('convert_amount')) {
    function convert_amount($amount) {
        $currency = "F CFA";
        if (is_numeric($amount)) {
            return number_format($amount, 2);
        }
        return 0;
    }
}

if (! function_exists('convert_number')) {
    function convert_number($amount) {
        if (is_numeric($amount)) {
            //return number_format($amount, 2);
            return number_format($amount, 2, ',',' ');
        }
        return 0;
    }
}
if (!function_exists('convert_amount_v2')) {
    function convert_amount_v2($amount) {
        if (is_numeric($amount)) {
            return number_format($amount, 0, '.', ' ');
        }
        return '0.00';
    }
}
if (!function_exists('getDefaultLogo')) {
    function getDefaultLogo() {
        return asset('apex.png'); 
    }
}
if (! function_exists('getLogo')) {
    function getLogo() {
        return asset(getSchoolHeaderData()['logo']);
    }
}
if (! function_exists('getCardRecto')) {
    function getCardRecto() {
        return asset(getSchoolHeaderData()['card_recto']);
    }
}
if (! function_exists('getCardVerso')) {
    function getCardVerso() {
        return asset(getSchoolHeaderData()['card_verso']);
    }
}
if (! function_exists('getBadgeParent')) {
    function getBadgeParent() {
        return asset(getSchoolHeaderData()['badge_parent']);
    }
}

if (! function_exists('getNameApp')) {
    function getNameApp() {
        return getSchoolHeaderData()['abbreviation'];
    }
}
if (! function_exists('getCardColor')) {
    function getCardColor() {
        return getSchoolHeaderData()['card_color'];
    }
}
if (! function_exists('getCardSign')) {
    function getCardSign() {
        return asset(getSchoolHeaderData()['card_sign']);
    }
}


if (! function_exists('getNoteByStudentByClass')) {
    function getNoteByStudentByClass($student,$sous_mat,$sequence,$session,$class_id) {
        $note = Note::where('usr_id', $student)
        ->where('s_mat_id', $sous_mat)
        ->where('trim_id', $sequence)
        ->where('note_annee', $session)
        ->where('class_id', $class_id)
        ->first();
        return $note;
    }
}
if (! function_exists('getAdminByName')) {
    function getAdminByName($id) {
        $admin = Admin::where('ad_id', $id)->firstOrFail();
        return $admin->ad_name;
    }
}
if (! function_exists('getClassNameSchool')) {
    function getClassNameSchool($user_id,$session = null) {
        $subscribe = Subscription::where('usr_id',$user_id)->where('inscrip_annee',$session)->latest("inscrip_id")->first();
        if($subscribe) return $subscribe->classSchool->class_name ?? null;
        else return null;
    }
}
if (! function_exists('getPensionClassSchool')) {
    function getPensionClassSchool($id) {
        $subscribe = Subscription::where('inscrip_id',operator: $id)->latest(column: "inscrip_id")->first();
        if($subscribe) return $subscribe->classSchool->class_pension ?? 0;
        else return 0;
    }
}
if (! function_exists('getClassNameById')) {
    function getClassNameById($class_id) {
        $class = ClassSchool::where('class_id',$class_id)->first();
        if($class) return $class->class_name ?? null;
        else return null;
    }
}
if (! function_exists(function: 'getLastYear')) {
    function getLastYear() {
        $year = Year::latest("annee_id")->first();
        if($year) return $year->annee_date ?? null;
        else return null;
    }
}
if (! function_exists('getDataPension')) {
    function getDataPension($inscription_id) {
        $pension = Pension::where('inscrip_id',$inscription_id)->latest(column: "pen_id")->get();
        if($pension) return $pension;
        else return null;
    }
}
if (! function_exists('getSumPension')) {
    function getSumPension($inscription_id, $date_limite = null) {
        $query = Pension::where('inscrip_id', $inscription_id);

        // Si une date est passée, on limite les paiements jusqu'à cette date incluse
        if (!empty($date_limite)) {
            $query->whereDate('pen_date', '<=', $date_limite);
        }

        // Ici on additionne bien le montant payé (ex: pen_amount)
        $pension = $query->sum("pen_montant");

        return $pension ?: 0;
    }
}

if (! function_exists('calculateStudents')) {
    function calculateStudents($type)
    {
        $currentDate = date('Y-m-d');
        $data = User::get();
        $count = 0;
        $weekStartDate = Carbon::now()->subDays(7)->format('Y-m-d');

        foreach ($data as $value) {
            $registrationDate = strtotime($value->date_reg);
            $dayRegistrationDate = date('Y-m-d', $registrationDate);
            $monthRegistrationDate = date('Y-m', $registrationDate);
            $yearRegistrationDate = date('Y', $registrationDate);
            if($value->usr_status == 1){
                switch ($type) {
                    case "toDay":
                        if ($currentDate == $dayRegistrationDate) {
                            $count++;
                        }
                        break;
                    case "month":
                        if (date('Y-m', strtotime($currentDate)) == $monthRegistrationDate) {
                            $count++;
                        }
                        break;
                    case "year":
                        if (date('Y', strtotime($currentDate)) == $yearRegistrationDate) {
                            $count++;
                        }
                        break;
                    case 'last_7_day':
                        if (Carbon::parse($registrationDate)->greaterThanOrEqualTo($weekStartDate)) {
                            $count++;
                        }
                        break;
                    case "all":
                        $count++;
                        break;
                    case "lastYear":
                        $lastYear = date('Y', strtotime('-1 year'));
                        if (date('Y', strtotime($currentDate)) == $lastYear) {
                            $count++;
                        }
                        break;
                    default:
                        $count = 0;
                }
            }
        }

        return $count;
    }

}


if (!function_exists('getPensionByType')) {
    function getPensionByType($type, $session = null) {
        $currentDate = Carbon::today();
        
        // Construire la requête de base
        $pensionsQuery = Pension::query();
        
        // Filtrer par session si spécifiée
        if ($session) {
            $pensionsQuery->whereHas('subscription', function($query) use ($session) {
                $query->where('inscrip_annee', $session);
            });
        }

        // Appliquer les filtres de date directement dans la requête SQL
        switch ($type) {
            case 'toDay':
                $pensionsQuery->whereDate('pen_date', $currentDate);
                break;

            case 'last_7_day':
                $pensionsQuery->where('pen_date', '>=', $currentDate->copy()->subDays(7));
                break;

            case 'month':
                $pensionsQuery->whereYear('pen_date', $currentDate->year)
                             ->whereMonth('pen_date', $currentDate->month);
                break;

            case 'year':
                $pensionsQuery->whereYear('pen_date', $currentDate->year);
                break;

            case 'lastYear':
                $pensionsQuery->whereYear('pen_date', $currentDate->copy()->subYear()->year);
                break;

            case 'session':
                // Pas de filtre de date supplémentaire, seulement la session
                break;

            default:
                return convert_amount_v2(0);
        }

        $amount = $pensionsQuery->sum('pen_montant');

        return convert_amount_v2($amount);
    }
}

if (!function_exists('fetchResponseByValue')) {
    function fetchResponseByValue($value) {
        $message = "";

        if ($value === "0") {
            $message = "Prémière Tranche en cours";
        } elseif ($value === "1") {
            $message = "Prémière Tranche Terminée";
        } elseif ($value === '1-1') {
            $message = "Deuxième Tranche en cours";
        } elseif ($value === '1-2') {
            $message = "Deuxième Tranche Terminée";
        } elseif ($value === '1-2-2') {
            $message = "Troisième Tranche en cours";
        } elseif ($value === '1-2-3') {
            $message = "Troisième Tranche Terminée";
        } else {
            $message = "Tranche Inconnue";
        }

        return $message;
    }
}


if (!function_exists('fetchResponseByValueV2')) {
    function fetchResponseByValueV2($value) {
        $message = "";

        if ($value === "1") {
            $message = "Première Tranche";
        } elseif ($value === "1-2") {
            $message = "Deuxième Tranche";
        } elseif ($value === "1-2-3") {
            $message = "Troisième Tranche";
        } elseif ($value === "0") {
            $message = "Inscription";
        } else {
            $message = "Tranche Inconnue";
        }

        return $message;
    }
}
if (!function_exists('ordinal_fr')) {

    function ordinal_fr($number)
    {
        if (!is_numeric($number)) {
            return $number;
        }

        if ($number == 1) {
            return $number . '<sup>ère</sup>';
        }

        return $number . '<sup>ème</sup>';
    }
}
if (!function_exists('ordinal_fr_term')) {

    function ordinal_fr_term($number)
    {
        if (!is_numeric($number)) {
            return $number;
        }

        if ($number == 1) {
            return $number . '<sup>er</sup>';
        }

        return $number . '<sup>ème</sup>';
    }
}
if (!function_exists('ordinal_en')) {
    function ordinal_en($number)
    {
        if (!is_numeric($number)) {
            return $number;
        }

        $number = intval($number);
        $suffix = 'th';

        // Exceptions pour 11, 12, 13
        if (!in_array(($number % 100), [11, 12, 13])) {
            switch ($number % 10) {
                case 1: $suffix = 'st'; break;
                case 2: $suffix = 'nd'; break;
                case 3: $suffix = 'rd'; break;
            }
        }

        return $number . '<sup>' . $suffix . '</sup>';
    }
}
if (!function_exists('getAppreciationGenerale')) {
    function getAppreciationGenerale($moyenneGenerale, $classCycle = 'FRANCOPHONE')
    {
        if (!is_numeric($moyenneGenerale)) {
            return '-';
        }

        // 🇫🇷 Français
        $appreciationsFr = [
            'excellent'   => 'Excellent',
            'tres_bien'   => 'Très bien',
            'bien'        => 'Bien',
            'assez_bien'  => 'Assez bien',
            'passable'      => 'Passable',
            'insuffisant' => 'Insuffisant',
        ];

        // 🇬🇧 Anglais
        $appreciationsEn = [
            'excellent'   => 'Excellent',
            'tres_bien'   => 'Very Good',
            'bien'        => 'Good',
            'assez_bien'  => 'Fairly Good',
            'passable'      => 'Average',
            'insuffisant' => 'Insufficient',
        ];

        // Sélection du dictionnaire selon la session
        $dict = strtoupper($classCycle) == 'ANGLOPHONE' ? $appreciationsEn : $appreciationsFr;

        // Logique d'appréciation
        if ($moyenneGenerale >= 18) {
            return $dict['excellent'];
        } elseif ($moyenneGenerale >= 16) {
            return $dict['tres_bien'];
        } elseif ($moyenneGenerale >= 14) {
            return $dict['bien'];
        } elseif ($moyenneGenerale >= 12) {
            return $dict['assez_bien'];
        } elseif ($moyenneGenerale >= 10) {
            return $dict['passable'];
        } else {
            return $dict['insuffisant'];
        }
    }
}
if (!function_exists('getRatingGenerale')) {
    function getRatingGenerale($moyenneGenerale, $classCycle = 'FRANCOPHONE')
    {
        if (!is_numeric($moyenneGenerale)) {
            return '-';
        }

        // 🇫🇷 Français
        $appreciationsFr = [
            '1'     => 'A+',
            '2'     => 'A',
            '3'     => 'ECA',
            '4'     => 'CNA',
        ];

        // 🇬🇧 Anglais
        $appreciationsEn = [
            '1'     => 'EXP',
            '2'     => 'CA',
            '3'     => 'CPA',
            '4'     => 'NA',
        ];

        // Sélection du dictionnaire selon la session
        $dict = strtoupper($classCycle) == 'ANGLOPHONE' ? $appreciationsEn : $appreciationsFr;

        // Logique d'appréciation
        if ($moyenneGenerale >= 16) {
            return $dict['1'];
        } elseif ($moyenneGenerale >= 14) {
            return $dict['2'];
        } elseif ($moyenneGenerale >= 10) {
            return $dict['3'];
        }else {
            return $dict['4'];
        }
    }
}

if (!function_exists('getStudentsWithRank')) {
    function getStudentsWithRank($student, $classId, $session, $sequence, $langType = null)
    {
        $query = StudentRank::where('class_id', $classId)
                ->where('session', $session)
                ->where('sequence', $sequence)
                ->where('usr_id', $student);

        // Si lang_type est spécifié, filtrer par langue
        if ($langType) {
            $query->where('lang_type_rank', $langType);
        } else {
            // Pour les systèmes unilingues, prendre le rang sans langue
            $query->whereNull('lang_type_rank');
        }

        return $query->first();
    }
}

if (!function_exists('getStudentsWithRankAll_OLd')) {
    function getStudentsWithRankAll_OLd($classId, $session, $sequence, $langType = null)
    {
        // Récupérer les étudiants avec leurs notes pour la session et séquence spécifiques
        $students = User::with(['subscription' => function($q) use ($classId, $session) {
                $q->where('class_id', $classId)
                  ->where('inscrip_annee', $session);
            }, 'notes' => function($q) use ($classId, $session, $sequence) {
                $q->where('class_id', $classId)
                  ->where('note_annee', $session)
                  ->where('trim_id', $sequence);
            }, 'notes.sousMatiere', 'notes.competence']) // Ajouter la relation competence
            ->whereHas('subscription', function($q) use ($classId, $session) {
                $q->where('class_id', $classId)
                  ->where('inscrip_annee', $session);
            })
            ->get()
            ->filter(function($student) {
                return $student->subscription !== null;
            });

        $studentsData = [];

        foreach ($students as $student) {
            $totalNote = 0;
            $totalBareme = 0;
            $subjectsWithNotes = 0;

            foreach ($student->notes as $note) {
                if ($note->note_s_mat_usr !== null && $note->sousMatiere && $note->competence) {
                    
                    // FILTRER PAR TYPE DE LANGUE (basé sur competence.lang_type)
                    $shouldInclude = false;
                    
                    if ($langType === 'FR') {
                        // Inclure seulement les compétences françaises
                        $shouldInclude = $note->competence->lang_type !== 'EN';
                    } elseif ($langType === 'EN') {
                        // Inclure seulement les compétences anglaises
                        $shouldInclude = $note->competence->lang_type === 'EN';
                    } else {
                        // Pas de filtre - inclure toutes les compétences
                        $shouldInclude = true;
                    }

                    if ($shouldInclude) {
                        $totalNote += $note->note_s_mat_usr;
                        $totalBareme += $note->sousMatiere->s_mat_bareme_max ?? 20;
                        $subjectsWithNotes++;
                    }
                }
            }

            // Éviter la division par zéro
            $moyenneGenerale = $totalBareme > 0 ? ($totalNote / $totalBareme) * 20 : 0;

            $studentsData[] = [
                'student' => $student,
                'totalPoints' => $totalNote,
                'totalBareme' => $totalBareme,
                'moyenneGenerale' => $moyenneGenerale,
                'subjectsWithNotes' => $subjectsWithNotes,
                'formattedTotal' => $totalNote . '/' . $totalBareme,
                'formattedAverage' => number_format($moyenneGenerale, 2) . '/20'
            ];
        }

        // Trier par moyenne générale décroissante
        usort($studentsData, function($a, $b) {
            return $b['moyenneGenerale'] <=> $a['moyenneGenerale'];
        });

        // Calcul des rangs avec gestion des ex-aequo
        $rank = 1;
        $previousAverage = null;
        $sameRankCount = 0;

        foreach ($studentsData as $index => &$studentData) {
            if ($previousAverage !== null && abs($studentData['moyenneGenerale'] - $previousAverage) < 0.01) {
                // Même moyenne = même rang
                $sameRankCount++;
            } else {
                // Nouvelle moyenne = nouveau rang
                $rank += $sameRankCount;
                $sameRankCount = 0;
            }
            
            $studentData['rank'] = $rank;
            $previousAverage = $studentData['moyenneGenerale'];
            
            // Préparer le rang suivant
            if ($sameRankCount == 0) {
                $rank++;
            }
        }

        return $studentsData;
    }
}

if (!function_exists('getStudentsWithRankAll')) {
    function getStudentsWithRankAll($classId, $session, $sequence, $langType = null)
    {
        // Récupérer directement les inscriptions pour cette session
        $subscriptions = Subscription::with([
            'student.notes' => function($q) use ($classId, $session, $sequence) {
                $q->where('class_id', $classId)
                  ->where('note_annee', $session)
                  ->where('trim_id', $sequence);
            },
            'student.notes.sousMatiere',
            'student.notes.competence'
        ])
        ->where('class_id', $classId)
        ->where('inscrip_annee', $session)
        ->get();

        $studentsData = [];

        foreach ($subscriptions as $subscription) {
            $student = $subscription->student;
            if (!$student) continue;

            $totalNote = 0;
            $totalBareme = 0;
            $subjectsWithNotes = 0;

            foreach ($student->notes as $note) {
                if ($note->note_s_mat_usr !== null && $note->sousMatiere && $note->competence) {
                    
                    // FILTRER PAR TYPE DE LANGUE
                    $shouldInclude = false;
                    
                    if ($langType === 'FR') {
                        $shouldInclude = $note->competence->lang_type !== 'EN';
                    } elseif ($langType === 'EN') {
                        $shouldInclude = $note->competence->lang_type === 'EN';
                    } else {
                        $shouldInclude = true;
                    }

                    if ($shouldInclude) {
                        $totalNote += $note->note_s_mat_usr;
                        $totalBareme += $note->sousMatiere->s_mat_bareme_max ?? 20;
                        $subjectsWithNotes++;
                    }
                }
            }

            $moyenneGenerale = $totalBareme > 0 ? ($totalNote / $totalBareme) * 20 : 0;

            $studentsData[] = [
                'student' => $student,
                'totalPoints' => $totalNote,
                'totalBareme' => $totalBareme,
                'moyenneGenerale' => $moyenneGenerale,
                'subjectsWithNotes' => $subjectsWithNotes,
                'formattedTotal' => $totalNote . '/' . $totalBareme,
                'formattedAverage' => number_format($moyenneGenerale, 2) . '/20'
            ];
        }

        // Trier par moyenne générale décroissante
        usort($studentsData, function($a, $b) {
            return $b['moyenneGenerale'] <=> $a['moyenneGenerale'];
        });

        // Calcul des rangs avec gestion des ex-aequo
        $rank = 1;
        $previousAverage = null;
        $sameRankCount = 0;

        foreach ($studentsData as $index => &$studentData) {
            if ($previousAverage !== null && abs($studentData['moyenneGenerale'] - $previousAverage) < 0.01) {
                $sameRankCount++;
            } else {
                $rank += $sameRankCount;
                $sameRankCount = 0;
            }
            
            $studentData['rank'] = $rank;
            $previousAverage = $studentData['moyenneGenerale'];
            
            if ($sameRankCount == 0) {
                $rank++;
            }
        }

        return $studentsData;
    }
}

if (!function_exists('getStudentsWithTermRankAll')) {
    function getStudentsWithTermRankAll($classId, $session, $termValue, $langType = null)
    {
        // Récupérer les séquences du terme
        $sequences = getSequencesByTerm($termValue);
        $divisor = getTermDivisor($termValue);
        
        // Récupérer directement les inscriptions pour cette session
        $subscriptions = Subscription::with([
            'student.notes' => function($q) use ($classId, $session, $sequences) {
                $q->where('class_id', $classId)
                  ->where('note_annee', $session)
                  ->whereIn('trim_id', $sequences);
            },
            'student.notes.sousMatiere',
            'student.notes.competence'
        ])
        ->where('class_id', $classId)
        ->where('inscrip_annee', $session)
        ->get();

        $studentsData = [];

        foreach ($subscriptions as $subscription) {
            $student = $subscription->student;
            if (!$student) continue;

            $totalNote = 0;
            $totalBareme = 0;
            $subjectsWithNotes = 0;

            // Agréger les notes de toutes les séquences du terme
            foreach ($student->notes as $note) {
                if ($note->note_s_mat_usr !== null && $note->sousMatiere && $note->competence) {
                    
                    // FILTRER PAR TYPE DE LANGUE
                    $shouldInclude = false;
                    
                    if ($langType === 'FR') {
                        $shouldInclude = $note->competence->lang_type !== 'EN';
                    } elseif ($langType === 'EN') {
                        $shouldInclude = $note->competence->lang_type === 'EN';
                    } else {
                        $shouldInclude = true;
                    }

                    if ($shouldInclude) {
                        $totalNote += $note->note_s_mat_usr;
                        $totalBareme += $note->sousMatiere->s_mat_bareme_max ?? 20;
                        $subjectsWithNotes++;
                    }
                }
            }

            // Calculer la moyenne en divisant par le bon diviseur
            $moyenneGenerale = $totalBareme > 0 ? ($totalNote / $totalBareme) * 20 : 0;
            $moyenneGenerale = $moyenneGenerale / $divisor;

            $studentsData[] = [
                'student' => $student,
                'totalPoints' => $totalNote / $divisor,
                'totalBareme' => $totalBareme / $divisor,
                'moyenneGenerale' => $moyenneGenerale,
                'subjectsWithNotes' => $subjectsWithNotes,
                'formattedTotal' => formatNote($totalNote / $divisor) . '/' . formatNote($totalBareme / $divisor),
                'formattedAverage' => formatNote($moyenneGenerale, 2) . '/20'
            ];
        }

        // Trier par moyenne générale décroissante
        usort($studentsData, function($a, $b) {
            return $b['moyenneGenerale'] <=> $a['moyenneGenerale'];
        });

        // Calcul des rangs avec gestion des ex-aequo
        $rank = 1;
        $previousAverage = null;
        $sameRankCount = 0;

        foreach ($studentsData as $index => &$studentData) {
            if ($previousAverage !== null && abs($studentData['moyenneGenerale'] - $previousAverage) < 0.01) {
                $sameRankCount++;
            } else {
                $rank += $sameRankCount;
                $sameRankCount = 0;
            }
            
            $studentData['rank'] = $rank;
            $previousAverage = $studentData['moyenneGenerale'];
            
            if ($sameRankCount == 0) {
                $rank++;
            }
        }

        return $studentsData;
    }
}

if (!function_exists('getTermDivisor')) {
    function getTermDivisor($termValue)
    {
        return match($termValue) {
            'first-term' => 2,  // Séquences 1+2
            'second-term' => 2, // Séquences 3+4  
            'third-term' => 2,  // Séquences 5+6
            'annual' => 6,      // Toutes les séquences 1+2+3+4+5+6
            default => 1
        };
    }
}


if (!function_exists('ordinalRank')) {

    /**
     * Retourne le rang avec ordinal en FR ou EN, en tenant compte du sexe pour le 1er.
     *
     * @param int $rank
     * @param string $lang 'FRANCOPHONE' ou 'ANGLOPHONE'
     * @param string|null $gender 'M' ou 'F' (pour français)
     * @return string
     */
    function ordinalRank(int $rank, string $lang = 'FRANCOPHONE', ?string $gender = null): string
    {
        if ($lang === 'FRANCOPHONE') {
            if ($rank == 1) {
                return $gender === 'F' ? '1<sup>ère</sup>' : '1<sup>er</sup>';
            }
            return $rank . '<sup>ème</sup>';
        } 

        if ($lang === 'ANGLOPHONE') {
            if ($rank == 1) return '1<sup>st</sup>';
            if ($rank == 2) return '2<sup>nd</sup>';
            if ($rank == 3) return '3<sup>rd</sup>';
            return $rank . '<sup>th</sup>';
        }

        return (string)$rank;
    }
}


if (!function_exists('lastDateOfTranche')) {
    function lastDateOfTranche($value) {
        $date = "";
        $year = date('Y');
        if ($value === "first") {
            $date = $year."-09-30";
        } elseif ($value === "second") {
            $date = $year."-11-30";
        } elseif ($value === 'third') {
            $date = $year."-01-30";
        } else {
            $date = date('Y-m-d');
        }

        return Carbon::parse($date)->format('Y-m-d');
    }
}
if (!function_exists('translateDate')) {
    function translateDate($date, $lang)
    {
        if ($lang == 'fr') {
            Carbon::setLocale('fr');
        } else {
            Carbon::setLocale('en');
        }

        $carbonDate = Carbon::parse($date);

        $formattedDate = $carbonDate->translatedFormat('l j F Y');

        return $formattedDate;
    }
}
if (!function_exists('translateCycle')) {
    function translateCycle($cycle)
    {
        if ($cycle == "MATERNELLE") {
            $cycle_en = "NURSERY";
        } elseif($cycle == "PRIMAIRE") {
            $cycle_en = "PRIMARY";
        }elseif($cycle == "SECONDAIRE") {
            $cycle_en = "HIGH";
        }else{
            $cycle_en = "UNKNOWN";
        }


        return $cycle_en;
    }
}

if (!function_exists('getTrimestersWithSequences')) {
    function getTrimestersWithSequences()
    {
        return [
            'first-term' => [
                'name_fr' => 'Premier Trimestre',
                'name_en' => 'First Term',
                'value' => 'first-term',
                'sequences' => [1, 2],
                'type' => 'term',
                'number' => 1
            ],
            'second-term' => [
                'name_fr' => 'Deuxième Trimestre',
                'name_en' => 'Second Term',
                'value' => 'second-term',
                'sequences' => [3, 4],
                'type' => 'term',
                'number' => 2
            ],
            'third-term' => [
                'name_fr' => 'Troisième Trimestre',
                'name_en' => 'Third Term',
                'value' => 'third-term',
                'sequences' => [5, 6],
                'type' => 'term',
                'number' => 3
            ],
            'annual' => [
                'name_fr' => 'Annuel',
                'name_en' => 'Annual',
                'value' => 'annual',
                'sequences' => [1, 2, 3, 4, 5, 6],
                'type' => 'annual',
                'number' => 4
            ]
        ];
    }
}

if (!function_exists('getSequencesByTerm')) {
    function getSequencesByTerm($termValue)
    {
        $trimesters = getTrimestersWithSequences();
        return $trimesters[$termValue]['sequences'] ?? [];
    }
}

if (!function_exists('getNameFrByTerm')) {
    function getNameFrByTerm($termValue)
    {
        $trimesters = getTrimestersWithSequences();
        return $trimesters[$termValue]['name_fr'] ?? [];
    }
}
if (!function_exists('getNameEnByTerm')) {
    function getNameEnByTerm($termValue)
    {
        $trimesters = getTrimestersWithSequences();
        return $trimesters[$termValue]['name_en'] ?? 'Term';
    }
}

if (!function_exists('getNumberByTerm')) {
    function getNumberByTerm($termValue)
    {
        $numbers = [
            'first-term' => 1,
            'second-term' => 2, 
            'third-term' => 3,
            'annual' => null
        ];
        return $numbers[$termValue] ?? null;
    }
}
if (!function_exists('getTermType')) {
    function getTermType($termValue)
    {
        $trimesters = getTrimestersWithSequences();
        return $trimesters[$termValue]['type'] ?? 'term';
    }
}

if (!function_exists('getStudentsWithTermRank')) {
    function getStudentsWithTermRank($studentId, $classId, $session, $termValue, $lang = null)
    {
        $termType = getTermType($termValue);
        $sequences = getSequencesByTerm($termValue);
        
        // Vérifier si le rang existe déjà
        $existingRank = DB::table('student_term_ranks')
            ->where('usr_id', $studentId)
            ->where('class_id', $classId)
            ->where('annee_date', $session)
            ->where('term_type', $termValue)
            ->first();
            
        if ($existingRank) {
            return $existingRank;
        }
        
        // Récupérer les moyennes de chaque séquence pour cet étudiant
        $sequenceAverages = [];
        
        foreach ($sequences as $sequence) {
            $studentData = getStudentsWithRankAll($classId, $session, $sequence, $lang);
            
            // Trouver l'étudiant dans les résultats
            foreach ($studentData as $data) {
                if ($data['student']->usr_id == $studentId) {
                    $sequenceAverages[] = $data['moyenneGenerale'];
                    break;
                }
            }
        }
        
        // Calculer la moyenne du trimestre/annuel
        if (empty($sequenceAverages)) {
            return null;
        }
        
        $moyenneTerm = array_sum($sequenceAverages) / count($sequenceAverages);
        
        // Insérer le nouveau rang
        $rankId = DB::table('student_term_ranks')->insertGetId([
            'usr_id' => $studentId,
            'class_id' => $classId,
            'annee_date' => $session,
            'term_type' => $termValue,
            'average' => $moyenneTerm,
            'total_points' => array_sum($sequenceAverages), // Pour information
            'rank' => 0, // Temporaire, sera mis à jour après
            'created_at' => now(),
            'updated_at' => now()
        ]);
        
        return (object) [
            'id' => $rankId,
            'usr_id' => $studentId,
            'class_id' => $classId,
            'annee_date' => $session,
            'term_type' => $termValue,
            'average' => $moyenneTerm,
            'total_points' => array_sum($sequenceAverages),
            'rank' => 0
        ];
    }
}

if (!function_exists('calculateTermRanksOld')) {
    function calculateTermRanksOld($classId, $session, $termValue, $lang = null)
    {
        $sequences = getSequencesByTerm($termValue);
        
        // Récupérer tous les étudiants de la classe
        $students = DB::table('inscriptions')
            ->join('users', 'inscriptions.usr_id', '=', 'users.usr_id')
            ->where('inscriptions.class_id', $classId)
            ->where('inscriptions.inscrip_annee', $session)
            ->select('users.usr_id')
            ->get();
            
        $studentsData = [];
        
        foreach ($students as $student) {
            $sequenceAverages = [];
            
            // Récupérer la moyenne de chaque séquence
            foreach ($sequences as $sequence) {
                $studentRankData = getStudentsWithRankAll($classId, $session, $sequence, $lang);
                
                foreach ($studentRankData as $data) {
                    if ($data['student']->usr_id == $student->usr_id) {
                        $sequenceAverages[] = $data['moyenneGenerale'];
                        break;
                    }
                }
            }
            
            if (!empty($sequenceAverages)) {
                $moyenneTerm = array_sum($sequenceAverages) / count($sequenceAverages);
                
                $studentsData[$student->usr_id] = [
                    'average' => $moyenneTerm,
                    'total_points' => array_sum($sequenceAverages)
                ];
                
                // CORRECTION : Ajouter lang_type_rank dans la mise à jour
                DB::table('student_term_ranks')->updateOrInsert(
                    [
                        'usr_id' => $student->usr_id,
                        'class_id' => $classId,
                        'annee_date' => $session,
                        'term_type' => $termValue,
                        'lang_type_rank' => $lang // Ajouter la langue comme critère
                    ],
                    [
                        'average' => $moyenneTerm,
                        'total_points' => array_sum($sequenceAverages),
                        'lang_type_rank' => $lang, // Stocker la langue
                        'updated_at' => now()
                    ]
                );
            }
        }
        
        // Trier par moyenne générale décroissante
        uasort($studentsData, function($a, $b) {
            return $b['average'] <=> $a['average'];
        });
        
        // Calcul des rangs avec gestion des ex-aequo
        $rank = 1;
        $previousAverage = null;
        $sameRankCount = 0;
        
        foreach ($studentsData as $studentId => $studentData) {
            if ($previousAverage !== null && abs($studentData['average'] - $previousAverage) < 0.01) {
                // Même moyenne = même rang
                $sameRankCount++;
            } else {
                // Nouvelle moyenne = nouveau rang
                $rank += $sameRankCount;
                $sameRankCount = 0;
            }
            
            // CORRECTION : Mettre à jour avec lang_type_rank
            DB::table('student_term_ranks')
                ->where('usr_id', $studentId)
                ->where('class_id', $classId)
                ->where('annee_date', $session)
                ->where('term_type', $termValue)
                ->where('lang_type_rank', $lang) // Filtrer par langue
                ->update(['rank' => $rank]);
            
            $previousAverage = $studentData['average'];
            
            // Préparer le rang suivant
            if ($sameRankCount == 0) {
                $rank++;
            }
        }
    }
}

if (!function_exists('calculateTermRanks')) {
    function calculateTermRanks($classId, $session, $termValue, $lang = null)
    {
        $sequences = getSequencesByTerm($termValue);
        
        // Récupérer tous les étudiants de la classe
        $students = DB::table('inscriptions')
            ->join('users', 'inscriptions.usr_id', '=', 'users.usr_id')
            ->where('inscriptions.class_id', $classId)
            ->where('inscriptions.inscrip_annee', $session)
            ->select('users.usr_id')
            ->get();
            
        $studentsData = [];
        
        foreach ($students as $student) {
            $sequenceAverages = [];
            
            // Récupérer la moyenne de chaque séquence
            foreach ($sequences as $sequence) {
                // Pour les classes unilingues (lang=null), getStudentsWithRankAll doit gérer le filtre langue automatiquement
                $studentRankData = getStudentsWithRankAll($classId, $session, $sequence, $lang);
                
                foreach ($studentRankData as $data) {
                    if ($data['student']->usr_id == $student->usr_id) {
                        $sequenceAverages[] = $data['moyenneGenerale'];
                        break;
                    }
                }
            }
            
            if (!empty($sequenceAverages)) {
                $moyenneTerm = array_sum($sequenceAverages) / count($sequenceAverages);
                
                $studentsData[$student->usr_id] = [
                    'average' => $moyenneTerm,
                    'total_points' => array_sum($sequenceAverages)
                ];
                
                // Pour les classes unilingues, lang_type_rank = NULL
                // Pour les classes bilingues, lang_type_rank = 'FR' ou 'EN'
                DB::table('student_term_ranks')->updateOrInsert(
                    [
                        'usr_id' => $student->usr_id,
                        'class_id' => $classId,
                        'annee_date' => $session,
                        'term_type' => $termValue,
                        'lang_type_rank' => $lang // NULL pour unilingue, 'FR'/'EN' pour bilingue
                    ],
                    [
                        'average' => $moyenneTerm,
                        'total_points' => array_sum($sequenceAverages),
                        'lang_type_rank' => $lang,
                        'updated_at' => now()
                    ]
                );
            }
        }
        
        // Trier par moyenne générale décroissante
        uasort($studentsData, function($a, $b) {
            return $b['average'] <=> $a['average'];
        });
        
        // Calcul des rangs avec gestion des ex-aequo
        $rank = 1;
        $previousAverage = null;
        $sameRankCount = 0;
        
        foreach ($studentsData as $studentId => $studentData) {
            if ($previousAverage !== null && abs($studentData['average'] - $previousAverage) < 0.01) {
                // Même moyenne = même rang
                $sameRankCount++;
            } else {
                // Nouvelle moyenne = nouveau rang
                $rank += $sameRankCount;
                $sameRankCount = 0;
            }
            
            // Mettre à jour le rang avec le bon filtre langue
            $query = DB::table('student_term_ranks')
                ->where('usr_id', $studentId)
                ->where('class_id', $classId)
                ->where('annee_date', $session)
                ->where('term_type', $termValue);
            
            if ($lang) {
                $query->where('lang_type_rank', $lang);
            } else {
                $query->whereNull('lang_type_rank');
            }
            
            $query->update(['rank' => $rank]);
            
            $previousAverage = $studentData['average'];
            
            // Préparer le rang suivant
            if ($sameRankCount == 0) {
                $rank++;
            }
        }
    }
}
// Fonction utilitaire pour récupérer le rang d'un étudiant après calcul
if (!function_exists('getStudentTermRank')) {
    function getStudentTermRank($studentId, $classId, $session, $termValue, $langType = null)
    {
        $query = DB::table('student_term_ranks')
            ->where('usr_id', $studentId)
            ->where('class_id', $classId)
            ->where('annee_date', $session)
            ->where('term_type', $termValue);
        
        // Si lang_type est spécifié, filtrer par langue
        if ($langType) {
            $query->where('lang_type_rank', $langType);
        } else {
            // Pour les systèmes unilingues, prendre le rang sans langue
            $query->whereNull('lang_type_rank');
        }
        
        return $query->first();
    }
}

if (!function_exists('getStudentSequenceAverage')) {
    function getStudentSequenceAverage($studentId, $classId, $session, $sequence, $lang = null)
    {
        // Version optimisée pour récupérer juste la moyenne d'une séquence
        $student = User::with(['notes' => function($q) use ($classId, $session, $sequence) {
                $q->where('class_id', $classId)
                  ->where('note_annee', $session)
                  ->where('trim_id', $sequence);
            }, 'notes.sousMatiere', 'notes.competence'])
            ->where('usr_id', $studentId)
            ->first();

        if (!$student) return 0;

        $totalNote = 0;
        $totalBareme = 0;

        foreach ($student->notes as $note) {
            if ($note->note_s_mat_usr !== null && $note->sousMatiere && $note->competence) {
                
                $shouldInclude = false;
                if ($lang === 'FR') {
                    $shouldInclude = $note->competence->lang_type !== 'EN';
                } elseif ($lang === 'EN') {
                    $shouldInclude = $note->competence->lang_type === 'EN';
                } else {
                    $shouldInclude = true;
                }

                if ($shouldInclude) {
                    $totalNote += $note->note_s_mat_usr;
                    $totalBareme += $note->sousMatiere->s_mat_bareme_max ?? 20;
                }
            }
        }

        return $totalBareme > 0 ? ($totalNote / $totalBareme) * 20 : 0;
    }
}


if (!function_exists('calculateStudentResults')) {
    function calculateStudentResults($studentData, $classId, $session, $term, $classSession, $gender, $rank = null)
    {
        // Récupérer les totaux depuis les données passées par le contrôleur
        $totalGeneral = $studentData['total_points'] ?? 0;
        $baremeGeneralMax = $studentData['total_bareme'] ?? 0;
        $sequenceCount = $studentData['sequence_count'] ?? 1;
        
        // Calcul de la moyenne générale sur 20 (déjà calculée dans le contrôleur)
        $moyenneGenerale = $studentData['average'] ?? 0;
        
        // Appréciation générale
        $appreciationGenerale = getRatingGenerale($moyenneGenerale, $classSession);
        
        // CALCUL DE LA MOYENNE GÉNÉRALE DE LA CLASSE
        $classAverage = 0;
        
        // Récupérer toutes les données des étudiants pour calculer la moyenne de classe
        $allStudentsRanks = DB::table('student_term_ranks')
            ->where('class_id', $classId)
            ->where('annee_date', $session)
            ->where('term_type', $term)
            ->get();
        
        if (!$allStudentsRanks->isEmpty()) {
            $classAverage = $allStudentsRanks->avg('average');
        }

        return [
            'totalGeneral' => round($totalGeneral, 2),
            'baremeGeneralMax' => round($baremeGeneralMax, 2),
            'moyenneGenerale' => $moyenneGenerale,
            'appreciationGenerale' => $appreciationGenerale,
            'classAverage' => $classAverage,
            'rankDisplay' => ordinalRank($rank ?? 0, $classSession, $gender)
        ];
    }
}

if (!function_exists('formatNote')) {
    function formatNote($value, $decimals = 0) {
        if ($value === null || $value === '') {
            return '';
        }
        
        $floatValue = floatval($value);
        
        // Si c'est un entier, pas de décimales
        if ($floatValue == intval($floatValue)) {
            return number_format($floatValue, 0);
        }
        
        // Si c'est un nombre décimal, utiliser le nombre de décimales spécifié
        return number_format($floatValue, $decimals);
    }
}

if (!function_exists('getStudentDecisionWithDetails')) {
    function getStudentDecisionWithDetails($average, $term, $lang = 'FR') {
        // Normaliser la langue - gérer à la fois 'FR' et 'FRANCOPHONE'
        $isFrench = (strtoupper($lang) === 'FR' || strtoupper($lang) === 'FRANCOPHONE');
        
        if ($term !== 'annual') {
            return [
                'decision' => $isFrench ? 'EN COURS' : 'IN PROGRESS',
                'color' => 'blue',
                'description' => $isFrench ? 'Résultats intermédiaires' : 'Intermediate results'
            ];
        }
        
        if ($average >= 10) {
            return [
                'decision' => $isFrench ? 'PROMU(E)' : 'PROMOTED',
                'color' => 'green',
                'description' => $isFrench ? 'Admis en classe supé