<?php

namespace App\Modules\Company\Loan\Requests;

use App\Models\Worker;
use App\Models\AssignedWorkers;
use App\Models\Availabilities;
use App\Models\BorrowApproved;
use App\Models\BorrowDenied;
use App\Models\BorrowPendingWorkers;
use App\Models\BorrowRequest;
use App\Models\Notifications;
use App\Models\InboundBillingRequests;
use App\Models\LoanApproved;
use App\Models\LoanBorrowList;
use App\Models\LoanDenied;
use App\Models\LoanPendingWorkers;
use App\Models\LoanRequest;
use App\Models\OutboundBillingRequests;

use App\Events\Company\CreateNotificationEvent;
use App\Events\Company\RequestWorkerApprovedEvent;
use App\Models\WorkerRequest;
use App\Models\WorkerRequestApplication;
use App\Traits\CreateUserActionTraits;
use App\Traits\CreateMobileNotificationTraits;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;
use Carbon\Carbon;
use DateInterval;
use DatePeriod;
use DateTime;

class ApprovePendingRequest
{
    use CreateUserActionTraits, CreateMobileNotificationTraits;

    public function approve($payload)
    {
        $selectedpendingworkers = $payload->selectedpendingworkers;
        $workerCompanyIdForLogs = [];

        foreach ($selectedpendingworkers as $selectedpendingworker) 
        {
            // Start transaction
            DB::beginTransaction();

            // fetch loan request details
            $id              = $selectedpendingworker['id'];                 // loan_pending_workers primary id
            $pendingId       = $selectedpendingworker['id'];
            $companyWorkerID = $selectedpendingworker['company_worker_id'];
            $loanRequest     = $this->fetchLoanRequestDetails($id);

            $dateFrom = $loanRequest[0]->date_from;
            $dateTo   = $loanRequest[0]->date_to;
            
            $dateToday = Carbon::now()->format('Y-m-d');
            $dateEnd   = Carbon::parse($dateTo)->format('Y-m-d');

            // Check if date request duration is past date
            if($dateToday > $dateEnd)
            {
                return response()->json(
                    [
                        'error'     => 'Unable to proceed. The worker '. $companyWorkerID . ' request date is already in the past. Please decline the request.',
                        'dateToday' => $dateToday,
                        'dateEnd'   => $dateEnd,
                    ]
                )->setStatusCode(201);
            }

            // Check if worker was still pending and not yet cancelled  
            $checkIfWorkerIsPendingAndNotYetCancelled = $this->checkIfWorkerIsPendingAndNotYetCancelled($id);
            
            if (!$checkIfWorkerIsPendingAndNotYetCancelled) 
            {
                return response()->json(['error' => 'Selected worker already loaned.'])->setStatusCode(201);
            }

            // Validate Conflicting Schedule from Whiteboard
            $workerId = $loanRequest[0]->worker_id;

            $loanerCompanyId          = $loanRequest[0]->loaner_company_id;
            $borrowerCompanyId        = $loanRequest[0]->borrower_company_id;
            $validateConflictSchedule = $this->validateConflictSchedule($workerId, $dateFrom, $dateTo);
            
            if (!$validateConflictSchedule->isEmpty()) 
            {
                return response()->json(
                    [
                        'error' => 'Conflicting schedule in whiteboard.',
                        'worker' => $validateConflictSchedule,
                    ]
                )->setStatusCode(201);
            }

            //validate if the date requested is already approved
            $checkIfActivelyApproved = $this->checkIfActivelyApproved($workerId, $dateFrom, $dateTo);

            if($checkIfActivelyApproved)
            {
                $errorMessage = 'The worker '. $companyWorkerID . ' is already loaned out on the requested date. Please declined the request.';
                return response()->json(['error' => $errorMessage])->setStatusCode(201);
            }   

            // Check if date request duration is past date

            // Fetch Worker rate from Availabilities Table
            $workerOriginalRate = $this->fetchWorkerOriginalRate($workerId)->rate;

            // save availabilities data to availabilities table
            $availabilitiesTransaction = $this->saveAvailabilities($workerId, 'borrowed', $loanRequest[0]->rate, $workerOriginalRate, $dateFrom, $dateTo, $loanerCompanyId, $borrowerCompanyId);

            // START OF APPROVING LOAN
            $loanPendingWorkerTransaction = $this->updateLoanPendingWorkerStatus($id); // Change status of loan_pending_workers table.

            // Add approved loan pending to loan_approved_histories table
            $loanerCompanyId   = $loanRequest[0]->loaner_company_id;
            $borrowerCompanyId = $loanRequest[0]->borrower_company_id;
            $dateRequested     = $loanRequest[0]->date_requested;
            $dateFrom          = $loanRequest[0]->date_from;
            $dateTo            = $loanRequest[0]->date_to;
            $rate              = $loanRequest[0]->rate;
            $originalRate      = $loanRequest[0]->original_rate;
            $workerId          = $loanRequest[0]->worker_id;
            $requesterNote     = $loanRequest[0]->requester_note;
            $requestContactID  = $loanRequest[0]->request_contact_id;
            $approverId        = Auth::user()->id;
            $approverNote      = $payload->approver_note;

            // preparing borrow pending worker to be saved on database
            $selectedPendingWorkerData = array(
                'loaner_company_id' => $loanerCompanyId,
                'borrower_company_id' => $borrowerCompanyId,
                'date_requested' => $dateRequested,
                'date_from' => $dateFrom,
                'date_to' => $dateTo,
                'rate' => $rate,
                'original_rate' => $originalRate,
                'worker_id' => $workerId,
                'requester_note' => $requesterNote,
                'approver_id' => $approverId,
                'approver_note' => $approverNote,
                'request_contact_id' => $requestContactID,
            );
            $loanApprovedTransaction = $this->savePendingWorkerToLoanApprovedHistories($selectedPendingWorkerData);

            // START OF APPROVING BORROW
            $id = $selectedpendingworker['id']; // borrow_pending_workers primary id
            $borrowPendingWorkerTransaction = $this->updateBorrowPendingWorkerStatus($id); // Change status of borrow_pending_workers table.

            // Add approved borrow pending worker to borrow_approved_histories table
            $borrowRequest     = $this->fetchBorrowRequestDetails($id);
            $loanerCompanyId   = $loanerCompanyId;                       // from loan_request
            $borrowerCompanyId = $borrowerCompanyId;                     // from loan_request
            $dateRequested     = $borrowRequest[0]->date_requested;
            $requested_by      = $borrowRequest[0]->requested_by;
            $dateFrom          = $borrowRequest[0]->date_from;
            $dateTo            = $borrowRequest[0]->date_to;
            $rate              = $borrowRequest[0]->rate;
            $originalRate      = $borrowRequest[0]->original_rate;
            $workerId          = $borrowRequest[0]->worker_id;
            $requesterNote     = $borrowRequest[0]->requester_note;
            $requestContactID  = $borrowRequest[0]->request_contact_id;
            $approverId        = Auth::user()->id;
            $approverNote      = $payload->approver_note;

            // preparing borrow pending worker to be saved on database
            $selectedPendingWorkerData = array(
                'loaner_company_id'   => $loanerCompanyId,
                'borrower_company_id' => $borrowerCompanyId,
                'date_requested'      => $dateRequested,
                'requested_by'        => $requested_by,
                'date_from'           => $dateFrom,
                'date_to'             => $dateTo,
                'rate'                => $rate,
                'original_rate'       => $originalRate,
                'worker_id'           => $workerId,
                'requester_note'      => $requesterNote,
                'approver_id'         => $approverId,
                'approver_note'       => $approverNote,
                'request_contact_id'  => $requestContactID,
            );
            $borrowApprovedTransaction = $this->savePendingWorkerToBorrowApprovedHistories($selectedPendingWorkerData);

            // START OF declining loan/borrow with the conflicting worker and duration of request
            $id                                              = $borrowRequest[0]->id;                                                                                // this id will be used for exemption of declining of loan/borrow request
            $dateFrom                                        = $borrowRequest[0]->date_from;
            $dateTo                                          = $borrowRequest[0]->date_to;
            $workerId                                        = $borrowRequest[0]->worker_id;
            $declineWorkerWithConflictingDurationTransaction = $this->declineWorkerWithConflictingDuration($id, $dateFrom, $dateTo, $workerId, $borrowerCompanyId);

            // START OF adding data in tables: loan_borrow_lists | inbound_billing_requests | outbound_billing_requests
            $dateToday         = Carbon::now()->format('Y-m-d-h-i-s');
            $loanerCompanyId   = $loanerCompanyId;                      // from loan_request
            $borrowerCompanyId = $borrowerCompanyId;                    // from loan_request
            $workerId          = $workerId;                             // from loan_pending_workers
            $rate              = $rate;                                 // from loan_pending_workers
            $originalRate      = $originalRate;                         // from loan_pending_workers
            $dateFrom          = $dateFrom;                             // from loan_pending_workers
            $dateTo            = $dateTo;                               // from loan_pending_workers

            $start                  = new DateTime($dateFrom);
            $dateTo                 = date('Y-m-d', strtotime($dateTo . ' + 1 day'));  // add 1 day for date range
            $end                    = new DateTime($dateTo);
            $interval               = new DateInterval('P1D');                         // 1 day interval
            $dateRange              = new DatePeriod($start, $interval, $end);
            $weekNumber             = 0;
            $weeks                  = array();
            $cut_off_dates_per_week = array();
            $days_count_per_week    = array();
            $count_days             = 0;                                               // for days column in loan_borrow_lists table (count days per week)
            
            foreach ($dateRange as $date) 
            {
                $weeks[$weekNumber][] = $date->format('Y-m-d'); // days of work
                $cut_off_dates_per_week[$weekNumber]['cut_off_date'] = date('Y-m-d', strtotime("friday", strtotime($date->format('Y-m-d')))); // cut-off date
                $count_days++;
                $days_count_per_week[$weekNumber]['days_count'] = $count_days; // cut-off date

                // check if day of the week is Friday or 5 then increment it to 1. Because Friday is the cut-off date
                if ($date->format('w') == 5) {
                    $count_days = 0; // reset count of the days for another week
                    $weekNumber++;
                }
            }

            for ($week = 0; $week < count($weeks); $week++) 
            {
                // convert dates of the week as json to be saved on days column in inbound_billing_requests
                $days = [];
                foreach ($weeks[$week] as $day) {
                    $days[] = [
                        'date' => $day,
                        'hours' => 0,
                    ];
                }

                // loan_borrow_list data
                $invoice_no            = 'IVN-' . $dateToday . '-' . Str::random(10);
                $duration              = count($days_count_per_week[$week]);
                $dateFrom              = $weeks[$week][0];
                $days_of_week_reversed = array_reverse($weeks[$week]);
                $dateTo                = $days_of_week_reversed[0];
                $cut_off_date          = $cut_off_dates_per_week[$week]['cut_off_date'];

                // preparing loan_borrow_list data to be saved on database
                $loan_borrow_list_data = array(
                    'invoice_no'          => $invoice_no,
                    'loaner_company_id'   => $loanerCompanyId,
                    'borrower_company_id' => $borrowerCompanyId,
                    'worker_id'           => $workerId,
                    'rate'                => $rate,
                    'original_rate'       => $originalRate,
                    'tax'                 => $this->getGovernmentTax(),
                    'duration'            => $duration,
                    'date_from'           => $dateFrom,
                    'date_to'             => $dateTo,
                    'status'              => 'Pending',
                );

                $loanBorrowListTransaction = $this->saveLoanBorrowList($loan_borrow_list_data);
                $loanBorrowListsId         = $loanBorrowListTransaction->id;                     // new loan_borrow_list (primary key)

                // preparing inbound_billing_requests data to be saved on database
                $inbound_billing_request_data = array(
                    'loan_borrow_lists_id' => $loanBorrowListsId,
                    'days'                 => json_encode($days),
                    'other_expense'        => 0,
                    'outstanding_balance'  => 0,
                    'total_billing'        => 0,
                    'initial_date'         => $dateFrom,
                    'cutoff_date'          => $cut_off_date,
                );

                $inboundBillingRequestTransaction = $this->saveInboundBillingRequest($inbound_billing_request_data);

                // preparing loan_borrow_list data to be saved on database
                $outboundBillingRequestData = array(
                    'loan_borrow_lists_id' => $loanBorrowListsId,
                    'other_expense'        => 0,
                    'loaner_status'        => 'Unpaid',
                    'conx_status'          => 'Unpaid',
                    'initial_date'         => $dateFrom,
                    'cutoff_date'          => $cut_off_date,
                );

                // save outbound_billing_request data to outbound_billing_requests table
                $outboundBillingRequestTransaction = $this->saveOutboundBillingRequest($outboundBillingRequestData);
            }

            // Company Worker ID to be saved in logs
            array_push($workerCompanyIdForLogs, $selectedpendingworker['company_worker_id']);


            // If there's an error or queries don't do their job, rollback!
            if (!$loanPendingWorkerTransaction || !$loanApprovedTransaction ||
                !$borrowPendingWorkerTransaction || !$borrowApprovedTransaction ||
                !$declineWorkerWithConflictingDurationTransaction ||
                !$loanBorrowListTransaction || !$inboundBillingRequestTransaction ||
                !$outboundBillingRequestTransaction ||
                !$availabilitiesTransaction) 
            {
                DB::rollBack();
                return response()->json(['error' => 'Server error.']);
            } 
            else 
            {
                DB::commit();
                event(new CreateNotificationEvent("update", "request", $pendingId));
                event(new RequestWorkerApprovedEvent($pendingId));

                $mobileNotificationData = array(
                    'worker_id' => $workerId,
                    'message'   => 'You have been successfully loaned out to another company named '. ucfirst($loanRequest[0]->borrower_company_name) . " with duration date of " . $dateFrom . " to " . $dateTo . ".",
                    'type'      => 'worker',
                    'link'      => '/worker/tasks',
                );
                
                $this->createMobileNotification($mobileNotificationData);

                //! DELETE WORKER REQUEST
                $this->deleteWorkerRequest(
                    workerId         : $workerId,
                    loanerCompanyId  : $loanerCompanyId,
                    borrowerCompanyId: $borrowerCompanyId
                );
            }
        }

        // Prepare log description
        if (count($workerCompanyIdForLogs) > 1) 
        {
            sort($workerCompanyIdForLogs);
            $last_employee = array_pop($workerCompanyIdForLogs);
            $workerCompanyIdForLogs = "Approved the request for Employees " . join(", ", $workerCompanyIdForLogs) . " and " . $last_employee;
        } 
        else 
        {
            $workerCompanyIdForLogs = "Approved the request for Employee " . join(", ", $workerCompanyIdForLogs);
        }

        // Process the db transation in saving logs
        $this->createUserAction($workerCompanyIdForLogs);

        return response()->json(['success' => 'Data saved successfully.'])->setStatusCode(201);
    }

    public function fetchLoanRequestDetails($id)
    {
        return LoanRequest::select(
            'loan_requests.id', 
            'loan_requests.created_at as date_requested',
            'loan_requests.loaner_company_id', 
            'loan_requests.borrower_company_id',
            'loan_requests.request_contact_id', 
            'loan_pending_workers.date_from', 'loan_pending_workers.date_to',
            'loan_pending_workers.rate',
            'loan_pending_workers.original_rate',
            'loan_pending_workers.worker_id',
            'loan_requests.note as requester_note',
            'companies.name as borrower_company_name')
            ->join('loan_pending_workers', 'loan_pending_workers.loan_request_id', '=', 'loan_requests.id')
            ->join('companies', 'companies.id', '=', 'loan_requests.borrower_company_id')
            ->where('loan_pending_workers.id', $id)
            ->limit(1)->get();
    }

    public function checkIfWorkerIsPendingAndNotYetCancelled($id)
    {
        return BorrowPendingWorkers::select('id')
            ->where('id', $id)
            ->where('status', 'Pending')
            ->first();
    }

    public function fetchBorrowRequestDetails($id)
    {
        return BorrowRequest::select(
            'borrow_requests.id',
            'borrow_requests.created_at as date_requested',
            'borrow_requests.requested_by',
            'borrow_requests.note as requester_note',
            'borrow_requests.request_contact_id',
            'borrow_pending_workers.search_id',
            'borrow_pending_workers.date_from',
            'borrow_pending_workers.date_to',
            'borrow_pending_workers.worker_id',
            'borrow_pending_workers.rate',
            'borrow_pending_workers.original_rate')
            ->join('borrow_pending_workers', 'borrow_pending_workers.borrow_request_id', '=', 'borrow_requests.id')
            ->where('borrow_pending_workers.id', $id)
            ->limit(1)->get();
    }

    public function updateLoanPendingWorkerStatus($id)
    {
        $selectedPendingWorkerData = array(
            'status' => 'Approved',
        );
        return LoanPendingWorkers::where('id', $id)->update($selectedPendingWorkerData);
    }

    public function savePendingWorkerToLoanApprovedHistories($selectedPendingWorkerData)
    {
        return LoanApproved::create($selectedPendingWorkerData);
    }

    public function updateBorrowPendingWorkerStatus($id)
    {
        $selectedPendingWorkerData = array(
            'status' => 'Approved',
        );
        return BorrowPendingWorkers::where('id', $id)->update($selectedPendingWorkerData);
    }

    public function savePendingWorkerToBorrowApprovedHistories($selectedPendingWorkerData)
    {
        return BorrowApproved::create($selectedPendingWorkerData);
    }

    public function saveLoanBorrowList($loan_borrow_list_data)
    {
        return LoanBorrowList::create($loan_borrow_list_data);
    }

    public function saveInboundBillingRequest($inbound_billing_request_data)
    {
        return InboundBillingRequests::create($inbound_billing_request_data);
    }

    public function saveOutboundBillingRequest($outboundBillingRequestData)
    {
        return OutboundBillingRequests::create($outboundBillingRequestData);
    }

    public function declineWorkerWithConflictingDuration($id, $dateFrom, $dateTo, $workerId, $borrowerCompanyId)
    {
        $fetchWorkersWithSameDurationExceptTheApprovedWorkerTransaction = $this->fetchWorkersWithSameDurationExceptTheApprovedWorker($id, $dateFrom, $dateTo, $workerId, $borrowerCompanyId);
        foreach ($fetchWorkersWithSameDurationExceptTheApprovedWorkerTransaction as $borrow_request) {
            $borrow_request_id = $borrow_request['id']; // same with loan_request_id
            $loanerCompanyId = $borrow_request['loaner_company_id'];
            $borrowerCompanyId = $borrow_request['borrower_company_id'];
            $dateRequested = $borrow_request['date_requested'];
            $requested_by = $borrow_request['requested_by'];
            $dateFrom = $borrow_request['date_from'];
            $dateTo = $borrow_request['date_to'];
            $rate = $borrow_request['rate'];
            $originalRate = $borrow_request['original_rate'];
            $workerId = $borrow_request['worker_id'];
            $requesterNote = $borrow_request['requester_note'];
            $approverId = 0; // 0 = system
            $approverNote = 'Declined by the system due to worker is already approved from the other request.';
            $workerCompanyId = $borrow_request['worker_company_id'];

            $selectedPendingWorkerData = array(
                'approver_note' => 'Declined by the system due to worker is already approved from the other request.',
                'status' => 'Declined',
            );
            $updateBorrowPendingWorkerTransaction = $this->updateBorrowPendingWorker($borrow_request_id, $selectedPendingWorkerData);
            $updateLoanPendingWorkerTransaction = $this->updateLoanPendingWorker($borrow_request_id, $selectedPendingWorkerData);

            // add pending worker to borrow_denied_histories table
            $selectedPendingWorkerData = array(
                'loaner_company_id' => $loanerCompanyId,
                'borrower_company_id' => $borrowerCompanyId,
                'date_requested' => $dateRequested,
                'requested_by' => $requested_by,
                'date_from' => $dateFrom,
                'date_to' => $dateTo,
                'rate' => $rate,
                'original_rate' => $originalRate,
                'worker_id' => $workerId,
                'requester_note' => $requesterNote,
                'approver_id' => $approverId,
                'approver_note' => $approverNote,
            );
            $addPendingWorkerToBorrowDeclinedHistoriesTransaction = $this->addPendingWorkerToBorrowDeclinedHistories($selectedPendingWorkerData);

            // add pending worker to loan_denied_histories table
            $selectedPendingWorkerData = array(
                'loaner_company_id' => $loanerCompanyId,
                'borrower_company_id' => $borrowerCompanyId,
                'date_requested' => $dateRequested,
                'requested_by' => $requested_by,
                'date_from' => $dateFrom,
                'date_to' => $dateTo,
                'rate' => $rate,
                'original_rate' => $originalRate,
                'worker_id' => $workerId,
                'requester_note' => $requesterNote,
                'approver_id' => $approverId,
                'approver_note' => $approverNote,
            );
            $addPendingWorkerToLoanDeclinedHistoriesTransaction = $this->addPendingWorkerToLoanDeclinedHistories($selectedPendingWorkerData);

            if($updateBorrowPendingWorkerTransaction || $updateLoanPendingWorkerTransaction || $addPendingWorkerToBorrowDeclinedHistoriesTransaction || $addPendingWorkerToLoanDeclinedHistoriesTransaction){

                $notificationForDeclinedWorker = array(
                    'type' => 'Borrow Request',
                    'company_id' => $borrowerCompanyId,
                    'link' => '/company/borrow/declined-requests',
                    'message' => 'Worker <b>'. $workerCompanyId .'</b> is declined by the system due to employee is already approved from the other request.',
                    'is_read' => false,
                );
                Notifications::create($notificationForDeclinedWorker);
            }
        }
        return true;
    }

    public function fetchWorkersWithSameDurationExceptTheApprovedWorker($id, $dateFrom, $dateTo, $workerId, $borrowerCompanyId)
    {
        return BorrowRequest::select(
            'borrow_requests.id',
            'borrow_requests.loaner_company_id',
            'borrow_requests.borrower_company_id',
            'borrow_requests.created_at as date_requested',
            'borrow_requests.requested_by',
            'borrow_pending_workers.search_id',
            'borrow_pending_workers.date_from',
            'borrow_pending_workers.date_to',
            'borrow_pending_workers.rate',
            'borrow_pending_workers.original_rate',
            'borrow_pending_workers.worker_id',
            'workers.worker_id as worker_company_id',
            'borrow_requests.note as requester_note')
            ->join('borrow_pending_workers', 'borrow_pending_workers.borrow_request_id', '=', 'borrow_requests.id')
            ->join('workers', 'workers.id', '=', 'borrow_pending_workers.worker_id')
            ->where('borrow_requests.id', '!=', $id)
            ->where('borrow_requests.borrower_company_id', '!=', $borrowerCompanyId)
            ->where(function ($query) use ($dateFrom, $dateTo) {
                $query->where('borrow_pending_workers.date_from', $dateFrom)
                    ->orWhere('borrow_pending_workers.date_to', $dateTo)
                    ->orWhereBetween('borrow_pending_workers.date_from', [$dateFrom, $dateTo])
                    ->orWhereBetween('borrow_pending_workers.date_to', [$dateFrom, $dateTo])
                    ->orWhereRaw('? BETWEEN borrow_pending_workers.date_from and borrow_pending_workers.date_to', [$dateFrom])
                    ->orWhereRaw('? BETWEEN borrow_pending_workers.date_from and borrow_pending_workers.date_to', [$dateTo]);
            })
            ->where('borrow_pending_workers.worker_id', $workerId)
            ->where('borrow_pending_workers.status', '!=', 'Cancelled')
            ->get();
    }

    public function updateBorrowPendingWorker($borrow_request_id, $selectedPendingWorkerData)
    {
        return BorrowPendingWorkers::where('id', $borrow_request_id)->update($selectedPendingWorkerData);
    }

    public function updateLoanPendingWorker($loan_request_id, $selectedPendingWorkerData)
    {
        return LoanPendingWorkers::where('id', $loan_request_id)->update($selectedPendingWorkerData);
    }

    public function addPendingWorkerToBorrowDeclinedHistories($selectedPendingWorkerData)
    {
        return BorrowDenied::create($selectedPendingWorkerData);
    }

    public function addPendingWorkerToLoanDeclinedHistories($selectedPendingWorkerData)
    {
        return LoanDenied::create($selectedPendingWorkerData);
    }

    public function fetchWorkerOriginalRate($workerId)
    {
        return Worker::select('workers.rate')->where('workers.id', $workerId)->first();
    }

    public function validateConflictSchedule($workerId, $start_date, $endDate)
    {
        return AssignedWorkers::select(
            'workers.worker_id',
            'workers.first_name',
            'workers.last_name',
            'projects.name as project_name')
            ->join('workers', 'workers.id', '=', 'assigned_workers.worker_id')
            ->join('assigned_availability', 'assigned_availability.assigned_worker_id', '=', 'assigned_workers.id')
            ->join('projects', 'projects.id', '=', 'assigned_workers.project_id')
            ->where(function ($query) use ($start_date, $endDate) {
                $query->where('assigned_availability.start_date', $start_date)
                    ->orWhere('assigned_availability.end_date', $endDate)
                    ->orWhereBetween('assigned_availability.start_date', [$start_date, $endDate])
                    ->orWhereBetween('assigned_availability.end_date', [$start_date, $endDate])
                    ->orWhereRaw('? BETWEEN assigned_availability.start_date and assigned_availability.end_date', [$start_date])
                    ->orWhereRaw('? BETWEEN assigned_availability.start_date and assigned_availability.end_date', [$endDate]);
            })
            ->where('assigned_workers.worker_id', $workerId)
            ->get();
    }

    public function saveAvailabilities($workerId, $workerType, $worker_rate, $workerOriginalRate, $start_date, $endDate, $loanerCompanyId, $borrowerCompanyId)
    {
        $availabilitiesData = $this->fetchAvailabilities($workerId, $start_date, $endDate, $loanerCompanyId);
        $availabilitiesId = $availabilitiesData[0]->id;
        $minAvailabilityDate = $availabilitiesData[0]->min_availability_date;
        $maxAvailabilityDate = $availabilitiesData[0]->max_availability_date;
        if ($start_date == $minAvailabilityDate) {
            $selectedNextWorkerAvailabilitiesData = $this->fetchNextAvailabilitiesOfSelectedWorker($maxAvailabilityDate, $workerId, $loanerCompanyId);
            if ($selectedNextWorkerAvailabilitiesData->count() < 1) {
                $availabilityData = array(
                    'min_availability_date' => date('Y-m-d', strtotime($endDate . '+1 day')),
                    'max_availability_date' => date('Y-m-d', strtotime($endDate . '+10 years +1 day')),
                );
                $this->updateAvailabilities($availabilitiesId, $availabilityData);
            } else {
                if ($endDate >= $maxAvailabilityDate) {
                    $this->deleteAvailabilities($availabilitiesId);
                } else {
                    $availabilityData = array(
                        'min_availability_date' => date('Y-m-d', strtotime($endDate . '+1 day')),
                        'max_availability_date' => $maxAvailabilityDate,
                    );
                    $this->updateAvailabilities($availabilitiesId, $availabilityData);
                }
            }
        } else {
            $selectedNextWorkerAvailabilitiesData = $this->fetchNextAvailabilitiesOfSelectedWorker($maxAvailabilityDate, $workerId, $loanerCompanyId);
            if ($selectedNextWorkerAvailabilitiesData->count() < 1) {
                $availabilityData = array(
                    'max_availability_date' => date('Y-m-d', strtotime($start_date . '-1 day')),
                );
                $this->updateAvailabilities($availabilitiesId, $availabilityData);
                $availabilityData = array(
                    'worker_id' => $workerId,
                    'company_id' => $loanerCompanyId,
                    'worker_type' => 'inhouse',
                    'rate' => $workerOriginalRate,
                    'min_availability_date' => date('Y-m-d', strtotime($endDate . '+1 day')),
                    'max_availability_date' => date('Y-m-d', strtotime($endDate . '+10 years +1 day')),
                );
                $this->createAvailabilities($availabilityData);
            } else {
                $availabilityData = array(
                    'max_availability_date' => date('Y-m-d', strtotime($start_date . '-1 day')),
                );
                $this->updateAvailabilities($availabilitiesId, $availabilityData);
                if (!$selectedNextWorkerAvailabilitiesData->isEmpty()) {
                    $endDatePlusOneDay = date('Y-m-d', strtotime($endDate . '+1 day'));
                    $nextAvailabilityId = $selectedNextWorkerAvailabilitiesData[0]->id;
                    $nextDateMinDate = $selectedNextWorkerAvailabilitiesData[0]->min_availability_date;
                    $nextDateMaxDate = $selectedNextWorkerAvailabilitiesData[0]->max_availability_date;
                    if ($endDatePlusOneDay >= $nextDateMinDate && $endDatePlusOneDay < $nextDateMaxDate) {
                        $availabilityData = array(
                            'min_availability_date' => date('Y-m-d', strtotime($endDate . '+1 day')),
                        );
                        $this->updateAvailabilities($nextAvailabilityId, $availabilityData);
                    } else if ($endDatePlusOneDay >= $nextDateMaxDate) {
                        $this->deleteAvailabilities($nextAvailabilityId);
                    } else if ($endDate < $maxAvailabilityDate) {
                        $availabilityData = array(
                            'max_availability_date' => date('Y-m-d', strtotime($start_date . '-1 day')),
                        );
                        $this->updateAvailabilities($availabilitiesId, $availabilityData);
                        $availabilityData = array(
                            'worker_id' => $workerId,
                            'company_id' => $loanerCompanyId,
                            'worker_type' => 'inhouse',
                            'rate' => $workerOriginalRate,
                            'min_availability_date' => date('Y-m-d', strtotime($endDate . '+1 day')),
                            'max_availability_date' => $maxAvailabilityDate,
                        );
                        $this->createAvailabilities($availabilityData);
                    }
                } else {
                    $availabilityData = array(
                        'worker_id' => $workerId,
                        'company_id' => $loanerCompanyId,
                        'worker_type' => 'inhouse',
                        'rate' => $workerOriginalRate,
                        'min_availability_date' => date('Y-m-d', strtotime($endDate . '+1 day')),
                        'max_availability_date' => date('Y-m-d', strtotime($endDate . '+10 years +1 day')),
                    );
                    $this->createAvailabilities($availabilityData);
                }
            }
        }

        $CRUDAvailabilitiesTransaction = true;
        $availabilityData = array(
            'worker_id' => $workerId,
            'company_id' => $borrowerCompanyId,
            'worker_type' => $workerType,
            'rate' => $worker_rate,
            'min_availability_date' => $start_date,
            'max_availability_date' => $endDate,
        );
        $createAvailabilities = $this->createAvailabilities($availabilityData);
        if (!$createAvailabilities) {
            $CRUDAvailabilitiesTransaction = false;
        }

        // combine availabilities
        $minAvailabilityDateToCompare = date('Y-m-d', strtotime($start_date . '-1 day'));
        $maxAvailabilityDateToCompare = date('Y-m-d', strtotime($endDate . '+1 day'));
        $combineAvailabilitiesDurations = $this->combineAvailabilitiesDurations($borrowerCompanyId, $workerId, $workerType, $minAvailabilityDateToCompare, $maxAvailabilityDateToCompare);
        if (!$combineAvailabilitiesDurations) {
            $CRUDAvailabilitiesTransaction = false;
        }

        return $CRUDAvailabilitiesTransaction;
    }

    public function fetchAvailabilities($workerId, $start_date, $endDate, $loanerCompanyId)
    {
        return Availabilities::select()
            ->where('availabilities.worker_id', $workerId)
            ->where('availabilities.company_id', $loanerCompanyId)
            ->where(function ($query) use ($start_date, $endDate) {
                $query->where('availabilities.min_availability_date', $start_date)
                    ->orWhere('availabilities.max_availability_date', $endDate)
                    ->orWhereBetween('availabilities.min_availability_date', [$start_date, $endDate])
                    ->orWhereBetween('availabilities.max_availability_date', [$start_date, $endDate])
                    ->orWhereRaw('? BETWEEN availabilities.min_availability_date and availabilities.max_availability_date', [$start_date])
                    ->orWhereRaw('? BETWEEN availabilities.min_availability_date and availabilities.max_availability_date', [$endDate]);
            })
            ->orderBy('availabilities.min_availability_date', 'asc')
            ->get();
    }

    public function fetchNextAvailabilitiesOfSelectedWorker($maxAvailabilityDate, $workerId, $loanerCompanyId)
    {
        return Availabilities::where('availabilities.min_availability_date', '>', $maxAvailabilityDate)
            ->where('availabilities.worker_id', $workerId)
            ->where('availabilities.company_id', $loanerCompanyId)
            ->orderBy('availabilities.min_availability_date', 'asc')
            ->get();
    }

    public function updateAvailabilities($availabilitiesId, $data)
    {
        return Availabilities::where('availabilities.id', $availabilitiesId)
            ->update($data);
    }

    public function deleteAvailabilities($availabilitiesId)
    {
        return Availabilities::where('availabilities.id', $availabilitiesId)->delete();
    }

    public function createAvailabilities($availabilityData)
    {
        return Availabilities::create($availabilityData);
    }

    public function combineAvailabilitiesDurations($companyId, $workerId, $workerType, $minAvailabilityDateToCompare, $maxAvailabilityDateToCompare)
    {
        $availabilitiesDataToCombine = $this->fetchAvailabilitiesDataToCombine($companyId, $workerId, $workerType, $minAvailabilityDateToCompare, $maxAvailabilityDateToCompare);
        $availabilitiesDataToCombineCount = count($availabilitiesDataToCombine);
        if ($availabilitiesDataToCombineCount > 1) {
            $availabilitiesIdsToDelete = [];
            $availabilitiesToCombineFirstData = $availabilitiesDataToCombine[0];
            $availabilitiesToCombineFirstDataId = $availabilitiesToCombineFirstData->id;
            $availabilitiesToCombineLastData = $availabilitiesDataToCombine[$availabilitiesDataToCombineCount - 1];
            $availabilitiesToCombineLastDataMaxAvailabilityDate = $availabilitiesToCombineLastData->max_availability_date;
            $dateNow = date('Y-m-d');
            $dateDifference = date_diff(date_create($dateNow), date_create($availabilitiesToCombineLastDataMaxAvailabilityDate));
            $dateDifferenceInYears = $dateDifference->format('%y');
            $updateAvailabilitiesTransaction = true;

            foreach ($availabilitiesDataToCombine as $availabilities) {
                if ($availabilities->id != $availabilitiesToCombineFirstDataId) {
                    array_push($availabilitiesIdsToDelete, $availabilities->id);
                }
            }

            if ($dateDifferenceInYears == 10) {
                $availabilitiesData = array(
                    'max_availability_date' => date('Y-m-d', strtotime($minAvailabilityDateToCompare . '+10 years 1 day')),
                );
                $updateAvailabilitiesTransaction = $this->updateAvailabilities($availabilitiesToCombineFirstDataId, $availabilitiesData);
            } else {
                $availabilitiesData = array(
                    'max_availability_date' => $availabilitiesToCombineLastDataMaxAvailabilityDate,
                );
                $updateAvailabilitiesTransaction = $this->updateAvailabilities($availabilitiesToCombineFirstDataId, $availabilitiesData);
            }
            $deleteAvailabilitiesTransaction = $this->deleteAvailabilitiesByIds($availabilitiesIdsToDelete);
            return $updateAvailabilitiesTransaction && $deleteAvailabilitiesTransaction;
        }
        return true;
    }

    public function fetchAvailabilitiesDataToCombine($companyId, $workerId, $workerType, $minAvailabilityDateToCompare, $maxAvailabilityDateToCompare)
    {
        return Availabilities::select(
            'availabilities.id',
            'availabilities.company_id',
            'availabilities.worker_type',
            'availabilities.rate',
            'availabilities.min_availability_date',
            'availabilities.max_availability_date')
            ->where('availabilities.worker_id', $workerId)
            ->where('availabilities.company_id', $companyId)
            ->where('availabilities.worker_type', $workerType)
            ->where(function ($query) use ($minAvailabilityDateToCompare, $maxAvailabilityDateToCompare) {
                $query->where('availabilities.min_availability_date', $minAvailabilityDateToCompare)
                    ->orWhere('availabilities.max_availability_date', $maxAvailabilityDateToCompare)
                    ->orWhereBetween('availabilities.min_availability_date', [$minAvailabilityDateToCompare, $maxAvailabilityDateToCompare])
                    ->orWhereBetween('availabilities.max_availability_date', [$minAvailabilityDateToCompare, $maxAvailabilityDateToCompare])
                    ->orWhereRaw('? BETWEEN availabilities.min_availability_date and availabilities.max_availability_date', [$minAvailabilityDateToCompare])
                    ->orWhereRaw('? BETWEEN availabilities.min_availability_date and availabilities.max_availability_date', [$maxAvailabilityDateToCompare]);
            })
            ->orderBy('availabilities.min_availability_date', 'asc')
            ->orderBy('availabilities.max_availability_date', 'asc')
            ->get();
    }

    public function deleteAvailabilitiesByIds($availabilitiesIds)
    {
        return Availabilities::whereIn('availabilities.id', $availabilitiesIds)->delete();
    }

    public function checkIfActivelyApproved($workerID, $dateFrom, $dateTo){
        return LoanApproved::select(
            'loan_approved_histories.loaner_company_id',
            'loan_approved_histories.borrower_company_id',
            'loan_approved_histories.date_from',
            'loan_approved_histories.date_to',
            'loan_approved_histories.worker_id',
        )
        ->where('loan_approved_histories.worker_id', $workerID)
        ->where(function ($query) use ($dateFrom, $dateTo) {
            $query->where('loan_approved_histories.date_from', $dateFrom) // equal to date from / first date
                ->orWhere('loan_approved_histories.date_to', $dateTo) //equal to date to / end date
                ->orWhereBetween('loan_approved_histories.date_from', [$dateFrom, $dateTo]) // within the range
                ->orWhereBetween('loan_approved_histories.date_to', [$dateFrom, $dateTo]) // within the range
                ->orWhereRaw('? BETWEEN loan_approved_histories.date_from and loan_approved_histories.date_to', [$dateFrom]) // within the range
                ->orWhereRaw('? BETWEEN loan_approved_histories.date_from and loan_approved_histories.date_to', [$dateTo]); // within the range
        })
        ->first();
    }

    private function deleteWorkerRequest(int $workerId, int $loanerCompanyId, int $borrowerCompanyId) 
    {
        $workerRequestApplication = WorkerRequestApplication::where(
            [
                [ 'worker_id',  '=', $workerId        ], 
                [ 'company_id', '=', $loanerCompanyId ], 
                [ 'status',     '=', 'accepted'       ] 
            ]
        )
        ->first(); 

        if ($workerRequestApplication != null)
        {              
            $workerRequest = WorkerRequest::where(
                [
                    [ 'id',         '=', $workerRequestApplication->worker_request_id ],  
                    [ 'company_id', '=', $borrowerCompanyId                           ]
                ]
            )->first();

            if ($workerRequest != null)
            {
                if (($workerRequest->number_of_applicants_successfully_borrowed + 1) >= $workerRequest->number_of_applicants_to_be_borrowed)
                {
                    $workerRequest->delete();

                    $notificationData = array(
                        'type'       => 'Employee Request',
                        'company_id' => $workerRequest->company_id,
                        'message'    => "An Employee Request has met the required approved employee requests. It has been automatically deleted.",
                        'link'       => '/company/worker-request/manage',
                        "is_read"    => false,
                    );

                    Notifications::create($notificationData);
                }
                else 
                {
                    $successfullyBorrowedEmployees = ($workerRequest->number_of_applicants_successfully_borrowed + 1);

                    $workerRequest->update(
                        [
                            'number_of_applicants_successfully_borrowed' => $successfullyBorrowedEmployees
                        ]
                    );  
                }
            }

        }  
    }
}