<?php
/**
 * Database Query Tool.
 *
 * Execute read-only SQL queries (SELECT) using wpdb. Returns results as
 * associative arrays with row limiting for large result sets.
 *
 * @package   Agenistrator
 * @since     1.0.0
 * @author    Alex Ruban <alex@maximum.guru>
 * @author    Maximum Software Inc <contact@maximum.software>
 * @license   GPL-3.0-only
 * @link      https://maximum.software/
 *
 * SPDX-FileCopyrightText: 2025-2026 Alex Ruban <alex@maximum.guru>
 * SPDX-FileCopyrightText: 2025-2026 Maximum Software Inc <contact@maximum.software>
 * SPDX-License-Identifier: GPL-3.0-only
 */

namespace Agenistrator;

// Direct access prevention
if ( ! defined( 'ABSPATH' ) ) {
	return;
}

/**
 * DB_Query_Tool class.
 */
class DB_Query_Tool extends Tool {

	/**
	 * Get the tool ID.
	 *
	 * @return string The tool ID.
	 * @since     1.0.0
	 */
	public function get_id() {
		return 'db_query';
	}

	/**
	 * Get the tool name.
	 *
	 * @return string The tool name.
	 * @since     1.0.0
	 */
	public function get_name() {
		return __( 'Database Query', 'agenistrator' );
	}

	/**
	 * Get the tool description.
	 *
	 * @return string The tool description.
	 * @since     1.0.0
	 */
	public function get_description() {
		return 'Execute a read-only SQL query (SELECT, SHOW, DESCRIBE, EXPLAIN).';
	}

	/**
	 * Get usage instructions for the LLM.
	 *
	 * @return string Instructions for using this tool.
	 * @since     1.0.0
	 */
	public function get_instructions() {
		return 'Use {prefix} placeholder for the WordPress table prefix.';
	}

	/**
	 * Check if this tool is read-only.
	 *
	 * @return bool True if the tool only reads data.
	 * @since     1.0.0
	 */
	public function is_read_only() {
		return true;
	}

	/**
	 * Get the parameters schema for this tool.
	 *
	 * @return array Parameters schema in JSON Schema format.
	 * @since     1.0.0
	 */
	public function get_parameters() {
		return array(
			'type'       => 'object',
			'properties' => array(
				'query'  => array(
					'type'        => 'string',
					'description' => 'SQL query to execute. Use {prefix} for table prefix (e.g., "SELECT * FROM {prefix}posts LIMIT 10").',
				),
				'limit'  => array(
					'type'        => 'integer',
					'description' => 'Maximum number of rows to return. Default: 100.',
				),
				'offset' => array(
					'type'        => 'integer',
					'description' => 'Number of rows to skip before returning results. Default: 0.',
				),
			),
			'required'   => array( 'query' ),
		);
	}

	/**
	 * Get the default enabled status for this tool.
	 *
	 * @return bool True if enabled by default, false otherwise.
	 * @since     1.0.0
	 */
	public function get_default_enabled_status() {
		return true;
	}

	/**
	 * Check if the current user can execute this tool.
	 *
	 * @return bool True if the current user has permission.
	 * @since     1.0.0
	 */
	public function current_user_can_execute() {
		return is_super_admin();
	}

	/**
	 * Execute the tool with the given arguments.
	 *
	 * @param array $args The tool arguments.
	 *
	 * @return array The tool execution result.
	 * @since     1.0.0
	 */
	protected function execute( $args ) {
		global $wpdb;

		$query  = $args['query'];
		$limit  = (int) ( $args['limit'] ?? 100 );
		$offset = (int) ( $args['offset'] ?? 0 );

		$query = str_replace( '{prefix}', $wpdb->prefix, $query );

		$query_upper      = strtoupper( trim( $query ) );
		$allowed_prefixes = array( 'SELECT', 'SHOW', 'DESCRIBE', 'DESC', 'EXPLAIN' );
		$is_allowed       = false;
		foreach ( $allowed_prefixes as $prefix ) {
			if ( strncmp( $query_upper, $prefix, strlen( $prefix ) ) === 0 ) {
				$is_allowed = true;
				break;
			}
		}

		if ( ! $is_allowed ) {
			return $this->error_response( 'Only SELECT, SHOW, DESCRIBE, and EXPLAIN queries are allowed. Use db_execute for write operations.' );
		}

		// Add LIMIT and OFFSET if not already present
		if ( stripos( $query, 'LIMIT' ) === false ) {
			// Query one extra row to determine has_more
			// $limit and $offset are cast to int above, safe for interpolation
			$query = rtrim( $query, ';' ) . ' LIMIT ' . ( $limit + 1 ) . " OFFSET {$offset}";
		}

		$previous_suppress = $wpdb->suppress_errors( true );
		try {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $query is intentionally raw SQL from admin tool call; caching not applicable.
			$results = $wpdb->get_results( $query, ARRAY_A );
			$error   = $wpdb->last_error;
		} finally {
			$wpdb->suppress_errors( $previous_suppress );
		}

		if ( $error ) {
			return $this->error_response( 'Query failed: ' . $error );
		}

		// Determine has_more by checking if we got more rows than requested
		$has_more = count( $results ) > $limit;
		if ( $has_more ) {
			// Remove the extra row
			array_pop( $results );
		}

		// Truncate large field values to prevent information overload
		$results = $this->truncate_large_fields( $results );

		return $this->success_response(
			'Query executed',
			array(
				'row_count' => count( $results ),
				'columns'   => ! empty( $results ) ? array_keys( $results[0] ) : array(),
				'results'   => $results,
				'limit'     => $limit,
				'offset'    => $offset,
				'has_more'  => $has_more,
			)
		);
	}

	/**
	 * Truncate large field values in results to prevent information overload
	 */
	private function truncate_large_fields( $results ) {
		if ( empty( $results ) ) {
			return $results;
		}

		$max_field_size = 1024; // 1KB threshold

		foreach ( $results as &$row ) {
			foreach ( $row as &$value ) {
				if ( is_string( $value ) && strlen( $value ) > $max_field_size ) {
					// Truncate to 1KB with token-boundary awareness
					$value = String_Utils::truncate_to_token_boundary_with_ellipsis( $value, $max_field_size );
				}
			}
		}

		return $results;
	}
}
