<?php
/**
 * SchemaManager.php.
 *
 * @package RewardsWP
 */

namespace Awesomemotive\Rewardswp\Database;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

use Awesomemotive\Rewardswp\GlobalConfig;
use wpdb;

/**
 * Class SchemaManager
 *
 * Handles database table creation and updates similarly to WordPress' dbDelta function.
 * Allows for safe table schema modifications without data loss.
 *
 * @since 1.0.0
 */
class SchemaManager {
	/**
	 * Globals wrapper.
	 *
	 * @since 1.0.0
	 *
	 * @var GlobalConfig
	 */
	protected GlobalConfig $global_config;

	/**
	 * Constructor.
	 *
	 * @since 1.0.0
	 * @param GlobalConfig $global_config Globals.
	 */
	public function __construct( GlobalConfig $global_config ) {
		$this->global_config = $global_config;
	}

	/**
	 * Creates or updates a database table based on the provided SQL query.
	 * Similar to WordPress' dbDelta but with more detailed feedback.
	 *
	 * @since 1.0.0
	 *
	 * @param string $query SQL query for table creation.
	 * @return array Array containing 'errors' and 'success' messages.
	 */
	public function db_delta( string $query ): array {
		$results = array(
			'errors'  => array(),
			'success' => array(),
		);

		// Extract table name and column definitions.
		if ( ! preg_match( '/CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?([^\s(]+)\s*\((.*)\)/is', $query, $matches ) ) {
			$results['errors'][] = 'Invalid SQL query format';
			return $results;
		}

		$table_name       = trim( $matches[1], '`' );
		$table_definition = $matches[2];

		// Parse column definitions.
		preg_match_all(
			'/\s*((?:[^,(]|\([^)]*\))+),?(?=\s*(?:[A-Za-z][A-Za-z0-9_]*\s+[^,]+|PRIMARY\s+KEY|KEY|UNIQUE\s+KEY|CONSTRAINT|$))/is',
			$table_definition,
			$column_matches
		);

		$columns = array();
		$indexes = array();

		foreach ( $column_matches[1] as $definition ) {
			$definition = trim( $definition );

			// Check if it's an index or column definition.
			if ( preg_match( '/^(?:PRIMARY\s+KEY|KEY|UNIQUE\s+KEY|CONSTRAINT)\s/i', $definition ) ) {
				$indexes[] = $definition;
			} elseif ( preg_match( '/^([^\s]+)/', $definition, $col_match ) ) {
					$col_name             = trim( $col_match[1], '`' );
					$columns[ $col_name ] = $definition;
			}
		}

		$db = $this->global_config->get_db();

		// Check if table exists.
		$table_exists = $db->get_var(
			$db->prepare(
				'SHOW TABLES LIKE %s',
				$table_name
			)
		);

		if ( ! $table_exists ) {
			return $this->create_new_table( $query, $results );
		}

		return $this->update_existing_table( $table_name, $columns, $indexes, $results );
	}

	/**
	 * Creates a new table in the database.
	 *
	 * @since 1.0.0
	 *
	 * @param string $query   SQL query for table creation.
	 * @param array  $results Current results array.
	 *
	 * @return array Updated results array.
	 */
	private function create_new_table( string $query, array $results ): array {
		$db = $this->global_config->get_db();

		$result = $db->query( $query );

		if ( false === $result ) {
			$results['errors'][] = sprintf(
				'Error creating table: %s',
				$db->last_error
			);
		} else {
			$results['success'][] = esc_html__( 'Table created successfully', 'rewardswp' );
		}

		return $results;
	}

	/**
	 * Updates an existing table schema.
	 *
	 * @since 1.0.0
	 *
	 * @param string $table_name Table name.
	 * @param array  $columns    Array of column definitions.
	 * @param array  $indexes    Array of index definitions.
	 * @param array  $results    Current results array.
	 *
	 * @return array Updated results array.
	 */
	private function update_existing_table( string $table_name, array $columns, array $indexes, array $results ): array {
		$db = $this->global_config->get_db();

		$existing_columns = $db->get_results( "SHOW COLUMNS FROM `$table_name`" );
		$existing_indexes = $db->get_results( "SHOW INDEXES FROM `$table_name`" );

		// Update columns.
		foreach ( $columns as $col_name => $col_definition ) {
			$results = $this->process_column( $table_name, $col_name, $col_definition, $existing_columns, $results );
		}

		// Update indexes.
		return $this->process_indexes( $table_name, $indexes, $existing_indexes, $results );
	}

	/**
	 * Processes a single column for creation or modification.
	 *
	 * @since 1.0.0
	 *
	 * @param string $table_name       Table name.
	 * @param string $col_name         Column name.
	 * @param string $col_definition   Column definition.
	 * @param array  $existing_columns Array of existing columns.
	 * @param array  $results          Current results array.
	 *
	 * @return array Updated results array.
	 */
	private function process_column( string $table_name, string $col_name, string $col_definition, array $existing_columns, array $results ): array {
		$column_exists = false;
		$db            = $this->global_config->get_db();

		foreach ( $existing_columns as $existing_col ) {
			if ( $existing_col->Field === $col_name ) {
				$column_exists = true;
				$current_def   = $this->get_column_definition( $table_name, $col_name );

				if ( $this->normalize_definition( $current_def ) !== $this->normalize_definition( $col_definition ) ) {
					$alter_query = "ALTER TABLE `$table_name` MODIFY $col_definition";
					$result      = $db->query( $alter_query );

					if ( false === $result ) {
						$results['errors'][] = sprintf(
							'Error modifying column %s: %s',
							$col_name,
							$db->last_error
						);
					} else {
						$results['success'][] = sprintf( 'Column %s updated', $col_name );
					}
				}
				break;
			}
		}

		if ( ! $column_exists ) {
			$alter_query = "ALTER TABLE `$table_name` ADD $col_definition";
			$result      = $db->query( $alter_query );

			if ( false === $result ) {
				$results['errors'][] = sprintf(
					'Error adding column %s: %s',
					$col_name,
					$db->last_error
				);
			} else {
				$results['success'][] = sprintf( 'Column %s added', $col_name );
			}
		}

		return $results;
	}

	/**
	 * Processes table indexes for creation.
	 *
	 * @since 1.0.0
	 *
	 * @param string $table_name       Table name.
	 * @param array  $indexes          Array of index definitions.
	 * @param array  $existing_indexes Array of existing indexes.
	 * @param array  $results          Current results array.
	 *
	 * @return array Updated results array.
	 */
	private function process_indexes( string $table_name, array $indexes, array $existing_indexes, array $results ): array {
		$db                   = $this->global_config->get_db();
		$existing_index_names = array();

		foreach ( $existing_indexes as $index ) {
			$existing_index_names[ $index->Key_name ] = true;
		}

		foreach ( $indexes as $index_def ) {
			if ( preg_match( '/^(?:PRIMARY\s+KEY|KEY\s+(`?\w+`?)|UNIQUE\s+KEY\s+(`?\w+`?))/i', $index_def, $matches ) ) {
				$index_name = isset( $matches[2] ) ? trim( $matches[2], '`' ) : ( isset( $matches[1] ) ? trim( $matches[1], '`' ) : 'PRIMARY' );

				if ( ! isset( $existing_index_names[ $index_name ] ) ) {
					$alter_query = "ALTER TABLE `$table_name` ADD $index_def";
					$result      = $db->query( $alter_query );

					if ( false === $result ) {
						$results['errors'][] = sprintf(
							'Error adding index %s: %s',
							$index_name,
							$db->last_error
						);
					} else {
						$results['success'][] = sprintf( 'Index %s added', $index_name );
					}
				}
			}
		}

		return $results;
	}

	/**
	 * Gets the current column definition from the database.
	 *
	 * @since 1.0.0
	 *
	 * @param string $table_name  Table name.
	 * @param string $column_name Column name.
	 *
	 * @return string Column definition.
	 */
	private function get_column_definition( string $table_name, string $column_name ): string {
		$db = $this->global_config->get_db();

		$result = $db->get_row(
			"SHOW CREATE TABLE `$table_name`",
			ARRAY_N
		);

		if ( $result && preg_match( "/`$column_name`[^,]+/", $result[1], $matches ) ) {
			return $matches[0];
		}

		return '';
	}

	/**
	 * Normalizes a column or index definition for comparison.
	 *
	 * @since 1.0.0
	 *
	 * @param string $definition Column or index definition.
	 *
	 * @return string Normalized definition.
	 */
	private function normalize_definition( string $definition ): string {
		return strtolower( preg_replace( '/\s+/', ' ', trim( $definition ) ) );
	}

	/**
	 * Add a new column to an existing table.
	 *
	 * @since 1.0.1
	 *
	 * @param string $table_name    The name of the table (with prefix).
	 * @param string $column_name   The name of the column to add.
	 * @param string $column_def    The column definition (type, constraints, etc).
	 * @param string $after_column  Optional. The column after which to add the new column.
	 * @return array Array containing 'errors' and 'success' messages.
	 */
	public function add_column( string $table_name, string $column_name, string $column_def, string $after_column = '' ): array {
		$results = array(
			'errors'  => array(),
			'success' => array(),
		);

		$db = $this->global_config->get_db();

		// Check if column already exists.
		$existing_columns = $db->get_results( "SHOW COLUMNS FROM `$table_name`" );
		foreach ( $existing_columns as $existing_col ) {
			if ( $existing_col->Field === $column_name ) {
				$results['errors'][] = sprintf( 'Column %s already exists in table %s', $column_name, $table_name );
				return $results;
			}
		}

		// Build the ALTER TABLE query.
		$query = sprintf( 'ALTER TABLE `%s` ADD COLUMN `%s` %s', $table_name, $column_name, $column_def );
		if ( ! empty( $after_column ) ) {
			$query .= sprintf( ' AFTER `%s`', $after_column );
		}

		// Execute the query.
		$result = $db->query( $query );

		if ( false === $result ) {
			$results['errors'][] = sprintf(
				'Error adding column %s: %s',
				$column_name,
				$db->last_error
			);
		} else {
			$results['success'][] = sprintf( 'Column %s added successfully', $column_name );
		}

		return $results;
	}
}
