<?php

namespace App\Modules\Admin\Companies;

use App\Models\Company;
use App\Models\Worker;
use App\Http\Resources\Admin\CompanyWorkerDetails as AdminCompanyWorkerDetailsResource;
use App\Http\Resources\Admin\CompanyWorkers as AdminCompanyWorkersResource;
use App\Http\Resources\Admin\CompanyWorkersSummary as AdminCompanyWorkersSummaryResource;
use App\Traits\SearchTraits;
use App\Traits\SortingTraits;
use Illuminate\Support\Facades\DB;

class CompanyWorkers
{
    use SortingTraits, SearchTraits;

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

        // 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);

        $companyId = $payload->company_id;
        return AdminCompanyWorkersResource::collection(
            Worker::select(
                'workers.id',
                'workers.worker_id',
                'workers.first_name',
                'workers.last_name',
                'workers.zipcode',
                'workers.rate',
                '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')->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);
                    });
                })
                ->whereNotNull('workers.email_verified_at')
                ->where('company_users.company_id', $companyId)
                ->where('company_users.role_id', 5) // 5 = worker
                ->where('worker_experiences.is_worker', true)
                ->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 . '%');
                })
                ->orderBy($sortField, $sortOrder)
                ->paginate(10)
        )->response()->setStatusCode(201);
    }

    public function getDetails($id)
    {
        return AdminCompanyWorkerDetailsResource::collection(
            Worker::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.additional_details',
                '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')
                ->where('workers.id', $id)
                ->where('company_users.role_id', 5) // 5 = worker
                ->where('worker_experiences.is_worker', true)
                ->get()
        )->response()->setStatusCode(201);
    }

    public function getSummaryData($payload)
    {
        $companyId = $payload->company_id;
        return AdminCompanyWorkersSummaryResource::collection(
            Company::select(
                DB::raw('(
                    SELECT COUNT(*)
                    FROM company_users
                    INNER JOIN workers ON workers.id = company_users.worker_id
                    WHERE company_users.company_id = companies.id
                        AND role_id = 5
                        AND email_verified_at IS NOT NULL
                    ) as no_of_employees'
                ), // 5 = Worker
                DB::raw('(SELECT COUNT(DISTINCT worker_id) FROM loan_approved_histories WHERE loan_approved_histories.loaner_company_id = companies.id GROUP BY loaner_company_id) as no_of_loaned_employees'),
                DB::raw('(SELECT COUNT(DISTINCT worker_id) FROM loan_approved_histories WHERE loan_approved_histories.borrower_company_id = companies.id GROUP BY borrower_company_id) as no_of_borrowed_employees'))
                ->where('companies.id', $companyId)
                ->limit(1)
                ->get()
        )->response()->setStatusCode(201);
    }
}