<?php

namespace App\Http\Controllers;
use App\Models\SchoolYear;
use App\Models\Setting;
use App\Models\Student;
use App\Models\Scolarity;
use App\Models\Level;
use App\Models\Serie;
use App\Models\Classe;
use App\Models\Inscription;
use App\Events\PaymentGetEvent;
use App\Events\StatutPyramideEvent;
use App\Jobs\StatutPyramideJob;
use App\Exports\StudentClasseExport;
use App\Imports\RegisterStudentImport;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Support\Facades\DB;
use Yajra\DataTables\Facades\DataTables;
use Illuminate\Http\Request;
use Illuminate\Support\Str;
use PDF;

class InscriptionController extends Controller
{
    /**
     * Display a listing of the resource.
     */
    public function index()
    {
        try{
            $data = Setting::where('school_id',auth()->user()->school_id)->first();
            $inscris = Inscription::where('school_id', auth()->user()->school_id)->where('school_year_id', $this->yearActif()->id)->count();
            $types = $this->typeEnseignement($data);
            return view('pages.inscriptions.index',[
                'year' => $this->yearActif(),
                'inscris' => $inscris,
                'statistics' => $this->inscriptionStatistik($data),
                'cycles' => sizeof($types) > 1 ? $types:[],
                'level' => sizeof($types) == 1 ? $this->levelsGet($types[0]['libelle']):[]
            ]);
        }
        catch (\Exception $e) {
            return back()->with([
                'str' => 'danger',
                'msg' => 'Une erreur est survenue !'
            ]);
        }
    }


    public function dataTable()
    {
        $query = Inscription::where('school_id', auth()->user()->school_id)->where('school_year_id', $this->yearActif()->id)->orderBy('created_at', 'DESC')->get();
        return DataTables::of($query)
        ->addColumn('image', function($row) {
            $url = asset($row->photo ? $row->urlStudent():($row->sexe == 'F' ? 'assets/images/stdt1_woman.png':'assets/images/stdt1_man.png'));
            return ('<img src="'.$url.'" class="img-radius m-0 p-0" alt="Photo Stuedent" style="height: 40px; width: 40px; border: 1px solid grey">');
        })
        ->addColumn('matricule', function ($row) {
            return ('<p class="m-0 pt-2">'.$row->student->matricule.'</p>');
        })
        ->addColumn('firstName', function ($row) {
            return ('<p class="m-0 pt-2">'.strtoupper($row->student->first_name).'</p>');
        })
        ->addColumn('lastName', function ($row) {
            return ('<p class="m-0 pt-2">'.ucwords($row->student->last_name).'</p>');
        })
        ->addColumn('genre', function ($row) {
            return ('<p class="m-0 pt-2">'.ucwords($row->student->sexe == 'F' ? 'Feminin':'Masculin').'</p>');
        })
        ->addColumn('classe', function ($row) {
            return ('<p class="m-0 pt-2">'.ucwords($row->classe->libelle).'</p>');
        })
        ->addColumn('created', function ($row) {
            return ('<p class="m-0 pt-2">'.(date('d-m-Y', strtotime($row->created_at)).' à '.date('H:i', strtotime($row->created_at))).'</p>');
        })
        ->addColumn('action', function ($row) {
            $url = route('inscription.pdf',$row->id);
            return ('<span class="card-block remove-label m-0 text-center">
                <a href="'.$url.'" target="_blank" class="btn btn-inverse btn-outline-inverse btn-icons" title="imprim"><i class="fas fa-file-alt m-0"></i></a>
                <button class="btn btn-inverse btn-outline-inverse btn-icons btnDelete pb-2 ml-1" title="annule" data-id="'.$row->id.'"><i class="far fa-trash-alt m-0"></i></button>
            </span>');
        })
        ->rawColumns(['image', 'matricule', 'firstName', 'lastName', 'genre', 'classe', 'created', 'action'])
        ->make(true);
    }

    /**
     * Generate PDF the form for creating a new resource.
     */
    public function generatePdf($id){
        try{
            $data = Inscription::where('id', $id)->where('school_year_id', $this->yearActif()->id)->first();
            $setting = Setting::where('school_id', auth()->user()->school_id)->first();
            $pdf = PDF::loadView('pdf.autorisation', [
                'school' => $setting,
                'data' => $data,
            ]);
            $pdf->setPaper('A4', 'portrait'); // ou 'A4', 'A3', etc.
            // return $pdf->download('autorisation.pdf');
            return $pdf->stream('autorisation_'.$id.'.pdf');
        }
        catch (\Exception $e) {
            return back()->with([
                'str' => 'danger',
                'msg' => 'Une erreur est survenue !'
            ]);
        }
    }

    /**
     * Show the form for creating a new resource.
     */
    public function create(Request $request)
    {
        try{
            $valid = $request->validate([ 'mat' => 'required|string']);
            $student = Student::where('matricule', $valid['mat'])->where('school_id', auth()->user()->school_id)->first();
            $exist = $student ? Inscription::where('student_id', $student->id)->latest()->first():null;
            $verify = $exist ? ($exist['school_year_id'] == $this->yearActif()->id ? true:false):null;
            $dts = null;
            if($verify){
                $exist = [
                    'classe' => $exist->classe->libelle,
                    'date' => date('d/m/Y', strtotime($exist->created_at)),
                    'heure' => date('H:i:s', strtotime($exist->created_at))
                ];
            }
            else{
                if($student){
                    if($student['type'] == 'superieur'){
                        $dts =  DB::table('levels')
                        ->JOIN('filiere_school', 'levels.id', '=', 'filiere_school.level_id')
                        ->SELECT('levels.id', 'levels.code')
                        ->WHERE('filiere_school.school_id', '=', auth()->user()->school_id)
                        ->get();
                    }
                    else{
                        $getId = typeEnseignementId($student['type']);
                        $dts = sizeof($getId) == 2 ? Level::where('type_enseignement_id', $getId[0])->orWhere('type_enseignement_id', $getId[1])->get():Level::where('type_enseignement_id', $getId[0])->get();
                    }
                }    
            }
            return response()->json(['status' => $student ? 200:201, 'student' => $student ?? null, 'level' => $dts, 'exist' => $verify ? $exist:null, 'matricule' => $valid]);
        }
        catch (\Exception $e) {
            return back()->with([
                'str' => 'danger',
                'msg' => 'Une erreur est survenue !'
            ]);
        }
    }

    /**
     * Store a newly created resource in storage.
     */
    public function store(Request $request)
    {
        try{
            $validate = $request->validate([
                'student' => 'required|integer',
                'getType' => 'required|string',
                'level' => 'required|string',
                'classe' => 'required|integer',
                'redoublant' => 'required|string',
                'affecte' => 'nullable|string',
                'boursier' => 'nullable|string',
                'serie' => 'nullable|string',
                'lv2' => 'nullable|string',
                'typeStud' => 'nullable|string',
            ]);

            if(VerifyBox(auth()->user()->school->setting, 'box')){
                $class = Classe::find($validate['classe']);
                $val = $this->verifyScolarity($class['type'], $class['school_year_id'], $class['level_id'], $class['serie_id']);
                if($val){
                    $result = $this->verifyInscription($request);
                }
                else{
                    $result = ['str' => 'danger', 'msg' => 'Une erreur est survenue !'];
                }
            }
            else{
                $result = $this->verifyInscription($request);
            }
            return back()->with([
                'str' => $result['str'],
                'msg' => $result['msg']
            ]);
        }
        catch (\Exception $e) {
            return back()->with([
                'str' => 'danger',
                'msg' => 'Une erreur est survenue !'
            ]);
        }
    }

    /**
     * Display the specified resource.
     */
    public function export(Request $request)
    {
        try{
            $val = $request->validate([
                'classe' => 'required|string'
            ]);
            $lettres = Str::random(2);
            $class = Classe::where('id', $val['classe'])->first();
            $name = 'file_inscription-'.$lettres.'_'.$class['libelle'];
            return Excel::download(new StudentClasseExport($val['classe']), $name.'.xlsx');
        }
        catch (\Exception $e) {
            return back()->with([
                'str' => 'danger',
                'msg' => 'Une erreur est survenue !'
            ]);
        }
    }

    /**
     * Show the form for editing the specified resource.
     */
    public function import(Request $request)
    {
        try{

            $request->validate(['files' => 'required|mimes:xlsx']);
            $year = $this->yearActif();
            // Récupérer le fichier
            $fichier = $request->file('files');
            $fils = explode('.', $fichier->getClientOriginalName());
            $lib = explode('_', $fils[0]);
            $class = Classe::where('libelle', $lib[2])->where('school_id', auth()->user()->school_id)->where('school_year_id', $year->id)->first();
            if($class){
                if(VerifyBox(auth()->user()->school->setting, 'box')){
                    $val = $this->verifyScolarity($class['type'], $class['school_year_id'], $class['level_id'], $class['serie_id']);
                    if($val){
                        Excel::import(new RegisterStudentImport(auth()->user()->school_id, $year['id']), $request->file('files'));
                        $result = ['str' => 'info', 'msg' => 'Importation réussie !'];
                    }
                    else{
                        $result = ['str' => 'danger', 'msg' => 'Une erreur est survenue !'];
                    }
                }else{
                    Excel::import(new RegisterStudentImport(auth()->user()->school_id, $year['id']), $request->file('files'));
                    $result = ['str' => 'info', 'msg' => 'Importation réussie !'];
                }
            }
            else{
                $result = ['str' => 'danger', 'msg' => 'Une erreur est survenue !'];
            }
            return redirect()->route('inscription.index')->with([
                'str' => $result['str'],
                'msg' => $result['msg']
            ]);
        }
        catch (\Exception $e) {
            return back()->with([
                'str' => 'danger',
                'msg' => 'Une erreur est survenue !'
            ]);
        }
    }


    /**
     * Get level the specified resource from storage.
     */
    public function getLevel(Request $request){
        $val = $request['libelle'];
        if(strtolower($val) == 'superieur'){
            $dts =  DB::table('levels')
            ->JOIN('filiere_school', 'levels.id', '=', 'filiere_school.level_id')
            ->SELECT('levels.id', 'levels.code')
            ->WHERE('filiere_school.school_id', '=', auth()->user()->school_id)
            ->get();
        }
        else{
            $getId = getIdLevel($val);
            $dts = sizeof($getId) == 2 ? Level::where('type_enseignement_id', $getId[0])->orWhere('type_enseignement_id', $getId[1])->get():Level::where('type_enseignement_id', $getId[0])->get();
        }

        return response()->json($dts);
    }

    /**
     * Get serie the specified resource from storage.
     */
    public function getSerie(Request $request){
        if($request['level'] == 'superieur'){
            $dt = Serie::where('type', $request['level'])->get();
        }
        elseif($request['level'] == 'technique'){
            $dt = Serie::where('type', $request['level'])->where(strtolower($request['libelle']), '1')->get();
        }else{
            $dt = Serie::where('type', 'secondaire')->where(strtolower($request['libelle']), '1')->get();
        }
        return response()->json($dt);
    }


    /**
     * Get serie the specified resource from storage.
     */
    public function getClasse(Request $request){
        $year = SchoolYear::where('status', '1')->first();
        if($request['lv2'] && $request['serie'] == null){
            $dts = $this->classeLv2($request['id'], $request['lv2'], $year['id']);
        }
        elseif($request['lv2'] && $request['serie']){
            $dts = $this->classeSerieLv2($request['id'], $request['serie'], $request['lv2'], $year['id']);
        }
        elseif($request['lv2'] == null && $request['serie']){
            $dts = Classe::where('school_id', auth()->user()->school_id)->where('school_year_id', $year['id'])->where('level_id', $request['id'])->where('serie_id', $request['serie'])->whereColumn('inscrits', '<', 'effectif')->get();
        }
        else{
            $dts = Classe::where('school_id', auth()->user()->school_id)->where('level_id', $request['id'])->whereColumn('inscrits', '<', 'effectif')->get();
        }
        return response()->json($dts);
    }

    /**
     * Get inscription the specified resource from storage.
     */
    public function getDelete(Request $request){
        try{
            $exist = Inscription::where('id', $request['id'])->where('school_year_id', $this->yearActif()->id)->first();
            $data = [
                'first' => strtoupper($exist->student->first_name),
                'last' => ucwords($exist->student->last_name), 
                'classe' => $exist->classe->libelle,
                'created' => date('d/m/Y', strtotime($exist->created_at)),
                'id' => $exist->id
            ];
            return response()->json($data);
        }
        catch (\Exception $e) {
            return back()->with([
                'str' => 'danger',
                'msg' => 'Une erreur est survenue !'
            ]);
        }
    }

    /**
     * Remove the specified resource from storage.
     */
    public function destroy(Request $request)
    {
        try{
            $val = $request->validate([
                'id' => 'required|string'
            ]);
            $data = Inscription::where('id', $val['id'])->where('school_year_id', $this->yearActif()->id)->first();
            $class = Classe::where('id', $data['classe_id'])->where('school_id', auth()->user()->school_id)->first();
            if($data && $class){
                $data->delete();
                $class->update(['inscrits' => $class['inscrits']-1]);
                // Déclenchement de Job ---------------------->
                if((auth()->user()->school->belonging == 'cdec') && ($class['type'] == 'secondaire')){
                    event(new StatutPyramideEvent($data));
                }
            }
            return back()->with([
                'str' => 'info',
                'msg' => 'Inscription supprimée.'
            ]);
        }
        catch (\Exception $e) {
            return back()->with([
                'str' => 'danger',
                'msg' => 'Une erreur est survenue !'
            ]);
        }
    }



    protected function verifyInscription($validate)
    {
        if(Student::find($validate['student'])){
            if(!$this->verifyRegister($validate['student'])){
                $val = $this->saveInscription(
                $validate['student'], explode('_',$validate['level'])[0] ,$validate['classe'], $validate['redoublant'], $validate['typeStud'] ?? null,
                    $validate['affecte'] ?? null, $validate['boursier'] ?? null, $validate['serie'] ? explode('_', $validate['serie'])[0]:null, $validate['lv2'] ?? null
                );
                if($val){
                    $class = Classe::where('id', $validate['classe'])->where('school_id', auth()->user()->school_id)->first();
                    $class->update(['inscrits' => $class['inscrits']+=1]);

                    // Déclenchement de l’événement
                    if(VerifyBox(auth()->user()->school->setting, 'box')){
                        event(new PaymentGetEvent($val, $class['id'], auth()->user()->school_id, $class['school_year_id']));
                    }

                    // Déclenchement de Job ---------------------->
                    if(auth()->user()->school->belonging == 'cdec'){
                        $class['type'] == 'secondaire' ? StatutPyramideJob::dispatch($this->getSexe($validate['student']), $class['school_year_id'], $class['level_id'], $class['serie_id'], auth()->user()->school_id, $validate['affecte'], $validate['redoublant']):null;
                    }
                }
                $str = 'success';
                $msg = 'Inscription Réussie avec success.';
            }
            else{
                $str = 'info';
                $msg = 'Tentative de duplication.';
            }
        }
        else{
            $str = 'danger';
            $msg = 'Une erreur est survenue !';
        }

        return ['str' => $str, 'msg' => $msg];
    }


    protected function saveInscription($student, $level, $classe, $redoublant, $typeStud = null, $affect = null, $boursier = null, $serie = null, $lv2 = null)
    {
        $val = Inscription::create([
            'redoublant' => $redoublant,
            'affecte' => $affect,
            'boursier' => $boursier,
            'lv2' => $lv2,
            'serie_id' => $serie,
            'level_id' => $level,
            'student_id' => $student,
            'classe_id' => $classe,
            'school_id' => auth()->user()->school_id,
            'school_year_id' => $this->yearActif()->id,
            'ancient' => $typeStud ?? '1' // 1 = ancien élève
        ]);
        return $val->id;
    }



    protected function verifyRegister($student)
    {
        $verify = Inscription::where('student_id', $student)->where('school_id', auth()->user()->school_id)->where('school_year_id', $this->yearActif()->id)->count();
        return $verify ?? null;
    }


    /**
     * Remove the specified resource from storage.
     */
    protected function typeEnseignement($dts)
    {
        $tab = []; $primaire = 0; $cycle = 0;
        foreach ($dts->school->typeEnseignements as $item) {
            if(!$primaire && ($item['id'] == 1 || $item['id'] == 2)){
                $tab[] = [
                    'libelle' => verifyPrimaire($dts->school->typeEnseignements),
                ];$primaire++;
            }
            elseif(!$cycle && ($item['id'] == 3 || $item['id'] == 4)){
                $tab[] = [
                    'libelle' => verifySeconde($dts->school->typeEnseignements),
                ]; $cycle++;
            }
            elseif($item['id'] == 5 || $item['id'] == 6){
                $tab[] =[
                    'libelle' => $item['type_enseignement']
                ];
            }
        }
        return $tab;
    }


    protected function classeLv2($level, $lv2, $year)
    {
        $vals = Classe::where('school_id', auth()->user()->school_id)->where('school_year_id', $year)->where('level_id', $level)->whereColumn('inscrits', '<', 'effectif')->get();
        $dts = [];
        foreach($vals as $item){
            if($item['lv2'] == $lv2 || $item['lv2'] == 'mixte'){
                $dts[] = $item;
            }
        }
        return $dts;
    }


    protected function classeSerieLv2($level, $serie, $lv2, $year)
    {
        $vals = Classe::where('school_id', auth()->user()->school_id)->where('school_year_id', $year)->where('level_id', $level)->where('serie_id', $serie)->whereColumn('inscrits', '<', 'effectif')->get();
        $dts = [];
        foreach($vals as $item){
            if($item['lv2'] == $lv2 || $item['lv2'] == 'mixte'){
                $dts[] = $item;
            }
        }
        return $dts;
    }


    protected function levelsGet($type)
    {
        if(strtolower($type) == 'superieur'){
           $dts =  DB::table('levels')
           ->JOIN('filiere_school', 'levels.id', '=', 'filiere_school.level_id')
           ->SELECT('levels.id', 'levels.code')
           ->WHERE('filiere_school.school_id', '=', auth()->user()->school_id)
           ->get();
        }
        else{
            $getId = getIdLevel($type);
           $dts = sizeof($getId) == 2 ? Level::where('type_enseignement_id', $getId[0])->orWhere('type_enseignement_id', $getId[1])->get():Level::where('type_enseignement_id', $getId[0])->get();
        }
        return $dts;
    }


    /**
     * Remove the specified resource from storage.
     */
    protected function inscriptionStatistik($dts)
    {
        $tab = []; $primaire = 0; $cycle = 0;
        foreach ($dts->school->typeEnseignements as $item) {
            if(!$primaire && ($item['id'] == 1 || $item['id'] == 2)){
                $val = verifyPrimaire($dts->school->typeEnseignements);
                $tab[] = [
                    'total' => $this->nbreTotalStudent($val), 
                    'inscris' => $this->nbreInscris($val),
                    'nbreF'  => $this->nbreInscrisSelonSexe($val, 'F'),
                    'nbreM'  => $this->nbreInscrisSelonSexe($val, 'M'),
                    'libelle' => changeVal($val) 
                ];$primaire++;
            }
            elseif(!$cycle && ($item['id'] == 3 || $item['id'] == 4)){
                $val = verifySeconde($dts->school->typeEnseignements);
                $tab[] = [
                    'total' => $this->nbreTotalStudent($val),
                    'inscris' => $this->nbreInscris($val),
                    'nbreF'  => $this->nbreInscrisSelonSexe($val, 'F'),
                    'nbreM'  => $this->nbreInscrisSelonSexe($val, 'M'),
                    'libelle' => changeVal($val)
                ]; $cycle++;
            }
            elseif($item['id'] == 5){
                $val = $item['type_enseignement'];
                $tab[] =[
                    'total' => $this->nbreTotalStudent($val),
                    'inscris' => $this->nbreInscris($val),
                    'nbreF'  => $this->nbreInscrisSelonSexe($val, 'F'),
                    'nbreM'  => $this->nbreInscrisSelonSexe($val, 'M'),
                    'libelle' => $val
                ];
            }
            elseif($item['id'] == 6){
                $val = $item['type_enseignement'];
                $tab[] =[
                    'total' => $this->nbreTotalStudent($val),
                    'inscris' => $this->nbreInscris($val),
                    'nbreF'  => $this->nbreInscrisSelonSexe($val, 'F'),
                    'nbreM'  => $this->nbreInscrisSelonSexe($val, 'M'),
                    'libelle' => $val
                ];
            }
        }
        return $tab;
    }


    protected function nbreInscris($type)
    {
        $year = $this->yearActif();
        $nbre =  DB::table('classes')
        ->JOIN('inscriptions', 'classes.id', '=', 'inscriptions.classe_id')
        ->WHERE('inscriptions.school_id', '=', auth()->user()->school_id)
        ->WHERE('inscriptions.school_year_id', '=', $year->id)
        ->WHERE('classes.type', '=', changeVal($type))->count();

        return ($nbre <= 9 ? '0'.$nbre:$nbre);
    }


    protected function nbreTotalStudent($type)
    {
        $nbre =  Student::where('type', changeVal($type))->where('status', '1')->where('school_id', auth()->user()->school_id)->count();
        return ($nbre <= 9 ? '0'.$nbre:$nbre);
    }


    protected function nbreInscrisSelonSexe($type, $sexe)
    {
        $year = $this->yearActif();
        $nbre =  DB::table('students')
        ->JOIN('inscriptions', 'students.id', '=', 'inscriptions.student_id')
        ->WHERE('inscriptions.school_id', '=', auth()->user()->school_id)
        ->WHERE('inscriptions.school_year_id', '=', $year->id)
        ->WHERE('students.sexe', '=', $sexe)
        ->WHERE('students.type', '=', changeVal($type))->count();
        return ($nbre <= 9 ? '0'.$nbre:$nbre);
    }



    protected function verifyScolarity($type, $year, $level = null, $serie = null)
    {
        $count = Scolarity::where('type', $type)->where('niveau', '1')->where('serie', '1')->where('school_year_id', $year)->where('school_id', auth()->user()->school_id)->count();
        if(!$count){
            $count = Scolarity::where('type', $type)->where('level_id', $level)->where('serie_id', $serie)->where('school_year_id', $year)->where('school_id', auth()->user()->school_id)->count();
        }
        return $count;
    }


    protected function getSexe($id){
        $val = Student::find($id);
        return $val['sexe'];
    }


    protected function yearActif(){
        $year = SchoolYear::where('status', '1')->first();
        return $year;
    }
}