<?php
/**
 * Database class for managing plugin database operations
 *
 * This class handles all database operations for the Ask Any plugin,
 * including content indexing, Q&A management, and chat history.
 *
 * @package Askany
 * @since 1.0.0
 */


namespace Askany;

use ask\askany\wpnts_nts_fs;

/**
 * Database class for managing plugin database operations
 *
 * @package Askany
 * @since 1.0.0
 */
class Database {
	/**
	 * Content index table name.
	 *
	 * @var string
	 */
	private $content_index_table;
	/**
	 * PDF index table name.
	 *
	 * @var string
	 */
	private $pdf_index_table;
	/**
	 * Chat history table name.
	 *
	 * @var string
	 */
	private $chat_history_table;
	/**
	 * Q&A pairs table name.
	 *
	 * @var string
	 */
	private $qa_pairs_table;
	/**
	 * Chat sessions table name.
	 *
	 * @var string
	 */
	private $chat_sessions_table;
	/**
	 * Chat messages table name.
	 *
	 * @var string
	 */
	private $chat_messages_table;
	/**
	 * User agents table name.
	 *
	 * @var string
	 */
	private $user_agents_table;
	/**
	 * Typing indicators table name.
	 *
	 * @var string
	 */
	private $typing_indicators_table;
	/**
	 * Home FAQs table name.
	 *
	 * @var string
	 */
	private $home_faqs_table;
	/**
	 * Home custom links table name.
	 *
	 * @var string
	 */
	private $home_links_table;

	/**
	 * API statistics table name.
	 *
	 * @var string
	 */
	private $api_stats_table;

	/**
	 * Feature requests table name.
	 *
	 * @var string
	 */
	private $feature_requests_table;

	/**
	 * Bug reports table name.
	 *
	 * @var string
	 */
	private $bug_reports_table;

	/**
	 * Embeddings table name for RAG.
	 *
	 * @var string
	 */
	private $embeddings_table;

	/**
	 * Constructor for Database class.
	 *
	 * Initializes the table names and ensures they exist.
	 */
	public function __construct() {
		global $wpdb;
		$this->content_index_table = $wpdb->prefix . 'askany_content_index';
		$this->pdf_index_table = $wpdb->prefix . 'askany_pdf_index';
		$this->chat_history_table = $wpdb->prefix . 'askany_chat_history';
		$this->qa_pairs_table = $wpdb->prefix . 'askany_qa_pairs';
		$this->chat_sessions_table = $wpdb->prefix . 'askany_chat_sessions';
		$this->chat_messages_table = $wpdb->prefix . 'askany_chat_messages';
		$this->user_agents_table = $wpdb->prefix . 'askany_user_agents';
		$this->typing_indicators_table = $wpdb->prefix . 'askany_typing_indicators';
		$this->home_faqs_table = $wpdb->prefix . 'askany_home_faqs';
		$this->home_links_table = $wpdb->prefix . 'askany_home_links';
		$this->api_stats_table = $wpdb->prefix . 'askany_api_stats';
		$this->feature_requests_table = $wpdb->prefix . 'askany_feature_requests';
		$this->bug_reports_table = $wpdb->prefix . 'askany_bug_reports';
		$this->embeddings_table = $wpdb->prefix . 'askany_embeddings';

		// Ensure tables exist
		$this->create_tables();
		
		// Run database migrations
		$this->run_migrations();
	}

	/**
	 * Activate the plugin.
	 *
	 * This method is called when the plugin is activated.
	 * It creates the necessary database tables and schedules the indexing event.
	 *
	 * @return void
	 */
	public function activate() {
		$this->create_tables();
		wp_schedule_single_event(time() + 60, 'askany_index_all_content');
	}

	/**
	 * Deactivate the plugin.
	 *
	 * This method is called when the plugin is deactivated.
	 * It clears the scheduled indexing event.
	 *
	 * @return void
	 */
	public function deactivate() {
		wp_clear_scheduled_hook('askany_index_all_content');
	}

	/**
	 * Create the necessary database tables.
	 *
	 * This method creates the content index, PDF index, chat history,
	 * and Q&A pairs tables if they do not already exist.
	 *
	 * @return void
	 */
	private function create_tables() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset_collate = $wpdb->get_charset_collate();

		// Content index table.
		$sql = "CREATE TABLE IF NOT EXISTS {$this->content_index_table} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            object_id bigint(20) NOT NULL,
            object_type varchar(20) NOT NULL,
            content longtext NOT NULL,
            title varchar(255) NOT NULL,
            url varchar(255) NOT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            UNIQUE KEY object_type_id (object_type, object_id),
            KEY object_id (object_id),
            KEY object_type (object_type)
        ) $charset_collate;";
		dbDelta($sql);

		// PDF index table.
		$sql = "CREATE TABLE IF NOT EXISTS {$this->pdf_index_table} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            file_name varchar(255) NOT NULL,
            file_path varchar(255) NOT NULL,
            content longtext NOT NULL,
            file_size bigint(20) NOT NULL,
            attachment_id bigint(20) NULL DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            last_updated datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY file_name (file_name),
            KEY attachment_id (attachment_id)
        ) $charset_collate;";
		dbDelta($sql);

		// Chat history table.
		$sql = "CREATE TABLE IF NOT EXISTS {$this->chat_history_table} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            message text NOT NULL,
            response text NOT NULL,
            products text,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id)
        ) $charset_collate;";
		dbDelta($sql);

		$sql = "CREATE TABLE IF NOT EXISTS {$this->qa_pairs_table} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            question text NOT NULL,
            answer text NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'active',
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (id)
        ) $charset_collate;";
		dbDelta($sql);

		// Chat sessions table for managing user chat sessions
		$sql = "CREATE TABLE IF NOT EXISTS {$this->chat_sessions_table} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            session_id varchar(255) NOT NULL,
            user_id bigint(20) NULL,
            user_name varchar(255) NULL,
            user_email varchar(255) NULL,
            user_ip varchar(45) NULL,
            status varchar(20) NOT NULL DEFAULT 'active',
            agent_id bigint(20) NULL,
            is_ai_handled tinyint(1) NOT NULL DEFAULT 1,
            last_activity datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            UNIQUE KEY session_id (session_id),
            KEY user_id (user_id),
            KEY agent_id (agent_id),
            KEY status (status),
            KEY last_activity (last_activity)
        ) $charset_collate;";
		dbDelta($sql);

		// Chat messages table for storing individual messages
		$sql = "CREATE TABLE IF NOT EXISTS {$this->chat_messages_table} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            session_id varchar(255) NOT NULL,
            sender_type varchar(20) NOT NULL,
            sender_id bigint(20) NULL,
            message text NOT NULL,
            message_type varchar(20) NOT NULL DEFAULT 'text',
            is_read tinyint(1) NOT NULL DEFAULT 0,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY session_id (session_id),
            KEY sender_type (sender_type),
            KEY sender_id (sender_id),
            KEY created_at (created_at),
            KEY is_read (is_read)
        ) $charset_collate;";
		dbDelta($sql);

		// User agents table for managing chat agents
		$sql = "CREATE TABLE IF NOT EXISTS {$this->user_agents_table} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            user_id bigint(20) NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'active',
            is_online tinyint(1) NOT NULL DEFAULT 0,
            last_seen datetime NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            UNIQUE KEY user_id (user_id),
            KEY status (status),
            KEY is_online (is_online)
        ) $charset_collate;";
		dbDelta($sql);

		// Typing indicators table for real-time typing status
		$sql = "CREATE TABLE IF NOT EXISTS {$this->typing_indicators_table} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            session_id varchar(255) NOT NULL,
            user_id bigint(20) NULL,
            user_type varchar(20) NOT NULL,
            is_typing tinyint(1) NOT NULL DEFAULT 1,
            last_activity datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            UNIQUE KEY session_user (session_id, user_id, user_type),
            KEY session_id (session_id),
            KEY last_activity (last_activity)
        ) $charset_collate;";
		dbDelta($sql);

		// Home FAQs table for home page FAQ section
		$sql = "CREATE TABLE IF NOT EXISTS {$this->home_faqs_table} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            question text NOT NULL,
            answer text NOT NULL,
            display_order int(11) NOT NULL DEFAULT 0,
            is_active tinyint(1) NOT NULL DEFAULT 1,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY is_active (is_active),
            KEY display_order (display_order)
        ) $charset_collate;";
		dbDelta($sql);

		// Home custom links table for home page quick actions
		$sql = "CREATE TABLE IF NOT EXISTS {$this->home_links_table} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            title varchar(255) NOT NULL,
            description text,
            url varchar(500) NOT NULL,
            icon varchar(100),
            display_order int(11) NOT NULL DEFAULT 0,
            is_active tinyint(1) NOT NULL DEFAULT 1,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY is_active (is_active),
            KEY display_order (display_order)
        ) $charset_collate;";
		dbDelta($sql);

		// API statistics table for tracking API performance
		$sql = "CREATE TABLE IF NOT EXISTS {$this->api_stats_table} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            request_type varchar(50) NOT NULL,
            api_provider varchar(20) NOT NULL,
            status varchar(20) NOT NULL,
            response_time decimal(8,3) NULL,
            error_message text NULL,
            query_text text NULL,
            session_id varchar(255) NULL,
            user_id bigint(20) NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY request_type (request_type),
            KEY api_provider (api_provider),
            KEY status (status),
            KEY created_at (created_at),
            KEY session_id (session_id)
        ) $charset_collate;";
		dbDelta($sql);

		// Feature requests table for storing user feature requests
		$sql = "CREATE TABLE IF NOT EXISTS {$this->feature_requests_table} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            unique_id varchar(100) NOT NULL,
            session_id varchar(255) NOT NULL,
            user_name varchar(255) NULL,
            user_email varchar(255) NULL,
            feature_title varchar(500) NOT NULL,
            feature_description text NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'pending',
            priority varchar(20) NOT NULL DEFAULT 'medium',
            notes text NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            UNIQUE KEY unique_id (unique_id),
            KEY session_id (session_id),
            KEY status (status),
            KEY priority (priority),
            KEY created_at (created_at)
        ) $charset_collate;";
		dbDelta($sql);

		// Bug reports table for storing user bug reports
		$sql = "CREATE TABLE IF NOT EXISTS {$this->bug_reports_table} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            unique_id varchar(100) NOT NULL,
            session_id varchar(255) NOT NULL,
            user_name varchar(255) NULL,
            user_email varchar(255) NULL,
            bug_title varchar(500) NOT NULL,
            bug_description text NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'pending',
            severity varchar(20) NOT NULL DEFAULT 'medium',
            notes text NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            UNIQUE KEY unique_id (unique_id),
            KEY session_id (session_id),
            KEY status (status),
            KEY severity (severity),
            KEY created_at (created_at)
        ) $charset_collate;";
		dbDelta($sql);

		// Embeddings table for RAG (Retrieval-Augmented Generation)
		$sql = "CREATE TABLE IF NOT EXISTS {$this->embeddings_table} (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            vector_id varchar(255) NOT NULL,
            content_id bigint(20) NOT NULL,
            content_type varchar(50) NOT NULL,
            embedding longtext NOT NULL,
            embedding_model varchar(100) NOT NULL,
            dimensions int(11) NOT NULL,
            chunk_index int(11) NOT NULL DEFAULT 0,
            total_chunks int(11) NOT NULL DEFAULT 1,
            source_url varchar(500),
            title varchar(500),
            excerpt text,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            UNIQUE KEY vector_id (vector_id),
            KEY content_id (content_id),
            KEY content_type (content_type),
            KEY chunk_index (chunk_index)
        ) $charset_collate;";
		dbDelta($sql);

		// Add embedding columns to content_index table
		$this->add_embedding_columns();

		// Insert default QA pairs.
		$this->insert_default_qa_pairs();
	}

	/**
	 * Save chat history to the database.
	 *
	 * @param string $session_id The session ID.
	 * @param string $query The user's query.
	 * @param mixed  $response The response from the assistant.
	 *
	 * @return bool|int The ID of the inserted row, or false on failure.
	 */
	public function save_chat_history( $session_id, $query, $response ) {
		global $wpdb;
		$user_id = get_current_user_id();

		return $wpdb->insert(
			$this->chat_history_table,
			array(
				'session_id' => $session_id,
				'user_id' => $user_id ? $user_id : null,
				'query' => $query,
				'response' => is_array($response) ? json_encode($response) : $response,
				'created_at' => current_time('mysql'),
			),
			array( '%s', '%d', '%s', '%s', '%s' )
		);
	}

	/**
	 * Get chat history from the database.
	 *
	 * @param int $limit The number of chat history records to retrieve.
	 *
	 * @return array|false An array of chat history records, or false on failure.
	 */
	public function get_chat_history( $limit = 100 ) {
		global $wpdb;
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM $this->chat_history_table ORDER BY created_at DESC LIMIT %d",
				$limit
			)
		);
	}

	/**
	 * Get content index statistics.
	 *
	 * @return array An array of content index statistics.
	 */
	public function get_content_index_stats() {
		global $wpdb;

		// Ensure tables exist before querying.
		$this->create_tables();

		$stats = array(
			'posts' => (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(id) FROM {$this->content_index_table} WHERE object_type = %s",
					'post'
				)
			),
			'comments' => (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(id) FROM {$this->content_index_table} WHERE object_type = %s",
					'comment'
				)
			),
			'products' => (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(id) FROM {$this->content_index_table} WHERE object_type = %s",
					'product'
				)
			),
		);
		return $stats;
	}

	/**
	 * Get PDF index statistics.
	 *
	 * @return array An array of PDF index statistics.
	 */
	public function get_pdf_stats() {
		global $wpdb;

		// Ensure tables exist before querying.
		$this->create_tables();

		return array(
			'total_pdfs' => (int) $wpdb->get_var("SELECT COUNT(*) FROM {$this->pdf_index_table}"),
			'total_size' => (int) $wpdb->get_var("SELECT SUM(LENGTH(content)) FROM {$this->pdf_index_table}"),
			'last_updated' => $wpdb->get_var("SELECT MAX(last_updated) FROM {$this->pdf_index_table}"),
		);
	}

	/**
	 * Get relevant content based on the search query.
	 *
	 * @param string $query The search query.
	 *
	 * @return string The relevant content.
	 */
	public function get_relevant_content( $query ) {
		global $wpdb;

		// Ensure tables exist before querying.
		$this->create_tables();

		$search_terms = explode(' ', $query);
		$valid_terms = array_filter($search_terms, function ( $term ) {
			return strlen($term) > 3;
		});

		if ( empty($valid_terms) ) {
			return '';
		}

		$placeholders = array();
		$sql_parts = array();

		foreach ( $valid_terms as $term ) {
			$sql_parts[] = 'content LIKE %s';
			$placeholders[] = '%' . $wpdb->esc_like($term) . '%';
		}

		$search_condition = '(' . implode(' OR ', $sql_parts) . ')';

		$content_query = $wpdb->prepare(
			"SELECT object_id, object_type, content, title, url 
             FROM {$this->content_index_table} 
             WHERE {$search_condition} 
             ORDER BY updated_at DESC 
             LIMIT 5",
			$placeholders
		);

		$pdf_query = $wpdb->prepare(
			"SELECT content, file_name 
             FROM {$this->pdf_index_table} 
             WHERE {$search_condition} 
             ORDER BY last_updated DESC 
             LIMIT 3",
			$placeholders
		);

		$content_results = $wpdb->get_results($content_query);
		$pdf_results = $wpdb->get_results($pdf_query);

		$relevant_content = '';

		foreach ( $content_results as $result ) {
			$excerpt = $this->extract_relevant_excerpt($result->content, $search_terms);
			$relevant_content .= sprintf(
				"From %s '%s' (%s):\n%s\n\n",
				ucfirst($result->object_type),
				$result->title,
				$result->url,
				$excerpt
			);
		}

		foreach ( $pdf_results as $result ) {
			$excerpt = $this->extract_relevant_excerpt($result->content, $search_terms);
			$relevant_content .= sprintf(
				"From PDF document '%s':\n%s\n\n",
				$result->file_name,
				$excerpt
			);
		}

		return $relevant_content;
	}

	/**
	 * Extract a relevant excerpt from the content.
	 *
	 * @param string $content The content to extract from.
	 * @param array  $search_terms The search terms to look for.
	 *
	 * @return string The extracted excerpt.
	 */
	private function extract_relevant_excerpt( $content, $search_terms ) {
		$content = wp_strip_all_tags($content);
		$max_excerpt_length = 300;

		$pos = -1;
		foreach ( $search_terms as $term ) {
			if ( strlen($term) > 3 ) {
				$term_pos = stripos($content, $term);
				if ( $term_pos !== false && ( $pos === -1 || $term_pos < $pos ) ) {
					$pos = $term_pos;
				}
			}
		}

		if ( $pos === -1 ) {
			return substr($content, 0, $max_excerpt_length) . '...';
		}

		$start = max(0, $pos - 100);
		$excerpt = substr($content, $start, $max_excerpt_length);

		if ( $start > 0 ) {
			$excerpt = '...' . $excerpt;
		}

		if ( strlen($content) > $start + $max_excerpt_length ) {
			$excerpt .= '...';
		}

		return $excerpt;
	}

	/**
	 * Get the names of all tables used by the plugin.
	 *
	 * @return array An array of table names.
	 */
	public function get_tables() {
		return array(
			'content_index' => $this->content_index_table,
			'pdf_index' => $this->pdf_index_table,
			'chat_history' => $this->chat_history_table,
			'qa_pairs' => $this->qa_pairs_table,
			'chat_sessions' => $this->chat_sessions_table,
			'chat_messages' => $this->chat_messages_table,
			'user_agents' => $this->user_agents_table,
			'typing_indicators' => $this->typing_indicators_table,
			'home_faqs' => $this->home_faqs_table,
			'home_links' => $this->home_links_table,
			'api_stats' => $this->api_stats_table,
			'embeddings' => $this->embeddings_table,
			'external_sources' => $this->get_external_sources_table(),
		);
	}

	/**
	 * Get chat sessions table name.
	 *
	 * @return string
	 */
	public function get_sessions_table() {
		return $this->chat_sessions_table;
	}

	/**
	 * Get chat messages table name.
	 *
	 * @return string
	 */
	public function get_messages_table() {
		return $this->chat_messages_table;
	}

	/**
	 * Get Q&A pairs table name.
	 *
	 * @return string
	 */
	public function get_qa_pairs_table() {
		return $this->qa_pairs_table;
	}

	/**
	 * Get external sources table name.
	 *
	 * @return string
	 */
	public function get_external_sources_table() {
		global $wpdb;
		return $wpdb->prefix . 'askany_external_sources';
	}

	/**
	 * Get content index table name.
	 *
	 * @return string
	 */
	public function get_content_index_table() {
		return $this->content_index_table;
	}

	/**
	 * Get PDF index table name.
	 *
	 * @return string
	 */
	public function get_pdf_index_table() {
		return $this->pdf_index_table;
	}

	/**
	 * Get chat history table name.
	 *
	 * @return string
	 */
	public function get_chat_history_table() {
		return $this->chat_history_table;
	}

	/**
	 * Get user agents table name.
	 *
	 * @return string
	 */
	public function get_user_agents_table() {
		return $this->user_agents_table;
	}

	/**
	 * Get typing indicators table name.
	 *
	 * @return string
	 */
	public function get_typing_indicators_table() {
		return $this->typing_indicators_table;
	}

	/**
	 * Get home FAQs table name.
	 *
	 * @return string
	 */
	public function get_home_faqs_table() {
		return $this->home_faqs_table;
	}

	/**
	 * Get home links table name.
	 *
	 * @return string
	 */
	public function get_home_links_table() {
		return $this->home_links_table;
	}

	/**
	 * Get API stats table name.
	 *
	 * @return string
	 */
	public function get_api_stats_table() {
		return $this->api_stats_table;
	}

	/**
	 * Index content into the database.
	 *
	 * @param int    $object_id The ID of the object.
	 * @param string $object_type The type of the object (post, comment, product).
	 * @param string $content The content to index.
	 * @param string $title The title of the object.
	 * @param string $url The URL of the object.
	 *
	 * @return bool|int The ID of the inserted row, or false on failure.
	 */
	public function index_content( $object_id, $object_type, $content, $title = '', $url = '' ) {
		global $wpdb;

		// Ensure tables exist before inserting.
		$this->create_tables();

		$data = array(
			'object_id' => $object_id,
			'object_type' => $object_type,
			'content' => $content,
			'title' => $title,
			'url' => $url,
			'created_at' => current_time('mysql'),
			'updated_at' => current_time('mysql'),
		);

		$format = array( '%d', '%s', '%s', '%s', '%s', '%s', '%s' );

		// Check if content already exists.
		$existing = $wpdb->get_var($wpdb->prepare(
			"SELECT id FROM {$this->content_index_table} 
             WHERE object_id = %d AND object_type = %s",
			$object_id,
			$object_type
		));

		if ( $existing ) {
			// Update existing content.
			return $wpdb->update(
				$this->content_index_table,
				$data,
				array( 'id' => $existing ),
				$format,
				array( '%d' )
			);
		} else {
			// Insert new content.
			return $wpdb->insert(
				$this->content_index_table,
				$data,
				$format
			);
		}
	}

	/**
	 * Index PDF content into the database.
	 *
	 * @param string $file_name The name of the PDF file.
	 * @param string $file_path The path to the PDF file.
	 * @param string $content The content of the PDF file.
	 *
	 * @return bool|int The ID of the inserted row, or false on failure.
	 */
	public function index_pdf( $file_name, $file_path, $content ) {
		global $wpdb;

		// Ensure tables exist before inserting.
		$this->create_tables();

		// Check if content is empty and log.
		if ( empty(trim($content)) ) {
			// error_log('AskAny: Empty content for PDF file: ' . $file_name);
		}

		// Better content handling - ensure there are no nulls or invalid characters.
		$sanitized_content = $this->sanitize_content($content);

		$data = array(
			'file_name' => $file_name,
			'file_path' => $file_path,
			'content' => $sanitized_content,
			'file_size' => filesize($file_path),
			'created_at' => current_time('mysql'),
			'last_updated' => current_time('mysql'),
		);

		$format = array( '%s', '%s', '%s', '%d', '%s', '%s' );

		// Check if PDF already exists.
		$existing = $wpdb->get_var($wpdb->prepare(
			"SELECT id FROM {$this->pdf_index_table} WHERE file_name = %s",
			$file_name
		));

		try {
			if ( $existing ) {
				// Update existing PDF.
				$result = $wpdb->update(
					$this->pdf_index_table,
					$data,
					array( 'id' => $existing ),
					$format,
					array( '%d' )
				);
			} else {
				// Insert new PDF.
				$result = $wpdb->insert(
					$this->pdf_index_table,
					$data,
					$format
				);
			}

			if ( $result === false ) {
				// error_log('AskAny Database Error: ' . $wpdb->last_error);
				return false;
			}

			return $existing ? $existing : $wpdb->insert_id;
		} catch ( \Exception $e ) {
			// error_log('AskAny Exception: ' . $e->getMessage());
			return false;
		}
	}

	/**
	 * Sanitize content before indexing.
	 *
	 * @param string $content The content to sanitize.
	 *
	 * @return string The sanitized content.
	 */
	private function sanitize_content( $content ) {
		if ( empty($content) ) {
			return '';
		}

		// Remove invalid UTF-8 sequences.
		$content = mb_convert_encoding($content, 'UTF-8', 'UTF-8');

		// Replace null bytes.
		$content = str_replace("\0", '', $content);

		// Normalize line endings.
		$content = str_replace([ "\r\n", "\r" ], "\n", $content);

		// Handle special cases for Q&A and Title/Content formats.
		if ( preg_match('/question\s*:/i', $content) || preg_match('/title\s*:/i', $content) ) {
			// Already in a structured format, no need to further structure it.
			return $content;
		}

		// Try to detect unstructured Q&A patterns.
		// This is for PDFs that don't have explicit "Question:" labels but follow a Q&A format.
		if ( preg_match('/(\?)\s+[A-Z]/', $content) ) {
			$lines = explode("\n", $content);
			$formatted = '';
			$in_question = false;
			$current_q = '';
			$current_a = '';

			foreach ( $lines as $line ) {
				$line = trim($line);
				if ( empty($line) ) {
					continue;
				}

				// Check if this line looks like a question.
				if ( substr_count($line, '?') > 0 ) {
					// If we were already in a question/answer, save it.
					if ( ! empty($current_q) ) {
						$formatted .= 'Question: ' . trim($current_q) . "\nAnswer: " . trim($current_a) . "\n\n";
						$current_q = '';
						$current_a = '';
					}
					$current_q = $line;
					$in_question = false;
				} else {
					// This is likely part of an answer.
					if ( ! empty($current_q) ) {
						$current_a .= ' ' . $line;
						$in_question = true;
					} else {
						// If no current question, just add the line as is.
						$formatted .= $line . "\n";
					}
				}
			}

			// Add the last Q&A pair if exists.
			if ( ! empty($current_q) ) {
				$formatted .= 'Question: ' . trim($current_q) . "\nAnswer: " . trim($current_a) . "\n\n";
			}

			if ( ! empty($formatted) ) {
				return $formatted;
			}
		}

		return $content;
	}

	/**
	 * Delete content from the index.
	 *
	 * @param int    $object_id The ID of the object to delete.
	 * @param string $object_type The type of the object (post, comment, product).
	 *
	 * @return bool|int The number of rows deleted, or false on failure.
	 */
	public function delete_content( $object_id, $object_type ) {
		global $wpdb;
		return $wpdb->delete(
			$this->content_index_table,
			array(
				'object_id' => $object_id,
				'object_type' => $object_type,
			),
			array( '%d', '%s' )
		);
	}

	/**
	 * Delete a PDF from the index.
	 *
	 * @param string $file_name The name of the PDF file to delete.
	 *
	 * @return bool|int The number of rows deleted, or false on failure.
	 */
	public function delete_pdf( $file_name ) {
		global $wpdb;
		return $wpdb->delete(
			$this->pdf_index_table,
			array( 'file_name' => $file_name ),
			array( '%s' )
		);
	}

	/**
	 * Add a Q&A pair to the database.
	 *
	 * @param string $question The question.
	 * @param string $answer The answer.
	 *
	 * @return bool|int The ID of the inserted row, or false on failure.
	 */
	public function add_qa_pair( $question, $answer ) {
		global $wpdb;

		return $wpdb->insert(
			$this->qa_pairs_table,
			array(
				'question' => $question,
				'answer' => $answer,
				'status' => 'active',
			),
			array( '%s', '%s', '%s' )
		);
	}

	/**
	 * Update a Q&A pair in the database.
	 *
	 * @param int    $id The ID of the Q&A pair.
	 * @param string $question The question.
	 * @param string $answer The answer.
	 *
	 * @return bool|int The number of rows updated, or false on failure.
	 */
	public function update_qa_pair( $id, $question, $answer ) {
		global $wpdb;

		return $wpdb->update(
			$this->qa_pairs_table,
			array(
				'question' => $question,
				'answer' => $answer,
				'updated_at' => current_time('mysql'),
			),
			array( 'id' => $id ),
			array( '%s', '%s', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Delete a Q&A pair from the database.
	 *
	 * @param int $id The ID of the Q&A pair to delete.
	 *
	 * @return bool|int The number of rows deleted, or false on failure.
	 */
	public function delete_qa_pair( $id ) {
		global $wpdb;

		return $wpdb->delete(
			$this->qa_pairs_table,
			array( 'id' => $id ),
			array( '%d' )
		);
	}

	/**
	 * Get all Q&A pairs from the database.
	 *
	 * @return array|false An array of Q&A pairs, or false on failure.
	 */
	public function get_qa_pairs() {
		global $wpdb;

		return $wpdb->get_results(
			"SELECT * FROM {$this->qa_pairs_table} ORDER BY created_at DESC"
		);
	}

	/**
	 * Get a specific Q&A pair from the database.
	 *
	 * @param int $id The ID of the Q&A pair to retrieve.
	 *
	 * @return object|null The Q&A pair object, or null if not found.
	 */
	public function get_qa_pair( $id ) {
		global $wpdb;

		return $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->qa_pairs_table} WHERE id = %d",
				$id
			)
		);
	}

	/**
	 * Find a matching Q&A pair for a given query using smart contextual matching.
	 *
	 * This method searches for Q&A pairs that match the given query using
	 * advanced contextual understanding, semantic similarity, and intent detection.
	 *
	 * @param string $query The search query.
	 *
	 * @return object|null The matching Q&A pair object, or null if not found.
	 */
	public function find_qa_match( $query ) {
		global $wpdb;

		// Get all active Q&A pairs
		$qa_pairs = $wpdb->get_results(
			"SELECT * FROM {$this->qa_pairs_table} 
			WHERE status = 'active'
			ORDER BY created_at DESC"
		);

		if ( empty($qa_pairs) ) {
			return null;
		}

		$query_normalized = $this->normalize_text($query);
		$best_match = null;
		$highest_score = 0;
		$minimum_threshold = 0.3; // Minimum similarity score to consider a match

		foreach ( $qa_pairs as $qa_pair ) {
			$question_normalized = $this->normalize_text($qa_pair->question);
			
			// Calculate multiple similarity scores
			$scores = array(
				'exact' => $this->calculate_exact_match_score($query_normalized, $question_normalized),
				'semantic' => $this->calculate_semantic_similarity($query_normalized, $question_normalized),
				'intent' => $this->calculate_intent_similarity($query_normalized, $question_normalized),
				'keyword' => $this->calculate_keyword_similarity($query_normalized, $question_normalized),
				'context' => $this->calculate_contextual_similarity($query_normalized, $question_normalized),
			);

			// Weighted scoring system
			$final_score = (
				$scores['exact'] * 0.4 +      // Exact matches get highest weight
				$scores['semantic'] * 0.25 +   // Semantic similarity
				$scores['intent'] * 0.2 +      // Intent matching
				$scores['keyword'] * 0.1 +     // Keyword matching
				$scores['context'] * 0.05      // Contextual clues
			);

			// Boost score for questions with similar structure
			if ( $this->has_similar_structure($query_normalized, $question_normalized) ) {
				$final_score *= 1.2;
			}

			// Penalty for questions that are too different in length
			$length_ratio = min(strlen($query_normalized), strlen($question_normalized)) / 
							max(strlen($query_normalized), strlen($question_normalized));
			if ( $length_ratio < 0.3 ) {
				$final_score *= 0.7;
			}

			if ( $final_score > $highest_score && $final_score >= $minimum_threshold ) {
				$highest_score = $final_score;
				$best_match = $qa_pair;
			}
		}

		return $best_match;
	}

	/**
	 * Insert default Q&A pairs into the database.
	 *
	 * This method inserts a set of default Q&A pairs into the database
	 * if they do not already exist.
	 *
	 * @return void
	 */
	private function insert_default_qa_pairs() {
		global $wpdb;

		if ( get_option('askany_default_qa_added') ) {
			return;
		}

		$table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$this->qa_pairs_table}'") === $this->qa_pairs_table;
		if ( ! $table_exists ) {
			return;
		}

		$qa_pairs = array(
			// Basic greetings.
			'hello' => 'Hello! How can I help you today?',
			'hi' => 'Hi there! How can I assist you?',
			'good morning' => 'Good morning! How can I help you today?',
			'good afternoon' => 'Good afternoon! What can I do for you?',
			'good evening' => 'Good evening! How may I assist you?',
			'who are you' => "I'm your AI shopping assistant, here to help you find products and answer questions about our store.",
			'help' => "I'd be happy to help! Please let me know what you're looking for or what questions you have about our products.",

			// Store information.
			'working hours' => 'Our working hours are from 9 AM to 6 PM, Monday to Friday, and from 10 AM to 4 PM on weekends.',
			
			// Ordering and shipping.
			'return policy' => 'We offer a 15-day return policy for most items. Please check our Returns page for detailed information.',
			'refund' => 'Refunds are processed within 5-7 business days after we receive your returned item.',

			// Website support.
			'customer support' => 'Our support team is available 24/7 via chat and email at support@example.com.',
		);

		$existing_count = $wpdb->get_var("SELECT COUNT(*) FROM {$this->qa_pairs_table}");

		if ( $existing_count > 0 ) {
			update_option('askany_default_qa_added', true);
			return;
		}

		foreach ( $qa_pairs as $question => $answer ) {
			$exists = $wpdb->get_var($wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->qa_pairs_table} WHERE LOWER(question) = %s",
				strtolower($question)
			));

			if ( ! $exists ) {
				$wpdb->insert(
					$this->qa_pairs_table,
					array(
						'question' => $question,
						'answer' => $answer,
						'status' => 'active',
						'created_at' => current_time('mysql'),
						'updated_at' => current_time('mysql'),
					),
					array( '%s', '%s', '%s', '%s', '%s' )
				);
			}
		}

		update_option('askany_default_qa_added', true);
	}

	/**
	 * Clear the content index.
	 *
	 * @return bool|int The number of rows deleted, or false on failure.
	 */
	public function clear_content_index() {
		global $wpdb;
		return $wpdb->query("TRUNCATE TABLE {$this->content_index_table}");
	}

	/**
	 * Clear the PDF index.
	 *
	 * @return bool|int The number of rows deleted, or false on failure.
	 */
	public function clear_pdf_index() {
		global $wpdb;
		return $wpdb->query("TRUNCATE TABLE {$this->pdf_index_table}");
	}

	/**
	 * Clear the chat history.
	 *
	 * @return bool|int The number of rows deleted, or false on failure.
	 */
	public function clear_chat_history() {
		global $wpdb;
		return $wpdb->query("TRUNCATE TABLE {$this->chat_history_table}");
	}

	// ===== CHAT SESSION MANAGEMENT =====

	/**
	 * Create or get a chat session.
	 *
	 * @param string $session_id The session ID.
	 * @param array  $user_data Optional user data.
	 *
	 * @return int|false The session ID or false on failure.
	 */
	public function create_chat_session( $session_id, $user_data = array() ) {
		global $wpdb;

		$user_id = get_current_user_id();
		$user_ip = '';
		if (isset($_SERVER['REMOTE_ADDR'])) {
			$user_ip = sanitize_text_field(wp_unslash($_SERVER['REMOTE_ADDR']));
		}

		// For logged-in users, check if they have an existing session
		if ( $user_id && strpos($session_id, 'user_') === 0 ) {
			// Check for existing user session
			$existing = $wpdb->get_var($wpdb->prepare(
				"SELECT id FROM {$this->chat_sessions_table} WHERE user_id = %d AND status = 'active' ORDER BY last_activity DESC LIMIT 1",
				$user_id
			));

			if ( $existing ) {
				// Update existing session with new session_id if different
				$current_session = $wpdb->get_row($wpdb->prepare(
					"SELECT session_id FROM {$this->chat_sessions_table} WHERE id = %d",
					$existing
				));

				if ( $current_session && $current_session->session_id !== $session_id ) {
					// Update session_id and last_activity
					$wpdb->update(
						$this->chat_sessions_table,
						array(
							'session_id' => $session_id,
							'last_activity' => current_time('mysql'),
						),
						array( 'id' => $existing ),
						array( '%s', '%s' ),
						array( '%d' )
					);
				} else {
					// Just update last activity
					$wpdb->update(
						$this->chat_sessions_table,
						array( 'last_activity' => current_time('mysql') ),
						array( 'id' => $existing ),
						array( '%s' ),
						array( '%d' )
					);
				}
				return $existing;
			}
		} else {
			// Check if session already exists (for anonymous users)
			$existing = $wpdb->get_var($wpdb->prepare(
				"SELECT id FROM {$this->chat_sessions_table} WHERE session_id = %s",
				$session_id
			));

			if ( $existing ) {
				// Update last activity and user info if user logged in
				$update_data = array( 'last_activity' => current_time('mysql') );

				if ( $user_id ) {
					$update_data['user_id'] = $user_id;
				}

				$wpdb->update(
					$this->chat_sessions_table,
					$update_data,
					array( 'id' => $existing ),
					array( '%s', '%d' ),
					array( '%d' )
				);
				return $existing;
			}
		}

		// Create new session
		$data = array(
			'session_id' => $session_id,
			'user_id' => $user_id ?: null,
			'user_name' => $user_data['name'] ?? '',
			'user_email' => $user_data['email'] ?? '',
			'user_ip' => $user_ip,
			'status' => 'active',
			'is_ai_handled' => 1, // Default to AI handled for new sessions
			'agent_id' => null, // No agent assigned by default
			'last_activity' => current_time('mysql'),
			'created_at' => current_time('mysql'),
		);

		$result = $wpdb->insert( $this->chat_sessions_table, $data );
		return $result ? $wpdb->insert_id : false;
	}

	/**
	 * Merge anonymous session with user account when user logs in.
	 *
	 * @param string $session_id The session ID.
	 * @param int    $user_id The user ID.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function merge_session_with_user( $session_id, $user_id ) {
		global $wpdb;

		// Check if user already has existing sessions
		$existing_user_sessions = $wpdb->get_results($wpdb->prepare(
			"SELECT session_id FROM {$this->chat_sessions_table} WHERE user_id = %d AND session_id != %s",
			$user_id,
			$session_id
		));

		// Update the current session with user ID
		$result = $wpdb->update(
			$this->chat_sessions_table,
			array( 'user_id' => $user_id ),
			array( 'session_id' => $session_id ),
			array( '%d' ),
			array( '%s' )
		);

		// Update all messages in this session to associate with user
		$wpdb->update(
			$this->chat_messages_table,
			array( 'sender_id' => $user_id ),
			array(
				'session_id' => $session_id,
				'sender_type' => 'user',
			),
			array( '%d' ),
			array( '%s', '%s' )
		);

		return $result !== false;
	}

	/**
	 * Get user's chat sessions.
	 *
	 * @param int $user_id The user ID.
	 *
	 * @return array|false Array of sessions or false on failure.
	 */
	public function get_user_chat_sessions( $user_id ) {
		global $wpdb;

		return $wpdb->get_results($wpdb->prepare(
			"SELECT * FROM {$this->chat_sessions_table} 
			 WHERE user_id = %d 
			 ORDER BY last_activity DESC",
			$user_id
		));
	}

	/**
	 * Get all active chat sessions with last message preview.
	 * Used by the mobile app REST endpoint.
	 *
	 * @return array|false Array of chat sessions or false on failure.
	 */
	public function get_active_chat_sessions_with_preview() {
		global $wpdb;

		return $wpdb->get_results(
			"SELECT s.*, u.display_name as user_display_name,
			        COUNT(m.id) as message_count,
			        MAX(m.created_at) as last_message_time,
			        (
			            SELECT m2.message FROM {$this->chat_messages_table} m2
			            WHERE m2.session_id = s.session_id
			            ORDER BY m2.created_at DESC LIMIT 1
			        ) as last_message
			 FROM {$this->chat_sessions_table} s
			 LEFT JOIN {$wpdb->users} u ON s.user_id = u.ID
			 LEFT JOIN {$this->chat_messages_table} m ON s.session_id = m.session_id
			 WHERE s.status = 'active'
			 GROUP BY s.id
			 ORDER BY s.last_activity DESC"
		);
	}

	/**
	 * Get all active chat sessions.
	 *
	 * @return array|false Array of chat sessions or false on failure.
	 */
	public function get_active_chat_sessions() {
		global $wpdb;

		return $wpdb->get_results(
			"SELECT s.*, u.display_name as user_display_name, 
			        COUNT(m.id) as message_count,
			        MAX(m.created_at) as last_message_time
			 FROM {$this->chat_sessions_table} s
			 LEFT JOIN {$wpdb->users} u ON s.user_id = u.ID
			 LEFT JOIN {$this->chat_messages_table} m ON s.session_id = m.session_id
			 WHERE s.status = 'active'
			 GROUP BY s.id
			 ORDER BY s.last_activity DESC"
		);
	}

	/**
	 * Get chat session by session ID.
	 *
	 * @param string $session_id The session ID.
	 *
	 * @return object|null The session object or null if not found.
	 */
	public function get_chat_session( $session_id ) {
		global $wpdb;

		return $wpdb->get_row($wpdb->prepare(
			"SELECT s.*, u.display_name as user_display_name
			 FROM {$this->chat_sessions_table} s
			 LEFT JOIN {$wpdb->users} u ON s.user_id = u.ID
			 WHERE s.session_id = %s",
			$session_id
		));
	}

	/**
	 * Assign agent to chat session.
	 *
	 * @param string $session_id The session ID.
	 * @param int    $agent_id The agent user ID.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function assign_agent_to_session( $session_id, $agent_id ) {
		global $wpdb;

		return $wpdb->update(
			$this->chat_sessions_table,
			array(
				'agent_id' => $agent_id,
				'is_ai_handled' => 0,
			),
			array( 'session_id' => $session_id ),
			array( '%d', '%d' ),
			array( '%s' )
		) !== false;
	}

	/**
	 * Release agent from chat session (back to AI).
	 *
	 * @param string $session_id The session ID.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function release_agent_from_session( $session_id ) {
		global $wpdb;

		return $wpdb->update(
			$this->chat_sessions_table,
			array(
				'agent_id' => null,
				'is_ai_handled' => 1,
			),
			array( 'session_id' => $session_id ),
			array( '%d', '%d' ),
			array( '%s' )
		) !== false;
	}

	// ===== CHAT MESSAGES MANAGEMENT =====

	/**
	 * Save chat message.
	 *
	 * @param string $session_id The session ID.
	 * @param string $sender_type The sender type (user, agent, ai).
	 * @param int    $sender_id The sender ID (user ID for user/agent).
	 * @param string $message The message content.
	 * @param string $message_type The message type (text, image, file).
	 *
	 * @return int|false The message ID or false on failure.
	 */
	public function save_chat_message( $session_id, $sender_type, $sender_id, $message, $message_type = 'text' ) {
		global $wpdb;

		$result = $wpdb->insert(
			$this->chat_messages_table,
			array(
				'session_id' => $session_id,
				'sender_type' => $sender_type,
				'sender_id' => $sender_id,
				'message' => $message,
				'message_type' => $message_type,
				'created_at' => current_time('mysql'),
			),
			array( '%s', '%s', '%d', '%s', '%s', '%s' )
		);

		if ( $result ) {
			$message_id = $wpdb->insert_id;
			
			// Update session last activity
			$wpdb->update(
				$this->chat_sessions_table,
				array( 'last_activity' => current_time('mysql') ),
				array( 'session_id' => $session_id ),
				array( '%s' ),
				array( '%s' )
			);

			// Get user/agent data for Slack notifications
			$user_data = array();
			if ( $sender_id > 0 ) {
				$user = get_user_by( 'id', $sender_id );
				if ( $user ) {
					$user_data = array(
						'id' => $user->ID,
						'name' => (string) $user->display_name,
						'display_name' => (string) $user->display_name,
						'email' => (string) $user->user_email,
					);
				}
			} else {
				// Get session user data for anonymous users
				$session = $this->get_chat_session( $session_id );
				if ( $session ) {
					$user_data = array(
						'name' => (string) ( $session->user_name ?: $session->user_display_name ?: __( 'Anonymous User', 'askany' ) ),
						'display_name' => (string) ( $session->user_display_name ?: $session->user_name ?: __( 'Anonymous User', 'askany' ) ),
						'email' => (string) ( $session->user_email ?: '' ),
					);
				}
			}

			// Trigger specific hooks for Slack notifications
			if ( $sender_type === 'user' ) {
				do_action( 'askany_user_message_saved', $session_id, $message, $user_data );
			} elseif ( $sender_type === 'agent' ) {
				do_action( 'askany_agent_message_saved', $session_id, $message, $user_data );
			} elseif ( $sender_type === 'assistant' ) {
				do_action( 'askany_ai_message_saved', $session_id, $message, array() );
			}

			// Trigger general hook for SSE broadcasting
			do_action( 'askany_message_saved', $session_id, $sender_type, array(
				'id' => $message_id,
				'message' => $message,
				'sender_type' => $sender_type,
				'sender_id' => $sender_id,
				'message_type' => $message_type,
				'created_at' => current_time('mysql')
			) );

			return $message_id;
		}

		return false;
	}

	/**
	 * Get chat messages for a session.
	 *
	 * @param string $session_id The session ID.
	 * @param int    $limit The number of messages to retrieve.
	 *
	 * @return array|false Array of messages or false on failure.
	 */
	public function get_chat_messages( $session_id, $limit = 100 ) {
		global $wpdb;

		return $wpdb->get_results($wpdb->prepare(
			"SELECT m.*, u.display_name as sender_name, u.user_email as sender_email
			 FROM {$this->chat_messages_table} m
			 LEFT JOIN {$wpdb->users} u ON m.sender_id = u.ID
			 WHERE m.session_id = %s
			 ORDER BY m.created_at ASC
			 LIMIT %d",
			$session_id,
			$limit
		));
	}

	/**
	 * Mark messages as read.
	 *
	 * @param string $session_id The session ID.
	 * @param string $sender_type Optional sender type to mark as read.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function mark_messages_as_read( $session_id, $sender_type = null ) {
		global $wpdb;

		$where = array( 'session_id' => $session_id );
		$where_format = array( '%s' );

		if ( $sender_type ) {
			$where['sender_type'] = $sender_type;
			$where_format[] = '%s';
		}

		return $wpdb->update(
			$this->chat_messages_table,
			array( 'is_read' => 1 ),
			$where,
			array( '%d' ),
			$where_format
		) !== false;
	}

	/**
	 * Get unread message count for a session.
	 *
	 * @param string $session_id The session ID.
	 * @param string $sender_type Optional sender type to count (e.g., 'user' for user messages only).
	 *
	 * @return int Number of unread messages.
	 */
	public function get_unread_message_count( $session_id, $sender_type = null ) {
		global $wpdb;

		$sql = "SELECT COUNT(*) FROM {$this->chat_messages_table} WHERE session_id = %s AND is_read = 0";
		$params = array( $session_id );

		if ( $sender_type ) {
			$sql .= " AND sender_type = %s";
			$params[] = $sender_type;
		}

		return (int) $wpdb->get_var( $wpdb->prepare( $sql, $params ) );
	}

	/**
	 * Get total unread message count across all sessions.
	 *
	 * @param string $sender_type Optional sender type to count (e.g., 'user' for user messages only).
	 *
	 * @return int Total number of unread messages.
	 */
	public function get_total_unread_message_count( $sender_type = null ) {
		global $wpdb;

		$sql = "SELECT COUNT(*) FROM {$this->chat_messages_table} WHERE is_read = 0";
		$params = array();

		if ( $sender_type ) {
			$sql .= " AND sender_type = %s";
			$params[] = $sender_type;
		}

		if ( empty( $params ) ) {
			return (int) $wpdb->get_var( $sql );
		}

		return (int) $wpdb->get_var( $wpdb->prepare( $sql, $params ) );
	}

	/**
	 * Mark all messages in a session as read when admin views them.
	 *
	 * @param string $session_id The session ID.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function mark_session_messages_as_read( $session_id ) {
		global $wpdb;

		return $wpdb->update(
			$this->chat_messages_table,
			array( 'is_read' => 1 ),
			array( 'session_id' => $session_id ),
			array( '%d' ),
			array( '%s' )
		) !== false;
	}

	// ===== USER AGENTS MANAGEMENT =====

	/**
	 * Add user as agent.
	 *
	 * @param int $user_id The user ID.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function add_user_agent( $user_id ) {
		global $wpdb;

		// Check if user is already an agent
		$existing = $wpdb->get_var($wpdb->prepare(
			"SELECT id FROM {$this->user_agents_table} WHERE user_id = %d",
			$user_id
		));

		if ( $existing ) {
			// Reactivate if inactive
			return $wpdb->update(
				$this->user_agents_table,
				array( 'status' => 'active' ),
				array( 'id' => $existing ),
				array( '%s' ),
				array( '%d' )
			) !== false;
		}

		return $wpdb->insert(
			$this->user_agents_table,
			array(
				'user_id' => $user_id,
				'status' => 'active',
				'created_at' => current_time('mysql'),
			),
			array( '%d', '%s', '%s' )
		) !== false;
	}

	/**
	 * Remove user as agent.
	 *
	 * @param int $user_id The user ID.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function remove_user_agent( $user_id ) {
		global $wpdb;

		return $wpdb->update(
			$this->user_agents_table,
			array( 'status' => 'inactive' ),
			array( 'user_id' => $user_id ),
			array( '%s' ),
			array( '%d' )
		) !== false;
	}

	/**
	 * Get all agents.
	 *
	 * @return array|false Array of agents or false on failure.
	 */
	public function get_agents() {
		global $wpdb;

		return $wpdb->get_results(
			"SELECT a.*, u.display_name, u.user_email, u.user_login
			 FROM {$this->user_agents_table} a
			 JOIN {$wpdb->users} u ON a.user_id = u.ID
			 WHERE a.status = 'active'
			 ORDER BY a.created_at DESC"
		);
	}

	/**
	 * Check if user is an agent.
	 *
	 * @param int $user_id The user ID.
	 *
	 * @return bool True if user is an agent, false otherwise.
	 */
	public function is_user_agent( $user_id ) {
		global $wpdb;

		$result = $wpdb->get_var($wpdb->prepare(
			"SELECT COUNT(*) FROM {$this->user_agents_table} WHERE user_id = %d AND status = 'active'",
			$user_id
		));

		return $result > 0;
	}

	/**
	 * Update agent online status.
	 *
	 * @param int  $user_id The user ID.
	 * @param bool $is_online Online status.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function update_agent_status( $user_id, $is_online ) {
		global $wpdb;

		return $wpdb->update(
			$this->user_agents_table,
			array(
				'is_online' => $is_online ? 1 : 0,
				'last_seen' => current_time('mysql'),
			),
			array( 'user_id' => $user_id ),
			array( '%d', '%s' ),
			array( '%d' )
		) !== false;
	}

	/**
	 * Get all registered users for agent selection.
	 *
	 * @return array|false Array of users or false on failure.
	 */
	public function get_all_users() {
		global $wpdb;

		return $wpdb->get_results(
			"SELECT u.ID, u.display_name, u.user_email, u.user_login,
			        CASE WHEN a.user_id IS NOT NULL THEN 1 ELSE 0 END as is_agent
			 FROM {$wpdb->users} u
			 LEFT JOIN {$this->user_agents_table} a ON u.ID = a.user_id AND a.status = 'active'
			 ORDER BY u.display_name ASC"
		);
	}

	/**
	 * Clear the Q&A pairs.
	 *
	 * @return bool|int The number of rows deleted, or false on failure.
	 */
	public function drop_tables() {
		global $wpdb;

		// Drop all tables created by this plugin.
		$wpdb->query("DROP TABLE IF EXISTS {$this->content_index_table}");
		$wpdb->query("DROP TABLE IF EXISTS {$this->pdf_index_table}");
		$wpdb->query("DROP TABLE IF EXISTS {$this->chat_history_table}");
		$wpdb->query("DROP TABLE IF EXISTS {$this->qa_pairs_table}");
		$wpdb->query("DROP TABLE IF EXISTS {$this->chat_sessions_table}");
		$wpdb->query("DROP TABLE IF EXISTS {$this->chat_messages_table}");
		$wpdb->query("DROP TABLE IF EXISTS {$this->user_agents_table}");

		// Delete related options.
		delete_option('askany_default_qa_added');

		return true;
	}

	// ===== TYPING INDICATORS MANAGEMENT =====

	/**
	 * Set typing indicator for a user in a session.
	 *
	 * @param string $session_id The session ID.
	 * @param int    $user_id The user ID (null for anonymous).
	 * @param string $user_type The user type (user, agent, ai).
	 * @param bool   $is_typing Whether the user is typing.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function set_typing_indicator( $session_id, $user_id, $user_type, $is_typing = true ) {
		global $wpdb;

		if ( $is_typing ) {
			// Insert or update typing indicator
			$result = $wpdb->replace(
				$this->typing_indicators_table,
				array(
					'session_id' => $session_id,
					'user_id' => $user_id,
					'user_type' => $user_type,
					'is_typing' => 1,
					'last_activity' => current_time('mysql'),
					'created_at' => current_time('mysql'),
				),
				array( '%s', '%d', '%s', '%d', '%s', '%s' )
			);
		} else {
			// Remove typing indicator
			$result = $wpdb->delete(
				$this->typing_indicators_table,
				array(
					'session_id' => $session_id,
					'user_id' => $user_id,
					'user_type' => $user_type,
				),
				array( '%s', '%d', '%s' )
			);
		}

		return $result !== false;
	}

	/**
	 * Get typing indicators for a session.
	 *
	 * @param string $session_id The session ID.
	 *
	 * @return array|false Array of typing indicators or false on failure.
	 */
	public function get_typing_indicators( $session_id ) {
		global $wpdb;

		// Clean up old typing indicators (older than 10 seconds)
		$cleanup_time = date('Y-m-d H:i:s', strtotime('-10 seconds'));
		$wpdb->query($wpdb->prepare(
			"DELETE FROM {$this->typing_indicators_table} WHERE last_activity < %s",
			$cleanup_time
		));

		return $wpdb->get_results($wpdb->prepare(
			"SELECT t.*, 
			        CASE 
			            WHEN t.user_type = 'agent' AND u.display_name IS NOT NULL THEN CONCAT(u.display_name, ' (Agent)')
			            WHEN t.user_type = 'agent' AND u.display_name IS NULL THEN 'Agent'
			            WHEN u.display_name IS NOT NULL THEN u.display_name
			            WHEN s.user_name IS NOT NULL THEN s.user_name
			            ELSE 'Anonymous User'
			        END as user_name
			 FROM {$this->typing_indicators_table} t
			 LEFT JOIN {$wpdb->users} u ON t.user_id = u.ID AND t.user_id > 0
			 LEFT JOIN {$this->chat_sessions_table} s ON t.session_id = s.session_id
			 WHERE t.session_id = %s AND t.is_typing = 1
			 ORDER BY t.last_activity DESC",
			$session_id
		));
	}

	/**
	 * Clean up old typing indicators.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function cleanup_typing_indicators() {
		global $wpdb;

		return $wpdb->delete(
			$this->typing_indicators_table,
			array( 'last_activity <' => date('Y-m-d H:i:s', strtotime('-30 seconds')) ),
			array( '%s' )
		) !== false;
	}

	/**
	 * Update session user name for anonymous users.
	 *
	 * @param string $session_id The session ID.
	 * @param string $user_name The user name to set.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function update_session_user_name( $session_id, $user_name ) {
		global $wpdb;

		$result = $wpdb->update(
			$this->chat_sessions_table,
			array( 'user_name' => $user_name ),
			array( 'session_id' => $session_id ),
			array( '%s' ),
			array( '%s' )
		);

		return $result !== false;
	}

	/**
	 * Get real-time updates for a session.
	 *
	 * @param string $session_id The session ID.
	 * @param string $last_message_time Last message timestamp.
	 *
	 * @return array Array containing new messages and typing indicators.
	 */
	public function get_realtime_updates( $session_id, $last_message_time = null ) {
		// Debug: Log what we're getting
		// error_log('get_realtime_updates - session_id: ' . $session_id . ', last_message_time: ' . $last_message_time);

		$updates = array(
			'messages' => array(),
			'typing_indicators' => array(),
			'session_info' => null,
			'debug_info' => array(
				'session_id' => $session_id,
				'last_message_time' => $last_message_time,
				'current_time' => current_time('mysql'),
				'php_time' => date('Y-m-d H:i:s'),
			),
		);

		try {
			// Get new messages since last check
			if ( $last_message_time ) {
				$updates['messages'] = $this->get_new_messages_since( $session_id, $last_message_time );
			} else {
				// If no last_message_time, get messages from last 5 minutes
				$five_minutes_ago = date('Y-m-d H:i:s', strtotime('-5 minutes'));
				$updates['messages'] = $this->get_new_messages_since( $session_id, $five_minutes_ago );
			}

			// Get typing indicators
			$updates['typing_indicators'] = $this->get_typing_indicators( $session_id );

			// Get updated session info
			$updates['session_info'] = $this->get_chat_session( $session_id );
		} catch ( Exception $e ) {
			// error_log('get_realtime_updates - Error: ' . $e->getMessage());
			$updates['error'] = $e->getMessage();
		}

		return $updates;
	}

	/**
	 * Get new messages since a specific timestamp.
	 *
	 * @param string $session_id The session ID.
	 * @param string $since_time The timestamp to check from.
	 *
	 * @return array|false Array of new messages or false on failure.
	 */
	public function get_new_messages_since( $session_id, $since_time ) {
		global $wpdb;

		// Debug: Log what we're querying
		// error_log('get_new_messages_since - session_id: ' . $session_id . ', since_time: ' . $since_time);

		// Validate inputs to prevent SQL errors
		if ( empty($session_id) ) {
			// error_log('get_new_messages_since - Empty session_id provided');
			return array();
		}

		// Handle invalid date format
		if ( ! empty($since_time) && ! strtotime($since_time) ) {
			// error_log('get_new_messages_since - Invalid date format: ' . $since_time);
			$since_time = date('Y-m-d H:i:s', strtotime('-5 minutes'));
		}

		// If since_time is empty, get messages from last 5 minutes
		if ( empty($since_time) ) {
			$since_time = date('Y-m-d H:i:s', strtotime('-5 minutes'));
		}

		$query = $wpdb->prepare(
			"SELECT m.*, u.display_name as sender_name, u.user_email as sender_email
			 FROM {$this->chat_messages_table} m
			 LEFT JOIN {$wpdb->users} u ON m.sender_id = u.ID
			 WHERE m.session_id = %s AND m.created_at > %s
			 ORDER BY m.created_at ASC",
			$session_id,
			$since_time
		);

		// Debug: Log the query
		// error_log('get_new_messages_since - Query: ' . $query);

		$results = $wpdb->get_results($query);

		// Debug: Log the results
		// error_log('get_new_messages_since - Found ' . count($results) . ' messages');

		return $results;
	}



	// ===== HOME PAGE FEATURES =====

	/**
	 * Get active FAQs for home page.
	 *
	 * @return array|false Array of FAQ objects or false on failure.
	 */
	public function get_home_faqs() {
		global $wpdb;

		return $wpdb->get_results(
			"SELECT * FROM {$this->home_faqs_table} 
			WHERE is_active = 1 
			ORDER BY display_order ASC, created_at ASC"
		);
	}

	/**
	 * Add a new FAQ for home page.
	 *
	 * @param string $question The FAQ question.
	 * @param string $answer The FAQ answer.
	 * @param int    $display_order Display order (optional).
	 *
	 * @return bool|int The ID of the inserted row, or false on failure.
	 */
	public function add_home_faq( $question, $answer, $display_order = 0 ) {
		global $wpdb;

		return $wpdb->insert(
			$this->home_faqs_table,
			array(
				'question' => $question,
				'answer' => $answer,
				'display_order' => $display_order,
				'is_active' => 1,
			),
			array( '%s', '%s', '%d', '%d' )
		);
	}

	/**
	 * Update a home page FAQ.
	 *
	 * @param int    $id The FAQ ID.
	 * @param string $question The FAQ question.
	 * @param string $answer The FAQ answer.
	 * @param int    $display_order Display order (optional).
	 *
	 * @return bool|int The number of rows updated, or false on failure.
	 */
	public function update_home_faq( $id, $question, $answer, $display_order = 0 ) {
		global $wpdb;

		return $wpdb->update(
			$this->home_faqs_table,
			array(
				'question' => $question,
				'answer' => $answer,
				'display_order' => $display_order,
			),
			array( 'id' => $id ),
			array( '%s', '%s', '%d' ),
			array( '%d' )
		);
	}

	/**
	 * Delete a home page FAQ.
	 *
	 * @param int $id The FAQ ID.
	 *
	 * @return bool|int The number of rows deleted, or false on failure.
	 */
	public function delete_home_faq( $id ) {
		global $wpdb;

		return $wpdb->delete(
			$this->home_faqs_table,
			array( 'id' => $id ),
			array( '%d' )
		);
	}

	/**
	 * Toggle FAQ active status.
	 *
	 * @param int $id The FAQ ID.
	 * @param int $is_active Active status (1 or 0).
	 *
	 * @return bool|int The number of rows updated, or false on failure.
	 */
	public function toggle_home_faq_status( $id, $is_active ) {
		global $wpdb;

		return $wpdb->update(
			$this->home_faqs_table,
			array( 'is_active' => $is_active ),
			array( 'id' => $id ),
			array( '%d' ),
			array( '%d' )
		);
	}

	/**
	 * Get active custom links for home page.
	 *
	 * @return array|false Array of link objects or false on failure.
	 */
	public function get_home_links() {
		global $wpdb;

		return $wpdb->get_results(
			"SELECT * FROM {$this->home_links_table} 
			WHERE is_active = 1 
			ORDER BY display_order ASC, created_at ASC"
		);
	}

	/**
	 * Add a new custom link for home page.
	 *
	 * @param string $title The link title.
	 * @param string $description The link description.
	 * @param string $url The link URL.
	 * @param string $icon The link icon (optional).
	 * @param int    $display_order Display order (optional).
	 *
	 * @return bool|int The ID of the inserted row, or false on failure.
	 */
	public function add_home_link( $title, $description, $url, $icon = '', $display_order = 0 ) {
		global $wpdb;

		return $wpdb->insert(
			$this->home_links_table,
			array(
				'title' => $title,
				'description' => $description,
				'url' => $url,
				'icon' => $icon,
				'display_order' => $display_order,
				'is_active' => 1,
			),
			array( '%s', '%s', '%s', '%s', '%d', '%d' )
		);
	}

	/**
	 * Update a home page custom link.
	 *
	 * @param int    $id The link ID.
	 * @param string $title The link title.
	 * @param string $description The link description.
	 * @param string $url The link URL.
	 * @param string $icon The link icon (optional).
	 * @param int    $display_order Display order (optional).
	 *
	 * @return bool|int The number of rows updated, or false on failure.
	 */
	public function update_home_link( $id, $title, $description, $url, $icon = '', $display_order = 0 ) {
		global $wpdb;

		return $wpdb->update(
			$this->home_links_table,
			array(
				'title' => $title,
				'description' => $description,
				'url' => $url,
				'icon' => $icon,
				'display_order' => $display_order,
			),
			array( 'id' => $id ),
			array( '%s', '%s', '%s', '%s', '%d' ),
			array( '%d' )
		);
	}

	/**
	 * Delete a home page custom link.
	 *
	 * @param int $id The link ID.
	 *
	 * @return bool|int The number of rows deleted, or false on failure.
	 */
	public function delete_home_link( $id ) {
		global $wpdb;

		return $wpdb->delete(
			$this->home_links_table,
			array( 'id' => $id ),
			array( '%d' )
		);
	}

	/**
	 * Toggle custom link active status.
	 *
	 * @param int $id The link ID.
	 * @param int $is_active Active status (1 or 0).
	 *
	 * @return bool|int The number of rows updated, or false on failure.
	 */
	public function toggle_home_link_status( $id, $is_active ) {
		global $wpdb;

		return $wpdb->update(
			$this->home_links_table,
			array( 'is_active' => $is_active ),
			array( 'id' => $id ),
			array( '%d' ),
			array( '%d' )
		);
	}

	/**
	 * Get all home page FAQs (including inactive ones) for admin management.
	 *
	 * @return array|false Array of FAQ objects or false on failure.
	 */
	public function get_all_home_faqs() {
		global $wpdb;

		return $wpdb->get_results(
			"SELECT * FROM {$this->home_faqs_table} 
			ORDER BY display_order ASC, created_at DESC"
		);
	}

	/**
	 * Get all home page custom links (including inactive ones) for admin management.
	 *
	 * @return array|false Array of link objects or false on failure.
	 */
	public function get_all_home_links() {
		global $wpdb;

		return $wpdb->get_results(
			"SELECT * FROM {$this->home_links_table} 
			ORDER BY display_order ASC, created_at DESC"
		);
	}

	/**
	 * Get a specific home page FAQ.
	 *
	 * @param int $id The FAQ ID.
	 *
	 * @return object|null The FAQ object or null if not found.
	 */
	public function get_home_faq( $id ) {
		global $wpdb;

		return $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->home_faqs_table} WHERE id = %d",
				$id
			)
		);
	}

	/**
	 * Get a specific home page custom link.
	 *
	 * @param int $id The link ID.
	 *
	 * @return object|null The link object or null if not found.
	 */
	public function get_home_link( $id ) {
		global $wpdb;

		return $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->home_links_table} WHERE id = %d",
				$id
			)
		);
	}

	/**
	 * Clear all messages for a specific session.
	 *
	 * @param string $session_id The session ID.
	 *
	 * @return bool|int The number of rows deleted, or false on failure.
	 */
	public function clear_session_messages( $session_id ) {
		global $wpdb;

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

		// Delete all messages for this session
		$result = $wpdb->delete(
			$this->chat_messages_table,
			array( 'session_id' => $session_id ),
			array( '%s' )
		);

		// Also clear typing indicators for this session
		$wpdb->delete(
			$this->typing_indicators_table,
			array( 'session_id' => $session_id ),
			array( '%s' )
		);

		return $result;
	}

	/**
	 * Delete a chat session and all associated data.
	 *
	 * @param string $session_id The session ID.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function delete_chat_session( $session_id ) {
		global $wpdb;

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

		// Start transaction
		$wpdb->query('START TRANSACTION');

		try {
			// Delete all messages for this session
			$messages_deleted = $wpdb->delete(
				$this->chat_messages_table,
				array( 'session_id' => $session_id ),
				array( '%s' )
			);

			// Delete typing indicators for this session
			$wpdb->delete(
				$this->typing_indicators_table,
				array( 'session_id' => $session_id ),
				array( '%s' )
			);

			// Delete the session itself
			$session_deleted = $wpdb->delete(
				$this->chat_sessions_table,
				array( 'session_id' => $session_id ),
				array( '%s' )
			);

			// Check if both operations were successful
			if ( $session_deleted !== false ) {
				$wpdb->query('COMMIT');
				return true;
			} else {
				$wpdb->query('ROLLBACK');
				return false;
			}
		} catch ( Exception $e ) {
			$wpdb->query('ROLLBACK');
			// error_log('Error deleting chat session: ' . $e->getMessage());
			return false;
		}
	}

	/**
	 * Get session statistics for admin dashboard.
	 *
	 * @return array Session statistics.
	 */
	public function get_session_statistics() {
		global $wpdb;

		$stats = array();

		// Total sessions
		$stats['total_sessions'] = $wpdb->get_var(
			"SELECT COUNT(*) FROM {$this->chat_sessions_table}"
		);

		// Active sessions (with messages in last 24 hours)
		$stats['active_sessions'] = $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(DISTINCT s.session_id) 
				FROM {$this->chat_sessions_table} s 
				LEFT JOIN {$this->chat_messages_table} m ON s.session_id = m.session_id 
				WHERE m.created_at > %s",
				date('Y-m-d H:i:s', strtotime('-24 hours'))
			)
		);

		// Total messages
		$stats['total_messages'] = $wpdb->get_var(
			"SELECT COUNT(*) FROM {$this->chat_messages_table}"
		);

		// AI handled sessions
		$stats['ai_sessions'] = $wpdb->get_var(
			"SELECT COUNT(*) FROM {$this->chat_sessions_table} WHERE is_ai_handled = 1"
		);

		// Agent handled sessions
		$stats['agent_sessions'] = $wpdb->get_var(
			"SELECT COUNT(*) FROM {$this->chat_sessions_table} WHERE is_ai_handled = 0"
		);

		return $stats;
	}

	/**
	 * Normalize text for better matching.
	 *
	 * @param string $text The text to normalize.
	 * @return string Normalized text.
	 */
	private function normalize_text( $text ) {
		// Convert to lowercase
		$text = strtolower($text);
		
		// Remove punctuation except apostrophes
		$text = preg_replace("/[^\w\s']/", ' ', $text);
		
		// Replace multiple spaces with single space
		$text = preg_replace('/\s+/', ' ', $text);
		
		// Trim whitespace
		$text = trim($text);
		
		return $text;
	}

	/**
	 * Calculate exact match score.
	 *
	 * @param string $query The user query.
	 * @param string $question The Q&A question.
	 * @return float Score between 0 and 1.
	 */
	private function calculate_exact_match_score( $query, $question ) {
		if ( $query === $question ) {
			return 1.0;
		}
		
		// Check for exact substring matches
		if ( strpos($question, $query) !== false || strpos($query, $question) !== false ) {
			return 0.8;
		}
		
		return 0.0;
	}

	/**
	 * Calculate semantic similarity using word overlap and synonyms.
	 *
	 * @param string $query The user query.
	 * @param string $question The Q&A question.
	 * @return float Score between 0 and 1.
	 */
	private function calculate_semantic_similarity( $query, $question ) {
		$query_words = array_filter(explode(' ', $query));
		$question_words = array_filter(explode(' ', $question));
		
		if ( empty($query_words) || empty($question_words) ) {
			return 0.0;
		}

		// Calculate Jaccard similarity
		$intersection = array_intersect($query_words, $question_words);
		$union = array_unique(array_merge($query_words, $question_words));
		
		$jaccard_score = count($intersection) / count($union);
		
		// Enhance with synonym matching
		$synonym_score = $this->calculate_synonym_similarity($query_words, $question_words);
		
		return max($jaccard_score, $synonym_score * 0.8);
	}

	/**
	 * Calculate intent similarity based on question patterns.
	 *
	 * @param string $query The user query.
	 * @param string $question The Q&A question.
	 * @return float Score between 0 and 1.
	 */
	private function calculate_intent_similarity( $query, $question ) {
		// Define intent patterns
		$intent_patterns = array(
			'what_is' => array('/^what\s+(is|are)/', '/^define/', '/^explain/'),
			'how_to' => array('/^how\s+(to|do|can)/', '/^can\s+you/', '/^steps\s+to/'),
			'why' => array('/^why/', '/^reason/', '/^because/'),
			'when' => array('/^when/', '/^time/', '/^schedule/'),
			'where' => array('/^where/', '/^location/', '/^place/'),
			'who' => array('/^who/', '/^person/', '/^people/'),
			'yes_no' => array('/^(do|does|did|is|are|was|were|can|could|will|would|should)\s+/', '/\?$/'),
			'personal' => array('/\b(you|your|yours)\b/', '/\b(i|me|my|mine)\b/'),
		);

		$query_intent = $this->detect_intent($query, $intent_patterns);
		$question_intent = $this->detect_intent($question, $intent_patterns);

		if ( $query_intent === $question_intent && $query_intent !== 'unknown' ) {
			return 0.9;
		}

		// Special handling for personal questions
		if ( $query_intent === 'personal' && $question_intent === 'personal' ) {
			// Check if both are asking about the same subject
			if ( $this->has_similar_personal_context($query, $question) ) {
				return 0.95;
			}
		}

		return 0.0;
	}

	/**
	 * Calculate keyword similarity with importance weighting.
	 *
	 * @param string $query The user query.
	 * @param string $question The Q&A question.
	 * @return float Score between 0 and 1.
	 */
	private function calculate_keyword_similarity( $query, $question ) {
		$query_words = explode(' ', $query);
		$question_words = explode(' ', $question);
		
		// Remove common stop words
		$stop_words = array('the', 'is', 'are', 'was', 'were', 'a', 'an', 'and', 'or', 'but', 'in', 'on', 'at', 'to', 'for', 'of', 'with', 'by', 'do', 'does', 'did', 'can', 'could', 'will', 'would', 'should');
		
		$query_keywords = array_diff($query_words, $stop_words);
		$question_keywords = array_diff($question_words, $stop_words);
		
		if ( empty($query_keywords) || empty($question_keywords) ) {
			return 0.0;
		}

		$matches = 0;
		$total_keywords = count($query_keywords);

		foreach ( $query_keywords as $keyword ) {
			if ( in_array($keyword, $question_keywords) ) {
				$matches++;
			}
		}

		return $matches / $total_keywords;
	}

	/**
	 * Calculate contextual similarity based on surrounding words and phrases.
	 *
	 * @param string $query The user query.
	 * @param string $question The Q&A question.
	 * @return float Score between 0 and 1.
	 */
	private function calculate_contextual_similarity( $query, $question ) {
		// Look for common phrases and contexts
		$context_patterns = array(
			'love_emotion' => array('feel', 'emotion', 'heart', 'romantic', 'relationship', 'care', 'affection'),
			'love_definition' => array('what', 'define', 'meaning', 'explain', 'definition', 'concept'),
			'personal_question' => array('you', 'your', 'do you', 'are you', 'can you'),
			'general_question' => array('what is', 'how does', 'why is', 'when does'),
		);

		$query_contexts = $this->detect_contexts($query, $context_patterns);
		$question_contexts = $this->detect_contexts($question, $context_patterns);

		$common_contexts = array_intersect($query_contexts, $question_contexts);
		
		if ( !empty($common_contexts) ) {
			return count($common_contexts) / max(count($query_contexts), count($question_contexts));
		}

		return 0.0;
	}

	/**
	 * Calculate synonym similarity.
	 *
	 * @param array $query_words Query words.
	 * @param array $question_words Question words.
	 * @return float Score between 0 and 1.
	 */
	private function calculate_synonym_similarity( $query_words, $question_words ) {
		// Simple synonym mapping - can be expanded
		$synonyms = array(
			'love' => array('affection', 'care', 'adore', 'cherish', 'romance'),
			'what' => array('define', 'explain', 'describe'),
			'how' => array('method', 'way', 'process'),
			'you' => array('yourself', 'user'),
			'feel' => array('emotion', 'sentiment', 'experience'),
		);

		$matches = 0;
		$total = count($query_words);

		foreach ( $query_words as $query_word ) {
			if ( in_array($query_word, $question_words) ) {
				$matches++;
				continue;
			}

			// Check synonyms
			if ( isset($synonyms[$query_word]) ) {
				foreach ( $synonyms[$query_word] as $synonym ) {
					if ( in_array($synonym, $question_words) ) {
						$matches += 0.8; // Synonym match gets slightly lower score
						break;
					}
				}
			}
		}

		return $total > 0 ? $matches / $total : 0.0;
	}

	/**
	 * Check if two questions have similar structure.
	 *
	 * @param string $query The user query.
	 * @param string $question The Q&A question.
	 * @return bool True if similar structure.
	 */
	private function has_similar_structure( $query, $question ) {
		// Check for similar question patterns
		$query_pattern = $this->get_question_pattern($query);
		$question_pattern = $this->get_question_pattern($question);

		return $query_pattern === $question_pattern;
	}

	/**
	 * Get question pattern.
	 *
	 * @param string $text The text to analyze.
	 * @return string The question pattern.
	 */
	private function get_question_pattern( $text ) {
		if ( preg_match('/^what\s+(is|are)/', $text) ) return 'what_is';
		if ( preg_match('/^how\s+(to|do|can)/', $text) ) return 'how_to';
		if ( preg_match('/^why/', $text) ) return 'why';
		if ( preg_match('/^when/', $text) ) return 'when';
		if ( preg_match('/^where/', $text) ) return 'where';
		if ( preg_match('/^who/', $text) ) return 'who';
		if ( preg_match('/^(do|does|did|is|are|was|were|can|could|will|would|should)\s+you/', $text) ) return 'personal_question';
		
		return 'statement';
	}

	/**
	 * Detect intent from text.
	 *
	 * @param string $text The text to analyze.
	 * @param array $patterns Intent patterns.
	 * @return string The detected intent.
	 */
	private function detect_intent( $text, $patterns ) {
		foreach ( $patterns as $intent => $pattern_list ) {
			foreach ( $pattern_list as $pattern ) {
				if ( preg_match($pattern, $text) ) {
					return $intent;
				}
			}
		}
		return 'unknown';
	}

	/**
	 * Check if two personal questions have similar context.
	 *
	 * @param string $query The user query.
	 * @param string $question The Q&A question.
	 * @return bool True if similar personal context.
	 */
	private function has_similar_personal_context( $query, $question ) {
		// Extract the main subject after "you" or similar personal pronouns
		$query_subject = $this->extract_personal_subject($query);
		$question_subject = $this->extract_personal_subject($question);

		return $query_subject === $question_subject;
	}

	/**
	 * Extract personal subject from question.
	 *
	 * @param string $text The text to analyze.
	 * @return string The personal subject.
	 */
	private function extract_personal_subject( $text ) {
		// Look for patterns like "do you [verb]", "are you [adjective]", etc.
		if ( preg_match('/(?:do|does|did|are|is|were|was|can|could|will|would|should)\s+you\s+(\w+)/', $text, $matches) ) {
			return $matches[1];
		}
		
		return '';
	}

	/**
	 * Detect contexts in text.
	 *
	 * @param string $text The text to analyze.
	 * @param array $context_patterns Context patterns.
	 * @return array Detected contexts.
	 */
	private function detect_contexts( $text, $context_patterns ) {
		$detected_contexts = array();
		
		foreach ( $context_patterns as $context => $keywords ) {
			foreach ( $keywords as $keyword ) {
				if ( strpos($text, $keyword) !== false ) {
					$detected_contexts[] = $context;
					break;
				}
			}
		}
		
		return array_unique($detected_contexts);
	}

	/**
	 * 
	Log API statistics.
	 *
	 * @param string $request_type Type of request (chat, search, etc.).
	 * @param string $api_provider API provider (openai, deepseek, fallback).
	 * @param string $status Status (success, error, timeout).
	 * @param float $response_time Response time in seconds.
	 * @param string $error_message Error message if any.
	 * @param string $query_text The user query.
	 * @param string $session_id Session ID.
	 * @param int $user_id User ID if available.
	 * @return bool|int The ID of the inserted row, or false on failure.
	 */

	public function log_api_stats( $request_type, $api_provider, $status, $response_time = null, $error_message = null, $query_text = null, $session_id = null, $user_id = null ) {
		global $wpdb;

		return $wpdb->insert(
			$this->api_stats_table,
			array(
				'request_type' => $request_type,
				'api_provider' => $api_provider,
				'status' => $status,
				'response_time' => $response_time,
				'error_message' => $error_message,
				'query_text' => $query_text,
				'session_id' => $session_id,
				'user_id' => $user_id,
				'created_at' => current_time('mysql'),
			),
			array( '%s', '%s', '%s', '%f', '%s', '%s', '%s', '%d', '%s' )
		);
	}

	/**
	 * Get API statistics for dashboard.
	 *
	 * @param int $days Number of days to get stats for (default 30).
	 * @return array API statistics data.
	 */
	public function get_api_stats( $days = 30 ) {
		global $wpdb;

		$date_from = date('Y-m-d H:i:s', strtotime("-{$days} days"));

		// Overall stats - only count actual API requests, not fallback usage
		$total_api_requests = $wpdb->get_var($wpdb->prepare(
			"SELECT COUNT(*) FROM {$this->api_stats_table} 
			WHERE created_at >= %s 
			AND api_provider IN ('openai', 'deepseek') 
			AND status NOT IN ('fallback_used', 'api_disabled')",
			$date_from
		));

		$successful_api_requests = $wpdb->get_var($wpdb->prepare(
			"SELECT COUNT(*) FROM {$this->api_stats_table} 
			WHERE status = 'success' 
			AND api_provider IN ('openai', 'deepseek') 
			AND created_at >= %s",
			$date_from
		));

		$failed_api_requests = $wpdb->get_var($wpdb->prepare(
			"SELECT COUNT(*) FROM {$this->api_stats_table} 
			WHERE status IN ('error', 'api_failed', 'complete_failure') 
			AND api_provider IN ('openai', 'deepseek') 
			AND created_at >= %s",
			$date_from
		));

		$fallback_usage = $wpdb->get_var($wpdb->prepare(
			"SELECT COUNT(*) FROM {$this->api_stats_table} 
			WHERE status IN ('fallback_used', 'api_disabled') 
			AND created_at >= %s",
			$date_from
		));

		$avg_response_time = $wpdb->get_var($wpdb->prepare(
			"SELECT AVG(response_time) FROM {$this->api_stats_table} 
			WHERE status = 'success' 
			AND response_time IS NOT NULL 
			AND api_provider IN ('openai', 'deepseek') 
			AND created_at >= %s",
			$date_from
		));

		// Provider breakdown - separate API providers from fallback
		$provider_stats = $wpdb->get_results($wpdb->prepare(
			"SELECT api_provider, 
					COUNT(*) as total_requests,
					SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as successful_requests,
					SUM(CASE WHEN status IN ('error', 'api_failed', 'complete_failure') THEN 1 ELSE 0 END) as failed_requests,
					AVG(CASE WHEN status = 'success' AND response_time IS NOT NULL THEN response_time END) as avg_response_time
			FROM {$this->api_stats_table} 
			WHERE created_at >= %s 
			AND api_provider IN ('openai', 'deepseek')
			GROUP BY api_provider",
			$date_from
		), ARRAY_A);

		// Add fallback statistics separately
		$fallback_stats = $wpdb->get_row($wpdb->prepare(
			"SELECT 'fallback' as api_provider,
					COUNT(*) as total_requests,
					0 as successful_requests,
					COUNT(*) as failed_requests,
					AVG(response_time) as avg_response_time
			FROM {$this->api_stats_table} 
			WHERE created_at >= %s 
			AND status IN ('fallback_used', 'api_disabled')",
			$date_from
		), ARRAY_A);

		if ( $fallback_stats && $fallback_stats['total_requests'] > 0 ) {
			$provider_stats[] = $fallback_stats;
		}

		// Daily stats for chart - only count actual API requests
		$daily_stats = $wpdb->get_results($wpdb->prepare(
			"SELECT DATE(created_at) as date,
					COUNT(*) as total_requests,
					SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as successful_requests,
					SUM(CASE WHEN status IN ('error', 'api_failed', 'complete_failure') THEN 1 ELSE 0 END) as failed_requests,
					AVG(CASE WHEN status = 'success' AND response_time IS NOT NULL THEN response_time END) as avg_response_time
			FROM {$this->api_stats_table} 
			WHERE created_at >= %s 
			AND api_provider IN ('openai', 'deepseek')
			AND status NOT IN ('fallback_used', 'api_disabled')
			GROUP BY DATE(created_at) 
			ORDER BY date ASC",
			$date_from
		), ARRAY_A);

		// Request type breakdown
		$request_type_stats = $wpdb->get_results($wpdb->prepare(
			"SELECT request_type,
					COUNT(*) as total_requests,
					SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as successful_requests,
					SUM(CASE WHEN status != 'success' THEN 1 ELSE 0 END) as failed_requests
			FROM {$this->api_stats_table} 
			WHERE created_at >= %s 
			GROUP BY request_type",
			$date_from
		), ARRAY_A);

		// Recent errors - only show actual API failures, not fallback usage (limited for overview)
		$recent_errors = $wpdb->get_results($wpdb->prepare(
			"SELECT api_provider, error_message, query_text, created_at
			FROM {$this->api_stats_table} 
			WHERE status IN ('error', 'api_failed', 'complete_failure', 'timeout') 
			AND created_at >= %s 
			ORDER BY created_at DESC 
			LIMIT 5",
			$date_from
		), ARRAY_A);

		return array(
			'overview' => array(
				'total_api_requests' => (int) $total_api_requests,
				'successful_requests' => (int) $successful_api_requests,
				'failed_requests' => (int) $failed_api_requests,
				'fallback_usage' => (int) $fallback_usage,
				'success_rate' => $total_api_requests > 0 ? round(($successful_api_requests / $total_api_requests) * 100, 2) : 0,
				'failure_rate' => $total_api_requests > 0 ? round(($failed_api_requests / $total_api_requests) * 100, 2) : 0,
				'fallback_rate' => ($total_api_requests + $fallback_usage) > 0 ? round(($fallback_usage / ($total_api_requests + $fallback_usage)) * 100, 2) : 0,
				'avg_response_time' => $avg_response_time ? round($avg_response_time, 3) : 0,
			),
			'providers' => $provider_stats,
			'daily_stats' => $daily_stats,
			'request_types' => $request_type_stats,
			'recent_errors' => $recent_errors,
		);
	}

	/**
	 * Get API performance metrics for specific time periods.
	 *
	 * @param string $period Period (today, week, month, year).
	 * @return array Performance metrics.
	 */
	public function get_api_performance_metrics( $period = 'week' ) {
		global $wpdb;

		$date_conditions = array(
			'today' => "DATE(created_at) = CURDATE()",
			'week' => "created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)",
			'month' => "created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)",
			'year' => "created_at >= DATE_SUB(NOW(), INTERVAL 365 DAY)",
		);

		$condition = isset($date_conditions[$period]) ? $date_conditions[$period] : $date_conditions['week'];

		// Get metrics for actual API requests (not fallback usage)
		$api_metrics = $wpdb->get_row(
			"SELECT 
				COUNT(*) as total_api_requests,
				SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) as successful_requests,
				SUM(CASE WHEN status IN ('error', 'api_failed', 'complete_failure') THEN 1 ELSE 0 END) as error_requests,
				SUM(CASE WHEN status = 'timeout' THEN 1 ELSE 0 END) as timeout_requests,
				AVG(CASE WHEN status = 'success' AND response_time IS NOT NULL THEN response_time END) as avg_response_time,
				MIN(CASE WHEN status = 'success' AND response_time IS NOT NULL THEN response_time END) as min_response_time,
				MAX(CASE WHEN status = 'success' AND response_time IS NOT NULL THEN response_time END) as max_response_time
			FROM {$this->api_stats_table} 
			WHERE {$condition} 
			AND api_provider IN ('openai', 'deepseek')
			AND status NOT IN ('fallback_used', 'api_disabled')",
			ARRAY_A
		);

		// Get fallback usage separately
		$fallback_metrics = $wpdb->get_row(
			"SELECT 
				COUNT(*) as fallback_requests
			FROM {$this->api_stats_table} 
			WHERE {$condition} 
			AND status IN ('fallback_used', 'api_disabled')",
			ARRAY_A
		);

		// Combine the metrics
		$metrics = array(
			'total_requests' => (int) ($api_metrics['total_api_requests'] ?? 0),
			'successful_requests' => (int) ($api_metrics['successful_requests'] ?? 0),
			'error_requests' => (int) ($api_metrics['error_requests'] ?? 0),
			'timeout_requests' => (int) ($api_metrics['timeout_requests'] ?? 0),
			'fallback_requests' => (int) ($fallback_metrics['fallback_requests'] ?? 0),
			'avg_response_time' => $api_metrics['avg_response_time'] ?? 0,
			'min_response_time' => $api_metrics['min_response_time'] ?? 0,
			'max_response_time' => $api_metrics['max_response_time'] ?? 0,
		);

		$total_api = (int) $metrics['total_requests'];
		$total_fallback = (int) $metrics['fallback_requests'];
		$total_all = $total_api + $total_fallback;
		
		return array(
			'total_requests' => $total_api,
			'successful_requests' => (int) $metrics['successful_requests'],
			'error_requests' => (int) $metrics['error_requests'],
			'timeout_requests' => (int) $metrics['timeout_requests'],
			'fallback_requests' => $total_fallback,
			'success_rate' => $total_api > 0 ? round(($metrics['successful_requests'] / $total_api) * 100, 2) : 0,
			'error_rate' => $total_api > 0 ? round(($metrics['error_requests'] / $total_api) * 100, 2) : 0,
			'fallback_rate' => $total_all > 0 ? round(($total_fallback / $total_all) * 100, 2) : 0,
			'avg_response_time' => $metrics['avg_response_time'] ? round($metrics['avg_response_time'], 3) : 0,
			'min_response_time' => $metrics['min_response_time'] ? round($metrics['min_response_time'], 3) : 0,
			'max_response_time' => $metrics['max_response_time'] ? round($metrics['max_response_time'], 3) : 0,
		);
	}
	/**
	 * Get paginated API errors.
	 * @param int $page Current page number (1-based).
	 * @param int $per_page Number of errors per page.
	 * @param int $days Number of days to get errors for.
	 * @return array Paginated error data.
	 */
	public function get_paginated_api_errors( $page = 1, $per_page = 10, $days = 30 ) {
		global $wpdb;

		$date_from = date('Y-m-d H:i:s', strtotime("-{$days} days"));
		$offset = ($page - 1) * $per_page;

		// Get total count
		$total_errors = $wpdb->get_var($wpdb->prepare(
			"SELECT COUNT(*) FROM {$this->api_stats_table} 
			WHERE status IN ('error', 'api_failed', 'complete_failure', 'timeout') 
			AND created_at >= %s",
			$date_from
		));

		// Get paginated errors
		$errors = $wpdb->get_results($wpdb->prepare(
			"SELECT api_provider, error_message, query_text, created_at, status, response_time
			FROM {$this->api_stats_table} 
			WHERE status IN ('error', 'api_failed', 'complete_failure', 'timeout') 
			AND created_at >= %s 
			ORDER BY created_at DESC 
			LIMIT %d OFFSET %d",
			$date_from,
			$per_page,
			$offset
		), ARRAY_A);

		$total_pages = ceil($total_errors / $per_page);

		return array(
			'errors' => $errors,
			'pagination' => array(
				'current_page' => $page,
				'per_page' => $per_page,
				'total_errors' => (int) $total_errors,
				'total_pages' => $total_pages,
				'has_prev' => $page > 1,
				'has_next' => $page < $total_pages,
			)
		);
	}

	/**
	 * Get paginated chat logs.
	 *
	 * @param int $page Current page number (1-based).
	 * @param int $per_page Number of chat logs per page.
	 * @param string $search Search query for filtering logs.
	 * @return array Paginated chat log data.
	 */
	public function get_paginated_chat_logs( $page = 1, $per_page = 20, $search = '' ) {
		global $wpdb;

		$offset = ($page - 1) * $per_page;
		$search_condition = '';
		$search_params = array();

		// Add search condition if search query is provided
		if ( ! empty($search) ) {
			$search_condition = "WHERE (message LIKE %s OR response LIKE %s)";
			$search_term = '%' . $wpdb->esc_like($search) . '%';
			$search_params = array($search_term, $search_term);
		}

		// Get total count with search
		$count_query = "SELECT COUNT(*) FROM {$this->chat_history_table} {$search_condition}";
		if ( ! empty($search_params) ) {
			$total_logs = $wpdb->get_var($wpdb->prepare($count_query, $search_params));
		} else {
			$total_logs = $wpdb->get_var($count_query);
		}

		// Get paginated chat logs with search
		$logs_query = "SELECT * FROM {$this->chat_history_table} 
					   {$search_condition} 
					   ORDER BY created_at DESC 
					   LIMIT %d OFFSET %d";
		
		$query_params = array_merge($search_params, array($per_page, $offset));
		$logs = $wpdb->get_results($wpdb->prepare($logs_query, $query_params));

		$total_pages = ceil($total_logs / $per_page);

		return array(
			'logs' => $logs,
			'pagination' => array(
				'current_page' => $page,
				'per_page' => $per_page,
				'total_logs' => (int) $total_logs,
				'total_pages' => $total_pages,
				'has_prev' => $page > 1,
				'has_next' => $page < $total_pages,
			)
		);
	}

	/**
	 * Clear API error logs.
	 *
	 * @param int $days Number of days to keep (0 = clear all errors).
	 * @return bool|int Number of rows deleted or false on failure.
	 */
	public function clear_api_errors( $days = 0 ) {
		global $wpdb;

		if ( $days > 0 ) {
			// Clear errors older than specified days
			$date_threshold = date('Y-m-d H:i:s', strtotime("-{$days} days"));
			return $wpdb->query($wpdb->prepare(
				"DELETE FROM {$this->api_stats_table} 
				WHERE status IN ('error', 'api_failed', 'complete_failure', 'timeout') 
				AND created_at < %s",
				$date_threshold
			));
		} else {
			// Clear all errors
			return $wpdb->query(
				"DELETE FROM {$this->api_stats_table} 
				WHERE status IN ('error', 'api_failed', 'complete_failure', 'timeout')"
			);
		}
	}

	/**
	 * Clear chat logs.
	 *
	 * @param int $days Number of days to keep (0 = clear all logs).
	 * @return bool|int Number of rows deleted or false on failure.
	 */
	public function clear_chat_logs( $days = 0 ) {
		global $wpdb;

		if ( $days > 0 ) {
			// Clear logs older than specified days
			$date_threshold = date('Y-m-d H:i:s', strtotime("-{$days} days"));
			return $wpdb->query($wpdb->prepare(
				"DELETE FROM {$this->chat_history_table} 
				WHERE created_at < %s",
				$date_threshold
			));
		} else {
			// Clear all logs
			return $wpdb->query("TRUNCATE TABLE {$this->chat_history_table}");
		}
	}

	/**
	 * Run database migrations.
	 *
	 * This method checks for and applies any necessary database schema updates.
	 * It uses WordPress options to track which migrations have been applied.
	 *
	 * @return void
	 */
	private function run_migrations() {
		// Migration: Add attachment_id column to pdf_index table
		$this->migrate_add_attachment_id_column();
		
		// Migration: Add unique key to content_index table
		$this->migrate_add_content_index_unique_key();
		
		// Migration: Add unique_id column to feature_requests and bug_reports tables
		$this->migrate_add_unique_id_columns();
		
		// Migration: Add RAG tables and columns
		$this->migrate_add_rag_support();
		
		// Migration: Update deprecated Gemini embedding model
		$this->migrate_update_gemini_embedding_model();
	}

	/**
	 * Migration: Add attachment_id column to pdf_index table.
	 *
	 * This migration adds the attachment_id column to the pdf_index table
	 * if it doesn't already exist. Uses an option flag to avoid repeated checks.
	 *
	 * @return void
	 */
	private function migrate_add_attachment_id_column() {
		// Check if migration has already been applied
		if ( get_option('askany_migration_pdf_attachment_id', false) ) {
			return;
		}

		global $wpdb;

		// Check if column exists
		$column_exists = $wpdb->get_results(
			$wpdb->prepare(
				"SHOW COLUMNS FROM {$this->pdf_index_table} LIKE %s",
				'attachment_id'
			)
		);

		// Add column if it doesn't exist
		if ( empty($column_exists) ) {
			$wpdb->query(
				"ALTER TABLE {$this->pdf_index_table} 
				ADD COLUMN attachment_id bigint(20) NULL DEFAULT NULL AFTER file_size,
				ADD KEY attachment_id (attachment_id)"
			);

			// Log the migration
			// error_log('AskAny: Added attachment_id column to pdf_index table');
		}

		// Mark migration as complete
		update_option('askany_migration_pdf_attachment_id', true);
	}

	/**
	 * Migration: Add unique key to content_index table.
	 *
	 * This migration adds a unique key on (object_type, object_id) to the content_index table
	 * to ensure proper functioning of the replace operation and prevent duplicate entries.
	 *
	 * @return void
	 */
	private function migrate_add_content_index_unique_key() {
		// Check if migration has already been applied
		if ( get_option('askany_migration_content_index_unique_key', false) ) {
			return;
		}

		global $wpdb;

		// Check if unique key already exists
		$key_exists = $wpdb->get_results(
			$wpdb->prepare(
				"SHOW KEYS FROM {$this->content_index_table} WHERE Key_name = %s",
				'object_type_id'
			)
		);

		// Add unique key if it doesn't exist
		if ( empty($key_exists) ) {
			// First, remove any duplicate entries before adding the unique key
			$wpdb->query(
				"DELETE t1 FROM {$this->content_index_table} t1
				INNER JOIN {$this->content_index_table} t2 
				WHERE t1.id > t2.id 
				AND t1.object_type = t2.object_type 
				AND t1.object_id = t2.object_id"
			);
			
			// Now add the unique key
			$wpdb->query(
				"ALTER TABLE {$this->content_index_table} 
				ADD UNIQUE KEY object_type_id (object_type, object_id)"
			);

			// error_log('AskAny: Added unique key to content_index table');
		}

		// Mark migration as complete
		update_option('askany_migration_content_index_unique_key', true);
	}

	/**
	 * Migration: Add unique_id column to feature_requests and bug_reports tables.
	 *
	 * This migration adds the unique_id column to both tables for tracking purposes.
	 *
	 * @return void
	 */
	private function migrate_add_unique_id_columns() {
		// Check if migration has already been applied
		if ( get_option('askany_migration_unique_id_columns', false) ) {
			return;
		}

		global $wpdb;

		// Migrate feature_requests table
		$column_exists = $wpdb->get_results(
			$wpdb->prepare(
				"SHOW COLUMNS FROM {$this->feature_requests_table} LIKE %s",
				'unique_id'
			)
		);

		if ( empty($column_exists) ) {
			// Add column
			$wpdb->query(
				"ALTER TABLE {$this->feature_requests_table} 
				ADD COLUMN unique_id varchar(100) NULL AFTER id"
			);

			// Generate unique IDs for existing records
			$existing_records = $wpdb->get_results(
				"SELECT id, user_name, created_at FROM {$this->feature_requests_table} WHERE unique_id IS NULL"
			);

			foreach ( $existing_records as $record ) {
				$clean_name = strtoupper(preg_replace('/[^a-zA-Z0-9]/', '', $record->user_name));
				if (empty($clean_name)) {
					$clean_name = 'USER';
				}
				$timestamp = strtotime($record->created_at);
				$unique_id = 'FEATURE-' . $clean_name . '-' . $timestamp;

				// Make sure it's unique by adding a counter if needed
				$counter = 0;
				$final_unique_id = $unique_id;
				while ( $wpdb->get_var($wpdb->prepare(
					"SELECT COUNT(*) FROM {$this->feature_requests_table} WHERE unique_id = %s",
					$final_unique_id
				)) > 0 ) {
					$counter++;
					$final_unique_id = $unique_id . '-' . $counter;
				}

				$wpdb->update(
					$this->feature_requests_table,
					array( 'unique_id' => $final_unique_id ),
					array( 'id' => $record->id ),
					array( '%s' ),
					array( '%d' )
				);
			}

			// Now make the column NOT NULL and add unique index
			$wpdb->query(
				"ALTER TABLE {$this->feature_requests_table} 
				MODIFY COLUMN unique_id varchar(100) NOT NULL,
				ADD UNIQUE KEY unique_id (unique_id)"
			);

			// error_log('AskAny: Added unique_id column to feature_requests table');
		}

		// Migrate bug_reports table
		$column_exists = $wpdb->get_results(
			$wpdb->prepare(
				"SHOW COLUMNS FROM {$this->bug_reports_table} LIKE %s",
				'unique_id'
			)
		);

		if ( empty($column_exists) ) {
			// Add column
			$wpdb->query(
				"ALTER TABLE {$this->bug_reports_table} 
				ADD COLUMN unique_id varchar(100) NULL AFTER id"
			);

			// Generate unique IDs for existing records
			$existing_records = $wpdb->get_results(
				"SELECT id, user_name, created_at FROM {$this->bug_reports_table} WHERE unique_id IS NULL"
			);

			foreach ( $existing_records as $record ) {
				$clean_name = strtoupper(preg_replace('/[^a-zA-Z0-9]/', '', $record->user_name));
				if (empty($clean_name)) {
					$clean_name = 'USER';
				}
				$timestamp = strtotime($record->created_at);
				$unique_id = 'BUG-' . $clean_name . '-' . $timestamp;

				// Make sure it's unique by adding a counter if needed
				$counter = 0;
				$final_unique_id = $unique_id;
				while ( $wpdb->get_var($wpdb->prepare(
					"SELECT COUNT(*) FROM {$this->bug_reports_table} WHERE unique_id = %s",
					$final_unique_id
				)) > 0 ) {
					$counter++;
					$final_unique_id = $unique_id . '-' . $counter;
				}

				$wpdb->update(
					$this->bug_reports_table,
					array( 'unique_id' => $final_unique_id ),
					array( 'id' => $record->id ),
					array( '%s' ),
					array( '%d' )
				);
			}

			// Now make the column NOT NULL and add unique index
			$wpdb->query(
				"ALTER TABLE {$this->bug_reports_table} 
				MODIFY COLUMN unique_id varchar(100) NOT NULL,
				ADD UNIQUE KEY unique_id (unique_id)"
			);

			// error_log('AskAny: Added unique_id column to bug_reports table');
		}

		// Mark migration as complete
		update_option('askany_migration_unique_id_columns', true);
	}

	/**
	 * Migration: Add RAG support (embeddings table and columns).
	 *
	 * This migration ensures the embeddings table exists and adds
	 * embedding-related columns to the content_index table.
	 *
	 * @return void
	 */
	private function migrate_add_rag_support() {
		// Check if migration has already been applied
		if ( get_option('askany_migration_rag_support', false) ) {
			return;
		}

		// The embeddings table is already created in create_tables()
		// The embedding columns are already added in add_embedding_columns()
		// This migration just sets the flag to prevent repeated checks
		
		// Mark migration as complete
		update_option('askany_migration_rag_support', true);
		// error_log('AskAny: RAG support migration completed');
	}

	/**
	 * Migration: Update deprecated Gemini embedding model.
	 *
	 * Updates any old Gemini model names to gemini-embedding-001 (Feb 2026 update).
	 *
	 * @return void
	 */
	private function migrate_update_gemini_embedding_model() {
		// Check if migration has already been applied
		if ( get_option('askany_migration_gemini_embedding_model_v2', false) ) {
			return;
		}

		$current_model = get_option('askany_embedding_model', '');
		
		// Update if using any old Gemini model name
		if ( in_array( $current_model, array( 'embedding-001', 'text-embedding-004' ) ) ) {
			update_option('askany_embedding_model', 'gemini-embedding-001');
			// error_log('AskAny: Updated Gemini embedding model to gemini-embedding-001');
		}

		// Mark migration as complete
		update_option('askany_migration_gemini_embedding_model_v2', true);
	}


	/**
	 * Save a feature request from user.
	 *
	 * @param string $session_id The session ID.
	 * @param string $user_name The user's name.
	 * @param string $user_email The user's email.
	 * @param string $title The feature title.
	 * @param string $description The feature description.
	 *
	 * @return bool|int The ID of the inserted row, or false on failure.
	 */
	public function save_feature_request( $session_id, $user_name, $user_email, $title, $description ) {
		global $wpdb;

		// Generate unique ID: FEATURE-USERNAME-TIMESTAMP
		$clean_name = strtoupper(preg_replace('/[^a-zA-Z0-9]/', '', $user_name));
		if (empty($clean_name)) {
			$clean_name = 'USER';
		}
		$unique_id = 'FEATURE-' . $clean_name . '-' . time();

		// Ensure uniqueness by adding a counter if needed
		$counter = 0;
		$final_unique_id = $unique_id;
		while ( $wpdb->get_var($wpdb->prepare(
			"SELECT COUNT(*) FROM {$this->feature_requests_table} WHERE unique_id = %s",
			$final_unique_id
		)) > 0 ) {
			$counter++;
			$final_unique_id = $unique_id . '-' . $counter;
		}

		$result = $wpdb->insert(
			$this->feature_requests_table,
			array(
				'unique_id'           => $final_unique_id,
				'session_id'          => $session_id,
				'user_name'           => $user_name,
				'user_email'          => $user_email,
				'feature_title'       => $title,
				'feature_description' => $description,
				'status'              => 'pending',
				'priority'            => 'medium',
			),
			array( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		if ($result) {
			return array(
				'id' => $wpdb->insert_id,
				'unique_id' => $final_unique_id
			);
		}

		return false;
	}

	/**
	 * Save a bug report from user.
	 *
	 * @param string $session_id The session ID.
	 * @param string $user_name The user's name.
	 * @param string $user_email The user's email.
	 * @param string $title The bug title.
	 * @param string $description The bug description.
	 *
	 * @return bool|int The ID of the inserted row, or false on failure.
	 */
	public function save_bug_report( $session_id, $user_name, $user_email, $title, $description ) {
		global $wpdb;

		// Generate unique ID: BUG-USERNAME-TIMESTAMP
		$clean_name = strtoupper(preg_replace('/[^a-zA-Z0-9]/', '', $user_name));
		if (empty($clean_name)) {
			$clean_name = 'USER';
		}
		$unique_id = 'BUG-' . $clean_name . '-' . time();

		// Ensure uniqueness by adding a counter if needed
		$counter = 0;
		$final_unique_id = $unique_id;
		while ( $wpdb->get_var($wpdb->prepare(
			"SELECT COUNT(*) FROM {$this->bug_reports_table} WHERE unique_id = %s",
			$final_unique_id
		)) > 0 ) {
			$counter++;
			$final_unique_id = $unique_id . '-' . $counter;
		}

		$result = $wpdb->insert(
			$this->bug_reports_table,
			array(
				'unique_id'       => $final_unique_id,
				'session_id'      => $session_id,
				'user_name'       => $user_name,
				'user_email'      => $user_email,
				'bug_title'       => $title,
				'bug_description' => $description,
				'status'          => 'pending',
				'severity'        => 'medium',
			),
			array( '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' )
		);

		if ($result) {
			return array(
				'id' => $wpdb->insert_id,
				'unique_id' => $final_unique_id
			);
		}

		return false;
	}

	/**
	 * Get feature requests with optional status filter.
	 *
	 * @param string|null $status Optional status filter (pending, in-progress, completed, rejected).
	 * @param int         $limit Maximum number of results to return.
	 *
	 * @return array|false Array of feature request objects or false on failure.
	 */
	public function get_feature_requests( $status = null, $limit = 50 ) {
		global $wpdb;

		$sql = "SELECT * FROM {$this->feature_requests_table}";

		if ( $status ) {
			$sql .= $wpdb->prepare(' WHERE status = %s', $status);
		}

		$sql .= ' ORDER BY created_at DESC';
		$sql .= $wpdb->prepare(' LIMIT %d', $limit);

		return $wpdb->get_results($sql);
	}

	/**
	 * Get bug reports with optional status filter.
	 *
	 * @param string|null $status Optional status filter (pending, in-progress, completed, rejected).
	 * @param int         $limit Maximum number of results to return.
	 *
	 * @return array|false Array of bug report objects or false on failure.
	 */
	public function get_bug_reports( $status = null, $limit = 50 ) {
		global $wpdb;

		$sql = "SELECT * FROM {$this->bug_reports_table}";

		if ( $status ) {
			$sql .= $wpdb->prepare(' WHERE status = %s', $status);
		}

		$sql .= ' ORDER BY created_at DESC';
		$sql .= $wpdb->prepare(' LIMIT %d', $limit);

		return $wpdb->get_results($sql);
	}

	/**
	 * Update feature request status.
	 *
	 * @param int    $id The feature request ID.
	 * @param string $status The new status.
	 * @param string $notes Optional notes.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function update_feature_request_status( $id, $status, $notes = '' ) {
		global $wpdb;

		$data = array( 'status' => $status );
		if ( ! empty($notes) ) {
			$data['notes'] = $notes;
		}

		$result = $wpdb->update(
			$this->feature_requests_table,
			$data,
			array( 'id' => $id ),
			array( '%s', '%s' ),
			array( '%d' )
		);

		return $result !== false;
	}

	/**
	 * Update bug report status.
	 *
	 * @param int    $id The bug report ID.
	 * @param string $status The new status.
	 * @param string $notes Optional notes.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function update_bug_report_status( $id, $status, $notes = '' ) {
		global $wpdb;

		$data = array( 'status' => $status );
		if ( ! empty($notes) ) {
			$data['notes'] = $notes;
		}

		$result = $wpdb->update(
			$this->bug_reports_table,
			$data,
			array( 'id' => $id ),
			array( '%s', '%s' ),
			array( '%d' )
		);

		return $result !== false;
	}

	/**
	 * Delete a feature request.
	 *
	 * @param int $id Feature request ID.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function delete_feature_request( $id ) {
		global $wpdb;

		$result = $wpdb->delete(
			$this->feature_requests_table,
			array( 'id' => $id ),
			array( '%d' )
		);

		return $result !== false;
	}

	/**
	 * Delete a bug report.
	 *
	 * @param int $id Bug report ID.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function delete_bug_report( $id ) {
		global $wpdb;

		$result = $wpdb->delete(
			$this->bug_reports_table,
			array( 'id' => $id ),
			array( '%d' )
		);

		return $result !== false;
	}

	/**
	 * Get feature request by unique ID.
	 *
	 * @param string $unique_id Unique feature request ID.
	 *
	 * @return object|false Feature request object or false on failure.
	 */
	public function get_feature_request_by_unique_id( $unique_id ) {
		global $wpdb;

		$sql = $wpdb->prepare(
			"SELECT * FROM {$this->feature_requests_table} WHERE unique_id = %s",
			$unique_id
		);

		return $wpdb->get_row($sql);
	}

	/**
	 * Get bug report by unique ID.
	 *
	 * @param string $unique_id Unique bug report ID.
	 *
	 * @return object|false Bug report object or false on failure.
	 */
	public function get_bug_report_by_unique_id( $unique_id ) {
		global $wpdb;

		$sql = $wpdb->prepare(
			"SELECT * FROM {$this->bug_reports_table} WHERE unique_id = %s",
			$unique_id
		);

		return $wpdb->get_row($sql);
	}

	/**
	 * Add embedding columns to content_index table
	 */
	private function add_embedding_columns() {
		global $wpdb;
		$table_name = $this->content_index_table;
		
		// Check if columns exist
		$columns = $wpdb->get_results( "SHOW COLUMNS FROM {$table_name}" );
		$column_names = wp_list_pluck( $columns, 'Field' );
		
		if ( ! in_array( 'has_embedding', $column_names ) ) {
			$wpdb->query( "ALTER TABLE {$table_name} ADD COLUMN has_embedding TINYINT(1) DEFAULT 0" );
		}
		
		if ( ! in_array( 'embedding_status', $column_names ) ) {
			$wpdb->query( "ALTER TABLE {$table_name} ADD COLUMN embedding_status VARCHAR(20) DEFAULT 'pending'" );
		}
		
		if ( ! in_array( 'embedding_error', $column_names ) ) {
			$wpdb->query( "ALTER TABLE {$table_name} ADD COLUMN embedding_error TEXT" );
		}
		
		if ( ! in_array( 'content_hash', $column_names ) ) {
			$wpdb->query( "ALTER TABLE {$table_name} ADD COLUMN content_hash VARCHAR(32)" );
		}
		
		// Add index if not exists
		$indexes = $wpdb->get_results( "SHOW INDEX FROM {$table_name}" );
		$index_names = wp_list_pluck( $indexes, 'Key_name' );
		
		if ( ! in_array( 'idx_embedding_status', $index_names ) ) {
			$wpdb->query( "ALTER TABLE {$table_name} ADD INDEX idx_embedding_status (embedding_status)" );
		}
		
		if ( ! in_array( 'idx_content_hash', $index_names ) ) {
			$wpdb->query( "ALTER TABLE {$table_name} ADD INDEX idx_content_hash (content_hash)" );
		}
		
		// Add content_hash column to embeddings table
		$embeddings_table = $this->embeddings_table;
		$emb_columns = $wpdb->get_results( "SHOW COLUMNS FROM {$embeddings_table}" );
		$emb_column_names = wp_list_pluck( $emb_columns, 'Field' );
		
		if ( ! in_array( 'chunk_hash', $emb_column_names ) ) {
			$wpdb->query( "ALTER TABLE {$embeddings_table} ADD COLUMN chunk_hash VARCHAR(32) AFTER chunk_index" );
		}
		
		// Add index for chunk_hash
		$emb_indexes = $wpdb->get_results( "SHOW INDEX FROM {$embeddings_table}" );
		$emb_index_names = wp_list_pluck( $emb_indexes, 'Key_name' );
		
		if ( ! in_array( 'idx_chunk_hash', $emb_index_names ) ) {
			$wpdb->query( "ALTER TABLE {$embeddings_table} ADD INDEX idx_chunk_hash (chunk_hash)" );
		}
	}

	/**
	 * Get indexed content by ID
	 *
	 * @param int $content_id The content ID.
	 * @return array|null Content data or null if not found.
	 */
	public function get_indexed_content_by_id( $content_id ) {
		global $wpdb;
		
		$result = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->content_index_table} WHERE id = %d",
				$content_id
			),
			ARRAY_A
		);
		
		return $result;
	}

	/**
	 * Get relevant content using RAG (vector search)
	 */
	public function get_relevant_content_rag( $query, $api_manager, $options = array() ) {
		// Check if RAG is enabled
		$rag_enabled = get_option( 'askany_rag_enabled', false );
		
		if ( ! $rag_enabled ) {
			// Fallback to keyword search
			return $this->get_relevant_content( $query );
		}
		
		// Generate query embedding
		$embedding_manager = new Embedding_Manager( $this, $api_manager );
		$query_embedding_data = $embedding_manager->generate_embedding( $query );
		
		if ( is_wp_error( $query_embedding_data ) ) {
			$error_message = $query_embedding_data->get_error_message();
			
			// Don't log quota errors since we show them in the UI
			$is_quota_error = stripos($error_message, 'quota') !== false || 
			                  stripos($error_message, 'rate limit') !== false;
			
			if (!$is_quota_error) {
				// error_log( 'AskAny RAG: Failed to generate query embedding - ' . $error_message );
			}
			
			// Fallback to keyword search
			return $this->get_relevant_content( $query );
		}
		
		// Search using vector similarity
		$vector_search = new Vector_Search( $this );
		$results = $vector_search->search( $query_embedding_data['embedding'], $options );
		
		if ( empty( $results ) ) {
			// Fallback to keyword search
			return $this->get_relevant_content( $query );
		}
		
		// Reassemble chunks if needed
		$results = $vector_search->reassemble_chunks( $results );
		
		// Format results
		$formatted_results = array();
		foreach ( $results as $result ) {
			$content_data = $this->get_indexed_content_by_id( $result['content_id'] );
			
			if ( $content_data ) {
				$formatted_results[] = array(
					'title' => $result['title'],
					'content' => $content_data['content'],
					'url' => $result['source_url'],
					'type' => $result['content_type'],
					'similarity' => $result['similarity'],
				);
			}
		}
		
		return $formatted_results;
	}

	/**
	 * Get embeddings table name
	 */
	public function get_embeddings_table() {
		return $this->embeddings_table;
	}

	/**
	 * Get feature requests table name
	 */
	public function get_feature_requests_table() {
		return $this->feature_requests_table;
	}

	/**
	 * Get bug reports table name
	 */
	public function get_bug_reports_table() {
		return $this->bug_reports_table;
	}
}

