<?php
/**
 * Crawler Analytics — read layer for all dashboard queries.
 *
 * Provides static methods for reading from the two crawler tables:
 *   - {prefix}aidf_crawler_log     Raw hits (id, bot_name, bot_ua, url_path, status_code, created_at)
 *   - {prefix}aidf_crawler_summary Daily rollups (id, bot_name, log_date, hit_count, top_page,
 *                                                  status_200, status_403, status_other)
 *
 * @package AIDF
 * @since   1.3.0
 */

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Static query helpers for the crawler analytics dashboard.
 *
 * All methods are static; no instantiation is needed.
 *
 * @since 1.3.0
 */
class AIDF_Crawler_Analytics {

	// -------------------------------------------------------------------------
	// Summary & overview
	// -------------------------------------------------------------------------

	/**
	 * Return a high-level summary for the given window.
	 *
	 * Reads from the summary table for speed. Falls back to zero values when no
	 * data exists so callers never need to handle null themselves.
	 *
	 * @since 1.3.0
	 *
	 * @param int $days Number of days to look back (default 30).
	 * @return array{
	 *   total_visits:    int,
	 *   unique_bots:     int,
	 *   most_active_bot: array{name: string, count: int}|null,
	 *   bots_list:       array<string, int>
	 * }
	 */
	public static function get_summary( $days = 30 ) {
		global $wpdb;

		$days = absint( $days );
		if ( $days < 1 ) {
			$days = 30;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT bot_name, SUM(hit_count) AS total
				FROM {$wpdb->prefix}aidf_crawler_summary
				WHERE log_date >= DATE_SUB(CURDATE(), INTERVAL %d DAY)
				GROUP BY bot_name
				ORDER BY total DESC",
				$days
			),
			ARRAY_A
		);

		$bots_list     = array();
		$total_visits  = 0;
		$most_active   = null;

		if ( ! empty( $rows ) ) {
			foreach ( $rows as $row ) {
				$count                       = (int) $row['total'];
				$bots_list[ $row['bot_name'] ] = $count;
				$total_visits               += $count;
			}

			// First row is most active (ORDER BY total DESC).
			$first = reset( $rows );
			$most_active = array(
				'name'  => $first['bot_name'],
				'count' => (int) $first['total'],
			);
		}

		return array(
			'total_visits'    => $total_visits,
			'unique_bots'     => count( $bots_list ),
			'most_active_bot' => $most_active,
			'bots_list'       => $bots_list,
		);
	}

	// -------------------------------------------------------------------------
	// Bot breakdown table
	// -------------------------------------------------------------------------

	/**
	 * Return per-bot statistics for the given window.
	 *
	 * Each row contains aggregated counts from the summary table plus last_seen
	 * and top_page looked up from the raw log and summary tables respectively.
	 *
	 * @since 1.3.0
	 *
	 * @param int $days Number of days to look back (default 30).
	 * @return array<int, array{
	 *   bot_name:     string,
	 *   visit_count:  int,
	 *   last_seen:    string,
	 *   top_page:     string,
	 *   status_200:   int,
	 *   status_403:   int,
	 *   status_other: int
	 * }>
	 */
	public static function get_bot_breakdown( $days = 30 ) {
		global $wpdb;

		$days = absint( $days );
		if ( $days < 1 ) {
			$days = 30;
		}

		// Aggregate counts from the pre-computed summary table.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
					bot_name,
					SUM(hit_count)   AS visit_count,
					SUM(status_200)  AS status_200,
					SUM(status_403)  AS status_403,
					SUM(status_other) AS status_other
				FROM {$wpdb->prefix}aidf_crawler_summary
				WHERE log_date >= DATE_SUB(CURDATE(), INTERVAL %d DAY)
				GROUP BY bot_name
				ORDER BY visit_count DESC",
				$days
			),
			ARRAY_A
		);

		if ( empty( $rows ) ) {
			return array();
		}

		// Fetch the most-visited page per bot from the summary table (most recent top_page).
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$top_pages_rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT bot_name, top_page
				FROM {$wpdb->prefix}aidf_crawler_summary
				WHERE log_date >= DATE_SUB(CURDATE(), INTERVAL %d DAY)
				  AND top_page != ''
				ORDER BY hit_count DESC",
				$days
			),
			ARRAY_A
		);

		// Keep only the first occurrence per bot (highest hit_count).
		$top_page_map = array();
		foreach ( $top_pages_rows as $tp_row ) {
			$name = $tp_row['bot_name'];
			if ( ! isset( $top_page_map[ $name ] ) ) {
				$top_page_map[ $name ] = $tp_row['top_page'];
			}
		}

		// Fetch last_seen from the raw log table for each bot.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$last_seen_rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT bot_name, MAX(created_at) AS last_seen
				FROM {$wpdb->prefix}aidf_crawler_log
				WHERE created_at >= DATE_SUB(NOW(), INTERVAL %d DAY)
				GROUP BY bot_name",
				$days
			),
			ARRAY_A
		);

		$last_seen_map = array();
		foreach ( $last_seen_rows as $ls_row ) {
			$last_seen_map[ $ls_row['bot_name'] ] = $ls_row['last_seen'];
		}

		// Merge all data into the final result array.
		$result = array();
		foreach ( $rows as $row ) {
			$name     = $row['bot_name'];
			$result[] = array(
				'bot_name'     => $name,
				'visit_count'  => (int) $row['visit_count'],
				'last_seen'    => isset( $last_seen_map[ $name ] ) ? $last_seen_map[ $name ] : '',
				'top_page'     => isset( $top_page_map[ $name ] ) ? $top_page_map[ $name ] : '',
				'status_200'   => (int) $row['status_200'],
				'status_403'   => (int) $row['status_403'],
				'status_other' => (int) $row['status_other'],
			);
		}

		return $result;
	}

	// -------------------------------------------------------------------------
	// Bot detail drill-down
	// -------------------------------------------------------------------------

	/**
	 * Return detailed analytics for a single bot over the given window.
	 *
	 * @since 1.3.0
	 *
	 * @param string $bot_name Human-readable bot name (as stored in the log).
	 * @param int    $days     Number of days to look back (default 30).
	 * @return array{
	 *   daily_trend:       array<string, int>,
	 *   status_breakdown:  array{status_200: int, status_403: int, status_other: int},
	 *   pages:             array<string, array{count: int, last_seen: string}>,
	 *   file_access:       array<int, string>,
	 *   first_seen:        string,
	 *   last_seen:         string
	 * }
	 */
	public static function get_bot_detail( $bot_name, $days = 30 ) {
		global $wpdb;

		$bot_name = sanitize_text_field( $bot_name );
		$days     = absint( $days );
		if ( $days < 1 ) {
			$days = 30;
		}

		// Daily trend from the summary table.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$trend_rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT log_date, SUM(hit_count) AS daily_count
				FROM {$wpdb->prefix}aidf_crawler_summary
				WHERE bot_name = %s
				  AND log_date >= DATE_SUB(CURDATE(), INTERVAL %d DAY)
				GROUP BY log_date
				ORDER BY log_date ASC",
				$bot_name,
				$days
			),
			ARRAY_A
		);

		$daily_trend = array();
		foreach ( $trend_rows as $tr ) {
			$daily_trend[ $tr['log_date'] ] = (int) $tr['daily_count'];
		}

		// Status breakdown from the summary table.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$status_row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT
					SUM(status_200)   AS status_200,
					SUM(status_403)   AS status_403,
					SUM(status_other) AS status_other
				FROM {$wpdb->prefix}aidf_crawler_summary
				WHERE bot_name = %s
				  AND log_date >= DATE_SUB(CURDATE(), INTERVAL %d DAY)",
				$bot_name,
				$days
			),
			ARRAY_A
		);

		$status_breakdown = array(
			'status_200'   => $status_row ? (int) $status_row['status_200'] : 0,
			'status_403'   => $status_row ? (int) $status_row['status_403'] : 0,
			'status_other' => $status_row ? (int) $status_row['status_other'] : 0,
		);

		// Pages accessed — from the raw log table.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$page_rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT url_path, COUNT(*) AS page_count, MAX(created_at) AS last_seen
				FROM {$wpdb->prefix}aidf_crawler_log
				WHERE bot_name = %s
				  AND created_at >= DATE_SUB(NOW(), INTERVAL %d DAY)
				GROUP BY url_path
				ORDER BY page_count DESC
				LIMIT 100",
				$bot_name,
				$days
			),
			ARRAY_A
		);

		$pages = array();
		foreach ( $page_rows as $pr ) {
			$pages[ $pr['url_path'] ] = array(
				'count'     => (int) $pr['page_count'],
				'last_seen' => $pr['last_seen'],
			);
		}

		// File access — intersect page paths with known discovery file paths.
		$known_file_paths = self::get_discovery_file_paths();
		$file_access      = array();
		foreach ( $known_file_paths as $file_path ) {
			if ( isset( $pages[ $file_path ] ) ) {
				$file_access[] = $file_path;
			}
		}

		// First/last seen from the raw log.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$seen_row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT MIN(created_at) AS first_seen, MAX(created_at) AS last_seen
				FROM {$wpdb->prefix}aidf_crawler_log
				WHERE bot_name = %s
				  AND created_at >= DATE_SUB(NOW(), INTERVAL %d DAY)",
				$bot_name,
				$days
			),
			ARRAY_A
		);

		return array(
			'daily_trend'      => $daily_trend,
			'status_breakdown' => $status_breakdown,
			'pages'            => $pages,
			'file_access'      => $file_access,
			'first_seen'       => $seen_row ? (string) $seen_row['first_seen'] : '',
			'last_seen'        => $seen_row ? (string) $seen_row['last_seen'] : '',
		);
	}

	// -------------------------------------------------------------------------
	// Chart data
	// -------------------------------------------------------------------------

	/**
	 * Return data formatted for a stacked bar chart.
	 *
	 * For 7-day and 30-day windows, buckets are individual dates (Y-m-d).
	 * For 90-day windows, buckets are weekly (keyed by the Monday start date, Y-m-d).
	 *
	 * Only the top 6 bots by total hits are represented individually;
	 * all remaining bots are aggregated under the "Other" key.
	 *
	 * @since 1.3.0
	 *
	 * @param int $days Number of days to look back (default 30).
	 * @return array<string, array<string, int>> Bucket => (bot_name => count).
	 */
	public static function get_chart_data( $days = 30 ) {
		global $wpdb;

		$days = absint( $days );
		if ( $days < 1 ) {
			$days = 30;
		}

		$use_weekly = ( 90 <= $days );

		if ( $use_weekly ) {
			// Weekly aggregation: bucket by the Monday of each week (DATE - WEEKDAY gives Monday).
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT
						bot_name,
						DATE_FORMAT(log_date - INTERVAL WEEKDAY(log_date) DAY, '%%Y-%%m-%%d') AS bucket,
						SUM(hit_count) AS total
					FROM {$wpdb->prefix}aidf_crawler_summary
					WHERE log_date >= DATE_SUB(CURDATE(), INTERVAL %d DAY)
					GROUP BY bot_name, bucket
					ORDER BY bucket ASC",
					$days
				),
				ARRAY_A
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$rows = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT
						bot_name,
						DATE_FORMAT(log_date, '%%Y-%%m-%%d') AS bucket,
						SUM(hit_count) AS total
					FROM {$wpdb->prefix}aidf_crawler_summary
					WHERE log_date >= DATE_SUB(CURDATE(), INTERVAL %d DAY)
					GROUP BY bot_name, bucket
					ORDER BY bucket ASC",
					$days
				),
				ARRAY_A
			);
		}

		if ( empty( $rows ) ) {
			return array();
		}

		// Determine top 6 bots by total across all buckets.
		$bot_totals = array();
		foreach ( $rows as $row ) {
			$name = $row['bot_name'];
			if ( ! isset( $bot_totals[ $name ] ) ) {
				$bot_totals[ $name ] = 0;
			}
			$bot_totals[ $name ] += (int) $row['total'];
		}

		arsort( $bot_totals );
		$top_bots = array_slice( array_keys( $bot_totals ), 0, 6 );

		// Build the chart array: bucket => (bot_name => count).
		$chart = array();
		foreach ( $rows as $row ) {
			$bucket = $row['bucket'];
			$name   = $row['bot_name'];
			$count  = (int) $row['total'];

			if ( ! isset( $chart[ $bucket ] ) ) {
				$chart[ $bucket ] = array();
			}

			if ( in_array( $name, $top_bots, true ) ) {
				$chart[ $bucket ][ $name ] = isset( $chart[ $bucket ][ $name ] )
					? $chart[ $bucket ][ $name ] + $count
					: $count;
			} else {
				$chart[ $bucket ]['Other'] = isset( $chart[ $bucket ]['Other'] )
					? $chart[ $bucket ]['Other'] + $count
					: $count;
			}
		}

		// Pad with empty buckets so the chart always shows the full date range.
		if ( $use_weekly ) {
			// Pad weekly buckets (keyed by Monday date).
			$padded = array();
			$date   = new \DateTime( 'now', new \DateTimeZone( 'UTC' ) );
			$date->modify( "-{$days} days" );
			// Align to Monday.
			$dow = (int) $date->format( 'N' ); // 1=Mon, 7=Sun.
			if ( $dow > 1 ) {
				$date->modify( 'next Monday' );
			}
			$end = new \DateTime( 'now', new \DateTimeZone( 'UTC' ) );
			while ( $date <= $end ) {
				$key = $date->format( 'Y-m-d' );
				$padded[ $key ] = isset( $chart[ $key ] ) ? $chart[ $key ] : new \stdClass();
				$date->modify( '+7 days' );
			}
			$chart = $padded;
		} else {
			// Pad daily buckets.
			$padded = array();
			$date   = new \DateTime( 'now', new \DateTimeZone( 'UTC' ) );
			$date->modify( "-{$days} days" );

			for ( $d = 0; $d < $days; $d++ ) {
				$date->modify( '+1 day' );
				$key = $date->format( 'Y-m-d' );
				$padded[ $key ] = isset( $chart[ $key ] ) ? $chart[ $key ] : new \stdClass();
			}
			$chart = $padded;
		}

		return $chart;
	}

	// -------------------------------------------------------------------------
	// File access panel
	// -------------------------------------------------------------------------

	/**
	 * Return per-file access statistics for known AI discovery files.
	 *
	 * Only paths that appear in the raw log are included. Each entry lists
	 * the total hit count and the distinct bot names that accessed the file.
	 *
	 * @since 1.3.0
	 *
	 * @param int $days Number of days to look back (default 30).
	 * @return array<string, array{hit_count: int, bots: array<int, string>}>
	 */
	public static function get_file_access( $days = 30 ) {
		global $wpdb;

		$days = absint( $days );
		if ( $days < 1 ) {
			$days = 30;
		}

		$known_paths = self::get_discovery_file_paths();

		if ( empty( $known_paths ) ) {
			return array();
		}

		// Build a safe IN() placeholder list.
		$placeholders = implode( ', ', array_fill( 0, count( $known_paths ), '%s' ) );

		// Merge days value + path values for prepare().
		$prepare_args = array_merge( array( $days ), $known_paths );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name is $wpdb->prefix constant; $placeholders is array_fill of %s tokens, not user input; $prepare_args merges validated values.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT url_path, COUNT(*) AS hit_count, GROUP_CONCAT(DISTINCT bot_name ORDER BY bot_name SEPARATOR ',') AS bots
				FROM {$wpdb->prefix}aidf_crawler_log
				WHERE created_at >= DATE_SUB(NOW(), INTERVAL %d DAY)
				  AND url_path IN ({$placeholders})
				GROUP BY url_path
				ORDER BY hit_count DESC",
				$prepare_args
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter

		$result = array();
		foreach ( $rows as $row ) {
			$bots_raw = ! empty( $row['bots'] ) ? explode( ',', $row['bots'] ) : array();
			$result[ $row['url_path'] ] = array(
				'hit_count' => (int) $row['hit_count'],
				'bots'      => $bots_raw,
			);
		}

		return $result;
	}

	// -------------------------------------------------------------------------
	// Log viewer
	// -------------------------------------------------------------------------

	/**
	 * Return a paginated slice of raw log entries matching the given filters.
	 *
	 * Accepted $args keys:
	 *   - bot_name   (string|array) Filter by one or more bot names.
	 *   - date_from  (string Y-m-d) Inclusive lower date bound.
	 *   - date_to    (string Y-m-d) Inclusive upper date bound.
	 *   - status_code (int|array)   Filter by one or more HTTP status codes.
	 *   - url_path   (string)       Substring match against url_path.
	 *   - page       (int)          1-based page number (default 1).
	 *   - per_page   (int)          Rows per page; -1 returns all (default 50).
	 *   - orderby    (string)       Column name to sort by (default 'created_at').
	 *   - order      (string)       ASC or DESC (default 'DESC').
	 *
	 * @since 1.3.0
	 *
	 * @param array<string, mixed> $args Query arguments.
	 * @return array{entries: array<int, array<string, mixed>>, total: int}
	 */
	public static function get_log_entries( $args = array() ) {
		global $wpdb;

		$defaults = array(
			'bot_name'    => '',
			'date_from'   => '',
			'date_to'     => '',
			'status_code' => '',
			'url_path'    => '',
			'page'        => 1,
			'per_page'    => 50,
			'orderby'     => 'created_at',
			'order'       => 'DESC',
		);

		$args     = wp_parse_args( $args, $defaults );
		$where    = array();
		$values   = array();

		// -- bot_name filter --------------------------------------------------
		if ( ! empty( $args['bot_name'] ) ) {
			$bot_names = is_array( $args['bot_name'] )
				? array_map( 'sanitize_text_field', $args['bot_name'] )
				: array( sanitize_text_field( $args['bot_name'] ) );

			$bot_names = array_filter( $bot_names );

			if ( ! empty( $bot_names ) ) {
				$ph      = implode( ', ', array_fill( 0, count( $bot_names ), '%s' ) );
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$where[] = "bot_name IN ({$ph})";
				$values  = array_merge( $values, $bot_names );
			}
		}

		// -- date_from filter -------------------------------------------------
		if ( ! empty( $args['date_from'] ) ) {
			$date_from = sanitize_text_field( $args['date_from'] );
			if ( preg_match( '/^\d{4}-\d{2}-\d{2}$/', $date_from ) ) {
				$where[]  = 'created_at >= %s';
				$values[] = $date_from . ' 00:00:00';
			}
		}

		// -- date_to filter ---------------------------------------------------
		if ( ! empty( $args['date_to'] ) ) {
			$date_to = sanitize_text_field( $args['date_to'] );
			if ( preg_match( '/^\d{4}-\d{2}-\d{2}$/', $date_to ) ) {
				$where[]  = 'created_at <= %s';
				$values[] = $date_to . ' 23:59:59';
			}
		}

		// -- status_code filter -----------------------------------------------
		if ( ! empty( $args['status_code'] ) ) {
			$codes = is_array( $args['status_code'] )
				? array_map( 'absint', $args['status_code'] )
				: array( absint( $args['status_code'] ) );

			$codes = array_filter( $codes );

			if ( ! empty( $codes ) ) {
				$ph      = implode( ', ', array_fill( 0, count( $codes ), '%d' ) );
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$where[] = "status_code IN ({$ph})";
				$values  = array_merge( $values, $codes );
			}
		}

		// -- url_path substring filter ----------------------------------------
		if ( ! empty( $args['url_path'] ) ) {
			$where[]  = 'url_path LIKE %s';
			$values[] = '%' . $wpdb->esc_like( sanitize_text_field( $args['url_path'] ) ) . '%';
		}

		// -- Build WHERE clause -----------------------------------------------
		$where_sql = ! empty( $where ) ? 'WHERE ' . implode( ' AND ', $where ) : '';

		// -- Sanitise ORDER BY ------------------------------------------------
		$allowed_orderby = array( 'id', 'bot_name', 'url_path', 'status_code', 'created_at' );
		$orderby         = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'created_at';
		$order           = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';

		$table = $wpdb->prefix . 'aidf_crawler_log';

		// -- COUNT query ------------------------------------------------------
		if ( ! empty( $values ) ) {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table is $wpdb->prefix constant; $where_sql is built from validated/prepared inputs only.
			$total = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM {$table} {$where_sql}",
					$values
				)
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter
		} else {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table is $wpdb->prefix constant, not user input.
			$total = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}

		// -- LIMIT / OFFSET ---------------------------------------------------
		$per_page = (int) $args['per_page'];
		$page     = max( 1, (int) $args['page'] );

		if ( -1 === $per_page ) {
			$limit_sql = '';
		} else {
			$per_page  = max( 1, $per_page );
			$offset    = ( $page - 1 ) * $per_page;
			$limit_sql = $wpdb->prepare( 'LIMIT %d OFFSET %d', $per_page, $offset );
		}

		// -- SELECT query -----------------------------------------------------
		// $orderby is validated against an allowlist; $order is coerced to ASC/DESC.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $orderby is allowlisted, $order is coerced to ASC/DESC.
		$order_sql = "ORDER BY {$orderby} {$order}";

		if ( ! empty( $values ) ) {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table is prefix+constant; $where_sql/$order_sql/$limit_sql built from validated/prepared inputs.
			$entries = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT id, bot_name, bot_ua, url_path, status_code, created_at
					FROM {$table}
					{$where_sql}
					{$order_sql}
					{$limit_sql}",
					$values
				),
				ARRAY_A
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter
		} else {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table is prefix+constant; $order_sql/$limit_sql built from validated/prepared inputs; no user data interpolated.
			$entries = $wpdb->get_results(
				"SELECT id, bot_name, bot_ua, url_path, status_code, created_at
				FROM {$table}
				{$order_sql}
				{$limit_sql}",
				ARRAY_A
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}

		return array(
			'entries' => $entries ? $entries : array(),
			'total'   => $total,
		);
	}

	// -------------------------------------------------------------------------
	// CSV export
	// -------------------------------------------------------------------------

	/**
	 * Output all matching log entries as a UTF-8 CSV file and exit.
	 *
	 * Sends the appropriate Content-Type and Content-Disposition headers,
	 * writes a UTF-8 BOM so Excel opens the file correctly, then streams
	 * each row before calling exit.
	 *
	 * @since 1.3.0
	 *
	 * @param array<string, mixed> $args Same filter arguments as get_log_entries(), per_page is ignored.
	 * @return void Does not return; calls exit after output.
	 */
	public static function export_csv( $args = array() ) {
		// Build a bot_name => operator lookup from the registry.
		$registry      = AIDF_Crawler_Registry::get_bots();
		$operator_map  = array();
		foreach ( $registry as $bot ) {
			$operator_map[ $bot['name'] ] = $bot['operator'];
		}

		$filename = 'ai-crawler-log-' . gmdate( 'Y-m-d' ) . '.csv';

		header( 'Content-Type: text/csv; charset=utf-8' );
		header( 'Content-Disposition: attachment; filename=' . $filename );
		header( 'Pragma: no-cache' );
		header( 'Expires: 0' );

		$output = fopen( 'php://output', 'w' ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen
		if ( ! $output ) {
			wp_die( esc_html__( 'Unable to open output stream.', 'ai-discovery-files' ) );
		}

		// UTF-8 BOM — helps Excel detect the encoding correctly.
		fwrite( $output, "\xEF\xBB\xBF" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite

		// Header row.
		fputcsv( $output, array( 'Time (ISO 8601)', 'Bot Name', 'Operator', 'URL Path', 'Status Code' ) );

		// Stream rows in chunks to avoid loading the entire table into memory.
		$chunk_size = 1000;
		$page       = 1;
		$args['per_page'] = $chunk_size;

		do {
			$args['page'] = $page;
			$result        = self::get_log_entries( $args );

			foreach ( $result['entries'] as $entry ) {
				$bot_name = $entry['bot_name'];
				$operator = isset( $operator_map[ $bot_name ] ) ? $operator_map[ $bot_name ] : '';

				// Convert MySQL datetime to ISO 8601 with UTC offset.
				$iso_time = str_replace( ' ', 'T', $entry['created_at'] ) . 'Z';

				fputcsv(
					$output,
					array(
						$iso_time,
						$bot_name,
						$operator,
						$entry['url_path'],
						(int) $entry['status_code'],
					)
				);
			}

			$page++;
		} while ( count( $result['entries'] ) === $chunk_size );

		fclose( $output ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
		exit;
	}

	// -------------------------------------------------------------------------
	// Summary rebuild (cron handler)
	// -------------------------------------------------------------------------

	/**
	 * Rebuild the summary table from raw log data.
	 *
	 * Runs daily via the `aidf_rebuild_crawler_summary` cron event. Aggregates
	 * log entries and upserts into the summary table using INSERT … ON DUPLICATE
	 * KEY UPDATE (the bot_day UNIQUE KEY prevents doubles).
	 *
	 * A second pass updates the top_page column per (bot_name, log_date) because
	 * MySQL does not support referencing the inserted-row value in the UPDATE
	 * clause when using a subquery for MAX.
	 *
	 * @since 1.3.0
	 *
	 * @param bool $full When true, processes the entire log table. When false
	 *                   (default), only processes the last 2 days for efficiency.
	 *                   The daily cron uses false; the manual Rebuild button uses true.
	 * @return void
	 */
	public static function rebuild_summary( $full = false ) {
		global $wpdb;

		$log_table     = $wpdb->prefix . 'aidf_crawler_log';
		$summary_table = $wpdb->prefix . 'aidf_crawler_summary';

		// Step 1: Upsert hit counts and status buckets from the raw log.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $summary_table and $log_table are $wpdb->prefix constants, not user input.
		$date_filter = $full ? '' : 'WHERE created_at >= DATE_SUB(NOW(), INTERVAL 2 DAY)';

		$wpdb->query(
			"INSERT INTO {$summary_table}
				(bot_name, log_date, hit_count, top_page, status_200, status_403, status_other)
			SELECT
				bot_name,
				DATE(created_at) AS log_date,
				COUNT(*) AS hit_count,
				'' AS top_page,
				SUM(CASE WHEN status_code >= 200 AND status_code < 400 THEN 1 ELSE 0 END) AS status_200,
				SUM(CASE WHEN status_code >= 400 AND status_code < 500 THEN 1 ELSE 0 END) AS status_403,
				SUM(CASE WHEN status_code >= 500 OR status_code < 200  THEN 1 ELSE 0 END) AS status_other
			FROM {$log_table}
			{$date_filter}
			GROUP BY bot_name, DATE(created_at)
			ON DUPLICATE KEY UPDATE
				hit_count    = VALUES(hit_count),
				status_200   = VALUES(status_200),
				status_403   = VALUES(status_403),
				status_other = VALUES(status_other)"
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// Step 2: Update top_page per (bot_name, log_date) with the most frequent url_path.
		//
		// We pull all distinct (bot_name, log_date) pairs from the summary then
		// update each in a loop to avoid complex correlated-subquery portability
		// issues across MySQL 5.7 / 8.x and MariaDB.
		//
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $summary_table is $wpdb->prefix constant, not user input.
		$top_page_filter = $full ? '' : 'WHERE log_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)';

		$pairs = $wpdb->get_results(
			"SELECT DISTINCT bot_name, log_date FROM {$summary_table} {$top_page_filter}",
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

		if ( empty( $pairs ) ) {
			return;
		}

		foreach ( $pairs as $pair ) {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $log_table is $wpdb->prefix constant, not user input.
			$top_page = $wpdb->get_var(
				$wpdb->prepare(
					"SELECT url_path
					FROM {$log_table}
					WHERE bot_name = %s
					  AND DATE(created_at) = %s
					GROUP BY url_path
					ORDER BY COUNT(*) DESC
					LIMIT 1",
					$pair['bot_name'],
					$pair['log_date']
				)
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			if ( null !== $top_page ) {
				// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $summary_table is $wpdb->prefix constant; values are sanitized via prepare/update format args.
				$wpdb->update(
					$summary_table,
					array( 'top_page' => $top_page ),
					array(
						'bot_name' => $pair['bot_name'],
						'log_date' => $pair['log_date'],
					),
					array( '%s' ),
					array( '%s', '%s' )
				);
				// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			}
		}
	}

	// -------------------------------------------------------------------------
	// Internal helpers
	// -------------------------------------------------------------------------

	/**
	 * Return the list of URL paths for all known AI discovery files.
	 *
	 * Derives the paths from AIDF_Plugin::get_file_types() so the list stays
	 * in sync automatically whenever a new file type is registered.
	 *
	 * @since 1.3.0
	 *
	 * @return array<int, string> Absolute URL paths, e.g. ['/llms.txt', '/ai.txt', ...].
	 */
	private static function get_discovery_file_paths() {
		$file_types = AIDF_Plugin::get_file_types();
		$paths      = array();

		foreach ( $file_types as $file_type ) {
			if ( ! empty( $file_type['filename'] ) ) {
				$paths[] = '/' . ltrim( $file_type['filename'], '/' );
			}
		}

		return $paths;
	}
}