<?php

namespace App\Http\Controllers\API\v1;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use App\Http\Requests\AptitudeStoreRequest;
use App\Http\Requests\AptitudeIndexRequest;
use App\Http\Requests\AptitudeUpdateRequest;
use Illuminate\Support\Facades\DB;
use App\Aptitude;
use Exception;

class AptitudeController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index(AptitudeIndexRequest $request)
    {
        try {

            if ($request->statut == "all") {

                $aptitudes = Aptitude::all();
            } else if ($request->statut == "many") {

                $aptitudes = Aptitude::where('statut', 'MANY')->get();
            } else if ($request->statut == "one") {

                $aptitudes = Aptitude::where('statut', 'ONE')->get();
            } else {

                return response()->json([
                    'status' => 'error',
                    'message' => 'This statut does exist'
                ])->withHeaders([
                    'Accept' => 'application/json',
                    'Content-Type' => 'application/json'
                ]);
            }

            return response()->json([
                'status' => 'success',
                'aptitudes' => $aptitudes
            ])->withHeaders([
                'Accept' => 'application/json',
                'Content-Type' => 'application/json'
            ]);
        } catch (Exception $e) {
            return response()->json([
                'status' => 'error',
                'message' => $e->getMessage()
            ], 404)->withHeaders([
                'Accept' => 'application/json',
                'Content-Type' => 'application/json'
            ]);
        }
    }

    public function aptitudeFiliere(Request $request)
    {
        try {
            $request->validate([
                'statut' => 'required'
            ]);

          
            $SchoolDepInfo= $request->get('SchoolDep');
            $departement = $SchoolDepInfo['departement_id'];
            $bd = $SchoolDepInfo['etablissement_id']."_".$departement;
            
            if ($request->departement_id) {
	            	$departement = hackKey(false,$request->departement_id);
            }

            if ($request->statut == 'many') {
                $aptitude_filiere = DB::connection('tenant')->select('SELECT * FROM `aptitude_filiere` WHERE `aptitude_id` IN (SELECT `aptitude_id` FROM `aptitude_filiere` WHERE `departement_id` = ? GROUP BY `aptitude_id` HAVING count(*) > 1) ORDER BY `aptitude_id` ASC',[$departement]);  //`aptitude_id`,`filiere_id`
                $aptitudes = array_column($aptitude_filiere,"aptitude_id");
                $aptitudes = Aptitude::whereIn('id',$aptitudes)
                ->get();

            } else if ($request->statut == 'one') {

                $aptitude_filiere = DB::connection('tenant')->select('SELECT * FROM `aptitude_filiere` WHERE `aptitude_id` IN (SELECT `aptitude_id` FROM `aptitude_filiere` WHERE `departement_id` = ?  GROUP BY `aptitude_id` HAVING count(*) = 1) ORDER BY `aptitude_id` ASC',[$departement]);
                $aptitudes = array_column($aptitude_filiere,"aptitude_id");
                $aptitudes = Aptitude::whereIn('id',$aptitudes)
                ->get();
            } else {

                return response()->json([
                    'status' => 'error',
                    'message' => 'This statut does not exist'
                ])->withHeaders([
                    'Accept' => 'application/json',
                    'Content-Type' => 'application/json'
                ]);
            }

            return response()->json([
                'status' => 'success',
                'aptitude_filiere' => $aptitude_filiere,
                'aptitudes' => $aptitudes
            ])->withHeaders([
                'Accept' => 'application/json',
                'Content-Type' => 'application/json'
            ]);
        } catch (Exception $e) {

            return response()->json([
                'status' => 'error',
                'message' => $e->getMessage()
            ], 404)->withHeaders([
                'Accept' => 'application/json',
                'Content-Type' => 'application/json'

            ]);
        }
    }

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

        try {

            $aptitude = new Aptitude();
            $aptitude->description = $request->description;
            $aptitude->statut = "ONE";
            $aptitude->save();

            //Get 'Autres' domaine's id on the database
            $autresDomainId = DB::table('domaines')->select('id')->where('nom', 'Autres')->first()->id;

            //Fill intermediary tables
            DB::connection('tenant')->insert('insert into aptitude_filiere (aptitude_id, filiere_id) values (?, ?)', [$aptitude->id, $request->filiere_id]);
            DB::insert('insert into aptitude_domaine (aptitude_id, domaine_id) values (?, ?)', [$aptitude->id, $autresDomainId]);


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

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

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

        try {
            $aptitude = Aptitude::findOrFail($id);

            $filieres = DB::connection('tenant')->table('aptitude_filiere')
                ->select('aptitude_filiere.filiere_id')
                ->where('aptitude_id', '=', $id)
                ->get();

            return response()->json([
                'status' => 'success',
                'aptitude' => $aptitude,
                'filieres' => $filieres,
            ])->withHeaders([
                'Accept' => 'application/json',
                'Content-Type' => 'application/json'
            ]);
        } catch (Exception $e) {

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

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

            $aptitude = Aptitude::findOrFail($id);
            $aptitude->statut = $request->statut;
            $aptitude->update();

            return response()->json([
                'status' => 'success',
                'aptitude' => $aptitude
            ])->withHeaders([
                'Accept' => 'application/json',
                'Content-Type' => 'application/json'
            ]);
        } catch (Exception $e) {

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

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