<?php
/**
 * Database Health Checker
 *
 * Handles database version checking, table creation, and incremental migrations
 * that were previously inlined in Plugin.php. Delegates schema definitions to
 * Database_Schema and upgrade orchestration to Database_Manager / Database_Upgrader.
 *
 * @package Wisewand
 * @since 2.8.2
 * @license GPL-2.0-or-later
 */

namespace Wisewand\Core;

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

use Wisewand\Database\Database_Manager;
use Wisewand\Database\Database_Schema;
use Wisewand\Database\Database_Upgrader;
use Wisewand\Database\Database_Version;
use Wisewand\Logging\Logger;

// phpcs:disable WordPress.PHP.DevelopmentFunctions.error_log_error_log, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- error_log() is only used within WP_DEBUG conditionals for debugging; direct database calls and schema changes required for database version migrations and custom plugin table operations

/**
 * Database health checker and migration runner
 *
 * Checks whether the database schema is current, creates any missing tables,
 * and runs incremental (numbered) migrations that are tracked by option flags.
 * Designed to be called early in plugin boot, before the Logger is available,
 * so every $this->logger reference is guarded.
 *
 * @since 2.8.2
 */
class Database_Health_Checker {

	/**
	 * Logger instance (may be null during early boot)
	 *
	 * @since 2.8.2
	 * @var Logger|null
	 */
	private $logger;

	/**
	 * Constructor
	 *
	 * @since 2.8.2
	 *
	 * @param Logger|null $logger Optional logger instance.
	 */
	public function __construct( $logger = null ) {
		$this->logger = $logger;
	}

	// =========================================================================
	// PUBLIC ENTRY POINTS
	// =========================================================================

	/**
	 * Check whether the stored plugin version matches the current version
	 *
	 * Called from Plugin::init_plugin() (before the logger exists). When the
	 * version has changed, missing tables are created and the version option is
	 * updated. Incremental numbered migrations are always run regardless of
	 * version change.
	 *
	 * @since 2.8.2
	 */
	public function check_database_version() {
		$installed_version = get_option( 'wisewand_plugin_version', '0.0.0' );

		if ( version_compare( $installed_version, WISEWAND_PLUGIN_VERSION, '<' ) ) {
			// Database needs update — ensure all tables exist.
			$this->create_missing_tables();
			update_option( 'wisewand_plugin_version', WISEWAND_PLUGIN_VERSION );

			if ( $this->logger ) {
				$this->logger->info(
					'Database updated',
					array(
						'from_version' => $installed_version,
						'to_version'   => WISEWAND_PLUGIN_VERSION,
					)
				);
			}
		}

		// Run specific migrations independently (version-agnostic).
		$this->run_database_migrations();
	}

	/**
	 * Check database schema and auto-heal if needed
	 *
	 * Hooked to admin_init (priority 5). Detects plugin version changes and
	 * delegates auto-healing, pending upgrades, and version bookkeeping to the
	 * Database_Manager / Database_Upgrader / Database_Version layer.
	 *
	 * @since 1.0.2
	 */
	public function check_database_health() {
		// Only run in admin context.
		if ( ! is_admin() ) {
			return;
		}

		// Check if plugin version changed.
		if ( Database_Version::plugin_version_changed() ) {
			if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
				$old_version = Database_Version::get_plugin_version();
				// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
				error_log( "Wisewand: Plugin version changed from {$old_version} to " . WISEWAND_PLUGIN_VERSION );
			}

			// Run database health check and auto-heal.
			Database_Manager::auto_heal();

			// Run any pending upgrades.
			if ( Database_Manager::needs_upgrade() ) {
				if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
					// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
					error_log( 'Wisewand: Running database upgrades' );
				}

				// This runs Migration 5 & 7 if needed.
				Database_Upgrader::run_all_migrations();
			}

			// Update plugin version.
			$old_version = Database_Version::get_plugin_version();
			Database_Version::update_plugin_version( WISEWAND_PLUGIN_VERSION );
			Database_Version::record_version_update( $old_version, WISEWAND_PLUGIN_VERSION );

			if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
				// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
				error_log( 'Wisewand: Database health check completed' );
			}
		}
	}

	/**
	 * Handle plugin version upgrade bookkeeping
	 *
	 * Called from Plugin::check_plugin_updates() when the stored version is
	 * behind the current version constant.
	 *
	 * @since 2.8.2
	 *
	 * @param string $from_version Previously installed version string.
	 */
	public function upgrade_plugin( $from_version ) {
		if ( $this->logger ) {
			$this->logger->info(
				'Plugin upgraded',
				array(
					'from_version' => $from_version,
					'to_version'   => WISEWAND_PLUGIN_VERSION,
				)
			);
		}

		// Migration: Update saved_keywords UNIQUE constraint to support same keyword in different languages.
		$this->migrate_saved_keywords_unique_constraint();
	}

	// =========================================================================
	// PRIVATE — TABLE CREATION
	// =========================================================================

	/**
	 * Create any Wisewand tables that are missing from the database
	 *
	 * Delegates schema SQL to Database_Schema::get_all_schemas() and runs
	 * dbDelta() for each entry. Falls back to direct $wpdb->query() for tables
	 * not included in the schema class (logs table must be created first so the
	 * Logger can operate during initialization).
	 *
	 * @since 2.8.2
	 * @global wpdb $wpdb WordPress database abstraction object.
	 */
	private function create_missing_tables() {
		global $wpdb;

		if ( ! $wpdb ) {
			return;
		}

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$schemas = Database_Schema::get_all_schemas();

		foreach ( $schemas as $table_suffix => $sql ) {
			$table_name = $wpdb->prefix . $table_suffix;

			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- SHOW TABLES with safe prefixed name
			$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) ) === $table_name;

			if ( ! $table_exists ) {
				dbDelta( $sql );

				if ( $wpdb->last_error ) {
					if ( $this->logger ) {
						$this->logger->error(
							'Failed to create database table',
							array(
								'table' => $table_name,
								'error' => $wpdb->last_error,
							)
						);
					}
				} elseif ( $this->logger ) {
					$this->logger->info( 'Created missing database table', array( 'table' => $table_name ) );
				}
			}
		}
	}

	// =========================================================================
	// PRIVATE — NUMBERED MIGRATIONS
	// =========================================================================

	/**
	 * Run all incremental numbered migrations that have not yet been applied
	 *
	 * Each migration is gated by a boolean option flag so it only runs once.
	 * The order matches the original sequence in Plugin.php.
	 *
	 * @since 2.8.2
	 */
	private function run_database_migrations() {
		// Migration 001: Update saved_keywords UNIQUE constraint.
		if ( ! get_option( 'wisewand_migration_001_keyword_language', false ) ) {
			$this->migrate_saved_keywords_unique_constraint();
			update_option( 'wisewand_migration_001_keyword_language', true );
		}

		// Migration 002: Add clustering columns to saved_keywords.
		if ( ! get_option( 'wisewand_migration_002_clustering', false ) ) {
			$this->migrate_add_clustering_columns();
			update_option( 'wisewand_migration_002_clustering', true );
		}

		// Migration 003: Add wordpress_categories to editorial_calendar.
		if ( ! get_option( 'wisewand_migration_003_categories', false ) ) {
			$this->migrate_add_categories_column();
			update_option( 'wisewand_migration_003_categories', true );
		}

		// Migration 004: Add AI matching columns to saved_keywords.
		if ( ! get_option( 'wisewand_migration_004_ai_matching', false ) ) {
			$this->migrate_add_ai_matching_columns();
			update_option( 'wisewand_migration_004_ai_matching', true );
		}

		// Migration 005: Add feed_type column to wisewand_rss_feeds.
		if ( ! get_option( 'wisewand_migration_005_feed_type', false ) ) {
			$this->migrate_add_feed_type_column();
			update_option( 'wisewand_migration_005_feed_type', true );
		}
	}

	/**
	 * Migration 001: Replace single-column UNIQUE KEY on keyword with composite (keyword, language_code)
	 *
	 * Allows the same keyword to be saved in multiple languages. Checks for the
	 * old 'keyword' UNIQUE key and replaces it with 'keyword_language' only when
	 * the old constraint is present and the new one is absent.
	 *
	 * @since 2.8.2
	 * @global wpdb $wpdb WordPress database abstraction object.
	 */
	private function migrate_saved_keywords_unique_constraint() {
		global $wpdb;

		if ( ! $wpdb ) {
			return;
		}

		$table_name = $wpdb->prefix . 'wisewand_saved_keywords';

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from $wpdb->prefix is safe; cannot be parameterized in DDL
		$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) ) === $table_name;
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		if ( ! $table_exists ) {
			return;
		}

		try {
			// Check for old single-column unique key.
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix is safe
			$existing_keys = $wpdb->get_results( $wpdb->prepare( "SHOW KEYS FROM %i WHERE Key_name = 'keyword'", $table_name ), ARRAY_A );

			$has_old_constraint = false;
			$has_new_constraint = false;

			foreach ( $existing_keys as $key ) {
				if ( 'keyword' === $key['Key_name'] && 0 == $key['Non_unique'] ) { // phpcs:ignore WordPress.PHP.StrictComparisons.LooseComparison -- DB returns string '0'
					$has_old_constraint = true;
				}
			}

			// Check if new composite constraint already exists.
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix is safe
			$new_keys           = $wpdb->get_results( "SHOW KEYS FROM {$table_name} WHERE Key_name = 'keyword_language'", ARRAY_A );
			$has_new_constraint = ! empty( $new_keys );

			// Only migrate if we have the old constraint and don't have the new one.
			if ( $has_old_constraint && ! $has_new_constraint ) {
				// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL; table name from $wpdb->prefix is safe
				$wpdb->query( "ALTER TABLE {$table_name} DROP INDEX keyword" );

				if ( $this->logger ) {
					$this->logger->info( 'Dropped old UNIQUE constraint on keyword column', array( 'table' => $table_name ) );
				}

				$wpdb->query( "ALTER TABLE {$table_name} ADD UNIQUE KEY `keyword_language` (`keyword`, `language_code`)" );
				// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

				if ( $wpdb->last_error ) {
					if ( $this->logger ) {
						$this->logger->error(
							'Failed to add composite UNIQUE constraint',
							array(
								'table' => $table_name,
								'error' => $wpdb->last_error,
							)
						);
					}
				} elseif ( $this->logger ) {
					$this->logger->info( 'Added composite UNIQUE constraint (keyword, language_code)', array( 'table' => $table_name ) );
				}
			} elseif ( $has_new_constraint && $this->logger ) {
				$this->logger->debug( 'Composite UNIQUE constraint already exists, skipping migration', array( 'table' => $table_name ) );
			}
		} catch ( \Exception $e ) {
			if ( $this->logger ) {
				$this->logger->error(
					'Migration failed for saved_keywords table',
					array(
						'error' => $e->getMessage(),
						'table' => $table_name,
					)
				);
			}
		}
	}

	/**
	 * Migration 002: Add keyword-clustering columns to saved_keywords
	 *
	 * Adds cluster_id, is_cluster_primary, cluster_similarity_score and the
	 * corresponding indexes. Each column is checked individually before ALTER.
	 *
	 * @since 2.8.2
	 * @global wpdb $wpdb WordPress database abstraction object.
	 */
	private function migrate_add_clustering_columns() {
		global $wpdb;
		$saved_keywords_table = $wpdb->prefix . 'wisewand_saved_keywords';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from $wpdb->prefix is safe; SHOW TABLES cannot use %s for table identifier
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $saved_keywords_table ) ) !== $saved_keywords_table ) {
			return;
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SHOW COLUMNS; table name from $wpdb->prefix is safe
		$columns = $wpdb->get_col( "SHOW COLUMNS FROM {$saved_keywords_table}" );

		if ( ! in_array( 'cluster_id', $columns, true ) ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL ALTER; table name from $wpdb->prefix is safe
			$wpdb->query(
				"ALTER TABLE `{$saved_keywords_table}`
				ADD COLUMN `cluster_id` bigint(20) DEFAULT NULL COMMENT 'Reference to cluster if part of one' AFTER `id`"
			);
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}

		if ( ! in_array( 'is_cluster_primary', $columns, true ) ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL ALTER; table name from $wpdb->prefix is safe
			$wpdb->query(
				"ALTER TABLE `{$saved_keywords_table}`
				ADD COLUMN `is_cluster_primary` tinyint(1) DEFAULT 0 COMMENT '1 if this is the primary keyword in its cluster' AFTER `is_enriched`"
			);
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}

		if ( ! in_array( 'cluster_similarity_score', $columns, true ) ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL ALTER; table name from $wpdb->prefix is safe
			$wpdb->query(
				"ALTER TABLE `{$saved_keywords_table}`
				ADD COLUMN `cluster_similarity_score` decimal(5,2) DEFAULT NULL COMMENT 'Avg similarity to cluster' AFTER `is_cluster_primary`"
			);
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}

		// Add indexes if not yet present.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SHOW INDEX; table name from $wpdb->prefix is safe
		$indexes     = $wpdb->get_results( "SHOW INDEX FROM {$saved_keywords_table}" );
		$index_names = array_column( $indexes, 'Key_name' );

		if ( ! in_array( 'cluster_id', $index_names, true ) ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL ALTER; table name from $wpdb->prefix is safe
			$wpdb->query( "ALTER TABLE `{$saved_keywords_table}` ADD KEY `cluster_id` (`cluster_id`)" );
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}

		if ( ! in_array( 'is_cluster_primary', $index_names, true ) ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL ALTER; table name from $wpdb->prefix is safe
			$wpdb->query( "ALTER TABLE `{$saved_keywords_table}` ADD KEY `is_cluster_primary` (`is_cluster_primary`)" );
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}

		if ( $this->logger ) {
			$this->logger->info( 'Migration 002: Added clustering columns to saved_keywords' );
		}
	}

	/**
	 * Migration 003: Add wordpress_categories column to editorial_calendar
	 *
	 * Stores a JSON array of WordPress category IDs to use during post import.
	 *
	 * @since 2.8.2
	 * @global wpdb $wpdb WordPress database abstraction object.
	 */
	private function migrate_add_categories_column() {
		global $wpdb;
		$editorial_calendar_table = $wpdb->prefix . 'wisewand_editorial_calendar';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from $wpdb->prefix is safe
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $editorial_calendar_table ) ) !== $editorial_calendar_table ) {
			return;
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SHOW COLUMNS; table name from $wpdb->prefix is safe
		$columns = $wpdb->get_col( "SHOW COLUMNS FROM {$editorial_calendar_table}" );

		if ( ! in_array( 'wordpress_categories', $columns, true ) ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL ALTER; table name from $wpdb->prefix is safe
			$wpdb->query(
				"ALTER TABLE `{$editorial_calendar_table}`
				ADD COLUMN `wordpress_categories` text DEFAULT NULL COMMENT 'JSON array of category IDs for WordPress import' AFTER `target_tags`"
			);
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			if ( $this->logger ) {
				$this->logger->info( 'Migration 003: Added wordpress_categories column to editorial_calendar' );
			}
		}
	}

	/**
	 * Migration 004: Add AI matching columns to saved_keywords
	 *
	 * Adds suggested_author_id, suggested_categories, ai_matched_at, and
	 * ai_model_used. Each column is checked individually before ALTER.
	 *
	 * @since 2.8.2
	 * @global wpdb $wpdb WordPress database abstraction object.
	 */
	private function migrate_add_ai_matching_columns() {
		global $wpdb;
		$saved_keywords_table = $wpdb->prefix . 'wisewand_saved_keywords';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from $wpdb->prefix is safe
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $saved_keywords_table ) ) !== $saved_keywords_table ) {
			return;
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SHOW COLUMNS; table name from $wpdb->prefix is safe
		$columns = $wpdb->get_col( "SHOW COLUMNS FROM {$saved_keywords_table}" );

		if ( ! in_array( 'suggested_author_id', $columns, true ) ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL ALTER; table name from $wpdb->prefix is safe
			$wpdb->query(
				"ALTER TABLE `{$saved_keywords_table}`
				ADD COLUMN `suggested_author_id` int(10) DEFAULT NULL COMMENT 'AI-suggested author ID' AFTER `cluster_similarity_score`"
			);
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}

		if ( ! in_array( 'suggested_categories', $columns, true ) ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL ALTER; table name from $wpdb->prefix is safe
			$wpdb->query(
				"ALTER TABLE `{$saved_keywords_table}`
				ADD COLUMN `suggested_categories` text DEFAULT NULL COMMENT 'JSON array of AI-suggested category IDs' AFTER `suggested_author_id`"
			);
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}

		if ( ! in_array( 'ai_matched_at', $columns, true ) ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL ALTER; table name from $wpdb->prefix is safe
			$wpdb->query(
				"ALTER TABLE `{$saved_keywords_table}`
				ADD COLUMN `ai_matched_at` datetime DEFAULT NULL COMMENT 'When AI matching was performed' AFTER `suggested_categories`"
			);
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}

		if ( ! in_array( 'ai_model_used', $columns, true ) ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL ALTER; table name from $wpdb->prefix is safe
			$wpdb->query(
				"ALTER TABLE `{$saved_keywords_table}`
				ADD COLUMN `ai_model_used` varchar(50) DEFAULT NULL COMMENT 'OpenAI model used for matching' AFTER `ai_matched_at`"
			);
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		}

		if ( $this->logger ) {
			$this->logger->info( 'Migration 004: Added AI matching columns to saved_keywords' );
		}
	}

	/**
	 * Migration 005: Add feed_type column to wisewand_rss_feeds
	 *
	 * Enables support for both 'articles' and 'discoverarticles' feed types.
	 * Existing records are backfilled to 'discoverarticles' since all feeds
	 * prior to this migration used that type.
	 *
	 * @since 2.8.2
	 * @global wpdb $wpdb WordPress database abstraction object.
	 */
	private function migrate_add_feed_type_column() {
		global $wpdb;
		$rss_feeds_table = $wpdb->prefix . 'wisewand_rss_feeds';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from $wpdb->prefix is safe
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $rss_feeds_table ) ) !== $rss_feeds_table ) {
			if ( $this->logger ) {
				$this->logger->warning( 'Migration 005: wisewand_rss_feeds table does not exist, skipping' );
			}
			return;
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SHOW COLUMNS; table name from $wpdb->prefix is safe
		$columns = $wpdb->get_col( "SHOW COLUMNS FROM {$rss_feeds_table}" );

		if ( ! in_array( 'feed_type', $columns, true ) ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL ALTER; table name from $wpdb->prefix is safe
			$result = $wpdb->query(
				"ALTER TABLE `{$rss_feeds_table}`
				ADD COLUMN `feed_type` VARCHAR(50) NOT NULL DEFAULT 'articles' COMMENT 'Feed type: articles or discoverarticles' AFTER `wisewand_project_id`"
			);
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

			if ( false === $result ) {
				if ( $this->logger ) {
					$this->logger->error(
						'Migration 005: Failed to add feed_type column',
						array(
							'table'      => $rss_feeds_table,
							'wpdb_error' => $wpdb->last_error,
						)
					);
				}
				// Don't mark migration as complete — caller checks return value via option flag.
				return;
			}

			if ( $this->logger ) {
				$this->logger->info( 'Migration 005: Added feed_type column to wisewand_rss_feeds' );
			}
		}

		// Backfill existing records with 'discoverarticles'.
		// All feeds prior to this migration used that type.
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- UPDATE for data migration; table name from $wpdb->prefix is safe
		$updated = $wpdb->query(
			"UPDATE `{$rss_feeds_table}`
			SET `feed_type` = 'discoverarticles'
			WHERE `feed_type` = 'articles'"
		);

		if ( $this->logger ) {
			$this->logger->info(
				'Migration 005: Backfilled feed_type for existing feeds',
				array(
					'feeds_updated' => $updated,
					'type'          => 'discoverarticles',
				)
			);
		}
	}
}
