<?php

namespace App\Http\Controllers;

use App\Models\Horario;
use App\Models\Sucursal;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;
use Illuminate\Validation\Rule;
use Illuminate\Support\Facades\DB;

class HorarioController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index(): JsonResponse
    {
        $horarios=Horario::leftjoin('sucursales', 'horarios.sucursal_id', '=', 'sucursales.id')
        ->selectRaw("
            horarios.*,
            sucursales.nombre sucursal,
            date_format(hora_entrada,'%l:%i %p') hora_entrada_temp,
            date_format(hora_salida,'%l:%i %p') hora_salida_temp,
            (
                CASE
                    WHEN dia=2 THEN 'Lunes'
                    WHEN dia=3 THEN 'Martes'
                    WHEN dia=4 THEN 'Miércoles'
                    WHEN dia=5 THEN 'Jueves'
                    WHEN dia=6 THEN 'Viernes'
                    WHEN dia=7 THEN 'Sábado'
                    WHEN dia=1 THEN 'Domingo'
                END
            ) dia_name
        ")->get();
        return response()->json([
            'data' =>$horarios,
        ], JsonResponse::HTTP_OK);
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request): JsonResponse
    {
        $request->validate([
            'sucursal_id' => ['required', Rule::exists('sucursales', 'id')],
            'dia' => ['required', 'numeric', 'max:2'],
            'hora_entrada' => ['required'],
            'hora_salida' => ['required'],
        ]);

        $horario = Horario::create($request->only([
            'sucursal_id',
            'dia',
            'hora_entrada',
            'hora_salida'
        ]));

        return response()->json([
            'data' => $horario,
        ], JsonResponse::HTTP_OK);
    }


    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show(Horario $horario): JsonResponse
    {
        return response()->json([
            'data' => $horario,
        ], JsonResponse::HTTP_OK);
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, Horario $horario): JsonResponse
    {
        $request->validate([
            'sucursal_id' => ['required', Rule::exists('sucursales', 'id')],
            'dia' => ['required', 'numeric', 'max:2'],
            'hora_entrada' => ['required'],
            'hora_salida' => ['required'],
        ]);

        $horario->update($request->only([
            'sucursal_id',
            'dia',
            'hora_entrada',
            'hora_salida'
        ]));

        return response()->json([
            'data' => $horario,
        ], JsonResponse::HTTP_OK);
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function destroy(Horario $horario): JsonResponse
    {
        $horario->delete();

        return response()->json(null, JsonResponse::HTTP_NO_CONTENT);
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function getLlenarDias(Sucursal $sucursal): JsonResponse
    {
        $sql = "
        SELECT
            *
            FROM
            (
                SELECT 1 id, 'Domingo' dia_name union
                SELECT 2 id, 'Lunes' dia_name union
                SELECT 3 id, 'Martes' dia_name union
                SELECT 4 id, 'Miércoles' dia_name union
                SELECT 5 id, 'Jueves' dia_name union
                SELECT 6 id, 'Viernes' dia_name union
                SELECT 7 id, 'Sábado' dia_name
            ) dias
        WHERE id not in(
            SELECT dia from horarios
            where sucursal_id={$sucursal->id}
        )";
        $dias = DB::select($sql);
        return response()->json([
            'data' => $dias
        ], JsonResponse::HTTP_OK);
    }


    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function getDisponible(Request $request): JsonResponse
    {
        $request->validate([
            'sucursal_id' => ['required'],
            'fecha' => ['required'],
        ]);
        $sql="
        SELECT
            DISTINCT
            time id,
            DATE_FORMAT(time, '%h:%i %p') name
        FROM (
            SELECT
                DISTINCT
                u.id terapista_id,
                u.`name`,
                time
            from (
                            select TIME('08:00:00') time union
                            select TIME('09:00:00') time union
                            select TIME('10:00:00') time union
                            select TIME('11:00:00') time union
                            select TIME('12:00:00') time union
                            select TIME('13:00:00') time union
                            select TIME('14:00:00') time union
                            select TIME('15:00:00') time union
                            select TIME('16:00:00') time union
                            select TIME('17:00:00') time union
                            select TIME('18:00:00') time union
                            select TIME('19:00:00') time
            ) as m,
            users u
            INNER JOIN model_has_roles m on u.id=m.model_id and role_id=2
            where
            u.estatus='activo' and
            u.id <> 36 and
            time >= (
                SELECT min(h.hora_entrada) from horarios h where h.dia=DAYOFWEEK('".$request->fecha."') and h.sucursal_id=".$request->sucursal_id."
            ) and
            time <= (
                SELECT max(h.hora_salida) from horarios h where h.dia=DAYOFWEEK('".$request->fecha."') and h.sucursal_id=".$request->sucursal_id."
            ) or
            time in ('".$request->hora."')
        ) resum
        WHERE NOT EXISTS (
            SELECT 1
                FROM (
                    SELECT
                        u.id,
                        u.`name`,
                        s.fecha_asignada,
                        DAYOFWEEK(s.fecha_asignada) dia_semana,
                        date(s.fecha_asignada) fecha,
                        time(s.fecha_asignada) hora
                    FROM users u
                    INNER JOIN model_has_roles m on u.id=m.model_id and role_id=2
                    INNER JOIN sesiones s on s.terapista_id = u.id and
                            date(s.fecha_asignada) = date(SUBDATE('".$request->fecha."',1)) and
                            s.sucursal_id=".$request->sucursal_id."
                    where u.estatus='activo'
                ) m where m.id = resum.terapista_id and m.hora = resum.time
        ) or
        time in ('".$request->hora."')
        ";
        $response = DB::select($sql);

        return response()->json([
            'data' =>$response,
        ], JsonResponse::HTTP_OK);
    }
}
