<?php

namespace App\Models;


use App\Models\Expertise;
use App\Models\WorkAreas;
use App\Models\CompanyUser;

use App\Models\WorkerExperience;
use App\Models\CompanyBurdenRate;
use Laravel\Sanctum\HasApiTokens;
use App\Models\CompanyExperience; 
use Illuminate\Support\Facades\Log;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Foundation\Auth\User as Authenticatable;

class Worker extends Authenticatable
{
    use HasApiTokens; 
    use SoftDeletes;
    use HasFactory;

    protected $table = 'workers';
    
    protected $fillable = 
    [
        'worker_id',
        'first_name',
        'last_name',
        'email_address',
        'password',
        'email_verification_code',
        'email_verified_at',
        'profile_picture',
        'is_active',
        'phone_num',
        'enable_2fa',
        'email_code',
        'mobile_code',
        'mobile_code_expiration',
        'blocked_expiration',
        'new_email_address',
        'street_address',
        'city',
        'state',
        'zipcode',
        'rate',
        'flat_rate',
        'overhead_charge',
        'overhead_type',
        'date_hired',
        'additional_details',
        'is_account_disabled',
        'availability_start_date',
        'availability_end_date',
        'resume_file_name',
        'distance',
    ];

    protected $hidden = [
        'password', 'remember_token',
    ];

    protected $casts = [
        'email_verified_at' => 'datetime',
    ];

    public function access()
    {
        return $this->hasMany('App\Models\CompanyUser', 'worker_id', 'id')
            ->join('roles', 'roles.id', '=', 'company_users.role_id')
            ->join('access_roles', 'access_roles.role_id', "=", 'roles.id')
            ->join('access', 'access.id', "=", 'access_roles.access_id');
    }

    public function work_area_lists()
    {
        return $this->hasMany('App\Models\WorkAreaLists', 'worker_id', 'id')
            ->join('work_areas', 'work_areas.id', '=', 'work_area_lists.work_area_id')
            ->where('work_area_lists.worker_type', 'worker')
            ->orderBy('work_areas.name', 'asc');
    }
    public function roles()
    {
        return $this->hasMany('App\Models\CompanyUser', 'worker_id', 'id', 'roles.id')
            ->join('roles', 'roles.id', '=', 'company_users.role_id')
            ->orderBy('roles.id', 'asc');
    }

    public function jobseeker_work_area_lists()
    {
        return $this->hasMany('App\Models\WorkAreaLists', 'worker_id', 'id')
            ->join('work_areas', 'work_areas.id', '=', 'work_area_lists.work_area_id')
            ->select('worker_id', 'name', 'work_area_lists.work_area_id')
            ->where('work_area_lists.worker_type', 'LIKE', 'jobseeker');
    }

    public function get_jobseeker_experiences()
    {
        return $this->hasOne('App\Models\WorkerExperience', 'worker_id', 'id')
            ->leftJoin('experiences', 'experiences.id', '=', 'worker_experiences.experience_id')
            ->leftJoin('expertises', 'expertises.id', '=', 'worker_experiences.expertise_id')
            ->select(
                'worker_id',
                'experience_id',
                'expertise_id',
                'experiences.name as experience_name',
                'expertises.name as expertise_name',
                'worker_experiences.experience as other_experience',
                'worker_experiences.expertise as other_expertise',
            )
            ->where('worker_experiences.is_worker', 'LIKE', 'false');
    }
    
    public function get_worker_experiences()
    {
        return $this->hasOne('App\Models\WorkerExperience', 'worker_id', 'id')
            ->leftJoin('company_experiences', 'company_experiences.id', '=', 'worker_experiences.experience_id')
            ->leftJoin('expertises', 'expertises.id', '=', 'worker_experiences.expertise_id')
            ->select(
                'worker_id',
                'experience_id',
                'expertise_id',
                'company_experiences.name as experience_name',
                'expertises.name as expertise_name',
                'worker_experiences.experience as other_experience',
                'worker_experiences.expertise as other_expertise',
            )
            ->where('worker_experiences.is_worker', 'LIKE', 'true');
    }

    public function rating()
    {
        return $this->hasOne(WorkerRatingAggregate::class ,'worker_id', 'id');
    }

    public function files()
    {
        return $this->hasMany(WorkerFile::class, 'worker_id', 'id');
    }

    /**
	 * Fetch experience hasOneThrough relationship via pivot table. 
	 * 
	 * // @return ? 
	 */
    public function experience()
    {
        return $this->hasOneThrough(
            related       : CompanyExperience::class,
            through       : WorkerExperience::class,
            firstKey      : "worker_id",
            secondKey     : "id",
            localKey      : "id",
            secondLocalKey: "experience_id"
        );
    } 

    /**
	 * Fetch expertise hasOneThrough relationship via pivot table. 
	 * 
	 * // @return ? 
	 */
    public function expertise()
    {
        return $this->hasOneThrough(
            related       : Expertise::class,
            through       : WorkerExperience::class,
            firstKey      : "worker_id",
            secondKey     : "id",
            localKey      : "id",
            secondLocalKey: "expertise_id"
        );
    } 


    /**
	 * Fetch workAreas belongsToMany relationship via pivot table. 
	 * 
	 * // @return ? 
	 */
    public function workAreas()
    {
        return $this->belongsToMany(
			related        : WorkAreas::class,
			table          : "work_area_lists",
			foreignPivotKey: "worker_id",
			relatedPivotKey: "work_area_id",
			parentKey      : "id",
			relatedKey     : "id", 
			relation       : "workAreas"
		);
    } 

    public function companyUser()
    {
        return $this->hasOne(CompanyUser::class, 'worker_id', 'id');
    }

    public function workerBillableRate()
    {
        return $this->hasOneThrough(
            CompanyBurdenRate::class, // Final model
            WorkerExperience::class,  // Intermediate model
            'worker_id',              // Foreign key on WorkerExperience
            'id',                     // Foreign key on CompanyBurdenRate
            'id',                     // Local key on Worker
            'burden_rate_id'          // Local key on WorkerExperience
        );
    }

    public function getWorkerBillableRate($id)
    {
        return Worker::select(
                'workers.worker_id', 
                'workers.rate', 
                'workers.flat_rate', 
                'workers.overhead_charge', 
                'workers.overhead_type', 
                'companies.service_charge', 
                'company_burden_rate.burden_rate', 
                'company_burden_rate.burden_rate_type'
            )
            ->join('worker_experiences', 'worker_experiences.worker_id', '=', 'workers.id')
            ->join('company_burden_rate', 'company_burden_rate.id', '=', 'worker_experiences.burden_rate_id')
            ->join('company_users', 'company_users.worker_id', '=', 'workers.id')
            ->join('companies', 'companies.id', '=', 'company_users.company_id')
            ->where('company_users.worker_id', $id)
            ->first(); 
    }


}