<?php
/**
 * GSC search-query sync (web) for Seasonal Radar YoY keyword forecasting.
 *
 * @package Newsroom_AI_Assistant
 */

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

if ( ! function_exists( 'newsai_gsc_query_table_name' ) ) {
	function newsai_gsc_query_table_name() {
		global $wpdb;
		return $wpdb->prefix . 'newsai_gsc_queries';
	}
}

if ( ! function_exists( 'newsai_gsc_query_table_exists' ) ) {
	function newsai_gsc_query_table_exists() {
		global $wpdb;
		$table = newsai_gsc_query_table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) === $table;
	}
}

if ( ! function_exists( 'newsai_gsc_query_sync_enabled' ) ) {
	function newsai_gsc_query_sync_enabled() {
		return get_option( 'newsai_gsc_query_sync_enabled', 'yes' ) === 'yes';
	}
}

if ( ! function_exists( 'newsai_gsc_create_query_table' ) ) {
	function newsai_gsc_create_query_table() {
		global $wpdb;
		$table   = newsai_gsc_query_table_name();
		$charset = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			query_hash CHAR(32) NOT NULL,
			query_text VARCHAR(512) NOT NULL,
			data_date DATE NOT NULL,
			search_type VARCHAR(16) NOT NULL DEFAULT 'web',
			clicks INT UNSIGNED NOT NULL DEFAULT 0,
			impressions INT UNSIGNED NOT NULL DEFAULT 0,
			position DECIMAL(8,2) NOT NULL DEFAULT 0,
			fetched_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY uq_query_date_type (query_hash, data_date, search_type),
			KEY idx_date (data_date),
			KEY idx_hash_date (query_hash, data_date)
		) {$charset};";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );
	}
}

if ( ! function_exists( 'newsai_gsc_normalize_query_text' ) ) {
	function newsai_gsc_normalize_query_text( $query ) {
		$query = trim( wp_strip_all_tags( (string) $query ) );
		if ( function_exists( 'mb_strlen' ) && mb_strlen( $query, 'UTF-8' ) > 500 ) {
			$query = mb_substr( $query, 0, 500, 'UTF-8' );
		} elseif ( strlen( $query ) > 500 ) {
			$query = substr( $query, 0, 500 );
		}
		return $query;
	}
}

if ( ! function_exists( 'newsai_gsc_query_hash' ) ) {
	function newsai_gsc_query_hash( $query_text ) {
		return md5( mb_strtolower( newsai_gsc_normalize_query_text( $query_text ), 'UTF-8' ) );
	}
}

if ( ! function_exists( 'newsai_gsc_get_max_query_data_date' ) ) {
	function newsai_gsc_get_max_query_data_date() {
		global $wpdb;
		if ( ! newsai_gsc_query_table_exists() ) {
			return '';
		}
		$table = newsai_gsc_query_table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$max = $wpdb->get_var( "SELECT MAX(data_date) FROM {$table} WHERE search_type = 'web'" );
		if ( ! is_string( $max ) || ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $max ) ) {
			return '';
		}
		return $max;
	}
}

if ( ! function_exists( 'newsai_gsc_should_run_full_query_sync' ) ) {
	function newsai_gsc_should_run_full_query_sync() {
		$last = (int) get_option( 'newsai_gsc_last_query_full_sync', 0 );
		return $last < 1 || ( time() - $last ) >= 7 * DAY_IN_SECONDS;
	}
}

if ( ! function_exists( 'newsai_gsc_compute_query_sync_window' ) ) {
	/**
	 * @param string $end_ymd Y-m-d.
	 * @return array{start:string,end:string,mode:string}
	 */
	function newsai_gsc_compute_query_sync_window( $end_ymd ) {
		$end_ymd = (string) $end_ymd;
		$end_ts  = strtotime( $end_ymd . ' UTC' );
		if ( $end_ts === false ) {
			$end_ts  = strtotime( '-3 days' );
			$end_ymd = gmdate( 'Y-m-d', $end_ts );
		}

		if ( newsai_gsc_should_run_full_query_sync() ) {
			return [
				'start' => gmdate( 'Y-m-d', strtotime( '-56 days', $end_ts ) ),
				'end'   => $end_ymd,
				'mode'  => 'full',
			];
		}

		$max_date = newsai_gsc_get_max_query_data_date();
		if ( $max_date === '' ) {
			return [
				'start' => gmdate( 'Y-m-d', strtotime( '-14 days', $end_ts ) ),
				'end'   => $end_ymd,
				'mode'  => 'initial',
			];
		}

		$start_ts = strtotime( $max_date . ' UTC -3 days' );
		$cap_ts   = strtotime( '-14 days', $end_ts );
		$mode     = 'incremental';
		if ( $start_ts < $cap_ts ) {
			$start_ts = $cap_ts;
			$mode     = 'catchup';
		}
		if ( $start_ts > $end_ts ) {
			$start_ts = strtotime( '-3 days', $end_ts );
		}

		return [
			'start' => gmdate( 'Y-m-d', $start_ts ),
			'end'   => $end_ymd,
			'mode'  => $mode,
		];
	}
}

if ( ! function_exists( 'newsai_gsc_upsert_query_row' ) ) {
	function newsai_gsc_upsert_query_row( $query_text, $date, $clicks, $impressions, $position ) {
		global $wpdb;
		$query_text = newsai_gsc_normalize_query_text( $query_text );
		if ( $query_text === '' || ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', (string) $date ) ) {
			return;
		}
		if ( ! newsai_gsc_query_table_exists() ) {
			newsai_gsc_create_query_table();
		}
		$hash  = newsai_gsc_query_hash( $query_text );
		$table = newsai_gsc_query_table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO {$table}
					(query_hash, query_text, data_date, search_type, clicks, impressions, position, fetched_at)
				 VALUES (%s, %s, %s, 'web', %d, %d, %f, %s)
				 ON DUPLICATE KEY UPDATE
					query_text = VALUES(query_text),
					clicks = VALUES(clicks),
					impressions = VALUES(impressions),
					position = VALUES(position),
					fetched_at = VALUES(fetched_at)",
				$hash,
				$query_text,
				$date,
				(int) $clicks,
				(int) $impressions,
				(float) $position,
				current_time( 'mysql', true )
			)
		);
	}
}

if ( ! function_exists( 'newsai_gsc_prune_query_rows' ) ) {
	function newsai_gsc_prune_query_rows() {
		global $wpdb;
		if ( ! newsai_gsc_query_table_exists() ) {
			return;
		}
		$table  = newsai_gsc_query_table_name();
		$cutoff = function_exists( 'newsai_gsc_prune_cutoff_date' )
			? newsai_gsc_prune_cutoff_date()
			: gmdate( 'Y-m-d', strtotime( '-395 days' ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( $wpdb->prepare( "DELETE FROM {$table} WHERE data_date < %s", $cutoff ) );
	}
}

if ( ! function_exists( 'newsai_gsc_sync_query_metrics' ) ) {
	/**
	 * Pull web search queries (query + date) for Seasonal Radar.
	 *
	 * @return array{rows:int,mode:string,errors:list<string>}
	 */
	function newsai_gsc_sync_query_metrics( $token, $property, $start, $end ) {
		$out = [
			'rows'   => 0,
			'mode'   => '',
			'errors' => [],
		];
		if ( ! newsai_gsc_query_sync_enabled() ) {
			return $out;
		}
		if ( ! function_exists( 'newsai_gsc_fetch_analytics_rows' ) ) {
			return $out;
		}

		if ( ! newsai_gsc_query_table_exists() ) {
			newsai_gsc_create_query_table();
		}

		$window = newsai_gsc_compute_query_sync_window( $end );
		$start  = $window['start'];
		$end    = $window['end'];
		$out['mode'] = (string) $window['mode'];

		$rows = newsai_gsc_fetch_analytics_rows( $token, $property, $start, $end, 'web', [ 'query', 'date' ], 2500, 60 );
		if ( is_wp_error( $rows ) ) {
			$out['errors'][] = $rows->get_error_message();
			return $out;
		}

		$max_rows = (int) apply_filters( 'newsai_gsc_query_sync_max_rows', 12000 );
		if ( $max_rows < 1000 ) {
			$max_rows = 1000;
		}

		foreach ( (array) $rows as $row ) {
			if ( $out['rows'] >= $max_rows ) {
				break;
			}
			$query = isset( $row['keys'][0] ) ? (string) $row['keys'][0] : '';
			$date  = isset( $row['keys'][1] ) ? (string) $row['keys'][1] : '';
			if ( $query === '' || $date === '' ) {
				continue;
			}
			$clicks = (int) ( $row['clicks'] ?? 0 );
			$impr   = (int) ( $row['impressions'] ?? 0 );
			if ( $clicks < 1 && $impr < 5 ) {
				continue;
			}
			newsai_gsc_upsert_query_row(
				$query,
				$date,
				$clicks,
				$impr,
				(float) ( $row['position'] ?? 0 )
			);
			++$out['rows'];
		}

		if ( $out['rows'] > 0 && $window['mode'] === 'full' ) {
			update_option( 'newsai_gsc_last_query_full_sync', time(), false );
		}
		newsai_gsc_prune_query_rows();

		return $out;
	}
}

if ( ! function_exists( 'newsai_gsc_query_row_count' ) ) {
	function newsai_gsc_query_row_count() {
		if ( ! newsai_gsc_query_table_exists() ) {
			return 0;
		}
		global $wpdb;
		$table = newsai_gsc_query_table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return (int) $wpdb->get_var( "SELECT COUNT(1) FROM {$table}" );
	}
}

if ( ! function_exists( 'newsai_gsc_query_data_available' ) ) {
	function newsai_gsc_query_data_available() {
		if ( ! newsai_gsc_query_table_exists() ) {
			return false;
		}
		global $wpdb;
		$table = newsai_gsc_query_table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		return (int) $wpdb->get_var( "SELECT COUNT(1) FROM {$table} LIMIT 1" ) > 0;
	}
}

if ( ! function_exists( 'newsai_seasonal_yoy_gsc_queries' ) ) {
	/**
	 * Top YoY GSC search queries for a calendar window.
	 *
	 * @return list<array{query_text:string,yoy_clicks:int,yoy_impr:int}>
	 */
	function newsai_seasonal_yoy_gsc_queries( $yoy_start, $yoy_end, $min_clicks ) {
		if ( ! newsai_gsc_query_data_available() ) {
			return [];
		}
		global $wpdb;
		$table = newsai_gsc_query_table_name();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT query_text, SUM(clicks) AS yoy_clicks, SUM(impressions) AS yoy_impr
				FROM {$table}
				WHERE search_type = 'web' AND data_date >= %s AND data_date <= %s
				GROUP BY query_hash, query_text
				HAVING yoy_clicks >= %d
				ORDER BY yoy_clicks DESC
				LIMIT 25",
				$yoy_start,
				$yoy_end,
				(int) $min_clicks
			),
			ARRAY_A
		);
		return is_array( $rows ) ? $rows : [];
	}
}
