<?php
/**
 * Handles general database operations (CRUD).
 *
 * @package Awesomemotive\Rewardswp\Database
 * @since 1.0.0
 */

/* phpcs:disable WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- We have control over our query engine */
/* phpcs:disable WordPress.DB.SlowDBQuery.slow_db_query_meta_value -- We have control over our query engine */

namespace Awesomemotive\Rewardswp\Database;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

use Awesomemotive\Rewardswp\GlobalConfig;
use function Awesomemotive\Rewardswp\Helpers\rewardswp_log_error;

/**
 * Class DatabaseHandler
 *
 * This class provides basic CRUD functionality for interacting with the WordPress database.
 *
 * @since 1.0.0
 */
class DatabaseHandler {
	/**
	 * Cache group name.
	 *
	 * @since 1.0.0
	 */
	const CACHE_GROUP = 'rewardswp_data_cache';

	/**
	 * Cache expiration time in seconds.
	 *
	 * @since 1.0.0
	 */
	const CACHE_EXPIRATION = HOUR_IN_SECONDS;

	/**
	 * Stores the cache keys for tables during the current request.
	 *
	 * @since 1.0.0
	 *
	 * @var array
	 */
	private array $cache_keys = [];


	/**
	 * Globals wrapper.
	 *
	 * @since 1.0.0
	 *
	 * @var GlobalConfig
	 */
	protected GlobalConfig $global_config;

	/**
	 * Constructor.
	 *
	 * @since 1.0.0
	 * @param GlobalConfig $global_config Globals.
	 */
	public function __construct( GlobalConfig $global_config ) {
		$this->global_config = $global_config;
	}

	/**
	 * Generates a cache key for queries.
	 *
	 * @since 1.0.0
	 *
	 * @param string       $type    Query type (e.g., 'get_row', 'get_results').
	 * @param string       $table   Table name.
	 * @param array        $where   WHERE clause.
	 * @param array|string $columns Columns being retrieved.
	 * @param int          $limit   Number of rows to retrieve.
	 * @param int          $offset  Optional. Offset for the rows.
	 *
	 * @return string Generated cache key.
	 */
	private function generate_cache_key( string $type, string $table, array $where, $columns, int $limit, int $offset = 0 ): string {
		return sprintf(
			'%s_%s_%s_%s_%d_%d',
			$type,
			$table,
			md5(
				wp_json_encode( $where )
			),
			md5(
				is_array( $columns )
					? wp_json_encode( $columns )
					: $columns
			),
			$limit,
			$offset
		);
	}

	/**
	 * Clears cache for a specific table.
	 *
	 * @since 1.0.0
	 *
	 * @param string $table Table name.
	 */
	private function clear_cache_for_table( string $table ) {
		// Check if we have keys for this table.
		if ( ! isset( $this->cache_keys[ $table ] ) ) {
			return;
		}

		// Delete all keys associated with the table.
		foreach ( $this->cache_keys[ $table ] as $cache_key ) {
			wp_cache_delete( $cache_key, self::CACHE_GROUP );
		}

		// Remove the keys from the property.
		unset( $this->cache_keys[ $table ] );
	}

	/**
	 * Saves a value in the cache and tracks the cache key for a specific table.
	 *
	 * @since 1.0.0
	 *
	 * @param string $table     Table name.
	 * @param string $cache_key Unique cache key.
	 * @param mixed  $value     The value to cache.
	 */
	private function set_cache( string $table, string $cache_key, $value ) {
		// Save the value in the cache.
		wp_cache_set( $cache_key, $value, self::CACHE_GROUP, self::CACHE_EXPIRATION );

		// Track the cache key in the property.
		if ( ! isset( $this->cache_keys[ $table ] ) ) {
			$this->cache_keys[ $table ] = [];
		}

		if ( ! in_array( $cache_key, $this->cache_keys[ $table ], true ) ) {
			$this->cache_keys[ $table ][] = $cache_key;
		}
	}

	/**
	 * Retrieves a value from the cache.
	 *
	 * @since 1.0.0
	 *
	 * @param string $cache_key Unique cache key.
	 *
	 * @return mixed The cached value, or false if not found.
	 */
	private function get_cache( string $cache_key ) {
		return wp_cache_get( $cache_key, self::CACHE_GROUP );
	}

	/**
	 * Inserts a row into the specified table and returns the inserted row's ID.
	 *
	 * @since 1.0.0
	 *
	 * @param string $table Table name.
	 * @param array  $data  Data to insert (key-value pairs).
	 *
	 * @return int The inserted row's ID on success, or 0 on failure.
	 */
	public function insert( string $table, array $data ): int {
		$db = $this->global_config->get_db();

		$result = $db->insert( $table, $data );

		if ( $result !== false ) {
			// Clear the cache.
			$this->clear_cache_for_table( $table );

			// Return the new object ID.
			return $db->insert_id;
		}

		return 0;
	}

	/**
	 * Updates rows in the specified table.
	 *
	 * @since 1.0.0
	 *
	 * @param string $table Table name.
	 * @param array  $data  Data to update (key-value pairs).
	 * @param array  $where WHERE clause for which rows to update.
	 *
	 * @return int|false Number of rows affected, or false on failure.
	 */
	public function update( string $table, array $data, array $where ) {
		$updated = $this->global_config->get_db()->update( $table, $data, $where );

		if ( $updated ) {
			$this->clear_cache_for_table( $table );
		}

		return $updated;
	}

	/**
	 * Deletes rows from the specified table.
	 *
	 * @since 1.0.0
	 *
	 * @param string $table Table name.
	 * @param array  $where WHERE clause for which rows to delete.
	 *
	 * @return int|false Number of rows deleted, or false on failure.
	 */
	public function delete( string $table, array $where ) {
		$deleted = $this->global_config->get_db()->delete( $table, $where );

		if ( $deleted ) {
			$this->clear_cache_for_table( $table );
		}

		return $deleted;
	}

	/**
	 * Retrieves a single row from the specified table, with caching.
	 *
	 * @since 1.0.0
	 *
	 * @param string $table   Table name.
	 * @param array  $where   WHERE clause for the row to retrieve.
	 * @param array  $columns Optional. Specific columns to retrieve. Defaults to all columns.
	 *
	 * @return object|null The row object, or null if no row found.
	 */
	public function get_row( string $table, array $where, array $columns = [] ): ?object {
		// Ensure column selection or fallback to all columns.
		$columns_str = ! empty( $columns ) ? implode( ',', array_map( 'sanitize_text_field', $columns ) ) : '*';

		// Generate a unique cache key.
		$cache_key = $this->generate_cache_key( 'get_row', $table, $where, $columns, 1 );

		// Attempt to retrieve the result from cache.
		$cached_result = $this->get_cache( $cache_key );

		if ( false !== $cached_result ) {
			return $cached_result;
		}

		// Retrieve the database instance.
		$db = $this->global_config->get_db();

		// Build the WHERE clause with placeholders and values.
		$where_clauses = [];
		$values        = [];

		foreach ( $where as $column => $value ) {
			$where_clauses[] = sanitize_text_field( $column ) . ' = %s';
			$values[]        = $value;
		}

		$where_str = implode( ' AND ', $where_clauses );

		// Prepare the query with placeholders and bind values.
		$query = $db->prepare(
			"SELECT $columns_str FROM " . sanitize_text_field( $table ) . " WHERE $where_str",
			...$values
		);

		// Execute the query and fetch the result.
		$result = $db->get_row( $query );

		// Cache the result for future use.
		$this->set_cache( $table, $cache_key, $result );

		return $result;
	}

	/**
	 * Retrieves multiple rows from the specified table with optional joins, filters, date queries, and pagination.
	 *
	 * @since 1.0.0
	 *
	 * @param string     $table       The name of the main table to retrieve rows from.
	 * @param array|null $query_args  Optional. An associative array representing WHERE conditions, joins, date queries, and meta queries.
	 *                                Includes:
	 *                                - 'join_query': Array of join conditions, each with 'type', 'table', 'on', and optional 'where'.
	 *                                - 'meta_query': Array of meta query conditions.
	 *                                - 'date_query': Array of date query conditions similar to WP_Query.
	 *                                - 'not_exists_query': Array of NOT EXISTS subquery conditions.
	 *                                  Each item can have:
	 *                                  - 'table': (optional) Table for the subquery. Defaults to the main table.
	 *                                  - 'link_column': Column in the subquery that references the main table.
	 *                                  - 'link_to': (optional) Column in the main table. Defaults to 'id'.
	 *                                  - 'where': Associative array of additional WHERE conditions for the subquery.
	 *
	 *                                Examples:
	 *                                Basic Example:
	 *                                [
	 *                                    'status' => 'active',
	 *                                    'join_query' => [
	 *                                        [
	 *                                            'type'  => 'INNER',
	 *                                            'table' => 'other_table',
	 *                                            'on'    => 'main_table.id = other_table.foreign_id',
	 *                                            'where' => [ 'other_table.column' => 'value' ]
	 *                                        ]
	 *                                    ],
	 *                                    'meta_query' => [
	 *                                        'compare' => 'AND', // or 'OR'.
	 *                                        'metatable' => 'meta_table',
	 *                                        'relationship_column' => 'main_id',
	 *                                        [
	 *                                            'key' => 'meta_key',
	 *                                            'value' => 'meta_value',
	 *                                            'compare' => '=', // Optional. Default '='.
	 *                                        ]
	 *                                    ],
	 *                                    'date_query' => [
	 *                                        'column' => 'created_at',
	 *                                        [
	 *                                            'after'     => '2023-01-01',
	 *                                            'before'    => '2023-12-31',
	 *                                            'inclusive' => true,
	 *                                            'compare'   => 'BETWEEN',
	 *                                        ],
	 *                                    ]
	 *                                ]
	 *
	 *                                Date Query Examples:
	 *                                - To get records from the last 7 days:
	 *                                  'date_query' => [
	 *                                      'column' => 'created_at',
	 *                                      [
	 *                                          'after'     => date( 'Y-m-d', strtotime( '-7 days' ) ),
	 *                                          'inclusive' => true,
	 *                                          'compare'   => '>=',
	 *                                      ],
	 *                                  ]
	 *
	 *                                - To get records between two specific dates:
	 *                                  'date_query' => [
	 *                                      'column' => 'created_at',
	 *                                      [
	 *                                          'after'     => '2023-01-01',
	 *                                          'before'    => '2023-12-31',
	 *                                          'inclusive' => true,
	 *                                          'compare'   => 'BETWEEN',
	 *                                      ],
	 *                                  ]
	 *
	 *                                - To get records older than a specific date:
	 *                                  'date_query' => [
	 *                                      'column' => 'created_at',
	 *                                      [
	 *                                          'before'    => '2022-12-31',
	 *                                          'inclusive' => false,
	 *                                          'compare'   => '<',
	 *                                      ],
	 *                                  ]
	 *
	 *                                - To get rewards whose 'expires_at' is in the past (expired rewards):
	 *                                  'date_query' => [
	 *                                      'column' => 'expires_at',
	 *                                      [
	 *                                          'before'    => date( 'Y-m-d H:i:s' ),
	 *                                          'inclusive' => false,
	 *                                          'compare'   => '<',
	 *                                      ],
	 *                                  ]
	 *
	 * @param string     $columns    Optional. Columns to select. Defaults to '*'.
	 * @param int|null   $limit      Optional. Max number of results. Defaults to no limit.
	 * @param int|null   $offset     Optional. Result offset for pagination. Defaults to no offset.
	 *
	 * @return array|null An array of results as objects or null on failure.
	 */
	public function get_results(
		string $table,
		?array $query_args = null,
		string $columns = '*',
		?int $limit = null,
		?int $offset = null
	): ?array {
		$cache_key = $this->generate_cache_key(
			'get_results',
			$table,
			$query_args ?? [],
			$columns,
			$limit ?? 0,
			$offset ?? 0
		);

		$cached_result = $this->get_cache( $cache_key );

		if ( false !== $cached_result ) {
			return $cached_result; // Return cached result.
		}

		$db    = $this->global_config->get_db();
		$query = "SELECT {$columns} FROM {$table}";

		// Build WHERE conditions for the main table.
		$where_conditions = [];

		// Initialize ORDER BY and ORDER clause.
		$order_by_clause = '';
		$order_column    = $query_args['orderby'] ?? null; // The column to order by.
		$order_dir       = strtoupper( $query_args['order'] ?? 'ASC' ); // ASC or DESC, default to ASC.

		// Validate and build the ORDER BY clause.
		if ( $order_column ) {
			$allowed_orders = [ 'ASC', 'DESC' ];
			$order_dir      = in_array( $order_dir, $allowed_orders, true )
				? $order_dir
				: 'ASC';

			// Sanitize column name to prevent SQL injection.
			if ( preg_match( '/^[a-zA-Z0-9_]+$/', $order_column ) ) {
				$order_by_clause = " ORDER BY {$order_column} {$order_dir}";
			}
		}

		// Prevent these values from being used for the where clause.
		unset( $query_args['order'], $query_args['orderby'] );

		// Process join_query if provided.
		$join_query = $query_args['join_query'] ?? null;
		unset( $query_args['join_query'] );

		if ( $join_query ) {
			foreach ( $join_query as $join ) {
				if ( isset( $join['type'], $join['table'], $join['on'] ) ) {
					$query .= " {$join['type']} JOIN {$join['table']} ON {$join['on']}";

					// Process WHERE conditions for the join table, if provided.
					if ( ! empty( $join['where'] ) ) {
						$join_where_conditions = [];
						foreach ( $join['where'] as $join_column => $join_value ) {
							$join_where_conditions[] = $db->prepare( "{$join['table']}.{$join_column} = %s", $join_value );
						}
						// Append join-specific WHERE conditions.
						if ( ! empty( $join_where_conditions ) ) {
							$where_conditions[] = '(' . implode( ' AND ', $join_where_conditions ) . ')';
						}
					}
				}
			}
		}

		// Extract not_exists_query before processing standard WHERE conditions.
		$not_exists_query = $query_args['not_exists_query'] ?? null;
		unset( $query_args['not_exists_query'] );

		// Process standard WHERE conditions for the main table.
		foreach ( $query_args as $column => $value ) {
			if ( 'meta_query' !== $column && 'date_query' !== $column && 'like_query' !== $column ) { // Ignore special query types here.
				if ( is_array( $value ) ) {
					// If the value is an array, create an IN clause.
					$placeholders       = implode( ',', array_fill( 0, count( $value ), '%s' ) );
					$where_conditions[] = $db->prepare( "{$column} IN ({$placeholders})", ...$value );
				} else {
					// Otherwise, create a simple equality condition.
					$where_conditions[] = $db->prepare( "{$column} = %s", $value );
				}
			}
		}

		// Process meta_query if provided.
		$meta_query = $query_args['meta_query'] ?? null;
		if ( $meta_query && isset( $meta_query['metatable'], $meta_query['relationship_column'] ) ) {
			$meta_table          = $meta_query['metatable'];
			$relationship_column = $meta_query['relationship_column'];
			$meta_compare        = $meta_query['compare'] ?? 'AND';

			// Add JOIN with the meta table.
			$query .= " INNER JOIN {$meta_table} ON {$meta_table}.{$relationship_column} = {$table}.id";

			$meta_conditions = [];
			foreach ( $meta_query as $meta ) {
				if ( is_array( $meta ) && isset( $meta['key'], $meta['value'] ) ) {
					$compare_operator  = $meta['compare'] ?? '=';
					$meta_conditions[] = $db->prepare(
						"{$meta_table}.meta_key = %s AND {$meta_table}.meta_value {$compare_operator} %s",
						$meta['key'],
						$meta['value']
					);
				}
			}

			// Combine meta conditions with the specified compare operator ('AND' or 'OR').
			if ( ! empty( $meta_conditions ) ) {
				$where_conditions[] = '(' . implode( " {$meta_compare} ", $meta_conditions ) . ')';
			}
		}

		// Process date_query if provided.
		$date_query = $query_args['date_query'] ?? null;
		if ( $date_query && isset( $date_query['column'] ) ) {
			$date_column     = $date_query['column'];
			$date_conditions = [];

			foreach ( $date_query as $date ) {
				if ( is_array( $date ) ) {
					$after     = $date['after'] ?? null;
					$before    = $date['before'] ?? null;
					$inclusive = $date['inclusive'] ?? false;
					$compare   = $date['compare'] ?? '=';

					if ( $after && $before ) {
						$operator          = $inclusive ? 'BETWEEN' : 'BETWEEN';
						$date_conditions[] = $db->prepare( "{$date_column} {$operator} %s AND %s", $after, $before );
					} elseif ( $after ) {
						$operator          = $inclusive ? '>=' : '>';
						$date_conditions[] = $db->prepare( "{$date_column} {$operator} %s", $after );
					} elseif ( $before ) {
						$operator          = $inclusive ? '<=' : '<';
						$date_conditions[] = $db->prepare( "{$date_column} {$operator} %s", $before );
					}
				}
			}

			if ( ! empty( $date_conditions ) ) {
				$where_conditions[] = '(' . implode( ' AND ', $date_conditions ) . ')';
			}
		}

		// Process like_query if provided.
		$like_query = $query_args['like_query'] ?? null;
		if ( $like_query && isset( $like_query['search_term'], $like_query['columns'] ) ) {
			$search_term = $like_query['search_term'];
			$columns     = $like_query['columns'];
			$compare     = $like_query['compare'] ?? 'OR'; // Default to OR comparison.

			// Escape the search term for LIKE.
			$escaped_search_term = '%' . $db->esc_like( $search_term ) . '%';

			$like_conditions = [];
			foreach ( $columns as $column ) {
				// Handle special CONCAT syntax for combined columns.
				if ( strpos( $column, 'CONCAT(' ) === 0 ) {
					$like_conditions[] = $db->prepare( "{$column} LIKE %s", $escaped_search_term );
				} else {
					// Sanitize column name to prevent SQL injection.
					if ( preg_match( '/^[a-zA-Z0-9_]+$/', $column ) ) {
						$like_conditions[] = $db->prepare( "{$column} LIKE %s", $escaped_search_term );
					}
				}
			}

			if ( ! empty( $like_conditions ) ) {
				$like_clause        = '(' . implode( " {$compare} ", $like_conditions ) . ')';
				$where_conditions[] = $like_clause;
			}
		}

		// Process not_exists_query if provided.
		if ( $not_exists_query ) {
			foreach ( $not_exists_query as $index => $ne ) {
				$ne_table       = $ne['table'] ?? $table;
				$ne_link_column = $ne['link_column'];
				$ne_link_to     = $ne['link_to'] ?? 'id';
				$ne_alias       = "_ne_{$index}";

				$ne_conditions = [
					"{$ne_alias}.{$ne_link_column} = {$table}.{$ne_link_to}",
				];

				if ( ! empty( $ne['where'] ) ) {
					foreach ( $ne['where'] as $ne_col => $ne_val ) {
						$ne_conditions[] = $db->prepare( "{$ne_alias}.{$ne_col} = %s", $ne_val );
					}
				}

				$ne_where           = implode( ' AND ', $ne_conditions );
				$where_conditions[] = "NOT EXISTS ( SELECT 1 FROM {$ne_table} AS {$ne_alias} WHERE {$ne_where} )";
			}
		}

		// Combine all WHERE conditions.
		$where_clause = ! empty( $where_conditions ) ? ' WHERE ' . implode( ' AND ', $where_conditions ) : '';

		// Add LIMIT and OFFSET clauses.
		$limit_str = '';
		if ( null !== $limit && $limit > 0 ) {
			$offset    = ( null !== $offset && $offset >= 0 ) ? $offset : 0;
			$limit_str = sprintf( ' LIMIT %d OFFSET %d', $limit, $offset );
		}

		// Final query with WHERE, JOINs, ORDER BY, and LIMIT/OFFSET.
		$final_query = "{$query}{$where_clause}{$order_by_clause}{$limit_str}";

		$results = $db->get_results( $final_query, 'ARRAY_A' );

		$this->set_cache( $table, $cache_key, $results );

		return $results;
	}

	/**
	 * Counts the number of rows in the specified table, optionally filtered by WHERE clause.
	 *
	 * @since 1.0.0
	 *
	 * @param string     $table Table name.
	 * @param array|null $where Optional. WHERE clause for the rows to count. Defaults to null.
	 *
	 * @return int Number of rows.
	 */
	public function count( string $table, ?array $where = null ): int {
		$column = 'COUNT(*)';

		$results = $this->get_results(
			$table,
			$where,
			$column,
			999999,
			0
		);

		return ( $results[0] ?? [] )[ $column ] ?? 0;
	}

	/**
	 * Get meta value by meta key.
	 *
	 * @since 1.0.0
	 *
	 * @param string $table     The table name to query meta data from.
	 * @param string $column    The column name that represents the object ID (e.g., advocate ID).
	 * @param int    $object_id The ID of the object (e.g., advocate ID).
	 * @param string $meta_key  The key of the meta value to retrieve.
	 * @param bool   $single    Whether to return a single value (true) or an array of values (false).
	 *
	 * @return mixed The meta value, or false if not found.
	 */
	public function get_meta( string $table, string $column, int $object_id, string $meta_key, bool $single = true ) {
		$db = $this->global_config->get_db();

		// Prepare the query with the provided table and column names.
		$query = $db->prepare( "SELECT meta_value FROM $table WHERE $column = %d AND meta_key = %s", $object_id, $meta_key );

		if ( $single ) {
			return $db->get_var( $query );
		}

		return $db->get_col( $query );
	}

	/**
	 * Update or create meta value.
	 *
	 * This method will create a new meta entry if it doesn't exist, or update the value if it does.
	 * If a prev_value is provided, only the entry with the matching value will be updated.
	 * If no prev_value is provided, all entries with the same meta_key will be updated.
	 *
	 * @since 1.0.0
	 *
	 * @param string     $table      The table name where the meta data is stored.
	 * @param string     $column     The column name that represents the object ID (e.g., advocate ID).
	 * @param int        $object_id  The ID of the object (e.g., advocate ID).
	 * @param string     $meta_key   The key of the meta value to update or create.
	 * @param mixed      $meta_value The value to update or create.
	 * @param mixed|null $prev_value Optional. The previous value to match before updating. If null, all values will be updated.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function update_meta( string $table, string $column, int $object_id, string $meta_key, $meta_value, $prev_value = null ): bool {
		$db = $this->global_config->get_db();

		// Prepare the conditions for updating.
		$conditions = [
			$column    => $object_id,
			'meta_key' => $meta_key,
		];
		$formats    = [ '%d', '%s' ];

		// If prev_value is provided, only update the specific entry that matches the previous value.
		if ( null !== $prev_value ) {
			$conditions['meta_value'] = maybe_serialize( $prev_value );
			$formats[]                = '%s';
		}

		// Update the meta value in the database.
		$result = $db->update(
			$table,
			[ 'meta_value' => maybe_serialize( $meta_value ) ],
			$conditions,
			[ '%s' ],
			$formats
		);

		// If no rows were affected, we need to determine if we should insert.
		// We should only insert if no previous value was specified, which implies we are adding a new meta.
		if ( ( $result === false || $db->rows_affected === 0 ) && null === $prev_value ) {
			return (bool) $db->insert(
				$table,
				[
					$column      => $object_id,
					'meta_key'   => $meta_key,
					'meta_value' => maybe_serialize( $meta_value ),
				],
				[ '%d', '%s', '%s' ]
			);
		}

		return (bool) $result;
	}

	/**
	 * Delete meta value.
	 *
	 * This method deletes a meta value for the specified object. If a meta value is provided,
	 * it will delete only the entry that matches both the meta_key and meta_value. If no meta
	 * value is provided, it will delete all entries for that meta key.
	 *
	 * @since 1.0.0
	 *
	 * @param string     $table     The table name where the meta data is stored.
	 * @param string     $column    The column name that represents the object ID (e.g., advocate ID).
	 * @param int        $object_id The ID of the object (e.g., advocate ID).
	 * @param string     $meta_key  The key of the meta value to delete.
	 * @param mixed|null $meta_value Optional. If provided, only delete entries with this meta value.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function delete_meta( string $table, string $column, int $object_id, string $meta_key, $meta_value = null ): bool {
		$db = $this->global_config->get_db();

		// Build the condition for deletion.
		$conditions = [
			$column    => $object_id,
			'meta_key' => $meta_key,
		];
		$formats    = [ '%d', '%s' ];

		// If a specific meta value is provided, add it to the condition.
		if ( null !== $meta_value ) {
			$conditions['meta_value'] = maybe_serialize( $meta_value );
			$formats[]                = '%s';
		}

		// Perform the deletion based on the constructed condition.
		return (bool) $db->delete( $table, $conditions, $formats );
	}

	/**
	 * Execute a raw SQL query with caching.
	 *
	 * Use this for complex queries that existing methods can't handle.
	 * Supports custom cache groups for different query types (e.g., reports).
	 *
	 * @since 1.1.0
	 *
	 * @param string $sql         SQL query with %s, %d placeholders.
	 * @param array  $params      Optional. Parameters to bind to query. Default empty array.
	 * @param bool   $cache       Optional. Whether to cache results. Default true.
	 * @param string $cache_group Optional. Cache group. Default CACHE_GROUP.
	 *
	 * @return array Array of results (ARRAY_A format) or empty array on failure.
	 */
	public function query( string $sql, array $params = [], bool $cache = true, string $cache_group = self::CACHE_GROUP ): array {
		$db = $this->global_config->get_db();

		// Generate cache key if caching is enabled.
		if ( $cache ) {
			$cache_key = 'custom_query_' . md5( $sql . wp_json_encode( $params ) );
			$cached    = wp_cache_get( $cache_key, $cache_group );

			if ( false !== $cached ) {
				return $cached;
			}
		}

		// Prepare and execute query.
		$prepared_query = empty( $params ) ? $sql : $db->prepare( $sql, ...$params );
		$results        = $db->get_results( $prepared_query, 'ARRAY_A' );

		// Handle query errors.
		if ( null === $results ) {
			// Log error for debugging.
			if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
				rewardswp_log_error(
					'Database query error: {message}',
					[
						'message' => $db->last_error,
					]
				);
			}
			return [];
		}

		// Cache results if enabled.
		if ( $cache ) {
			wp_cache_set( $cache_key, $results, $cache_group, self::CACHE_EXPIRATION );
		}

		return $results;
	}

	/**
	 * Get a single value from a query.
	 *
	 * Useful for COUNT, SUM, MAX, etc. queries.
	 * Supports custom cache groups for different query types (e.g., reports).
	 *
	 * @since 1.1.0
	 *
	 * @param string $sql         SQL query with %s, %d placeholders.
	 * @param array  $params      Optional. Parameters to bind to query. Default empty array.
	 * @param bool   $cache       Optional. Whether to cache results. Default true.
	 * @param string $cache_group Optional. Cache group. Default CACHE_GROUP.
	 *
	 * @return mixed|null Single value or null if not found.
	 */
	public function get_var( string $sql, array $params = [], bool $cache = true, string $cache_group = self::CACHE_GROUP ) {
		$db = $this->global_config->get_db();

		// Generate cache key if caching is enabled.
		if ( $cache ) {
			$cache_key = 'custom_var_' . md5( $sql . wp_json_encode( $params ) );
			$cached    = wp_cache_get( $cache_key, $cache_group );

			if ( false !== $cached ) {
				return $cached;
			}
		}

		// Prepare and execute query.
		$prepared_query = empty( $params ) ? $sql : $db->prepare( $sql, ...$params );
		$result         = $db->get_var( $prepared_query );

		// Cache result if enabled.
		if ( $cache && null !== $result ) {
			wp_cache_set( $cache_key, $result, $cache_group, self::CACHE_EXPIRATION );
		}

		return $result;
	}

	/**
	 * Get a column from a query.
	 *
	 * Useful for getting arrays of IDs or single column values.
	 * Supports custom cache groups for different query types (e.g., reports).
	 *
	 * @since 1.1.0
	 *
	 * @param string $sql         SQL query with %s, %d placeholders.
	 * @param array  $params      Optional. Parameters to bind to query. Default empty array.
	 * @param bool   $cache       Optional. Whether to cache results. Default true.
	 * @param string $cache_group Optional. Cache group. Default CACHE_GROUP.
	 *
	 * @return array Array of column values or empty array on failure.
	 */
	public function get_col( string $sql, array $params = [], bool $cache = true, string $cache_group = self::CACHE_GROUP ): array {
		$db = $this->global_config->get_db();

		// Generate cache key if caching is enabled.
		if ( $cache ) {
			$cache_key = 'custom_col_' . md5( $sql . wp_json_encode( $params ) );
			$cached    = wp_cache_get( $cache_key, $cache_group );

			if ( false !== $cached ) {
				return $cached;
			}
		}

		// Prepare and execute query.
		$prepared_query = empty( $params ) ? $sql : $db->prepare( $sql, ...$params );
		$results        = $db->get_col( $prepared_query );

		// Handle query errors.
		if ( null === $results ) {
			// Log error for debugging.
			if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
				rewardswp_log_error(
					'Database column query error: {message}',
					[
						'message' => $db->last_error,
					]
				);
			}
			return [];
		}

		// Cache results if enabled.
		if ( $cache ) {
			wp_cache_set( $cache_key, $results, $cache_group, self::CACHE_EXPIRATION );
		}

		return $results;
	}
}
