<?php 

	class DB{
		private $connection;
		private $last_query;

		public function __construct($key = 0){
			$this->connection = new Connection($key);
		}

		private function beginTransaction():bool{
			try{
				$this->connection->beginTransaction();
				return true;
			}
			catch (PDOException $e) {
				return false;
			}
		}

		private function filter(string $variable):string{
			return preg_replace("/[\.\,\#\?\*\@\!\$\&\+\(\)\[\]\{\}\´\`\^\~\'\"\=\>\<\;\ç\º\ª\á\à\ã\â\ä\ñ\é\è\ë\ê\í\ì\î\ï\ó\ò\ô\õ\ö\ú\ù\û\ü\Á\À\Ã\Â\Ä\Ñ\É\È\Ë\Ê\Í\Ì\Î\Ï\Ó\Ò\Ô\Õ\Ö\Ú\Ù\Û\Ü\Ç\ \¨\¬\£\¢\§\°\%\-]|( not| is)/i", "", $variable);
		}

		private function logicalOperators(string $variable):string{
			$aux = preg_replace("/[^\<\>\=\!]/i", "", $variable);

			if (!empty($aux)) {
				return $aux;
			}
			else if (preg_match("/(is not)/i", $variable)) {
				return "IS NOT";
			}
			else if (preg_match("/(is)/i", $variable)) {
				return "IS";
			}
			else if (preg_match("/(not)/i", $variable)) {
				return "NOT";
			}
			else{
				return "=";
			}
		}

		private function recFields(array $conditions, array $fields = []):array{
			$i=0;
			$sql = "";

			foreach ($conditions as $key => $value) {
				$k = self::filter($key);

				if ((trim(strtoupper($k)) == "AND" or trim(strtoupper($k)) == "OR" or trim(strtoupper($k)) == "XOR") and gettype($value) == "array") {
					$sql .= ($i > 0) ? " AND (" : "("; 
					$j = 0;

					foreach ($value as $k1 => $v1) {
						$k2 = self::filter($k1);

						if (gettype($v1) == "array") {
							$aux = self::recFields([$k1 => $v1], $fields);
							$sql .= (($j > 0) ? " {$k} " : "")."(".$aux[0].")";
							$fields = $aux[1];
						}
						else {
							$sql .= (($j > 0) ? " {$k} " : "")."{$k2} ".self::logicalOperators($k1)." :{$k2}_{$j}_c";
							$fields[":{$k2}_{$j}_c"] = $v1;
						}

						$j++;
					}

					$sql .= ")"; 
				}
				else{
					if (gettype($value) == "array") {
						$sql .= ($i > 0) ? " AND (" : "("; 
						$j = 0;

						foreach ($value as $v) {
							$sql .= (($j > 0) ? " OR " : "")."{$k} ".self::logicalOperators($key)." :{$k}_{$j}_c";
							$fields[":{$k}_{$j}_c"] = $v;
							$j++;
						}

						$sql .= ")"; 
					}
					else{
						$sql .= (($i > 0) ? " AND " : "")."{$k} ".self::logicalOperators($key)." :{$k}_c";
						$fields[":{$k}_c"] = $value;
					}
				}

				$i++;
			}

			return [$sql, $fields];
		}

		private function getSQL(string $sql, array $values):string{
			$query = $sql;
			foreach ($values as $key => $value) {
				if (gettype($value) == "integer" or gettype($value) == "boolean") {
					$query = str_replace($key, $value, $query);
				}
				else if (gettype($value) == "NULL") {
					$query = str_replace($key, "NULL", $query);
				}
				else{
					$query = str_replace($key, $this->connection->quote($value), $query);
				}
			}
			return $query;
		}

		public function insert(string $table, array $values):int{
			try{
				self::beginTransaction();

				$part1 = "INSERT INTO {$table} (";
				$part2 = ") VALUES (";
				
				$i = 0;
				$fields = [];

				foreach ($values as $key => $value) {
					$k = self::filter($key);

					$part1 .= (($i > 0) ? ", " : "").$k;
					$part2 .= (($i > 0) ? ", " : "").":{$k}";
					$fields[":{$k}"] = $value;

					$i++;
				}

				$insert = $this->connection->prepare($part1.$part2.")");

				foreach ($fields as $key => $value) {
					switch (gettype($value)) {
						case "string":
							$insert->bindValue($key, $value, PDO::PARAM_STR);
							break;
						case "integer":
							$insert->bindValue($key, $value, PDO::PARAM_INT);
							break;
						case "double":
							$insert->bindValue($key, $value, PDO::PARAM_STR);
							break;
						case "NULL":
							$insert->bindValue($key, $value, PDO::PARAM_NULL);
							break;
						case "boolean":
							$insert->bindValue($key, $value, PDO::PARAM_BOOL);
							break;
						default:
							throw new Exception("ERROR: The value of the $key key is invalid, it does not allow array or object.", 500);
							break;
					}
				}

				$status = $insert->execute();

				$this->last_query = self::getSQL($insert->queryString, $fields);

				return ($status) ? intval($this->connection->lastInsertId()) : 0;
			}
			catch (PDOException $e) {
				$this->connection->rollBack();
				throw new Exception("ERROR: There was an internal error in the DB class. (".$e->getMessage().")", 500);
			}
		}

		public function update(string $table, array $values, $conditions = [], array $conditions_values = []):bool{
			if (gettype($conditions) == "array" or gettype($conditions) == "string"){
				try{
					self::beginTransaction();

					$sql = "UPDATE {$table} SET ";

					$i = 0;
					$fields_values = [];

					foreach ($values as $key => $value) {
						$k = self::filter($key);
						$sql .= (($i > 0) ? ", " : "")."{$k} = :{$k}_v";
						$fields_values[":{$k}_v"] = $value;
						$i++;
					}

					$fields_conditions = [];

					if (gettype($conditions) == "string") {
						$sql .= " WHERE ".$conditions;
					}
					else if (count($conditions) > 0) {
						$aux = self::recFields($conditions);
						$sql .= " WHERE ".$aux[0];
						$fields_conditions = $aux[1];
					}

					$update = $this->connection->prepare($sql);

					$fields = array_merge($fields_values, $fields_conditions, $conditions_values);

					foreach ($fields as $key => $value) {
						switch (gettype($value)) {
							case "string":
								$update->bindValue($key, $value, PDO::PARAM_STR);
								break;
							case "integer":
								$update->bindValue($key, $value, PDO::PARAM_INT);
								break;
							case "double":
								$update->bindValue($key, $value, PDO::PARAM_STR);
								break;
							case "NULL":
								$update->bindValue($key, $value, PDO::PARAM_NULL);
								break;
							case "boolean":
								$update->bindValue($key, $value, PDO::PARAM_BOOL);
								break;
							default:
								throw new Exception("ERROR: The value of the $key key is invalid, it does not allow array or object.", 500);
								break;
						}
					}

					$status = $update->execute();

					$this->last_query = self::getSQL($update->queryString, $fields);

					return ($status) ? true : false;
				}
				catch (PDOException $e) {
					$this->connection->rollBack();
					throw new Exception("ERROR: There was an internal error in the DB class. (".$e->getMessage().")", 500);
				}
			}
			else{
				throw new Exception("ERROR: Invalid parameter value type.", 500);
			}
		}

		public function insertOrUpdate(string $table, array $values_insert, array $values_update, array $conditions):bool{
			if ($this->existElementInTable($table, $conditions) == false) {
				$result = $this->insert($table, $values_insert);

				return ($result > 0) ? true : false;
			}
			else{
				return $this->update($table, $values_update, $conditions);
			}
		}

		public function delete(string $table, $conditions = [], array $conditions_values = []):bool{
			if (gettype($conditions) == "array" or gettype($conditions) == "string"){
				try{
					self::beginTransaction();

					$sql = "DELETE FROM {$table}";

					$fields_conditions = [];

					if (gettype($conditions) == "string") {
						$sql .= " WHERE ".$conditions;
					}
					else if (count($conditions) > 0) {
						$aux = self::recFields($conditions);
						$sql .= " WHERE ".$aux[0];
						$fields_conditions = $aux[1];
					}

					$delete = $this->connection->prepare($sql);

					$fields = array_merge($fields_conditions, $conditions_values);

					foreach ($fields as $key => $value) {
						switch (gettype($value)) {
							case "string":
								$delete->bindValue($key, $value, PDO::PARAM_STR);
								break;
							case "integer":
								$delete->bindValue($key, $value, PDO::PARAM_INT);
								break;
							case "double":
								$delete->bindValue($key, $value, PDO::PARAM_STR);
								break;
							case "NULL":
								$delete->bindValue($key, $value, PDO::PARAM_NULL);
								break;
							case "boolean":
								$delete->bindValue($key, $value, PDO::PARAM_BOOL);
								break;
							default:
								throw new Exception("ERROR: The value of the $key key is invalid, it does not allow array or object.", 500);
								break;
						}
					}

					$status = $delete->execute();

					$this->last_query = self::getSQL($delete->queryString, $fields);

					return ($status) ? true : false;
				}
				catch (PDOException $e) {
					$this->connection->rollBack();
					throw new Exception("ERROR: There was an internal error in the DB class. (".$e->getMessage().")", 500);
				}
			}
			else{
				throw new Exception("ERROR: Invalid parameter value type.", 500);
			}
		}

		public function query(string $sql, array $fields = []):object{
			try{
				self::beginTransaction();

				$query = $this->connection->prepare($sql);

				foreach ($fields as $key => $value){
					switch (gettype($value)) {
						case "string":
							$query->bindValue($key, $value, PDO::PARAM_STR);
							break;
						case "integer":
							$query->bindValue($key, $value, PDO::PARAM_INT);
							break;
						case "double":
							$query->bindValue($key, $value, PDO::PARAM_STR);
							break;
						case "NULL":
							$query->bindValue($key, $value, PDO::PARAM_NULL);
							break;
						case "boolean":
							$query->bindValue($key, $value, PDO::PARAM_BOOL);
							break;
						default:
							throw new Exception("ERROR: The value of the $key key is invalid, it does not allow array or object.", 500);
							break;
					}
				}

				$query->execute();
				
				$this->last_query = self::getSQL($query->queryString, $fields);

				return $query;
			}
			catch (PDOException $e) {
				$this->connection->rollBack();
				throw new Exception("ERROR: There was an internal error in the DB class. (".$e->getMessage().")", 500);
			}
		}

		public function commit():void{
			$this->connection->commit();
		}

		public function rollBack():void{
			$this->connection->rollBack();
		}

		public function lastInsertId():int{
			return intval($this->connection->lastInsertId());
		}

		public function lastQuery():string{
			return $this->last_query;
		}

		public function existElementInTable(string $table, $conditions = [], array $conditions_values = []):bool{
			if (gettype($conditions) == "array" or gettype($conditions) == "string"){
				try{ 
					$sql = "SELECT COUNT(*) AS total FROM {$table}";

					$fields_conditions = [];

					if (gettype($conditions) == "string") {
						$sql .= " WHERE ".$conditions;
					}
					else if (count($conditions) > 0) {
						$aux = self::recFields($conditions);
						$sql .= " WHERE ".$aux[0];
						$fields_conditions = $aux[1];
					}

					$select = $this->connection->prepare($sql);

					$fields = array_merge($fields_conditions, $conditions_values);

					foreach ($fields as $key => $value) {
						switch (gettype($value)) {
							case "string":
								$select->bindValue($key, $value, PDO::PARAM_STR);
								break;
							case "integer":
								$select->bindValue($key, $value, PDO::PARAM_INT);
								break;
							case "double":
								$select->bindValue($key, $value, PDO::PARAM_STR);
								break;
							case "NULL":
								$select->bindValue($key, $value, PDO::PARAM_NULL);
								break;
							case "boolean":
								$select->bindValue($key, $value, PDO::PARAM_BOOL);
								break;
							default:
								throw new Exception("ERROR: The value of the $key key is invalid, it does not allow array or object.", 500);
								break;
						}
					}

					$select->execute();

					$this->last_query = self::getSQL($select->queryString, $fields);

					if ($select->fetch()->total > 0) {
						return true;
					}
					else{
						return false;
					}
				}
				catch (PDOException $e) {
					throw new Exception("ERROR: There was an internal error in the DB class. (".$e->getMessage().")", 500);
				}
			}
			else{
				throw new Exception("ERROR: Invalid parameter value type.", 500);
			}
		}

		public function truncate(string $table):bool{
			try{
				$truncate = $this->connection->prepare("TRUNCATE TABLE {$table}");

				$status = $truncate->execute();

				return ($status) ? true : false;
			}
			catch (PDOException $e) {
				$this->connection->rollBack();
				throw new Exception("ERROR: There was an internal error in the DB class. (".$e->getMessage().")", 500);
			}
		}	
	}

?>