<?php

namespace App\Modules\Report\Booking\Domain;

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

class BySellerMonthlyReportBooking implements StorageCriteria
{

    public function __construct(
        private DateStart $dateStart,
        private DateEnd $dateEnd
    ) {
    }

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

    private function generateSelectQuery(array $selectColumns)
    {
        return implode(",\n\t", $selectColumns);
    }

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


        // 1 month interval
        $dateInterval = new DateInterval('P1M');

        // months between date start and date end
        $datePeriod = new \DatePeriod(
            new \DateTime($dateStart),
            $dateInterval,
            new \DateTime($dateEnd)
        );

        $firstColumns = [
            '`a`.`Admin_Name`',
            '`a`.`Admin_LastName`',
            '`a`.`Admin_Abrv`',
            '`a`.`Id_Admin`',
            '`b`.`Id_Booking`',
            '`fn_booking_count_passengers_to_travel`(`b`.`Id_Booking`) AS `Passengers`',
            '`fn_booking_have_salkantay`(`b`.`Id_Booking`) AS `Salkantay`', // 0 or 1
            'IF(`u`.`User_Type` = 2, 1, 0) AS `Endorser`',
            'IF(`u`.`User_Type` = 1 AND `b`.`Booking_Type` = 1, 1, 0) AS `ReservaWeb`',
            'DAY(`Booking_DateConfirm`) AS `Day`',
            'MONTH(`Booking_DateConfirm`) AS `Month`',
            'YEAR(`Booking_DateConfirm`) AS `Year`'
        ];

        $secondColumns = [
            '`aux`.`Admin_Name`',
            '`aux`.`Admin_LastName`',
        ];

        //  BOOKING
        // $secondColumns[] = 'COUNT(`aux`.`Id_Booking`) AS `BOOKINGS_TOTAL`';
        $generic = fn (callable $callable, string $title) => fn (int $month, int $year, string $monthName) => "COUNT(IF(" . $callable($month, $year) . ", `aux`.`Id_Booking`, NULL)) AS `$title $monthName`";
        $generic2 = fn (string $apply) => fn (string $month, string $year) => "`Month` = $month AND `Year` = $year$apply";

        $data = [
            $generic($generic2(" AND `Endorser`"), 'ENDOSADORES'),
            $generic($generic2(" AND `ReservaWeb`"), 'RESERVA WEB'),
            $generic($generic2(" AND NOT(`ReservaWeb` OR `Endorser`) AND `Salkantay`"), 'SALKANTAY'),
            $generic($generic2(" AND NOT(`ReservaWeb` OR `Endorser`) AND NOT(`Salkantay`)"), 'NOT SALKANTAY'),
            $generic($generic2(""), "TOTAL"),
        ];

        $monthNames = ['Enero', 'Febrero', 'Marzo', 'Abril', 'Mayo', 'Junio', 'Julio', 'Agosto', 'Septiembre', 'Octubre', 'Noviembre', 'Diciembre'];
        foreach ($datePeriod as $date) {
            $month = $date->format('m');
            $year = $date->format('Y');
            $secondColumns = array_merge($secondColumns, array_map(fn ($callback) => $callback($month, $year, $monthNames[$month - 1]), $data));
        }

        $secondColumns[] = 'SUM(`Passengers`) AS `PAX_TOTAL`';

        $generic = fn (callable $callable, string $title) => fn (int $month, int $year, string $monthName) => "SUM(IF(" . $callable($month, $year) . ", `Passengers`, NULL)) AS `$title $monthName`";
        $generic2 = fn (string $apply) => fn (string $month, string $year) => "`Month` = $month AND `Year` = $year$apply";

        $data = [
            $generic($generic2(" AND `Endorser`"), 'ENDOSADORES PAX'),
            $generic($generic2(" AND `ReservaWeb`"), 'RESERVA WEB PAX'),
            $generic($generic2(" AND NOT(`ReservaWeb` OR `Endorser`) AND `Salkantay`"), 'SALKANTAY PAX'),
            $generic($generic2(" AND NOT(`ReservaWeb` OR `Endorser`) AND NOT(`Salkantay`)"), 'NOT SALKANTAY PAX'),
            $generic($generic2(""), "TOTAL PAX"),
        ];

        foreach ($datePeriod as $date) {
            $month = $date->format('m');
            $year = $date->format('Y');
            $secondColumns = array_merge($secondColumns, array_map(fn ($callback) => $callback($month, $year, $monthNames[$month - 1]), $data));
        }
        $firstSelect = $this->generateSelectQuery($firstColumns);
        $secondSelect = $this->generateSelectQuery($secondColumns);

        $query = "SELECT $secondSelect
            FROM (
                SELECT $firstSelect
                FROM `t_booking` `b`
                LEFT JOIN `t_admin` `a` ON `b`.`Id_Admin` = `a`.`Id_Admin`
                LEFT JOIN `t_user` `u` ON `u`.`Id_User` = `b`.`Id_User`
                WHERE `b`.`Booking_DateConfirm` BETWEEN '$dateStart' AND '$dateEnd'
                AND `b`.`Booking_Status` > 1
            ) `aux`
            GROUP BY `aux`.`Id_Admin`";
        echo $query . '<br/>';
        return DB::select(
            $query,
            []
        );
    }

    protected function analize($data)
    {
        return $data;
    }
}
