<?php
/**
 * Entity: Abstract.
 *
 * @package wsal
 *
 * @since 4.4.2.1
 */

declare(strict_types=1);

namespace WSAL\Entities;

use WSAL\Helpers\Logger;
use WSAL\Helpers\Validator;
use WSAL\Helpers\PHP_Helper;
use WSAL\Controllers\Connection;
use WSAL\WP_Sensors\WP_Database_Sensor;

// Exit if accessed directly.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

if ( ! class_exists( '\WSAL\Entities\Abstract_Entity' ) ) {

	/**
	 * Responsible for the common entity operations.
	 */
	abstract class Abstract_Entity {

		/**
		 * Contains the table name.
		 *
		 * @var string
		 *
		 * @since 4.4.2.1
		 */
		private static $table = '';

		/**
		 * Holds the DB connection (for caching purposes)
		 *
		 * @var \wpdb Connection
		 *
		 * @since 4.4.2.1
		 */
		private static $connection = null;

		/**
		 * Keeps the info about the columns of the table - name, type
		 *
		 * @var array
		 *
		 * @since 4.5.0
		 */
		private static $fields = array();

		/**
		 * Returns the the table name
		 *
		 * @param \wpdb $connection -  - \wpdb connection to be used for name extraction.
		 *
		 * @return string
		 *
		 * @since 4.4.2.1
		 * @since 4.6.0 - Added $connection parameter
		 */
		public static function get_table_name( $connection = null ): string {
			if ( null !== $connection ) {
				if ( $connection instanceof \wpdb ) {
					return $connection->base_prefix . static::$table;
				}
			}
			return static::get_connection()->base_prefix . static::$table;
		}

		/**
		 * Returns the current connection (used by the plugin)
		 *
		 * @return \wpdb
		 *
		 * @since 4.4.2.1
		 */
		public static function get_connection() {
			if ( null === self::$connection ) {
				self::$connection = Connection::get_connection();
			}
			return self::$connection;
		}

		/**
		 * Sets connection to the given value.
		 *
		 * @param \wpdb $connection - The connection to set to @see \WSAL_Connector_MySQLDB .
		 *
		 * @return void
		 *
		 * @since 4.4.2.1
		 */
		public static function set_connection( $connection ) {
			self::$connection = $connection;
		}

		/**
		 * As this is static class, we need to destroy the connection sometimes.
		 *
		 * @return void
		 *
		 * @since 4.4.2.1
		 */
		public static function destroy_connection() {
			self::$connection = null;
			// Destroy factory connection as well.
			Connection::destroy_connection();
		}

		/**
		 * Checks if the table needs to be recreated / created
		 *
		 * @param string $table_name - The name of the table to check for.
		 * @param string $create_ddl - The create table syntax.
		 * @param \wpdb  $connection - \wpdb connection to be used for name extraction.
		 *
		 * @return bool
		 *
		 * @since 4.4.2.1
		 * @since 4.6.0 Added $connection parameter
		 */
		public static function maybe_create_table( string $table_name, string $create_ddl, $connection = null ): bool {
			if ( null !== $connection ) {
				if ( $connection instanceof \wpdb ) {
					$_wpdb = $connection;
				}
			} else {
				$_wpdb = static::get_connection();
			}

			foreach ( $_wpdb->get_col( 'SHOW TABLES', 0 ) as $table ) {
				if ( $table === $table_name ) {
					return true;
				}
			}

			if ( WP_Database_Sensor::is_sensor_enabled() ) {
				\remove_filter( 'query', array( WP_Database_Sensor::class, 'event_drop_query' ) );
			}

			// Didn't find it, so try to create it.
			$_wpdb->query( $create_ddl );

			// We cannot directly tell that whether this succeeded!
			foreach ( $_wpdb->get_col( 'SHOW TABLES', 0 ) as $table ) {
				if ( $table === $table_name ) {
					return true;
				}
			}

			return false;
		}

		/**
		 * Checks if external database is in use.
		 *
		 * @return boolean
		 *
		 * @since 4.4.2.1
		 */
		public static function is_external_db(): bool {
			$db_config = Connection::get_config();

			return is_array( $db_config ) && ! empty( $db_config );
		}

		/**
		 * Checks for given column existence using custom connection.
		 *
		 * @param string       $table_name - The name of the table.
		 * @param string       $col_name - The name of the column.
		 * @param string       $col_type - Type of the column.
		 * @param boolean|null $is_null - Is it null.
		 * @param mixed        $key - Is it key.
		 * @param mixed        $default - The default value of the column.
		 * @param mixed        $extra - Extra parameters.
		 *
		 * @return boolean - True if the column exists and all given parameters are the same, false otherwise.
		 *
		 * @since 4.4.2.1
		 */
		public static function check_column(
			string $table_name,
			string $col_name,
			string $col_type,
			?bool $is_null = null,
			$key = null,
			$default = null,
			$extra = null
		): bool {

			$diffs   = 0;
			$results = static::get_connection()->get_results( "DESC $table_name" );

			foreach ( $results as $row ) {

				if ( $row->Field === $col_name ) { // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase

					// Got our column, check the params.
					if ( ( null !== $col_type ) && ( strtolower( str_replace( ' ', '', $row->Type ) ) !== strtolower( str_replace( ' ', '', $col_type ) ) ) ) { // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
						++$diffs;
					}
					if ( ( null !== $is_null ) && ( $row->Null !== $is_null ) ) { // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
						++$diffs;
					}
					if ( ( null !== $key ) && ( $row->Key !== $key ) ) { // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
						++$diffs;
					}
					if ( ( null !== $default ) && ( $row->Default !== $default ) ) { // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
						++$diffs;
					}
					if ( ( null !== $extra ) && ( $row->Extra !== $extra ) ) { // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
						++$diffs;
					}

					if ( $diffs > 0 ) {
						return false;
					}

					return true;
				} // End if found our column.
			}

			return false;
		}

		/**
		 * Checks if the given index exists in the table
		 *
		 * @param string $index - The index to check for (text).
		 *
		 * @return boolean
		 *
		 * @since 5.3.4
		 */
		public static function check_index_exists( string $index ): bool {
			$index = \sanitize_text_field( $index );

			$results = static::get_connection()->get_results( 'SHOW INDEX FROM ' . self::get_table_name(), \ARRAY_A );

			foreach ( $results as $row ) {
				if ( isset( $row['Key_name'] ) && $row['Key_name'] === $index ) {
					return true;
				}
			}

			return false;
		}

		/**
		 * Checks and returns last mysql error
		 *
		 * @param \wpdb $_wpdb - The Mysql resource class.
		 *
		 * @return integer
		 *
		 * @since 4.4.2.1
		 */
		public static function get_last_sql_error( $_wpdb ): int {
			$code = 0;
			if ( $_wpdb->dbh instanceof \mysqli ) {
				$code = \mysqli_errno( $_wpdb->dbh ); // phpcs:ignore WordPress.DB.RestrictedFunctions.mysql_mysqli_errno
			}

			if ( is_resource( $_wpdb->dbh ) ) {
				// Please do not report this code as a PHP 7 incompatibility. Observe the surrounding logic.
				$code = mysql_errno( $_wpdb->dbh ); // phpcs:ignore WordPress.DB.RestrictedFunctions.mysql_mysql_errno
			}

			// WP Playground SQLite compatibility fix.
			if ( $_wpdb instanceof \WP_SQLite_DB ) {
				$code = 1146;
			}

			return $code;
		}

		/**
		 * Drop the table from the DB.
		 *
		 * @param \wpdb $connection - \wpdb connection to be used for name extraction.
		 *
		 * @return bool
		 *
		 * @since 4.6.0
		 */
		public static function drop_table( $connection = null ) {
			if ( null !== $connection ) {
				if ( $connection instanceof \wpdb ) {
					$_wpdb = $connection;
				}
			} else {
				$_wpdb = static::get_connection();
			}

			$table_name = self::get_table_name( $_wpdb );
			static::get_connection()->query( 'DROP TABLE IF EXISTS ' . $table_name );

			return true;
		}

		/**
		 * Truncate the table.
		 *
		 * @param \wpdb $connection - \wpdb connection to be used for name extraction.
		 *
		 * @return bool
		 *
		 * @since 5.1.1
		 */
		public static function truncate_table( $connection = null ) {
			if ( null !== $connection ) {
				if ( $connection instanceof \wpdb ) {
					$_wpdb = $connection;
				}
			} else {
				$_wpdb = static::get_connection();
			}

			$table_name = self::get_table_name( $_wpdb );

			if ( self::check_table_exists( $table_name, $connection ) ) {
				static::get_connection()->query( 'TRUNCATE ' . $table_name );

				return true;
			}

			return false;
		}

		/**
		 * Count records in the table using primary index 'id'.
		 * Adds a LIMIT for performance.
		 *
		 * @param \wpdb $connection Optional DB connection.
		 *
		 * @return int
		 *
		 * @since 5.5.0
		 */
		public static function count_records( $connection = null ): int {
			if ( null !== $connection ) {
				if ( $connection instanceof \wpdb ) {
					$_wpdb = $connection;
				}
			} else {
				$_wpdb = static::get_connection();
			}

			$table_name = self::get_table_name();

			if ( ! self::check_table_exists( $table_name, $connection ) ) {
					return 0;
			}

			$sql = 'SELECT count(id) FROM ' . self::get_table_name( $_wpdb ) . ' LIMIT 1';

			return (int) $_wpdb->get_var( $sql );
		}

		/**
		 * Checks if give table exists
		 *
		 * @param string $table_name - The table to check for.
		 * @param \wpdb  $connection  - \wpdb connection to be used for name extraction.
		 *
		 * @return boolean
		 *
		 * @since 4.4.2.1
		 * @since 4.6.0 - added $connection parameter
		 */
		public static function check_table_exists( ?string $table_name = null, $connection = null ): bool {
			if ( null !== $connection ) {
				if ( $connection instanceof \wpdb ) {
					$_wpdb = $connection;
				}
			} else {
				$_wpdb = static::get_connection();
			}

			if ( null === $table_name ) {
				$table_name = self::get_table_name( $_wpdb );
			}

			foreach ( $_wpdb->get_col( 'SHOW TABLES', 0 ) as $table ) {
				if ( $table === $table_name ) {
					return true;
				}
			}
			return false;
		}

		/**
		 * Checks if a table exists for a given connection (if no connection is provided - current connection will be used.)
		 *
		 * @param \wpdb $connection  - \wpdb connection to be used for name extraction.
		 *
		 * @return boolean
		 *
		 * @since 4.6.0
		 */
		public static function is_installed( $connection = null ): bool {
			return self::check_table_exists( self::get_table_name( $connection ), $connection );
		}

		/**
		 * Returns records in the table based on condition
		 *
		 * @param string $cond - The condition.
		 * @param array  $args - The arguments (values).
		 * @param \wpdb  $connection - \wpdb connection to be used for name extraction.
		 *
		 * @return int
		 *
		 * @since 4.5.0
		 */
		public static function count( $cond = '%d', $args = array( 1 ), $connection = null ) {
			if ( null !== $connection ) {
				if ( $connection instanceof \wpdb ) {
					$_wpdb = $connection;
				}
			} else {
				$_wpdb = static::get_connection();
			}

			$sql = $_wpdb->prepare( 'SELECT COUNT(*) FROM ' . self::get_table_name( $_wpdb ) . ' WHERE ' . $cond, $args );

			$_wpdb->suppress_errors( true );
			$count = (int) $_wpdb->get_var( $sql );
			if ( '' !== $_wpdb->last_error ) {
				if ( 1146 === self::get_last_sql_error( $_wpdb ) ) {
					if ( ( static::class )::create_table( $_wpdb ) ) {
						$count = 0;
					}
				}
			}
			$_wpdb->suppress_errors( false );

			return $count;
		}

		/**
		 * Saves the given data into the table
		 * The data should be in following format:
		 * field name => value
		 *
		 * It checks the given data array against the table fields and determines the types based on that, it stores the values in the table then.
		 *
		 * @param array $data - The data to be saved (check above about the format).
		 * @param \wpdb $connection - \wpdb connection to be used for name extraction.
		 *
		 * @return int
		 *
		 * @since 4.5.0
		 * @since 5.5.0 - Added connection parameter.
		 */
		public static function save( $data, $connection = null ) {
			if ( null !== $connection ) {
				if ( $connection instanceof \wpdb ) {
					$_wpdb = $connection;
				}
			} else {
				$_wpdb = static::get_connection();
			}

			$format      = array();
			$insert_data = array();

			foreach ( $data as $key => $val ) {
				if ( isset( ( static::class )::$fields[ $key ] ) ) {
					$insert_data[ $key ] = $val;
					$format[ $key ]      = '%s';
					if ( 'int' === ( static::class )::$fields[ $key ] ) {
						$format[ $key ] = '%d';
					}
					if ( 'float' === ( static::class )::$fields[ $key ] ) {
						$format[ $key ] = '%f';
					}
				}
			}

			if ( ! empty( $format ) ) {
				$_wpdb->suppress_errors( true );

				$_wpdb->replace( self::get_table_name( $_wpdb ), $insert_data, $format );

				if ( '' !== $_wpdb->last_error ) {
					if ( 1146 === self::get_last_sql_error( $_wpdb ) ) {
						if ( ( static::class )::create_table( $_wpdb ) ) {
							$_wpdb->replace( self::get_table_name( $_wpdb ), $data, $format );
						}
					}
				}

				$_wpdb->suppress_errors( false );

				return $_wpdb->insert_id;
			}

			return 0; // no record is inserted.
		}

		/**
		 * Prepares the data array and the format array based on the existing table fields
		 *
		 * @param array $data - The data to make preparation from.
		 *
		 * @return array
		 *
		 * @since 4.5.0
		 */
		public static function prepare_data( array $data ): array {

			$format      = array();
			$insert_data = array();

			foreach ( $data as $key => $val ) {
				if ( isset( ( static::class )::$fields[ $key ] ) ) {
					$insert_data[ $key ] = $val;
					$format[ $key ]      = '%s';
					if ( 'int' === ( static::class )::$fields[ $key ] ) {
						$format[ $key ] = '%d';
					}
					if ( 'float' === ( static::class )::$fields[ $key ] ) {
						$format[ $key ] = '%f';
					}
				}
			}

			return array( $insert_data, $format );
		}

		/**
		 * Load record from DB (Single row).
		 *
		 * @param string $cond - (Optional) Load condition.
		 * @param array  $args - (Optional) Load condition arguments.
		 * @param \wpdb  $connection - \wpdb connection to be used for name extraction.
		 * @param string $extra - The extra SQL string (if needed).
		 *
		 * @return array|object|null|void - Database query result or null on failure.
		 *
		 * @since 5.0.0
		 *
		 * @since 5.6.0 - Changed return comment
		 */
		public static function load( $cond = 'id=%d', $args = array( 1 ), $connection = null, $extra = '' ) {
			if ( null !== $connection ) {
				if ( $connection instanceof \wpdb ) {
					$_wpdb = $connection;
				}
			} else {
				$_wpdb = static::get_connection();
			}
			$sql = $_wpdb->prepare( 'SELECT * FROM ' . self::get_table_name( $_wpdb ) . ' WHERE ' . $cond, $args );

			if ( ! empty( trim( $extra ) ) ) {
				$sql .= $extra;
			}

			return $_wpdb->get_row( $sql, ARRAY_A );
		}

		/**
		 * Load records from DB (Multi rows).
		 *
		 * @param string $cond Load condition.
		 * @param array  $args (Optional) Load condition arguments.
		 * @param \wpdb  $connection - \wpdb connection to be used for name extraction.
		 * @param string $extra - The extra SQL string (if needed).
		 *
		 * @return array
		 *
		 * @since 5.0.0
		 */
		public static function load_array( $cond, $args = array(), $connection = null, $extra = '' ) {

			if ( null !== $connection ) {
				if ( $connection instanceof \wpdb ) {
					$_wpdb = $connection;
				}
			} else {
				$_wpdb = static::get_connection();
			}

			$result = array();
			$sql    = $_wpdb->prepare( 'SELECT * FROM ' . self::get_table_name( $_wpdb ) . ' WHERE ' . $cond, $args );

			if ( ! empty( trim( $extra ) ) ) {
				$sql .= $extra;
			}

			$_wpdb->suppress_errors( true );
			$results = $_wpdb->get_results( $sql, ARRAY_A );

			if ( '' !== $_wpdb->last_error ) {
				if ( 1146 === self::get_last_sql_error( $_wpdb ) ) {
					if ( ( static::class )::create_table( $_wpdb ) ) {
						$results = array();
					}
				}
			}
			$_wpdb->suppress_errors( false );

			foreach ( $results as $data ) {
				$result[] = static::load_data( $data );
			}

			return $result;
		}

		/**
		 * Load object data from variable.
		 *
		 * @param array|object $data Data array or object.
		 * @throws \Exception - Unsupported type.
		 *
		 * @since 5.0.0
		 */
		public static function load_data( $data ) {
			foreach ( (array) $data as $key => $val ) {
				$data[ $key ] = self::cast_to_correct_type( $key, $val );
			}
			return $data;
		}

		/**
		 * Casts given value to a correct type based on the type of property (identified by the $key) in the $copy object.
		 * This is to allow automatic type casting instead of handling each database column individually.
		 *
		 * @param string $key  Column key.
		 * @param mixed  $val  Value.
		 *
		 * @return mixed
		 * @throws \Exception - Unsupported type of data.
		 */
		public static function cast_to_correct_type( $key, $val ) {
			if ( ! is_null( $val ) && in_array( $key, array( 'user_id', 'username' ), true ) ) {
				// Username and user_id cannot have the default value set because some database queries rely on having
				// null values in the database.
				if ( 'user_id' === $key ) {
					return intval( $val );
				} elseif ( 'username' === $key ) {
					return (string) $val;
				}
			} elseif ( 'roles' === $key ) {
				return is_array( $val ) ? implode( ',', $val ) : $val;
			} elseif ( isset( ( static::class )::$fields_values[ $key ] ) ) {
				switch ( true ) {
					case is_string( ( static::class )::$fields_values[ $key ] ):
					case Validator::is_ip_address( $val ):
						return (string) $val;
					case is_array( ( static::class )::$fields_values[ $key ] ):
					case is_object( ( static::class )::$fields_values[ $key ] ):
						$json_decoded_val = PHP_Helper::json_decode( $val );
						return is_null( $json_decoded_val ) ? $val : $json_decoded_val;
					case is_int( ( static::class )::$fields_values[ $key ] ):
						return (int) $val;
					case is_float( ( static::class )::$fields_values[ $key ] ):
						$num_arr = \explode( '.', $val );

						$num_arr = array_slice( $num_arr, 0, 2 );

						$num_arr = array_map( 'intval', $num_arr );

						return implode( '.', $num_arr );
					case is_bool( ( static::class )::$fields_values[ $key ] ):
						return (bool) $val;
					default:
						throw new \Exception( \esc_html__( 'Unsupported type "', 'wp-security-audit-log' ) . gettype( ( static::class )::$fields_values[ $key ] ) . '"' ); // phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped
				}
			}
		}

		/**
		 * Delete records in DB matching a query.
		 *
		 * @param string $query Full SQL query.
		 * @param array  $args  (Optional) Query arguments.
		 * @param \wpdb  $connection - \wpdb connection to be used for name extraction.
		 *
		 * @return int|bool
		 *
		 * @since 4.6.0
		 */
		public static function delete_query( $query, $args = array(), $connection = null ) {
			if ( null !== $connection ) {
				if ( $connection instanceof \wpdb ) {
					$_wpdb = $connection;
				}
			} else {
				$_wpdb = static::get_connection();
			}

			$sql = count( $args ) ? $_wpdb->prepare( $query, $args ) : $query;

			$_wpdb->suppress_errors( true );
			$res = $_wpdb->query( $sql );
			if ( '' !== $_wpdb->last_error ) {
				if ( 1146 === self::get_last_sql_error( $_wpdb ) ) {
					if ( ( static::class )::create_table( $_wpdb ) ) {
						$res = true;
					}
				}
			}
			$_wpdb->suppress_errors( false );

			return $res;
		}

		/**
		 * Deletes data from the database.
		 *
		 * @param array $data - Array of data to be deleted.
		 * @param \wpdb $connection - \wpdb connection to be used for name extraction.
		 *
		 * @return bool
		 *
		 * @since 4.6.0
		 */
		public static function delete( $data, $connection = null ) {
			if ( isset( $data['id'] ) ) {
				return self::delete_by_id( (int) $data['id'], $connection );
			}
		}

		/**
		 * Default delete method
		 *
		 * @param integer $id - The real id of the table.
		 * @param \wpdb   $connection - \wpdb connection to be used for name extraction.
		 *
		 * @return int|bool
		 *
		 * @since 4.6.0
		 */
		public static function delete_by_id( int $id, $connection = null ) {
			if ( null !== $connection ) {
				if ( $connection instanceof \wpdb ) {
					$_wpdb = $connection;
				}
			} else {
				$_wpdb = static::get_connection();
			}

			$result = $_wpdb->delete(
				self::get_table_name( $_wpdb ),
				array( 'id' => $id ),
				array( '%d' )
			);

			return $result;
		}

		/**
		 * Duplicates table row by its ID
		 *
		 * @param integer $id - The ID of row to duplicate.
		 * @param \wpdb   $connection - The connection which has to be used.
		 *
		 * @return mixed
		 *
		 * @since 5.0.0
		 */
		public static function duplicate_by_id( int $id, $connection ) {
			if ( null !== $connection ) {
				if ( $connection instanceof \wpdb ) {
					$_wpdb = $connection;
				}
			} else {
				$_wpdb = static::get_connection();
			}

			$sql = 'INSERT INTO ' . self::get_table_name( $_wpdb ) . '
				(' . \implode( ',', static::get_duplicate_fields( false ) ) . ')
			SELECT 
				' . \implode( ',', static::get_duplicate_fields( true ) ) . '
			FROM 
				' . self::get_table_name( $_wpdb ) . '
			WHERE 
				id = ' . $id;

			$_wpdb->suppress_errors( true );

			$result = $_wpdb->query(
				$sql
			);

			if ( '' !== $_wpdb->last_error ) {
				if ( 1146 === self::get_last_sql_error( $_wpdb ) ) {
					if ( ( static::class )::create_table( $_wpdb ) ) {

						$result = $_wpdb->query(
							$sql
						);

					}
				}
			}
			$_wpdb->suppress_errors( false );

			return $_wpdb->insert_id;
		}

		/**
		 * Returns array with fields to duplicate, gets rid of id and created_on columns.
		 *
		 * @param bool $duplicate_values - When called for duplication, gives the class ability to set fields that must have specific values in the database.
		 *
		 * @return array
		 *
		 * @since 5.0.0
		 */
		public static function get_duplicate_fields( bool $duplicate_values ): array {
			$fields = self::get_fields();
			unset( $fields['id'] );
			if ( $duplicate_values && isset( $fields['created_on'] ) ) {
				$fields = \array_keys( $fields );
				$time   = \microtime( true );
				$key    = array_search( 'created_on', $fields, true );

				$fields[ $key ] = $time;

				return $fields;
			}

			return array_keys( $fields );
		}

		/**
		 * Default find method
		 *
		 * @param array $data - Must contains formats and data. The array should contain:
		 * 'data' - Associative array of all the fields and values to search for.
		 * 'formats' - array of all the formats for the data we are searching for.
		 *
		 * @return array|bool
		 *
		 * @since 4.6.0
		 */
		public static function find( array $data ) {
			/**
			 * \wpdb has very powerful method called process_fields @see \wpdb::process_fields().
			 * Unfortunately this method is not accessible, because it is marked protected. The best solution at the moment is to clone the class, lower the visibility and use the method.
			 *
			 * That of course takes resources so possible solution is to add also caching to this method, so that is marked as todo below.
			 *
			 * TODO: Add caching functionality to the method.
			 */
			// phpcs:disable
			$wpdb_clone = new class() extends \wpdb {

				public function __construct() {
					$dbuser     = defined( 'DB_USER' ) ? DB_USER : '';
					$dbpassword = defined( 'DB_PASSWORD' ) ? DB_PASSWORD : '';
					$dbname     = defined( 'DB_NAME' ) ? DB_NAME : '';
					$dbhost     = defined( 'DB_HOST' ) ? DB_HOST : '';

					parent::__construct( $dbuser, $dbpassword, $dbname, $dbhost );
				}

				public function process_fields( $name, $data, $formats ) {
					return parent::process_fields( $name, $data, $formats );
				}

			};
			// phpcs:enable

			$_wpdb = static::get_connection();

			$where_clause = $wpdb_clone->process_fields(
				self::get_table_name(),
				$data['data'],
				$data['formats']
			);

			$where_data = self::prepare_full_where( $where_clause );

			$conditions = $where_data['conditions'];
			$values     = $where_data['values'];

			$_wpdb->check_current_query = false;

			$sql = $_wpdb->prepare(
				'SELECT * FROM `' . self::get_table_name( $_wpdb ) . '` WHERE ' . $conditions,
				$values
			);

			$_wpdb->suppress_errors( true );

			$result = $_wpdb->get_results(
				$sql,
				ARRAY_A
			);

			if ( '' !== $_wpdb->last_error ) {
				if ( 1146 === self::get_last_sql_error( $_wpdb ) ) {
					if ( ( static::class )::create_table( $_wpdb ) ) {

						$result = array();

					}
				}
			}
			$_wpdb->suppress_errors( false );

			return $result;
		}

		/**
		 * Prepares full where clause
		 *
		 * @param array        $where_clause - Array prepared based on fields and values from the WP_DB.
		 * @param string       $condition - The where clause condition - default AND.
		 * @param string|null  $criteria - The criteria to check for.
		 * @param boolean|null $left_pref - For any starting value - partial where clause.
		 * @param boolean|null $right_pref - For any ending value - partial where clause.
		 *
		 * @return array
		 *
		 * @since 4.6.0
		 */
		public static function prepare_full_where(
			array $where_clause,
			string $condition = ' AND ',
			?string $criteria = ' = ',
			?bool $left_pref = false,
			?bool $right_pref = false
		): array {

			foreach ( $where_clause as $field => $value ) {
				if ( is_null( $value['value'] ) ) {
					$conditions[] = '`' . self::get_table_name() . '` . `' . $field . '` IS null';
					continue;
				}

				if ( \is_array( $value['value'] ) ) {
					$cond_string  = '(`' . self::get_table_name() . '` . `' . $field . '` ';
					$cond_string .= ' BETWEEN ';
					foreach ( $value['value'] as $val ) {
						$cond_string .= $value['format'] . ' ' . $condition . ' ';
						$values[]     =
						( ( $left_pref ) ? ' % ' : '' ) .
						$val .
						( ( $right_pref ) ? ' % ' : '' );
					}
					$cond_string  = rtrim( $cond_string, ' ' . $condition . ' ' ) . ')';
					$conditions[] = $cond_string;

					continue;
				}

				$conditions[] = '`' . self::get_table_name() . '` . `' . $field . '` ' . $criteria . ' ' .
				$value['format'];
				$values[]     =
				( ( $left_pref ) ? ' % ' : '' ) .
				$value['value'] .
				( ( $right_pref ) ? ' % ' : '' );
			}

			$conditions = implode( ' ' . $condition . ' ', $conditions );

			return array(
				'conditions' => $conditions,
				'values'     => $values,
			);
		}

		/**
		 * Similar to LoadMulti but allows the use of a full SQL query.
		 *
		 * @param string $query Full SQL query.
		 * @param array  $args  (Optional) Query arguments.
		 *
		 * @return array List of loaded records.
		 *
		 * @since 4.6.0
		 */
		public static function load_multi_query( $query, $args = array() ) {
			$_wpdb  = static::get_connection();
			$result = array();
			$sql    = count( $args ) ? $_wpdb->prepare( $query, $args ) : $query;
			foreach ( $_wpdb->get_results( $sql, ARRAY_A ) as $data ) {
				$result[] = self::load_data( $data );
			}

			return $result;
		}

		/**
		 * Builds SQL for given table.
		 *
		 * @param string       $query - Partial SQL query string.
		 * @param array|string $args - Arguments to be applied to the query.
		 *
		 * @return array
		 *
		 * @since 4.6.0
		 */
		public static function build_multi_query( $query, $args ) {
			$sql_query = 'SELECT * FROM `' . self::get_table_name() . '` ' . $query;

			return self::load_multi_query( $sql_query, $args );
		}

		/**
		 * Load multiple records from DB.
		 *
		 * @param string $cond (Optional) Load condition (eg: 'some_id = %d' ).
		 * @param array  $args (Optional) Load condition arguments (rg: array(45) ).
		 *
		 * @return array List of loaded records.
		 *
		 * @since 4.6.0
		 */
		public static function load_multi( $cond, $args = array() ) {
			$_wpdb  = static::get_connection();
			$result = array();
			$sql    = ( ! is_array( $args ) || ! count( $args ) ) // Do we really need to prepare() or not?
				? ( $cond )
				: $_wpdb->prepare( $cond, $args );

			$_wpdb->suppress_errors( true );

			$data_collected = $_wpdb->get_results( $sql, ARRAY_A );

			if ( '' !== $_wpdb->last_error ) {
				if ( 1146 === Occurrences_Entity::get_last_sql_error( $_wpdb ) ) {
					if ( Occurrences_Entity::create_table( $_wpdb ) ) {
						$data_collected = array();
					}
				}
			}
			$_wpdb->suppress_errors( false );

			foreach ( $data_collected as $data ) {
				foreach ( $data as $key => $val ) {
					$data[ $key ] = self::cast_to_correct_type( $key, $val );
				}

				$sql = $_wpdb->prepare( 'SELECT * FROM ' . Metadata_Entity::get_table_name() . ' WHERE  occurrence_id = %d', $data['id'] );

				$_wpdb->suppress_errors( true );
				$results = $_wpdb->get_results( $sql, ARRAY_A );

				if ( '' !== $_wpdb->last_error ) {
					if ( 1146 === Metadata_Entity::get_last_sql_error( $_wpdb ) ) {
						if ( Metadata_Entity::create_table( $_wpdb ) ) {
							$results = array();
						}
					}
				}
				$_wpdb->suppress_errors( false );
				$meta_results = array();
				foreach ( $results as $meta_key => $meta_val ) {
					$json_decoded_val                  = PHP_Helper::json_decode( $meta_val['value'] );
					$val                               = is_null( $json_decoded_val ) ? $meta_val['value'] : $json_decoded_val;
					$meta_results[ $meta_val['name'] ] = maybe_unserialize( $val );
				}

				foreach ( Occurrences_Entity::$migrated_meta as $meta_key => $column_name ) {
					$meta_results[ $meta_key ] = $data[ $column_name ];
				}

				$data['meta_values'] = $meta_results;

				$result[] = $data;
			}

			return $result;
		}

		/**
		 * Return the table fields and default values
		 *
		 * @return array
		 *
		 * @since 4.6.0
		 */
		public static function get_fields_values(): array {
			return ( static::class )::$fields_values;
		}

		/**
		 * Return the table fields
		 *
		 * @return array
		 *
		 * @since 4.6.0
		 */
		public static function get_fields(): array {
			return ( static::class )::$fields;
		}

		/**
		 * Builds a query based on the given parameters.
		 * The array could contain of:
		 *  - All ORs in the WHERE
		 *  - All ANDs in the WHERE
		 * It must be SQL string with interpolation, and its value.
		 *
		 * @param array $select_fields - Fields to use in the select statement of the query.
		 * @param array $query_parameters - Array with all the where clause expressions.
		 * @param array $order_by - Ordering array with fields and type of order.
		 * @param array $limit - Limit section of the query - array with range (if there is a need of range).
		 * @param array $join - Join section of the query.
		 * @param \wpdb $connection - \wpdb connection to be used for name extraction.
		 *
		 * @return array
		 *
		 * @since 4.6.0
		 */
		public static function build_query(
			array $select_fields = array(),
			array $query_parameters = array(),
			array $order_by = array(),
			array $limit = array(),
			array $join = array(),
			$connection = null
		) {

			$where_clause = ' WHERE 1 ';
			$query        = 'SELECT ';
			$order_clause = '';
			$limit_clause = '';
			$join_clause  = '';

			$values     = array();
			$sql_values = array();

			$string_clause = '';
			/**
			 * $query_parameters = [
			 *   OR = [
			 *      0 = [
			 *         'alert_id' = '"%s"' = [
			 *            0 = "1007"
			 *         ]
			 *      ]
			 *   ]
			 * $query_parameters = [
			 *   'AND' = [
			 *      0 = [
			 *          'wp_wsal_occurrences.alert_id LIKE %s' = '"9073"'
			 *      ]
			 *   ]
			 *   'OR' = [
			 *      0 = [
			 *         'alert_id' = '"%s"' = [
			 *            0 = '"9073"'
			 *            1 = '"4015"'
			 *         ]
			 *      ]
			 *      1 = [
			 *         'object' = '"%s"' = [
			 *            0 = 'woocommerce-product'
			 *         ]
			 *      ]
			 *   ]
			 *
			 * WHERE 1  AND ((wp_wsal_occurrences.alert_id LIKE %s) AND ( (alert_id = "%s") OR (alert_id = "%s") )  AND ( ( object = %s ) ) )
			 */
			$first_time = true;
			if ( isset( $query_parameters['OR'] ) && ! empty( $query_parameters['OR'] ) && isset( $query_parameters['AND'] ) && ! empty( $query_parameters['AND'] ) ) {

				$query_parameters = array(
					'OR'  => $query_parameters['OR'],
					'AND' => $query_parameters['AND'],
				);

			}
			foreach ( $query_parameters as $clause => $values ) {
				if ( ! $first_time ) {
					$string_clause .= ' ' . \strtoupper( $clause ) . ' ( ';
				} else {
					$string_clause .= ' ( ';
				}
				$first_time          = false;
				$sub_join_clause     = '';
				$sub_sub_join_clause = '';
				foreach ( $values as $sub_clause => $sub_values ) {
					$sub_join_clause = '';
					if ( ! \is_int( $sub_clause ) ) {
						$sub_join_clause = $sub_clause;
						if ( ' ( ' !== $string_clause ) {
							$string_clause = \rtrim( $string_clause, ' ( ' );
							if ( ! $first_time ) {
								$string_clause = \rtrim( $string_clause, ' ' . \strtoupper( $clause ) . ' ' );
							}

							if ( empty( $sub_sub_join_clause ) ) {
								$string_clause .= ' ' . \strtoupper( $clause ) . ' ( ( ';
							} elseif ( ! empty( $sub_sub_join_clause ) ) {
								$string_clause      .= ' ' . \strtoupper( $clause ) . ' ( ';
								$sub_sub_join_clause = '';
							}
						} else {
							$string_clause .= ' ( ';
						}
					}
					if ( \is_array( $sub_values ) ) {
						foreach ( $sub_values as $value => $val ) {
							$sql_val = $val;
							if ( ! \is_int( $value ) ) {
								$sub_sub_join_clause = $value;
							} else {
								$sub_sub_join_clause = $sub_join_clause;
							}
							if ( \is_array( $val ) ) {
								foreach ( $val as $sql_string => $substitutes ) {
									$sql_value = $sql_string;
									if ( \is_array( $substitutes ) ) {
										foreach ( $substitutes as $substitute ) {
											$sql_values[] = $substitute;

											if ( ! empty( $sub_sub_join_clause ) ) {
												$logic_join = $sub_sub_join_clause;
											} else {
												$logic_join = $clause;
											}
											$string_clause .= '(' . $sql_value . ') ' . \strtoupper( $logic_join ) . ' ';
										}
									} else {
										if ( ! empty( $sub_sub_join_clause ) ) {
											$logic_join = $sub_sub_join_clause;
										} else {
											$logic_join = $clause;
										}
										$string_clause .= '(' . $sql_value . ') ' . \strtoupper( $logic_join ) . ' ';
										if ( \is_array( $sql_val ) ) {
											$sql_values[] = reset( $sql_val );
										} else {
											$sql_values[] = $sql_val;
										}
									}
								}

								unset( $sub_values[ $value ] );

								$string_clause = \rtrim( $string_clause, $sub_sub_join_clause . ' ' );
								if ( count( $sub_values ) ) {
									$string_clause .= ' ) ' . $clause . ' ( ';
								} else {
									$string_clause .= ' ) ';
								}
							} else {
								if ( ! empty( $sub_join_clause ) ) {
									$logic_join = $sub_join_clause;
									$value      = \array_key_first( $val );
									$sql_val    = reset( $val[ \array_key_first( $val ) ] );
								} else {
									$logic_join = $clause;
								}
								$string_clause .= '(' . $value . ') ' . \strtoupper( $logic_join ) . ' ';
								$sql_values[]   = $sql_val;

								unset( $values[ $sub_clause ] );
								if ( 0 === count( $values ) ) {
									$string_clause  = \rtrim( $string_clause, \strtoupper( $logic_join ) . ' ' );
									$string_clause .= ' ) ';
								}
							}
						}
					} elseif ( \is_array( $sub_values ) ) {
							$string_clause .= '(' . \array_key_first( $sub_values ) . ')';
							$sql_values[]   = reset( $sub_values );
					} else {
						$string_clause .= '(' . $sub_clause . ')';
						$sql_values[]   = $sub_values;
					}
				}
				if ( ! empty( $sub_join_clause ) ) {
					$logic_join = $sub_join_clause;
				} else {
					$logic_join = $clause;
				}
				$string_clause = \rtrim( $string_clause, $logic_join . ' ' );
				if ( isset( $sub_clause ) && ! \is_int( $sub_clause ) ) {
					$sub_join_clause = $sub_clause;
					$string_clause  .= ' ) ';
				}
				if ( empty( $sub_sub_join_clause ) ) {
					$string_clause .= ' ) ';
				}
			}

			if ( ! empty( $string_clause ) ) {
				$where_clause .= ' AND (' . $string_clause . ')';
			}

			if ( ! empty( $order_by ) ) {
				$order_clause .= ' ORDER BY ';
				foreach ( $order_by as $clause => $order ) {
					$order_clause .= $clause . ' ' . $order . ', ';
				}
				$order_clause = \rtrim( $order_clause, ', ' );
			}

			if ( ! empty( $limit ) ) {
				$limit_clause = ' LIMIT ';
				foreach ( $limit as $value ) {
					$limit_clause .= $value . ', ';
				}

				$limit_clause = \rtrim( $limit_clause, ', ' );
			}

			if ( ! empty( $select_fields ) ) {
				foreach ( $select_fields as $field => $alias ) {
					$query .= $field;
					if ( $alias !== $field ) {
						$query .= ' AS ' . $alias;
					}
					$query .= ', ';
				}
				$query = \rtrim( $query, ', ' );
			} else {
				$query .= self::get_table_name( $connection ) . '.*';
			}

			/**
			 * Join format:
			 * [
			 *   'table_name' - Table to join
			 *        [
			 *           'direction' - Left, right, full - type of the join.
			 *           'join_fields' - fields and corresponding tables to join.
			 *              [
			 *                 'join_table_left' - If it is set, join given table, if not use the current
			 *                 'join_field_left' - name of the field to join
			 *                 'join_table_right' - If it is set, join given table, if not use the default (no table)
			 *                 'join_field_right' - name of the field to join
			 *              ]
			 *        ]
			 * ]
			 */
			if ( ! empty( $join ) ) {
				foreach ( $join as $table => $rules ) {

					if ( isset( $rules['direction'] ) ) {
						$join_clause .= ' ' . \strtoupper( $rules['direction'] ) . ' JOIN ' . $table . ' ON ';
					} else {
						$join_clause .= ' LEFT JOIN ' . $table . ' ON ';
					}

					foreach ( $rules['join_fields'] as $fields ) {
						if ( isset( $fields['join_table_left'] ) ) {
							$join_clause .= $fields['join_table_left'] . '.';
						} else {
							$join_clause .= $table . '.';
						}
						$join_clause .= $fields['join_field_left'] . ' = ';
						if ( isset( $fields['join_table_right'] ) ) {
							$join_clause .= $fields['join_table_right'] . '.';
						}
						$join_clause .= $fields['join_field_right'] . ' AND ';
					}

					$join_clause = rtrim( $join_clause, ' AND ' );
				}
			}

			$query .= ' FROM ' . self::get_table_name( $connection );
			$query .= ' ' . $join_clause . ' ' . $where_clause . ' ' . $order_clause . ' ' . $limit_clause;

			if ( null !== $connection ) {
				if ( $connection instanceof \wpdb ) {
					$_wpdb = $connection;
				}
			} else {
				$_wpdb = static::get_connection();
			}

			$sql_values = array_filter( $sql_values );

			// If it is simple query without placeholders - lets act properly.
			if ( ! empty( $sql_values ) ) {
				$sql = $_wpdb->prepare( $query, $sql_values );
			} else {
				$sql = $query;
			}

			$_wpdb->suppress_errors( true );
			$results = $_wpdb->get_results( $sql, ARRAY_A );

			if ( '' !== $_wpdb->last_error ) {
				if ( 1146 === self::get_last_sql_error( $_wpdb ) ) {
					if ( ( static::class )::create_table( $_wpdb ) ) {
						$results = array();
					}
				} else {
					Logger::log( 'Error: ' . (string) $_wpdb->last_error . ' Line: ' . __LINE__ . ' File: ' . __FILE__ . ' SQL data: query - ' . $query . ' Values: ' . \print_r( $sql_values, true ) ); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_print_r
				}
			}
			$_wpdb->suppress_errors( false );

			if ( empty( $results ) ) {
				$results = array( 0 => array( 0 ) );
			}

			return $results;
		}

		/**
		 * Creates array with the full filed names (table name included) for the given table
		 *
		 * @param \wpdb $connection - \wpdb connection to be used for name extraction.
		 *
		 * @return array
		 *
		 * @since 4.6.0
		 */
		public static function prepare_full_select_statement( $connection = null ): array {
			$full_fields = array();
			foreach ( \array_keys( ( static::class )::$fields ) as $field ) {
				$full_fields[ self::get_table_name( $connection ) . '.' . $field ] = self::get_table_name( $connection ) . $field;
			}

			return $full_fields;
		}

		/**
		 * Returns the table size in Megabyte format
		 *
		 * @param \wpdb $connection - The connection object to be used, if empty the default is used.
		 *
		 * @return int
		 *
		 * @since 4.6.0
		 */
		public static function get_table_size( $connection = null ) {
			if ( null !== $connection ) {
				if ( $connection instanceof \wpdb ) {
					$_wpdb = $connection;
				}
			} else {
				$_wpdb = static::get_connection();
			}

			$sql = "SELECT 
				ROUND(((data_length + index_length) / 1024 / 1024), 2) AS `Size (MB)`
			FROM
				information_schema.TABLES
			WHERE
				table_schema = '" . $_wpdb->dbname . "'
				AND table_name = '" . self::get_table_name( $_wpdb ) . "';";

			$_wpdb->suppress_errors( true );
			$results = $_wpdb->get_var( $sql );

			if ( '' !== $_wpdb->last_error ) {
				if ( 1146 === self::get_last_sql_error( $_wpdb ) ) {
					if ( ( static::class )::create_table( $_wpdb ) ) {
						$results = array();
					}
				}
			}
			$_wpdb->suppress_errors( false );

			if ( $results ) {
				return $results;
			}

			return 0;
		}
	}
}