<?php
/**
 * GSC Query Store.
 *
 * Caches the page×query layer of Search Console data — one row per
 * (post, query) — which powers Refresh Intelligence (what each post ranks
 * for) and the Cannibalization Detector (which queries have multiple of
 * your URLs competing). Rows below a minimum impression floor are dropped
 * to keep the table lean and meaningful.
 *
 * @package Platform\Helm\Modules\GSC
 */

declare( strict_types = 1 );

namespace Platform\Helm\Modules\GSC;

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

final class GscQueryStore {

	private const TABLE          = 'platform_helm_gsc_queries';
	private const SCHEMA_OPTION  = 'platform_helm_gsc_q_schema_version';
	private const SCHEMA_VERSION = '1.0.0';

	public function table(): string {
		global $wpdb;
		return $wpdb->prefix . self::TABLE;
	}

	public function maybe_create_table(): void {
		if ( self::SCHEMA_VERSION === (string) get_option( self::SCHEMA_OPTION, '' ) ) {
			return;
		}
		global $wpdb;
		$table   = $this->table();
		$charset = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			post_id bigint(20) unsigned NOT NULL DEFAULT 0,
			url varchar(255) NOT NULL DEFAULT '',
			query varchar(191) NOT NULL DEFAULT '',
			clicks int(10) unsigned NOT NULL DEFAULT 0,
			impressions int(10) unsigned NOT NULL DEFAULT 0,
			ctr float NOT NULL DEFAULT 0,
			position float NOT NULL DEFAULT 0,
			updated_at datetime DEFAULT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY post_query (post_id, query),
			KEY query (query),
			KEY post_id (post_id),
			KEY impressions (impressions)
		) {$charset};";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );
		update_option( self::SCHEMA_OPTION, self::SCHEMA_VERSION, false );
	}

	/**
	 * @param array<int,array{post_id:int,url:string,query:string,clicks:int,impressions:int,ctr:float,position:float}> $rows
	 */
	public function upsert( array $rows ): int {
		if ( empty( $rows ) ) {
			return 0;
		}
		global $wpdb;
		$table = $this->table();
		$now   = current_time( 'mysql' );
		$total = 0;

		foreach ( array_chunk( $rows, 200 ) as $chunk ) {
			$ph     = [];
			$values = [];
			foreach ( $chunk as $r ) {
				$ph[]     = '(%d,%s,%s,%d,%d,%f,%f,%s)';
				$values[] = (int) $r['post_id'];
				$values[] = (string) $r['url'];
				$values[] = mb_substr( (string) $r['query'], 0, 191 );
				$values[] = (int) $r['clicks'];
				$values[] = (int) $r['impressions'];
				$values[] = (float) $r['ctr'];
				$values[] = (float) $r['position'];
				$values[] = $now;
			}
			$sql = "INSERT INTO {$table} (post_id,url,query,clicks,impressions,ctr,position,updated_at) VALUES "
				. implode( ',', $ph )
				. " ON DUPLICATE KEY UPDATE url=VALUES(url),clicks=VALUES(clicks),impressions=VALUES(impressions),ctr=VALUES(ctr),position=VALUES(position),updated_at=VALUES(updated_at)";
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
			$wpdb->query( $wpdb->prepare( $sql, $values ) );
			$total += count( $chunk );
		}
		return $total;
	}

	/**
	 * Top queries for a set of posts (for Refresh Intelligence), grouped by post.
	 *
	 * @param array<int,int> $post_ids
	 * @return array<int,array<int,array<string,mixed>>> post_id => rows (impr desc)
	 */
	public function top_queries_for_posts( array $post_ids, int $per_post = 3 ): array {
		$post_ids = array_values( array_unique( array_filter( array_map( 'intval', $post_ids ) ) ) );
		if ( empty( $post_ids ) ) {
			return [];
		}
		global $wpdb;
		$table = $this->table();
		$ph    = implode( ',', array_fill( 0, count( $post_ids ), '%d' ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT post_id,query,clicks,impressions,ctr,position FROM {$table} WHERE post_id IN ({$ph}) ORDER BY post_id ASC, impressions DESC",
			$post_ids
		), ARRAY_A );

		$out = [];
		foreach ( (array) $rows as $r ) {
			$pid = (int) $r['post_id'];
			if ( ! isset( $out[ $pid ] ) ) {
				$out[ $pid ] = [];
			}
			if ( count( $out[ $pid ] ) < $per_post ) {
				$out[ $pid ][] = $r;
			}
		}
		return $out;
	}

	/**
	 * Queries with 2+ competing URLs (cannibalization), worst first.
	 *
	 * @return array<int,array{query:string,pages:int,impressions:int,clicks:int}>
	 */
	public function cannibalized_queries( int $min_pages = 2, int $min_impressions = 50, int $limit = 100 ): array {
		global $wpdb;
		$table = $this->table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT query, COUNT(DISTINCT post_id) AS pages, SUM(impressions) AS impressions, SUM(clicks) AS clicks
			 FROM {$table}
			 GROUP BY query
			 HAVING pages >= %d AND impressions >= %d
			 ORDER BY impressions DESC
			 LIMIT %d",
			$min_pages,
			$min_impressions,
			$limit
		), ARRAY_A );

		$out = [];
		foreach ( (array) $rows as $r ) {
			$out[] = [
				'query'       => (string) $r['query'],
				'pages'       => (int) $r['pages'],
				'impressions' => (int) $r['impressions'],
				'clicks'      => (int) $r['clicks'],
			];
		}
		return $out;
	}

	/**
	 * The competing pages for one query (best position first).
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function pages_for_query( string $query ): array {
		global $wpdb;
		$table = $this->table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT post_id,url,clicks,impressions,ctr,position FROM {$table} WHERE query = %s ORDER BY position ASC",
			$query
		), ARRAY_A );
		return is_array( $rows ) ? $rows : [];
	}

	public function count_rows(): int {
		global $wpdb;
		$table = $this->table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" );
	}

	/**
	 * Opportunity queries: meaningful impressions but the best-ranking page is
	 * still weak (high position) — content gaps worth creating or expanding.
	 *
	 * @return array<int,array{query:string,impressions:int,clicks:int,best_position:float}>
	 */
	public function opportunity_queries( int $min_impressions = 100, float $min_position = 8.0, int $limit = 40 ): array {
		global $wpdb;
		$table = $this->table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT query, SUM(impressions) AS impressions, SUM(clicks) AS clicks, MIN(position) AS best_position
			 FROM {$table}
			 GROUP BY query
			 HAVING impressions >= %d AND best_position >= %f
			 ORDER BY impressions DESC
			 LIMIT %d",
			$min_impressions,
			$min_position,
			$limit
		), ARRAY_A );
		return is_array( $rows ) ? $rows : [];
	}

	public function clear(): void {
		global $wpdb;
		$table = $this->table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		$wpdb->query( "TRUNCATE TABLE {$table}" );
	}
}
