<?php
/**
 * Keyword 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 Keyword_Repository
 *
 * Handles database interactions for Saved Keywords and Clusters.
 */
class Keyword_Repository extends Base_Repository {

	/**
	 * Clusters table name.
	 *
	 * @var string
	 */
	protected $clusters_table;

	/**
	 * Initialize the repository.
	 */
	public function __construct() {
		parent::__construct( 'wisewand_saved_keywords' );
		$this->clusters_table = $this->wpdb->prefix . 'wisewand_keyword_clusters';
	}

	/**
	 * Get a keyword by ID.
	 *
	 * @param int $id Keyword ID.
	 * @return object|null Row object if exists, null otherwise.
	 */
	public function get_keyword( $id ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table query required.
		return $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT * FROM %i WHERE id = %d",
				$this->table_name,
				$id
			)
		);
	}

	/**
	 * Check if keyword exists in database.
	 *
	 * @param string $keyword       Keyword to check.
	 * @param string $language_code Language code.
	 * @return bool True if exists.
	 */
	public function keyword_exists( $keyword, $language_code = null ) {
		if ( $language_code !== null ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$exists = $this->wpdb->get_var(
				$this->wpdb->prepare(
					"SELECT id FROM %i WHERE keyword = %s AND language_code = %s",
					$this->table_name,
					$keyword,
					$language_code
				)
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$exists = $this->wpdb->get_var(
				$this->wpdb->prepare(
					"SELECT id FROM %i WHERE keyword = %s",
					$this->table_name,
					$keyword
				)
			);
		}

		return ! empty( $exists );
	}

	/**
	 * Get keyword ID from database.
	 *
	 * @param string $keyword       Keyword.
	 * @param string $language_code Language code.
	 * @return int|null Keyword ID or null.
	 */
	public function get_keyword_id( $keyword, $language_code = null ) {
		if ( $language_code !== null ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$id = $this->wpdb->get_var(
				$this->wpdb->prepare(
					"SELECT id FROM %i WHERE keyword = %s AND language_code = %s",
					$this->table_name,
					$keyword,
					$language_code
				)
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$id = $this->wpdb->get_var(
				$this->wpdb->prepare(
					"SELECT id FROM %i WHERE keyword = %s",
					$this->table_name,
					$keyword
				)
			);
		}

		return $id ? (int) $id : null;
	}

	/**
	 * Save (Insert or Update) a keyword.
	 *
	 * @param array $data Column data.
	 * @return int|false Insert ID on success, false on failure.
	 */
	public function save_keyword( $data ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $this->wpdb->insert(
			$this->table_name,
			$data
		);

		if ( $result === false ) {
			return false;
		}
		return $this->wpdb->insert_id;
	}

	/**
	 * Update a keyword.
	 *
	 * @param int   $keyword_id Keyword ID.
	 * @param array $data       Data to update.
	 * @return bool Success.
	 */
	public function update_keyword( $keyword_id, $data ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $this->wpdb->update(
			$this->table_name,
			$data,
			array( 'id' => $keyword_id )
		);

		return $result !== false;
	}

	/**
	 * Delete a keyword by ID.
	 *
	 * @param int $id Keyword ID.
	 * @return int|false Number of rows affected.
	 */
	public function delete_keyword( $id ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table query required.
		return $this->wpdb->delete(
			$this->table_name,
			array( 'id' => $id ),
			array( '%d' )
		);
	}

	/**
	 * Get all saved keywords.
	 *
	 * @return array List of keywords.
	 */
	public function get_all_keywords() {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->get_results(
			$this->wpdb->prepare( "SELECT * FROM %i ORDER BY priority_score DESC, created_at DESC", $this->table_name ),
			ARRAY_A
		);
	}

	/**
	 * Bulk delete keywords.
	 *
	 * @param array $keyword_ids List of IDs.
	 * @return int|false Number of rows deleted.
	 */
	public function bulk_delete( $keyword_ids ) {
		$ids_placeholder = implode( ',', array_fill( 0, count( $keyword_ids ), '%d' ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->query(
			$this->wpdb->prepare(
				"DELETE FROM %i WHERE id IN ($ids_placeholder)",
				array_merge( array( $this->table_name ), $keyword_ids )
			)
		);
	}

	/**
	 * Get keyword statistics.
	 *
	 * @return array Statistics.
	 */
	public function get_statistics() {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return array(
			'total'        => $this->wpdb->get_var( $this->wpdb->prepare( "SELECT COUNT(*) FROM %i", $this->table_name ) ),
			'enriched'     => $this->wpdb->get_var( $this->wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE is_enriched = 1", $this->table_name ) ),
			'not_enriched' => $this->wpdb->get_var( $this->wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE is_enriched = 0", $this->table_name ) ),
			'by_source'    => array(
				'manual'        => $this->wpdb->get_var( $this->wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE keyword_source = 'manual'", $this->table_name ) ),
				'keyword_ideas' => $this->wpdb->get_var( $this->wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE keyword_source = 'keyword_ideas'", $this->table_name ) ),
			),
		);
	}

	/**
	 * Get cluster by hash.
	 *
	 * @param string $hash Cluster hash.
	 * @return object|null
	 */
	public function get_cluster_by_hash( $hash ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table query required.
		return $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT * FROM %i WHERE cluster_hash = %s",
				$this->clusters_table,
				$hash
			),
			ARRAY_A
		);
	}

	/**
	 * Create a cluster.
	 *
	 * @param array $data Cluster data.
	 * @return int|false Insert ID.
	 */
	public function create_cluster( $data ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $this->wpdb->insert(
			$this->clusters_table,
			$data
		);

		if ( $result === false ) {
			return false;
		}
		return $this->wpdb->insert_id;
	}

	/**
	 * Ensure database schema is up to date.
	 */
	public function ensure_schema() {
		$schema_version = get_option( 'wisewand_saved_keywords_schema_version', '1.0' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$columns = $this->wpdb->get_col( "SHOW COLUMNS FROM {$this->table_name}" );

		// Version 1.1: Add language, location, competitor_spy enum
		if ( version_compare( $schema_version, '1.1', '<' ) ) {
			// Update enum to include competitor_spy
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
			$this->wpdb->query(
				"ALTER TABLE {$this->table_name}
                MODIFY COLUMN keyword_source enum('manual','keyword_ideas','competitor_spy') DEFAULT 'manual'"
			);

			// Add language_code if missing
			if ( ! in_array( 'language_code', $columns ) ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
				$this->wpdb->query( "ALTER TABLE {$this->table_name} ADD COLUMN language_code varchar(10) DEFAULT 'en' AFTER seed_keyword" );
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
				$this->wpdb->query( "ALTER TABLE {$this->table_name} ADD INDEX language_code (language_code)" );
			}

			// Add location_code if missing
			if ( ! in_array( 'location_code', $columns ) ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
				$this->wpdb->query( "ALTER TABLE {$this->table_name} ADD COLUMN location_code int(10) DEFAULT 2840 AFTER language_code" );
			}

			update_option( 'wisewand_saved_keywords_schema_version', '1.1' );
		}

		// Version 1.2: Add CPC and Competition columns
		if ( version_compare( $schema_version, '1.2', '<' ) ) {
			// Refresh columns list
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$columns = $this->wpdb->get_col( "SHOW COLUMNS FROM {$this->table_name}" );

			// Add cpc column
			if ( ! in_array( 'cpc', $columns ) ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
				$this->wpdb->query( "ALTER TABLE {$this->table_name} ADD COLUMN cpc decimal(10,2) DEFAULT NULL AFTER difficulty" );
			}

			// Add competition column
			if ( ! in_array( 'competition', $columns ) ) {
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
				$this->wpdb->query( "ALTER TABLE {$this->table_name} ADD COLUMN competition varchar(20) DEFAULT NULL AFTER cpc" );
			}

			update_option( 'wisewand_saved_keywords_schema_version', '1.2' );
		}
	}
	/**
	 * Assign cluster to keyword.
	 *
	 * @param int $keyword_id Keyword ID.
	 * @param int $cluster_id Cluster ID.
	 * @return bool Success.
	 */
	public function assign_cluster( $keyword_id, $cluster_id ) {
		return $this->update_keyword( $keyword_id, array( 'cluster_id' => $cluster_id ) );
	}

	/**
	 * Bulk assign cluster.
	 *
	 * @param array $keyword_ids List of keyword IDs.
	 * @param int   $cluster_id  Cluster ID.
	 * @return int|false Number of rows updated.
	 */
	public function bulk_assign_cluster( $keyword_ids, $cluster_id ) {
		$ids_placeholder = implode( ',', array_fill( 0, count( $keyword_ids ), '%d' ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->query(
			$this->wpdb->prepare(
				"UPDATE %i SET cluster_id = %d WHERE id IN ($ids_placeholder)",
				array_merge( array( $this->table_name, $cluster_id ), $keyword_ids )
			)
		);
	}

	/**
	 * Get cluster statistics.
	 *
	 * @param int $cluster_id Cluster ID.
	 * @return array Statistics.
	 */
	public function get_cluster_stats( $cluster_id ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$total = $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COUNT(*) FROM %i WHERE cluster_id = %d",
				$this->table_name,
				$cluster_id
			)
		);

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$auto_matchable = $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT COUNT(*) FROM %i WHERE cluster_id = %d AND auto_categorized_from_url = 1",
				$this->table_name,
				$cluster_id
			)
		);

		return array(
			'total'          => (int) $total,
			'auto_matchable' => (int) $auto_matchable,
		);
	}

	/**
	 * Get keywords by IDs.
	 *
	 * @param array $ids List of IDs.
	 * @return array List of keywords.
	 */
	public function get_keywords_by_ids( $ids ) {
		if ( empty( $ids ) ) {
			return array();
		}
		$ids_placeholder = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM %i WHERE id IN ($ids_placeholder)",
				array_merge( array( $this->table_name ), $ids )
			),
			ARRAY_A
		);
	}
	/**
	 * Get statistics for AI category matching.
	 *
	 * @param string $filter_mode Filter mode (uncategorized, exclude_ranking, only_ranking, without_position, all).
	 * @return array Statistics array with total, auto_matchable, ai_needed.
	 */
	public function get_matching_statistics( $filter_mode ) {
		$where_clauses = $this->build_matching_filter_clauses( $filter_mode );
		$where_sql     = ! empty( $where_clauses ) ? 'WHERE ' . implode( ' AND ', $where_clauses ) : '';

		// Count total keywords matching filter
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$total_keywords = (int) $this->wpdb->get_var( "SELECT COUNT(*) FROM {$this->table_name} {$where_sql}" );

		// Count keywords that can be auto-matched from URLs
		$auto_matchable_sql = $where_sql ? $where_sql . " AND page_url IS NOT NULL AND page_url != ''" : "WHERE page_url IS NOT NULL AND page_url != ''";
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$count_auto_matchable = (int) $this->wpdb->get_var( "SELECT COUNT(*) FROM {$this->table_name} {$auto_matchable_sql}" );

		return array(
			'total'          => $total_keywords,
			'auto_matchable' => $count_auto_matchable,
			'ai_needed'      => max( 0, $total_keywords - $count_auto_matchable ),
		);
	}

	/**
	 * Build WHERE clauses for matching filter modes.
	 *
	 * @param string $filter_mode Filter mode.
	 * @return array Array of WHERE clause strings.
	 */
	private function build_matching_filter_clauses( $filter_mode ) {
		$where_clauses = array();

		switch ( $filter_mode ) {
			case 'uncategorized':
				$where_clauses[] = "(suggested_categories IS NULL OR suggested_categories = '' OR suggested_categories = '[]')";
				break;

			case 'exclude_ranking':
				$where_clauses[] = "(suggested_categories IS NULL OR suggested_categories = '' OR suggested_categories = '[]')";
				$where_clauses[] = "(position_abs IS NULL OR page_url IS NULL OR page_url = '')";
				break;

			case 'only_ranking':
				$where_clauses[] = 'position_abs IS NOT NULL';
				$where_clauses[] = "page_url IS NOT NULL AND page_url != ''";
				break;

			case 'without_position':
				$where_clauses[] = 'position_abs IS NULL';
				$where_clauses[] = "(page_url IS NULL OR page_url = '')";
				$where_clauses[] = "(suggested_categories IS NULL OR suggested_categories = '' OR suggested_categories = '[]')";
				break;

			case 'all':
			default:
				// No filter - all keywords
				break;
		}

		return $where_clauses;
	}

	/**
	 * Update keyword by keyword text and language.
	 *
	 * @param string $keyword       Keyword text.
	 * @param string $language_code Language code.
	 * @param array  $data          Data to update.
	 * @return int|false Number of rows updated, or false on error.
	 */
	public function update_keyword_by_text( $keyword, $language_code, $data ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->update(
			$this->table_name,
			$data,
			array(
				'keyword'       => $keyword,
				'language_code' => $language_code,
			),
			array_fill( 0, count( $data ), '%s' ),
			array( '%s', '%s' )
		);
	}
	/**
	 * Get all keywords that have embeddings for a specific language
	 *
	 * @param string $language_code
	 * @return array List of objects with id, keyword, embedding
	 */
	public function get_keywords_with_embeddings( $language_code ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT id, keyword, embedding FROM {$this->table_name} 
				WHERE language_code = %s 
				AND embedding IS NOT NULL",
				$language_code
			)
		);
	}
	/**
	 * Ensure embedding column exists.
	 *
	 * Robust check to add the column if missing.
	 * Called just-in-time before saving to handle migration failures.
	 * Uses transient caching to avoid performance penalty on every request.
	 */
	public function ensure_embedding_column() {
		// Check cache first (performance optimization)
		if ( get_transient( 'wisewand_schema_embedding_verified' ) ) {
			return;
		}

		// Check if column exists
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
		$column_exists = $this->wpdb->get_results(
			$this->wpdb->prepare( "SHOW COLUMNS FROM `{$this->table_name}` LIKE %s", 'embedding' )
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		if ( empty( $column_exists ) ) {
			// Add embedding column
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
			$this->wpdb->query(
				"ALTER TABLE `{$this->table_name}`
				ADD COLUMN embedding mediumblob DEFAULT NULL COMMENT 'Binary storage of 256-float vector'"
			);
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}

		// Cache the verification for 7 days
		set_transient( 'wisewand_schema_embedding_verified', true, WEEK_IN_SECONDS );
	}
}
