<?php
/** 
 * @package     VikRestaurants
 * @subpackage  core
 * @author      E4J s.r.l.
 * @copyright   Copyright (C) 2023 E4J s.r.l. All Rights Reserved.
 * @license     http://www.gnu.org/licenses/gpl-2.0.html GNU/GPL
 * @link        https://vikwp.com
 */

// No direct access
defined('ABSPATH') or die('No script kiddies please!');

/**
 * Helper class used to perform some availability checks
 * when trying to search for a free table.
 *
 * @since 1.8
 */
class VREAvailabilitySearch extends JObject
{
	/**
	 * A list of all room tables.
	 *
	 * @var array
	 */
	protected static $_tables = null;

	/**
	 * A lookup to count all the reservations per time slot on a given date.
	 * 
	 * @var array
	 * @since 1.11
	 */
	protected static $_reservations = [];

	/**
	 * The availability search date.
	 * The date must be formatted according to the format
	 * specified in configuration.
	 *
	 * @var string
	 */
	protected $date;

	/**
	 * The availability search hour (24H format).
	 *
	 * @var integer
	 */
	protected $hour;

	/**
	 * The availability search minute.
	 *
	 * @var integer
	 */
	protected $min;

	/**
	 * The availability search people.
	 *
	 * @var integer
	 */
	protected $people;

	/**
	 * Whether the system should return only the tables belonging to the selected room.
	 * 
	 * @var int|null
	 * @since 1.11
	 */
	protected $room;

	/**
	 * Flag used to check whether the user is an administrator.
	 *
	 * @var boolean
	 */
	protected $admin;

	/**
	 * A forced time of stay expressed in minutes.
	 *
	 * @var integer
	 */
	protected $staytime;

	/**
	 * A list of allowed statuses that represents a valid reservation.
	 *
	 * @var array
	 */
	protected $statuses = [];

	/**
	 * Class constructor.
	 *
	 * @param 	mixed 	 $date    Either a date string or a UNIX timestamp.
	 * @param 	mixed 	 $time    Either a hourmin string (e.g. 20:30) or an array
	 * 							  containing the hour and the minutes.
	 * @param 	integer  $people  The number of participants.
	 * @param 	boolean  $admin   True to exclude publishing filters. If not specified,
	 * 							  this value will be based on the current client.
	 *
	 * @uses 	setDate()
	 * @uses 	setTime()
	 * @uses 	setPeople()
	 * @uses 	setAdmin()
	 */
	public function __construct($date, $time, $people = 2, $admin = null)
	{
		// set arguments
		$this->setDate($date)
			->setTime($time)
			->setPeople($people)
			->setAdmin($admin);

		/**
		 * Auto-detect all the status codes that can be used to reserve a table.
		 * 
		 * @since 1.9
		 */
		$this->statuses = JHtml::fetch('vrehtml.status.find', 'code', ['restaurant' => 1, 'reserved' => 1]);

		if (!$this->statuses)
		{
			throw new RuntimeException('Detected a misconfiguration of the status codes', 500);
		}
	}

	/**
	 * Updates the searched date.
	 *
	 * @param 	mixed 	Either a date string or a UNIX timestamp.
	 *
	 * @return 	self 	This object to support chaining.
	 */
	public function setDate($date)
	{
		if (preg_match("/^\d+$/", $date))
		{
			// we have a UNIX timestamp, convert it to a formatted date
			$date = date(VREFactory::getConfig()->get('dateformat'), $date);
		}

		// register property
		$this->set('date', $date);

		return $this;
	}

	/**
	 * Updates the searched time.
	 *
	 * @param 	mixed 	$time   Either a hourmin string (e.g. 20:30) or an array
	 * 							containing the hour and the minutes. In case of array,
	 * 						    it must specify the "hour" and "min" attributes.
	 *
	 * @return 	self 	This object to support chaining.
	 */
	public function setTime($time)
	{
		if ($time)
		{
			if (is_array($time))
			{
				// extract hour and minutes from array
				extract($time);
			}
			else
			{
				// explode hour and minutes from time string
				list($hour, $min) = explode(':', $time);
			}

			// register properties
			$this->set('hour', (int) $hour);
			$this->set('min' , (int) $min );
		}

		return $this;
	}

	/**
	 * Updates the searched number of people.
	 *
	 * @param 	integer  $people  The number of participants.
	 *
	 * @return 	self 	 This object to support chaining.
	 */
	public function setPeople($people)
	{
		// do not accept a number lower than 1
		$this->set('people', max(array(1, $people)));

		return $this;
	}

	/**
	 * Sets the room filter.
	 *
	 * @param 	integer  $roomId  The roomID.
	 *
	 * @return 	self 	 This object to support chaining.
	 */
	public function setRoom($roomId)
	{
		$this->set('room', (int) $roomId);

		return $this;
	}

	/**
	 * Sets the permissions of the user (admin or customer).
	 *
	 * @param 	boolean  $admin  True to exclude publishing filters. If not specified,
	 * 							 this value will be based on the current client.
	 *
	 * @return 	self 	 This object to support chaining.
	 */
	public function setAdmin($admin)
	{
		if (is_null($admin))
		{
			// check current client
			$admin = JFactory::getApplication()->isClient('administrator');
		}

		// register property
		$this->set('admin', (bool) $admin);

		return $this;
	}

	/**
	 * Checks whether the user is an administrator.
	 *
	 * @return 	boolean
	 */
	public function isAdmin()
	{
		return (bool) $this->get('admin', false);
	}

	/**
	 * Forces the time of stay when checking the availability of a specific reservation.
	 *
	 * @param 	integer  $min  The time of stay in minutes.
	 *
	 * @return 	self 	 This object to support chaining.
	 *
	 * @since 	1.8.2
	 */
	public function setStayTime($min)
	{
		// register property
		$this->set('staytime', abs((int) $min));

		return $this;
	}

	/**
	 * Returns the specified time of stay or the default one, in case it was not forced.
	 *
	 * @return 	integer
	 * 
	 * @since 	1.8.2
	 */
	public function getStayTime($default = null)
	{
		// get specified time of stay
		$staytime = (int) $this->get('staytime', 0);

		// make sure the value is valid
		if ($staytime <= 0)
		{
			// use the default value
			$staytime = (int) $default;
		}

		/**
		 * Trigger an event to allow third-party plugins to alter the stay time at runtime.
		 * 
		 * @param   int                    $stayTime  The current default value.
		 * @param   VREAvailabilitySearch  $search    The instance holding the booking details.
		 * 
		 * @return  int  The preferred stay time.
		 * 
		 * @since   1.11
		 */
		$result = VREFactory::getPlatform()->getDispatcher()->filter('onUsePreferredStayTime', [$staytime, $this]);

		/** @var E4J\VikRestaurants\Event\EventResponse $result */

		if ($result->has())
		{
			// get first acceptable value
			$staytime = (int) $result->first();
		}

		// validate again
		if ($staytime <= 0)
		{
			// use the configuration value
			$staytime = VREFactory::getConfig()->getUint('averagetimestay');
		}

		return $staytime;
	}

	/**
	 * Returns the details of the specified tables.
	 *
	 * @param 	array  $ids  The array of IDs.
	 *
	 * @return 	array  The table details list.
	 */
	public function getTables(array $ids = [])
	{
		$tmp = [];

		// load all the existing tables
		$tables = self::loadTables();

		$roomId = $this->get('room');

		/**
		 * Preserve the ordering provided by the availability results,
		 * because the first available table is always the most optimal.
		 * 
		 * @since 1.9
		 */
		foreach ($ids as $id)
		{
			if (!isset($tables[$id]))
			{
				// table not included in search results
				continue;
			}

			/**
			 * Ignore the tables that don't belong to the selected room, if any.
			 * 
			 * @since 1.11
			 */
			if ($roomId && $tables[$id]->id_room != $roomId)
			{
				// the table doesn't belong to the selected room
				continue;
			}

			$tmp[] = $tables[$id];
		}

		return $tmp;
	}

	/**
	 * Returns the details of the tables that
	 * belong to the specified room.
	 *
	 * @param 	integer  $id  The ID of the room.
	 *
	 * @return 	array    The table details list.
	 */
	public function getRoomTables($id)
	{
		$tmp = array();

		// iterate tables
		foreach (self::loadTables() as $table)
		{
			// check if the table room matches the specified one
			if ($table->id_room == $id)
			{
				$tmp[] = $table;
			}
		}

		return $tmp;
	}

	/**
	 * Returns the details of the specified table.
	 *
	 * @param 	integer  $id  The table ID.
	 *
	 * @return 	mixed    The details of the table if exists, null otherwise.
	 *
	 * @uses 	getTables()
	 */
	public function getTable($id)
	{
		// get tables list
		$tables = $this->getTables(array((int) $id));

		// return first element found (NULL in case it was not found)
		return array_shift($tables);
	}

	/**
	 * Returns the minimum and maximum capacity supported by
	 * the given cluster of tables.
	 *
	 * @param 	array 	$cluster  A list of table IDs.
	 * @param 	mixed 	$min      The total number of tables that will
	 * 							  be used to sum the minimum capacity.
	 *
	 * @return 	object  An object containing the min and max capacity.
	 *
	 * @uses 	getTables()
	 */
	public function getClusterCapacity(array $cluster, $min = null)
	{
		$capacity = new stdClass;
		$capacity->min = array();
		$capacity->max = 0;

		foreach ($this->getTables($cluster) as $t)
		{
			// sum table maximum capacity to total
			$capacity->max += (int) $t->max_capacity;

			// push minimum capacity within the list
			$capacity->min[] = (int) $t->min_capacity;
		}

		if (is_null($min))
		{
			// sum all capacities
			$capacity->min = array_sum($capacity->min);
		}
		else
		{
			// sort minimum capacities (ASC)
			sort($capacity->min, SORT_NUMERIC);

			// sum the first N ($min) lowest minimum capacities
			$capacity->min = array_sum(array_splice($capacity->min, 0, $min));
		}

		return $capacity;
	}

	/**
	 * Calculates the distance among the tables of the specified cluster.
	 * 
	 * @param   int[]  $cluster  A list of table IDs.
	 * 
	 * @return  float  The total distance.
	 * 
	 * @since   1.11
	 */
	protected function getClusterDistance(array $cluster)
	{
		$points = [];

		// calculate the center position of all the tables
		foreach ($this->getTables($cluster) as $t)
		{
			$center = new stdClass;
			$center->x = ($t->design_data->posx ?? 0) + ($t->design_data->width ?? 0) / 2;
			$center->y = ($t->design_data->posy ?? 0) + ($t->design_data->height ?? 0) / 2;

			$points[] = $center;
		}

		// sort the points graphically
		usort($points, function($a, $b) {
			if ($a->y < $b->y) {
				return -1;
			} else if ($a->y > $b->y) {
				return 1;
			}

			if ($a->x < $b->x) {
				return -1;
			} else if ($a->x > $b->x) {
				return 1;
			}

			return 0;
		});

		$distance = 0;

		// iterate the points one by one
		for ($i = 0; $i < count($points) - 1; $i++)
		{
			$a = $points[$i];
			$b = $points[$i + 1];

			// calculate the distance between the current point and the next one
			$distance += sqrt(pow($a->x - $b->x, 2) + pow($a->y - $b->y, 2));
		}

		return $distance;
	}

	/**
	 * Compares the occupancy stats of the new cluster against the optimal one.
	 * 
	 * This method prioritizes the following attributes:
	 * 
	 * - the lowest number of available seats;
	 * - the lowest number of occupied tables;
	 * - the lowest distance among the tables.
	 *
	 * @param   int    $prevSeats     The lowest number of seats currently (at the moment).
	 * @param   int    $newSeats      The number of seats belonging to the current cluster.
	 * @param   int    $prevTables    The lowest number of reserved tables (at the moment).
	 * @param   int    $newTables     The number of tables belonging to the current cluster.
	 * @param   float  $prevDistance  The lowest distance among the tables of the cluster (at the moment).
	 * @param   float  $newDistance   The distance among the tables of the current cluster.
	 * 
	 * @return  int    Returns 0 if the both the clusters share the same stats. Returns 1 if the new cluster is more optimal.
	 *                 Returns -1 if the new cluster is less optimal.
	 * 
	 * @since   1.11
	 */
	protected function compareClusters(?int $prevSeats, int $newSeats, ?int $prevTables, int $newTables, ?float $prevDistance, float $newDistance)
	{
		if (is_null($prevSeats))
		{
			return 1;
		}

		// prioritize the number of clusters with the lowest number of seats
		if ($newSeats > $prevSeats)
		{
			// the maximum number of seats of the new combination is higher than the previous one, ignore 
			return -1;
		}
		else if ($newSeats < $prevSeats)
		{
			// the maximum number of seats of the new combination is lower than the previous one, keep it
			return 1;
		}

		// same number of seats, move to the next condition (locked tables)
		if ($newTables > $prevTables)
		{
			// the maximum number of resources (tables) of the new combination is higher than the previous one, ignore 
			return -1;
		}
		else if ($newTables < $prevTables)
		{
			// the maximum number of resources (tables) of the new combination is lower than the previous one, keep it
			return 1;
		}

		/**
		 * This value represents the margin of error to take into account, since the tables might not be perfectly aligned
		 * to the millimeter. It would not make sense to exclude table combinations due to a deviation of just a few pixels.
		 */
		$distanceThreshold = 1.5;

		// same number of tables, move to the next condition (distance)
		if ($newDistance > $prevDistance * $distanceThreshold)
		{
			// the calculated distance of the new combination is higher than the previous one, ignore 
			return -1;
		}
		else if ($newDistance * $distanceThreshold < $prevDistance)
		{
			// the calculated distance of the new combination is lower than the previous one, keep it
			return 1;
		}

		// same resources
		return 0;
	}

	/**
	 * Finds all the possible combination of a cluster able to
	 * host the specified number of people.
	 *
	 * @param 	array 	$cluster  A list of tables.
	 *
	 * @return 	array 	A list of sub-clusters.
	 */
	protected function generateClustersCombination(array $cluster)
	{
		// initialize by adding the empty set to start the inner loop
		$results = array(array());

		// iterate all elements within the list
		foreach ($cluster as $element)
		{
			// Recursively generate a combination of the existing
			// elements and the current one. The foreach won't cause
			// a loop because, even if we are injecting new elements
			// within the source array ($results), its copy is used
			// as iterator.
			foreach ($results as $combination)
			{
				// extend combination
				$results[] = array_merge(array($element), $combination);
			}
		}

		// pop first empty element
		array_shift($results);

		$clusters = array();

		foreach ($results as $r)
		{
			// get cluster capacity
			$capacity = $this->getClusterCapacity($r);

			// make sure the cluster supports the requested capacity
			if ($capacity->min <= $this->people && $this->people <= $capacity->max)
			{
				// keep generated combination
				$clusters[] = $r;
			}
		}

		return $clusters;
	}

	/**
	 * Finds the most optimal combination that can handle the required
	 * number of people within the given cluster tables.
	 * 
	 * This method takes the cluster with the lowest count of wasted seats.
	 * Among a cluster with a waste of 2 seats (10 seats of 12) and a cluster
	 * with a waste of 4 seats (10 seats of 14), the first one will be taken.
	 *
	 * In case of a tie in terms of waste, the method will take the cluster
	 * with the lowest number of used resources (tables). Among a cluster that
	 * uses 3 tables to reach a capacity of 12 seats and a cluster with 2 tables
	 * to reach the same capacity, the second one will be taken.
	 *
	 * @param 	array 	$clusters  A list of clusters (bi-dimensional).
	 *
	 * @return 	array 	The best table combinations.
	 * 
	 * @since   1.11    The method now returns an array of combinations.
	 */
	protected function findClusterOptimalCombination(array $clusters)
	{
		$comb  = [];
		$waste = null;
		$dist  = null;

		// iterate all combinations found
		foreach ($clusters as $cluster)
		{
			// get cluster capacity
			$capacity = $this->getClusterCapacity($cluster);

			// make sure the cluster supports the requested capacity
			if ($capacity->min <= $this->people && $this->people <= $capacity->max)
			{
				// calculate waste (number of seats that are going to be lost)
				$tmp = $capacity->max - $this->people;

				// calculate the distance among the tables of this cluster
				$distance = $this->getClusterDistance($cluster);

				// check whether the new cluster is optimal
				$cmpResult = $this->compareClusters(
					$waste,                            // previous total seats
					$tmp,                              // new total seats
					$comb ? count($comb[0]) : null,    // previous total tables
					count($cluster),                   // new total tables
					$dist,                             // previous distance
					$distance                          // new distance
				);

				// register the cluster in case the stats of the new cluster are equal or higher than the previous one
				if ($cmpResult >= 0)
				{
					if ($cmpResult == 1)
					{
						// found a temporary optimal cluster, reset the array
						$comb = [];	
					}

					// push the new cluster within the array
					$comb[] = $cluster;
					// register new waste
					$waste = $tmp;
					// register new distance
					$dist = $distance;
				}
			}
		}

		return $comb;
	}

	/**
	 * CHECK IF TABLE IS AVAILABLE
	 */

	/**
	 * Checks whether the specified table is available for the searched arguments.
	 *
	 * @param 	integer  $id_table  The table to check for.
	 * @param 	integer  $id_res    The reservation ID to exclude while checking.
	 * @param 	array 	 &$cluster  A list containing all the tables that needs to
	 * 								be booked in addition to the specified one.
	 *
	 * @return 	boolean  True if the table is available, false otherwise.
	 *
	 * @uses 	isDefaultTableAvailable()
	 * @uses 	isSharedTableAvailable()
	 */
	public function isTableAvailable($id_table, $id_res = null, &$cluster = null)
	{
		/**
		 * We can no longer accept reservations in case there are too many arrivals at
		 * the selected date and time.
		 * 
		 * @since 1.11
		 */
		if (!$this->checkArrivals())
		{
			return false;
		}

		// get table details
		$table = $this->getTable($id_table);

		if (!$table)
		{
			// table not found
			return false;
		}

		if ($table->multi_res)
		{
			// check whether the SHARED table if available
			return $this->isSharedTableAvailable($id_table, $id_res);
		}

		if ($table->min_capacity <= $this->people && $this->people <= $table->max_capacity)
		{

			// use default availability check
			return $this->isDefaultTableAvailable($id_table, $id_res);
		}

		// check if the specified table is available as a cluster
		$cluster = $this->getAvailableClusters($id_res, $id_table);

		if ($cluster)
		{
			// take only the first one available
			$cluster = $cluster[0];

			// cluster available, find index of the requested table
			$index = array_search($id_table, $cluster);
			// remove table from cluster
			array_splice($cluster, $index, 1);

			return true;
		}

		// table is not available
		return false;
	}

	/**
	 * Checks whether the specified NON SHARED table is available for the searched arguments.
	 *
	 * @param 	integer  $id_table  The table to check for.
	 * @param 	integer  $id_res    The reservation ID to exclude while checking.
	 *
	 * @return 	boolean  True if the table is available, false otherwise.
	 */
	protected function isDefaultTableAvailable($id_table, $id_res = null)
	{
		$dbo = JFactory::getDbo();

		// get average time of stay
		$avg = VREFactory::getConfig()->getUint('averagetimestay');

		// get reservation duration
		$duration = $this->getStayTime($avg);

		// fetch check-in and check-out
		$in_ts  = VikRestaurants::createTimestamp($this->date, $this->hour, $this->min);
		$out_ts = VikRestaurants::createTimestamp($this->date, $this->hour, (int) $this->min + $duration);

		$q = $dbo->getQuery(true);

		// SELECT data
		$q->select('t.*');

		// load tables and rooms from DB
		$q->from($dbo->qn('#__vikrestaurants_table', 't'));
		$q->from($dbo->qn('#__vikrestaurants_room', 'rm'));

		// join the tables with the rooms
		$q->where($dbo->qn('t.id_room') . ' = ' . $dbo->qn('rm.id'));
		$q->where($dbo->qn('t.id') . ' = ' . (int) $id_table);

		// exclude multi reservation table
		$q->where($dbo->qn('t.multi_res') . ' = 0');

		/**
		 * Use publishing restrictions only for front-end users.
		 *
		 * @since 1.7.4
		 */
		if (!$this->isAdmin())
		{
			$q->where($dbo->qn('rm.published') . ' = 1');
			$q->where($dbo->qn('t.published') . ' = 1');
			$q->where((int) $this->people . ' BETWEEN ' . $dbo->qn('t.min_capacity') . ' AND ' . $dbo->qn('t.max_capacity'));
		}

		$availQuery = $dbo->getQuery(true)
			->select($dbo->qn('t.id')) 
			->from($dbo->qn('#__vikrestaurants_reservation', 'r')) 
			->where($dbo->qn('t.id') . ' = ' . $dbo->qn('r.id_table'))
			->where($dbo->qn('r.status') . ' IN (' . implode(',', array_map(array($dbo, 'q'), $this->statuses)) . ')')
			->andWhere(array(
				'(' . $dbo->qn('r.checkin_ts') . " <= {$in_ts}  AND {$in_ts}  <  " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
				'(' . $dbo->qn('r.checkin_ts') . " <  {$out_ts} AND {$out_ts} <= " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
				'(' . $dbo->qn('r.checkin_ts') . " <= {$in_ts}  AND {$out_ts} <= " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
				'(' . $dbo->qn('r.checkin_ts') . " >= {$in_ts}  AND {$out_ts} >= " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
			), 'OR');

		if ($id_res)
		{
			// ignore the specified reservation so that it won't be considered while
			// checking the availability of the table
			$availQuery->where($dbo->qn('r.id') . ' <> ' . (int) $id_res);
			$availQuery->where($dbo->qn('r.id_parent') . ' <> ' . (int) $id_res);
		}

		// exclude if reserved
		$q->where('NOT EXISTS(' . $availQuery . ')');

		$dbo->setQuery($q, 0, 1);
		$table = $dbo->loadObject();

		if (!$table)
		{
			// table not available
			return false;
		}

		if ($this->isAdmin())
		{
			// table available
			return true;
		}

		/**
		 * Check whether the table is available through the apposite framework.
		 * Only for front-end users.
		 * 
		 * @since 1.10
		 */
		return JModelVRE::getInstance('roomclosure')->isRoomOpen($table->id_room, $in_ts);
	}

	/**
	 * Checks whether the specified SHARED table is available for the searched arguments.
	 *
	 * @param 	integer  $id_table  The table to check for.
	 * @param 	integer  $id_res    The reservation ID to exclude while checking.
	 *
	 * @return 	boolean  True if the table is available, false otherwise.
	 */
	protected function isSharedTableAvailable($id_table, $id_res = null)
	{
		$dbo = JFactory::getDbo();

		// get average time of stay
		$avg = VREFactory::getConfig()->getUint('averagetimestay');

		// get reservation duration
		$duration = $this->getStayTime($avg);

		// fetch check-in and check-out
		$in_ts  = VikRestaurants::createTimestamp($this->date, $this->hour, $this->min);
		$out_ts = VikRestaurants::createTimestamp($this->date, $this->hour, (int) $this->min + $duration);

		// calculate total number of people hosted by the table
		$occupancyQuery = $dbo->getQuery(true)
			->select('SUM(' . $dbo->qn('r.people') . ')')
			->from($dbo->qn('#__vikrestaurants_reservation', 'r'))
			->where($dbo->qn('t.id') . ' = ' . $dbo->qn('r.id_table'))
			->where($dbo->qn('r.status') . ' IN (' . implode(',', array_map(array($dbo, 'q'), $this->statuses)) . ')')
			->andWhere(array(
				'(' . $dbo->qn('r.checkin_ts') . " <= {$in_ts}  AND {$in_ts}  <  " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
				'(' . $dbo->qn('r.checkin_ts') . " <  {$out_ts} AND {$out_ts} <= " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
				'(' . $dbo->qn('r.checkin_ts') . " <= {$in_ts}  AND {$out_ts} <= " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
				'(' . $dbo->qn('r.checkin_ts') . " >= {$in_ts}  AND {$out_ts} >= " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
			), 'OR');

		if ($id_res)
		{
			// ignore the specified reservation so that it won't be considered while
			// checking the availability of the table
			$occupancyQuery->where($dbo->qn('r.id') . ' <> ' . (int) $id_res);
			$occupancyQuery->where($dbo->qn('r.id_parent') . ' <> ' . (int) $id_res);
		}

		$q = $dbo->getQuery(true);

		// SELECT current number of people hosted in the table
		$q->select('IFNULL((' . $occupancyQuery . '), 0) AS ' . $dbo->qn('total'));
		$q->select($dbo->qn('t.min_capacity'));
		$q->select($dbo->qn('t.max_capacity'));
		$q->select($dbo->qn('t.id_room'));

		// load tables and rooms from DB
		$q->from($dbo->qn('#__vikrestaurants_table', 't'));
		$q->leftjoin($dbo->qn('#__vikrestaurants_room', 'rm') . ' ON ' . $dbo->qn('t.id_room') . ' = ' . $dbo->qn('rm.id'));

		// filter by table ID
		$q->where($dbo->qn('t.id') . ' = ' . (int) $id_table);

		// exclude single reservation table
		$q->where($dbo->qn('t.multi_res') . ' = 1');

		/**
		 * Use publishing restrictions only for front-end users.
		 *
		 * @since 1.7.4
		 */
		if (!$this->isAdmin())
		{
			$q->where($dbo->qn('rm.published') . ' = 1');
			$q->where($dbo->qn('t.published') . ' = 1');
		}

		// make sure the table is capable of hosting the specified number of people
		$q->having($dbo->qn('t.min_capacity') . ' <= ' . (int) $this->people);
		$q->having('(' . $dbo->qn('total') . ' + ' . (int) $this->people . ') <= ' . $dbo->qn('t.max_capacity'));

		$dbo->setQuery($q);
		$table = $dbo->loadObject();

		if (!$table)
		{
			// table not available
			return false;
		}

		if ($this->isAdmin())
		{
			// table available
			return true;
		}

		/**
		 * Check whether the table is available through the apposite framework.
		 * Only for front-end users.
		 * 
		 * @since 1.10
		 */
		return JModelVRE::getInstance('roomclosure')->isRoomOpen($table->id_room, $in_ts);
	}

	/**
	 * Returns a list of available tables for the searched arguments.
	 *
	 * @param 	integer  $id_res    The reservation ID to exclude while checking.
	 *
	 * @return 	array 	 A list of available tables.
	 *
	 * @uses 	getFreeTables()
	 * @uses 	getAvailableSharedTables()
	 */
	public function getAvailableTables($id_res = null)
	{
		/**
		 * We can no longer accept reservations in case there are too many arrivals at
		 * the selected date and time.
		 * 
		 * @since 1.11
		 */
		if (!$this->checkArrivals())
		{
			return [];
		}

		// get free tables first
		$tables = $this->getFreeTables($id_res);

		/**
		 * Always search for a cluster, otherwise certain rooms might
		 * not be able to host reservations as long as other rooms
		 * have free single tables.
		 *
		 * @since 1.8.3
		 */

		// find all the optimal clusters in each room
		foreach ($this->getAvailableClusters($id_res) as $cluster)
		{
			// merge tables found with the previous results
			$tables = array_merge($tables, $cluster);
		}

		// then get available shared tables
		$shared = $this->getAvailableSharedTables($id_res);

		// marge all fetched results
		$tables = array_merge($tables, $shared);
		// avoid duplicate records and reset keys
		$tables = array_values(array_unique($tables));

		// get table details
		$tables = $this->getTables($tables);

		return $tables;
	}

	/**
	 * Returns a list of tables that are not occupied for the searched arguments.
	 *
	 * @param 	integer  $id_res    The reservation ID to exclude while checking.
	 * @param 	boolean  $capacity  False to ignore the capacity of the table.
	 *
	 * @return 	array 	 A list of available tables.
	 */
	protected function getFreeTables($id_res = null, $capacity = true)
	{
		$dbo = JFactory::getDbo();

		// get average time of stay
		$avg = VREFactory::getConfig()->getUint('averagetimestay');

		// get reservation duration
		$duration = $this->getStayTime($avg);

		// fetch check-in and check-out
		$in_ts  = VikRestaurants::createTimestamp($this->date, $this->hour, $this->min);
		$out_ts = VikRestaurants::createTimestamp($this->date, $this->hour, (int) $this->min + $duration);

		$q = $dbo->getQuery(true);

		// SELECT data
		$q->select('t.*');

		// load tables and rooms from DB
		$q->from($dbo->qn('#__vikrestaurants_table', 't'));
		$q->from($dbo->qn('#__vikrestaurants_room', 'rm'));

		// join the tables with the rooms
		$q->where($dbo->qn('t.id_room') . ' = ' . $dbo->qn('rm.id'));

		/**
		 * Use publishing restrictions only for front-end users.
		 *
		 * @since 1.7.4
		 */
		if (!$this->isAdmin())
		{
			$q->where($dbo->qn('rm.published') . ' = 1');
			$q->where($dbo->qn('t.published') . ' = 1');
		}

		$availQuery = $dbo->getQuery(true)
			->select($dbo->qn('t.id')) 
			->from($dbo->qn('#__vikrestaurants_reservation', 'r')) 
			->where($dbo->qn('t.id') . ' = ' . $dbo->qn('r.id_table'))
			->where($dbo->qn('r.status') . ' IN (' . implode(',', array_map(array($dbo, 'q'), $this->statuses)) . ')')
			->andWhere(array(
				'(' . $dbo->qn('r.checkin_ts') . " <= {$in_ts}  AND {$in_ts}  <  " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
				'(' . $dbo->qn('r.checkin_ts') . " <  {$out_ts} AND {$out_ts} <= " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
				'(' . $dbo->qn('r.checkin_ts') . " <= {$in_ts}  AND {$out_ts} <= " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
				'(' . $dbo->qn('r.checkin_ts') . " >= {$in_ts}  AND {$out_ts} >= " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
			), 'OR');

		if ($id_res)
		{
			// ignore the specified reservation so that it won't be considered while
			// checking the availability of the table
			$availQuery->where($dbo->qn('r.id') . ' <> ' . (int) $id_res);
			$availQuery->where($dbo->qn('r.id_parent') . ' <> ' . (int) $id_res);
		}

		// exclude shared tables
		$q->where($dbo->qn('t.multi_res') . ' = 0');

		if ($capacity)
		{
			// make sure the table can host the given number of people
			$q->where((int) $this->people . ' BETWEEN ' . $dbo->qn('t.min_capacity') . ' AND ' . $dbo->qn('t.max_capacity'));
		}

		// exclude if reserved
		$q->where('NOT EXISTS(' . $availQuery . ')');

		// NON SHARED tables comes first
		$q->order($dbo->qn('t.multi_res') . ' ASC');
		// then take the tables with lower capacity
		$q->order($dbo->qn('t.max_capacity') . ' ASC');
		// finally use the rooms ordering
		$q->order($dbo->qn('rm.ordering') . ' ASC');
		// keep the natural ordering of the tables to avoid random results
		$q->order($dbo->qn('t.id') . ' ASC');
		
		$dbo->setQuery($q);
		$tables = $dbo->loadObjectList();

		if (!$this->isAdmin())
		{
			$roomClosureModel = JModelVRE::getInstance('roomclosure');

			/**
			 * Take only the tables that actually belong to an active room.
			 * Only for front-end users.
			 * 
			 * @since 1.10
			 */
			$tables = array_values(array_filter($tables, function($table) use ($roomClosureModel, $in_ts) {
				return $roomClosureModel->isRoomOpen($table->id_room, $in_ts);
			}));
		}

		// preserve only the ID of the table
		$tables = array_map(function($table) {
			return $table->id;
		}, $tables);
		
		return $tables;
	}

	/**
	 * Returns a list of SHARED tables that are still available for the searched arguments.
	 *
	 * @param 	integer  $id_res    The reservation ID to exclude while checking.
	 *
	 * @return 	array 	 A list of available tables.
	 */
	protected function getAvailableSharedTables($id_res = null)
	{
		$dbo = JFactory::getDbo();

		// get average time of stay
		$avg = VREFactory::getConfig()->getUint('averagetimestay');

		// get reservation duration
		$duration = $this->getStayTime($avg);

		// fetch check-in and check-out
		$in_ts  = VikRestaurants::createTimestamp($this->date, $this->hour, $this->min);
		$out_ts = VikRestaurants::createTimestamp($this->date, $this->hour, (int) $this->min + $duration);

		// calculate total number of people hosted by the table
		$occupancyQuery = $dbo->getQuery(true)
			->select('SUM(' . $dbo->qn('r.people') . ')')
			->from($dbo->qn('#__vikrestaurants_reservation', 'r'))
			->where($dbo->qn('t.id') . ' = ' . $dbo->qn('r.id_table'))
			->where($dbo->qn('r.status') . ' IN (' . implode(',', array_map(array($dbo, 'q'), $this->statuses)) . ')')
			->andWhere(array(
				'(' . $dbo->qn('r.checkin_ts') . " <= {$in_ts}  AND {$in_ts}  <  " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
				'(' . $dbo->qn('r.checkin_ts') . " <  {$out_ts} AND {$out_ts} <= " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
				'(' . $dbo->qn('r.checkin_ts') . " <= {$in_ts}  AND {$out_ts} <= " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
				'(' . $dbo->qn('r.checkin_ts') . " >= {$in_ts}  AND {$out_ts} >= " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
			), 'OR');

		if ($id_res)
		{
			// ignore the specified reservation so that it won't be considered while
			// checking the availability of the table
			$occupancyQuery->where($dbo->qn('r.id') . ' <> ' . (int) $id_res);
			$occupancyQuery->where($dbo->qn('r.id_parent') . ' <> ' . (int) $id_res);
		}

		$q = $dbo->getQuery(true);

		// SELECT current number of people hosted in the table
		$q->select('IFNULL((' . $occupancyQuery . '), 0) AS ' . $dbo->qn('total'));
		$q->select($dbo->qn('t.min_capacity'));
		$q->select($dbo->qn('t.max_capacity'));
		$q->select($dbo->qn('t.id'));
		$q->select($dbo->qn('t.id_room'));

		// load tables and rooms from DB
		$q->from($dbo->qn('#__vikrestaurants_table', 't'));
		$q->leftjoin($dbo->qn('#__vikrestaurants_room', 'rm') . ' ON ' . $dbo->qn('t.id_room') . ' = ' . $dbo->qn('rm.id'));

		// exclude single reservation table
		$q->where($dbo->qn('t.multi_res') . ' = 1');

		/**
		 * Use publishing restrictions only for front-end users.
		 *
		 * @since 1.7.4
		 */
		if (!$this->isAdmin())
		{
			$q->where($dbo->qn('rm.published') . ' = 1');
			$q->where($dbo->qn('t.published') . ' = 1');
		}

		// make sure the table is capable of hosting the specified number of people
		$q->having($dbo->qn('t.min_capacity') . ' <= ' . (int) $this->people);
		$q->having('(' . $dbo->qn('total') . ' + ' . (int) $this->people . ') <= ' . $dbo->qn('t.max_capacity'));

		$dbo->setQuery($q);
		$tables = $dbo->loadObjectList();

		if (!$this->isAdmin())
		{
			$roomClosureModel = JModelVRE::getInstance('roomclosure');

			/**
			 * Take only the tables that actually belong to an active room.
			 * Only for front-end users.
			 * 
			 * @since 1.10
			 */
			$tables = array_values(array_filter($tables, function($table) use ($roomClosureModel, $in_ts) {
				return $roomClosureModel->isRoomOpen($table->id_room, $in_ts);
			}));
		}

		// preserve only the ID of the table
		$tables = array_map(function($table) {
			return $table->id;
		}, $tables);
		
		return $tables;
	}

	/**
	 * Returns a list of tables that can be merged together in
	 * order to cover the specified number of participants.
	 *
	 * @param 	integer  $id_res    The reservation ID to exclude while checking.
	 * @param 	integer  $id_table  If specified, the cluster must contain the
	 * 								specified table ID.
	 *
	 * @return 	array    A list containing the clusters.
	 */
	protected function getAvailableClusters($id_res = null, $id_table = null)
	{
		// return a list of tables available for the selected
		// date and time (skip capacity restrictions)
		$free = $this->getFreeTables($id_res, $capacity = false);

		// get details of the tables found
		$freeTables = $this->getTables($free);

		$clusters = array();

		// get available clusters
		foreach ($freeTables as $t)
		{
			// register cluster if not empty and it hasn't been
			// included yet within the list
			if ($t->cluster && !in_array($t->cluster, $clusters))
			{
				// in case the table ID is specified, make sure that the
				// cluster contains the given ID
				if (is_null($id_table) || in_array($id_table, $t->cluster))
				{
					// strip tables (from cluster) that are not available
					$clusters[] = array_values(array_filter($t->cluster, function($t2) use ($free)
					{
						// make sure the table is currently available
						return in_array($t2, $free);
					}));
				}
			}
		}

		$self = $this;

		// take only the clusters that support the requested number of people
		$clusters = array_values(array_filter($clusters, function($cluster) use ($self)
		{
			// Get minimum and maximum capacity of the cluster.
			// 2 indicates the minimum number of tables needed to
			// form a cluster.
			$capacity = $self->getClusterCapacity($cluster, 2);
			$people   = (int) $self->get('people', 0);

			// make sure the selected number of people stays between the
			// cluster capacity range
			return $capacity->min <= $people && $people <= $capacity->max;
		}));

		if (!$clusters)
		{
			// no clusters found, don't need to go ahead
			return array();
		}

		$rooms = array();

		// decompose found clusters in sub-clusters to have
		// all the possible combinations of tables
		foreach ($clusters as $cluster)
		{
			// get all combinations
			$list = $this->generateClustersCombination($cluster);

			// join previous combinations with the new ones
			foreach ($list as $tmp)
			{
				// in case the table ID is specified, make sure that the
				// combination contains the given ID
				if (is_null($id_table) || in_array($id_table, $tmp))
				{
					// get room to which the first table of the cluster belongs
					$id_room = $this->getTable($tmp[0])->id_room;

					// split clusters in rooms
					if (!isset($rooms[$id_room]))
					{
						$rooms[$id_room] = array();
					}

					$rooms[$id_room][] = $tmp;
				}
			}
		}

		$tables = array();

		// iterate combinations
		foreach ($rooms as $id_room => $combinations)
		{
			// find the most optimal combination of tables for the current room
			$tables = array_merge($tables, $this->findClusterOptimalCombination($combinations));
		}

		if (!$tables)
		{
			// something went wrong...
			return array();
		}

		return $tables;
	}

	/**
	 * Counts the total number of participants for each table on
	 * the specified search arguments.
	 *
	 * @return 	array 	An associative array of occurrences.
	 */
	public function getTablesOccurrence()
	{
		$dbo = JFactory::getDbo();

		// get average time of stay
		$avg = VREFactory::getConfig()->getUint('averagetimestay');

		// get reservation duration
		$duration = $this->getStayTime($avg);

		// fetch check-in and check-out
		$in_ts  = VikRestaurants::createTimestamp($this->date, $this->hour, $this->min);
		$out_ts = VikRestaurants::createTimestamp($this->date, $this->hour, (int) $this->min + $duration);

		$rows = array();

		// calculate total number of people hosted by the table
		$q = $dbo->getQuery(true)
			->select($dbo->qn('r.id_table'))
			->select('SUM(' . $dbo->qn('r.people') . ') AS ' . $dbo->qn('total'))
			->from($dbo->qn('#__vikrestaurants_reservation', 'r'))
			->where($dbo->qn('r.id_parent') . ' <= 0')
			->where($dbo->qn('r.closure') . ' = 0')
			->where($dbo->qn('r.status') . ' IN (' . implode(',', array_map(array($dbo, 'q'), $this->statuses)) . ')')
			->andWhere(array(
				'(' . $dbo->qn('r.checkin_ts') . " <= {$in_ts}  AND {$in_ts}  <  " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
				'(' . $dbo->qn('r.checkin_ts') . " <  {$out_ts} AND {$out_ts} <= " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
				'(' . $dbo->qn('r.checkin_ts') . " <= {$in_ts}  AND {$out_ts} <= " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
				'(' . $dbo->qn('r.checkin_ts') . " >= {$in_ts}  AND {$out_ts} >= " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
			), 'OR')
			->group($dbo->qn('r.id_table'));

		$dbo->setQuery($q);

		// create associative array (TABLE ID -> COUNT)
		foreach ($dbo->loadObjectList() as $r)
		{
			$rows[$r->id_table] = (int) $r->total;
		}

		return $rows;
	}

	/**
	 * Returns the total number of seats available for booking.
	 * This is a sum of all the maximum capacities of the
	 * published tables in the requested period.
	 *
	 * @return 	integer  The total seats count.
	 */
	public function getSeatsCount()
	{
		$dbo = JFactory::getDbo();

		// get average time of stay
		$avg = VREFactory::getConfig()->getUint('averagetimestay');

		// get reservation duration
		$duration = $this->getStayTime($avg);

		// fetch check-in and check-out
		$in_ts  = VikRestaurants::createTimestamp($this->date, $this->hour, $this->min);
		$out_ts = VikRestaurants::createTimestamp($this->date, $this->hour, (int) $this->min + $duration);

		$q = $dbo->getQuery(true);

		// SELECT data
		$q->select('SUM(' . $dbo->qn('t.max_capacity') . ') AS ' . $dbo->qn('total'));

		// load tables and rooms from DB
		$q->from($dbo->qn('#__vikrestaurants_table', 't'));
		$q->from($dbo->qn('#__vikrestaurants_room', 'rm'));

		// join the tables with the rooms
		$q->where($dbo->qn('t.id_room') . ' = ' . $dbo->qn('rm.id'));

		// use publishing restrictions only for front-end users
		if (!$this->isAdmin())
		{
			$q->where($dbo->qn('rm.published') . ' = 1');
			$q->where($dbo->qn('t.published') . ' = 1');
		}
		
		$dbo->setQuery($q);
		
		// return total count
		return (int) $dbo->loadResult();
	}

	/**
	 * Returns a list of reservations for each table.
	 *
	 * @return 	array 	An associative array of reservations (Table -> Reservations[]).
	 */
	public function getReservations()
	{
		$dbo = JFactory::getDbo();

		// get average time of stay
		$avg = VREFactory::getConfig()->getUint('averagetimestay');

		// get reservation duration
		$duration = $this->getStayTime($avg);

		// fetch check-in and check-out
		$in_ts  = VikRestaurants::createTimestamp($this->date, $this->hour, $this->min);
		$out_ts = VikRestaurants::createTimestamp($this->date, $this->hour, (int) $this->min + $duration);

		$clusterQuery = $dbo->getQuery(true)
			->select($dbo->qn('ti.name'))
			->from($dbo->qn('#__vikrestaurants_reservation', 'ri'))
			->leftjoin($dbo->qn('#__vikrestaurants_table', 'ti') . ' ON ' . $dbo->qn('ri.id_table') . ' = ' . $dbo->qn('ti.id'))
			->where($dbo->qn('ri.id') . ' = ' . $dbo->qn('r.id_parent'));

		// calculate total number of people hosted by the table
		$q = $dbo->getQuery(true)
			->select('r.*')
			->select($dbo->qn('c.code'))
			->select($dbo->qn('c.icon', 'code_icon'))
			->select($dbo->qn('os.notes', 'code_notes'))
			->select(sprintf(
				'IF (%s = 0, NULL, (%s)) AS %s',
				$dbo->qn('r.id_parent'),
				$clusterQuery,
				$dbo->qn('parent_table')
			))
			->from($dbo->qn('#__vikrestaurants_reservation', 'r'))
			->leftjoin($dbo->qn('#__vikrestaurants_res_code', 'c') . ' ON ' . $dbo->qn('r.rescode') . ' = ' . $dbo->qn('c.id'))
			->leftjoin(
				$dbo->qn('#__vikrestaurants_order_status', 'os') . ' ON ' . $dbo->qn('os.id_order') . ' = ' . $dbo->qn('r.id') . 
				' AND ' . $dbo->qn('os.id_rescode') . ' = ' . $dbo->qn('r.rescode') . 
				' AND ' . $dbo->qn('os.group') . ' = 1'
			)
			->where($dbo->qn('r.status') . ' IN (' . implode(',', array_map(array($dbo, 'q'), $this->statuses)) . ')')
			->andWhere(array(
				'(' . $dbo->qn('r.checkin_ts') . " <= {$in_ts}  AND {$in_ts}  <  " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
				'(' . $dbo->qn('r.checkin_ts') . " <  {$out_ts} AND {$out_ts} <= " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
				'(' . $dbo->qn('r.checkin_ts') . " <= {$in_ts}  AND {$out_ts} <= " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
				'(' . $dbo->qn('r.checkin_ts') . " >= {$in_ts}  AND {$out_ts} >= " . $dbo->qn('r.checkin_ts') . " + IF(" . $dbo->qn('r.stay_time') . " > 0, " . $dbo->qn('r.stay_time') . ", {$avg}) * 60)\n",
			), 'OR');

		$dbo->setQuery($q);
		return $dbo->loadObjectList();
	}

	/**
	 * Returns a list of rooms and the related tables.
	 *
	 * @return 	array
	 */
	public function getRooms()
	{
		$dbo = JFactory::getDbo();

		$ts = VikRestaurants::createTimestamp($this->date, $this->hour, $this->min);
		$model = JModelVRE::getInstance('roomclosure');

		$q = $dbo->getQuery(true);

		// select room details
		$q->select('r.*');

		// load rooms table
		$q->from($dbo->qn('#__vikrestaurants_room', 'r'));

		// sort rooms
		$q->order($dbo->qn('r.ordering') . ' ASC');

		$dbo->setQuery($q);

		$rooms = [];

		// iterate rooms
		foreach ($dbo->loadObjectList() as $r)
		{
			/**
			 * Check the room closures through the apposite framework.
			 * 
			 * @since 1.10
			 */
			$r->is_closed = $model->isRoomClosed($r->id, $ts);

			// retrieve all the room tables
			$r->tables = $this->getRoomTables($r->id);

			// copy room within the list
			$rooms[] = $r;
		}

		return $rooms;
	}

	/**
	 * Returns a list of suggested times before and after
	 * the selected checkin time.
	 *
	 * @param 	integer  $n  The maximum number of hints to display.
	 *
	 * @return 	array    A list of hints.
	 */
	public function getSuggestedTimes($n = 2)
	{
		$index = null;
		$shift = $this->getTimeShift($index);

		if (!$shift || is_null($index))
		{
			// no suggested hints
			return array();
		}

		$before = $after = array();

		$tmp = $index;

		$roomId = $this->get('room');

		// iterate backward as long as it is possible and
		// until we fetched the first N available times
		while (count($before) !== $n && !empty($shift[$tmp - 1]))
		{
			$tmp--;
			// check if prev slot is available 
			$search = new static($this->date, $shift[$tmp]->value, $this->people);

			if ($roomId)
			{
				$search->setRoom($roomId);
			}

			// find available tables
			$avail = $search->getAvailableTables();

			// free space
			unset($search);

			// make sure there is at least a standard table available
			if ($avail && !$avail[0]->multi_res)
			{
				// register time (at the beginning) as hint
				array_unshift($before, $shift[$tmp]->value);
			}
		}

		// push NULL value at the beginning until
		// the length of the array reached the
		// requested amount
		while (count($before) !== $n)
		{
			array_unshift($before, null);
		}

		$tmp = $index;

		// iterate afterward as long as it is possible and
		// until we fetched the first N available times
		while (count($after) !== $n && !empty($shift[$tmp + 1]))
		{
			$tmp++;
			// check if prev slot is available 
			$search = new static($this->date, $shift[$tmp]->value, $this->people);

			if ($roomId)
			{
				$search->setRoom($roomId);
			}

			// find available tables
			$avail = $search->getAvailableTables();

			// free space
			unset($search);

			// make sure there is at least a standard table available
			if ($avail && !$avail[0]->multi_res)
			{
				// register time (at the end) as hint
				array_push($after, $shift[$tmp]->value);
			}
		}

		// push NULL value at the end until
		// the length of the array reached the
		// requested amount
		while (count($after) !== $n)
		{
			array_push($after, null);
		}

		// merge fetched hints
		$hints = array_merge($before, $after);

		// get current date and time
		$now = VikRestaurants::now();

		// unset hints that are in the past
		foreach ($hints as &$hint)
		{
			if ($hint)
			{
				// fetch hint timestamp
				list($hour, $min) = explode(':', $hint);
				$ts = VikRestaurants::createTimestamp($this->date, $hour, $min);

				if ($now <= $ts)
				{
					// valid time, obtain a time object
					$hint = JHtml::fetch('vikrestaurants.min2time', $hour * 60 + $min, $string = false);
					// include timestamp
					$hint->ts = $ts;
				}
				else
				{
					// the hint time is before the current time, unset it
					$hint = null;
				}
			}
		}

		return $hints;
	}

	/**
	 * Returns the shift array to which the time belongs.
	 *
	 * @param 	integer  &$index  This arguments will be filled with the
	 * 							  index of the current time.
	 *
	 * @return 	mixed    The shift array on success, null otherwise.
	 */
	protected function getTimeShift(&$index = null)
	{
		// get available times
		$times = JHtml::fetch('vikrestaurants.times', $restaurant = 1, $this->date);

		// calculate time in minutes
		$hm = (int) $this->hour * 60 + (int) $this->min;

		// iterate all working shifts
		foreach ($times as $k => $shift)
		{
			// iterate all times in shift
			foreach ($shift as $i => $time)
			{
				$tmp = explode(':', $time->value);

				// calculate shift time in minutes
				$hm2 = (int) $tmp[0] * 60 + (int) $tmp[1];

				// check if the times match
				if ($hm == $hm2)
				{
					// register index
					$index = $i;

					// return shift found
					return $shift;
				}
			}
		}

		return null;
	}

	/**
	 * Load tables statically to avoid loading them more than once.
	 *
	 * @return 	array  A list of available tables.
	 */
	protected static function loadTables()
	{
		// check if tables are already cached
		if (static::$_tables === null)
		{
			$dbo = JFactory::getDbo();

			$q = $dbo->getQuery(true)
				->select('t.*')
				->select($dbo->qn('r.name', 'room_name'))
				->from($dbo->qn('#__vikrestaurants_table', 't'))
				->from($dbo->qn('#__vikrestaurants_room', 'r'))
				->where($dbo->qn('r.id') . ' = ' . $dbo->qn('t.id_room'))
				->order($dbo->qn('r.ordering') . ' ASC')
				->order($dbo->qn('t.ordering') . ' ASC')
				->order($dbo->qn('t.id') . ' ASC');

			$dbo->setQuery($q);
			$tables = $dbo->loadObjectList();

			// get all clusters
			$q = $dbo->getQuery(true)
				->select('*')
				->from($dbo->qn('#__vikrestaurants_table_cluster'));

			$dbo->setQuery($q);
			$clusters = $dbo->loadObjectList();

			foreach ($tables as $table)
			{
				$table->design_data = $table->design_data ? json_decode($table->design_data) : null;

				$table->cluster = [];

				// recover all the tables linked to the current one
				foreach ($clusters as $cl)
				{
					// make sure the current table belongs to this cluster
					if ($cl->id_table_1 == $table->id || $cl->id_table_2 == $table->id)
					{
						$table->cluster[] = $cl->id_table_1;
						$table->cluster[] = $cl->id_table_2;
					}
				}

				// strip duplicated values
				$table->cluster = array_values(array_unique($table->cluster));

				// sort cluster by ascending table ID
				sort($table->cluster, SORT_NUMERIC);

				// register table by ID
				static::$_tables[$table->id] = $table;
			}
		}

		return static::$_tables;
	}

	/**
	 * Returns a cluster containing all the tables that can
	 * be linked to the specified table.
	 *
	 * @param 	integer  $id       The table ID.
	 * @param 	boolean  $include  True to include the requested table.
	 *
	 * @return 	array 	 A list of tables.
	 */
	public static function getTablesCluster($id, $include = false)
	{
		$dbo = JFactory::getDbo();

		$q = $dbo->getQuery(true)
			->select(sprintf(
				'IF (%1$s = %3$s, %2$s, %1$s)',
				$dbo->qn('id_table_1'),
				$dbo->qn('id_table_2'),
				(int) $id
			))
			->from($dbo->qn('#__vikrestaurants_table_cluster'))
			->where(array(
				$dbo->qn('id_table_1') . ' = ' . (int) $id,
				$dbo->qn('id_table_2') . ' = ' . (int) $id,
			), 'OR');

		$dbo->setQuery($q);
		$list = $dbo->loadColumn();

		if (!$list)
		{
			return [];
		}

		if ($include)
		{
			// push specified ID as first
			array_unshift($list, (int) $id);
		}

		return array_values(array_unique($list));
	}

	/**
	 * Checks whether a new reservation is not going to exceed the maximum number
	 * of arrivals per time slot.
	 * 
	 * @return  bool  True in case the reservation can be accepted, false otherwise.
	 * 
	 * @since   1.11
	 */
	protected function checkArrivals()
	{
		if ($this->isAdmin())
		{
			// ignore if we are logged in as administrators
			return true;
		}

		$config = VREFactory::getConfig();

		// obtain the maximum number of simultaneous arrivals
		$maxReservations = $config->getUint('resperint');
		// obtain the maximum number of simultaneous guests
		$maxGuests = $config->getUint('guestperint');

		if ($maxReservations <= 0 && $maxGuests <= 0)
		{
			// both restrictions ignored per configuration
			return true;
		}

		// convert the hours and minutes into minutes
		$time = (int) $this->hour * 60 + (int) $this->min;

		// obtain the times table for the current date
		$timesTable = $this->getTimesTable($this->date);

		// make sure the number of arrivals plus one is not greater than the maximum threshold
		if ($maxReservations > 0 && ($timesTable[$time]['count'] ?? 0) + 1 > $maxReservations)
		{
			// exceeded the maximum threshold
			return false;
		}

		// get total number of guests at the current time
		$totalGuests = $timesTable[$time]['guests'] ?? 0;

		// Make sure the number of guests plus the searched ones is not greater than the maximum threshold.
		// In case we have no guests at the selected time, accept it anyway.
		if ($maxGuests > 0 && $totalGuests > 0 && $totalGuests + $this->people > $maxGuests)
		{
			// exceeded the maximum threshold
			return false;
		}

		return true;
	}

	/**
	 * Returns a reservations lookup for the given date, where the keys are filled in
	 * with times and the values are the number of confirmed reservations.
	 * 
	 * @param   string  $date  The date formatted as the system preferences.
	 * 
	 * @return  array
	 * 
	 * @since   1.11
	 */
	protected static function getTimesTable(string $date)
	{
		if (isset(static::$_reservations[$date]))
		{
			return static::$_reservations[$date];
		}

		static::$_reservations[$date] = [];

		$start = VikRestaurants::createTimestamp($date, 0, 0);
		$end = VikRestaurants::createTimestamp($date, 23, 59);

		$db = JFactory::getDbo();

		// get any reserved codes
   		$reserved = JHtml::fetch('vrehtml.status.find', 'code', ['restaurant' => 1, 'reserved' => 1]);

		$query = $db->getQuery(true)
			->select(sprintf(
				'DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(%s), @@session.time_zone, \'%s\'), \'%%H:%%i\') AS %s',
				$db->qn('checkin_ts'),
				date('P'), // returns the string offset '+02:00'
				$db->qn('time'),
			))
			->select('COUNT(1) AS ' . $db->qn('count'))
			->select('sum(' . $db->qn('people') . ') AS ' . $db->qn('guests'))
			->from($db->qn('#__vikrestaurants_reservation'))
			->group($db->qn('time'))
			->where($db->qn('checkin_ts') . ' BETWEEN ' . $start . ' AND ' . $end)
			->where($db->qn('closure') . ' = 0')
			->where($db->qn('closure') . ' = 0');

		if ($reserved)
		{
			// filter by reserved status
			$query->where($db->qn('status') . ' IN (' . implode(',', array_map(array($db, 'q'), $reserved)) . ')');
		}

		$db->setQuery($query);

		foreach ($db->loadObjectList() as $r)
		{
			// convert H:i time into minutes
			$time = JHtml::fetch('vikrestaurants.time2min', $r->time);

			static::$_reservations[$date][$time] = [
				'count' => $r->count,
				'guests' => $r->guests,
			];
		}

		return static::$_reservations[$date];
	}
}
