<?php

namespace App\Modules\Company\Whiteboard\AssignWorker;

use App\Models\AssignedAvailability;
use App\Models\AssignedWorkers;
use App\Models\Availabilities;
use App\Models\ProjectWorkersTimesheet;

class AssignWorkerWithConflict
{
    public function override($companyId, $workerId, $userInputStartDate, $userInputEndDate, $workerType, $projectId)
    {
        $assignedAvailabilityDataToOverride = $this->fetchAssignedAvailabilityToOverride($companyId, $workerId, $userInputStartDate, $userInputEndDate, $workerType);
        $assignedAvailabilityDataToOverrideCount = count($assignedAvailabilityDataToOverride);

        $dateToday = date('Y-m-d');
        $dateYesterday = date('Y-m-d', strtotime(date('Y-m-d') . '-1 day'));
        $CRUDAvailabilitiesTransaction = true;
        $CRUDAssignedWorkersTransaction = true;
        $CRUDAssignedAvailabilityTransaction = true;
        $deleteProjectWorkersTimesheetTransaction = true;

        if ($assignedAvailabilityDataToOverrideCount > 0) {
            // variables to use
            $conflictingAssignedAvailabilityFirstData = $assignedAvailabilityDataToOverride[0];
            $conflictingAssignedAvailabilityFirstDataId = $conflictingAssignedAvailabilityFirstData->id;
            $conflictingAssignedAvailabilityFirstDataAvailabilityId = $conflictingAssignedAvailabilityFirstData->availability_id;
            $conflictingAssignedAvailabilityFirstDataRate = $conflictingAssignedAvailabilityFirstData->rate;
            $conflictingAssignedAvailabilityFirstDataStartDate = $conflictingAssignedAvailabilityFirstData->start_date;
            $conflictingAssignedAvailabilityFirstDataIsDraft = $conflictingAssignedAvailabilityFirstData->is_draft;
            $conflictingAssignedAvailabilityLastData = $assignedAvailabilityDataToOverride[$assignedAvailabilityDataToOverrideCount - 1];
            $conflictingAssignedAvailabilityLastDataEndDate = $conflictingAssignedAvailabilityLastData->end_date;

            // if conflicting assigned_availability start_date < date_today && conflicting assigned_availability end_date last data == user input end_date && $conflicting assigned_availability first data is_draft = 0
            // if conflicting assigned_availability start_date < date_today && conflicting assigned_availability end_date last data > user input end_date && $conflicting assigned_availability first data is_draft = 0
            // if conflicting assigned_availability start_date < date_today && conflicting assigned_availability end_date last data < user input end_date && $conflicting assigned_availability first data is_draft = 0
            if (($conflictingAssignedAvailabilityFirstDataStartDate < $dateToday && $conflictingAssignedAvailabilityLastDataEndDate == $userInputEndDate && !$conflictingAssignedAvailabilityFirstDataIsDraft) ||
                ($conflictingAssignedAvailabilityFirstDataStartDate < $dateToday && $conflictingAssignedAvailabilityLastDataEndDate > $userInputEndDate && !$conflictingAssignedAvailabilityFirstDataIsDraft) ||
                ($conflictingAssignedAvailabilityFirstDataStartDate < $dateToday && $conflictingAssignedAvailabilityLastDataEndDate < $userInputEndDate && !$conflictingAssignedAvailabilityFirstDataIsDraft)) {

                // update assigned_availability
                $assignedAvailabilityData = array(
                    'end_date' => $dateYesterday,
                );
                $updateAssignedAvailability = $this->updateAssignedAvailability($conflictingAssignedAvailabilityFirstDataId, $assignedAvailabilityData);
                if (!$updateAssignedAvailability) {
                    $CRUDAssignedAvailabilityTransaction = false;
                }

                // restore availabilties and delete 2nd to last assigned_availabilities, assigned_workers by ids, and timesheet
                $restoreAvailabilitiesAndDelete2ndToLastAssignedAvailabilitiesAndAssignedWorkersAndTimesheet = $this->restoreAvailabilitiesAndDelete2ndToLastAssignedAvailabilitiesAndAssignedWorkersAndTimesheet($assignedAvailabilityDataToOverride, $workerId, $companyId, $workerType);
                if (!$restoreAvailabilitiesAndDelete2ndToLastAssignedAvailabilitiesAndAssignedWorkersAndTimesheet) {
                    $CRUDAvailabilitiesTransaction = false;
                    $CRUDAssignedWorkersTransaction = false;
                    $CRUDAssignedAvailabilityTransaction = false;
                }

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

                // create assigned_workers
                $assignedWorkersData = array(
                    'project_id' => $projectId,
                    'worker_id' => $workerId,
                );
                $createAssignedWorkers = $this->createAssignedWorkers($assignedWorkersData);
                if (!$createAssignedWorkers) {
                    $CRUDAssignedWorkersTransaction = false;
                }

                // create assigned_availability
                $assignedAvailabilityData = array(
                    'availability_id' => $conflictingAssignedAvailabilityFirstDataAvailabilityId,
                    'assigned_worker_id' => $createAssignedWorkers->id,
                    'worker_type' => $workerType,
                    'rate' => $conflictingAssignedAvailabilityFirstDataRate,
                    'start_date' => $userInputStartDate,
                    'end_date' => $userInputEndDate,
                    'is_draft' => 1,
                );
                $createAssignedAvailability = $this->createAssignedAvailability($assignedAvailabilityData);
                if (!$createAssignedAvailability) {
                    $CRUDAssignedAvailabilityTransaction = false;
                }

                // combine assigned_availability
                $startDateToCompare = date('Y-m-d', strtotime($userInputStartDate . '-1 day'));
                $endDateToCompare = date('Y-m-d', strtotime($userInputEndDate . '+1 day'));
                $combineAvailabilitiesDurations = $this->combineAssignedAvailabilityDurations($projectId, $companyId, $workerId, $workerType, $startDateToCompare, $endDateToCompare);
                if (!$combineAvailabilitiesDurations) {
                    $CRUDAvailabilitiesTransaction = false;
                }

                // update availabilities
                $updateAvailabilitiesBasedOnUserInputDuration = $this->updateAvailabilitiesBasedOnUserInputDuration($companyId, $workerId, $workerType, $userInputStartDate, $userInputEndDate);
                if (!$updateAvailabilitiesBasedOnUserInputDuration) {
                    $CRUDAvailabilitiesTransaction = false;
                }
            }
            // if conflicting assigned_availability start_date < date_today && conflicting assigned_availability end_date last data == user input end_date && $conflicting assigned_availability first data is_draft = 1
            // if conflicting assigned_availability start_date < date_today && conflicting assigned_availability end_date last data > user input end_date && $conflicting assigned_availability first data is_draft = 1
            // if conflicting assigned_availability start_date < date_today && conflicting assigned_availability end_date last data < user input end_date && $conflicting assigned_availability first data is_draft = 1
            // if conflicting assigned_availability start_date == user input start_date && conflicting assigned_availability end_date last data == user input end_date
            // if conflicting assigned_availability start_date == user input start_date && conflicting assigned_availability end_date last data > user input end_date
            // if conflicting assigned_availability start_date == user input start_date && conflicting assigned_availability end_date last data < user input end_date
            // if conflicting assigned_availability start_date > user input start_date && conflicting assigned_availability end_date last data == user input end_date
            // if conflicting assigned_availability start_date > user input start_date && conflicting assigned_availability end_date last data > user input end_date
            // if conflicting assigned_availability start_date > user input start_date && conflicting assigned_availability end_date last data < user input end_date
            // if conflicting assigned_availability start_date < user input start_date && conflicting assigned_availability end_date last data == user input end_date
            // if conflicting assigned_availability start_date < user input start_date && conflicting assigned_availability end_date last data > user input end_date
            // if conflicting assigned_availability start_date < user input start_date && conflicting assigned_availability end_date last data < user input end_date
            else if (($conflictingAssignedAvailabilityFirstDataStartDate < $dateToday && $conflictingAssignedAvailabilityLastDataEndDate == $userInputEndDate && $conflictingAssignedAvailabilityFirstDataIsDraft) ||
                ($conflictingAssignedAvailabilityFirstDataStartDate < $dateToday && $conflictingAssignedAvailabilityLastDataEndDate > $userInputEndDate && $conflictingAssignedAvailabilityFirstDataIsDraft) ||
                ($conflictingAssignedAvailabilityFirstDataStartDate < $dateToday && $conflictingAssignedAvailabilityLastDataEndDate < $userInputEndDate && $conflictingAssignedAvailabilityFirstDataIsDraft) ||
                ($conflictingAssignedAvailabilityFirstDataStartDate == $userInputStartDate && $conflictingAssignedAvailabilityLastDataEndDate == $userInputEndDate) ||
                ($conflictingAssignedAvailabilityFirstDataStartDate == $userInputStartDate && $conflictingAssignedAvailabilityLastDataEndDate > $userInputEndDate) ||
                ($conflictingAssignedAvailabilityFirstDataStartDate == $userInputStartDate && $conflictingAssignedAvailabilityLastDataEndDate < $userInputEndDate) ||
                ($conflictingAssignedAvailabilityFirstDataStartDate > $userInputStartDate && $conflictingAssignedAvailabilityLastDataEndDate == $userInputEndDate) ||
                ($conflictingAssignedAvailabilityFirstDataStartDate > $userInputStartDate && $conflictingAssignedAvailabilityLastDataEndDate > $userInputEndDate) ||
                ($conflictingAssignedAvailabilityFirstDataStartDate > $userInputStartDate && $conflictingAssignedAvailabilityLastDataEndDate < $userInputEndDate) ||
                ($conflictingAssignedAvailabilityFirstDataStartDate < $userInputStartDate && $conflictingAssignedAvailabilityLastDataEndDate == $userInputEndDate) ||
                ($conflictingAssignedAvailabilityFirstDataStartDate < $userInputStartDate && $conflictingAssignedAvailabilityLastDataEndDate > $userInputEndDate) ||
                ($conflictingAssignedAvailabilityFirstDataStartDate < $userInputStartDate && $conflictingAssignedAvailabilityLastDataEndDate < $userInputEndDate)) {
                // restore availabilties and delete assigned_availabilities, assigned_workers by ids, and timesheet
                $restoreAvailabilitiesAndDeleteAssignedAvailabilitiesAndAssignedWorkersAndTimesheet = $this->restoreAvailabilitiesAndDeleteAssignedAvailabilitiesAndAssignedWorkersAndTimesheet($assignedAvailabilityDataToOverride, $workerId, $companyId, $workerType);
                if (!$restoreAvailabilitiesAndDeleteAssignedAvailabilitiesAndAssignedWorkersAndTimesheet) {
                    $CRUDAvailabilitiesTransaction = false;
                    $CRUDAssignedWorkersTransaction = false;
                    $CRUDAssignedAvailabilityTransaction = false;
                }

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

                // create assigned_workers
                $assignedWorkersData = array(
                    'project_id' => $projectId,
                    'worker_id' => $workerId,
                );
                $createAssignedWorkers = $this->createAssignedWorkers($assignedWorkersData);
                if (!$createAssignedWorkers) {
                    $CRUDAssignedWorkersTransaction = false;
                }

                // create assigned_availability
                $assignedAvailabilityData = array(
                    'availability_id' => $conflictingAssignedAvailabilityFirstDataAvailabilityId,
                    'assigned_worker_id' => $createAssignedWorkers->id,
                    'worker_type' => $workerType,
                    'rate' => $conflictingAssignedAvailabilityFirstDataRate,
                    'start_date' => $userInputStartDate,
                    'end_date' => $userInputEndDate,
                    'is_draft' => 1,
                );
                $createAssignedAvailability = $this->createAssignedAvailability($assignedAvailabilityData);
                if (!$createAssignedAvailability) {
                    $CRUDAssignedAvailabilityTransaction = false;
                }

                // combine assigned_availability
                $startDateToCompare = date('Y-m-d', strtotime($userInputStartDate . '-1 day'));
                $endDateToCompare = date('Y-m-d', strtotime($userInputEndDate . '+1 day'));
                $combineAvailabilitiesDurations = $this->combineAssignedAvailabilityDurations($projectId, $companyId, $workerId, $workerType, $startDateToCompare, $endDateToCompare);
                if (!$combineAvailabilitiesDurations) {
                    $CRUDAvailabilitiesTransaction = false;
                }

                // update availabilities
                $updateAvailabilitiesBasedOnUserInputDuration = $this->updateAvailabilitiesBasedOnUserInputDuration($companyId, $workerId, $workerType, $userInputStartDate, $userInputEndDate);
                if (!$updateAvailabilitiesBasedOnUserInputDuration) {
                    $CRUDAvailabilitiesTransaction = false;
                }
            }
        }

        return $CRUDAvailabilitiesTransaction &&
            $CRUDAssignedWorkersTransaction &&
            $CRUDAssignedAvailabilityTransaction &&
            $deleteProjectWorkersTimesheetTransaction;
    }

    public function fetchAssignedAvailabilityToOverride($companyId, $workerId, $userInputStartDate, $userInputEndDate, $workerType)
    {
        return AssignedWorkers::select(
            'assigned_availability.id',
            'assigned_availability.availability_id',
            'assigned_availability.assigned_worker_id',
            'assigned_availability.worker_type',
            'assigned_availability.rate',
            'assigned_availability.start_date',
            'assigned_availability.end_date',
            'assigned_availability.is_draft')
            ->join('assigned_availability', 'assigned_availability.assigned_worker_id', '=', 'assigned_workers.id')
            ->join('projects', 'projects.id', '=', 'assigned_workers.project_id')
            ->where('assigned_workers.worker_id', $workerId)
            ->where('projects.company_id', $companyId)
            ->when($workerType == 'inhouse', function ($query) {
                return $query->where('assigned_availability.worker_type', 'inhouse');
            })
            ->when($workerType == 'borrowed', function ($query) {
                return $query->where('assigned_availability.worker_type', 'borrowed');
            })
            ->where(function ($query) use ($userInputStartDate, $userInputEndDate) {
                $query->where('assigned_availability.start_date', $userInputStartDate)
                    ->orWhere('assigned_availability.end_date', $userInputEndDate)
                    ->orWhereBetween('assigned_availability.start_date', [$userInputStartDate, $userInputEndDate])
                    ->orWhereBetween('assigned_availability.end_date', [$userInputStartDate, $userInputEndDate])
                    ->orWhereRaw('? BETWEEN assigned_availability.start_date and assigned_availability.end_date', [$userInputStartDate])
                    ->orWhereRaw('? BETWEEN assigned_availability.start_date and assigned_availability.end_date', [$userInputEndDate]);
            })
            ->orderBy('assigned_availability.start_date', 'asc')
            ->orderBy('assigned_availability.end_date', 'asc')
            ->get();
    }

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

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

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

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

    public function createAssignedWorkers($assignedWorkersData)
    {
        return AssignedWorkers::create($assignedWorkersData);
    }

    public function deleteAssignedWorkersByIds($assignedWorkersIds)
    {
        return AssignedWorkers::whereIn('assigned_workers.id', $assignedWorkersIds)->delete();
    }

    public function createAssignedAvailability($assignedAvailabilityData)
    {
        return AssignedAvailability::create($assignedAvailabilityData);
    }

    public function updateAssignedAvailability($assignedAvailabilityId, $assignedAvailabilityData)
    {
        return AssignedAvailability::where('assigned_availability.id', $assignedAvailabilityId)->update($assignedAvailabilityData);
    }

    public function deleteAssignedAvailabilityByIds($assignedAvailabilityIds)
    {
        return AssignedAvailability::whereIn('assigned_availability.id', $assignedAvailabilityIds)->delete();
    }

    public function deleteProjectWorkersTimesheet($workerId, $workerType, $assignedAvailabilityStartDate, $assignedAvailabilityEndDate)
    {
        $dateToday = date('Y-m-d');
        return ProjectWorkersTimesheet::where('project_workers_timesheet.worker_id', $workerId)
            ->where('project_workers_timesheet.worker_type', $workerType)
            ->where('project_workers_timesheet.date', '>=', $dateToday)
            ->where(function ($query) use ($assignedAvailabilityStartDate, $assignedAvailabilityEndDate) {
                $query->where('project_workers_timesheet.date', $assignedAvailabilityStartDate)
                    ->orWhere('project_workers_timesheet.date', $assignedAvailabilityEndDate)
                    ->orWhereBetween('project_workers_timesheet.date', [$assignedAvailabilityStartDate, $assignedAvailabilityEndDate])
                    ->orWhereBetween('project_workers_timesheet.date', [$assignedAvailabilityStartDate, $assignedAvailabilityEndDate]);
            })
            ->delete();
    }

    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 combineAssignedAvailabilityDurations($projectId, $companyId, $workerId, $workerType, $startDateToCompare, $endDateToCompare)
    {
        $assignedAvailabilityToCombine = $this->fetchAssignedAvailabilityDataToCombine($projectId, $companyId, $workerId, $workerType, $startDateToCompare, $endDateToCompare);
        $availabilitiesDataToCombineCount = count($assignedAvailabilityToCombine);
        if ($availabilitiesDataToCombineCount > 1) {
            $assignedWorkersIdsToDelete = [];
            $assignedAvailabilityIdsToDelete = [];
            $assignedAvailabilityToCombineFirstData = $assignedAvailabilityToCombine[0];
            $assignedAvailabilityToCombineFirstDataId = $assignedAvailabilityToCombineFirstData->id;
            $assignedAvailabilityToCombineLastData = $assignedAvailabilityToCombine[$availabilitiesDataToCombineCount - 1];
            $assignedAvailabilityToCombineLastDataEndDate = $assignedAvailabilityToCombineLastData->end_date;
            foreach ($assignedAvailabilityToCombine as $assignedAvailability) {
                if ($assignedAvailability->id != $assignedAvailabilityToCombineFirstDataId) {
                    array_push($assignedWorkersIdsToDelete, $assignedAvailability->assigned_worker_id);
                    array_push($assignedAvailabilityIdsToDelete, $assignedAvailability->id);
                }
            }
            $assignedAvailabilityData = array(
                'end_date' => $assignedAvailabilityToCombineLastDataEndDate,
            );
            $updateAssignedAvailabilityTransaction = $this->updateAssignedAvailability($assignedAvailabilityToCombineFirstDataId, $assignedAvailabilityData);
            $deleteAssignedWorkersTransaction = $this->deleteAssignedWorkersByIds($assignedWorkersIdsToDelete);
            $deleteAssignedAvailabilityTransaction = $this->deleteAssignedAvailabilityByIds($assignedAvailabilityIdsToDelete);
            return $updateAssignedAvailabilityTransaction && $deleteAssignedWorkersTransaction && $deleteAssignedAvailabilityTransaction;
        }
        return true;
    }

    public function fetchAssignedAvailabilityDataToCombine($projectId, $companyId, $workerId, $workerType, $startDateToCompare, $endDateToCompare)
    {
        return AssignedAvailability::select(
            'assigned_availability.id',
            'assigned_availability.availability_id',
            'assigned_availability.assigned_worker_id',
            'assigned_availability.worker_type',
            'assigned_availability.rate',
            'assigned_availability.start_date',
            'assigned_availability.end_date',
            'assigned_availability.is_draft')
            ->join('assigned_workers', 'assigned_workers.id', '=', 'assigned_availability.assigned_worker_id')
            ->join('projects', 'projects.id', '=', 'assigned_workers.project_id')
            ->where('assigned_workers.project_id', $projectId)
            ->where('assigned_workers.worker_id', $workerId)
            ->where('projects.company_id', $companyId)
            ->where('assigned_availability.worker_type', $workerType)
            ->where('assigned_availability.is_draft', 1)
            ->where(function ($query) use ($startDateToCompare, $endDateToCompare) {
                $query->where('assigned_availability.start_date', $startDateToCompare)
                    ->orWhere('assigned_availability.end_date', $endDateToCompare)
                    ->orWhereBetween('assigned_availability.start_date', [$startDateToCompare, $endDateToCompare])
                    ->orWhereBetween('assigned_availability.end_date', [$startDateToCompare, $endDateToCompare])
                    ->orWhereRaw('? BETWEEN assigned_availability.start_date and assigned_availability.end_date', [$startDateToCompare])
                    ->orWhereRaw('? BETWEEN assigned_availability.start_date and assigned_availability.end_date', [$endDateToCompare]);
            })
            ->orderBy('assigned_availability.start_date', 'asc')
            ->orderBy('assigned_availability.end_date', 'asc')
            ->get();
    }

    public function fetchAvailabilitiesWithinRange($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 restoreAvailabilitiesAndDelete2ndToLastAssignedAvailabilitiesAndAssignedWorkersAndTimesheet($assignedAvailabilityDataToOverride, $workerId, $companyId, $workerType)
    {
        // variables to use
        $conflictingAssignedAvailabilityFirstData = $assignedAvailabilityDataToOverride[0];
        $conflictingAssignedAvailabilityFirstDataAvailabilityId = $conflictingAssignedAvailabilityFirstData->id;
        $conflictingAssignedAvailabilityFirstDataRate = $conflictingAssignedAvailabilityFirstData->rate;
        $assignedWorkersIdsToDelete = [];
        $assignedAvailabilityIdsToDelete = [];
        $dateToday = date('Y-m-d');
        $createAvailabilitiesTransaction = true;
        $deleteAssignedAvailabilityTransaction = true;
        $deleteAssignedWorkersTransaction = true;
        $deleteProjectWorkersTimesheetTransaction = true;

        foreach ($assignedAvailabilityDataToOverride as $assignedAvailability) {
            $assignedAvailabilityId = $assignedAvailability->id;
            $assignedAvailabilityAssignedWorkerId = $assignedAvailability->assigned_worker_id;
            $assignedAvailabilityStartDate = $assignedAvailability->start_date;
            $assignedAvailabilityEndDate = $assignedAvailability->end_date;
            if ($assignedAvailability->id == $conflictingAssignedAvailabilityFirstDataAvailabilityId) {
                // create availabilities
                $availabilitiesData = array(
                    'worker_id' => $workerId,
                    'company_id' => $companyId,
                    'worker_type' => $workerType,
                    'rate' => $conflictingAssignedAvailabilityFirstDataRate,
                    'min_availability_date' => $dateToday,
                    'max_availability_date' => $assignedAvailabilityEndDate,
                );
                $createAvailabilities = $this->createAvailabilities($availabilitiesData);
                if (!$createAvailabilities) {
                    $createAvailabilitiesTransaction = false;
                }
            } else {
                array_push($assignedWorkersIdsToDelete, $assignedAvailabilityAssignedWorkerId);
                array_push($assignedAvailabilityIdsToDelete, $assignedAvailabilityId);

                // create availabilities
                $availabilitiesData = array(
                    'worker_id' => $workerId,
                    'company_id' => $companyId,
                    'worker_type' => $workerType,
                    'rate' => $conflictingAssignedAvailabilityFirstDataRate,
                    'min_availability_date' => $assignedAvailabilityStartDate,
                    'max_availability_date' => $assignedAvailabilityEndDate,
                );
                $createAvailabilities = $this->createAvailabilities($availabilitiesData);
                if (!$createAvailabilities) {
                    $createAvailabilitiesTransaction = false;
                }
            }

            $assignedAvailabilityIsDraft = $assignedAvailability->is_draft;
            // delete timesheet data
            if (!$assignedAvailabilityIsDraft) {
                $deleteProjectWorkersTimesheetTransaction = $this->deleteProjectWorkersTimesheet($workerId, $workerType, $assignedAvailabilityStartDate, $assignedAvailabilityEndDate);
            }
        }

        if (count($assignedWorkersIdsToDelete) > 0) {
            $deleteAssignedWorkersTransaction = $this->deleteAssignedWorkersByIds($assignedWorkersIdsToDelete);
            $deleteAssignedAvailabilityTransaction = $this->deleteAssignedAvailabilityByIds($assignedAvailabilityIdsToDelete);
        }

        return $createAvailabilitiesTransaction &&
            $deleteAssignedWorkersTransaction &&
            $deleteAssignedAvailabilityTransaction &&
            $deleteProjectWorkersTimesheetTransaction;
    }

    public function updateAvailabilitiesBasedOnUserInputDuration($companyId, $workerId, $workerType, $userInputStartDate, $userInputEndDate)
    {
        $availabilitiesData = $this->fetchAvailabilitiesWithinRange($companyId, $workerId, $workerType, $userInputStartDate, $userInputEndDate);
        $availabilityId = $availabilitiesData[0]->id;
        $maxAvailabilityDate = $availabilitiesData[0]->max_availability_date;
        $minAvailabilityDate = $availabilitiesData[0]->min_availability_date;
        $workerRate = $availabilitiesData[0]->rate;
        $CRUDAvailabilitiesTransaction = true;
        if ($userInputStartDate == $minAvailabilityDate) {
            if ($userInputEndDate >= $maxAvailabilityDate) {
                $deleteAvailabilities = $this->deleteAvailabilities($availabilityId);
                if (!$deleteAvailabilities) {
                    $CRUDAvailabilitiesTransaction = false;
                }
            } else {
                $isLastData = $this->checkAvailabilitiesIfLastData($minAvailabilityDate, $maxAvailabilityDate);
                $availabilityData = array(
                    'min_availability_date' => date('Y-m-d', strtotime($userInputEndDate . '+1 day')),
                    'max_availability_date' => $isLastData ? date('Y-m-d', strtotime($userInputEndDate . '+10 years +1 day')) : $maxAvailabilityDate,
                );
                $updateAvailabilities = $this->updateAvailabilities($availabilityId, $availabilityData);
                if (!$updateAvailabilities) {
                    $CRUDAvailabilitiesTransaction = false;
                }
            }
        } else {
            $availabilityData = array(
                'max_availability_date' => date('Y-m-d', strtotime($userInputStartDate . '-1 day')),
            );
            $updateAvailabilities = $this->updateAvailabilities($availabilityId, $availabilityData);
            if (!$updateAvailabilities) {
                $CRUDAvailabilitiesTransaction = false;
            }
            if ($userInputEndDate < $maxAvailabilityDate) {
                $isLastData = $this->checkAvailabilitiesIfLastData($minAvailabilityDate, $maxAvailabilityDate);
                $availabilityData = array(
                    'worker_id' => $workerId,
                    'company_id' => $companyId,
                    'worker_type' => $workerType,
                    'rate' => $workerRate,
                    'min_availability_date' => date('Y-m-d', strtotime($userInputEndDate . '+1 day')),
                    'max_availability_date' => $isLastData ? date('Y-m-d', strtotime($userInputEndDate . '+10 years +1 day')) : $maxAvailabilityDate,
                );
                $createAvailabilities = $this->createAvailabilities($availabilityData);
                if (!$createAvailabilities) {
                    $CRUDAvailabilitiesTransaction = false;
                }
            }
        }
        return $CRUDAvailabilitiesTransaction;
    }

    public function checkAvailabilitiesIfLastData($maxAvailabilityDate, $minAvailabilityDate)
    {
        $maxAvailabilityDate = strtotime($maxAvailabilityDate);
        $minAvailabilityDate = strtotime($minAvailabilityDate);
        $diff = abs($maxAvailabilityDate - $minAvailabilityDate);
        $years = floor($diff / (365 * 60 * 60 * 24));
        return $years == 10 ? true : false;
    }

    public function restoreAvailabilitiesAndDeleteAssignedAvailabilitiesAndAssignedWorkersAndTimesheet($assignedAvailabilityDataToOverride, $workerId, $companyId, $workerType)
    {
        // variables to use
        $conflictingAssignedAvailabilityFirstData = $assignedAvailabilityDataToOverride[0];
        $conflictingAssignedAvailabilityFirstDataRate = $conflictingAssignedAvailabilityFirstData->rate;
        $assignedWorkersIdsToDelete = [];
        $assignedAvailabilityIdsToDelete = [];
        $createAvailabilitiesTransaction = true;
        $deleteAssignedAvailabilityTransaction = true;
        $deleteAssignedWorkersTransaction = true;
        $deleteProjectWorkersTimesheetTransaction = true;

        foreach ($assignedAvailabilityDataToOverride as $assignedAvailability) {
            $assignedAvailabilityId = $assignedAvailability->id;
            $assignedAvailabilityAssignedWorkerId = $assignedAvailability->assigned_worker_id;
            $assignedAvailabilityStartDate = $assignedAvailability->start_date;
            $assignedAvailabilityEndDate = $assignedAvailability->end_date;

            array_push($assignedWorkersIdsToDelete, $assignedAvailabilityAssignedWorkerId);
            array_push($assignedAvailabilityIdsToDelete, $assignedAvailabilityId);

            // create availabilities
            $availabilitiesData = array(
                'worker_id' => $workerId,
                'company_id' => $companyId,
                'worker_type' => $workerType,
                'rate' => $conflictingAssignedAvailabilityFirstDataRate,
                'min_availability_date' => $assignedAvailabilityStartDate,
                'max_availability_date' => $assignedAvailabilityEndDate,
            );
            $createAvailabilities = $this->createAvailabilities($availabilitiesData);
            if (!$createAvailabilities) {
                $createAvailabilitiesTransaction = false;
            }

            $assignedAvailabilityIsDraft = $assignedAvailability->is_draft;
            // delete timesheet data
            if (!$assignedAvailabilityIsDraft) {
                $deleteProjectWorkersTimesheetTransaction = $this->deleteProjectWorkersTimesheet($workerId, $workerType, $assignedAvailabilityStartDate, $assignedAvailabilityEndDate);
            }
        }

        if (count($assignedWorkersIdsToDelete) > 0) {
            $deleteAssignedWorkersTransaction = $this->deleteAssignedWorkersByIds($assignedWorkersIdsToDelete);
            $deleteAssignedAvailabilityTransaction = $this->deleteAssignedAvailabilityByIds($assignedAvailabilityIdsToDelete);
        }

        return $createAvailabilitiesTransaction &&
            $deleteAssignedWorkersTransaction &&
            $deleteAssignedAvailabilityTransaction &&
            $deleteProjectWorkersTimesheetTransaction;
    }
}