<?php

namespace App\Http\Controllers\Owner\Passenger;

use App\Modules\Shared\Domain\Bus\Query\QueryBus;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class PassengerExcelReviewReportController
{
    public function __construct(
        private QueryBus $queryBus
    ) {
    }

    public function __invoke(Request $request)
    {
        dd('here');
        $object = DB::select(
            "SELECT `pq`.`Package_Code` AS `Paquete`,
                    `t`.`Tour_Code` AS `Tour`,
                    CONCAT(`p`.`Passenger_Name`, ' / ', `p`.`Passenger_LastName`) AS `Pasajero`,
                    DAYOFMONTH(`pr`.`PassengerReview_DateUpdate`) AS `Dia_llenado`,
                    MONTH(`pr`.`PassengerReview_DateUpdate`) AS `Mes_llenado`,
                    YEAR(`pr`.`PassengerReview_DateUpdate`) AS `Año_llenado`,
                    DAYOFMONTH(`g`.`Group_DateStart`) AS `Dia_ida`,
                    MONTH(`g`.`Group_DateStart`) AS `Mes_ida`,
                    YEAR(`g`.`Group_DateStart`) AS `Año_ida`,
                    DAYOFMONTH(`g`.`Group_DateEnd`) AS `Dia_retorno`,
                    MONTH(`g`.`Group_DateEnd`) AS `Mes_retorno`,
                    YEAR(`g`.`Group_DateEnd`) AS `Año_retorno`,
                    `bt`.`BookingTour_TourDuration` AS `N° de días`,
                    ( CASE `p`.`Passenger_Gender` WHEN 1 THEN 'M' ELSE 'F' END ) AS `Sexo`,
                    YEAR(CURDATE())-YEAR(`p`.`Passenger_DOB`) + IF(DATE_FORMAT(CURDATE(),'%m-%d') > DATE_FORMAT(`p`.`Passenger_DOB`,'%m-%d'), 0 , -1 ) AS `Edad`,
                    `uc`.`UserCountry_Name` AS `Nacionalidad`,
                    (
                        CONCAT(
                        '[',
                        GROUP_CONCAT(JSON_OBJECT(
                            'Id_PassengerReview',       `pr`.`Id_PassengerReview`,
                            'PassengerReview_Status',   `pr`.`PassengerReview_Status`,
                            'PassengerReview_Values',   `pr`.`PassengerReview_Values`,
                            'PassengerReview_Comment',  `pr`.`PassengerReview_Comment`,
                            'PassengerReview_Score',    `pr`.`PassengerReview_Score`,
                            'PassengerReview_DateUpdate',`pr`.`PassengerReview_DateUpdate`,

                            'Id_Provider',              `pv`.`Id_Provider`,
                            'Provider_BusinessName',    `pv`.`Provider_BusinessName`,
                            'Id_TypeProvider',          `tp`.`Id_TypeProvider`,
                            'TypeProvider_Name',        `tp`.`TypeProvider_Name`,
                            'Id_Question',              `q`.`Id_Question`,
                            'Question_Type',            `q`.`Question_Type`,
                            'Question_General',         `q`.`Question_General`
                            ) SEPARATOR ','),
                        ']'
                        )
                    ) AS `PassengerReviewAll_JSON`
            FROM    `t_group_passenger`         AS `gp` INNER JOIN
                    `t_group`                   AS `g`      ON `g`.`Id_Group` = `gp`.`Id_Group` INNER JOIN
                    `t_booking_tour_passenger`  AS `btp`    ON `btp`.`Id_BookingTourPassenger` = `gp`.`Id` AND `gp`.`GroupPassenger_Type` = 1 INNER JOIN
                    `t_booking_tour`            AS `bt`     ON `bt`.`Id_BookingTour` = `btp`.`Id_BookingTour` INNER JOIN
                    `t_booking`                 AS `b`      ON `b`.`Id_Booking` = `bt`.`Id_Booking` INNER JOIN
                    `t_tour`                    AS `t`      ON `t`.`Id_Tour` = `bt`.`Id_Tour` INNER JOIN
                    `t_package`                 AS `pq`     ON `pq`.`Id_Package` = `bt`.`Id_Package` 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` LEFT JOIN
                    `t_passenger_review`        AS `pr`     ON `pr`.`Id_BookingTourPassenger` = `gp`.`Id` AND `pr`.`Id_Group` = `gp`.`Id_Group` LEFT JOIN
                    `t_question`                AS `q`      ON `q`.`Id_Question` = `pr`.`Id_Question` LEFT JOIN
                    `t_provider`                AS `pv`     ON `pr`.`PassengerReview_Provider` = CONCAT('|', `pv`.`Id_Provider`, '|') LEFT JOIN
                    `t_type_provider`           AS `tp`     ON `tp`.`Id_TypeProvider` = `pv`.`Id_TypeProvider`
            WHERE
                `pr`.`PassengerReview_DateUpdate` BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59' AND
                `pr`.`PassengerReview_Status` = 2
            GROUP BY `gp`.`Id_GroupPassenger`
            ORDER BY `pr`.`PassengerReview_DateUpdate` DESC",
            []
        );
        $objects = $this->generateObjects($object);
        // return $this->genCSV($object);
        // dd($objects);
    }

    public function genCSV($objects)
    {
        $value = '';
        $keys = [];
        if (count($objects)) {
            $keys = array_keys(get_object_vars($objects[0]));
            $value .= implode(';', array_map(fn($key) => '"' . $key . '"', $keys)) . "\n";
        }
        $value .= implode("\n", array_map(fn($obj) => $this->genCSVElements($obj, $keys), $objects));
        return $value;
    }

    public function genCSVElements(&$obj, &$keys)
    {
        return implode(';', array_map(fn($key) => '"' . $obj->$key . '"', $keys));
    }

    public function generateObjects($listGroupPassenger)
    {
        return array_map(fn($groupPassenger) => $this->generateObject($groupPassenger), $listGroupPassenger);
    }

    public function generateObject($groupPassenger)
    {
        $PassengerReviewAll = json_decode($groupPassenger->PassengerReviewAll_JSON);
        unset($groupPassenger->PassengerReviewAll_JSON);
        $Global = $this->findGeneralScoreClean($PassengerReviewAll);
        $Guide = $this->findTypeProvider($PassengerReviewAll, 1);
        $SecondGuide = $this->findTypeProvider($PassengerReviewAll, 2);
        $Chef = $this->findTypeProvider($PassengerReviewAll, 3);
        $RestaurantMollepata = $this->findProviderName($PassengerReviewAll, 6, 'Mollepata');
        $RestaurantAguas = $this->findProviderName($PassengerReviewAll, 6, 'Aguas Calientes');
        $Transport = $this->findTypeProvider($PassengerReviewAll, 5);
        $Horseman = $this->findTypeProvider($PassengerReviewAll, 4);
        $CampsSkyCamp = $this->findProviderName($PassengerReviewAll, 15, 'Sky Camp');
        $CampsSkyView = $this->findProviderName($PassengerReviewAll, 15, 'Mountain Sky View');
        $CampsJungle = $this->findProviderName($PassengerReviewAll, 15, 'Jungle Domes');
        $CampsAndean = $this->findProviderName($PassengerReviewAll, 15, 'Andean Huts');
        $Hotel = $this->findTypeProvider($PassengerReviewAll, 8);
        $Recomend = $this->findQuestion($PassengerReviewAll, 16);
        $RecomendWhy = $this->findQuestion($PassengerReviewAll, 17);
        $Contact = $this->findQuestion($PassengerReviewAll, 18);
        $Moments = $this->findQuestion($PassengerReviewAll, 11);
        $Comments = $this->findQuestion($PassengerReviewAll, 13);
        
        $groupPassenger->Global_Score = $this->getAllFrom($Global, fn($g) => $g->PassengerReview_Score);
        
        $groupPassenger->Guide_Name = $this->getAllFrom($Guide, fn($g) => $g->Provider_BusinessName);
        $groupPassenger->Guide_Score = $this->getAllFrom($Guide, fn($g) => $g->PassengerReview_Score);
        $groupPassenger->Guide_Comment = $this->getAllFrom($Guide, fn($g) => $g->PassengerReview_Comment);
        
        $groupPassenger->SecondGuide_Name = $this->getAllFrom($SecondGuide, fn($g) => $g->Provider_BusinessName);
        $groupPassenger->SecondGuide_Score = $this->getAllFrom($SecondGuide, fn($g) => $g->PassengerReview_Score);
        $groupPassenger->SecondGuide_Comment = $this->getAllFrom($SecondGuide, fn($g) => $g->PassengerReview_Comment);
        
        $groupPassenger->Chef_Name = $this->getAllFrom($Chef, fn($g) => $g->Provider_BusinessName);
        $groupPassenger->Chef_Score = $this->getAllFrom($Chef, fn($g) => $g->PassengerReview_Score);
        $groupPassenger->Chef_Comment = $this->getAllFrom($Chef, fn($g) => $g->PassengerReview_Comment);
        
        $groupPassenger->RestaurantMollepata_Name = $this->getAllFrom($RestaurantMollepata, fn($g) => $g->Provider_BusinessName);
        $groupPassenger->RestaurantMollepata_Score = $this->getAllFrom($RestaurantMollepata, fn($g) => $g->PassengerReview_Score);
        $groupPassenger->RestaurantMollepata_Comment = $this->getAllFrom($RestaurantMollepata, fn($g) => $g->PassengerReview_Comment);
        
        $groupPassenger->RestaurantAguas_Name = $this->getAllFrom($RestaurantAguas, fn($g) => $g->Provider_BusinessName);
        $groupPassenger->RestaurantAguas_Score = $this->getAllFrom($RestaurantAguas, fn($g) => $g->PassengerReview_Score);
        $groupPassenger->RestaurantAguas_Comment = $this->getAllFrom($RestaurantAguas, fn($g) => $g->PassengerReview_Comment);
        
        $groupPassenger->Transport_Name = $this->getAllFrom($Transport, fn($g) => $g->Provider_BusinessName);
        $groupPassenger->Transport_Score = $this->getAllFrom($Transport, fn($g) => $g->PassengerReview_Score);
        $groupPassenger->Transport_Comment = $this->getAllFrom($Transport, fn($g) => $g->PassengerReview_Comment);
        
        $groupPassenger->Horseman_Name = $this->getAllFrom($Horseman, fn($g) => $g->Provider_BusinessName);
        $groupPassenger->Horseman_Score = $this->getAllFrom($Horseman, fn($g) => $g->PassengerReview_Score);
        $groupPassenger->Horseman_Comment = $this->getAllFrom($Horseman, fn($g) => $g->PassengerReview_Comment);

        $groupPassenger->CampsSkyCamp_Name = $this->getAllFrom($CampsSkyCamp, fn($g) => $g->Provider_BusinessName);
        $groupPassenger->CampsSkyCamp_Score = $this->getAllFrom($CampsSkyCamp, fn($g) => $g->PassengerReview_Score);
        $groupPassenger->CampsSkyCamp_Comment = $this->getAllFrom($CampsSkyCamp, fn($g) => $g->PassengerReview_Comment);
        
        $groupPassenger->CampsSkyView_Name = $this->getAllFrom($CampsSkyView, fn($g) => $g->Provider_BusinessName);
        $groupPassenger->CampsSkyView_Score = $this->getAllFrom($CampsSkyView, fn($g) => $g->PassengerReview_Score);
        $groupPassenger->CampsSkyView_Comment = $this->getAllFrom($CampsSkyView, fn($g) => $g->PassengerReview_Comment);
        
        $groupPassenger->CampsJungle_Name = $this->getAllFrom($CampsJungle, fn($g) => $g->Provider_BusinessName);
        $groupPassenger->CampsJungle_Score = $this->getAllFrom($CampsJungle, fn($g) => $g->PassengerReview_Score);
        $groupPassenger->CampsJungle_Comment = $this->getAllFrom($CampsJungle, fn($g) => $g->PassengerReview_Comment);
        
        $groupPassenger->CampsAndean_Name = $this->getAllFrom($CampsAndean, fn($g) => $g->Provider_BusinessName);
        $groupPassenger->CampsAndean_Score = $this->getAllFrom($CampsAndean, fn($g) => $g->PassengerReview_Score);
        $groupPassenger->CampsAndean_Comment = $this->getAllFrom($CampsAndean, fn($g) => $g->PassengerReview_Comment);
        
        $groupPassenger->Hotel_Name = $this->getAllFrom($Hotel, fn($g) => $g->Provider_BusinessName);
        $groupPassenger->Hotel_Score = $this->getAllFrom($Hotel, fn($g) => $g->PassengerReview_Score);
        $groupPassenger->Hotel_Comment = $this->getAllFrom($Hotel, fn($g) => $g->PassengerReview_Comment);
        
        $groupPassenger->Recomend_Comment = $this->getAllFrom($Recomend, fn($g) => $this->getRecomend($g->PassengerReview_Values));
        $groupPassenger->RecomendWhy_Comment = $this->getAllFrom($RecomendWhy, fn($g) => $g->PassengerReview_Comment);

        $groupPassenger->Trip_Advisor = $this->getAllFrom($Contact, fn($g) => $this->getContact($g->PassengerReview_Values, 0));
        $groupPassenger->Google = $this->getAllFrom($Contact, fn($g) => $this->getContact($g->PassengerReview_Values, 1));
        $groupPassenger->Social_Network = $this->getAllFrom($Contact, fn($g) => $this->getContact($g->PassengerReview_Values, 2));
        $groupPassenger->Referencias = $this->getAllFrom($Contact, fn($g) => $this->getContact($g->PassengerReview_Values, 3));
        $groupPassenger->Otros = $this->getAllFrom($Contact, fn($g) => $this->getContact($g->PassengerReview_Values, 4));

        $groupPassenger->Moments_Comment = $this->getAllFrom($Moments, fn($g) => $g->PassengerReview_Comment);

        $groupPassenger->Comments_Suggestions = $this->getAllFrom($Comments, fn($g) => $g->PassengerReview_Comment);
        
        return $groupPassenger;
    }

    public function getContact($values, $index)
    {
        $contact = json_decode($values);
        if (!isset($contact[$index]->selected)) {
            return '';
        }
        if ($contact[$index]->selected) {
            return 'Sí';
        }
        return '';
    }

    public function getRecomend($values)
    {
        $data = json_decode($values);
        if ($data[0]->selected) {
            return 'Sí';
        }
        if ($data[1]->selected) {
            return 'No';
        }
        return '';
    }

    public function getAllFrom($reviewArray, $callable)
    {
        return implode(',', array_map($callable, $reviewArray));
    }

    public function findGeneralScoreClean($reviewAll)
    {
        $find = [];
        foreach ($reviewAll as $value) {
            if ($value->Id_TypeProvider == null && $value->Question_General == 2 && in_array($value->Question_Type, [1, 3, 8])) {
                $find[] = $value;
            }
        }
        return $find;
    }

    public function findQuestion($reviewAll, $idQuestion)
    {
        $find = [];
        foreach ($reviewAll as $value) {
            if ($value->Id_Question == $idQuestion) {
                $find[] = $value;
            }
        }
        return $find;
    }

    public function findTypeProvider($reviewAll, $idTypeProvider)
    {
        $find = [];
        foreach ($reviewAll as $value) {
            if ($value->Id_TypeProvider == $idTypeProvider) {
                $find[] = $value;
            }
        }
        return $find;
    }

    public function findProviderName($reviewAll, $idTypeProvider, $ProviderName)
    {
        $find = [];
        foreach ($reviewAll as $value) {
            if ($value->Id_TypeProvider == $idTypeProvider && str_contains($value->Provider_BusinessName, $ProviderName)) {
                $find[] = $value;
            }
        }
        return $find;
    }

}
