<?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;
}

use WP_Error;
use Wisewand\Logging\Logger;

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

class Saved_Keywords_Manager {

	private $options;
	private $logger;
	private $dataforseo_client;
	private $repository;
	private $semantic_engine;

	public function __construct( Options $options, Logger $logger, $dataforseo_client = null, \Wisewand\Repositories\Keyword_Repository $repository = null ) {
		$this->options           = $options;
		$this->logger            = $logger;
		$this->dataforseo_client = $dataforseo_client;
		$this->repository        = $repository ?: new \Wisewand\Repositories\Keyword_Repository();

		// Initialize Semantic Engine if OpenAI is configured
		if ( \Wisewand\Utils\OpenAI_Client::is_configured() ) {
			$api_key = \Wisewand\Utils\OpenAI_Client::get_stored_api_key();
			$openai_client = new \Wisewand\Utils\OpenAI_Client( $api_key, $logger );
			$this->semantic_engine = new \Wisewand\Utils\Semantic_Deduplication_Engine( $logger, $openai_client, $this->repository );
		}
	}

	public function save_keywords( $keywords_array, $auto_enrich = false, $source = 'manual', $seed_keyword = null, $language_code = 'en', $location_code = 2840 ) {
		// Start transaction for atomic operation
		$this->repository->start_transaction();

		try {
			// Ensure schema is up to date (Just-In-Time fix for missing column)
			$this->repository->ensure_embedding_column();

			$saved   = array();
			$skipped = array();
			$failed  = array();

			// Optimization: Batch generate embeddings to reduce API calls (1 call instead of N)
			$embeddings_map = array();
			if ( $this->semantic_engine && \Wisewand\Utils\OpenAI_Client::is_configured() ) {
				// Filter valid keywords for embedding
				$keywords_to_embed = array();
				foreach ( $keywords_array as $k ) {
					$k = trim( $k );
					if ( ! empty( $k ) && ! $this->keyword_exists( $k, $language_code ) ) {
						$keywords_to_embed[] = $k;
					}
				}

				if ( ! empty( $keywords_to_embed ) ) {
					$api_key = \Wisewand\Utils\OpenAI_Client::get_stored_api_key();
					$client  = new \Wisewand\Utils\OpenAI_Client( $api_key, $this->logger );
					
					// Batch call
					$batch_embeddings = $client->get_embeddings( $keywords_to_embed );
					
					if ( ! is_wp_error( $batch_embeddings ) && is_array( $batch_embeddings ) ) {
						// Map back to keywords (assuming order is preserved, which OpenAI guarantees)
						// But to be safe, we'll use array index mapping if keys are 0,1,2...
						// OpenAI returns list in order.
						$i = 0;
						foreach ( $keywords_to_embed as $kw ) {
							if ( isset( $batch_embeddings[ $i ] ) ) {
								$embeddings_map[ $kw ] = $batch_embeddings[ $i ];
							}
							$i++;
						}
					}
				}
			}

			foreach ( $keywords_array as $keyword ) {
				$keyword = trim( $keyword );
				if ( empty( $keyword ) ) {
					continue;
				}

				// CRITICAL FIX: Database UNIQUE constraint is ONLY 2 fields (keyword, language_code)
				// NOT 3 fields as originally implemented. Must match database schema exactly.
				// Schema: UNIQUE KEY keyword_language (keyword, language_code)
				// TODO: Phase 2 - migrate schema to 3 fields (keyword, language_code, location_code)
				if ( $this->keyword_exists( $keyword, $language_code ) ) {
					$skipped[] = $keyword;

					$this->logger->debug(
						'Keyword already exists - skipped duplicate',
						array(
							'keyword'       => $keyword,
							'language_code' => $language_code,
							'location_code' => $location_code,
						)
					);
					continue;
				}

				// Semantic Deduplication Check (if engine available)
				$embedding = null;
				$embedding_vector = $embeddings_map[ $keyword ] ?? null;

				if ( $this->semantic_engine && $embedding_vector ) {
					// Use pre-calculated vector from batch
					$semantic_duplicates = $this->semantic_engine->find_duplicates_by_vector( $embedding_vector, $keyword, $language_code );
					
					// If we found a "Duplicate" (very high similarity)
					if ( ! empty( $semantic_duplicates ) && $semantic_duplicates[0]['is_duplicate'] ) {
						$skipped[] = $keyword;
						$this->logger->info(
							'Skipped semantic duplicate',
							array(
								'keyword'  => $keyword,
								'match'    => $semantic_duplicates[0]['keyword'],
								'score'    => $semantic_duplicates[0]['score'],
							)
						);
						continue;
					}

					// Prepare embedding for storage
					$embedding = serialize( $embedding_vector );
				}

				$keyword_id = $this->save_keyword( $keyword, $source, $seed_keyword, $language_code, $location_code, $embedding );

				if ( is_wp_error( $keyword_id ) ) {
					// Track failure with detailed info
					$error_code = $keyword_id->get_error_code();
					$error_msg  = $keyword_id->get_error_message();

					$failed[] = array(
						'keyword'       => $keyword,
						'error'         => $error_msg,
						'error_code'    => $error_code,
						'language_code' => $language_code,
						'location_code' => $location_code,
					);



					// Different log level for duplicates vs real errors
					if ( $error_code === 'duplicate_keyword' ) {
						$this->logger->debug(
							'Duplicate keyword detected during batch save (pre-check missed)',
							array(
								'keyword'       => $keyword,
								'error'         => $error_msg,
								'language_code' => $language_code,
								'location_code' => $location_code,
							)
						);
					} else {
						$this->logger->warning(
							'Keyword save failed in batch',
							array(
								'keyword'       => $keyword,
								'error'         => $error_msg,
								'error_code'    => $error_code,
								'language_code' => $language_code,
								'location_code' => $location_code,
							)
						);
					}

					// Continue processing other keywords - don't abort entire batch
					continue;
				}

				$saved[] = $keyword;



				if ( $auto_enrich && $this->dataforseo_client ) {
					$this->enrich_keyword( $keyword_id, $language_code, $location_code );
				}
			}

			// Commit transaction if no critical errors
			$this->repository->commit();



			$this->logger->info(
				'Batch save keywords completed',
				array(
					'saved_count'   => count( $saved ),
					'skipped_count' => count( $skipped ),
					'failed_count'  => count( $failed ),
					'language_code' => $language_code,
				)
			);

		} catch ( \Exception $e ) {
			// Rollback on exception
			$this->repository->rollback();

			$this->logger->error(
				'Batch save keywords failed with exception',
				array(
					'error'         => $e->getMessage(),
					'language_code' => $language_code,
				)
			);

			return array(
				'saved'         => array(),
				'skipped'       => array(),
				'failed'        => array(
					array(
						'keyword' => 'batch',
						'error'   => $e->getMessage(),
					),
				),
				'saved_count'   => 0,
				'skipped_count' => 0,
				'failed_count'  => 1,
				'error'         => $e->getMessage(),
			);
		}

		return array(
			'saved'         => $saved,
			'skipped'       => $skipped,
			'failed'        => $failed,
			'saved_count'   => count( $saved ),
			'skipped_count' => count( $skipped ),
			'failed_count'  => count( $failed ),
		);
	}

	public function save_keyword( $keyword, $source = 'manual', $seed_keyword = null, $language_code = 'en', $location_code = 2840, $embedding = null ) {
		$user_id = get_current_user_id();

		$data = array(
			'keyword'        => $keyword,
			'keyword_source' => $source,
			'language_code'  => $language_code,
			'location_code'  => $location_code,
			'created_by'     => $user_id,
		);

		if ( $embedding ) {
			$data['embedding'] = $embedding;
		}

		if ( $seed_keyword ) {
			$data['seed_keyword'] = $seed_keyword;
			if ( $source === 'keyword_ideas' ) {
				$data['tags'] = 'keyword_ideas_' . sanitize_title( $seed_keyword );
			}
		}

		// Debug logging for batch save issues
		$this->logger->debug(
			'Attempting to save keyword',
			array(
				'keyword'       => $keyword,
				'language_code' => $language_code,
				'location_code' => $location_code,
				'user_id'       => $user_id,
				'source'        => $source,
				'data'          => $data,
			)
		);

		$keyword_id = $this->repository->save_keyword( $data );

		if ( ! $keyword_id ) {
			// Enhanced error logging to detect UNIQUE constraint violations
			$error_message = $this->repository->get_last_error();
			$is_duplicate  = stripos( $error_message, 'duplicate' ) !== false || stripos( $error_message, '1062' ) !== false;

			$this->logger->error(
				'Failed to save keyword',
				array(
					'keyword'       => $keyword,
					'language_code' => $language_code,
					'location_code' => $location_code,
					'error'         => $error_message,
					'is_duplicate'  => $is_duplicate,
					'data'          => $data,
				)
			);

			// Return specific error for duplicates
			if ( $is_duplicate ) {
				return new WP_Error(
					'duplicate_keyword',
					sprintf(
						'Keyword "%s" already exists for language "%s" and location "%d"',
						$keyword,
						$language_code,
						$location_code
					)
				);
			}

			return new WP_Error( 'db_error', 'Failed to save keyword: ' . $error_message );
		}

		return $keyword_id;
	}

	public function enrich_keyword( $keyword_id, $language_code = null, $location_code = null ) {
		$keyword_row = $this->repository->get_keyword( $keyword_id );
		// Convert object to array for compatibility
		$keyword_row = $keyword_row ? (array) $keyword_row : null;

		if ( ! $keyword_row ) {
			$this->logger->error( 'Keyword not found for enrichment', array( 'keyword_id' => $keyword_id ) );
			return false;
		}

		if ( ! $this->dataforseo_client ) {
			$this->logger->error( 'DataForSEO client not available for enrichment' );
			return false;
		}

		try {
			$keyword = $keyword_row['keyword'];

			// Use language/location from params or from keyword row
			$language_code = $language_code ?? $keyword_row['language_code'] ?? 'en';
			$location_code = $location_code ?? $keyword_row['location_code'] ?? 2840;

			$this->logger->debug(
				'Starting keyword enrichment',
				array(
					'keyword'       => $keyword,
					'language_code' => $language_code,
					'location_code' => $location_code,
				)
			);

			// Use search_volume endpoint for complete data
			$volume_data = array(
				array(
					'location_code' => $location_code,
					'language_code' => $language_code,
					'keywords'      => array( $keyword ),
				),
			);

			$response = $this->dataforseo_client->request( 'POST', '/keywords_data/google_ads/search_volume/live', $volume_data );

			if ( is_wp_error( $response ) ) {
				throw new \Exception( $response->get_error_message() );
			}

			$task    = $response[0] ?? array();
			$items   = $task['result'] ?? array();
			$kw_data = $items[0] ?? array();

			$this->logger->debug(
				'Enrichment API response',
				array(
					'keyword'               => $keyword,
					'task_status'           => $task['status_message'] ?? 'unknown',
					'items_count'           => count( $items ),
					'kw_data_keys'          => array_keys( $kw_data ),
					'raw_volume'            => $kw_data['search_volume'] ?? 'missing',
					'raw_competition'       => $kw_data['competition'] ?? 'missing',
					'raw_competition_index' => $kw_data['competition_index'] ?? 'missing',
					'full_kw_data'          => $kw_data, // Complete response for debugging
				)
			);

			// Extract and validate data
			$search_volume = (int) ( $kw_data['search_volume'] ?? 0 );

			// Try multiple sources for difficulty (DataForSEO provides different fields)
			$difficulty = null;

			// Method 1: competition_index (0-100, most reliable)
			if ( isset( $kw_data['competition_index'] ) && is_numeric( $kw_data['competition_index'] ) ) {
				$difficulty = (int) $kw_data['competition_index'];
			}
			// Method 2: competition (LOW/MEDIUM/HIGH) - convert to scale
			elseif ( isset( $kw_data['competition'] ) ) {
				$comp_map   = array(
					'LOW'    => 25,
					'MEDIUM' => 50,
					'HIGH'   => 75,
				);
				$difficulty = $comp_map[ strtoupper( $kw_data['competition'] ) ] ?? null;
			}
			// Method 3: cpc (cost per click) - estimate difficulty from bid prices
			elseif ( isset( $kw_data['cpc'] ) && $kw_data['cpc'] > 0 ) {
				// High CPC usually means high competition
				// $0-1 = low (20), $1-3 = medium (50), $3+ = high (70)
				$cpc = (float) $kw_data['cpc'];
				if ( $cpc >= 3 ) {
					$difficulty = 70;
				} elseif ( $cpc >= 1 ) {
					$difficulty = 50;
				} else {
					$difficulty = 20;
				}
			}

			// Extract CPC (cost per click)
			$cpc = null;
			if ( isset( $kw_data['cpc'] ) && is_numeric( $kw_data['cpc'] ) ) {
				$cpc = round( (float) $kw_data['cpc'], 2 );
			}

			// Extract Competition (LOW/MEDIUM/HIGH text value)
			$competition = null;
			if ( isset( $kw_data['competition'] ) && is_string( $kw_data['competition'] ) ) {
				$competition = strtoupper( $kw_data['competition'] );
				if ( ! in_array( $competition, array( 'LOW', 'MEDIUM', 'HIGH' ) ) ) {
					$competition = null; // Invalid value
				}
			}

			$this->logger->debug(
				'Keyword enrichment data extracted',
				array(
					'keyword'     => $keyword,
					'volume'      => $search_volume,
					'difficulty'  => $difficulty,
					'cpc'         => $cpc,
					'competition' => $competition,
				)
			);

			$search_intent = $this->detect_search_intent( $keyword );
			$content_type  = $this->detect_content_type( $keyword );
			$priority      = $this->calculate_priority( $search_volume, $difficulty );

			// Update database with all enriched data
			$update_result = $this->repository->update_keyword(
				$keyword_id,
				array(
					'search_volume'  => $search_volume,
					'difficulty'     => $difficulty,
					'cpc'            => $cpc,
					'competition'    => $competition,
					'search_intent'  => $search_intent,
					'content_type'   => $content_type,
					'priority_score' => $priority,
					'is_enriched'    => 1,
				)
			);

			if ( ! $update_result ) {
				throw new \Exception( 'Database update failed: ' . $this->repository->get_last_error() );
			}

			$this->logger->info(
				'Keyword enriched successfully',
				array(
					'keyword'    => $keyword,
					'volume'     => $search_volume,
					'difficulty' => $difficulty,
					'priority'   => $priority,
				)
			);

			return true;

		} catch ( \Exception $e ) {
			$this->logger->error(
				'Failed to enrich keyword',
				array(
					'keyword_id' => $keyword_id,
					'keyword'    => $keyword_row['keyword'] ?? 'unknown',
					'error'      => $e->getMessage(),
				)
			);
			return false;
		}
	}

	public function get_all_saved_keywords() {
		return $this->repository->get_all_keywords();
	}

	public function delete_keyword( $keyword_id ) {
		$result = $this->repository->delete_keyword( $keyword_id );

		if ( $result === false ) {
			return new WP_Error( 'db_error', 'Failed to delete keyword' );
		}

		return true;
	}

	public function bulk_delete( $keyword_ids ) {
		return $this->repository->bulk_delete( $keyword_ids );
	}

	/**
	 * Check if keyword exists in database
	 *
	 * CRITICAL: Must match database UNIQUE constraint exactly:
	 * Database Schema: UNIQUE KEY keyword_language (keyword, language_code)
	 *
	 * @since 1.0.0
	 * @since 1.3.2 FIXED - Database has 2-field constraint (keyword, language_code) NOT 3 fields
	 *               Previous code incorrectly checked 3 fields causing batch save failures
	 *
	 * @param string   $keyword       Keyword to check.
	 * @param string   $language_code Language code (e.g., 'en'). Default null.
	 * @param int|null $location_code IGNORED - kept for backward compatibility but NOT part of UNIQUE constraint.
	 *
	 * @return bool True if keyword exists, false otherwise.
	 */
	public function keyword_exists( $keyword, $language_code = null, $location_code = null ) {
		return $this->repository->keyword_exists( $keyword, $language_code );
	}

	/**
	 * Get keyword ID from database
	 *
	 * CRITICAL: Must match database UNIQUE constraint exactly:
	 * Database Schema: UNIQUE KEY keyword_language (keyword, language_code)
	 *
	 * @since 1.0.0
	 * @since 1.3.2 FIXED - Database has 2-field constraint (keyword, language_code) NOT 3 fields
	 *
	 * @param string   $keyword       Keyword to find.
	 * @param string   $language_code Language code (e.g., 'en'). Default null.
	 * @param int|null $location_code IGNORED - kept for backward compatibility but NOT part of UNIQUE constraint.
	 *
	 * @return int|null Keyword ID if found, null otherwise.
	 */
	public function get_keyword_id( $keyword, $language_code = null, $location_code = null ) {
		return $this->repository->get_keyword_id( $keyword, $language_code );
	}

	private function detect_search_intent( $keyword ) {
		$keyword_lower = strtolower( $keyword );

		if ( preg_match( '/\b(buy|purchase|price|cheap|deal|discount|coupon|shop|store)\b/i', $keyword_lower ) ) {
			return 'transactional';
		}

		if ( preg_match( '/\b(best|top|review|vs|versus|compare|comparison)\b/i', $keyword_lower ) ) {
			return 'commercial';
		}

		if ( preg_match( '/\b(what|how|why|when|where|guide|tutorial|tips)\b/i', $keyword_lower ) ) {
			return 'informational';
		}

		return 'informational';
	}

	private function detect_content_type( $keyword ) {
		$keyword_lower = strtolower( $keyword );

		if ( preg_match( '/\b(best|top \d+|top ten)\b/i', $keyword_lower ) ) {
			return 'listicle';
		}

		if ( preg_match( '/\b(review|vs|versus|compare)\b/i', $keyword_lower ) ) {
			return 'comparison';
		}

		return 'blog';
	}

	private function calculate_priority( $volume, $difficulty ) {
		// Ensure numeric types to prevent string*int errors
		$volume     = is_numeric( $volume ) ? (int) $volume : 0;
		$difficulty = is_numeric( $difficulty ) ? (int) $difficulty : null;

		$score = 0;

		if ( $volume > 10000 ) {
			$score += 40;
		} elseif ( $volume > 5000 ) {
			$score += 30;
		} elseif ( $volume > 2000 ) {
			$score += 20;
		} elseif ( $volume > 500 ) {
			$score += 10;
		}

		if ( $difficulty !== null ) {
			if ( $difficulty < 30 ) {
				$score += 30;
			} elseif ( $difficulty < 50 ) {
				$score += 20;
			} elseif ( $difficulty < 70 ) {
				$score += 10;
			}
		}

		return $score;
	}

	public function save_keyword_ideas( $keyword_ideas, $seed_keyword ) {
		global $wpdb;

		$this->logger->info(
			'Saving keyword ideas',
			array(
				'seed'        => $seed_keyword,
				'ideas_count' => count( $keyword_ideas ),
			)
		);

		$saved   = 0;
		$skipped = 0;
		$failed  = array();

		// Start transaction for atomic operation
		$this->repository->start_transaction();

		try {
			foreach ( $keyword_ideas as $idea ) {
				$idea_language = $idea['language_code'] ?? 'en';
				$idea_location = $idea['location_code'] ?? 2840;

				// CRITICAL FIX: Database UNIQUE constraint is ONLY 2 fields (keyword, language_code)
				// NOT 3 fields as originally implemented. Must match database schema exactly.
				// Schema: UNIQUE KEY keyword_language (keyword, language_code)
				// TODO: Phase 2 - migrate schema to 3 fields (keyword, language_code, location_code)
				if ( $this->keyword_exists( $idea['keyword'], $idea_language ) ) {
					++$skipped;
					$this->logger->debug(
						'Keyword already exists - skipped duplicate',
						array(
							'keyword'       => $idea['keyword'],
							'language_code' => $idea_language,
							'location_code' => $idea_location,
						)
					);
					continue;
				}

				$insert_data = array(
					'keyword'        => $idea['keyword'],
					'keyword_source' => 'keyword_ideas',
					'seed_keyword'   => $seed_keyword,
					'search_volume'  => $idea['search_volume'] ?? 0,
					'difficulty'     => $idea['difficulty'] ?? null,
					'search_intent'  => $idea['search_intent'] ?? 'informational',
					'content_type'   => $idea['content_type'] ?? 'blog',
					'priority_score' => $idea['priority_score'] ?? 0,
					'is_enriched'    => 1,
					'tags'           => 'keyword_ideas_' . sanitize_title( $seed_keyword ),
					'created_by'     => get_current_user_id(),
					'language_code'  => $idea['language_code'] ?? 'en',
					'location_code'  => $idea['location_code'] ?? 2840,
				);

				$this->logger->debug(
					'Inserting keyword idea',
					array(
						'keyword'    => $idea['keyword'],
						'volume'     => $insert_data['search_volume'],
						'difficulty' => $insert_data['difficulty'],
					)
				);

				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom plugin table insert for keyword ideas
				$result = $this->repository->save_keyword( $insert_data );

				if ( $result ) {
					++$saved;
				} else {
					// Track failure with details
					$failed[] = array(
						'keyword' => $idea['keyword'],
						'error'   => $this->repository->get_last_error(),
					);

					$this->logger->error(
						'Failed to insert keyword',
						array(
							'keyword' => $idea['keyword'],
							'error'   => $this->repository->get_last_error(),
						)
					);
				}
			}

			// Commit transaction
			$this->repository->commit();

			$this->logger->info(
				'Keyword ideas saved',
				array(
					'saved'   => $saved,
					'skipped' => $skipped,
					'failed'  => count( $failed ),
				)
			);

		} catch ( \Exception $e ) {
			// Rollback on exception
			$this->repository->rollback();

			$this->logger->error(
				'Keyword ideas save failed with exception',
				array(
					'error' => $e->getMessage(),
					'seed'  => $seed_keyword,
				)
			);

			return array(
				'saved'   => 0,
				'skipped' => 0,
				'failed'  => array(
					array(
						'keyword' => 'batch',
						'error'   => $e->getMessage(),
					),
				),
				'error'   => $e->getMessage(),
			);
		}

		return array(
			'saved'   => $saved,
			'skipped' => $skipped,
			'failed'  => $failed,
		);
	}

	public function update_keyword( $keyword_id, $updates ) {
		$allowed_fields = array( 'keyword', 'search_volume', 'difficulty', 'search_intent', 'content_type', 'priority_score', 'notes', 'tags' );
		$data           = array();

		foreach ( $allowed_fields as $field ) {
			if ( isset( $updates[ $field ] ) ) {
				$data[ $field ] = $updates[ $field ];
			}
		}

		if ( empty( $data ) ) {
			return true;
		}

		return $this->repository->update_keyword( $keyword_id, $data );
	}

	public function get_statistics() {
		return $this->repository->get_statistics();
	}
}
