<?php
namespace SabaiApps\Framework\Model;

use SabaiApps\Framework\Criteria;
use SabaiApps\Framework\Exception;
use SabaiApps\Framework\DB\AbstractDB;

/**
 * AbstractGateway class for managing database operations for entities.
 * Provides methods for CRUD operations, criteria-based queries, and visitor pattern implementation.
 */
abstract class AbstractGateway implements Criteria\IVisitor {

	/**
	 * @var AbstractDB Database instance used for queries.
	 */
	protected $_db;

	/**
	 * Sets the database instance.
	 *
	 * @param AbstractDB $db Database instance.
	 */
	public function setDB( AbstractDB $db ) {
		$this->_db = $db;
	}

	/**
	 * Gets the full table name including the resource prefix.
	 *
	 * @return string Table name.
	 */
	public function getTableName() {
		return $this->_db->getResourcePrefix() . $this->getName();
	}

	/**
	 * Gets all fields used within this gateway, including sort fields.
	 *
	 * @return array List of fields.
	 */
	public function getAllFields() {
		return $this->getFields() + $this->getSortFields();
	}

	/**
	 * Selects a row by its ID.
	 *
	 * @param int   $id Row ID.
	 * @param array $fields Fields to select.
	 * @return AbstractRowset Rowset object.
	 */
	public function selectById( $id, array $fields = array() ) {
		return $this->_db->query( $this->_getSelectByIdQuery( $id, $fields ) );
	}

	/**
	 * Selects rows by their IDs.
	 *
	 * @param array $ids Array of row IDs.
	 * @param array $fields Fields to select.
	 * @return AbstractRowset Rowset object.
	 */
	public function selectByIds( array $ids, array $fields = array() ) {
		return $this->_db->query( $this->_getSelectByIdsQuery( $ids, $fields ) );
	}

	/**
	 * Selects rows based on criteria.
	 *
	 * @param Criteria\AbstractCriteria $criteria Criteria object.
	 * @param array                     $fields Fields to select.
	 * @param int                       $limit Maximum number of rows to return.
	 * @param int                       $offset Number of rows to skip.
	 * @param array|null                $sort Sort fields.
	 * @param array|null                $order Sort order.
	 * @param string|null               $group Grouping field.
	 * @return AbstractRowset Rowset object.
	 */
	public function selectByCriteria( Criteria\AbstractCriteria $criteria, array $fields = array(), $limit = 0, $offset = 0, ?array $sort = null, ?array $order = null, $group = null ) {
		$criterions = array();
		$criteria->acceptVisitor( $this, $criterions );
		$query = $this->_getSelectByCriteriaQuery( implode( ' ', $criterions ), $fields );

		return $this->selectBySQL( $query, $limit, $offset, $sort, $order, $group );
	}

	/**
	 * Inserts a new row into the table.
	 *
	 * @param array $values Values to insert.
	 * @return int ID of the inserted row.
	 * @throws Exception If the insert operation fails.
	 */
	public function insert( array $values ) {
		$this->_beforeInsert( $values );
		if ( 1 !== $this->_db->exec( $this->_getInsertQuery( $values ) ) ) {
			throw new Exception( sprintf( 'Failed inserting a new row to the table %s. Last DB error: %s', $this->getTableName(), $this->_db->lastError() ) );
		}
		if ( false === $id = $this->_db->lastInsertId( $this->getTableName() ) ) {
			throw new Exception( sprintf( 'Failed fetching the last insert ID from %s. Last DB error: %s', $this->getTableName(), $this->_db->lastError() ) );
		}
		if ( 0 === $id ) { // PK is not an auto_increment field
			$id = $values[ $this->_getIdFieldName() ];
		} else {
			$values[ $this->_getIdFieldName() ] = $id;
		}
		$this->_afterInsert( $id, $values );

		return $id;
	}

	/**
	 * Hook called before inserting a new row.
	 *
	 * @param array $new Reference to the new row values.
	 */
	protected function _beforeInsert( array &$new ) {}

	/**
	 * Hook called after inserting a new row.
	 *
	 * @param mixed $id Inserted row ID.
	 * @param array $new Inserted row values.
	 */
	protected function _afterInsert( $id, array $new ) {}

	/**
	 * Updates a row by its ID.
	 *
	 * @param int   $id Row ID.
	 * @param array $values Values to update.
	 * @throws Exception If the update operation fails.
	 */
	public function updateById( $id, array $values ) {
		if ( ! $old = $this->selectById( $id )->fetchAssoc() ) {
			throw new Exception( sprintf( 'Failed fetching a row with an ID of %s from %s on before update. Last DB error: %s', $id, $this->getTableName(), $this->_db->lastError() ) );
		}
		$this->_beforeUpdate( $id, $values, $old );
		$this->_db->exec( $this->_getUpdateQuery( $id, $values ) );
		$this->_afterUpdate( $id, $values, $old );
	}

	/**
	 * Hook called before updating a row.
	 *
	 * @param mixed $id Row ID.
	 * @param array $new Reference to new values.
	 * @param array $old Old values.
	 */
	protected function _beforeUpdate( $id, array &$new, array $old ) {}

	/**
	 * Hook called after updating a row.
	 *
	 * @param mixed $id Row ID.
	 * @param array $new New values.
	 * @param array $old Old values.
	 */
	protected function _afterUpdate( $id, array $new, array $old ) {}

	/**
	 * Deletes a row by its ID.
	 *
	 * @param int $id Row ID.
	 * @throws Exception If the delete operation fails.
	 */
	public function deleteById( $id ) {
		if ( ! $old = $this->selectById( $id )->fetchAssoc() ) {
			throw new Exception( sprintf( 'Failed fetching a row with an ID of %s from %s on before delete. Last DB error: %s', $id, $this->getTableName(), $this->_db->lastError() ) );
		}
		$this->_beforeDelete( $id, $old );
		$this->_db->exec( $this->_getDeleteQuery( $id ) );
		$this->_afterDelete( $id, $old );
	}

	/**
	 * Hook called before deleting a row.
	 *
	 * @param mixed $id Row ID.
	 * @param array $old Old values.
	 */
	protected function _beforeDelete( $id, array $old ) {}

	/**
	 * Hook called after deleting a row.
	 *
	 * @param mixed $id Row ID.
	 * @param array $old Old values.
	 */
	protected function _afterDelete( $id, array $old ) {}

	/**
	 * Updates rows based on criteria.
	 *
	 * @param Criteria\AbstractCriteria $criteria Criteria object.
	 * @param array                     $values Values to update.
	 * @return int Number of affected rows.
	 * @throws Exception If the update operation fails.
	 */
	public function updateByCriteria( Criteria\AbstractCriteria $criteria, array $values ) {
		$sets   = array();
		$fields = $this->getFields();
		foreach ( array_keys( $values ) as $k ) {
			if ( isset( $fields[ $k ] ) ) {
				$operator = '=';
				$this->_sanitizeForQuery( $values[ $k ], $fields[ $k ], $operator );
				$sets[ $k ] = $k . $operator . $values[ $k ];
			}
		}
		$criterions = array();
		$criteria->acceptVisitor( $this, $criterions );

		return $this->_db->exec( $this->_getUpdateByCriteriaQuery( implode( ' ', $criterions ), $sets ) );
	}

	/**
	 * Deletes rows based on criteria.
	 *
	 * @param Criteria\AbstractCriteria $criteria Criteria object.
	 * @return int Number of affected rows.
	 */
	public function deleteByCriteria( Criteria\AbstractCriteria $criteria ) {
		$criterions = array();
		$criteria->acceptVisitor( $this, $criterions );

		return $this->_db->exec( $this->_getDeleteByCriteriaQuery( implode( ' ', $criterions ) ) );
	}

	/**
	 * Counts rows based on criteria.
	 *
	 * @param Criteria\AbstractCriteria $criteria Criteria object.
	 * @return int Number of rows matching the criteria.
	 */
	public function countByCriteria( Criteria\AbstractCriteria $criteria ) {
		$criterions = array();
		$criteria->acceptVisitor( $this, $criterions );

		return $this->_db->query( $this->_getCountByCriteriaQuery( implode( ' ', $criterions ) ) )->fetchSingle();
	}

	/**
	 * Selects rows using a raw SQL query.
	 *
	 * @param string      $sql SQL query string.
	 * @param int         $limit Maximum number of rows to return.
	 * @param int         $offset Number of rows to skip.
	 * @param array|null  $sort Sort fields.
	 * @param array|null  $order Sort order.
	 * @param string|null $group Grouping field.
	 * @return AbstractRowset Rowset object.
	 */
	public function selectBySQL( $sql, $limit = 0, $offset = 0, ?array $sort = null, ?array $order = null, $group = null ) {
		if ( isset( $group ) ) {
			$fields = $this->getFields();
			$groups = array();
			foreach ( (array) $group as $_group ) {
				if ( isset( $fields[ $_group ] ) ) {
					$groups[] = $_group;
				}
			}
			if ( ! empty( $groups ) ) {
				$sql .= ' GROUP BY ' . implode( ', ', $groups );
			}
		}
		if ( isset( $sort ) ) {
			$sort_fields = $this->getSortFields();
			foreach ( array_keys( $sort ) as $i ) {
				if ( isset( $sort_fields[ $sort[ $i ] ] ) ) {
					$order_by[] = $sort[ $i ] . ' ' . ( isset( $order[ $i ] ) && $order[ $i ] == 'DESC' ? 'DESC' : 'ASC' );
				}
			}
			if ( isset( $order_by ) ) {
				$sql .= ' ORDER BY ' . implode( ', ', $order_by );
			}
		}

		return $this->_db->query( $sql, $limit, $offset );
	}

	/**
	 * Visitor pattern: handles empty criteria.
	 *
	 * @param Criteria\EmptyCriteria $criteria
	 * @param array                  $criterions
	 */
	public function visitCriteriaEmpty( Criteria\EmptyCriteria $criteria, &$criterions ) {
		$criterions[] = '1=1';
	}

	/**
	 * Visitor pattern: handles composite criteria.
	 *
	 * @param Criteria\CompositeCriteria $criteria
	 * @param array                      $criterions
	 */
	public function visitCriteriaComposite( Criteria\CompositeCriteria $criteria, &$criterions ) {
		if ( $criteria->isEmpty() ) {
			$criterions[] = '1=1';
			return;
		}
		$elements     = $criteria->getElements();
		$count        = count( $elements );
		$conditions   = $criteria->getConditions();
		$criterions[] = '(';
		$elements[0]->acceptVisitor( $this, $criterions );
		for ( $i = 1; $i < $count; $i++ ) {
			$criterions[] = $conditions[ $i ];
			$elements[ $i ]->acceptVisitor( $this, $criterions );
		}
		$criterions[] = ')';
	}

	/**
	 * Visitor pattern: handles NOT composite criteria.
	 *
	 * @param Criteria\CompositeNotCriteria $criteria
	 * @param array                         $criterions
	 */
	public function visitCriteriaCompositeNot( Criteria\CompositeNotCriteria $criteria, &$criterions ) {
		$criterions[] = 'NOT';
		$criterions[] = $this->visitCriteriaComposite( $criteria, $criterions );
	}

	/**
	 * Helper for visiting value-based criteria.
	 *
	 * @param Criteria\AbstractValueCriteria $criteria
	 * @param array                          $criterions
	 * @param string                         $operator
	 */
	private function _visitCriteriaValue( Criteria\AbstractValueCriteria $criteria, &$criterions, $operator ) {
		$field  = $criteria->getField();
		$fields = $this->getAllFields();
		if ( ! isset( $fields[ $field ] ) ) {
			return;
		}

		$value = $criteria->getValue();
		$this->_sanitizeForQuery( $value, $fields[ $field ], $operator );
		$criterions[] = $field;
		$criterions[] = $operator;
		$criterions[] = $value;
	}

	/**
	 * Visitor pattern: handles "is" criteria.
	 */
	public function visitCriteriaIs( Criteria\IsCriteria $criteria, &$criterions ) {
		$this->_visitCriteriaValue( $criteria, $criterions, '=' );
	}

	/**
	 * Visitor pattern: handles "is not" criteria.
	 */
	public function visitCriteriaIsNot( Criteria\IsNotCriteria $criteria, &$criterions ) {
		$this->_visitCriteriaValue( $criteria, $criterions, '!=' );
	}

	/**
	 * Visitor pattern: handles "smaller than" criteria.
	 */
	public function visitCriteriaIsSmallerThan( Criteria\IsSmallerThanCriteria $criteria, &$criterions ) {
		$this->_visitCriteriaValue( $criteria, $criterions, '<' );
	}

	/**
	 * Visitor pattern: handles "greater than" criteria.
	 */
	public function visitCriteriaIsGreaterThan( Criteria\IsGreaterThanCriteria $criteria, &$criterions ) {
		$this->_visitCriteriaValue( $criteria, $criterions, '>' );
	}

	/**
	 * Visitor pattern: handles "or smaller than" criteria.
	 */
	public function visitCriteriaIsOrSmallerThan( Criteria\IsOrSmallerThanCriteria $criteria, &$criterions ) {
		$this->_visitCriteriaValue( $criteria, $criterions, '<=' );
	}

	/**
	 * Visitor pattern: handles "or greater than" criteria.
	 */
	public function visitCriteriaIsOrGreaterThan( Criteria\IsOrGreaterThanCriteria $criteria, &$criterions ) {
		$this->_visitCriteriaValue( $criteria, $criterions, '>=' );
	}

	/**
	 * Visitor pattern: handles "is null" criteria.
	 */
	public function visitCriteriaIsNull( Criteria\IsNullCriteria $criteria, &$criterions ) {
		$field  = $criteria->getField();
		$fields = $this->getAllFields();
		if ( ! isset( $fields[ $field ] ) ) {
			return;
		}

		$criterions[] = $field;
		$criterions[] = 'IS NULL';
	}

	/**
	 * Visitor pattern: handles "is not null" criteria.
	 */
	public function visitCriteriaIsNotNull( Criteria\IsNotNullCriteria $criteria, &$criterions ) {
		$field  = $criteria->getField();
		$fields = $this->getAllFields();
		if ( ! isset( $fields[ $field ] ) ) {
			return;
		}

		$criterions[] = $field;
		$criterions[] = 'IS NOT NULL';
	}

	/**
	 * Helper for visiting array-based criteria.
	 *
	 * @param Criteria\AbstractArrayCriteria $criteria
	 * @param array                          $criterions
	 * @param string                         $format
	 */
	private function _visitCriteriaArray( Criteria\AbstractArrayCriteria $criteria, &$criterions, $format ) {
		$field  = $criteria->getField();
		$fields = $this->getAllFields();
		if ( ! isset( $fields[ $field ] ) ) {
			return;
		}

		$values = $criteria->getArray();
		if ( ! empty( $values ) ) {
			$data_type = $fields[ $field ];
			$operator  = null;
			foreach ( $values as $v ) {
				$this->_sanitizeForQuery( $v, $data_type, $operator );
				$value[] = $v;
			}
			$criterions[] = sprintf( $format, $field, implode( ',', $value ) );
		}
	}

	/**
	 * Visitor pattern: handles "in" criteria.
	 */
	public function visitCriteriaIn( Criteria\InCriteria $criteria, &$criterions ) {
		$this->_visitCriteriaArray( $criteria, $criterions, '%s IN (%s)' );
	}

	/**
	 * Visitor pattern: handles "not in" criteria.
	 */
	public function visitCriteriaNotIn( Criteria\NotInCriteria $criteria, &$criterions ) {
		$this->_visitCriteriaArray( $criteria, $criterions, '%s NOT IN (%s)' );
	}

	/**
	 * Helper for visiting string-based criteria.
	 *
	 * @param Criteria\AbstractValueCriteria $criteria
	 * @param array                          $criterions
	 * @param string                         $format
	 * @param string                         $operator
	 */
	private function _visitCriteriaString( Criteria\AbstractValueCriteria $criteria, &$criterions, $format, $operator = 'LIKE' ) {
		$field  = $criteria->getField();
		$fields = $this->getAllFields();
		if ( ! isset( $fields[ $field ] ) ) {
			return;
		}

		$value = sprintf( $format, $criteria->getValue() );
		$this->_sanitizeForQuery( $value, $fields[ $field ], $operator );
		$criterions[] = $field;
		$criterions[] = $operator;
		$criterions[] = $value;
	}

	/**
	 * Visitor pattern: handles "starts with" criteria.
	 */
	public function visitCriteriaStartsWith( Criteria\StartsWithCriteria $criteria, &$criterions ) {
		$this->_visitCriteriaString( $criteria, $criterions, '%s%%' );
	}

	/**
	 * Visitor pattern: handles "ends with" criteria.
	 */
	public function visitCriteriaEndsWith( Criteria\EndsWithCriteria $criteria, &$criterions ) {
		$this->_visitCriteriaString( $criteria, $criterions, '%%%s' );
	}

	/**
	 * Visitor pattern: handles "contains" criteria.
	 */
	public function visitCriteriaContains( Criteria\ContainsCriteria $criteria, &$criterions ) {
		$this->_visitCriteriaString( $criteria, $criterions, '%%%s%%' );
	}

	/**
	 * Visitor pattern: handles "not contains" criteria.
	 */
	public function visitCriteriaNotContains( Criteria\ContainsCriteria $criteria, &$criterions ) {
		$this->_visitCriteriaString( $criteria, $criterions, '%%%s%%', 'NOT LIKE' );
	}

	/**
	 * Helper for visiting field-to-field comparison criteria.
	 *
	 * @param Criteria\AbstractFieldCriteria $criteria
	 * @param array                          $criterions
	 * @param string                         $operator
	 */
	private function _visitCriteriaField( Criteria\AbstractFieldCriteria $criteria, &$criterions, $operator ) {
		$field  = $criteria->getField();
		$field2 = $criteria->getField2();
		$fields = $this->getAllFields();
		if ( ! isset( $fields[ $field ] ) || ! isset( $fields[ $field2 ] ) ) {
			return;
		}

		$criterions[] = $field;
		$criterions[] = $operator;
		$criterions[] = $field2;
	}

	/**
	 * Visitor pattern: handles "is field" criteria.
	 */
	public function visitCriteriaIsField( Criteria\IsFieldCriteria $criteria, &$criterions ) {
		$this->_visitCriteriaField( $criteria, $criterions, '=' );
	}

	/**
	 * Visitor pattern: handles "is not field" criteria.
	 */
	public function visitCriteriaIsNotField( Criteria\IsNotFieldCriteria $criteria, &$criterions ) {
		$this->_visitCriteriaField( $criteria, $criterions, '!=' );
	}

	/**
	 * Visitor pattern: handles "smaller than field" criteria.
	 */
	public function visitCriteriaIsSmallerThanField( Criteria\IsSmallerThanFieldCriteria $criteria, &$criterions ) {
		$this->_visitCriteriaField( $criteria, $criterions, '<' );
	}

	/**
	 * Visitor pattern: handles "greater than field" criteria.
	 */
	public function visitCriteriaIsGreaterThanField( Criteria\IsGreaterThanFieldCriteria $criteria, &$criterions ) {
		$this->_visitCriteriaField( $criteria, $criterions, '>' );
	}

	/**
	 * Visitor pattern: handles "or smaller than field" criteria.
	 */
	public function visitCriteriaIsOrSmallerThanField( Criteria\IsOrSmallerThanFieldCriteria $criteria, &$criterions ) {
		$this->_visitCriteriaField( $criteria, $criterions, '<=' );
	}

	/**
	 * Visitor pattern: handles "or greater than field" criteria.
	 */
	public function visitCriteriaIsOrGreaterThanField( Criteria\IsOrGreaterThanFieldCriteria $criteria, &$criterions ) {
		$this->_visitCriteriaField( $criteria, $criterions, '>=' );
	}

	/**
	 * Sanitizes a value for use in a query based on its data type.
	 *
	 * @param mixed  $value
	 * @param int    $dataType
	 * @param string $operator
	 */
	private function _sanitizeForQuery( &$value, $dataType, &$operator ) {
		switch ( $dataType ) {
			case Model::KEY_TYPE_INT_NULL:
				if ( is_numeric( $value ) ) {
					$value = intval( $value );
				} else {
					$value    = 'NULL';
					$operator = ( $operator === '!=' ) ? 'IS NOT' : 'IS';
				}
				return;
			case Model::KEY_TYPE_INT:
				$value = intval( $value );
				return;
			case Model::KEY_TYPE_TEXT:
			case Model::KEY_TYPE_JSON:
			case Model::KEY_TYPE_VARCHAR:
				$value = $this->_db->escapeString( $value );
				return;
			case Model::KEY_TYPE_FLOAT:
				$value = str_replace( ',', '.', floatval( $value ) );
				return;
			case Model::KEY_TYPE_BOOL:
				$value = $this->_db->escapeBool( $value );
				return;
			case Model::KEY_TYPE_BLOB:
				$value = $this->_db->escapeBlob( $value );
				return;
			case Model::KEY_TYPE_DATETIME:
				$value = $this->_db->escapeDatetime( $value );
				return;
			default:
				$value = $this->_db->escapeString( $value );
		}
	}

	/**
	 * Gets the fields that can be used for sorting.
	 * This method will only be overwritten by assoc entities.
	 *
	 * @return array
	 */
	public function getSortFields() {
		return $this->getFields();
	}

	/**
	 * Gets the last error message returned by the database driver.
	 *
	 * @return string
	 */
	public function getError() {
		return $this->_db->lastError();
	}

	// Abstract methods to be implemented by subclasses.
	abstract public function getName();
	abstract public function getFields();
	abstract protected function _getIdFieldName();
	abstract protected function _getSelectByIdQuery( $id, $fields );
	abstract protected function _getSelectByIdsQuery( $ids, $fields );
	abstract protected function _getSelectByCriteriaQuery( $criteriaStr, $fields );
	abstract protected function _getInsertQuery( &$values );
	abstract protected function _getUpdateQuery( $id, $values );
	abstract protected function _getDeleteQuery( $id );
	abstract protected function _getUpdateByCriteriaQuery( $criteriaStr, $sets );
	abstract protected function _getDeleteByCriteriaQuery( $criteriaStr );
	abstract protected function _getCountByCriteriaQuery( $criteriaStr );
}
