<?php
/**
 * GSC Store.
 *
 * Owns a small custom table that caches per-post Search Console metrics so
 * the Content Auditor can read them with zero live API calls. Keyed by
 * post_id (one row per matched post). Index-coverage columns are reserved
 * for the URL Inspection pass that the Auditor will drive later.
 *
 * @package Platform\Helm\Modules\GSC
 */

declare( strict_types = 1 );

namespace Platform\Helm\Modules\GSC;

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

final class GscStore {

	private const TABLE          = 'platform_helm_gsc';
	private const SCHEMA_OPTION  = 'platform_helm_gsc_schema_version';
	private const SCHEMA_VERSION = '1.0.0';

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

	/** Create/upgrade the table if needed. Cheap no-op once current. */
	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 '',
			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,
			index_status varchar(40) NOT NULL DEFAULT '',
			index_detail varchar(190) NOT NULL DEFAULT '',
			last_inspected datetime DEFAULT NULL,
			updated_at datetime DEFAULT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY post_id (post_id),
			KEY clicks (clicks)
		) {$charset};";

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

	/**
	 * Bulk upsert Search Analytics metrics, keyed by post_id.
	 *
	 * @param array<int,array{post_id:int,url:string,clicks:int,impressions:int,ctr:float,position:float}> $rows
	 * @return int Number of rows written.
	 */
	public function upsert_metrics( 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 ) {
			$placeholders = [];
			$values       = [];
			foreach ( $chunk as $r ) {
				$placeholders[] = '(%d,%s,%d,%d,%f,%f,%s)';
				$values[]       = (int) $r['post_id'];
				$values[]       = (string) $r['url'];
				$values[]       = (int) round( (float) $r['clicks'] );
				$values[]       = (int) round( (float) $r['impressions'] );
				$values[]       = (float) $r['ctr'];
				$values[]       = (float) $r['position'];
				$values[]       = $now;
			}
			$sql = "INSERT INTO {$table} (post_id,url,clicks,impressions,ctr,position,updated_at) VALUES "
				. implode( ',', $placeholders )
				. " 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;
	}

	/**
	 * Metrics for one post, or null if we have none.
	 *
	 * @return array<string,mixed>|null
	 */
	/**
	 * Metrics for many posts at once (one query), keyed by post_id.
	 *
	 * @param array<int,int> $post_ids
	 * @return array<int,array<string,mixed>>
	 */
	public function get_for_posts( array $post_ids ): array {
		$post_ids = array_values( array_unique( array_filter( array_map( 'intval', $post_ids ) ) ) );
		if ( empty( $post_ids ) ) {
			return [];
		}
		global $wpdb;
		$table        = $this->table();
		$placeholders = 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,clicks,impressions,ctr,position FROM {$table} WHERE post_id IN ({$placeholders})", $post_ids ), ARRAY_A );
		$out = [];
		foreach ( (array) $rows as $r ) {
			$out[ (int) $r['post_id'] ] = $r;
		}
		return $out;
	}

	public function get_for_post( int $post_id ): ?array {
		global $wpdb;
		$table = $this->table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE post_id = %d", $post_id ), ARRAY_A );
		return is_array( $row ) ? $row : null;
	}

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

	/**
	 * Top pages by clicks, for the settings-page sample.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function top_pages( int $limit = 10 ): 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} ORDER BY clicks DESC, impressions DESC LIMIT %d",
			$limit
		), ARRAY_A );
		return is_array( $rows ) ? $rows : [];
	}

	/**
	 * High-impression, low-CTR pages — the intent-mismatch / off-brand signal.
	 * Worst (most wasted impressions) first.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function low_ctr_pages( int $min_impressions = 20000, float $max_ctr = 0.005, 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 post_id,url,clicks,impressions,ctr,position FROM {$table} WHERE impressions >= %d AND ctr <= %f ORDER BY impressions DESC LIMIT %d",
			$min_impressions,
			$max_ctr,
			$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}" );
	}
}
