<?php
/**
 * Database operations for Agentorie Chat.
 *
 * @package    Agentorie_Chat
 * @subpackage Agentorie_Chat/includes
 * @since      1.0.0
 */

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

/**
 * Database class.
 *
 * @since 1.0.0
 */
class Agentorie_Database {
	/**
	 * Singleton instance.
	 *
	 * @var Agentorie_Database|null
	 */

	private static $instance = null;
	const DB_VERSION         = '1.6.0';

	/**
	 * Get singleton instance.
	 *
	 * @since 1.0.0
	 * @return Agentorie_Database
	 */
	public static function get_instance() {
		if ( null === self::$instance ) {
			self::$instance = new self();
		}
		return self::$instance;
	}

	/**
	 * Constructor.
	 *
	 * @since 1.0.0
	 */
	private function __construct() {
		$this->maybe_upgrade();
	}

	/**
	 * Run database upgrades if needed.
	 *
	 * @since 1.0.0
	 */
	private function maybe_upgrade() {
		$installed_version = get_option( 'agentorie_chat_db_version', '0' );
		if ( version_compare( $installed_version, self::DB_VERSION, '<' ) ) {
			self::create_tables();

			// v1.2.0: Drop legacy visitors table.
			if ( version_compare( $installed_version, '1.2.0', '<' ) ) {
				global $wpdb;
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->query( 'DROP TABLE IF EXISTS ' . $wpdb->prefix . 'agentorie_visitors' );
			}

			// Database migrations for plugin updates.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from internal methods

			// v1.3.0: Add admin_id column to messages table.
			if ( version_compare( $installed_version, '1.3.0', '<' ) ) {
				global $wpdb;
				$table = self::messages_table();
				// Check if column exists before adding.
				if ( ! self::column_exists( $table, 'admin_id' ) ) {
					$wpdb->query( "ALTER TABLE {$table} ADD COLUMN admin_id BIGINT UNSIGNED DEFAULT NULL AFTER is_admin" );
					$wpdb->query( "CREATE INDEX idx_admin_id ON {$table}(admin_id)" );
				}
			}

			// v1.4.0: Add admin_intervened column to sessions table (permanent flag).
			if ( version_compare( $installed_version, '1.4.0', '<' ) ) {
				global $wpdb;
				$table = self::sessions_table();
				// Check if column exists before adding.
				if ( ! self::column_exists( $table, 'admin_intervened' ) ) {
					$wpdb->query( "ALTER TABLE {$table} ADD COLUMN admin_intervened TINYINT(1) NOT NULL DEFAULT 0 AFTER admin_takeover" );
					// Migrate existing data: set admin_intervened = 1 for sessions that have admin messages.
					$messages_table = self::messages_table();
					$wpdb->query( "UPDATE {$table} s SET s.admin_intervened = 1 WHERE EXISTS (SELECT 1 FROM {$messages_table} m WHERE m.session_id = s.session_id AND m.is_admin = 1)" );
				}
			}

			// v1.5.0: Remove obsolete keywords column from site_content table.
			// Keywords are no longer used - content is injected directly into AI prompts.
			if ( version_compare( $installed_version, '1.5.0', '<' ) ) {
				global $wpdb;
				$table = self::site_content_table();

				// Check if table exists first.
				$table_exists = $wpdb->get_var(
					$wpdb->prepare(
						'SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = %s AND table_name = %s',
						DB_NAME,
						$table
					)
				);

				if ( $table_exists && self::column_exists( $table, 'keywords' ) ) {
					// Drop the old FULLTEXT index that includes keywords.
					$wpdb->query( "ALTER TABLE {$table} DROP INDEX search_idx" );

					// Drop the keywords column.
					$wpdb->query( "ALTER TABLE {$table} DROP COLUMN keywords" );

					// Recreate FULLTEXT index without keywords.
					$wpdb->query( "ALTER TABLE {$table} ADD FULLTEXT KEY search_idx (title, excerpt)" );
				}
			}

			// v1.6.0: Add product_data column for WooCommerce enriched product storage.
			if ( version_compare( $installed_version, '1.6.0', '<' ) ) {
				global $wpdb;
				$table = self::site_content_table();

				if ( ! self::column_exists( $table, 'product_data' ) ) {
					$wpdb->query( "ALTER TABLE {$table} ADD COLUMN product_data TEXT DEFAULT NULL AFTER excerpt" );
				}
			}

			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			update_option( 'agentorie_chat_db_version', self::DB_VERSION );
		}
	}

	/**
	 * Convert a date stored in the site timezone (current_time( 'mysql' )) to ISO 8601
	 * with its UTC offset, so that browsers show it in the reader's own timezone.
	 *
	 * @since 1.2.0
	 * @param mixed $datetime Date in 'Y-m-d H:i:s' format, site timezone.
	 * @return mixed ISO 8601 date, or the value unchanged if it is not a date.
	 */
	public static function to_iso8601( $datetime ) {
		if ( ! is_string( $datetime ) || '' === $datetime || 0 === strpos( $datetime, '0000-00-00' ) ) {
			return $datetime;
		}

		$date = date_create_immutable( $datetime, wp_timezone() );

		return $date ? $date->format( DATE_ATOM ) : $datetime;
	}

	/**
	 * Convert the date fields of rows read from the plugin tables to ISO 8601.
	 *
	 * @since 1.2.0
	 * @param array $rows Rows (associative arrays) or a single row.
	 * @return array Same rows with created_at, last_activity and taken_over_at converted.
	 */
	public static function dates_to_iso8601( array $rows ) {
		$keys = array( 'created_at', 'last_activity', 'taken_over_at' );

		if ( ! empty( $rows ) && ! isset( $rows[0] ) ) {
			foreach ( $keys as $key ) {
				if ( isset( $rows[ $key ] ) ) {
					$rows[ $key ] = self::to_iso8601( $rows[ $key ] );
				}
			}
			return $rows;
		}

		foreach ( $rows as $index => $row ) {
			if ( is_array( $row ) ) {
				$rows[ $index ] = self::dates_to_iso8601( $row );
			}
		}

		return $rows;
	}

	/**
	 * Get the Unix timestamp of a date stored in the site timezone.
	 *
	 * @since 1.2.0
	 * @param mixed $datetime Date in 'Y-m-d H:i:s' format, site timezone.
	 * @return int Unix timestamp, 0 if the date is invalid.
	 */
	public static function to_timestamp( $datetime ) {
		if ( ! is_string( $datetime ) || '' === $datetime ) {
			return 0;
		}

		$date = date_create_immutable( $datetime, wp_timezone() );

		return $date ? $date->getTimestamp() : 0;
	}

	/**
	 * Get the sessions table name.
	 *
	 * @since 1.0.0
	 * @return string Table name with prefix.
	 */
	public static function sessions_table() {
		global $wpdb;
		return $wpdb->prefix . 'agentorie_sessions';
	}

	/**
	 * Get the messages table name.
	 *
	 * @since 1.0.0
	 * @return string Table name with prefix.
	 */
	public static function messages_table() {
		global $wpdb;
		return $wpdb->prefix . 'agentorie_chat_messages';
	}

	/**
	 * Get the contacts table name.
	 *
	 * @since 1.0.0
	 * @return string Table name with prefix.
	 */
	public static function contacts_table() {
		global $wpdb;
		return $wpdb->prefix . 'agentorie_contacts';
	}

	/**
	 * Get the site content table name for search indexing.
	 *
	 * @return string Table name with prefix.
	 */
	public static function site_content_table() {
		global $wpdb;
		return $wpdb->prefix . 'agentorie_site_content';
	}

	/**
	 * Check if a column exists in a table.
	 *
	 * Uses SHOW COLUMNS instead of INFORMATION_SCHEMA for better
	 * compatibility with shared hosting environments that may have
	 * restricted permissions.
	 *
	 * @since 1.0.0
	 * @param string $table  Full table name (with prefix).
	 * @param string $column Column name to check.
	 * @return bool True if column exists, false otherwise.
	 */
	private static function column_exists( $table, $column ) {
		global $wpdb;

		// Escape table name for use in SHOW COLUMNS (can't use prepare for table names).
		$table = esc_sql( $table );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method
		$result = $wpdb->get_results(
			$wpdb->prepare(
				"SHOW COLUMNS FROM `{$table}` LIKE %s",
				$column
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return ! empty( $result );
	}

	/**
	 * Create plugin database tables.
	 *
	 * Uses dbDelta for safe table creation and upgrades.
	 *
	 * @since 1.0.0
	 * @return void
	 */
	public static function create_tables() {
		global $wpdb;

		$charset_collate = $wpdb->get_charset_collate();
		$sessions_table  = self::sessions_table();
		$messages_table  = self::messages_table();
		$contacts_table  = self::contacts_table();
		$content_table   = self::site_content_table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$sql = "CREATE TABLE {$sessions_table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			session_id VARCHAR(64) NOT NULL,
			visitor_id VARCHAR(32) DEFAULT NULL,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			last_activity DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			admin_takeover TINYINT(1) NOT NULL DEFAULT 0,
			admin_intervened TINYINT(1) NOT NULL DEFAULT 0,
			human_requested TINYINT(1) NOT NULL DEFAULT 0,
			negotiate_detected TINYINT(1) NOT NULL DEFAULT 0,
			current_url VARCHAR(2048) DEFAULT NULL,
			page_title VARCHAR(255) DEFAULT NULL,
			is_active TINYINT(1) NOT NULL DEFAULT 1,
			is_excluded TINYINT(1) NOT NULL DEFAULT 0,
			PRIMARY KEY (id),
			UNIQUE KEY session_id (session_id),
			KEY visitor_id (visitor_id),
			KEY is_active (is_active),
			KEY last_activity (last_activity),
			KEY is_excluded (is_excluded),
			KEY admin_intervened (admin_intervened),
			KEY idx_active_activity (is_active, last_activity)
		) {$charset_collate};

		CREATE TABLE {$messages_table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			session_id VARCHAR(64) NOT NULL,
			role ENUM('user', 'assistant') NOT NULL DEFAULT 'user',
			message TEXT NOT NULL,
			is_admin TINYINT(1) NOT NULL DEFAULT 0,
			admin_id BIGINT UNSIGNED DEFAULT NULL,
			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 role (role),
			KEY is_read (is_read),
			KEY created_at (created_at),
			KEY admin_id (admin_id)
		) {$charset_collate};

		CREATE TABLE {$contacts_table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			session_id VARCHAR(64) NOT NULL,
			name VARCHAR(255) DEFAULT NULL,
			email VARCHAR(255) DEFAULT NULL,
			phone VARCHAR(50) DEFAULT NULL,
			message TEXT DEFAULT NULL,
			created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY session_id (session_id),
			KEY email (email),
			KEY created_at (created_at)
		) {$charset_collate};

		CREATE TABLE {$content_table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			post_id BIGINT UNSIGNED NOT NULL,
			post_type VARCHAR(50) NOT NULL DEFAULT 'page',
			title VARCHAR(255) NOT NULL,
			url VARCHAR(500) NOT NULL,
			excerpt TEXT,
			product_data TEXT DEFAULT NULL,
			is_indexed TINYINT(1) NOT NULL DEFAULT 1,
			last_sync DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY unique_post (post_id),
			KEY post_type (post_type),
			KEY is_indexed (is_indexed),
			FULLTEXT KEY search_idx (title, excerpt)
		) {$charset_collate};";
		// phpcs:enable

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );

		update_option( 'agentorie_chat_db_version', self::DB_VERSION );
	}

	/**
	 * Drop all plugin database tables.
	 *
	 * @since 1.0.0
	 * @return void
	 */
	public static function drop_tables() {
		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from internal methods, not user input
		$wpdb->query( 'DROP TABLE IF EXISTS ' . self::messages_table() );
		$wpdb->query( 'DROP TABLE IF EXISTS ' . self::contacts_table() );
		$wpdb->query( 'DROP TABLE IF EXISTS ' . self::sessions_table() );
		$wpdb->query( 'DROP TABLE IF EXISTS ' . self::site_content_table() );
		// Also drop legacy visitors table if exists.
		$wpdb->query( 'DROP TABLE IF EXISTS ' . $wpdb->prefix . 'agentorie_visitors' );
		// phpcs:enable
		delete_option( 'agentorie_chat_db_version' );
	}

	/**
	 * Insert or update a chat session.
	 *
	 * @since 1.0.0
	 * @param array $data Session data.
	 * @return int|false Session ID on success, false on failure.
	 */
	public static function upsert_session( $data ) {
		global $wpdb;

		$defaults = array(
			'session_id'         => '',
			'visitor_id'         => null,
			'admin_takeover'     => 0,
			'human_requested'    => 0,
			'negotiate_detected' => 0,
			'current_url'        => null,
			'page_title'         => null,
			'is_active'          => 1,
			'is_excluded'        => 0,
			'last_activity'      => current_time( 'mysql' ),
		);

		$data     = wp_parse_args( $data, $defaults );
		$existing = self::get_session( $data['session_id'] );

		if ( $existing ) {
			// Build update data - always include these fields.
			$update_data   = array(
				'last_activity'      => $data['last_activity'],
				'admin_takeover'     => $data['admin_takeover'],
				'human_requested'    => $data['human_requested'],
				'negotiate_detected' => $data['negotiate_detected'],
				'is_active'          => $data['is_active'],
			);
			$update_format = array( '%s', '%d', '%d', '%d', '%d' );

			// Only update visitor_id if provided and not empty (don't overwrite existing with empty).
			if ( ! empty( $data['visitor_id'] ) ) {
				$update_data['visitor_id'] = $data['visitor_id'];
				$update_format[]           = '%s';
			}

			// Only update current_url if provided and not empty.
			if ( ! empty( $data['current_url'] ) ) {
				$update_data['current_url'] = $data['current_url'];
				$update_format[]            = '%s';
			}

			// Only update page_title if provided and not empty.
			if ( ! empty( $data['page_title'] ) ) {
				$update_data['page_title'] = $data['page_title'];
				$update_format[]           = '%s';
			}

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->update(
				self::sessions_table(),
				$update_data,
				array( 'session_id' => $data['session_id'] ),
				$update_format,
				array( '%s' )
			);
			return $existing['id'];
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$result = $wpdb->insert(
			self::sessions_table(),
			array(
				'session_id'         => $data['session_id'],
				'visitor_id'         => $data['visitor_id'],
				'admin_takeover'     => $data['admin_takeover'],
				'human_requested'    => $data['human_requested'],
				'negotiate_detected' => $data['negotiate_detected'],
				'current_url'        => $data['current_url'],
				'page_title'         => $data['page_title'],
				'is_active'          => $data['is_active'],
				'is_excluded'        => $data['is_excluded'],
				'last_activity'      => $data['last_activity'],
				'created_at'         => current_time( 'mysql' ),
			),
			array( '%s', '%s', '%d', '%d', '%d', '%s', '%s', '%d', '%d', '%s', '%s' )
		);

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

	/**
	 * Get a session by session ID.
	 *
	 * @since 1.0.0
	 * @param string $session_id Session ID.
	 * @return array|null Session data or null if not found.
	 */
	public static function get_session( $session_id ) {
		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method
		$result = $wpdb->get_row(
			$wpdb->prepare( 'SELECT * FROM ' . self::sessions_table() . ' WHERE session_id = %s', $session_id ),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $result ? $result : null;
	}

	/**
	 * Get an existing session or create a new one.
	 *
	 * @since 1.0.0
	 * @param string $session_id Session ID.
	 * @param array  $data       Optional session data for creation.
	 * @return array Session data.
	 */
	public static function get_or_create_session( $session_id, $data = array() ) {
		$existing = self::get_session( $session_id );
		if ( $existing ) {
			return $existing;
		}

		if ( function_exists( 'agentorie_chat_is_ip_excluded' ) && agentorie_chat_is_ip_excluded() ) {
			$data['is_excluded'] = 1;
		}

		self::upsert_session( array_merge( array( 'session_id' => $session_id ), $data ) );
		$session = self::get_session( $session_id );
		return $session ? $session : $data;
	}

	/**
	 * Update session fields.
	 *
	 * @since 1.0.0
	 * @param string $session_id Session ID.
	 * @param array  $data       Fields to update.
	 * @return bool True on success, false on failure.
	 */
	public static function update_session( $session_id, $data ) {
		global $wpdb;

		$allowed_fields = array( 'visitor_id', 'admin_takeover', 'admin_intervened', 'human_requested', 'negotiate_detected', 'current_url', 'page_title', 'is_active', 'last_activity' );
		$update_data    = array();
		$update_format  = array();

		foreach ( $allowed_fields as $field ) {
			if ( isset( $data[ $field ] ) ) {
				$update_data[ $field ] = $data[ $field ];
				if ( in_array( $field, array( 'admin_takeover', 'admin_intervened', 'human_requested', 'negotiate_detected', 'is_active' ), true ) ) {
					$update_format[] = '%d';
				} else {
					$update_format[] = '%s';
				}
			}
		}

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

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$result = $wpdb->update( self::sessions_table(), $update_data, array( 'session_id' => $session_id ), $update_format, array( '%s' ) );
		return false !== $result;
	}

	/**
	 * Update a single session field.
	 *
	 * @param string $session_id Session ID.
	 * @param string $field      Field name.
	 * @param mixed  $value      Field value.
	 * @return bool True on success.
	 */
	public static function update_session_field( $session_id, $field, $value ) {
		return self::update_session( $session_id, array( $field => $value ) );
	}

	/**
	 * Delete a session and its related messages and contacts.
	 *
	 * @since 1.0.0
	 * @param string $session_id Session ID.
	 * @return bool True on success, false on failure.
	 */
	public static function delete_session( $session_id ) {
		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$wpdb->delete( self::messages_table(), array( 'session_id' => $session_id ), array( '%s' ) );
		$wpdb->delete( self::contacts_table(), array( 'session_id' => $session_id ), array( '%s' ) );
		$result = $wpdb->delete( self::sessions_table(), array( 'session_id' => $session_id ), array( '%s' ) );
		// phpcs:enable
		return false !== $result;
	}

	/**
	 * Get active conversations within a timeout window.
	 *
	 * @since 1.0.0
	 * @param int $timeout_minutes Inactivity timeout in minutes.
	 * @return array List of active conversations.
	 */
	public static function get_active_conversations( $timeout_minutes = 60 ) {
		global $wpdb;

		$sessions_table = self::sessions_table();
		$messages_table = self::messages_table();
		$cutoff         = wp_date( 'Y-m-d H:i:s', strtotime( "-{$timeout_minutes} minutes" ) );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$sql = "SELECT s.*, COUNT(m.id) as msg_count,
				SUM(CASE WHEN m.role = 'user' THEN 1 ELSE 0 END) as client_msg_count,
				SUM(CASE WHEN m.is_read = 0 AND m.role = 'user' THEN 1 ELSE 0 END) as unread_count,
				(SELECT message FROM {$messages_table} WHERE session_id = s.session_id AND role = 'user' ORDER BY created_at DESC LIMIT 1) as last_client_message,
				(SELECT message FROM {$messages_table} WHERE session_id = s.session_id AND role = 'user' ORDER BY created_at DESC LIMIT 1) as last_message
			FROM {$sessions_table} s
			LEFT JOIN {$messages_table} m ON m.session_id = s.session_id
			WHERE s.is_active = 1 AND s.last_activity >= %s
			GROUP BY s.id ORDER BY s.last_activity DESC";

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql built with internal table names
		$results = $wpdb->get_results( $wpdb->prepare( $sql, $cutoff ), ARRAY_A );
		// phpcs:enable
		return $results ? $results : array();
	}

	/**
	 * Get all conversations with filters and sorting.
	 *
	 * @param int    $limit           Number of conversations to retrieve.
	 * @param int    $offset          Offset for pagination.
	 * @param string $filter_date     Date filter (today, yesterday, 7days, 30days, 90days).
	 * @param string $filter_flag     Flag filter (admin, human, negotiate, none).
	 * @param string $filter_messages Message count filter (1-5, 6-10, 10+).
	 * @param string $search          Search term for session_id or visitor_id.
	 * @param string $orderby         Column to order by.
	 * @param string $order           Order direction (ASC or DESC).
	 * @return array
	 */
	public static function get_all_conversations( $limit = 20, $offset = 0, $filter_date = '', $filter_flag = '', $filter_messages = '', $search = '', $orderby = 'created_at', $order = 'DESC' ) {
		global $wpdb;

		$sessions_table = self::sessions_table();
		$messages_table = self::messages_table();

		$where_clauses = array( '1=1' );
		$query_params  = array();

		// Date filter.
		if ( '' !== $filter_date ) {
			switch ( $filter_date ) {
				case 'today':
					$where_clauses[] = 's.created_at >= %s';
					$query_params[]  = wp_date( 'Y-m-d 00:00:00' );
					break;
				case 'yesterday':
					$where_clauses[] = 's.created_at >= %s AND s.created_at < %s';
					$query_params[]  = wp_date( 'Y-m-d 00:00:00', strtotime( '-1 day' ) );
					$query_params[]  = wp_date( 'Y-m-d 00:00:00' );
					break;
				case '7days':
					$where_clauses[] = 's.created_at >= %s';
					$query_params[]  = wp_date( 'Y-m-d 00:00:00', strtotime( '-7 days' ) );
					break;
				case '30days':
					$where_clauses[] = 's.created_at >= %s';
					$query_params[]  = wp_date( 'Y-m-d 00:00:00', strtotime( '-30 days' ) );
					break;
				case '90days':
					$where_clauses[] = 's.created_at >= %s';
					$query_params[]  = wp_date( 'Y-m-d 00:00:00', strtotime( '-90 days' ) );
					break;
			}
		}

		// Flag filter.
		if ( '' !== $filter_flag ) {
			switch ( $filter_flag ) {
				case 'admin':
					$where_clauses[] = 's.admin_intervened = 1';
					break;
				case 'human':
					$where_clauses[] = 's.human_requested = 1';
					break;
				case 'negotiate':
					$where_clauses[] = 's.negotiate_detected = 1';
					break;
				case 'none':
					$where_clauses[] = 's.admin_intervened = 0 AND s.human_requested = 0 AND s.negotiate_detected = 0';
					break;
			}
		}

		// Search filter.
		if ( '' !== $search ) {
			$search_like     = '%' . $wpdb->esc_like( $search ) . '%';
			$where_clauses[] = '(s.session_id LIKE %s OR s.visitor_id LIKE %s)';
			$query_params[]  = $search_like;
			$query_params[]  = $search_like;
		}

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

		// Message count filter (applied via HAVING).
		$having_sql = '';
		if ( '' !== $filter_messages ) {
			switch ( $filter_messages ) {
				case '1-5':
					$having_sql = 'HAVING msg_count BETWEEN 1 AND 5';
					break;
				case '6-10':
					$having_sql = 'HAVING msg_count BETWEEN 6 AND 10';
					break;
				case '10+':
					$having_sql = 'HAVING msg_count > 10';
					break;
			}
		}

		// Validate orderby.
		$allowed_orderby = array( 'created_at', 'msg_count', 'session_id', 'visitor_id' );
		if ( ! in_array( $orderby, $allowed_orderby, true ) ) {
			$orderby = 'created_at';
		}

		// Validate order.
		$order = strtoupper( $order );
		if ( ! in_array( $order, array( 'ASC', 'DESC' ), true ) ) {
			$order = 'DESC';
		}

		// Build ORDER BY column reference.
		$orderby_column = 's.' . $orderby;
		if ( 'msg_count' === $orderby ) {
			$orderby_column = 'msg_count';
		}

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$sql = "SELECT s.*, COUNT(m.id) as msg_count,
				SUM(CASE WHEN m.role = 'user' THEN 1 ELSE 0 END) as client_msg_count,
				(SELECT message FROM {$messages_table} WHERE session_id = s.session_id AND role = 'user' ORDER BY created_at DESC LIMIT 1) as last_message
			FROM {$sessions_table} s
			LEFT JOIN {$messages_table} m ON m.session_id = s.session_id
			WHERE {$where_sql}
			GROUP BY s.id
			{$having_sql}
			ORDER BY {$orderby_column} {$order}
			LIMIT %d OFFSET %d";

		$query_params[] = $limit;
		$query_params[] = $offset;

		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql built with internal table names and whitelisted columns
		$results = $wpdb->get_results( $wpdb->prepare( $sql, $query_params ), ARRAY_A );
		// phpcs:enable

		return $results ? $results : array();
	}

	/**
	 * Count all conversations with filters.
	 *
	 * @param string $filter_date     Date filter.
	 * @param string $filter_flag     Flag filter.
	 * @param string $filter_messages Message count filter.
	 * @param string $search          Search term.
	 * @return int
	 */
	public static function count_all_conversations( $filter_date = '', $filter_flag = '', $filter_messages = '', $search = '' ) {
		global $wpdb;

		$sessions_table = self::sessions_table();
		$messages_table = self::messages_table();

		$where_clauses = array( '1=1' );
		$query_params  = array();

		// Date filter.
		if ( '' !== $filter_date ) {
			switch ( $filter_date ) {
				case 'today':
					$where_clauses[] = 's.created_at >= %s';
					$query_params[]  = wp_date( 'Y-m-d 00:00:00' );
					break;
				case 'yesterday':
					$where_clauses[] = 's.created_at >= %s AND s.created_at < %s';
					$query_params[]  = wp_date( 'Y-m-d 00:00:00', strtotime( '-1 day' ) );
					$query_params[]  = wp_date( 'Y-m-d 00:00:00' );
					break;
				case '7days':
					$where_clauses[] = 's.created_at >= %s';
					$query_params[]  = wp_date( 'Y-m-d 00:00:00', strtotime( '-7 days' ) );
					break;
				case '30days':
					$where_clauses[] = 's.created_at >= %s';
					$query_params[]  = wp_date( 'Y-m-d 00:00:00', strtotime( '-30 days' ) );
					break;
				case '90days':
					$where_clauses[] = 's.created_at >= %s';
					$query_params[]  = wp_date( 'Y-m-d 00:00:00', strtotime( '-90 days' ) );
					break;
			}
		}

		// Flag filter.
		if ( '' !== $filter_flag ) {
			switch ( $filter_flag ) {
				case 'admin':
					$where_clauses[] = 's.admin_intervened = 1';
					break;
				case 'human':
					$where_clauses[] = 's.human_requested = 1';
					break;
				case 'negotiate':
					$where_clauses[] = 's.negotiate_detected = 1';
					break;
				case 'none':
					$where_clauses[] = 's.admin_intervened = 0 AND s.human_requested = 0 AND s.negotiate_detected = 0';
					break;
			}
		}

		// Search filter.
		if ( '' !== $search ) {
			$search_like     = '%' . $wpdb->esc_like( $search ) . '%';
			$where_clauses[] = '(s.session_id LIKE %s OR s.visitor_id LIKE %s)';
			$query_params[]  = $search_like;
			$query_params[]  = $search_like;
		}

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

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		if ( '' !== $filter_messages ) {
			$having_sql = '';
			switch ( $filter_messages ) {
				case '1-5':
					$having_sql = 'HAVING msg_count BETWEEN 1 AND 5';
					break;
				case '6-10':
					$having_sql = 'HAVING msg_count BETWEEN 6 AND 10';
					break;
				case '10+':
					$having_sql = 'HAVING msg_count > 10';
					break;
			}

			$sql = "SELECT COUNT(*) FROM (
						SELECT s.session_id, COUNT(m.id) as msg_count
						FROM {$sessions_table} s
						LEFT JOIN {$messages_table} m ON s.session_id = m.session_id
						WHERE {$where_sql}
						GROUP BY s.session_id
						{$having_sql}
					) as filtered";
		} else {
			$sql = "SELECT COUNT(DISTINCT s.session_id)
					FROM {$sessions_table} s
					WHERE {$where_sql}";
		}

		if ( ! empty( $query_params ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql built with internal table names
			$count = (int) $wpdb->get_var( $wpdb->prepare( $sql, $query_params ) );
		} else {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql built with internal table names
			$count = (int) $wpdb->get_var( $sql );
		}
		// phpcs:enable

		return $count;
	}

	/**
	 * Count active sessions within a timeout window.
	 *
	 * @since 1.0.0
	 * @param int $timeout_minutes Inactivity timeout in minutes.
	 * @return int Number of active sessions.
	 */
	public static function count_active_sessions( $timeout_minutes = 60 ) {
		global $wpdb;
		$cutoff = wp_date( 'Y-m-d H:i:s', strtotime( "-{$timeout_minutes} minutes" ) );
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method
		$count = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM ' . self::sessions_table() . ' WHERE is_active = 1 AND last_activity >= %s', $cutoff ) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $count;
	}

	/**
	 * Insert a chat message.
	 *
	 * @since 1.0.0
	 * @param array $data Message data.
	 * @return int|false Message ID on success, false on failure.
	 */
	public static function insert_message( $data ) {
		global $wpdb;

		$defaults = array(
			'session_id' => '',
			'role'       => 'user',
			'message'    => '',
			'is_admin'   => 0,
			'admin_id'   => null,
			'is_read'    => 0,
			'created_at' => current_time( 'mysql' ),
		);

		$data = wp_parse_args( $data, $defaults );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$result = $wpdb->insert(
			self::messages_table(),
			array(
				'session_id' => $data['session_id'],
				'role'       => $data['role'],
				'message'    => $data['message'],
				'is_admin'   => $data['is_admin'],
				'admin_id'   => $data['admin_id'],
				'is_read'    => $data['is_read'],
				'created_at' => $data['created_at'],
			),
			array( '%s', '%s', '%s', '%d', '%d', '%d', '%s' )
		);

		if ( $result ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->update( self::sessions_table(), array( 'last_activity' => current_time( 'mysql' ) ), array( 'session_id' => $data['session_id'] ), array( '%s' ), array( '%s' ) );
		}

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

	/**
	 * Get messages for a session.
	 *
	 * @since 1.0.0
	 * @param string $session_id Session ID.
	 * @param int    $limit      Maximum number of messages.
	 * @param int    $after_id   Return messages after this ID.
	 * @return array List of messages.
	 */
	public static function get_messages( $session_id, $limit = 100, $after_id = 0 ) {
		global $wpdb;
		$table = self::messages_table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method
		if ( $after_id > 0 ) {
			$results = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT m.*, 
					COALESCE(name_meta.meta_value, u.display_name) AS admin_name,
					avatar_meta.meta_value AS admin_avatar
				FROM {$table} m
				LEFT JOIN {$wpdb->usermeta} name_meta 
					ON m.admin_id = name_meta.user_id AND name_meta.meta_key = 'agentorie_chat_livechat_name'
				LEFT JOIN {$wpdb->usermeta} avatar_meta 
					ON m.admin_id = avatar_meta.user_id AND avatar_meta.meta_key = 'agentorie_chat_livechat_avatar'
				LEFT JOIN {$wpdb->users} u ON m.admin_id = u.ID
				WHERE m.session_id = %s AND m.id > %d 
				ORDER BY m.created_at ASC 
				LIMIT %d",
					$session_id,
					$after_id,
					$limit
				),
				ARRAY_A
			);
		} else {
			$results = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT m.*, 
					COALESCE(name_meta.meta_value, u.display_name) AS admin_name,
					avatar_meta.meta_value AS admin_avatar
				FROM {$table} m
				LEFT JOIN {$wpdb->usermeta} name_meta 
					ON m.admin_id = name_meta.user_id AND name_meta.meta_key = 'agentorie_chat_livechat_name'
				LEFT JOIN {$wpdb->usermeta} avatar_meta 
					ON m.admin_id = avatar_meta.user_id AND avatar_meta.meta_key = 'agentorie_chat_livechat_avatar'
				LEFT JOIN {$wpdb->users} u ON m.admin_id = u.ID
				WHERE m.session_id = %s 
				ORDER BY m.created_at ASC 
				LIMIT %d",
					$session_id,
					$limit
				),
				ARRAY_A
			);
		}
		// phpcs:enable
		return $results ? $results : array();
	}

	/**
	 * Mark all messages in a session as read.
	 *
	 * @since 1.0.0
	 * @param string $session_id Session ID.
	 * @return int Number of messages marked as read.
	 */
	public static function mark_messages_read( $session_id ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return (int) $wpdb->update(
			self::messages_table(),
			array( 'is_read' => 1 ),
			array(
				'session_id' => $session_id,
				'is_read'    => 0,
			),
			array( '%d' ),
			array( '%s', '%d' )
		);
	}

	/**
	 * Get unread message count, optionally for a specific session.
	 *
	 * @since 1.0.0
	 * @param string|null $session_id Optional session ID.
	 * @return int Unread message count.
	 */
	public static function get_unread_count( $session_id = null ) {
		global $wpdb;
		$messages_table = self::messages_table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from internal methods
		if ( null !== $session_id ) {
			$count = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$messages_table} WHERE session_id = %s AND is_read = 0 AND role = 'user'", $session_id ) );
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			return $count;
		}

		$sessions_table  = self::sessions_table();
		$timeout_minutes = 60;
		$cutoff          = wp_date( 'Y-m-d H:i:s', strtotime( "-{$timeout_minutes} minutes" ) );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from internal methods
		$count = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$messages_table} m
			INNER JOIN {$sessions_table} s ON m.session_id = s.session_id
			WHERE m.is_read = 0 AND m.role = 'user' AND s.is_active = 1 AND s.last_activity >= %s",
				$cutoff
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $count;
	}

	/**
	 * Insert a contact form submission.
	 *
	 * @since 1.0.0
	 * @param array $data Contact data.
	 * @return int|false Contact ID on success, false on failure.
	 */
	public static function insert_contact( $data ) {
		global $wpdb;

		$defaults = array(
			'session_id' => '',
			'name'       => null,
			'email'      => null,
			'phone'      => null,
			'message'    => null,
			'created_at' => current_time( 'mysql' ),
		);

		$data = wp_parse_args( $data, $defaults );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$result = $wpdb->insert( self::contacts_table(), $data, array( '%s', '%s', '%s', '%s', '%s', '%s' ) );
		return $result ? $wpdb->insert_id : false;
	}

	/**
	 * Get contact submissions with pagination.
	 *
	 * @since 1.0.0
	 * @param int $limit  Number of contacts to retrieve.
	 * @param int $offset Pagination offset.
	 * @return array List of contacts.
	 */
	public static function get_contacts( $limit = 50, $offset = 0 ) {
		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method
		$results = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM ' . self::contacts_table() . ' ORDER BY created_at DESC LIMIT %d OFFSET %d', $limit, $offset ), ARRAY_A );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $results ? $results : array();
	}

	/**
	 * Count conversations created today.
	 *
	 * @since 1.0.0
	 * @return int Number of conversations today.
	 */
	public static function count_conversations_today() {
		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method
		$count = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM ' . self::sessions_table() . ' WHERE created_at >= %s AND is_excluded = 0', wp_date( 'Y-m-d 00:00:00' ) ) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $count;
	}

	/**
	 * Count messages sent today.
	 *
	 * @since 1.0.0
	 * @return int Number of messages today.
	 */
	public static function count_messages_today() {
		global $wpdb;
		$sessions_table = self::sessions_table();
		$messages_table = self::messages_table();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from internal methods
		$count = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$messages_table} m INNER JOIN {$sessions_table} s ON m.session_id = s.session_id WHERE m.created_at >= %s AND s.is_excluded = 0", wp_date( 'Y-m-d 00:00:00' ) ) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $count;
	}

	/**
	 * Count human agent requests today.
	 *
	 * @since 1.0.0
	 * @return int Number of human requests today.
	 */
	public static function count_human_requests_today() {
		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method
		$count = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM ' . self::sessions_table() . ' WHERE human_requested = 1 AND created_at >= %s AND is_excluded = 0', wp_date( 'Y-m-d 00:00:00' ) ) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $count;
	}

	/**
	 * Count conversations created this month.
	 *
	 * @since 1.0.0
	 * @return int Number of conversations this month.
	 */
	public static function count_conversations_this_month() {
		global $wpdb;
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method
		$count = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM ' . self::sessions_table() . ' WHERE created_at >= %s AND is_excluded = 0', wp_date( 'Y-m-01 00:00:00' ) ) );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $count;
	}

	/**
	 * Get messages for multiple sessions in a single query.
	 *
	 * Used by CSV export to avoid N+1 query performance issues.
	 * Returns messages grouped by session_id.
	 *
	 * @since 1.1.4
	 * @param array $session_ids Array of session IDs.
	 * @param int   $limit_per_session Maximum messages per session.
	 * @return array Associative array keyed by session_id, each containing an array of messages.
	 */
	public static function get_messages_batch( $session_ids, $limit_per_session = 500 ) {
		global $wpdb;

		if ( empty( $session_ids ) ) {
			return array();
		}

		$table        = self::messages_table();
		$placeholders = implode( ',', array_fill( 0, count( $session_ids ), '%s' ) );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method, placeholders built dynamically with array_fill
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT session_id, role, message, is_admin, created_at
				FROM {$table}
				WHERE session_id IN ({$placeholders})
				ORDER BY session_id, created_at ASC",
				...$session_ids
			),
			ARRAY_A
		);
		// phpcs:enable

		// Group by session_id and enforce per-session limit.
		$grouped = array();
		$counts  = array();
		if ( $results ) {
			foreach ( $results as $row ) {
				$sid = $row['session_id'];
				if ( ! isset( $counts[ $sid ] ) ) {
					$counts[ $sid ] = 0;
				}
				if ( $counts[ $sid ] < $limit_per_session ) {
					$grouped[ $sid ][] = $row;
					++$counts[ $sid ];
				}
			}
		}

		return $grouped;
	}

	/**
	 * Get message count per day for a given number of days.
	 *
	 * @since 1.0.0
	 * @param int $days Number of days to look back.
	 * @return array Array of date => count entries.
	 */
	public static function get_messages_per_day( $days = 7 ) {
		global $wpdb;
		$table = self::messages_table();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method
		$results = $wpdb->get_results( $wpdb->prepare( "SELECT DATE(created_at) as date, COUNT(*) as count FROM {$table} WHERE created_at >= %s GROUP BY DATE(created_at) ORDER BY date ASC", wp_date( 'Y-m-d 00:00:00', strtotime( '-' . absint( $days ) . ' days' ) ) ), ARRAY_A );
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return $results ? $results : array();
	}

	/**
	 * Get comprehensive chat statistics.
	 *
	 * @since 1.0.0
	 * @return array Associative array of statistics.
	 */
	public static function get_stats() {
		global $wpdb;

		$sessions_table = self::sessions_table();
		$messages_table = self::messages_table();
		$contacts_table = self::contacts_table();

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names from internal methods
		return array(
			'total_conversations' => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$sessions_table} WHERE is_excluded = 0" ),
			'conversations_today' => self::count_conversations_today(),
			'total_messages'      => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$messages_table} m INNER JOIN {$sessions_table} s ON m.session_id = s.session_id WHERE s.is_excluded = 0" ),
			'messages_today'      => self::count_messages_today(),
			'client_messages'     => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$messages_table} m INNER JOIN {$sessions_table} s ON m.session_id = s.session_id WHERE m.role = 'user' AND s.is_excluded = 0" ),
			'bot_messages'        => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$messages_table} m INNER JOIN {$sessions_table} s ON m.session_id = s.session_id WHERE m.role = 'assistant' AND m.is_admin = 0 AND s.is_excluded = 0" ),
			'admin_messages'      => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$messages_table} m INNER JOIN {$sessions_table} s ON m.session_id = s.session_id WHERE m.is_admin = 1 AND s.is_excluded = 0" ),
			'admin_interventions' => (int) $wpdb->get_var( "SELECT COUNT(DISTINCT m.session_id) FROM {$messages_table} m INNER JOIN {$sessions_table} s ON m.session_id = s.session_id WHERE m.is_admin = 1 AND s.is_excluded = 0" ),
			'human_requests'      => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$sessions_table} WHERE human_requested = 1 AND is_excluded = 0" ),
			'negotiations'        => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$sessions_table} WHERE negotiate_detected = 1 AND is_excluded = 0" ),
			'total_contacts'      => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$contacts_table}" ),
		);
		// phpcs:enable
	}

	/**
	 * Mark old sessions as inactive (soft cleanup).
	 *
	 * This method preserves all conversation history in the database for the
	 * webmaster to read and analyze. It only flags sessions as inactive so that
	 * if the same visitor returns later, the widget will start a new conversation
	 * (and a new session will be counted in the monthly quota).
	 *
	 * Conversation data is never deleted by this method. Permanent deletion is
	 * handled exclusively by the GDPR purge tools (manual button in the Stats
	 * tab, or WordPress Privacy Tools).
	 *
	 * @since 1.0.0
	 * @param int $hours Number of hours of inactivity before a session is flagged inactive.
	 * @return int Number of sessions flagged inactive.
	 */
	public static function cleanup_old_sessions( $hours = 6 ) {
		global $wpdb;

		$hours  = max( 1, absint( $hours ) );
		$cutoff = wp_date( 'Y-m-d H:i:s', strtotime( "-{$hours} hours" ) );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from internal method
		$result = $wpdb->query(
			$wpdb->prepare(
				'UPDATE ' . self::sessions_table() . ' SET is_active = 0 WHERE last_activity < %s AND is_active = 1',
				$cutoff
			)
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		return false === $result ? 0 : (int) $result;
	}

	/**
	 * Clean up all old data (sessions, messages, contacts).
	 *
	 * @since 1.0.0
	 * @param int $hours Number of hours of inactivity before cleanup.
	 * @return array Cleanup results by data type.
	 */
	public static function cleanup_old_data( $hours = 168 ) {
		return array(
			'sessions' => self::cleanup_old_sessions( $hours ),
		);
	}
}