<?php

namespace App\Modules\Provider\Domain;

use App\Modules\Shared\Domain\Storage\Criteria\StorageCriteria;
use Illuminate\Support\Facades\DB;
class PassengerDistributionListPassengerCount implements StorageCriteria
{
    public function __construct(
        private string $search,
        private string $dateStart,
        private string $dateEnd,
        private int $idProvider,
    ) {}

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

    protected function query()
    {
        $dateStartFormat = $this->dateStart . ' 00:00:00';
        $dateEndFormat   = $this->dateEnd . ' 23:59:59';

        $sql = "
                SELECT count(*) AS total
            FROM t_group_provider
                     INNER JOIN `t_group` AS `g`
                                ON t_group_provider.Id_Group = g.Id_Group
                                    AND Group_DateStart BETWEEN ? AND ?
                                    AND `g`.`Group_Status` > 0
                                    AND `g`.`Group_ServiceType` IN (1, 2)
                     INNER JOIN `t_group_passenger` AS gp ON gp.Id_Group = g.Id_Group
                     INNER JOIN `t_booking_tour_passenger` AS `btp`
                                ON `btp`.`Id_BookingTourPassenger` = `gp`.`Id`
                     INNER JOIN `t_booking_tour` AS `bt` ON btp.Id_BookingTour = bt.Id_BookingTour
                     INNER JOIN `t_booking` AS `b` ON bt.Id_Booking = b.Id_Booking
                     INNER JOIN `t_tour` AS `t` ON t.Id_Tour = bt.Id_Tour
                     INNER JOIN `t_passenger` AS `p` ON `p`.`Id_Passenger` = `btp`.`Id_Passenger`
                     INNER JOIN `t_user_country` AS `uc` ON `uc`.`Id_UserCountry` = `p`.`Id_UserCountry`
            WHERE Id_Provider = ?
              AND (? = ''
                OR uc.UserCountry_Name LIKE CONCAT('%', ?, '%')
                OR uc.UserCountry_Code LIKE CONCAT('%', ?, '%')
                OR p.Passenger_Name LIKE CONCAT('%', ?, '%')
                OR p.Passenger_LastName LIKE CONCAT('%', ?, '%')
                OR p.Passenger_Email LIKE CONCAT('%', ?, '%')
                OR t.Tour_Name LIKE CONCAT('%', ?, '%')
                OR g.Group_Name LIKE CONCAT('%', ?, '%'))
            ORDER BY `g`.`Group_DateStart` DESC
                ";

        return DB::select($sql, [
            $dateStartFormat,
            $dateEndFormat,
            $this->idProvider,
            $this->search,
            $this->search,
            $this->search,
            $this->search,
            $this->search,
            $this->search,
            $this->search,
            $this->search,
        ]);
    }

    protected function analize($data)
    {
        if (!count($data)) {
            return 0;
        }
        return (int) $data[0]->total;
    }
}
