<?php

namespace App\Imports;

use App\Models\ParentEleve;
use App\Models\Student;
use App\Models\School;
use App\Models\Nationality;
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 StudentExcelImport implements ToCollection, WithHeadingRow
{
    protected $school;
    protected $yearSchool;
    protected $table;
    public function __construct($school, $yearSchool, $table)
    {
        $this->school = $school;
        $this->yearSchool = $yearSchool;
        $this->table = $table;
    }

    /**
    * @param Collection $collection
    */
    public function collection(Collection $rows)
    {
        foreach($rows as $item){
            $dateNaiss = $this->dateFormat($item['date_naissance_eleve']);

            if(isset($item['type_enseignement']) && isset($item['nom_eleve']) && isset($item['prenoms_eleve']) && isset($item['pareent_charge_des_etudes']) && isset($dateNaiss)){
                if($this->verifyTypeEnseignement($item['type_enseignement'])){
                    $student = $this->verifyStudent($item['nom_eleve'], $item['prenoms_eleve'], $dateNaiss, $item['nom_pere'], $item['prenoms_pere'], $item['profession_pere']);             
                    if(!($this->verifyMatricule($item['matricule_eleve'])) && $student == 0){
                        if(!$this->verifyStudentExists(strtolower($item['nom_eleve']), strtolower($item['prenoms_eleve']), $this->verityGenreStudent($item['sexe_eleve']), $this->dateFormat($item['date_naissance_eleve']), strtolower($item['lieu_naissance_eleve']), strtolower($item['lieu_de_residence']))){
                            $parent = $this->verifyParent($item['1_numero_telephone'], $item['2_numero_telephone']);
                            if(!$parent){
                                $val = detnedValeur($item['pareent_charge_des_etudes']);
                                $parent = $this->saveParent($item['nom_'.$val], $item['prenoms_'.$val], determinedSex($item['pareent_charge_des_etudes']), $item['profession_'.$val], $item['1_numero_telephone'], $item['2_numero_telephone']);
                            }
                            
                            if($parent){
                                $val = $item['1_numero_telephone'].$item['2_numero_telephone'];
                                $item['matricule_eleve'] = changeVal($item['type_enseignement']) == 'primaire' ? $this->generatematriculePrimaire($item['matricule_eleve']):$item['matricule_eleve'];                                
                                // Save Student ----------------------------
                                if(isset($item['matricule_eleve']) && isset($item['nom_eleve']) && isset($item['prenoms_eleve'])){

                                    $this->verifyEndCreateStudent(
                                        $item['matricule_eleve'], strtolower($item['nom_eleve']), strtolower($item['prenoms_eleve']), $this->verityGenreStudent($item['sexe_eleve']),
                                        $dateNaiss, strtolower($item['lieu_naissance_eleve']), $this->id_pays($item['pays_de_naissance']) ?? '109', strtolower($item['lieu_de_residence']),
                                        strtolower($item['nom_pere']), strtolower($item['prenoms_pere']), strtolower($item['profession_pere']), strtolower($item['nom_mere']), strtolower($item['prenoms_mere']),
                                        strtolower($item['profession_mere']), changeVal($item['type_enseignement']), $parent->id
                                    );
                                }
                            }
                        }
                    }
                }
            }
        }
    }

    // Vérification et enregistrement d'un parent en charge des études de l'élève
    private function verifyParent($numero1, $numero2 = null)
    {
        $parent = ParentEleve::where('telephon1', $numero1)->orWhere('telephon2', $numero1)->first();
        $parent = $parent ?? ($numero2 ? ParentEleve::where('telephon1', $numero2)->orWhere('telephon2', $numero2)->first():null);
        return $parent ?? null;
    }

    // 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', $nom)->where('last_name', $prenom)->where('date_birth', date('d-m-Y', strtotime($datNaiss)))->get();
        $cont = $student->where('sexe', $sexe)->where('place_birth', $lieuNaiss)->where('place_residence', $residence)->count();
        return $cont;
    }

    private function saveParent($fistName, $lastName = null, $sexe, $prof = null, $phon1, $phon2 = null)
    {
        if(isset($fistName) && isset($sexe) && isset($phon1)){
            $dts = ParentEleve::create([
                'first_name' => strtolower($fistName),
                'last_name' => strtolower($lastName ?? 'A DEFINIR'),
                'sexe' => $this->veritySexeParent($sexe),
                'profession' => strtolower($prof ?? 'A DEFINIR'),
                'telephon1' => $phon1,
                'telephon2' => $phon2 ?? null,
                'school_id' => $this->school,
                'school_year_id' => $this->yearSchool
            ]);
        }
        return $dts ?? null;
    }


    // Verification de l'existence d'un eleve par son matricule
    private function verifyMatricule($matricule)
    {
        $matricule = Student::where('matricule', $matricule)->first();
        return $matricule ? true:false;
    }


    // Verification de l'existence d'un eleve par son nom, prenoms et sa date de naissance
    private function verifyStudent($nom, $prenom, $datNaiss, $nomPere, $prenomPere, $profPere)
    {
        $student = Student::where('first_name', $nom)->where('last_name', $prenom)->where('date_birth', date('m-d-Y', strtotime($datNaiss)))->get();
        $cont = $student->where('first_name_father', $nomPere)->where('last_name_father', $prenomPere)->where('profession_father', $profPere)->count();
        return $cont;
    }


    // 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 .........
    }


    // Recupération de l'id du pays de naissance de l'élève
    private function id_pays($libelle)
    {
        $dts = Nationality::where('libelle', strtolower($libelle))->first();
        return  $dts ? $dts->id:null;
    }


    private function verifyTypeEnseignement($type)
    {
        foreach($this->table as $item){
            if(($item == $type)){
                $id = 1;
            }
        }
        return $id ?? null;
    }


    private function dateFormat($value, $format = 'd-m-Y')
    {
        if (is_numeric($value)) {
            try {
                return Carbon::instance(ExcelDate::excelToDateTimeObject($value));
            } catch (\Exception $e) {
                return null;
            }
        } else {
            try {
                return Carbon::createFromFormat($format, $value);
            } catch (\Exception $e) {
                $value = DateTime::createFromFormat('d/m/Y', $value);
                // return $value->format('d-m-Y');
                return $value;
            }
        }
    }


     // 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';
    }


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

}