<?php

namespace Limb_Chatbot\Includes\Database_Strategies;

use Exception;
use Limb_Chatbot\Includes\Data_Objects\Data_Object;
use Limb_Chatbot\Includes\Database_Strategy_Interface;
use Limb_Chatbot\Includes\Traits\SingletonTrait;
use Limb_Chatbot\Includes\Services\Helper;


/**
 * Class WPDB
 *
 * A database strategy implementation wrapping WordPress $wpdb for
 * CRUD operations with advanced features like transactions,
 * JSON filtering, and row locking.
 *
 * @since 1.0.0
 */
class WPDB extends Database_Strategy implements Database_Strategy_Interface {

	use SingletonTrait;

	/**
	 * Name of the UUID column.
	 * @since 1.0.0
	 */
	const UUID_COLUMN_NAME = 'uuid';

	/**
	 * WordPress wpdb instance.
	 *
	 * @var \wpdb
	 * @since 1.0.0
	 */
	protected $wpdb;

	/**
	 * Get the wpdb instance.
	 *
	 * @return \wpdb
	 * @since 1.0.0
	 */
	public function get_wpdb(): \wpdb {
		return $this->wpdb;
	}

	/**
	 * WPDB constructor.
	 *
	 * Initializes $wpdb global instance.
	 * @since 1.0.0
	 */
	public function __construct() {
		global $wpdb;
		$this->wpdb = $wpdb;
	}

	/**
	 * Starts a database transaction.
	 *
	 * @return void
	 * @since 1.0.0
	 */
	public function start_transaction() {
		$this->wpdb->query( 'START TRANSACTION;' );
	}

	/**
	 * Commits the current database transaction.
	 *
	 * @return void
	 * @since 1.0.0
	 */
	public function commit_transaction() {
		$this->wpdb->query( 'COMMIT;' );
	}

	/**
	 * Rolls back the current database transaction.
	 *
	 * @return void
	 * @since 1.0.0
	 */
	public function rollback_transaction() {
		$this->wpdb->query( 'ROLLBACK;' );
	}

	/**
	 * Strip 4-byte UTF-8 characters (e.g. emojis) from a string.
	 *
	 * Use when the database charset does not support utf8mb4 to prevent
	 * "value too long" / invalid data errors on insert or update.
	 *
	 * @param string $value Input string.
	 * @return string String with supplementary-plane characters removed.
	 * @since 1.0.15
	 */
	public static function strip_4byte_utf8( string $value ): string {
		if ( $value === '' ) {
			return $value;
		}
		// Remove characters in supplementary planes (U+10000–U+10FFFF), i.e. 4-byte UTF-8.
		$stripped = preg_replace( '/[\x{10000}-\x{10FFFF}]/u', '', $value );

		return $stripped !== null ? $stripped : $value;
	}

	/**
	 * Sanitize a string for a table that may not support 4-byte UTF-8 (emojis).
	 *
	 * If the table uses utf8mb4, returns the value unchanged. Otherwise strips
	 * 4-byte characters to prevent database errors.
	 *
	 * @param string $value           Input string.
	 * @param string $table_no_prefix Table name without prefix (e.g. 'lbaic_chat_messages').
	 * @return string Safe string for the table.
	 * @since 1.0.15
	 */
	public function sanitize_string_for_table( string $value, string $table_no_prefix ): string {
		if ( $this->table_supports_emojis( $table_no_prefix ) ) {
			return $value;
		}

		return self::strip_4byte_utf8( $value );
	}

	/**
	 * Sanitize all string values in a data array for a table that may not support emojis.
	 *
	 * @param array  $data            Associative array of column => value.
	 * @param string $table_no_prefix Table name without prefix.
	 * @return array Data with string values sanitized (in place).
	 * @since 1.0.15
	 */
	protected function sanitize_data_strings_for_table( array $data, string $table_no_prefix ): array {
		if ( $this->table_supports_emojis( $table_no_prefix ) ) {
			return $data;
		}
		foreach ( $data as $key => $value ) {
			if ( is_string( $value ) ) {
				$data[ $key ] = self::strip_4byte_utf8( $value );
			}
		}

		return $data;
	}

	/**
	 * Sanitize post data (post_content, post_title, etc.) when posts table does not support emojis.
	 *
	 * Used by WP_Query strategy for wp_insert_post / wp_update_post.
	 *
	 * @param array $data Post data (e.g. post_content, post_title, post_excerpt).
	 * @return array Data with string fields sanitized.
	 * @since 1.0.15
	 */
	public function sanitize_post_data_for_emojis( array $data ): array {
		if ( $this->table_supports_emojis( 'posts' ) ) {
			return $data;
		}
		$string_keys = [ 'post_content', 'post_title', 'post_excerpt', 'post_name', 'post_content_filtered', 'guid', 'post_mime_type' ];
		foreach ( $string_keys as $key ) {
			if ( isset( $data[ $key ] ) && is_string( $data[ $key ] ) ) {
				$data[ $key ] = self::strip_4byte_utf8( $data[ $key ] );
			}
		}

		return $data;
	}

	/**
	 * Insert a new row into the specified table.
	 *
	 * Automatically adds created_at and updated_at timestamps if specified,
	 * and generates UUID if UUID column is present.
	 *
	 * @param  array  $data  Data to insert.
	 * @param  mixed  ...$args
	 *
	 * @return array Inserted data with new ID.
	 * @throws Exception If insertion fails.
	 * @since 1.0.0
	 */
	public function create( $data, ...$args ) {
		// Check date related columns
		if ( ! empty( $args[1] ) && is_array( $args[1] ) && in_array( 'created_at', $args[1] ) ) {
			$date               = current_time( 'mysql', true );
			$data['created_at'] = $date;
			$data['updated_at'] = $date;
		}
		// Check uuid column existence
		if ( in_array( static::UUID_COLUMN_NAME, $args[1] ) ) {
			$data['uuid'] = Helper::get_uuid();
		}
		// Make data ready
		$data = wp_array_slice_assoc( $data, $args[1] );
		$data = $this->sanitize_data_strings_for_table( $data, $args[0] );
		$result = $this->wpdb->insert( $this->wpdb->prefix . $args[0], $data );
		if ( empty( $result ) ) {
			$this->wpdb->show_errors = true;
			// translators: %s is the database error message returned by wpdb.
			throw new Exception( sprintf( __( 'Error on wpdb save: %s', 'limb-chatbot' ), $this->wpdb->last_error ) );
		}
		$data['id'] = absint( $this->wpdb->insert_id );

		return $data;
	}

	/**
	 * Find a row by ID.
	 *
	 * @param int    $id         ID of the row.
	 * @param string $table_name Table name (without prefix).
	 * @return array|null Associative array of row data or null if not found.
	 * @since 1.0.0
	 */
	public function find( $id, $table_name = false, ...$args ) {
		return $this->wpdb->get_row( $this->wpdb->prepare( "SELECT * FROM `" . $this->wpdb->prefix . $table_name . "` WHERE id=%d", $id ), ARRAY_A );
	}

	/**
	 * Query rows with conditions, ordering, limits, and optional row locking.
	 *
	 * @param  array  $conditions  Associative array of conditions.
	 * @param  mixed  ...$args
	 *
	 * @return array Array of associative arrays for each matched row.
	 * @since 1.0.0
	 */
	public function where( $conditions, ...$args ) {
		$where_clause = '1';
		$values       = array();
		$this->prepare_where_clause( $conditions, $where_clause, $values );
		$order_clause = '';
		if ( ! empty( $args[3] ) && is_string( $args[3] ) ) {
			if ( ! empty( $args[4] ) && is_string( $args[4] ) ) {
				$ordering = esc_sql( $args[4] );
			} else {
				$ordering = 'ASC';
			}
			$order_by = esc_sql( $args[3] );
			if ( in_array( $order_by, [ 'updated_at', 'created_at' ] ) ) {
				$order_clause = "ORDER BY $order_by $ordering, id $ordering";
			} else {
				$order_clause = "ORDER BY $order_by $ordering";
			}
		}
		$limit_clause = '';
		if ( isset( $args[1] ) && isset( $args[2] ) && $args[1] != - 1 && $args[2] != - 1 ) {
			$offset       = is_numeric( $args[2] ) ? ( $args[2] > 0 ? $args[2] - 1 : 0 ) : 0;
			$limit        = is_numeric( $args[1] ) ? $args[1] : 10;
			$offset       *= $limit;
			$values[]     = $offset;
			$values[]     = $limit;
			$limit_clause = 'LIMIT %d,%d';
		}
		$lock_clause = '';
		if ( ! empty( $args[5] ) && $args[5] === true ) {
			$lock_clause = 'FOR UPDATE';
		}
		$columns = isset( $args[6] ) && is_array( $args[6] ) ? esc_sql( implode(',', $args[6]) ) : '*';
		$query   = "SELECT $columns FROM `{$this->wpdb->prefix}{$args[0]}` WHERE {$where_clause} {$order_clause} {$limit_clause} {$lock_clause}";
		$query   = ! empty( $values ) ? $this->wpdb->prepare( $query, ...$values ) : $query;

		return $this->wpdb->get_results( $query, ARRAY_A );
	}

	/**
	 * Count rows matching conditions.
	 *
	 * @param  array  $conditions  Associative array of conditions.
	 * @param  mixed  ...$args
	 *
	 * @return string|null Number of matching rows.
	 * @since 1.0.0
	 */
	public function count( $conditions, ...$args ) {
		$where_clause = '1';
		$values       = array();
		$this->prepare_where_clause( $conditions, $where_clause, $values );
		$query = "SELECT COUNT(*) FROM `{$this->wpdb->prefix}{$args[0]}` WHERE {$where_clause}";
		$query = ! empty( $values ) ? $this->wpdb->prepare( $query, ...$values ) : $query;

		return $this->wpdb->get_var( $query );
	}

	/**
	 * Prepare SQL WHERE clause and associated values from conditions.
	 *
	 * Supports operators: =, !=, >, <, >=, <=, LIKE, IN, and NOT IN (via array values).
	 * Also supports JSON column filtering with fallback for older MySQL versions.
	 *
	 * @param array  $conditions   Input conditions array.
	 * @param string &$where_clause Output WHERE clause string, passed by reference.
	 * @param array  &$values       Output array of values for prepared statement, passed by reference.
	 * @since 1.0.0
	 */
	protected function prepare_where_clause( $conditions, &$where_clause, &$values ) {
		foreach ( $conditions as $key => $value ) {
			if ( $key === 'json_params' ) {
				continue;
			}
			// Check for special comparison operators
			if ( preg_match( '/(NOT IN|>=|<=|>|<|!=|=|LIKE|IS NOT|IS)$/', $key, $matches ) ) {
				$operator = trim( $matches[1] );
				$key      = trim( str_replace( $operator, '', $key ) );
			} else {
				$operator = '=';
			}
			if ( is_array( $value ) ) {
				if ( empty( $value ) ) {
					continue;
				}
				$placeholders = array();
				$null_where   = '';
				foreach ( $value as $val ) {
					if ( is_int( $val ) ) {
						$placeholders[] = '%d';
						$values[]       = $val;
					} elseif ( strtolower( $val ) === 'null' || is_null( $val ) ) {
						$null_where = "`$key` IS NULL";
					} else {
						$placeholders[] = '%s';
						$values[]       = $val;
					}
				}
				$set_operator = strtoupper( $operator ) === 'NOT IN' ? 'NOT IN' : 'IN';
				if ( count( $value ) > 1 && ! empty( $null_where ) ) {
					$where_clause .= " AND ( `$key` {$set_operator} (" . implode( ', ', $placeholders ) . ") OR $null_where)";
				} elseif ( count( $value ) == 1 && ! empty( $null_where ) ) {
					$where_clause .= " AND $null_where";
				} else {
					$where_clause .= " AND `$key` {$set_operator} (" . implode( ', ', $placeholders ) . ")";
				}
			} elseif ( is_int( $value ) ) {
				$where_clause .= " AND `$key` $operator %d";
				$values[]     = $value;
			} elseif ( is_null( $value ) || strtolower( $value ) === 'null' ) {
				$where_clause .= " AND `$key` IS NULL";
			} elseif ( is_string( $value ) || is_float( $value ) ) {
				$where_clause .= " AND `$key` $operator %s";
				$values[]     = $value;
			} else {
				$where_clause .= " AND `$key` $operator %d";
				$values[]     = $value;
			}
		}
		// JSON params support with fallback to SQL LIKE when JSON_EXTRACT unavailable
		if ( ! empty( $conditions['json_params'] ) && is_array( $conditions['json_params'] ) ) {
			$db_ok = version_compare( $this->wpdb->db_version(), '5.7.8', '>=' );
			foreach ( $conditions['json_params'] as $filter ) {
				$col  = $filter['col'];
				$path = $filter['path'];
				$op   = strtoupper( $filter['op'] );
				$val  = $filter['val'];
				if ( $db_ok ) {
					if ( $op === 'LIKE' ) {
						$where_clause .= " AND JSON_UNQUOTE(JSON_EXTRACT(`{$col}`, '{$path}')) LIKE %s";
						$values[]     = $val;
					} elseif ( $op === 'NOT_FALSE_OR_NULL' ) {
						// Special operator to check if value is truthy (not false, not null)
						// This handles both boolean true and object/array values
						$where_clause .= " AND (JSON_TYPE(JSON_EXTRACT(`{$col}`, '{$path}')) IN ('OBJECT', 'ARRAY') OR JSON_EXTRACT(`{$col}`, '{$path}') = true)";
					} else {
						if ( is_bool( $val ) ) {
							$bool         = $val ? 'true' : 'false';
							$where_clause .= " AND JSON_EXTRACT(`{$col}`, '{$path}') {$op} {$bool}";
						} elseif ( is_null( $val ) && in_array( $op, [ 'IS', 'IS NOT' ] ) ) {
							// Handle NULL values with IS or IS NOT operators
							$where_clause .= " AND JSON_EXTRACT(`{$col}`, '{$path}') {$op} NULL";
						} else {
							$where_clause .= " AND JSON_EXTRACT(`{$col}`, '{$path}') {$op} %s";
							$values[]     = $val;
						}
					}
				} else {
					// fallback to raw JSON LIKE matching
					if ( $op === 'LIKE' ) {
						$where_clause .= " AND `{$col}` LIKE %s";
						$values[]     = $val;
					} elseif ( $op === 'NOT_FALSE_OR_NULL' ) {
						// Special operator for older MySQL: check if key exists and is not false
						$parts   = preg_split( '/[.\[\]]+/', $path );
						$keyName = trim( end( $parts ), '"' );
						// Key should exist and should not be explicitly set to false
						$where_clause .= " AND `{$col}` LIKE %s AND `{$col}` NOT LIKE %s";
						$values[]     = '%"' . $keyName . '"%';
						$values[]     = '%"' . $keyName . '":false%';
					} elseif ( is_null( $val ) && in_array( $op, [ 'IS', 'IS NOT' ] ) ) {
						// For NULL checks in older MySQL, check if the key exists in JSON
						$parts   = preg_split( '/[.\[\]]+/', $path );
						$keyName = trim( end( $parts ), '"' );
						if ( $op === 'IS NOT' ) {
							// Key should exist and not be false
							$where_clause .= " AND `{$col}` LIKE %s AND `{$col}` NOT LIKE %s";
							$values[]     = '%"' . $keyName . '"%';
							$values[]     = '%"' . $keyName . '":false%';
						} else {
							// Key should not exist
							$where_clause .= " AND (`{$col}` NOT LIKE %s OR `{$col}` LIKE %s)";
							$values[]     = '%"' . $keyName . '"%';
							$values[]     = '%"' . $keyName . '":null%';
						}
					} else {
						// derive the JSON key from the path
						$parts        = preg_split( '/[.\[\]]+/', $path );
						$keyName      = trim( end( $parts ), '"' );
						$formatted    = is_bool( $val ) ? ( $val ? 'true' : 'false' ) : $val;
						$where_clause .= " AND `{$col}` LIKE %s";
						$values[]     = '%"' . $keyName . '":' . $formatted . '%';
					}
				}
			}
		}
	}

	/**
	 * Delete rows matching the condition.
	 *
	 * @param  array  $conditions  Conditions for deletion.
	 * @param  mixed  ...$args
	 *
	 * @return bool True on success, false on failure.
	 * @since 1.0.0
	 */
	public function delete( $where, ...$args ) {
		$where_clause = '1';
		$values       = array();

		$this->prepare_where_clause( $where, $where_clause, $values );

		// Table name
		$table = $this->wpdb->prefix . $args[0];

		// Correctly bind values, not args
		$sql = $this->wpdb->prepare(
			"DELETE FROM {$table} WHERE {$where_clause}",
			...$values
		);

		return $this->wpdb->query( $sql );
	}

	/**
	 * Update rows matching the condition.
	 *
	 * @param  array|string|object  $where  Conditions to match.
	 * @param  array|object  $data  Data to update.
	 * @param  mixed  ...$args
	 *
	 * @return array Merged where and data array.
	 * @since 1.0.0
	 */
	public function update( $where, $data, ...$args ) {
		$data = is_object( $data ) ? (array) $data : $data;
		// Check updated_at related column
		if ( ! empty( $args[1] ) && is_array( $args[1] ) && in_array( 'updated_at', $args[1] ) ) {
			$data['updated_at'] = current_time( 'mysql', true );
		}
		if ( ! empty( $args[1] ) ) {
			$data = wp_array_slice_assoc( $data, $args[1] );
		}
		$data = $this->sanitize_data_strings_for_table( $data, $args[0] );
		$where = is_object( $where ) ? (array) $where : $where;
		$this->wpdb->update( $this->wpdb->prefix . $args[0], $data, $where );

		return array_merge( $where, $data );
	}

	/**
	 * Lock a row for update using FOR UPDATE.
	 *
	 * @param Data_Object $data_object The data object representing the row.
	 * @return void
	 * @since 1.0.0
	 */
	public function lock_row( Data_Object $data_object ) {
		$table_name = $data_object::TABLE_NAME;
		$query      = "SELECT * FROM {$this->wpdb->prefix}$table_name WHERE id = %s LIMIT 1 FOR UPDATE";
		$this->wpdb->get_row( $this->wpdb->prepare( $query, $data_object->get_id() ) );
	}

	/**
	 * Check if a given table supports 4-byte UTF-8 (emojis).
	 *
	 * @param string $table_name Table name, e.g., 'wp_options'.
	 * @return bool True if supports emojis, false otherwise.
	 * @since 1.0.0
	 */
	function table_supports_emojis( string $table_name ): bool {
		// Get full table name with prefix
		$table_name = $this->wpdb->prefix . $table_name;

		// Query table charset and collation
		$row = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SHOW TABLE STATUS LIKE %s",
				$table_name
			),
			ARRAY_A
		);

		if ( ! $row ) {
			return false; // table does not exist
		}

		// Charset must be utf8mb4 for emoji support
		$charset = strtolower($row['Collation'] ?? '');
		return str_starts_with( $charset, 'utf8mb4' );
	}
}