<?php

namespace App\Modules\Briefing\Domain;

use App\Modules\Shared\Domain\DateTimeEnd;
use App\Modules\Shared\Domain\DateTimeStart;
use App\Modules\Shared\Domain\Storage\Criteria\StorageCriteria;
use Illuminate\Support\Facades\DB;

class ListBriefing implements StorageCriteria
{

    public function __construct(
        private DateTimeStart $dateTimeStart,
        private DateTimeEnd $dateTimeEnd,
        private BriefingType $type,
        private BriefingOrderBy $orderBy,
        private bool $lockInShare = false
    ) {
    }

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

    protected function query()
    {
        $lockInShare = $this->lockInShare ? 'LOCK IN SHARE MODE' : '';
        if ($this->type->value() === 'all') {
            $typeWhere = '1';
        } else if ($this->type->value() === 'ontime') {
            $typeWhere = "`bt`.`BookingTour_DateStart` = DATE_ADD(CONVERT(`bt`.`BookingTour_DateBriefing`, DATE), INTERVAL 1 DAY)";
        } else {
            $typeWhere = "`bt`.`BookingTour_DateStart` <> DATE_ADD(CONVERT(`bt`.`BookingTour_DateBriefing`, DATE), INTERVAL 1 DAY)";
        }

        if ($this->orderBy->value() === 'dateStart') {
            $orderBy = "`bt`.`BookingTour_DateStart` BETWEEN ? AND ?";
            $sort = 'ORDER BY `all_frm`.`BookingTour_DateStart` ASC';
        } else {
            $orderBy = "`bt`.`BookingTour_DateBriefing` BETWEEN ? AND ?";
            $sort = 'ORDER BY `all_frm`.`BookingTour_DateBriefing` ASC';
        }

        $select1 = DB::select(
            "SELECT *
            FROM (
                SELECT
                    `tmp_frm`.`Id_BookingTour`,
                    `tmp_frm`.`BookingTour_DateStart`,
                    `tmp_frm`.`BookingTour_PickUp`,
                    `tmp_frm`.`BookingTour_Type`,
                    `tmp_frm`.`BookingTour_NoPax`,
                    `tmp_frm`.`BookingTour_Name`,
                    `tmp_frm`.`BookingTour_TourName`,
                    `tmp_frm`.`BookingTour_FeeType`,
                    `tmp_frm`.`BookingTour_Full`,
                    `tmp_frm`.`BookingTour_MinimumFull`,
                    `tmp_frm`.`BookingTour_Group`,
                    `tmp_frm`.`BookingTour_TourDuration`,
                    `tmp_frm`.`BookingTour_DateBriefing`,
                    `tmp_frm`.`BookingTour_PlaceBriefing`,
                    `tmp_frm`.`BookingTour_UseBookingPlaceBriefing`,
                    `tmp_frm`.`BookingTour_Status`,
                    `tmp_frm`.`Id_Booking`,
                    `tmp_frm`.`Booking_Code`,
                    `p`.`Id_Package`,
                    `p`.`Package_Code`,
                    `p`.`Package_Name`,
                    `t`.`Id_Tour`,
                    `t`.`Tour_Code`,
                    `t`.`Tour_Name`,
                    `t`.`Tour_Color`,
                    `g`.`Id_Group`,
                    `g`.`Group_Name`,
                    `g`.`Group_DateStart`,
                    `g`.`Group_Remark`,
                    `tmp_frm`.`Passenger_Amount`
                FROM (
                    SELECT
                        `bt`.`Id_BookingTour`,
                        `bt`.`BookingTour_DateStart`,
                        `bt`.`BookingTour_PickUp`,
                        `bt`.`BookingTour_Type`,
                        `bt`.`BookingTour_NoPax`,
                        `bt`.`BookingTour_Name`,
                        `bt`.`BookingTour_TourName`,
                        `bt`.`BookingTour_FeeType`,
                        `bt`.`BookingTour_Full`,
                        `bt`.`BookingTour_MinimumFull`,
                        `bt`.`BookingTour_Group`,
                        `bt`.`BookingTour_TourDuration`,
                        `bt`.`BookingTour_DateBriefing`,
                        `bt`.`BookingTour_PlaceBriefing`,
                        `bt`.`BookingTour_UseBookingPlaceBriefing`,
                        `bt`.`BookingTour_Status`,
                        `b`.`Id_Booking`,
                        `b`.`Booking_Code`,
                        `bt`.`Id_Package`,
                        `bt`.`Id_Tour`,
                        `gp`.`Id_Group`,
                        COUNT(`gp`.`Id_GroupPassenger`) AS `Passenger_Amount`
                    FROM `t_booking_tour_passenger` `btp`
                    INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `btp`.`Id_BookingTour`
                    INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking`
                    INNER JOIN `t_group_passenger` `gp` ON `gp`.`Id` = `btp`.`Id_BookingTourPassenger`
                        AND `gp`.`GroupPassenger_Type` = 1
                    WHERE $orderBy
                    AND $typeWhere
                    AND `btp`.`BookingTourPassenger_Status` = 2 AND
                    `bt`.`BookingTour_Status` = 2 AND
                    `fn_booking_tour_passenger_check`(
                        `b`.`Booking_Status`,
                        `b`.`Booking_ManuallyConfirmed`,
                        1,
                        1,
                        `btp`.`BookingTourPassenger_PaymentStatus`
                    )
                    GROUP BY `gp`.`Id_Group`, `bt`.`BookingTour_DateBriefing`
                ) AS `tmp_frm`
                INNER JOIN `t_group` `g` ON `g`.`Id_Group` = `tmp_frm`.`Id_Group`
                INNER JOIN `t_package` `p` ON `p`.`Id_Package` = `tmp_frm`.`Id_Package`
                INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `tmp_frm`.`Id_Tour`
                UNION
                SELECT
                    `tmp_frm`.`Id_BookingTour`,
                    `tmp_frm`.`BookingTour_DateStart`,
                    `tmp_frm`.`BookingTour_PickUp`,
                    `tmp_frm`.`BookingTour_Type`,
                    `tmp_frm`.`BookingTour_NoPax`,
                    `tmp_frm`.`BookingTour_Name`,
                    `tmp_frm`.`BookingTour_TourName`,
                    `tmp_frm`.`BookingTour_FeeType`,
                    `tmp_frm`.`BookingTour_Full`,
                    `tmp_frm`.`BookingTour_MinimumFull`,
                    `tmp_frm`.`BookingTour_Group`,
                    `tmp_frm`.`BookingTour_TourDuration`,
                    `tmp_frm`.`BookingTour_DateBriefing`,
                    `tmp_frm`.`BookingTour_PlaceBriefing`,
                    `tmp_frm`.`BookingTour_UseBookingPlaceBriefing`,
                    `tmp_frm`.`BookingTour_Status`,
                    `tmp_frm`.`Id_Booking`,
                    `tmp_frm`.`Booking_Code`,
                    `p`.`Id_Package`,
                    `p`.`Package_Code`,
                    `p`.`Package_Name`,
                    `t`.`Id_Tour`,
                    `t`.`Tour_Code`,
                    `t`.`Tour_Name`,
                    `t`.`Tour_Color`,
                    0 AS `Id_Group`,
                    '' AS `Group_Name`,
                    '' AS `Group_Remark`,
                    '' AS `Group_DateStart`,
                    `tmp_frm`.`Passenger_Amount`
                FROM (
                    SELECT
                        `bt`.`Id_BookingTour`,
                        `bt`.`BookingTour_DateStart`,
                        `bt`.`BookingTour_PickUp`,
                        `bt`.`BookingTour_Type`,
                        `bt`.`BookingTour_NoPax`,
                        `bt`.`BookingTour_Name`,
                        `bt`.`BookingTour_TourName`,
                        `bt`.`BookingTour_FeeType`,
                        `bt`.`BookingTour_Full`,
                        `bt`.`BookingTour_MinimumFull`,
                        `bt`.`BookingTour_Group`,
                        `bt`.`BookingTour_TourDuration`,
                        `bt`.`BookingTour_DateBriefing`,
                        `bt`.`BookingTour_PlaceBriefing`,
                        `bt`.`BookingTour_UseBookingPlaceBriefing`,
                        `bt`.`BookingTour_Status`,
                        `b`.`Id_Booking`,
                        `b`.`Booking_Code`,
                        `bt`.`Id_Package`,
                        `bt`.`Id_Tour`,
                        `gp`.`Id_Group`,
                        COUNT(`btp`.`Id_BookingTourPassenger`) AS `Passenger_Amount`
                    FROM `t_booking_tour_passenger` `btp`
                    INNER JOIN `t_booking_tour` `bt` ON `bt`.`Id_BookingTour` = `btp`.`Id_BookingTour`
                    INNER JOIN `t_booking` `b` ON `b`.`Id_Booking` = `bt`.`Id_Booking`
                    LEFT JOIN `t_group_passenger` `gp` ON `gp`.`Id` = `btp`.`Id_BookingTourPassenger`
                        AND `gp`.`GroupPassenger_Type` = 1
                    WHERE $orderBy
                    AND $typeWhere
                    AND `gp`.`Id_GroupPassenger` IS NULL
                    AND `btp`.`BookingTourPassenger_Status` = 2 AND
                    `bt`.`BookingTour_Status` = 2 AND
                    `fn_booking_tour_passenger_check`(
                        `b`.`Booking_Status`,
                        `b`.`Booking_ManuallyConfirmed`,
                        1,
                        1,
                        `btp`.`BookingTourPassenger_PaymentStatus`
                    )
                    GROUP BY `bt`.`Id_BookingTour`
                ) AS `tmp_frm`
                INNER JOIN `t_package` `p` ON `p`.`Id_Package` = `tmp_frm`.`Id_Package`
                INNER JOIN `t_tour` `t` ON `t`.`Id_Tour` = `tmp_frm`.`Id_Tour`
            ) AS `all_frm`
            $sort",
            [
                $this->dateTimeStart->value(),
                $this->dateTimeEnd->value(),
                $this->dateTimeStart->value(),
                $this->dateTimeEnd->value()
            ]
        );

        return array_merge($select1);
    }

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