<?php

namespace App\Http\Controllers\API\v1;

use App\Http\Controllers\Controller;
use App\Http\Requests\BdCheckRequest;
use Exception;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Support\Facades\Config;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;

use Illuminate\Support\Facades\Mail;
use Illuminate\Support\Str;
use App\Mail\ForgotPwd;
use \Carbon\Carbon;

class MainController extends Controller
{
    public function ecole_departements(Request $request, $idEtablissement)
    {

        try {//


                   // DB::disconnect('tenant');
                   // Artisan::call('config:cache');
                   //changeEnv(['DB_DATABASE_SECOND'   => $request->get('School')['bd']])
                   //Config::set('database.connections.tenant.database', $request->get('School')['bd']);
                if(changeEnv(['database'   => $request->get('School')['bd']])){
                    //DB::purge('tenant');
                    //Artisan::call('config:clear');
                    // Do something
                    //myconf
                   // Config::set('switchDb.myconf.db', $request->get('School')['bd']);
                    return response()->json([
                        'success' =>  true,
                        'data' => DB::connection('tenant')->table('departement')->where('afficher', "YES")->get(),
                        'message' => 'DEPARTEMENTS_GOT_SUCCESSFULLY'
                    ], 200);
                } else {
                    return response()->json([
                        'message' => 'impossible de balancer la connection',
                    ]);
                }

            } catch (Exception $e) {
            return response()->json([
                'success' =>  false,
                'message' => $e->getMessage()
            ], 400);
        }
    }

    public function ecole_departements_list(Request $request)
    {

        try {//
                return response()->json([
                    'success' =>  true,
                    'data' => DB::connection('tenant')->table('departement')->where('afficher', "YES")->get(),
                    'message' => 'DEPARTEMENTS_GOT_SUCCESSFULLY'
                ], 200);

            } catch (Exception $e) {
            return response()->json([
                'success' =>  false,
                'message' => $e->getMessage()
            ], 400);
        }
    }

    public function departement_filieres(Request $request, $idDepartement)
    {

        try {

            if($idDepartement == 1000000 ) {
                $filieres = DB::connection('tenant')
                ->table('filieres')
                ->get();

            } else {
                $filieres = DB::connection('tenant')
                ->table('filieres')
                ->where('idDepartement', '=', $idDepartement)
                ->get();
            }

            return response()->json([
                'success' =>  true,
                'data' => $filieres,
                'message' => 'FILIERES_GOT_SUCCESSFULLY'
            ], 200);
        } catch (Exception $e) {
            return response()->json([
                'success' =>  false,
                'message' => $e->getMessage()
            ], 400);
        }
    }

    public function filiere_ues(BdCheckRequest $request, $idFiliere)
    {
        try {
            $ues = DB::connection('tenant')
                ->table('unites')
                ->where('idFiliere', '=', $idFiliere)
                ->get();

            return response()->json([
                'success' =>  true,
                'data' => $ues,
                'message' => 'UES_GOT_SUCCESSFULLY'
            ], 200);
        } catch (Exception $e) {
            return response()->json([
                'success' =>  false,
                'message' => $e->getMessage()
            ], 400);
        }
    }

    public function current_acad(BdCheckRequest $request) {
        try {
           $acad = DB::connection('tenant')->table('periodeinscription')->where('affiche', 'YES')->select('annee_acad')->first();
           $annee = $acad->annee_acad;
           return sendResponse($annee, "YEAR_GOT_SUCCESSFULLY");

       } catch (Exception $e) {
            return sendError("SERVER ERROR, PLEASE CONTACT ADMINISTRATOR", $e->getMessage(), 400);
       }
   }

    //filiere_ecues
    public function filiere_ecues(BdCheckRequest $request, $idFiliere)
    {
        try {
            $acad = DB::connection('tenant')->table('periodeinscription')->where('affiche', 'YES')->select('annee_acad')->first();
            $annee = $acad->annee_acad;
            $ues = DB::connection('tenant')->table('ecu')
                ->select('unites.*', 'ecu.*', 'unites.intituleUnite as co', 'unites.semestre as se')
                ->join('unites', function($join)
                {
                    $join->on('ecu.code_unite', '=', 'unites.code_unite');
                    $join->on('ecu.idFiliere','=','unites.idFiliere');
                    $join->on('ecu.annee','=','unites.annee');
                    $join->on('ecu.niveauFormation','=','unites.niveauFormation');
                    $join->on('ecu.annee_acad','=','unites.annee_acad');
                })
                ->where('ecu.idFiliere','=',$idFiliere)
                ->where('ecu.annee_acad', '=', $annee)
                ->orderBy('se')
                ->get()
                ->groupBy(['se', 'co']);

              //$ues = DB::connection('tenant')->table('unites')->get();

            return sendResponse($ues, "UES_GOT_SUCCESSFULLY");

        } catch (Exception $e) {
            return sendError("SERVER ERROR, PLEASE CONTACT ADMINISTRATOR", $e->getMessage(), 400);
        }
    }


    public function ue_ecus(BdCheckRequest $request, $code_unite)
    {
        try {
            $acad = DB::connection('tenant')->table('periodeinscription')->where('affiche', 'YES')->select('annee_acad')->first();
            $annee = $acad->annee_acad;
            $ue = DB::connection('tenant')
                ->table('unites')
                ->where('code_unite', '=', $code_unite)
                ->where('annee_acad', '=', $annee)
                ->first();

            $ecus = DB::connection('tenant')
                ->table('ecu')
                ->where('code_unite', '=', $ue->code_unite)
                ->where('idFiliere', '=', $ue->idFiliere)
                ->where('annee', '=', $ue->annee)
                ->where('niveauFormation', '=', $ue->niveauFormation)
                ->where('annee_acad', '=', $ue->annee_acad)
                ->get();

            return sendResponse($ecus, "ECUS_GOT_SUCCESSFULLY");

        } catch (Exception $e) {
            return sendError("SERVER ERROR, PLEASE CONTACT ADMINISTRATOR", $e->getMessage(), 400);
        }
    }

    // Prendre tous les etablissements

    public function etablissements()
    {

        try {

            $etablissements = DB::table('etablissement')
                ->select('*')
                ->get();

            return sendResponse($etablissements, "DATA_GOT_SUCCESSFULY");

        } catch (Exception $e) {

            return sendError("SERVER ERROR, PLEASE CONTACT ADMINISTRATOR", $e->getMessage(), 400);

        }
    }

    public function etablissement(Request $request, $idEtablissement)
    {
        try {
            return sendResponse(DB::table('etablissement')->where('idEtablissement', $idEtablissement)->first(), "ETABLISSEMENT_GOT_SUCCESSFULLY");
        } catch (Exception $e) {
            return sendError("SERVER ERROR, PLEASE CONTACT ADMINISTRATOR", $e->getMessage(), 400);
        }
    }

    public function departement(BdCheckRequest $request, $idDepartement)
    {
        try {
            return sendResponse(DB::connection('tenant')->table('departement')->where('idDepartement', $idDepartement)->first(), "DEPARTEMENT_GOT_SUCCESSFULLY");
        } catch (Exception $e) {
            return sendError("SERVER ERROR, PLEASE CONTACT ADMINISTRATOR", $e->getMessage(), 400);
        }
    }



    //Fonction pour récupérer les étudiants d'un département

    public function etudiants_departement(Request $request,$id){

        try {

            $request->validate([
                'bd' => ['required', 'min:2', 'string'],
            ]);


            $etudiants_departement = DB::connection('tenant')->table('filieres')
                ->where('idDepartement','=',$id)
                ->join('tabinscription','filieres.idFiliere','=','tabinscription.idFiliere')
                ->where(function ($query){
                    $query->where('tabinscription.annee_acad','=',date('Y', strtotime('+1 year')))
                          ->orWhere('tabinscription.annee_acad','=',date('Y'));
                })
                //->select('tabinscription.matricule')
                ->join('tabetudiant','tabinscription.matricule','=','tabetudiant.matricule')
                ->select('tabetudiant.*')
                ->get();

            if ($etudiants_departement){

                return response()->json([
                    'success' => true,
                    'data' => $etudiants_departement,
                    'message' => 'DATA_GOT_SUCCESSFULY'
                ])->withHeaders([
                    'Accept' => 'application/json',
                    'Content-Type' => 'application/json'
                ]);
            }
            else{
                return response()->json([
                    'success' => false,
                    //'data' => array(),
                    'message' => 'NO_DATA_FOUND_FOR_ETUDIANT'
                ])->withHeaders([
                    'Accept' => 'application/json',
                    'Content-Type' => 'application/json'
                ]);
            }

        } catch (Exception $e) {

            return response()->json([
                'success' => false,
                'message' => $e->getMessage()
            ], 400)->withHeaders([
                'Accept' => 'application/json',
                'Content-Type' => 'application/json'
            ]);
        }
    }

    public function students_count(Request $request,$id){
        try {

            $request->validate([
                'bd' => ['required', 'min:2', 'string'],
            ]);
            $nb_profs_departement = DB::connection('tenant')->table('enseignant')
                ->where('idDepartement','=',$id)
                ->get()
                ->count();
            //Obtenir le nombre d'etudiants d'un departement
            $nb_etudiants_departement = DB::connection('tenant')->table('filieres')
                ->where('filieres.idDepartement','=',$id)
                ->join('tabinscription','filieres.idFiliere','=','tabinscription.idFiliere')
                ->where(function ($query){
                    $acad = DB::connection('tenant')->table('periodeinscription')->where('affiche', 'YES')->select('annee_acad')->first();
                    $annee = $acad->annee_acad;
                    $query->where('tabinscription.annee_acad','=',$annee);
                })
                ->count();

            return response()->json([
                'success' => true,
                'data' => $nb_etudiants_departement,
                'message' => 'DATA_GOT_SUCCESSFULY'
            ])->withHeaders([
                'Accept' => 'application/json',
                'Content-Type' => 'application/json'
            ]);
        } catch (Exception $e) {

            return response()->json([
                'success' => false,
                'message' => $e->getMessage()
            ], 400)->withHeaders([
                'Accept' => 'application/json',
                'Content-Type' => 'application/json'
            ]);
        }
    }

    public function profs_count(Request $request,$id){
        try {

            $request->validate([
                'bd' => ['required', 'min:2', 'string'],
            ]);
            $nb_profs_departement = DB::connection('tenant')->table('enseignant')
                ->where('idDepartement','=',$id)
                ->get()
                ->count();

            return response()->json([
                'success' => true,
                'data' => $nb_profs_departement,
                'message' => 'DATA_GOT_SUCCESSFULY'
            ])->withHeaders([
                'Accept' => 'application/json',
                'Content-Type' => 'application/json'
            ]);
        } catch (Exception $e) {

            return response()->json([
                'success' => false,
                'message' => $e->getMessage()
            ], 400)->withHeaders([
                'Accept' => 'application/json',
                'Content-Type' => 'application/json'
            ]);
        }
    }


    public function profs_departement(Request $request,$id){
        try {

            $request->validate([
                'bd' => ['required', 'min:2', 'string'],
            ]);
            $profs_departement = DB::connection('tenant')->table('filieres')
                ->where('filieres.idDepartement','=',$id)
                ->join('ecu','filieres.idFiliere','=','ecu.idFiliere')
                ->where(function ($query,$annee){
                    $acad = DB::connection('tenant')->table('periodeinscription')->where('affiche', 'YES')->select('annee_acad')->first();
                    $annee = $acad->annee_acad;
                    $query->where('ecu.annee_acad','=',$annee);
                          //->orWhere('ecu.annee_acad','=',date('Y'));
                })
                ->join('enseignant','ecu.matricule','=','enseignant.matricule')
                ->select('enseignant.*')
                ->distinct()
                ->get();

            if($profs_departement){

                return response()->json([
                    'success' => true,
                    'data' => $profs_departement,
                    'message' => 'DATA_GOT_SUCCESSFULY'
                ])->withHeaders([
                    'Accept' => 'application/json',
                    'Content-Type' => 'application/json'
                ]);
            }
            else{

                return response()->json([
                    'success' => false,
                    //'data' => array(),
                    'message' => 'NO_DATA_FOUND_FOR_PROF'
                ])->withHeaders([
                    'Accept' => 'application/json',
                    'Content-Type' => 'application/json'
                ]);
            }

        } catch (Exception $e) {

            return response()->json([
                'success' => false,
                'message' => $e->getMessage()
            ], 400)->withHeaders([
                'Accept' => 'application/json',
                'Content-Type' => 'application/json'
            ]);
        }
    }



    public function stats_departement(Request $request,$id){

        try {

            $request->validate([
                'bd' => ['required', 'min:2', 'string'],
                //'data' => ['required','nullable']
            ]);

            /*if(changeEnv(['database'   => 'iitech_site_epac'])){
                // Do something
                $data = DB::connection('tenant')->table('theme_user')
                    ->select('user_matricule')
                    ->where('statut','=','PENDING')
                    ->orWhere('statut','=','ACTIVATED')
                    ->orWhere('statut','=','TAKEN')
                    ->get()
                    ->pluck('user_matricule')
                    ->toArray();
            }
             if(changeEnv(['database'   => 'iitech_epac'])){
            }*/

            //Obtenir le nombre de professeurs d'un departement
            /*$nb_profs_departement = DB::connection('tenant')->table('filieres')
                ->where('filieres.idDepartement','=',$id)
                ->join('ecu','filieres.idFiliere','=','ecu.idFiliere')
                ->where(function ($query){
                    $acad = DB::connection('tenant')->table('periodeinscription')->where('affiche', 'YES')->select('annee_acad')->first();
                    $annee = $acad->annee_acad;
                    $query->where('ecu.annee_acad','=',$annee);
                          //->orWhere('ecu.annee_acad','=',date('Y'));
                })
                ->join('enseignant','ecu.matricule','=','enseignant.matricule')
                ->select('enseignant.*')
                ->distinct()
                ->get()
                ->count();*/

            $nb_profs_departement = DB::connection('tenant')->table('enseignant')
                ->where('idDepartement','=',$id)
                ->get()
                ->count();

            //Obtenir le nombre d'etudiants d'un departement
            $nb_etudiants_departement = DB::connection('tenant')->table('filieres')
                ->where('filieres.idDepartement','=',$id)
                ->join('tabinscription','filieres.idFiliere','=','tabinscription.idFiliere')
                ->where(function ($query){
                    $acad = DB::connection('tenant')->table('periodeinscription')->where('affiche', 'YES')->select('annee_acad')->first();
                    $annee = $acad->annee_acad;
                    $query->where('tabinscription.annee_acad','=',$annee);
                          //->orWhere('tabinscription.annee_acad','=',date('Y'));
                })
                //->select('tabinscription.matricule')
                ->join('tabetudiant','tabinscription.matricule','=','tabetudiant.matricule')
                ->select('tabetudiant.*')
                ->count();


            //Obtenir le nombre d'etudiants n'ayant pas de theme par filiere
            $etudiants_sans_theme = DB::connection('tenant')->table('filieres')
                ->where('filieres.idDepartement','=',$id)
                ->join('tabinscription','filieres.idFiliere','=','tabinscription.idFiliere')
                ->where(function ($query){
                    $query->where([['tabinscription.annee','=',3],['tabinscription.niveauFormation','=',1]])
                          ->orWhere([['tabinscription.annee','=',2],['tabinscription.niveauFormation','=',2]])
                          ->orWhere([['tabinscription.annee','=',3],['tabinscription.niveauFormation','=',3]]);
                })
                ->select(DB::raw('tabinscription.idFiliere,count(*) as total'))
                ->whereNotIn('tabinscription.matricule',$request->data)
                ->groupBy('tabinscription.idFiliere')
                ->get();

            return response()->json([
                'success' => true,
                'data' => [
                    'nb_profs_departement' => $nb_profs_departement,
                    'nb_etudiants_departement' => $nb_etudiants_departement,
                    'nb_etudiants_sans_theme' => $etudiants_sans_theme
                ],
                'message' => 'DATA_GOT_SUCCESSFULY'
            ])->withHeaders([
                'Accept' => 'application/json',
                'Content-Type' => 'application/json'
            ]);

        } catch (Exception $e) {

            return response()->json([
                'success' => false,
                'message' => $e->getMessage()
            ], 400)->withHeaders([
                'Accept' => 'application/json',
                'Content-Type' => 'application/json'
            ]);
        }
    }


    public function prof_ecues(Request $request,$matricule){
        try {

            $annee = current_acad();

            $ecues = DB::connection('tenant')->table('filieres')
                ->join('ecu','ecu.idFiliere','=','filieres.idFiliere')
                ->where('ecu.annee_acad','=',$annee)
                ->join('departement','filieres.idDepartement','=','departement.idDepartement')
                ->join('enseignant','ecu.matricule','=','enseignant.matricule')
                ->where('enseignant.matricule','=',$matricule)
                ->orderBy('ecu.niveauFormation')
                ->orderBy('ecu.annee')
                ->orderBy('ecu.intituleEcu')
                ->select(
                    'ecu.idEC as id_EC','ecu.code_ecu', 'ecu.intituleEcu', 'ecu.niveauFormation', 'ecu.annee', 'ecu.masse_horaire', 'ecu.matricule',
                    'filieres.intituleFilieres', 'filieres.sigleFilieres', 'departement.sigledept', 'departement.desrdept'
                )
                ->distinct()
                ->get();

            if($ecues){
                $ecues = json_decode(json_encode($ecues), false);  //transform to array

                //get total of hours done by the prof
                $ids_ecus = array_column($ecues,'id_EC');

                $hours_ecus = DB::connection('tenant')
                    ->table('gestbook_enregistrements')
                    ->select('ecu as ecu_id','Heure_arrivee','Heure_fin')
                    ->whereIn('ecu',$ids_ecus)
                    ->get()
                    ->groupBy('ecu_id');

                foreach ($hours_ecus as $ecu_id => $value) {
                    $hours = 0;
                    foreach ($value as $tab_hours) {
                        $hour = abs(strtotime($tab_hours->Heure_fin) - strtotime($tab_hours->Heure_arrivee))/(60*60);
                        $hours += $hour;
                    }
                    $index = array_search($ecu_id,$ids_ecus);

                    if($index !== false){ // cas impossible mais pr prévenir des erreurs ...
                        $ecues[$index]->Heures_effectuees = $hours;
                    }
                }

                return sendResponse($ecues, "DATA_GOT_SUCCESSFULY");
            }
            else{

                return sendError("NO_DATA_FOUND_FOR_PROF", [], 400);
            }

        } catch (Exception $e) {

            return sendError("SERVER ERROR, PLEASE CONTACT ADMINISTRATOR", $e->getMessage(), 400);
        }
    }

    public function ecue_chapitres(Request $request,$ecuId){
        try {

            $chapitres = DB::connection('tenant')->table('ecu')
            ->where('ecu.idEC','=',$ecuId)
            ->join('chapitre','ecu.idEC','=','chapitre.idecu')
            ->orderBy('chapitre.chapitrenumero', "asc")
            ->select('chapitre.idchapitre as id_chapitre','chapitre.idecu as id_ecu','chapitre.chapitreintitule','chapitre.chapitrenumero','chapitre.validite','chapitre.chapitreEncours','chapitre.dateinsertion','chapitre.annee_acad')
            ->get();

            // $chapitres = DB::connection('tenant')->table('chapitre')
            //     //->select('chapitre.*')
            //     ->join('ecu','chapitre.idecu','=','ecu.idEC')
            //     ->where('ecu.idEC','=',$ecuId)
            //     ->orderBy('chapitre.chapitrenumero', "Asc")
            //     ->select('chapitre.idchapitre as id_chapitre','chapitre.idecu as id_ecu','chapitre.chapitreintitule','chapitre.chapitrenumero','chapitre.validite','chapitre.chapitreEncours','chapitre.dateinsertion','chapitre.annee_acad')
            //     // ->join('sections','chapitres.idChap','=','sections.idChap')
            //     // ->select('ecu.*', 'filieres.intituleFilieres', 'filieres.sigleFilieres', 'departement.sigledept', 'departement.desrdept')
            //     // ->distinct()
            //     ->get();

            if($chapitres){
                foreach ($chapitres as $i => $chap) {
                    $sections = DB::connection('tenant')->table('gestbook_sectionchapitre')
                    ->where('chapitre_id','=',$chap->id_chapitre)
                    ->select('idsectionchapitre as id_sectionchapitre','intitule','numero','validite','chapitre_id','date','annee_acad')
                    ->get();

                    $chapitres[$i]->sections = $sections;
                }

                return sendResponse($chapitres, "DATA_GOT_SUCCESSFULY");
            } else{
                return sendError("NO_DATA_FOUND_FOR_PROF", [], 400);
            }

        } catch (Exception $e) {

            return sendError("SERVER ERROR, PLEASE CONTACT ADMINISTRATOR", $e->getMessage(), 400);
        }
    }


    // Added by caca to handle software api checking

    public function check_auth_client(Request $request) {

    	try {

    	    $check_client_key = DB::table('customer_software')->where('client_key', client_secure_me($request->secret_key, false))->first();

            if ($check_client_key) {
                $check_key = DB::table('softwares')->where('id', $check_client_key->software_id)->first();

                if ($check_key) {
                    $uris = $check_key->redirect_uri;
                    //
                    $uris = json_decode($uris, true);
                    //return sendError("YOU ARE NOT AUTHORIZED TO ACCESS WITH THIS URI",str_replace( '\\', '',$request->redirect_uri));
                    foreach ($uris as $key => $uri) {
                        if (str_replace( '\\', '',$uri) == str_replace( '\\', '',str_replace( 'https://', '',str_replace( 'http://', '',$request->redirect_uri)))) {
                            return sendResponse($check_key, "DATA_GOT_SUCCESSFULLY");
                        }
                    }
                    return sendError("YOU ARE NOT AUTHORIZED TO ACCESS WITH THIS URI");
                }

            }

            return sendError("YOU ARE NOT AUTHORIZED TO ACCESS");

      } catch (Exception $e) {
            return sendError("SERVER ERROR, PLEASE CONTACT ADMINISTRATOR", $e->getMessage(), 400);
      }

    }


    // Data on ecu

    public function ecus_data(Request $request){
        try {

            $ecus_id_array = $request->ecus_id_array;

            $ecus_id_array = is_array($ecus_id_array) ? $ecus_id_array : [$ecus_id_array] ;

            $ecus_data = DB::connection('tenant')
                ->table('ecu')
                ->select('*')
                ->whereIn('idEC',$ecus_id_array)
                ->get();

            return sendResponse($ecus_data, "DATA_GOT_SUCCESSFULY");

        } catch (Exception $e) {
            return sendError("SERVER ERROR, PLEASE CONTACT ADMINISTRATOR", $e->getMessage(), 400);
        }
    }

    // Data on ecu with a groupBy on idEC

    public function ecus_infos(Request $request){
        try {
            $annee_acad = current_acad();
            $ecus_ids = array_values($request->ecuIds);

            $ecus_data = DB::connection('tenant')
            ->table('ecu')
            ->whereIn('idEC',$ecus_ids)
            ->where('ecu.annee_acad', $annee_acad)
            ->join('filieres','ecu.idFiliere','=','filieres.idFiliere')
            ->join('gestbook_offre_formation_info_ecu','gestbook_offre_formation_info_ecu.ecu','=','ecu.idEC')
            ->select(
                'filieres.idFiliere as id_Filiere','filieres.idDepartement as id_Departement','filieres.idMention as id_Mention','filieres.intituleFilieres','filieres.sigleFilieres','filieres.conditionInscription','filieres.objectif_formation',
                // 'ecu.idFiliere as id_Filiere',
                'ecu.idEC as id_EC','ecu.code_ecu','ecu.code_unite','ecu.intituleEcu','ecu.credit','ecu.insertion','ecu.rattrapage','ecu.annee','ecu.niveauFormation','ecu.annee_acad','ecu.matricule','ecu.masse_horaire',

                'gestbook_offre_formation_info_ecu.teaching_method as methode',
                'gestbook_offre_formation_info_ecu.learning_purpose as apprentissage',
                'gestbook_offre_formation_info_ecu.bibliography as reference',
                'gestbook_offre_formation_info_ecu.webography',
                'gestbook_offre_formation_info_ecu.evaluation_mode  as mode_compo'
            )
            ->get()
            ->groupBy('id_EC');

            $hours_ecus = DB::connection('tenant')
            ->table('gestbook_enregistrements')
            ->whereIn('ecu',$ecus_ids)
            ->select('ecu','Heure_arrivee','Heure_fin')
            ->get()
            ->groupBy('ecu');

            foreach ($hours_ecus as $ecu_id => $value) {
                $hours = 0;

                foreach ($value as $tab_hours) {
                    $hour = abs(strtotime($tab_hours->Heure_fin) - strtotime($tab_hours->Heure_arrivee))/(60*60);
                    $hours += $hour;
                }

                $ecus_data[$ecu_id][0]->Heures_effectuees = $hours;
            }

            $ecus_data = encryptKeys($ecus_data);

            return sendResponse($ecus_data, "DATA_GOT_SUCCESSFULY");

        } catch (Exception $e) {
            return sendError("SERVER ERROR, PLEASE CONTACT ADMINISTRATOR", $e->getMessage(), 400);
        }
    }

    // Students who have ecu

    public function students_ecu($ecuId){

        try {

            $datas = DB::connection('tenant')
                ->table('ecu')
                ->where('idEC',$ecuId)
                ->join('tabinscriunite', function($join)
                {
                    $join->on('ecu.code_unite', '=', 'tabinscriunite.code_unite');
                    $join->on('ecu.idFiliere','=','tabinscriunite.idFiliere');
                    $join->on('ecu.annee_acad','=','tabinscriunite.inscription_actuelle');
                    $join->on('ecu.annee','=','tabinscriunite.annee');
                    $join->on('ecu.niveauFormation','=','tabinscriunite.niveauFormation');
                })
                ->select('premiere_inscription','inscription_actuelle','tabinscriunite.matricule as matricule_etudiant')
                ->get();

            $matricules_normal = [];
            $matricules_reprise = [];

            foreach($datas as $data){

                if($data->premiere_inscription == $data->inscription_actuelle){

                    array_push($matricules_normal,$data->matricule_etudiant);
                }
                else{

                    array_push($matricules_reprise,$data->matricule_etudiant);
                }
            }

            $students_normal = DB::connection('tenant')
                ->table('tabetudiant')
                ->select('*')
                ->whereIn('matricule',$matricules_normal)
                ->get();

            $students_reprise = DB::connection('tenant')
                ->table('tabetudiant')
                ->select('*')
                ->whereIn('matricule',$matricules_reprise)
                ->get();

            $prof = DB::connection('tenant')
                ->table('ecu')
                ->where('idEC',$ecuId)
                ->join('enseignant','ecu.matricule','=','enseignant.matricule')
                ->select('ecu.matricule','nom','prenom')
                ->get();

            $success = ['prof' => $prof,
                        'students_normal' => $students_normal,
                        'students_reprise' => $students_reprise
                        ];
            return sendResponse($success, "DATA_GOT_SUCCESSFULY");

        } catch (Exception $e) {
            return sendError("SERVER ERROR, PLEASE CONTACT ADMINISTRATOR", $e->getMessage(), 400);
        }

    }

    public function chapitres_data(Request $request){

        try {

            $toTreat = [];

            foreach ($request->chapitres as $key => $value) {
                array_push($toTreat,$value);
            }

            $data = DB::connection('tenant')
                ->table('chapitre')
                ->whereIn('idchapitre',$toTreat)
                //->join('gestbook_sectionchapitre','chapitre.idchapitre','gestbook_sectionchapitre.chapitre_id')
                ->orderBy('chapitre.chapitrenumero', "Asc")
                ->select('idchapitre as id_chapitre','idecu as id_ecu','chapitreintitule','chapitrenumero','validite','chapitreEncours','dateinsertion','annee_acad')
                ->get()
                ->groupBy('id_chapitre');

            $data = encryptKeys($data);

            return sendResponse($data, "DATA_GOT_SUCCESSFULY");

        } catch (Exception $e) {
            return sendError("SERVER ERROR, PLEASE CONTACT ADMINISTRATOR", $e->getMessage(), 400);
        }


    }

    public function chapitre_quizs(Request $request,$chap_id)
    {
        try {

            $qcms_ids = DB::connection('tenant')
                ->table('question')
                ->where('idchapitre',$chap_id)
                ->get()->pluck('idquestion')->toArray();

            shuffle($qcms_ids);

            $qcms_ids = array_slice($qcms_ids, 0, 10);

            $qcms = DB::connection('tenant')
                ->table('question')
                ->whereIn('idquestion',$qcms_ids)
                //->join('reponses_qcms','reponses_qcms.qcm_id','=','qcms.id_qcm')
                ->get();

            $reponses = DB::connection('tenant')
                ->table('propositionreponse')
                ->whereIn('idquestion',$qcms_ids)
                ->get()
                ->groupBy('idquestion');

            // $reponses = encryptKeys($reponses);

            $success = ['qcms' => $qcms,'reponses' => $reponses];
            return sendResponse($success, "DATA_GOT_SUCCESSFULY");
        } catch (Exception $e) {
            return sendError("SERVER ERROR, PLEASE CONTACT ADMINISTRATOR", $e->getMessage(), 400);
        }
    }

    public function register(Request $request)
    {
        try {

            // Student case

            $user = DB::connection('tenant')->table('tabetudiant')
                ->where('matricule',$request->matri)
                ->where('date_nais',$request->datenaiss)
                ->first();

            if(!empty($user)){
                $user_v = DB::table('users_student')->where('matricule',$request->matri)->first();
                if(empty($user_v)){
                    DB::table('users_student')->insert([
                        'username' => $request->login,
                        'password' => Hash::make($request->motpass1),
                        'email' => $request->adresse,
                        'matricule' => $request->matri,
                        'phone' => $request->phone,
                        'idEtablissement' => $request->idEtablissement
                    ]);
                    return sendResponse([], "Inscription réussie.");
                }
                return sendResponse([], "Vous etes déjà inscrit.");
            }else{

                // Teacher case

                $user = DB::table('geststudent_r')
                    ->where('matricule',$request->matri)
                    ->where('date_nais',$request->datenaiss)
                    ->first();

                if(!empty($user)){

                    if($user->estAjour == 'NO'){
                        // return sendError("Votre inscription au sein de la plateforme n'est pas à jour. Veuillez contacter les administrateurs.");
                        return response()->json(['success' => false,'data' => [],
                            'message' => "Votre inscription au sein de la plateforme n'est pas à jour. Veuillez contacter les administrateurs."],400)
                            ->withHeaders(['Accept' => 'application/json','Content-Type' => 'application/json']);
                    }

                    $user_v = DB::table('users')->where('matricule',$request->matri)->first();
                    if(empty($user_v)){
                        DB::table('users')->insert([
                            'username' => $request->login,
                            'password' => Hash::make($request->motpass1),
                            'email' => $request->adresse,
                            'matricule' => $request->matri,
                            'phone' => $request->phone,
                            'type_user' => 4,
                        ]);
                        return sendResponse([], "Inscription réussie.");
                    }
                    return sendResponse([], "Vous etes déjà inscrit.");
                }else{
                    // return sendError("Vous n'etes pas autorisé(e) à vous inscrire sur la plateforme. Veuillez contacter les administrateurs.");
                    return response()->json(['success' => false,'data' => [],
                        'message' => "Vous n'etes pas autorisé(e) à vous inscrire sur la plateforme. Veuillez contacter les administrateurs."],400)
                        ->withHeaders(['Accept' => 'application/json','Content-Type' => 'application/json']);
                }

            }

        } catch (Exception $e) {
            // return sendError("Une erreur innattendue est survenue.", $e->getMessage(), 400);
            return response()->json(['success' => false,'data' => [],
                'message' => "Une erreur innattendue est survenue."],400)
                ->withHeaders(['Accept' => 'application/json','Content-Type' => 'application/json']);
        }
    }

    public function forgot_pwd(Request $request)
    {
        try {

            // Student case
            $user_v = DB::table('users_student')->where('email',$request->email)->first();

            if(empty($user_v)){
                // Teacher case
                $user_v = DB::table('users')->where('email',$request->email)->first();
            }

            if(empty($user_v)){
                // return sendError("Cette adresse email n'existe pas au sein de la plateforme.");
                return response()->json(['success' => false,'data' => [],
                    'message' => "Cette adresse email n'existe pas au sein de la plateforme."],400)
                    ->withHeaders(['Accept' => 'application/json','Content-Type' => 'application/json']);
            }else{
                $token = Str::random(10);

                DB::table('password_resets')->insert([
                    'email' => $request->email,
                    'token' => $token,
                    'created_at' => Carbon::now(),
                    'updated_at' => Carbon::now(),
                ]);

                $code = $token;

                Mail::to($user_v->email)->send(new ForgotPwd($code));

                // if($user_v->type_user != 0) {

                    $params = array(
                        'token' => 'zpdi4b58vfbxy785',
                        'to' => '+229' . $user_v->phone,
                        'body' => "*Réinitialisation de mot de passe sur la plateforme AfricaWebStudent* \n \n \n".
                            "Bonjour. \n".
                            "Une demande de réinitialisation de mot de passe a été reçue pour votre compte AfricaWebStudent. \n".
                            "Votre code de réinitialisation est : *" . $code . "*. \n"
                    );

                    $curl = curl_init();
                    curl_setopt_array($curl, array(
                        CURLOPT_URL => "https://api.ultramsg.com/instance33911/messages/chat",
                        CURLOPT_RETURNTRANSFER => true,
                        CURLOPT_ENCODING => "",
                        CURLOPT_MAXREDIRS => 10,
                        CURLOPT_TIMEOUT => 30,
                        CURLOPT_SSL_VERIFYHOST => 0,
                        CURLOPT_SSL_VERIFYPEER => 0,
                        CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
                        CURLOPT_CUSTOMREQUEST => "POST",
                        CURLOPT_POSTFIELDS => http_build_query($params),
                        CURLOPT_HTTPHEADER => array(
                        "content-type: application/x-www-form-urlencoded"
                        ),
                    ));
                    $response = curl_exec($curl);
                    $err = curl_error($curl);
                    curl_close($curl);
                // }

                return sendResponse([],'Mail sent successfully.');
            }

        } catch (Exception $e) {
            // return sendError("Une erreur innattendue est survenue.", $e->getMessage(), 400);
            return response()->json(['success' => false,'data' => [],
                'message' => "Une erreur innattendue est survenue."],400)
                ->withHeaders(['Accept' => 'application/json','Content-Type' => 'application/json']);
        }
    }

    public function reset_pwd(Request $request)
    {
        try {
            $result = DB::table('password_resets')->where(['email' => $request->email])->first();
            if(!$result){
                // return sendError("Adresse email invalide pour la réinitialisation de mot de passe.");
                return response()->json(['success' => false,'data' => [],
                    'message' => "Adresse email invalide pour la réinitialisation de mot de passe."],400)
                    ->withHeaders(['Accept' => 'application/json','Content-Type' => 'application/json']);
            }

            $result = DB::table('password_resets')
                ->where([
                'email' => $request->email,
                'token' => $request->code
                ])->first();

            if(!$result){
                // return sendError("Code invalide.");
                return response()->json(['success' => false,'data' => [],
                    'message' => "Code invalide."],400)
                    ->withHeaders(['Accept' => 'application/json','Content-Type' => 'application/json']);
            }

            // Update history stuff

            $user_v = DB::table('users_student')->where('email',$request->email)->first();

            date_default_timezone_set('Africa/Porto-Novo');
            $updated_at = date('Y-m-d H:i:s', time());

            if(!empty($user_v)){
                $matricule = DB::table('users_student')->where('email', $request->email)->first()->matricule;
            }else{
                $matricule = DB::table('users')->where('email', $request->email)->first()->matricule;
            }

            $identique = DB::table('validepassword')->first()->identique;

            $sql0 = DB::table('psswrdHistoryupdate')->where('matricule', $matricule)->get();
            if(count($sql0) > 0){

                $retour = "";
                $result = false;
                $now   = time();
                foreach ($sql0 as $value) {
                    $password  = $value->password;
                    $update_at = $value->update_at;
                    $retour = $this->dateDiff($now, strtotime($update_at));
                    $day    = $retour['day'];
                    $hour   = $retour['hour'];
                    $minute = $retour['minute'];

                    if($day < $identique && password_verify($request->password, $password)) {
                        $result = true;
                    }
                }
                if($result) {
                    // return sendError('MOT DE PASSE REJETE PAR LE SYSTEME. VEUILLEZ CHOISIR UN AUTRE');
                    return response()->json(['success' => false,'data' => [],
                    'message' => "MOT DE PASSE REJETE PAR LE SYSTEME. VEUILLEZ CHOISIR UN AUTRE"],400)
                    ->withHeaders(['Accept' => 'application/json','Content-Type' => 'application/json']);
                }else {
                    // Delete the line in password_resets table
                    DB::table('password_resets')
                        ->where([
                        'email' => $request->email,
                        'token' => $request->code
                        ])->delete();

                    DB::table('psswrdHistoryupdate')->insert([
                        'matricule' => $matricule,
                        'password' => $this->encryptnew($request->password),
                        'update_at' => $updated_at
                    ]);

                    if(!empty($user_v)){
                        DB::table('users_student')->where('email', $request->email)
                        ->update([
                            'password' => $this->encryptnew($request->password),
                            'updated_at' => $updated_at
                        ]);
                    }else{
                        DB::table('users')->where('email', $request->email)
                        ->update([
                            'password' => $this->encryptnew($request->password),
                            'updated_at' => $updated_at
                        ]);
                    }
                }
            }else{
                // Delete the line in password_resets table
                DB::table('password_resets')
                    ->where([
                    'email' => $request->email,
                    'token' => $request->code
                    ])->delete();

                DB::table('psswrdHistoryupdate')->insert([
                    'matricule' => $matricule,
                    'password' => $this->encryptnew($request->password),
                    'update_at' => $updated_at
                ]);

                if(!empty($user_v)){
                    DB::table('users_student')->where('email', $request->email)
                    ->update([
                        'password' => $this->encryptnew($request->password),
                        'updated_at' => $updated_at
                    ]);
                }else{
                    DB::table('users')->where('email', $request->email)
                    ->update([
                        'password' => $this->encryptnew($request->password),
                        'updated_at' => $updated_at
                    ]);
                }
            }

            return sendResponse([],'Votre mot de passe a été réinitialisé avec succès.');

        } catch (Exception $e) {
            // return sendError("Une erreur innattendue est survenue.", $e->getMessage(), 400);
            return response()->json(['success' => false,'data' => [],
            'message' => "Une erreur innattendue est survenue."],400)
            ->withHeaders(['Accept' => 'application/json','Content-Type' => 'application/json']);
        }
    }

    protected function dateDiff($date1, $date2)
    {
        $diff = ($date1 - $date2); // abs pour avoir la valeur absolute, ainsi éviter d'avoir une différence négative
        $retour = array();

        $tmp = $diff;
        $retour['second'] = $tmp % 60;

        $tmp = floor( ($tmp - $retour['second']) /60 );
        $retour['minute'] = $tmp % 60;

        $tmp = floor( ($tmp - $retour['minute'])/60 );
        $retour['hour'] = $tmp % 24;

        $tmp = floor( ($tmp - $retour['hour'])  /24 );
        $retour['day'] = $tmp;

        return $retour;
    }

    protected function encryptnew($plaintext_password) {
    	if(!empty($plaintext_password)) {
            $hash = password_hash($plaintext_password, PASSWORD_DEFAULT);
            return $hash;
    	}
    }

}
