<?php

namespace App\Http\Controllers;

use App\Models\job;
use App\Models\JobApplication;
use App\Models\jobAttachment;
use App\Models\jobCategory;
use App\Models\jobReview;
use App\Models\User;
use Carbon\Carbon;
use Illuminate\Http\Request;
use App\Notifications;
use App\Notifications\jobContact;
use Illuminate\Notifications\Notification;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Notification as FacadesNotification;
use Illuminate\Support\Facades\Storage;
use PDO;
use stdClass;

class JobController extends Controller
{
    public function getJobMeta($id)
    {
        $user = User::find($id);

        $pdo = DB::connection()->getPdo();

        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

        $stmt = $pdo->prepare('CALL get_job_categories()');

        $stmt->execute();

        $categories = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');

        $stmt->nextRowset();

        $sub_categories = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');

        $stmt->nextRowset();

        $benefits = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');

        $stmt->nextRowset();

        $license_types = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');

        $stmt->nextRowset();

        $experience_years = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');

        $stmt->nextRowset();

        $salary_ranges  = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');

        $stmt->nextRowset();

        $cities = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');

        $stmt->nextRowset();

        $states  = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');


        return response()->json([
            'categories' => $categories,
            'sub_categories' => $sub_categories,
            'benefits' => $benefits,
            'license_types' => $license_types,
            'experience_years' => $experience_years,
            'salary_ranges' => $salary_ranges,
            'cities' => $cities,
            'states' => $states
        ],200,[], JSON_NUMERIC_CHECK);
    }

    public function saveJob(Request $request,$id)
    {
        if($request->input('id') != null)
        {
            $job = Job::find($request->input('id'));
        }
        else
        {
            $job = new Job();
        }


        $job_categories_array = $request->input('job_categories');
        $job_sub_categories_array = $request->input('job_sub_categories');
        $job_benefits_array = $request->input('job_benefits');


        $job->company_name = $request->input("company_name");	
        $job->company_website = $request->input("company_website");	
        $job->job_title = $request->input("job_title");	
        $job->job_category_id = $request->input("job_category_id");	
        $job->job_sub_category_id = $request->input("job_sub_category_id");	
        $job->state = $request->input("state") == 'Other' ? $request->input("state1") : $request->input("state");
        $job->state1 = $request->input("state1");
        $job->city = $request->input("city") == 'Other' ? $request->input("city1") : $request->input("city");
        $job->city1 = $request->input("city1");
        $job->no_of_opening = $request->input("no_of_opening");
        $job->job_shift = $request->input("job_shift");	
        $job->job_tags = $request->input("job_tags");	
        $job->job_description = $request->input("job_description");	
        $job->job_responsibilities = $request->input("job_responsibilities");	
        $job->qualification = $request->input("qualification");	
        $job->license_type = $request->input("license_type");
        $job->license_type1 = $request->input("license_type1");
        $job->experience_years = $request->input("experience_years");
        $job->experience_description = $request->input("experience_description");	
        $job->salary = $request->input("salary");
        $job->posted_by_title = $request->input("posted_by_title");	
        $job->posted_by_first_name = $request->input("posted_by_first_name");	
        $job->posted_by_last_name = $request->input("posted_by_last_name");	
        $job->posted_by_designation = $request->input("posted_by_designation");	
        $job->posted_by_phone_number = $request->input("posted_by_phone_number");	
        $job->posted_by_ext = $request->input("posted_by_ext");	
        $job->posted_by_mobile_no = $request->input("posted_by_mobile_no");	
        $job->posted_by_email = $request->input("posted_by_email");	
        $job->is_top_job = $request->input("is_top_job");	
        $job->is_featured_job = $request->input("is_featured_job");	
        $job->user_id = $id;

        // $job->job_benefits!: Array<JobBenefit>

        // $job->job_categories!: Array<jobCategory>
        // $job->job_sub_categories!: Array<jobSubCategory>

        // return response()->json(['sucess' => $job],200);

        // return response()->json(['test' => $job_benefits_array],500);
    
        $job->save();




        $job->jobBenefits()->detach();
        if(sizeof($job_benefits_array) > 0)
        {
            $job_benefits = array();
            foreach($job_benefits_array as $b)
            {
                $benefit = new stdClass();
                $benefit->benefit_id = $b['benefit_id'];
                $benefit->details = isset($b['details']) ? $b['details'] : null;

                $job_benefits [] = (array) $benefit;
            }
            $job->jobBenefits()->attach($job_benefits);
        }

        $job->jobCategories()->detach();
        if(sizeof($job_categories_array) > 0)
        {
            $job_categories = array();
            foreach($job_categories_array as $bca)
            {
                $category = new \stdClass();
                $category->category_id = $bca['category_id'];

                $job_categories [] = (array)$category;
            }
            $job->jobCategories()->attach($job_categories);
        }

        $job->jobSubCategories()->detach();
        if(sizeof($job_sub_categories_array) > 0)
        {
            $job_sub_categories = array();
            foreach($job_sub_categories_array as $bsca)
            {
                $sub_category = new \stdClass();
                $sub_category->sub_category_id = $bsca['sub_category_id'];

                $job_sub_categories [] = (array)$sub_category;
            }
            $job->jobSubCategories()->attach($job_sub_categories);
        }

        return response()->json(['sucess' => 'saved'],200);
    }

    public function getJobs($page_no,$id)
    {

        $id = $id == 'undefined' || $id == 'null' ? 0 : $id;

        $pdo = DB::connection()->getPdo();

        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);


        $stmt = $pdo->prepare('CALL get_jobs(:page_no,:puserid)');

        $stmt->execute(array('page_no' => $page_no,'puserid' => $id));

        $job_count = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');
        $job_count = $job_count != null ? $job_count[0]->job_count : 0;

        $stmt->nextRowset();

        $jobs = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');


        return response()->json([
            'jobs' => $jobs,
            'job_count' => $job_count 
        ],200,[], JSON_NUMERIC_CHECK) ;
    }

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

        $application = new JobApplication();

        $application->user_id = $request->input('user_id');	
        
        $application->job_id = $request->input('job_id');		

		$application->personal_title = $request->input('personal_title');		

		$application->personal_first_name = $request->input('personal_first_name');		

		$application->personal_last_name = $request->input('personal_last_name');		

		$application->personal_email = $request->input('personal_email');		

		$application->personal_home_phone_number = $request->input('personal_home_phone_number');	

		$application->personal_mobile_number = $request->input('personal_mobile_number');	

		$application->personal_address_1 = $request->input('personal_address_1');		

		$application->personal_address_2 = $request->input('personal_address_2');		

		$application->personal_city = $request->input('personal_city');		

		$application->personal_state = $request->input('personal_state');
        
        $application->personal_state1 = $request->input('personal_state1');

		$application->personal_country_id = $request->input('personal_country_id');		

		$application->personal_postal_code = $request->input('personal_postal_code');		

		$application->eexp_occupation = $request->input('eexp_occupation');		

		$application->eexp_exp_years = $request->input('eexp_exp_years');		

		$application->eexp_other_held_position = $request->input('eexp_other_held_position');		

		$application->eexp_expected_position = $request->input('eexp_expected_position');		

		$application->eexp_education = $request->input('eexp_education');		

		$application->eexp_certification = $request->input('eexp_certification');		

		$application->ldd_license_type = $request->input('ldd_license_type');		

		$application->ldd_exp_years = $request->input('ldd_exp_years');		

		$application->ldd_exp_years_canada = $request->input('ldd_exp_years_canada');		

		$application->ldd_exp_years_usa = $request->input('ldd_exp_years_usa');		

		$application->ldd_is_company_driver = $request->input('ldd_is_company_driver');

        $application->ldd_is_owner_operator = $request->input('ldd_is_owner_operator');

        $application->ldd_operation_type = $request->input('ldd_operation_type');

		$application->ldd_is_usa_entry_allowed = $request->input('ldd_is_usa_entry_allowed');		

		$application->ldd_is_van_exp = $request->input('ldd_is_van_exp');		

		$application->ldd_is_reefer_exp = $request->input('ldd_is_reefer_exp');		

		$application->ldd_is_tanker_exp = $request->input('ldd_is_tanker_exp');		

		$application->ldd_is_trains_exp = $request->input('ldd_is_trains_exp');		

		$application->ldd_is_other_exp = $request->input('ldd_is_other_exp');
        
        $application->ldd_is_ontario = $request->input('ldd_is_ontario');		

		$application->ldd_is_quebec = $request->input('ldd_is_quebec');		

		$application->ldd_is_british_columbia = $request->input('ldd_is_british_columbia');		

		$application->ldd_is_prairie = $request->input('ldd_is_prairie');		

		$application->ldd_is_usa = $request->input('ldd_is_usa');		

		// $application->resume_encrypted_name = $request->input('resume_encrypted_name');		

		// $application->resume_original_name = $request->input('resume_original_name');		

		// $application->driving_letter_encrypted_name = $request->input('driving_letter_encrypted_name');		

		// $application->driving_letter_original_name = $request->input('driving_letter_original_name');		

		$application->job_comments = $request->input('job_comments');		

		$application->created_by = $id;

        $application->status_id = 1;	

        $application->save();

        $job_application_folder_path = $application->id.'/';
        
        $folder_path =  'public/uploads/jobapplication/' . $job_application_folder_path ;

        $application->resume_encrypted_name = $request->input('resume_encrypted_name');

        if($application->resume_encrypted_name != null)
        {

            preg_match("/data:image\/(.*?);/", $application->resume_encrypted_name, $file_extension) ||
            preg_match("/data:text\/(.*?);/", $application->resume_encrypted_name, $file_extension) ||
            preg_match("/data:application\/(.*?);/", $application->resume_encrypted_name, $file_extension) ||
            preg_match("/data:video\/(.*?);/", $application->resume_encrypted_name, $file_extension);
            @list($type, $file_data) = explode(';', $application->resume_encrypted_name);
            @list(, $file_data) = explode(',', $file_data);
            
    
            $fileName = 'file_' . time() . '_' . rand(pow(10, 3 - 1), pow(10, 3) - 1) . '.' . $file_extension[1];
    
            Storage::disk('local')->put($folder_path.'/'. $fileName, base64_decode($file_data));
    
            $application->resume_encrypted_name ='uploads/jobapplication/' .$job_application_folder_path. '/'.$fileName;

            $application->resume_original_name = $request->input('resume_original_name');
    
            $application->save();
        }

        $application->driving_letter_encrypted_name = $request->input('driving_letter_encrypted_name');

        if($application->driving_letter_encrypted_name != null)
        {


            preg_match("/data:image\/(.*?);/", $application->driving_letter_encrypted_name, $file_extension) ||
            preg_match("/data:text\/(.*?);/", $application->driving_letter_encrypted_name, $file_extension) ||
            preg_match("/data:application\/(.*?);/", $application->driving_letter_encrypted_name, $file_extension) ||
            preg_match("/data:video\/(.*?);/", $application->driving_letter_encrypted_name, $file_extension);
            @list($type, $file_data) = explode(';', $application->driving_letter_encrypted_name);
            @list(, $file_data) = explode(',', $file_data);
            
    
            $fileName = 'file_' . time() . '_' . rand(pow(10, 3 - 1), pow(10, 3) - 1) . '.' . $file_extension[1];
    
            Storage::disk('local')->put($folder_path.'/'. $fileName, base64_decode($file_data));
    
            $application->driving_letter_encrypted_name ='uploads/jobapplication/' .$job_application_folder_path. '/'.$fileName;
            $application->driving_letter_original_name = $request->input('driving_letter_original_name');
    
            $application->save();
        }

        return response()->json(['success' => 'save successfully',
    'id' => $application->id],201);
    }

    public function getJobsListing($page_no,$id)
    {
        $pdo = DB::connection()->getPdo();

        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);


        $stmt = $pdo->prepare('CALL get_jobs_listing(:puserid,:page_no)');

        $stmt->execute(array('puserid' => $id,'page_no' => $page_no));

        $job_count = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');
        $job_count = $job_count != null ? $job_count[0]->job_count : 0;

        $stmt->nextRowset();

        $jobs = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');


        return response()->json([
            'jobs' => $jobs,
            'job_count' => $job_count 
        ],200,[], JSON_NUMERIC_CHECK) ;
    }

    public function getJob($id,$user_id)
    {
        $job = Job::find($id);
        $job_categories = $job->jobCategories()->select('id as category_id','name')->get();
        $job_sub_categories = $job->jobSubCategories()->select('id as sub_category_id','name')->get();
        $job_benefits = $job->jobBenefits()->get();
     

        $pdo = DB::connection()->getPdo();

        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

        $user = User::find($user_id);


        $stmt = $pdo->prepare('CALL get_job_categories()');
        

        $stmt->execute();

        $categories = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');

        $stmt->nextRowset();

        $sub_categories = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');

        $stmt->nextRowset();

        $benefits = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');

        $stmt->nextRowset();

        $license_types = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');

        $stmt->nextRowset();

        $experience_years = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');

        $stmt->nextRowset();

        $salary_ranges  = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');

        $stmt->nextRowset();

        $cities = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');

        $stmt->nextRowset();

        $states  = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');


        $benefits_array = array();

        if(sizeof($benefits) > 0)
        {
            foreach($benefits as $b)
            {
                $benefit = new stdClass();
                $benefit = $b;

                $benefit_obj = collect($job_benefits)->where('id',$b->benefit_id)->first();

                if($benefit_obj != null)
                {
                    $benefit->is_included = true;
                    $benefit->details = $benefit_obj->pivot->details;
                }
                else
                {
                    $benefit->is_included = false;
                    $benefit->details = null;
                }
                $benefits_array [] = (array) $benefit;
            }
        }

        return response()->json([
            'job' => $job, 
            'job_categories' => $job_categories,
            'job_sub_categories' => $job_sub_categories,
            'job_benefits' => $benefits_array,
            'categories' => $categories,
            'sub_categories' => $sub_categories,
            'license_types' => $license_types,
            'experience_years' => $experience_years,
            'salary_ranges' => $salary_ranges,
            'cities' => $cities,
            'states' => $states
        ],200,[], JSON_NUMERIC_CHECK);
    }


    public function deleteJob($id)
    {
        $job = Job::find($id);

        $job_folder_path = $job->id.'/';

        $job->jobCategories()->detach();

        $job->jobSubCategories()->detach();

        $job->jobApplications()->delete();
        
        Storage::disk('local')->delete('public/uploads/jobapplication/'.$job_folder_path);

        $job->delete();

        return response()->json(['success' => 'deleted successfully'],201);
    }

    public function getJobApplications($job_id,$user_id)
    {
        $pdo = DB::connection()->getPdo();

        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

        $stmt = $pdo->prepare('CALL get_job_applications(:job_id,:user_id)');
        

        $stmt->execute(array('job_id' => $job_id,'user_id' => $user_id));

        $applications = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');

        return response()->json([
            'applications' => $applications,
        ],200,[], JSON_NUMERIC_CHECK);       
    }

    public function getJobApplication($application_id)
    {
        $application = JobApplication::find($application_id);

        return response()->json([
            'application' => $application,
        ],200,[], JSON_NUMERIC_CHECK);       
    }

    public function getJobDetails($id,$user_id)
    {

        $user_id = $user_id == 'undefined' || $user_id == 'null' ? 0 : $user_id;

        $pdo = DB::connection()->getPdo();

        $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

        $stmt = $pdo->prepare('CALL get_job_details(?,?)');

        $stmt->execute(array($id,$user_id));
        
        $job = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');
        $job = $job[0];
        $stmt->nextRowset();
        

        $job_benefits = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');

        $stmt->nextRowset();

        $related_jobs = $stmt->fetchAll(PDO::FETCH_CLASS, 'stdClass');


        $job_obj = new stdClass();

        $job_obj = $job;

        $job_obj->tags = explode(',', $job->job_tags);

        return response()->json([
            'job' => $job_obj,
            'job_benefits' => $job_benefits,
            'related_jobs' => $related_jobs
        ],200,[], JSON_NUMERIC_CHECK);       

    }

    public function changeJobApplicationStatus(Request $request,$id)
    {
        $application = JobApplication::find($request->input('id'));

        $application->status_id = $request->input('status_id');
        $application->updated_by = $id;

        $application->save();
    }


}