<?php
/**
 * Database Schema Definition
 *
 * Complete CREATE TABLE statements with all migrations (1-8) merged.
 * This is the single source of truth for the database structure.
 *
 * @package Wisewand
 * @since 1.0.2
 * @license GPL-2.0-or-later
 */

namespace Wisewand\Database;

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

/**
 * Database schema manager
 *
 * Provides complete CREATE TABLE statements for all Wisewand tables
 * with all migrations merged into base schema.
 *
 * @since 1.0.2
 */
class Database_Schema {

	/**
	 * Current database schema version
	 *
	 * Increment this when schema changes require migration.
	 *
	 * @since 1.0.2
	 * @var string
	 */
	const SCHEMA_VERSION = '1.4.0';

	/**
	 * Get charset collate string
	 *
	 * Force utf8mb4 for MariaDB 11.4+ compatibility.
	 *
	 * @since 1.0.2
	 * @return string Charset collate SQL fragment.
	 */
	public static function get_charset_collate() {
		return 'DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci';
	}

	/**
	 * Get editorial calendar table schema
	 *
	 * Merged migrations:
	 * - Migration 1: wordpress_categories column
	 * - Migration 4: idx_cron_status_date and idx_wisewand_article_id indexes
	 * - Migration 5: wordpress_status ENUM includes imported_draft, imported_published
	 *
	 * @since 1.0.2
	 * @param string $table_name Full table name with prefix.
	 * @return string CREATE TABLE SQL statement.
	 */
	public static function get_editorial_calendar_schema( $table_name ) {
		$charset_collate = self::get_charset_collate();

		return "CREATE TABLE IF NOT EXISTS {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			keyword varchar(255) NOT NULL,
			keyword_source enum('current_ranking','manual','discovery','saved') DEFAULT 'manual',
			search_volume int(10) DEFAULT 0,
			current_position int(3) DEFAULT NULL,
			search_intent varchar(32) DEFAULT 'informational',
			difficulty int(3) DEFAULT NULL,
			priority_score decimal(5,2) DEFAULT 0,
			publication_action enum('create','update','skip') DEFAULT 'create',
			existing_post_id bigint(20) DEFAULT NULL,
			wisewand_payload longtext NOT NULL,
			wisewand_article_id varchar(100) DEFAULT NULL,
			wisewand_project_id varchar(100) DEFAULT NULL,
			wisewand_persona_id varchar(100) DEFAULT NULL,
			wisewand_lang varchar(10) DEFAULT 'en',
			wisewand_type varchar(50) DEFAULT 'blog',
			wisewand_length varchar(20) DEFAULT 'auto',
			wisewand_status enum('not_created','creating','generating','ready','failed') DEFAULT 'not_created',
			wisewand_error text DEFAULT NULL,
			wisewand_retry_count int(3) DEFAULT 0,
			wordpress_post_id bigint(20) DEFAULT NULL,
			wordpress_status enum('scheduled','importing','imported_draft','imported_published','failed') DEFAULT 'scheduled',
			wordpress_error text DEFAULT NULL,
			publication_date date NOT NULL,
			publication_time time DEFAULT NULL,
			publication_time_range varchar(20) DEFAULT 'random',
			publish_as_draft tinyint(1) DEFAULT 1,
			auto_publish tinyint(1) DEFAULT 0,
			content_merge_strategy enum('replace','append','prepend') DEFAULT 'replace',
			target_post_type varchar(20) DEFAULT 'post',
			target_author int(10) DEFAULT NULL,
			target_category varchar(255) DEFAULT NULL,
			target_tags text DEFAULT NULL,
			wordpress_categories text DEFAULT NULL COMMENT 'JSON array of category IDs for WordPress import',
			scheduled_at datetime DEFAULT CURRENT_TIMESTAMP,
			generation_triggered_at datetime DEFAULT NULL,
			generation_completed_at datetime DEFAULT NULL,
			import_started_at datetime DEFAULT NULL,
			published_at datetime DEFAULT NULL,
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			notes text DEFAULT NULL,
			user_modified tinyint(1) DEFAULT 0,
			created_by int(10) DEFAULT NULL,
			PRIMARY KEY (id),
			KEY keyword (keyword),
			KEY publication_date (publication_date),
			KEY wisewand_status (wisewand_status),
			KEY wordpress_status (wordpress_status),
			KEY keyword_source (keyword_source),
			KEY publication_action (publication_action),
			KEY priority_score (priority_score DESC),
			KEY idx_cron_status_date (wisewand_status, publication_date, wordpress_status),
			KEY idx_wisewand_article_id (wisewand_article_id),
			UNIQUE KEY keyword_date (keyword, publication_date)
		) {$charset_collate};";
	}

	/**
	 * Get saved keywords table schema
	 *
	 * Merged migrations:
	 * - Migration 2: cluster_id, is_cluster_primary, cluster_similarity_score
	 * - Migration 3: page_url, position_abs, wordpress_post_id, auto_categorized_from_url,
	 *                url_match_confidence, url_matched_at
	 *
	 * @since 1.0.2
	 * @param string $table_name Full table name with prefix.
	 * @return string CREATE TABLE SQL statement.
	 */
	public static function get_saved_keywords_schema( $table_name ) {
		$charset_collate = self::get_charset_collate();

		return "CREATE TABLE IF NOT EXISTS {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			cluster_id bigint(20) DEFAULT NULL COMMENT 'Reference to cluster if part of one',
			keyword varchar(255) NOT NULL,
			keyword_source enum('manual','keyword_ideas','competitor_spy') DEFAULT 'manual',
			seed_keyword varchar(255) DEFAULT NULL,
			language_code varchar(10) DEFAULT 'en',
			location_code int(10) DEFAULT 2840,
			search_volume int(10) DEFAULT 0,
			difficulty int(3) DEFAULT NULL,
			search_intent varchar(32) DEFAULT 'informational',
			content_type varchar(32) DEFAULT 'blog',
			priority_score decimal(5,2) DEFAULT 0,
			is_enriched tinyint(1) DEFAULT 0,
			is_cluster_primary tinyint(1) DEFAULT 0 COMMENT '1 if this is the primary keyword in its cluster',
			cluster_similarity_score decimal(5,2) DEFAULT NULL COMMENT 'Avg similarity to cluster',
			page_url text DEFAULT NULL COMMENT 'Ranking URL from SEO analysis',
			position_abs int(3) DEFAULT NULL COMMENT 'Current ranking position',
			wordpress_post_id bigint(20) DEFAULT NULL COMMENT 'Matched WordPress post ID',
			suggested_author_id int(10) DEFAULT NULL COMMENT 'AI-suggested author ID',
			suggested_categories text DEFAULT NULL COMMENT 'JSON array of AI-suggested category IDs',
			ai_matched_at datetime DEFAULT NULL COMMENT 'When AI matching was performed',
			ai_model_used varchar(50) DEFAULT NULL COMMENT 'OpenAI model used for matching',
			auto_categorized_from_url tinyint(1) DEFAULT 0 COMMENT '1 if categories were auto-detected from URL',
			url_match_confidence decimal(3,2) DEFAULT NULL COMMENT 'URL matching confidence score (0.00-1.00)',
			url_matched_at datetime DEFAULT NULL COMMENT 'When URL matching was performed',
			tags text DEFAULT NULL,
			notes text DEFAULT NULL,
			created_by int(10) DEFAULT NULL,
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY keyword_language (keyword, language_code, location_code),
			KEY keyword_source (keyword_source),
			KEY is_enriched (is_enriched),
			KEY language_code (language_code),
			KEY priority_score (priority_score DESC),
			KEY created_at (created_at DESC),
			KEY cluster_id (cluster_id),
			KEY is_cluster_primary (is_cluster_primary),
			KEY wordpress_post_id (wordpress_post_id),
			KEY position_abs (position_abs),
			KEY auto_categorized_from_url (auto_categorized_from_url)
		) {$charset_collate};";
	}

	/**
	 * Get RSS feeds table schema
	 *
	 * Merged migrations:
	 * - Migration 6: enabled_for_site, use_custom_publication_schedule,
	 *                publication_time_range, publication_allowed_days, custom_interval_hours
	 * - Migration 7: sync_status, target_language
	 *
	 * @since 1.0.2
	 * @param string $table_name Full table name with prefix.
	 * @return string CREATE TABLE SQL statement.
	 */
	public static function get_rss_feeds_schema( $table_name ) {
		$charset_collate = self::get_charset_collate();

		return "CREATE TABLE IF NOT EXISTS {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			wisewand_feed_id varchar(255) NOT NULL,
			feed_name varchar(255) NOT NULL,
			feed_url text,
			source_url text,
			feed_icon text,
			feed_color varchar(7),
			wisewand_status varchar(50),
			wisewand_frequency varchar(50),
			wisewand_limit int(10) DEFAULT 1,
			wisewand_cost decimal(10,2) DEFAULT 0,
			wisewand_project_id varchar(255),
			enabled_for_site tinyint(1) DEFAULT 0 COMMENT 'Whether feed is activated for this WordPress site',
			sync_status varchar(20) DEFAULT 'enabled' COMMENT 'Feed sync status: enabled, disabled, paused',
			last_fetched_at datetime DEFAULT NULL,
			auto_import_enabled tinyint(1) DEFAULT 1,
			import_frequency varchar(50) DEFAULT 'every_6_hours',
			import_schedule_type enum('hourly','every_6_hours','daily','custom') DEFAULT 'every_6_hours',
			import_schedule_time time DEFAULT NULL,
			import_schedule_days varchar(50) DEFAULT NULL,
			custom_interval_hours int DEFAULT 12 COMMENT 'Custom sync interval in hours',
			max_articles_per_sync int(10) DEFAULT 10,
			import_status_filter varchar(50) DEFAULT 'success',
			target_post_status varchar(20) DEFAULT 'draft',
			publish_mode varchar(20) DEFAULT 'global' COMMENT 'Publish mode: global, schedule, publish, draft',
			target_author bigint(20) unsigned DEFAULT NULL,
			target_categories text,
			target_language varchar(10) DEFAULT NULL COMMENT 'WPML target language for imported articles',
			target_tags text,
			use_ai_category_matching tinyint(1) DEFAULT 0,
			use_custom_publication_schedule tinyint(1) DEFAULT 0 COMMENT 'Use custom publication schedule vs global',
			publication_time_range varchar(20) DEFAULT NULL COMMENT 'morning/afternoon/evening/random',
			publication_allowed_days varchar(50) DEFAULT NULL COMMENT 'JSON array of allowed days (1=Monday)',
			total_articles int(10) DEFAULT 0,
			articles_success int(10) DEFAULT 0,
			articles_error int(10) DEFAULT 0,
			articles_imported int(10) DEFAULT 0,
			articles_pending int(10) DEFAULT 0,
			articles_failed int(10) DEFAULT 0,
			last_sync_at datetime DEFAULT NULL,
			last_import_at datetime DEFAULT NULL,
			next_sync_at datetime DEFAULT NULL,
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			created_by bigint(20) unsigned DEFAULT NULL,
			PRIMARY KEY (id),
			UNIQUE KEY wisewand_feed_id (wisewand_feed_id),
			KEY auto_import_enabled (auto_import_enabled),
			KEY next_sync_at (next_sync_at),
			KEY wisewand_project_id (wisewand_project_id),
			KEY enabled_for_site (enabled_for_site),
			KEY sync_status (sync_status)
		) {$charset_collate};";
	}

	/**
	 * Get RSS articles table schema
	 *
	 * Merged migrations:
	 * - Migration 6: scheduled_publish_time column and index
	 * - Migration 8: import_status ENUM includes 'deleted'
	 *
	 * @since 1.0.2
	 * @param string $table_name Full table name with prefix.
	 * @return string CREATE TABLE SQL statement.
	 */
	public static function get_rss_articles_schema( $table_name ) {
		$charset_collate = self::get_charset_collate();

		return "CREATE TABLE IF NOT EXISTS {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			feed_id bigint(20) unsigned NOT NULL,
			feed_wisewand_id varchar(255) NOT NULL,
			wisewand_article_id varchar(255) NOT NULL,
			article_title varchar(500),
			article_subject text,
			article_status varchar(50),
			article_created_at datetime DEFAULT NULL,
			article_updated_at datetime DEFAULT NULL,
			wisewand_is_published tinyint(1) DEFAULT 0,
			has_cover_image tinyint(1) DEFAULT 0,
			last_output_id varchar(100) DEFAULT NULL COMMENT 'Cached output ID from discovery (eliminates redundant API calls)',
			import_status enum('pending','importing','imported','failed','skipped','deleted') DEFAULT 'pending',
			wordpress_post_id bigint(20) unsigned DEFAULT NULL,
			wordpress_post_status varchar(20),
			imported_at datetime DEFAULT NULL,
			scheduled_publish_time datetime DEFAULT NULL COMMENT 'When article should be published (respects time/day settings)',
			import_error text,
			import_attempts int(10) DEFAULT 0,
			last_import_attempt_at datetime DEFAULT NULL,
			discovered_at datetime DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY unique_article (wisewand_article_id),
			KEY feed_id (feed_id),
			KEY feed_wisewand_id (feed_wisewand_id),
			KEY import_status (import_status),
			KEY wordpress_post_id (wordpress_post_id),
			KEY article_status (article_status),
			KEY scheduled_publish_time (scheduled_publish_time)
		) {$charset_collate};";
	}

	// ======================================================================
	// CORE INFRASTRUCTURE TABLES
	// ======================================================================

	/**
	 * Get logs table schema
	 *
	 * System logging table for tracking operations, errors, and API calls.
	 *
	 * @since 1.0.4
	 * @param string $table_name Full table name with prefix.
	 * @return string CREATE TABLE SQL statement.
	 */
	public static function get_logs_schema( $table_name ) {
		$charset_collate = self::get_charset_collate();

		return "CREATE TABLE IF NOT EXISTS {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			request_id varchar(64) NOT NULL DEFAULT '',
			timestamp datetime DEFAULT CURRENT_TIMESTAMP,
			level varchar(20) NOT NULL DEFAULT 'info',
			message text NOT NULL,
			context longtext,
			ip_address varchar(45) DEFAULT '',
			user_agent varchar(500) DEFAULT '',
			endpoint varchar(100) DEFAULT '',
			method varchar(10) DEFAULT '',
			status_code int(3) DEFAULT NULL,
			PRIMARY KEY (id),
			KEY request_id (request_id),
			KEY timestamp (timestamp),
			KEY level (level),
			KEY ip_address (ip_address)
		) {$charset_collate};";
	}

	/**
	 * Get rate limits table schema
	 *
	 * Rate limiting tracking for API endpoints and IP addresses.
	 *
	 * @since 1.0.4
	 * @param string $table_name Full table name with prefix.
	 * @return string CREATE TABLE SQL statement.
	 */
	public static function get_rate_limits_schema( $table_name ) {
		$charset_collate = self::get_charset_collate();

		return "CREATE TABLE IF NOT EXISTS {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			ip_address varchar(45) NOT NULL DEFAULT '',
			endpoint varchar(100) NOT NULL DEFAULT '',
			requests_count int(10) unsigned NOT NULL DEFAULT 1,
			window_start datetime NOT NULL,
			window_end datetime NOT NULL,
			PRIMARY KEY (id),
			KEY ip_endpoint_window (ip_address, endpoint, window_start),
			KEY window_end (window_end)
		) {$charset_collate};";
	}

	/**
	 * Get pull queue table schema
	 *
	 * Queue for managing retry logic and async operations.
	 *
	 * @since 1.0.4
	 * @param string $table_name Full table name with prefix.
	 * @return string CREATE TABLE SQL statement.
	 */
	public static function get_pull_queue_schema( $table_name ) {
		$charset_collate = self::get_charset_collate();

		return "CREATE TABLE IF NOT EXISTS {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			article_id varchar(100) NOT NULL DEFAULT '',
			status varchar(20) NOT NULL DEFAULT 'pending',
			attempts int(5) unsigned NOT NULL DEFAULT 0,
			max_attempts int(5) unsigned NOT NULL DEFAULT 12,
			next_attempt datetime DEFAULT NULL,
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			error_message text,
			meta longtext,
			PRIMARY KEY (id),
			KEY article_id (article_id),
			KEY status (status),
			KEY next_attempt (next_attempt)
		) {$charset_collate};";
	}

	// ======================================================================
	// SEO ANALYSIS TABLES
	// ======================================================================

	/**
	 * Get SEO keywords table schema
	 *
	 * Keyword analysis and ranking data from SEO audit.
	 *
	 * @since 1.0.4
	 * @param string $table_name Full table name with prefix.
	 * @return string CREATE TABLE SQL statement.
	 */
	public static function get_seo_keywords_schema( $table_name ) {
		$charset_collate = self::get_charset_collate();

		return "CREATE TABLE IF NOT EXISTS {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			domain varchar(255) NOT NULL,
			language_code varchar(10) NOT NULL DEFAULT 'en',
			location_code int(10) NOT NULL DEFAULT 2840,
			keyword varchar(255) NOT NULL,
			search_volume int(10) DEFAULT NULL,
			difficulty int(3) DEFAULT NULL,
			trend_json longtext,
			position_abs int(3) DEFAULT NULL,
			position_grp int(3) DEFAULT NULL,
			page_url text,
			detected_language varchar(10) DEFAULT NULL,
			intent varchar(32) DEFAULT NULL,
			priority_score decimal(5,2) DEFAULT NULL,
			bucket varchar(24) DEFAULT NULL,
			source enum('ranked','site_idea','related','overview') DEFAULT 'ranked',
			fetched_at datetime DEFAULT CURRENT_TIMESTAMP,
			expires_at datetime DEFAULT NULL,
			PRIMARY KEY (id),
			UNIQUE KEY domain_lang_keyword (domain, language_code, location_code, keyword),
			KEY domain_lang_loc (domain, language_code, location_code),
			KEY expires_at (expires_at),
			KEY priority_score (priority_score DESC),
			KEY bucket (bucket)
		) {$charset_collate};";
	}

	/**
	 * Get SEO pages table schema
	 *
	 * Page-level ranking metrics and keyword distribution.
	 *
	 * @since 1.0.4
	 * @param string $table_name Full table name with prefix.
	 * @return string CREATE TABLE SQL statement.
	 */
	public static function get_seo_pages_schema( $table_name ) {
		$charset_collate = self::get_charset_collate();

		return "CREATE TABLE IF NOT EXISTS {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			domain varchar(255) NOT NULL,
			language_code varchar(10) NOT NULL DEFAULT 'en',
			location_code int(10) NOT NULL DEFAULT 2840,
			url text NOT NULL,
			pos_1 int(10) DEFAULT 0,
			pos_2_3 int(10) DEFAULT 0,
			pos_4_10 int(10) DEFAULT 0,
			pos_11_20 int(10) DEFAULT 0,
			keywords_count int(10) DEFAULT 0,
			etv decimal(10,2) DEFAULT 0,
			fetched_at datetime DEFAULT CURRENT_TIMESTAMP,
			expires_at datetime DEFAULT NULL,
			PRIMARY KEY (id),
			UNIQUE KEY domain_lang_url (domain, language_code, location_code, url(100)),
			KEY domain_lang_loc (domain, language_code, location_code),
			KEY expires_at (expires_at),
			KEY etv (etv DESC)
		) {$charset_collate};";
	}

	/**
	 * Get SEO settings table schema
	 *
	 * Per-domain SEO analysis configuration and preferences.
	 *
	 * @since 1.0.4
	 * @param string $table_name Full table name with prefix.
	 * @return string CREATE TABLE SQL statement.
	 */
	public static function get_seo_settings_schema( $table_name ) {
		$charset_collate = self::get_charset_collate();

		return "CREATE TABLE IF NOT EXISTS {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			domain varchar(255) NOT NULL,
			language_code varchar(10) NOT NULL,
			location_code int(10) NOT NULL,
			auto_detect_enabled tinyint(1) DEFAULT 1,
			min_volume int(10) DEFAULT 100,
			max_difficulty int(3) DEFAULT 80,
			priority_weights json,
			batch_sizes json,
			ttl_settings json,
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY domain_lang_loc (domain, language_code, location_code)
		) {$charset_collate};";
	}

	/**
	 * Get SEO snapshots table schema
	 *
	 * Historical snapshots of SEO analysis results.
	 *
	 * @since 1.0.4
	 * @param string $table_name Full table name with prefix.
	 * @return string CREATE TABLE SQL statement.
	 */
	public static function get_seo_snapshots_schema( $table_name ) {
		$charset_collate = self::get_charset_collate();

		return "CREATE TABLE IF NOT EXISTS {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			snapshot_id varchar(64) NOT NULL,
			target_domain varchar(255) NOT NULL,
			language_code varchar(10) NOT NULL DEFAULT 'fr',
			location_code int(10) NOT NULL DEFAULT 2250,
			analysis_date datetime DEFAULT CURRENT_TIMESTAMP,
			total_keywords int(10) DEFAULT 0,
			total_opportunities int(10) DEFAULT 0,
			quick_wins int(10) DEFAULT 0,
			top_performers int(10) DEFAULT 0,
			snapshot_data longtext,
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY snapshot_target_date (snapshot_id, target_domain, analysis_date),
			KEY target_domain (target_domain),
			KEY analysis_date (analysis_date),
			KEY snapshot_id (snapshot_id)
		) {$charset_collate};";
	}

	/**
	 * Get SEO keywords v2 table schema
	 *
	 * Enhanced keyword data with versioning and snapshot association.
	 *
	 * @since 1.0.4
	 * @param string $table_name Full table name with prefix.
	 * @return string CREATE TABLE SQL statement.
	 */
	public static function get_seo_keywords_v2_schema( $table_name ) {
		$charset_collate = self::get_charset_collate();

		return "CREATE TABLE IF NOT EXISTS {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			snapshot_id varchar(64) NOT NULL,
			target_domain varchar(255) NOT NULL,
			language_code varchar(10) NOT NULL DEFAULT 'fr',
			location_code int(10) NOT NULL DEFAULT 2250,
			keyword varchar(255) NOT NULL,
			keyword_type enum('current','opportunity') DEFAULT 'current',
			position_abs int(3) DEFAULT NULL,
			page_url text,
			search_volume int(10) DEFAULT 0,
			search_intent varchar(32) DEFAULT 'informational',
			content_type varchar(32) DEFAULT 'blog',
			priority_score decimal(5,2) DEFAULT 0,
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY snapshot_id (snapshot_id),
			KEY target_domain (target_domain),
			KEY keyword_type (keyword_type),
			KEY priority_score (priority_score DESC),
			KEY position_abs (position_abs)
		) {$charset_collate};";
	}

	/**
	 * Get SEO domains table schema
	 *
	 * Domain discovery and subdomain tracking for SEO analysis.
	 *
	 * @since 1.0.4
	 * @param string $table_name Full table name with prefix.
	 * @return string CREATE TABLE SQL statement.
	 */
	public static function get_seo_domains_schema( $table_name ) {
		$charset_collate = self::get_charset_collate();

		return "CREATE TABLE IF NOT EXISTS {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			main_domain varchar(255) NOT NULL,
			discovered_domain varchar(255) NOT NULL,
			domain_type enum('main','subdomain','related') DEFAULT 'main',
			has_content tinyint(1) DEFAULT 0,
			keyword_count int(10) DEFAULT 0,
			last_checked datetime DEFAULT CURRENT_TIMESTAMP,
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY main_discovered (main_domain, discovered_domain),
			KEY main_domain (main_domain),
			KEY domain_type (domain_type)
		) {$charset_collate};";
	}

	// ======================================================================
	// KEYWORD CLUSTERING AND COMPETITOR ANALYSIS TABLES
	// ======================================================================

	/**
	 * Get keyword clusters table schema
	 *
	 * Keyword clustering for deduplication and cannibalization prevention.
	 *
	 * @since 1.0.4
	 * @param string $table_name Full table name with prefix.
	 * @return string CREATE TABLE SQL statement.
	 */
	public static function get_keyword_clusters_schema( $table_name ) {
		$charset_collate = self::get_charset_collate();

		return "CREATE TABLE IF NOT EXISTS {$table_name} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			cluster_hash varchar(64) NOT NULL COMMENT 'MD5 hash of sorted keyword IDs',
			primary_keyword_id bigint(20) unsigned NOT NULL,
			variant_keyword_ids text NOT NULL COMMENT 'JSON array of variant keyword IDs',
			similarity_scores text DEFAULT NULL COMMENT 'JSON object of pairwise similarity scores',
			cluster_metrics text DEFAULT NULL COMMENT 'JSON object with size, avg_similarity, etc',
			language_code varchar(10) DEFAULT NULL,
			location_code int(10) DEFAULT NULL,
			auto_detected tinyint(1) DEFAULT 1 COMMENT 'Auto-detected vs manually created',
			user_approved tinyint(1) DEFAULT 0 COMMENT 'User reviewed and approved',
			user_modified tinyint(1) DEFAULT 0 COMMENT 'User changed the primary',
			is_active tinyint(1) DEFAULT 1 COMMENT 'Active or ungrouped',
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			UNIQUE KEY cluster_hash (cluster_hash),
			KEY primary_keyword_id (primary_keyword_id),
			KEY language_code (language_code),
			KEY is_active (is_active),
			KEY user_approved (user_approved)
		) {$charset_collate};";
	}

	/**
	 * Get competitor analysis table schema
	 *
	 * Historical tracking of competitor spy analyses.
	 *
	 * @since 1.0.4
	 * @param string $table_name Full table name with prefix.
	 * @return string CREATE TABLE SQL statement.
	 */
	public static function get_competitor_analysis_schema( $table_name ) {
		$charset_collate = self::get_charset_collate();

		return "CREATE TABLE IF NOT EXISTS {$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};";
	}

	/**
	 * Get competitor keywords table schema
	 *
	 * Detailed keyword data from competitor analysis.
	 *
	 * @since 1.0.4
	 * @param string $table_name Full table name with prefix.
	 * @return string CREATE TABLE SQL statement.
	 */
	public static function get_competitor_keywords_schema( $table_name ) {
		$charset_collate = self::get_charset_collate();

		return "CREATE TABLE IF NOT EXISTS {$table_name} (
			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};";
	}

	/**
	 * Get all table schemas
	 *
	 * Returns array of all Wisewand table schemas with migrations merged.
	 *
	 * @since 1.0.2
	 * @global wpdb $wpdb WordPress database abstraction object.
	 * @return array Associative array of table name => CREATE TABLE SQL.
	 */
	public static function get_all_schemas() {
		global $wpdb;

		return array(
			// Core Content Tables.
			'wisewand_editorial_calendar' => self::get_editorial_calendar_schema(
				$wpdb->prefix . 'wisewand_editorial_calendar'
			),
			'wisewand_saved_keywords'     => self::get_saved_keywords_schema(
				$wpdb->prefix . 'wisewand_saved_keywords'
			),

			// RSS Feed Tables.
			'wisewand_rss_feeds'          => self::get_rss_feeds_schema(
				$wpdb->prefix . 'wisewand_rss_feeds'
			),
			'wisewand_rss_articles'       => self::get_rss_articles_schema(
				$wpdb->prefix . 'wisewand_rss_articles'
			),

			// Infrastructure Tables.
			'wisewand_logs'               => self::get_logs_schema(
				$wpdb->prefix . 'wisewand_logs'
			),
			'wisewand_rate_limits'        => self::get_rate_limits_schema(
				$wpdb->prefix . 'wisewand_rate_limits'
			),
			'wisewand_pull_queue'         => self::get_pull_queue_schema(
				$wpdb->prefix . 'wisewand_pull_queue'
			),

			// SEO Analysis Tables.
			'wisewand_seo_keywords'       => self::get_seo_keywords_schema(
				$wpdb->prefix . 'wisewand_seo_keywords'
			),
			'wisewand_seo_pages'          => self::get_seo_pages_schema(
				$wpdb->prefix . 'wisewand_seo_pages'
			),
			'wisewand_seo_settings'       => self::get_seo_settings_schema(
				$wpdb->prefix . 'wisewand_seo_settings'
			),
			'wisewand_seo_snapshots'      => self::get_seo_snapshots_schema(
				$wpdb->prefix . 'wisewand_seo_snapshots'
			),
			'wisewand_seo_keywords_v2'    => self::get_seo_keywords_v2_schema(
				$wpdb->prefix . 'wisewand_seo_keywords_v2'
			),
			'wisewand_seo_domains'        => self::get_seo_domains_schema(
				$wpdb->prefix . 'wisewand_seo_domains'
			),

			// Clustering and Competitor Tables.
			'wisewand_keyword_clusters'   => self::get_keyword_clusters_schema(
				$wpdb->prefix . 'wisewand_keyword_clusters'
			),
			'wisewand_competitor_analysis' => self::get_competitor_analysis_schema(
				$wpdb->prefix . 'wisewand_competitor_analysis'
			),
			'wisewand_competitor_keywords' => self::get_competitor_keywords_schema(
				$wpdb->prefix . 'wisewand_competitor_keywords'
			),
		);
	}

	/**
	 * Get current schema version
	 *
	 * @since 1.0.2
	 * @return string Current schema version.
	 */
	public static function get_version() {
		return self::SCHEMA_VERSION;
	}
}
