<?php
/**
 * Data function run_sql_query.
 *
 * @package Askshelf
 */

declare( strict_types = 1 );

namespace Askshelf\Tools;

use Askshelf\Data\Analytics;
use Askshelf\Data\SqlGuard;
use Askshelf\Settings;

defined( 'ABSPATH' ) || exit;

/**
 * A read-only query the model writes itself, for questions no other tool answers. Off
 * unless the owner turns on "Custom queries"; the admin page shows the query and runs it
 * only when the owner clicks Run. Every check is in SqlGuard.
 */
final class CustomQuery implements Tool {

	/** {@inheritDoc} */
	public function name(): string {
		return 'run_sql_query';
	}

	/** {@inheritDoc} */
	public function scopes(): array {
		return array( Settings::SCOPE_CUSTOM_QUERIES );
	}

	/** {@inheritDoc} */
	public function schema(): array {
		return Schema::object(
			array(
				'sql'     => array(
					'type'      => 'string',
					'minLength' => 10,
					'maxLength' => 4000,
				),
				'purpose' => array(
					'type'      => 'string',
					'maxLength' => 300,
				),
			),
			array( 'sql', 'purpose' )
		);
	}

	/**
	 * Runs the data function.
	 *
	 * @param array<string, mixed> $args Validated arguments.
	 * @return array<string, mixed>
	 */
	public function run( array $args ): array {
		global $wpdb;
		$sql    = SqlGuard::prepare( (string) $args['sql'] );
		$result = SqlGuard::run( $sql );

		$notes = array( 'A custom query written for this question. Built-in lookups follow WooCommerce Analytics exactly; check this one’s filters before relying on it.' );
		$lower = strtolower( (string) $args['sql'] );
		if ( str_contains( $lower, 'wc_order_stats' ) && ! str_contains( $lower, 'status' ) ) {
			$notes[] = sprintf(
				'This query does not filter order status. WooCommerce Analytics leaves out orders with these statuses: %s.',
				implode( ', ', array_map( static fn( string $s ): string => 'wc-' . $s, Analytics::excluded_statuses() ) )
			);
		}
		if ( $result['truncated'] ) {
			$notes[] = sprintf( 'Only the first %d rows are shown.', SqlGuard::MAX_ROWS );
		}

		return array(
			// Shown to the owner in "How I got this", without the table prefix.
			'sql'       => str_replace( '`' . $wpdb->prefix, '`', $sql ),
			'columns'   => $result['columns'],
			'rows'      => $result['rows'],
			'row_count' => count( $result['rows'] ),
			'truncated' => $result['truncated'],
			'rows_read' => count( $result['rows'] ),
			'notes'     => $notes,
		);
	}
}