<?php

namespace App\Modules\Company\Whiteboard;

use App\Models\AssignedAvailability;
use App\Models\AssignedWorkers;
use App\Models\Availabilities;
use App\Models\ProjectWorkersTimesheet;
use App\Modules\Company\Whiteboard\ReassignWorker\ConflictChecker\AssignedAvailabilityConflictChecker;
use App\Modules\Company\Whiteboard\ReassignWorker\ConflictChecker\UnavailableSchedulesConflictChecker;
use App\Modules\Company\Whiteboard\Validation;
use App\Traits\UserTraits;
use App\Traits\ValidatorTraits;
use Illuminate\Support\Facades\DB;

class ReassignWorkerToProject
{
    use UserTraits, ValidatorTraits;

    private $assignedAvailabilityConflictChecker;
    private $unavailableSchedulesConflictChecker;
    private $validation;

    public function __construct()
    {
        $this->assignedAvailabilityConflictChecker = new AssignedAvailabilityConflictChecker;
        $this->unavailableSchedulesConflictChecker = new UnavailableSchedulesConflictChecker;
        $this->validation = new Validation;
    }

    public function reassign($payload)
    {
        DB::beginTransaction();

        $companyId = $this->getCurrentUser()->company_id;
        $projectId = $payload->project_id;
        $workerId = $payload->worker_id;
        $assignedWorkerId = $payload->assigned_worker_id;
        $workerType = $payload->worker_type;
        $action = $payload->action;
        $userInputStartDate = date('Y-m-d', strtotime($payload->start_date));
        $userInputEndDate = date('Y-m-d', strtotime($payload->end_date));
        $isDraft = $payload->is_draft;
        $dateToday = date('Y-m-d');
        $dateYesterday = date('Y-m-d', strtotime($dateToday . '-1 day'));
        $CRUDAvailabilitiesTransaction = true;
        $CRUDAssignedAvailabilityTransaction = true;
        $CRUDAssignedWorkersTransaction = true;
        $deleteProjectWorkersTimesheetTransaction = true;

        if ($userInputEndDate < $dateToday && $isDraft == 0) {
            return response()->json(['error' => 'Unable to reassign employee for the past dates.'])->setStatusCode(201);
        }

        $validation = $this->validation->validatePayloadOnAssigningWorker($payload, $userInputStartDate, $userInputEndDate);
        if (!$validation) {
            return $validation;
        }

        $checkConflictForUnavailableSchedules = $this->unavailableSchedulesConflictChecker->checkConflictForUnavailableSchedules(
            $companyId,
            $workerId,
            $workerType,
            $userInputStartDate,
            $userInputEndDate);
        if ($checkConflictForUnavailableSchedules != 'no conflict') {
            return $checkConflictForUnavailableSchedules;
        }

        $conflictAssignedAvailability = $this->assignedAvailabilityConflictChecker->checkConflictForAssignedAvailability(
            $companyId,
            $workerId,
            $workerType,
            $userInputStartDate,
            $userInputEndDate);
        $conflictAssignedAvailabilityCount = count($conflictAssignedAvailability);

        // fetch latest worker rate from availabilities table
        $workerRate = $this->fetchWorkerRateFromAvailabilitiesTable($companyId, $workerId, $workerType)->rate;

        if ($conflictAssignedAvailabilityCount > 0) {
            if ($action == null && $conflictAssignedAvailabilityCount > 1) {
                return response()->json([
                    'error' => 'Worker was already assigned from one of the selected dates.',
                    'conflicting_dates' => $conflictAssignedAvailability,
                    'allow_override' => true,
                ])->setStatusCode(201);
            } else {
                // variables to use
                $conflictingAssignedAvailabilityFirstData = $conflictAssignedAvailability[0];
                $conflictingAssignedAvailabilityFirstDataId = $conflictingAssignedAvailabilityFirstData->id;
                $conflictingAssignedAvailabilityFirstDataAvailabilityId = $conflictingAssignedAvailabilityFirstData->availability_id;
                $conflictingAssignedAvailabilityFirstDataRate = $conflictingAssignedAvailabilityFirstData->rate;
                $conflictingAssignedAvailabilityFirstDataStartDate = $conflictingAssignedAvailabilityFirstData->start_date;
                $conflictingAssignedAvailabilityFirstDataIsDraft = $conflictingAssignedAvailabilityFirstData->is_draft;
                $conflictingAssignedAvailabilityLastData = $conflictAssignedAvailability[$conflictAssignedAvailabilityCount - 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($conflictAssignedAvailability, $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($conflictAssignedAvailability, $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;
                    }
                }
            }
        } else {
            // fetch original assigned_availability data
            $assignedAvailabilityDefaultData = $this->fetchDefaultAssignedAvailabilityData($assignedWorkerId);
            $assignedAvailabilityDefaultDataStartDate = $assignedAvailabilityDefaultData->start_date;
            $assignedAvailabilityDefaultDataEndDate = $assignedAvailabilityDefaultData->end_date;
            $assignedAvailabilityDefaultDataIsDraft = $assignedAvailabilityDefaultData->is_draft;
            $availabilitiesData = $this->fetchAvailabilitiesData($workerId, $workerType, $userInputStartDate, $userInputEndDate, $companyId);
            $availabilitiesDataId = $availabilitiesData->id;
            $availabilitiesDataRate = $availabilitiesData->rate;

            /*
             * no conflicting schedule. re-assign worker directly into a project with past dates
             */
            if ($assignedAvailabilityDefaultDataStartDate < $dateToday && $assignedAvailabilityDefaultDataIsDraft == 0) {
                // 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' => $availabilitiesDataId,
                    'assigned_worker_id' => $createAssignedWorkers->id,
                    'worker_type' => $workerType,
                    'rate' => $availabilitiesDataRate,
                    '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;
                }
            }
            /*
             * no conflicting schedule. re-assign worker directly into a project without past dates
             */
            else {
                // create availabilities
                $availabilitiesData = array(
                    'worker_id' => $workerId,
                    'company_id' => $companyId,
                    'worker_type' => $workerType,
                    'rate' => $workerRate,
                    'min_availability_date' => $assignedAvailabilityDefaultDataStartDate,
                    'max_availability_date' => $assignedAvailabilityDefaultDataEndDate,
                );
                $createAvailabilities = $this->createAvailabilities($availabilitiesData);
                if (!$createAvailabilities) {
                    $CRUDAvailabilitiesTransaction = false;
                }

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

                // update assigned_workers
                $assignedWorkersData = array(
                    'project_id' => $projectId,
                );
                $updateAssignedWorkers = $this->updateAssignedWorkers($assignedWorkerId, $assignedWorkersData);
                if (!$updateAssignedWorkers) {
                    $CRUDAssignedWorkersTransaction = false;
                }

                // update assigned_availability
                $assignedAvailabilityData = array(
                    'rate' => $workerRate,
                    'start_date' => $userInputStartDate,
                    'end_date' => $userInputEndDate,
                    'is_draft' => 1,
                );
                $updateAssignedAvailability = $this->updateAssignedAvailability($assignedWorkerId, $assignedAvailabilityData);
                if (!$updateAssignedAvailability) {
                    $CRUDAssignedAvailabilityTransaction = false;
                }

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

        if (!$CRUDAvailabilitiesTransaction ||
            !$CRUDAssignedAvailabilityTransaction ||
            !$CRUDAssignedWorkersTransaction ||
            !$deleteProjectWorkersTimesheetTransaction) {
            DB::rollBack();
            return response()->json(['error' => 'Server error.']);
        } else {
            DB::commit();
            return response()->json(['success' => 'Data added successfully.'])->setStatusCode(201);
        }
    }

    public function fetchWorkerRateFromAvailabilitiesTable($companyId, $workerId, $workerType)
    {
        return Availabilities::select(
            'availabilities.rate')
            ->where('availabilities.company_id', $companyId)
            ->where('availabilities.worker_id', $workerId)
            ->where('availabilities.worker_type', $workerType)
            ->orderBy('availabilities.updated_at', 'desc')
            ->withTrashed()
            ->first();
    }

    public function fetchDefaultAssignedAvailabilityData($assignedWorkerId)
    {
        return AssignedAvailability::select(
            'assigned_availability.start_date',
            'assigned_availability.end_date',
            'assigned_availability.is_draft')
            ->where('assigned_availability.assigned_worker_id', $assignedWorkerId)
            ->first();
    }

    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 updateAssignedWorkers($assignedWorkersId, $assignedWorkersData)
    {
        return AssignedWorkers::where('assigned_workers.id', $assignedWorkersId)->update($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 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 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 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) {
                $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' => date('Y-m-d', strtotime($userInputEndDate . '+10 years +1 day')),
                );
                $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;
    }

    public function fetchAvailabilitiesData($workerId, $workerType, $userInputStartDate, $userInputEndDate, $companyId)
    {
        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 ($userInputStartDate, $userInputEndDate) {
                $query->where('availabilities.min_availability_date', $userInputStartDate)
                    ->orWhere('availabilities.max_availability_date', $userInputEndDate)
                    ->orWhereBetween('availabilities.min_availability_date', [$userInputStartDate, $userInputEndDate])
                    ->orWhereBetween('availabilities.max_availability_date', [$userInputStartDate, $userInputEndDate])
                    ->orWhereRaw('? BETWEEN availabilities.min_availability_date and availabilities.max_availability_date', [$userInputStartDate])
                    ->orWhereRaw('? BETWEEN availabilities.min_availability_date and availabilities.max_availability_date', [$userInputEndDate]);
            })
            ->orderBy('availabilities.min_availability_date', 'asc')
            ->orderBy('availabilities.max_availability_date', 'asc')
            ->first();
    }
}