<?php
namespace Drupal\restaurant\Bookings;


use Drupal\restaurant\Database\Db;
use Drupal\restaurant\Date\Date;
use Drupal\restaurant\App\Misc;

class Booking
{
	/**
	 * Instance of Cms_Settings
	 *
	 * @var Object
	 */
	protected $settings;

	/**
	 * Instance of Cms_Date
	 *
	 * @var Object
	 */
	protected $date;

	protected $times;

  /**
  *  Drupal database object
  */
	protected $conn;

	protected $service;
	
	/**
	* HTML email to send on successfull booking 
	*/
	protected $htmlEmail;
	
	/**
	* Text alternative email to send on booking
	*/
	protected $textEmail;

	public function __construct($conn)
	{
	   
		$this->conn = $conn;
		$this->date = new Date();
		//$this->service = \Drupal::service('restaurant.misc');
		
		$this->service = new Misc();
		
	}

	public function removeCache()
	{
		$this->conn->delete('bookings_cache');
	}

	public function makeBooking($data, array $req = array())
	{
		$service = $data['service'];
		unset ($data['service']);

		$data['bookingtime'] = ltrim($data['bookingtime']);

		$data['created'] = (isset ($data['created'])) ? $data['created'] : date('Y-m-d H:i:s');
		$data['modified'] = (isset ($data['modified'])) ? $data['modified'] : $data['created'];
		
		$id = $this->conn->insert('restaurant_bookings')->fields($data)->execute();
		$hash = password_hash($id, PASSWORD_DEFAULT);
		$this->conn->update('restaurant_bookings')->fields(['cancelurl' => $hash])->condition('id', $id, '=')->execute();

		$this->conn->delete('restaurant_bookings_requirements')
		           ->condition('booking_id', $id)
		           ->execute();
		
		foreach ($req as $key => $val) {
		    $insert = array(
		        'booking_id' => $id,
		        'requirement_id' => $val
		    );
		    $this->conn->insert('restaurant_bookings_requirements')->fields($insert)->execute();
		}
		
		$date = $data['bookingtime'];

		$close = $this->getServiceClose($date, $service);
		$tables = $this->getTables($data['bookingtime'], $close, $data['guests'], $service);

		$time = strtotime("+ " . $this->service->getSetting('bookinglength'), strtotime($date));

		if (! empty ($tables)) {
			$time = strtotime("+ " . $this->service->getSetting('bookinglength'), strtotime($date));

			$data = array(
				'booking_id' => $this->conn->query("SELECT MAX(id) FROM restaurant_bookings")->fetchField(),
				'start' => $date,
				'end' => date('Y-m-d H:i:s', $time)
			);
			if (is_array($tables)) {
				foreach ($tables as $key => $val) {
					$data['table_id'] = $val;
					$this->conn->insert('restaurant_bookings_tables')->fields($data)->execute();
				}
			} else {
				$data['table_id'] = $tables;
				$this->conn->insert('restaurant_bookings_tables')->fields($data)->execute();
			}
		}

		return $id;
	}
	
	/**
	* return the html email for sending
	*/
	public function getHtmlEmail()
	{
		return $this->htmlEmail;
	}
	
	/**
	* Return the alternative plain text email
	*/
	public function getTextEmail()
	{
		return $this->textEmail;
	}
	
	/**
	 * 
	 * @param unknown $bookingid
	 * @param bool $html - values = htmlpage, htmlresults, textbody
	 *
	 * @return string
	 */
	public function getBookingResults($bookingid)
	{
		 
	    $booking = $this->conn->query("SELECT * FROM restaurant_bookings WHERE id = ?", array($bookingid))->fetch();
	    $time = strtotime($booking->bookingtime);
	    $day = date('l', $time);
	    $bookingtime = date('H:i:s', $time);
	    $service = $this->conn->query("SELECT * FROM restaurant_opening_times WHERE day = ? AND '$bookingtime' BETWEEN open AND close", array($day))->fetch();
	    
	    $config = \Drupal::config('system.site');
	    $sitename = $config->get('name');
	    
	    $dateformat = date('d/m/Y h:ia', strtotime($booking->bookingtime));
	    $datepart = date('d/m/Y', strtotime($booking->bookingtime));
	    
	    
	    $sql = "SELECT restaurant_requirements.requirement FROM restaurant_requirements LEFT JOIN restaurant_bookings_requirements ON 
	    restaurant_bookings_requirements.requirement_id = restaurant_requirements.id WHERE restaurant_bookings_requirements.booking_id = ?";
	    
	    $result = $this->conn->query($sql, array($booking->id))->fetchCol();
	    $other = $this->conn->query("SELECT requirements FROM restaurant_bookings WHERE id = ?", array($booking->id))->fetchField();
	    if (! empty ($other)) {
	    	$result[] = $other;
		 }
	    
	    $html_requirements = ''; $text_requirements = '';
	    if (! empty ($result)) {
	    	$text_requirements = 'Requirements ' . implode(", ", $result);
	    	
	    	$html_requirements .= '<dd style="display: block; clear: both; font-weight: bold; padding: 0; margin: 0; margin-top: 10px;">Requirements</dd>';
	    	$html_requirements .= '<dt style="display: block; clear: both;">' . implode(", ", $result) . '</dt>';
	    }
	   
	  

$htmlBody = <<<HTMLBODY

<dl style="display: block; width: 300px; border: 0px;">

    <dd style="display: block; clear: both; font-weight: bold; padding: 0; margin: 0; margin-top: 10px;">Name </dd>
    <dt style="display: block; clear: both;">{$booking->firstname} {$booking->lastname}</dt>

    <dd style="display: block; clear: both; font-weight: bold; padding: 0; margin: 0; margin-top: 10px;">Reference Number </dd>
    <dt style="display: block;">{$booking->id}</dt>

    <dd style="display: block; clear: both; font-weight: bold; padding: 0; margin: 0; margin-top: 10px;">Venue </dd>
    <dt style="display: block">{$sitename}</dt>

    <dd style="display: block; clear: both; font-weight: bold; padding: 0; margin: 0; margin-top: 10px;">Service </dd>
    <dt style="display: block">{$service->name}</dt>

    <dd style="display: block; clear: both; font-weight: bold; padding: 0; margin: 0; margin-top: 10px;">Time </dd>
    <dt style="display: block">{$bookingtime}</dt>        

    <dd style="display: block; clear: both; font-weight: bold; padding: 0; margin: 0; margin-top: 10px;">Date </dd>
    <dt style="display: block">{$datepart}</dt>

    <dd style="display: block; clear: both; font-weight: bold; padding: 0; margin: 0; margin-top: 10px;">Number of Guests </dd>
    <dt style="display: block">{$booking->guests}</dt>

    <dd style="display: block; clear: both; font-weight: bold; padding: 0; margin: 0; margin-top: 10px;">Phone </dd>
    <dt style="display: block">{$booking->phone}</dt>

    <dd style="display: block; clear: both; font-weight: bold; padding: 0; margin: 0; margin-top: 10px;">Email </dd>
    <dt style="display: block">{$booking->email}</dt>

    {$html_requirements}

</dl>
HTMLBODY;
    
$textBody = <<<TEXTBODY

Name {$booking->firstname} {$booking->lastname}

Reference Number {$booking->id}

Venue {$sitename}

Service {$service->name}

Time {$bookingtime}    

Date {$datepart}

Number of Guests {$booking->guests}

Phone {$booking->phone}

Email {$booking->email}

    {$text_requirements}

TEXTBODY;

	$config = \Drupal::config('system.site');
  	$sitename = $config->get('name');
  	
  	//$hash = password_hash($booking->id, PASSWORD_DEFAULT);
  	$cancelHtml = '<a href="' . $_SERVER['REQUEST_SCHEME'] . '://' . $_SERVER['SERVER_NAME'] . '/booking/cancel?ref=' . $bookingid . '">Cancel Booking</a>';
  	$cancelText = $_SERVER['REQUEST_SCHEME'] . '://' . $_SERVER['SERVER_NAME'] . '/booking/cancel?ref=' . $bookingid;

	$email = $this->conn->query("SELECT data FROM restaurant_settings WHERE name = 'emailtemplate'")->fetchField(); 
	
	
	$HtmlEmail = str_replace('[firstname]', $booking->firstname, $email);
	$HtmlEmail = str_replace('[sitename]', $sitename, $HtmlEmail);
	$HtmlEmail = str_replace('[bookinginfo]', $htmlBody, $HtmlEmail);
	$this->htmlEmail = str_replace('[cancelurl]', $cancelHtml, $HtmlEmail);
	
	$TextEmail = str_replace('[firstname]', $booking->firstname, $email);
	$TextEmail = str_replace('[sitename]', $sitename, $TextEmail);
   $TextEmail = str_replace('[bookinginfo]', str_replace('<br>', '', $textBody), $TextEmail);
	$this->textEmail = str_replace('[cancelurl]', $cancelText, $TextEmail);
	$this->textEmail = strip_tags($this->textEmail);
	
	return $this;

	}

	/**
	 * Get the close time for a service
	 *
	 * @param string $date
	 * @param unknown_type $service
	 * @return string
	 */
	public function getServiceClose($date, $service)
	{
		$period = $this->service->getSetting('bookinglength');
		if ($period == 'entire') {
			$close = $this->getCloseTime($date, $service);
		} else {
			$time = strtotime("+ " . $this->service->getSetting('bookinglength'));
			$close = date('Y-m-d H:i:s', $time);
		}
		return $close;
	}

	/**
	 * get the closing time for a booking
	 *
	 * @param string $date
	 * @param string $service
	 * @return string
	 */
	public function getCloseTime($date, $service)
	{
		$times = $this->getTimes(date('Y-m-d', strtotime($date)));

		$close = $times[$service]['close'];

		if ($this->service->getSetting('bookuntil') == 'T') {
			if ($this->service->getSetting('bookinglength') != 'entire') {
				$time = strtotime($close);
				$close = date('Y-m-d H:i:s', strtotime("- " . $this->service->getSetting('bookinglength'), $time));
			}
		}

		return $close;
	}

	/**
	 *
	 * @param string $open booking open time
	 */
	protected function getBookingEnd($open)
	{

	}

	/**
	 * Get tables(s) for a booking
	 * TODO have getTables() try to match two or more tables if a single one is not available.
	 * @param string $open
	 * @param string $close
	 * @param string $guests
	 * @param string $service
	 * @return unknown 
	 */
	public function getTables($open, $close, $guests, $service)
	{
		//if (strtotime($close) > strtotime($this->getServiceClose(date('Y-m-d', strtotime($open)), $service))) {//}

		/*$sql = "SELECT restaurant_tables.id, restaurant_tables.number, restaurant_tables.name, restaurant_bookings_tables.start, restaurant_bookings_tables.end FROM restaurant_tables LEFT JOIN restaurant_bookings_tables ON
		restaurant_tables.id = restaurant_bookings_tables.table_id WHERE ((restaurant_bookings_tables.start IS NULL AND restaurant_bookings_tables.end IS NULL) OR
		((restaurant_bookings_tables.start NOT BETWEEN '{$open}' AND '{$close}') AND (restaurant_bookings_tables.end NOT BETWEEN '{$open}' AND '{$close}')
		AND '{$open}' NOT BETWEEN restaurant_bookings_tables.start AND restaurant_bookings_tables.end)) ORDER BY restaurant_tables.number ASC"; */
	   
	    
		
		$sql = "SELECT restaurant_tables.id, restaurant_tables.number, restaurant_tables.name, restaurant_bookings_tables.start, restaurant_bookings_tables.end FROM restaurant_tables LEFT JOIN restaurant_bookings_tables ON
		restaurant_tables.id = restaurant_bookings_tables.table_id WHERE (restaurant_bookings_tables.start NOT BETWEEN '{$open}' AND '{$close}') AND (restaurant_bookings_tables.end NOT BETWEEN '{$open}' AND '{$close}')
		AND ('{$open}' NOT BETWEEN restaurant_bookings_tables.start AND restaurant_bookings_tables.end) ORDER BY restaurant_tables.number ASC"; 

		$tables = $this->conn->query($sql)->fetchAll();
		
		$lowest = 100000;
		foreach ($tables as $key => $val) {
			if ($val->number >= $guests) {
				if ($val->number < $lowest) {
					$lowest = $val->number;
				}
			}
		}

		foreach ($tables as $key => $val) {
			if ($val->number == $lowest) {
				return $val->id;
			}
		}
		

		//TODO impliment multiple tables bookings
		if ($this->service->getSetting('multipletables') == 1) {

			if (! empty ($tables)) {
				$tmpguests = $guests + 2;
				$finish = (sizeof($tables) * 100);
				$findtables = array();
				for($i = 0; $i< $finish; $i++) {
					$table1 = rand(1, $guests);
					$table2 = rand(1, $guests);
					$capacity = $table1 + $table2;
					if ($capacity <= $tmpguests) {
						$dbtable2 = '';
						$dbtable1 = '';
						foreach ($tables as $key => $val) {
							if ($val->number == $table1) {
								$dbtable1 = $val;
								break;
							}
						}
						foreach ($tables as $key => $val) {
							if ($val->number == $table2) {
								$dbtable2 = $val;
								break;
							}
						}
						if ((! empty ($dbtable1)) && (! empty ($dbtable2)) && ($dbtable1->id != $dbtable2->id)) {
							$total = $dbtable1->number + $dbtable2->number;
							$findtables[] = array('number' => $total, 'tables' => array($dbtable1->id, $dbtable2->id));
						}
					}
				}

				if (! empty ($findtables)) {

					$return = false;
					$lowest = 100000;
					foreach ($findtables as $key => $val) {
						if ($val['number'] < $lowest) {
							$lowest = $val['number'];
							break;
						}
					}

					foreach ($findtables as $key => $val) {
						if ($val['number'] == $lowest) {
							$return = $val['tables'];
							break;
						}
					}

					return $return;
				}

			}
		} 
		return false;
	}





	/**
	 * Loops through times and removes those without an available table(s)
	 * @param unknown_type $times
	 * @param unknown_type $date
	 * @param int guests
	 */
	public function filterTimes(&$times, $date, $guests)
	{
		//$date = (! is_int($date)) ? date('Y-m-d', strtotime($date)) : date('Y-m-d', $date);
		$date = $this->setDate($date);
		
		foreach ($times as $key => $val) {
			
			$close = $this->getCloseTime($date, $key);
			
			$serviceclose = $this->getServiceClose($date, $key);
			
			
			foreach ($val['Times'] as $k => $v) {
				$open = date('Y-m-d H:i:s', strtotime($date . ' ' . $v));
				
				$tables = $this->getTables($open, $close, $guests, $key);
				
				
				if (empty ($tables)) {
					unset ($times[$key]['Times'][$k]);
				}
			}
			if (empty ($times[$key]['Times'])) {
				unset ($times[$key]);
			}
		}
		
	}
	
	/**
	* return a formated date (Y-m-d) for a date provided or a timestamp
	*/
	private function setDate($date)
	{
		return (! is_int($date)) ? date('Y-m-d', strtotime($date)) : date('Y-m-d', $date);
	}

	/*public function getTimesByService($exceptions = false)
	{
		if (! $exceptions) {
			$service = $this->db->fetchCol("SELECT DISTINCT name FROM restaurant_opening_times");
			foreach ($service as $val) {

			}
		}

	} */


	/**
	 * Get the service times for $date in 15 minute intervals
	 * @param string $date
	 * @param $all return all times for a day (even times in past)
	 * @return multitype:|multitype:multitype:NULL  multitype:unknown
	 */
	public function getTimes($date, $all = false)
	{

		$date = $this->setDate($date);
		
		$date = $date . ' 23:59:59';

		if (! $all) {

			if (strtotime($date) < time()) {
				return array();
			}
		}

		$days = \Drupal\restaurant\Date\Date::getDays();
		

		foreach ($days as $key => $val) {
			$day = date('l', strtotime($date));
			if ($day == $val) {
				$day = $val;
				break;
			}
		}
		
		

		$sessions = $this->conn->query("SELECT name FROM restaurant_opening_times WHERE day = ? ORDER BY open ASC", array($day))->fetchCol();
		

		$date = date('Y-m-d', strtotime($date));
		$return = array();
		foreach ($sessions as $key => $val) {
			$return[$val] = $this->getBookingTimes($val, $day, $date, $all);
		}

		$date = date('Y-m-d', strtotime($date));
		$exceptions_open = $this->conn->query("SELECT name FROM restaurant_opening_exceptions WHERE date(from_date) = ? AND type = 'open'", array($date))->fetchCol();
		foreach ($exceptions_open as $key => $val) {
			$return[$val] = $this->getOpenExceptions($date, $return, $val);
		}

		$this->setClosedExceptions($date, $return);
		$times =  $return;
		

		if (!$all) {
			foreach ($times as $key => $val) {
				if (date('Y-m-d') == $date) {
					if (! empty ($val['Times'])) {
						foreach ($val['Times'] as $k => $v) {
							$time = strtotime($date . ' ' . $v);
							$offset = $this->service->getSetting('takebookingsuntil');
							if ($offset != 'any') {
								$bookingtime = strtotime("- " . $offset, $time);
								if (time() > $bookingtime) {
									unset ($times[$key]['Times'][$k]);
								}
							} else {
								if (time() > $time) {
									if (isset ($times[$key]['Times'][$k])) {
										unset ($times[$key]['Times'][$k]);
									}

								}
							}

						}
					}
				}
				if (empty ($times[$key]['Times'])) {
					unset ($times[$key]);
				}
			}
		}
		
		return $times;
	}

	/**
	 * Return the opening times for a service time
	 * @param string $open date
	 * @return array
	 */
	public function getSessionTimes($open)
	{
		$times = $this->getTimes(date('Y-m-d', strtotime($open)), true);
		$time = date('g:i A', strtotime($open));
		foreach ($times as $key => $val) {
			foreach ($val['Times'] as $k => $v) {
				if ($v == $time) {
					return $val['Times'];
				}
			}
		}
	}

	/**
	 * override any available service times for $date if a closed exception is set
	 * @param string $date
	 * @param array $times2
	 */
	public function setClosedExceptions($date, &$times2)
	{
		$times = $this->conn->query("SELECT * FROM restaurant_opening_exceptions WHERE DATE(from_date) = ? AND type = 'close'", array($date))->fetch();
		$time = strtotime("00:00");
		$return = array();
		if (! empty ($times)) {
			for ($i = 1; $i< 97; $i++) {
				$to_date = strtotime(date('H:i', strtotime($times->to_date)));
				$close = strtotime($times->to_date);
				$length = $this->service->getSetting('bookinglength');
				if ($length != 'entire') {
					if ($this->service->getSetting('bookuntil') == 'T'){
						$close = strtotime("- " . $this->service->getSetting('bookinglength'), strtotime($times->to_date));
					}
				}
				$open = strtotime(date('H:i', strtotime($times->from_date)));
				if (($time >= $open) && ($time <= $close)) {
					$return['Times'][] = $this->date->getTime(array('date' => $time));
					$return['close'] = $times->to_date;
				}
				$time = strtotime("+ 15 minutes",$time);
			}
		}

		if (isset ($return['Times'])) {
			foreach ($return['Times'] as $key => $val) {
				foreach ($times2 as $k => $v) {
					foreach ($v['Times'] as $m => $j) {
						if ($val == $j) {
							unset ($times2[$k]['Times'][$m]);
						}

					}
				}
			}
		}
	}

	/**
	 * Setup opening times if there is an opening exception for $date.
	 * @param string $date
	 * @param array $times2
	 * @param string $name
	 * @return multitype:unknown
	 */
	public function getOpenExceptions($date, &$times2, $name)
	{
		$times = $this->conn->query("SELECT * FROM restaurant_opening_exceptions WHERE name = ? AND DATE(from_date) = ? AND type = 'open'", array($name, $date))->fetch();

		$time = strtotime("00:00");
		$return = array();
		for ($i = 1; $i< 97; $i++) {
			$to_date = strtotime(date('H:i', strtotime($times->to_date)));
			$close = strtotime($times->to_date);
			$length = $this->service->getSetting('bookinglength');
			if ($length != 'entire') {
				if ($this->service->getSetting('bookuntil') == 'T'){
					$close = strtotime("- " . $this->service->getSetting('bookinglength'), strtotime($times->to_date));
				}
			}
			$open = strtotime(date('H:i', strtotime($times->from_date)));
			if (($time >= $open) && ($time <= $close)) {
				$return['Times'][] = $this->date->getTime(array('date' => $time));
				$return['close'] = $times->to_date;
			}
			$time = strtotime("+ 15 minutes",$time);
		}
		foreach ($return['Times'] as $key => $val) {
			foreach ($times2 as $k => $v) {
				foreach ($v['Times'] as $m => $j) {
					if ($val == $j) {
						unset ($times2[$k]['Times'][$m]);
					}

				}
			}
		}
		return $return;
	}

	/**
	 * return the bookings times for $date from the database
	 * @param string $name
	 * @param string $day
	 * @param string $date
	 * @return multitype:NULL
	 */
	public function getBookingTimes($name, $day, $date, $all = false)
	{
		$times = $this->conn->query("SELECT * FROM restaurant_opening_times WHERE name = ? AND day = ?", array($name, $day))->fetch();
		
		$time = strtotime("00:00");
		$return = array();
		for ($i = 1; $i < 97; $i++) {
			$close = strtotime($times->close);
			$length = $this->service->getSetting('bookinglength');
			if ($length != 'entire') {
				if ($this->service->getSetting('bookuntil') == 'T') {
					$close = strtotime("- " . $this->service->getSetting('bookinglength'), strtotime($times->close));
				}
			}
			if ($all) {
				$close = strtotime($times->close);
			}
			if (($time >= strtotime($times->open)) && ($time <= $close)) {
				$return['Times'][] = $this->date->getTime(array('date' => $time));
				$return['close'] = date('Y-m-d H:i:s', strtotime($date . ' ' . $times->close));
			}
			$time = strtotime("+ 15 minutes", $time);
		}
		return $return;
	}

	/**
	 * Return all bookings for a table and date
	 * @param string $table the table id
	 * @param string $date
	 * @return array
	 */
	public function getBookings($table, $date)
	{
		$sql = "SELECT restaurant_bookings_tables.*, restaurant_bookings.lastname, restaurant_bookings.firstname FROM restaurant_bookings_tables LEFT JOIN restaurant_bookings ON restaurant_bookings_tables.booking_id = restaurant_bookings.id WHERE table_id = ? AND DATE(start) = ?";
		$bookings = $this->conn->query($sql, array($table, $date))->fetchAll();
		return $bookings;
	}
}
