<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use Illuminate\Pagination\Paginator;
use Illuminate\Support\Facades\Http;
use Illuminate\Support\Facades\Request;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Pagination\LengthAwarePaginator;


class DashboardController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function listVehicles()
    {
        // ENV variables
        $manager_id = env('MANAGER_ID');
        $B2L = env('B2L');
        $PVO = env('PVO');
        $DASHBOARD_DEBUG = env('DASHBOARD_DEBUG');
        
         
        // DATE variables
        date_default_timezone_set('Europe/Paris');
        $datetime = strtotime('-' . env('DATE') . ' days');
        $date_prise_en_compte = date('Y-m-d H:i:s', $datetime);
        $datetime1 = strtotime('-1 days');
        $newdate = date('Y-m-d H:i:s', $datetime1);

        // dd($hourNow);

        // Query
        $list = DB::connection('mysql2')
        ->table("vehicules")
        ->Join("users", "vehicules.client_id", "=", "users.id")
        ->select(
            "accepted",
            "vehicules.id",
            "vehicules.name",
            "manager_id",
            "client_id",
            "users.id_bee2link",
            "id_client_bee2link",
            "statut_bee2link",
            "bee2link_sent",
            "users.id_pvo",
            "id_client_pvo",
            "vehicules.pvo_sent",
            "operator_id",
            "mail_sent_operator",
            "vehicules.created_at",
            "vehicules.updated_at"
        )
        ->where(function($query){
            $query->where("accepted", "=", 1)
                  ->where("vehicules.deleted_at", "=", NULL)
                  ->where("mail_sent_operator", "=", NULL);
                  
        })
        ->where(function($query){
            $query->where("pvo_sent", "=", NULL)
                  ->orWhere("bee2link_sent", "=", NULL);
        })
        ->where(function($query){
            $query->where("users.id_pvo", "!=", NULL)
                  ->orWhere("users.id_bee2link", "!=", NULL)
                  ->orWhere("vehicules.id_client_pvo", "!=", NULL)
                  ->orWhere("vehicules.id_client_bee2link", "!=", NULL);
        })
        ->where("vehicules.updated_at", ">=", $date_prise_en_compte)
        ->orderBy("vehicules.updated_at", "desc");

         
        // Condition d'affichage en fonction du manager ID
        if($manager_id !== NULL)
        {
            $vehicles = $list->where('manager_id', '=', $manager_id)->get();
            //$vehicles = $list->where('manager_id', '=', '623')->get();
        }
        else
        {
           $vehicles = $list->get();
        }

        // Tri de la 1ere query
        $trash = [];
        $filter_list = [];
        $list_vehicles = [];

        for($i = 0; $i < count($vehicles); $i++){
                array_push($filter_list, $vehicles[$i]);
        }
        for($i = 0; $i < count($filter_list); $i++){
            if(($filter_list[$i]->id_bee2link === NULL && $filter_list[$i]->id_client_bee2link === NULL) && ($filter_list[$i]->pvo_sent !== NULL)){
                array_push($trash, $filter_list[$i]);
            }
            elseif(($filter_list[$i]->id_pvo === NULL && $filter_list[$i]->id_client_pvo === NULL) && ($filter_list[$i]->bee2link_sent !== NULL)){
                array_push($trash, $filter_list[$i]);
            }
            else{
                array_push($list_vehicles, $filter_list[$i]);
            }
        }
        $vehicles = $this->paginate($list_vehicles);

        // Query afin de recuperer des informations supplémentaires
        $users = DB::connection('mysql2')
        ->table('users')
        ->select('id', 'entreprise', 'name','email','tel1')
        ->orderBy('updated_at', 'desc')
        ->get();

        // Recuperer le nombre de véhicules PVO/B2L non remontés
        $pvo_not_sent = 0;
        $bee2link_not_sent = 0;
        foreach($list_vehicles as $count){
            if($count->pvo_sent === NULL && ($count->id_pvo != NULL || $count->id_client_pvo != NULL)){
                $pvo_not_sent++;
            }
            if($count->bee2link_sent === NULL && ($count->id_bee2link != NULL || $count->id_client_bee2link != NULL)){
                $bee2link_not_sent++;
            }
        }

        // Query afin de recuperer la date du dernier véhicule remonté chez PVO
        $last_vehicle = DB::connection('mysql2')
            ->table('vehicules')
            ->select('pvo_sent_at','name')
            ->where('pvo_sent_at', '!=', NULL)
            ->where(function ($query) {
                $query->where('pvo_sent', '=', 1)
                    ->where('id_pvo', '!=', NULL)
                    ->orWhere('id_client_pvo', '!=', NULL);
                })
            ->orderBy('pvo_sent_at', 'DESC')
            ->limit(1)
            ->get();

        // Vérification du dernier véhicule non-remonté chez PVO //
        $dateNow = date("d-m-Y");
        $hourNow = date_create(date("H:i:s"));
        $alert = NULL;
        $lastVehicleNotSendToPvo = NULL;

        for($i = 0; $i < count($list_vehicles); $i++){
            if(($list_vehicles[$i]->id_pvo != NULL OR $list_vehicles[$i]->id_client_pvo != NULL) && $list_vehicles[$i]->pvo_sent === NULL){
                $lastVehicleNotSendToPvo = $list_vehicles[$i];
                break;
            }
        }

       
    //      print_r($lastVehicleNotSendToPvo) ;
      
      // echo "=>".$lastVehicleNotSendToPvo->updated_at ; 
      // exit ; 
       
     //  $dateLastVehicule = date('d-m-Y', strtotime($lastVehicleNotSendToPvo->updated_at));
       
       
     //  $hourLastVehicule = date_create(date('H:i:s', strtotime($lastVehicleNotSendToPvo->updated_at)));
       
       if ($lastVehicleNotSendToPvo !== NULL) {
    $dateLastVehicule = date('d-m-Y', strtotime($lastVehicleNotSendToPvo->updated_at));
    $hourLastVehicule = date_create(date('H:i:s', strtotime($lastVehicleNotSendToPvo->updated_at)));
    } else {
    $dateLastVehicule = NULL;
    $hourLastVehicule = NULL;
    }


        if($dateNow === $dateLastVehicule){

            $difference = date_diff($hourNow, $hourLastVehicule);
            $negative = "-";
            $sign = $difference->format('%R');
            $minutes = $difference->h * 60;
            $minutes += $difference->i;

            if($sign === $negative){
                if($minutes >= 30){
                    $alert = "alerte_orange";
                }
                if($minutes >= 45){
                    $alert = "alerte_rouge";
                }
                if($minutes >= 60){
                    $alert = "alerte_rouge_modal";
                }
            }
        }
       
       
        // exit ; 
        return view('dashboardVehicles', compact('vehicles', 'users', 'pvo_not_sent', 'bee2link_not_sent', 'last_vehicle', 'date_prise_en_compte', 'PVO', 'B2L', 'DASHBOARD_DEBUG', 'lastVehicleNotSendToPvo', 'alert'));
      
    }

    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function listVehicles2()
    {
        // ENV variables
        $manager_id = env('MANAGER_ID');
        $B2L = env('B2L');
        $PVO = env('PVO');
        $DASHBOARD_DEBUG = env('DASHBOARD_DEBUG');

        // DATE variables
        date_default_timezone_set('Europe/Paris');
        $datetime = strtotime('-' . env('DATE') . ' days');
        $date_prise_en_compte = date('Y-m-d H:i:s', $datetime);
        $datetime1 = strtotime('-1 days');
        $newdate = date('Y-m-d H:i:s', $datetime1);

        // Query
        $list = DB::connection('mysql2')
        ->table("vehicules")
        ->Join("users", "vehicules.client_id", "=", "users.id")
        ->select(
            "accepted",
            "vehicules.name",
            "manager_id",
            "client_id",
            "users.id_bee2link",
            "id_client_bee2link",
            "statut_bee2link",
            "bee2link_sent",
            "users.id_pvo",
            "id_client_pvo",
            "vehicules.pvo_sent",
            "operator_id",
            "mail_sent_operator",
            "vehicules.created_at",
            "vehicules.updated_at"
        )
        ->where(function($query){
            $query->where("accepted", "=", 1)
                  ->where("vehicules.deleted_at", "=", NULL);
        })
        ->where(function($query){
            $query->where("pvo_sent", "=", NULL)
                  ->orWhere("bee2link_sent", "=", NULL);
        })
        ->where(function($query){
            $query->where("users.id_pvo", "!=", NULL)
                  ->orWhere("users.id_bee2link", "!=", NULL)
                  ->orWhere("vehicules.id_client_pvo", "!=", NULL)
                  ->orWhere("vehicules.id_client_bee2link", "!=", NULL);
        })
        ->where(function($query){
            $datetime1 = strtotime('-1 days');
            $newdate = date('Y-m-d H:i:s', $datetime1);
            $query->where("mail_sent_operator", "=", NULL)
                  ->orWhere("vehicules.updated_at", ">=", $newdate);
        })
        ->where("vehicules.updated_at", ">=", $date_prise_en_compte)
        ->orderBy("vehicules.updated_at", "desc");

        // Condition d'affichage en fonction du manager ID
        if($manager_id !== NULL)
        {
            $vehicles = $list->where('manager_id', '=', $manager_id)->get();
        }
        else
        {
           $vehicles = $list->get();
        }

        // Tri de la 1ere query
        $trash = [];
        $filter_list = [];
        $list_vehicles = [];

        for($i = 0; $i < count($vehicles); $i++){
                array_push($filter_list, $vehicles[$i]);
        }
        for($i = 0; $i < count($filter_list); $i++){
            if(($filter_list[$i]->id_bee2link === NULL && $filter_list[$i]->id_client_bee2link === NULL) && ($filter_list[$i]->pvo_sent !== NULL)){
                array_push($trash, $filter_list[$i]);
            }
            elseif(($filter_list[$i]->id_pvo === NULL && $filter_list[$i]->id_client_pvo === NULL) && ($filter_list[$i]->bee2link_sent !== NULL)){
                array_push($trash, $filter_list[$i]);
            }
            else{
                array_push($list_vehicles, $filter_list[$i]);
            }
        }
        $vehicles = $this->paginate($list_vehicles);

        // Query afin de recuperer des informations supplémentaires
        $users = DB::connection('mysql2')
        ->table('users')
        ->select('id', 'entreprise', 'name','email','tel1')
        ->orderBy('updated_at', 'desc')
        ->get();

        // Recuperer le nombre de véhicules PVO/B2L non remontés
        $pvo_not_sent = 0;
        $bee2link_not_sent = 0;

        foreach($list_vehicles as $count){
            if($count->pvo_sent === NULL && ($count->id_pvo != NULL || $count->id_client_pvo != NULL)){
                $pvo_not_sent++;
            }
            if($count->bee2link_sent === NULL && ($count->id_bee2link != NULL || $count->id_client_bee2link != NULL)){
                $bee2link_not_sent++;
            }
        }

        // Query afin de recuperer la date du dernier véhicule remonté chez PVO
        $last_vehicle = DB::connection('mysql2')
            ->table('vehicules')
            ->select('pvo_sent_at','name')
            ->where('pvo_sent_at', '!=', NULL)
            ->where(function ($query) {
                $query->where('pvo_sent', '=', 1)
                    ->where('id_pvo', '!=', NULL)
                    ->orWhere('id_client_pvo', '!=', NULL);
                })
            ->orderBy('updated_at', 'DESC')
            ->limit(1)
            ->get();

        return view('dashboardVehicles2', compact('vehicles', 'users', 'pvo_not_sent', 'bee2link_not_sent', 'last_vehicle', 'date_prise_en_compte', 'PVO', 'B2L', 'DASHBOARD_DEBUG'));

    }

     /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function listManager()
    {
        $paginate = DB::connection('mysql2')
                            ->table('users')
                            ->select('entreprise', 'manager_id')
                            ->where('entreprise', '!=', NULL)
                            ->where('manager_id', '!=', NULL)
                            ->orderBy('entreprise')
                            ->paginate(10);

        return view('dashboard_list_manager', compact('paginate'));
    }

     /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function dashboardParManager(int $id)
    {
        // ENV variables
        $B2L = env('B2L');
        $PVO = env('PVO');
        $DASHBOARD_DEBUG = env('DASHBOARD_DEBUG');

        // DATE variables
        date_default_timezone_set('Europe/Paris');
        $datetime = strtotime('-' . env('DATE') . ' days');
        $date_prise_en_compte = date('Y-m-d H:i:s', $datetime);
        $datetime1 = strtotime('-1 days');
        $newdate = date('Y-m-d H:i:s', $datetime1);

        $vehicles = DB::connection('mysql2')
        ->table("vehicules")
        ->Join("users", "vehicules.client_id", "=", "users.id")
        ->select(
            "accepted",
            "vehicules.id",
            "vehicules.name",
            "manager_id",
            "client_id",
            "users.id_bee2link",
            "id_client_bee2link",
            "statut_bee2link",
            "bee2link_sent",
            "users.id_pvo",
            "id_client_pvo",
            "vehicules.pvo_sent",
            "operator_id",
            "mail_sent_operator",
            "vehicules.created_at",
            "vehicules.updated_at"
        )
        ->where(function($query){
            $query->where("accepted", "=", 1)
                  ->where("vehicules.deleted_at", "=", NULL)
                  ->where("mail_sent_operator", "=", NULL);
        })
        ->where(function($query){
            $query->where("pvo_sent", "=", NULL)
                  ->orWhere("bee2link_sent", "=", NULL);
        })
        ->where(function($query){
            $query->where("users.id_pvo", "!=", NULL)
                  ->orWhere("users.id_bee2link", "!=", NULL)
                  ->orWhere("vehicules.id_client_pvo", "!=", NULL)
                  ->orWhere("vehicules.id_client_bee2link", "!=", NULL);
        })
        ->where("vehicules.updated_at", ">=", $date_prise_en_compte)
        ->where("manager_id", "=", $id)
        ->orderBy("vehicules.updated_at", "desc")
        ->get();

        // Tri de la 1ere query
        $trash = [];
        $filter_list = [];
        $list_vehicles = [];

        for($i = 0; $i < count($vehicles); $i++){
                array_push($filter_list, $vehicles[$i]);
        }
        for($i = 0; $i < count($filter_list); $i++){
            if(($filter_list[$i]->id_bee2link === NULL && $filter_list[$i]->id_client_bee2link === NULL) && ($filter_list[$i]->pvo_sent !== NULL)){
                array_push($trash, $filter_list[$i]);
            }
            elseif(($filter_list[$i]->id_pvo === NULL && $filter_list[$i]->id_client_pvo === NULL) && ($filter_list[$i]->bee2link_sent !== NULL)){
                array_push($trash, $filter_list[$i]);
            }
            else{
                array_push($list_vehicles, $filter_list[$i]);
            }
        }

        $pvo_not_sent = 0;
        $bee2link_not_sent = 0;

        foreach($list_vehicles as $count){
            if($count->pvo_sent === NULL && ($count->id_pvo != NULL || $count->id_client_pvo != NULL)){
                $pvo_not_sent++;
            }
            if($count->bee2link_sent === NULL && ($count->id_bee2link != NULL || $count->id_client_bee2link != NULL)){
                $bee2link_not_sent++;
            }
        }

        // Query afin de recuperer des informations supplémentaires
        $users = DB::connection('mysql2')
        ->table('users')
        ->select('id', 'entreprise', 'name','email','tel1')
        ->orderBy('updated_at', 'desc')
        ->get();

        $manager_name = DB::connection('mysql2')
                            ->table('users')
                            ->select('name','entreprise')
                            ->where('id', '=', $id)
                            ->get();

        // query afin de recupérer le dernier véhicule remonté chez PVO
        if(isset($vehicles[0]->client_id)) {
        $last_vehicle = DB::connection('mysql2')
            ->table('vehicules')
            ->join('users', 'vehicules.client_id', '=', 'users.id')
            ->select('pvo_sent_at','vehicules.name')
            ->where('pvo_sent_at', '!=', NULL)
            ->where(function ($query) {
                $query->where('pvo_sent', '=', 1)
                    ->where('vehicules.id_pvo', '!=', NULL)
                    ->orWhere('vehicules.id_client_pvo', '!=', NULL);
                })
            ->orderBy('vehicules.updated_at', 'DESC')
            ->where('client_id', '=', $vehicles[0]->client_id)
            ->limit(1)
            ->get();
        }else{
            $last_vehicle = "INCONNU";
        }

            $vehicles = $this->paginate($list_vehicles);

        return view('dashboard_manager', compact('vehicles', 'users', 'date_prise_en_compte', 'PVO', 'B2L', 'DASHBOARD_DEBUG', 'manager_name','last_vehicle', 'bee2link_not_sent', 'pvo_not_sent'));
    }

     /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function dashboardParClient(string $name)
    {
        $manager_name = $name;
        // ENV variables
        $B2L = env('B2L');
        $PVO = env('PVO');
        $DASHBOARD_DEBUG = env('DASHBOARD_DEBUG');

        // DATE variables
        date_default_timezone_set('Europe/Paris');
        $datetime = strtotime('-' . env('DATE') . ' days');
        $date_prise_en_compte = date('Y-m-d H:i:s', $datetime);
        $datetime1 = strtotime('-1 days');
        $newdate = date('Y-m-d H:i:s', $datetime1);

        $vehicles = DB::connection('mysql2')
        ->table("vehicules")
        ->Join("users", "vehicules.client_id", "=", "users.id")
        ->select(
            "accepted",
            "vehicules.id",
            "vehicules.name",
            "manager_id",
            "client_id",
            "users.id_bee2link",
            "id_client_bee2link",
            "statut_bee2link",
            "bee2link_sent",
            "users.id_pvo",
            "id_client_pvo",
            "vehicules.pvo_sent",
            "operator_id",
            "mail_sent_operator",
            "vehicules.created_at",
            "vehicules.updated_at"
        )
        ->where(function($query){
            $query->where("accepted", "=", 1)
                  ->where("vehicules.deleted_at", "=", NULL)
                  ->where("mail_sent_operator", "=", NULL);
        })
        ->where(function($query){
            $query->where("pvo_sent", "=", NULL)
                  ->orWhere("bee2link_sent", "=", NULL);
        })
        ->where(function($query){
            $query->where("users.id_pvo", "!=", NULL)
                  ->orWhere("users.id_bee2link", "!=", NULL)
                  ->orWhere("vehicules.id_client_pvo", "!=", NULL)
                  ->orWhere("vehicules.id_client_bee2link", "!=", NULL);
        })
        ->where("vehicules.updated_at", ">=", $date_prise_en_compte)
        ->where("users.name", "=", $name)
        ->orderBy("vehicules.updated_at", "desc")
        ->get();

        // Tri de la 1ere query
        $trash = [];
        $filter_list = [];
        $list_vehicles = [];

        for($i = 0; $i < count($vehicles); $i++){
                array_push($filter_list, $vehicles[$i]);
        }
        for($i = 0; $i < count($filter_list); $i++){
            if(($filter_list[$i]->id_bee2link === NULL && $filter_list[$i]->id_client_bee2link === NULL) && ($filter_list[$i]->pvo_sent !== NULL)){
                array_push($trash, $filter_list[$i]);
            }
            elseif(($filter_list[$i]->id_pvo === NULL && $filter_list[$i]->id_client_pvo === NULL) && ($filter_list[$i]->bee2link_sent !== NULL)){
                array_push($trash, $filter_list[$i]);
            }
            else{
                array_push($list_vehicles, $filter_list[$i]);
            }
        }

        $pvo_not_sent = 0;
        $bee2link_not_sent = 0;

        foreach($list_vehicles as $count){
            if($count->pvo_sent === NULL && ($count->id_pvo != NULL || $count->id_client_pvo != NULL)){
                $pvo_not_sent++;
            }
            if($count->bee2link_sent === NULL && ($count->id_bee2link != NULL || $count->id_client_bee2link != NULL)){
                $bee2link_not_sent++;
            }
        }

        // Query afin de recuperer des informations supplémentaires
        $users = DB::connection('mysql2')
        ->table('users')
        ->select('id', 'entreprise', 'name','email','tel1')
        ->orderBy('updated_at', 'desc')
        ->get();

        // $manager_name = DB::connection('mysql2')
        //                     ->table('users')
        //                     ->select('name','entreprise')
        //                     ->where('id', '=', $id)
        //                     ->get();

        // query afin de recupérer le dernier véhicule remonté chez PVO
        $last_vehicle = DB::connection('mysql2')
            ->table('vehicules')
            ->join('users', 'vehicules.client_id', '=', 'users.id')
            ->select('pvo_sent_at','vehicules.name')
            ->where('pvo_sent_at', '!=', NULL)
            ->where(function ($query) {
                $query->where('pvo_sent', '=', 1)
                    ->where('vehicules.id_pvo', '!=', NULL)
                    ->orWhere('vehicules.id_client_pvo', '!=', NULL);
                })
            ->orderBy('vehicules.updated_at', 'DESC')
            ->where('client_id', '=', $vehicles[0]->client_id)
            ->limit(1)
            ->get();

            $vehicles = $this->paginate($list_vehicles);

        return view('dashboard_client', compact('manager_name','vehicles', 'users', 'date_prise_en_compte', 'PVO', 'B2L', 'DASHBOARD_DEBUG','last_vehicle', 'bee2link_not_sent', 'pvo_not_sent'));
    }

    public function paginate($items, $perPage = 10, $page = null, $options = []){

        $page = $page ?: (Paginator::resolveCurrentPage() ?: 1);

        $items = $items instanceof Collection ? $items : Collection::make($items);

        return new LengthAwarePaginator($items->forPage($page, $perPage), $items->count(), $perPage, $page, $options);
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        //
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        //
    }

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
        //
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function edit($id)
    {
        //
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
        //
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function destroy($id)
    {
        //
    }
}