<?php

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use App\Room;
use App\AccommodationEquipment;

class HomeController extends Controller
{
    public function __construct()
    {
    }

    public function index()
    {
        // DELETE UNCONFIRMED RESERVATIONS
        $now = new \DateTime();
        $now->modify('- 2 days');
        $now = $now->format('Y-m-d H:i:s');

        DB::table('reservations')
            ->join('booked','booked.reservation_id','=','reservations.reservation_id')
            ->where('confirmation','=','false')
            ->where('reservation_date','<', $now)
            ->delete();

        $accommodations = DB::table('accommodations')
            ->where('permission','=','true')
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                    ->from('rooms')
                    ->whereRaw('rooms.accommodation_id = accommodations.accommodation_id');
            })
            ->get();

        $beds = null; $rooms = null; $equipment = null; $equipments = null; $image=null;

        // NUMBER OF ROOMS AND BEDS IN EACH OF ACCOMMODATION
        if($accommodations != null){
            foreach ($accommodations as $accommodation) {
                $rooms[$accommodation->accommodation_id] = Room::where('accommodation_id', '=', $accommodation->accommodation_id)->count();
                $beds[$accommodation->accommodation_id] = Room::where('accommodation_id', '=', $accommodation->accommodation_id)->sum('beds');
                $equipment[$accommodation->accommodation_id] = AccommodationEquipment::where('accommodation_id','=',$accommodation->accommodation_id);
                $image[$accommodation->accommodation_id] = DB::table('accommodations')
                    ->join('images','accommodations.image_id','=','images.image_id')
                    ->select('images.image')
                    ->where('accommodation_id','=',$accommodation->accommodation_id)
                    ->first();
                $equipments[$accommodation->accommodation_id] = DB::table('accommodation_equipment')->
                    join('equipment','accommodation_equipment.equipment_id', '=', 'equipment.equipment_id')->
                    select('equipment_name')->where('accommodation_equipment.accommodation_id','=',$accommodation->accommodation_id)
                    ->get();
            }
        }
        return view('home', ['accommodations' => $accommodations, 'beds' => $beds, 'rooms' => $rooms, 'equipment' => $equipments, 'image'=>$image]);
    }
}
