<?php

namespace App\Modules\BookingTour\Domain;

use App\Http\Controllers\Owner\ModuleNotification\Index;
use App\Modules\BookingTour\Domain\IdBookingTour;
use App\Modules\HtlCamp\Domain\HtlCamp;
use App\Modules\HtlCampBooking\Domain\HtlCampBooking;
use App\Modules\HtlCampLock\Domain\HtlCampLock;
use App\Modules\HtlCampLock\Domain\HtlCampLockStatus;
use App\Modules\HtlCampSchedule\Application\Find\FindHtlCampScheduleQuery;
use App\Modules\HtlCampSchedule\Domain\HtlCampSchedule;
use App\Modules\Shared\Domain\Bus\Query\QueryBus;
use App\Modules\Shared\Domain\DateStart;
use App\Modules\Shared\Domain\DateUtils;
use App\Modules\Shared\Domain\QueryBuilder\Operator\OperatorAnd;
use App\Modules\Shared\Domain\QueryBuilder\Operator\OperatorBuilder;
use App\Modules\Shared\Domain\QueryBuilder\Operator\OperatorEqual;
use App\Modules\Shared\Domain\QueryBuilder\Operator\OperatorIn;
use App\Modules\Shared\Domain\Storage\Criteria\StorageCriteria;
use App\Modules\Shared\Domain\Utils;
use DateTime;
use Illuminate\Support\Facades\DB;

class DateChangesBookingTour implements StorageCriteria
{

    public function __construct(
        private IdBookingTour $idBookingTour,
        private BookingTourDateStart $dateStart,
        private QueryBus $queryBus
    ) {}


    public function run()
    {
        return $this->analize($this->query());
    }

    protected function query()
    {


        $selectTour = 't.Id_BookingTour, t.BookingTour_DateStart, t.BookingTour_Type, t.BookingTour_NoPax, t.BookingTour_Name, t.BookingTour_Group, t.Id_Booking, t.Id_Package, t.Id_Tour';

        $selectBookingExtra = 'be.Id_BookingExtra, be.BookingExtra_Quantity, be.BookingExtra_Price, be.BookingExtra_Total, be.BookingExtra_Feature, be.BookingExtra_Show, be.BookingExtra_Included, be.Id_Provider, be.Id_BookingTour, e.Id_Extra, e.Extra_Code, e.Extra_Name, e.Extra_Feature, e.Extra_Price, e.Extra_PriceConfidential, e.Extra_Group, e.Extra_Status, e.Extra_Lock, e.Id_TypeExtra, e.Id_Location,e.Id_Extra_Refer';
        $selectBookingExtraPassenger = 'bep.Id_BookingExtraPassenger, bep.BookingExtraPassenger_Date, bep.BookingExtraPassenger_DateEntryStart, bep.BookingExtraPassenger_DateEntryEnd, bep.BookingExtraPassenger_DatePurchase, bep.BookingExtraPassenger_Code, bep.BookingExtraPassenger_Amount, bep.BookingExtraPassenger_Remark, bep.BookingExtraPassenger_Mask, bep.BookingExtraPassenger_Status, bep.Id_Admin, bep.Id_BookingExtra, btp.Id_BookingTourPassenger,         btp.BookingTourPassenger_Date, btp.BookingTourPassenger_DatePrint, btp.BookingTourPassenger_Lock, btp.BookingTourPassenger_Reportable, btp.BookingTourPassenger_Hotel, btp.BookingTourPassenger_Remark, btp.BookingTourPassenger_Price, btp.BookingTourPassenger_Full, btp.BookingTourPassenger_ValidGroups, btp.BookingTourPassenger_NoValidGroups, btp.BookingTourPassenger_HasBeenPrinted, btp.BookingTourPassenger_Status, btp.BookingTourPassenger_PaymentStatus, btp.Id_BookingTour, p.Id_Passenger, p.Passenger_DateCreate, p.Passenger_DateEdit, p.Passenger_Name, p.Passenger_LastName, p.Passenger_Email, p.Passenger_Phone, p.Passenger_PhoneCode, p.Passenger_Gender, p.Passenger_DOB, p.Passenger_NoDocument, p.Passenger_DateEntrance, p.Passenger_Restriction, p.Passenger_Extras, p.Passenger_Order, p.Passenger_ImgDocument1, p.Passenger_ImgDocument2, p.Passenger_Token, p.Passenger_Reportable, p.Passenger_Status, p.Passenger_PaymentStatus, p.Passenger_InfoStatus, p.Passenger_CheckedInStatus, p.Passenger_ConfirmMessage, p.Passenger_Full, p.Id_Booking, p.Id_TypeDocument, p.Id_UserCountry, p.Id_TypeRate';
        $selectPassengersRoom = 'rp.Id_RoomPassenger, rp.RoomPassenger_IsNotValid, rp.RoomPassenger_Status, rp.Id_BookingExtraPassenger, rp.Id_Room, rp.Id_Passenger, rp.Id_PaxInformationRoomPassenger, p.Id_Passenger, p.Passenger_Name, p.Passenger_LastName, p.Passenger_Email, p.Passenger_Phone, p.Passenger_PhoneCode, p.Passenger_Gender, p.Passenger_DOB, p.Passenger_NoDocument, p.Passenger_Restriction, p.Passenger_Extras, p.Passenger_Order, p.Passenger_Reportable, p.Passenger_Status, p.Passenger_PaymentStatus, p.Passenger_InfoStatus, p.Passenger_CheckedInStatus, p.Passenger_ConfirmMessage, p.Passenger_Full, p.Id_Booking, p.Id_TypeDocument, p.Id_UserCountry';




        $selectBookingUpgrade = 'bu.Id_BookingUpgrade, bu.BookingUpgrade_Quantity, bu.BookingUpgrade_Price, bu.BookingUpgrade_Total, bu.BookingUpgrade_Feature, bu.BookingUpgrade_Included, bu.Id_Provider, bu.Id_BookingTour,         u.Id_Upgrade, u.Upgrade_Code, u.Upgrade_Name, u.Upgrade_Feature, u.Upgrade_Price, u.Upgrade_PriceConfidential, u.Upgrade_OneWay, u.Upgrade_Status, u.Upgrade_Lock, u.Id_TypeUpgrade, u.Id_Upgrade_Refer';
        $selectBookingUpgradePassenger = 'bup.Id_BookingUpgradePassenger, bup.BookingUpgradePassenger_Is23MigrationValue,	bup.BookingUpgradePassenger_Date,	bup.BookingUpgradePassenger_OnlyReturn,	bup.BookingUpgradePassenger_DateEntry,	bup.BookingUpgradePassenger_Datereturn,	bup.BookingUpgradePassenger_DatePurchase,	bup.BookingUpgradePassenger_Carrier,	bup.BookingUpgradePassenger_Code,	bup.BookingUpgradePassenger_Amount,	bup.BookingUpgradePassenger_RoundCarrier,	bup.BookingUpgradePassenger_RoundCode,	bup.BookingUpgradePassenger_RoundAmount,	bup.BookingUpgradePassenger_Remark,	bup.BookingUpgradePassenger_Mask,	bup.BookingUpgradePassenger_Status,	bup.Id_Provider,	bup.Id_Provider_Return,	bup.Id_Admin,	bup.Id_BookingUpgrade, btp.Id_BookingTourPassenger,	btp.BookingTourPassenger_Date,	btp.BookingTourPassenger_DatePrint,	btp.BookingTourPassenger_Lock,	btp.BookingTourPassenger_Reportable,	btp.BookingTourPassenger_Hotel,	btp.BookingTourPassenger_Remark,	btp.BookingTourPassenger_Price,	btp.BookingTourPassenger_Full,	btp.BookingTourPassenger_ValidGroups,	btp.BookingTourPassenger_NoValidGroups,	btp.BookingTourPassenger_HasBeenPrinted,	btp.BookingTourPassenger_Status,	btp.BookingTourPassenger_PaymentStatus,	btp.Id_BookingTour, p.Id_Passenger,	p.Passenger_DateCreate,	p.Passenger_DateEdit,	p.Passenger_Name,	p.Passenger_LastName,	p.Passenger_Email,	p.Passenger_Phone,	p.Passenger_PhoneCode,	p.Passenger_Gender,	p.Passenger_DOB,	p.Passenger_NoDocument,	p.Passenger_DateEntrance,	p.Passenger_Restriction,	p.Passenger_Extras,	p.Passenger_Order,	p.Passenger_ImgDocument1,	p.Passenger_ImgDocument2,	p.Passenger_Token,	p.Passenger_Reportable,	p.Passenger_Status,	p.Passenger_PaymentStatus,	p.Passenger_InfoStatus,	p.Passenger_CheckedInStatus,	p.Passenger_ConfirmMessage,	p.Passenger_Full,	p.Id_Booking,	p.Id_TypeDocument,	p.Id_UserCountry,	p.Id_TypeRate';


        $selectPassengers = 'btp.Id_BookingTourPassenger, btp.BookingTourPassenger_Date, btp.BookingTourPassenger_Lock, btp.BookingTourPassenger_Remark, btp.BookingTourPassenger_ValidGroups, btp.BookingTourPassenger_NoValidGroups, btp.BookingTourPassenger_Status, btp.Id_BookingTour, p.Id_Passenger, p.Passenger_Name, p.Passenger_LastName, p.Passenger_Email, p.Passenger_Phone, p.Passenger_PhoneCode, p.Passenger_Gender, p.Passenger_DOB, p.Passenger_NoDocument, p.Passenger_Status, p.Id_Booking, p.Id_TypeDocument, p.Id_UserCountry ';

        $selectRooms = 'r.Id_Room, r.Room_RequestDateStart, r.Room_RequestDateEnd, r.Room_RequestLastDateStart, r.Room_RequestLastDateEnd, r.Room_ReservationDateAlarm, r.Room_ReservationDateFinal, r.Room_Date, r.Room_Observation, r.Room_BuyPrice, r.Room_Paid, r.Room_BuyDateStart, r.Room_BuyDateEnd, r.Room_BuyDatePayment, r.Room_BuyCode, r.Room_Lock, r.Room_SoftLock, r.Room_PassengersAmount, r.Room_NoValidAmount, r.Room_DateChanged, r.Room_Status, r.Id_Booking, r.Id_BookingExtra, r.Id_Provider, r.Id_Provider_Buy, r.Id_PaxInformationRoom, r.Id_Admin, r.Id_Admin_Validated, r.Id_TypePayment, tr.Id_TypeRoom, tr.TypeRoom_Name, tr.TypeRoom_Abrv, tr.TypeRoom_Capacity, tr.TypeRoom_Status';


        $selectTourLock = 'tl.Id_TourLock, tl.TourLock_DateStart, tl.TourLock_DateEnd, tl.TourLock_Remark, tl.Id_Tour ';

        $selectCampBooking = 'hcb.Id_HtlCampBooking, hcb.HtlCampBooking_Date, hcb.HtlCampBooking_DateStart, hcb.HtlCampBooking_Amount, hcb.HtlCampBooking_AmountTotal, hcb.HtlCampBooking_Status, hcb.HtlCampBooking_Lock, hcb.HtlCampBooking_Remark, hcb.Id_HtlCampSchedule, hcb.Id_BookingTour';
        $selectCampLock = 'hcl.Id_HtlCampLock, hcl.HtlCampLock_Date, hcl.HtlCampLock_DateStart, hcl.HtlCampLock_Remark, hcl.HtlCampLock_Status, hcl.Id_HtlCampSchedule';
        $selectCampSchedule = 'hcs.Id_HtlCampSchedule, hcs.HtlCampSchedule_Date, hcs.HtlCampSchedule_DateSchedule, hcs.HtlCampSchedule_FromCamp, hcs.HtlCampSchedule_Availability, hcs.HtlCampSchedule_InternalAvailability, hcs.HtlCampSchedule_ExternalAvailability, hcs.HtlCampSchedule_AmountUsage, hcs.HtlCampSchedule_Lock, hc.Id_HtlCamp, hc.HtlCamp_Name, hc.HtlCamp_Abrv, hc.HtlCamp_Icon, hc.HtlCamp_Icon_Thumb, hc.HtlCamp_Availability, hc.HtlCamp_InternalAvailability, hc.HtlCamp_ExternalAvailability, hc.HtlCamp_Order, hc.HtlCamp_Status, hc.Id_HtlTypeCamp';


        $selectGroup = 'g.Id_Group, g.Group_Date, g.Group_DateUpdate, g.Group_DateStart, g.Group_DateEnd, g.Group_Hour, g.Group_Name, g.Group_Token, g.Group_Origin, g.Group_Destination, g.Group_Type, g.Group_Order, g.Group_TourDuration, g.Group_Remark, g.Group_MasterType, g.Group_ServiceType, g.Group_NoValidPassengers, g.Group_ValidPassengers, g.Group_Status, g.Id_Master, g.Id_Service, g.Id_StatusGroup';


        // PART TOUR SERVICES EXIST

        $idBookingTour = $this->idBookingTour->value();

        $existingServices  = array_merge(
            array_map(
                function ($upgrade) {
                    $upgrade->id = $upgrade->Id_BookingUpgrade;
                    $upgrade->cant = $upgrade->BookingUpgrade_Quantity;
                    $upgrade->price = $upgrade->BookingUpgrade_Price;
                    $upgrade->total = $upgrade->BookingUpgrade_Total;
                    $upgrade->included = $upgrade->BookingUpgrade_Included;
                    $upgrade->name = $upgrade->Upgrade_Name;
                    $upgrade->type = 1;
                    $upgrade->show = 2;
                    return $upgrade;
                },
                DB::select('call sp_booking_upgrade_list(?)', [$idBookingTour]),            
            ),
            array_map(
                function ($extra) {

                    $extra->id = $extra->Id_BookingExtra;
                    $extra->cant = $extra->BookingExtra_Quantity;
                    $extra->price = $extra->BookingExtra_Price;
                    $extra->total = $extra->BookingExtra_Total;
                    $extra->included = $extra->BookingExtra_Included;
                    $extra->name = $extra->Extra_Name;
                    $extra->type = 2;
                    $extra->show = $extra->BookingExtra_Show;
                    return $extra;
                },
                DB::select('call sp_booking_extra_list(?)', [$idBookingTour]),
            ),
            array_map(
                function ($equipment) {

                    $equipment->id = $equipment->Id_BookingEquipment;
                    $equipment->cant = $equipment->BookingEquipment_Quantity;
                    $equipment->price = $equipment->BookingEquipment_Price;
                    $equipment->total = $equipment->BookingEquipment_Total;
                    $equipment->included = $equipment->BookingEquipment_Included;
                    $equipment->name = $equipment->Item_Name;
                    $equipment->type = 3;
                    $equipment->show = 2;
                    return $equipment;
                },
                DB::select('call sp_booking_equipment_list(?)', [$idBookingTour]),
            ),
        );        

        // GET PASAJEROS DE CADA SERVICIO            
        foreach ($existingServices as $service) {
            $service ->  passengers = $this->getPassengersByService($service);
        }                        
        // END PART TOUR SERVICES EXIST
        
        
        $resultTour = DB::select(
            "SELECT $selectTour
            FROM t_booking_tour AS t
            WHERE t.Id_BookingTour = ?",
            [
                $this->idBookingTour->value()
            ]
        );


        // EXTRA RESULT
        $resultItemExtra = DB::select(
            "SELECT $selectBookingExtra,
                    ex.Extra_Code AS Extra_Refer_Code,
                    ex.Extra_Name AS Extra_Refer_Name
            FROM t_booking_extra AS be
            INNER JOIN t_extra as e
            ON be.Id_Extra = e.Id_Extra

            LEFT JOIN t_extra AS ex
            ON e.Id_Extra_Refer = ex.Id_Extra

            WHERE be.Id_BookingTour = ?",
            [
                $resultTour[0]->Id_BookingTour
            ]
        );


        foreach ($resultItemExtra as $index => $bookingExtra) {

            $rooms = DB::select(
                "SELECT $selectRooms
                FROM t_room as r 
                INNER JOIN t_type_room as tr 
                ON r.Id_TypeRoom = tr.Id_TypeRoom
                WHERE (r.Room_Status = 4 OR r.Room_Status = 5) AND r.Id_BookingExtra = ?
                GROUP BY r.Id_Room",
                [
                    $bookingExtra->Id_BookingExtra
                ]
            );

            $flagRoomsConfirmed = false;

            foreach ($rooms as $indexRoom => $room) {
                $passengersRoom =  DB::select(
                    "SELECT $selectPassengersRoom
                    FROM t_room_passenger as rp 
                    INNER JOIN t_passenger as p
                    ON rp.Id_Passenger = p.Id_Passenger
                    WHERE rp.Id_Room = ?",
                    [
                        $room->Id_Room
                    ]
                );

                $flagRoomsConfirmed = true;
                $rooms[$indexRoom]->passengersRoom = $passengersRoom;
            }


            $passengers = [];


            if ($flagRoomsConfirmed) {
                // PASAJEROS
                $passengers = DB::select(
                    "SELECT $selectBookingExtraPassenger
                    FROM t_booking_extra_passenger as bep
                    INNER JOIN t_booking_tour_passenger as btp
                    ON bep.Id_BookingTourPassenger =  btp.Id_BookingTourPassenger
                    INNER JOIN t_passenger as p 
                    ON btp.Id_Passenger = p.Id_Passenger
                    WHERE bep.Id_BookingExtra = ?",
                    [
                        $bookingExtra->Id_BookingExtra
                    ]
                );
            } else {
                $passengers = DB::select(
                    "SELECT $selectBookingExtraPassenger
                    FROM t_booking_extra_passenger as bep
                    INNER JOIN t_booking_tour_passenger as btp
                    ON bep.Id_BookingTourPassenger =  btp.Id_BookingTourPassenger
                    INNER JOIN t_passenger as p 
                    ON btp.Id_Passenger = p.Id_Passenger
                    WHERE ( bep.BookingExtraPassenger_Mask = 2 OR bep.BookingExtraPassenger_Status = 2 ) 
                    AND bep.Id_BookingExtra = ?",
                    [
                        $bookingExtra->Id_BookingExtra
                    ]
                );
            }
            $resultItemExtra[$index]->passengers = $passengers;
            $resultItemExtra[$index]->rooms      = $rooms;

            if (count($rooms) == 0 && count($passengers) == 0) {
                $resultItemExtra[$index] = [];
            }
        }


        $resultItemExtra = array_values(array_filter($resultItemExtra, function ($item) {
            return !empty($item);
        }));


        // UPGRADES RESULT        
        $resultItemUpgrade = DB::select(
            "SELECT $selectBookingUpgrade,
                up.Upgrade_Code AS Upgrade_Refer_Code,
                up.Upgrade_Name AS Upgrade_Refer_Name

            FROM t_booking_upgrade AS bu
            INNER JOIN t_upgrade as u
            ON bu.Id_Upgrade = u.Id_Upgrade
            LEFT JOIN t_upgrade AS up
            ON u.Id_Upgrade_Refer = up.Id_Upgrade

            WHERE bu.Id_BookingTour = ?",
            [
                $resultTour[0]->Id_BookingTour
            ]
        );


        foreach ($resultItemUpgrade as $index => $bookingUpgrade) {
            // PASAJEROS
            $passengersUpgrade = DB::select(
                "SELECT $selectBookingUpgradePassenger 
                FROM t_booking_upgrade_passenger as bup
                INNER JOIN t_booking_tour_passenger as btp
                ON bup.Id_BookingTourPassenger =  btp.Id_BookingTourPassenger
                INNER JOIN t_passenger as p 
                ON btp.Id_Passenger = p.Id_Passenger 
                WHERE ( bup.BookingUpgradePassenger_Mask = 2 OR bup.BookingUpgradePassenger_Status = 2 )  
                AND bup.Id_BookingUpgrade = ?",
                [
                    $bookingUpgrade->Id_BookingUpgrade
                ]
            );
            $resultItemUpgrade[$index]->passengers = $passengersUpgrade;

            if (count($passengersUpgrade) == 0) {
                $resultItemUpgrade[$index] = [];
            }
        };


        $resultItemUpgrade = array_values(array_filter($resultItemUpgrade, function ($item) {
            return !empty($item);
        }));



        // RESULT PASSENGERS
        $resultPassengers = DB::select(
            "SELECT $selectPassengers
            FROM t_booking_tour_passenger AS btp
            INNER JOIN t_passenger AS p
            ON btp.Id_Passenger = p.Id_Passenger
            WHERE ( p.Passenger_Status = 2 ) 
            AND btp.Id_BookingTour = ?",
            [
                $resultTour[0]->Id_BookingTour
            ]
        );
        $idsBookingTourPassenger = collect($resultPassengers)->pluck('Id_BookingTourPassenger')->toArray();
        $idsBookingTourPassenger = implode(',', $idsBookingTourPassenger);


        // TOUR LOCK
        $tourLocks = $this->getTourLocks($this->idBookingTour->value(), $this->dateStart->value());
        $tourLocked = false;
        if (count($tourLocks) > 0) {
            $tourLocked = true;
        }

        // CAMP LOCK
        $resultCamp = DB::select(
            "SELECT $selectCampBooking
            FROM t_htl_camp_booking AS hcb
            WHERE   hcb.HtlCampBooking_Status = 2 AND
                    hcb.Id_BookingTour = ?",
            [
                $this->idBookingTour->value()
            ]
        );

        foreach ($resultCamp as $index => $campBooking) {
            $campDate = DateTime::createFromFormat('Y-m-d H:i:s', $campBooking->HtlCampBooking_DateStart);
            $dateDifference = DateUtils::dateDiferenceInDays($campDate->format('Y-m-d'), $resultTour[0]->BookingTour_DateStart);
            $dateStart = DateUtils::changeDate($this->dateStart->value(), $dateDifference);

            $schedule = $this->findCurrentSchedule($campBooking->Id_HtlCampSchedule);
            $campLocks = $this->searchCampLocks($schedule->Id_HtlCamp, $dateStart);
            $campSchedule = $this->searchExpectedSchedule($schedule->Id_HtlCamp, $dateStart);

            $availability       = $campSchedule[0]->HtlCampSchedule_InternalAvailability;
            $newAmountUsage     = $campSchedule[0]->HtlCampSchedule_AmountUsage + $campBooking->HtlCampBooking_Amount;
            $campSchedule[0]->HtlCampSchedule_AmountUsage = $newAmountUsage;

            $campOverBooking = false;
            if ($availability < $newAmountUsage) {
                $campOverBooking = true;
            }
            $campLocked = false;
            if (count($campLocks) > 0) {
                $campLocked = true;
            }

            $resultCamp[$index]->CampLocks        = $campLocks;
            $resultCamp[$index]->CampLocked       = $campLocked;
            $resultCamp[$index]->CampSchedule     = $campSchedule;
            $resultCamp[$index]->CampOverBooking  = $campOverBooking;
        }

        $resultCampFilter = [];
        $resultCampFilter = collect($resultCamp)->reject(function ($camp) {
            return $camp->CampOverBooking === false;
        })->values()->toArray();


        // GROUP PASSENGERS
        $idsBookingTourPassenger = $this->getIdsBookingTourPassenger($this->idBookingTour->value());
        $groups = $this->getGroups($idsBookingTourPassenger);

        return [
            "Id_BookingTour"            => $resultTour[0]->Id_BookingTour,
            "BookingTour_DateStart"     => $resultTour[0]->BookingTour_DateStart,
            "BookingTour_Type"          => $resultTour[0]->BookingTour_Type,
            "BookingTour_NoPax"         => $resultTour[0]->BookingTour_NoPax,
            "BookingTour_Name"          => $resultTour[0]->BookingTour_Name,
            "BookingTour_Group"         => $resultTour[0]->BookingTour_Group,
            "Id_Booking"                => $resultTour[0]->Id_Booking,
            "Id_Package"                => $resultTour[0]->Id_Package,
            "Id_Tour"                   => $resultTour[0]->Id_Tour,
            "Items_Extra"               => $resultItemExtra,
            "Items_Upgrade"             => $resultItemUpgrade,
            "BookingTour_Passengers"    => $resultPassengers,
            "TourLocks"                 => $tourLocks,
            "TourLocked"                => $tourLocked,
            "Camps"                     => $resultCampFilter,
            "GroupList"                 => $groups,
            "ExistingServices"          => $existingServices,
            
        ];
    }

    private function findCurrentSchedule($idHtlCampSchedule)
    {
        $op = new OperatorBuilder();
        $table = HtlCampSchedule::table();
        $prefix = '`hcs`';
        $select = HtlCampSchedule::select($prefix);
        $operation = $op->equal("$prefix.`Id_HtlCampSchedule`", $idHtlCampSchedule);
        return DB::select("SELECT $select FROM `$table` $prefix WHERE {$operation->query()}", $operation->values())[0];
    }

    private function searchCampLocks($idHtlCamp, $dateStart)
    {
        $op = new OperatorBuilder();
        $table = HtlCampLock::table();
        $pf = '`hcl`';
        $select = HtlCampLock::select($pf);

        $pfS = '`hcs`';
        $tableSchedule = HtlCampSchedule::table();
        $operation = $op->and(
            $op->equal("$pf.`HtlCampLock_DateStart`", $dateStart),
            $op->equal("$pfS.`Id_HtlCamp`", $idHtlCamp),
            $op->equal("$pf.`HtlCampLock_Status`", HtlCampLockStatus::ACTIVE),
        );
        return DB::select("SELECT $select
            FROM `$table` $pf
            INNER JOIN `$tableSchedule` $pfS ON $pfS.`Id_HtlCampSchedule` = $pf.`Id_HtlCampSchedule`
            WHERE {$operation->query()}", $operation->values());
    }


    private function searchExpectedSchedule($idHtlCamp, $dateStart)
    {
        $response = $this->queryBus->ask(new FindHtlCampScheduleQuery($idHtlCamp, $dateStart . ' 00:00:00'));
        $op = new OperatorBuilder();
        $table = HtlCamp::table();
        $prefix = '`hc`';
        $select = HtlCamp::select($prefix);
        $operation = $op->equal("$prefix.`Id_HtlCamp`", $idHtlCamp);
        $htlCamp = DB::select("SELECT $select FROM `$table` $prefix WHERE {$operation->query()}", $operation->values())[0];
        return [Utils::join($htlCamp, $response)];
    }

    private function getIdsBookingTourPassenger($idBookingTour)
    {
        $passengers = DB::select(
            "SELECT btp.*, p.*
            FROM t_booking_tour_passenger AS btp
            INNER JOIN t_passenger AS p
            ON btp.Id_Passenger = p.Id_Passenger
            WHERE ( p.Passenger_Status = 2 ) 
            AND btp.Id_BookingTour = ?",
            [$idBookingTour]
        );
        $idBookingTourPassenger = array_map(function ($item) {
            return $item->Id_BookingTourPassenger;
        }, $passengers);
        return $idBookingTourPassenger;
    }
    private function getGroups(array $idsBookingTourPassenger)
    {
        $placeholders = implode(',', array_fill(0, count($idsBookingTourPassenger), '?'));
        $op = new OperatorAnd(
            new OperatorEqual('gp.GroupPassenger_Type', 1),
            new OperatorIn('gp.Id', $idsBookingTourPassenger)
        );
        $sql = "SELECT 
            g.Id_Group,
            MIN(gp.Id) AS Id_BookingTourPassenger,
            g.Group_Name,
            g.Group_DateStart,
            g.Group_DateEnd,
            gpr.Id_GroupProvider,
            p.Id_Provider,
            p.Provider_TradeName,
            p.Provider_Phone
        FROM t_group_passenger AS gp
        INNER JOIN t_group AS g ON gp.Id_Group = g.Id_Group
        LEFT JOIN t_group_provider AS gpr ON gpr.Id_Group = g.Id_Group
        LEFT JOIN t_provider AS p ON gpr.Id_Provider = p.Id_Provider
        WHERE {$op->query()}
        GROUP BY g.Id_Group, g.Group_Name,g.Group_DateStart,g.Group_DateEnd, gpr.Id_GroupProvider, p.Id_Provider, p.Provider_BusinessName, p.Provider_Phone";
        $bindings = $op->values();
        return DB::select($sql, $bindings);
    }

    private function getTourLocks($idBookingTour, string $dateStart)
    {
        $tourLocks = DB::select(
            "SELECT bt.*,tl.* FROM `t_booking_tour` as bt
            INNER JOIN t_tour_lock as tl
            ON bt.Id_Tour = tl.Id_Tour
            WHERE bt.Id_BookingTour =  ? AND ? BETWEEN tl.TourLock_DateStart AND tl.TourLock_DateEnd
            GROUP BY bt.Id_BookingTour",
            [$idBookingTour, $dateStart]
        );
        return $tourLocks;
    }

    private function getPassengersByService($service){
        if (!empty($service->Id_BookingUpgrade)) {
            return $this->getPassengersUpgrade($service->Id_BookingUpgrade);
        } elseif (!empty($service->Id_BookingExtra)) {
            return $this->getPassengersExtra($service->Id_BookingExtra);
        } elseif (!empty($service->Id_BookingEquipment)) {
            return $this->getPassengersEquipment($service->Id_BookingEquipment);
        }
    }

    private function getPassengersUpgrade($idBookingUpgrade){
        $oParam = array($idBookingUpgrade, 0);
        $oData  = DB::select('call sp_booking_upgrade_passenger_list(?, ?)', $oParam);
        return $oData;
    }
    private function getPassengersExtra($idBookingExtra){
        $oParam = [$idBookingExtra, 0];
        $oData  = DB::select('call sp_booking_extra_passenger_list(?,?)', $oParam);
        return $oData;
    }
    private function getPassengersEquipment($idBookingEquipment){
        $oParam = [$idBookingEquipment, 0];
        $oData  = DB::select('call sp_booking_equipment_passenger_list(?,?)', $oParam);
        return $oData;
    }



    protected function analize($data)
    {
        return $data;
    }
}
