<?php

namespace App\Http\Controllers;

Use Alert;
use Carbon\Carbon;
use App\Models\Job;
use App\Models\JobVendor;
use App\Models\Consultant;
use App\Events\UpdateOptBar;
use Illuminate\Http\Request;
use App\Events\UpdateOptLine;
use App\Models\ConsultantJob;
use App\Events\UpdateBenchBar;
use App\Events\UpdateOptPolar;
use App\Events\UpdateBenchLine;
use App\Models\ConsultantAging;
use App\Models\ConsultantBatch;
use App\Events\UpdateBenchPolar;
use App\Models\ConsultantResume;
use App\Models\ConsultantComment;
use App\Models\ConsultantContact;
use App\Models\ConsultantTrainer;
use App\Events\UpdateOptLiveCards;
use Illuminate\Support\Facades\DB;
use App\Models\ConsultantEducation;
use App\Models\ConsultantReference;
use App\Events\UpdateBenchLiveCards;
use App\Models\ConsultantAttachment;
use App\Models\ConsultantExperience;
use App\Models\ConsultantGuesthouse;
use App\Models\ConsultantTechnology;
use App\Models\ConsultantCertificate;

class ConsultantController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index(Request $request)
    {
        $uuids = userUserIds();
        $rdates = $request->dates;
        $statuses = config('custom.employee_statuses');
        $technologies = technologySelect();

        if($request->ajax()) {
            $mdates = explode(' - ', $rdates);
            $ldates = [date('Y-m-d', strtotime($mdates[0])), date('Y-m-d', strtotime($mdates[1]))];
            
            $list = Consultant::with(['technologies'])->whereBetween('created_at', $ldates)->whereIn('created_by', $uuids)->latest()->get();
            return view('consultants.ajax', compact('list', 'statuses', 'technologies'));
        } else {
            $list = Consultant::with(['technologies'])->whereIn('created_by', $uuids)->latest()->get();
            return view('consultants.index', compact('list', 'statuses', 'technologies'));
        }   
    }

    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function create()
    {
        $data['company_id'] = auth()->user()->company_id;
        $data['countries'] = countrySelect();
        $data['usstates'] = stateSelect(231);
        $data['indstates'] = stateSelect(101);
        $data['states'] = stateSelect();
        $data['cities'] = citySelect();
        $data['technologies'] = technologySelect();
        $data['universities'] = universitySelect();
        $data['clients'] = clientSelect();
        $data['statuses'] = config('custom.employee_statuses');
        $data['guesthouses'] = guestHouseSelect();
        $data['vendor_companies'] = vendorCompanySelect();
        $data['vendors'] = vendorSelect();
        $data['visa_statuses'] = config('custom.visa_statuses');
        $data['opts'] = userSelect(3);
        $data['benchsales'] = userSelect(4);
        $data['trainers'] = userSelect(6);
        $data['payrates_intervals'] = config('custom.payrates_intervals');
        $data['consultant_reference'] = config('custom.consultant_reference');
        $data['batches'] = batchSelect();
        $data['recruiters'] = roleUserSelect(['opt_recruiter']);

        return view('consultants.create', $data);
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
        /* Useful Variables */
        $company_id = auth()->user()->company_id;

        DB::beginTransaction();
        /* Save Consultant */
            $consultant = new Consultant;
            $consultant->company_id = $company_id;
            $consultant->name = ucwords($request->name);
            $consultant->email = $request->email;
            $consultant->mobile = $request->mobile;
            $consultant->date_of_birth = dbDateFormat($request->dob);
            $consultant->origin_country_id = $request->origin_country_id;
            $consultant->origin_state_id = $request->origin_state_id;
            $consultant->origin_city_id = $request->origin_city_id;
            $consultant->current_country_id = $request->current_country_id;
            $consultant->current_state_id = $request->current_state_id;
            $consultant->current_city_id = $request->current_city_id;
            $consultant->ssn = $request->ssn;
            $consultant->skype_id = $request->skype_id;
            $consultant->driving_licence = $request->driving_licence;
            $consultant->visa_status = $request->visa_status;
            $consultant->visa_start_date = $request->visa_start_date;
            $consultant->visa_end_date = $request->visa_end_date;
            $consultant->came_to_usa = $request->came_to_usa;
            $consultant->home_visit = $request->home_visit;
            $consultant->relocate = $request->relocate;
            $consultant->zipcode = $request->zipcode;
            $consultant->reference = $request->consultant_reference;
            $consultant->referer_name = $request->referer_name;
            $consultant->marital_status = $request->marital_status;
            $consultant->created_by = auth()->user()->id;
            if(isset($request->tech_recruiter_id)){
                $consultant->tech_recruiter_id = $request->tech_recruiter_id;
            }
            $consultant->created_ip = $request->ip();
            $consultant->status = $request->status;
            $consultant->save();
        /* Save Consultant Ends */

        /* Save Technologies */
        if(isset($consultant->id)) {
            $technologies = $request->technologies;
            if(count($technologies) > 0) {
                foreach($technologies as $tech) {
                    $ct = new ConsultantTechnology;
                    $ct->consultant_id = $consultant->id;
                    $ct->technology_id = $tech;
                    $ct->save();
                }
            }
        }

        /* Save Comments */
        if(isset($consultant->id) && $request->comments != NULL) {
            $ccmnt = new ConsultantComment;
            $ccmnt->consultant_id = $consultant->id;
            $ccmnt->comment = $request->comments;
            $ccmnt->user_id = auth()->user()->id;
            $ccmnt->save();
        }

        /* Save Educations */
        if(isset($consultant->id)) {
            /* US Education */
            if(count($request->sevis_id) > 0 && $request->sevis_id[0] != ''){
                $cusedu_count = count($request->sevis_id);
                for($i=0; $i<$cusedu_count; $i++) {
                    $cuseducation = new ConsultantEducation;
                    $cuseducation->consultant_id = $consultant->id;
                    $cuseducation->type = 1;
                    $cuseducation->sevis_id = isset($request->sevis_id[$i])?$request->sevis_id[$i]:NULL;
                    $cuseducation->university_id = isset($request->university_id[$i])?$request->university_id[$i]:NULL;
                    $cuseducation->specialization = isset($request->masters_in[$i])?$request->masters_in[$i]:NULL;
                    $cuseducation->graduation_date = isset($request->graduation_date[$i])?dbDateFormat($request->graduation_date[$i]):NULL;
                    $cuseducation->country_id = isset($request->university_country[$i])?$request->university_country[$i]:NULL;
                    $cuseducation->state_id = isset($request->university_state[$i])?$request->university_state[$i]:NULL;
                    $cuseducation->city_id = isset($request->university_city[$i])?$request->university_city[$i]:NULL;
                    $cuseducation->address = isset($request->university_address[$i])?$request->university_address[$i]:NULL;
                    $cuseducation->save();
                }
            }

            /* IND Education */
            if(count($request->ind_university_id) > 0  && $request->sevis_id[0] != ''){
                $cindedu_count = count($request->ind_university_id);
                for($i=0; $i<$cindedu_count; $i++) {
                    $cindeducation = new ConsultantEducation;
                    $cindeducation->consultant_id = $consultant->id;
                    $cindeducation->type = 2;
                    $cindeducation->sevis_id = NULL;
                    $cindeducation->university_id = isset($request->ind_university_id[$i])?$request->ind_university_id[$i]:NULL;
                    $cindeducation->specialization = isset($request->ind_graduate_in[$i])?$request->ind_graduate_in[$i]:NULL;
                    $cindeducation->graduation_date = isset($request->ind_graduation_date[$i])?dbDateFormat($request->ind_graduation_date[$i]):NULL;
                    $cindeducation->country_id = NULL;
                    $cindeducation->state_id = NULL;
                    $cindeducation->city_id = NULL;
                    $cindeducation->address = isset($request->ind_university_address[$i])?$request->ind_university_address[$i]:NULL;
                    $cindeducation->save();
                }
            }
        }

        /* Save Certificates */
        if(isset($consultant->id)) {
            if(count($request->certification_name) > 0 && $request->certification_name[0] != '') {
                $ccrt_count = count($request->certification_name);
                for ($i=0; $i < $ccrt_count; $i++) { 
                    $ccrt = new ConsultantCertificate;
                    $ccrt->consultant_id = $consultant->id;
                    $ccrt->name = isset($consultant->certification_name[$i])?$consultant->certification_name[$i]:"";
                    $ccrt->certificate_id = isset($request->certification_Id[$i])?$request->certification_Id[$i]:"";
                    $ccrt->certified_on = isset($request->completion_date[$i])?dbDateFormat($request->completion_date[$i]):"";
                    $ccrt->validity = isset($request->certification_validity[$i])?dbDateFormat($request->certification_validity[$i]):"";
                    $ccrt->status = isset($request->certificate_status[$i])?$request->certificate_status[$i]:"";
                    $ccrt->save();
                }
            }
        }

        /* Save Experiences */
        if(isset($consultant->id)) {
            /* Resume Experience */
            if(count($request->res_company_name) > 0 && $request->res_company_name[0] != '') {
                $crexp_count = count($request->res_company_name);
                for ($i=0; $i < $crexp_count; $i++) { 
                    $crexp = new ConsultantExperience;
                    $crexp->consultant_id = $consultant->id;
                    $crexp->type = 1;
                    $crexp->company_name = isset($request->res_company_name[$i])?$request->res_company_name[$i]:NULL;
                    $crexp->designation = isset($request->res_designation[$i])?$request->res_designation[$i]:NULL;
                    $crexp->experience = isset($request->res_experience[$i])?$request->res_experience[$i]:NULL;
                    $crexp->country_id = isset($request->res_country[$i])?$request->res_country[$i]:NULL;
                    $crexp->state_id = isset($request->res_state[$i])?$request->res_state[$i]:NULL;
                    $crexp->city_id = isset($request->res_city[$i])?$request->res_city[$i]:NULL;
                    $crexp->save();
                }
            }

            /* IT Experience */
            if(count($request->it_company_name) > 0 && $request->it_company_name[0] != '') {
                $citexp_count = count($request->it_company_name);
                for ($i=0; $i < $citexp_count; $i++) { 
                    $citexp = new ConsultantExperience;
                    $citexp->consultant_id = $consultant->id;
                    $citexp->type = 2;
                    $citexp->company_name = isset($request->it_company_name[$i])?$request->it_company_name[$i]:NULL;
                    $citexp->designation = isset($request->it_designation[$i])?$request->it_designation[$i]:NULL;
                    $citexp->experience = isset($request->it_experience[$i])?$request->it_experience[$i]:NULL;
                    $citexp->country_id = isset($request->it_country[$i])?$request->it_country[$i]:NULL;
                    $citexp->state_id = isset($request->it_state[$i])?$request->it_state[$i]:NULL;
                    $citexp->city_id = isset($request->it_city[$i])?$request->it_city[$i]:NULL;
                    $citexp->save();
                }
            }
        }

        /* Save Reference */
        if(isset($consultant->id)) {
            if(count($request->client_name) > 0 && $request->client_name[0] != '') {
                $crfr_count = count($request->client_name);
                for ($i=0; $i < $crfr_count; $i++) { 
                    $crfr = new ConsultantReference;
                    $crfr->consultant_id = $consultant->id;
                    $crfr->client_name = isset($request->client_name[$i])?$request->client_name[$i]:NULL;
                    $crfr->supervisor_name = isset($request->supervisor_name[$i])?$request->supervisor_name[$i]:NULL;
                    $crfr->supervisor_email = isset($request->supervisor_email[$i])?$request->supervisor_email[$i]:NULL;
                    $crfr->supervisor_phone = isset($request->supervisor_phone[$i])?$request->supervisor_phone[$i]:NULL;
                    $crfr->supervisor_designation = isset($request->supervisor_designation[$i])?$request->supervisor_designation[$i]:NULL;
                    $crfr->country_id = isset($request->refer_country[$i])?$request->refer_country[$i]:NULL;
                    $crfr->state_id = isset($request->refer_state[$i])?$request->refer_state[$i]:NULL;
                    $crfr->city_id = isset($request->refer_city[$i])?$request->refer_city[$i]:NULL;
                    $crfr->save();
                }                
            }
        }

        /* Save Additional details Trainer & Batch & Guest House */
        if(isset($consultant->id)) {
            // Trainer
            if($request->trainer_id != NULL) {
                $ctrnr = new ConsultantTrainer;
                $ctrnr->consultant_id = $consultant->id;
                $ctrnr->trainer_id = $request->trainer_id;
                $ctrnr->save();
            }
            
            // Batch
            if($request->batch_id != NULL) {
                $ctrnr = new ConsultantBatch;
                $ctrnr->consultant_id = $consultant->id;
                $ctrnr->batch_id = $request->batch_id;
                $ctrnr->save();
            }
            
            // Guesthouse
            if($request->guesthouse_id != NULL) {
                $ctrnr = new ConsultantGuesthouse;
                $ctrnr->consultant_id = $consultant->id;
                $ctrnr->guest_house_id = $request->guesthouse_id;
                $ctrnr->allocated_on = isset($request->guest_aloc_date)?dbDateFormat($request->guest_aloc_date):NULL;
                $ctrnr->leaves_on = NULL;
                $ctrnr->save();
            }
        }

        /* Save Emergencies */
        if(isset($consultant->id)) {
            // US Emergencies
            if(count($request->us_emergency_name) > 0 && $request->us_emergency_name[0] != '') {
                $cusemc_count = count($request->us_emergency_name);
                for ($i=0; $i < $cusemc_count; $i++) { 
                    $cusemc = new ConsultantContact;
                    $cusemc->consultant_id = $consultant->id;
                    $cusemc->type = 1;
                    $cusemc->name = isset($request->us_emergency_name[$i])?$request->us_emergency_name[$i]:NULL;
                    $cusemc->email = isset($request->us_emergency_email[$i])?$request->us_emergency_email[$i]:NULL;
                    $cusemc->phone = isset($request->us_emergency_mobile[$i])?$request->us_emergency_mobile[$i]:NULL;
                    $cusemc->relation = isset($request->us_emergency_relation[$i])?$request->us_emergency_relation[$i]:NULL;
                    $cusemc->country_id = 231;
                    $cusemc->save();
                }                
            }

            // Overseas Emergencies
            if(count($request->ovr_emergency_name) > 0 && $request->ovr_emergency_name[0] != '') {
                $cusemc_count = count($request->ovr_emergency_name);
                for ($i=0; $i < $cusemc_count; $i++) { 
                    $cusemc = new ConsultantContact;
                    $cusemc->consultant_id = $consultant->id;
                    $cusemc->type = 2;
                    $cusemc->name = isset($request->ovr_emergency_name[$i])?$request->ovr_emergency_name[$i]:NULL;
                    $cusemc->email = isset($request->ovr_emergency_email[$i])?$request->ovr_emergency_email[$i]:NULL;
                    $cusemc->phone = isset($request->ovr_emergency_mobile[$i])?$request->ovr_emergency_mobile[$i]:NULL;
                    $cusemc->relation = isset($request->ovr_emergency_relation[$i])?$request->ovr_emergency_relation[$i]:NULL;
                    $cusemc->country_id = isset($request->ovr_country[$i])?$request->ovr_country[$i]:NULL;
                    $cusemc->save();
                }                
            }
        }

        /* Save Resume */
        if(isset($consultant->id)) {
            if($request->resume_skills != NULL) {
                $cresume = new ConsultantResume;
                $cresume->consultant_id = $consultant->id;
                $cresume->skills = isset($request->resume_skills)?$request->resume_skills:NULL;
                $cresume->summary = isset($request->profile_summary)?$request->profile_summary:NULL;
                $cresume->resume = isset($request->resume)?$request->resume:NULL;
                $cresume->save();
            }
        }
        
        /* Save Attachments */
        if(isset($consultant->id)) {
            if(count($request->attachment_name) > 0 && $request->attachment_name[0] != '') {
                $catt_count = count($request->attachment_name);
                for ($i=0; $i < $catt_count; $i++) { 
                    $catt = new ConsultantAttachment;
                    $catt->consultant_id = $consultant->id;
                    $catt->name = isset($request->attachment_name[$i])?$request->attachment_name[$i]:NULL;
                    if(isset($request->attachments[$i])) {
                        $file = $request->attachments[$i];
                        $ext = $file->getClientOriginalExtension();
                        $filename = $file->hashName().'.'.$ext;
                        $file->move(public_path('/storage/attachments'), $filename);
                        $catt->file = $filename;
                    }
                    $catt->save();
                }
            }
        }

        /* Saving Aging for status */
        if(isset($consultant->id)) {
            $ca = new ConsultantAging;
            $ca->consultant_id = $consultant->id;
            $ca->status = $request->status;
            $ca->save();
        }
        DB::commit();

        // Updating to live data from broadcast
        broadcast(new UpdateOptLiveCards());
        broadcast(new UpdateOptPolar());
        broadcast(new UpdateOptLine());
        broadcast(new UpdateOptBar());
        broadcast(new UpdateBenchBar());
        broadcast(new UpdateBenchLine());
        broadcast(new UpdateBenchLiveCards());
        broadcast(new UpdateBenchPolar());

        Alert('success', 'Consultant has been added successfully');
        return redirect(route('consultants.create'));
    }

    /**
     * Display the specified resource.
     *
     * @param  \App\Models\Consultant  $consultant
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
        $consultant = Consultant::with(['technologies', 'creater', 'certificates'])->whereId($id)->first();

        $statuses = config('custom.employee_statuses');
        $technologies = technologySelect();
        $visas = config('custom.visa_statuses');
        
        return view('consultants.show', compact('consultant', 'statuses', 'technologies', 'visas'));
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  \App\Models\Consultant  $consultant
     * @return \Illuminate\Http\Response
     */
    public function edit($id)
    {
        // Consultant editable details
        $data['consultant'] = Consultant::findOrFail($id);
        $data['ctechnologies'] = ConsultantTechnology::whereConsultant_id($id)->pluck('technology_id')->all();
        $data['cuseducations'] = ConsultantEducation::whereConsultant_id($id)->whereType(1)->get();
        $data['cindeducations'] = ConsultantEducation::whereConsultant_id($id)->whereType(2)->get();
        $data['ccertificates'] = ConsultantCertificate::whereConsultant_id($id)->get();
        $data['cresexperiences'] = ConsultantExperience::whereConsultant_id($id)->whereType(1)->get();
        $data['citexperiences'] = ConsultantExperience::whereConsultant_id($id)->whereType(2)->get();
        $data['creferences'] = ConsultantReference::whereConsultant_id($id)->get();
        $data['cbatch'] = ConsultantBatch::whereConsultant_id($id)->latest()->first();
        $data['cguesthouse'] = ConsultantGuesthouse::whereConsultant_id($id)->latest()->first();
        $data['ctrainer'] = ConsultantTrainer::whereConsultant_id($id)->latest()->first();
        $data['cuscontacts'] = ConsultantContact::whereConsultant_id($id)->whereType(1)->get();
        $data['cindcontacts'] = ConsultantContact::whereConsultant_id($id)->whereType(2)->get();
        $data['cresume'] = ConsultantResume::whereConsultant_id($id)->latest()->first();
        $data['cattachments'] = ConsultantAttachment::whereConsultant_id($id)->get();

        // Required form variables
        $data['company_id'] = auth()->user()->company_id;
        $data['origin_cities'] = citySelect($data['consultant']->origin_state);
        $data['countries'] = countrySelect();
        $data['states'] = stateSelect();
        $data['cities'] = citySelect();
        $data['current_cities'] = citySelect($data['consultant']->current_state);
        $data['usstates'] = stateSelect(231);
        $data['indstates'] = stateSelect(101);
        $data['technologies'] = technologySelect();
        $data['universities'] = universitySelect();
        $data['clients'] = clientSelect();
        $data['statuses'] = config('custom.employee_statuses');
        $data['guesthouses'] = guestHouseSelect();
        $data['vendor_companies'] = vendorCompanySelect();
        $data['vendors'] = vendorSelect();
        $data['visa_statuses'] = config('custom.visa_statuses');
        $data['opts'] = userSelect(3);
        $data['benchsales'] = userSelect(4);
        $data['trainers'] = userSelect(6);
        $data['payrates_intervals'] = config('custom.payrates_intervals');
        $data['consultant_reference'] = config('custom.consultant_reference');
        $data['batches'] = batchSelect();
        $data['recruiters'] = roleUserSelect(['opt_recruiter']);

        return view('consultants.edit', $data);
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  \App\Models\Consultant  $consultant
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
        /* Useful Variables */
        $company_id = auth()->user()->company_id;

        DB::beginTransaction();
        /* Save Consultant */
            $consultant = Consultant::findOrFail($id);
            $consultant->company_id = $company_id;
            $consultant->name = ucwords($request->name);
            $consultant->email = $request->email;
            $consultant->mobile = $request->mobile;
            $consultant->date_of_birth = dbDateFormat($request->dob);
            $consultant->origin_country_id = $request->origin_country_id;
            $consultant->origin_state_id = $request->origin_state_id;
            $consultant->origin_city_id = $request->origin_city_id;
            $consultant->current_country_id = $request->current_country_id;
            $consultant->current_state_id = $request->current_state_id;
            $consultant->current_city_id = $request->current_city_id;
            $consultant->ssn = $request->ssn;
            $consultant->skype_id = $request->skype_id;
            $consultant->driving_licence = $request->driving_licence;
            $consultant->visa_status = $request->visa_status;
            $consultant->visa_start_date = $request->visa_start_date;
            $consultant->visa_end_date = $request->visa_end_date;
            $consultant->came_to_usa = $request->came_to_usa;
            $consultant->home_visit = $request->home_visit;
            $consultant->relocate = $request->relocate;
            $consultant->zipcode = $request->zipcode;
            $consultant->reference = $request->consultant_reference;
            $consultant->referer_name = $request->referer_name;
            $consultant->marital_status = $request->marital_status;
            $consultant->created_by = auth()->user()->id;
            if(isset($request->tech_recruiter_id)){
                $consultant->tech_recruiter_id = $request->tech_recruiter_id;
            }
            $consultant->created_ip = $request->ip();

            // Creating previous status
            $prev_status = $consultant->status;
            // New status
            $consultant->status = $request->status;            
            $consultant->update();
        /* Save Consultant Ends */
        
        /* Save Technologies */
        if(isset($consultant->id)) {
            $technologies = $request->technologies;
            if(count($technologies) > 0) {
                // Delete previous data
                ConsultantTechnology::whereConsultant_id($id)->delete();

                foreach($technologies as $tech) {
                    $ct = new ConsultantTechnology;
                    $ct->consultant_id = $consultant->id;
                    $ct->technology_id = $tech;
                    $ct->save();
                }
            }
        }

        /* Save Comments */
        if(isset($consultant->id) && $request->comments != NULL) {
            $ccmnt = new ConsultantComment;
            $ccmnt->consultant_id = $consultant->id;
            $ccmnt->comment = $request->comments;
            $ccmnt->user_id = auth()->user()->id;
            $ccmnt->save();
        }

        /* Save Educations */
        if(isset($consultant->id)) {
            /* US Education */
            if(count($request->sevis_id) > 0 && $request->sevis_id[0] != ''){
                // Delete previous data
                ConsultantEducation::whereConsultant_id($id)->whereType(1)->delete();
                
                $cusedu_count = count($request->sevis_id);
                for($i=0; $i<$cusedu_count; $i++) {
                    $cuseducation = new ConsultantEducation;
                    $cuseducation->consultant_id = $consultant->id;
                    $cuseducation->type = 1;
                    $cuseducation->sevis_id = isset($request->sevis_id[$i])?$request->sevis_id[$i]:NULL;
                    $cuseducation->university_id = isset($request->university_id[$i])?$request->university_id[$i]:NULL;
                    $cuseducation->specialization = isset($request->masters_in[$i])?$request->masters_in[$i]:NULL;
                    $cuseducation->graduation_date = isset($request->graduation_date[$i])?dbDateFormat($request->graduation_date[$i]):NULL;
                    $cuseducation->country_id = isset($request->university_country[$i])?$request->university_country[$i]:NULL;
                    $cuseducation->state_id = isset($request->university_state[$i])?$request->university_state[$i]:NULL;
                    $cuseducation->city_id = isset($request->university_city[$i])?$request->university_city[$i]:NULL;
                    $cuseducation->address = isset($request->university_address[$i])?$request->university_address[$i]:NULL;
                    $cuseducation->save();
                }
            }

            /* IND Education */
            if(count($request->ind_university_id) > 0  && $request->sevis_id[0] != ''){
                // Delete previous data
                ConsultantEducation::whereConsultant_id($id)->whereType(2)->delete();
                
                // Delete previous data
                ConsultantEducation::whereConsultant_id($id)->delete();
                
                $cindedu_count = count($request->ind_university_id);
                for($i=0; $i<$cindedu_count; $i++) {
                    $cindeducation = new ConsultantEducation;
                    $cindeducation->consultant_id = $consultant->id;
                    $cindeducation->type = 2;
                    $cindeducation->sevis_id = NULL;
                    $cindeducation->university_id = isset($request->ind_university_id[$i])?$request->ind_university_id[$i]:NULL;
                    $cindeducation->specialization = isset($request->ind_graduate_in[$i])?$request->ind_graduate_in[$i]:NULL;
                    $cindeducation->graduation_date = isset($request->ind_graduation_date[$i])?dbDateFormat($request->ind_graduation_date[$i]):NULL;
                    $cindeducation->country_id = NULL;
                    $cindeducation->state_id = NULL;
                    $cindeducation->city_id = NULL;
                    $cindeducation->address = isset($request->ind_university_address[$i])?$request->ind_university_address[$i]:NULL;
                    $cindeducation->save();
                }
            }
        }

        /* Save Certificates */
        if(isset($consultant->id)) {
            if(count($request->certification_name) > 0 && $request->certification_name[0] != '') {
                // Delete previous data
                ConsultantCertificate::whereConsultant_id($id)->delete();
                
                $ccrt_count = count($request->certification_name);
                for ($i=0; $i < $ccrt_count; $i++) { 
                    $ccrt = new ConsultantCertificate;
                    $ccrt->consultant_id = $consultant->id;
                    $ccrt->name = isset($consultant->certification_name[$i])?$consultant->certification_name[$i]:"";
                    $ccrt->certificate_id = isset($request->certification_Id[$i])?$request->certification_Id[$i]:"";
                    $ccrt->certified_on = isset($request->completion_date[$i])?dbDateFormat($request->completion_date[$i]):"";
                    $ccrt->validity = isset($request->certification_validity[$i])?dbDateFormat($request->certification_validity[$i]):"";
                    $ccrt->status = isset($request->certificate_status[$i])?$request->certificate_status[$i]:"";
                    $ccrt->save();
                }
            }
        }

        /* Save Experiences */
        if(isset($consultant->id)) {
            /* Resume Experience */
            if(count($request->res_company_name) > 0 && $request->res_company_name[0] != '') {
                // Delete previous data
                ConsultantExperience::whereConsultant_id($id)->whereType(1)->delete();
                
                $crexp_count = count($request->res_company_name);
                for ($i=0; $i < $crexp_count; $i++) { 
                    $crexp = new ConsultantExperience;
                    $crexp->consultant_id = $consultant->id;
                    $crexp->type = 1;
                    $crexp->company_name = isset($request->res_company_name[$i])?$request->res_company_name[$i]:NULL;
                    $crexp->designation = isset($request->res_designation[$i])?$request->res_designation[$i]:NULL;
                    $crexp->experience = isset($request->res_experience[$i])?$request->res_experience[$i]:NULL;
                    $crexp->country_id = isset($request->res_country[$i])?$request->res_country[$i]:NULL;
                    $crexp->state_id = isset($request->res_state[$i])?$request->res_state[$i]:NULL;
                    $crexp->city_id = isset($request->res_city[$i])?$request->res_city[$i]:NULL;
                    $crexp->save();
                }
            }

            /* IT Experience */
            if(count($request->it_company_name) > 0 && $request->it_company_name[0] != '') {
                // Delete previous data
                ConsultantExperience::whereConsultant_id($id)->whereType(2)->delete();
                
                $citexp_count = count($request->it_company_name);
                for ($i=0; $i < $citexp_count; $i++) { 
                    $citexp = new ConsultantExperience;
                    $citexp->consultant_id = $consultant->id;
                    $citexp->type = 2;
                    $citexp->company_name = isset($request->it_company_name[$i])?$request->it_company_name[$i]:NULL;
                    $citexp->designation = isset($request->it_designation[$i])?$request->it_designation[$i]:NULL;
                    $citexp->experience = isset($request->it_experience[$i])?$request->it_experience[$i]:NULL;
                    $citexp->country_id = isset($request->it_country[$i])?$request->it_country[$i]:NULL;
                    $citexp->state_id = isset($request->it_state[$i])?$request->it_state[$i]:NULL;
                    $citexp->city_id = isset($request->it_city[$i])?$request->it_city[$i]:NULL;
                    $citexp->save();
                }
            }
        }

        /* Save Reference */
        if(isset($consultant->id)) {
            if(count($request->client_name) > 0 && $request->client_name[0] != '') {
                // Delete previous data
                ConsultantReference::whereConsultant_id($id)->delete();
                
                $crfr_count = count($request->client_name);
                for ($i=0; $i < $crfr_count; $i++) { 
                    $crfr = new ConsultantReference;
                    $crfr->consultant_id = $consultant->id;
                    $crfr->client_name = isset($request->client_name[$i])?$request->client_name[$i]:NULL;
                    $crfr->supervisor_name = isset($request->supervisor_name[$i])?$request->supervisor_name[$i]:NULL;
                    $crfr->supervisor_email = isset($request->supervisor_email[$i])?$request->supervisor_email[$i]:NULL;
                    $crfr->supervisor_phone = isset($request->supervisor_phone[$i])?$request->supervisor_phone[$i]:NULL;
                    $crfr->supervisor_designation = isset($request->supervisor_designation[$i])?$request->supervisor_designation[$i]:NULL;
                    $crfr->country_id = isset($request->refer_country[$i])?$request->refer_country[$i]:NULL;
                    $crfr->state_id = isset($request->refer_state[$i])?$request->refer_state[$i]:NULL;
                    $crfr->city_id = isset($request->refer_city[$i])?$request->refer_city[$i]:NULL;
                    $crfr->save();
                }                
            }
        }

        /* Save Additional details Trainer & Batch & Guest House */
        if(isset($consultant->id)) {
            // Trainer
            if($request->trainer_id != NULL) {
                $ctrnr = new ConsultantTrainer;
                $ctrnr->consultant_id = $consultant->id;
                $ctrnr->trainer_id = $request->trainer_id;
                $ctrnr->save();
            }
            
            // Batch
            if($request->batch_id != NULL) {
                $ctrnr = new ConsultantBatch;
                $ctrnr->consultant_id = $consultant->id;
                $ctrnr->batch_id = $request->batch_id;
                $ctrnr->save();
            }
            
            // Guesthouse
            if($request->guesthouse_id != NULL) {
                $ctrnr = new ConsultantGuesthouse;
                $ctrnr->consultant_id = $consultant->id;
                $ctrnr->guest_house_id = $request->guesthouse_id;
                $ctrnr->allocated_on = isset($request->guest_aloc_date)?dbDateFormat($request->guest_aloc_date):NULL;
                $ctrnr->leaves_on = NULL;
                $ctrnr->save();
            }
        }

        /* Save Emergencies */
        if(isset($consultant->id)) {
            // US Emergencies
            if(count($request->us_emergency_name) > 0 && $request->us_emergency_name[0] != '') {
                // Delete previous data
                ConsultantContact::whereConsultant_id($id)->whereType(1)->delete();
                
                $cusemc_count = count($request->us_emergency_name);
                for ($i=0; $i < $cusemc_count; $i++) { 
                    $cusemc = new ConsultantContact;
                    $cusemc->consultant_id = $consultant->id;
                    $cusemc->type = 1;
                    $cusemc->name = isset($request->us_emergency_name[$i])?$request->us_emergency_name[$i]:NULL;
                    $cusemc->email = isset($request->us_emergency_email[$i])?$request->us_emergency_email[$i]:NULL;
                    $cusemc->phone = isset($request->us_emergency_mobile[$i])?$request->us_emergency_mobile[$i]:NULL;
                    $cusemc->relation = isset($request->us_emergency_relation[$i])?$request->us_emergency_relation[$i]:NULL;
                    $cusemc->country_id = 231;
                    $cusemc->save();
                }                
            }

            // Overseas Emergencies
            if(count($request->ovr_emergency_name) > 0 && $request->ovr_emergency_name[0] != '') {
                // Delete previous data
                ConsultantContact::whereConsultant_id($id)->whereType(2)->delete();
                
                $cusemc_count = count($request->ovr_emergency_name);
                for ($i=0; $i < $cusemc_count; $i++) { 
                    $cusemc = new ConsultantContact;
                    $cusemc->consultant_id = $consultant->id;
                    $cusemc->type = 2;
                    $cusemc->name = isset($request->ovr_emergency_name[$i])?$request->ovr_emergency_name[$i]:NULL;
                    $cusemc->email = isset($request->ovr_emergency_email[$i])?$request->ovr_emergency_email[$i]:NULL;
                    $cusemc->phone = isset($request->ovr_emergency_mobile[$i])?$request->ovr_emergency_mobile[$i]:NULL;
                    $cusemc->relation = isset($request->ovr_emergency_relation[$i])?$request->ovr_emergency_relation[$i]:NULL;
                    $cusemc->country_id = isset($request->ovr_country[$i])?$request->ovr_country[$i]:NULL;
                    $cusemc->save();
                }                
            }
        }

        /* Save Resume */
        if(isset($consultant->id)) {
            if($request->resume_skills != NULL) {
                $cresume = new ConsultantResume;
                $cresume->consultant_id = $consultant->id;
                $cresume->skills = isset($request->resume_skills)?$request->resume_skills:NULL;
                $cresume->summary = isset($request->profile_summary)?$request->profile_summary:NULL;
                $cresume->resume = isset($request->resume)?$request->resume:NULL;
                $cresume->save();
            }
        }
        
        /* Save Attachments */
        if(isset($consultant->id)) {
            if(count($request->attachment_name) > 0 && $request->attachment_name[0] != '') {
                $catt_count = count($request->attachment_name);
                for ($i=0; $i < $catt_count; $i++) { 
                    $catt = new ConsultantAttachment;
                    $catt->consultant_id = $consultant->id;
                    $catt->name = isset($request->attachment_name[$i])?$request->attachment_name[$i]:NULL;
                    if(isset($request->attachments[$i])) {
                        $file = $request->attachments[$i];
                        $ext = $file->getClientOriginalExtension();
                        $filename = $file->hashName().'.'.$ext;
                        $file->move(public_path('/storage/attachments'), $filename);
                        $catt->file = $filename;
                    }
                    $catt->save();
                }
            }
        }

        // Saving Job
        if($request->status == 'in_job') {
            $job = new Job;
            $job->title = $request->job_title;
            $job->description = 'Saving direct job';
            $job->min_payrate = $request->payrate;
            $job->max_payrate = $request->ot_payrate;
            $job->client_id = $request->client_id;
            $job->created_by = auth()->user()->id;
            $job->save();

            // Save Job vendor
            $jv = new JobVendor;
            $jv->job_id = $job->id;
            $jv->vendor_company_id = $request->vendor_company_id;
            $jv->vendor_id = $request->vendor_id;
            $jv->save();

            // Saving Consultant Jobs data
            $cj = new ConsultantJob;
            $cj->consultant_id = $id;
            $cj->job_id = $job->id;
            $cj->payrate = $request->payrate;
            $cj->ot_payrate = $request->ot_payrate;
            $cj->status = 1; // Applied
            $cj->created_by = auth()->user()->id;
            $cj->save();
        }

        /* Saving Aging for status */
        if(isset($consultant->id)) {
            if($prev_status != $request->status) {
                // Find days he is in the previous status
                $previous = ConsultantAging::whereConsultant_id($id)->latest()->first();
                if(is_null($previous)) {
                    // Save new status
                    $ca = new ConsultantAging;
                    $ca->consultant_id = $consultant->id;
                    $ca->status = $request->status;
                    if($request->status == 'in_job') {
                        $ca->join_date = $request->join_date;
                        $ca->job_id = $job->id;
                    }
                    $ca->save();
                } else {
                    $prev_date = Carbon::parse($previous->created_at);
                    $curr_date = Carbon::now();
                    $diffDays = $prev_date->diffInDays($curr_date);
                    
                    // Update previous status days
                    $previous->days = $diffDays;
                    $previous->active = 0;
                    $previous->update();

                    // making all previous statuses to be inactive
                    ConsultantAging::whereConsultant_id($id)->update(['active'=>0]);

                    $ca = new ConsultantAging;
                    $ca->consultant_id = $consultant->id;
                    $ca->status = $request->status;
                    $ca->save();
                }
            }
        }
        DB::commit();

        // Updating opt live data if user will training or marketing
        if(in_array($request->status, ['in_training', 'on_bench', 'open_bench'])) {
            // Updating to live data from broadcast
            broadcast(new UpdateOptLiveCards());
            broadcast(new UpdateOptPolar());
            broadcast(new UpdateOptLine());
            broadcast(new UpdateOptBar());
            broadcast(new UpdateBenchBar());
            broadcast(new UpdateBenchLine());
            broadcast(new UpdateBenchLiveCards());
            broadcast(new UpdateBenchPolar());
        }

        Alert('success', 'Consultant has been updated successfully');
        return redirect(route('consultants.index'));
    }

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