<?php
/**
 * Database operations for the Activity Log for MCP plugin.
 *
 * @package ALFMCP
 */

namespace ALFMCP\Classes;

use ALFMCP\ALFMCP_Helper;

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

/**
 * Database handler class.
 */
class ALFMCP_DB {

	/**
	 * Database schema version.
	 *
	 * @var string
	 */
	private static $db_version = '0.0.3';

	/**
	 * Option flag marking the one-time legacy header scrub as complete.
	 *
	 * @var string
	 */
	const HEADERS_SCRUBBED_OPTION = 'alfmcp_headers_scrubbed';

	/**
	 * Rows processed per batch by the legacy header scrub.
	 *
	 * @var int
	 */
	const SCRUB_BATCH_SIZE = 500;

	/**
	 * Cache group name.
	 *
	 * @var string
	 */
	const CACHE_GROUP = 'alfmcp';

	/**
	 * Get table name.
	 *
	 * @return string
	 */
	public static function get_table_name() {
		return ALFMCP_Helper::get_table_name();
	}

	/**
	 * Get security events table name.
	 *
	 * @return string
	 */
	public static function get_security_table_name() {
		global $wpdb;
		return $wpdb->prefix . 'alfmcp_security_events';
	}

	/**
	 * Create the database tables.
	 */
	public static function create_table() {
		global $wpdb;

		$table_name      = self::get_table_name();
		$charset_collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table_name} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			route TEXT NOT NULL,
			server_namespace VARCHAR(191) NOT NULL,
			tool_name VARCHAR(191) NULL,
			user_id BIGINT UNSIGNED NULL,
			method VARCHAR(10) NOT NULL,
			mcp_session_id VARCHAR(255) NULL,
			headers LONGTEXT NULL,
			request_body LONGTEXT NULL,
			response_status SMALLINT NULL,
			response_body LONGTEXT NULL,
			tool_status VARCHAR(20) DEFAULT 'unknown',
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY namespace (server_namespace),
			KEY tool (tool_name),
			KEY created (created_at),
			KEY user (user_id)
		) {$charset_collate};";

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

		// Security events table.
		$security_table = self::get_security_table_name();

		$security_sql = "CREATE TABLE {$security_table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			rule_name VARCHAR(50) NOT NULL,
			session_id VARCHAR(255) NULL,
			details LONGTEXT NULL,
			severity VARCHAR(20) NOT NULL DEFAULT 'warning',
			acknowledged TINYINT(1) NOT NULL DEFAULT 0,
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY rule_name (rule_name),
			KEY created (created_at)
		) {$charset_collate};";

		dbDelta( $security_sql );

		// Add composite indexes to existing requests table via ALTER TABLE
		// (dbDelta is unreliable for adding indexes to existing tables).
		self::maybe_add_index( $table_name, 'session_created', '(mcp_session_id, created_at)' );
		self::maybe_add_index( $table_name, 'status_created', '(tool_status, created_at)' );
	}

	/**
	 * Redact sensitive headers on rows written before redaction existed.
	 *
	 * Versions up to 1.0.0 stored every request header verbatim, so installs
	 * upgrading to 1.1.0 are still holding replayable Authorization and Cookie
	 * values in a plaintext column — values the admin UI and the MCP abilities
	 * both hand back out. Redacting on write only protects new rows; the
	 * existing ones have to be scrubbed.
	 *
	 * Runs once, in batches, and only rewrites rows that actually change.
	 *
	 * @return int Number of rows rewritten.
	 */
	public static function scrub_legacy_headers() {
		global $wpdb;

		if ( 'done' === get_option( self::HEADERS_SCRUBBED_OPTION ) ) {
			return 0;
		}

		$table   = self::get_table_name();
		$updated = 0;
		$last_id = 0;

		do {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- One-time upgrade routine over the plugin's own table; caching is irrelevant and flush_cache() runs below.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					'SELECT id, headers FROM %i WHERE id > %d AND headers IS NOT NULL AND headers != %s ORDER BY id ASC LIMIT %d',
					$table,
					$last_id,
					'',
					self::SCRUB_BATCH_SIZE
				),
				ARRAY_A
			);

			if ( empty( $rows ) ) {
				break;
			}

			foreach ( $rows as $row ) {
				$last_id = (int) $row['id'];
				$headers = json_decode( $row['headers'], true );

				if ( ! is_array( $headers ) ) {
					continue;
				}

				$redacted = ALFMCP_Logger::redact_sensitive_headers( $headers );

				if ( $redacted === $headers ) {
					continue;
				}

				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- See above.
				$wpdb->update(
					$table,
					array( 'headers' => wp_json_encode( $redacted ) ),
					array( 'id' => $last_id ),
					array( '%s' ),
					array( '%d' )
				);

				++$updated;
			}
		} while ( count( $rows ) === self::SCRUB_BATCH_SIZE );

		update_option( self::HEADERS_SCRUBBED_OPTION, 'done' );
		self::flush_cache();

		return $updated;
	}

	/**
	 * Add an index to a table if it doesn't already exist.
	 *
	 * @param string $table      Table name.
	 * @param string $index_name Index name.
	 * @param string $columns    Column specification including parentheses.
	 */
	private static function maybe_add_index( $table, $index_name, $columns ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema introspection on information_schema; one-off during table creation.
		$existing = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = %s AND index_name = %s',
				$table,
				$index_name
			)
		);

		if ( '0' === $existing || 0 === (int) $existing ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter -- One-off DDL during table creation; $table and $index_name are internal hardcoded constants, not user input.
			$wpdb->query( "ALTER TABLE `{$table}` ADD INDEX `{$index_name}` {$columns}" );
		}
	}

	/**
	 * Ensure table exists (self-healing).
	 */
	public static function ensure_table_exists() {
		$installed_version = get_option( 'alfmcp_db_version' );
		if ( $installed_version !== self::$db_version ) {
			self::create_table();
			self::scrub_legacy_headers();
			update_option( 'alfmcp_db_version', self::$db_version );
		}
	}

	/**
	 * Flush all caches in the alfmcp group.
	 *
	 * Public so callers that write to the table outside this class (upgrade
	 * routines, tests inserting fixtures directly) can invalidate reads.
	 */
	public static function flush_cache() {
		wp_cache_delete( 'unique_tools', self::CACHE_GROUP );
		wp_cache_delete( 'unique_users', self::CACHE_GROUP );
	}

	/**
	 * Check if a date string includes a time component.
	 *
	 * @param string $date Date or datetime string.
	 * @return bool True if the string contains time (not just Y-m-d).
	 */
	private static function has_time( $date ) {
		return strlen( $date ) > 10;
	}

	/**
	 * Insert a new request log.
	 *
	 * @param array $data Request data.
	 * @return int|false Insert ID or false on failure.
	 */
	public static function insert_request( $data ) {
		global $wpdb;

		self::ensure_table_exists();

		$defaults = array(
			'route'            => '',
			'server_namespace' => '',
			'tool_name'        => null,
			'user_id'          => null,
			'method'           => '',
			'mcp_session_id'   => null,
			'headers'          => null,
			'request_body'     => null,
			'response_status'  => null,
			'response_body'    => null,
			'tool_status'      => 'unknown',
			'created_at'       => current_time( 'mysql' ),
		);

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

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Inserting into plugin's own custom log table; flush_cache() called below.
		$result = $wpdb->insert(
			self::get_table_name(),
			$data,
			array( '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s' )
		);

		if ( false === $result ) {
			return false;
		}

		self::flush_cache();

		return $wpdb->insert_id;
	}

	/**
	 * Update request with response data.
	 *
	 * @param int   $id   Request ID.
	 * @param array $data Response data.
	 * @return bool
	 */
	public static function update_response( $id, $data ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Updating plugin's own custom log table; per-row cache invalidated below.
		$result = $wpdb->update(
			self::get_table_name(),
			$data,
			array( 'id' => $id ),
			array( '%d', '%s', '%s' ),
			array( '%d' )
		);

		if ( false !== $result ) {
			wp_cache_delete( 'request_' . $id, self::CACHE_GROUP );
		}

		return false !== $result;
	}

	/**
	 * Get requests with filters and pagination.
	 *
	 * @param array $args Query arguments.
	 * @return array
	 */
	public static function get_requests( $args = array() ) {
		global $wpdb;

		$defaults = array(
			'page'        => 1,
			'per_page'    => 10,
			'date_from'   => null,
			'date_to'     => null,
			'tool'        => null,
			'tool_status' => null,
			'user_id'     => null,
			'search'      => null,
			'orderby'     => 'created_at',
			'order'       => 'DESC',
		);

		$args      = wp_parse_args( $args, $defaults );
		$cache_key = 'requests_' . md5( wp_json_encode( $args ) );
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );

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

		$table = self::get_table_name();

		// Build COUNT query from literal strings only; values collected alongside.
		$count_sql    = 'SELECT COUNT(*) FROM %i WHERE 1=1';
		$count_values = array( $table );

		if ( ! empty( $args['date_from'] ) ) {
			$count_sql     .= self::has_time( $args['date_from'] ) ? ' AND created_at >= %s' : ' AND DATE(created_at) >= %s';
			$count_values[] = $args['date_from'];
		}
		if ( ! empty( $args['date_to'] ) ) {
			$count_sql     .= self::has_time( $args['date_to'] ) ? ' AND created_at <= %s' : ' AND DATE(created_at) <= %s';
			$count_values[] = $args['date_to'];
		}
		if ( ! empty( $args['tool'] ) ) {
			$count_sql     .= ' AND tool_name = %s';
			$count_values[] = $args['tool'];
		}
		if ( ! empty( $args['tool_status'] ) ) {
			$count_sql     .= ' AND tool_name IS NOT NULL AND tool_status = %s';
			$count_values[] = $args['tool_status'];
		}
		if ( isset( $args['user_id'] ) && '' !== $args['user_id'] ) {
			if ( 'guest' === $args['user_id'] ) {
				$count_sql .= ' AND user_id IS NULL';
			} else {
				$count_sql     .= ' AND user_id = %d';
				$count_values[] = intval( $args['user_id'] );
			}
		}
		if ( ! empty( $args['search'] ) ) {
			$like           = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$count_sql     .= ' AND (tool_name LIKE %s OR route LIKE %s)';
			$count_values[] = $like;
			$count_values[] = $like;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $count_sql composed from literal-only conditional appends above; runtime values bound via %s/%d/%i placeholders. Cached above.
		$total = (int) $wpdb->get_var( $wpdb->prepare( $count_sql, $count_values ) );

		$offset          = ( max( 1, $args['page'] ) - 1 ) * $args['per_page'];
		$limit           = absint( $args['per_page'] );
		$allowed_orderby = array( 'created_at', 'user_id', 'route' );
		$orderby         = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'created_at';
		$order           = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';

		// Build SELECT query from literal strings; orderby/order are allowlist-validated above.
		$select_sql    = 'SELECT * FROM %i WHERE 1=1';
		$select_values = array( $table );

		if ( ! empty( $args['date_from'] ) ) {
			$select_sql     .= self::has_time( $args['date_from'] ) ? ' AND created_at >= %s' : ' AND DATE(created_at) >= %s';
			$select_values[] = $args['date_from'];
		}
		if ( ! empty( $args['date_to'] ) ) {
			$select_sql     .= self::has_time( $args['date_to'] ) ? ' AND created_at <= %s' : ' AND DATE(created_at) <= %s';
			$select_values[] = $args['date_to'];
		}
		if ( ! empty( $args['tool'] ) ) {
			$select_sql     .= ' AND tool_name = %s';
			$select_values[] = $args['tool'];
		}
		if ( ! empty( $args['tool_status'] ) ) {
			$select_sql     .= ' AND tool_name IS NOT NULL AND tool_status = %s';
			$select_values[] = $args['tool_status'];
		}
		if ( isset( $args['user_id'] ) && '' !== $args['user_id'] ) {
			if ( 'guest' === $args['user_id'] ) {
				$select_sql .= ' AND user_id IS NULL';
			} else {
				$select_sql     .= ' AND user_id = %d';
				$select_values[] = intval( $args['user_id'] );
			}
		}
		if ( ! empty( $args['search'] ) ) {
			$like            = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$select_sql     .= ' AND (tool_name LIKE %s OR route LIKE %s)';
			$select_values[] = $like;
			$select_values[] = $like;
		}

		$select_sql     .= ' ORDER BY ' . $orderby . ' ' . $order . ' LIMIT %d OFFSET %d';
		$select_values[] = $limit;
		$select_values[] = $offset;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $select_sql composed from literal-only conditional appends above; runtime values bound via %s/%d/%i placeholders. Cached above.
		$results = $wpdb->get_results( $wpdb->prepare( $select_sql, $select_values ), ARRAY_A );

		$result = array(
			'data'  => $results ? $results : array(),
			'total' => $total,
		);

		wp_cache_set( $cache_key, $result, self::CACHE_GROUP, 60 );

		return $result;
	}

	/**
	 * Delete all request logs.
	 *
	 * @return int|false Number of rows deleted or false on failure.
	 */
	public static function clear_all_requests() {
		global $wpdb;

		$table = self::get_table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- TRUNCATE on plugin's own custom log table; flush_cache() called below.
		$result = $wpdb->query( $wpdb->prepare( 'TRUNCATE TABLE %i', $table ) );

		self::flush_cache();

		return $result;
	}

	/**
	 * Get unique tool names.
	 *
	 * @return array
	 */
	public static function get_unique_tools() {
		$cached = wp_cache_get( 'unique_tools', self::CACHE_GROUP );
		if ( false !== $cached ) {
			return $cached;
		}

		global $wpdb;

		$table = self::get_table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Querying plugin's own custom log table; cached above.
		$results = $wpdb->get_col(
			$wpdb->prepare( 'SELECT DISTINCT tool_name FROM %i WHERE tool_name IS NOT NULL ORDER BY tool_name ASC', $table )
		);

		$results = $results ? $results : array();
		wp_cache_set( 'unique_tools', $results, self::CACHE_GROUP, 300 );

		return $results;
	}

	/**
	 * Get unique user IDs.
	 *
	 * @return array
	 */
	public static function get_unique_users() {
		$cached = wp_cache_get( 'unique_users', self::CACHE_GROUP );
		if ( false !== $cached ) {
			return $cached;
		}

		global $wpdb;

		$table = self::get_table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Querying plugin's own custom log table; cached above.
		$results = $wpdb->get_col(
			$wpdb->prepare( 'SELECT DISTINCT user_id FROM %i WHERE user_id IS NOT NULL ORDER BY user_id ASC', $table )
		);

		$results = $results ? array_map( 'intval', $results ) : array();
		wp_cache_set( 'unique_users', $results, self::CACHE_GROUP, 300 );

		return $results;
	}

	/**
	 * Get aggregate statistics.
	 *
	 * @param array $args Optional date_from/date_to filters.
	 * @return array
	 */
	public static function get_stats( $args = array() ) {
		global $wpdb;

		$cache_key = 'stats_' . md5( wp_json_encode( $args ) );
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );

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

		$table = self::get_table_name();

		// Build a literal-only WHERE-tail string and matching value list. Each query
		// below substitutes its own SELECT prefix and reuses these.
		$where_tail   = '';
		$where_values = array();

		if ( ! empty( $args['date_from'] ) ) {
			$where_tail    .= self::has_time( $args['date_from'] ) ? ' AND created_at >= %s' : ' AND DATE(created_at) >= %s';
			$where_values[] = $args['date_from'];
		}
		if ( ! empty( $args['date_to'] ) ) {
			$where_tail    .= self::has_time( $args['date_to'] ) ? ' AND created_at <= %s' : ' AND DATE(created_at) <= %s';
			$where_values[] = $args['date_to'];
		}
		if ( ! empty( $args['tool'] ) ) {
			$where_tail    .= ' AND tool_name = %s';
			$where_values[] = $args['tool'];
		}
		if ( isset( $args['user_id'] ) && '' !== $args['user_id'] ) {
			if ( 'guest' === $args['user_id'] ) {
				$where_tail .= ' AND user_id IS NULL';
			} else {
				$where_tail    .= ' AND user_id = %d';
				$where_values[] = intval( $args['user_id'] );
			}
		}

		$total_sql = 'SELECT COUNT(*) FROM %i WHERE 1=1' . $where_tail;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $total_sql composed from literal-only conditional appends above; runtime values bound via %s/%d/%i placeholders. Cached above.
		$total = (int) $wpdb->get_var( $wpdb->prepare( $total_sql, array_merge( array( $table ), $where_values ) ) );

		$success_sql = "SELECT COUNT(*) FROM %i WHERE tool_status = 'success'" . $where_tail;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $success_sql composed from literal-only conditional appends above; runtime values bound via %s/%d/%i placeholders. Cached above.
		$success_count = (int) $wpdb->get_var( $wpdb->prepare( $success_sql, array_merge( array( $table ), $where_values ) ) );

		$error_sql = "SELECT COUNT(*) FROM %i WHERE tool_status = 'error'" . $where_tail;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $error_sql composed from literal-only conditional appends above; runtime values bound via %s/%d/%i placeholders. Cached above.
		$error_count = (int) $wpdb->get_var( $wpdb->prepare( $error_sql, array_merge( array( $table ), $where_values ) ) );

		$by_tool_sql = 'SELECT tool_name, COUNT(*) as count FROM %i WHERE tool_name IS NOT NULL' . $where_tail . ' GROUP BY tool_name ORDER BY count DESC';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $by_tool_sql composed from literal-only conditional appends above; runtime values bound via %s/%d/%i placeholders. Cached above.
		$by_tool = $wpdb->get_results( $wpdb->prepare( $by_tool_sql, array_merge( array( $table ), $where_values ) ), ARRAY_A );

		$sessions_sql = 'SELECT COUNT(DISTINCT mcp_session_id) FROM %i WHERE mcp_session_id IS NOT NULL' . $where_tail;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sessions_sql composed from literal-only conditional appends above; runtime values bound via %s/%d/%i placeholders. Cached above.
		$unique_sessions = (int) $wpdb->get_var( $wpdb->prepare( $sessions_sql, array_merge( array( $table ), $where_values ) ) );

		$success_rate = $total > 0 ? round( ( $success_count / $total ) * 100, 1 ) : 0;

		$result = array(
			'total_requests'  => $total,
			'success_count'   => $success_count,
			'error_count'     => $error_count,
			'success_rate'    => $success_rate,
			'unique_sessions' => $unique_sessions,
			'by_tool'         => $by_tool ? $by_tool : array(),
		);

		wp_cache_set( $cache_key, $result, self::CACHE_GROUP, 60 );

		return $result;
	}

	/**
	 * Get requests for a session ID with pagination and optional body exclusion.
	 *
	 * @param array $args { session_id, page, per_page, exclude_bodies }.
	 * @return array { data, total }
	 */
	public static function get_by_session( $args = array() ) {
		$defaults = array(
			'session_id'     => '',
			'page'           => 1,
			'per_page'       => 50,
			'exclude_bodies' => false,
		);

		$args      = wp_parse_args( $args, $defaults );
		$cache_key = 'session_' . md5( wp_json_encode( $args ) );
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );

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

		global $wpdb;

		$table  = self::get_table_name();
		$offset = ( max( 1, $args['page'] ) - 1 ) * $args['per_page'];
		$limit  = absint( $args['per_page'] );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Querying plugin's own custom log table; cached above.
		$total = (int) $wpdb->get_var(
			$wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE mcp_session_id = %s', $table, $args['session_id'] )
		);

		if ( $args['exclude_bodies'] ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Querying plugin's own custom log table; cached above.
			$results = $wpdb->get_results(
				$wpdb->prepare( 'SELECT id, route, server_namespace, tool_name, user_id, method, mcp_session_id, response_status, tool_status, created_at FROM %i WHERE mcp_session_id = %s ORDER BY created_at ASC LIMIT %d OFFSET %d', $table, $args['session_id'], $limit, $offset ),
				ARRAY_A
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Querying plugin's own custom log table; cached above.
			$results = $wpdb->get_results(
				$wpdb->prepare( 'SELECT * FROM %i WHERE mcp_session_id = %s ORDER BY created_at ASC LIMIT %d OFFSET %d', $table, $args['session_id'], $limit, $offset ),
				ARRAY_A
			);
		}

		$result = array(
			'data'  => $results ? $results : array(),
			'total' => $total,
		);

		wp_cache_set( $cache_key, $result, self::CACHE_GROUP, 60 );

		return $result;
	}

	/**
	 * Search requests by keyword across route, tool_name, request_body, and response_body.
	 *
	 * @param array $args Search arguments with 'query', 'page', 'per_page'.
	 * @return array
	 */
	public static function search_requests( $args = array() ) {
		global $wpdb;

		$defaults = array(
			'query'    => '',
			'page'     => 1,
			'per_page' => 10,
		);

		$args      = wp_parse_args( $args, $defaults );
		$cache_key = 'search_' . md5( wp_json_encode( $args ) );
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );

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

		$table  = self::get_table_name();
		$search = '%' . $wpdb->esc_like( $args['query'] ) . '%';
		$offset = ( max( 1, $args['page'] ) - 1 ) * $args['per_page'];
		$limit  = absint( $args['per_page'] );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Querying plugin's own custom log table; cached above.
		$total = (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM %i WHERE route LIKE %s OR tool_name LIKE %s OR request_body LIKE %s OR response_body LIKE %s',
				$table,
				$search,
				$search,
				$search,
				$search
			)
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Querying plugin's own custom log table; cached above.
		$results = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM %i WHERE route LIKE %s OR tool_name LIKE %s OR request_body LIKE %s OR response_body LIKE %s ORDER BY created_at DESC LIMIT %d OFFSET %d',
				$table,
				$search,
				$search,
				$search,
				$search,
				$limit,
				$offset
			),
			ARRAY_A
		);

		$result = array(
			'data'  => $results ? $results : array(),
			'total' => $total,
		);

		wp_cache_set( $cache_key, $result, self::CACHE_GROUP, 30 );

		return $result;
	}

	/**
	 * Get recent error entries.
	 *
	 * @param int $limit Number of errors to return.
	 * @return array
	 */
	public static function get_errors( $limit = 20 ) {
		$limit     = absint( $limit );
		$cache_key = 'errors_' . $limit;
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );

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

		global $wpdb;

		$table = self::get_table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Querying plugin's own custom log table; cached above.
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM %i WHERE tool_status = 'error' OR (response_status IS NOT NULL AND response_status >= 400) ORDER BY created_at DESC LIMIT %d",
				$table,
				$limit
			),
			ARRAY_A
		);

		$results = $results ? $results : array();
		wp_cache_set( $cache_key, $results, self::CACHE_GROUP, 30 );

		return $results;
	}

	/**
	 * Get per-tool performance metrics.
	 *
	 * @param string|null $tool_name Optional tool name filter.
	 * @return array
	 */
	public static function get_tool_performance( $tool_name = null ) {
		$cache_key = 'tool_perf_' . md5( (string) $tool_name );
		$cached    = wp_cache_get( $cache_key, self::CACHE_GROUP );

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

		global $wpdb;

		$table = self::get_table_name();

		if ( ! empty( $tool_name ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Querying plugin's own custom log table; cached above.
			$results = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT tool_name, COUNT(*) as total_calls, SUM(CASE WHEN tool_status = 'success' THEN 1 ELSE 0 END) as success_count, SUM(CASE WHEN tool_status = 'error' THEN 1 ELSE 0 END) as error_count, COUNT(DISTINCT user_id) as unique_users FROM %i WHERE tool_name = %s GROUP BY tool_name ORDER BY total_calls DESC",
					$table,
					$tool_name
				),
				ARRAY_A
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Querying plugin's own custom log table; cached above.
			$results = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT tool_name, COUNT(*) as total_calls, SUM(CASE WHEN tool_status = 'success' THEN 1 ELSE 0 END) as success_count, SUM(CASE WHEN tool_status = 'error' THEN 1 ELSE 0 END) as error_count, COUNT(DISTINCT user_id) as unique_users FROM %i WHERE tool_name IS NOT NULL GROUP BY tool_name ORDER BY total_calls DESC",
					$table
				),
				ARRAY_A
			);
		}

		if ( ! $results ) {
			return array();
		}

		$formatted = array_map(
			function ( $row ) {
				$total      = (int) $row['total_calls'];
				$error_rate = $total > 0 ? round( ( (int) $row['error_count'] / $total ) * 100, 1 ) : 0;
				return array(
					'tool_name'     => $row['tool_name'],
					'total_calls'   => $total,
					'success_count' => (int) $row['success_count'],
					'error_count'   => (int) $row['error_count'],
					'error_rate'    => $error_rate,
					'unique_users'  => (int) $row['unique_users'],
				);
			},
			$results
		);

		wp_cache_set( $cache_key, $formatted, self::CACHE_GROUP, 60 );

		return $formatted;
	}

	/**
	 * Delete logs older than a given date.
	 *
	 * @param string $before_date MySQL date string (Y-m-d).
	 * @return int|false Number of rows deleted or false on failure.
	 */
	public static function delete_before( $before_date ) {
		global $wpdb;

		$table = self::get_table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DELETE on plugin's own custom log table; flush_cache() called below.
		$result = $wpdb->query(
			$wpdb->prepare( 'DELETE FROM %i WHERE DATE(created_at) < %s', $table, $before_date )
		);

		self::flush_cache();

		return $result;
	}

	/**
	 * Insert a security event row.
	 *
	 * @param array $data Event data with rule_name, session_id, details, severity.
	 * @return int|false Insert ID or false on failure.
	 */
	public static function insert_security_event( $data ) {
		global $wpdb;

		$defaults = array(
			'rule_name'    => '',
			'session_id'   => null,
			'details'      => null,
			'severity'     => 'warning',
			'acknowledged' => 0,
			'created_at'   => current_time( 'mysql' ),
		);

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

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Inserting into plugin's own custom security events table; write operation.
		$result = $wpdb->insert(
			self::get_security_table_name(),
			$data,
			array( '%s', '%s', '%s', '%s', '%d', '%s' )
		);

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

	/**
	 * Get security events with filters and pagination.
	 *
	 * @param array $args Query arguments: page, per_page, rule_name, severity, date_from, date_to.
	 * @return array { data, total }
	 */
	public static function get_security_events( $args = array() ) {
		global $wpdb;

		$defaults = array(
			'page'      => 1,
			'per_page'  => 20,
			'rule_name' => null,
			'severity'  => null,
			'date_from' => null,
			'date_to'   => null,
			'orderby'   => 'created_at',
			'order'     => 'DESC',
		);

		$args  = wp_parse_args( $args, $defaults );
		$table = self::get_security_table_name();

		// Build COUNT query from literal strings only.
		$count_sql    = 'SELECT COUNT(*) FROM %i WHERE 1=1';
		$count_values = array( $table );

		if ( ! empty( $args['rule_name'] ) ) {
			$count_sql     .= ' AND rule_name = %s';
			$count_values[] = $args['rule_name'];
		}
		if ( ! empty( $args['severity'] ) ) {
			$count_sql     .= ' AND severity = %s';
			$count_values[] = $args['severity'];
		}
		if ( ! empty( $args['date_from'] ) ) {
			$count_sql     .= ' AND created_at >= %s';
			$count_values[] = $args['date_from'];
		}
		if ( ! empty( $args['date_to'] ) ) {
			$count_sql     .= ' AND created_at <= %s';
			$count_values[] = $args['date_to'];
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $count_sql composed from literal-only conditional appends above; runtime values bound via %s/%i placeholders.
		$total = (int) $wpdb->get_var( $wpdb->prepare( $count_sql, $count_values ) );

		$offset          = ( max( 1, $args['page'] ) - 1 ) * $args['per_page'];
		$limit           = absint( $args['per_page'] );
		$allowed_orderby = array( 'created_at' );
		$orderby         = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'created_at';
		$order           = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';

		$select_sql    = 'SELECT * FROM %i WHERE 1=1';
		$select_values = array( $table );

		if ( ! empty( $args['rule_name'] ) ) {
			$select_sql     .= ' AND rule_name = %s';
			$select_values[] = $args['rule_name'];
		}
		if ( ! empty( $args['severity'] ) ) {
			$select_sql     .= ' AND severity = %s';
			$select_values[] = $args['severity'];
		}
		if ( ! empty( $args['date_from'] ) ) {
			$select_sql     .= ' AND created_at >= %s';
			$select_values[] = $args['date_from'];
		}
		if ( ! empty( $args['date_to'] ) ) {
			$select_sql     .= ' AND created_at <= %s';
			$select_values[] = $args['date_to'];
		}

		$select_sql     .= ' ORDER BY ' . $orderby . ' ' . $order . ' LIMIT %d OFFSET %d';
		$select_values[] = $limit;
		$select_values[] = $offset;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $select_sql composed from literal-only conditional appends above; runtime values bound via %s/%d/%i placeholders. orderby/order from allowlist.
		$results = $wpdb->get_results( $wpdb->prepare( $select_sql, $select_values ), ARRAY_A );

		return array(
			'data'  => $results ? $results : array(),
			'total' => $total,
		);
	}

	/**
	 * Get security event stats for the last 7 days.
	 *
	 * @return array { total_alerts, active_sessions, top_rule, success_rate }
	 */
	public static function get_security_stats() {
		global $wpdb;

		$sec_table = self::get_security_table_name();
		$req_table = self::get_table_name();
		$cutoff    = gmdate( 'Y-m-d H:i:s', strtotime( '-7 days', strtotime( current_time( 'mysql' ) ) ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Querying plugin's own custom security events table; 7d rolling stats called sparingly.
		$total_alerts = (int) $wpdb->get_var(
			$wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE created_at >= %s', $sec_table, $cutoff )
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Querying plugin's own custom log table; 7d rolling stats called sparingly.
		$active_sessions = (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(DISTINCT mcp_session_id) FROM %i WHERE mcp_session_id IS NOT NULL AND created_at >= %s',
				$req_table,
				$cutoff
			)
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Querying plugin's own custom security events table; 7d rolling stats called sparingly.
		$top_rule = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT rule_name FROM %i WHERE created_at >= %s GROUP BY rule_name ORDER BY COUNT(*) DESC LIMIT 1',
				$sec_table,
				$cutoff
			)
		);

		$stats        = self::get_stats( array( 'date_from' => $cutoff ) );
		$success_rate = isset( $stats['success_rate'] ) ? $stats['success_rate'] : 0;

		return array(
			'total_alerts'    => $total_alerts,
			'active_sessions' => $active_sessions,
			'top_rule'        => $top_rule ? $top_rule : null,
			'success_rate'    => $success_rate,
		);
	}

	/**
	 * Set the acknowledged state of a security event.
	 *
	 * @param int  $id           Event ID.
	 * @param bool $acknowledged True to acknowledge, false to unacknowledge.
	 * @return bool
	 */
	public static function acknowledge_security_event( $id, $acknowledged = true ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Updating plugin's own custom security events table; write operation.
		$result = $wpdb->update(
			self::get_security_table_name(),
			array( 'acknowledged' => $acknowledged ? 1 : 0 ),
			array( 'id' => absint( $id ) ),
			array( '%d' ),
			array( '%d' )
		);

		return false !== $result;
	}

	/**
	 * Delete all acknowledged security events.
	 *
	 * @return int|false Number of rows deleted or false.
	 */
	public static function clear_acknowledged_events() {
		global $wpdb;

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DELETE on plugin's own custom security events table; write operation.
		return $wpdb->query(
			$wpdb->prepare( 'DELETE FROM %i WHERE acknowledged = 1', self::get_security_table_name() )
		);
	}
}