<?php
/**
 * Database Manager
 *
 * Orchestrates database creation, upgrades, and auto-healing.
 * Single entry point for all database operations.
 *
 * @package Wisewand
 * @since 1.0.2
 * @license GPL-2.0-or-later
 */

namespace Wisewand\Database;

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

// phpcs:disable WordPress.PHP.DevelopmentFunctions.error_log_error_log, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- error_log() only used within WP_DEBUG conditionals; direct database calls and schema changes required for database management

/**
 * Database manager orchestrator
 *
 * Coordinates schema creation, upgrades, and auto-healing.
 *
 * @since 1.0.2
 */
class Database_Manager {

	/**
	 * Initialize database on plugin activation
	 *
	 * Creates all tables and runs necessary migrations.
	 * Safe to run multiple times - CREATE TABLE IF NOT EXISTS is idempotent.
	 *
	 * @since 1.0.2
	 * @global wpdb $wpdb WordPress database abstraction object.
	 * @return bool True if initialization successful.
	 */
	public static function initialize() {
		global $wpdb;

		if ( ! $wpdb ) {
			return false;
		}

		if ( WP_DEBUG ) {
			error_log( 'Wisewand: Starting database initialization' );
		}

		// Get all table schemas from Database_Schema
		$schemas = Database_Schema::get_all_schemas();

		// Create all tables
		foreach ( $schemas as $table_name => $create_sql ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL from Database_Schema is safe
			// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table creation query with safe table name
			$wpdb->query( $create_sql );

			if ( $wpdb->last_error ) {
				if ( WP_DEBUG ) {
					error_log( 'Wisewand: Error creating table ' . $table_name . ': ' . $wpdb->last_error );
				}
				return false;
			}

			if ( WP_DEBUG ) {
				error_log( 'Wisewand: Created/verified table ' . $table_name );
			}
		}

		// Run data migrations for existing installations
		if ( Database_Upgrader::needs_migration() ) {
			if ( WP_DEBUG ) {
				error_log( 'Wisewand: Running data migrations for existing installation' );
			}
			Database_Upgrader::run_all_migrations();
		}

		// Set initial schema version
		Database_Version::update_version( Database_Schema::get_version() );

		if ( WP_DEBUG ) {
			error_log( 'Wisewand: Database initialization completed successfully' );
			error_log( 'Wisewand: Schema version: ' . Database_Schema::get_version() );
		}

		return true;
	}

	/**
	 * Auto-heal database
	 *
	 * Checks for missing tables and columns, and auto-creates them.
	 * Called on admin_init to ensure database integrity.
	 *
	 * @since 1.0.2
	 * @return bool True if healing performed or not needed.
	 */
	public static function auto_heal() {
		// 1. Check for missing tables first
		$missing_tables = self::verify_tables_exist();
		if ( ! empty( $missing_tables ) ) {
			if ( WP_DEBUG ) {
				error_log( 'Wisewand: Found missing tables during auto-heal: ' . implode( ', ', $missing_tables ) );
				error_log( 'Wisewand: Triggering database initialization to fix missing tables' );
			}
			// Re-run initialization to create missing tables
			self::initialize();
		}

		// 2. Check for missing columns (schema updates)
		$required_columns = self::get_required_columns();

		// For v1.0.2, all columns are in base schema
		// No auto-healing needed yet
		if ( empty( $required_columns ) ) {
			return true;
		}

		$healed = false;

		foreach ( $required_columns as $table => $columns ) {
			foreach ( $columns as $column => $definition ) {
				if ( ! self::column_exists( $table, $column ) ) {
					if ( WP_DEBUG ) {
						error_log( "Wisewand: Auto-healing missing column {$column} in {$table}" );
					}

					$success = self::add_column( $table, $column, $definition );

					if ( $success ) {
						$healed = true;
						if ( WP_DEBUG ) {
							error_log( "Wisewand: Successfully healed {$table}.{$column}" );
						}
					} else {
						if ( WP_DEBUG ) {
							error_log( "Wisewand: Failed to heal {$table}.{$column}" );
						}
					}
				}
			}
		}

		return true;
	}

	/**
	 * Check if column exists in table
	 *
	 * @since 1.0.2
	 * @global wpdb $wpdb WordPress database abstraction object.
	 * @param string $table Table name (without prefix).
	 * @param string $column Column name.
	 * @return bool True if column exists.
	 */
	public static function column_exists( $table, $column ) {
		global $wpdb;

		$table_name = $wpdb->prefix . $table;

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from $wpdb->prefix is safe
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix is safe
		$results = $wpdb->get_results(
			$wpdb->prepare( "SHOW COLUMNS FROM `{$table_name}` LIKE %s", $column )
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		return ! empty( $results );
	}

	/**
	 * Add column to table
	 *
	 * @since 1.0.2
	 * @global wpdb $wpdb WordPress database abstraction object.
	 * @param string $table Table name (without prefix).
	 * @param string $column Column name.
	 * @param string $definition Column definition SQL.
	 * @return bool True if column added successfully.
	 */
	public static function add_column( $table, $column, $definition ) {
		global $wpdb;

		$table_name = $wpdb->prefix . $table;

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name and column definition are safe
		// phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix is safe
		$wpdb->query( "ALTER TABLE `{$table_name}` ADD COLUMN {$definition}" );
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		return ! $wpdb->last_error;
	}

	/**
	 * Get required columns for auto-healing
	 *
	 * Returns array of columns that should exist for current version.
	 * Empty for v1.0.2 - all columns are in base schema.
	 * Future versions can add columns here for automatic healing.
	 *
	 * Format:
	 * array(
	 *   'table_name' => array(
	 *     'column_name' => 'column_definition SQL'
	 *   )
	 * )
	 *
	 * @since 1.0.2
	 * @return array Required columns configuration.
	 */
	public static function get_required_columns() {
		// For v1.0.2: All columns are in base schema
		// No auto-healing needed yet
		//
		// Example for future versions:
		// return array(
		//   'wisewand_editorial_calendar' => array(
		//     'new_column' => "`new_column` varchar(255) DEFAULT NULL COMMENT 'Description' AFTER `existing_column`",
		//   ),
		// );

		return array();
	}

	/**
	 * Check if database needs upgrade
	 *
	 * Compares current schema version with latest version.
	 *
	 * @since 1.0.2
	 * @return bool True if upgrade needed.
	 */
	public static function needs_upgrade() {
		return Database_Version::needs_upgrade();
	}

	/**
	 * Verify all required tables exist
	 *
	 * Checks that all Wisewand tables exist in database.
	 *
	 * @since 1.0.2
	 * @global wpdb $wpdb WordPress database abstraction object.
	 * @return array Array of missing table names (empty if all exist).
	 */
	public static function verify_tables_exist() {
		global $wpdb;

		// All tables defined in Database_Schema::get_all_schemas()
		$required_tables = array(
			// Core Content Tables
			'wisewand_editorial_calendar',
			'wisewand_saved_keywords',

			// RSS Feed Tables
			'wisewand_rss_feeds',
			'wisewand_rss_articles',

			// Infrastructure Tables
			'wisewand_logs',
			'wisewand_rate_limits',
			'wisewand_pull_queue',

			// SEO Analysis Tables
			'wisewand_seo_keywords',
			'wisewand_seo_pages',
			'wisewand_seo_settings',
			'wisewand_seo_snapshots',
			'wisewand_seo_keywords_v2',
			'wisewand_seo_domains',

			// Clustering and Competitor Tables
			'wisewand_keyword_clusters',
			'wisewand_competitor_analysis',
			'wisewand_competitor_keywords',
		);

		$missing_tables = array();

		foreach ( $required_tables as $table_name ) {
			$full_table_name = $wpdb->prefix . $table_name;

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Verification query
			$exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $full_table_name ) );

			if ( ! $exists ) {
				$missing_tables[] = $table_name;
			}
		}

		return $missing_tables;
	}

	/**
	 * Get database health status
	 *
	 * Returns comprehensive health check information.
	 *
	 * @since 1.0.2
	 * @global wpdb $wpdb WordPress database abstraction object.
	 * @return array Health status information.
	 */
	public static function get_health_status() {
		global $wpdb;

		$missing_tables = self::verify_tables_exist();
		$schema_version = Database_Version::get_current_version();
		$latest_version = Database_Schema::get_version();
		$needs_upgrade = self::needs_upgrade();

		// Get database version
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Database version query
		$db_version = $wpdb->get_var( 'SELECT VERSION()' );

		$status = array(
			'healthy'         => empty( $missing_tables ) && ! $needs_upgrade,
			'schema_version'  => $schema_version,
			'latest_version'  => $latest_version,
			'needs_upgrade'   => $needs_upgrade,
			'missing_tables'  => $missing_tables,
			'db_version'      => $db_version,
			'charset_collate' => Database_Schema::get_charset_collate(),
			'migration_status' => Database_Upgrader::get_migration_status(),
		);

		return $status;
	}

	/**
	 * Repair database
	 *
	 * Attempts to fix common database issues.
	 *
	 * @since 1.0.2
	 * @return bool True if repairs successful.
	 */
	public static function repair() {
		if ( WP_DEBUG ) {
			error_log( 'Wisewand: Starting database repair' );
		}

		// Re-run initialization to recreate missing tables
		$init_success = self::initialize();

		// Run auto-healing for missing columns
		$heal_success = self::auto_heal();

		if ( WP_DEBUG ) {
			error_log( 'Wisewand: Database repair completed' );
			error_log( 'Wisewand: Initialization: ' . ( $init_success ? 'SUCCESS' : 'FAILED' ) );
			error_log( 'Wisewand: Auto-heal: ' . ( $heal_success ? 'SUCCESS' : 'FAILED' ) );
		}

		return $init_success && $heal_success;
	}
}
