<?php

namespace App\Http\Controllers;

use Excel;
use App\User;
use DataTables;
use App\AcademyCategory;
use App\AcademySchedule;
use Illuminate\Http\Request;
use App\AcademySchedulesCoach;
use App\AcademyCategoriesSchedule;
use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Exports\AcademySchedulesExport;
use App\Services\AcademyLocationsService;
use Illuminate\Support\Facades\Auth;

class AcademyScheduleController extends Controller
{
    private $__TRAINER_ROL = 14;

    public function index()
    {
        return view('academy.schedules.list');
    }

    public function indexAdd()
    {
        $categories = AcademyCategory::select('academy_categories.id', 'academy_categories.name', 'academy_categories.academy_location_id')
            ->with('academy_location')->where('academy_categories.active', 1)
            ->orderBy('academy_categories.name', 'ASC');
        $coaches = User::select('users.id', 'users.first_name', 'users.last_name', 'users.email', 'users.document')
            ->where([['users.active', 1], ['users.rol_id', $this->__TRAINER_ROL]]);

        $academyLocationsService = new AcademyLocationsService;
        $enableFranchise = $academyLocationsService->enableFranchises() == 'true';
        if (!$academyLocationsService->validateAuthorizedLocations()) {
            $authorizedLocations = $academyLocationsService->getAuthorizedLocations();
            $categories = $categories->join('academy_locations', 'academy_locations.id', '=', 'academy_categories.academy_location_id')
                ->whereIn('academy_locations.id', $authorizedLocations);
            $coaches = $coaches->join('academy_location_users', 'academy_location_users.user_id', '=', 'users.id')
                ->whereIn('academy_location_users.academy_location_id', $authorizedLocations);
        }

        $categories = $categories->get();
        $coaches = $coaches->get();
        return view('academy.schedules.create', compact('categories', 'coaches', 'enableFranchise'));
    }

    public function indexEdit($id)
    {
        $object = AcademySchedule::with('academy_schedules_coaches')->findOrFail($id);
        $academy_schedules_coaches = array();
        if (count($object->academy_schedules_coaches)) {
            foreach ($object->academy_schedules_coaches as $item) {
                $academy_schedules_coaches[] = $item->user_id;
            }
        }
        $object->coaches = $academy_schedules_coaches;

        $academy_categories_schedules = AcademyCategoriesSchedule::select('academy_category_id')
            ->where('academy_schedule_id', $id)
            ->pluck('academy_category_id')
            ->toArray();

        $categories = AcademyCategory::select('academy_categories.id', 'academy_categories.name', 'academy_categories.academy_location_id')
            ->with('academy_location')->where('academy_categories.active', 1)
            ->orderBy('academy_categories.name', 'ASC');
        $coaches = User::select('users.id', 'users.first_name', 'users.last_name', 'users.email', 'users.document')
            ->where([['users.active', true], ['users.rol_id', $this->__TRAINER_ROL]]);

        $academyLocationsService = new AcademyLocationsService;
        $enableFranchise = $academyLocationsService->enableFranchises() == 'true';

        if (!$academyLocationsService->validateAuthorizedLocations()) {
            $authorizedLocations = $academyLocationsService->getAuthorizedLocations();
            $categories = $categories->join('academy_locations', 'academy_locations.id', '=', 'academy_categories.academy_location_id')
                ->whereIn('academy_locations.id', $authorizedLocations);
            $coaches = $coaches->join('academy_location_users', 'academy_location_users.user_id', '=', 'users.id')
                ->whereIn('academy_location_users.academy_location_id', $authorizedLocations);
        }

        $categories = $categories->get();
        $coaches = $coaches->get();

        return view(
            'academy.schedules.edit',
            compact(
                'object',
                'academy_categories_schedules',
                'categories',
                'coaches',
                'enableFranchise'
            )
        );
    }

    public function tableFilter(Request $request)
    {
        $obj = $this->buildGetQuery($request);

        return DataTables::of($obj)
            ->addColumn('actions', function ($obj) {
                return '
                    <i class="fa fa-pencil iconMini" onClick="clickEdit(' . $obj->id . ')" data-id="' . $obj->id . '" title="Editar"></i>
                    <i class="fa fa-trash iconMini" onClick="clickDelete(' . $obj->id . ')" data-id="' . $obj->id . '" title="Eliminar"></i>
                ';
            })
            ->editColumn('active', function ($obj) {
                if ($obj->active == 0) {
                    return '<div class="switch"><label><div class="checkbox checbox-switch switch-success"> <label> ' . __('messages.no') . ' <input type="checkbox" onChange="chk(' . $obj->id . ')" data-id="' . $obj->id . '" id="Checkactive' . $obj->id . '" name="Checkactivo" /> <span></span>' . __('messages.yes') . ' </label></div> </label> </div>';
                } else {
                    return '<div class="switch"><label> <div class="checkbox checbox-switch switch-success"> <label>   ' . __('messages.no') . ' <input type="checkbox" onChange="chk(' . $obj->id . ')" data-id="' . $obj->id . '" id="Checkactive' . $obj->id . '" name="Checkactivo" checked="" />
                        <span></span> ' . __('messages.yes') . ' </label> </div>  </label> </div>';
                }
            })
            ->editColumn('created_at', function ($obj) {
                return \Carbon\Carbon::parse($obj->created_at)->format('Y-m-d h:i:s A');
            })
            ->rawColumns(['actions', 'active'])
            ->make(true);
    }

    public function create(Request $request)
    {
        try {
            $categories = $request->input('categories');
            $schedule = AcademySchedule::join('academy_categories_schedules', 'academy_categories_schedules.academy_schedule_id', '=', 'academy_schedules.id')
                ->where([['academy_schedules.name', $request->input('name')], ['academy_schedules.active', true]])
                ->where(function ($q) use ($categories) {
                    if ($categories) {
                        $q->whereIn('academy_categories_schedules.academy_category_id', $categories);
                    }
                })
                ->first();
            if ($schedule) {
                return response(array("r" => false, "type" => "error", "title" => "Oops...", "m" => __('messages.already_exists', ['name' => $request->input('name')]), "data" => null));
            }
            $data = $request->except(['generalMultiselect', 'tags']);
            if ($model = AcademySchedule::create($data)) {
                if ($request->input('coaches')) {
                    foreach ($request->input('coaches') as $coach) {
                        $data = new AcademySchedulesCoach();
                        $data->academy_schedule_id = $model->id;
                        $data->user_id = $coach;
                        $data->save();
                    }
                }

                if ($request->input('categories')) {
                    foreach ($request->input('categories') as $categoryId) {
                        $data = new AcademyCategoriesSchedule();
                        $data->academy_schedule_id = $model->id;
                        $data->academy_category_id = $categoryId;
                        $data->save();
                    }
                }
                $this->adjustAvailableSlotsSchedules();
                $this->registerLog(Auth::user()->id, 'Crear horario academia', json_encode($request->all()), "Create", $this->getModule($request));
                return response(array("r" => true, "type" => "success", "title" => "", "m" => __('messages.created_successfully'), "data" => $model->id));
            } else {
                return response(array("r" => false, "type" => "error", "title" => "Oops...", "m" => __('messages.error_creating'), "data" => null));
            }
        } catch (\Exception $e) {
            return response(
                array(
                    "r" => false,
                    "type" => "error",
                    "title" => "Oops...",
                    "m" => __($e->getMessage()),
                    "data" => null
                )
            );
        }
    }

    public function update(Request $request, $id)
    {
        try {
            $request['id'] = $id;
            $categories = $request->input('categories');
            $schedule = AcademySchedule::join('academy_categories_schedules', 'academy_categories_schedules.academy_schedule_id', '=', 'academy_schedules.id')
                ->where([['academy_schedules.id', '!=', $id], ['academy_schedules.name', $request->input('name')], ['academy_schedules.active', true]])
                ->where(function ($q) use ($categories) {
                    if ($categories) {
                        $q->whereIn('academy_categories_schedules.academy_category_id', $categories);
                    }
                })
                ->first();
            if ($schedule) {
                return response(array("r" => false, "type" => "error", "title" => "Oops...", "m" => __('messages.already_exists', ['name' => $request->input('name')]), "data" => null));
            }
            $data = $request->except(['categories', 'coaches']);
            if (AcademySchedule::where('id', $id)->update($data)) {
                if ($request->input('coaches')) {
                    AcademySchedulesCoach::where('academy_schedule_id', $id)->delete();
                    foreach ($request->input('coaches') as $coach) {
                        $data = new AcademySchedulesCoach();
                        $data->academy_schedule_id = $id;
                        $data->user_id = $coach;
                        $data->save();
                    }
                }

                if ($request->input('categories')) {
                    AcademyCategoriesSchedule::where('academy_schedule_id', $id)->delete();
                    foreach ($request->input('categories') as $categoryId) {
                        $data = new AcademyCategoriesSchedule();
                        $data->academy_schedule_id = $id;
                        $data->academy_category_id = $categoryId;
                        $data->save();
                    }
                }

                $this->adjustAvailableSlotsSchedules();
                $this->registerLog(Auth::user()->id, 'Editar horario academia', json_encode($request->all()), "Update", $this->getModule($request));
                return response(array("r" => true, "type" => "success", "title" => "", "m" => __('messages.updated_successfully'), "data" => $id));
            } else {
                return response(array("r" => false, "type" => "error", "title" => "Oops...", "m" => __('messages.error_updating'), "data" => null));
            }
        } catch (\Exception $e) {
            return response(
                array(
                    "r" => false,
                    "type" => "error",
                    "title" => "Oops...",
                    "m" => __($e->getMessage()),
                    "data" => null
                )
            );
        }
    }

    public function delete(Request $request, $id)
    {
        try {
            $schedule = AcademySchedule::find($id);

            if (!$schedule) {
                return response([
                    "r" => false,
                    "type" => "error",
                    "title" => "Oops...",
                    "m" => __('messages.not_found'),
                    "data" => null
                ]);
            }

            // Definir las relaciones a verificar
            $relations = [
                'academy_schedules_coaches' => 'Entrenadores asociados',
            ];

            // Comprobar si existen relaciones
            $related = [];

            // Verificar entrenadores activos asociados
            foreach ($relations as $method => $relationName) {
                // Filtrar solo los coaches cuyo usuario NO esté eliminado
                $hasActiveCoaches = $schedule->$method()
                    ->whereHas('user', function ($query) {
                        $query->whereNull('deleted_at'); 
                    })
                    ->exists();

                if ($hasActiveCoaches) {
                    $related[] = $method;  
                }
            }

            // Si existen relaciones, bloquear la eliminación 
            if (!empty($related)) {
                return response([
                    "r" => false,
                    "type" => "error",
                    "title" => "No se puede eliminar",
                    "m" => "El horario no puede ser eliminado porque tiene relaciones con: " . implode(', ', $related),
                    "data" => null
                ]);
            }

            // Eliminar las relaciones asociadas
            AcademySchedulesCoach::where('academy_schedule_id', $id)->delete();
            AcademyCategoriesSchedule::where('academy_schedule_id', $id)->delete();

            // Eliminar el horario si no tiene relaciones
            if ($schedule->delete()) {
                $this->registerLog(Auth::user()->id, 'Eliminó horario academia', json_encode($schedule), "Delete", $this->getModule($request));
                return response([
                    "r" => true,
                    "type" => "success",
                    "title" => "",
                    "m" => __('messages.deleted_successfully'),
                    "data" => null
                ]);
            }

            return response([
                "r" => false,
                "type" => "error",
                "title" => "Oops...",
                "m" => __('messages.error_removing'),
                "data" => null
            ]);

        } catch (\Exception $e) {
            return response([
                "r" => false,
                "type" => "error",
                "title" => "Oops...",
                "m" => __('messages.delete_relation_data') . " Detalles: " . $e->getMessage(),
                "data" => null
            ]);
        }
    }

    public function activate(Request $request)
    {
        try {
            $id = $request['id'];
            $state = $request['state'];

            $schedule = AcademySchedule::find($id);
            $schedule->active = $state;
            $schedule->update();

            $this->registerLog(Auth::user()->id, 'Activar/Desactivar horario academia', json_encode($schedule), "Update", $this->getModule($request));
            return array('r' => true, 'd' => null, 'm' => __('messages.updated_successfully'));
        } catch (\Throwable $th) {
            return array('r' => false, 'd' => null, 'm' => __('messages.error_updating'));
        }
    }

    public function validateScheduleAvailability($schedule)
    {
        $response = DB::table('academy_schedules as ac')
            ->selectRaw('IF(ac.current_capacity > 0, TRUE, FALSE) as status, ac.name as schedule')
            ->where('ac.active', true)
            ->where('ac.id', $schedule)
            ->first();
        if (!$response) {
            $schedule = AcademySchedule::find($schedule);
            return response(array('r' => false, 'm' => trans('messages.academy_schedules_controller.without_slots', ['schedule' => $schedule ? $schedule->name : __('messages.selected')])));
        }
        return response(array('r' => $response->status, 'm' => !$response->status ? trans('messages.academy_schedules_controller.without_slots', ['schedule' => $response->schedule]) : ''));
    }


    public function adjustAvailableSlotsSchedules($schedule = null)
    {
        // Step 1: Subquery to calculate the total per academy_schedule_id
        $subQuery = DB::table('academy_users as au')
            ->select('au.academy_schedule_id', DB::raw('COUNT(au.id) as total'))
            ->join('academy_purchases as ap', 'ap.academy_user_id', '=', 'au.id')
            ->join('payment_transactions as pt', 'pt.id', '=', 'ap.payment_transaction_id')
            ->whereNull('au.deleted_at')
            ->whereNull('ap.deleted_at')
            ->where('au.active', true)
            ->where('ap.term_type', 'Inscripción')
            ->where('ap.term', date('Y'))
            ->where('pt.state', 'CONFIRMED')
            ->when($schedule, function ($query) use ($schedule) {
                return $query->where('au.academy_schedule_id', $schedule);
            })
            ->groupBy('au.academy_schedule_id');

        // Step 2: Main query to get academy_schedules and join the subquery
        $query = DB::table('academy_schedules as ac')
            ->select('ac.id', DB::raw('(ac.total_capacity - IFNULL(MAX(ob1.total), 0)) as current_capacity'))
            ->leftJoinSub($subQuery, 'ob1', 'ob1.academy_schedule_id', '=', 'ac.id')
            ->where('ac.active', true)
            ->when($schedule, function ($query) use ($schedule) {
                return $query->where('ac.id', $schedule);
            })
            ->groupBy('ac.id')
            ->get();

        // Step 3: Update the current_capacity column
        $query->each(function ($item) {
            AcademySchedule::where('id', $item->id)->update(['current_capacity' => $item->current_capacity]);
        });
    }

    public function validateExport(Request $request)
    {
        $results = $this->buildGetQuery($request)->get();
        if (count($results) > 0) {
            $name = 'ReporteAcademiaHorarios' . time() . '.xlsx';
            Excel::store(new AcademySchedulesExport($results), $name, 'public');
            return response()->json(['success' => true, 'message' => 'Validación OK', 'data' => $name]);
        }
        return response()->json(['success' => false, 'message' => 'No existen datos a exportar']);
    }

    function buildGetQuery(Request $request)
    {
        $query = AcademySchedule::select(
            'academy_schedules.id',
            'academy_schedules.name',
            'academy_schedules.total_capacity',
            'academy_schedules.current_capacity',
            'academy_schedules.active',
            'academy_schedules.created_at'
        )->with(
            'academy_schedules_coaches',
            'academy_schedules_coaches.user',
            'academy_categories_schedules',
            'academy_categories_schedules.academy_category',
            'academy_categories_schedules.academy_category.academy_location'
        );

        $academyLocationsService = new AcademyLocationsService;
        if (!$academyLocationsService->validateAuthorizedLocations()) {
            $authorizedLocations = $academyLocationsService->getAuthorizedLocations();
            $query->whereHas('academy_categories_schedules.academy_category.academy_location', function ($query) use ($authorizedLocations) {
                $query->whereIn('academy_locations.id', $authorizedLocations);
            });
        }

        if ($request['coaches']) {
            $query->join('academy_schedules_coaches', 'academy_schedules_coaches.academy_schedule_id', '=', 'academy_schedules.id')
                ->whereIn('academy_schedules_coaches.user_id', $request['coaches']);
        }

        if ($request['categories'] || $request['locations']) {
            $query->join('academy_categories_schedules', 'academy_categories_schedules.academy_schedule_id', '=', 'academy_schedules.id');

            if ($request['categories']) {
                $query->whereIn('academy_categories_schedules.academy_category_id', $request['categories']);
            } else if ($request['locations']) {
                $query->join('academy_categories', 'academy_categories.id', '=', 'academy_categories_schedules.academy_category_id')
                    ->whereIn('academy_categories.academy_location_id', $request['locations']);
            }
        }

        return $query;
    }
}