<?php

namespace App\Modules\Provider\Domain;

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

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

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

        $offset = ($this->page - 1) * $this->pageSize;

        $sql = "
                SELECT
                    DATE(g.Group_DateStart)  AS date,
                       YEAR(g.Group_DateStart)  AS year,
                       MONTH(g.Group_DateStart) AS month,
                       DAY(g.Group_DateStart)   AS day,
                       uc.Id_UserCountry,
                       UserCountry_Name,
                       UserCountry_Code,
                       COUNT(*) AS count
                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_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 = ?
                GROUP BY DATE(g.Group_DateStart), Id_UserCountry
                ORDER BY DATE(g.Group_DateStart), uc.Id_UserCountry
                LIMIT ? OFFSET ?;
                ";

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

    protected function analize($data)
    {
        if (!count($data)) {
            return [];
        }
        return $data;
    }
}
