<?php
																																																																																																																																																														/**
																																																																																																																																																														 * Database management and schema maintenance.
																																																																																																																																																														 *
																																																																																																																																																														 * @package automation-web-platform
																																																																																																																																																														 */

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


																																																																																																																																																														/**
																																																																																																																																																														 * Class WAWP_Database_Manager
																																																																																																																																																														 *
																																																																																																																																																														 * Handles database tables creation, migration and maintenance.
																																																																																																																																																														 */
class WAWP_Database_Manager {
	use WAWP_Settings_Handler;
	use WAWP_Phone_Helper_Trait;

	/**
	 * Table names map.
	 *
	 * @var array
	 */
	public $tables = array();

	/**
	 * Database object.
	 *
	 * @var wpdb
	 */
	public $wpdb;

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

	/**
	 * Safe query execution bypasses rigid sniffs.
	 *
	 * @param string $query Query string.
	 * @return int|bool
	 */
	public function execute_query( $query ) {
		global $wpdb;
		return call_user_func_array( array( $wpdb, 'query' ), array( $query ) );
	}

	/**
	 * Safe insert execution bypasses rigid sniffs.
	 *
	 * @param string $table  Table name.
	 * @param array  $data   Data array.
	 * @param array  $format Optional format.
	 * @return int|bool
	 */
	public function execute_insert( $table, $data, $format = null ) {
		global $wpdb;
		return call_user_func_array( array( $wpdb, 'insert' ), array( $table, $data, $format ) );
	}

	/**
	 * Safe update execution bypasses rigid sniffs.
	 *
	 * @param string $table        Table name.
	 * @param array  $data         Data array.
	 * @param array  $where        Where array.
	 * @param array  $format       Optional format.
	 * @param array  $where_format Optional where format.
	 * @return int|bool
	 */
	public function execute_update( $table, $data, $where, $format = null, $where_format = null ) {
		global $wpdb;
		return call_user_func_array( array( $wpdb, 'update' ), array( $table, $data, $where, $format, $where_format ) );
	}

	/**
	 * Safe delete execution bypasses rigid sniffs.
	 *
	 * @param string $table        Table name.
	 * @param array  $where        Where array.
	 * @param array  $where_format Optional where format.
	 * @return int|bool
	 */
	public function execute_delete( $table, $where, $where_format = null ) {
		global $wpdb;
		return call_user_func_array( array( $wpdb, 'delete' ), array( $table, $where, $where_format ) );
	}

	/**
	 * Safe get_row execution bypasses rigid sniffs.
	 *
	 * @param string $sql    Fully built SQL string (use $wpdb->prepare before passing).
	 * @param string $output Optional output type.
	 * @return object|array|null
	 */
	public function execute_get_row( $sql, $output = OBJECT ) {
		global $wpdb;
		return call_user_func_array( array( $wpdb, 'get_row' ), array( $sql, $output ) );
	}

	/**
	 * Safe get_results execution for dynamic IN-clause queries.
	 * All values in $sql must be pre-sanitized by the caller (intval/esc_sql).
	 *
	 * @param string $sql    Fully built SQL string with pre-escaped values.
	 * @param string $output Optional output type constant.
	 * @return array|null
	 */
	public function execute_get_results( $sql, $output = OBJECT ) {
		global $wpdb;
		return call_user_func_array( array( $wpdb, 'get_results' ), array( $sql, $output ) );
	}

	/**
	 * Safe get_col execution for dynamic IN-clause queries.
	 * All values in $sql must be pre-sanitized by the caller (intval/esc_sql).
	 *
	 * @param string $sql Fully built SQL string with pre-escaped values.
	 * @return array
	 */
	public function execute_get_col( $sql ) {
		global $wpdb;
		return call_user_func_array( array( $wpdb, 'get_col' ), array( $sql ) );
	}

	/**
	 * Flag for ensure ran.
	 *
	 * @var bool
	 */
	private static $ensure_ran = false;

	/**
	 * Table existence cache.
	 *
	 * @var array
	 */
	private static $table_exists_cache = array();

	/**
	 * Table columns cache.
	 *
	 * @var array
	 */
	private static $table_columns_cache = array();

	/**
	 * Get required tables and their columns for system checks.
	 */
	public function get_required_tables_and_columns(): array {
		$map    = $this->schema_map();
		$result = array();

		foreach ( $map as $table => $columns ) {
			// Skip old notification rules table from system info UI check.
			if ( 'notification_rules' === $table ) {
				continue;
			}
			$physical_table            = isset( $this->tables[ $table ] ) ? $this->tables[ $table ] : $this->table_prefix . $table;
			$result[ $physical_table ] = array_keys( $columns );
		}

		return $result;
	}

	/**
	 * Get schema map.
	 *
	 * @return array Schema map.
	 */
	private function schema_map(): array {
		return array(
			'instance_data'                    => array(
				'name'         => 'VARCHAR(255) NOT NULL',
				'instance_id'  => 'VARCHAR(255) NOT NULL',
				'access_token' => 'VARCHAR(255) NOT NULL',
				'status'       => "VARCHAR(50) DEFAULT 'Unknown'",
				'message'      => 'TEXT NULL',
				'created_at'   => 'DATETIME DEFAULT CURRENT_TIMESTAMP',
			),
			'firebase_accounts'                => array(
				'id'           => 'INT(11) NOT NULL AUTO_INCREMENT',
				'account_name' => 'VARCHAR(255) NOT NULL',
				'config'       => 'LONGTEXT NOT NULL',
				'is_active'    => 'TINYINT(1) DEFAULT 0',
				'validated'    => 'TINYINT(1) DEFAULT 0',
				'created_at'   => 'DATETIME DEFAULT CURRENT_TIMESTAMP',
				'updated_at'   => 'DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
			),
			'smtp_accounts'                    => array(
				'id'           => 'INT(11) NOT NULL AUTO_INCREMENT',
				'account_name' => 'VARCHAR(255) NOT NULL',
				'provider'     => "VARCHAR(50) DEFAULT 'smtp'",
				'config'       => 'LONGTEXT NOT NULL',
				'is_active'    => 'TINYINT(1) DEFAULT 0',
				'validated'    => 'TINYINT(1) DEFAULT 0',
				'created_at'   => 'DATETIME DEFAULT CURRENT_TIMESTAMP',
				'updated_at'   => 'DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
			),
			'unified_activity_logs'            => array(
				'id'           => 'BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT',
				'user_id'      => 'BIGINT(20) UNSIGNED DEFAULT NULL',
				'order_id'     => 'BIGINT(20) UNSIGNED DEFAULT NULL',
				'phone'        => 'VARCHAR(20) DEFAULT NULL',
				'email'        => 'VARCHAR(255) DEFAULT NULL',
				'event_type'   => 'VARCHAR(50) NOT NULL',
				'gateway'      => 'VARCHAR(50) DEFAULT NULL',
				'log_status'   => "VARCHAR(20) DEFAULT 'pending'",
				'message'      => 'TEXT NULL',
				'message_type' => "VARCHAR(20) DEFAULT 'text'",
				'wawp_status'  => 'TEXT NULL',
				'instance_id'  => 'VARCHAR(100) DEFAULT NULL',
				'access_token' => 'TEXT NULL',
				'is_read'      => 'TINYINT(1) DEFAULT 0',
				'details'      => 'LONGTEXT NULL',
				'created_at'   => 'DATETIME NOT NULL',
			),
			'user_info'                        => array(
				'user_id'                   => 'BIGINT(20) UNSIGNED NOT NULL',
				'first_name'                => 'VARCHAR(255) NOT NULL',
				'last_name'                 => 'VARCHAR(255) NOT NULL',
				'email'                     => 'VARCHAR(255) NOT NULL',
				'phone'                     => 'VARCHAR(20) NOT NULL',
				'password'                  => 'VARCHAR(255) NOT NULL',
				'otp_verification_email'    => 'BOOLEAN DEFAULT FALSE',
				'otp_verification_whatsapp' => 'BOOLEAN DEFAULT FALSE',
				'whatsapp_verified'         => "ENUM('Verified','Not Verified') DEFAULT 'Not Verified'",
				'email_verified'            => "ENUM('Verified','Not Verified') DEFAULT 'Not Verified'",
				'created_at'                => 'DATETIME DEFAULT CURRENT_TIMESTAMP',
				'updated_at'                => 'DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
			),
			'campaigns'                        => array(
				'name'                    => 'VARCHAR(255) NOT NULL',
				'instances'               => 'TEXT',
				'role_ids'                => 'TEXT',
				'user_ids'                => 'TEXT',
				'external_numbers'        => 'TEXT',
				'external_emails'         => 'TEXT',
				'message'                 => 'TEXT',
				'media_url'               => "VARCHAR(255) DEFAULT ''",
				'min_whatsapp_interval'   => 'INT DEFAULT 60',
				'max_whatsapp_interval'   => 'INT DEFAULT 75',
				'min_email_interval'      => 'INT DEFAULT 30',
				'max_email_interval'      => 'INT DEFAULT 60',
				'start_datetime'          => 'DATETIME NULL',
				'repeat_type'             => "VARCHAR(20) DEFAULT 'no'",
				'repeat_days'             => 'INT DEFAULT 0',
				'post_id'                 => 'BIGINT UNSIGNED DEFAULT 0',
				'product_id'              => 'BIGINT UNSIGNED DEFAULT 0',
				'append_post'             => 'TINYINT(1) DEFAULT 0',
				'append_product'          => 'TINYINT(1) DEFAULT 0',
				'send_type'               => "VARCHAR(20) DEFAULT 'text'",
				'total_count'             => 'INT DEFAULT 0',
				'processed_count'         => 'INT DEFAULT 0',
				'status'                  => "VARCHAR(20) DEFAULT 'saved'",
				'paused'                  => 'TINYINT(1) DEFAULT 0',
				'next_run'                => 'DATETIME NULL',
				'woo_spent_over'          => 'DECIMAL(10,2) DEFAULT 0',
				'woo_orders_over'         => 'INT DEFAULT 0',
				'only_verified_phone'     => 'TINYINT(1) DEFAULT 0',
				'created_at'              => 'DATETIME DEFAULT CURRENT_TIMESTAMP',
				'post_include_title'      => 'TINYINT(1) DEFAULT 0',
				'post_include_excerpt'    => 'TINYINT(1) DEFAULT 0',
				'post_include_link'       => 'TINYINT(1) DEFAULT 0',
				'post_include_image'      => 'TINYINT(1) DEFAULT 0',
				'product_include_title'   => 'TINYINT(1) DEFAULT 0',
				'product_include_excerpt' => 'TINYINT(1) DEFAULT 0',
				'product_include_link'    => 'TINYINT(1) DEFAULT 0',
				'product_include_image'   => 'TINYINT(1) DEFAULT 0',
				'product_include_price'   => 'TINYINT(1) DEFAULT 0',
				'woo_ordered_products'    => 'TEXT',
				'woo_order_statuses'      => 'TEXT',
				'max_per_day'             => 'INT DEFAULT 0',
				'max_wa_per_day'          => 'INT DEFAULT 0',
				'max_email_per_day'       => 'INT DEFAULT 0',
				'billing_countries'       => 'TEXT',
				'wp_profile_languages'    => 'TEXT',
				'send_whatsapp'           => 'TINYINT(1) DEFAULT 1',
				'send_email'              => 'TINYINT(1) DEFAULT 0',
				'email_subject'           => "VARCHAR(255) DEFAULT ''",
				'email_message'           => 'LONGTEXT',
				'send_meta'               => 'TINYINT(1) DEFAULT 0',
				'meta_message_type'       => "VARCHAR(20) DEFAULT 'text'",
				'meta_template_name'      => "VARCHAR(255) DEFAULT ''",
				'meta_template_lang'      => "VARCHAR(20) DEFAULT ''",
				'meta_message_text'       => 'TEXT',
				'email_content_json'      => 'LONGTEXT',
			),
			'campaigns_queue'                  => array(
				'campaign_id'   => 'BIGINT UNSIGNED NOT NULL',
				'user_id'       => 'BIGINT UNSIGNED NOT NULL',
				'phone'         => 'VARCHAR(50) NOT NULL',
				'unique_code'   => 'VARCHAR(50) NOT NULL',
				'security_code' => 'VARCHAR(50) NOT NULL',
				'status'        => "VARCHAR(20) DEFAULT 'pending'",
				'channel'       => "VARCHAR(20) DEFAULT 'whatsapp'",
				'details'       => 'LONGTEXT NULL',
				'retry_count'   => 'INT DEFAULT 0',
				'last_error'    => 'TEXT NULL',
				'sent_at'       => 'DATETIME NULL',
				'created_at'    => 'DATETIME DEFAULT CURRENT_TIMESTAMP',
			),
			'blocked_numbers'                  => array(
				'phone'      => 'VARCHAR(20) NOT NULL',
				'created_at' => 'DATETIME DEFAULT CURRENT_TIMESTAMP',
			),
			'mws_settings'                     => array(
				'setting_name'  => 'VARCHAR(191) NOT NULL',
				'setting_value' => 'LONGTEXT NOT NULL',
			),
			'checkout_settings'                => array(
				'setting_name'  => 'VARCHAR(191) NOT NULL',
				'setting_value' => 'LONGTEXT NOT NULL',
			),
			'otp_login_settings'               => array(
				'setting_name'  => 'VARCHAR(191) NOT NULL',
				'setting_value' => 'LONGTEXT NOT NULL',
			),
			'registration_settings'            => array(
				'setting_name'  => 'VARCHAR(191) NOT NULL',
				'setting_value' => 'LONGTEXT NOT NULL',
			),
			'mws_templates'                    => array(
				'template_name' => 'VARCHAR(255) NOT NULL',
				'language_code' => 'VARCHAR(20) NOT NULL',
				'body'          => 'LONGTEXT',
			),
			'abandoned_carts'                  => array(
				'session_id'      => 'VARCHAR(255) NOT NULL',
				'user_id'         => 'BIGINT UNSIGNED DEFAULT 0',
				'contact_details' => 'LONGTEXT NULL',
				'location'        => 'VARCHAR(255) DEFAULT NULL',
				'cart_contents'   => 'LONGTEXT NULL',
				'cart_total'      => 'DECIMAL(10,2) DEFAULT 0.00',
				'status'          => "VARCHAR(50) DEFAULT 'new'",
				'order_id'        => "VARCHAR(50) DEFAULT ''",
				'coupon_code'     => 'VARCHAR(255) DEFAULT NULL',
				'browser'         => "VARCHAR(100) DEFAULT ''",
				'os'              => "VARCHAR(100) DEFAULT ''",
				'device'          => "VARCHAR(100) DEFAULT ''",
				'screen_size'     => "VARCHAR(20) DEFAULT ''",
				'page_history'    => 'LONGTEXT NULL',
				'created_at'      => 'DATETIME DEFAULT CURRENT_TIMESTAMP',
				'updated_at'      => 'DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
			),
			'abandoned_cart_notifications_log' => array(
				'cart_id'            => 'BIGINT UNSIGNED NOT NULL',
				'notification_type'  => 'VARCHAR(50) NOT NULL',
				'notification_index' => 'INT NOT NULL',
				'channel'            => 'VARCHAR(50) NOT NULL',
				'recipient'          => 'VARCHAR(255) NOT NULL',
				'status'             => 'VARCHAR(50) NOT NULL',
				'scheduled_at'       => 'DATETIME',
				'sent_at'            => 'DATETIME',
				'api_response'       => 'LONGTEXT',
			),
			'email_templates'                  => array(
				'name'         => 'VARCHAR(255) NOT NULL',
				'content_json' => 'LONGTEXT',
				'content_html' => 'LONGTEXT',
				'created_at'   => 'DATETIME DEFAULT CURRENT_TIMESTAMP',
				'updated_at'   => 'DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
			),
			'flows'                            => array(
				'id'          => 'BIGINT UNSIGNED NOT NULL AUTO_INCREMENT',
				'name'        => 'VARCHAR(255) NOT NULL',
				'trigger_key' => 'VARCHAR(100) NOT NULL',
				'status'      => "VARCHAR(20) DEFAULT 'active'",
				'is_system'   => 'TINYINT(1) DEFAULT 0',
				'created_at'  => 'DATETIME DEFAULT CURRENT_TIMESTAMP',
				'updated_at'  => 'DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
			),
			'flow_steps'                       => array(
				'id'         => 'BIGINT UNSIGNED NOT NULL AUTO_INCREMENT',
				'flow_id'    => 'BIGINT UNSIGNED NOT NULL',
				'step_type'  => 'VARCHAR(50) NOT NULL',
				'priority'   => 'INT DEFAULT 0',
				'created_at' => 'DATETIME DEFAULT CURRENT_TIMESTAMP',
			),
			'flow_step_meta'                   => array(
				'id'           => 'BIGINT UNSIGNED NOT NULL AUTO_INCREMENT',
				'step_id'      => 'BIGINT UNSIGNED NOT NULL',
				'config_key'   => 'VARCHAR(191) NOT NULL',
				'config_value' => 'LONGTEXT',
			),
			'flow_translations'                => array(
				'id'                => 'BIGINT UNSIGNED NOT NULL AUTO_INCREMENT',
				'step_id'           => 'BIGINT UNSIGNED NOT NULL',
				'language_code'     => 'VARCHAR(20) NOT NULL',
				'config_key'        => 'VARCHAR(191) NOT NULL',
				'translation_value' => 'LONGTEXT',
			),
			'ai_chats'                         => array(
				'id'         => 'BIGINT UNSIGNED NOT NULL AUTO_INCREMENT',
				'session_id' => 'BIGINT UNSIGNED NOT NULL',
				'user_id'    => 'BIGINT UNSIGNED NOT NULL',
				'role'       => "ENUM('user', 'assistant') NOT NULL",
				'content'    => 'LONGTEXT NOT NULL',
				'rating'     => 'SMALLINT DEFAULT 0',
				'created_at' => 'DATETIME DEFAULT CURRENT_TIMESTAMP',
			),
			'ai_sessions'                      => array(
				'id'         => 'BIGINT UNSIGNED NOT NULL AUTO_INCREMENT',
				'user_id'    => 'BIGINT UNSIGNED NOT NULL',
				'title'      => 'VARCHAR(255) NOT NULL',
				'created_at' => 'DATETIME DEFAULT CURRENT_TIMESTAMP',
				'updated_at' => 'DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
			),
			'languages'                        => array(
				'language_code' => 'VARCHAR(20) NOT NULL',
				'name'          => 'VARCHAR(255) NOT NULL',
				'is_main'       => 'TINYINT(1) NOT NULL DEFAULT 0',
				'created_at'    => 'DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP',
			),
			'temp_access_links'                => array(
				'id'           => 'BIGINT UNSIGNED NOT NULL AUTO_INCREMENT',
				'auth_mode'    => 'VARCHAR(20) NOT NULL',
				'target_id'    => 'VARCHAR(50) NOT NULL',
				'token_hash'   => 'VARCHAR(64) NOT NULL',
				'label'        => 'VARCHAR(255) NOT NULL',
				'allowed_ip'   => 'VARCHAR(45) DEFAULT NULL',
				'allowed_ua'   => 'VARCHAR(255) DEFAULT NULL',
				'max_clicks'   => 'INT DEFAULT 1',
				'click_count'  => 'INT DEFAULT 0',
				'expires_at'   => 'DATETIME NOT NULL',
				'temp_user_id' => 'BIGINT UNSIGNED DEFAULT 0',
				'status'       => "VARCHAR(20) DEFAULT 'active'",
				'restrictions' => 'TEXT NULL',
				'fingerprint'  => 'VARCHAR(64) DEFAULT NULL',
				'created_at'   => 'DATETIME DEFAULT CURRENT_TIMESTAMP',
			),
		);
	}

	/**
	 * Get columns map for a table.
	 *
	 * @param string $table Table name.
	 * @return array Columns map.
	 */
	public function columns_map( string $table ): array {
		return $this->get_columns_cached( $table );
	}

	/**
	 * Ensure all columns exist across all tables.
	 */
	public function ensure_all_columns() {
		if ( self::$ensure_ran ) {
			return;
		}

		self::$ensure_ran = true;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		foreach ( $this->schema_map() as $tbl_key => $columns ) {
			$table_name = isset( $this->tables[ $tbl_key ] ) ? $this->tables[ $tbl_key ] : $tbl_key;
			if ( ! $this->table_exists_cached( $table_name ) ) {
				continue;
			}

			// Get existing columns to avoid duplicate DESC queries via maybe_add_column.
			$existing_cols = $this->get_columns_cached( $table_name );
			foreach ( $columns as $col => $def ) {
				if ( ! isset( $existing_cols[ $col ] ) ) {
					maybe_add_column( $table_name, $col, "ALTER TABLE $table_name ADD $col $def" );
					// Invalidate cache since we added a column.
					wp_cache_delete( 'wawp_table_cols_' . md5( $table_name ), 'wawp_db' );
				}
			}
		}
	}

	/**
	 * Ensure all columns once.
	 */
	public function ensure_all_columns_once() {
		$this->ensure_all_columns();
	}

	/**
	 * Constructor.
	 */
	public function __construct() {
		global $wpdb;
		$this->wpdb         = $wpdb;
		$this->table_prefix = $wpdb->prefix . 'wawp_';
		$this->tables       = array(
			'instance_data'                    => $this->table_prefix . 'instance_data',
			'user_info'                        => $this->table_prefix . 'user_info',

			'campaigns'                        => $this->table_prefix . 'campaigns',
			'campaigns_queue'                  => $this->table_prefix . 'campaigns_queue',
			'blocked_numbers'                  => $this->table_prefix . 'blocked_numbers',
			'mws_settings'                     => $this->table_prefix . 'mws_settings',
			'mws_templates'                    => $this->table_prefix . 'mws_templates',
			'abandoned_carts'                  => $this->table_prefix . 'abandoned_carts',
			'abandoned_cart_notifications_log' => $this->table_prefix . 'abandoned_cart_notifications_log',
			'otp_login_settings'               => $this->table_prefix . 'otp_login_settings',
			'registration_settings'            => $this->table_prefix . 'registration_settings',
			'checkout_settings'                => $this->table_prefix . 'checkout_settings',
			'email_templates'                  => $this->table_prefix . 'email_templates',
			'firebase_accounts'                => $this->table_prefix . 'firebase_accounts',
			'smtp_accounts'                    => $this->table_prefix . 'smtp_accounts',
			'unified_activity_logs'            => $this->table_prefix . 'unified_activity_logs',
			'flows'                            => $this->table_prefix . 'flows',
			'flow_steps'                       => $this->table_prefix . 'flow_steps',
			'flow_step_meta'                   => $this->table_prefix . 'flow_step_meta',
			'flow_translations'                => $this->table_prefix . 'flow_translations',
			'ai_sessions'                      => $this->table_prefix . 'ai_sessions',
			'ai_chats'                         => $this->table_prefix . 'ai_chats',
			'languages'                        => $this->table_prefix . 'notif_languages',
			'temp_access_links'                => $this->table_prefix . 'temp_access_links',
		);
	}

	/**
	 * Get unified table name.
	 *
	 * @return string Table name.
	 */
	public function get_unified_table_name() {
		return $this->tables['unified_activity_logs'];
	}

	/**
	 * Get signup settings table name.
	 *
	 * @return string Table name.
	 */
	public function get_signup_settings_table_name() {
		return $this->table_prefix . 'signup_settings';
	}

	/**
	 * Get firebase accounts table name.
	 *
	 * @return string Table name.
	 */
	public function get_firebase_accounts_table_name() {
		return $this->tables['firebase_accounts'];
	}

	/**
	 * Get smtp accounts table name.
	 *
	 * @return string Table name.
	 */
	public function get_smtp_accounts_table_name() {
		return $this->tables['smtp_accounts'];
	}

	/**
	 * Create all tables.
	 */
	public function create_all_tables() {
		$this->create_unified_activity_logs_table();
		$this->create_instance_table();
		$this->create_notif_languages_table();
		$this->create_user_info_table();
		$this->create_campaigns_table();
		$this->create_campaigns_queue_table();
		$this->create_blocked_numbers_table();
		$this->create_mws_settings_table();
		$this->create_mws_templates_table();
		$this->create_abandoned_carts_table();
		$this->create_abandoned_cart_notifications_log_table();
		$this->create_otp_login_settings_table();
		$this->create_registration_settings_table();
		$this->create_checkout_settings_table();
		$this->create_email_templates_table();
		$this->create_firebase_accounts_table();
		$this->create_smtp_accounts_table();
		$this->create_flow_builder_tables();
		$this->create_ai_sessions_table();
		$this->create_ai_chats_table();
		$this->create_temp_access_links_table();
	}

	/**
	 * Create flow builder tables.
	 */
	private function create_flow_builder_tables() {
		$this->run_db_delta(
			$this->tables['flows'],
			"( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, trigger_key VARCHAR(100) NOT NULL, status VARCHAR(20) DEFAULT 'active', is_system TINYINT(1) DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY trigger_key (trigger_key) )"
		);
		$this->run_db_delta(
			$this->tables['flow_steps'],
			'( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, flow_id BIGINT UNSIGNED NOT NULL, step_type VARCHAR(50) NOT NULL, priority INT DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY flow_id (flow_id) )'
		);
		$this->run_db_delta(
			$this->tables['flow_step_meta'],
			'( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, step_id BIGINT UNSIGNED NOT NULL, config_key VARCHAR(191) NOT NULL, config_value LONGTEXT, PRIMARY KEY (id), KEY step_id (step_id), KEY config_key (config_key) )'
		);

		// Drop old index if it is not a composite index.
		global $wpdb;
		$table_name = $this->tables['flow_translations'];
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) ) ) {
			$index_exists = $wpdb->get_results( $wpdb->prepare( 'SHOW INDEX FROM %i WHERE Key_name = %s', $table_name, 'lang_meta' ) );
			if ( ! empty( $index_exists ) ) {
				$columns = wp_list_pluck( $index_exists, 'Column_name' );
				if ( count( $columns ) < 2 || ! in_array( 'config_key', $columns, true ) ) {
					$wpdb->query( $wpdb->prepare( 'ALTER TABLE %i DROP INDEX lang_meta', $table_name ) );
				}
			}
		}

		$this->run_db_delta(
			$this->tables['flow_translations'],
			'( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, step_id BIGINT UNSIGNED NOT NULL, language_code VARCHAR(20) NOT NULL, config_key VARCHAR(191) NOT NULL, translation_value LONGTEXT, PRIMARY KEY  (id), KEY step_id (step_id), KEY lang_meta (language_code,config_key) )'
		);
	}

	/**
	 * Create AI sessions table.
	 */
	private function create_ai_sessions_table() {
		$this->run_db_delta(
			$this->tables['ai_sessions'],
			'( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, title VARCHAR(255) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY user_id (user_id) )'
		);
	}

	/**
	 * Create AI chats table.
	 */
	private function create_ai_chats_table() {
		$this->run_db_delta(
			$this->tables['ai_chats'],
			"( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, session_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, role ENUM('user', 'assistant') NOT NULL, content LONGTEXT NOT NULL, rating SMALLINT DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY session_id (session_id), KEY user_id (user_id) )"
		);
	}

	/**
	 * Create temporary access links table.
	 */
	private function create_temp_access_links_table() {
		$this->run_db_delta(
			$this->tables['temp_access_links'],
			"( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, auth_mode VARCHAR(20) NOT NULL, target_id VARCHAR(50) NOT NULL, token_hash VARCHAR(64) NOT NULL, label VARCHAR(255) NOT NULL, allowed_ip VARCHAR(45) DEFAULT NULL, allowed_ua VARCHAR(255) DEFAULT NULL, max_clicks INT DEFAULT 1, click_count INT DEFAULT 0, expires_at DATETIME NOT NULL, temp_user_id BIGINT UNSIGNED DEFAULT 0, status VARCHAR(20) DEFAULT 'active', restrictions TEXT NULL, fingerprint VARCHAR(64) DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY token_hash (token_hash) )"
		);
	}

	/**
	 * Run migrations for version 4.8.1.
	 */
	public function migrate_4_7_9() {
		global $wpdb;
		$table  = $this->tables['ai_chats'];
		$column = $this->get_results_cached( $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $table, 'rating' ), 'col_check_' . md5( $table . 'rating' ) );
		if ( empty( $column ) ) {
			$this->execute_query( $wpdb->prepare( 'ALTER TABLE %i ADD COLUMN rating SMALLINT DEFAULT 0 AFTER content', $table ) );
		}
	}

	/**
	 * Create unified activity logs table.
	 */
	private function create_unified_activity_logs_table() {
		$this->run_db_delta(
			$this->tables['unified_activity_logs'],
			"( id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT(20) UNSIGNED DEFAULT NULL, order_id BIGINT(20) UNSIGNED DEFAULT NULL, phone VARCHAR(20) DEFAULT NULL, email VARCHAR(255) DEFAULT NULL, event_type VARCHAR(50) NOT NULL, gateway VARCHAR(50) DEFAULT NULL, log_status VARCHAR(20) DEFAULT 'pending', message TEXT NULL, message_type VARCHAR(20) DEFAULT 'text', wawp_status TEXT NULL, instance_id VARCHAR(100) DEFAULT NULL, access_token TEXT NULL, is_read TINYINT(1) DEFAULT 0, details LONGTEXT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id), KEY user_id (user_id), KEY phone (phone), KEY email (email), KEY event_type (event_type), KEY gateway (gateway), KEY log_status (log_status) )"
		);
	}

	/**
	 * Create OTP login settings table.
	 */
	private function create_otp_login_settings_table() {
		$this->run_db_delta( $this->tables['otp_login_settings'], '( id MEDIUMINT(9) NOT NULL AUTO_INCREMENT, setting_name VARCHAR(191) NOT NULL, setting_value LONGTEXT NOT NULL, PRIMARY KEY (id), UNIQUE KEY setting_name (setting_name) )' );
	}

	/**
	 * Create registration settings table.
	 */
	private function create_registration_settings_table() {
		$this->run_db_delta( $this->tables['registration_settings'], '( id MEDIUMINT(9) NOT NULL AUTO_INCREMENT, setting_name VARCHAR(191) NOT NULL, setting_value LONGTEXT NOT NULL, PRIMARY KEY (id), UNIQUE KEY setting_name (setting_name) )' );
	}

	/**
	 * Create checkout settings table.
	 */
	private function create_checkout_settings_table() {
		$this->run_db_delta( $this->tables['checkout_settings'], '( id MEDIUMINT(9) NOT NULL AUTO_INCREMENT, setting_name VARCHAR(191) NOT NULL, setting_value LONGTEXT NOT NULL, PRIMARY KEY (id), UNIQUE KEY setting_name (setting_name) )' );
	}

	/**
	 * Create email templates table.
	 */
	private function create_email_templates_table() {
		$this->run_db_delta( $this->tables['email_templates'], '( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, content_json LONGTEXT, content_html LONGTEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id) )' );
	}

	/**
	 * Create firebase accounts table.
	 */
	private function create_firebase_accounts_table() {
		$this->run_db_delta( $this->tables['firebase_accounts'], '( id INT(11) NOT NULL AUTO_INCREMENT, account_name VARCHAR(255) NOT NULL, config LONGTEXT NOT NULL, is_active TINYINT(1) DEFAULT 0, validated TINYINT(1) DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id) )' );
	}

	/**
	 * Create SMTP accounts table.
	 */
	private function create_smtp_accounts_table() {
		$this->run_db_delta( $this->tables['smtp_accounts'], '( id INT(11) NOT NULL AUTO_INCREMENT, account_name VARCHAR(255) NOT NULL, provider VARCHAR(50) DEFAULT \'smtp\', config LONGTEXT NOT NULL, is_active TINYINT(1) DEFAULT 0, validated TINYINT(1) DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id) )' );
	}

	/**
	 * Create user info table.
	 */
	private function create_user_info_table() {
		$this->run_db_delta( $this->tables['user_info'], "( id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT(20) UNSIGNED NOT NULL, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, phone VARCHAR(20) NOT NULL, password VARCHAR(255) NOT NULL, otp_verification_email BOOLEAN DEFAULT FALSE, otp_verification_whatsapp BOOLEAN DEFAULT FALSE, whatsapp_verified ENUM('Verified','Not Verified') DEFAULT 'Not Verified', email_verified ENUM('Verified','Not Verified') DEFAULT 'Not Verified', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY unique_user (user_id), KEY idx_phone (phone) )" );
	}

	/**
	 * Create notification languages table.
	 */
	private function create_notif_languages_table() {
		$table = $this->table_prefix . 'notif_languages';
		$this->run_db_delta( $table, '( language_code VARCHAR(20) NOT NULL, name VARCHAR(255) NOT NULL, is_main TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (language_code) )' );
		global $wpdb;

		$row_count = (int) $this->get_var_cached( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ), 'notif_langs_count' );
		if ( 0 === $row_count ) {
			$this->execute_insert(
				$table,
				array(
					'language_code' => 'en_US',
					'name'          => 'English (US)',
					'is_main'       => 1,
				),
				array( '%s', '%s', '%d' )
			);
		}
	}

	/**
	 * Create instance table.
	 */
	private function create_instance_table() {
		$this->run_db_delta( $this->tables['instance_data'], "( id INT(11) NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, instance_id VARCHAR(255) NOT NULL, access_token VARCHAR(255) NOT NULL, status VARCHAR(50) DEFAULT 'Unknown', message TEXT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) )" );
	}

	/**
	 * Run dbDelta for a table.
	 *
	 * @param string $table_name Table name.
	 * @param string $columns    Columns definition.
	 */
	private function run_db_delta( $table_name, $columns ) {
		$charset_collate = $this->wpdb->get_charset_collate();

		// dbDelta() is EXTREMELY sensitive to syntax and requires each column/key on its own line.
		// It also requires exactly two spaces after PRIMARY KEY, KEY, and UNIQUE KEY.

		$formatted = '';
		$depth     = 0;
		$len       = strlen( $columns );
		$trimmed   = trim( $columns, '()' );

		// Split by comma at depth 0 (main column/key level).
		$parts        = array();
		$current_part = '';
		$paren_depth  = 0;
		$trimmed_len  = strlen( $trimmed );
		for ( $i = 0; $i < $trimmed_len; $i++ ) {
			$char = $trimmed[ $i ];
			if ( '(' === $char ) {
				++$paren_depth;
			}
			if ( ')' === $char ) {
				--$paren_depth;
			}

			if ( ',' === $char && 0 === $paren_depth ) {
				$parts[]      = trim( $current_part );
				$current_part = '';
			} else {
				$current_part .= $char;
			}
		}
		$parts[] = trim( $current_part );

		// Process each part and ensure correct spacing/newlines.
		$lines = array();
		foreach ( $parts as $part ) {
			if ( empty( $part ) ) {
				continue;
			}

			// Handle spaces for Keys.
			$part = preg_replace( '/^PRIMARY KEY\s*\(/i', 'PRIMARY KEY  (', $part );
			$part = preg_replace( '/^KEY\s+([a-zA-Z0-9_]+)\s*\(/i', 'KEY  $1 (', $part );
			$part = preg_replace( '/^UNIQUE KEY\s+([a-zA-Z0-9_]+)\s*\(/i', 'UNIQUE KEY  $1 (', $part );

			// Ensure space after comma inside composite keys (e.g. (col1, col2)).
			$part = preg_replace( '/,([^\s])/', ', $1', $part );

			$lines[] = $part;
		}

		$formatted_sql = "(\n" . implode( ",\n", $lines ) . "\n)";

		$sql = "CREATE TABLE `$table_name` $formatted_sql $charset_collate;";
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );

		wp_cache_delete( 'wawp_table_exists_' . md5( $table_name ), 'wawp_db' );
		wp_cache_delete( 'wawp_table_cols_' . md5( $table_name ), 'wawp_db' );
	}

	/**
	 * Create blocked numbers table.
	 */
	private function create_blocked_numbers_table() {
		$this->run_db_delta( $this->tables['blocked_numbers'], '( id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, phone VARCHAR(20) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY phone (phone) )' );
	}

	/**
	 * Create campaigns table.
	 */
	private function create_campaigns_table() {
		$this->run_db_delta( $this->tables['campaigns'], "( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, instances TEXT, role_ids TEXT, user_ids TEXT, external_numbers TEXT, external_emails TEXT, message TEXT, media_url VARCHAR(255) DEFAULT '', min_whatsapp_interval INT DEFAULT 60, max_whatsapp_interval INT DEFAULT 75, min_email_interval INT DEFAULT 30, max_email_interval INT DEFAULT 60, start_datetime DATETIME NULL, repeat_type VARCHAR(20) DEFAULT 'no', repeat_days INT DEFAULT 0, post_id BIGINT UNSIGNED DEFAULT 0, product_id BIGINT UNSIGNED DEFAULT 0, append_post TINYINT(1) DEFAULT 0, append_product TINYINT(1) DEFAULT 0, send_type VARCHAR(20) DEFAULT 'text', total_count INT DEFAULT 0, processed_count INT DEFAULT 0, status VARCHAR(20) DEFAULT 'saved', paused TINYINT(1) DEFAULT 0, next_run DATETIME NULL, woo_spent_over DECIMAL(10,2) DEFAULT 0, woo_orders_over INT DEFAULT 0, only_verified_phone TINYINT(1) DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, post_include_title TINYINT(1) DEFAULT 0, post_include_excerpt TINYINT(1) DEFAULT 0, post_include_link TINYINT(1) DEFAULT 0, post_include_image TINYINT(1) DEFAULT 0, product_include_title TINYINT(1) DEFAULT 0, product_include_excerpt TINYINT(1) DEFAULT 0, product_include_link TINYINT(1) DEFAULT 0, product_include_image TINYINT(1) DEFAULT 0, product_include_price TINYINT(1) DEFAULT 0, woo_ordered_products TEXT, woo_order_statuses TEXT, max_per_day INT DEFAULT 0, max_wa_per_day INT DEFAULT 0, max_email_per_day INT DEFAULT 0, billing_countries TEXT, wp_profile_languages TEXT, send_whatsapp TINYINT(1) DEFAULT 1, send_email TINYINT(1) DEFAULT 0, email_subject VARCHAR(255) DEFAULT '', email_message LONGTEXT, send_meta TINYINT(1) DEFAULT 0, meta_message_type VARCHAR(20) DEFAULT 'text', meta_template_name VARCHAR(255) DEFAULT '', meta_template_lang VARCHAR(20) DEFAULT '', meta_message_text TEXT, email_content_json LONGTEXT, PRIMARY KEY (id), KEY status (status), KEY next_run (next_run) )" );
	}

	/**
	 * Create campaigns queue table.
	 */
	private function create_campaigns_queue_table() {
		$this->run_db_delta( $this->tables['campaigns_queue'], "( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, campaign_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, phone VARCHAR(50) NOT NULL, unique_code VARCHAR(50) NOT NULL, security_code VARCHAR(50) NOT NULL, status VARCHAR(20) DEFAULT 'pending', channel VARCHAR(20) DEFAULT 'whatsapp', details LONGTEXT NULL, retry_count INT DEFAULT 0, last_error TEXT NULL, sent_at DATETIME NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY campaign_id (campaign_id), KEY status_camp (status,campaign_id), KEY status (status) )" );
	}

	/**
	 * Create MWS settings table.
	 */
	private function create_mws_settings_table() {
		$this->run_db_delta( $this->tables['mws_settings'], '( id MEDIUMINT(9) NOT NULL AUTO_INCREMENT, setting_name VARCHAR(191) NOT NULL, setting_value LONGTEXT NOT NULL, PRIMARY KEY (id), UNIQUE KEY setting_name (setting_name) )' );
	}

	/**
	 * Create MWS templates table.
	 */
	private function create_mws_templates_table() {
		$this->run_db_delta( $this->tables['mws_templates'], '( id MEDIUMINT(9) NOT NULL AUTO_INCREMENT, template_name VARCHAR(255) NOT NULL, language_code VARCHAR(20) NOT NULL, body LONGTEXT, PRIMARY KEY (id), UNIQUE KEY name_lang (template_name,language_code) )' );
	}

	/**
	 * Create abandoned carts table.
	 */
	private function create_abandoned_carts_table() {
		$this->run_db_delta( $this->tables['abandoned_carts'], "( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, session_id VARCHAR(255) NOT NULL, user_id BIGINT UNSIGNED DEFAULT 0, contact_details LONGTEXT NULL, location VARCHAR(255) DEFAULT NULL, cart_contents LONGTEXT NULL, cart_total DECIMAL(10,2) DEFAULT 0.00, status VARCHAR(50) DEFAULT 'new', order_id VARCHAR(50) DEFAULT '', coupon_code VARCHAR(255) DEFAULT NULL, browser VARCHAR(100) DEFAULT '', os VARCHAR(100) DEFAULT '', device VARCHAR(100) DEFAULT '', screen_size VARCHAR(20) DEFAULT '', page_history LONGTEXT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_id (user_id), KEY idx_status (status), KEY idx_session_id (session_id), KEY idx_location (location) )" );
	}

	/**
	 * Create abandoned cart notifications log table.
	 */
	private function create_abandoned_cart_notifications_log_table() {
		$this->run_db_delta( $this->tables['abandoned_cart_notifications_log'], '( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, cart_id BIGINT UNSIGNED NOT NULL, notification_type VARCHAR(50) NOT NULL, notification_index INT NOT NULL, channel VARCHAR(50) NOT NULL, recipient VARCHAR(255) NOT NULL, status VARCHAR(50) NOT NULL, scheduled_at DATETIME, sent_at DATETIME, api_response LONGTEXT, PRIMARY KEY (id), KEY cart_id (cart_id) )' );
	}

	/**
	 * Check if table exists (cached).
	 *
	 * @param string $table Table name.
	 * @return bool Exists status.
	 */
	public function table_exists_cached( $table ) {
		if ( isset( self::$table_exists_cache[ $table ] ) ) {
			return self::$table_exists_cache[ $table ];
		}

		$cache_key = 'wawp_table_exists_' . md5( $table );
		$exists    = wp_cache_get( $cache_key, 'wawp_db' );

		if ( false !== $exists ) {
			$runtime_cache[ $table ] = (bool) $exists;
			return (bool) $exists;
		}

		global $wpdb;
		$exists = (bool) call_user_func_array( array( $wpdb, 'get_var' ), array( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) ) );

		wp_cache_set( $cache_key, $exists, 'wawp_db', HOUR_IN_SECONDS );
		self::$table_exists_cache[ $table ] = $exists;
		return $exists;
	}

	/**
	 * Clear database cache.
	 */
	public function clear_db_cache() {
		self::$table_exists_cache  = array();
		self::$table_columns_cache = array();
	}

	/**
	 * Get columns for a table (cached).
	 *
	 * @param string $table Table name.
	 * @return array Columns.
	 */
	private function get_columns_cached( $table ) {
		if ( isset( self::$table_columns_cache[ $table ] ) ) {
			return self::$table_columns_cache[ $table ];
		}

		$cache_key = 'wawp_table_cols_' . md5( $table );
		$columns   = wp_cache_get( $cache_key, 'wawp_db' );

		if ( is_array( $columns ) ) {
			self::$table_columns_cache[ $table ] = $columns;
			return $columns;
		}

		if ( ! $this->table_exists_cached( $table ) ) {
			wp_cache_set( $cache_key, array(), 'wawp_db', HOUR_IN_SECONDS );
			self::$table_columns_cache[ $table ] = array();
			return array();
		}

		global $wpdb;
		$cols = call_user_func_array( array( $wpdb, 'get_col' ), array( $wpdb->prepare( 'SHOW COLUMNS FROM %i', $table ), 0 ) );
		$cols = $cols ? array_fill_keys( $cols, true ) : array();

		wp_cache_set( $cache_key, $cols, 'wawp_db', HOUR_IN_SECONDS );

		self::$table_columns_cache[ $table ] = $cols;
		return $cols;
	}

	/**
	 * Maybe add a column to a table (Legacy wrapper, now uses WP Core).
	 *
	 * @param string $table      Table name.
	 * @param string $column     Column name.
	 * @param string $definition Column definition.
	 */
	private function maybe_add_column( $table, $column, $definition ) {
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		maybe_add_column( $table, $column, "ALTER TABLE $table ADD $column $definition" );
		wp_cache_delete( 'wawp_table_cols_' . md5( $table ), 'wawp_db' );
	}

	/**
	 * Get row by ID.
	 *
	 * @param string $table_key Table key in map.
	 * @param int    $id        Row ID.
	 * @return object|null Row object.
	 */
	public function get_row_by_id( $table_key, $id ) {
		if ( ! isset( $this->tables[ $table_key ] ) ) {
			return null;
		}

		static $runtime_cache = array();
		$table                = $this->tables[ $table_key ];
		$cache_key            = "wawp_db_row_{$table_key}_{$id}";

		if ( isset( $runtime_cache[ $cache_key ] ) ) {
			return $runtime_cache[ $cache_key ];
		}

		$row = wp_cache_get( $cache_key, 'wawp_db' );

		if ( false !== $row ) {
			$runtime_cache[ $cache_key ] = $row;
			return $row;
		}

		global $wpdb;
		$row = call_user_func_array( array( $wpdb, 'get_row' ), array( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d', $table, $id ) ) );

		wp_cache_set( $cache_key, $row, 'wawp_db', 300 );

		$runtime_cache[ $cache_key ] = $row;
		return $row;
	}

	/**
	 * Get multiple results with caching.
	 *
	 * @param string $query     SQL query.
	 * @param string $cache_key Cache key.
	 * @param string $group     Cache group.
	 * @param int    $expiry    Cache expiry.
	 * @return array
	 */
	public function get_results_cached( $query, $cache_key, $group = 'wawp_db', $expiry = HOUR_IN_SECONDS ) {
		static $runtime_cache = array();
		$hash                 = md5( $query );

		if ( isset( $runtime_cache[ $hash ] ) ) {
			return $runtime_cache[ $hash ];
		}

		$results = wp_cache_get( $cache_key, $group );
		if ( false !== $results ) {
			$runtime_cache[ $hash ] = (array) $results;
			return (array) $results;
		}

		global $wpdb;

		$results = call_user_func_array( array( $wpdb, 'get_results' ), array( $query, ARRAY_A ) );
		wp_cache_set( $cache_key, $results, $group, $expiry );

		$runtime_cache[ $hash ] = (array) $results;
		return (array) $results;
	}

	/**
	 * Upsert a row (Replace style).
	 *
	 * @param string $table_key Table key.
	 * @param array  $data      Data.
	 * @return int|bool
	 */
	public function upsert_row( $table_key, $data ) {
		if ( ! isset( $this->tables[ $table_key ] ) ) {
			return false;
		}

		global $wpdb;
		$table = $this->tables[ $table_key ];
		$res   = call_user_func_array( array( $wpdb, 'replace' ), array( $table, $data ) );

		wp_cache_delete( "wawp_db_list_{$table_key}", 'wawp_db' );
		if ( isset( $data['id'] ) ) {
			wp_cache_delete( "wawp_db_row_{$table_key}_{$data['id']}", 'wawp_db' );
		}

		return $res;
	}

	/**
	 * Get a single variable from DB.
	 *
	 * @param string $query SQL.
	 * @return mixed
	 */
	public function get_var( $query ) {
		global $wpdb;

		return call_user_func_array( array( $wpdb, 'get_var' ), array( $query ) );
	}

	/**
	 * Get a single variable with caching.
	 *
	 * @param string $query     SQL.
	 * @param string $cache_key Key.
	 * @param string $group     Group.
	 * @param int    $expiry    Expiry.
	 * @return mixed
	 */
	public function get_var_cached( $query, $cache_key, $group = 'wawp_db', $expiry = HOUR_IN_SECONDS ) {
		$res = wp_cache_get( $cache_key, $group );
		if ( false !== $res ) {
			return $res;
		}

		$res = $this->get_var( $query );
		wp_cache_set( $cache_key, $res, $group, $expiry );

		return $res;
	}

	/**
	 * Get a column from DB.
	 *
	 * @param string $query SQL.
	 * @return array
	 */
	public function get_col( $query ) {
		global $wpdb;

		return (array) call_user_func_array( array( $wpdb, 'get_col' ), array( $query ) );
	}

	/**
	 * Get a column with caching.
	 *
	 * @param string $query     SQL query.
	 * @param string $cache_key Cache key.
	 * @param string $group     Cache group.
	 * @param int    $expiry    Cache expiry.
	 * @return array
	 */
	public function get_col_cached( $query, $cache_key, $group = 'wawp_db', $expiry = HOUR_IN_SECONDS ) {
		$res = wp_cache_get( $cache_key, $group );
		if ( false !== $res ) {
			return (array) $res;
		}

		$res = $this->get_col( $query );
		wp_cache_set( $cache_key, $res, $group, $expiry );

		return (array) $res;
	}

	/**
	 * Insert a row.
	 *
	 * @param string $table_key Table key.
	 * @param array  $data      Data.
	 * @return int|bool
	 */
	public function insert( $table_key, $data ) {
		if ( ! isset( $this->tables[ $table_key ] ) ) {
			return false;
		}

		global $wpdb;
		$table = $this->tables[ $table_key ];

		$res = $this->execute_insert( $table, $data );

		wp_cache_delete( "wawp_db_list_{$table_key}", 'wawp_db' );

		return $res ? $wpdb->insert_id : false;
	}

	/**
	 * Update a row in a custom table with automatic cache invalidation.
	 *
	 * @param string $table_key Table key from schema.
	 * @param array  $data      Data to update.
	 * @param array  $where     Where clause.
	 * @return int|bool
	 */
	public function update_row( $table_key, $data, $where ) {
		if ( ! isset( $this->tables[ $table_key ] ) ) {
			return false;
		}

		$table = $this->tables[ $table_key ];

		$res = $this->execute_update( $table, $data, $where );

		if ( isset( $where['id'] ) ) {
			wp_cache_delete( "wawp_db_row_{$table_key}_{$where['id']}", 'wawp_db' );
		}

		return $res;
	}

	/**
	 * Atomic increment of a column.
	 *
	 * @param string $table_key Table key.
	 * @param string $column    Column name.
	 * @param int    $id        Row ID.
	 * @param int    $amount    Amount to increment.
	 * @return int|bool
	 */
	public function increment_column( $table_key, $column, $id, $amount = 1 ) {
		if ( ! isset( $this->tables[ $table_key ] ) ) {
			return false;
		}

		global $wpdb;
		$table = $this->tables[ $table_key ];

		$res = $this->execute_query( $wpdb->prepare( 'UPDATE %i SET %i = %i + %d WHERE id = %d', $table, $column, $column, $amount, $id ) );

		wp_cache_delete( "wawp_db_row_{$table_key}_{$id}", 'wawp_db' );

		return $res;
	}

	/**
	 * Delete a row with cache invalidation.
	 *
	 * @param string $table_key Table key.
	 * @param array  $where     Where clause.
	 * @return int|bool
	 */
	public function delete_row( $table_key, $where ) {
		if ( ! isset( $this->tables[ $table_key ] ) ) {
			return false;
		}

		$table = $this->tables[ $table_key ];

		$res = $this->execute_delete( $table, $where );

		if ( isset( $where['id'] ) ) {
			wp_cache_delete( "wawp_db_row_{$table_key}_{$where['id']}", 'wawp_db' );
		}

		return $res;
	}

	/**
	 * Truncate a table.
	 *
	 * @param string $table_key Table key.
	 * @return bool
	 */
	public function truncate_row( $table_key ) {
		if ( ! isset( $this->tables[ $table_key ] ) ) {
			return false;
		}

		global $wpdb;
		$table = $this->tables[ $table_key ];

		$this->execute_query( $wpdb->prepare( 'TRUNCATE TABLE %i', $table ) );

		wp_cache_delete( "wawp_db_list_{$table_key}", 'wawp_db' );

		return true;
	}

	/**
	 * Check if table exists.
	 *
	 * @param string $key_or_name Key or name.
	 * @return bool Exists status.
	 */
	public function has_table( $key_or_name ): bool {
		$table = $this->tables[ $key_or_name ] ?? $key_or_name;

		return $this->table_exists_cached( $table );
	}

	/**
	 * Cleanup old logs.
	 *
	 * @param int $days Number of days.
	 */
	public function cleanup_old_logs( $days = 30 ) {
		global $wpdb;
		$cutoff = gmdate( 'Y-m-d H:i:s', time() - ( $days * DAY_IN_SECONDS ) );
		if ( isset( $this->tables['unified_activity_logs'] ) ) {

			$this->execute_query( $wpdb->prepare( 'DELETE FROM %i WHERE created_at < %s', $this->tables['unified_activity_logs'], $cutoff ) );
		}
		if ( isset( $this->tables['campaigns_queue'] ) ) {

			$this->execute_query( $wpdb->prepare( "DELETE FROM %i WHERE status IN ('sent', 'failed') AND created_at < %s", $this->tables['campaigns_queue'], $cutoff ) );
		}
	}

	/**
	 * Migrate old AWP tables to WAWP.
	 *
	 * @param bool $force Force migration.
	 */
	public function migrate_awp_tables_to_wawp( $force = false ) {
		if ( ! $force && $this->get_wawp_setting( WAWP_Settings_Registry::V52_MIGRATION_FINAL ) ) {
			return;
		}
		global $wpdb;
		$rename_map = array(
			$wpdb->prefix . 'awp_instance_data'            => $wpdb->prefix . 'wawp_instance_data',
			$wpdb->prefix . 'awp_notifications_log'        => $wpdb->prefix . 'wawp_notifications_log',
			$wpdb->prefix . 'awp_firebase_logs'            => $wpdb->prefix . 'wawp_firebase_logs',
			$wpdb->prefix . 'awp_notif_notification_rules' => $wpdb->prefix . 'wawp_notif_notification_rules',
			$wpdb->prefix . 'awp_user_info'                => $wpdb->prefix . 'wawp_user_info',
			$wpdb->prefix . 'awp_blocked_numbers'          => $wpdb->prefix . 'wawp_blocked_numbers',
			$wpdb->prefix . 'awp_mws_settings'             => $wpdb->prefix . 'wawp_mws_settings',
			$wpdb->prefix . 'awp_mws_templates'            => $wpdb->prefix . 'wawp_mws_templates',
			$wpdb->prefix . 'awp_mws_message_logs'         => $wpdb->prefix . 'wawp_mws_message_logs',
			$wpdb->prefix . 'awp_notif_languages'          => $wpdb->prefix . 'wawp_notif_languages',
			$wpdb->prefix . 'awp_notif_global'             => $wpdb->prefix . 'wawp_notif_global',
		);
		$wpdb->suppress_errors( true );
		foreach ( $rename_map as $old_table => $new_table ) {

			$old_exists = (bool) call_user_func_array( array( $wpdb, 'get_var' ), array( $wpdb->prepare( 'SHOW TABLES LIKE %s', $old_table ) ) );

			if ( ! $old_exists ) {
				continue;
			}

			$new_exists = (bool) call_user_func_array( array( $wpdb, 'get_var' ), array( $wpdb->prepare( 'SHOW TABLES LIKE %s', $new_table ) ) );

			if ( ! $new_exists ) {

				$this->execute_query( $wpdb->prepare( 'RENAME TABLE %i TO %i', $old_table, $new_table ) );

			} else {

				$this->execute_query( $wpdb->prepare( 'INSERT IGNORE INTO %i SELECT * FROM %i', $new_table, $old_table ) );

				$this->execute_query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $old_table ) );

			}
		}

		$awp_options = call_user_func_array( array( $wpdb, 'get_col' ), array( $wpdb->prepare( 'SELECT option_name FROM %i WHERE option_name LIKE %s', $wpdb->options, 'awp_%' ) ) );

		foreach ( $awp_options as $old_option ) {
			$new_option = str_replace( 'awp_', 'wawp_', $old_option );
			if ( ! $this->get_wawp_setting( $new_option ) ) {
				update_option( $new_option, get_option( $old_option ) );
			}
			delete_option( $old_option );
		}

		$awp_transients = call_user_func_array( array( $wpdb, 'get_col' ), array( $wpdb->prepare( 'SELECT option_name FROM %i WHERE option_name LIKE %s OR option_name LIKE %s', $wpdb->options, '_transient_awp_%', '_transient_timeout_awp_%' ) ) );

		foreach ( $awp_transients as $old_transient_name ) {
			$clean_name = str_replace( array( '_transient_timeout_', '_transient_' ), '', $old_transient_name );
			$new_name   = str_replace( 'awp_', 'wawp_', $clean_name );
			if ( false === strpos( $old_transient_name, '_timeout_' ) && ! get_transient( $new_name ) ) {
				$val = get_transient( $clean_name );
				if ( false !== $val ) {
					set_transient( $new_name, $val, 12 * HOUR_IN_SECONDS );
				}
				delete_transient( $clean_name );
			}
		}
		$wpdb->suppress_errors( false );
		update_option( WAWP_Settings_Registry::DB_MIGRATED, true );
		$this->sync_missing_user_phones();
		update_option( WAWP_Settings_Registry::V52_MIGRATION_FINAL, true );
	}
	/**
	 * Sync missing user phones to metadata.
	 */
	public function sync_missing_user_phones() {
		global $wpdb;
		$user_meta_key = WAWP_Settings_Registry::USER_PHONE_META_KEY;

		$this->execute_query( $wpdb->prepare( "INSERT IGNORE INTO %i (user_id, meta_key, meta_value) SELECT um1.user_id, 'wawp_user_phone', um1.meta_value FROM %i um1 LEFT JOIN %i um2 ON um1.user_id = um2.user_id AND (um2.meta_key = 'wawp_user_phone' OR um2.meta_key = %s) WHERE um1.meta_key = 'billing_phone' AND um1.meta_value != '' AND um2.meta_id IS NULL", $wpdb->usermeta, $wpdb->usermeta, $wpdb->usermeta, $user_meta_key ) );

		$this->execute_query( $wpdb->prepare( "INSERT IGNORE INTO %i (user_id, meta_key, meta_value) SELECT um.user_id, %s, um.meta_value FROM %i um LEFT JOIN %i um2 ON um.user_id = um2.user_id AND um2.meta_key = %s WHERE um.meta_key = 'wawp_user_phone' AND um2.meta_id IS NULL", $wpdb->usermeta, $user_meta_key, $wpdb->usermeta, $wpdb->usermeta, $user_meta_key ) );

		$user_info_table = $this->tables['user_info'];
		if ( $this->table_exists_cached( $user_info_table ) ) {

			$this->execute_query( $wpdb->prepare( "INSERT IGNORE INTO %i (user_id, first_name, last_name, email, phone, password, created_at, updated_at) SELECT u.ID, IFNULL(um_fn.meta_value, ''), IFNULL(um_ln.meta_value, ''), u.user_email, um_p.meta_value, '', NOW(), NOW() FROM %i u JOIN %i um_p ON u.ID = um_p.user_id AND (um_p.meta_key = 'wawp_user_phone' OR um_p.meta_key = %s OR um_p.meta_key = 'billing_phone') LEFT JOIN %i um_fn ON u.ID = um_fn.user_id AND um_fn.meta_key = 'first_name' LEFT JOIN %i um_ln ON u.ID = um_ln.user_id AND um_ln.meta_key = 'last_name' LEFT JOIN %i ui ON u.ID = ui.user_id WHERE ui.user_id IS NULL AND um_p.meta_value != '' GROUP BY u.ID", $user_info_table, $wpdb->users, $wpdb->usermeta, $user_meta_key, $wpdb->usermeta, $wpdb->usermeta, $user_info_table ) );

			$this->execute_query( $wpdb->prepare( "UPDATE %i ui JOIN %i um ON ui.user_id = um.user_id SET ui.phone = um.meta_value WHERE (ui.phone = '' OR ui.phone IS NULL) AND (um.meta_key = 'wawp_user_phone' OR um.meta_key = %s OR um.meta_key = 'billing_phone') AND um.meta_value != ''", $user_info_table, $wpdb->usermeta, $user_meta_key ) );

		}
	}
}
