<?php
/**
 * Its used for query builder.
 *
 * @since: 21/09/2021
 * @author: Sarwar Hasan
 * @version 1.0.0
 * @package Appsbd\V1\Core
 */

namespace Appsbd_Lite\V4\Core;

if ( ! class_exists( __NAMESPACE__ . '\QueryBuilder' ) ) {


	/**
	 * Its class query builder
	 *
	 * @package Appsbd\V1\Core
	 */
	class QueryBuilder {

		/**
		 * Its property select.
		 *
		 * @var string Its string.
		 */
		private $select = '*';
		/**
		 * Its property where
		 *
		 * @var string Its string.
		 */
		private $where = '';
		/**
		 * Its property from
		 *
		 * @var string Its string.
		 */
		private $from = '';
		/**
		 * Its property join.
		 *
		 * @var string Its string.
		 */
		private $join = '';
		/**
		 * Its property order_by.
		 *
		 * @var string Its string.
		 */
		private $order_by = '';
		/**
		 * Its property limit.
		 *
		 * @var string Its string.
		 */
		private $limit = '';
		/**
		 * Its property qb_set.
		 *
		 * @var array Its array.
		 */
		private $qb_set = array();
		/**
		 * Its property qb isescape.
		 *
		 * @var array Its array.
		 */
		private $qb_isescape = array();
		/**
		 * Its property _like_escape_chr.
		 *
		 * @var string Its string.
		 */
		protected $_like_escape_chr = '!';
		/**
		 * Its property group_by
		 *
		 * @var mixed
		 */
		protected $group_by='';

		/**
		 * The reset is generated by appsbd
		 */
		public function reset() {
			$this->select      = '*';
			$this->where       = '';
			$this->from        = '';
			$this->join        = '';
			$this->order_by    = '';
			$this->limit       = '';
			$this->qb_set      = array();
			$this->qb_isescape = array();
		}


		/**
		 * The select is generated by appsbd
		 *
		 * @param string $select Its select param.
		 * @param bool   $escape Its escape param.
		 */
		public function select( $select = '*', $escape = true ) {
			$this->select = $select;
		}


		/**
		 * The getSettedProperties is generated by appsbd
		 *
		 * @return array
		 */
		public function get_setted_properties() {
			return $this->qb_set;
		}


		/**
		 * The where is generated by appsbd
		 *
		 * @param any    $key Its key param.
		 * @param null   $value Its value param.
		 * @param bool   $escape Its escape param.
		 * @param string $type Its type param.
		 */
		public function where( $key, $value = null, $escape = true, $type = 'AND' ) {
			if ( ! is_array( $key ) ) {
				$key = array( $key => $value );
			}

			foreach ( $key as $k => $v ) {
				$k = ( $escape ) ? $this->escape_key( $k ) : $k;
				if ( null !== $v ) {
					if ( true == $escape ) {
						$v = ' ' . $this->escape( $v );
					}

					if ( ! $this->_has_operator( $k ) ) {
						$k .= ' = ';
					}
				} elseif ( ! $this->_has_operator( $k ) ) {
					
					$k .= ' IS NULL';
				} elseif ( preg_match( '/\s*(!?=|<>|IS(?:\s+NOT)?)\s*$/i', $k, $match, PREG_OFFSET_CAPTURE ) ) {
					$k = substr( $k, 0, $match[0][1] ) . ( '=' === $match[1][0] ? ' IS NULL' : ' IS NOT NULL' );
				}

				$this->where .= ( ! empty( $this->where ) ? " $type " : '' ) . ' ' . $k . $v;

			}

		}


		/**
		 * The set is generated by appsbd
		 *
		 * @param any    $key Its key param.
		 * @param string $value Its value param.
		 * @param bool   $escape Its escape param.
		 *
		 * @return $this
		 */
		public function set( $key, $value = '', $escape = true ) {
			$key = $this->_object_to_array( $key );
			if ( ! is_array( $key ) ) {
				$key = array( $key => $value );
			}
			foreach ( $key as $k => $v ) {
				$this->qb_set[ $this->escape_key( $k ) ]      = $v;
				$this->qb_isescape[ $this->escape_key( $k ) ] = $escape;
			}

			return $this;
		}


		/**
		 * The like is generated by appsbd
		 *
		 * @param any    $field Its field param.
		 * @param string $match Its match param.
		 * @param string $side Its side param.
		 * @param bool   $escape Its escape param.
		 * @param string $condition_type Its condition type.
		 * @param bool   $is_not_like Its not like.
		 */
		public function like( $field, $match = '', $side = 'both', $escape = true, $condition_type = 'AND', $is_not_like = false ) {
			return $this->_like( $field, $match, $condition_type, $side, ( $is_not_like ? ' not ' : '' ), $escape );
		}


		/**
		 * The _like is generated by appsbd
		 *
		 * @param any    $field Its field param.
		 * @param string $match Its match param.
		 * @param string $type Its type param.
		 * @param string $side Its side param.
		 * @param string $not Its not param.
		 * @param null   $escape Its escape param.
		 */
		protected function _like( $field, $match = '', $type = 'AND', $side = 'both', $not = '', $escape = null ) {
			$field = ( $escape ) ? $this->escape_key( $field ) : $field;
			if ( 'none' === $side ) {
				$like_statement = "{$field} {$not} LIKE '{$match}'";
			} elseif ( 'before' === $side ) {
				$like_statement = "{$field} {$not} LIKE '%{$match}'";
			} elseif ( 'after' === $side ) {
				$like_statement = "{$field} {$not} LIKE '{$match}%'";
			} else {
				$like_statement = "{$field} {$not} LIKE '%{$match}%'";
			}
			$this->where( $like_statement, '', false, $type );
		}


		/**
		 * The limit is generated by appsbd
		 *
		 * @param any $value Its value param.
		 * @param int $offset Its offset param.
		 */
		public function limit( $value, $offset = 0 ) {
			$value = preg_replace( '/[^0-9]/', '', $value );
			if ( empty( $value ) ) {
				return;
			}
			if ( $offset && $offset > 0 ) {
				$this->limit = " LIMIT $offset , $value ";
			} else {
				$this->limit = " LIMIT $value ";
			}
		}


		/**
		 * The FROM is generated by appsbd
		 *
		 * @param any $table Its table param.
		 */
		public function from( $table ) {
			$this->from = $table;
		}


		/**
		 * The order_by is generated by appsbd
		 *
		 * @param any    $orderby Its orderby param.
		 * @param string $direction Its direction param.
		 * @param bool   $escape Its escape param.
		 */
		public function order_by( $orderby, $direction = '', $escape = true ) {
			$orderby = ( $escape ? $this->escape_key( $orderby, ' ,' ) : $orderby );
			$comma    = ! empty( $this->order_by ) ? ',' : '';
			if ( '' == $direction && is_string( $orderby ) ) {
				$this->order_by .= "{$comma} {$orderby}";
			} else {
				$direction = $this->escape_key( strtoupper( $direction ) );
				if ( in_array( $direction, array( 'ASC', 'DESC' ) ) ) {
					$this->order_by .= "{$comma} {$orderby} {$direction}";
				}
			}

		}


		/**
		 * The _object_to_array is generated by appsbd
		 *
		 * @param any $object Its object param.
		 *
		 * @return array
		 */
		protected function _object_to_array( $object ) {
			if ( ! is_object( $object ) ) {
				return $object;
			}

			$array = array();
			foreach ( get_object_vars( $object ) as $key => $val ) {
				
				if ( ! is_object( $val ) && ! is_array( $val ) ) {
					$array[ $key ] = $val;
				}
			}

			return $array;
		}


		/**
		 * The _has_operator is generated by appsbd
		 *
		 * @param any $str Its str param.
		 *
		 * @return bool
		 */
		protected function _has_operator( $str ) {
			return (bool) preg_match( '/(<|>|!|=|\sIS NULL|\sIS NOT NULL|\sEXISTS|\sBETWEEN|\sLIKE|\sIN\s*\(|\s)/i', trim( $str ) );
		}


		/**
		 * The escape is generated by appsbd
		 *
		 * @param any $str Its str param.
		 *
		 * @return array|int|string
		 */
		public function escape( $str ) {
			if ( is_array( $str ) ) {
				$str = array_map( array( &$this, 'escape' ), $str );

				return $str;
			} elseif ( is_string( $str ) || ( is_object( $str ) && method_exists( $str, '__toString' ) ) ) {
				return "'" . $this->escape_str( $str ) . "'";
			} elseif ( is_bool( $str ) ) {
				return ( false === $str ) ? 0 : 1;
			} elseif ( null === $str ) {
				return 'NULL';
			}

			return $str;
		}


		/**
		 * The escape_str is generated by appsbd
		 *
		 * @param any $str Its str param.
		 *
		 * @return array|string|string[]|null
		 */
		public function escape_str( $str ) {
			if ( is_array( $str ) ) {
				foreach ( $str as $key => $val ) {
					$str[ $key ] = $this->escape_str( $val );
				}

				return $str;
			}

			$str = $this->_escape_str( $str );

			return $str;
		}


		/**
		 * The _escape_str is generated by appsbd
		 *
		 * @param any $str Its str param.
		 *
		 * @return string|string[]|null
		 */
		protected function _escape_str( $str ) {
			return str_replace( "'", "''", $this->remove_invisible_characters( $str ) );
		}


		/**
		 * The remove_invisible_characters is generated by appsbd
		 *
		 * @param any  $str Its str param.
		 * @param bool $url_encoded Its url_encoded param.
		 *
		 * @return string|string[]|null
		 */
		public function remove_invisible_characters( $str, $url_encoded = true ) {
			$non_displayables = array();

			
			
			if ( $url_encoded ) {
				$non_displayables[] = '/%0[0-8bcef]/';    
				$non_displayables[] = '/%1[0-9a-f]/';    
			}

			$non_displayables[] = '/[\x00-\x08\x0B\x0C\x0E-\x1F\x7F]+/S';    

			do {
				$str = preg_replace( $non_displayables, '', $str, -1, $count );
			} while ( $count );

			return $str;
		}


		/**
		 * The join is generated by appsbd
		 *
		 * @param any    $table Its table param.
		 * @param any    $cond Its cond param.
		 * @param string $type Its type param.
		 * @param bool   $escape Its escape param.
		 */
		public function join( $table, $cond, $type = '', $escape = true ) {
			if ( '' !== $type ) {
				$type = strtoupper( trim( $type ) );

				if ( ! in_array(
					$type,
					array(
						'LEFT',
						'RIGHT',
						'OUTER',
						'INNER',
						'LEFT OUTER',
						'RIGHT OUTER',
					),
					true
				) ) {
					$type = '';
				} else {
					$type .= ' ';
				}
			}
			$this->join .= " {$type} JOIN " . $table . " ON $cond ";

		}


		/**
		 * The getSelectQuery is generated by appsbd
		 *
		 * @param string $table Its table param.
		 * @param null   $limit Its limit param.
		 * @param null   $offset Its offset param.
		 *
		 * @return string
		 */
		public function get_select_query( $table = '', $limit = null, $offset = null ) {
			if ( ! empty( $limit ) ) {
				$this->limit( $limit, $offset );
			}
			if ( ! empty( $table ) ) {
				$this->from( $table );
			}
			if ( ! empty( $this->where ) ) {
				$this->where = ' WHERE ' . $this->where;
			}
			if ( ! empty( $this->order_by ) ) {
				$this->order_by = ' ORDER BY ' . $this->order_by;
			}
			$this->select = rtrim( $this->select, ', ' );

			$query = "SELECT {$this->select} FROM {$this->from} {$this->join} {$this->where} {$this->order_by} {$this->group_by} {$this->limit}";

			$this->reset();

			return $query;
		}

		/**
		 * The where is generated by appsbd
		 *
		 * @param any    $key Its key param.
		 * @param null   $value Its value param.
		 * @param bool   $escape Its escape param.
		 * @param string $type Its type param.
		 */
		public function group_by( $group_by ) {
			if(!empty($group_by)){
				$this->group_by = "GROUP BY $group_by";
			}
		}
		/**
		 * The getUpdateQuery is generated by appsbd
		 *
		 * @param string $table Its table param.
		 * @param false  $no_limit Its Nolimit param.
		 *
		 * @return string
		 */
		public function get_update_query( $table = "", $no_limit = false ) {
			if ( ! $no_limit ) {
				$this->limit( 1, 0 );
			} else {
				$this->limit = '';
			}
			if ( ! empty( $table ) ) {
				$this->from( $table );
			}
			if ( ! empty( $this->where ) ) {
				$this->where = ' WHERE ' . $this->where;
			}
			if ( ! empty( $this->order_by ) ) {
				$this->order_by = ' ORDER BY ' . $this->order_by;
			}
			$kevvaluestr = '';
			foreach ( $this->qb_set as $key => $value ) {
				if ( $this->qb_isescape[ $key ] ) {
					$value = $this->escape( $value );
				}
				$kevvaluestr .= "{$key}={$value},";

			}
			$kevvaluestr = rtrim( $kevvaluestr, ',' );
			$query       = "UPDATE {$this->from} SET {$kevvaluestr} {$this->where} {$this->limit}";
			$this->reset();

			return $query;
		}


		/**
		 * The getDeleteQuery is generated by appsbd
		 *
		 * @param string $table Its table param.
		 * @param false  $no_limit Its Nolimit param.
		 *
		 * @return string
		 */
		public function get_delete_query( $table = '', $no_limit = false ) {
			if ( ! $no_limit ) {
				$this->limit( 1, 0 );
			} else {
				$this->limit = '';
			}
			if ( ! empty( $table ) ) {
				$this->from( $table );
			}
			if ( ! empty( $this->where ) ) {
				$this->where = ' WHERE ' . $this->where;
			}
			if ( ! empty( $this->order_by ) ) {
				$this->order_by = ' ORDER BY ' . $this->order_by;
			}
			$kevvaluestr = '';
			foreach ( $this->qb_set as $key => $value ) {
				if ( $this->qb_isescape[ $key ] ) {
					$value = $this->escape( $value );
				}
				$kevvaluestr .= "{$key}={$value},";

			}
			$kevvaluestr = rtrim( $kevvaluestr, ',' );
			$query       = "DELETE FROM {$this->from} {$this->where} {$this->limit}";

			$this->reset();

			return $query;
		}


		/**
		 * The escape_key is generated by appsbd
		 *
		 * @param any    $str Its str param.
		 * @param string $allowedchar Its allowedchar param.
		 *
		 * @return string|string[]|null
		 */
		private function escape_key( $str, $allowedchar = '' ) {
			return preg_replace( "/[^a-z0-9._{$allowedchar}]/i", '', $str );
		}

	}
}
