<?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 DataQueryReviewReportController
{
    public function __construct(
        private QueryBus $queryBus
    ) {
    }

    public function __invoke(Request $request)
    {
        $dateStart = '2022-06-01 00:00:00';
        $dateEnd = '2023-08-29 23:59:59';
        $object = DB::select(
            "SELECT `pk`.`Package_Code` AS `Paquete`,
                    `t`.`Tour_Code` AS `Tour`,
                    `p`.`Id_Passenger` AS `IdPass`,
                    CONCAT(`p`.`Passenger_Name`, ' / ', `p`.`Passenger_LastName`) AS `Pasajero`,
                    ( CASE WHEN `pr`.`Id_PassengerReview` IS NOT NULL THEN 'SI' ELSE 'NO' END ) AS `Tiene_Review`,
                    DAYOFMONTH(`pr`.`PassengerReview_DateUpdate`) AS `Dia_llenado`,
                    MONTH(`pr`.`PassengerReview_DateUpdate`) AS `Mes_llenado`,
                    YEAR(`pr`.`PassengerReview_DateUpdate`) AS `Año_llenado`,
                    (
                        CONCAT(
                        '[',
                        GROUP_CONCAT(DISTINCT JSON_OBJECT(
                            'Id_Provider',              `gpv_p`.`Id_Provider`,
                            'Provider_BusinessName',    `gpv_p`.`Provider_BusinessName`,
                            'Id_TypeProvider',          `gpv_tp`.`Id_TypeProvider`,
                            'TypeProvider_Name',        `gpv_tp`.`TypeProvider_Name`
                            ) SEPARATOR ','),
                        ']'
                        )
                    ) AS `GroupProvider_JSON`,
                    (
                        CONCAT(
                        '[',
                        GROUP_CONCAT(DISTINCT 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_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` AND `g`.`Group_ServiceType` IN (1,2) 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 `pk`     ON `pk`.`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_group_provider`          AS `gpv`    ON `gpv`.`Id_Group` = `g`.`Id_Group` LEFT JOIN
                    `t_provider`                AS `gpv_p`  ON `gpv_p`.`Id_Provider` = `gpv`.`Id_Provider`LEFT JOIN
                    `t_type_provider`           AS `gpv_tp` ON `gpv_tp`.`Id_TypeProvider` = `gpv_p`.`Id_TypeProvider` LEFT JOIN
                    `t_passenger_review`        AS `pr`     ON `pr`.`Id_GroupPassenger` = `gp`.`Id_GroupPassenger` AND `gp`.`GroupPassenger_ReviewStatus` = 2 LEFT JOIN
                    `t_question`                AS `q`      ON `q`.`Id_Question` = `pr`.`Id_Question`
            WHERE   `g`.`Group_DateStart` BETWEEN ? AND ?
            AND `pr`.`PassengerReview_Status` = 2
            GROUP BY `gp`.`Id_GroupPassenger`
            ORDER BY `g`.`Group_DateStart` DESC, `pk`.`Package_Code` ASC, `t`.`Tour_Code` ASC, `gp`.`Id_GroupPassenger` ASC",
            [
                $dateStart,
                $dateEnd
            ]
        );
        $objects = $this->generateObjects($object);
        return $this->genTable($objects);
    }

    public function genTable($objects)
    {
        $value = "<table>\n";
        $keys = [];
        if (count($objects)) {
            $keys = array_keys(get_object_vars($objects[0]));
            $value .= '<tr>' . implode('', array_map(fn($key) => '<th>' . $key . '</th>', $keys)) . "</tr>\n";
        }
        $value .= implode("\n", array_map(fn($obj) => $this->genRows($obj, $keys), $objects)) . '</table>';
        return $value;
    }

    public function genRows(&$obj, &$keys)
    {
        return '<tr>' . implode('', array_map(fn($key) => '<td>' . $obj->$key . '</td>', $keys)) . "</tr>\n";
    }

    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);
        unset($groupPassenger->GroupProvider_JSON);
        $Contact = $this->findQuestion($PassengerReviewAll, 18);
        $Moments = $this->findQuestion($PassengerReviewAll, 11);
        $Comments = $this->findQuestion($PassengerReviewAll, 13);

        

        $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));
        
        return $groupPassenger;
    }

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

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

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

}
