<?php
if (!defined('ABSPATH')) die('No direct access allowed');

require_once WPO_PLUGIN_MAIN_PATH . 'vendor/autoload.php';

use PHPSQLParser\PHPSQLParser;

if (!class_exists('WPO_DB_Table_Analysis')) :

class WPO_DB_Table_Analysis {

	/**
	 * Name of the option to enable/disable the analysis
	 *
	 * @var string
	 */
	const ENABLED_SETTING_NAME = 'enable-db-analysis';

	/**
	 * DB queries stack
	 *
	 * @var array
	 */
	private $queries = array();

	/**
	 * Time-to-live for query report data
	 *
	 * @var int
	 */
	const REPORT_DATA_TTL_IN_SECONDS = 3600;

	/**
	 * How many PIDs at least to keep, so we can build statistics, ignoring TTL
	 *
	 * @var int
	 */
	const REPORT_PID_MINIMUM_COUNT_KEEP = 10;

	/**
	 * How many PIDs at most to keep, so we can build statistics, to avoid large report data
	 *
	 * @var int
	 */
	const REPORT_PID_MAXIMUM_COUNT_KEEP = 50;

	/**
	 * How many query snapshots (query per N-seconds grouping count) we want to keep
	 *
	 * @var int
	 */
	const REPORT_SNAPSHOTS_MAXIMUM_COUNT_KEEP = 50;

	/**
	 * How many query execution times we want to keep, sorted from slowest to fastest
	 *
	 * @var int
	 */
	const REPORT_QUERY_MAXIMUM_COUNT_KEEP = 15;

	/**
	 * Window size for snapshots grouping, in seconds
	 *
	 * @var int
	 */
	const SNAP_SECONDS_GROUPING = 5;

	/**
	 * If a query is slower than X nanoseconds, save the call stack in the report
	 *
	 * @var int
	 */
	const QUERY_SAVE_STACK_THRESHOLD_MICROSECONDS = 1000;
	
	/**
	 * WP Option name to store usage data
	 *
	 * @var string
	 */
	const TABLE_USAGE_OPTION_NAME = 'wp-optimize-table-usage';
	
	/**
	 * Group WordPress internal queries under this name
	 *
	 * @var string
	 */
	const WP_INTERNALS_NAME = 'wordpress-internals';

	/**
	 * Parser instance
	 *
	 * @var PHPSQLParser|null
	 */
	private $parser;

	/**
	 * Register the `shutdown` event to do the analysis after user got their response, to avoid slowing down requests
	 * Also register filters to gather query data
	 *
	 * @return void
	 */
	public function __construct() {
		global $wp_version;
		
		$handler = array($this, 'execute_query_analysis');
		if (!has_action('shutdown', $handler)) {
			add_action('shutdown', $handler);
		}
		
		if (!defined('SAVEQUERIES')) {
			define('SAVEQUERIES', true);
		}
		
		if (true === SAVEQUERIES && version_compare($wp_version, '5.3.0', '>=')) {
			// log_query_custom_data filter available since WordPress 5.3.0
			$handler = array($this, 'save_plugin_query_filter');
			if (!has_filter('log_query_custom_data', $handler)) {
				add_filter('log_query_custom_data', $handler, 10, 4);
			}
		} else {
			// We cannot extract extended information about the queries if SAVEQUERIES is disabled
			$handler = array($this, 'save_plugin_query_raw_filter');
			if (!has_filter('query', $handler)) {
				add_filter('query', $handler);
			}
		}
	}

	/**
	 * Initialize the class
	 *
	 * @return WPO_DB_Table_Analysis
	 */
	static public function get_instance() {
		static $_instance = null;
		if (null === $_instance) {
			$_instance = new self();
		}
		return $_instance;
	}

	/**
	 * Filter only the query, without extended information
	 *
	 * @param string $query Query SQL
	 * @return string
	 */
	public function save_plugin_query_raw_filter($query) {
		$this->save_plugin_query_filter(array(), $query, 0, "");

		return $query;
	}

	/**
	 * Filter called on every DB query, saves it for later analysis. Because it is a filter, it has to return the same $query_data
	 *
	 * @param array|null  $query_data      Custom query data.
	 * @param string 	  $query           The query's SQL.
	 * @param float  	  $query_time      Total time spent on the query, in seconds.
	 * @param string 	  $query_callstack Comma-separated list of the calling functions.
	 * @return array
	 */
	public function save_plugin_query_filter($query_data, $query, $query_time, $query_callstack) {
		$plugin_name = self::get_plugin_path_from_debug_backtrace();
		$query_data = $query_data ?? array();

		if (!$this->is_self_reference() && ('' !== $plugin_name)) {
			$this->queries[$plugin_name][] = array(
				"query" => $query,
				"time" => $query_time,
				"stack" => $query_callstack
			);
		}

		return $query_data;
	}

	/**
	 * During `shutdown` event, after user got their response, finish the request before analyzing the query and saving report to database
	 *
	 * @return void
	 */
	public function execute_query_analysis() {

		$this->flush_output_and_finalize_request();

		$table_usage = get_option(self::TABLE_USAGE_OPTION_NAME, array());

		foreach ($this->queries as $plugin_name => $queries) {
			foreach ($queries as $query_data) {

				$query = $query_data['query'] ?? '';

				$table_name = $this->get_query_table($query);
		
				if ('' !== $table_name) {
					$table_usage[$plugin_name][$table_name]['last_used'] = gmdate("Y-m-d H:i:s");

					// Store per-thread usage statistics for the current table
					$this->store_per_thread_table_usage($table_usage, $plugin_name, $table_name);

					// Store per-query execution time statistics for the current table
					$this->store_per_query_table_usage($table_usage, $plugin_name, $table_name, $query_data);

					// If the query is considered slow, store its call stack
					$this->maybe_store_slow_query_stack_table_usage($table_usage, $plugin_name, $table_name, $query_data);

					// Only keep latest data to avoid ever-growing reports
					$table_usage = $this->remove_old_threads($table_usage);
				}
			}
		}

		// Use native `update_option` so we can set autoload=false, we only use this option for query analysis
		update_option(self::TABLE_USAGE_OPTION_NAME, $table_usage, false);
	}

	/**
	 * Stores per-thread usage statistics for the given table.
	 *
	 * @param array  $table_usage The reference to the overall table usage tracking array.
	 * @param string $plugin_name The name of the plugin that executed the query.
	 * @param string $table_name  The name of the table being used.
	 *
	 * @return void
	 */
	private function store_per_thread_table_usage(&$table_usage, $plugin_name, $table_name) {
		static $script_pid = null;

		if (!isset($table_usage[$plugin_name][$table_name]['per_thread'])) {
			$table_usage[$plugin_name][$table_name]['per_thread'] = array();
		}
		
		if (null === $script_pid) {
			$script_pid = function_exists('getmypid') ? getmypid() : false;
		}

		if (false === $script_pid) return;

		// round data to per self::SNAP_SECONDS_GROUPING basis
		$snap_ts = round(time() / self::SNAP_SECONDS_GROUPING) * self::SNAP_SECONDS_GROUPING;
		$snap_ts_utc = gmdate('Y-m-d H:i:s', $snap_ts);

		if (!isset($table_usage[$plugin_name][$table_name]['per_thread'][$script_pid])) {
			$table_usage[$plugin_name][$table_name]['per_thread'][$script_pid] = array('timestamp' => $snap_ts_utc, 'snapshots' => array());
		}

		if (!isset($table_usage[$plugin_name][$table_name]['per_thread'][$script_pid]['snapshots'][$snap_ts_utc])) {
			$table_usage[$plugin_name][$table_name]['per_thread'][$script_pid]['snapshots'][$snap_ts_utc] = 0;
		}

		$table_usage[$plugin_name][$table_name]['per_thread'][$script_pid]['snapshots'][$snap_ts_utc]++;
	}

	/**
	 * Stores per-query usage statistics for the given table.
	 *
	 * @param array  $table_usage The reference to the overall table usage tracking array.
	 * @param string $plugin_name The name of the plugin that executed the query.
	 * @param string $table_name  The name of the table being used.
	 * @param array  $query_data  The query data.
	 *
	 * @return void
	 */
	private function store_per_query_table_usage(&$table_usage, $plugin_name, $table_name, $query_data) {
		$query_md5 = md5($query_data['query']);
		$query_time = round(($query_data['time'] ?? 0) * 1000000); // Convert to microseconds

		if (!isset($table_usage[$plugin_name][$table_name]['per_query'])) {
			$table_usage[$plugin_name][$table_name]['per_query'] = array();
		}

		if (isset($table_usage[$plugin_name][$table_name]['per_query'][$query_md5])) {
			$table_usage[$plugin_name][$table_name]['per_query'][$query_md5] = max($table_usage[$plugin_name][$table_name]['per_query'][$query_md5], $query_time);
		} else {
			$table_usage[$plugin_name][$table_name]['per_query'][$query_md5] = $query_time;
		}
	}

	/**
	 * Stores the query call stack if the query execution time exceeds the slow query threshold.
	 *
	 * This helps to track and debug slow queries by recording their call stack,
	 * but only if their execution time exceeds the defined threshold.
	 *
	 * @param array  $table_usage The reference to the overall table usage tracking array.
	 * @param string $plugin_name The name of the plugin that executed the query.
	 * @param string $table_name  The name of the table being used.
	 * @param array  $query_data  The query data.
	 *
	 * @return void
	 */
	private function maybe_store_slow_query_stack_table_usage(&$table_usage, $plugin_name, $table_name, $query_data) {
		$query_md5 = md5($query_data['query']);
		$query_call_stack = $query_data['stack'] ?? '';

		if (!isset($table_usage[$plugin_name][$table_name]['slow_query_stack'])) {
			$table_usage[$plugin_name][$table_name]['slow_query_stack'] = array();
		}

		if (!empty($query_call_stack) && isset($table_usage[$plugin_name][$table_name]['per_query'][$query_md5]) && self::QUERY_SAVE_STACK_THRESHOLD_MICROSECONDS < $table_usage[$plugin_name][$table_name]['per_query'][$query_md5]) {
			$table_usage[$plugin_name][$table_name]['slow_query_stack'][$query_md5] = $query_call_stack;
		}
	}

	/**
	 * Flushes output buffers and finalizes the client request.
	 *
	 * @return void
	 */
	private function flush_output_and_finalize_request() {
		if (ob_get_contents()) ob_end_clean();
		ignore_user_abort();
		ob_start();
		ob_end_flush();
		flush();
		if (is_callable('fastcgi_finish_request')) {
			fastcgi_finish_request();
		}
		if (is_callable( 'litespeed_finish_request')) {
			litespeed_finish_request();
		}
	}

	/**
	 * Obtain from the stack trace which plugin executed this query
	 *
	 * @return string
	 */
	static public function get_plugin_path_from_debug_backtrace() {
		$ignored_classes = array(
			'wpdb'
		);
		
		$trace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_debug_backtrace -- Using it to detect call stack of a query
		foreach ($trace as $trc) {
			$ignore = isset($trc['class']) && in_array($trc['class'], $ignored_classes);

			if (isset($trc['file']) && (__FILE__ !== $trc['file']) && (false !== strpos($trc['file'], WP_PLUGIN_DIR)) && !$ignore) {
				$aux = explode(WP_PLUGIN_DIR, $trc['file']);
				$aux = explode('/', $aux[1]);
				$plugin_name = $aux[1];
				
				// Check if get_plugins() function exists. This is required on the front end of the
				// site, since it is in a file normally only loaded in the admin.
				if (!function_exists( 'get_plugins')) {
					require_once ABSPATH . 'wp-admin/includes/plugin.php';
				}

				$all_plugins = get_plugins();
				
				foreach ($all_plugins as $plugin_file => $plugin_data) {
					list($plugin_folder) = explode('/', $plugin_file);

					if ($plugin_folder === $plugin_name) {
						return $plugin_file;
					}
				}
			}
		}

		// No plugin folder found in backtrace paths, must be WordPress internals
		return self::WP_INTERNALS_NAME;
	}

	/**
	 * Detect if this class executes the query
	 *
	 * @return boolean
	 */
	private function is_self_reference() {
		$trace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS);  // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_debug_backtrace -- Using it to detect if call stack is a self reference
		
		$this_class_count = 0;

		foreach ($trace as $trc) {
			if (isset($trc['class']) && ('WPO_DB_Table_Analysis' === $trc['class']) && ('analyze_plugin_query' === $trc['function'])) {
				$this_class_count++;
			}
		}

		return $this_class_count > 1;
	}

	/**
	 * Parses a SQL query and extract the target table name, if it couldn't parse return empty string.
	 *
	 * Uses PHPSQLParser to parse the given SQL query and extract the table name
	 * from supported query types: SELECT, UPDATE, DELETE, and INSERT. Each query
	 * type has a corresponding SQL clause used to locate the table name:
	 *
	 * - SELECT: extracted from the FROM clause
	 * - UPDATE: extracted from the UPDATE clause
	 * - DELETE: extracted from the FROM clause
	 * - INSERT: extracted from the INSERT INTO clause
	 *
	 * For subqueries, this method recursively parses the subquery expression to
	 * resolve the innermost table name. Backtick characters are stripped from
	 * the returned table name.
	 *
	 * @param string $query The SQL query string to be parsed.
	 * @return string The extracted table name, or an empty string
	 */
	private function get_query_table($query) {
		$parseable_methods = array(
			'SELECT' => 'FROM',
			'UPDATE' => 'UPDATE',
			'DELETE' => 'FROM',
			'INSERT' => 'INSERT',
		);

		if (!is_string($query) || '' === trim($query)) return '';

		$parsed = $this->parse_query($query);
		if (empty($parsed)) {
			return '';
		}

		if (!is_array($parsed)) {
			$this->error_log('Error parsing query');
			return '';
		}

		$is_parseable = false;
		$method_delimiter = "";
		foreach (array_keys($parsed) as $parsed_sql_command_name) {
			if (isset($parseable_methods[$parsed_sql_command_name])) {
				$is_parseable = true;
				$method_delimiter = $parseable_methods[$parsed_sql_command_name];
				break;
			}
		}

		if ($is_parseable && isset($parsed[$method_delimiter])) {
			$table_name = '';

			foreach ($parsed[$method_delimiter] as $method_parts) {
				if (!is_array($method_parts) || !isset($method_parts['expr_type'])) {
					$this->error_log('Unexpected parser result for query (missing expr_type)');
					continue;
				}

				if ('table' === $method_parts['expr_type']) {
					if (!isset($method_parts['table'])) {
						$this->error_log("Unexpected parser result for query (missing table name)");
						continue;
					}
					$table_name = str_replace('`', '', $method_parts['table']);
				} elseif ('subquery' === $method_parts['expr_type']) {

					if (!isset($method_parts['base_expr'])) {
						$this->error_log("Unexpected parser result for query (missing base_expr)");
						continue;
					}
					
					$table_name = $this->get_query_table($method_parts['base_expr']);
				}
			}

			return $table_name;
		} else {
			return '';
		}
	}

	/**
	 * Parse SQL query with PHPSQLParser
	 *
	 * @param string $query
	 * @return array
	 */
	private function parse_query($query) {
		if (!is_string($query) || '' === trim($query)) return array();
		return $this->get_parser()->parse($query);
	}

	/**
	 * Get PHPSQLParser instance
	 *
	 * @return PHPSQLParser
	 */
	private function get_parser() {
		if (is_null($this->parser)) $this->parser = new PHPSQLParser();
		return $this->parser;
	}

	/**
	 * Get rid of old information, but always leave at least self::REPORT_PID_MINIMUM_COUNT_KEEP pids for statistical purposes
	 *
	 * @param array $usage Array of pid table usage
	 * @return array
	 */
	private function remove_old_threads($usage) {
		$ttl = self::REPORT_DATA_TTL_IN_SECONDS;
		$pid_count_to_keep = self::REPORT_PID_MINIMUM_COUNT_KEEP;
		$pid_max_count_to_keep = self::REPORT_PID_MAXIMUM_COUNT_KEEP;

		$dates_max_count_to_keep = self::REPORT_SNAPSHOTS_MAXIMUM_COUNT_KEEP;

		$remove_from = gmdate("Y-m-d H:i:s", time() - $ttl);

		$pid_keep = array();
		foreach ($usage as $plugin => $tables) {
			foreach ($tables as $table => $pids) {
				
				if (empty($pids['per_thread'])) continue;

				uasort($pids['per_thread'], array($this, 'sort_pids_by_timestamp'));
				foreach ($pids['per_thread'] as $pid => $dates) {
					if ($dates['timestamp'] > $remove_from || count($pid_keep) < $pid_count_to_keep) {
						uksort($dates['snapshots'], array($this, 'sort_snaphots_by_timestamp'));

						$keep_snaps = array();
						foreach ($dates['snapshots'] as $date => $count) {
							if (count($keep_snaps) < $dates_max_count_to_keep) {
								$keep_snaps[$date] = $count;
							}
						}
						$dates['snapshots'] = $keep_snaps;

						$pid_keep[$pid] = $dates;
		
						if (count($pid_keep) >= $pid_max_count_to_keep) {
							break;
						}
					}
				}

				arsort($pids['per_query']);
				$pids['per_query'] = array_slice($pids['per_query'], 0, self::REPORT_QUERY_MAXIMUM_COUNT_KEEP);

				$usage[$plugin][$table]['per_thread'] = $pid_keep;
				$usage[$plugin][$table]['per_query'] = $pids['per_query'];

				foreach ($usage[$plugin][$table]['slow_query_stack'] as $slow_key => $slow_stack) {
					if (!array_key_exists($slow_key, $usage[$plugin][$table]['per_query'])) {
						unset($usage[$plugin][$table]['slow_query_stack'][$slow_key]);
					}
				}
				
			}
		}

		return $usage;
		
	}

	/**
	 * Sort array by timestamp
	 *
	 * @param array $pid_a PID date element
	 * @param array $pid_b PID date element
	 * @return int
	 */
	private function sort_pids_by_timestamp(array $pid_a, array $pid_b): int {
		return $pid_a['timestamp'] < $pid_b['timestamp'] ? 1 : ($pid_a['timestamp'] === $pid_b['timestamp'] ? 0 : -1);
	}

	/**
	 * Sort snapshot array by timestamp
	 *
	 * @param string $timstamp_a UTC date
	 * @param string $timstamp_b UTC date
	 * @return int
	 */
	private function sort_snaphots_by_timestamp(string $timstamp_a, string $timstamp_b): int {
		return $timstamp_a < $timstamp_b ? 1 : ($timstamp_a === $timstamp_b ? 0 : -1);
	}

	/**
	 * Log error messages to debug potential issues with the parser
	 *
	 * @param string $message
	 * @return void
	 */
	private function error_log($message) {
		$debug_backtrace_output = wp_debug_backtrace_summary(); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_wp_debug_backtrace_summary -- We want to log parsing errors to debug potential issues with the parser
		error_log($message . ': ' . $debug_backtrace_output); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- We want to log parsing errors to debug potential issues with the parser
	}

	/**
	 * Accumulate total queries from per thread summary
	 *
	 * @param array $threads Query total per minute per thread
	 * @return float
	 */
	static public function report_query_usage_per_minute($threads) {
		$per_minute = array();

		if (is_array($threads)) {
			foreach ($threads as $info) {
				foreach ($info['snapshots'] as $timestamp => $count) {
					if (!isset($per_minute[$timestamp])) {
						$per_minute[$timestamp] = 0;
					}
					$per_minute[$timestamp] += $count;
				}
			}
		}

		if (0 === count($per_minute)) {
			return 0;
		} else {
			return (int) round(array_sum($per_minute) / count($per_minute), 0);
		}
	}

	/**
	 * Calculate average time for all queries
	 *
	 * @param array $data Query time data
	 * @return string
	 */
	static public function report_query_time_average($data) {
		if (0 === count($data)) {
			return esc_html__("N/A", 'wp-optimize');
		} else {
			return round(array_sum($data) / count($data) / 1000, 2) . ' ms';
		}
	}

	/**
	 * Remove usage data from the DB
	 *
	 * @return void
	 */
	static public function wipe_usage_data() {
		delete_option(self::TABLE_USAGE_OPTION_NAME);
	}
}

endif;
