dimension}, COALESCE(t.position, 0) as position, COALESCE(t.diffPosition, 0) as diffPosition, COALESCE(t.diffPosition, 100) as diffPosition1, COALESCE(t.impressions, 0) as impressions, COALESCE(t.diffImpressions, 0) as diffImpressions, COALESCE(t.clicks, 0) as clicks, COALESCE(t.diffClicks, 0) as diffClicks, COALESCE(t.ctr, 0) as ctr, COALESCE(t.diffCtr, 0) as diffCtr
			FROM {$wpdb->prefix}rank_math_analytics_keyword_manager km
			LEFT JOIN (
				SELECT
					t1.{$dimension} as {$dimension}, ROUND( t1.position, 0 ) as position, ROUND( t1.impressions, 0 ) as impressions, ROUND( t1.clicks, 0 ) as clicks, ROUND( t1.ctr, 0 ) as ctr,
					COALESCE( ROUND( t1.position - COALESCE( t2.position, 100 ), 0 ), 0 ) as diffPosition,
					COALESCE( ROUND( t1.impressions - COALESCE( t2.impressions, 100 ), 0 ), 0 ) as diffImpressions,
					COALESCE( ROUND( t1.clicks - COALESCE( t2.clicks, 100 ), 0 ), 0 ) as diffClicks,
					COALESCE( ROUND( t1.ctr - COALESCE( t2.ctr, 100 ), 0 ), 0 ) as diffCtr
				FROM
					(SELECT a.{$dimension}, a.position, a.impressions,a.clicks,a.ctr FROM {$wpdb->prefix}rank_math_analytics_gsc AS a
					 WHERE 1 = 1{$ids_where}) AS t1
				LEFT JOIN
					(SELECT a.{$dimension}, a.position, a.impressions,a.clicks,a.ctr FROM {$wpdb->prefix}rank_math_analytics_gsc AS a
					 WHERE 1 = 1{$old_ids_where}) AS t2
				ON t1.{$dimension} = t2.{$dimension}) AS t on t.{$dimension} = km.keyword
				{$where_like_keyword1}
			{$where}
			{$order}
			{$limit}",
			ARRAY_A
		);
		// phpcs:enable

		// Step6. Get keywords list from above results.
		$keywords = array_column( $positions, 'query' );
		$keywords = array_map( 'esc_sql', $keywords );
		$keywords = array_map( 'strtolower', $keywords );
		$keywords = '(\'' . join( '\', \'', $keywords ) . '\')';

		// step7. Get other metrics data.
		$query   = $wpdb->prepare(
			"SELECT t1.{$dimension} as {$dimension}, t1.clicks, t1.impressions, t1.ctr,
				COALESCE( t1.clicks - t2.clicks, 0 ) as diffClicks,
				COALESCE( t1.impressions - t2.impressions, 0 ) as diffImpressions,
				COALESCE( t1.ctr - t2.ctr, 0 ) as diffCtr
			FROM
				( SELECT {$dimension}, SUM( clicks ) as clicks, SUM(impressions) as impressions, AVG(position) as position, AVG(ctr) as ctr FROM {$wpdb->prefix}rank_math_analytics_gsc WHERE 1 = 1{$dates} AND {$dimension} IN {$keywords} GROUP BY {$dimension}) as t1
			LEFT JOIN
				( SELECT {$dimension}, SUM( clicks ) as clicks, SUM(impressions) as impressions, AVG(position) as position, AVG(ctr) as ctr FROM {$wpdb->prefix}rank_math_analytics_gsc WHERE 1 = 1{$dates} AND {$dimension} IN {$keywords} GROUP BY {$dimension}) as t2
			ON t1.query = t2.query",
			Stats::get()->start_date,
			Stats::get()->end_date,
			Stats::get()->compare_start_date,
			Stats::get()->compare_end_date
		);
		$metrics = $wpdb->get_results( $query, ARRAY_A );

		// Step8. Merge above two results.
		$positions = Stats::get()->set_dimension_as_key( $positions, $dimension );
		$metrics   = Stats::get()->set_dimension_as_key( $metrics, $dimension );
		$data      = Stats::get()->get_merged_metrics( $positions, $metrics );

		// Step9. Construct return data.
		foreach ( $data as $keyword => $row ) {
			$data[ $keyword ]['graph'] = [];

			$data[ $keyword ]['clicks'] = [
				'total'      => (int) $data[ $keyword ]['clicks'],
				'difference' => (int) $data[ $keyword ]['diffClicks'],
			];

			$data[ $keyword ]['impressions'] = [
				'total'      => (int) $data[ $keyword ]['impressions'],
				'difference' => (int) $data[ $keyword ]['diffImpressions'],
			];

			$data[ $keyword ]['position'] = [
				'total'      => (float) $data[ $keyword ]['position'],
				'difference' => (float) $data[ $keyword ]['diffPosition'],
			];

			$data[ $keyword ]['ctr'] = [
				'total'      => (float) $data[ $keyword ]['ctr'],
				'difference' => (float) $data[ $keyword ]['diffCtr'],
			];

			unset(
				$data[ $keyword ]['diffClicks'],
				$data[ $keyword ]['diffImpressions'],
				$data[ $keyword ]['diffPosition'],
				$data[ $keyword ]['diffCtr']
			);
		}

		return $data;
	}

	/**
	 * Get tracked keywords.
	 *
	 * @param  array $args Array of arguments.
	 * @return array
	 */
	public function get_tracked_keywords( $args = [] ) {
		global $wpdb;

		$args = wp_parse_args(
			$args,
			[
				'dimension' => 'query',
				'order'     => 'ASC',
				'orderBy'   => 'diffPosition',
				'offset'    => 0,
				'perpage'   => 20000,
				'sub_where' => " AND query IN ( SELECT keyword from {$wpdb->prefix}rank_math_analytics_keyword_manager )",
			]
		);

		$data    = Stats::get()->get_analytics_data( $args );
		$history = $this->get_graph_data_for_keywords( \array_keys( $data ) );
		$data    = Stats::get()->set_query_position( $data, $history );

		// Add remaining keywords.
		if ( 5 !== $args['perpage'] ) {
			$rows = DB::keywords()->get();
			foreach ( $rows as $row ) {
				if ( ! isset( $data[ $row->keyword ] ) ) {
					$data[ $row->keyword ] = [
						'query'       => $row->keyword,
						'graph'       => [],
						'clicks'      => [
							'total'      => 0,
							'difference' => 0,
						],
						'impressions' => [
							'total'      => 0,
							'difference' => 0,
						],
						'position'    => [
							'total'      => 0,
							'difference' => 0,
						],
						'ctr'         => [
							'total'      => 0,
							'difference' => 0,
						],
						'pageviews'   => [
							'total'      => 0,
							'difference' => 0,
						],
					];
				}
			}
		}

		return $data;
	}

	/**
	 * Get most recent day's keywords.
	 *
	 * @return array
	 */
	public function get_recent_keywords() {
		global $wpdb;

		$query = $wpdb->prepare(
			"SELECT query
			FROM {$wpdb->prefix}rank_math_analytics_gsc
			WHERE DATE(created) = (SELECT MAX(DATE(created)) FROM {$wpdb->prefix}rank_math_analytics_gsc WHERE created BETWEEN %s AND %s)
			GROUP BY query",
			Stats::get()->start_date,
			Stats::get()->end_date
		);
		$data = $wpdb->get_results( $query ); // phpcs:ignore

		return $data;
	}

	/**
	 * Get top 5 winning keywords.
	 *
	 * @return array
	 */
	public function get_winning_keywords() {
		$cache_key = Stats::get()->get_cache_key( 'winning_keywords', Stats::get()->days . 'days' );
		$cache     = get_transient( $cache_key );

		if ( false !== $cache ) {
			return $cache;
		}

		// Get most recent day's keywords only.
		$keywords = $this->get_recent_keywords();
		$keywords = wp_list_pluck( $keywords, 'query' );
		$keywords = array_map( 'strtolower', $keywords );
		$data     = Stats::get()->get_analytics_data(
			[
				'order'     => 'ASC',
				'dimension' => 'query',
				'where'     => 'WHERE COALESCE( ROUND( t1.position - COALESCE( t2.position, 100 ), 0 ), 0 ) < 0',
			]
		);
		$history  = $this->get_graph_data_for_keywords( \array_keys( $data ) );
		$data     = Stats::get()->set_query_position( $data, $history );

		set_transient( $cache_key, $data, DAY_IN_SECONDS );

		return $data;
	}

	/**
	 * Get top 5 losing keywords.
	 *
	 * @return array
	 */
	public function get_losing_keywords() {
		$cache_key = Stats::get()->get_cache_key( 'losing_keywords', Stats::get()->days . 'days' );
		$cache     = get_transient( $cache_key );

		if ( false !== $cache ) {
			return $cache;
		}

		// Get most recent day's keywords only.
		$keywords = $this->get_recent_keywords();
		$keywords = wp_list_pluck( $keywords, 'query' );
		$keywords = array_map( 'strtolower', $keywords );

		$data    = Stats::get()->get_analytics_data(
			[
				'dimension' => 'query',
				'where'     => 'WHERE COALESCE( ROUND( t1.position - COALESCE( t2.position, 100 ), 0 ), 0 ) > 0',
			]
		);
		$history = $this->get_graph_data_for_keywords( \array_keys( $data ) );
		$data    = Stats::get()->set_query_position( $data, $history );

		set_transient( $cache_key, $data, DAY_IN_SECONDS );

		return $data;
	}

	/**
	 * Get keywords graph data.
	 *
	 * @param array  $keywords Keywords to get data for.
	 * @param string $sub_query Database sub-query.
	 *
	 * @return array
	 */
	public function get_graph_data_for_keywords( $keywords, $sub_query = '' ) {
		global $wpdb;

		$intervals     = Stats::get()->get_intervals();
		$sql_daterange = Stats::get()->get_sql_date_intervals( $intervals );
		$keywords      = \array_map( 'esc_sql', $keywords );
		$keywords      = '(\'' . join( '\', \'', $keywords ) . '\')';

		$query = $wpdb->prepare(
			"SELECT a.query, a.position, t.max_created AS date, t.range_group
			FROM {$wpdb->prefix}rank_math_analytics_gsc AS a
			INNER JOIN (
				SELECT
					query,
					{$sql_daterange},
					MAX(created) AS max_created
				FROM {$wpdb->prefix}rank_math_analytics_gsc
				WHERE created BETWEEN %s AND %s
				AND query IN {$keywords}
				{$sub_query}
				GROUP BY query, range_group
				ORDER BY max_created ASC
			) t
			ON a.query = t.query AND a.created = t.max_created
			ORDER BY a.created ASC",
			Stats::get()->start_date,
			Stats::get()->end_date
		);
		$data  = $wpdb->get_results( $query );
		$data  = Stats::get()->filter_graph_rows( $data );

		return array_map( [ Stats::get(), 'normalize_graph_rows' ], $data );
	}

	/**
	 * Get pages by keyword.
	 *
	 * @param  WP_REST_Request $request Full details about the request.
	 * @return WP_REST_Response|WP_Error Response object on success, or WP_Error object on failure.
	 */
	public function get_keyword_pages( WP_REST_Request $request ) {
		global $wpdb;

		$query = $wpdb->prepare(
			"SELECT DISTINCT g.page
			FROM {$wpdb->prefix}rank_math_analytics_gsc as g
			WHERE g.query = %s AND g.created BETWEEN %s AND %s
			ORDER BY g.created DESC
			LIMIT 5",
			$request->get_param( 'query' ),
			Stats::get()->start_date,
			Stats::get()->end_date
		);

		$data    = $wpdb->get_results( $query ); // phpcs:ignore
		$pages   = wp_list_pluck( $data, 'page' );
		$console = Stats::get()->get_analytics_data(
			[
				'objects'   => true,
				'pageview'  => true,
				'sub_where' => " AND page IN ('" . join( "', '", $pages ) . "')",
			]
		);
		return $console;

	}

	/**
	 * Add focus keywords to Rank Tracker.
	 *
	 * @param  int $post_id Post ID.
	 * @return mixed
	 */
	public function add_post_focus_keyword( $post_id ) {
		if ( wp_is_post_revision( $post_id ) ) {
			return;
		}

		$auto_add_fks = Helper::get_settings( 'general.auto_add_focus_keywords', [] );
		if (
			empty( $auto_add_fks['enable_auto_import'] ) ||
			empty( $auto_add_fks['post_types'] ) ||
			! in_array( get_post_type( $post_id ), $auto_add_fks['post_types'], true )
		) {
			return;
		}

		$focus_keyword = Helper::get_post_meta( 'focus_keyword', $post_id );
		if ( empty( $focus_keyword ) ) {
			return;
		}

		$keywords_data = [];
		$keywords      = explode( ',', $focus_keyword );
		if ( ! empty( $auto_add_fks['secondary_keyword'] ) ) {
			$keywords_data = $keywords;
		} else {
			$keywords_data[] = current( $keywords );
		}

		DB::bulk_insert_query_focus_keyword_data( $keywords_data );
	}
}