<?php
/**
 * MainWP AUM Database Controller
 *
 * This file handles all interactions with the DB.
 *
 * @package MainWP/Extensions/AUM
 */

namespace MainWP\Extensions\AUM;

/**
 * Class MainWP_AUM_DB_Base
 */
class MainWP_AUM_DB_Base {

	// phpcs:disable WordPress.DB.RestrictedFunctions, WordPress.DB.PreparedSQL.NotPrepared -- unprepared SQL ok, accessing the database directly to custom database functions.

	/**
	 * Private static instance.
	 *
	 * @static
	 * @var $instance  MainWP_AUM_DB_Base.
	 */
	private static $instance = null;

	/**
	 * Table prefix.
	 *
	 * @var string $table_prefix
	 */
	protected $table_prefix;

	/**
	 * WordPress Database.
	 *
	 * @var mixed $wpdb WordPress Database.
	 */
	protected $wpdb;

	/**
	 * Method __construct()
	 *
	 * Contructor.
	 */
	public function __construct() {

		self::$instance = $this;

		/**
		 * WordPress Database.
		 *
		 * @var mixed $wpdb Global WordPress Database.
		 */
		global $wpdb;

		$this->wpdb         = &$wpdb;
		$this->table_prefix = $wpdb->prefix . 'mainwp_uptime_';
	}

	/**
	 * Method test_connection()
	 *
	 * Test db connection.
	 */
	protected function test_connection() {
		if ( ! self::ping( $this->wpdb->dbh ) ) {
			$this->wpdb->db_connect();
		}
	}

	/**
	 * Method table_name()
	 *
	 * Create entire table name.
	 *
	 * @param mixed $suffix Table suffix.
	 * @param null  $tablePrefix Table prefix.
	 *
	 * @return string Table name.
	 */
	protected function table_name( $suffix, $tablePrefix = null ) {
		return ( null == $tablePrefix ? $this->table_prefix : $tablePrefix ) . $suffix;
	}

	/**
	 * Method get_row_result()
	 *
	 * Get row result.
	 *
	 * @param mixed $sql SQL Query.
	 *
	 * @return mixed null|Row
	 */
	public function get_row_result( $sql ) {
		if ( null == $sql ) {
			return null;
		}

		return $this->wpdb->get_row( $sql, OBJECT );
	}

	/**
	 * Method get_results_result()
	 *
	 * Get Results of result.
	 *
	 * @param mixed $sql SQL query.
	 *
	 * @return mixed null|get_results()
	 */
	public function get_results_result( $sql ) {
		if ( null == $sql ) {
			return null;
		}

		return $this->wpdb->get_results( $sql, OBJECT_K );
	}

	/**
	 * Method query()
	 *
	 * SQL Query.
	 *
	 * @param mixed $sql SQL Query.
	 *
	 * @return mixed false|$result.
	 */
	public function query( $sql ) {
		if ( null == $sql ) {
			return false;
		}

		$result = self::m_query( $sql, $this->wpdb->dbh );

		if ( ! $result || ( 0 == self::num_rows( $result ) ) ) {
			return false;
		}

		return $result;
	}

	/**
	 * Method escape()
	 *
	 * Escape SQL Data.
	 *
	 * @param mixed $data Data to escape.
	 *
	 * @return mixed Escapped SQL Data.
	 */
	protected function escape( $data ) {
		if ( function_exists( 'esc_sql' ) ) {
			return esc_sql( $data );
		} else {
			return $this->wpdb->escape( $data );
		}
	}

	/**
	 * Method use_mysqli()
	 *
	 * Use MySQLi, Support old & new versions of WordPress (3.9+).
	 *
	 * @return boolean|self false|$instance Instance of \mysqli
	 */
	public static function use_mysqli() {
		if ( ! function_exists( '\mysqli_connect' ) ) {
			return false;
		}
		return ( self::$instance->wpdb->dbh instanceof \mysqli );
	}

	/**
	 * Method get_where_sql_clauses()
	 *
	 * @return array Query array.
	 */
	public function get_where_sql( $conditions ) {
		$sql_clauses = array();
		foreach ( $conditions as $key => $value ) {
			if ( is_array( $value ) ) {
				if ( is_string( $key ) && ! in_array( $key, array( 'OR', 'AND' ) ) ) {
					$values = array();
					foreach ( $value as $val ) {
						$values[] = '"' . $this->escape( $val ) . '"';
					}
					$values        = implode( ',', $values );
					$sql_clauses[] = $this->escape( $key ) . ' IN (' . $values . ')';
				} else {
					$clauses          = $this->get_where_sql_clauses( $value );
					$logical_operator = 'OR' == $key ? ' OR ' : ' AND ';
					$sql_clauses[]    = '(' . implode( $logical_operator, $clauses ) . ')';
				}
				continue;
			}

			$operator = preg_match( '/\s+(<|>|<=|>=|<>|\!=|[\w\s]+)/', $key ) ? ' ' : ' = ';

			if ( 'url_address_like' == $key ) {
				$results = $this->get_urls_by_monitor_url( $value, 'LIKE' );

				$where_sql = '  monitor_id IN ("';
				if ( ! empty( $results ) ) {
					foreach ( $results as $item ) {
						$where_sql .= $item->monitor_id . '", "';
					}
				}
				$where_sql .= 'NONE") ';

				$sql_clauses[] = $where_sql;
				// $sql_clauses[] = "replace(replace(replace(replace(replace(replace(url_address, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www.', ''), '/', '') LIKE \"%" . $this->escape( $value ) . '%"';
			} elseif ( 'url_address' == $key ) {
				$results = $this->get_urls_by_monitor_url( $value, '=' );

				$where_sql = ' monitor_id IN ("';
				if ( ! empty( $results ) ) {
					foreach ( $results as $item ) {
						$where_sql .= $item->monitor_id . '", "';
					}
				}
				$where_sql .= 'NONE") ';

				$sql_clauses[] = $where_sql;

				// $sql_clauses[] = "replace(replace(replace(replace(replace(replace(url_address, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', ''), 'www.', ''), '/', '')" . $operator . '"' . $this->escape( $value ) . '"';
			} else {
				$sql_clauses[] = $this->escape( $key ) . $operator . '"' . $this->escape( $value ) . '"';
			}
		}

		return implode( ' AND ', $sql_clauses );
	}


	public function get_urls_by_monitor_url( $url, $operator, $service = '' ) {

		$url_address = MainWP_AUM_Main::get_instance()->clean_url_parts( $url );

		$sql1 = "
				SELECT tb3.* FROM (
				select *, ( if (left( url2 , 4) = 'www.', right(url2, length(url2) - 4), url2 ) ) AS url3
				FROM
				(select *,( if (right( url1, 1) = '/', LEFT( url1, length(url1) - 1), url1 ) ) as url2
				FROM (
				select *,( replace(replace(replace(replace(url_address, 'https://www.',''), 'http://www.',''), 'https://', ''), 'http://', '') ) AS url1
				from " . $this->table_name( 'monitor_urls' ) . ' ) as tb1 ) as tb2 ) as tb3
		';

		if ( '=' === $operator ) {
			$sql1 .= " where url3 = '" . $this->escape( $url_address ) . "'";
		} else {
			$sql1 .= " where url3 LIKE '%" . $this->escape( $url_address ) . "%'";
		}

		if ( ! empty( $service ) ) {
			$sql1 .= $this->wpdb->prepare( ' AND tb3.service = %s ', $service );
		}

		$results = $this->wpdb->get_results( $sql1 );
		return $results;
	}

	/**
	 * Method ping()
	 *
	 * Ping MySQLi.
	 *
	 * @param mixed $link Query link.
	 *
	 * @return mixed \mysqli_ping
	 */
	public static function ping( $link ) {
		if ( self::use_mysqli() ) {
			return \mysqli_ping( $link );
		} else {
			return \mysql_ping( $link );
		}
	}

	/**
	 * Method m_query()
	 *
	 * MySQLi m_query.
	 *
	 * @param mixed $query Query params.
	 * @param mixed $link Query link.
	 *
	 * @return mixed \mysqli_query
	 */
	public static function m_query( $query, $link ) {
		if ( self::use_mysqli() ) {
			return \mysqli_query( $link, $query );
		} else {
			return \mysql_query( $query, $link );
		}
	}

	/**
	 * Method fetch_object()
	 *
	 * Fetch object.
	 *
	 * @param mixed $result Query result.
	 *
	 * @return boolean|mixed false|\mysqli_fetch_object
	 */
	public static function fetch_object( $result ) {
		if ( false === $result ) {
			return false;
		}

		if ( self::use_mysqli() ) {
			return \mysqli_fetch_object( $result );
		} else {
			return \mysql_fetch_object( $result );
		}
	}

	/**
	 * Method free_result()
	 *
	 * MySQLi free result.
	 *
	 * @param mixed $result Query result.
	 *
	 * @return boolean|mixed false|\mysqli_free_result
	 */
	public static function free_result( $result ) {
		if ( false === $result ) {
			return false;
		}

		if ( self::use_mysqli() ) {
			return \mysqli_free_result( $result );
		} else {
			return \mysql_free_result( $result );
		}
	}

	/**
	 * Method data_seek()
	 *
	 * MySQLi data seek.
	 *
	 * @param mixed $result Query result.
	 * @param mixed $offset Query offset.
	 *
	 * @return boolean|mixed false|\mysqli_data_seek
	 */
	public static function data_seek( $result, $offset ) {
		if ( false === $result ) {
			return false;
		}

		if ( self::use_mysqli() ) {
			return \mysqli_data_seek( $result, $offset );
		} else {
			return \mysql_data_seek( $result, $offset );
		}
	}

	/**
	 * Method fetch_array()
	 *
	 * MySQLi fetch array.
	 *
	 * @param mixed $result Query result.
	 * @param null  $result_type Query result type.
	 *
	 * @return boolean|mixed false|\mysqli_fetch_array.
	 */
	public static function fetch_array( $result, $result_type = null ) {
		if ( false === $result ) {
			return false;
		}

		if ( self::use_mysqli() ) {
			return \mysqli_fetch_array( $result, ( null == $result_type ? MYSQLI_BOTH : $result_type ) );
		} else {
			return \mysql_fetch_array( $result, ( null == $result_type ? MYSQL_BOTH : $result_type ) );
		}
	}


	/**
	 * Method num_rows()
	 *
	 * MySQLi number of rows.
	 *
	 * @param mixed $result Query result.
	 *
	 * @return boolean|mixed false|\mysqli_num_rows.
	 */
	public static function num_rows( $result ) {
		if ( false === $result ) {
			return 0;
		}

		if ( self::use_mysqli() ) {
			return \mysqli_num_rows( $result );
		} else {
			return \mysql_num_rows( $result );
		}
	}

	/**
	 * Method is_result()
	 *
	 * Return instance of \mysqli_result
	 *
	 * @param mixed $result Query result.
	 *
	 * @return boolean|mixed false|\mysqli_result
	 */
	public static function is_result( $result ) {
		if ( false === $result ) {
			return false;
		}

		if ( self::use_mysqli() ) {
			return ( $result instanceof \mysqli_result );
		} else {
			return is_resource( $result );
		}
	}
	// phpcs:enable
}
