<?php
/**
 * GSC Index Store.
 *
 * Caches the result of Search Console URL Inspection per post — is Google
 * actually indexing this URL, and if not, why. Read-only diagnosis: the
 * "Request indexing" step still happens manually in the GSC UI (Google does
 * not officially support requesting indexing for articles via API).
 *
 * @package Platform\Helm\Modules\GSC
 */

declare( strict_types = 1 );

namespace Platform\Helm\Modules\GSC;

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

final class GscIndexStore {

	private const TABLE          = 'platform_helm_gsc_index';
	private const SCHEMA_OPTION  = 'platform_helm_gsc_index_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} (
			post_id bigint(20) unsigned NOT NULL,
			url varchar(255) NOT NULL DEFAULT '',
			verdict varchar(20) NOT NULL DEFAULT '',
			coverage varchar(120) NOT NULL DEFAULT '',
			indexing_state varchar(60) NOT NULL DEFAULT '',
			robots_state varchar(40) NOT NULL DEFAULT '',
			last_crawl datetime DEFAULT NULL,
			google_canonical varchar(255) NOT NULL DEFAULT '',
			user_canonical varchar(255) NOT NULL DEFAULT '',
			checked_at datetime DEFAULT NULL,
			PRIMARY KEY  (post_id),
			KEY verdict (verdict),
			KEY checked_at (checked_at)
		) {$charset};";

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

	/**
	 * @param array<string,mixed> $row
	 */
	public function upsert( int $post_id, array $row ): void {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$wpdb->replace(
			$this->table(),
			[
				'post_id'          => $post_id,
				'url'              => (string) ( $row['url'] ?? '' ),
				'verdict'          => (string) ( $row['verdict'] ?? '' ),
				'coverage'         => (string) ( $row['coverage'] ?? '' ),
				'indexing_state'   => (string) ( $row['indexing_state'] ?? '' ),
				'robots_state'     => (string) ( $row['robots_state'] ?? '' ),
				'last_crawl'       => $row['last_crawl'] ?? null,
				'google_canonical' => (string) ( $row['google_canonical'] ?? '' ),
				'user_canonical'   => (string) ( $row['user_canonical'] ?? '' ),
				'checked_at'       => current_time( 'mysql' ),
			],
			[ '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' ]
		);
	}

	/** @return array<int,int> post IDs already checked */
	public function checked_ids(): array {
		global $wpdb;
		$table = $this->table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		$ids = $wpdb->get_col( "SELECT post_id FROM {$table}" );
		return array_map( 'intval', (array) $ids );
	}

	/** Highest post ID checked so far — used as a forward cursor (0 if none). */
	public function max_post_id(): int {
		global $wpdb;
		$table = $this->table();
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		return (int) $wpdb->get_var( "SELECT MAX(post_id) FROM {$table}" );
	}

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

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

	/**
	 * Posts Google is NOT cleanly indexing (verdict != PASS), worst checked first.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function problems( int $limit = 500 ): array {
		return $this->rows( 'issues', $limit );
	}

	/**
	 * Rows by filter: 'issues' (verdict != PASS), 'pass', or 'all'.
	 *
	 * @return array<int,array<string,mixed>>
	 */
	public function rows( string $filter = 'issues', int $limit = 500 ): array {
		global $wpdb;
		$table = $this->table();
		$where = 'pass' === $filter ? "WHERE verdict = 'PASS'" : ( 'all' === $filter ? '' : "WHERE verdict <> 'PASS'" );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT * FROM {$table} {$where} ORDER BY checked_at DESC LIMIT %d",
			$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}" );
	}
}
