<?php

namespace Planaday\Database\SQLDatabaseControllers\Booking;

use Exception;
use Planaday\Database\Abstraction\SQLQuery\ColumnTypeMap;
use Planaday\Database\Abstraction\SQLQuery\SQLFactory;
use Planaday\Database\SQLDatabaseControllers\Table;
use Planaday\Entities\Booking\DTO\Booking;
use Planaday\Entities\Booking\DTO\BookingCompany;
use Planaday\Entities\Booking\DTO\BookingForm;
use Planaday\Entities\Booking\DTO\BookingStudent;
use Planaday\Entities\Booking\DTO\Transaction;
use Planaday\Entities\Booking\Enum\BookingStatus;
use Planaday\Platform\PlatformAbstraction\DatabaseInterface;
use Planaday\Utility\DataGrouping\Collection;
use Planaday\Utility\DataGrouping\Map;
use Planaday\Utility\DataGrouping\UUIDGenerator;

class BookingTable extends Table {
	public const BOOKING_TABLE = 'booking';

	public const ID_COLUMN = 'id'; //for security this uses a UUID instead of an auto incrementing integer
	public const ID_PASSWORD_HASH_COLUMN = 'id_password_hash';
	public const BOOKING_ID_COLUMN = 'booking_id';
	public const STATUS_COLUMN = 'status';
	public const CREATED_AT_COLUMN = 'created_at';
	public const SEND_TO_API_AT = 'send_to_api_at';
	public const LAST_UPDATED_AT_COLUMN = 'last_updated_at';
	public const TRANSACTION_ID_COLUMN = 'transaction_id';
	public const COURSE_ID_COLUMN = 'course_id';
	public const COMPANY_ID_COLUMN = 'company_id';
	public const COMPANY_ID_OVERRIDE_COLUMN = 'company_id_override';
	public const TERMS_AND_CONDITIONS_COLUMN = 'terms_and_conditions';
	public const NONCE_COLUMN = 'nonce';

	protected UUIDGenerator $uuidGenerator;


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

		$columns[self::ID_COLUMN] = implode(" ", [
			$columnTypeMap->UUID(),
			$columnTypeMap->NOT_NULL(),
			$columnTypeMap->PRIMARY_KEY()
		]);

		$columns[self::ID_PASSWORD_HASH_COLUMN] = implode(" ", [
			$columnTypeMap->HASH(),
			$columnTypeMap->DEFAULT_NULL()
		]);

		$columns[self::COURSE_ID_COLUMN] = implode(" ", [
			$columnTypeMap->ID(),
			$columnTypeMap->NOT_NULL()
		]);

		$columns[self::TRANSACTION_ID_COLUMN] =
		$columns[self::BOOKING_ID_COLUMN] = implode(" ", [
			$columnTypeMap->CODE(),
			$columnTypeMap->DEFAULT_NULL()
		]);

		$columns[self::COMPANY_ID_COLUMN] = implode(" ", [
			$columnTypeMap->ID(),
			$columnTypeMap->DEFAULT_NULL()
		]);

		$columns[self::COMPANY_ID_OVERRIDE_COLUMN] = implode(" ", [
			$columnTypeMap->ID(),
			$columnTypeMap->DEFAULT_NULL()
		]);

		$columns[self::STATUS_COLUMN] = implode(  " ", [
			$columnTypeMap->STATUS(),
			$columnTypeMap->DEFAULT_NULL()
		]);

		$columns[self::SEND_TO_API_AT] = implode(  " ", [
			$columnTypeMap->DATETIME(),
			$columnTypeMap->DEFAULT_NULL()
		]);

		$columns[self::CREATED_AT_COLUMN] = $columnTypeMap->CREATION_TIMESTAMP();
		$columns[self::LAST_UPDATED_AT_COLUMN] = $columnTypeMap->LAST_UPDATED_TIMESTAMP();

		$columns[self::TERMS_AND_CONDITIONS_COLUMN] = implode(  " ", [
			$columnTypeMap->BOOLEAN(),
			$columnTypeMap->DEFAULT_NULL()
		]);

		$columns[self::NONCE_COLUMN] = implode(  " ", [
			$columnTypeMap->TEXT(),
			$columnTypeMap->DEFAULT_NULL()
		]);

		$this->uuidGenerator = $UUIDGenerator;

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

	/**
	 * @param Booking $booking
	 * @param int|null $companyId
	 * @param string|null $transactionId
	 *
	 * @return string id of created booking if new, otherwise id of existing
	 */
	public function set(Booking $booking, ?int $companyId, ?string $transactionId):string {
		$table = $this->getSqlTable();
		$array = $this->bookingObjectToArray($booking, $companyId, $transactionId);

		if ( $booking->getDatabaseId() !== null && $this->querySender->idExists($this, $booking->getDatabaseId())) {
			$where = $this->sql->compare($table->getColumn(self::ID_COLUMN), $booking->getDatabaseId(), true);
			$query = $this->sql->update($table, $where, $array);

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

			return $booking->getDatabaseId();
		}

		try {
			$array[ self::ID_COLUMN ] = $id = $this->uuidGenerator->generateUUIDv4();
		} catch ( Exception ) {
			$array[ self::ID_COLUMN ] = $id = number_format(time(), 20);
		}

		$query = $this->sql->insert($table, $array);
		$this->querySender->sendQuery($query->toQuery());

		$booking->setDatabaseId($id);
		return $booking->getDatabaseId();
	}

	public function updateHash(string $id, ?string $password):void {
		$table = $this->getSqlTable();
		$where = $this->sql->compare($table->getColumn(self::ID_COLUMN), $id, true);
		$query = $this->sql->update($table, $where, [
			self::ID_PASSWORD_HASH_COLUMN => $password ? bin2hex(hash('sha256', $id . $password, true)) : null
		]);

		$this->querySender->sendQuery($query->toQuery());
	}

	public function checkHash(string $id, string $password):bool {
		$table = $this->getSqlTable();
		$where = $this->sql->compare($table->getColumn(self::ID_COLUMN), $id, true);
		$hashColumn = $table->getColumn(self::ID_PASSWORD_HASH_COLUMN);
		$hashColumn->setAlias("Hash");
		$query = $this->sql->query($table, $hashColumn);
		$query->setWhere($where);
		$query->setLimit(1);

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

		if (!is_array($result) || count($result) < 1){
			$this->logWarning("Requesting Hash check on invalid ID");
			return false;
		}

		$hash = $result[0]["Hash"];
		return $hash === bin2hex(hash('sha256', $id . $password, true));
	}

	public function remove(string $id):void {
		$table = $this->getSqlTable();
		$where = $this->sql->compare( $table->getColumn( self::ID_COLUMN ), $id, true );
		$query = $this->sql->delete( $table, $where );

		$this->querySender->sendQuery( $query->toQuery() );
	}

	/**
	 * @param string $id
	 * @param BookingStudent[] $students
	 * @param array<string, string|string[]> $attributes
	 * @param int[] $materials
	 * @param callable(int):(BookingCompany|null) $getCompanyCallback
	 * @param callable(string):(Transaction|null) $getTransactionCallback
	 * @param array $dayParts
	 *
	 * @return Booking|null
	 */
	public function get(string $id, array $students, array $attributes, array $materials, callable $getCompanyCallback, callable $getTransactionCallback, array $dayParts): ?Booking {
		$table = $this->getSqlTable();
		$where = $this->sql->compare($table->getColumn(self::ID_COLUMN), $id, true);
		$query = $this->sql->query($table, $table);
		$query->setWhere($where);
		$query->setLimit(1);

		$result = $this->querySender->sendQuery($query->toQuery());
		if (!is_array($result) || count($result) < 1) {
			$this->logWarning("No booking found with id $id", $result);
			return null;
		}

		return $this->bookingArrayToObject($result[0], $students, $attributes, $materials, $getCompanyCallback, $getTransactionCallback, $dayParts);
	}

	public function getIdByNonceAndCourseId(string $nonce, int $courseId): ?string {
		$table = $this->getSqlTable();

		$where = $this->sql->and(
			$this->sql->compare($table->getColumn(self::NONCE_COLUMN), $nonce, true),
			$this->sql->compare($table->getColumn(self::COURSE_ID_COLUMN), $courseId, true)
		);

		$select = $table->getColumn(self::ID_COLUMN);
		$select->setAlias("BookingId");

		$query = $this->sql->query($table, $select);
		$query->setWhere($where);
		$query->setLimit(1);

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

		return ( $result[0] ?? [])["BookingId"] ?? null;
	}

	public function getBookingByTransaction(string $transactionId): ?string {
		$table = $this->getSqlTable();
		$where = $this->sql->compare($table->getColumn(self::TRANSACTION_ID_COLUMN), $transactionId, true);
		$select = $table->getColumn(self::ID_COLUMN);
		$select->setAlias("BookingId");
		$query = $this->sql->query($table, $select);
		$query->setWhere($where);
		$query->setLimit(1);

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

		return ( $result[0] ?? [])["BookingId"] ?? null;
	}

	public function bookingObjectToArray(Booking $booking, ?int $companyId, ?string $transactionId): array {
		$returnArray = [];

		$returnArray[ self::BOOKING_ID_COLUMN ] = $booking->getBookingId();
		$returnArray[ self::STATUS_COLUMN ] = $booking->getBookingStatus()->value;
		$returnArray[ self::COURSE_ID_COLUMN ] = $booking->getBookingForm()->getCourseId();
		$returnArray[ self::TERMS_AND_CONDITIONS_COLUMN ] = $booking->getBookingForm()->isTermsAndConditions();

		$returnArray[ self::COMPANY_ID_COLUMN ] = $companyId;
		$returnArray[ self::TRANSACTION_ID_COLUMN ] = $transactionId;

		$returnArray[ self::NONCE_COLUMN ] = $booking->getBookingForm()->getNonce();

		return $returnArray;
	}

	/**
	 * @param array $array
	 * @param array $students
	 * @param array $attributes
	 * @param array $materials
	 * @param callable(int):(BookingCompany|null) $getCompanyCallback
	 * @param callable(?string):(Transaction|null) $getTransactionCallback
	 * @param array $dayParts
	 *
	 * @return Booking
	 */
	public function bookingArrayToObject(array $array, array $students, array $attributes, array $materials, callable $getCompanyCallback, callable $getTransactionCallback, array $dayParts): Booking {
		$form = (new BookingForm())
			->setCourseId($array[ self::COURSE_ID_COLUMN ] ?? null)
			->setMaterials(Collection::fromArray($materials))
			->setCompany($array[self::COMPANY_ID_COLUMN] !== null ? $getCompanyCallback( $array[self::COMPANY_ID_COLUMN] ) : null)
			->setAttributes(Map::fromArray($attributes))
			->setStudents(Collection::fromArray($students))
			->setTermsAndConditions($array[ self::TERMS_AND_CONDITIONS_COLUMN ] ?? null)
			->setDayParts(Collection::fromArray($dayParts))
			->setCompanyId( $array[ self::COMPANY_ID_OVERRIDE_COLUMN] ?? null )
			->setNonce( $array[ self::NONCE_COLUMN ] ?? null );

		return (new Booking())
			->setBookingId( $array[ self::BOOKING_ID_COLUMN ] ?? null )
			->setBookingForm( $form )
			->setBookingStatus( BookingStatus::tryFrom($array[ self::STATUS_COLUMN ] ?? null) ?? BookingStatus::Pending)
			->setTransaction( ($array[self::TRANSACTION_ID_COLUMN] ?? null) !== null ? $getTransactionCallback( $array[self::TRANSACTION_ID_COLUMN] ) : null )
			->setDatabaseId( $array[ self::ID_COLUMN ] ?? null );
	}

	public function getCountAll(): int {
		$table = $this->getSqlTable();
		$count = $this->sql->count($table->getColumn(self::ID_COLUMN));
		$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;
	}

	/**
	 * @param string $beforeDate
	 *
	 * @return string[]
	 */
	public function getOldBookings(string $beforeDate): array {
		$table = $this->getSqlTable();
		$idColumn = $table->getColumn( self::ID_COLUMN );
		$idColumn->setAlias( 'Id' );
		$query = $this->sql->query( $table, $idColumn );
		$where = $this->sql->compare($table->getColumn(self::LAST_UPDATED_AT_COLUMN), date('Y-m-d H:i:s', strtotime($beforeDate)), false, false);
		$query->setWhere($where);

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

		$this->logDebug("Found " . count($result) . " bookings to delete", $result);

		$ids = [];
		foreach ($result as $row) {
			$ids[] = $row['Id'];
		}

		return $ids;
	}
}