<?php

namespace App\Modules\Company\Employees;

use App\Exports\WorkersExport;
use App\Http\Resources\Company\Worker as WorkerResource;
use App\Traits\SearchTraits;
use App\Traits\SortingTraits;
use App\Traits\UserTraits;
use App\Traits\ValidatorTraits;
use App\Models\Worker as WorkerModel;
use Illuminate\Support\Facades\DB;

class Worker
{
    use SortingTraits, SearchTraits, UserTraits, ValidatorTraits;

    public function getData($payload)
    {
        $companyId = $this->getCurrentUser()->company_id;
        $availabilityStatus = $payload->availability_status;

        // sorting
        $sortField = $this->sortField($payload, 'first_name'); // first_name = default column to search
        $sortOrder = $this->sortOrder($payload, 'asc');

        // searching
        $nameSearch = $this->searchField($payload->first_name);
        $workerIdSearch = $this->searchField($payload->worker_id);
        $expertiseSearch = $payload->expertise_name;
        $experienceSearch = $payload->experience_name;
        $areaOfWorkSearch = $payload->work_area_lists;
        $zipcodeSearch = $this->searchField($payload->zipcode);

        // advance search
        $statusSearch = '';
        $dateSearch = '';
        if (isset($payload->status)) {
            $statusSearch = $payload->status;
            $date = $payload->date;
            $dateSearch = date('Y-m-d', strtotime(str_replace('"', '', $date)));
        }

        // validate query params
        $rules = array(
            'availability_status' => 'required|in:reserved,for-lease,leased',
        );
        $validate = $this->validateRequest($payload, $rules);
        if ($validate) {
            return $validate;
        }

        return WorkerResource::collection(
            WorkerModel::select(
                'workers.id',
                'workers.worker_id',
                'workers.email_address',
                'workers.phone_num',
                'workers.first_name',
                'workers.last_name',
                'workers.profile_picture',
                'workers.street_address',
                'workers.city',
                'workers.state',
                'workers.zipcode',
                'workers.flat_rate as rate',
                'workers.date_hired',
                'workers.resume_file_name as resume',
                'company_users.availability_status',
                'workers.additional_details',
                'worker_experiences.experience_id',
                DB::raw("(CASE WHEN company_experiences.name='Other' AND worker_experiences.experience != null THEN worker_experiences.experience ELSE company_experiences.name END) as experience_name"),
                'worker_experiences.experience as experience',
                'worker_experiences.expertise as expertise',
                'worker_experiences.expertise_id',
                'expertises.name as expertise_name')
                // DB::raw("(CASE WHEN expertises.name='Other' THEN worker_experiences.expertise ELSE expertises.name END) as expertise_name")
                ->join('company_users', 'company_users.worker_id', '=', 'workers.id')
                ->join('worker_experiences', 'worker_experiences.worker_id', '=', 'workers.id')
                ->join('company_experiences', 'company_experiences.id', '=', 'worker_experiences.experience_id')
                ->join('expertises', 'expertises.id', '=', 'worker_experiences.expertise_id')
                ->with('work_area_lists')->whereHas('work_area_lists', function ($query) use ($areaOfWorkSearch) {
                    $query->when(!empty($areaOfWorkSearch), function ($query) use ($areaOfWorkSearch) {
                        return $query->whereIn('work_area_lists.work_area_id', $areaOfWorkSearch);
                    });
                })
                ->where('company_users.company_id', $companyId)
                ->where('company_users.role_id', 5) // 5 = worker
                ->where('worker_experiences.is_worker', 'true')
                ->whereNotNull('workers.email_verified_at')
                ->where('company_users.availability_status', $availabilityStatus)
                ->when(!empty($nameSearch), function ($query) use ($nameSearch) {
                    return $query->where(function ($query) use ($nameSearch) {
                        $query->where('workers.first_name', 'LIKE', $nameSearch . '%')
                            ->orWhere('workers.last_name', 'LIKE', $nameSearch . '%')
                            ->orWhere(DB::raw('CONCAT(workers.first_name, " ", workers.last_name)'), 'LIKE', $nameSearch . '%');
                    });
                })
                ->when(!empty($workerIdSearch), function ($query) use ($workerIdSearch) {
                    return $query->where('workers.worker_id', 'LIKE', $workerIdSearch . '%');
                })
                ->when(!empty($expertiseSearch), function ($query) use ($expertiseSearch) {
                    return $query->whereIn('expertises.id', $expertiseSearch);
                })
                ->when(!empty($experienceSearch), function ($query) use ($experienceSearch) {
                    return $query->whereIn('company_experiences.id', $experienceSearch);
                })
                ->when(!empty($zipcodeSearch), function ($query) use ($zipcodeSearch) {
                    return $query->where('workers.zipcode', 'LIKE', $zipcodeSearch . '%');
                })
                ->when($statusSearch == 'loaned', function ($query) use ($dateSearch) {
                    return $query->leftjoin('loan_borrow_lists', 'loan_borrow_lists.worker_id', '=', 'workers.id')
                        ->where('loan_borrow_lists.date_from', '<=', $dateSearch)
                        ->where('loan_borrow_lists.date_to', '>=', $dateSearch)
                        ->groupBy('workers.id');
                })
                ->when($statusSearch == 'available', function ($query) use ($dateSearch) {
                    return $query->whereNotExists(function ($query) use ($dateSearch) {
                        $query->select(DB::raw(1))
                            ->from('loan_borrow_lists')
                            ->whereRaw('loan_borrow_lists.worker_id = workers.id')
                            ->where('loan_borrow_lists.date_from', '<=', $dateSearch)
                            ->where('loan_borrow_lists.date_to', '>=', $dateSearch);
                    });
                })
                ->orderBy($sortField, $sortOrder)
                ->paginate(20)
        )->response()->setStatusCode(201);
    }

    public function getDetails($id)
    {
        $companyId = $this->getCurrentUser()->company_id;
        return WorkerResource::collection(
            WorkerModel::select(
                'workers.id',
                'workers.worker_id',
                'workers.email_address',
                'workers.phone_num',
                'workers.first_name',
                'workers.last_name',
                'workers.profile_picture',
                'workers.street_address',
                'workers.city',
                'workers.state',
                'workers.zipcode',
                'workers.rate as overheaded',
                'workers.flat_rate as rate',
                'workers.date_hired',
                'workers.distance',
                'workers.resume_file_name as resume',
                'company_users.availability_status',
                'workers.additional_details',
                'worker_experiences.experience_id',
                'worker_experiences.experience',
                'worker_experiences.expertise_id',
                'worker_experiences.expertise as expertise',
                'company_experiences.name as experience_name',
                'expertises.name as expertise_name')
                ->join('company_users', 'company_users.worker_id', '=', 'workers.id')
                ->join('worker_experiences', 'worker_experiences.worker_id', '=', 'workers.id')
                ->join('company_experiences', 'company_experiences.id', '=', 'worker_experiences.experience_id')
                ->join('expertises', 'expertises.id', '=', 'worker_experiences.expertise_id')
                ->with('work_area_lists:work_area_id,worker_id,name')
                ->with('roles:worker_id,role_id,name')
                ->with('files')
                ->where('workers.id', $id)
                ->where('company_users.company_id', $companyId)
                ->where('company_users.role_id', 5) // 5 = worker
                ->where('worker_experiences.is_worker', true)
                ->get()
        )->response()->setStatusCode(201);
    }

    public function getAllEmployeesData($payload)
    {
        $companyId = $this->getCurrentUser()->company_id;
        $sortField = $this->sortField($payload, 'first_name'); // first_name = default column to search
        $sortOrder = $this->sortOrder($payload, 'asc');

        $nameSearch = $this->searchField($payload->first_name);
        $workerIdSearch = $this->searchField($payload->worker_id);
        $expertiseSearch = $payload->expertise_name;
        $experienceSearch = $payload->experience_name;
        $areaOfWorkSearch = $payload->work_area_lists;
        $zipcodeSearch = $this->searchField($payload->zipcode);

        return WorkerResource::collection(
            WorkerModel::select(
                'workers.id',
                'workers.worker_id',
                'workers.email_address',
                'workers.phone_num',
                'workers.first_name',
                'workers.last_name',
                'workers.profile_picture',
                'workers.street_address',
                'workers.city',
                'workers.state',
                'workers.zipcode',
                'workers.flat_rate as rate',
                'workers.date_hired',
                'company_users.availability_status',
                'workers.additional_details',
                'worker_experiences.experience_id',
                DB::raw("(CASE WHEN company_experiences.name='Other' AND worker_experiences.experience != null THEN worker_experiences.experience ELSE company_experiences.name END) as experience_name"),
                'worker_experiences.expertise_id',
                'expertises.name as expertise_name',
                // DB::raw("(CASE WHEN expertises.name='Other' THEN worker_experiences.expertise ELSE expertises.name END) as expertise_name"),
                DB::raw("IFNULL(MIN(loan_borrow_lists.date_from),'N/A')"),
                DB::raw("IFNULL(MAX(loan_borrow_lists.date_to),'N/A')"),
                DB::raw("IF(company_users.company_id = " . $companyId . ",IF(availability_status = 'for-lease','For Lease', 'Reserved' ),'Borrowed') as type"),
                DB::raw("IF(workers.email_verified_at IS NOT NULL, 'yes', 'no') as account_setup")

                )
                ->join('company_users', 'company_users.worker_id', '=', 'workers.id')
                ->join('worker_experiences', 'worker_experiences.worker_id', '=', 'workers.id')
                ->join('company_experiences', 'company_experiences.id', '=', 'worker_experiences.experience_id')
                ->join('expertises', 'expertises.id', '=', 'worker_experiences.expertise_id')
                ->with('roles:worker_id,role_id,name')
                ->leftjoin('loan_borrow_lists', 'loan_borrow_lists.worker_id', '=', 'workers.id')
                ->where('company_users.company_id', $companyId)
                ->where('company_users.role_id', 5) // 5 = worker
                ->where('worker_experiences.is_worker', true)
               // ->whereNotNull('workers.email_verified_at')
                ->with('work_area_lists')->whereHas('work_area_lists', function ($query) use ($areaOfWorkSearch) {
                    $query->when(!empty($areaOfWorkSearch), function ($query) use ($areaOfWorkSearch) {
                        return $query->whereIn('work_area_lists.work_area_id', $areaOfWorkSearch);
                    });
                })
                ->when(!empty($nameSearch), function ($query) use ($nameSearch) {
                    return $query->where(function ($query) use ($nameSearch) {
                        $query->where('workers.first_name', 'LIKE', $nameSearch . '%')
                            ->orWhere('workers.last_name', 'LIKE', $nameSearch . '%')
                            ->orWhere(DB::raw('CONCAT(workers.first_name, " ", workers.last_name)'), 'LIKE', $nameSearch . '%');
                    });
                })
                ->when(!empty($workerIdSearch), function ($query) use ($workerIdSearch) {
                    return $query->where('workers.worker_id', 'LIKE', $workerIdSearch . '%');
                })
                ->when(!empty($expertiseSearch), function ($query) use ($expertiseSearch) {
                    return $query->whereIn('worker_experiences.expertise_id', $expertiseSearch);
                })
                ->when(!empty($experienceSearch), function ($query) use ($experienceSearch) {
                    return $query->whereIn('worker_experiences.experience_id', $experienceSearch);
                })
                ->when(!empty($zipcodeSearch), function ($query) use ($zipcodeSearch) {
                    return $query->where('workers.zipcode', 'LIKE', $zipcodeSearch . '%');
                })
                ->orWhere(function ($q) use ($companyId) {
                    $q->where('loan_borrow_lists.borrower_company_id', $companyId)
                        ->whereRaw('loan_borrow_lists.date_to >= CURDATE()');
                })
                ->orderBy($sortField, $sortOrder)
                ->groupBy('workers.id')
                ->paginate(20)
        )->response()->setStatusCode(201);
    }

    public function borrowerView($id)
    {
        return WorkerResource::collection(
            WorkerModel::select(
                'workers.id',
                'workers.worker_id',
                'workers.zipcode',
                'search.hourly_rate as rate',
                'company_users.company_id',
                'workers.date_hired',
                'workers.additional_details',
                'workers.resume_file_name as resume',
                'workers.created_at', 'workers.updated_at', 'workers.deleted_at',
                'worker_experiences.experience_id', 'company_experiences.name as experience_name',
                'worker_experiences.expertise_id', 'expertises.name as expertise_name')
                ->join('company_users', 'workers.id', '=', 'company_users.worker_id')
                ->join('search', 'search.worker_id', '=', 'workers.id')
                ->join('worker_experiences', 'worker_experiences.worker_id', '=', 'company_users.worker_id')
                ->join('company_experiences', 'company_experiences.id', '=', 'worker_experiences.experience_id')
                ->join('expertises', 'expertises.id', '=', 'worker_experiences.expertise_id')
                ->with('work_area_lists:work_area_id,worker_id,name')
                ->with('rating')
                ->with('files')
                ->where('search.id', $id)
                ->get()
        )->response()->setStatusCode(201);
    }

    public function exportWorkersData($payload)
    {
        $filters = $payload->all();
        $headers = [
            'Access-Control-Allow-Origin' => '*',
            'Access-Control-Allow-Methods' => 'POST, GET, OPTIONS, PUT, DELETE',
            'Access-Control-Allow-Credentials' => 'true',
            'Access-Control-Max-Age' => '86400',
            'Access-Control-Allow-Headers' => 'Content-Type, Authorization, X-Requested-With, api_token',
        ];
        return (new WorkersExport($this->getCurrentUser()->company_id, $filters))->download('Workers.csv');
    }

}