<?php

namespace App\Http\Controllers\Hotel;

use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\File;
use Illuminate\Http\Request;
use Carbon\CarbonPeriod;
use Carbon\Carbon;
use stdClass;


use App\Models\Environment\PaymentMethod;
use App\Models\Hotel\Client;
use App\Models\Hotel\Regimen;
use App\Models\Hotel\Reservation;
use App\Models\Hotel\ReservationHistory;
use App\Models\Hotel\Room;
use App\Models\Hotel\RoomType;
use App\Models\Hotel\Source;

use App\Models\Hotel\LodgingPayments;
use App\Models\Hotel\ConsumptionPayments;
use App\Models\Hotel\ExtraPayments;

use App\Models\User;

use App\Helpers\Environment\SystemFeaturesHelper;
use App\Helpers\Formats\FormatCurrencyHelper;

//? Modulo encargado para la sección de Informes

class InformesHotelController extends Controller
{
    //* Constructor
        public function __construct() {
            $this->middleware(['auth']);
        }

    //* On Main View
    public function index() {

        // Extract Hotel Reports Structure
        $hotel_reports = json_decode(File::get(base_path('resources/json/hotel/hoteleria_reportes_base.json')), true);

        // Traduce títulos dinámicamente
            foreach ($hotel_reports['statisticalSummaries'] as &$item) {
                $item['title'] = __($item['title']);
            }

            foreach ($hotel_reports['generalReports'] as &$item) {
                $item['title'] = __($item['title']);
            }

            foreach ($hotel_reports['statisticalReports'] as $key => &$group) {
                foreach ($group as &$report) {
                    $report['title'] = __($report['title']);
                }
            }

        // Define Data to Show
        $statisticalSummaries = $hotel_reports['statisticalSummaries'];
        $generalReports = $hotel_reports['generalReports'];
        $statisticalReports = new stdClass;
            $statisticalReports->ingresos = $hotel_reports['statisticalReports']['ingresos'];
            $statisticalReports->reservas = $hotel_reports['statisticalReports']['reservas'];
            $statisticalReports->clientes = $hotel_reports['statisticalReports']['clientes'];

        return view('hotel.reports.index', compact(['statisticalReports', 'generalReports', 'statisticalSummaries']));
    }


    //* Reportes Data
      // Incomes
        public function checkinBalances() {
            $timezone = config('app.timezone');
            $reservations = Reservation::where('has_checkIn', 'Si')->where('has_checkOut', 'No')->get();
            $lodgingP_total = 0.0;
            $consumptionP_total = 0.0;
            $extraP_total = 0.0;
            $final_total = 0.0;

            foreach ($reservations as $reserve) {
                $lodgingP_total += $reserve->lodgingPayments->where('status', 'Activo')
                                                            ->where('payment_date', '>=', Carbon::today($timezone))
                                                            ->sum('payment_price');

                $consumptionP_total += $reserve->consumptionPayments->where('status', 'Activo')
                                                                    ->where('payment_date', '>=', Carbon::today($timezone))
                                                                    ->sum('payment_price');

                $extraP_total += $reserve->extraPayments->where('status', 'Activo')
                                                        ->where('payment_date', '>=', Carbon::today($timezone))
                                                        ->sum('payment_price');

            }
            $final_total = $lodgingP_total + $consumptionP_total + $extraP_total;
            $lodgingP_total = number_format($lodgingP_total, 2, '.', '');
            $consumptionP_total = number_format($consumptionP_total, 2, '.', '');
            $extraP_total = number_format($extraP_total, 2, '.', '');
            $final_total = number_format($final_total, 2, '.', '');

            if (request()->ajax()) {

                return datatables()->of($reservations)
                    ->addColumn('room_name', function ($rooms) {
                        return $rooms->Room->room_name;
                    })
                    ->addColumn('lodging_payments', function ($lodgingP) {
                        $lodging = $lodgingP->lodgingPayments->where('status', 'Activo')->sum('payment_price');
                        return number_format($lodging, 2, '.', '');
                    })

                    ->addColumn('consumption_charges', function ($consumptionC) {
                        $consumption = $consumptionC->consumptionCharges->where('status', 'Activo')->sum('consumption_price');
                        return number_format($consumption, 2, '.', '');
                    })
                    ->addColumn('consumption_payments', function ($consumptionP) {
                        $consumption = $consumptionP->consumptionPayments->where('status', 'Activo')->sum('payment_price');
                        return number_format($consumption, 2, '.', '');
                    })

                    ->addColumn('extra_charges', function ($extraC) {
                        $extra = $extraC->extraCharges->where('status', 'Activo')->sum('extras_price');
                        return number_format($extra, 2, '.', '');
                    })
                    ->addColumn('extra_payments', function ($extraP) {
                        $extra = $extraP->extraPayments->where('status', 'Activo')->sum('payment_price');
                        return number_format($extra, 2, '.', '');
                    })

                    ->with([
                        'totals' => [
                            'lodging' => (float) $lodgingP_total,
                            'consumption' => (float) $consumptionP_total,
                            'extras' => (float) $extraP_total,
                            'final' => (float) $final_total,
                        ]
                    ])

                    ->make(true);
            }

            return view('hotel.reports.queries.checkinBalances');

        }

        public function checkoutBalances(Request $request) {
          //* Table Data
            if (request()->ajax()) {
              //* Build Filters
                // Config Data
                $tz = config('app.timezone');

                // Control Data
                $from = $request->filled('from')
                    ? Carbon::createFromFormat('Y-m-d', $request->from, $tz)->startOfDay()
                    : now($tz)->startOfMonth()->startOfDay();

                $to = $request->filled('to')
                    ? Carbon::createFromFormat('Y-m-d', $request->to, $tz)->endOfDay()
                    : now($tz)->endOfMonth()->endOfDay();

              //* Build Table
                return datatables()->of(
                    Reservation::whereBetween('date_entry', [$from, $to])->where('has_checkIn', 'Si')->where('has_checkOut', 'Si')->get()
                )
                    ->addColumn('recepcionist', function ($users) {
                        return $users->user->name;
                    })

                    ->addColumn('lodging_payments', function ($lodgingP) {
                        $lodging = $lodgingP->lodgingPayments->where('status', 'Activo')->sum('payment_price');
                        return number_format($lodging, 2, '.', '');
                    })

                    ->addColumn('consumption_charges', function ($consumptionC) {
                        $consumption = $consumptionC->consumptionCharges->where('status', 'Activo')->sum('consumption_price');
                        return number_format($consumption, 2, '.', '');
                    })
                    ->addColumn('consumption_payments', function ($consumptionP) {
                        $consumption = $consumptionP->consumptionPayments->where('status', 'Activo')->sum('payment_price');
                        return number_format($consumption, 2, '.', '');
                    })

                    ->addColumn('extra_charges', function ($extraC) {
                        $extra = $extraC->extraCharges->where('status', 'Activo')->sum('extras_price');
                        return number_format($extra, 2, '.', '');
                    })
                    ->addColumn('extra_payments', function ($extraP) {
                        $extra = $extraP->extraPayments->where('status', 'Activo')->sum('payment_price');
                        return number_format($extra, 2, '.', '');
                    })
                    ->make(true);
            }

          //* Return only view
            return view('hotel.reports.queries.checkoutBalances');
        }

        public function incomePerMonth(Request $request)  {
          //* Table Data
            if (request()->ajax()) {
              // Control Data
                Carbon::setLocale(app()->getLocale());
                $year = $request->integer('year');

                $income = [];

              // Build Returned Data
                for ($j = 1; $j <= 12; $j++) {
                    $month = ucfirst(Carbon::create($year, $j, 10)->translatedFormat('F Y'));

                    $lodgingByMonth = LodgingPayments::whereYear('payment_date', $year)
                                        ->whereMonth('payment_date', $j)->where('status', 'Activo')->sum('payment_price');
                    $consumptionByMonth = ConsumptionPayments::whereYear('payment_date', $year)
                                        ->whereMonth('payment_date', $j)->where('status', 'Activo')->sum('payment_price');
                    $extraByMonth = ExtraPayments::whereYear('payment_date', $year)
                                        ->whereMonth('payment_date', $j)->where('status', 'Activo')->sum('payment_price');

                    $data = [
                        "income_month" => $month,
                        "income_lodging" => $lodgingByMonth,
                        "income_consumption" => $consumptionByMonth,
                        "income_extras" => $extraByMonth,
                    ];

                    array_push($income, $data);

                }

                return datatables()->of($income) ->make(true);
            }

          //* Return View Data
            return view('hotel.reports.queries.incomePerMonth');

        }

        public function incomePerPeriod(Request $request)  {

            if (request()->ajax()){
                //* Obtenemos todos los pagos realizados dentro del periodo dado
                    if(
                        $request->has('start_date') && $request->filled('start_date') &&
                        $request->has('end_date') && $request->filled('end_date')
                    ) {
                        $start_date = $request->start_date;
                        $end_date = $request->end_date;
                    } else {
                        $start_date = date("Y-m-01");
                        $end_date = date("Y-m-t");
                    }

                    $start_date = Carbon::parse($start_date)->startOf('day');
                    $end_date = Carbon::parse($end_date)->endOf('day');

                    $lodging_payments = LodgingPayments::where('status', 'Activo')->whereBetween('payment_date', [$start_date, $end_date])->get();
                    $extra_payments = ExtraPayments::where('status', 'Activo')->whereBetween('payment_date', [$start_date, $end_date])->get();
                    $consumption_payments = ConsumptionPayments::where('status', 'Activo')->whereBetween('payment_date', [$start_date, $end_date])->get();

                //* Obtenemos los ID de las Reservaciones correspondientes a los pagos (sin repetir el ID dentro del Array)
                    $reservation_ids = collect([$lodging_payments, $extra_payments, $consumption_payments])
                        ->flatten()
                        ->pluck('reservation_id')
                        ->unique();

                    $reservations = Reservation::whereIn('id', $reservation_ids)->get();

                //* Construimos la colección a retornar

                    $incomes = [];

                    foreach ($reservations as $reservation) {
                        $incomes_data = new stdClass;
                        $incomes_data->date_entry = $reservation->date_entry;
                        $incomes_data->id = $reservation->id;
                        $incomes_data->client_reference = $reservation->client_reference;
                        $incomes_data->room_name = $reservation->Room->room_name;
                        $incomes_data->total_income = 0;
                        $incomes_data->total_income_simple = 0;

                        foreach ($lodging_payments as $payment)
                            if ($payment->reservation_id == $reservation->id)
                                $incomes_data->total_income += $payment->payment_price;

                        foreach ($consumption_payments as $payment)
                            if ($payment->reservation_id == $reservation->id)
                                $incomes_data->total_income += $payment->payment_price;

                        foreach ($extra_payments as $payment)
                            if ($payment->reservation_id == $reservation->id)
                                $incomes_data->total_income += $payment->payment_price;

                        $incomes_data->total_income_simple = $incomes_data->total_income;
                        $incomes_data->total_income = FormatCurrencyHelper::formatCurrency( number_format($incomes_data->total_income, 2, '.', ',') );

                        array_push($incomes,$incomes_data);
                    }

                return datatables()->of($incomes)
                    ->addColumn('payment_types', function($data) use ($lodging_payments, $consumption_payments, $extra_payments) {
                        $payment_types = '<ol style="text-align: justify; padding-left: 1.8rem; list-style-type:upper-roman">';

                        foreach ($lodging_payments as $payment)
                            if ($payment->reservation_id == $data->id)
                                $payment_types .= '<li>Hospedaje</li>';

                        foreach ($consumption_payments as $payment)
                            if ($payment->reservation_id == $data->id)
                                $payment_types .= '<li>Consumo</li>';

                        foreach ($extra_payments as $payment)
                            if ($payment->reservation_id == $data->id)
                                $payment_types .= '<li>Extra</li>';

                        $payment_types .= '</ol>';

                        return $payment_types;
                    })
                    ->addColumn('payment_incomes', function($data) use ($lodging_payments, $consumption_payments, $extra_payments) {
                        $payment_incomes = '<ol style="text-align: justify; padding-left: 1.8rem; list-style-type:upper-roman">';

                        foreach ($lodging_payments as $payment)
                            if ($payment->reservation_id == $data->id)
                                $payment_incomes .= '<li>' . FormatCurrencyHelper::formatCurrency( number_format($payment->payment_price, 2, '.', ',') ) . '</li>';

                        foreach ($consumption_payments as $payment)
                            if ($payment->reservation_id == $data->id)
                                $payment_incomes .= '<li>' . FormatCurrencyHelper::formatCurrency( number_format($payment->payment_price, 2, '.', ',') ) . '</li>';

                        foreach ($extra_payments as $payment)
                            if ($payment->reservation_id == $data->id)
                                $payment_incomes .= '<li>' . FormatCurrencyHelper::formatCurrency( number_format($payment->payment_price, 2, '.', ',') ) . '</li>';

                        $payment_incomes .= '</ol>';

                        return $payment_incomes;
                    })
                    ->addColumn('payment_methods', function($data) use ($lodging_payments, $consumption_payments, $extra_payments) {
                        $payment_methods = '<ol style="text-align: justify; padding-left: 1.8rem; list-style-type:upper-roman">';

                        foreach ($lodging_payments as $payment)
                            if ($payment->reservation_id == $data->id)
                                $payment_methods .= '<li>' . $payment->payment_method . '</li>';

                        foreach ($consumption_payments as $payment)
                            if ($payment->reservation_id == $data->id)
                                $payment_methods .= '<li>' . $payment->payment_method . '</li>';

                        foreach ($extra_payments as $payment)
                            if ($payment->reservation_id == $data->id)
                                $payment_methods .= '<li>' . $payment->payment_method . '</li>';

                        $payment_methods .= '</ol>';

                        return $payment_methods;
                    })
                    ->rawColumns(['payment_types', 'payment_incomes', 'payment_methods'])

                    ->with([
                        'totals' => [
                            'lodging' => $lodging_payments->sum('payment_price'),
                            'consumption' => $extra_payments->sum('payment_price'),
                            'extras' => $consumption_payments->sum('payment_price')
                        ]
                    ])
                    ->make(true);
            }

            return view('hotel.reports.queries.incomePerPeriod');

        }

        public function incomePerPeriodAndPaymentMethod(Request $request)  {

            if (request()->ajax()){
                //* Obtenemos todos los pagos realizados dentro del periodo dado
                    if(
                        $request->has('start_date') && $request->filled('start_date') &&
                        $request->has('end_date') && $request->filled('end_date') &&
                        $request->has('payment_method') && $request->filled('payment_method')
                    ) {
                        $start_date = Carbon::parse($request->start_date)->startOfDay();
                        $end_date = Carbon::parse($request->end_date)->endOfDay();

                        $lodging_payments = LodgingPayments::where('status', 'Activo')->whereBetween('payment_date', [$start_date, $end_date])->where('payment_method', $request->payment_method)->get();
                        $extra_payments = ExtraPayments::where('status', 'Activo')->whereBetween('payment_date', [$start_date, $end_date])->where('payment_method', $request->payment_method)->get();
                        $consumption_payments = ConsumptionPayments::where('status', 'Activo')->whereBetween('payment_date', [$start_date, $end_date])->where('payment_method', $request->payment_method)->get();
                    } else {
                        $start_date = Carbon::now()->startOfMonth()->startOfDay();
                        $end_date = Carbon::now()->endOfMonth()->endOfDay();

                        $lodging_payments = LodgingPayments::where('status', 'Activo')->whereBetween('payment_date', [$start_date, $end_date])->get();
                        $extra_payments = ExtraPayments::where('status', 'Activo')->whereBetween('payment_date', [$start_date, $end_date])->get();
                        $consumption_payments = ConsumptionPayments::where('status', 'Activo')->whereBetween('payment_date', [$start_date, $end_date])->get();
                    }


                //* Obtenemos las Reservaciones correspondientes a los pagos

                $reservation_ids = collect([$lodging_payments, $extra_payments, $consumption_payments])
                                        ->flatten()
                                        ->pluck('reservation_id')
                                        ->unique();

                $reservations = Reservation::whereIn('id', $reservation_ids)->get();

                //* Construimos la colección a retornar

                    $incomes = [];

                    foreach ($reservations as $reservation) {
                        $incomes_data = new stdClass;
                            $incomes_data->date_entry = $reservation->date_entry;
                            $incomes_data->id = $reservation->id;
                            $incomes_data->client_reference = $reservation->client_reference;
                            $incomes_data->room_name = $reservation->Room->room_name;
                            $incomes_data->lodgingPayments = 0;
                            $incomes_data->consumptionPayments = 0;
                            $incomes_data->extraPayments = 0;

                            foreach($lodging_payments as $payment)
                                if($payment->reservation_id == $reservation->id)
                                    $incomes_data->lodgingPayments += $payment->payment_price;

                            foreach($consumption_payments as $payment)
                                if($payment->reservation_id == $reservation->id)
                                    $incomes_data->consumptionPayments += $payment->payment_price;

                            foreach($extra_payments as $payment)
                                if($payment->reservation_id == $reservation->id)
                                    $incomes_data->extraPayments += $payment->payment_price;

                            $incomes_data->totalIncomeSimple = $incomes_data->lodgingPayments + $incomes_data->consumptionPayments + $incomes_data->extraPayments;
                            $incomes_data->lodgingPaymentsSimple = $incomes_data->lodgingPayments;
                            $incomes_data->consumptionPaymentsSimple = $incomes_data->consumptionPayments;
                            $incomes_data->extraPaymentsSimple = $incomes_data->extraPayments;

                            $incomes_data->lodgingPayments = FormatCurrencyHelper::formatCurrency( $incomes_data->lodgingPaymentsSimple );
                            $incomes_data->consumptionPayments = FormatCurrencyHelper::formatCurrency( $incomes_data->consumptionPaymentsSimple );
                            $incomes_data->extraPayments = FormatCurrencyHelper::formatCurrency( $incomes_data->extraPaymentsSimple );
                            $incomes_data->totalIncome = FormatCurrencyHelper::formatCurrency($incomes_data->totalIncomeSimple);

                        array_push($incomes,$incomes_data);
                    }


                return datatables()->of($incomes)
                    ->make(true);
            }

            $paymentMethods = PaymentMethod::all();
            return view('hotel.reports.queries.incomePerPeriodAndPaymentMethod', compact('paymentMethods'));

        }

        public function incomePerPeriodAndRoomType(Request $request) {

            if (request()->ajax()){
                //* Definimos la información a utilizar
                  // Si el rango de fechas fue dado
                    $start_date = $request->filled('start_date')
                        ? $request->start_date : Carbon::now()->startOfWeek(Carbon::SUNDAY)->format('Y-m-d');
                    $end_date = $request->filled('end_date')
                        ? $request->end_date : Carbon::now()->endOfWeek(Carbon::SATURDAY)->format('Y-m-d');

                    $start_date = Carbon::parse($start_date)->startOf('day');
                    $end_date = Carbon::parse($end_date)->endOf('day');

                  // Control Data
                    $room_types = RoomType::whereNotIn('id', [1, 2])->get();
                    $payment_methods = PaymentMethod::pluck('method_name');

                  // Rango de Fechas desglosado
                    $considered_dates = collect(CarbonPeriod::create($start_date, $end_date))->map(fn (Carbon $date) => $date->format('Y-m-d'));

                  // Pagos realizados dentro del periodo
                    $lodging_payments = LodgingPayments::where('status', 'Activo')->whereBetween('payment_date', [$start_date, $end_date])->get();
                    $extra_payments = ExtraPayments::where('status', 'Activo')->whereBetween('payment_date', [$start_date, $end_date])->get();
                    $consumption_payments = ConsumptionPayments::where('status', 'Activo')->whereBetween('payment_date', [$start_date, $end_date])->get();
                    $reservations = Reservation::whereDate('date_entry', '<=', $end_date)
                        ->whereDate('date_departure', '>=', $start_date)
                        ->get();

                    // Obtener el total de ingresos por origen
                        $lodging_total = $lodging_payments->sum('payment_price');
                        $consumption_total = $extra_payments->sum('payment_price');
                        $extra_total = $consumption_payments->sum('payment_price');

                    // Agrupamos los pagos de los módulos según el método y la fecha de registro
                        // El identificador de cada agrupación es "FECHA_REGISTRO | METODO_PAGO"
                    $groupedLodging = $lodging_payments->groupBy(
                        fn($p) => Carbon::parse($p->payment_date)->format('Y-m-d') . '|' . $p->payment_method
                    );
                    $groupedExtra = $extra_payments->groupBy(
                        fn($p) => Carbon::parse($p->payment_date)->format('Y-m-d') . '|' . $p->payment_method
                    );
                    $groupedConsumption = $consumption_payments->groupBy(
                        fn($p) => Carbon::parse($p->payment_date)->format('Y-m-d') . '|' . $p->payment_method
                    );

                    // Construcción de datos
                    $incomes = $considered_dates->map(function ($date) use ($room_types, $payment_methods, $reservations, $groupedLodging, $groupedExtra, $groupedConsumption) {
                        $data = (object)[
                            'date' => $date,
                            'room_types' => [],
                            'occupied_rooms_quantity' => 0,
                            'lodging_incomes' => [],
                            'consumption_incomes' => [],
                            'extra_incomes' => [],
                            'total_income' => 0,
                        ];

                        $parsedDate = Carbon::parse($date);

                        // Definir Ocupación por tipo y día
                        foreach ($room_types as $type) {
                            // Revisamos cuantas habitaciones (por cada Tipo) estaban ocupadas en el día indicado
                            $occupied = $reservations->where('roomType_id', $type->id)
                                ->filter(fn($r) => $parsedDate->between(Carbon::parse($r->date_entry), Carbon::parse($r->date_departure)->subDay()))
                                ->count();

                            $data->occupied_rooms_quantity += $occupied;
                            $data->room_types[] = (object)[
                                'name' => $type->tr_name,
                                'occupied_rooms' => $occupied,
                            ];
                        }

                        // Obtenemos los ingresos por modulo y día
                        foreach ($payment_methods as $method) {
                            $key = $date . '|' . $method;

                            $lodgingTotal = $groupedLodging->get($key)?->sum('payment_price') ?? 0;
                            $consumptionTotal = $groupedConsumption->get($key)?->sum('payment_price') ?? 0;
                            $extraTotal = $groupedExtra->get($key)?->sum('payment_price') ?? 0;

                            $data->lodging_incomes[] = (object)['method' => $method, 'value' => $lodgingTotal];
                            $data->consumption_incomes[] = (object)['method' => $method, 'value' => $consumptionTotal];
                            $data->extra_incomes[] = (object)['method' => $method, 'value' => $extraTotal];

                            $data->total_income += $lodgingTotal + $consumptionTotal + $extraTotal;
                        }

                        // Damos formato a los totales
                        $data->total_income_simple = number_format($data->total_income, 2, '.', ',');
                        $data->total_income = FormatCurrencyHelper::formatCurrency($data->total_income_simple);

                        return $data;
                    });

                return datatables()->of($incomes)
                    ->addColumn("room_types", fn($data) => $this->renderList($data->room_types, 'name', 'occupied_rooms', __('neutral_words')['occupied']))
                    ->addColumn("occupied_rooms_quantity", fn($data) => "<span  style='font-size:1.1rem'>{$data->occupied_rooms_quantity}</span>")
                    ->addColumn("lodging_incomes", fn($data) => $this->renderMoneyList($data->lodging_incomes))
                    ->addColumn("consumption_incomes", fn($data) => $this->renderMoneyList($data->consumption_incomes))
                    ->addColumn("extra_incomes", fn($data) => $this->renderMoneyList($data->extra_incomes))
                    ->rawColumns(["room_types", "occupied_rooms_quantity",  "lodging_incomes", "consumption_incomes", "extra_incomes"])

                    ->with([
                        'totals' => [
                            'lodging' => (float) $lodging_total,
                            'consumption' => (float) $consumption_total,
                            'extras' => (float) $extra_total,
                        ]
                    ])
                    ->make(true);
            }

            return view('hotel.reports.queries.incomePerPeriodAndRoomType');
        }
        public function renderList($items, $labelKey, $valueKey, $singular) {
            $plural = session('locale') === "en"
                ? $singular
                : $singular."s";

            $html = '<ul style="text-align: justify; padding-left: 1.8rem;">';
            foreach ($items as $item) {
                $label = $item->{$labelKey};
                $value = $item->{$valueKey};
                $suffix = $value == 1 ? $singular : $plural;
                $html .= "<li><span style='font-weight: 500;'>{$label}:</span> {$value} {$suffix}</li>";
            }
            return $html . '</ul>';
        }

        public function renderMoneyList($items) {
            $html = '<ul style="text-align: justify; padding-left: 1.8rem;">';
            foreach ($items as $item) {
                $formatted = FormatCurrencyHelper::formatCurrency(number_format($item->value, 2, '.', ','));
                $html .= "<li><span style='font-weight: 500;'>{$item->method}:</span> {$formatted}</li>";
            }
            return $html . '</ul>';
        }

      // Reservation
        public function reservationsPerPeriod() {
            if(request()->ajax()){

                return datatables()->of(Reservation::where('has_checkIn', 'Si')->get())
                    ->addColumn('user',function($user){
                        return $user->User->name;
                    })
                    ->addColumn('num_room',function($rooms){
                        return $rooms->Room->room_name;
                    })
                    ->addColumn('estado',function($statuses){
                        return __('seeders')['statuses'][$statuses->Status->status_name];
                    })
                    ->make(true);
            }

            return view('hotel.reports.queries.reservationsPerPeriod');

        }

        public function deferredBookings() {
            if (request()->ajax()) {

                return datatables()->of(Reservation::where('guest_status_id', 5)->get())
                    ->addColumn('recepcionist', function ($users) {
                        return $users->user->name;
                    })
                    ->addColumn('source', function ($sources) {
                        return $sources->source->source_name;
                    })
                    ->addColumn('regimen', function ($regimens) {
                        return $regimens->regimen->regimen_name;
                    })
                    ->addColumn('room', function ($rooms) {
                        return $rooms->room->room_name;
                    })
                    ->make(true);
            }

            return view('hotel.reports.queries.deferredBookings');

        }

        public function commonSources() {

            if (request()->ajax()) {

                // Reservation Data
                $sourcesStatistics = [];

                $sources = Source::all();
                foreach ($sources as $source) {

                    $reservationQuantity = Reservation::all()->count();
                    $sourceQuantity = Reservation::where('source_id', $source->id)->count();
                    $sourcePercentage = ($sourceQuantity/$reservationQuantity)*100;

                    $data = [
                        "source_name" => $source->source_name,
                        "source_quantity" => $sourceQuantity,
                        "source_percentage" => number_format($sourcePercentage, 2, '.', ',').'%',
                    ];

                    array_push($sourcesStatistics, $data);
                }
                return datatables()->of($sourcesStatistics)
                    ->make(true);
            }

            return view('hotel.reports.queries.commonSources');

        }

        public function bookingHistory() {

            $histories = ReservationHistory::all()->reverse();
            $historyFormat = [];

            $button_message = __('reports')['hotel']['statisticalReports']['reservations']['consult_history'];

            foreach ($histories as $history) {
                $data = [
                    "creation" => Carbon::createFromFormat('Y-m-d H:i:s', $history->created_at)->format('Y-m-d'),
                    "time" => Carbon::parse($history->created_at)->format('H:i:s'),
                    "id" => $history->reservation_data['id'],
                    "client" => $history->reservation_data['client'],
                    "editor_name" => $history->editor_name,
                    "history" => '<button title="'.$button_message.'" type="button" style="font-size:0.9rem; font-weight:400" name="activeHistory" id="' . $history->id . '" class="mb-1 activeHistory btn btn-success btn-sm col-4" >&#10687</button>',
                ];

                array_push($historyFormat, $data);
            }

            if (request()->ajax()) {
                return datatables()->of($historyFormat)
                    ->rawColumns(['history'])
                    ->make(true);
            }

            return view('hotel.reports.queries.bookingHistory');

        }
        public function bookingHistoryData($id){
            if(request()->ajax()){
                $data = ReservationHistory::findOrFail($id);
                $data->created_at = $data->created_at->toDateTimeString();
                return response()->json(['data' => $data]);
            }
        }

        public function bookingUpdateHistory($folio) {

            $histories = ReservationHistory::where('id_reservation',$folio)->orderBy('id', 'desc')->get();
            $historyFormat = [];
            $changes_html = "";

            foreach ($histories as $history) {

                switch ($history->reservation_data['action']) {
                    case 'Update':
                        if(count($history->reservation_changes)){
                            $changes_html = '<ul>';
                            foreach ($history->reservation_changes as $key => $changes) {

                                $key = $this->setNameData($key);

                                $changes_html = $changes_html.'<li class="text-left" style="font-size: 0.9rem;"><b>'.$key.'</b>: <span> </span>'.$changes.'</li>';
                            }
                            $changes_html = $changes_html.'</ul>';
                        } else{
                            $changes_html = '<p class="text-center" style="font-size: 1.1rem; font-weight:600; color:#1c1c1c;">- '. __('reports')['hotel']['statisticalReports']['reservations']['no_changes'] .' -</p>';
                        }
                        break;

                    case 'Create':
                        $changes_html = '<p class="text-center" style="font-size: 1.1rem; font-weight:600;">- '. __('reports')['hotel']['statisticalReports']['reservations']['created'] .' -</p>';
                        break;

                    case 'Delete':
                        $changes_html = '<p class="text-center" style="font-size: 1.1rem; font-weight:600; color:#dc3545;">- '. __('reports')['hotel']['statisticalReports']['reservations']['deleted'] .' -</p>';
                        break;
                }

                $data = [
                    "creation" => Carbon::createFromFormat('Y-m-d H:i:s', $history->created_at)->format('Y-m-d'),
                    "time" => Carbon::parse($history->created_at)->format('H:i:s'),
                    "id" => $history->reservation_data['id'],
                    "client" => $history->reservation_data['client'],
                    "editor_name" => $history->editor_name,
                    "changes" => $changes_html,
                ];

                array_push($historyFormat, $data);
            }

            if (request()->ajax()) {
                return datatables()->of($historyFormat)
                    ->rawColumns(['changes'])
                    ->make(true);
            }

            return view('hotel.reports.queries.bookingUpdateHistory', compact('folio'));

        }
        public function setNameData($reservation_field){
            $key_value = [
                'client' => __('entity_fields')['reservation']['client'],
                'checkin' => __('entity_fields')['reservation_changes']['check_in'],
                'checkin_time' => __('entity_fields')['reservation_changes']['check_in_time'],
                'checkout' => __('entity_fields')['reservation_changes']['check_out'],
                'checkout_time' => __('entity_fields')['reservation_changes']['check_out_time'],
                'room_type' => __('entity_fields')['common']['room_type'],
                'room_name' => __('entity_fields')['reservation_changes']['room_name'],
                'status' => __('entity_fields')['reservation_changes']['status'],
                'guest_status' => __('entity_fields')['reservation_changes']['guest_status'],
                'adults' => __('entity_fields')['reservation_changes']['adults'],
                'children' => __('entity_fields')['reservation_changes']['children'],
                'nights' => __('entity_fields')['reservation_changes']['nights'],
                'hours' => __('entity_fields')['reservation_changes']['hours'],
                'regimen' => __('entity_fields')['reservation']['regimen'],
                'lodging_contract' => __('entity_fields')['reservation_changes']['lodging_contract'],
                'consumption_contract' => __('entity_fields')['reservation_changes']['consumption_contract'],
                'extras_contract' => __('entity_fields')['reservation_changes']['extras_contract'],
                'source' => __('entity_fields')['reservation']['source'],
                'agency' => __('entity_fields')['reservation']['agency'],
                'room_cost' =>  __('entity_fields')['reservation_changes']['room_cost'],
                'lodging_cost' =>  __('entity_fields')['reservation_changes']['lodging_cost'],
                'consumption_cost' =>  __('entity_fields')['reservation_changes']['consumption_cost'],
                'extra_cost' =>  __('entity_fields')['reservation_changes']['extra_cost'],
                'total_cost' =>  __('entity_fields')['reservation_changes']['total_cost'],
            ];

            foreach ($key_value as $key => $value) {
                if ($key === $reservation_field) {
                    return $value;
                }
            }
            return 'Unknow';
        }

        public function checkinPerUserAndPeriod(Request $request) {
          //* Table Data
            if(request()->ajax()){
              // Build Filters
                // Default Query
                $query = Reservation::whereNotNull('checkin_receptionist_id')
                    ->whereYear('date_entry', $request->year)
                    ->whereMonth('date_entry', $request->month);

                // Search by applier
                if ($request->filled('receptionist_id'))
                    $query->where('checkin_receptionist_id', $request->receptionist_id);

                // Filter Data
                $query_count = $query->count();
                $query->get();

              // Build Table
                return datatables()->of($query)
                    ->addColumn('month',function($reservation){
                        $extracted_month = Carbon::parse($reservation->date_entry)->translatedFormat('F');
                        return ucfirst($extracted_month);
                    })
                    ->addColumn('user',function($reservation){
                        $checkin_user = $reservation->checkinUser;
                        return "{$checkin_user->name} {$checkin_user->last_name}";
                    })
                    ->addColumn('room_type',function($rooms){
                        return $rooms->RoomType->tr_name;
                    })
                    ->addColumn('room_number',function($rooms){
                        return $rooms->Room->room_name;
                    })
                    ->with([
                        'checkin_count' => $query_count,
                    ])
                    ->make(true);
            }

          //* View Data
            // Support Data
            $month_keys = [
                'jan', 'feb', 'mar', 'apr', 'may', 'jun',
                'jul', 'aug', 'sep', 'oct', 'nov', 'dec'
            ];

            $months = array_map(function ($key, $index) {
                return [ "value" => (string) ($index + 1), "name"  => __('months')[$key] ];
            }, $month_keys, array_keys($month_keys));

            // Return Data
            $users = User::all();
            $actual_month = date('m');
            $actual_year = date("Y");

            return view(
                'hotel.reports.queries.checkinPerUserAndPeriod',
                compact('users', 'months', 'actual_month', 'actual_year')
            );
        }

        public function occupationPerPeriod(Request $request) {
          //* Table Data
            if(request()->ajax()){
              //* Control Data
                // Extract Request
                $analyze_express = $request->boolean('analyze_express');
                $start = Carbon::parse($request->input('from'))->startOfDay();
                $end = Carbon::parse($request->input('to'))->endOfDay();

                // Define report period
                $daysBetween = $start->diffInDays($end) + 1;

                // Records
                $reservations = Reservation::with('roomType')
                    ->whereNotIn('roomType_id', [1, 2]) // Avoid Overbooked and Unassigned
                    ->where(function ($query) use ($start, $end) {
                        $query->whereBetween('date_entry', [$start, $end])
                            ->orWhereBetween('date_departure', [$start, $end])
                            ->orWhere(function ($q) use ($start, $end) {
                                $q->where('date_entry', '<=', $start)
                                    ->where('date_departure', '>=', $end);
                            });
                    })
                    ->get();

                $roomTypes = RoomType::whereNotIn('id', [1, 2]) // Avoid Overbooked and Unassigned
                    ->where('is_express_type', $analyze_express)->get();

              //* Compute Room Occupancy
                $roomTypesData = [];
                foreach ($roomTypes as $roomType) {
                    $nightsCover = [];
                    $occupiedNights = 0;
                    $occupiedRooms = $reservations->where('roomType_id', $roomType->id);

                    foreach ($occupiedRooms as $reserve) {
                        $entry = Carbon::parse($reserve->date_entry);
                        $departure = Carbon::parse($reserve->date_departure);

                        $periodStart = $entry->greaterThan($start) ? $entry : $start;
                        $periodEnd = $departure->lessThan($end) ? $departure : $end;

                        if ($periodStart <= $periodEnd) {
                            $period = CarbonPeriod::create($periodStart, $periodEnd);

                            foreach ($period as $date) {
                                $day = $date->format('Y-m-d');

                                $occupiedNights++;
                                $nightsCover[$day] = true; // evita duplicados
                            }
                        }
                    }

                    $totalRooms = Room::where('roomType_id', $roomType->id)->count();
                    $perfectOccupancy = $totalRooms * $daysBetween;

                    $occupationReached = $perfectOccupancy > 0
                        ? ($occupiedNights / $perfectOccupancy) * 100
                        : 0;

                    $roomTypesData[] = [
                        "room_type" => $roomType->tr_name,
                        "nights" => count($nightsCover),
                        "occupation" => number_format($occupationReached, 2) . '%',
                    ];
                }

                return datatables()->of($roomTypesData)->make(true);
            }

          //* View Data
            return view('hotel.reports.queries.occupationPerPeriod');
        }

        public function occupationPerMonth(Request $request) {
          //* Table Data
            if ($request->ajax()) {
              // Control Data
                // Extract Request
                $analyze_express = $request->boolean('analyze_express');
                $month = (int) $request->input('month');
                $year = (int) $request->input('year');

                // Define report period
                $monthLength = cal_days_in_month(CAL_GREGORIAN, $month, $year);

                $start = date("Y-m-d", strtotime("{$year}-{$month}-01"));
                $end = date("Y-m-d", strtotime("{$year}-{$month}-{$monthLength}"));

                // Get reserves on selected month
                $reservations = Reservation::with('roomType')->get();
                $reservesByPeriod = collect();

                foreach ($reservations as $reserve) {
                    // Exclude Overbooking & Unassigned
                    if (in_array($reserve->roomType->id, [1, 2])) continue;

                    // Apply express filter
                    if ($reserve->roomType->is_express_type != $analyze_express) continue;

                    // Correct intersection logic
                    if ($reserve->date_entry <= $end && $reserve->date_departure >= $start)
                        $reservesByPeriod->push($reserve);
                }

                //Get Room Types
                $roomTypes = RoomType::whereNotIn('id', [1, 2]) // Avoid Overbooked and Unassigned
                    ->where('is_express_type', $analyze_express)->get();

              //* Compute Room Occupancy
                $roomTypesData = [];
                foreach ($roomTypes as $roomType) {
                    $nightsCover = [];
                    $occupiedNights = 0;
                    $occupiedRooms = $reservesByPeriod->where('roomType_id', $roomType->id);

                    foreach ($occupiedRooms as $occupiedRoom) {
                        $entry = strtotime($occupiedRoom->date_entry);
                        $departure = strtotime($occupiedRoom->date_departure);

                        for ($day = strtotime($start); $day < strtotime($end); $day += 86400) {

                            if ($day >= $entry && $day < $departure) {
                                $occupiedNights++;

                                $date = date("Y-m-d", $day);
                                if (!in_array($date, $nightsCover)) {
                                    $nightsCover[] = $date;
                                }
                            }
                        }
                    }

                    $totalRooms = Room::where('roomType_id', $roomType->id)->count();
                    $perfectOccupancy = $totalRooms * $monthLength;

                    $occupationReached = $perfectOccupancy > 0
                        ? ($occupiedNights / $perfectOccupancy) * 100
                        : 0;

                    $roomTypesData[] = [
                        "room_type" => $roomType->tr_name,
                        "nights" => count($nightsCover),
                        "occupation" => number_format($occupationReached, 2, '.', '') . '%',
                    ];
                }

                return datatables()->of($roomTypesData)->make(true);
            }

          //* View Data
            // Support Data
            $month_keys = [
                'jan', 'feb', 'mar', 'apr', 'may', 'jun',
                'jul', 'aug', 'sep', 'oct', 'nov', 'dec'
            ];

            $months = array_map(function ($key, $index) {
                return [ "value" => (string) ($index + 1), "name"  => __('months')[$key] ];
            }, $month_keys, array_keys($month_keys));

            // Return Data
            $defaultYear = 2023;
            $actualYear = date("Y");
            $actualMonth = date('m');

            return view('hotel.reports.queries.occupationPerMonth', compact(['actualYear', 'actualMonth', 'months']));
        }

        public function occupationPerYear(Request $request) {
          //* Table Data
            if(request()->ajax()){
              //* Control Data
                // Extract Request
                $analyze_express = $request->boolean('analyze_express');
                $year = $request->input('year');

                // Define report period
                $start = "$year-01-01";
                $end   = "$year-12-31";

                $yearLength = Carbon::parse($start)->diffInDays($end) + 1; // Cantidad de días en el año

                // Records
                $reservations = Reservation::where(function ($q) use ($start, $end) {
                        $q->whereBetween('date_entry', [$start, $end])->orWhereBetween('date_departure', [$start, $end]);
                    })
                    ->whereHas('roomType', function ($q) {
                        $q->whereNotIn('id', [1, 2]); // Avoid Overbooked and Unassigned
                    })->get();

                $roomTypes = RoomType::whereNotIn('id', [1, 2]) // Avoid Overbooked and Unassigned
                    ->where('is_express_type', $analyze_express)->get();

              //* Compute Room Occupancy
                $roomTypesData = [];
                foreach ($roomTypes as $roomType) {
                    $nightsCover = [];
                    $occupiedNights = 0;
                    $occupiedRooms = $reservations->where('roomType_id', $roomType->id);

                    foreach ($occupiedRooms as $reserve) {
                        $entry = Carbon::parse($reserve->date_entry);
                        $departure = Carbon::parse($reserve->date_departure);

                        $startDate = $entry->copy()->max($start);
                        $endDate   = $departure->copy()->min($end);

                        for ($date = $startDate; $date < $endDate; $date->addDay()) {
                            $occupiedNights++;

                            $day = $date->toDateString();
                            $nightsCover[$day] = true;
                        }
                    }

                    $totalRooms = Room::where('roomType_id', $roomType->id)->count();
                    $perfectOccupancy = $totalRooms * $yearLength;

                    $occupationReached = $perfectOccupancy
                        ? ($occupiedNights / $perfectOccupancy) * 100
                        : 0;

                    $roomTypesData[] = [
                        "room_type" => $roomType->tr_name,
                        "nights" => count($nightsCover),
                        "occupation" => number_format($occupationReached, 2) . '%',
                    ];
                }

                return datatables()->of($roomTypesData)->make(true);
            }

          //* View Data
            $actualYear = date("Y");
            return view('hotel.reports.queries.occupationPerYear', compact('actualYear'));
        }

        public function arrivesPerPeriod() {

            if(request()->ajax()){

                return datatables()->of(Reservation::latest())
                    ->addColumn('room',function($rooms){
                        return $rooms->Room->room_name;
                    })
                    ->addColumn('room_type',function($roomTypes){
                        return $roomTypes->roomType->tr_name;
                    })
                    ->addColumn('agency',function($agencies){
                        $regimen_value = 'Ninguna';
                        if($agencies?->agency){
                            $regimen_value = $agencies->agency->agency_name;
                        }
                        return $regimen_value;
                    })
                    ->addColumn('regimen',function($regimens){
                        return $regimens->regimen->regimen_name;
                    })
                    ->make(true);
            }

            return view('hotel.reports.queries.arrivesPerPeriod');

        }

        public function overbookingPerPeriod() {

            if(request()->ajax()){
                return datatables()->of(Reservation::where('roomType_id', 1)->get())
                    ->addColumn('source',function($sources){
                        return $sources->source->source_name;
                    })
                    ->addColumn('regimen',function($regimens){
                        return $regimens->regimen->regimen_name;
                    })
                    ->make(true);
            }

            return view('hotel.reports.queries.overbookingPerPeriod');

        }

        public function overbookingOnFuture() {

            if(request()->ajax()){
                $mutable = Carbon::now();
                $overBookings = Reservation::where('roomType_id', 1)->get();
                $futureOverBookings = [];

                foreach ($overBookings as $overBooking) {
                    if($mutable < $overBooking->date_entry){
                        array_push($futureOverBookings, $overBooking);
                    }
                }

                return datatables()->of($futureOverBookings)
                    ->addColumn('source',function($sources){
                        return $sources->source->source_name;
                    })
                    ->addColumn('regimen',function($regimens){
                        return $regimens->regimen->regimen_name;
                    })
                    ->make(true);
            }

            return view('hotel.reports.queries.overbookingOnFuture');

        }

        public function regimenPerPeriod() {
            return view('hotel.reports.queries.regimenPerPeriod');
        }
        public function regimenPerPeriodData($start, $end) {

            $start = date("Y-m-d", strtotime($start));
            $end = date("Y-m-d", strtotime($end));

            $regimens = Regimen::all();
            $reservations = Reservation::all();
            $reservesByPeriod = [];
            $regimensData = [];

            foreach ($reservations as $reserve) {
                if ( $end >= $reserve->date_entry && $start <= $reserve->date_entry) {
                    array_push($reservesByPeriod, $reserve);
                }
            }

            $reservesByPeriod = collect($reservesByPeriod);

            foreach ($regimens as $regimen) {

                $adultQuantity = $reservesByPeriod->where('regimen_id', $regimen->id)->sum('number_adults');
                $childQuantity = $reservesByPeriod->where('regimen_id', $regimen->id)->sum('number_children');

                $data = [
                    "regimen_name" => $regimen->regimen_name,
                    "adults" => $adultQuantity,
                    "children" => $childQuantity,
                ];

                array_push($regimensData, $data);
            }
            if(request()->ajax()){
                return datatables()->of($regimensData)
                    ->make(true);
            }
        }

        public function onDestinyPerPeriod(Request $request) {
          //* Table Data
            if(request()->ajax()){
              //* Build Filters
                // Config Data
                $tz = config('app.timezone');

                // Control Data
                $from = $request->filled('from')
                    ? Carbon::createFromFormat('Y-m-d', $request->from, $tz)->startOfDay()
                    : now($tz)->startOfMonth()->startOfDay();

                $to = $request->filled('to')
                    ? Carbon::createFromFormat('Y-m-d', $request->to, $tz)->endOfDay()
                    : now($tz)->endOfMonth()->endOfDay();

              //* Load Data
                $query = Reservation::where('lodging_contract', 'Pago en Destino')
                    ->whereBetween('date_entry', [$from, $to])
                    ->where('status_id','!=',7);

              //* Compute Totals
                $total_price = (clone $query)->sum('total_price');

                $lodging_paid = (clone $query)->get()->sum(function($r){
                    return $r->lodgingPayments->where('status','Activo')->sum('payment_price');
                });

                $lodging_remaining = $total_price - $lodging_paid;
                    if($lodging_remaining < 0) $lodging_remaining = 0;

              //* Build Table
                return datatables()->of($query->latest())
                    ->addColumn('source',function($sources){
                        return $sources->source->source_name;
                    })
                    ->filterColumn('source', function($query, $keyword) {
                        $query->whereHas('source', function($q) use ($keyword) {
                            $q->where('source_name', 'LIKE', "%{$keyword}%");
                        });
                    })
                    ->addColumn('regimen',function($regimens){
                        return $regimens->regimen->regimen_name;
                    })
                    ->addColumn('total_price',function($reservation){
                        return FormatCurrencyHelper::formatCurrency( $reservation->total_price );
                    })
                    ->addColumn('lodging_payments', function ($lodgingP) {
                        $lodging = $lodgingP->lodgingPayments->where('status', 'Activo')->sum('payment_price');
                        return FormatCurrencyHelper::formatCurrency( number_format($lodging, 2, '.', '') );
                    })
                    ->addColumn('payment_left', function ($reservation) {
                        $settled_payments = $reservation->lodgingPayments->where('status', 'Activo')->sum('payment_price');
                        $payment_left = ($reservation->total_price) - $settled_payments;
                            if($payment_left < 0) $payment_left = 0;

                        return FormatCurrencyHelper::formatCurrency( number_format($payment_left, 2, '.', '') );
                    })
                    ->with([
                        'total_price_raw' => $total_price,
                        'lodging_paid_raw' => $lodging_paid,
                        'lodging_remaining_raw' => $lodging_remaining,
                    ])
                    ->make(true);
            }

          //* Return only view
            return view('hotel.reports.queries.onDestinyPerPeriod');
        }

        public function creditPerPeriod(Request $request) {
          //* Table Data
            if(request()->ajax()){
              //* Build Filters
                // Config Data
                $tz = config('app.timezone');

                // Control Data
                $from = $request->filled('from')
                    ? Carbon::createFromFormat('Y-m-d', $request->from, $tz)->startOfDay()
                    : now($tz)->startOfMonth()->startOfDay();

                $to = $request->filled('to')
                    ? Carbon::createFromFormat('Y-m-d', $request->to, $tz)->endOfDay()
                    : now($tz)->endOfMonth()->endOfDay();

              //* Load Data
                $query = Reservation::where('lodging_contract', 'Credito')
                    ->whereBetween('date_entry', [$from, $to])
                    ->where('status_id','!=',7);

              //* Compute Totals
                $total_price = (clone $query)->sum('total_price');

                $lodging_paid = (clone $query)->get()->sum(function($r){
                    return $r->lodgingPayments->where('status','Activo')->sum('payment_price');
                });

                $lodging_remaining = $total_price - $lodging_paid;
                    if($lodging_remaining < 0) $lodging_remaining = 0;

              //* Build Table
                return datatables()->of($query->latest())
                    ->addColumn('source',function($sources){
                        return $sources->source->source_name;
                    })
                    ->filterColumn('source', function($query, $keyword) {
                        $query->whereHas('source', function($q) use ($keyword) {
                            $q->where('source_name', 'LIKE', "%{$keyword}%");
                        });
                    })
                    ->addColumn('regimen',function($regimens){
                        return $regimens->regimen->regimen_name;
                    })
                    ->addColumn('total_price',function($reservation){
                        return FormatCurrencyHelper::formatCurrency( $reservation->total_price );
                    })
                    ->addColumn('lodging_payments', function ($lodgingP) {
                        $lodging = $lodgingP->lodgingPayments->where('status', 'Activo')->sum('payment_price');
                        return FormatCurrencyHelper::formatCurrency( number_format($lodging, 2, '.', '') );
                    })
                    ->addColumn('payment_left', function ($reservation) {
                        $settled_payments = $reservation->lodgingPayments->where('status', 'Activo')->sum('payment_price');
                        $payment_left = ($reservation->total_price) - $settled_payments;
                            if($payment_left < 0) $payment_left = 0;

                        return FormatCurrencyHelper::formatCurrency( number_format($payment_left, 2, '.', '') );
                    })
                    ->with([
                        'total_price_raw' => $total_price,
                        'lodging_paid_raw' => $lodging_paid,
                        'lodging_remaining_raw' => $lodging_remaining,
                    ])
                    ->make(true);
            }

          //* Return only view
            return view('hotel.reports.queries.creditPerPeriod');
        }

        public function courtesyPerPeriod(Request $request) {
          //* Table Data
            if(request()->ajax()){
              //* Build Filters
                // Config Data
                $tz = config('app.timezone');

                // Control Data
                $from = $request->filled('from')
                    ? Carbon::createFromFormat('Y-m-d', $request->from, $tz)->startOfDay()
                    : now($tz)->startOfMonth()->startOfDay();

                $to = $request->filled('to')
                    ? Carbon::createFromFormat('Y-m-d', $request->to, $tz)->endOfDay()
                    : now($tz)->endOfMonth()->endOfDay();

              //* Load Data
                $query = Reservation::where('lodging_contract', 'Cortesia')
                    ->whereBetween('date_entry', [$from, $to])
                    ->where('status_id','!=',7);

              //* Compute Totals
                $total_price = (clone $query)->sum('total_price');

                $lodging_paid = (clone $query)->get()->sum(function($r){
                    return $r->lodgingPayments->where('status','Activo')->sum('payment_price');
                });

                $lodging_remaining = $total_price - $lodging_paid;
                    if($lodging_remaining < 0) $lodging_remaining = 0;

              //* Build Table
                return datatables()->of($query->latest())
                    ->addColumn('source',function($sources){
                        return $sources->source->source_name;
                    })
                    ->filterColumn('source', function($query, $keyword) {
                        $query->whereHas('source', function($q) use ($keyword) {
                            $q->where('source_name', 'LIKE', "%{$keyword}%");
                        });
                    })
                    ->addColumn('regimen',function($regimens){
                        return $regimens->regimen->regimen_name;
                    })
                    ->addColumn('total_price',function($reservation){
                        return FormatCurrencyHelper::formatCurrency( $reservation->total_price );
                    })
                    ->addColumn('lodging_payments', function ($lodgingP) {
                        $lodging = $lodgingP->lodgingPayments->where('status', 'Activo')->sum('payment_price');
                        return FormatCurrencyHelper::formatCurrency( number_format($lodging, 2, '.', '') );
                    })
                    ->addColumn('payment_left', function ($reservation) {
                        $settled_payments = $reservation->lodgingPayments->where('status', 'Activo')->sum('payment_price');
                        $payment_left = ($reservation->total_price) - $settled_payments;
                            if($payment_left < 0) $payment_left = 0;

                        return FormatCurrencyHelper::formatCurrency( number_format($payment_left, 2, '.', '') );
                    })
                    ->with([
                        'total_price_raw' => $total_price,
                        'lodging_paid_raw' => $lodging_paid,
                        'lodging_remaining_raw' => $lodging_remaining,
                    ])
                    ->make(true);
            }

          //* Return only view
            return view('hotel.reports.queries.courtesyPerPeriod');
        }

      // Clients
        public function clientsPerPeriod(Request $request) {
          //* Table Data
            if(request()->ajax()){
              //* Build Filters
                // Config Data
                $tz = config('app.timezone');
                $express_reservations = SystemFeaturesHelper::getFeatureStatus("express_reservations");
                $control_hours = SystemFeaturesHelper::getFeatureStatus("control_hours");

                // Control Data
                $from = $request->filled('from')
                    ? Carbon::createFromFormat('Y-m-d', $request->from, $tz)->startOfDay()
                    : now($tz)->startOfMonth()->startOfDay();

                $to = $request->filled('to')
                    ? Carbon::createFromFormat('Y-m-d', $request->to, $tz)->endOfDay()
                    : now($tz)->endOfMonth()->endOfDay();

              //* Load Data
                $query = Reservation::where('guest_status_id', 6)->where('status_id','!=',7)
                    ->whereBetween('date_entry', [$from, $to]);

              //* Build Basic Table
                return datatables()->of($query->latest())
                    ->addColumn('guest', function($data){
                      // Control Data
                        // Records
                        $guest_data = $data->client;

                        // Lang
                        $none_text = __('neutral_words')['none_2'];

                        // Renders
                        $contact_data = [];

                      // Seed Guest Data
                        if ($guest_data->client_phone != $none_text) // Phone Number
                            $contact_data[] = "&#x260E; {$guest_data->client_phone}";

                        if ($guest_data->client_email != $none_text) { // Email
                            $contact_data[] = "&#9993; {$guest_data->client_email}";
                        } else { // Other Options
                            if (SystemFeaturesHelper::getFeatureStatus("identity_documents") && $guest_data->identity_document_number != "--- --- ---") // Identity Document
                                $contact_data[] = "&#x1F5CE; {$guest_data->identity_document_number}";
                        }

                        $contact_data = implode(' - ', $contact_data);

                      // Build Row Data
                        return "
                            {$data->client_reference}<br>
                            <small>{$contact_data}</small>
                        ";
                    })
                    ->addColumn('contract', function($data){
                        return [
                            'Pago en Destino' => 'PD',
                            'Cortesia' => 'CT',
                            'Credito' => 'CD',
                        ][$data->lodging_contract] ?? null;
                    })
                    ->addColumn('source', function($data){
                      // Control Data
                        $source_data = $data->source;

                      // Build Return
                        $resource_content = $source_data->source_name;

                        if ($data->source_id === 3)
                            $resource_content .= "<br><i>({$data->agency->agency_name})</i>";

                        return $resource_content;
                    })
                    ->addColumn('stay', function($data) use($express_reservations, $control_hours){
                      // Dynamic Lang
                        $reservation_fields = __('entity_fields')['reservation'];

                      // Build Response
                        // Period
                        $check_in = ($control_hours || $express_reservations)
                            ? "{$data->date_entry} {$data->hour_entry}"
                            : $data->date_entry;
                        $check_out = ($control_hours || $express_reservations)
                            ? "{$data->date_departure} {$data->hour_departure}"
                            : $data->date_departure;

                        // Stay Summary
                        $nights = trans_choice($reservation_fields['dynamic_number_nights'], $data->number_nights);
                        $duration = "{$data->number_nights} {$nights}";

                            if ($express_reservations) {
                                $hours = trans_choice($reservation_fields['dynamic_number_hours'], $data->number_hours);
                                $duration .= ", {$data->number_hours} {$hours}";
                            }

                        return "{$check_in}, <br> {$check_out}, <br> ({$duration})";
                    })
                    ->addColumn('room_data', function($data){
                        return "
                            {$data->Room->room_name}<br>
                            <small>({$data->roomType->tr_name})</small>
                        ";
                    })
                    ->addColumn('pax_compute', function($data){
                        $pax_compute = "{$data->number_adults} ".__('entity_fields')['reservation']['number_adults'];

                        if ($data->number_children > 0)
                            $pax_compute .= "<br>, {$data->number_children} ".__('entity_fields')['reservation']['number_children'];

                        return $pax_compute;
                    })
                    ->rawColumns(['guest', 'source', 'stay', 'room_data', 'pax_compute'])
                    ->make(true);
            }

          //* Return only view
            return view('hotel.reports.queries.clientsPerPeriod');
        }

}
