<?php
/**
 * SEO Analysis Repository Class
 *
 * @package Wisewand
 * @subpackage Wisewand/includes/Repositories
 *
 * phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- False positive from Plugin Checker
 * All queries use $wpdb->prepare() with proper placeholders. The checker incorrectly flags
 * the prepare() method calls themselves. Known PHPCS limitation.
 *
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Repository pattern requirement
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table, no object cache
 */

namespace Wisewand\Repositories;

/**
 * Class SEO_Analysis_Repository
 *
 * Handles database interactions for SEO analysis data (rankings, opportunities).
 */
class SEO_Analysis_Repository extends Base_Repository {

	/**
	 * Initialize the repository.
	 */
	public function __construct() {
		parent::__construct( 'wisewand_seo_keywords_v2' );
	}

	/**
	 * Store analysis results (rankings and opportunities).
	 *
	 * @param array $results Analysis results.
	 * @return bool Success.
	 */
	public function store_analysis_results( $results ) {
		$domain        = $results['domain'];
		$language_code = $results['language_code'];
		$location_code = $results['location_code'];
		$snapshot_id   = 'snap_' . gmdate( 'Ymd_His' );

		$this->start_transaction();

		try {
			// Store current rankings
			if ( ! empty( $results['current_rankings'] ) ) {
				foreach ( $results['current_rankings'] as $ranking ) {
					$data = array(
						'snapshot_id'    => $snapshot_id,
						'target_domain'  => $domain,
						'language_code'  => $language_code,
						'location_code'  => $location_code,
						'keyword'        => $ranking['keyword'],
						'keyword_type'   => $ranking['type'],
						'position_abs'   => $ranking['position'],
						'page_url'       => $ranking['page_url'] ?? '',
						'search_volume'  => $ranking['search_volume'] ?? 0,
						'search_intent'  => $ranking['search_intent'] ?? 'informational',
						'content_type'   => $ranking['content_type'] ?? 'blog',
						'priority_score' => $ranking['priority'] ?? 0,
						'raw_data'       => json_encode( $ranking ),
					);

					// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
					$this->wpdb->insert( $this->table_name, $data );
				}
			}

			// Store opportunities
			if ( ! empty( $results['opportunities'] ) ) {
				foreach ( $results['opportunities'] as $opportunity ) {
					$data = array(
						'snapshot_id'    => $snapshot_id,
						'target_domain'  => $domain,
						'language_code'  => $language_code,
						'location_code'  => $location_code,
						'keyword'        => $opportunity['keyword'],
						'keyword_type'   => $opportunity['type'],
						'position_abs'   => $opportunity['position'],
						'page_url'       => $opportunity['page_url'] ?? '',
						'search_volume'  => $opportunity['search_volume'] ?? 0,
						'search_intent'  => $opportunity['search_intent'] ?? 'informational',
						'content_type'   => $opportunity['content_type'] ?? 'blog',
						'priority_score' => $opportunity['priority'] ?? 0,
						'raw_data'       => json_encode( $opportunity ),
					);

					// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
					$this->wpdb->insert( $this->table_name, $data );
				}
			}

			// Clean up old snapshots (keep last 30 days)
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$this->wpdb->query(
				$this->wpdb->prepare(
					"DELETE FROM %i WHERE target_domain = %s AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)",
					$this->table_name,
					$domain
				)
			);

			$this->commit();
			return true;

		} catch ( \Exception $e ) {
			$this->rollback();
			return false;
		}
	}

	/**
	 * Get latest snapshot ID.
	 *
	 * @param string $domain        Domain.
	 * @param string $language_code Language code.
	 * @param int    $location_code Location code.
	 * @return string|null Snapshot ID.
	 */
	public function get_latest_snapshot_id( $domain, $language_code, $location_code ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT snapshot_id FROM %i
                WHERE target_domain = %s AND language_code = %s AND location_code = %d
                ORDER BY created_at DESC LIMIT 1",
				$this->table_name,
				$domain,
				$language_code,
				$location_code
			)
		);
	}

	/**
	 * Get keywords by snapshot ID.
	 *
	 * @param string $snapshot_id Snapshot ID.
	 * @return array Keywords.
	 */
	public function get_keywords_by_snapshot( $snapshot_id ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM %i WHERE snapshot_id = %s ORDER BY priority_score DESC",
				$this->table_name,
				$snapshot_id
			),
			ARRAY_A
		);
	}

	/**
	 * Get previous snapshot ID.
	 *
	 * @param string $domain        Domain.
	 * @param string $language_code Language code.
	 * @param int    $location_code Location code.
	 * @param string $current_snapshot_id Current snapshot ID.
	 * @return string|null Previous snapshot ID.
	 */
	public function get_previous_snapshot_id( $domain, $language_code, $location_code, $current_snapshot_id ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT snapshot_id FROM %i
                WHERE target_domain = %s AND language_code = %s AND location_code = %d
                AND snapshot_id < %s
                ORDER BY created_at DESC LIMIT 1",
				$this->table_name,
				$domain,
				$language_code,
				$location_code,
				$current_snapshot_id
			)
		);
	}

	/**
	 * Get snapshot date.
	 *
	 * @param string $snapshot_id Snapshot ID.
	 * @return string|null Date.
	 */
	public function get_snapshot_date( $snapshot_id ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT created_at FROM %i WHERE snapshot_id = %s LIMIT 1",
				$this->table_name,
				$snapshot_id
			)
		);
	}

	/**
	 * Ensure database schema is up to date.
	 */
	public function ensure_schema() {
		$charset_collate = $this->wpdb->get_charset_collate();

		// Check if table exists
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$table_exists = $this->wpdb->get_var( $this->wpdb->prepare( 'SHOW TABLES LIKE %s', $this->table_name ) ) === $this->table_name;

		if ( ! $table_exists ) {
			$sql = "CREATE TABLE {$this->table_name} (
				id bigint(20) NOT NULL AUTO_INCREMENT,
				snapshot_id varchar(50) NOT NULL,
				target_domain varchar(255) NOT NULL,
				language_code varchar(10) DEFAULT 'en',
				location_code int(11) DEFAULT 2250,
				keyword varchar(255) NOT NULL,
				keyword_type varchar(50) NOT NULL,
				position_abs int(11) DEFAULT 0,
				page_url text,
				search_volume int(11) DEFAULT 0,
				search_intent varchar(50) DEFAULT 'informational',
				content_type varchar(50) DEFAULT 'blog',
				priority_score int(11) DEFAULT 0,
				raw_data longtext,
				created_at datetime DEFAULT CURRENT_TIMESTAMP,
				PRIMARY KEY  (id),
				KEY snapshot_id (snapshot_id),
				KEY target_domain (target_domain),
				KEY keyword (keyword)
			) $charset_collate;";

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

	/**
	 * Get latest ranking data for a keyword.
	 *
	 * @param string $keyword        Keyword text.
	 * @param string $language_code  Language code.
	 * @param int    $location_code  Location code.
	 * @return object|null Ranking data with page_url and position_abs, or null.
	 */
	public function get_latest_keyword_ranking( $keyword, $language_code, $location_code ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT page_url, position_abs
				 FROM %i
				 WHERE keyword = %s
				 AND language_code = %s
				 AND location_code = %d
				 AND page_url IS NOT NULL
				 AND page_url != ''
				 ORDER BY created_at DESC
				 LIMIT 1",
				$this->table_name,
				$keyword,
				$language_code,
				$location_code
			)
		);
	}
}
