<?php
/**
 * Analytics AJAX Endpoints
 *
 * All AJAX handlers for the analytics dashboard. Every handler here reads the
 * plugin's own local analytics tables and ships in the free plugin, except the
 * AI adoption report, which describes the PRO-only AI metadata feature and is
 * removed from the free build.
 *
 * @package Gatewell
 * @subpackage Analytics
 * @since 2.0.0
 */

namespace Gatewell\Analytics;

defined( 'ABSPATH' ) || exit;

/**
 * Class Analytics_Ajax
 *
 * @since 2.0.0
 */
class Analytics_Ajax {

	/**
	 * Register AJAX actions.
	 *
	 * @since 2.0.0
	 */
	public static function init() {
		$endpoints = array(
			'gatewell_get_analytics_overview',
			'gatewell_get_activity_chart',
			'gatewell_get_file_types',
			'gatewell_get_rejection_reasons',
			'gatewell_get_recent_uploads',
			'gatewell_get_top_posts',
			'gatewell_get_context_matrix',
			'gatewell_get_quality_scores',
			'gatewell_get_user_scores',
			'gatewell_clear_analytics_data',
		);

		foreach ( $endpoints as $action ) {
			$method = str_replace( 'gatewell_', '', $action );
			add_action( 'wp_ajax_' . $action, array( __CLASS__, $method ) );
		}
	}

	// -----------------------------------------------------------------------
	// Helpers
	// -----------------------------------------------------------------------

	/**
	 * Verify nonce and capability for every request.
	 *
	 * Called as the first statement of every endpoint registered by init().
	 * Both failure paths call wp_send_json_error(), which ends the request, so
	 * no endpoint body can run without a valid nonce and manage_options.
	 *
	 * @since 2.0.0
	 */
	private static function verify_request() {
		if ( ! isset( $_POST['nonce'] ) || ! wp_verify_nonce( sanitize_text_field( wp_unslash( $_POST['nonce'] ) ), 'gatewell_analytics_nonce' ) ) {
			wp_send_json_error( array( 'message' => 'Security check failed.' ), 403 );
		}

		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( array( 'message' => 'Insufficient permissions.' ), 403 );
		}
	}

	/**
	 * Get days parameter from request (default 7).
	 *
	 * @return int
	 * @since 2.0.0
	 */
	private static function get_days() {
		// phpcs:ignore WordPress.Security.NonceVerification.Missing -- Nonce and capability are verified by self::verify_request(), which is the first statement of every endpoint that reaches this code and calls wp_send_json_error() (which exits) when either check fails. The value is also absint()'d and then clamped to a fixed allow-list below.
		$days = isset( $_POST['days'] ) ? absint( $_POST['days'] ) : 7;
		// Clamp to allowed values.
		if ( ! in_array( $days, array( 7, 30, 90 ), true ) ) {
			$days = 7;
		}
		return $days;
	}

	/**
	 * Get table names shorthand.
	 *
	 * @return array
	 * @since 2.0.0
	 */
	private static function tables() {
		return \Gatewell\Migration::get_analytics_tables();
	}

	// -----------------------------------------------------------------------
	// Endpoint Handlers
	// -----------------------------------------------------------------------

	/**
	 * Overview metric cards.
	 */
	public static function get_analytics_overview() {
		self::verify_request();
		global $wpdb;
		$days   = self::get_days();
		$tables = self::tables();

		$cache_key = Analytics_Cache::key( 'overview_' . $days );
		$cached    = get_transient( $cache_key );

		if ( is_array( $cached ) ) {
			$row       = $cached['row'];
			$space_row = $cached['space_row'];
		} else {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names come from Migration::get_analytics_tables() -- $wpdb->prefix plus a hard-coded literal, never request data -- and a table name cannot be bound with prepare(). Every runtime value is bound through a placeholder.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned analytics table; no WordPress API reads it. The result IS cached -- see the get_transient()/set_transient() pair wrapping this query. The sniff only recognises wp_cache_*() as caching, and a non-persistent object cache would be empty on every one of the separate AJAX requests this dashboard makes, so a short transient is the correct primitive here.
			$row = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT
						SUM(total_uploads) AS total_uploads,
						SUM(total_rejections) AS total_rejections,
						AVG(avg_compression_ratio) AS avg_compression,
						AVG(avg_alt_text_quality) AS avg_quality
					FROM {$tables['daily']}
					WHERE date >= DATE_SUB(CURDATE(), INTERVAL %d DAY)",
					$days
				)
			);

			$space_row = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT SUM(file_size_original - file_size_final) AS space_saved
					 FROM {$tables['events']}
					 WHERE created_at >= DATE_SUB(NOW(), INTERVAL %d DAY) AND was_rejected = 0",
					$days
				)
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			set_transient(
				$cache_key,
				array(
					'row'       => $row,
					'space_row' => $space_row,
				),
				Analytics_Cache::TTL
			);
		}

		$total_uploads    = $row ? (int) $row->total_uploads    : 0;
		$total_rejections = $row ? (int) $row->total_rejections : 0;
		$avg_compression  = $row ? round( (float) $row->avg_compression, 1 ) : 0;
		$avg_quality      = $row ? round( (float) $row->avg_quality, 1 ) : 0;
		$space_saved      = $space_row ? max( 0, (int) $space_row->space_saved ) : 0;
		$rejection_rate   = $total_uploads > 0 ? round( ( $total_rejections / $total_uploads ) * 100, 1 ) : 0;

		wp_send_json_success( array(
			'total_uploads'    => $total_uploads,
			'total_rejections' => $total_rejections,
			'rejection_rate'   => $rejection_rate,
			'space_saved'      => $space_saved,
			'space_saved_mb'   => round( $space_saved / 1024 / 1024, 2 ),
			'avg_compression'  => $avg_compression,
			'avg_quality'      => $avg_quality,
			'days'             => $days,
		) );
	}

	/**
	 * Daily upload counts for activity chart.
	 */
	public static function get_activity_chart() {
		self::verify_request();
		global $wpdb;
		$days   = self::get_days();
		$tables = self::tables();

		$cache_key = Analytics_Cache::key( 'activity_' . $days );
		$rows = get_transient( $cache_key );

		if ( false === $rows ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names come from Migration::get_analytics_tables() -- $wpdb->prefix plus a hard-coded literal, never request data -- and a table name cannot be bound with prepare(). Every runtime value is bound through a placeholder.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned analytics table; no WordPress API reads it. The result IS cached -- see the get_transient()/set_transient() pair wrapping this query. The sniff only recognises wp_cache_*() as caching, and a non-persistent object cache would be empty on every one of the separate AJAX requests this dashboard makes, so a short transient is the correct primitive here.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT date, total_uploads, total_rejections
					 FROM {$tables['daily']}
					 WHERE date >= DATE_SUB(CURDATE(), INTERVAL %d DAY)
					 ORDER BY date ASC",
					$days
				)
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			set_transient( $cache_key, $rows, Analytics_Cache::TTL );
		}

		$labels  = array();
		$uploads = array();
		$rejects = array();

		foreach ( $rows as $row ) {
			$labels[]  = $row->date;
			$uploads[] = (int) $row->total_uploads;
			$rejects[] = (int) $row->total_rejections;
		}

		wp_send_json_success( array(
			'labels'     => $labels,
			'uploads'    => $uploads,
			'rejections' => $rejects,
		) );
	}

	/**
	 * File type distribution.
	 */
	public static function get_file_types() {
		self::verify_request();
		global $wpdb;
		$days   = self::get_days();
		$tables = self::tables();

		$cache_key = Analytics_Cache::key( 'file_types_' . $days );
		$rows = get_transient( $cache_key );

		if ( false === $rows ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names come from Migration::get_analytics_tables() -- $wpdb->prefix plus a hard-coded literal, never request data -- and a table name cannot be bound with prepare(). Every runtime value is bound through a placeholder.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned analytics table; no WordPress API reads it. The result IS cached -- see the get_transient()/set_transient() pair wrapping this query. The sniff only recognises wp_cache_*() as caching, and a non-persistent object cache would be empty on every one of the separate AJAX requests this dashboard makes, so a short transient is the correct primitive here.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT file_type, COUNT(*) AS count
					 FROM {$tables['events']}
					 WHERE created_at >= DATE_SUB(NOW(), INTERVAL %d DAY)
					 GROUP BY file_type
					 ORDER BY count DESC",
					$days
				)
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			set_transient( $cache_key, $rows, Analytics_Cache::TTL );
		}

		$labels = array();
		$counts = array();
		foreach ( $rows as $row ) {
			$labels[] = $row->file_type ?: 'unknown';
			$counts[] = (int) $row->count;
		}

		wp_send_json_success( array( 'labels' => $labels, 'counts' => $counts ) );
	}

	/**
	 * Top rejection reasons.
	 */
	public static function get_rejection_reasons() {
		self::verify_request();
		global $wpdb;
		$days   = self::get_days();
		$tables = self::tables();

		$cache_key = Analytics_Cache::key( 'rejections_' . $days );
		$rows = get_transient( $cache_key );

		if ( false === $rows ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names come from Migration::get_analytics_tables() -- $wpdb->prefix plus a hard-coded literal, never request data -- and a table name cannot be bound with prepare(). Every runtime value is bound through a placeholder.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned analytics table; no WordPress API reads it. The result IS cached -- see the get_transient()/set_transient() pair wrapping this query. The sniff only recognises wp_cache_*() as caching, and a non-persistent object cache would be empty on every one of the separate AJAX requests this dashboard makes, so a short transient is the correct primitive here.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT rejection_reason, COUNT(*) AS count
					 FROM {$tables['events']}
					 WHERE was_rejected = 1
					   AND created_at >= DATE_SUB(NOW(), INTERVAL %d DAY)
					 GROUP BY rejection_reason
					 ORDER BY count DESC
					 LIMIT 10",
					$days
				)
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			set_transient( $cache_key, $rows, Analytics_Cache::TTL );
		}

		$data = array();
		foreach ( $rows as $row ) {
			$data[] = array(
				'reason' => $row->rejection_reason ?: 'Unknown',
				'count'  => (int) $row->count,
			);
		}

		wp_send_json_success( array( 'reasons' => $data ) );
	}

	/**
	 * Recent uploads list.
	 */
	public static function get_recent_uploads() {
		self::verify_request();
		global $wpdb;
		$days   = self::get_days();
		$tables = self::tables();
		// phpcs:ignore WordPress.Security.NonceVerification.Missing -- Nonce and capability are verified by self::verify_request(), which is the first statement of every endpoint that reaches this code and calls wp_send_json_error() (which exits) when either check fails. The value is absint()'d, capped at 50 and bound through a %d placeholder.
		$limit  = isset( $_POST['limit'] ) ? min( 50, absint( $_POST['limit'] ) ) : 20;

		$cache_key = Analytics_Cache::key( 'recent_' . $days . '_' . $limit );
		$rows = get_transient( $cache_key );

		if ( false === $rows ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names come from Migration::get_analytics_tables() -- $wpdb->prefix plus a hard-coded literal, never request data -- and a table name cannot be bound with prepare(). Every runtime value is bound through a placeholder.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned analytics table; no WordPress API reads it. The result IS cached -- see the get_transient()/set_transient() pair wrapping this query. The sniff only recognises wp_cache_*() as caching, and a non-persistent object cache would be empty on every one of the separate AJAX requests this dashboard makes, so a short transient is the correct primitive here.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT e.id, e.created_at, e.attachment_id, e.post_id, e.post_type,
					        e.file_type, e.file_size_original, e.file_size_final,
					        e.alt_text_quality_score, e.was_rejected, e.upload_context
					 FROM {$tables['events']} e
					 WHERE e.created_at >= DATE_SUB(NOW(), INTERVAL %d DAY)
					 ORDER BY e.created_at DESC
					 LIMIT %d",
					$days,
					$limit
				)
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			set_transient( $cache_key, $rows, Analytics_Cache::TTL );
		}

		$items = array();
		foreach ( $rows as $row ) {
			$attachment_url = wp_get_attachment_image_url( (int) $row->attachment_id, 'thumbnail' );
			$items[] = array(
				'id'                  => (int) $row->id,
				'created_at'          => $row->created_at,
				'attachment_id'       => (int) $row->attachment_id,
				'attachment_url'      => $attachment_url ?: '',
				'post_id'             => (int) $row->post_id,
				'post_type'           => $row->post_type,
				'file_type'           => $row->file_type,
				'file_size_original'  => (int) $row->file_size_original,
				'file_size_final'     => (int) $row->file_size_final,
				'alt_text_quality'    => (int) $row->alt_text_quality_score,
				'was_rejected'        => (bool) $row->was_rejected,
				'context'             => $row->upload_context,
			);
		}

		wp_send_json_success( array( 'uploads' => $items ) );
	}

	/**
	 * Top posts by upload count.
	 */
	public static function get_top_posts() {
		self::verify_request();
		global $wpdb;
		$days   = self::get_days();
		$tables = self::tables();

		$cache_key = Analytics_Cache::key( 'top_posts_' . $days );
		$rows = get_transient( $cache_key );

		if ( false === $rows ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names come from Migration::get_analytics_tables() -- $wpdb->prefix plus a hard-coded literal, never request data -- and a table name cannot be bound with prepare(). Every runtime value is bound through a placeholder.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned analytics table; no WordPress API reads it. The result IS cached -- see the get_transient()/set_transient() pair wrapping this query. The sniff only recognises wp_cache_*() as caching, and a non-persistent object cache would be empty on every one of the separate AJAX requests this dashboard makes, so a short transient is the correct primitive here.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT post_id, post_type, COUNT(*) AS upload_count,
					        AVG(alt_text_quality_score) AS avg_quality
					 FROM {$tables['events']}
					 WHERE post_id > 0
					   AND created_at >= DATE_SUB(NOW(), INTERVAL %d DAY)
					 GROUP BY post_id, post_type
					 ORDER BY upload_count DESC
					 LIMIT 15",
					$days
				)
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			set_transient( $cache_key, $rows, Analytics_Cache::TTL );
		}

		$posts = array();
		foreach ( $rows as $row ) {
			$post = get_post( (int) $row->post_id );
			$posts[] = array(
				'post_id'      => (int) $row->post_id,
				'post_type'    => $row->post_type,
				'post_title'   => $post ? get_the_title( $post ) : '#' . $row->post_id,
				'post_url'     => $post ? get_edit_post_link( $post ) : '',
				'upload_count' => (int) $row->upload_count,
				'avg_quality'  => round( (float) $row->avg_quality, 1 ),
			);
		}

		wp_send_json_success( array( 'posts' => $posts ) );
	}

	/**
	 * Context vs post type matrix.
	 */
	public static function get_context_matrix() {
		self::verify_request();
		global $wpdb;
		$days   = self::get_days();
		$tables = self::tables();

		$cache_key = Analytics_Cache::key( 'context_' . $days );
		$rows = get_transient( $cache_key );

		if ( false === $rows ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names come from Migration::get_analytics_tables() -- $wpdb->prefix plus a hard-coded literal, never request data -- and a table name cannot be bound with prepare(). Every runtime value is bound through a placeholder.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned analytics table; no WordPress API reads it. The result IS cached -- see the get_transient()/set_transient() pair wrapping this query. The sniff only recognises wp_cache_*() as caching, and a non-persistent object cache would be empty on every one of the separate AJAX requests this dashboard makes, so a short transient is the correct primitive here.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT upload_context, post_type, COUNT(*) AS count
					 FROM {$tables['events']}
					 WHERE created_at >= DATE_SUB(NOW(), INTERVAL %d DAY)
					 GROUP BY upload_context, post_type
					 ORDER BY count DESC",
					$days
				)
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			set_transient( $cache_key, $rows, Analytics_Cache::TTL );
		}

		// Build matrix: { post_type => { context => count } }
		$matrix     = array();
		$contexts   = array();
		$post_types = array();

		foreach ( $rows as $row ) {
			$pt  = $row->post_type ?: 'none';
			$ctx = $row->upload_context ?: 'media-library';

			if ( ! isset( $matrix[ $pt ] ) ) {
				$matrix[ $pt ] = array();
			}
			$matrix[ $pt ][ $ctx ] = (int) $row->count;

			if ( ! in_array( $ctx, $contexts, true ) ) {
				$contexts[] = $ctx;
			}
			if ( ! in_array( $pt, $post_types, true ) ) {
				$post_types[] = $pt;
			}
		}

		wp_send_json_success( array(
			'matrix'     => $matrix,
			'contexts'   => $contexts,
			'post_types' => $post_types,
		) );
	}

	/**
	 * Average metadata quality scores grouped by upload context.
	 *
	 * Pure local aggregation over the plugin's own analytics table — the alt
	 * text and title scores are computed on every upload by
	 * Analytics_Logger::calculate_text_quality_score(), so this is meaningful
	 * in the free plugin.
	 *
	 * @since 2.0.0
	 */
	public static function get_quality_scores() {
		self::verify_request();
		global $wpdb;
		$days   = self::get_days();
		$tables = self::tables();

		$cache_key = Analytics_Cache::key( 'quality_' . $days );
		$rows = get_transient( $cache_key );

		if ( false === $rows ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names come from Migration::get_analytics_tables() -- $wpdb->prefix plus a hard-coded literal, never request data -- and a table name cannot be bound with prepare(). Every runtime value is bound through a placeholder.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned analytics table; no WordPress API reads it. The result IS cached -- see the get_transient()/set_transient() pair wrapping this query. The sniff only recognises wp_cache_*() as caching, and a non-persistent object cache would be empty on every one of the separate AJAX requests this dashboard makes, so a short transient is the correct primitive here.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT upload_context,
					        AVG(alt_text_quality_score) AS avg_alt_quality,
					        AVG(title_quality_score) AS avg_title_quality,
					        COUNT(*) AS count
					 FROM {$tables['events']}
					 WHERE created_at >= DATE_SUB(NOW(), INTERVAL %d DAY)
					 GROUP BY upload_context
					 ORDER BY avg_alt_quality DESC",
					$days
				)
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			set_transient( $cache_key, $rows, Analytics_Cache::TTL );
		}

		$scores = array();
		foreach ( $rows as $row ) {
			$scores[] = array(
				'context'          => $row->upload_context,
				'avg_alt_quality'  => round( (float) $row->avg_alt_quality, 1 ),
				'avg_title_quality' => round( (float) $row->avg_title_quality, 1 ),
				'count'            => (int) $row->count,
			);
		}

		wp_send_json_success( array( 'scores' => $scores ) );
	}

	/**
	 * Per-user metadata quality averages.
	 *
	 * Pure local aggregation over the plugin's own analytics table.
	 *
	 * @since 2.0.0
	 */
	public static function get_user_scores() {
		self::verify_request();
		global $wpdb;
		$days   = self::get_days();
		$tables = self::tables();

		// Optional extra SELECT column. Hard-coded literal, never user input.
		$extra_select = '';

		$cache_key = Analytics_Cache::key( 'user_scores_' . $days );
		$rows = get_transient( $cache_key );

		if ( false === $rows ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names come from Migration::get_analytics_tables() -- $wpdb->prefix plus a hard-coded literal, never request data -- and a table name cannot be bound with prepare(). Every runtime value is bound through a placeholder.
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Plugin-owned analytics table; no WordPress API reads it. The result IS cached -- see the get_transient()/set_transient() pair wrapping this query. The sniff only recognises wp_cache_*() as caching, and a non-persistent object cache would be empty on every one of the separate AJAX requests this dashboard makes, so a short transient is the correct primitive here.
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT user_id,
					        COUNT(*) AS upload_count,
					        AVG(alt_text_quality_score) AS avg_quality
					        {$extra_select}
					 FROM {$tables['events']}
					 WHERE created_at >= DATE_SUB(NOW(), INTERVAL %d DAY)
					 GROUP BY user_id
					 ORDER BY avg_quality DESC
					 LIMIT 20",
					$days
				)
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			set_transient( $cache_key, $rows, Analytics_Cache::TTL );
		}

		$users = array();
		foreach ( $rows as $row ) {
			$user  = get_userdata( (int) $row->user_id );
			$entry = array(
				'user_id'      => (int) $row->user_id,
				'display_name' => $user ? $user->display_name : __( 'Unknown User', 'gatewell' ),
				'upload_count' => (int) $row->upload_count,
				'avg_quality'  => round( (float) $row->avg_quality, 1 ),
			);
			$users[] = $entry;
		}

		wp_send_json_success( array( 'users' => $users ) );
	}


	/**
	 * Clear all analytics data.
	 *
	 * @since 2.0.0
	 */
	public static function clear_analytics_data() {
		self::verify_request();

		if ( ! class_exists( 'Gatewell\\Analytics\\Analytics_Logger' ) ) {
			wp_send_json_error( array( 'message' => 'Analytics logger not found.' ) );
		}

		Analytics_Logger::get_instance()->clear_all_data();

		wp_send_json_success( array( 'message' => __( 'Analytics data cleared successfully.', 'gatewell' ) ) );
	}
}