<?php

namespace Planaday\Database\SQLDatabaseControllers\Booking;

use Planaday\Database\Abstraction\SQLQuery\ColumnTypeMap;
use Planaday\Database\Abstraction\SQLQuery\SQLFactory;
use Planaday\Database\SQLDatabaseControllers\Table;
use Planaday\Platform\PlatformAbstraction\DatabaseInterface;

class StudentExtraFieldTable extends Table {
	public const STUDENT_EXTRA_FIELD_TABLE = 'student_extra_field';

	public const ID_COLUMN = 'id';
	public const STUDENT_ID_COLUMN = 'student_id';
	public const EXTRA_FIELD_ID_COLUMN = 'extra_field_id';
	public const VALUE_COLUMN = 'value';

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

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

		$columns[self::ID_COLUMN] .= ' ' . $columnTypeMap->AUTO_INCREMENT() . ' ' . $columnTypeMap->PRIMARY_KEY();

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

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

	/**
	 * @param int $studentId
	 * @param int $extraFieldId
	 * @param string|string[] $value
	 * @param int|null $id
	 *
	 * @return int id of created booking attribute if new, otherwise id of existing
	 */
	public function set(int $studentId, int $extraFieldId, string|array $value, ?int $id = null):int {
		if (!is_array($value)) $value = [$value];

		$table = $this->getSqlTable();
		$array = $this->valuesToArray($studentId, $extraFieldId, $value);

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

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

			return $id;
		}

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

		return $this->querySender->getLastGeneratedInsertId();
	}

	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() );
	}

	public function removeByStudentId(int $studentId):void {
		$table = $this->getSqlTable();
		$where = $this->sql->compare( $table->getColumn( self::STUDENT_ID_COLUMN ), $studentId, true );
		$query = $this->sql->delete( $table, $where );

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

	public function get(int $id): ?array {
		$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 extra field found with id $id", $result);
			return null;
		}

		return $this->resultArrayToOutputArray($result[0]);
	}

	public function getByStudentId(int $studentId): array {
		$table = $this->getSqlTable();
		$where = $this->sql->compare( $table->getColumn( self::STUDENT_ID_COLUMN ), $studentId, 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 extra field found with student id $studentId", $result );

			return [];
		}

		foreach ( $result as $index => $item ) {
			$result[ $index ] = $this->resultArrayToOutputArray( $item );
		}

		return $result;
	}

	/**
	 * @param int $studentId
	 * @param int $extraFieldId
	 * @param string[] $value
	 *
	 * @return array
	 */
	public function valuesToArray(int $studentId, int $extraFieldId, array $value): array {
		$returnArray = [];

		$returnArray[ self::STUDENT_ID_COLUMN ] = $studentId;
		$returnArray[ self::EXTRA_FIELD_ID_COLUMN ] = $extraFieldId;

		foreach ($value as $index => $item){
			$value[$index] = str_replace("'", "\'", $item);
		}
		$returnArray[self::VALUE_COLUMN] = implode("','", $value);

		return $returnArray;
	}

	public function resultArrayToOutputArray(array $array): array {
		$array[self::VALUE_COLUMN] = explode("','", $array[self::VALUE_COLUMN]);

		foreach ($array[self::VALUE_COLUMN] as $index => $item){
			$array[self::VALUE_COLUMN][$index] = str_replace("\'", "'", $item);
		}

		return $array;
	}
}