<?php

namespace App\Http\Controllers\Hotel;

//? Models
use App\Models\Environment\PaymentMethod;

use App\Models\Hotel\ConsumptionPayments;
use App\Models\Hotel\ReservationHistory;
use App\Models\Hotel\LodgingPayments;
use App\Models\Hotel\ExtraPayments;
use App\Models\Hotel\Reservation;
use App\Models\Hotel\RoomType;
use App\Models\Hotel\Regimen;
use App\Models\Hotel\Source;
use App\Models\Hotel\Room;

use App\Models\User;

//? Controllers
use App\Http\Controllers\Hotel\InformesHotelController;

//? Support
use Illuminate\Support\Facades\Response;
use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Carbon\CarbonPeriod;
use Carbon\Carbon;

use App\Helpers\Environment\SystemFeaturesHelper;
use App\Helpers\Support\CSVReportHelper;

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


    //* Reportes Data
      // Incomes
        public function checkinBalances(Request $request) {

            //* Get Data for CSV
                $timezone = config('app.timezone');
                $reservations = Reservation::where('has_checkIn', 'Si')->where('has_checkOut', 'No')->get();

            //* Tax Breakdown
                $tax_breakdown = json_decode($request->tax_breakdown, true);
                $has_breakdown = $tax_breakdown && isset($tax_breakdown['is_applied']) && $tax_breakdown['is_applied'];

            //* Define CSV Structure
                //? Payment Summary Table

                    // Basic Data
                    $lodgingP_total = 0.0;
                    $consumptionP_total = 0.0;
                    $extraP_total = 0.0;
                    $report_total = 0.0;

                    // Structure
                    $csvData = [[
                        __('reports')['hotel']['common']['incomes'] ."(".__('reports')['hotel']['common']['lodging'].")",
                        __('reports')['hotel']['common']['incomes'] ."(".__('reports')['hotel']['common']['consumption'].")",
                        __('reports')['hotel']['common']['incomes'] ."(".__('reports')['hotel']['common']['extras'].")",
                        __('reports')['hotel']['common']['total_incomes'],
                    ]];

                    // Get Summary
                        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');
                        }

                        $report_total = $lodgingP_total + $consumptionP_total + $extraP_total;

                    // Compute Tax Breakdown
                    $grouped_breakdown = null;
                    if ($has_breakdown) {
                        $grouped_breakdown =
                            CSVReportHelper::calculateGroupedReverseBreakdown(
                                [ 'lodging' => $lodgingP_total, 'consumption' => $consumptionP_total, 'extras' => $extraP_total ],
                                $tax_breakdown['taxes']
                            );
                    }

                    // Add Data to CSV
                    $csvData[] = [
                        number_format($lodgingP_total, 2, '.', ','),
                        number_format($consumptionP_total, 2, '.', ','),
                        number_format($extraP_total, 2, '.', ','),
                        number_format($report_total, 2, '.', ',')
                    ];

                    // Append Breakdown section
                    if ($grouped_breakdown) {
                        $this->appendHotelTaxBreakdown(
                            $csvData, $grouped_breakdown
                        );
                    }

                    // Add Space Between Summary and Main Table
                    array_push($csvData, []);

                //? Main Table

                    // Structure
                        $csvData[] = [
                            'Folio',
                            __('entity_fields')['reservation']['client'],
                            __('entity_fields')['reservation']['room'],
                            __('reports')['hotel']['common']['lodging'] ."(".__('entity_fields')['reservation']['contract'].")",
                            __('reports')['hotel']['common']['lodging'] ."(".__('reports')['hotel']['common']['rate'].")",
                            __('reports')['hotel']['common']['lodging'] ."(".__('reports')['hotel']['common']['paid'].")",
                            __('reports')['hotel']['common']['consumption'] ."(".__('entity_fields')['reservation']['contract'].")",
                            __('reports')['hotel']['common']['consumption'] ."(".__('reports')['hotel']['common']['rate'].")",
                            __('reports')['hotel']['common']['consumption'] ."(".__('reports')['hotel']['common']['paid'].")",
                            __('reports')['hotel']['common']['extras'] ."(".__('entity_fields')['reservation']['contract'].")",
                            __('reports')['hotel']['common']['extras'] ."(".__('reports')['hotel']['common']['rate'].")",
                            __('reports')['hotel']['common']['extras'] ."(".__('reports')['hotel']['common']['paid'].")",
                        ];

                    // Add Data to CSV
                        foreach ($reservations as $reserve) {
                            $row = [
                                // Main Table
                                    $reserve->id,
                                    $reserve->client_reference,
                                    $reserve->Room->room_name,

                                    $reserve->lodging_contract,
                                    $reserve->total_price,
                                    number_format($reserve->lodgingPayments->where('status', 'Activo')->sum('payment_price'), 2, '.', ''),

                                    $reserve->consumption_contract,
                                    number_format($reserve->consumptionCharges->where('status', 'Activo')->sum('consumption_price'), 2, '.', ''),
                                    number_format($reserve->consumptionPayments->where('status', 'Activo')->sum('payment_price'), 2, '.', ''),

                                    $reserve->extras_contract,
                                    number_format($reserve->extraCharges->where('status', 'Activo')->sum('extras_price'), 2, '.', ''),
                                    number_format($reserve->extraPayments->where('status', 'Activo')->sum('payment_price'), 2, '.', ''),
                            ];
                            $csvData[] = $row;
                        }


            //* Define CSV Characteristics
                $filename = 'saldos_del_dia_' . Carbon::now($timezone)->format('Y-m-d') . '.csv';

                $handle = fopen($filename, 'w+');
                fputs($handle, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

                foreach ($csvData as $row) {
                    fputcsv($handle, $row);
                }

                fclose($handle);

                $headers = [
                    'Content-Type' => 'text/csv',
                ];

                return Response::download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }

        public function checkoutBalances(Request $request) {
            //* Format Dates
                $startDate = Carbon::parse($request->start)->format('Y-m-d');
                $endDate = Carbon::parse($request->end)->format('Y-m-d');

            //* Get Data for CSV
                $reservations = Reservation::where('has_checkIn', 'Si')
                                            ->where('has_checkOut', 'Si')
                                            ->whereBetween('date_departure', [$startDate, $endDate])
                                            ->get();

            //* Tax Breakdown
                $tax_breakdown = json_decode($request->tax_breakdown, true);
                $has_breakdown = $tax_breakdown && isset($tax_breakdown['is_applied']) && $tax_breakdown['is_applied'];

            //* Define CSV Structure

                $csvData = [[
                    'Folio',
                    __('entity_fields')['reservation']['client'],
                    'CheckOut',
                    __('reports')['hotel']['common']['attended_by'],

                    __('reports')['hotel']['common']['lodging'] ."(".__('entity_fields')['reservation']['contract'].")",
                    __('reports')['hotel']['common']['lodging'] ."(".__('reports')['hotel']['common']['balance'].")",
                    __('reports')['hotel']['common']['lodging'] ."(".__('reports')['hotel']['common']['paid'].")",

                    __('reports')['hotel']['common']['consumption'] ."(".__('entity_fields')['reservation']['contract'].")",
                    __('reports')['hotel']['common']['consumption'] ."(".__('reports')['hotel']['common']['balance'].")",
                    __('reports')['hotel']['common']['consumption'] ."(".__('reports')['hotel']['common']['paid'].")",

                    __('reports')['hotel']['common']['extras'] ."(".__('entity_fields')['reservation']['contract'].")",
                    __('reports')['hotel']['common']['extras'] ."(".__('reports')['hotel']['common']['balance'].")",
                    __('reports')['hotel']['common']['extras'] ."(".__('reports')['hotel']['common']['paid'].")",
                ]];

            //* Add Data to CSV
                $lodging_total = 0.0;
                $consumption_total = 0.0;
                $extras_total = 0.0;

                foreach ($reservations as $reserve) {
                  // Compute incomes per origin
                    $lodging_payments = $reserve->lodgingPayments->where('status', 'Activo')->sum('payment_price');
                    $consumption_payments = $reserve->consumptionPayments->where('status', 'Activo')->sum('payment_price');
                    $extra_payments = $reserve->extraPayments->where('status', 'Activo')->sum('payment_price');

                  // Increment geneal totals
                    $lodging_total += $lodging_payments;
                    $consumption_total += $consumption_payments;
                    $extras_total += $extra_payments;

                  // Append Row
                    $csvData[] = [
                        $reserve->id,
                        $reserve->client_reference,
                        $reserve->date_departure,
                        $reserve->user->name,

                        $reserve->lodging_contract,
                        $reserve->total_price,
                        number_format($lodging_payments, 2, '.', ''),

                        $reserve->consumption_contract,
                        number_format($reserve->consumptionCharges->where('status', 'Activo')->sum('consumption_price'), 2, '.', ''),
                        number_format($consumption_payments, 2, '.', ''),

                        $reserve->extras_contract,
                        number_format($reserve->extraCharges->where('status', 'Activo')->sum('extras_price'), 2, '.', ''),
                        number_format($extra_payments, 2, '.', ''),
                    ];
                }

                // Compute Tax Breakdown
                $grouped_breakdown = null;
                if ($has_breakdown) {
                    $grouped_breakdown =
                        CSVReportHelper::calculateGroupedReverseBreakdown(
                            [ 'lodging' => $lodging_total, 'consumption' => $consumption_total, 'extras' => $extras_total ],
                            $tax_breakdown['taxes']
                        );
                }

                // Append Breakdown section
                if ($grouped_breakdown) {
                    $this->appendHotelTaxBreakdown(
                        $csvData, $grouped_breakdown
                    );
                }

            //* Define CSV Characteristics
                $filename = 'saldos_por_periodo_' . $startDate . "_" . $endDate . '.csv';

                $handle = fopen($filename, 'w+');
                fputs($handle, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

                foreach ($csvData as $row) {
                    fputcsv($handle, $row);
                }

                fclose($handle);

                $headers = [
                    'Content-Type' => 'text/csv',
                ];

            return Response::download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }

        public function incomePerMonth(Request $request) {
            //* Extract Data
                $year = $request->integer('year');

            //* Tax Breakdown
                $tax_breakdown = json_decode($request->tax_breakdown, true);
                $has_breakdown = $tax_breakdown && isset($tax_breakdown['is_applied']) && $tax_breakdown['is_applied'];

            //* Define CSV Structure
                $csvData = [[
                    __('neutral_words')['month'],
                    "Total (".__('reports')['hotel']['common']['lodging'].")",
                    "Total (".__('reports')['hotel']['common']['consumption'].")",
                    "Total (".__('reports')['hotel']['common']['extras'].")",
                ]];

            //* Add Data to CSV
                $lodging_total = 0.0;
                $consumption_total = 0.0;
                $extras_total = 0.0;

                for ($j = 1; $j <= 12; $j++) {
                  // Parse Date
                    $date = new \DateTime("$year-$j-10");
                    $month = $date->format('F Y');

                    $month = '="' . $month . '"'; // Forzar que se trate como texto en el CSV añadiendo un prefijo

                  // Compute incomes per origin
                    $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');

                  // Increment geneal totals
                    $lodging_total += $lodgingByMonth;
                    $consumption_total += $consumptionByMonth;
                    $extras_total += $extraByMonth;

                  // Append Row
                    $csvData[] = [
                        $month,
                        $lodgingByMonth,
                        $consumptionByMonth,
                        $extraByMonth,
                    ];
                }

                // Compute Tax Breakdown
                $grouped_breakdown = null;
                if ($has_breakdown) {
                    $grouped_breakdown =
                        CSVReportHelper::calculateGroupedReverseBreakdown(
                            [ 'lodging' => $lodging_total, 'consumption' => $consumption_total, 'extras' => $extras_total ],
                            $tax_breakdown['taxes']
                        );
                }

                // Append Breakdown section
                if ($grouped_breakdown) {
                    $this->appendHotelTaxBreakdown(
                        $csvData, $grouped_breakdown
                    );
                }

            //* Define CSV Characteristics
                $filename = 'ingresos_por_mes_' . $year . '.csv';

                $handle = fopen($filename, 'w+');
                fputs($handle, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

                foreach ($csvData as $row) {
                    fputcsv($handle, $row);
                }

                fclose($handle);

                $headers = [
                    'Content-Type' => 'text/csv',
                ];

            return Response::download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }

        public function incomePerPeriod(Request $request) {
            //* Request Controls
                $start = $request->input("start", now()->format('Y-m-01'));
                $end = $request->input("end",  now()->format('Y-m-t'));

            //* Format Dates
                $startDate = Carbon::parse($start)->startOf('day');
                $endDate = Carbon::parse($end)->endOf('day');

            //* Get Data for CSV

                // Obtenemos todos los pagos realizados dentro del periodo dado
                    $lodging_payments = LodgingPayments::where('status', 'Activo')->whereBetween('payment_date', [$startDate, $endDate])->get();
                    $extra_payments = ExtraPayments::where('status', 'Activo')->whereBetween('payment_date', [$startDate, $endDate])->get();
                    $consumption_payments = ConsumptionPayments::where('status', 'Activo')->whereBetween('payment_date', [$startDate, $endDate])->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();

            //* Tax Breakdown
                $tax_breakdown = json_decode($request->tax_breakdown, true);
                $has_breakdown = $tax_breakdown && isset($tax_breakdown['is_applied']) && $tax_breakdown['is_applied'];

            //* Define CSV Structure
                $csvData = [[
                    __('entity_fields')['common']['date'],
                    'Folio',
                    __('entity_fields')['reservation']['client'],
                    __('entity_fields')['reservation']['room'],
                    __('reports')['hotel']['common']['income_type'],
                    __('reports')['hotel']['common']['income'],
                    __('entity_fields')['common']['payment_method'],
                    'Total',
                ]];

            //* Add Data to CSV
                $general_total = 0;
                $lodging_total = 0.0;
                $consumption_total = 0.0;
                $extras_total = 0.0;

                foreach ($reservations as $reservation) {

                    // Get Total Incomes for Reservation
                        $total_income = 0;

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

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

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

                        $general_total += $total_income;
                        $total_income = number_format($total_income, 2, '.', ',');

                    // Get Tipos de Ingresos
                        $payment_types = collect()
                            ->merge($lodging_payments->filter(fn($payment) => $payment->reservation_id == $reservation->id)->map(fn() => 'Hospedaje'))
                            ->merge($consumption_payments->filter(fn($payment) => $payment->reservation_id == $reservation->id)->map(fn() => 'Consumo'))
                            ->merge($extra_payments->filter(fn($payment) => $payment->reservation_id == $reservation->id)->map(fn() => 'Extra'));

                        $payment_types = $payment_types->implode(',');
                        $all_payments = $lodging_payments
                            ->merge($consumption_payments)
                            ->merge($extra_payments);

                    // Get Ingresos
                        $payment_incomes = $all_payments
                            ->where('reservation_id', $reservation->id)
                            ->pluck('payment_price')
                            ->map(fn($price) => number_format($price, 2, '.', ','))
                            ->implode(',');

                    // Get Métodos de Pago
                        $payment_methods = $all_payments
                            ->where('reservation_id', $reservation->id)
                            ->pluck('payment_method')
                            ->implode(',');

                    $row = [
                        $reservation->date_entry,
                        $reservation->id,
                        $reservation->client_reference,
                        $reservation->Room->room_name,

                        $payment_types,
                        $payment_incomes,
                        $payment_methods,

                        $total_income,
                    ];

                    $csvData[] = $row;
                }

                // Compute Tax Breakdown
                $grouped_breakdown = null;
                if ($has_breakdown) {
                    $grouped_breakdown =
                        CSVReportHelper::calculateGroupedReverseBreakdown(
                            [ 'lodging' => $lodging_total, 'consumption' => $consumption_total, 'extras' => $extras_total ],
                            $tax_breakdown['taxes']
                        );
                }

                // Append Breakdown section
                if ($grouped_breakdown) {
                    $this->appendHotelTaxBreakdown(
                        $csvData, $grouped_breakdown
                    );
                }

            //* Define CSV Characteristics
                $filename = 'ingresos_por_periodo_' . $startDate->format('Y-m-d') . "_" . $endDate->format('Y-m-d') . '.csv';

                $handle = fopen($filename, 'w+');
                fputs($handle, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

                foreach ($csvData as $row) {
                    fputcsv($handle, $row);
                }

                fclose($handle);

                $headers = [
                    'Content-Type' => 'text/csv',
                ];

            return Response::download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }

        public function incomePerPeriodAndPaymentMethod(Request $request) {
            //* Format Dates
                $startDate = Carbon::parse($request->start)->startOfDay();
                $endDate = Carbon::parse($request->end)->endOfDay();
                $payment_method = $request->payment_method;

            //* Get Data for CSV
                // Obtenemos todos los pagos realizados dentro del periodo dado
                if($payment_method != 'null'){
                    $lodging_payments = LodgingPayments::where('status', 'Activo')->whereBetween('payment_date', [$startDate, $endDate])->where('payment_method', $payment_method)->get();
                    $extra_payments = ExtraPayments::where('status', 'Activo')->whereBetween('payment_date', [$startDate, $endDate])->where('payment_method', $payment_method)->get();
                    $consumption_payments = ConsumptionPayments::where('status', 'Activo')->whereBetween('payment_date', [$startDate, $endDate])->where('payment_method', $payment_method)->get();
                } else {
                    $lodging_payments = LodgingPayments::where('status', 'Activo')->whereBetween('payment_date', [$startDate, $endDate])->get();
                    $extra_payments = ExtraPayments::where('status', 'Activo')->whereBetween('payment_date', [$startDate, $endDate])->get();
                    $consumption_payments = ConsumptionPayments::where('status', 'Activo')->whereBetween('payment_date', [$startDate, $endDate])->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();

            //* Tax Breakdown
                $tax_breakdown = json_decode($request->tax_breakdown, true);
                $has_breakdown = $tax_breakdown && isset($tax_breakdown['is_applied']) && $tax_breakdown['is_applied'];

            //* Define CSV Structure
                $csvData = [[
                    __('entity_fields')['common']['date'],
                    'Folio',
                    __('entity_fields')['reservation']['client'],
                    __('entity_fields')['reservation']['room'],
                    __('reports')['hotel']['common']['lodging'],
                    __('reports')['hotel']['common']['consumption'],
                    __('reports')['hotel']['common']['extras'],
                    'Total',
                ]];

            //* Add Data to CSV
                $lodging_total = 0.0;
                $consumption_total = 0.0;
                $extras_total = 0.0;

                foreach ($reservations as $reservation) {
                  // Compute incomes per origin
                    $lodgingPayments = 0;
                    $consumptionPayments = 0;
                    $extraPayments = 0;

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

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

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

                    $total_income = $lodgingPayments + $consumptionPayments + $extraPayments;

                  // Increment geneal totals
                    $lodging_total += $lodgingPayments;
                    $consumption_total += $consumptionPayments;
                    $extras_total += $extraPayments;

                  // Parse visual render
                    $lodgingPayments = '$ '.number_format($lodgingPayments, 2, '.', ',');
                    $consumptionPayments = '$ '.number_format($consumptionPayments, 2, '.', ',');
                    $extraPayments = '$ '.number_format($extraPayments, 2, '.', ',');
                    $total_income = '$ '.number_format($total_income, 2, '.', ',');

                  // Append Row
                    $csvData[] = [
                        $reservation->date_entry,
                        $reservation->id,
                        $reservation->client_reference,
                        $reservation->Room->room_name,
                        $lodgingPayments,
                        $consumptionPayments,
                        $extraPayments,
                        $total_income,
                    ];
                }

                // Compute Tax Breakdown
                $grouped_breakdown = null;
                if ($has_breakdown) {
                    $grouped_breakdown =
                        CSVReportHelper::calculateGroupedReverseBreakdown(
                            [ 'lodging' => $lodging_total, 'consumption' => $consumption_total, 'extras' => $extras_total ],
                            $tax_breakdown['taxes']
                        );
                }

                // Append Breakdown section
                if ($grouped_breakdown) {
                    $this->appendHotelTaxBreakdown(
                        $csvData, $grouped_breakdown
                    );
                }


            //* Define CSV Characteristics
                if($payment_method != 'null'){
                    $filename = 'ingresos_por_periodo_' . $startDate->format('Y-m-d') . "_" . $endDate->format('Y-m-d') . '_metodo_pago_' . $payment_method . '.csv';
                } else{
                    $filename = 'ingresos_por_periodo_' . $startDate->format('Y-m-d') . "_" . $endDate->format('Y-m-d') . '_metodo_pago_Todos.csv';
                }

                $handle = fopen($filename, 'w+');
                fputs($handle, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

                foreach ($csvData as $row) {
                    fputcsv($handle, $row);
                }

                fclose($handle);

                $headers = [
                    'Content-Type' => 'text/csv',
                ];

            return Response::download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }

        public function incomePerPeriodAndRoomType(Request $request) {
            //* Request Controls
                $start = $request->input("start", now()->startOfWeek(0)->format('Y-m-d'));
                $end = $request->input("end",  now()->endOfWeek(6)->format('Y-m-d'));

            //* Format Dates
                $start_date = Carbon::parse($start)->startOf('day');
                $end_date = Carbon::parse($end)->endOf('day');

            //* Get Data for CSV
              // Control Data
                $room_types = RoomType::whereNotIn('id', [1, 2])->get(); // Ajusta si necesario
                $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();

              // 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
                );

            //* Tax Breakdown
                $tax_breakdown = json_decode($request->tax_breakdown, true);
                $has_breakdown = $tax_breakdown && isset($tax_breakdown['is_applied']) && $tax_breakdown['is_applied'];

                $lodging_total = $lodging_payments->sum('payment_price');
                $consumption_total = $extra_payments->sum('payment_price');
                $extras_total = $consumption_payments->sum('payment_price');

            //* Define CSV Structure

                $csvHeader = array_merge(
                    [__('entity_fields')['common']['date']],
                    $room_types->pluck('tr_name')->toArray(),
                    ['Total'],
                    $payment_methods->toArray(),
                    [__('reports')['hotel']['common']['extras'], __('reports')['hotel']['common']['total_incomes']]
                );
                $csvData = [$csvHeader];
                $totalsRow = array_fill(0, count($csvHeader), 0);

            //* Add Data to CSV
                foreach ($considered_dates as $date) {
                    $parsedDate = Carbon::parse($date);
                    $row = [$parsedDate->format('Y-m-d')];
                    $occupiedTotal = 0;

                    // 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();

                        $row[] = $occupied;
                        $occupiedTotal += $occupied;
                    }

                    $row[] = $occupiedTotal;

                    // Obtenemos los ingresos por modulo y día
                    $incomeTotal = 0;
                    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;

                        $paymentTotal = $lodgingTotal + $consumptionTotal;
                        $extraOnly = $extraTotal;

                        $incomeTotal += $paymentTotal + $extraOnly;

                        $row[] = number_format($paymentTotal, 2, '.', '');
                    }

                    // Damos formato a los totales
                    $extraSum = $extra_payments
                        ->filter(fn($p) => Carbon::parse($p->payment_date)->format('Y-m-d') == $date )
                        ->sum('payment_price');

                    $row[] = number_format($extraSum, 2, '.', '');
                    $row[] = number_format($incomeTotal, 2, '.', '');

                    // Agregamos fila al CSV
                    $csvData[] = $row;

                    // Sumamos totales
                    foreach ($row as $i => $val) {
                        if (is_numeric($val)) $totalsRow[$i] += $val;
                    }
                }

                // Formato de Totales
                $totalsRow[0] = 'TOTALES';
                $totalsRow = array_map(fn($v) => is_numeric($v) ? number_format($v, 2, '.', '') : $v, $totalsRow);
                $csvData[] = $totalsRow;

                // Compute Tax Breakdown
                $grouped_breakdown = null;
                if ($has_breakdown) {
                    $grouped_breakdown =
                        CSVReportHelper::calculateGroupedReverseBreakdown(
                            [ 'lodging' => $lodging_total, 'consumption' => $consumption_total, 'extras' => $extras_total ],
                            $tax_breakdown['taxes']
                        );
                }

                // Append Breakdown section
                if ($grouped_breakdown) {
                    $this->appendHotelTaxBreakdown(
                        $csvData, $grouped_breakdown
                    );
                }

            //* Define CSV Characteristics
                $filename = 'ingresos_por_periodo_' . $start_date->format('Y-m-d') . "_" . $end_date->format('Y-m-d') . '_y_tipo_habitaciones.csv';

                $handle = fopen($filename, 'w+');
                fputs($handle, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

                foreach ($csvData as $row) {
                    fputcsv($handle, $row);
                }

                fclose($handle);

                $headers = [
                    'Content-Type' => 'text/csv',
                ];

            return Response::download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }

      // Reservation
        public function reservationsPerPeriod($start, $end) {

            //* Format Dates
                $startDate = date("Y-m-d", strtotime($start));
                $endDate = date("Y-m-d", strtotime($end));

            //* Get Reservations for CSV
                $reservations = Reservation::where('has_checkIn', 'Si')
                                    ->whereBetween('date_entry', [$startDate, $endDate])
                                    ->get();

            //* Define CSV Structure
                $csvData = [];
                $csvData[] = [
                    'Folio',
                    __('entity_fields')['reservation']['client'],
                    'Check-in',
                    'Check-out',
                    __('entity_fields')['reservation']['room'],
                    __('entity_fields')['reservation']['number_nights'],
                    __('entity_fields')['common']['status'],
                    __('entity_fields')['common']['receptionist']
                ];

                foreach ($reservations as $reservation) {
                    $csvData[] = [
                        $reservation->id,
                        $reservation->client_reference,
                        $reservation->date_entry,
                        $reservation->date_departure,
                        $reservation->room->room_name,
                        $reservation->number_nights,
                        $reservation->status->status_name,
                        $reservation->user->name,
                    ];
                }

            //* Define CSV Characteristics
                $filename = "reservas_por_periodo_" . $startDate . "_" . $endDate . ".csv";

                $handle = fopen($filename, 'w+');
                fputs($handle, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

                foreach ($csvData as $row) {
                    fputcsv($handle, $row);
                }

                fclose($handle);

                $headers = [
                    'Content-Type' => 'text/csv',
                ];

            return Response::download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }

        public function deferredBookings($start, $end) {

            //* Format Dates
                $startDate = date("Y-m-d", strtotime($start));
                $endDate = date("Y-m-d", strtotime($end));

            //* Get Data for CSV
                $reservations = Reservation::where('guest_status_id', 5)
                                            ->whereBetween('date_entry', [$startDate, $endDate])
                                            ->get();

            //* Define CSV Structure

                $csvData = [[
                    'Folio',
                    __('entity_fields')['reservation']['client'],
                    __('entity_fields')['reservation']['source'],
                    __('entity_fields')['reservation']['date_entry'],
                    __('entity_fields')['reservation']['date_departure'],
                    __('entity_fields')['reservation']['room'],
                    __('entity_fields')['reservation']['number_nights'],
                    __('entity_fields')['reservation']['regimen'],
                    __('reports')['hotel']['common']['attended_by'],
                ]];

            //* Add Data to CSV
                foreach ($reservations as $reserve) {
                    $row = [
                        $reserve->id,
                        $reserve->client_reference,
                        $reserve->source->source_name,
                        $reserve->date_entry,
                        $reserve->date_departure,
                        $reserve->room->room_name,
                        $reserve->number_nights,
                        $reserve->regimen->regimen_name,
                        $reserve->user->name,
                    ];
                    $csvData[] = $row;
                }


            //* Define CSV Characteristics
                $filename = 'reservas_postergadas_' . $startDate . "_" . $endDate . '.csv';

                $handle = fopen($filename, 'w+');
                fputs($handle, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

                foreach ($csvData as $row) {
                    fputcsv($handle, $row);
                }

                fclose($handle);

                $headers = [
                    'Content-Type' => 'text/csv',
                ];

            return Response::download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }

        public function commonSources() {

            //* Get Data for CSV
                $sources = Source::all();

            //* Define CSV Structure
                $csvData = [[
                    __('entity_fields')['reservation']['source'],
                    __('reports')['hotel']['common']['quantity'],
                    __('reports')['hotel']['common']['percentage'],
                ]];

            //* Add Data to CSV
                foreach ($sources as $source) {

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

                    // Add as Row
                    $row = [
                        $source->source_name,
                        $sourceQuantity,
                        $sourcePercentage.'%'
                    ];

                    $csvData[] = $row;
                }


            //* Define CSV Characteristics
                $filename = 'fuentes_reserva_comunes.csv';

                $handle = fopen($filename, 'w+');
                fputs($handle, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

                foreach ($csvData as $row) {
                    fputcsv($handle, $row);
                }

                fclose($handle);

                $headers = [
                    'Content-Type' => 'text/csv',
                ];

            return Response::download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }

        public function bookingHistory($start, $end) {

            //* Format Dates
            $startDate = date("Y-m-d 00:00:00", strtotime($start));
            $endDate = date("Y-m-d 23:59:59", strtotime($end));

            //* Get Data for CSV
            $histories = ReservationHistory::whereBetween('created_at', [$startDate, $endDate])
                                            ->orderBy('created_at', 'desc')
                                            ->get();

            //* Define CSV Structure
                $csvData = [[
                    __('entity_fields')['common']['date'],
                    __('entity_fields')['reservation_history']['time'],
                    __('entity_fields')['reservation_history']['reservation'],
                    __('entity_fields')['reservation_history']['guest'],
                    __('entity_fields')['reservation_history']['editor'],
                ]];

            //* Add Data to CSV

                foreach ($histories as $history) {
                    $row = [
                        Carbon::createFromFormat('Y-m-d H:i:s', $history->created_at)->format('Y-m-d'),
                        Carbon::parse($history->created_at)->format('H:i:s'),
                        $history->reservation_data['id'],
                        $history->reservation_data['client'],
                        $history->editor_name,
                    ];

                    $csvData[] = $row;
                }


            //* Define CSV Characteristics
                $filename = 'historial_de_reservaciones_' . date("Y-m-d", strtotime($start)) . "_" . date("Y-m-d", strtotime($end)) . '.csv';

                $handle = fopen($filename, 'w+');
                fputs($handle, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

                foreach ($csvData as $row) {
                    fputcsv($handle, $row);
                }

                fclose($handle);

                $headers = [
                    'Content-Type' => 'text/csv',
                ];

            return Response::download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }

        public function bookingUpdateHistory($folio) {

            //* Get Data for CSV
            $histories = ReservationHistory::where('id_reservation',$folio)->orderBy('id', 'desc')->get();

            //* Define CSV Structure
                $csvData = [[
                    __('entity_fields')['common']['date'],
                    __('entity_fields')['reservation_history']['time'],
                    __('entity_fields')['reservation_history']['reservation'],
                    __('entity_fields')['reservation_history']['guest'],
                    __('entity_fields')['reservation_history']['editor'],
                    __('entity_fields')['reservation_history']['changes'],
                ]];

            //* Add Data to CSV

                foreach ($histories as $history) {

                    switch ($history->reservation_data['action']) {
                        case 'Update':
                            if(count($history->reservation_changes)){
                                $changes_html = ''; // Inicializa la variable fuera del foreach
                                $totalChanges = count($history->reservation_changes);
                                $currentChange = 0;

                                foreach ($history->reservation_changes as $key => $changes) {
                                    $currentChange++; // Incrementa el contador en cada iteración
                                    $key =  (new InformesHotelController)->setNameData($key);
                                    $changes_html .= $key . ': ' . $changes;

                                    // Solo agrega la coma si no es el último elemento
                                    if ($currentChange < $totalChanges) {
                                        $changes_html .= ', ';
                                    }
                                }
                            } else{
                                $changes_html = __('reports')['hotel']['statisticalReports']['reservations']['no_changes'];
                            }
                            break;

                        case 'Create':
                            $changes_html = __('reports')['hotel']['statisticalReports']['reservations']['created'];
                            break;

                        case 'Delete':
                            $changes_html = __('reports')['hotel']['statisticalReports']['reservations']['deleted'];
                            break;
                    }

                    // Add as Row
                    $row = [
                        Carbon::createFromFormat('Y-m-d H:i:s', $history->created_at)->format('Y-m-d'),
                        Carbon::parse($history->created_at)->format('H:i:s'),
                        $history->reservation_data['id'],
                        $history->reservation_data['client'],
                        $history->editor_name,
                        $changes_html,
                    ];

                    $csvData[] = $row;
                }


            //* Define CSV Characteristics
                $filename = 'historial_de_cambios_reserva_'. $folio .'.csv';

                $handle = fopen($filename, 'w+');
                fputs($handle, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

                foreach ($csvData as $row) {
                    fputcsv($handle, $row);
                }

                fclose($handle);

                $headers = [
                    'Content-Type' => 'text/csv',
                ];

            return Response::download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }

        public function checkinPerUserAndPeriod(Request $request) {
          //* Get Records
            $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();
            $reservations = $query->get();

          //* Parse Data
            $month_name = Carbon::createFromDate(null, $request->month, 1)->format('F');
            if ($request->filled('receptionist_id')) {
                $receptionist_name = User::where('id', $request->receptionist_id)
                    ->selectRaw("CONCAT(name, ' ', last_name) as full_name")
                    ->value('full_name');
            } else {
                $receptionist_name = __('neutral_words')['all'];
            }

          //* Define CSV Structure
            // Check-in Counting
            $csvData = [[
                __('reports')['hotel']['statisticalReports']['reservations']['check_ins_completed'],
                $query_count
            ]];
            array_push($csvData, []);

            // Basic Header
            $csvData[] = [
                "Folio",
                __('neutral_words')['month'],
                __('entity_fields')['reservation']['check_in'],
                __('entity_fields')['reservation']['check_out'],
                __('entity_fields')['common']['room_type'],
                __('entity_fields')['reservation']['room'],
                __('entity_fields')['common']['receptionist'],
            ];

          //* Add Data to CSV
            foreach ($reservations as $reservation) {
              // Parse Data
                $parsed_month = Carbon::parse($reservation->date_entry)->translatedFormat('F');
                $parsed_month = ucfirst($parsed_month);

                $checkin_user = $reservation->checkinUser;

              // Build Row Data
                $row = [
                    $reservation->id,
                    $parsed_month,
                    $reservation->date_entry,
                    $reservation->date_departure,
                    $reservation->roomType->tr_name,
                    $reservation->room->room_name,
                    "{$checkin_user->name} {$checkin_user->last_name}",
                ];

              // Append Data
                $csvData[] = $row;
            }

          //* Define CSV Characteristics
            $filename = "check_ins_por_{$receptionist_name}_{$month_name}_{$request->year}.csv";

            $handle = fopen($filename, 'w+');
            fputs($handle, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

            foreach ($csvData as $row) {
                fputcsv($handle, $row);
            }

            fclose($handle);

            $headers = [
                'Content-Type' => 'text/csv',
            ];

            return Response::download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }

        public function occupationPerPeriod(Request $request) {
          //* 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])
                ->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])
                ->where('is_express_type', $analyze_express)->get();

          //* Define CSV Structure
            $csvData = [[
                __('entity_fields')['common']['room_type'],
                __('entity_fields')['reservation']['number_nights'],
                __('neutral_words')['occupation'].' %',
            ]];

          //* Add Data to CSV
            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;

                $csvData[] = [
                    $roomType->tr_name,
                    count($nightsCover),
                    number_format($occupationReached, 2) . '%',
                ];
            }

          //* Define CSV Characteristics
            $type = $analyze_express ? "express" : "normal";
            $filename = "ocupacion_por_periodo_{$start->format('Y-m-d')}_{$end->format('Y-m-d')}_{$type}.csv";

            $handle = fopen($filename, 'w+');
            fputs($handle, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

            foreach ($csvData as $row) {
                fputcsv($handle, $row);
            }

            fclose($handle);
            $headers = ['Content-Type' => 'text/csv'];

            return Response::download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }

        public function occupationPerMonth(Request $request) {
          //* 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])
                ->where('is_express_type', $analyze_express)
                ->get();

          //* Define CSV Structure
            $csvData = [[
                __('entity_fields')['common']['room_type'],
                __('entity_fields')['reservation']['number_nights'],
                __('reports')['hotel']['common']['percentage'] . ' %',
            ]];

          //* Add Data to CSV
            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;

                //* Add row to CSV
                $csvData[] = [
                    $roomType->tr_name,
                    count($nightsCover),
                    number_format($occupationReached, 2, '.', '') . '%',
                ];
            }

          //* Define CSV Characteristics
            $type = $analyze_express ? "express" : "normal";
            $filename = "ocupacion_por_mes_{$month}-{$year}_{$type}.csv";

            $handle = fopen($filename, 'w+');
            fputs($handle, chr(0xEF) . chr(0xBB) . chr(0xBF));

            foreach ($csvData as $row) {
                fputcsv($handle, $row);
            }

            fclose($handle);
            $headers = ['Content-Type' => 'text/csv'];

            return response()->download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }

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

            // 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]);
                })
                ->get();

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

          //* Define CSV Structure
            $csvData = [[
                __('entity_fields')['common']['room_type'],
                __('entity_fields')['reservation']['number_nights'],
                __('reports')['hotel']['common']['percentage'] . ' %',
            ]];

          //* Add Data to CSV
            foreach ($roomTypes as $roomType) {

                $occupiedRooms = $reservations->where('roomType_id', $roomType->id);

                $occupiedNights = 0;
                $nightsCover = [];

                foreach ($occupiedRooms as $reserve) {

                    $entry = Carbon::parse($reserve->date_entry);
                    $departure = Carbon::parse($reserve->date_departure);

                    // Ajustar al rango del año
                    $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;

                $csvData[] = [
                    $roomType->tr_name,
                    count($nightsCover),
                    number_format($occupationReached, 2, '.', '') . '%',
                ];
            }

          //* Define CSV Characteristics
            $type = $analyze_express ? "express" : "normal";
            $filename = "ocupacion_por_año_{$year}_{$type}.csv";

            $handle = fopen($filename, 'w+');
            fputs($handle, chr(0xEF) . chr(0xBB) . chr(0xBF));

            foreach ($csvData as $row) {
                fputcsv($handle, $row);
            }

            fclose($handle);
            $headers = ['Content-Type' => 'text/csv'];

            return response()->download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }

        public function arrivesPerPeriod($start, $end) {
            //* Format Dates
            $startDate = date("Y-m-d", strtotime($start));
            $endDate = date("Y-m-d", strtotime($end));

            //* Get Data for CSV
            $reservations = Reservation::whereBetween('date_entry', [$startDate, $endDate])
                                            ->get();

            //* Define CSV Structure
                $csvData = [[
                    'Folio',
                    __('entity_fields')['reservation']['client'],
                    __('entity_fields')['reservation']['agency'],
                    'Check-In',
                    'Check-Out',
                    __('entity_fields')['reservation']['room'],
                    __('entity_fields')['common']['room_type'],
                    __('entity_fields')['reservation']['number_adults'],
                    __('entity_fields')['reservation']['number_children'],
                    __('entity_fields')['reservation']['number_nights'],
                    __('entity_fields')['reservation']['regimen'],
                ]];

            //* Add Data to CSV

                foreach ($reservations as $reserve) {
                    $row = [
                        $reserve->id,
                        $reserve->client_reference,
                        $reserve?->agency ? $reserve->agency->agency_name : 'Ninguna',
                        $reserve->date_entry,
                        $reserve->date_departure,
                        $reserve->Room->room_name,
                        $reserve->roomType->tr_name,
                        $reserve->number_adults,
                        $reserve->number_children,
                        $reserve->number_nights,
                        $reserve->regimen->regimen_name,
                    ];

                    $csvData[] = $row;
                }

            //* Define CSV Characteristics
                $filename = 'llegadas_por_periodo_' . $startDate . "_" . $endDate . '.csv';

                $handle = fopen($filename, 'w+');
                fputs($handle, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

                foreach ($csvData as $row) {
                    fputcsv($handle, $row);
                }

                fclose($handle);

                $headers = [
                    'Content-Type' => 'text/csv',
                ];

            return Response::download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }

        public function overbookingPerPeriod($start, $end) {
            //* Format Dates
            $startDate = date("Y-m-d", strtotime($start));
            $endDate = date("Y-m-d", strtotime($end));

            //* Get Data for CSV
            $reservations = Reservation::where('roomType_id', 1)
                                        ->whereBetween('date_entry', [$startDate, $endDate])
                                        ->get();

            //* Define CSV Structure
                $csvData = [[
                    'Folio',
                    __('entity_fields')['reservation']['client'],
                    __('entity_fields')['reservation']['source'],
                    __('entity_fields')['reservation']['booked_on'],
                    'Check-In',
                    'Check-Out',
                    __('entity_fields')['reservation']['number_adults'],
                    __('entity_fields')['reservation']['number_children'],
                    __('entity_fields')['reservation']['number_nights'],
                    __('entity_fields')['reservation']['regimen'],
                ]];

            //* Add Data to CSV

                foreach ($reservations as $reserve) {
                    $row = [
                        $reserve->id,
                        $reserve->client_reference,
                        $reserve->source->source_name,
                        $reserve->reservation_date,
                        $reserve->date_entry,
                        $reserve->date_departure,
                        $reserve->number_adults,
                        $reserve->number_children,
                        $reserve->number_nights,
                        $reserve->regimen->regimen_name,
                    ];

                    $csvData[] = $row;
                }

            //* Define CSV Characteristics
                $filename = 'overbooking_por_periodo_' . $startDate . "_" . $endDate . '.csv';

                $handle = fopen($filename, 'w+');
                fputs($handle, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

                foreach ($csvData as $row) {
                    fputcsv($handle, $row);
                }

                fclose($handle);

                $headers = [
                    'Content-Type' => 'text/csv',
                ];

            return Response::download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }

        public function overbookingOnFuture() {

            //* Get Data for CSV
                $mutable = Carbon::now();
                $overBookings = Reservation::where('roomType_id', 1)->get();


            //* Define CSV Structure
                $csvData = [[
                    'Folio',
                    __('entity_fields')['reservation']['client'],
                    __('entity_fields')['reservation']['source'],
                    __('entity_fields')['reservation']['booked_on'],
                    'Check-In',
                    'Check-Out',
                    __('entity_fields')['reservation']['number_adults'],
                    __('entity_fields')['reservation']['number_children'],
                    __('entity_fields')['reservation']['number_nights'],
                    __('entity_fields')['reservation']['regimen'],
                ]];

            //* Add Data to CSV

                foreach ($overBookings as $overBooking) {
                    if($mutable < $overBooking->date_entry){
                        $row = [
                            $overBooking->id,
                            $overBooking->client_reference,
                            $overBooking->source->source_name,
                            $overBooking->reservation_date,
                            $overBooking->date_entry,
                            $overBooking->date_departure,
                            $overBooking->number_adults,
                            $overBooking->number_children,
                            $overBooking->number_nights,
                            $overBooking->regimen->regimen_name,
                        ];

                        $csvData[] = $row;
                    }
                }

            //* Define CSV Characteristics
                $filename = 'overbooking_on_future_' . Carbon::now( config('app.timezone') )->format('Y-m-d') . '.csv';

                $handle = fopen($filename, 'w+');
                fputs($handle, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

                foreach ($csvData as $row) {
                    fputcsv($handle, $row);
                }

                fclose($handle);

                $headers = [
                    'Content-Type' => 'text/csv',
                ];

                return Response::download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }

        public function regimenPerPeriod($start, $end) {

            //* Get Data for CSV
                $regimens = Regimen::all();
                $reservations = Reservation::all();
                $reservesByPeriod = [];

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

                $reservesByPeriod = collect($reservesByPeriod);

            //* Define CSV Structure
                $csvData = [[
                    __('entity_fields')['reservation']['regimen'],
                    __('entity_fields')['reservation']['number_adults'],
                    __('entity_fields')['reservation']['number_children'],
                ]];

            //* Add Data to CSV

                foreach ($regimens as $regimen) {

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

                    $row = [
                        $regimen->regimen_name,
                        $adultQuantity,
                        $childQuantity,
                    ];

                    $csvData[] = $row;
                }

            //* Define CSV Characteristics
                $filename = 'regimen_por_periodo_' . $start . "_" . $end . '.csv';

                $handle = fopen($filename, 'w+');
                fputs($handle, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

                foreach ($csvData as $row) {
                    fputcsv($handle, $row);
                }

                fclose($handle);

                $headers = [
                    'Content-Type' => 'text/csv',
                ];

            return Response::download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }

        public function onDestinyPerPeriod($start, $end) {
            //* Format Dates
                $startDate = date("Y-m-d", strtotime($start));
                $endDate = date("Y-m-d", strtotime($end));

            //* Get Data for CSV
                $reservations = Reservation::where('lodging_contract', 'Pago en Destino')
                                            ->whereBetween('date_entry', [$startDate, $endDate])
                                            ->get();

            //* Define CSV Structure
                $csvData = [[
                    'Folio',
                    __('entity_fields')['reservation']['client'],
                    __('entity_fields')['reservation']['source'],
                    'Check-In',
                    'Check-Out',
                    __('entity_fields')['reservation']['regimen'],
                    __('reports')['hotel']['common']['balance'] . "(". __('reports')['hotel']['common']['agreed'] .")",
                    __('reports')['hotel']['common']['balance'] . "(". __('reports')['hotel']['common']['credited'] .")",
                    __('reports')['hotel']['common']['balance'] . "(". __('reports')['hotel']['common']['outstanding'] .")",
                ]];

            //* Add Data to CSV

                foreach ($reservations as $reserve) {

                    // Saldo Abonado
                        $lodging_payment = $reserve->lodgingPayments->where('status', 'Activo')->sum('payment_price');

                    // Saldo Pendiente
                        $settled_payments = $reserve->lodgingPayments->where('status', 'Activo')->sum('payment_price');
                        $payment_left = ($reserve->total_price) - $settled_payments;
                        if($payment_left < 0) $payment_left = 0;


                    $row = [
                        $reserve->id,
                        $reserve->client_reference,
                        $reserve->source->source_name,
                        $reserve->date_entry,
                        $reserve->date_departure,
                        $reserve->regimen->regimen_name,
                        $reserve->total_price,
                        number_format($lodging_payment),
                        number_format($payment_left, 2, '.', ''),
                    ];

                    $csvData[] = $row;
                }

            //* Define CSV Characteristics
                $filename = 'hospedaje_en_destino_por_periodo_' . $startDate . "_" . $endDate . '.csv';

                $handle = fopen($filename, 'w+');
                fputs($handle, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

                foreach ($csvData as $row) {
                    fputcsv($handle, $row);
                }

                fclose($handle);

                $headers = [
                    'Content-Type' => 'text/csv',
                ];

            return Response::download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }

        public function creditPerPeriod($start, $end) {
            //* Format Dates
                $startDate = date("Y-m-d", strtotime($start));
                $endDate = date("Y-m-d", strtotime($end));

            //* Get Data for CSV
                $reservations = Reservation::where('lodging_contract', 'Credito')
                                            ->whereBetween('date_entry', [$startDate, $endDate])
                                            ->get();

            //* Define CSV Structure
                $csvData = [[
                    'Folio',
                    __('entity_fields')['reservation']['client'],
                    __('entity_fields')['reservation']['source'],
                    'Check-In',
                    'Check-Out',
                    __('entity_fields')['reservation']['regimen'],
                    __('reports')['hotel']['common']['balance'] . "(". __('reports')['hotel']['common']['agreed'] .")",
                    __('reports')['hotel']['common']['balance'] . "(". __('reports')['hotel']['common']['credited'] .")",
                    __('reports')['hotel']['common']['balance'] . "(". __('reports')['hotel']['common']['outstanding'] .")",
                ]];

            //* Add Data to CSV

                foreach ($reservations as $reserve) {

                    // Saldo Abonado
                        $lodging_payment = $reserve->lodgingPayments->where('status', 'Activo')->sum('payment_price');

                    // Saldo Pendiente
                        $settled_payments = $reserve->lodgingPayments->where('status', 'Activo')->sum('payment_price');
                        $payment_left = ($reserve->total_price) - $settled_payments;
                        if($payment_left < 0) $payment_left = 0;


                    $row = [
                        $reserve->id,
                        $reserve->client_reference,
                        $reserve->source->source_name,
                        $reserve->date_entry,
                        $reserve->date_departure,
                        $reserve->regimen->regimen_name,
                        $reserve->total_price,
                        number_format($lodging_payment),
                        number_format($payment_left, 2, '.', ''),
                    ];

                    $csvData[] = $row;
                }

            //* Define CSV Characteristics
                $filename = 'hospedaje_a_credito_por_periodo_' . $startDate . "_" . $endDate . '.csv';

                $handle = fopen($filename, 'w+');
                fputs($handle, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

                foreach ($csvData as $row) {
                    fputcsv($handle, $row);
                }

                fclose($handle);

                $headers = [
                    'Content-Type' => 'text/csv',
                ];

            return Response::download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }

        public function courtesyPerPeriod($start, $end) {
            //* Format Dates
                $startDate = date("Y-m-d", strtotime($start));
                $endDate = date("Y-m-d", strtotime($end));

            //* Get Data for CSV
                $reservations = Reservation::where('lodging_contract', 'Cortesia')
                                            ->whereBetween('date_entry', [$startDate, $endDate])
                                            ->get();

            //* Define CSV Structure
                $csvData = [[
                    'Folio',
                    __('entity_fields')['reservation']['client'],
                    __('entity_fields')['reservation']['source'],
                    'Check-In',
                    'Check-Out',
                    __('entity_fields')['reservation']['regimen'],
                    __('reports')['hotel']['common']['balance'] . "(". __('reports')['hotel']['common']['agreed'] .")",
                    __('reports')['hotel']['common']['balance'] . "(". __('reports')['hotel']['common']['credited'] .")",
                    __('reports')['hotel']['common']['balance'] . "(". __('reports')['hotel']['common']['outstanding'] .")",
                ]];

            //* Add Data to CSV

                foreach ($reservations as $reserve) {

                    // Saldo Abonado
                        $lodging_payment = $reserve->lodgingPayments->where('status', 'Activo')->sum('payment_price');

                    // Saldo Pendiente
                        $settled_payments = $reserve->lodgingPayments->where('status', 'Activo')->sum('payment_price');
                        $payment_left = ($reserve->total_price) - $settled_payments;
                        if($payment_left < 0) $payment_left = 0;


                    $row = [
                        $reserve->id,
                        $reserve->client_reference,
                        $reserve->source->source_name,
                        $reserve->date_entry,
                        $reserve->date_departure,
                        $reserve->regimen->regimen_name,
                        $reserve->total_price,
                        number_format($lodging_payment),
                        number_format($payment_left, 2, '.', ''),
                    ];

                    $csvData[] = $row;
                }

            //* Define CSV Characteristics
                $filename = 'hospedaje_de_cortesia_por_periodo_' . $startDate . "_" . $endDate . '.csv';

                $handle = fopen($filename, 'w+');
                fputs($handle, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

                foreach ($csvData as $row) {
                    fputcsv($handle, $row);
                }

                fclose($handle);

                $headers = [
                    'Content-Type' => 'text/csv',
                ];

            return Response::download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }

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

                // 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();


            //* Get Data for CSV
                $reservations = Reservation::where('guest_status_id', 6)->where('status_id','!=',7)
                    ->whereBetween('date_entry', [$from, $to])->latest()->get();

            //* Define CSV Structure
                $csvData = [[
                    __('entity_fields')['reservation']['guest'],
                    __('entity_fields')['common']['phone'],
                    __('entity_fields')['common']['email'],

                    ...($identity_documents ? [
                        __('entity_fields')['client']['identity_document_type'],
                        __('entity_fields')['client']['identity_document_number'],
                    ] : []),

                    __('entities')['hotel']['reservation'],
                    __('entity_fields')['reservation']['contract'],
                    __('entities')['hotel']['reservation_source'],
                    __('entity_fields')['reservation']['date_entry_large'],

                    ...(($control_hours || $express_reservations) ? [
                        __('entity_fields')['reservation_changes']['check_in_time'],
                    ] : []),

                    __('entity_fields')['reservation']['date_departure_large'],
                    ...(($control_hours || $express_reservations) ? [
                        __('entity_fields')['reservation_changes']['check_out_time'],
                    ] : []),
                    __('entity_fields')['reservation']['number_nights_large'],
                    ...(($control_hours || $express_reservations) ? [
                        __('entity_fields')['reservation']['number_hours_large'],
                    ] : []),

                    __('entity_fields')['reservation']['number_adults'],
                    __('entity_fields')['reservation']['number_children'],
                ]];

            //* Add Data to CSV
                foreach ($reservations as $r) {
                  // Control Data
                    // Records
                    $guest_data = $r->client;

                  // Parsed Data
                    $lodging_contract = [
                            'Pago en Destino' => 'PD',
                            'Cortesia' => 'CT',
                            'Credito' => 'CD',
                        ][$r->lodging_contract] ?? null;

                    $reservation_source = $r->source->source_name;
                        if ($r->source_id === 3) $reservation_source .= " ({$r->agency->agency_name})";

                  // Build Row
                    $csvData[] = [
                        $r->client_reference,
                        $guest_data->client_phone,
                        $guest_data->client_email,

                        ...($identity_documents ? [
                            __('entity_forms')['client'][$guest_data->identity_document_type],
                            $guest_data->identity_document_number,
                        ] : []),

                        $r->id,
                        $lodging_contract,
                        $reservation_source,
                        $r->date_entry,

                        ...(($control_hours || $express_reservations) ? [$r->hour_entry] : []),
                        $r->date_departure,
                        ...(($control_hours || $express_reservations) ? [$r->hour_departure] : []),
                        $r->number_nights,
                        ...(($control_hours || $express_reservations) ? [$r->number_hours] : []),

                        $r->number_adults,
                        $r->number_children,
                    ];
                }

            //* Define CSV Characteristics
                $filename = 'clientes_por_periodo_' . $from->format('Y-m-d') . "_" . $to->format('Y-m-d') . '.csv';

                $handle = fopen($filename, 'w+');
                fputs($handle, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

                foreach ($csvData as $row) {
                    fputcsv($handle, $row);
                }

                fclose($handle);

                $headers = [
                    'Content-Type' => 'text/csv',
                ];

            return Response::download($filename, $filename, $headers)->deleteFileAfterSend(true);
        }


    //* Complementary Render Data
      // Tax Breakdown
        private function appendHotelTaxBreakdown(array &$csvData, array $breakdowns): void
        {
          //* Append Breakdown Header
            $csvData[] = [];
            $csvData[] = [__('entity_forms')['tax_prod_serv']['sections']['tax_breakdown']];
            $csvData[] = [];

          //* Append Breakdown per Income
            $this->appendSection($csvData, __('reports')['hotel']['common']['lodging'], $breakdowns['lodging']); // Lodging
            $this->appendSection($csvData, __('reports')['hotel']['common']['consumption'], $breakdowns['consumption']); // Consumption
            $this->appendSection($csvData, __('reports')['hotel']['common']['extras'], $breakdowns['extras']); // Extras
        }

        private function appendSection(array &$csvData, string $title, array $section): void
        {
          //* Avoid render section if has not specified taxes
            if (empty($section['breakdown'])) return;

          //* Append Subtotal
            $csvData[] = [$title];
            $csvData[] = [
                __('reports')['shared']['tax_breakdown_form']['base_computed'],
                number_format($section['subtotal'], 2)
            ];

          //* Append Tax Breakdown
            $section_total = $section['subtotal'];

            foreach ($section['breakdown'] as $tax) {
              // Apply tax on base
                if ($tax['type'] === 'transfer') {
                    $section_total += $tax['amount'];
                } else {
                    $section_total -= $tax['amount'];
                }

              // Draw cell data
                $sign = ($tax['type'] === 'transfer') ? '+' : '-';

                $csvData[] = [
                    "{$tax['name']} ({$sign}{$tax['rate']}%)",
                    number_format($tax['amount'], 2)
                ];
            }

          //* Append base total
            $csvData[] = [
                'Total',
                number_format(
                    $section_total,
                    2
                )
            ];
            $csvData[] = [];
        }

}
