<?php

namespace App\Modules\Provider\Domain;

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

class PassengerDistributionNationality implements StorageCriteria
{
    public function __construct(
        private string $dateStart,
        private string $dateEnd,
        private int $idTypeProvider,
        private string $search,
        private int $orderCountryId,
        private string $orderType,
        private string $orderOrientation,
        private int $providersWithData,
        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;
        $orderDirection = strtoupper($this->orderOrientation) === 'DESC' ? 'DESC' : 'ASC';

        $params = [];

        $providerWhere = ['p.Provider_Status > 1'];

        if ($this->idTypeProvider !== 0) {
            $providerWhere[] = 'p.Id_TypeProvider = ?';
            $params[] = $this->idTypeProvider;
        }

        if ($this->search !== '') {
            $like = '%' . $this->search . '%';
            $providerWhere[] = "(
            p.Provider_BusinessName LIKE ?
            OR p.Provider_TradeName  LIKE ?
            OR p.Provider_NickName   LIKE ?
            OR p.Provider_NoDocument LIKE ?
            OR p.Provider_Address    LIKE ?
        )";
            $params = array_merge($params, [$like, $like, $like, $like, $like]);
        }

        $providerWhereSql = implode(' AND ', $providerWhere);

        $providersWithDataSql = $this->providersWithData !== 0 ? 'WHERE Total_Provider > 0' : '';

        $finalOrderExpression = "a.Provider_BusinessName $orderDirection";
        $orderExpression = '';

        if ($this->orderType === 'Total') {
            $orderExpression      = ", Total_Provider $orderDirection";
            $finalOrderExpression = "a.Total_Provider $orderDirection, a.Provider_BusinessName";
        } elseif ($this->orderType !== '') {
            $orderExpression      = ", OrderValue $orderDirection";
            $finalOrderExpression = "a.OrderValue $orderDirection, a.Provider_BusinessName";
        }

        DB::statement("
        CREATE TEMPORARY TABLE tmp_aggregated_nationality AS
        WITH base AS (
            SELECT
                CASE
                    WHEN p.Id_TypeProvider = 1 AND gp.Group_ServiceType = 2 THEN 1.1
                    ELSE tp.Id_TypeProvider
                END AS Id_TypeProvider_Aux,
                CASE
                    WHEN p.Id_TypeProvider = 1 AND gp.Group_ServiceType = 2 THEN 'Guía Primero (Alcances)'
                    ELSE tp.TypeProvider_Name
                END AS TypeProvider_Name_Aux,
                p.Id_Provider,
                p.Provider_BusinessName,
                gp.Id_UserCountry,
                gp.UserCountry_Name,
                gp.UserCountry_Code
            FROM t_provider p
            INNER JOIN t_type_provider tp ON tp.Id_TypeProvider = p.Id_TypeProvider
            LEFT JOIN (
                SELECT
                    tgp.Id_Provider,
                    g.Group_ServiceType,
                    uc.Id_UserCountry,
                    uc.UserCountry_Name,
                    uc.UserCountry_Code
                FROM t_group_provider tgp
                INNER JOIN t_group g
                    ON tgp.Id_Group = g.Id_Group
                    AND g.Group_DateStart BETWEEN ? AND ?
                    AND g.Group_Status > 0
                    AND g.Group_ServiceType IN (1, 2)
                INNER JOIN t_group_passenger gp ON gp.Id_Group = g.Id_Group
                INNER JOIN t_booking_tour_passenger btp ON btp.Id_BookingTourPassenger = gp.Id
                INNER JOIN t_passenger pas ON pas.Id_Passenger = btp.Id_Passenger
                INNER JOIN t_user_country uc ON uc.Id_UserCountry = pas.Id_UserCountry
            ) gp ON p.Id_Provider = gp.Id_Provider
            WHERE $providerWhereSql
        )
        SELECT
            Id_Provider,
            Provider_BusinessName,
            Id_TypeProvider_Aux,
            TypeProvider_Name_Aux,
            Id_UserCountry,
            UserCountry_Name,
            UserCountry_Code,
            COUNT(*) AS Total,
            SUM(COUNT(Id_UserCountry)) OVER (PARTITION BY Id_Provider, Id_TypeProvider_Aux) AS Total_Provider,
            MAX(SUM(CASE WHEN Id_UserCountry = ? THEN 1 ELSE 0 END))
                OVER (PARTITION BY Id_Provider, Id_TypeProvider_Aux) AS OrderValue
        FROM base
        GROUP BY Id_Provider, Id_UserCountry, Id_TypeProvider_Aux
    ", array_merge([$dateStartFormat, $dateEndFormat], $params, [$this->orderCountryId]));

        DB::statement("ALTER TABLE tmp_aggregated_nationality ADD INDEX idx_provider_type (Id_Provider, Id_TypeProvider_Aux)");
        DB::statement("ALTER TABLE tmp_aggregated_nationality ADD INDEX idx_total (Total_Provider)");
        DB::statement("ALTER TABLE tmp_aggregated_nationality ADD INDEX idx_order (OrderValue)");

        $result = DB::select("
        WITH paged_providers AS (
            SELECT DISTINCT
                Id_Provider,
                Id_TypeProvider_Aux,
                Total_Provider,
                OrderValue
            FROM tmp_aggregated_nationality
            $providersWithDataSql
            ORDER BY
                Id_TypeProvider_Aux
                $orderExpression
            LIMIT ? OFFSET ?
        )
        SELECT a.*
        FROM tmp_aggregated_nationality a
        INNER JOIN paged_providers p
            ON  a.Id_Provider = p.Id_Provider
            AND a.Id_TypeProvider_Aux = p.Id_TypeProvider_Aux
        ORDER BY
            a.Id_TypeProvider_Aux,
            $finalOrderExpression
    ", [$this->pageSize, $offset]);

        DB::statement("DROP TEMPORARY TABLE IF EXISTS tmp_aggregated_nationality");

        return $result;
    }

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