<?php

namespace App\Modules\Company\BorrowCart;

use App\Models\CompanyCart;
use App\Models\RequestContact;
use App\Models\BorrowRequest;
use App\Models\LoanRequest;
use App\Models\BorrowPendingWorkers;
use App\Models\LoanPendingWorkers;
use Illuminate\Support\Facades\DB;
use App\Http\Resources\Company\BorrowCart as BorrowCartResource;

use App\Traits\SearchTraits;
use App\Traits\SortingTraits;
use App\Traits\UserTraits;
use App\Traits\MobileNumberValidatorTraits;
use App\Traits\ValidatorTraits;
use App\Events\Company\CreateNotificationEvent;
use App\Models\WorkerRequestApplication;

class Cart{
    use SortingTraits, SearchTraits, UserTraits, ValidatorTraits, MobileNumberValidatorTraits;

    public function getData($payload)
    {
        $companyId = $this->getCurrentUser()->company_id;

        // sorting
        $sortField = $this->sortField($payload, 'date_from'); // date_from = default column to search
        $sortOrder = $this->sortOrder($payload, 'asc');

        // searching
        $durationStartDateSearch = $payload->durationStartDate;
        $durationEndValueSearch = $payload->durationEndValue;
        if (!empty($durationStartDateSearch) && !empty($durationEndValueSearch)) {
            $durationStartDateSearch = date('Y-m-d', strtotime($payload->durationStartDate));
            $durationEndValueSearch = date('Y-m-d', strtotime($payload->durationEndValue));
        }
        $workerIdSearch = $this->searchField($payload->worker_company_id);
        $zipcodeSearch = $this->searchField($payload->zip_code);
        $expertiseSearch = $payload->expertise_name;
        $experienceSearch = $payload->experience_name;
        $areaOfWorkSearch = $payload->work_area_lists;

        return BorrowCartResource::collection(
            CompanyCart::select(
                'company_carts.id',
                'company_carts.worker_id',
                'company_carts.date_from',
                'company_carts.date_to',
                'search.id as search_id',
                'search.worker_company_id',
                'search.zip_code',
                'search.hourly_rate',
                'worker_experiences.experience_id',
                'company_experiences.name as experience_name',
                'worker_experiences.expertise_id',
                'expertises.name as expertise_name')
                ->join('search', 'search.id', '=', 'company_carts.search_id')
                ->join('worker_experiences', 'worker_experiences.worker_id', '=', 'company_carts.worker_id')
                ->join('company_experiences', 'company_experiences.id', '=', 'worker_experiences.experience_id')
                ->join('expertises', 'expertises.id', '=', 'worker_experiences.expertise_id')
                ->with('work_area_lists')->whereHas('work_area_lists', function ($query) use ($areaOfWorkSearch) {
                    $query->when(!empty($areaOfWorkSearch), function ($query) use ($areaOfWorkSearch) {
                        return $query->whereIn('work_area_lists.work_area_id', $areaOfWorkSearch);
                    });
                })
                ->where('company_carts.company_id', $companyId)
                ->where('worker_experiences.is_worker', true)
                ->when(!empty($workerIdSearch), function ($query) use ($workerIdSearch) {
                    return $query->where('search.worker_company_id', 'LIKE', $workerIdSearch . '%');
                })
                ->when(!empty($zipcodeSearch), function ($query) use ($zipcodeSearch) {
                    return $query->where('search.zip_code', 'LIKE', $zipcodeSearch . '%');
                })
                ->when(!empty($expertiseSearch), function ($query) use ($expertiseSearch) {
                    return $query->whereIn('expertises.id', $expertiseSearch);
                })
                ->when(!empty($experienceSearch), function ($query) use ($experienceSearch) {
                    return $query->whereIn('company_experiences.id', $experienceSearch);
                })
                ->when(!empty($durationStartDateSearch) && !empty($durationEndValueSearch), function ($query) use ($durationStartDateSearch, $durationEndValueSearch) {
                    return $query->where(function ($query) use ($durationStartDateSearch, $durationEndValueSearch) {
                        $query->where('company_carts.date_from', $durationStartDateSearch)
                            ->orWhere('company_carts.date_to', $durationEndValueSearch)
                            ->orWhereBetween('company_carts.date_from', [$durationStartDateSearch, $durationEndValueSearch])
                            ->orWhereBetween('company_carts.date_to', [$durationStartDateSearch, $durationEndValueSearch])
                            ->orWhereRaw('? BETWEEN company_carts.date_from and company_carts.date_to', [$durationStartDateSearch])
                            ->orWhereRaw('? BETWEEN company_carts.date_from and company_carts.date_to', [$durationEndValueSearch]);
                    });
                })
                ->orderBy($sortField, $sortOrder)
                ->paginate(20)
        )->response()->setStatusCode(201);
    }

    /**
     * GET COMPANY CART COUNT 
     * 
     * @return int 
     */
    public function getCount() : int 
    {
        $company_id = $this->getCurrentUser()->company_id;
        
        $cart = CompanyCart::select('id')
            ->where('company_carts.company_id', '=', $company_id)
            ->whereNull('company_carts.deleted_at')
            ->distinct()
            ->get();

        return $cart->count();
    }

    public function remove($id)
    {
        $companyId  =   $this->getCurrentUser()->company_id;

        $cartItem   =   CompanyCart::where('company_carts.id', $id)
                            ->where('company_carts.company_id', $companyId)
                            ->first();

        //! RESET WORKER REQUEST APPLICATION STATUS IF EXIST
        WorkerRequestApplication::query()
            ->with('workerRequest')
            ->select('id')
            ->where(
                [
                    ['worker_id',  '=', $cartItem->worker_id], 
                    ['status',     '=', 'in-cart'           ]
                ]
            )
            ->whereHas(
                'workerRequest',
                function ($q) use ($companyId) 
                {
                    $q->where('worker_requests.company_id', '=', $companyId);
                }
            )
            ->update(
                [
                    'status' => 'pending', 
                ]
            );

        $cartItem->forceDelete(); 

        return response()->json(
            [
                'success' => 'Worker removed from cart successfully.'
            ]
        )->setStatusCode(201);
    }

    public function submit($payload)
    {
        $rules = array(
            'contactName' => 'required|max:100',
            'contactNumber' => 'required|max:20',
            'street' => 'required|max:100',
            'city' => 'required|max:50',
            'state' => 'required|max:50',
            'zipcode' => 'required|string|min:5|max:5',
            'contactEmailAddress' => 'required|max:100',
        );

        $validate = $this->validateRequest($payload, $rules);

        if ($validate) 
        {
            return $validate;
        }
        
        $validate = $this->validatePhoneNumber($payload->contactNumber);

        if ($validate) 
        {
            return response()->json(['errors' => ["Sorry, you have used non-existing US mobile number."]])->setStatusCode(201);
        }

        // start transaction
        DB::beginTransaction();

        $loanBorrowRequestTransaction = true;
        $deleteCompanyCartTransaction = true;

        $borrowingCompanyId = $this->getCurrentUser()->company_id;
        
        $loaningCompanies = CompanyCart::select('company_users.company_id')
            ->join('search', 'search.id', '=', 'company_carts.search_id')
            ->join('company_users', 'company_users.worker_id', '=', 'company_carts.worker_id')
            ->where('company_carts.company_id', $borrowingCompanyId)
            ->whereNotNull('company_users.company_id')
            ->distinct()
            ->groupBy('company_users.company_id')
            ->get(['company_carts.company_id']); //used to identidy which column to be distinct

        foreach ($loaningCompanies as $loaningCompany) 
        {
            $loaningCompanyId = $loaningCompany->company_id;

            $loanedWorkersPerLoaningCompany = CompanyCart::select(
                DB::raw('DISTINCT(company_carts.worker_id)'),
                'company_carts.id',
                'company_carts.search_id',
                'company_carts.worker_id',
                'company_carts.date_from',
                'company_carts.date_to',
                'search.original_rate',
                'search.hourly_rate')
                ->join('search', 'search.id', '=', 'company_carts.search_id')
                ->join('company_users', 'company_users.worker_id', '=', 'company_carts.worker_id')
                ->where('company_carts.company_id', $borrowingCompanyId)
                ->where('company_users.company_id', $loaningCompanyId)
                ->get(); //used to identidy which column to be distinct

            $saveLoanBorrowRequest = $this->saveLoanBorrowRequest($borrowingCompanyId, $loaningCompanyId, $loanedWorkersPerLoaningCompany, $payload);

            if (!$saveLoanBorrowRequest) 
            {
                $loanBorrowRequestTransaction = false;
            }
        }

        $deleteCompanyCartTransaction = $this->deleteCompanyCart($borrowingCompanyId);

        // if there's an error or queries don't do their job, rollback!
        if (!$loanBorrowRequestTransaction || !$deleteCompanyCartTransaction) 
        {
            DB::rollBack();

            return response()->json(['error' => 'Server error.']);
        } 
        else 
        {
            DB::commit();

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

    public function saveLoanBorrowRequest($borrowingCompanyId, $loaningCompanyId, $loanedWorkersPerLoaningCompany, $payload)
    {
        //save request contact data
        $requestContactData = 
        [
            'contact_person_name' => $payload->contactName,
            'contact_person_no'   => $this->databaseFormatNumber($payload->contactNumber),
            'street'              => $payload->street,
            'city'                => $payload->city,
            'state'               => $payload->state,
            'zipcode'             => $payload->zipcode,
            'email_address'       => $payload->contactEmailAddress,
        ];

        $requestContactTransaction = RequestContact::create($requestContactData);

        // save borrow request
        $requestedBy = $this->getCurrentUser()->id;

        $borrowRequestData = 
        [
            'loaner_company_id'   => $loaningCompanyId,
            'borrower_company_id' => $borrowingCompanyId,
            'requested_by'        => $requestedBy,
            'note'                => $payload->requesterNote,
            'request_contact_id'  => $requestContactTransaction->id,
        ];
        $saveBorrowRequestTransaction = BorrowRequest::create($borrowRequestData);
        
        if (!$saveBorrowRequestTransaction) 
        {
            return false;
        }

        $savedBorrowRequestLatestId = $saveBorrowRequestTransaction->id;

        // save loan request
        $loanRequestData = 
        [
            'loaner_company_id'   => $loaningCompanyId,
            'borrower_company_id' => $borrowingCompanyId,
            'note'                => $payload->requesterNote,
            'request_contact_id'  => $requestContactTransaction->id,
        ];

        $saveLoanRequestTransaction = LoanRequest::create($loanRequestData);

        if (!$saveLoanRequestTransaction) 
        {
            return false;
        }
        $savedLoanRequestLatestId = $saveLoanRequestTransaction->id;

        // save loan and borrow pending workers
        $counter        = 0;
        $totalRequested = count($loanedWorkersPerLoaningCompany);

        foreach ($loanedWorkersPerLoaningCompany as $loanedWorker) 
        {
            $counter++;
            // save loan pending workers
            $loanPendingWorkerData = 
            [
                'search_id'         => $loanedWorker->search_id,
                'borrow_request_id' => $savedBorrowRequestLatestId,
                'loan_request_id'   => $savedLoanRequestLatestId,
                'worker_id'         => $loanedWorker->worker_id,
                'date_from'         => $loanedWorker->date_from,
                'date_to'           => $loanedWorker->date_to,
                'original_rate'     => $loanedWorker->original_rate,
                'rate'              => $loanedWorker->hourly_rate,
                'approver_note'     => '',
                'status'            => 'Pending',
            ];

            $saveLoanPendingWorkerTransaction = LoanPendingWorkers::create($loanPendingWorkerData);
            
            if (!$saveLoanPendingWorkerTransaction) 
            {
                return false;
            }

            // save borrow pending workers
            $borrowPendingWorkerData = 
            [
                'search_id'         => $loanedWorker->search_id,
                'borrow_request_id' => $savedLoanRequestLatestId,
                'worker_id'         => $loanedWorker->worker_id,
                'date_from'         => $loanedWorker->date_from,
                'date_to'           => $loanedWorker->date_to,
                'original_rate'     => $loanedWorker->original_rate,
                'rate'              => $loanedWorker->hourly_rate,
                'approver_note'     => '',
                'status'            => 'Pending',
            ];

            $saveBorrowPendingWorkerTransaction = BorrowPendingWorkers::create($borrowPendingWorkerData);

            //! IF CART ITEM CAME FROM WORKER REQUEST, UPDATE THEIR STATUS
            WorkerRequestApplication::query()
                ->with('workerRequest')
                ->select('id')
                ->where(
                    [
                        ['worker_id',  '=', $loanedWorker->worker_id],
                        ['status',     '=', 'in-cart']
                    ]
                )
                ->whereHas(
                    'workerRequest',
                    function ($q) use ($borrowingCompanyId) 
                    {
                        $q->where('worker_requests.company_id', '=', $borrowingCompanyId);
                    }
                )
                ->update(
                    [
                        'status' => 'accepted',
                    ]
                );

            if (!$saveBorrowPendingWorkerTransaction) 
            {
                return false;
            }

            if ($counter == $totalRequested) 
            {
                event(new CreateNotificationEvent("create", "request", $saveLoanPendingWorkerTransaction->id, $savedLoanRequestLatestId, $savedBorrowRequestLatestId));
            }
        }

        return true;
    }

    public function deleteCompanyCart($borrowingCompanyId)
    {
        return CompanyCart::where('company_carts.company_id', $borrowingCompanyId)->forceDelete();
    }

}