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

namespace Wisewand\Repositories;

/**
 * Class Competitor_Repository
 *
 * Handles database interactions for Competitor Analysis.
 */
class Competitor_Repository extends Base_Repository {

	/**
	 * Competitor keywords table name.
	 *
	 * @var string
	 */
	protected $keywords_table;

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

	/**
	 * Save analysis to database.
	 *
	 * @param array $data Analysis data.
	 * @return int|false Insert ID on success, false on failure.
	 */
	public function save_analysis( $data ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $this->wpdb->insert(
			$this->table_name,
			$data
		);

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

	/**
	 * Update analysis record.
	 *
	 * @param int   $id   Analysis ID (primary key) or analysis_id string.
	 * @param array $data Data to update.
	 * @return bool Success.
	 */
	public function update_analysis( $id, $data ) {
		$where = is_numeric( $id ) ? array( 'id' => $id ) : array( 'analysis_id' => $id );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$result = $this->wpdb->update(
			$this->table_name,
			$data,
			$where
		);

		return $result !== false;
	}

	/**
	 * Save competitor keywords.
	 *
	 * @param array $keywords List of keyword data arrays.
	 * @return void
	 */
	public function save_competitor_keywords( $keywords ) {
		foreach ( $keywords as $data ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$this->wpdb->insert(
				$this->keywords_table,
				$data
			);
		}
	}

	/**
	 * Get analysis history for a user domain.
	 *
	 * @param string $user_domain User domain.
	 * @param int    $limit       Limit results.
	 * @return array List of analysis records.
	 */
	public function get_analysis_history( $user_domain, $limit = 10 ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM %i WHERE user_domain = %s ORDER BY created_at DESC LIMIT %d",
				$this->table_name,
				$user_domain,
				$limit
			)
		);
	}

	/**
	 * Get keywords for a specific analysis.
	 *
	 * @param string $analysis_id Analysis ID.
	 * @return array List of keywords.
	 */
	public function get_analysis_keywords( $analysis_id ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return $this->wpdb->get_results(
			$this->wpdb->prepare(
				"SELECT * FROM %i WHERE analysis_id = %s ORDER BY priority_score DESC",
				$this->keywords_table,
				$analysis_id
			)
		);
	}

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

		$analysis_sql = "CREATE TABLE IF NOT EXISTS {$this->table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            analysis_id varchar(64) NOT NULL,
            user_domain varchar(255) NOT NULL,
            competitor_domains text NOT NULL,
            language_code varchar(10) NOT NULL DEFAULT 'en',
            location_code int(10) NOT NULL DEFAULT 2840,
            analysis_type enum('gap','best_performers','hybrid') DEFAULT 'hybrid',
            total_keywords_found int(10) DEFAULT 0,
            keywords_saved int(10) DEFAULT 0,
            filters_used longtext,
            analysis_data longtext,
            created_by int(10) DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY analysis_id (analysis_id),
            KEY user_domain (user_domain),
            KEY created_at (created_at DESC)
        ) $charset_collate;";

		$keywords_sql = "CREATE TABLE IF NOT EXISTS {$this->keywords_table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            analysis_id varchar(64) NOT NULL,
            keyword varchar(255) NOT NULL,
            search_volume int(10) DEFAULT 0,
            difficulty int(3) DEFAULT NULL,
            competitor_domain varchar(255) NOT NULL,
            competitor_position int(3) DEFAULT NULL,
            user_position int(3) DEFAULT NULL,
            gap_type enum('missing','outranked','both') DEFAULT 'missing',
            relevance_score decimal(5,2) DEFAULT 0,
            priority_score decimal(5,2) DEFAULT 0,
            serp_data longtext,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY analysis_id (analysis_id),
            KEY keyword (keyword),
            KEY priority_score (priority_score DESC),
            KEY gap_type (gap_type),
            KEY competitor_domain (competitor_domain)
        ) $charset_collate;";

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