<?php

namespace Planaday\Database\SQLDatabaseControllers\Booking;

use DateTime;
use Exception;
use Planaday\Database\Abstraction\SQLQuery\ColumnTypeMap;
use Planaday\Database\Abstraction\SQLQuery\SQLFactory;
use Planaday\Database\SQLDatabaseControllers\Table;
use Planaday\Entities\Booking\DTO\Address;
use Planaday\Entities\Booking\DTO\BookingStudent;
use Planaday\Platform\PlatformAbstraction\DatabaseInterface;
use Planaday\Utility\DataGrouping\Map;

class BookingStudentTable extends Table {
	public const BOOKING_STUDENT_TABLE = 'booking_student';

	public const ID_COLUMN = 'id';
	public const BOOKING_ID_COLUMN = 'booking_id';
	public const GENDER_COLUMN = 'gender';
	public const INITIALS_COLUMN = 'initials';
	public const FIRST_NAME_COLUMN = 'first_name';
	public const NICK_NAME_COLUMN = 'nick_name';
	public const PREFIX_COLUMN = 'prefix';
	public const LAST_NAME_COLUMN = 'last_name';
	public const MAIDEN_NAME_COLUMN = 'maiden_name';
	public const ADDRESS_COLUMN = 'address_id';
	public const E_MAIL_COLUMN = 'email';
	public const PHONE_NUMBER_COLUMN = 'phone_number';
	public const DATE_OF_BIRTH_COLUMN = 'date_of_birth';
	public const PLACE_OF_BIRTH_COLUMN = 'place_of_birth';
	public const COMPANY_POSITION_COLUMN = 'company_position';
	public const COST_CENTER_CODE_COLUMN = 'cost_center_code';
	public const EMPLOYEE_NUMBER_COLUMN = 'employee_number';
	public const INTERNAL_REFERENCE_COLUMN = 'internal_reference';
	public const REMARK_COLUMN = 'remark';
	public const STAP_REGULATION_COLUMN = 'stap_regulation';
	public const IS_CONTACT_COLUMN = 'is_contact';
	public const PROCESS_CODE_95_COLUMN = 'process_code_95';
	public const PROCESS_SOOB_COLUMN = 'process_soob';

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

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

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

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

		$columns[self::COMPANY_POSITION_COLUMN] =
		$columns[self::INITIALS_COLUMN] =
		$columns[self::PREFIX_COLUMN] =
		$columns[self::NICK_NAME_COLUMN] =
		$columns[self::FIRST_NAME_COLUMN] =
		$columns[self::LAST_NAME_COLUMN] =
		$columns[self::MAIDEN_NAME_COLUMN] = implode(" ", [
			$columnTypeMap->NAME(),
			$columnTypeMap->DEFAULT_NULL()
		]);

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

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

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

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

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

		$columns[self::COST_CENTER_CODE_COLUMN] =
		$columns[self::EMPLOYEE_NUMBER_COLUMN] =
		$columns[self::INTERNAL_REFERENCE_COLUMN] = implode(" ", [
			$columnTypeMap->CODE(),
			$columnTypeMap->DEFAULT_NULL()
		]);

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

		$columns[self::STAP_REGULATION_COLUMN] =
		$columns[self::PROCESS_CODE_95_COLUMN] =
		$columns[self::PROCESS_SOOB_COLUMN] =
		$columns[self::IS_CONTACT_COLUMN] = implode(" ", [
			$columnTypeMap->BOOLEAN(),
			$columnTypeMap->DEFAULT_NULL()
		]);

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

	/**
	 * @param BookingStudent $student
	 * @param int|null $address
	 * @param string $bookingId
	 *
	 * @return int id of created student if new, otherwise id of existing
	 */
	public function set(BookingStudent $student, ?int $address, string $bookingId):int {
		$table = $this->getSqlTable();
		$array = $this->studentObjectToArray($student, $address, $bookingId);

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

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

			return $student->getDatabaseId();
		}

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

		$student->setDatabaseId($this->querySender->getLastGeneratedInsertId());
		return $student->getDatabaseId();
	}

	public function remove(int $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 int $id
	 * @param callable(int):array<int, string|string[]> $extraFields
	 * @param callable(int):(Address|null) $getAddressCallback
	 *
	 * @return BookingStudent|null
	 */
	public function get(int $id, callable $extraFields, callable $getAddressCallback): ?BookingStudent {
		$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 student found with id $id", $result);
			return null;
		}

		return $this->studentArrayToObject($result[0], $extraFields, $getAddressCallback);
	}

	public function studentObjectToArray(BookingStudent $student, ?int $addressId, string $bookingId): array {
		$returnArray = [];

		$returnArray[ self::BOOKING_ID_COLUMN ] = $bookingId;
		$returnArray[ self::GENDER_COLUMN ] = $student->getGender();
		$returnArray[ self::COMPANY_POSITION_COLUMN ] = $student->getCompanyPosition();
		$returnArray[ self::INITIALS_COLUMN ] = $student->getInitials();
		$returnArray[ self::PREFIX_COLUMN ] = $student->getPrefix();
		$returnArray[ self::NICK_NAME_COLUMN ] = $student->getNickName();
		$returnArray[ self::FIRST_NAME_COLUMN ] = $student->getFirstName();
		$returnArray[ self::LAST_NAME_COLUMN ] = $student->getLastName();
		$returnArray[ self::MAIDEN_NAME_COLUMN ] = $student->getMaidenName();
		$returnArray[ self::ADDRESS_COLUMN ] = $addressId;
		$returnArray[ self::E_MAIL_COLUMN ] = $student->getEMail();
		$returnArray[ self::PHONE_NUMBER_COLUMN ] = $student->getPhoneNumber();
		$returnArray[ self::DATE_OF_BIRTH_COLUMN ] = $student->getDateOfBirth()?->format( "Y-m-d" );
		$returnArray[ self::PLACE_OF_BIRTH_COLUMN ] = $student->getPlaceOfBirth();
		$returnArray[ self::COST_CENTER_CODE_COLUMN ] = $student->getCostCenterCode();
		$returnArray[ self::EMPLOYEE_NUMBER_COLUMN ] = $student->getEmployeeNumber();
		$returnArray[ self::INTERNAL_REFERENCE_COLUMN ] = $student->getInternalReference();
		$returnArray[ self::REMARK_COLUMN ] = $student->getRemark();
		$returnArray[ self::STAP_REGULATION_COLUMN ] = $student->isStapRegulation();
		$returnArray[ self::PROCESS_CODE_95_COLUMN ] = $student->isProcessCode95();
		$returnArray[ self::PROCESS_SOOB_COLUMN ] = $student->isProcessSoob();
		$returnArray[ self::IS_CONTACT_COLUMN ] = $student->isContact();

		return $returnArray;
	}

	/**
	 * @param array $array
	 * @param callable(int):array<int, string|string[]> $getExtraFieldsClosure
	 * @param callable(int):(Address|null) $getAddressCallback
	 *
	 * @return BookingStudent
	 */
	public function studentArrayToObject(array $array, callable $getExtraFieldsClosure, callable $getAddressCallback): BookingStudent {
		$address = ($array[self::ADDRESS_COLUMN] != null) ? $getAddressCallback( $array[self::ADDRESS_COLUMN] ) : null;
		$address = $address ?? new Address();

		if ($array[ self::DATE_OF_BIRTH_COLUMN ]) try {
			$dateTime = new DateTime($array[self::DATE_OF_BIRTH_COLUMN]);
		} catch ( Exception ){
			$this->logError("Could not parse date of birth", $array);
			$dateTime = null;
		} else $dateTime = null;

		return (new BookingStudent())
			->setGender( $array[ self::GENDER_COLUMN ] )
			->setInitials( $array[ self::INITIALS_COLUMN ] )
			->setPrefix( $array[ self::PREFIX_COLUMN ] )
			->setNickName( $array[ self::NICK_NAME_COLUMN ] )
			->setFirstName( $array[ self::FIRST_NAME_COLUMN ] )
			->setLastName( $array[ self::LAST_NAME_COLUMN ] )
			->setMaidenName( $array[ self::MAIDEN_NAME_COLUMN ] )
			->setEmail( $array[ self::E_MAIL_COLUMN ] )
			->setPhoneNumber( $array[ self::PHONE_NUMBER_COLUMN ] )
			->setDateOfBirth( $dateTime )
			->setPlaceOfBirth( $array[ self::PLACE_OF_BIRTH_COLUMN ] )
			->setCompanyPosition( $array[ self::COMPANY_POSITION_COLUMN ] )
			->setCostCenterCode( $array[ self::COST_CENTER_CODE_COLUMN ] )
			->setEmployeeNumber( $array[ self::EMPLOYEE_NUMBER_COLUMN ] )
			->setInternalReference( $array[ self::INTERNAL_REFERENCE_COLUMN ] )
			->setRemark( $array[ self::REMARK_COLUMN ] )
			->setStapRegulation( $array[ self::STAP_REGULATION_COLUMN ] )
			->setIsContact( $array[ self::IS_CONTACT_COLUMN ] )
			->setProcessCode95( $array[ self::PROCESS_CODE_95_COLUMN ] )
			->setProcessSoob( $array[ self::PROCESS_SOOB_COLUMN ] )
			->setExtraFields( Map::fromArray($getExtraFieldsClosure( $array[ self::ID_COLUMN ] )) )
			->setAddress( $address )
			->setDatabaseId( $array[ self::ID_COLUMN ] );
	}

	/**
	 * @param string $bookingId
	 * @param callable(int):array<int, string|string[]> $getExtraFieldsClosure
	 * @param callable(int):(Address|null) $getAddressCallback
	 *
	 * @return BookingStudent[]
	 */
	public function getByBookingId(string $bookingId, callable $getExtraFieldsClosure, callable $getAddressCallback): array {
		$table = $this->getSqlTable();
		$where = $this->sql->compare( $table->getColumn( self::BOOKING_ID_COLUMN ), $bookingId, true );
		$query = $this->sql->query( $table, $table );
		$query->setWhere( $where );

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

			return [];
		}

		$students = [];

		foreach ( $result as $student ) {
			$students[] = $this->studentArrayToObject( $student, $getExtraFieldsClosure, $getAddressCallback );
		}

		return $students;
	}
}