<?php

namespace SerpIQ\Api\Controllers;

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

final class Queries_Controller {
	private static ?Queries_Controller $instance = null;

	public static function instance(): Queries_Controller {
		if ( null === self::$instance ) {
			self::$instance = new self();
		}
		return self::$instance;
	}

	public function register_routes(): void {
		register_rest_route(
			'serpiq/v1',
			'/queries',
			array(
				'methods'             => 'GET',
				'callback'            => array( $this, 'list' ),
				'permission_callback' => array( \SerpIQ\Api\Rest_Permission::class, 'admin_rest_request' ),
				'args'                => $this->common_list_args( array( 'clicks', 'impressions', 'ctr', 'position', 'query' ) ),
			)
		);

		register_rest_route(
			'serpiq/v1',
			'/queries-with-pages',
			array(
				'methods'             => 'GET',
				'callback'            => array( $this, 'list_with_pages' ),
				'permission_callback' => array( \SerpIQ\Api\Rest_Permission::class, 'admin_rest_request' ),
				'args'                => $this->common_list_args( array( 'clicks', 'impressions', 'ctr', 'position', 'query' ) ),
			)
		);

		register_rest_route(
			'serpiq/v1',
			'/queries-with-change',
			array(
				'methods'             => 'GET',
				'callback'            => array( $this, 'list_with_change' ),
				'permission_callback' => array( \SerpIQ\Api\Rest_Permission::class, 'admin_rest_request' ),
				'args'                => $this->common_list_args( array( 'clicks', 'impressions', 'ctr', 'position', 'query' ) ),
			)
		);
	}

	public function validate_date_arg( mixed $value, \WP_REST_Request $request, string $param ): bool {
		unset( $request, $param );
		$v = is_scalar( $value ) ? (string) $value : '';
		$v = trim( $v );
		if ( '' === $v ) {
			return true;
		}
		return (bool) preg_match( '/^\d{4}-\d{2}-\d{2}$/', $v );
	}

	/**
	 * @param string[] $allowed_order
	 * @return array<string, array<string, mixed>>
	 */
	private function common_list_args( array $allowed_order ): array {
		return array(
			'start'   => array(
				'required'          => false,
				'type'              => 'string',
				'sanitize_callback' => 'sanitize_text_field',
				'validate_callback' => array( $this, 'validate_date_arg' ),
			),
			'end'     => array(
				'required'          => false,
				'type'              => 'string',
				'sanitize_callback' => 'sanitize_text_field',
				'validate_callback' => array( $this, 'validate_date_arg' ),
			),
			'search'  => array(
				'required'          => false,
				'type'              => 'string',
				'sanitize_callback' => 'sanitize_text_field',
			),
			'country' => array(
				'required'          => false,
				'type'              => 'string',
				'sanitize_callback' => 'sanitize_text_field',
			),
			'device'  => array(
				'required'          => false,
				'type'              => 'string',
				'sanitize_callback' => 'sanitize_text_field',
			),
			'page'    => array(
				'required'          => false,
				'type'              => 'integer',
				'default'           => 1,
				'sanitize_callback' => 'absint',
			),
			'perPage' => array(
				'required'          => false,
				'type'              => 'integer',
				'default'           => 50,
				'sanitize_callback' => 'absint',
			),
			'orderBy' => array(
				'required'          => false,
				'type'              => 'string',
				'sanitize_callback' => 'sanitize_key',
				'enum'              => $allowed_order,
			),
			'orderDir' => array(
				'required'          => false,
				'type'              => 'string',
				'default'           => 'DESC',
				'sanitize_callback' => 'sanitize_text_field',
				'enum'              => array( 'ASC', 'DESC', 'asc', 'desc' ),
			),
		);
	}

	public function list( \WP_REST_Request $request ): \WP_REST_Response {
		global $wpdb;

		$start = $this->sanitize_date( (string) $request->get_param( 'start' ) ) ?? gmdate( 'Y-m-d', strtotime( '-28 day' ) );
		$end = $this->sanitize_date( (string) $request->get_param( 'end' ) ) ?? gmdate( 'Y-m-d', strtotime( '-1 day' ) );
		$search = sanitize_text_field( (string) $request->get_param( 'search' ) );
		$country = sanitize_text_field( (string) $request->get_param( 'country' ) );
		$device = sanitize_text_field( (string) $request->get_param( 'device' ) );

		$page = max( 1, absint( $request->get_param( 'page' ) ) );
		$per_page = absint( $request->get_param( 'perPage' ) );
		$per_page = $per_page > 0 ? min( 200, $per_page ) : 50;
		$offset = ( $page - 1 ) * $per_page;

		$order_by = sanitize_key( (string) $request->get_param( 'orderBy' ) );
		$order_dir = strtoupper( (string) $request->get_param( 'orderDir' ) );
		$order_dir = in_array( $order_dir, array( 'ASC', 'DESC' ), true ) ? $order_dir : 'DESC';
		$allowed_order = array( 'clicks', 'impressions', 'ctr', 'position', 'query' );
		if ( ! in_array( $order_by, $allowed_order, true ) ) {
			$order_by = 'clicks';
		}

		$table = $wpdb->prefix . 'serpiq_queries';
		$where = 'date BETWEEN %s AND %s';
		$params = array( $start, $end );

		if ( '' !== $country ) {
			$where .= ' AND country = %s';
			$params[] = $country;
		}
		if ( '' !== $device ) {
			$where .= ' AND device = %s';
			$params[] = $device;
		}
		if ( '' !== $search ) {
			$where .= ' AND query LIKE %s';
			$params[] = '%' . $wpdb->esc_like( $search ) . '%';
		}

		$sql_count = "SELECT COUNT(DISTINCT query) FROM %i WHERE {$where}";
		$total = (int) $wpdb->get_var( $wpdb->prepare( $sql_count, array_merge( array( $table ), $params ) ) );

		$sql = "
			SELECT
				query,
				COALESCE(SUM(clicks),0) AS clicks,
				COALESCE(SUM(impressions),0) AS impressions,
				CASE WHEN COALESCE(SUM(impressions),0) > 0 THEN (COALESCE(SUM(clicks),0) / COALESCE(SUM(impressions),0)) ELSE 0 END AS ctr,
				CASE WHEN COALESCE(SUM(impressions),0) > 0 THEN (COALESCE(SUM(position * impressions),0) / COALESCE(SUM(impressions),0)) ELSE 0 END AS position
			FROM %i
			WHERE {$where}
			GROUP BY query
			ORDER BY %i {$order_dir}
			LIMIT %d OFFSET %d
		";

		$params2 = array_merge( array( $table ), $params, array( $order_by, $per_page, $offset ) );
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $params2 ), ARRAY_A );

		return new \WP_REST_Response(
			array(
				'page'    => $page,
				'perPage' => $per_page,
				'total'   => $total,
				'items'   => is_array( $rows ) ? $rows : array(),
			),
			200
		);
	}

	public function list_with_pages( \WP_REST_Request $request ): \WP_REST_Response {
		global $wpdb;

		$start = $this->sanitize_date( (string) $request->get_param( 'start' ) ) ?? gmdate( 'Y-m-d', strtotime( '-28 day' ) );
		$end   = $this->sanitize_date( (string) $request->get_param( 'end' ) ) ?? gmdate( 'Y-m-d', strtotime( '-1 day' ) );
		$search  = sanitize_text_field( (string) $request->get_param( 'search' ) );
		$country = sanitize_text_field( (string) $request->get_param( 'country' ) );
		$device  = sanitize_text_field( (string) $request->get_param( 'device' ) );

		$table  = $wpdb->prefix . 'serpiq_queries';
		$where  = 'date BETWEEN %s AND %s';
		$params = array( $start, $end );

		if ( '' !== $country ) {
			$where     .= ' AND country = %s';
			$params[] = $country;
		}
		if ( '' !== $device ) {
			$where     .= ' AND device = %s';
			$params[] = $device;
		}
		if ( '' !== $search ) {
			$where     .= ' AND query LIKE %s';
			$params[] = '%' . $wpdb->esc_like( $search ) . '%';
		}

		// Hard cap to avoid huge payloads.
		$max_rows = 5000;

		$sql = "
			SELECT
				query,
				page_url,
				COALESCE(SUM(clicks),0) AS clicks,
				COALESCE(SUM(impressions),0) AS impressions,
				CASE WHEN COALESCE(SUM(impressions),0) > 0
					THEN (COALESCE(SUM(clicks),0) / COALESCE(SUM(impressions),0))
					ELSE 0 END AS ctr,
				CASE WHEN COALESCE(SUM(impressions),0) > 0
					THEN (COALESCE(SUM(position * impressions),0) / COALESCE(SUM(impressions),0))
					ELSE 0 END AS position
			FROM %i
			WHERE {$where}
			GROUP BY query, page_url
			ORDER BY clicks DESC
			LIMIT %d
		";

		$params2 = array_merge( array( $table ), $params, array( $max_rows ) );
		$rows    = $wpdb->get_results( $wpdb->prepare( $sql, $params2 ), ARRAY_A );

		return new \WP_REST_Response(
			array(
				'items' => is_array( $rows ) ? $rows : array(),
			),
			200
		);
	}

	/**
	 * List queries with metrics for the current period and the immediately previous period
	 * (same length), to allow "change" calculations on the frontend.
	 *
	 * Returns items of the shape:
	 * [
	 *   'query'    => '...',
	 *   'current'  => [ 'clicks' => ..., 'impressions' => ..., 'ctr' => ..., 'position' => ... ],
	 *   'previous' => [ 'clicks' => ..., 'impressions' => ..., 'ctr' => ..., 'position' => ... ],
	 * ]
	 */
	public function list_with_change( \WP_REST_Request $request ): \WP_REST_Response {
		global $wpdb;

		$start = $this->sanitize_date( (string) $request->get_param( 'start' ) ) ?? gmdate( 'Y-m-d', strtotime( '-28 day' ) );
		$end   = $this->sanitize_date( (string) $request->get_param( 'end' ) ) ?? gmdate( 'Y-m-d', strtotime( '-1 day' ) );
		$search  = sanitize_text_field( (string) $request->get_param( 'search' ) );
		$country = sanitize_text_field( (string) $request->get_param( 'country' ) );
		$device  = sanitize_text_field( (string) $request->get_param( 'device' ) );

		// Compute previous period range (same length immediately before current start).
		$start_ts = strtotime( $start );
		$end_ts   = strtotime( $end );
		if ( false === $start_ts || false === $end_ts || $start_ts > $end_ts ) {
			return new \WP_REST_Response(
				array(
					'items' => array(),
				),
				200
			);
		}

		$days         = (int) floor( ( $end_ts - $start_ts ) / DAY_IN_SECONDS ) + 1;
		$prev_end_ts  = $start_ts - DAY_IN_SECONDS;
		$prev_start_ts = $prev_end_ts - ( $days - 1 ) * DAY_IN_SECONDS;
		$prev_start   = gmdate( 'Y-m-d', $prev_start_ts );
		$prev_end     = gmdate( 'Y-m-d', $prev_end_ts );

		$table = $wpdb->prefix . 'serpiq_queries';

		// Shared filters (country, device, search).
		$filter_sql  = '';
		$filter_args = array();
		if ( '' !== $country ) {
			$filter_sql  .= ' AND country = %s';
			$filter_args[] = $country;
		}
		if ( '' !== $device ) {
			$filter_sql  .= ' AND device = %s';
			$filter_args[] = $device;
		}
		if ( '' !== $search ) {
			$filter_sql  .= ' AND query LIKE %s';
			$filter_args[] = '%' . $wpdb->esc_like( $search ) . '%';
		}

		// Current period.
		$sql_current = "
			SELECT
				query,
				COALESCE(SUM(clicks),0) AS clicks,
				COALESCE(SUM(impressions),0) AS impressions,
				CASE WHEN COALESCE(SUM(impressions),0) > 0
					THEN (COALESCE(SUM(clicks),0) / COALESCE(SUM(impressions),0))
					ELSE 0 END AS ctr,
				CASE WHEN COALESCE(SUM(impressions),0) > 0
					THEN (COALESCE(SUM(position * impressions),0) / COALESCE(SUM(impressions),0))
					ELSE 0 END AS position
			FROM %i
			WHERE date BETWEEN %s AND %s
			{$filter_sql}
			GROUP BY query
			ORDER BY clicks DESC
			LIMIT 5000
		";
		$args_current = array_merge( array( $table ), array( $start, $end ), $filter_args );
		$current_rows = $wpdb->get_results( $wpdb->prepare( $sql_current, $args_current ), ARRAY_A );

		// Previous period.
		$sql_prev = "
			SELECT
				query,
				COALESCE(SUM(clicks),0) AS clicks,
				COALESCE(SUM(impressions),0) AS impressions,
				CASE WHEN COALESCE(SUM(impressions),0) > 0
					THEN (COALESCE(SUM(clicks),0) / COALESCE(SUM(impressions),0))
					ELSE 0 END AS ctr,
				CASE WHEN COALESCE(SUM(impressions),0) > 0
					THEN (COALESCE(SUM(position * impressions),0) / COALESCE(SUM(impressions),0))
					ELSE 0 END AS position
			FROM %i
			WHERE date BETWEEN %s AND %s
			{$filter_sql}
			GROUP BY query
		";
		$args_prev = array_merge( array( $table ), array( $prev_start, $prev_end ), $filter_args );
		$prev_rows = $wpdb->get_results( $wpdb->prepare( $sql_prev, $args_prev ), ARRAY_A );

		// Index previous rows by query for quick lookup.
		$prev_map = array();
		if ( is_array( $prev_rows ) ) {
			foreach ( $prev_rows as $row ) {
				$q = isset( $row['query'] ) ? (string) $row['query'] : '';
				if ( '' === $q ) {
					continue;
				}
				$prev_map[ $q ] = array(
					'clicks'      => (int) ( $row['clicks'] ?? 0 ),
					'impressions' => (int) ( $row['impressions'] ?? 0 ),
					'ctr'         => (float) ( $row['ctr'] ?? 0 ),
					'position'    => (float) ( $row['position'] ?? 0 ),
				);
			}
		}

		$items = array();
		if ( is_array( $current_rows ) ) {
			foreach ( $current_rows as $row ) {
				$q = isset( $row['query'] ) ? (string) $row['query'] : '';
				if ( '' === $q ) {
					continue;
				}
				$current_metrics = array(
					'clicks'      => (int) ( $row['clicks'] ?? 0 ),
					'impressions' => (int) ( $row['impressions'] ?? 0 ),
					'ctr'         => (float) ( $row['ctr'] ?? 0 ),
					'position'    => (float) ( $row['position'] ?? 0 ),
				);
				$prev_metrics = $prev_map[ $q ] ?? array(
					'clicks'      => 0,
					'impressions' => 0,
					'ctr'         => 0.0,
					'position'    => 0.0,
				);

				$items[] = array(
					'query'    => $q,
					'current'  => $current_metrics,
					'previous' => $prev_metrics,
				);
			}
		}

		return new \WP_REST_Response(
			array(
				'currentRange'  => array(
					'start' => $start,
					'end'   => $end,
				),
				'previousRange' => array(
					'start' => $prev_start,
					'end'   => $prev_end,
				),
				'items'         => $items,
			),
			200
		);
	}

	private function sanitize_date( string $date ): ?string {
		if ( '' === $date ) {
			return null;
		}
		return preg_match( '/^\d{4}-\d{2}-\d{2}$/', $date ) ? $date : null;
	}
}

