<?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/>.
 */

/**
 * Keyword Cluster Manager
 *
 * Manages database operations for keyword clusters and deduplication.
 *
 * @package Wisewand
 * @subpackage Utils
 * @version 1.0.0
 */

namespace Wisewand\Utils;

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


use Wisewand\Logging\Logger;
use Wisewand\Utils\Options;

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

class Keyword_Cluster_Manager {

	private $logger;
	private $options;
	private $clusters_table;
	private $keywords_table;

	public function __construct( Options $options, Logger $logger ) {
		global $wpdb;

		$this->options        = $options;
		$this->logger         = $logger;
		$this->clusters_table = $wpdb->prefix . 'wisewand_keyword_clusters';
		$this->keywords_table = $wpdb->prefix . 'wisewand_saved_keywords';
	}

	/**
	 * Save clusters to database
	 *
	 * @param array $clusters Cluster data from Deduplication_Engine
	 * @param bool  $auto_detected Whether clusters were auto-detected
	 * @return array Save result with stats
	 */
	public function save_clusters( $clusters, $auto_detected = true ) {
		global $wpdb;

		// Check if clusters table exists
        // 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', $this->clusters_table ) ) !== $this->clusters_table ) {
			$this->logger->error( 'Clusters table does not exist. Please reactivate the plugin to run migrations.' );
			return array(
				'saved'   => 0,
				'updated' => 0,
				'errors'  => array( 'Clusters table does not exist. Please deactivate and reactivate the plugin.' ),
			);
		}

		$saved   = 0;
		$updated = 0;
		$errors  = array();

		foreach ( $clusters as $cluster ) {
			try {
				// Generate cluster hash for deduplication
				$cluster_hash = $this->generate_cluster_hash( $cluster['keyword_ids'] );

				// Check if cluster already exists
				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table query for cluster existence check
				$existing = $wpdb->get_row(
					$wpdb->prepare(
						"SELECT id FROM %i WHERE cluster_hash = %s",
						$this->clusters_table,
						$cluster_hash
					),
					ARRAY_A
				);

				$cluster_data = array(
					'cluster_hash'        => $cluster_hash,
					'primary_keyword_id'  => $cluster['primary_id'],
					'variant_keyword_ids' => wp_json_encode( $cluster['variant_ids'] ),
					'similarity_scores'   => wp_json_encode( $cluster['similarities'] ),
					'cluster_metrics'     => wp_json_encode(
						array(
							'size'           => $cluster['size'],
							'avg_similarity' => $cluster['avg_similarity'],
						)
					),
					'auto_detected'       => $auto_detected ? 1 : 0,
					'is_active'           => 1,
				);

				if ( $existing ) {
					// Update existing cluster
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table update for existing cluster
					$wpdb->update(
						$this->clusters_table,
						array_merge( $cluster_data, array( 'updated_at' => current_time( 'mysql' ) ) ),
						array( 'id' => $existing['id'] )
					);
					++$updated;
				} else {
					// Insert new cluster
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom plugin table insert for new cluster
					$wpdb->insert( $this->clusters_table, $cluster_data );
					++$saved;
				}

				// Update keyword records
				$this->update_keyword_cluster_references( $cluster['primary_id'], $cluster['variant_ids'] );

			} catch ( \Exception $e ) {
				$errors[] = $e->getMessage();
				$this->logger->error(
					'Failed to save cluster',
					array(
						'cluster' => $cluster,
						'error'   => $e->getMessage(),
					)
				);
			}
		}

		return array(
			'saved'   => $saved,
			'updated' => $updated,
			'errors'  => $errors,
		);
	}

	/**
	 * Update keyword records with cluster references
	 */
	private function update_keyword_cluster_references( $primary_id, $variant_ids ) {
		global $wpdb;

		// Set primary keyword
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table update for cluster primary keyword flag
		$wpdb->update(
			$this->keywords_table,
			array( 'is_cluster_primary' => 1 ),
			array( 'id' => $primary_id )
		);

		// Set variant keywords
		if ( ! empty( $variant_ids ) ) {
			$placeholders = implode( ',', array_fill( 0, count( $variant_ids ), '%d' ) );
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table bulk update for cluster variants with IN clause
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix is safe
			$wpdb->query(
				$wpdb->prepare(
					"UPDATE {$this->keywords_table}
                SET is_cluster_primary = 0
                WHERE id IN ($placeholders)",
					...$variant_ids
				)
			);
		}
	}

	/**
	 * Generate unique hash for cluster
	 */
	private function generate_cluster_hash( $keyword_ids ) {
		sort( $keyword_ids );
		return md5( implode( '_', $keyword_ids ) );
	}

	/**
	 * Get all active clusters with full data
	 *
	 * @param array $filters Optional filters
	 * @return array Clusters with keyword data
	 */
	public function get_clusters( $filters = array() ) {
		global $wpdb;

		$where_clauses = array( 'c.is_active = 1' );
		$where_params  = array();

		if ( ! empty( $filters['min_similarity'] ) ) {
			$where_clauses[] = 'JSON_EXTRACT(c.cluster_metrics, "$.avg_similarity") >= %f';
			$where_params[]  = $filters['min_similarity'];
		}

		if ( ! empty( $filters['min_size'] ) ) {
			$where_clauses[] = 'JSON_EXTRACT(c.cluster_metrics, "$.size") >= %d';
			$where_params[]  = $filters['min_size'];
		}

		if ( ! empty( $filters['language_code'] ) ) {
			$where_clauses[] = 'c.language_code = %s';
			$where_params[]  = $filters['language_code'];
		}

		$where_sql = implode( ' AND ', $where_clauses );

		if ( ! empty( $where_params ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin tables JOIN query with whitelisted WHERE conditions
			$query = $wpdb->prepare(
				"SELECT c.*, k.keyword as primary_keyword, k.search_volume, k.priority_score, k.search_intent
                FROM %i c
                LEFT JOIN %i k ON k.id = c.primary_keyword_id
                WHERE {$where_sql}
                ORDER BY JSON_EXTRACT(c.cluster_metrics, '$.size') DESC, k.search_volume DESC",
				$this->clusters_table,
				$this->keywords_table,
				...$where_params
			);
		} else {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin tables JOIN query, no user input in table names or WHERE clause
			$query = $wpdb->prepare(
				"SELECT c.*, k.keyword as primary_keyword, k.search_volume, k.priority_score, k.search_intent
                FROM %i c
                LEFT JOIN %i k ON k.id = c.primary_keyword_id
                WHERE {$where_sql}
                ORDER BY JSON_EXTRACT(c.cluster_metrics, '$.size') DESC, k.search_volume DESC",
				$this->clusters_table,
				$this->keywords_table
			);
		}

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Query is conditionally prepared above when params exist
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query constructed with prepared components
		$clusters = $wpdb->get_results( $query, ARRAY_A );

		// Enrich clusters with variant data
		foreach ( $clusters as &$cluster ) {
			$cluster['variant_keyword_ids'] = json_decode( $cluster['variant_keyword_ids'], true ) ?? array();
			$cluster['similarity_scores']   = json_decode( $cluster['similarity_scores'], true ) ?? array();
			$cluster['cluster_metrics']     = json_decode( $cluster['cluster_metrics'], true ) ?? array();

			// Get variant keywords data
			if ( ! empty( $cluster['variant_keyword_ids'] ) ) {
				$placeholders = implode( ',', array_fill( 0, count( $cluster['variant_keyword_ids'] ), '%d' ) );
                // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table batch query with IN clause
				$variants_query = $wpdb->prepare(
					"SELECT id, keyword, search_volume, priority_score, search_intent, difficulty
                    FROM %i
                    WHERE id IN ($placeholders)",
					$this->keywords_table,
					...$cluster['variant_keyword_ids']
				);

                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Query is properly prepared above with $wpdb->prepare()
				// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query constructed with prepared components
				$cluster['variants'] = $wpdb->get_results( $variants_query, ARRAY_A );
			} else {
				$cluster['variants'] = array();
			}
		}

		return $clusters;
	}

	/**
	 * Set new primary for a cluster
	 *
	 * @param int $cluster_id
	 * @param int $new_primary_id
	 * @return bool Success
	 */
	public function set_cluster_primary( $cluster_id, $new_primary_id ) {
		global $wpdb;

		// Get current cluster
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table query for cluster data
		$cluster = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM %i WHERE id = %d",
				$this->clusters_table,
				$cluster_id
			),
			ARRAY_A
		);

		if ( ! $cluster ) {
			return false;
		}

		$old_primary_id = $cluster['primary_keyword_id'];
		$variant_ids    = json_decode( $cluster['variant_keyword_ids'], true ) ?? array();

		// Verify new primary is in the cluster
		if ( ! in_array( $new_primary_id, array_merge( array( $old_primary_id ), $variant_ids ) ) ) {
			return false;
		}

		// Rebuild variant list
		$all_ids         = array_merge( array( $old_primary_id ), $variant_ids );
		$new_variant_ids = array_values( array_diff( $all_ids, array( $new_primary_id ) ) );

		// Update cluster
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table update for cluster primary change
		$wpdb->update(
			$this->clusters_table,
			array(
				'primary_keyword_id'  => $new_primary_id,
				'variant_keyword_ids' => wp_json_encode( $new_variant_ids ),
				'user_modified'       => 1,
			),
			array( 'id' => $cluster_id )
		);

		// Update keyword flags
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table update for clearing old primary flag
		$wpdb->update( $this->keywords_table, array( 'is_cluster_primary' => 0 ), array( 'id' => $old_primary_id ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table update for setting new primary flag
		$wpdb->update( $this->keywords_table, array( 'is_cluster_primary' => 1 ), array( 'id' => $new_primary_id ) );

		$this->logger->info(
			'Cluster primary changed',
			array(
				'cluster_id'  => $cluster_id,
				'old_primary' => $old_primary_id,
				'new_primary' => $new_primary_id,
			)
		);

		return true;
	}

	/**
	 * Ungroup cluster (mark as not duplicates)
	 *
	 * @param int $cluster_id
	 * @return bool Success
	 */
	public function ungroup_cluster( $cluster_id ) {
		global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table update for deactivating cluster
		$result = $wpdb->update(
			$this->clusters_table,
			array(
				'is_active'  => 0,
				'updated_at' => current_time( 'mysql' ),
			),
			array( 'id' => $cluster_id )
		);

		if ( $result ) {
			// Clear cluster flags from keywords
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table query for cluster data
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix is safe
			$cluster = $wpdb->get_row(
				$wpdb->prepare(
					"SELECT * FROM {$this->clusters_table} WHERE id = %d",
					$cluster_id
				),
				ARRAY_A
			);

			if ( $cluster ) {
				$all_ids = array_merge(
					array( $cluster['primary_keyword_id'] ),
					json_decode( $cluster['variant_keyword_ids'], true ) ?? array()
				);

				foreach ( $all_ids as $id ) {
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table update for clearing keyword cluster flags
					$wpdb->update(
						$this->keywords_table,
						array(
							'is_cluster_primary' => 0,
							'cluster_id'         => null,
						),
						array( 'id' => $id )
					);
				}
			}

			$this->logger->info( 'Cluster ungrouped', array( 'cluster_id' => $cluster_id ) );
		}

		return (bool) $result;
	}

	/**
	 * Delete variant keywords (keep only primary)
	 *
	 * @param int $cluster_id
	 * @return array Result with deleted count
	 */
	public function delete_variants( $cluster_id ) {
		global $wpdb;

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table query for cluster data
		$cluster = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->clusters_table} WHERE id = %d",
				$cluster_id
			),
			ARRAY_A
		);

		if ( ! $cluster ) {
			return array(
				'success' => false,
				'message' => 'Cluster not found',
			);
		}

		$variant_ids = json_decode( $cluster['variant_keyword_ids'], true ) ?? array();

		if ( empty( $variant_ids ) ) {
			return array(
				'success' => false,
				'message' => 'No variants to delete',
			);
		}

		$deleted = 0;
		foreach ( $variant_ids as $id ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table delete for variant keywords
			if ( $wpdb->delete( $this->keywords_table, array( 'id' => $id ) ) ) {
				++$deleted;
			}
		}

		// Update cluster to remove deleted variants
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table update for clearing variant list
		$wpdb->update(
			$this->clusters_table,
			array( 'variant_keyword_ids' => wp_json_encode( array() ) ),
			array( 'id' => $cluster_id )
		);

		$this->logger->info(
			'Cluster variants deleted',
			array(
				'cluster_id' => $cluster_id,
				'deleted'    => $deleted,
			)
		);

		return array(
			'success' => true,
			'deleted' => $deleted,
		);
	}

	/**
	 * Approve cluster (mark as reviewed by user)
	 *
	 * @param int $cluster_id
	 * @return bool Success
	 */
	public function approve_cluster( $cluster_id ) {
		global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table update for cluster approval
		$result = $wpdb->update(
			$this->clusters_table,
			array(
				'user_approved' => 1,
				'updated_at'    => current_time( 'mysql' ),
			),
			array( 'id' => $cluster_id )
		);

		return (bool) $result;
	}

	/**
	 * Get cluster statistics
	 *
	 * @return array Statistics
	 */
	public function get_statistics() {
		global $wpdb;

        		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table stats query
		$stats = $wpdb->get_row(
			$wpdb->prepare(
				"
            SELECT
                COUNT(*) as total_clusters,
                SUM(JSON_LENGTH(variant_keyword_ids)) as total_variants,
                AVG(JSON_EXTRACT(cluster_metrics, '$.avg_similarity')) as avg_similarity,
                SUM(CASE WHEN user_approved = 1 THEN 1 ELSE 0 END) as approved_count,
                SUM(CASE WHEN user_modified = 1 THEN 1 ELSE 0 END) as modified_count
            FROM %i
            WHERE is_active = 1
        ",
				$this->clusters_table
			),
			ARRAY_A
		);

		// Get unique keywords count
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectDatabaseQuery.NoCaching -- Custom plugin table count query for total keywords
		$total_keywords = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i", $this->keywords_table ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table count query for clustered keywords
		$clustered_keywords = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE cluster_id IS NOT NULL", $this->keywords_table ) );

		return array(
			'total_clusters'  => intval( $stats['total_clusters'] ?? 0 ),
			'total_variants'  => intval( $stats['total_variants'] ?? 0 ),
			'avg_similarity'  => round( floatval( $stats['avg_similarity'] ?? 0 ), 2 ),
			'approved_count'  => intval( $stats['approved_count'] ?? 0 ),
			'modified_count'  => intval( $stats['modified_count'] ?? 0 ),
			'total_keywords'  => intval( $total_keywords ),
			'unique_keywords' => intval( $total_keywords ) - intval( $clustered_keywords ),
		);
	}

	/**
	 * Get cluster by ID with full data
	 *
	 * @param int $cluster_id
	 * @return array|null Cluster data
	 */
	public function get_cluster( $cluster_id ) {
		global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin tables JOIN query for cluster details
		$cluster = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT c.*, k.keyword as primary_keyword, k.search_volume, k.priority_score
            FROM %i c
            LEFT JOIN %i k ON k.id = c.primary_keyword_id
            WHERE c.id = %d",
				$this->clusters_table,
				$this->keywords_table,
				$cluster_id
			),
			ARRAY_A
		);

		if ( ! $cluster ) {
			return null;
		}

		// Decode JSON fields
		$cluster['variant_keyword_ids'] = json_decode( $cluster['variant_keyword_ids'], true ) ?? array();
		$cluster['similarity_scores']   = json_decode( $cluster['similarity_scores'], true ) ?? array();
		$cluster['cluster_metrics']     = json_decode( $cluster['cluster_metrics'], true ) ?? array();

		// Get variant data
		if ( ! empty( $cluster['variant_keyword_ids'] ) ) {
			$placeholders = implode( ',', array_fill( 0, count( $cluster['variant_keyword_ids'] ), '%d' ) );
            // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table batch query with IN clause
			$query = $wpdb->prepare(
				"SELECT * FROM %i WHERE id IN ($placeholders)",
				$this->keywords_table,
				...$cluster['variant_keyword_ids']
			);

            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Query is properly prepared above
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Query constructed with prepared components
			$cluster['variants'] = $wpdb->get_results( $query, ARRAY_A );
		} else {
			$cluster['variants'] = array();
		}

		return $cluster;
	}

	/**
	 * Clear all clusters (for re-scanning)
	 *
	 * @return int Number of clusters cleared
	 */
	public function clear_all_clusters() {
		global $wpdb;

		// Check if clusters table exists
        // 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', $this->clusters_table ) ) !== $this->clusters_table ) {
			// Table doesn't exist yet - nothing to clear
			return 0;
		}

		// Deactivate all clusters
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table bulk update for deactivating clusters
		$wpdb->query( $wpdb->prepare( "UPDATE %i SET is_active = %d", $this->clusters_table, 0 ) );

		// Check if cluster columns exist in keywords table before updating
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DDL SHOW COLUMNS query for column existence check
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix is safe
		$columns = $wpdb->get_col( "SHOW COLUMNS FROM {$this->keywords_table}" );

		if ( in_array( 'cluster_id', $columns ) ) {
			// Clear cluster references from keywords
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table bulk update for clearing cluster references
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix is safe
		$wpdb->query( $wpdb->prepare( "UPDATE {$this->keywords_table} SET cluster_id = NULL, is_cluster_primary = %d", 0 ) );
		}

		$count = $wpdb->rows_affected;

		$this->logger->info( 'All clusters cleared', array( 'count' => $count ) );

		return $count;
	}

	/**
	 * Get cluster for a specific keyword ID
	 *
	 * @param int $keyword_id
	 * @return array|null Cluster data if keyword is in a cluster
	 */
	public function get_cluster_for_keyword( $keyword_id ) {
		global $wpdb;

		// Check if keyword is a primary
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table query for finding cluster by primary keyword
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix is safe
		$as_primary = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->clusters_table}
            WHERE primary_keyword_id = %d AND is_active = 1",
				$keyword_id
			),
			ARRAY_A
		);

		if ( $as_primary ) {
			return $this->get_cluster( $as_primary['id'] );
		}

		// Check if keyword is a variant
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table query for finding cluster by variant keyword
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix is safe
		$as_variant = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$this->clusters_table}
            WHERE variant_keyword_ids LIKE %s AND is_active = 1",
				'%"' . $keyword_id . '"%'
			),
			ARRAY_A
		);

		if ( ! empty( $as_variant ) ) {
			return $this->get_cluster( $as_variant[0]['id'] );
		}

		return null;
	}

	/**
	 * Bulk approve all clusters
	 *
	 * @return int Number of clusters approved
	 */
	public function approve_all_clusters() {
		global $wpdb;

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from $wpdb->prefix (constructor line 29), no user input
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix is safe
		$wpdb->query( $wpdb->prepare( "UPDATE {$this->clusters_table} SET user_approved = %d WHERE is_active = %d", 1, 1 ) );

		return $wpdb->rows_affected;
	}

	/**
	 * Export clusters as structured data (for CSV/reports)
	 *
	 * @return array Exportable cluster data
	 */
	public function export_clusters() {
		$clusters = $this->get_clusters();

		$export_data = array();

		foreach ( $clusters as $cluster ) {
			$row = array(
				'cluster_id'       => $cluster['id'],
				'primary_keyword'  => $cluster['primary_keyword'],
				'primary_volume'   => $cluster['search_volume'] ?? 0,
				'primary_priority' => $cluster['priority_score'] ?? 0,
				'variant_count'    => count( $cluster['variants'] ),
				'avg_similarity'   => round( $cluster['cluster_metrics']['avg_similarity'] * 100, 1 ) . '%',
				'variants'         => implode( ', ', array_column( $cluster['variants'], 'keyword' ) ),
			);

			$export_data[] = $row;
		}

		return $export_data;
	}
}
