<?php

namespace App\Modules\Room\Domain;

use App\Http\Controllers\Owner\Room\RoomListOrderBy;
use App\Http\Controllers\Owner\Room\RoomListOrderType;
use App\Modules\Booking\Domain\Booking;
use App\Modules\BookingTour\Domain\BookingTour;
use App\Modules\Shared\Domain\DateEnd;
use App\Modules\Shared\Domain\DateStart;
use App\Modules\Shared\Domain\QueryBuilder\Operator\OperatorAnd;
use App\Modules\Shared\Domain\QueryBuilder\Operator\OperatorBetween;
use App\Modules\Shared\Domain\QueryBuilder\Operator\OperatorIn;
use App\Modules\Shared\Domain\Storage\Criteria\StorageCriteria;
use App\Modules\TypeRoom\Domain\TypeRoom;
use Illuminate\Support\Facades\DB;

class ListAllRoom implements StorageCriteria
{
    public function __construct(
        private DateStart $dateStart,
        private DateEnd $dateEnd,
        private RoomListOrderBy $orderBy,
        private RoomListOrderType $orderType,
        private array $statuses,
        private array $idExtras,
        private array $idProviders,
        private array $locks,
        private array $idCategories,
        private array $idTours,
        private array $idTypeRooms,
        private array $UpgradeStatuses,
    ) {}

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

    protected function query()
    {
        $data = [];
        $dates = [
            RoomListOrderBy::ROOM => '`r`.`Room_RequestDateStart`',
            RoomListOrderBy::TOUR => '`bt`.`BookingTour_DateStart`',
        ];

        $orderBy = [
            RoomListOrderBy::ROOM => '`r`.`Room_RequestDateStart`',
            RoomListOrderBy::TOUR => '`bt`.`BookingTour_DateStart`',
        ];

        $operator = new OperatorAnd(
            new OperatorBetween($dates[$this->orderBy->value()], $this->dateStart->value() . ' 00:00:00', $this->dateEnd->value() . ' 23:59:59'),
            new OperatorIn('`r`.`Room_Status`', $this->statuses),
            new OperatorIn('`e`.`Id_Extra`', $this->idExtras),
            new OperatorIn('`r`.`Id_Provider`', [0, ...$this->idProviders]),
            new OperatorIn('`r`.`Room_Lock`', $this->locks),
            new OperatorIn('`bt`.`Id_Tour`', $this->idTours),
            new OperatorIn('`r`.`Id_TypeRoom`', $this->idTypeRooms),
            new OperatorIn('`be`.`BookingExtra_Included`', $this->UpgradeStatuses),
        );

        $select = Room::select('`r`');
        $table = Room::table();
        $typeRoomSelect = TypeRoom::select('`tr`');
        $bookingTourSelect = BookingTour::select('`bt`');
        $bookingSelect = Booking::select('`b`');

        return DB::select(
            "SELECT $select,
                $typeRoomSelect,
                `be`.`Id_BookingExtra`,
                `be`.`BookingExtra_Quantity`,
                `be`.`BookingExtra_Price`,
                `be`.`BookingExtra_Total`,
                `be`.`BookingExtra_Feature`,
                `be`.`BookingExtra_Included`,
    
                `e`.`Id_Extra`,
                `e`.`Extra_Name`,

                $bookingTourSelect,
                
                `t`.`Id_Tour`,
                `t`.`Tour_Name`,
                `t`.`Tour_Code`,
                `t`.`Tour_Color`,

                `pk`.`Id_Package`,
                `pk`.`Package_Name`,
                `pk`.`Package_Code`,

                `r`.`Id_Provider`,
                `pr`.`Provider_NickName`,
                `pr`.`Provider_Phone`,
                `pr`.`Provider_ImgProfile_Thumb`,

                $bookingSelect,

                CONCAT('[', GROUP_CONCAT(JSON_OBJECT(
                    'Id_RoomPassenger', `rp`.`Id_RoomPassenger`,
                    'RoomPassenger_IsNotValid', `rp`.`RoomPassenger_IsNotValid`,
                    'RoomPassenger_Status', `rp`.`RoomPassenger_Status`,

                    'Id_BookingExtraPassenger', `bep`.`Id_BookingExtraPassenger`,
                    'BookingExtraPassenger_DateEntryStart', `bep`.`BookingExtraPassenger_DateEntryStart`,
                    'BookingExtraPassenger_DateEntryEnd', `bep`.`BookingExtraPassenger_DateEntryEnd`,
                    'BookingExtraPassenger_DatePurchase', `bep`.`BookingExtraPassenger_DatePurchase`,
                    'BookingExtraPassenger_Remark', `bep`.`BookingExtraPassenger_Remark`,
                    'BookingExtraPassenger_Status', `bep`.`BookingExtraPassenger_Status`,

                    'Id_BookingTourPassenger', `btp`.`Id_BookingTourPassenger`,
                    'BookingTourPassenger_Remark', `btp`.`BookingTourPassenger_Remark`,
                    'BookingTourPassenger_Price', `btp`.`BookingTourPassenger_Price`,
                    'BookingTourPassenger_Full', `btp`.`BookingTourPassenger_Full`,
                    'BookingTourPassenger_ValidGroups', `btp`.`BookingTourPassenger_ValidGroups`,
                    'BookingTourPassenger_NoValidGroups', `btp`.`BookingTourPassenger_NoValidGroups`,
                    'BookingTourPassenger_Status', `btp`.`BookingTourPassenger_Status`,
                    'BookingTourPassenger_PaymentStatus', `btp`.`BookingTourPassenger_PaymentStatus`,

                    'Id_Passenger', `p`.`Id_Passenger`,
                    'Passenger_Name', `p`.`Passenger_Name`,
                    'Passenger_LastName', `p`.`Passenger_LastName`,
                    'Passenger_Gender', `p`.`Passenger_Gender`,
                    'Passenger_DOB', `p`.`Passenger_DOB`,
                    'Passenger_NoDocument', `p`.`Passenger_NoDocument`,
                    'Passenger_Status', `p`.`Passenger_Status`,
                    'Passenger_PaymentStatus', `p`.`Passenger_PaymentStatus`,
                    
                    'Id_TypeDocument', `td`.`Id_TypeDocument`,
                    'TypeDocument_Name', `td`.`TypeDocument_Name`,
                    'TypeDocument_Abrv', `td`.`TypeDocument_Abrv`
                    ) SEPARATOR ','), ']'
                ) AS `PassengerList`,
                (COUNT(`rp`.`Id_RoomPassenger`)) AS `PassengerCount`
            FROM `$table` `r`
            INNER JOIN `t_type_room` `tr` ON `tr`.`Id_TypeRoom` = `r`.`Id_TypeRoom`
            INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `r`.`Id_BookingExtra`
            INNER JOIN `t_extra` `e` ON `e`.`Id_Extra` = `be`.`Id_Extra`
            INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `be`.`Id_BookingTour`
            INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking`
            INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `bt`.`Id_Tour`
            INNER JOIN `t_package` `pk` ON `pk`.`Id_Package` = `bt`.`Id_Package`
            LEFT JOIN `t_provider` `pr` ON `pr`.`Id_Provider` = `r`.`Id_Provider`

            LEFT JOIN `t_room_passenger` `rp` ON `rp`.`Id_Room` = `r`.`Id_Room` AND `rp`.`RoomPassenger_Status` <> 0
            LEFT JOIN `t_booking_extra_passenger` `bep` ON `bep`.`Id_BookingExtraPassenger` = `rp`.`Id_BookingExtraPassenger`
            LEFT JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger`
            LEFT JOIN `t_passenger` `p` ON `p`.`Id_Passenger` = `btp`.`Id_Passenger`
            LEFT JOIN `t_type_document` `td` ON `td`.`Id_TypeDocument` = `p`.`Id_TypeDocument`
            WHERE {$operator->query()}
            AND
            CASE
                WHEN `bep`.`BookingExtraPassenger_Status` = 2 THEN 1
                WHEN `bep`.`BookingExtraPassenger_Status` = 1 THEN
                `btp`.`BookingTourPassenger_Status` IN (2, 3)
                AND `fn_booking_tour_passenger_check`(
                    `b`.`Booking_Status`, `b`.`Booking_ManuallyConfirmed`, 1, 1, `btp`.`BookingTourPassenger_PaymentStatus`
                )
            END
            AND `e`.`Id_TypeExtra` = 4
            GROUP BY `r`.`Id_Room`
            ORDER BY " . $orderBy[$this->orderBy->value()] . " " . $this->orderType->value() . "
            ",
            $operator->values()
        );
    }

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