<?php
/**
 * This file is part of the Wisewand WordPress plugin.
 *
 * @package Wisewand
 * @since 1.0.0
 * @license GPL-2.0-or-later
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <https://www.gnu.org/licenses/>.
 */

namespace Wisewand\Utils;

// Exit if accessed directly
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Keyword Deduplication Engine
 *
 * Advanced clustering engine for detecting and grouping duplicate/similar keywords
 * to prevent content cannibalization.
 *
 * @package Wisewand
 * @subpackage Utils
 * @version 1.0.0
 */


use Wisewand\Logging\Logger;

// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table names derived from $wpdb->prefix are safe to interpolate; dynamic placeholder generation for IN clauses with array unpacking; direct database calls required for custom plugin table operations

class Keyword_Deduplication_Engine {

	// Configuration constants
	const RANKING_POSITION_MIN        = 1;
	const RANKING_POSITION_MAX        = 50;
	const PRIORITY_SCORE_BASE         = 1000;
	const SIMILARITY_HIGH_THRESHOLD   = 0.90;
	const SIMILARITY_MEDIUM_THRESHOLD = 0.75;
	const CACHE_DURATION              = 900; // 15 minutes in seconds

	// Priority tier constants
	const TIER_RANKING   = 'ranking';
	const TIER_PUBLISHED = 'published';
	const TIER_SCHEDULED = 'scheduled';
	const TIER_PLANNED   = 'planned';

	private $logger;
	private $similarity_threshold;
	private $metrics_required;

	/**
	 * @param Logger $logger
	 * @param float  $similarity_threshold Default 0.75 (strict mode)
	 * @param bool   $metrics_required Require metrics validation
	 */
	public function __construct( Logger $logger, $similarity_threshold = 0.75, $metrics_required = true ) {
		$this->logger               = $logger;
		$this->similarity_threshold = $similarity_threshold;
		$this->metrics_required     = $metrics_required;
	}

	/**
	 * Main clustering method - detects and groups similar keywords
	 *
	 * @param array       $keywords Array of keyword data (must have 'id', 'keyword' keys)
	 * @param callable    $progress_callback Optional callback for progress updates
	 * @param string|null $language_code Language code for ranking keyword filtering
	 * @param bool        $include_ranking_keywords Whether to fetch and integrate ranking keywords
	 * @return array Clusters data
	 */
	public function detect_clusters( $keywords, $progress_callback = null, $language_code = null, $include_ranking_keywords = true ) {
		$this->log_progress(
			$progress_callback,
			array(
				'step'           => 'init',
				'message'        => 'Initializing deduplication engine...',
				'total_keywords' => count( $keywords ),
			)
		);

		if ( empty( $keywords ) ) {
			return array(
				'clusters' => array(),
				'unique'   => array(),
			);
		}

		// Step 0: Fetch and merge ranking keywords if enabled
		if ( $include_ranking_keywords ) {
			$this->log_progress(
				$progress_callback,
				array(
					'step'    => 'fetch_rankings',
					'message' => 'Fetching ranking keywords to prevent cannibalization...',
				)
			);

			$ranking_keywords = $this->get_ranking_keywords( $language_code );

			$this->log_progress(
				$progress_callback,
				array(
					'step'          => 'fetch_rankings_complete',
					'message'       => 'Found ' . count( $ranking_keywords ) . ' ranking keywords',
					'ranking_count' => count( $ranking_keywords ),
				)
			);

			// Merge ranking keywords with provided keywords
			$keywords = $this->merge_ranking_keywords( $keywords, $ranking_keywords );

			$this->log_progress(
				$progress_callback,
				array(
					'step'              => 'merge_complete',
					'message'           => 'Total keywords after merge: ' . count( $keywords ),
					'total_after_merge' => count( $keywords ),
				)
			);
		}

		// Step 1: Pre-filter into candidate groups (massive performance boost)
		$this->log_progress(
			$progress_callback,
			array(
				'step'    => 'pre_filter',
				'message' => 'Pre-filtering keywords by language, first letter, word count...',
			)
		);

		$groups = Similarity_Calculator::pre_filter_candidates( $keywords );

		$this->log_progress(
			$progress_callback,
			array(
				'step'    => 'pre_filter_complete',
				'message' => 'Pre-filtering complete',
				'groups'  => count( $groups ),
			)
		);

		// Step 2: Build similarity graph
		$this->log_progress(
			$progress_callback,
			array(
				'step'    => 'similarity',
				'message' => 'Calculating similarity scores (multi-algorithm)...',
			)
		);

		$similarity_graph = $this->build_similarity_graph( $groups, $progress_callback );

		// Step 3: Detect clusters using connected components
		$this->log_progress(
			$progress_callback,
			array(
				'step'    => 'clustering',
				'message' => 'Building clusters from similarity graph...',
			)
		);

		$clusters = $this->extract_clusters( $similarity_graph, $keywords );

		// Step 4: Select primary keyword for each cluster
		$this->log_progress(
			$progress_callback,
			array(
				'step'    => 'select_primary',
				'message' => 'Selecting primary keywords (highest volume/priority)...',
			)
		);

		$clusters = $this->select_cluster_primaries( $clusters, $keywords );

		// Step 5: Find unique (non-clustered) keywords
		$clustered_ids = array();
		foreach ( $clusters as $cluster ) {
			$clustered_ids = array_merge( $clustered_ids, $cluster['keyword_ids'] );
		}

		$unique_keywords = array_filter(
			$keywords,
			function ( $kw ) use ( $clustered_ids ) {
				return ! in_array( $kw['id'], $clustered_ids );
			}
		);

		$this->log_progress(
			$progress_callback,
			array(
				'step'            => 'complete',
				'message'         => 'Deduplication complete!',
				'clusters_found'  => count( $clusters ),
				'unique_keywords' => count( $unique_keywords ),
				'total_variants'  => count( $clustered_ids ),
			)
		);

		return array(
			'clusters' => $clusters,
			'unique'   => array_values( $unique_keywords ),
			'stats'    => array(
				'total_keywords' => count( $keywords ),
				'clusters'       => count( $clusters ),
				'unique'         => count( $unique_keywords ),
				'variants'       => count( $clustered_ids ),
				'reduction'      => count( $clusters ) + count( $unique_keywords ),
			),
		);
	}

	/**
	 * Fetch ranking keywords from both data sources
	 *
	 * Retrieves keywords currently ranking in top 50 positions from:
	 * 1. SEO Intelligence snapshots (wisewand_seo_keywords_v2)
	 * 2. Saved keywords with position data (wisewand_saved_keywords)
	 *
	 * Implements caching (15 minutes) and error recovery for production stability.
	 *
	 * @param string|null $language_code Optional language filter
	 * @return array Ranking keywords with priority tier
	 */
	private function get_ranking_keywords( $language_code = null ) {
		// Check cache first
		$cache_key = 'wisewand_ranking_kw_' . ( $language_code ?: 'all' );
		$cached    = get_transient( $cache_key );

		if ( $cached !== false ) {
			$this->logger->debug(
				'Using cached ranking keywords',
				array(
					'language' => $language_code,
					'count'    => count( $cached ),
				)
			);
			return $cached;
		}

		global $wpdb;
		$ranking_keywords = array();

		try {
			// Source 1: SEO Intelligence snapshots (wisewand_seo_keywords_v2)
			$seo_table = $wpdb->prefix . 'wisewand_seo_keywords_v2';

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DDL SHOW TABLES query for table existence check
			if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $seo_table ) ) === $seo_table ) {
				// Build query conditionally to avoid SQL injection
				if ( $language_code ) {
                    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table query for SEO keyword rankings
					// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix is safe
					$seo_rankings = $wpdb->get_results(
						$wpdb->prepare(
							"SELECT DISTINCT
                            keyword,
                            position_abs,
                            page_url,
                            language_code,
                            target_domain,
                            created_at
                        FROM {$seo_table}
                        WHERE position_abs BETWEEN %d AND %d
                        AND language_code = %s
                        AND keyword_type = %s
                        AND keyword IS NOT NULL
                        AND keyword != ''
                        ORDER BY position_abs ASC",
							self::RANKING_POSITION_MIN,
							self::RANKING_POSITION_MAX,
							$language_code,
							'current'
						),
						ARRAY_A
					);
				} else {
                    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table query for SEO keyword rankings
					// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix is safe
					$seo_rankings = $wpdb->get_results(
						$wpdb->prepare(
							"SELECT DISTINCT
                            keyword,
                            position_abs,
                            page_url,
                            language_code,
                            target_domain,
                            created_at
                        FROM {$seo_table}
                        WHERE position_abs BETWEEN %d AND %d
                        AND keyword_type = %s
                        AND keyword IS NOT NULL
                        AND keyword != ''
                        ORDER BY position_abs ASC",
							self::RANKING_POSITION_MIN,
							self::RANKING_POSITION_MAX,
							'current'
						),
						ARRAY_A
					);
				}

				foreach ( $seo_rankings as $row ) {
					$normalized_keyword = strtolower( trim( $row['keyword'] ) );

					if ( empty( $normalized_keyword ) ) {
						continue;
					}

					// Use keyword as key to prevent duplicates
					if ( ! isset( $ranking_keywords[ $normalized_keyword ] ) ||
						$row['position_abs'] < $ranking_keywords[ $normalized_keyword ]['position_abs'] ) {

						$ranking_keywords[ $normalized_keyword ] = array(
							'id'                 => 'ranking_seo_' . md5( $normalized_keyword . '_' . $row['language_code'] ),
							'keyword'            => $row['keyword'],
							'normalized_keyword' => $normalized_keyword,
							'position_abs'       => (int) $row['position_abs'],
							'page_url'           => $row['page_url'],
							'language_code'      => $row['language_code'],
							'source'             => 'seo_intelligence',
							'is_ranking'         => true,
							'priority_tier'      => self::TIER_RANKING,
							'priority_score'     => self::PRIORITY_SCORE_BASE - (int) $row['position_abs'],
							'created_at'         => $row['created_at'],
						);
					}
				}

				$this->logger->debug(
					'Ranking Keywords: Fetched from SEO Intelligence',
					array(
						'count'           => isset( $seo_rankings ) ? count( $seo_rankings ) : 0,
						'unique_keywords' => count( $ranking_keywords ),
					)
				);
			}

			// Source 2: Saved keywords with ranking data (wisewand_saved_keywords)
			$saved_table = $wpdb->prefix . 'wisewand_saved_keywords';

			if ( $language_code ) {
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table query for saved keyword rankings
				// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix is safe
				$saved_rankings = $wpdb->get_results(
					$wpdb->prepare(
						"SELECT
                        id,
                        keyword,
                        position_abs,
                        page_url,
                        wordpress_post_id,
                        language_code,
                        search_volume,
                        priority_score,
                        suggested_categories
                    FROM {$saved_table}
                    WHERE position_abs BETWEEN %d AND %d
                    AND language_code = %s
                    AND keyword IS NOT NULL
                    AND keyword != ''
                    ORDER BY position_abs ASC",
						self::RANKING_POSITION_MIN,
						self::RANKING_POSITION_MAX,
						$language_code
					),
					ARRAY_A
				);
			} else {
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table query for saved keyword rankings
				// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix is safe
				$saved_rankings = $wpdb->get_results(
					$wpdb->prepare(
						"SELECT
                        id,
                        keyword,
                        position_abs,
                        page_url,
                        wordpress_post_id,
                        language_code,
                        search_volume,
                        priority_score,
                        suggested_categories
                    FROM {$saved_table}
                    WHERE position_abs BETWEEN %d AND %d
                    AND keyword IS NOT NULL
                    AND keyword != ''
                    ORDER BY position_abs ASC",
						self::RANKING_POSITION_MIN,
						self::RANKING_POSITION_MAX
					),
					ARRAY_A
				);
			}

			foreach ( $saved_rankings as $row ) {
				$normalized_keyword = strtolower( trim( $row['keyword'] ) );

				if ( empty( $normalized_keyword ) ) {
					continue;
				}

				// Check if this keyword is already in ranking_keywords from SEO data
				if ( isset( $ranking_keywords[ $normalized_keyword ] ) ) {
					// Update with saved keyword data if it has better info
					$ranking_keywords[ $normalized_keyword ]['saved_id']             = $row['id'];
					$ranking_keywords[ $normalized_keyword ]['wordpress_post_id']    = $row['wordpress_post_id'];
					$ranking_keywords[ $normalized_keyword ]['suggested_categories'] = $row['suggested_categories'];

					// Use better position if available
					if ( $row['position_abs'] < $ranking_keywords[ $normalized_keyword ]['position_abs'] ) {
						$ranking_keywords[ $normalized_keyword ]['position_abs']   = (int) $row['position_abs'];
						$ranking_keywords[ $normalized_keyword ]['priority_score'] = self::PRIORITY_SCORE_BASE - (int) $row['position_abs'];
					}
				} else {
					// New ranking keyword from saved keywords
					$ranking_keywords[ $normalized_keyword ] = array(
						'id'                   => 'ranking_saved_' . $row['id'],
						'saved_id'             => $row['id'],
						'keyword'              => $row['keyword'],
						'normalized_keyword'   => $normalized_keyword,
						'position_abs'         => (int) $row['position_abs'],
						'page_url'             => $row['page_url'],
						'wordpress_post_id'    => $row['wordpress_post_id'],
						'language_code'        => $row['language_code'],
						'search_volume'        => $row['search_volume'],
						'suggested_categories' => $row['suggested_categories'],
						'source'               => 'saved_keywords',
						'is_ranking'           => true,
						'priority_tier'        => self::TIER_RANKING,
						'priority_score'       => self::PRIORITY_SCORE_BASE - (int) $row['position_abs'],
					);
				}
			}

			$result = array_values( $ranking_keywords );

			// Cache for 15 minutes
			set_transient( $cache_key, $result, self::CACHE_DURATION );

			$this->logger->info(
				'Ranking Keywords: Fetch complete',
				array(
					'seo_rankings'   => isset( $seo_rankings ) ? count( $seo_rankings ) : 0,
					'saved_rankings' => isset( $saved_rankings ) ? count( $saved_rankings ) : 0,
					'total_unique'   => count( $result ),
					'language'       => $language_code ?? 'all',
					'cached'         => true,
				)
			);

			return $result;

		} catch ( \Exception $e ) {
			$this->logger->error(
				'Failed to fetch ranking keywords, continuing without them',
				array(
					'error'    => $e->getMessage(),
					'language' => $language_code,
				)
			);

			// Return empty array instead of failing entire process
			return array();
		}
	}

	/**
	 * Merge ranking keywords with provided keywords
	 *
	 * Implements smart merging:
	 * - If keyword exists in both lists, mark as ranking and upgrade priority
	 * - If ranking keyword is new, add it with protection flags
	 * - Assign priority tiers: Ranking > Published > Planned
	 *
	 * Uses batch fetching to avoid N+1 query problems.
	 *
	 * @param array $keywords Provided keywords (from UI selection)
	 * @param array $ranking_keywords Fetched ranking keywords
	 * @return array Merged and prioritized keyword list
	 */
	private function merge_ranking_keywords( $keywords, $ranking_keywords ) {
		$merged         = array();
		$ranking_lookup = array();

		// Build lookup by normalized keyword for fast matching
		foreach ( $ranking_keywords as $ranking_kw ) {
			$normalized                    = strtolower( trim( $ranking_kw['keyword'] ) );
			$ranking_lookup[ $normalized ] = $ranking_kw;
		}

		// BATCH FETCH: Get all post statuses and scheduled keywords at once (avoid N+1 queries)
		$tier_cache = $this->batch_fetch_tier_data( $keywords );

		// Process provided keywords
		foreach ( $keywords as $kw ) {
			$normalized = strtolower( trim( $kw['keyword'] ) );

			if ( isset( $ranking_lookup[ $normalized ] ) ) {
				// Keyword is already ranking! Merge data and upgrade priority
				$ranking_data = $ranking_lookup[ $normalized ];

				$merged[] = array_merge(
					$kw,
					array(
						'is_ranking'        => true,
						'priority_tier'     => self::TIER_RANKING,
						'position_abs'      => $ranking_data['position_abs'],
						'page_url'          => $ranking_data['page_url'],
						'wordpress_post_id' => $ranking_data['wordpress_post_id'] ?? null,
						'priority_score'    => self::PRIORITY_SCORE_BASE - (int) $ranking_data['position_abs'],
						'ranking_source'    => $ranking_data['source'],
						'is_protected'      => true, // Protect from being marked as variant
					)
				);

				// Remove from ranking lookup (already merged)
				unset( $ranking_lookup[ $normalized ] );

				$this->logger->debug(
					'Merged ranking keyword',
					array(
						'keyword'  => $kw['keyword'],
						'position' => $ranking_data['position_abs'],
					)
				);
			} else {
				// Not ranking - determine tier using cached data (NO database queries!)
				$tier = $this->determine_keyword_tier_cached( $kw, $tier_cache );

				$merged[] = array_merge(
					$kw,
					array(
						'is_ranking'     => false,
						'priority_tier'  => $tier,
						'priority_score' => $kw['priority_score'] ?? 0,
						'is_protected'   => false,
					)
				);
			}
		}

		// Add remaining ranking keywords that weren't in the provided list
		foreach ( $ranking_lookup as $ranking_kw ) {
			$merged[] = array_merge(
				$ranking_kw,
				array(
					'is_protected'        => true,
					'is_external_ranking' => true, // Mark as external (not in saved keywords)
				)
			);

			$this->logger->debug(
				'Added external ranking keyword',
				array(
					'keyword'  => $ranking_kw['keyword'],
					'position' => $ranking_kw['position_abs'],
					'source'   => $ranking_kw['source'],
				)
			);
		}

		$this->logger->info(
			'Keyword Merge Complete',
			array(
				'original_count'    => count( $keywords ),
				'ranking_count'     => count( $ranking_keywords ),
				'merged_count'      => count( $merged ),
				'ranking_in_merged' => count( array_filter( $merged, fn( $k ) => $k['is_ranking'] ?? false ) ),
				'protected_count'   => count( array_filter( $merged, fn( $k ) => $k['is_protected'] ?? false ) ),
			)
		);

		return $merged;
	}

	/**
	 * Batch fetch tier determination data to avoid N+1 queries
	 *
	 * Fetches all post statuses and scheduled keywords in two queries instead of
	 * one query per keyword.
	 *
	 * @param array $keywords Keywords to analyze
	 * @return array Cache with post_statuses and scheduled_keywords
	 */
	private function batch_fetch_tier_data( $keywords ) {
		global $wpdb;

		$cache = array(
			'post_statuses'      => array(),
			'scheduled_keywords' => array(),
		);

		// BATCH FETCH 1: Get all post statuses at once
		$post_ids = array_filter( array_unique( array_column( $keywords, 'wordpress_post_id' ) ) );

		if ( ! empty( $post_ids ) ) {
			$placeholders = implode( ',', array_fill( 0, count( $post_ids ), '%d' ) );
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Batch fetch post statuses with IN clause, more efficient than individual get_post() calls
			$results = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT ID, post_status FROM {$wpdb->posts} WHERE ID IN ($placeholders)",
					...$post_ids
				),
				ARRAY_A
			);

			foreach ( $results as $row ) {
				$cache['post_statuses'][ $row['ID'] ] = $row['post_status'];
			}

			$this->logger->debug(
				'Batch fetched post statuses',
				array(
					'post_ids' => count( $post_ids ),
					'found'    => count( $cache['post_statuses'] ),
				)
			);
		}

		// BATCH FETCH 2: Get all scheduled keywords at once
		$keyword_texts = array_unique( array_column( $keywords, 'keyword' ) );

		if ( ! empty( $keyword_texts ) ) {
			$calendar_table = $wpdb->prefix . 'wisewand_editorial_calendar';

            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DDL SHOW TABLES query for table existence check
			if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $calendar_table ) ) === $calendar_table ) {
				$placeholders = implode( ',', array_fill( 0, count( $keyword_texts ), '%s' ) );
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table batch query for scheduled keywords, more efficient than individual checks
					// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix is safe
					$results = $wpdb->get_col(
						$wpdb->prepare(
							"SELECT DISTINCT keyword FROM {$calendar_table}
                         WHERE keyword IN ($placeholders)
                         AND wisewand_status IN ('pending', 'in_progress', 'completed')",
							...$keyword_texts
						)
					);

				$cache['scheduled_keywords'] = array_flip( $results );

				$this->logger->debug(
					'Batch fetched scheduled keywords',
					array(
						'keyword_texts' => count( $keyword_texts ),
						'scheduled'     => count( $cache['scheduled_keywords'] ),
					)
				);
			}
		}

		return $cache;
	}

	/**
	 * Determine keyword priority tier using cached data (no database queries)
	 *
	 * @param array $keyword Keyword data
	 * @param array $cache Pre-fetched tier data from batch_fetch_tier_data()
	 * @return string Priority tier
	 */
	private function determine_keyword_tier_cached( $keyword, $cache ) {
		// Check if published (has WordPress post that's published)
		if ( ! empty( $keyword['wordpress_post_id'] ) ) {
			$post_status = $cache['post_statuses'][ $keyword['wordpress_post_id'] ] ?? null;

			if ( 'publish' === $post_status ) {
				return self::TIER_PUBLISHED;
			}
		}

		// Check if scheduled in editorial calendar
		if ( isset( $cache['scheduled_keywords'][ $keyword['keyword'] ] ) ) {
			return self::TIER_SCHEDULED;
		}

		// Default: planned (saved but not published/scheduled)
		return self::TIER_PLANNED;
	}

	/**
	 * Determine keyword priority tier based on status
	 *
	 * @deprecated Use determine_keyword_tier_cached() with batch_fetch_tier_data() instead
	 *             to avoid N+1 query problems.
	 *
	 * This method is kept for backward compatibility but should not be used in new code.
	 *
	 * @param array $keyword Keyword data
	 * @return string Priority tier
	 */
	private function determine_keyword_tier( $keyword ) {
		// Fetch tier data for single keyword (not efficient, but maintains backward compatibility)
		$cache = $this->batch_fetch_tier_data( array( $keyword ) );
		return $this->determine_keyword_tier_cached( $keyword, $cache );
	}

	/**
	 * Build similarity graph using pre-filtered groups
	 */
	private function build_similarity_graph( $groups, $progress_callback = null ) {
		$graph             = array(); // Format: [keyword_id => [similar_id => similarity_score]]
		$comparisons       = 0;
		$total_comparisons = 0;

		// Calculate total comparisons
		foreach ( $groups as $group ) {
			$n                  = count( $group );
			$total_comparisons += ( $n * ( $n - 1 ) ) / 2;
		}

		foreach ( $groups as $filter_key => $group ) {
			$group_size = count( $group );

			if ( $group_size < 2 ) {
				continue; // No need to compare single keyword
			}

			$this->log_progress(
				$progress_callback,
				array(
					'step'    => 'comparing_group',
					'message' => "Comparing group: {$filter_key} ({$group_size} keywords)",
				)
			);

			for ( $i = 0; $i < $group_size; $i++ ) {
				for ( $j = $i + 1; $j < $group_size; $j++ ) {
					$kw1 = $group[ $i ];
					$kw2 = $group[ $j ];

					// Calculate similarity
					$similarity = Similarity_Calculator::calculate_similarity(
						$kw1['keyword'],
						$kw2['keyword']
					);

					++$comparisons;

					// Get language-specific threshold (more lenient for French/Spanish/etc)
					$lang      = $kw1['language_code'] ?? 'en';
					$threshold = Similarity_Calculator::get_threshold_for_language( $lang );

					// Only proceed if above language-specific threshold
					if ( $similarity >= $threshold ) {
						// Validate metrics if required
						if ( $this->metrics_required ) {
							if ( ! Similarity_Calculator::are_metrics_similar( $kw1, $kw2 ) ) {
								continue; // Different metrics = different target keywords
							}
						}

						// Add to graph
						if ( ! isset( $graph[ $kw1['id'] ] ) ) {
							$graph[ $kw1['id'] ] = array();
						}
						if ( ! isset( $graph[ $kw2['id'] ] ) ) {
							$graph[ $kw2['id'] ] = array();
						}

						$graph[ $kw1['id'] ][ $kw2['id'] ] = $similarity;
						$graph[ $kw2['id'] ][ $kw1['id'] ] = $similarity;
					}

					// Progress update every 50 comparisons
					if ( $comparisons % 50 === 0 && $progress_callback ) {
						$this->log_progress(
							$progress_callback,
							array(
								'step'              => 'similarity_progress',
								'progress'          => round( ( $comparisons / $total_comparisons ) * 100, 1 ),
								'comparisons'       => $comparisons,
								'total_comparisons' => $total_comparisons,
							)
						);
					}
				}
			}
		}

		return $graph;
	}

	/**
	 * Extract clusters from similarity graph using connected components
	 */
	private function extract_clusters( $graph, $keywords ) {
		$visited  = array();
		$clusters = array();

		foreach ( $graph as $keyword_id => $connections ) {
			if ( isset( $visited[ $keyword_id ] ) ) {
				continue;
			}

			// Find all connected keywords (depth-first search)
			$cluster_ids = $this->find_connected_component( $keyword_id, $graph, $visited );

			if ( count( $cluster_ids ) >= 2 ) {
				// Get similarity scores for this cluster
				$cluster_similarities = $this->get_cluster_similarities( $cluster_ids, $graph );

				$clusters[] = array(
					'keyword_ids'    => $cluster_ids,
					'size'           => count( $cluster_ids ),
					'similarities'   => $cluster_similarities,
					'avg_similarity' => $this->calculate_average_similarity( $cluster_similarities ),
				);
			}
		}

		// Sort clusters by size (largest first)
		usort(
			$clusters,
			function ( $a, $b ) {
				return $b['size'] - $a['size'];
			}
		);

		return $clusters;
	}

	/**
	 * Find all keywords connected to a given keyword (DFS)
	 */
	private function find_connected_component( $start_id, $graph, &$visited ) {
		$component = array();
		$stack     = array( $start_id );

		while ( ! empty( $stack ) ) {
			$current_id = array_pop( $stack );

			if ( isset( $visited[ $current_id ] ) ) {
				continue;
			}

			$visited[ $current_id ] = true;
			$component[]            = $current_id;

			// Add all connected neighbors to stack
			if ( isset( $graph[ $current_id ] ) ) {
				foreach ( $graph[ $current_id ] as $neighbor_id => $similarity ) {
					if ( ! isset( $visited[ $neighbor_id ] ) ) {
						$stack[] = $neighbor_id;
					}
				}
			}
		}

		return $component;
	}

	/**
	 * Get similarity scores within a cluster
	 */
	private function get_cluster_similarities( $cluster_ids, $graph ) {
		$similarities = array();

		foreach ( $cluster_ids as $id1 ) {
			if ( ! isset( $graph[ $id1 ] ) ) {
				continue;
			}

			foreach ( $cluster_ids as $id2 ) {
				if ( $id1 >= $id2 ) {
					continue; // Skip self and reverse pairs
				}

				if ( isset( $graph[ $id1 ][ $id2 ] ) ) {
					$key                  = $id1 . '_' . $id2;
					$similarities[ $key ] = $graph[ $id1 ][ $id2 ];
				}
			}
		}

		return $similarities;
	}

	/**
	 * Calculate average similarity for a cluster
	 */
	private function calculate_average_similarity( $similarities ) {
		if ( empty( $similarities ) ) {
			return 0.0;
		}

		return round( array_sum( $similarities ) / count( $similarities ), 4 );
	}

	/**
	 * Select primary keyword for each cluster
	 */
	private function select_cluster_primaries( $clusters, $keywords ) {
		// Create keyword lookup
		$keyword_lookup = array();
		foreach ( $keywords as $kw ) {
			$keyword_lookup[ $kw['id'] ] = $kw;
		}

		foreach ( $clusters as &$cluster ) {
			// Get all keywords in this cluster
			$cluster_keywords = array_map(
				function ( $id ) use ( $keyword_lookup ) {
					return $keyword_lookup[ $id ] ?? null;
				},
				$cluster['keyword_ids']
			);

			$cluster_keywords = array_filter( $cluster_keywords );

			// Select primary using priority rules
			$primary = $this->choose_primary_keyword( $cluster_keywords );

			$cluster['primary_id']      = $primary['id'];
			$cluster['primary_keyword'] = $primary['keyword'];
			$cluster['primary_data']    = $primary;

			// Store variants (all except primary)
			$cluster['variant_ids'] = array_values( array_diff( $cluster['keyword_ids'], array( $primary['id'] ) ) );
			$cluster['variants']    = array_filter(
				$cluster_keywords,
				function ( $kw ) use ( $primary ) {
					return $kw['id'] !== $primary['id'];
				}
			);
		}

		return $clusters;
	}

	/**
	 * Choose primary keyword using 3-tier priority system
	 *
	 * CRITICAL: Ranking keywords MUST always be protected from cannibalization
	 *
	 * Priority hierarchy:
	 * 1. Ranking keywords (position 1-50) - ALWAYS primary
	 * 2. Published keywords (has published WordPress post)
	 * 3. Planned keywords (saved or scheduled, not yet published)
	 *
	 * Within each tier:
	 * - User-selected primary (if marked)
	 * - Better ranking position (for ranking tier)
	 * - Highest search volume
	 * - Highest priority score
	 * - Longest keyword (more specific)
	 */
	private function choose_primary_keyword( $keywords ) {
		if ( empty( $keywords ) ) {
			return null;
		}

		usort(
			$keywords,
			function ( $a, $b ) {
				// TIER 0: User-selected primary (can override ranking, but with warning logged)
				if ( ! empty( $a['is_cluster_primary'] ) ) {
					if ( ! empty( $b['is_ranking'] ) ) {
						$this->logger->warning(
							'User override: Selected non-ranking keyword over ranking keyword',
							array(
								'selected'        => $a['keyword'],
								'ranking_keyword' => $b['keyword'],
								'position'        => $b['position_abs'] ?? 'unknown',
							)
						);
					}
					return -1;
				}
				if ( ! empty( $b['is_cluster_primary'] ) ) {
					if ( ! empty( $a['is_ranking'] ) ) {
						$this->logger->warning(
							'User override: Selected non-ranking keyword over ranking keyword',
							array(
								'selected'        => $b['keyword'],
								'ranking_keyword' => $a['keyword'],
								'position'        => $a['position_abs'] ?? 'unknown',
							)
						);
					}
					return 1;
				}

				// TIER 1: Ranking keywords ALWAYS win (unless user override)
				$a_ranking = ! empty( $a['is_ranking'] );
				$b_ranking = ! empty( $b['is_ranking'] );

				if ( $a_ranking && ! $b_ranking ) {
					return -1; // a is ranking, b is not → a wins
				}
				if ( ! $a_ranking && $b_ranking ) {
					return 1; // b is ranking, a is not → b wins
				}

				// Both ranking OR both not ranking → use tier system
				if ( $a_ranking && $b_ranking ) {
					// Both ranking: better position wins
					$pos_a = intval( $a['position_abs'] ?? 999 );
					$pos_b = intval( $b['position_abs'] ?? 999 );

					if ( $pos_a !== $pos_b ) {
						return $pos_a - $pos_b; // Lower position = better (ascending)
					}
				}

				// TIER 2: Published vs Planned
				$tier_map = array(
					self::TIER_RANKING   => 3,    // Highest (already handled above)
					self::TIER_PUBLISHED => 2,  // Medium
					self::TIER_SCHEDULED => 1,  // Low-medium
					self::TIER_PLANNED   => 0,    // Lowest
				);

				$tier_a = $tier_map[ $a['priority_tier'] ?? self::TIER_PLANNED ] ?? 0;
				$tier_b = $tier_map[ $b['priority_tier'] ?? self::TIER_PLANNED ] ?? 0;

				if ( $tier_a !== $tier_b ) {
					return $tier_b - $tier_a; // Higher tier wins (descending)
				}

				// TIER 3: Within same tier, use traditional metrics

				// 3a. Highest search volume
				$vol_a = intval( $a['search_volume'] ?? 0 );
				$vol_b = intval( $b['search_volume'] ?? 0 );

				if ( $vol_a !== $vol_b ) {
					return $vol_b - $vol_a; // Descending
				}

				// 3b. Highest priority score
				$priority_a = floatval( $a['priority_score'] ?? 0 );
				$priority_b = floatval( $b['priority_score'] ?? 0 );

				if ( $priority_a !== $priority_b ) {
					return ( $priority_b > $priority_a ) ? 1 : -1;
				}

				// 3c. Longest keyword (more specific usually better)
				return strlen( $b['keyword'] ) - strlen( $a['keyword'] );
			}
		);

		$primary = $keywords[0];

		// Log primary selection for debugging
		$this->logger->debug(
			'Primary keyword selected',
			array(
				'keyword'      => $primary['keyword'],
				'tier'         => $primary['priority_tier'] ?? 'unknown',
				'is_ranking'   => $primary['is_ranking'] ?? false,
				'position'     => $primary['position_abs'] ?? 'N/A',
				'is_protected' => $primary['is_protected'] ?? false,
			)
		);

		return $primary;
	}

	/**
	 * Check for ranking keyword conflicts before scheduling
	 *
	 * Prevents cannibalization of existing rankings by warning when:
	 * - Keyword is already ranking (position 1-50)
	 * - Similar keyword variants are ranking
	 * - Cluster contains ranking keywords
	 *
	 * @param string      $keyword Keyword to check
	 * @param string|null $language_code Language filter
	 * @return array Ranking conflict data with warnings
	 */
	public function check_ranking_conflicts( $keyword, $language_code = null ) {
		global $wpdb;

		$conflicts = array(
			'has_conflicts'   => false,
			'exact_ranking'   => null,
			'similar_ranking' => array(),
			'risk_level'      => 'none',
			'recommendation'  => '',
		);

		// Fetch ranking keywords for this language
		$ranking_keywords = $this->get_ranking_keywords( $language_code );

		if ( empty( $ranking_keywords ) ) {
			return $conflicts;
		}

		$normalized_keyword = strtolower( trim( $keyword ) );

		// Check 1: Exact ranking match
		foreach ( $ranking_keywords as $ranking_kw ) {
			$ranking_normalized = strtolower( trim( $ranking_kw['keyword'] ) );

			if ( $normalized_keyword === $ranking_normalized ) {
				$conflicts['exact_ranking']  = array(
					'keyword'  => $ranking_kw['keyword'],
					'position' => $ranking_kw['position_abs'],
					'page_url' => $ranking_kw['page_url'],
					'post_id'  => $ranking_kw['wordpress_post_id'] ?? null,
				);
				$conflicts['has_conflicts']  = true;
				$conflicts['risk_level']     = 'high';
				$conflicts['recommendation'] = sprintf(
					/* translators: %d: Ranking position number (e.g., 1, 5, 10) */
					__( 'This keyword is already ranking at position #%d. Consider updating the existing content instead of creating new.', 'wisewand-ai-content-writer-seo-content-generator' ),
					$ranking_kw['position_abs']
				);

				$this->logger->warning(
					'Ranking Conflict: Exact match found',
					array(
						'keyword'  => $keyword,
						'position' => $ranking_kw['position_abs'],
					)
				);

				break;
			}
		}

		// Check 2: Similar ranking keywords (using similarity algorithm)
		if ( ! class_exists( 'Wisewand\\Utils\\Similarity_Calculator' ) ) {
			require_once WISEWAND_PLUGIN_DIR . 'includes/Utils/Similarity_Calculator.php';
		}

		foreach ( $ranking_keywords as $ranking_kw ) {
			$similarity = Similarity_Calculator::calculate_similarity( $keyword, $ranking_kw['keyword'] );

			// Get language-specific threshold
			$lang      = $language_code ?? 'en';
			$threshold = Similarity_Calculator::get_threshold_for_language( $lang );

			if ( $similarity >= $threshold ) {
				$conflicts['similar_ranking'][] = array(
					'keyword'    => $ranking_kw['keyword'],
					'position'   => $ranking_kw['position_abs'],
					'page_url'   => $ranking_kw['page_url'],
					'post_id'    => $ranking_kw['wordpress_post_id'] ?? null,
					'similarity' => round( $similarity, 2 ),
				);

				$conflicts['has_conflicts'] = true;

				if ( $conflicts['risk_level'] === 'none' ) {
					$conflicts['risk_level']     = $similarity >= self::SIMILARITY_HIGH_THRESHOLD ? 'high' : 'medium';
					$conflicts['recommendation'] = sprintf(
						/* translators: 1: Similar existing keyword, 2: Ranking position number, 3: New keyword being checked */
						__( 'Similar keyword "%1$s" is already ranking at position #%2$d. Creating content for "%3$s" may cause keyword cannibalization.', 'wisewand-ai-content-writer-seo-content-generator' ),
						$ranking_kw['keyword'],
						$ranking_kw['position_abs'],
						$keyword
					);
				}
			}
		}

		$this->logger->info(
			'Ranking Conflict Check Complete',
			array(
				'keyword'       => $keyword,
				'has_conflicts' => $conflicts['has_conflicts'],
				'exact_match'   => ! empty( $conflicts['exact_ranking'] ),
				'similar_count' => count( $conflicts['similar_ranking'] ),
				'risk_level'    => $conflicts['risk_level'],
			)
		);

		return $conflicts;
	}

	/**
	 * Check for cannibalization risks (scheduled or published keywords)
	 *
	 * @param string $keyword Keyword to check
	 * @param int    $keyword_id Keyword ID to exclude from results
	 * @return array Cannibalization warnings
	 */
	public function check_cannibalization( $keyword, $keyword_id = null ) {
		global $wpdb;

		$warnings = array(
			'calendar' => array(),
			'posts'    => array(),
			'clusters' => array(),
		);

		// Check 1: Editorial Calendar
		$calendar_table = $wpdb->prefix . 'wisewand_editorial_calendar';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table query for checking editorial calendar conflicts
		$calendar_query = $wpdb->prepare(
			"SELECT keyword, publication_date, wisewand_status, wordpress_status
            FROM %i
            WHERE keyword LIKE %s
            AND wisewand_status != 'failed'
            ORDER BY publication_date DESC
            LIMIT 10",
			$calendar_table,
			'%' . $wpdb->esc_like( $keyword ) . '%'
		);

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is already prepared above with $wpdb->prepare()
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query constructed with prepared components
		$calendar_matches = $wpdb->get_results( $calendar_query, ARRAY_A );

		foreach ( $calendar_matches as $match ) {
			$similarity = Similarity_Calculator::calculate_similarity( $keyword, $match['keyword'] );

			if ( $similarity >= self::SIMILARITY_MEDIUM_THRESHOLD ) {
				$warnings['calendar'][] = array(
					'keyword'    => $match['keyword'],
					'date'       => $match['publication_date'],
					'status'     => $match['wisewand_status'],
					'wp_status'  => $match['wordpress_status'],
					'similarity' => round( $similarity * 100, 1 ),
				);
			}
		}

		// Check 2: Published WordPress Posts
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct query with LIKE pattern matching more efficient than get_posts() for conflict detection
		$posts_query = $wpdb->prepare(
			"SELECT ID, post_title, post_name, post_date, post_status
            FROM %i
            WHERE post_type = 'post'
            AND post_status IN ('publish', 'draft', 'future')
            AND (post_title LIKE %s OR post_name LIKE %s)
            ORDER BY post_date DESC
            LIMIT 10",
			$wpdb->posts,
			'%' . $wpdb->esc_like( $keyword ) . '%',
			'%' . $wpdb->esc_like( sanitize_title( $keyword ) ) . '%'
		);

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is already prepared above with $wpdb->prepare()
		$post_matches = $wpdb->get_results( $posts_query, ARRAY_A );

		foreach ( $post_matches as $post ) {
			// Check both title and slug
			$title_similarity = Similarity_Calculator::calculate_similarity( $keyword, $post['post_title'] );
			$slug_similarity  = Similarity_Calculator::calculate_similarity( $keyword, str_replace( '-', ' ', $post['post_name'] ) );

			$max_similarity = max( $title_similarity, $slug_similarity );

			if ( $max_similarity >= self::SIMILARITY_MEDIUM_THRESHOLD ) {
				$warnings['posts'][] = array(
					'id'         => $post['ID'],
					'title'      => $post['post_title'],
					'slug'       => $post['post_name'],
					'date'       => $post['post_date'],
					'status'     => $post['post_status'],
					'similarity' => round( $max_similarity * 100, 1 ),
					'edit_url'   => admin_url( 'post.php?post=' . $post['ID'] . '&action=edit' ),
				);
			}
		}

		// Check 3: Existing Clusters
		if ( $keyword_id ) {
			$clusters_table = $wpdb->prefix . 'wisewand_keyword_clusters';

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DDL SHOW TABLES query for table existence check
			if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $clusters_table ) ) === $clusters_table ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table query for cluster conflicts
				$cluster_query = $wpdb->prepare(
					'SELECT c.*, k.keyword as primary_keyword
                    FROM %i c
                    LEFT JOIN %i k ON k.id = c.primary_keyword_id
                    WHERE c.is_active = 1
                    AND (c.primary_keyword_id = %d OR c.variant_keyword_ids LIKE %s)',
					$clusters_table,
					$wpdb->prefix . 'wisewand_saved_keywords',
					$keyword_id,
					'%"' . $keyword_id . '"%'
				);

                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is already prepared above with $wpdb->prepare()
				// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query constructed with prepared components
				$cluster_matches = $wpdb->get_results( $cluster_query, ARRAY_A );

				foreach ( $cluster_matches as $match ) {
					$warnings['clusters'][] = array(
						'cluster_id'      => $match['id'],
						'primary_keyword' => $match['primary_keyword'],
						'variant_count'   => count( json_decode( $match['variant_keyword_ids'], true ) ?? array() ),
					);
				}
			}
		}

		return $warnings;
	}

	/**
	 * Log progress callback
	 */
	private function log_progress( $callback, $data ) {
		if ( $callback && is_callable( $callback ) ) {
			call_user_func( $callback, $data );
		}

		if ( $this->logger && ! empty( $data['step'] ) ) {
			$this->logger->debug( 'Deduplication: ' . ( $data['message'] ?? $data['step'] ), $data );
		}
	}
}
