<?php
namespace App\Models;
use League\Period\Period;

class ReservationsModel extends BaseModel{
    public string $images_table_name = "vb_unit_images";
    public string $reservations_table = "escapiareservations";
    public string $reservationsMain_table = "reservations";
    public string $contactsTable = "contacts";
    public string $ru_seasons = "ru_seasons";
    public string $ru_charges = "ru_charges";
    public string $ru_taxes = "ru_taxes";
    public string $scheduletransactions = "scheduletransactions";

    public function getAvailabilityByUnitId($unitId, $startDate, $endDate) : bool{
        $startDate = date('Y-m-d',strtotime('+1 day',strtotime($startDate)));
        $endDate = date('Y-m-d',strtotime('-1 day',strtotime($endDate)));

        $q = "SELECT * FROM $this->reservations_table where unit_id='$unitId' AND iscancelled = false AND
            (
                (arrival_checkin_date   >= '$startDate' and arrival_checkin_date     <= '$endDate') OR
                (arrival_checkout_date  >= '$startDate' and arrival_checkout_date    <= '$endDate') OR
                (arrival_checkin_date   <= '$startDate' and arrival_checkout_date    >= '$endDate')
            )
        ";
        $reservations = $this->select($q);
        if(count($reservations) > 0){
            return false;
        }
        else{
            return true;
        }
    }
    
    public function getPaymentScheduleByReservationId($id){
        $q = "SELECT * FROM $this->scheduletransactions WHERE reservation_id = ? ORDER BY scheduled_date ASC";
        $paymentSchedules = $this->select($q,[$id]);
        return $paymentSchedules;
    }
    
    public function getCheckinAllowedByUnitId($unitId, $date) : bool{
        
    }
    public function getCheckoutAllowedByUnitId($unitId, $date) : bool{
        $date = date('Y-m-d',strtotime("-1 day",strtotime($date)));
        $q = "SELECT * FROM $this->reservations_table where unit_id='$unitId' AND iscancelled = false AND arrival_checkin_date = '$date'";

        $reservations = $this->select($q);
        if(count($reservations) > 0){
            return false;
        }
        else{
            return true;
        }
    }
    
    
    public function getAllReservationsByDates($startDate,$endDate){
        $startDateTime = date('Y-m-d H:i:s',strtotime($startDate));
        if(empty($endDate)){
            $endDate = date('Y-m-d',strtotime('+3 years'));
            $endDateTime = date('Y-m-d H:i:s',strtotime($endDate));
        }
        
        $q = "SELECT * FROM $this->reservations_table where timestamp >= '$startDateTime' AND timestamp <= '$endDateTime' ORDER BY timestamp ASC";
        $tempResults = $this->select($q);

        $resNumberForFetch = [];        
        $resNumberForFetchall = [];
        foreach($tempResults as $tempResult){
            $resNumberForFetch[] = $tempResult['booking_number'];
            $resNumberForFetchall[$tempResult['booking_number']] = $tempResult;
        }

        $q = "SELECT * FROM $this->reservationsMain_table where source_category = '42' AND source_subcategory = '63' AND  "; //is_from_escapia = false AND // CONVERT(STR_TO_DATE(checkin_date,\"%m/%d/%Y\"),DATE) >= '".date('Y-m-d',strtotime($startDate))."'";
        $q .= "booking_number IN (\"".implode('","',$resNumberForFetch).'") ';
        $startDate = date('Y-m-d',strtotime($startDate));

        if(empty($endDate)){
            $endDate = date('Y-m-d',strtotime('+3 years'));
        }
        $endDate = date('Y-m-d',strtotime($endDate));
        // $q .= "
        //     (
        //         (CONVERT(STR_TO_DATE(checkin_date,\"%m/%d/%Y\"),DATE)   >= '$startDate' and CONVERT(STR_TO_DATE(checkin_date,\"%m/%d/%Y\"),DATE)     <= '$endDate') OR
        //         (CONVERT(STR_TO_DATE(checkout_date,\"%m/%d/%Y\"),DATE)  >= '$startDate' and CONVERT(STR_TO_DATE(checkout_date,\"%m/%d/%Y\"),DATE)    <= '$endDate') OR
        //         (CONVERT(STR_TO_DATE(checkin_date,\"%m/%d/%Y\"),DATE)   <= '$startDate' and CONVERT(STR_TO_DATE(checkout_date,\"%m/%d/%Y\"),DATE)    >= '$endDate')
        //     ) 
        // ";
        
        // if(!empty($endDate)){
        //     $q .= "AND CONVERT(STR_TO_DATE(checkout_date,\"%m/%d/%Y\"),DATE) >= '".date('Y-m-d',strtotime($startDate))."'"
        // }
        
        $q .= " ORDER BY id DESC";
        $reservations = $this->select($q);

        foreach($reservations as &$res){
            $res['timestamp'] = $resNumberForFetchall[$res['booking_number']]['timestamp'];
        }

        return $reservations;
    }
    
    public function getAllReservations(){
        $q = "SELECT * FROM $this->reservationsMain_table where CONVERT(STR_TO_DATE(checkout_date,\"%m/%d/%Y\"),DATE) >= '".date('Y-m-d',strtotime('-40 days'))."' ORDER BY id DESC";
        return $this->select($q);
    }

    public function getReservationByBookingNumber($booking_number){
        $q = "SELECT * FROM $this->reservationsMain_table where booking_number = '$booking_number'";
        return $this->select($q);
    }
    public function getEscapiaReservationByBookingNumber($booking_number){
        $q = "SELECT * FROM $this->reservations_table where booking_number = '$booking_number'";
        return $this->select($q);
    }

    public function getGuest($id){
        $q = "SELECT * FROM $this->contactsTable where id = $id";
        return $this->select($q);
    }

    public function updateCancelStatus($bookingNumber){
        $q = "Update $this->reservationsMain_table set vrbo_cancelled_by='CANCELLED_BY_TRAVELER' where booking_number='$bookingNumber'";
        $this->update($q);
    }
    
    public function insertHistory($resId,$message){
        $q = "Insert Into reservationshistory (res_id,created_by,msg) VALUES (?,?,?)";
        $this->insert($q,[$resId,0,$message]);        
    }
    



}