<?php
/**
 * Read-side queries over the crawler log.
 *
 * @package WPLens
 */

namespace WPLens\Crawler;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter
//
// Justification: this file is a data-access layer. Table names are built
// from $wpdb->prefix . a static suffix, which is safe by construction; the linter
// cannot prove that. All user input flows through $wpdb->prepare() placeholders.


final class LogRepository {

	private function table(): string {
		global $wpdb;
		return $wpdb->prefix . 'wplens_crawler_log';
	}

	public function totals_by_bot( int $days = 30 ): array {
		global $wpdb;
		$table = $this->table();
		$since = gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS );

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT bot_slug, bot_family, COUNT(*) AS visits FROM {$table} WHERE visited_at >= %s GROUP BY bot_slug, bot_family ORDER BY visits DESC",
				$since
			),
			ARRAY_A
		);

		return is_array( $rows ) ? $rows : array();
	}

	public function daily_series( int $days = 30 ): array {
		global $wpdb;
		$table = $this->table();
		$since = gmdate( 'Y-m-d 00:00:00', time() - $days * DAY_IN_SECONDS );

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT DATE(visited_at) AS day, COUNT(*) AS visits FROM {$table} WHERE visited_at >= %s GROUP BY DATE(visited_at) ORDER BY day ASC",
				$since
			),
			ARRAY_A
		);

		return is_array( $rows ) ? $rows : array();
	}

	public function top_pages( int $days = 30, int $limit = 10 ): array {
		global $wpdb;
		$table = $this->table();
		$since = gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS );

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT request_uri, COUNT(*) AS visits, MAX(post_id) AS post_id FROM {$table} WHERE visited_at >= %s GROUP BY request_uri ORDER BY visits DESC LIMIT %d",
				$since,
				$limit
			),
			ARRAY_A
		);

		return is_array( $rows ) ? $rows : array();
	}

	public function recent( int $limit = 100 ): array {
		global $wpdb;
		$table = $this->table();
		$limit = max( 1, min( 500, $limit ) );

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT id, visited_at, bot_slug, bot_family, user_agent, request_uri, request_method, response_code, post_id FROM {$table} ORDER BY visited_at DESC LIMIT %d",
				$limit
			),
			ARRAY_A
		);

		return is_array( $rows ) ? $rows : array();
	}

	public function blind_spots( int $days = 30, int $limit = 50 ): array {
		global $wpdb;
		$table_log = $this->table();
		$since     = gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS );

		// Published posts/pages with zero crawler hits in window.
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT p.ID, p.post_title, p.post_type, p.post_modified_gmt
				 FROM {$wpdb->posts} p
				 LEFT JOIN {$table_log} l
				   ON l.post_id = p.ID AND l.visited_at >= %s
				 WHERE p.post_status = 'publish'
				   AND p.post_type IN ('post','page')
				   AND l.id IS NULL
				 ORDER BY p.post_modified_gmt DESC
				 LIMIT %d",
				$since,
				$limit
			),
			ARRAY_A
		);

		return is_array( $rows ) ? $rows : array();
	}

	public function total_count( int $days = 30 ): int {
		global $wpdb;
		$table = $this->table();
		$since = gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS );

		return (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE visited_at >= %s", $since )
		);
	}

	public function truncate(): int {
		global $wpdb;
		$table = $this->table();
		return (int) $wpdb->query( "TRUNCATE TABLE {$table}" );
	}
}
