<?php

namespace App\DAO;

use App\DAO\DAOResponse\DAOResponse;
use App\DAO\DAOResponse\DAOResponseInterface;
use App\DAO\Filter\QueryFilter;
use App\DAO\Order\QueryOrder;
use App\DAO\Pagination\Pagination;
use App\Models\ModelEntity;
use App\Services\LogService;
use App\Utils\Collections\QueryFilterCollection;
use App\Utils\Collections\QueryOrderCollection;
use CodeIgniter\Model;

/**
 * Class DAO
 *
 * @package App\DAO
 */
class DAO extends ModelEntity {
	/**
	 *
	 * @var string
	 */
	private $limit_regex = '/[Ll][Ii][Mm][Ii][Tt](\s)*(\d+|\?|:\w+:)?\1*,?\2*(\d+|\?||:\w+:)?\3*$/';

	/**
	 *
	 * @param string $sql_origin
	 * @param Pagination $pagination
	 */
	protected function setNumRowsPagination(string $sql_origin, Pagination &$pagination, array $binds = [ ]): void {

		$sql = trim( $sql_origin );
		$sql = preg_replace( '/\s{2,}/', ' ', $sql );
		$sql = preg_replace( '/SELECT.*FROM/', 'SELECT COUNT(*) as num_rows FROM', $sql );
		$sql = preg_replace( '/select.*from/', 'SELECT COUNT(*) as num_rows FROM', $sql );
		$sql = preg_replace( $this->limit_regex, '', $sql );
		$query = $this->db->query( $sql, $binds );
		$error = $this->db->error();
		if ($error && $error ['code']) {
			$log_service = new LogService();
			$log_service->log_error( $error ['code'], 'Erro durante a busca do total de linhas de uma paginação.', [ 
					'sql_origin' => $sql_origin
			] );
		} else {
			$result_array = $query->getResultArray();
			$result_array = $result_array ? $result_array [0] : [ ];
			if (isset( $result_array ['num_rows'] )) {
				$pagination->setNumRows( $result_array ['num_rows'] );
			} else {
				$pagination->setNumRows( count( $result_array ) );
			}
		}

	}
	
	protected function setNumRowsPagination2(string $sql_count, Pagination &$pagination, array $binds = [ ]): void {
		
		$sql = trim( $sql_count );
// 		$sql = preg_replace( '/\s{2,}/', ' ', $sql );
// 		$sql = preg_replace( '/SELECT.*FROM/', 'SELECT COUNT(*) as num_rows FROM', $sql );
// 		$sql = preg_replace( '/select.*from/', 'SELECT COUNT(*) as num_rows FROM', $sql );
// 		$sql = preg_replace( $this->limit_regex, '', $sql );
		$query = $this->db->query( $sql, $binds );
		$error = $this->db->error();
		if ($error && $error ['code']) {
			$log_service = new LogService();
			$log_service->log_error( $error ['code'], 'Erro durante a busca do total de linhas de uma paginação.', [
					'sql_origin' => $sql_count
			] );
		} else {
			$result_array = $query->getResultArray();
			$result_array = $result_array ? $result_array [0] : [ ];
			if (isset( $result_array ['num_rows'] )) {
				$pagination->setNumRows( $result_array ['num_rows'] );
			} else {
				$pagination->setNumRows( count( $result_array ) );
			}
		}
		
	}

	/**
	 *
	 * @param array|null $result_array
	 * @return int
	 */
	protected function calculateDAOResponseStatusForSelect(?array $result_array): int {

		$error_database = $this->db->error();
		if ($error_database ['code']) {
			$status_response = DAOResponseInterface::DATABASE_CONNECTION_ERROR;
			$log_service = new LogService();
			$log_service->log_error( $status_response, 'Ocorreu um erro durante a listagem dos pacientes.', $error_database );
		} else {
			$status_response = count( $result_array ?? [ ]) > 0 ? DAOResponseInterface::SUCESS : DAOResponseInterface::RECORD_NOT_FOUND;
		}
		return $status_response;

	}

	/**
	 *
	 * @param array $binds
	 * @param Pagination|null $pagination
	 * @param QueryFilterCollection|null $query_filter_collection
	 * @param QueryOrderCollection|null $query_order_collection
	 * @param string $sql
	 * @param callable $data_mapper
	 * @return DAOResponse
	 */
	protected function getDAOResponseFromPaginatedSelect(array $binds, ?Pagination $pagination, ?QueryFilterCollection $query_filter_collection,
			?QueryOrderCollection $query_order_collection, string $sql, callable $data_mapper): DAOResponse {

		if ($query_filter_collection) {
			$query_filter_collection->forEach( function (QueryFilter $query_filter) use (&$sql) {
				$this->insertQueryFilterOnSQLWhere( $query_filter, $sql );
			} );
		}
		if ($pagination) {
			$sql .= ' LIMIT :offset: ,:limit:';
			$binds ['offset'] = $pagination->calculateOffset();
			$binds ['limit'] = $pagination->calculateLimit();
		}
		if ($query_order_collection) {
			$this->insertQueryOrderOnSQL( $query_order_collection, $sql );
		}
		log_message( 'info', 'Consulta executada: ' . trim( $sql ) );
		$query = $this->db->query( $sql, $binds );
		$result_array = $query->getResultArray();
		$dao_response = DAOResponse::builder()->setStatus( $this->calculateDAOResponseStatusForSelect( $result_array ) )
			->build();
		if ($dao_response->getStatus() === DAOResponseInterface::SUCESS) {
			$dao_response->setData( $data_mapper( $result_array ) );
			if ($pagination) {
				$this->setNumRowsPagination( $sql, $pagination, $binds );
				$dao_response->setPagination( $pagination );
			}
		}
		return $dao_response;

	}

	protected function getDAOResponseFromPaginatedSelect2(array $binds, ?Pagination $pagination, ?QueryFilterCollection $query_filter_collection,
			?QueryOrderCollection $query_order_collection, string $sql, string $sql_count, callable $data_mapper): DAOResponse {
				
				if ($query_filter_collection) {
					$query_filter_collection->forEach( function (QueryFilter $query_filter) use (&$sql) {
						$this->insertQueryFilterOnSQLWhere( $query_filter, $sql );
					} );
				}
				
				if ($query_filter_collection) {
					$query_filter_collection->forEach( function (QueryFilter $query_filter) use (&$sql_count) {
						$this->insertQueryFilterOnSQLWhere( $query_filter, $sql_count );
					} );
				}
				if ($pagination) {
					$sql .= ' LIMIT :offset: ,:limit:';
					$binds ['offset'] = $pagination->calculateOffset();
					$binds ['limit'] = $pagination->calculateLimit();
				}
				if ($query_order_collection) {
					$this->insertQueryOrderOnSQL( $query_order_collection, $sql );
				}
				log_message( 'info', 'Consulta executada: ' . trim( $sql ) );
				$query = $this->db->query( $sql, $binds );
				$result_array = $query->getResultArray();
				$dao_response = DAOResponse::builder()->setStatus( $this->calculateDAOResponseStatusForSelect( $result_array ) )
				->build();
				if ($dao_response->getStatus() === DAOResponseInterface::SUCESS) {
					$dao_response->setData( $data_mapper( $result_array ) );
					if ($pagination) {
						$this->setNumRowsPagination2( $sql_count, $pagination, $binds );
						$dao_response->setPagination( $pagination );
					}
				}
				return $dao_response;
				
	}
	
	
	/**
	 *
	 * @param string $sql
	 * @param callable $data_mapper
	 * @param QueryFilterCollection $query_filter_collection
	 * @param QueryOrderCollection $query_order_collection
	 * @param array $binds
	 * @return DAOResponse
	 */
	protected function getDAOResponseFromMultipleRowSelect(string $sql, callable $data_mapper, QueryFilterCollection $query_filter_collection,
			QueryOrderCollection $query_order_collection, array $binds = [ ]): DAOResponse {

		if ($query_filter_collection->length) {
			$query_filter_collection->forEach( function (QueryFilter $query_filter) use (&$sql) {
				$this->insertQueryFilterOnSQLWhere( $query_filter, $sql );
			} );
		}
		if ($query_order_collection->length) {
			$this->insertQueryOrderOnSQL( $query_order_collection, $sql );
		}
		log_message( 'info', 'Consulta executada: ' . trim( $sql ) );
		$query = $this->db->query( $sql, $binds );
		$result_array = $query->getResultArray();
		$dao_response = DAOResponse::builder()->setStatus( $this->calculateDAOResponseStatusForSelect( $result_array ) )
			->build();
		if ($dao_response->getStatus() === DAOResponseInterface::SUCESS) {
			$dao_response->setData( $data_mapper( $result_array ) );
		}
		return $dao_response;

	}

	/**
	 *
	 * @param array $binds
	 * @param QueryFilterCollection|null $query_filter_collection
	 * @param string $sql
	 * @param callable $data_mapper
	 * @return DAOResponse
	 */
	protected function getDAOResponseFromUniqueRowSelect(array $binds, ?QueryFilterCollection $query_filter_collection, string $sql, callable $data_mapper): DAOResponse {

		if ($query_filter_collection) {
			$query_filter_collection->forEach( function (QueryFilter $query_filter) use (&$sql) {
				$this->insertQueryFilterOnSQLWhere( $query_filter, $sql );
			} );
		}
		$sql = preg_replace( str_replace( '$', '', $this->limit_regex ), '', $sql, 1 ) . ' LIMIT 1';
		log_message( 'info', 'Consulta executada: ' . trim( $sql ) );
		$query = $this->db->query( $sql, $binds );
		$result_array = $query->getResultArray();
		$pos = strpos( $result_array [0] ['arquivo'], '/' );
		$text = substr( $result_array [0] ['arquivo'], $pos, strlen( $result_array [0] ['arquivo'] ) );
		$pos = strpos( $text, '.' );
		$rest = substr( $text, $pos, strlen( $text ) );
		$ext = str_replace( ".", "", $rest );
		$result_array [0] ['ext'] = $ext;
		$dao_response = DAOResponse::builder()->setStatus( $this->calculateDAOResponseStatusForSelect( $result_array ) )
			->build();
		if ($dao_response->getStatus() === DAOResponseInterface::SUCESS) {
			$dao_response->setData( $data_mapper( $result_array ) );
		}
		return $dao_response;

	}

	/**
	 *
	 * @param QueryFilter $query_filter
	 * @param string $sql
	 */
	protected function insertQueryFilterOnSQLWhere(QueryFilter $query_filter, string &$sql): void {

		$sql = trim( preg_replace( '/\s{2,}/', ' ', $sql ) );
		if ($query_filter->getIsExact()) {
			$search = $this->db->escapeString( $query_filter->getValue() );
			$search_pattern = " = '$search'";
		} else {
			$comparator = $query_filter->getComparator();
			if ($comparator) {
				$search = $this->db->escapeString( $query_filter->getValue() );
				$search_pattern = " $comparator '$search'";
			} else {
				$search = $this->db->escapeLikeString( $query_filter->getValue() );
				$search_pattern = " LIKE \"%$search%\"";
			}
		}
		$filter = $query_filter->getTable() . '.' . "`{$query_filter->getNameParam()}`" . $search_pattern;
		$where_regex = '[Ww][Hh][Ee][Rr][Ee]';
		$group_by_regex = '[Gg][Rr][Oo][Uu][Pp]\s[Bb][Yy]';
		$has_where = (preg_match( "/$where_regex/", $sql ) ? '1' : '0');
		$has_group_by = (preg_match( "/$group_by_regex/", $sql ) ? '1' : '0');
		switch ($has_where . $has_group_by) {
			case '00' :
				$sql .= " WHERE $filter";
				break;
			case '01' :
				$sql = preg_replace( "/$group_by_regex/", "WHERE $filter GROUP BY ", $sql );
				break;
			case '10' :
				$sql .= " AND ($filter)";
				break;
			case '11' :
				$match_array = [ ];
				preg_match( "/$where_regex.*$group_by_regex/", $sql, $match_array );
				$match_string = preg_replace( "/$group_by_regex/", '', $match_array [0] );
				$match_string .= " AND $filter GROUP BY";
				$sql = preg_replace( "/$where_regex.*$group_by_regex/", $match_string, $sql );
				break;
			default :
				break;
		}

	}

	/**
	 *
	 * @param QueryOrderCollection $query_order_collection
	 * @param string $sql
	 */
	protected function insertQueryOrderOnSQL(QueryOrderCollection $query_order_collection, string &$sql): void {

		if (preg_match( $this->limit_regex, $sql )) {
			$sql = preg_replace( '/[Ll][Ii][Mm][Ii][Tt]/', $query_order_collection->generateSQL() . ' LIMIT', $sql, 1 );
		} else {
			$sql .= $query_order_collection->generateSQL();
		}

	}

	/**
	 *
	 * @param Model $model
	 * @param int $id_record
	 * @return DAOResponse
	 */
	protected function getDAOResponseForDeletion(Model $model, int $id_record): DAOResponse {

		$deletion_status = $model->delete( $id_record );
		$error = $this->db->error();
		if ($error ['code']) {
			$error_message = "Ocorreu um erro durante a exclusão do registro da entidade chamada nesse caminho: " . get_class( $model );
			$log_service = new LogService();
			$log_service->log_error( $error ['code'], $error_message, $error );
			$dao_response_status = $error ['code'];
		} else {
			$dao_response_status = $deletion_status->connID->affected_rows === 1 ? DAOResponseInterface::SUCESS : DAOResponseInterface::RECORD_NOT_FOUND;
		}
		return DAOResponse::builder()->setStatus( $dao_response_status )
			->build();

	}

}
