<?php

namespace App\Modules\Room\Domain;

use App\Http\Controllers\Owner\Room\RoomListOrderBy;
use App\Http\Controllers\Owner\Room\RoomListOrderType;
use App\Modules\DefinedService\Location\Domain\IdLocation;
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\OperatorEqual;
use App\Modules\Shared\Domain\QueryBuilder\Operator\OperatorAllIn;
use App\Modules\Shared\Domain\QueryBuilder\Operator\OperatorIn;
use App\Modules\Shared\Domain\QueryBuilder\Operator\OperatorLike;
use App\Modules\Shared\Domain\QueryBuilder\Operator\OperatorOr;
use App\Modules\Shared\Domain\Storage\Criteria\StorageCriteria;
use Illuminate\Support\Facades\DB;

class CountListAllNoRoom 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 $idTours,
        private array $UpgradeStatuses,
        private bool $allExtras,
        private bool $allTours,
        private bool $allUpgradeStatuses,
        private IdLocation $idLocation,
        private ?string $search,
    ) {}

    public function run()
    {
        $dates = [
            RoomListOrderBy::ROOM => '`bep`.`BookingExtraPassenger_DateEntryStart`',
            RoomListOrderBy::TOUR => '`bt`.`BookingTour_DateStart`',
            RoomListOrderBy::ROOM_RESERVATION_DATE_FINAL => '`bep`.`BookingExtraPassenger_DateEntryStart`',
        ];

        if ($this->idLocation->value() == 0) {
            $locationOperator = new OperatorEqual('1', '1');
        } else {
            $locationOperator = new OperatorEqual('`e`.`Id_Location`', $this->idLocation->value());
        }

        $searchOperator = $this->search
            ? new OperatorOr(
                new OperatorLike('`p`.`Passenger_Name`', '%' . $this->search . '%'),
                new OperatorLike('`p`.`Passenger_LastName`', '%' . $this->search . '%'),
                new OperatorLike('`p`.`Passenger_NoDocument`', '%' . $this->search . '%'),
                new OperatorLike('`b`.`Booking_Code`', '%' . $this->search . '%'),
                new OperatorLike('`t`.`Tour_Code`', '%' . $this->search . '%'),
                new OperatorLike('`pk`.`Package_Code`', '%' . $this->search . '%'),
                new OperatorLike('`b`.`Booking_Observation`', '%' . $this->search . '%'),
            )
            : new OperatorEqual('1', '1');

        $operator = new OperatorAnd(
            new OperatorBetween($dates[$this->orderBy->value()], $this->dateStart->value() . ' 00:00:00', $this->dateEnd->value() . ' 23:59:59'),
            new OperatorIn('`bep`.`BookingExtraPassenger_Status`', [1, 2]),
            new OperatorAllIn('`e`.`Id_Extra`', $this->allExtras, $this->idExtras),
            new OperatorAllIn('`bt`.`Id_Tour`', $this->allTours, $this->idTours),
            new OperatorAllIn('`be`.`BookingExtra_Mode`', $this->allUpgradeStatuses, $this->UpgradeStatuses),
            new OperatorEqual('`e`.`Id_TypeExtra`', 4),
            $locationOperator,
            $searchOperator,
        );

        $result = DB::select(
            "SELECT COUNT(*) as `total`
            FROM `t_booking_extra_passenger` `bep`
            INNER JOIN `t_booking_extra` `be` ON `be`.`Id_BookingExtra` = `bep`.`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`
            INNER JOIN `t_booking_tour_passenger` `btp` ON `btp`.`Id_BookingTourPassenger` = `bep`.`Id_BookingTourPassenger`
            INNER JOIN `t_passenger` `p` ON `p`.`Id_Passenger` = `btp`.`Id_Passenger`
            WHERE NOT EXISTS (
                SELECT 1 FROM `t_room_passenger` `rp`
                WHERE `rp`.`Id_BookingExtraPassenger` = `bep`.`Id_BookingExtraPassenger`
                AND `rp`.`RoomPassenger_Status` <> 0
            )
            AND {$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
            ",
            $operator->values()
        );

        return $result;
    }
}
