<?php

namespace App\Imports;

use App\Models\Classe;
use App\Models\Student;
use App\Models\Inscription;
use App\Models\ParentEleve;
use App\Models\School;
use App\Events\PaymentGetEvent;
use App\Jobs\StatutPyramideJob;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use PhpOffice\PhpSpreadsheet\Shared\Date as ExcelDate;
use Carbon\Carbon;
use DateTime;

class RegisterStudentImport implements ToCollection, WithHeadingRow
{
    protected $school;
    protected $year;

    public function __construct($school, $year)
    {
        $this->school = $school;
        $this->year = $year;
    }

    /**
    * @param Collection $collection
    */
    public function collection(Collection $rows)
    {
        //dd($this->dateFormat($rows[5]['date_naissance']));
        foreach($rows as $item){
            $i = 0;
            while($i < (int)$item['n']){
                if(isset($item['nom']) && isset($item['premons']) &&  isset($item['lieu_de_residence']) &&  isset($item['charge_des_etudes']) &&  isset($item['contact1']) && isset($item['sexe']) && $this->dateFormat($item['date_naissance']) && isset($item['lieu_naissance'])){
                    if($this->getClasse($item['classe'])){
                        $class = $this->getClasse($item['classe']);
                        if(!($class['type'] == 'primaire')){
                            $lv2 = $class['lv2'] ? ($class['lv2'] == 'mixte' ? $this->verifyLv2($item['lv2']):explode('-', str_replace(' ', '', $item['classe']))[1]):null;
                            if($this->verifyMatriculeChiffre($item['matricule']) && $this->verifyBooleen($item['affecte']) && $this->verifyBooleen($item['doublant']) && $this->verifyBooleen($item['bousier']) && $this->verifyBooleen($item['ancien_eleve'])){
                                // 1000 ...................
                                if(!$this->verifyStudentExists($item['nom'], $item['premons'], $this->verityGenreStudent($item['sexe']), $this->dateFormat($item['date_naissance']), $item['lieu_naissance'], $item['lieu_de_residence'])){
                                    $parent = $this->verifySaveParent($item['nom_parent'], $item['prenom_parent'], $item['charge_des_etudes'], $item['profession_parent'], $item['contact1'], $item['contact2']);
                                    if($parent){
                                        $this->globalVerify($parent,
                                            $item['matricule'], $item['nom'], $item['premons'], $this->verityGenreStudent($item['sexe']), $this->dateFormat($item['date_naissance']), $item['lieu_naissance'], $item['lieu_de_residence'],
                                            $item['ancien_eleve'], $this->verifyBooleen($item['affecte']), $this->verifyBooleen($item['doublant']), $this->verifyBooleen($item['bousier']), $item['classe'], $lv2
                                        );
                                    }
                                }
                            }
                        }
                        else{
                            // Traitement pour les élèves de primaires
                            if($this->verifyBooleen($item['doublant']) && $this->verifyBooleen($item['ancien_eleve'])){
                                if(!$this->verifyStudentExists($item['nom'], $item['premons'], $this->verityGenreStudent($item['sexe']), $this->dateFormat($item['date_naissance']), $item['lieu_naissance'], $item['lieu_de_residence'])){
                                    $parent = $this->verifySaveParent($item['nom_parent'], $item['prenom_parent'], $item['charge_des_etudes'], $item['profession_parent'], $item['contact1'], $item['contact2']);
                                    if($parent){
                                        $matricule = $item['matricule'] ? $this->generatematriculePrimaire($item['matricule']):null;
                                        $this->globalVerify($parent,
                                            $matricule, $item['nom'], $item['premons'], $this->verityGenreStudent($item['sexe']), $this->dateFormat($item['date_naissance']), $item['lieu_naissance'], $item['lieu_de_residence'],
                                            $item['ancien_eleve'], null, $this->verifyBooleen($item['doublant']), null, $item['classe'], null
                                        );  
                                    }
                                }
                            }
                        }
                    }
                }
                $i++;
            }
        }
    }


    private function globalVerify($parent, $matricule, $nom, $prenom, $sexe, $dtenais, $lieuNais, $residence, $statut, $affecte = null, $redoublant, $bousier = null, $classe, $lv2 = null)
    {
        $class = $this->getClasse($classe);
        $student = $this->verifyEndCreateStudent($matricule, $nom, $prenom, $sexe, $dtenais, $lieuNais, $residence, $class->type, $parent);
        if($student && $class->id){
            $inscrit = Inscription::where('student_id', $student)->where('classe_id', $class->id)->count();
            if(!$inscrit){
                if($this->createInscription($redoublant, $affecte, $bousier, $statut, $student, $class->id, $class->level_id, $class->serie_id, $lv2, $sexe, $class->type)){
                    $class = Classe::find($class->id);
                    $class->update(['inscrits' => $class['inscrits']+=1]);
                }
            }
        }
    }


    // Verification de l'existence d'un eleve par son matricule
    private function verifyEndCreateStudent($matricule, $nom, $prenom, $sexe, $dtenais, $lieuNais, $residence, $type, $parent)
    {
        $val = Student::where('matricule', $matricule)->first();
        if(!$val){
            if($parent){
                $val = Student::create([
                    'matricule' => str_replace(' ', '', $matricule),
                    'first_name' => strtolower(str_replace(' ', '', $nom)),
                    'last_name' => strtolower($prenom),
                    'sexe' => $sexe,
                    'date_birth' => $dtenais,
                    'place_birth' => strtolower($lieuNais),
                    'nationalitie_id' => '109', // Default end Cote d'ivoire
                    'place_residence' => strtolower($residence),
                    'first_name_father' => '---',
                    'last_name_father' => '---',
                    'profession_father' => '---',
                    'first_name_mother' => '---',
                    'last_name_mother' => '---',
                    'profession_mother' => '---',
                    'type' => $type,
                    'photo' => null,
                    'parent_eleve_id' => $parent,
                    'school_id' => $this->school,
                    'school_year_id' => $this->year,
                ]);
            }
        }
        return $val->id;
    }


    private function verifyBooleen($val)
    {   
        return strtolower($val) == 'oui' ? 'oui':'non';
    }


    // Verification de la classe ................
    private function getClasse($str)
    {
        $val = explode('-', str_replace(' ', '', $str));
        $class = Classe::where('libelle', $val[0])->where('school_id', $this->school)->where('school_year_id', $this->year)->first();
        return $class;
    }


    // Verification du sexe de parent en charge des etudes .........
    private function veritySexeParent($val)
    {
        if($val == 'mère' || $val == 'tutrice'){
            $genre = 'F';
        }
        return $genre ?? 'H';
    }


    private function verityGenreStudent($genre)
    {
        if (preg_match("/^f.*$/", strtolower($genre))) {
            $val = 'F';
        }
        return $val ?? 'M';
    }

    
    // Verifier si la date est valable pour l'eleve ....................
    private function dateFormat($value)
    {
        if ($value && is_numeric($value)) {
            $formattedDate = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value)->format('d-m-Y');
        } elseif ($value) {
            try {
                return Carbon::createFromFormat('d-m-Y', $value);
            } catch (\Exception $e) {
                $value = DateTime::createFromFormat('d/m/Y', $value);
                return $value->format('d-m-Y');
            }
        }
        return $formattedDate;
    }


    // Verification de l'existence d'un eleve par son matricule
    private function verifyMatriculeChiffre($matricule)
    {
        return (strlen($matricule) == 9 )?? null;
    }


    private function verifyLv2($texte)
    {
        if (preg_match("/^all.*$/", strtolower($texte))) {
            $val = 'allemand';
        }
        elseif (preg_match("/^esp.*$/", strtolower($texte))) {
            $val = 'espagnol';
        }
        return $val ?? 'allemand';
    }


    // Génération de matricule pour les élèves de primaires
    private function generatematriculePrimaire($val)
    {
        $school = School::find($this->school);
        $str = substr($school->name_school, 0, 2);
        return $val.strtoupper($str).$this->school; // Matricule pour le primaiare .........
    }


    // Verification de l'existence d'un eleve par son nom, prenoms et sa date de naissance
    private function verifyStudentExists($nom, $prenom, $sexe, $datNaiss, $lieuNaiss, $residence)
    {
        $student = Student::where('first_name', strtolower($nom))->where('last_name', strtolower($prenom))->where('date_birth', $this->dateFormat($datNaiss))->get();
        $cont = $student->where('sexe', $sexe)->where('place_birth', strtolower($lieuNaiss))->where('place_residence', strtolower($residence))->count();
        return $cont;
    }


    // Save parent en charge des Etudes ..................................................
    private function verifySaveParent($nom, $prenom = null, $sexe, $fonction = null, $phon1, $phon2 = null)
    {
        $parent = ParentEleve::where('telephon1', $phon1)->orWhere('telephon2', $phon1)->first();
        $parent = $parent ?? ($phon2 ? ParentEleve::where('telephon1', $phon2)->orWhere('telephon2', $phon2)->first():null);

        if(!$parent){
            if(isset($nom) && isset($sexe) && isset($phon1)){
                $parent = ParentEleve::create([
                    'first_name' => strtolower($nom),
                    'last_name' => strtolower($prenom),
                    'sexe' => $this->veritySexeParent($sexe),
                    'profession' => strtolower($fonction),
                    'telephon1' => $phon1,
                    'telephon2' => $phon2 ?? null,
                    'school_id' => $this->school,
                    'school_year_id' => $this->year
                ]);
            }
        }
        return $parent ? $parent->id:null;
    }


    private function createInscription($doublant, $affect = null, $boursier = null, $statut, $student, $classe, $level, $serie = null, $lv2 = null, $sexe, $type)
    {
        $exist = Inscription::where('student_id', $student)->where('school_year_id', $this->year)->where('school_id', $this->school)->count();
        if(!$exist){
            $create = Inscription::create([
                'redoublant' => $doublant,
                'affecte' => $affect,
                'boursier' => $boursier,
                'lv2' => $lv2,
                'serie_id' => $serie,
                'level_id' => $level,
                'student_id' => $student,
                'classe_id' => $classe,
                'ancient' => $statut == 'oui' ? '1':'0', // 1 = ancien élève
                'school_id' => $this->school,
                'school_year_id' => $this->year
            ]);

            // Déclenchement de l’événement
            if(VerifyBox(auth()->user()->school->setting, 'box')){
                event(new PaymentGetEvent($create['id'], $classe, $this->school, $this->year));
            }
            
            // Déclenchement de Job ---------------------->
            if(auth()->user()->school->belonging == 'cdec'){
                $type == 'secondaire' ? StatutPyramideJob::dispatch($sexe, $this->year, $level, $serie = null, $this->school, $affect, $doublant):null;
            }
        }
        return $exist ? null:$create;
    }
}