<?php
/**
 * GSC Snapshot Store.
 *
 * Records a daily time-series row so you can see whether the SEO work is
 * actually recovering traffic (the audit's "check after 60-90 days"). One
 * site-wide row per day, plus one row per watch-listed post per day. History
 * only accrues from the day monitoring is switched on — that's expected.
 *
 * @package Platform\Helm\Modules\GSC
 */

declare( strict_types = 1 );

namespace Platform\Helm\Modules\GSC;

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

final class GscSnapshotStore {

	private const TABLE          = 'platform_helm_gsc_snapshots';
	private const SCHEMA_OPTION  = 'platform_helm_gsc_snap_schema_version';
	private const SCHEMA_VERSION = '1.0.0';
	public  const WATCHLIST_OPTION = 'platform_helm_gsc_watchlist';

	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,
			snap_date date NOT NULL,
			post_id bigint(20) unsigned NOT NULL DEFAULT 0,
			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,
			PRIMARY KEY  (id),
			UNIQUE KEY snap (snap_date, post_id),
			KEY post_id (post_id)
		) {$charset};";

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

	/**
	 * Record one snapshot row for today (idempotent per day via REPLACE).
	 */
	public function record( int $post_id, int $clicks, int $impressions, float $ctr, float $position, ?string $date = null ): void {
		global $wpdb;
		$table = $this->table();
		$date  = $date ?: current_time( 'Y-m-d' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$wpdb->replace(
			$table,
			[
				'snap_date'   => $date,
				'post_id'     => $post_id,
				'clicks'      => $clicks,
				'impressions' => $impressions,
				'ctr'         => $ctr,
				'position'    => $position,
			],
			[ '%s', '%d', '%d', '%d', '%f', '%f' ]
		);
	}

	/**
	 * Snapshot history for a post (0 = site-wide), oldest first.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function history( int $post_id = 0, int $limit = 180 ): array {
		global $wpdb;
		$table = $this->table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT snap_date,clicks,impressions,ctr,position FROM {$table} WHERE post_id = %d ORDER BY snap_date DESC LIMIT %d",
			$post_id,
			$limit
		), ARRAY_A );
		$rows = is_array( $rows ) ? array_reverse( $rows ) : [];
		return $rows;
	}

	/** @return array<int,int> */
	public function watchlist(): array {
		$v = get_option( self::WATCHLIST_OPTION, [] );
		return is_array( $v ) ? array_values( array_unique( array_filter( array_map( 'intval', $v ) ) ) ) : [];
	}

	public function add_to_watchlist( int $post_id ): void {
		$list = $this->watchlist();
		if ( $post_id > 0 && ! in_array( $post_id, $list, true ) ) {
			$list[] = $post_id;
			update_option( self::WATCHLIST_OPTION, $list, false );
		}
	}

	public function remove_from_watchlist( int $post_id ): void {
		$list = array_values( array_diff( $this->watchlist(), [ $post_id ] ) );
		update_option( self::WATCHLIST_OPTION, $list, false );
	}
}
