Elep

<?php
/**
 * Data mapper for CRUD.
 *
 * @package Calotes\DB
 */

namespace Calotes\DB;

use Calotes\Base\Model;
use Calotes\Base\Component;

/**
 * Responsible for performing CRUD operations.
 */
class Mapper extends Component {

/**
	 * Contain the current model class name.
	 *
	 * @var string
	 */
	private $repository;

/**
	 * The columns to select in the query.
	 *
	 * @var string
	 */
	private $select = '';

/**
	 * Where statements for the query.
	 *
	 * @var array
	 */
	private $where = array();

/**
	 * The grouping parameter for the query.
	 *
	 * @var string
	 */
	private $group = '';

/**
	 * The ordering parameter for the query.
	 *
	 * @var string
	 */
	private $order = '';

/**
	 * The limit for the query results.
	 *
	 * @var string
	 */
	private $limit = '';

/**
	 * Cache for storing retrieved records.
	 *
	 * @var array
	 */
	private $known = array();

/**
	 * Store the last executed query.
	 *
	 * @var string
	 */
	public $saved_queries = '';

/**
	 * Set the repository class name.
	 *
	 * @param  mixed $class_name  The class name to set for the repository.
	 *
	 * @return $this
	 */
	public function get_repository( $class_name ) {
		$this->repository = $class_name;

return $this;
	}

/**
	 * Set the columns to select in the SQL query.
	 *
	 * @param  mixed $select  The columns to select.
	 *
	 * @return $this
	 */
	public function select( $select ) {
		$this->select = $select;

return $this;
	}

/**
	 * Set the WHERE clause for the query based on the provided arguments.
	 *
	 * Supports multiple call signatures:
	 * - where($column, $value) - equals comparison
	 * - where($column, $operator, $value) - custom operator comparison
	 *
	 * @param  mixed ...$args  The conditions to apply in the WHERE clause.
	 *
	 * @return $this
	 */
	public function where( ...$args ) {
		$result = $this->prepare_where_args( $args );

if ( null === $result ) {
			return $this;
		}

[ $column, $operator, $value ] = $result;

if ( ! $this->valid_operator( $operator ) ) {
			return $this;
		}

$sql = $this->compile_where( $column, $operator, $value );

if ( $sql ) {
			$this->where[] = $sql;
		}

return $this;
	}

/**
	 * Prepare where arguments - handles both 2-arg and 3-arg signatures.
	 *
	 * @param  array $args  The arguments passed to where().
	 *
	 * @return array|null [$column, $operator, $value] or null if invalid argument count.
	 */
	private function prepare_where_args( array $args ): ?array {
		$count = count( $args );

if ( 2 === $count ) {
			return array( $args[0], '=', $args[1] );
		}

if ( 3 === $count ) {
			return array( $args[0], $args[1], $args[2] );
		}

return null;
	}

/**
	 * Compile a where clause into SQL.
	 *
	 * @param  string $column    The column name.
	 * @param  string $operator  The operator.
	 * @param  mixed  $value     The value to compare against.
	 *
	 * @return string|null The compiled SQL or null if invalid.
	 */
	private function compile_where( string $column, string $operator, $value ): ?string {
		global $wpdb;

$op_lower = strtolower( $operator );

// Handle IN / NOT IN operators.
		if ( in_array( $op_lower, array( 'in', 'not in' ), true ) ) {
			return $this->compile_where_in( $column, $operator, $value );
		}

// Handle BETWEEN operator.
		if ( 'between' === $op_lower ) {
			return $this->compile_where_between( $column, $operator, $value );
		}

// Handle basic comparison operators.
		$placeholder = $this->guess_var_type( $value );

// phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return $wpdb->prepare( "`$column` $operator $placeholder", $value );
	}

/**
	 * Compile a WHERE IN / NOT IN clause.
	 *
	 * @param  string $column    The column name.
	 * @param  string $operator  The operator (IN or NOT IN).
	 * @param  mixed  $values    The values (should be array, invalid inputs skipped).
	 *
	 * @return string|null The compiled SQL or null if empty values.
	 */
	private function compile_where_in( string $column, string $operator, $values ): ?string {
		if ( ! is_array( $values ) || 0 === count( $values ) ) {
			return null;
		}

global $wpdb;

$placeholders = array();
		foreach ( $values as $val ) {
			$placeholders[] = $this->guess_var_type( $val );
		}

$sql_template = "`$column` $operator (" . implode( ', ', $placeholders ) . ')';

// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		return $wpdb->prepare( $sql_template, ...$values );
	}

/**
	 * Compile a WHERE BETWEEN clause.
	 *
	 * @param  string $column    The column name.
	 * @param  string $operator  The operator (BETWEEN).
	 * @param  mixed  $values    The values (should be array with min/max, invalid inputs skipped).
	 *
	 * @return string|null The compiled SQL or null if invalid values.
	 */
	private function compile_where_between( string $column, string $operator, $values ): ?string {
		if ( ! is_array( $values ) || count( $values ) < 2 ) {
			return null;
		}

global $wpdb;

$placeholder_min = $this->guess_var_type( $values[0] );
		$placeholder_max = $this->guess_var_type( $values[1] );

return $wpdb->prepare(
			"`$column` $operator $placeholder_min AND $placeholder_max", // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$values[0],
			$values[1]
		);
	}

/**
	 * Guess the type of value for correcting placeholder.
	 *
	 * @param  mixed $value  The value to guess.
	 *
	 * @return string
	 */
	private function guess_var_type( $value ): string {
		if ( false !== filter_var( $value, FILTER_VALIDATE_INT ) ) {
			return '%d';
		}

if ( false !== filter_var( $value, FILTER_VALIDATE_FLOAT ) ) {
			return '%f';
		}

return '%s';
	}

/**
	 * Find a record by its ID.
	 *
	 * @param  int $id  The ID of the record.
	 *
	 * @return $this
	 */
	public function find_by_id( $id ) {
		global $wpdb;
		$this->where[] = $wpdb->prepare( 'id = %d', $id );

return $this;
	}

/**
	 * Set the group by clause for the SQL query based on the provided argument.
	 *
	 * @param  string $group_by  The column to group by.
	 *
	 * @return $this
	 */
	public function group_by( $group_by ) {
		global $wpdb;
		$this->group = str_replace(
			"'",
			'',
			$wpdb->prepare( 'GROUP BY %s', $group_by )
		);

return $this;
	}

/**
	 * Set the order for the SQL query based on the provided arguments.
	 *
	 * @param  mixed  $order_by  The column to order by.
	 * @param  string $order  The order direction, defaults to 'asc'.
	 *
	 * @return $this
	 */
	public function order_by( $order_by, $order = 'asc' ) {
		global $wpdb;
		if ( ! in_array( $order, array( 'asc', 'desc' ), true ) ) {
			// Fall it back.
			$order = 'asc';
		}
		$this->order = str_replace(
			"'",
			'',
			$wpdb->prepare( 'ORDER BY %s %s', $order_by, $order )
		);

return $this;
	}

/**
	 * Set the limit for the SQL query based on the provided value.
	 *
	 * @param  int      $limit  The limit value.
	 * @param  int|null $offset The offset value.
	 *
	 * @return $this
	 */
	public function limit( $limit, $offset = null ) {
		global $wpdb;

if ( null === $offset ) {
			$this->limit = $wpdb->prepare( 'LIMIT %d', $limit );
		} else {
			$this->limit = $wpdb->prepare( 'LIMIT %d OFFSET %d', $limit, $offset );
		}

return $this;
	}

/**
	 * Find the first.
	 *
	 * @return null|Model
	 */
	public function first() {
		$this->limit         = 'LIMIT 0,1';
		$sql                 = $this->query_build(); // SQL is prepared here. We will ignore prepare rules.
		$this->saved_queries = $sql;
		global $wpdb;
		$data = $wpdb->get_row( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
		if ( is_null( $data ) ) {
			return null;
		}
		// Check if we have any json string in property.
		foreach ( $data as &$datum ) {
			if ( is_string( $datum ) ) {
				$tmp = json_decode( $datum, true );
				if ( is_array( $tmp ) ) {
					$datum = $tmp;
				}
			}
		}
		$class_name = $this->repository;
		$model      = new $class_name();
		$model->import( $data );

return $model;
	}

/**
	 * Retrieves the models based on the data obtained from get_results().
	 *
	 * @return array
	 */
	public function get() {
		$data   = $this->get_results();
		$models = array();
		foreach ( $data as $row ) {
			foreach ( $row as &$property ) {
				if ( is_string( $property ) ) {
					$tmp = json_decode( $property, true );
					if ( is_array( $tmp ) ) {
						$property = $tmp;
					}
				}
			}
			$class_name = $this->repository;
			$model      = new $class_name();
			$model->import( $row );
			$models[] = $model;
		}

return $models;
	}

/**
	 * Get records in array form.
	 *
	 * @return array
	 * @since 2.7.0
	 */
	public function get_results() {
		$sql                 = $this->query_build(); // SQL is prepared here.
		$this->saved_queries = $sql;

global $wpdb;
		$data = $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery
		if ( is_null( $data ) ) {
			$data = array();
		}

return $data;
	}

/**
	 * Get the count of records based on the provided query.
	 *
	 * @return string|null The count of records.
	 */
	public function count() {
		global $wpdb;
		$sql = $this->query_build( 'COUNT(*)' ); // SQL is prepared here.

$result = $wpdb->get_var( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery

return $result;
	}

/**
	 * Handle the insert/update of current model.
	 *
	 * @param  Model $model  The model to save.
	 *
	 * @return int|bool The ID of current record OR false.
	 * @throws \ReflectionException If class is not defined.
	 */
	public function save( Model &$model ) {
		global $wpdb;
		$data          = $model->export();
		$data_type     = array();
		$exported_type = $model->export_type();
		unset( $data['table'] );
		unset( $data['safe'] );
		foreach ( $data as $key => &$val ) {
			if ( is_array( $val ) ) {
				$val = wp_json_encode( $val );
			} elseif ( is_bool( $val ) ) {
				$val = $val ? 1 : 0;
			}

$data_type[] = $exported_type[ $key ] ?? '%s';
		}
		$table = self::table( $model );
		if ( $model->id ) {
			$ret = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
				$table,
				$data,
				array( 'id' => $model->id ),
				$data_type,
				array( '%d' )
			);
		} else {
			$ret = $wpdb->insert( $table, $data, $data_type ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
			// Bind this for later use.
			$model->id = $wpdb->insert_id;
		}

if ( false === $ret ) {
			return false;
		}

return $wpdb->insert_id;
	}

/**
	 * Delete a record from the database table based on the provided conditions.
	 *
	 * @param  mixed $where  The conditions to apply when deleting the record.
	 *
	 * @return int|false The number of rows affected or false on failure.
	 * @throws \ReflectionException If class is not defined.
	 */
	public function delete( $where ) {
		$table = self::table();
		global $wpdb;

return $wpdb->delete( $table, $where ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
	}

/**
	 * Delete all records from the database table based on the provided conditions.
	 *
	 * @return int|false The number of rows affected or false on failure.
	 * @throws \ReflectionException If class is not defined.
	 */
	public function delete_all() {
		$table = self::table();
		global $wpdb;

$where = implode( ' AND ', $this->where );
		$sql   = "DELETE FROM $table WHERE $where";
		$this->clear();

return $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
	}

/**
	 * Delete records from the database table based on the provided conditions and limit.
	 *
	 * @return int|false The number of rows affected or false on failure.
	 * @throws \ReflectionException If class is not defined.
	 */
	public function delete_by_limit() {
		$table = self::table();
		global $wpdb;

$where = implode( ' AND ', $this->where );
		$limit = $this->limit;
		$order = $this->order;
		$sql   = "DELETE FROM $table WHERE $where $order $limit";
		$this->clear();

return $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
	}

/**
	 * Handle the truncation of a database table.
	 *
	 * @return int|false The number of rows affected or false on failure.
	 * @throws \ReflectionException If class is not defined.
	 */
	public function truncate() {
		$table = self::table();
		global $wpdb;

// Uninstall/reset flows can run in mixed Free/Pro states where some tables were never created.
		$exists = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$wpdb->prepare(
				'SHOW TABLES LIKE %s',
				$wpdb->esc_like( $table )
			)
		);

if ( empty( $exists ) ) {
			return false;
		}

$query = "TRUNCATE TABLE $table"; // SQL is prepared here. so we can ignore WordPress.DB.PreparedSQL.NotPrepared.

return $wpdb->query( $query );  // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
	}

/**
	 * It is used to retrieve the table name associated with a given model.
	 *
	 * @param  mixed $model  (optional) The model object or class name. If not provided, it uses the "repository"
	 *                    property of the class.
	 *
	 * @return string|false The table name with the WordPress database prefix, or false if the table property doesn't
	 *     exist or an exception occurs.
	 * @throws \ReflectionException If the model class doesn't exist.
	 */
	private function table( $model = null ) {
		if ( is_null( $model ) ) {
			$model = $this->get_model();
		}
		if ( is_object( $model ) && method_exists( $model, 'get_table' ) ) {
			$table = $model->get_table();
			if ( null !== $table ) {
				global $wpdb;

// Have to set the prefix.
				return $wpdb->base_prefix . $table;
			}
		}
		// This when class doesn't exist.
		return false;
	}

/**
	 * Reset all the queries prepare after an action.
	 */
	private function clear() {
		$this->select = '';
		$this->where  = array();
		$this->group  = '';
		$this->order  = '';
		$this->limit  = '';
	}

/**
	 * Join the stuff on the table to make a full query statement.
	 * SQL params e.g. WHERE, ORDER or LIMIT were escaped on separate methods.
	 *
	 * @param  string $select  Columns to select.
	 *
	 * @return string
	 * @throws \ReflectionException If class is not defined.
	 */
	private function query_build( string $select = '*' ) {
		$table       = $this->table();
		$where_parts = array_filter(
			$this->where,
			static function ( $clause ) {
				return null !== $clause && '' !== trim( (string) $clause );
			}
		);
		$where       = implode( ' AND ', $where_parts );
		$where       = '' === $where ? '1=1' : $where;

$select   = '' !== $this->select ? $this->select : $select;
		$group_by = $this->group;
		$order_by = $this->order;
		$limit    = $this->limit;
		$sql      = "SELECT $select FROM $table WHERE $where $group_by $order_by $limit";
		$this->clear();

return $sql;
	}

/**
	 * Checks if the given operator is valid.
	 *
	 * @param  string $operator  The operator to check.
	 *
	 * @return bool True if the operator is valid, false otherwise.
	 */
	private function valid_operator( $operator ) {
		$operator = strtolower( $operator );
		$allowed  = array(
			'in',
			'not in',
			'>',
			'<',
			'=',
			'<=',
			'>=',
			'like',
			'between',
			'regexp',
			'not regexp',
		);

return in_array( $operator, $allowed, true );
	}

/**
	 * Cache the model instance for clone & reference use.
	 *
	 * @return mixed
	 */
	private function get_model() {
		if ( isset( $this->known[ $this->repository ] ) ) {
			return $this->known[ $this->repository ];
		}
		$class                            = $this->repository;
		$model                            = new $class();
		$this->known[ $this->repository ] = $model;

return $model;
	}
}