<?php

namespace Planaday\Database\SQLDatabaseControllers\Course;

use ArrayAccess;
use Countable;
use Planaday\Database\Abstraction\SQLQuery\ColumnTypeMap;
use Planaday\Database\Abstraction\SQLQuery\SQLFactory;
use Planaday\Database\SQLDatabaseControllers\DayPart\DayPartColumnEnum;
use Planaday\Database\SQLDatabaseControllers\DayPart\DayPartInstructorLink;
use Planaday\Database\SQLDatabaseControllers\DayPart\DayPartLocationLink;
use Planaday\Database\SQLDatabaseControllers\DayPart\DayPartTable;
use Planaday\Database\SQLDatabaseControllers\LinkingTable;
use Planaday\Database\SQLDatabaseControllers\Location\LocationColumnEnum;
use Planaday\Database\SQLDatabaseControllers\Location\LocationTable;
use Planaday\Database\SQLDatabaseControllers\Table;
use Planaday\Entities\Course\DTO\Course;
use Planaday\Entities\Course\DTO\CourseSearch;
use Planaday\Platform\PlatformAbstraction\DatabaseInterface;
use Planaday\Utility\DataGrouping\ArrayFunctions;
use Planaday\Utility\DataGrouping\Collection;
use Planaday\Utility\DataGrouping\Map;
use Traversable;

class CourseTable extends Table {
	public const COURSE_TABLE = 'course';

	public function __construct( DatabaseInterface $querySender, SQLFactory $sql, ColumnTypeMap $columnTypeMap) {
		$columns = [];

		$columns[CourseColumnEnum::ID] = implode(" ", [
			$columnTypeMap->ID(),
			$columnTypeMap->NOT_NULL(),
			$columnTypeMap->PRIMARY_KEY()
		]);
		$columns[CourseColumnEnum::TEMPLATE_ID] = implode(" ", [
			$columnTypeMap->ID(),
			$columnTypeMap->DEFAULT_NULL()
		]);

		$columns[CourseColumnEnum::NAME]  =
		$columns[CourseColumnEnum::LEVEL] = implode(" ", [
			$columnTypeMap->NAME(),
			$columnTypeMap->DEFAULT_NULL()
		]);

		$columns[CourseColumnEnum::DESCRIPTION]       =
		$columns[CourseColumnEnum::LEVEL_DESCRIPTION] = implode(" ", [
			$columnTypeMap->DESCRIPTION(),
			$columnTypeMap->DEFAULT_NULL()
		]);

		$columns[CourseColumnEnum::STATUS]        =
		$columns[CourseColumnEnum::CODE]          =
		$columns[CourseColumnEnum::TYPE]          =
		$columns[CourseColumnEnum::COST_VAT_CODE] =
		$columns[CourseColumnEnum::LANGUAGE]      = implode(" ", [
			$columnTypeMap->CODE(),
			$columnTypeMap->DEFAULT_NULL()
		]);

		$columns[CourseColumnEnum::DAY_PART_AMOUNT] =
		$columns[CourseColumnEnum::USERS_MIN]       =
		$columns[CourseColumnEnum::USERS_MAX]       =
		$columns[CourseColumnEnum::USERS_SCHEDULED] =
		$columns[CourseColumnEnum::USERS_AVAILABLE] =
		$columns[CourseColumnEnum::USERS_OPTIONS]   = implode(" ", [
			$columnTypeMap->AVAILABILITY_COUNT(),
			$columnTypeMap->DEFAULT_NULL()
		]);

		$columns[CourseColumnEnum::COST_COST]   =
		$columns[CourseColumnEnum::COST_COURSE] = implode(" ", [
			$columnTypeMap->PRICE(),
			$columnTypeMap->DEFAULT_NULL()
		]);

		$columns[CourseColumnEnum::COST_VAT] = implode(" ", [
			$columnTypeMap->TAX(),
			$columnTypeMap->DEFAULT_NULL()
		]);

		$columns[CourseColumnEnum::COST_REMARK] = implode(" ", [
			$columnTypeMap->REMARK(),
			$columnTypeMap->DEFAULT_NULL()
		]);

		$columns[CourseColumnEnum::START_GUARANTEED]      =
		$columns[CourseColumnEnum::MONEY_BACK_GUARANTEED] =
		$columns[CourseColumnEnum::HAS_E_LEARNING]        =
		$columns[CourseColumnEnum::HAS_CODE95]            =
		$columns[CourseColumnEnum::HAS_SOOB]              =
		$columns[CourseColumnEnum::HAS_STAP]              =
		$columns[CourseColumnEnum::STAP_ONLY]             = implode(" ", [
			$columnTypeMap->BOOLEAN(),
			$columnTypeMap->DEFAULT_NULL()
		]);

		$columns[CourseColumnEnum::LABELS] = implode(" ", [
			$columnTypeMap->LABELS(),
			$columnTypeMap->DEFAULT_NULL()
		]);

		$columns[CourseColumnEnum::LAST_UPDATE] = $columnTypeMap->LAST_UPDATED_TIMESTAMP();

		parent::__construct( $querySender, $sql, self::COURSE_TABLE, $columns, [] );
	}

	public function setCourse( Course $course ):void {
		$databaseArray = CourseDatabaseObject::getDatabaseArrayFromCourse($course);
		$table = $this->getSqlTable();
		$query = $this->sql->insert($table, $databaseArray);
		$this->querySender->runPreparedQuery($query->toQuery(true), $databaseArray);
	}

	public function getCourseById( int $id ): ?Course {
		$table = $this->getSqlTable();
		$query = $this->sql->query($table, $table);
		$query->setWhere($this->sql->compare($table->getColumn(CourseColumnEnum::ID), $id, true));
		$query->setLimit(1);

		$result = $this->querySender->sendQuery($query->toQuery());

		if (!is_array($result) || count($result) == 0){
			return null;
		}

		return CourseDatabaseObject::getCourseObject($result[0]);
	}

	/**
	 * @param CourseSearch $search
	 *
	 * @return ArrayAccess<int|string, Course>&Traversable<int|string, Course>&Countable
	 */
	public function getCoursesBySearch(CourseSearch $search): ArrayAccess&Traversable&Countable {
		$courseTable = $this->getSqlTable();
		$queryObject = $this->sql->query($courseTable, $courseTable);

		$sqlWhereStatement = $this->sql->and();

		if ($search->getIds() !== null) $sqlWhereStatement->addCondition(
			$this->sql->in($courseTable->getColumn(CourseColumnEnum::ID), $search->getIds()));

		if ($search->getCodes() !== null) $sqlWhereStatement->addCondition(
			$this->sql->in($courseTable->getColumn(CourseColumnEnum::CODE), $search->getCodes()));

		if ($search->getName() !== null) $sqlWhereStatement->addCondition(
			$this->sql->like($courseTable->getColumn(CourseColumnEnum::NAME), $search->getName()));

		if ($search->getDescription() !== null) $sqlWhereStatement->addCondition(
			$this->sql->like($courseTable->getColumn(CourseColumnEnum::DESCRIPTION), $search->getDescription()));

		if ($search->getTypes() !== null) $sqlWhereStatement->addCondition(
			$this->sql->in($courseTable->getColumn(CourseColumnEnum::TYPE), $search->getTypes()));

		if ($search->getStatuses() !== null) $sqlWhereStatement->addCondition(
			$this->sql->in($courseTable->getColumn(CourseColumnEnum::STATUS), $search->getStatuses()));

		if ($search->getDayPartAmountMin() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::DAY_PART_AMOUNT), $search->getDayPartAmountMin(), true, true));

		if ($search->getDayPartAmountMax() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::DAY_PART_AMOUNT), $search->getDayPartAmountMax(), true, false));

		if ($search->getNeedsDayPartInfo()){
			$dayPartTable = $this->sql->table($this->querySender->convertTableString(DayPartTable::DAY_PART_TABLE));

			$courseIdColumn = $courseTable->getColumn(CourseColumnEnum::ID);
			$dayPartCourseIdColumn = $dayPartTable->getColumn(DayPartColumnEnum::COURSE_ID);
			$queryObject->setFrom($this->sql->join($courseTable, $dayPartTable, $courseIdColumn, $dayPartCourseIdColumn));

			if ($search->getDayPartName()) $sqlWhereStatement->addCondition(
				$this->sql->like($dayPartTable->getColumn(DayPartColumnEnum::NAME), $search->getDayPartName()));

			if ($search->getDayPartDescription()) $sqlWhereStatement->addCondition(
				$this->sql->like($dayPartTable->getColumn(DayPartColumnEnum::DESCRIPTION), $search->getDayPartDescription()));

			if ($search->getLocations() !== null || $search->getCities() !== null) {
				$locationTable = $this->sql->table($this->querySender->convertTableString(LocationTable::LOCATION_TABLE));
				$dayPartLocationTable = $this->sql->table($this->querySender->convertTableString(DayPartLocationLink::DAY_PART_LOCATION_LINK_TABLE));

				$dayPartIdColumn = $dayPartTable->getColumn(DayPartColumnEnum::ID);
				$linkDayPartIdColumn = $dayPartLocationTable->getColumn(LinkingTable::convertOuterColumnToInnerName($dayPartIdColumn));

				$locationIdColumn = $locationTable->getColumn(LocationColumnEnum::ID);
				$linkLocationIdColumn = $dayPartLocationTable->getColumn(LinkingTable::convertOuterColumnToInnerName($locationIdColumn));

				$queryObject->setFrom(
					$this->sql->join( $queryObject->getFrom(), $dayPartLocationTable, $dayPartIdColumn, $linkDayPartIdColumn )
				);

				if ($search->getLocations() !== null) {
					$sqlWhereStatement->addCondition(
						$this->sql->in($linkLocationIdColumn, $search->getLocations()));
				}

				if (!empty($search->getCities())) {
					$queryObject->setFrom(
						$this->sql->join( $queryObject->getFrom(), $locationTable, $linkLocationIdColumn, $locationIdColumn )
					);


					$sqlWhereStatement->addCondition(
						$this->sql->in($locationTable->getColumn(LocationColumnEnum::CITY), $search->getCities())
					);
				}
			}

			if ($search->getInstructors() !== null) {
				$instructorTable = $this->sql->table($this->querySender->convertTableString(DayPartInstructorLink::INSTRUCTOR_TABLE_PLACEHOLDER));
				$dayPartInstructorTable = $this->sql->table($this->querySender->convertTableString(DayPartInstructorLink::DAY_PART_INSTRUCTOR_LINK_TABLE));

				$dayPartIdColumn = $dayPartTable->getColumn(DayPartColumnEnum::ID);
				$linkDayPartIdColumn = $dayPartInstructorTable->getColumn(LinkingTable::convertOuterColumnToInnerName($dayPartIdColumn));

				$instructorIdColumn = $instructorTable->getColumn(LocationColumnEnum::ID);
				$linkInstructorIdColumn = $dayPartInstructorTable->getColumn(LinkingTable::convertOuterColumnToInnerName($instructorIdColumn));

				$queryObject->setFrom(
					$this->sql->join( $queryObject->getFrom(), $dayPartInstructorTable, $dayPartIdColumn, $linkDayPartIdColumn )
				);

				$sqlWhereStatement->addCondition(
					$this->sql->in($linkInstructorIdColumn, $search->getInstructors()));
			}

			if ($search->getDayPartLabels() !== null){
				$sqlDayPartLabelsOr = $this->sql->or();
				foreach ($search->getDayPartLabels() as $label){
					$sqlDayPartLabelsOr->addCondition($this->sql->like($dayPartTable->getColumn(DayPartColumnEnum::LABELS), $label));
				}

				$sqlWhereStatement->addCondition($sqlDayPartLabelsOr);
			}

			if ($search->getDateMin() !== null) $sqlWhereStatement->addCondition(
				$this->sql->compare($dayPartTable->getColumn(DayPartColumnEnum::START_TIME), date('Y-m-d 00:00:00', strtotime($search->getDateMin())), true, true));

			if ($search->getDateMax() !== null) $sqlWhereStatement->addCondition(
				$this->sql->compare($dayPartTable->getColumn(DayPartColumnEnum::START_TIME), date('Y-m-d 23:59:59', strtotime($search->getDateMax())), true, false));

			if ($search->getEndDateMin() !== null) $sqlWhereStatement->addCondition(
				$this->sql->or(
					$this->sql->compare(
						$dayPartTable->getColumn(DayPartColumnEnum::END_TIME),
						date('Y-m-d 00:00:00', strtotime($search->getEndDateMin())),
						true,
						true
					),
					$this->sql->compare(
						$dayPartTable->getColumn(DayPartColumnEnum::END_TIME),
						null,
						true
					)
				));

			if ($search->getEndDateMax() !== null) $sqlWhereStatement->addCondition(
				$this->sql->compare($dayPartTable->getColumn(DayPartColumnEnum::END_TIME), date('Y-m-d 23:59:59', strtotime($search->getEndDateMax())), true, false));
		}

		if ($search->getUsersMinMin() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::USERS_MIN), $search->getUsersMinMin(), true, true));

		if ($search->getUsersMinMax() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::USERS_MIN), $search->getUsersMinMax(), true, false));

		if ($search->getUsersMaxMin() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::USERS_MAX), $search->getUsersMaxMin(), true, true));

		if ($search->getUsersMaxMax() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::USERS_MAX), $search->getUsersMaxMax(), true, false));

		if ($search->getUsersScheduledMin() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::USERS_SCHEDULED), $search->getUsersScheduledMin(), true, true));

		if ($search->getUsersScheduledMax() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::USERS_SCHEDULED), $search->getUsersScheduledMax(), true, false));

		if ($search->getUsersAvailableMin() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::USERS_AVAILABLE), $search->getUsersAvailableMin(), true, true));

		if ($search->getUsersAvailableMax() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::USERS_AVAILABLE), $search->getUsersAvailableMax(), true, false));

		if ($search->getUsersOptionsMin() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::USERS_OPTIONS), $search->getUsersOptionsMin(), true, true));

		if ($search->getUsersOptionsMax() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::USERS_OPTIONS), $search->getUsersOptionsMax(), true, false));

		if ($search->getCostMin() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::COST_COST), $search->getCostMin(), true, true));

		if ($search->getCostMax() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::COST_COST), $search->getCostMax(), true, false));

		if ($search->getCourseCostMin() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::COST_COURSE), $search->getCourseCostMin(), true, true));

		if ($search->getCourseCostMax() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::COST_COURSE), $search->getCourseCostMax(), true, false));

		if ($search->getVatMin() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::COST_VAT), $search->getVatMin(), true, true));

		if ($search->getVatMax() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::COST_VAT), $search->getVatMax(), true, false));

		if ($search->getVatCodes() !== null) $sqlWhereStatement->addCondition(
			$this->sql->in($courseTable->getColumn(CourseColumnEnum::COST_VAT_CODE), $search->getVatCodes()));

		if ($search->getCostRemark() !== null) $sqlWhereStatement->addCondition(
			$this->sql->like($courseTable->getColumn(CourseColumnEnum::COST_REMARK), $search->getCostRemark()));

		if ($search->getTemplateIds() !== null) $sqlWhereStatement->addCondition(
			$this->sql->in($courseTable->getColumn(CourseColumnEnum::TEMPLATE_ID), $search->getTemplateIds()));

		if ($search->getLevels() !== null) $sqlWhereStatement->addCondition(
			$this->sql->in($courseTable->getColumn(CourseColumnEnum::LEVEL), $search->getLevels()));

		if ($search->getLevelDescription() !== null) $sqlWhereStatement->addCondition(
			$this->sql->like($courseTable->getColumn(CourseColumnEnum::LEVEL_DESCRIPTION), $search->getLevelDescription()));

		if ($search->getIsStartGuaranteed() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::START_GUARANTEED), (int)$search->getIsStartGuaranteed(), true));

		if ($search->getIsMoneyBackGuaranteed() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::MONEY_BACK_GUARANTEED), (int)$search->getIsMoneyBackGuaranteed(), true));

		if ($search->getHasELearning() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::HAS_E_LEARNING), (int)$search->getHasELearning(), true));

		if ($search->getHasCode95() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::HAS_CODE95), (int)$search->getHasCode95(), true));

		if ($search->getHasSoob() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::HAS_SOOB), (int)$search->getHasSoob(), true));

		if ($search->getHasStap() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::HAS_STAP), (int)$search->getHasStap(), true));

		if ($search->getIsStapOnly() !== null) $sqlWhereStatement->addCondition(
			$this->sql->compare($courseTable->getColumn(CourseColumnEnum::STAP_ONLY), (int)$search->getIsStapOnly(), true));

		if ($search->getLabels() !== null){
			$sqlLabelsOr = $this->sql->or();
			foreach ($search->getLabels() as $label){
				$sqlLabelsOr->addCondition($this->sql->like($courseTable->getColumn(CourseColumnEnum::LABELS), "|$label|"));
			}

			$sqlWhereStatement->addCondition($sqlLabelsOr);
		}

		if ($search->getLanguages() !== null) $sqlWhereStatement->addCondition(
			$this->sql->in($courseTable->getColumn(CourseColumnEnum::LANGUAGE), $search->getLanguages()));

		if ($search->getCompanies() !== null) {
			$companyTable = $this->sql->table($this->querySender->convertTableString(CourseCompanyLink::COMPANY_TABLE_PLACEHOLDER));
			$companyLinkTable = $this->sql->table($this->querySender->convertTableString(CourseCompanyLink::COURSE_COMPANY_LINK));

			$companyIdColumn = $companyTable->getColumn(CourseCompanyLink::COMPANY_COLUMN_PLACEHOLDER);
			$courseIdColumn = $courseTable->getColumn(CourseColumnEnum::ID);

			$linkCompanyIdColumn = $companyLinkTable->getColumn(LinkingTable::convertOuterColumnToInnerName($companyIdColumn));
			$linkCourseIdColumn = $companyLinkTable->getColumn(LinkingTable::convertOuterColumnToInnerName($courseIdColumn));

			$queryObject->setFrom(
				$this->sql->join( $queryObject->getFrom(), $companyLinkTable, $courseIdColumn, $linkCourseIdColumn)
			);

			$sqlWhereStatement->addCondition(
				$this->sql->in($linkCompanyIdColumn, $search->getCompanies())
			);
		}

		$queryObject->setWhere($sqlWhereStatement);
		$queryObject->setOrderBy(isset($dayPartTable)
			? $dayPartTable->getColumn(DayPartColumnEnum::START_TIME)
			: $courseTable->getColumn(CourseColumnEnum::NAME));
		$queryObject->isDistinct();

		$query = $queryObject->toQuery();

		$this->logDebug("Sending search query: $query");

		$results = $this->querySender->sendQuery($query);

		return $this->getCoursesFromResult($results);
	}

	/**
	 * @return ArrayAccess<int|string, string>&Traversable<int|string, string>&Countable
	 */
	public function getUniqueLabelsCourse(): ArrayAccess&Traversable&Countable {
		$query = "SELECT " . CourseColumnEnum::LABELS . " FROM {pad-table}";

		$results = $this->querySender->getQueryResults($this, $query);

		if (!is_array($results)) return Collection::fromArray([]);

		$labelArrays = [];

		foreach ($results as $result){
			if ($result[CourseColumnEnum::LABELS] == '' || $result[CourseColumnEnum::LABELS] == '||') continue;
			$labelArrays[] = explode('|', substr($result[CourseColumnEnum::LABELS], 1, -1));
		}

		$labels = [];

		foreach ($labelArrays as $array){
			$labels = array_merge($labels, $array);
		}

		usort($labels, function($a, $b) {return $a <=> $b;});

		return Collection::fromArray(array_unique($labels));
	}

	/**
	 * @return ArrayAccess<int|string, Course>&Traversable<int|string, Course>&Countable
	 */
	public function getAll(): ArrayAccess&Traversable&Countable {
		return $this->getCoursesFromResult($this->getAllRaw());
	}


	/**
	 * @return ArrayAccess&Traversable&Countable
	 */
	public function getAllRaw(): ArrayAccess&Traversable&Countable{
		$query = "SELECT * FROM {pad-table}";

		$results = $this->querySender->getQueryResults($this, $query);

		if (!is_array($results) || count($results) == 0){
			return Collection::fromArray([]);
		}

		return Map::fromArray($results);
	}

	/**
	 * @param $results
	 *
	 * @return ArrayAccess<int|string, Course>&Traversable<int|string, Course>&Countable
	 */
	public function getCoursesFromResult($results): ArrayAccess&Traversable&Countable{
		if (!ArrayFunctions::is_arrayType($results) || count($results) == 0){
			return Collection::fromArray([]);
		}

		$returnArray = [];

		foreach ($results as $result){
			$returnArray[] = CourseDatabaseObject::getCourseObject($result);
		}

		return Collection::fromArray($returnArray);
	}

	/**
	 * @param ArrayAccess<int|string, Course>&Traversable<int|string, Course>&Countable $courses
	 *  @param bool $wipeDB
	 *
	 * @return void
	 */
	public function setAll( ArrayAccess&Traversable&Countable $courses, bool $wipeDB = false ): void {
		if ($wipeDB){
			$sql = "TRUNCATE TABLE " . $this->getSqlTable()->getFromQuery();
			$this->querySender->sendQuery($sql);
		}

		foreach ($courses as $course){
			$this->setCourse($course);
		}
	}

	/**
	 * @param int|null $startDate
	 * @param int|null $endDate
	 *
	 * @return ArrayAccess<int|string, Course>&Traversable<int|string, Course>&Countable
	 */
	public function getCoursesByTimeSlot( ?int $startDate, ?int $endDate ): ArrayAccess&Traversable&Countable {
		$table = $this->getSqlTable();
		$dayPartTable = $this->sql->table($this->querySender->convertTableString(DayPartTable::DAY_PART_TABLE));
		$query = $this->sql->query($this->sql->join($table, $dayPartTable, $table->getColumn(CourseColumnEnum::ID), $dayPartTable->getColumn(DayPartColumnEnum::COURSE_ID)), $table);
		$where = $this->sql->and(
			$this->sql->compare(
				$dayPartTable->getColumn(DayPartColumnEnum::START_TIME),
				date('Y-m-d 23:59:59', $endDate),
				true,
				false
			),
			$this->sql->or(
				$this->sql->compare(
					$dayPartTable->getColumn(DayPartColumnEnum::END_TIME),
					date('Y-m-d 00:00:00', $startDate),
					true,
					true
				),
				$this->sql->compare(
					$dayPartTable->getColumn(DayPartColumnEnum::END_TIME),
					null,
					true
				)
			)
		);

		$query->setWhere($where);
		$query->setOrderBy($dayPartTable->getColumn(DayPartColumnEnum::START_TIME)->setOrderDirection(true));

		$query->isDistinct();

		$results = $this->querySender->sendQuery($query->toQuery());

		return $this->getCoursesFromResult($results);
	}

	public function getCountAll(): int {
		$table = $this->getSqlTable();
		$count = $this->sql->count($table->getColumn(CourseColumnEnum::ID));
		$count->setAlias('Count');
		$query = $this->sql->query( $table, $count );

		$result = $this->querySender->sendQuery($query->toQuery());

		if (!is_array($result) || count($result) == 0){
			return 0;
		}

		return $result[0]['Count'] ?? 0;
	}
}