<?php

namespace Planaday\Database\SQLDatabaseControllers;

use ArrayAccess;
use Countable;
use Planaday\Database\Abstraction\SQLQuery\ColumnTypeMap;
use Planaday\Database\Abstraction\SQLQuery\QueryBuilding\SQLColumn;
use Planaday\Database\Abstraction\SQLQuery\SQLFactory;
use Planaday\Platform\PlatformAbstraction\DatabaseInterface;
use Planaday\Utility\DataGrouping\Collection;
use Traversable;

class LinkingTable extends Table {

	public function __construct( DatabaseInterface $querySender, SQLFactory $sql, string $name, SQLColumn $column1, SQLColumn $column2, ColumnTypeMap $columnTypeMap) {
		$columns = [];
		$column1Name = str_replace('.', '_', $column1->getFullColumnIdentifier());
		$column2Name = str_replace('.', '_', $column2->getFullColumnIdentifier());

		$columns[$column1Name] =
		$columns[$column2Name] = implode(" ", [
			$columnTypeMap->STRING_ID(),
			$columnTypeMap->NOT_NULL()
		]);

		$constraints = [
			'CombinedPK' => "PRIMARY KEY ($column1Name, $column2Name)"
		];

		parent::__construct( $querySender, $sql, $name, $columns, $constraints );
	}

	/**
	 * @param string|int $id
	 * @param SQLColumn $searchColumn
	 * @param SQLColumn $resultColumn
	 *
	 * @return ArrayAccess<int|string, int|string>&Traversable<int|string, int|string>&Countable
	 */
	protected function getConnections(string|int $id, SQLColumn $searchColumn, SQLColumn $resultColumn): ArrayAccess&Traversable&Countable {
		$table = $this->sql->table($this->querySender->convertTableString($this->name));

		$searchColumnName = str_replace('.', '_', $searchColumn->getFullColumnIdentifier());
		$resultColumnName = str_replace('.', '_', $resultColumn->getFullColumnIdentifier());

		$column = $table->getColumn($searchColumnName);
		$query = $this->sql->query($table, $table);
		$query->setWhere($this->sql->compare($column, $id, true));

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

		$returnArray = [];

		if ($results !== null ) foreach ($results as $result) {
			$resultId = $result[$resultColumnName];
			if ($resultId !== null) {
				$returnArray[] = $resultId;
			}
		}

		return Collection::fromArray($returnArray);
	}

	/**
	 * @param string|int $id
	 * @param ArrayAccess<int|string, int|string>&Traversable<int|string, int|string>&Countable $linkIds
	 * @param SQLColumn $idColumn
	 * @param SQLColumn $linkIdColumn
	 *
	 * @return void
	 */
	protected function setConnections(string|int $id, ArrayAccess&Traversable&Countable $linkIds, SQLColumn $idColumn, SQLColumn $linkIdColumn): void {
		$table = $this->sql->table($this->querySender->convertTableString($this->name));

		$idColumnName = self::convertOuterColumnToInnerName($idColumn);
		$linkIdColumnName = self::convertOuterColumnToInnerName($linkIdColumn);

		$idColumn = $table->getColumn($idColumnName);
		$linkIdColumn = $table->getColumn($linkIdColumnName);

		$where = $this->sql->compare($idColumn, $id, true);
		$deleteQuery = "DELETE FROM " . $table->getFromQuery() . " WHERE " . $where->toConditionQuery();

		$this->querySender->sendQuery($deleteQuery);

		if (count($linkIds) === 0) {
			return;
		}

		$dataEntries = [];

		if (is_string($id)) {
			$id = '"' . $this->querySender->escapeSql($id) . '"';
		}

		foreach ($linkIds as $linkId) {
			if (is_string($linkId)) {
				$linkId = '"' . $this->querySender->escapeSql($linkId) . '"';
			}

			$dataEntries[] = "(" . $id . ", " . $linkId . ")";
		}

		$insertQuery = "INSERT INTO " . $table->getFromQuery() . " (" . $idColumn->getColumn() . ", " . $linkIdColumn->getColumn() . ") VALUES ";
		$insertQuery .= implode(", ", $dataEntries);

		$this->querySender->sendQuery($insertQuery);
	}

	public static function convertOuterColumnToInnerName(SQLColumn $columnName): string {
		return str_replace('.', '_', $columnName->getFullColumnIdentifier());
	}
}