<?php

namespace SerpIQ\Api\Controllers;

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

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

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

	public function register_routes(): void {
		register_rest_route(
			'serpiq/v1',
			'/opportunities/list',
			array(
				'methods'             => 'GET',
				'callback'            => array( $this, 'list_opportunities' ),
				'permission_callback' => array( \SerpIQ\Api\Rest_Permission::class, 'admin_rest_request' ),
				'args'                => 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' ) ),
					'country' => array( 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ),
					'device'  => array( 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ),
					'search'  => array( 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ),
					'orderBy' => array( 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_key' ),
					'orderDir' => array( 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_key' ),
					'min_impressions' => array( 'required' => false, 'type' => 'integer', 'sanitize_callback' => 'absint' ),
					'max_ctr_pct'      => array( 'required' => false, 'type' => 'number' ),
					'min_impressions_pos' => array( 'required' => false, 'type' => 'integer', 'sanitize_callback' => 'absint' ),
					'page'    => array( 'required' => false, 'type' => 'integer', 'default' => 1, 'sanitize_callback' => 'absint' ),
					'perPage' => array( 'required' => false, 'type' => 'integer', 'default' => 10, 'sanitize_callback' => 'absint' ),
				),
			)
		);

		register_rest_route(
			'serpiq/v1',
			'/opportunities/high-impressions-low-ctr',
			array(
				'methods'             => 'GET',
				'callback'            => array( $this, 'high_impressions_low_ctr' ),
				'permission_callback' => array( \SerpIQ\Api\Rest_Permission::class, 'admin_rest_request' ),
				'args'                => 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' ) ),
					'country' => array( 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ),
					'device'  => array( 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ),
					'min_impressions' => array( 'required' => false, 'type' => 'integer', 'sanitize_callback' => 'absint' ),
					'max_ctr_pct'      => array( 'required' => false, 'type' => 'number' ),
					'page'    => array( 'required' => false, 'type' => 'integer', 'default' => 1, 'sanitize_callback' => 'absint' ),
					'perPage' => array( 'required' => false, 'type' => 'integer', 'default' => 50, 'sanitize_callback' => 'absint' ),
				),
			)
		);

		register_rest_route(
			'serpiq/v1',
			'/opportunities/position-8-20',
			array(
				'methods'             => 'GET',
				'callback'            => array( $this, 'position_8_20' ),
				'permission_callback' => array( \SerpIQ\Api\Rest_Permission::class, 'admin_rest_request' ),
				'args'                => 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' ) ),
					'country' => array( 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ),
					'device'  => array( 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ),
					'min_impressions' => array( 'required' => false, 'type' => 'integer', 'sanitize_callback' => 'absint' ),
					'page'    => array( 'required' => false, 'type' => 'integer', 'default' => 1, 'sanitize_callback' => 'absint' ),
					'perPage' => array( 'required' => false, 'type' => 'integer', 'default' => 50, 'sanitize_callback' => 'absint' ),
				),
			)
		);

		register_rest_route(
			'serpiq/v1',
			'/opportunities/query-pages',
			array(
				'methods'             => 'POST',
				'callback'            => array( $this, 'query_pages' ),
				'permission_callback' => array( \SerpIQ\Api\Rest_Permission::class, 'admin_rest_request' ),
				'args'                => array(
					'queries' => array( 'required' => true, 'type' => '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' ) ),
					'country' => array( 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ),
					'device'  => array( 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ),
					'limit'   => array( 'required' => false, 'type' => 'integer', 'default' => 3, 'sanitize_callback' => 'absint' ),
				),
			)
		);
	}

	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 );
	}

	public function list_opportunities( \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' ) );
		$country = sanitize_text_field( (string) $request->get_param( 'country' ) );
		$device  = sanitize_text_field( (string) $request->get_param( 'device' ) );
		$search  = sanitize_text_field( (string) $request->get_param( 'search' ) );
		$order_by = sanitize_key( (string) $request->get_param( 'orderBy' ) );
		$order_dir = strtoupper( sanitize_key( (string) $request->get_param( 'orderDir' ) ) );

		$allowed_order = array( 'impressions', 'clicks', 'ctr', 'position', 'query' );
		if ( ! in_array( $order_by, $allowed_order, true ) ) {
			$order_by = 'impressions';
		}
		if ( 'ASC' !== $order_dir && 'DESC' !== $order_dir ) {
			$order_dir = 'DESC';
		}

		$min_impressions = max( 0, absint( $request->get_param( 'min_impressions' ) ) );
		if ( $min_impressions <= 0 ) {
			$min_impressions = 100;
		}

		$min_impressions_pos = max( 0, absint( $request->get_param( 'min_impressions_pos' ) ) );
		if ( $min_impressions_pos <= 0 ) {
			$min_impressions_pos = 10;
		}

		$max_ctr_pct = (float) $request->get_param( 'max_ctr_pct' );
		if ( $max_ctr_pct <= 0 || $max_ctr_pct > 100 ) {
			$max_ctr_pct = 5.0;
		}
		$max_ctr_decimal = $max_ctr_pct / 100;

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

		$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 ) . '%';
		}

		// Shared aggregations used in both "snippet" and "position" opportunities.
		$having = '
			(
				COALESCE(SUM(impressions),0) >= %d
				AND ( COALESCE(SUM(clicks),0) / NULLIF(COALESCE(SUM(impressions),0),0) ) <= %f
			)
			OR
			(
				COALESCE(SUM(impressions),0) >= %d
				AND ( COALESCE(SUM(position * impressions),0) / NULLIF(COALESCE(SUM(impressions),0),0) ) BETWEEN 8 AND 20
			)
		';

		// Count distinct queries that match either condition.
		$sql_count = "
			SELECT COUNT(*) FROM (
				SELECT query
				FROM %i
				WHERE {$where}
				GROUP BY query
				HAVING {$having}
			) AS sub
		";
		$count_params = array_merge( array( $table ), $params, array( $min_impressions, $max_ctr_decimal, $min_impressions_pos ) );
		$total        = (int) $wpdb->get_var( $wpdb->prepare( $sql_count, $count_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
			HAVING {$having}
			ORDER BY %i {$order_dir}, impressions DESC
			LIMIT %d OFFSET %d
		";
		$list_params = array_merge( array( $table ), $params, array( $min_impressions, $max_ctr_decimal, $min_impressions_pos, $order_by, $per_page, $offset ) );
		$rows        = $wpdb->get_results( $wpdb->prepare( $sql, $list_params ), ARRAY_A );

		$items = array();
		$queries = array();
		if ( is_array( $rows ) ) {
			foreach ( $rows as $row ) {
				$q = isset( $row['query'] ) ? (string) $row['query'] : '';
				$q = trim( $q );
				if ( '' === $q ) {
					continue;
				}
				$clicks      = (float) ( $row['clicks'] ?? 0 );
				$impressions = (float) ( $row['impressions'] ?? 0 );
				$ctr         = (float) ( $row['ctr'] ?? 0 );
				$position    = (float) ( $row['position'] ?? 0 );

				$labels = array();
				if ( $impressions >= $min_impressions && $ctr <= $max_ctr_decimal ) {
					$labels[] = 'improve_snippet';
				}
				if ( $impressions >= $min_impressions_pos && $position >= 8 && $position <= 20 ) {
					$labels[] = 'new_content'; // refined after query→page resolution
				}

				$items[] = array(
					'query'       => $q,
					'clicks'      => $clicks,
					'impressions' => $impressions,
					'ctr'         => $ctr,
					'position'    => $position,
					'labels'      => $labels,
					'pages'       => array(),
				);
				$queries[] = $q;
			}
		}

		// Resolve top page for the returned queries (limit 1) and refine "new_content" → "improve_content" when a WP post is detected.
		if ( ! empty( $items ) ) {
			$page_map = $this->query_pages_internal( $queries, $start, $end, $country, $device, 1 );
			foreach ( $items as &$item ) {
				$q = (string) ( $item['query'] ?? '' );
				$pages = isset( $page_map[ $q ] ) && is_array( $page_map[ $q ] ) ? $page_map[ $q ] : array();
				$item['pages'] = $pages;
				$has_existing = false;
				foreach ( $pages as $p ) {
					if ( (int) ( $p['post_id'] ?? 0 ) > 0 ) {
						$has_existing = true;
						break;
					}
				}
				if ( $has_existing && in_array( 'new_content', $item['labels'], true ) ) {
					$item['labels'] = array_values( array_diff( $item['labels'], array( 'new_content' ) ) );
					if ( ! in_array( 'improve_content', $item['labels'], true ) ) {
						$item['labels'][] = 'improve_content';
					}
				}
			}
			unset( $item );
		}

		return new \WP_REST_Response(
			array(
				'page'    => $page,
				'perPage' => $per_page,
				'total'   => $total,
				'orderBy' => $order_by,
				'orderDir'=> $order_dir,
				'items'   => $items,
			),
			200
		);
	}

	public function high_impressions_low_ctr( \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' ) );
		$country = sanitize_text_field( (string) $request->get_param( 'country' ) );
		$device  = sanitize_text_field( (string) $request->get_param( 'device' ) );

		$min_impressions = max( 0, absint( $request->get_param( 'min_impressions' ) ) );
		if ( $min_impressions <= 0 ) {
			$min_impressions = 100;
		}

		$max_ctr_pct = (float) $request->get_param( 'max_ctr_pct' );
		if ( $max_ctr_pct <= 0 || $max_ctr_pct > 100 ) {
			$max_ctr_pct = 5.0;
		}
		$max_ctr_decimal = $max_ctr_pct / 100;

		$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;

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

		$sql_count = "
			SELECT COUNT(*) FROM (
				SELECT query
				FROM %i
				WHERE date BETWEEN %s AND %s
				" . ( '' !== $country ? ' AND country = %s' : '' ) . "
				" . ( '' !== $device ? ' AND device = %s' : '' ) . "
				GROUP BY query
				HAVING COALESCE(SUM(impressions),0) >= %d
				   AND ( COALESCE(SUM(clicks),0) / NULLIF(COALESCE(SUM(impressions),0),0) ) <= %f
			) AS sub
		";
		$count_params = array( $start, $end );
		if ( '' !== $country ) {
			$count_params[] = $country;
		}
		if ( '' !== $device ) {
			$count_params[] = $device;
		}
		$count_params[] = $min_impressions;
		$count_params[] = $max_ctr_decimal;
		$total = (int) $wpdb->get_var( $wpdb->prepare( $sql_count, array_merge( array( $table ), $count_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 date BETWEEN %s AND %s
			" . ( '' !== $country ? ' AND country = %s' : '' ) . "
			" . ( '' !== $device ? ' AND device = %s' : '' ) . "
			GROUP BY query
			HAVING COALESCE(SUM(impressions),0) >= %d
			   AND ( COALESCE(SUM(clicks),0) / NULLIF(COALESCE(SUM(impressions),0),0) ) <= %f
			ORDER BY impressions DESC
			LIMIT %d OFFSET %d
		";
		$params = array( $start, $end );
		if ( '' !== $country ) {
			$params[] = $country;
		}
		if ( '' !== $device ) {
			$params[] = $device;
		}
		$params[] = $min_impressions;
		$params[] = $max_ctr_decimal;
		$params[] = $per_page;
		$params[] = $offset;

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

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

	public function position_8_20( \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' ) );
		$country = sanitize_text_field( (string) $request->get_param( 'country' ) );
		$device  = sanitize_text_field( (string) $request->get_param( 'device' ) );

		$min_impressions = max( 0, absint( $request->get_param( 'min_impressions' ) ) );
		if ( $min_impressions <= 0 ) {
			$min_impressions = 10;
		}

		$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;

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

		$sql_count = "
			SELECT COUNT(*) FROM (
				SELECT query
				FROM %i
				WHERE date BETWEEN %s AND %s
				" . ( '' !== $country ? ' AND country = %s' : '' ) . "
				" . ( '' !== $device ? ' AND device = %s' : '' ) . "
				GROUP BY query
				HAVING COALESCE(SUM(impressions),0) >= %d
				   AND ( COALESCE(SUM(position * impressions),0) / NULLIF(COALESCE(SUM(impressions),0),0) ) BETWEEN 8 AND 20
			) AS sub
		";
		$count_params = array( $start, $end );
		if ( '' !== $country ) {
			$count_params[] = $country;
		}
		if ( '' !== $device ) {
			$count_params[] = $device;
		}
		$count_params[] = $min_impressions;
		$total = (int) $wpdb->get_var( $wpdb->prepare( $sql_count, array_merge( array( $table ), $count_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 date BETWEEN %s AND %s
			" . ( '' !== $country ? ' AND country = %s' : '' ) . "
			" . ( '' !== $device ? ' AND device = %s' : '' ) . "
			GROUP BY query
			HAVING COALESCE(SUM(impressions),0) >= %d
			   AND ( COALESCE(SUM(position * impressions),0) / NULLIF(COALESCE(SUM(impressions),0),0) ) BETWEEN 8 AND 20
			ORDER BY impressions DESC
			LIMIT %d OFFSET %d
		";
		$params = array( $start, $end );
		if ( '' !== $country ) {
			$params[] = $country;
		}
		if ( '' !== $device ) {
			$params[] = $device;
		}
		$params[] = $min_impressions;
		$params[] = $per_page;
		$params[] = $offset;

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

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

	/**
	 * For a list of queries, return top page(s) for each query within the period.
	 * Used by Opportunities to avoid misleading "new content" suggestions when a query already maps to a page.
	 *
	 * Response shape:
	 * {
	 *   items: {
	 *     "<query>": [
	 *        { page_url, clicks, impressions, ctr, position, post_id, post_title }
	 *     ]
	 *   }
	 * }
	 */
	public function query_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' ) );
		$country = sanitize_text_field( (string) $request->get_param( 'country' ) );
		$device  = sanitize_text_field( (string) $request->get_param( 'device' ) );

		$limit = absint( $request->get_param( 'limit' ) );
		$limit = $limit > 0 ? min( 10, $limit ) : 3;

		$queries = $request->get_param( 'queries' );
		$queries = is_array( $queries ) ? $queries : array();
		$queries = array_values(
			array_filter(
				array_map(
					static function ( $q ) {
						$q = is_scalar( $q ) ? (string) $q : '';
						$q = trim( $q );
						return $q;
					},
					$queries
				)
			)
		);
		$queries = array_slice( array_unique( $queries ), 0, 200 );

		if ( empty( $queries ) ) {
			return new \WP_REST_Response( array( 'items' => array() ), 200 );
		}

		$grouped = $this->query_pages_internal( $queries, $start, $end, $country, $device, $limit );

		return new \WP_REST_Response(
			array(
				'items' => $grouped,
			),
			200
		);
	}

	private function query_pages_internal( array $queries, string $start, string $end, string $country, string $device, int $limit ): array {
		global $wpdb;

		$limit = $limit > 0 ? min( 10, $limit ) : 3;
		$queries = array_slice( array_unique( $queries ), 0, 200 );
		if ( empty( $queries ) ) {
			return array();
		}

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

		$where  = 'date BETWEEN %s AND %s AND query IN (' . implode( ',', array_fill( 0, count( $queries ), '%s' ) ) . ')';
		$params = array_merge( array( $start, $end ), $queries );

		if ( '' !== $country ) {
			$where    .= ' AND country = %s';
			$params[] = $country;
		}
		if ( '' !== $device ) {
			$where    .= ' AND device = %s';
			$params[] = $device;
		}

		$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 query ASC, impressions DESC
		";

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

		$grouped = array();
		if ( is_array( $rows ) ) {
			foreach ( $rows as $row ) {
				$q = isset( $row['query'] ) ? (string) $row['query'] : '';
				if ( '' === $q ) {
					continue;
				}
				if ( ! isset( $grouped[ $q ] ) ) {
					$grouped[ $q ] = array();
				}
				if ( count( $grouped[ $q ] ) >= $limit ) {
					continue;
				}

				$page_url = isset( $row['page_url'] ) ? (string) $row['page_url'] : '';
				$post_id  = 0;
				$title    = '';
				if ( '' !== $page_url && function_exists( 'url_to_postid' ) ) {
					$post_id = (int) url_to_postid( $page_url );
					if ( $post_id > 0 ) {
						$title = (string) get_the_title( $post_id );
					}
				}

				$grouped[ $q ][] = array(
					'page_url'     => $page_url,
					'clicks'       => (float) ( $row['clicks'] ?? 0 ),
					'impressions'  => (float) ( $row['impressions'] ?? 0 ),
					'ctr'          => (float) ( $row['ctr'] ?? 0 ),
					'position'     => (float) ( $row['position'] ?? 0 ),
					'post_id'      => $post_id,
					'post_title'   => $title,
				);
			}
		}

		return $grouped;
	}

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