<?php
/**
 * Verified offline transition from the complete legacy namespace to schema13.
 *
 * @package ABeCeBooking
 */
namespace ABeCe\Booking\Infrastructure\Database;

use ABeCe\Booking\Support\DatabaseCompatibility;
use ABeCe\Booking\Support\LegacyIdentifiers;
use RuntimeException;

/** This workflow is never called automatically from a web or cron request. */
final class SchemaUpgrade {
	private int $connection   = 0;
	private string $lock_name = '';

	/** @param \wpdb $wpdb Site database connection. */
	public function __construct( private readonly \wpdb $wpdb ) {}

	/**
	 * Validate server, storage and explicit database-scoped permissions without DDL.
	 *
	 * @return array<string, mixed>
	 */
	public function preflight(): array {
		if ( $this->wpdb->get_var( 'SELECT DATABASE()' ) !== DB_NAME ) {
			throw new RuntimeException( 'ABECE_BOOKING_DATABASE_CONNECTION_IDENTITY_CHANGED' );
		}
		$server = DatabaseCompatibility::inspect( $this->wpdb );
		if ( ! $server['supported'] ) {
			throw new RuntimeException( 'ABECE_BOOKING_DATABASE_SERVER_UNSUPPORTED' );
		}
		Tables::validate_prefix( $this->wpdb );
		$layout  = DatabaseState::layout( $this->wpdb );
		$version = DatabaseState::version( $this->wpdb );
		if ( ! in_array( $version, array( 12, 13 ), true ) || ! in_array( $layout['state'], array( 'legacy', 'current' ), true ) || ( $version === 13 && $layout['state'] !== 'current' ) ) {
			throw new RuntimeException( 'ABECE_BOOKING_SCHEMA12_OR_RECOVERABLE_JOURNAL_REQUIRED' );
		}
		$snapshot = new DatabaseSnapshot( $this->wpdb );
		$tables   = $snapshot->tables();
		foreach ( $tables as $table ) {
			$engine = $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=%s', $table ) );
			if ( $engine !== 'InnoDB' || $this->wpdb->last_error ) {
				throw new RuntimeException( 'ABECE_BOOKING_NON_INNODB_TABLE' );
			}
		}
		$grants = $this->wpdb->get_col( 'SHOW GRANTS' );
		if ( $this->wpdb->last_error || ! $this->has_permissions( $grants ) ) {
			throw new RuntimeException( 'ABECE_BOOKING_DATABASE_PRIVILEGES_INSUFFICIENT' );
		}
		$can_drain = false;
		foreach ( $grants as $grant ) {
			if ( preg_match( '/^GRANT (.+) ON \*\.\* TO /i', $grant, $match ) && ( $match[1] === 'ALL PRIVILEGES' || in_array( 'PROCESS', explode( ', ', strtoupper( $match[1] ) ), true ) ) ) {
				$can_drain = true;
			}
		}
		if ( ! $can_drain ) {
			throw new RuntimeException( 'ABECE_BOOKING_PROCESS_PRIVILEGE_REQUIRED_FOR_DRAIN' );
		}
		$this->verify_foreign_keys( array_values( $layout['state'] === 'legacy' ? Tables::legacy( $this->wpdb ) : Tables::all( $this->wpdb ) ) );
		if ( $layout['state'] === 'legacy' ) {
			Tables::historical(
				function (): void {
					foreach ( Schema::current( $this->wpdb ) as $table => $sql ) {
							Schema::verify( $this->wpdb, $table, $sql );
					}
				}
			);
		}
		return array(
			'server'  => $server,
			'layout'  => $layout,
			'version' => $version,
			'tables'  => $tables,
		);
	}

	/**
	 * Execute or resume only under the persistent gate and real SQL WRITE locks.
	 *
	 * @param string $directory Private backup destination.
	 * @param string $verification_db Empty non-running SQL restore target.
	 * @return array<string, mixed> Safe summary; never raw rows or credentials.
	 */
	public function run( string $directory, string $verification_db ): array {
		if ( PHP_SAPI !== 'cli' ) {
			throw new RuntimeException( 'ABECE_BOOKING_OFFLINE_CLI_REQUIRED' );
		}
		DatabaseSnapshot::private_directory( $directory );
		$gate            = new OfflineGate( $this->wpdb );
		$this->lock_name = Tables::lock( $this->wpdb, 'schema' );
		if ( (int) $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT GET_LOCK(%s,15)', $this->lock_name ) ) !== 1 ) {
			throw new RuntimeException( 'ABECE_BOOKING_MIGRATION_LOCK_BUSY' );
		}
		$this->connection  = (int) $this->wpdb->get_var( 'SELECT CONNECTION_ID()' );
		$snapshot          = new DatabaseSnapshot( $this->wpdb );
		$started           = microtime( true );
		$sql_lock_seconds  = 0.0;
		$lock_wait_seconds = 0.0;
		$rename_seconds    = 0.0;
		try {
			// A second migrator must not inspect yesterday's namespace while the
			// first one is renaming it. Validate the layout only after serialization.
			$preflight = $this->preflight();
			if ( DatabaseState::ready( $this->wpdb ) && ! $gate->active() ) {
				return array(
					'status'          => 'PASS',
					'schema'          => 13,
					'tables'          => 24,
					'already_current' => true,
				);
			}
			$gate->install();
			$gate_started = microtime( true );
			$gate->drain();
			$drain_seconds = microtime( true ) - $gate_started;
			$this->assert_connection();
			$layout  = DatabaseState::layout( $this->wpdb );
			$journal = $this->journal();
			if ( $layout['state'] === 'current' && ! $journal ) {
				throw new RuntimeException( 'ABECE_BOOKING_RENAMED_TABLES_WITHOUT_JOURNAL' );
			}
			$tables         = $snapshot->tables();
			$lock_requested = microtime( true );
			$snapshot->lock( $tables );
			$locked_at          = microtime( true );
			$lock_wait_seconds += $locked_at - $lock_requested;
			$this->assert_connection();
			if ( $journal ) {
				$backup = $this->verified_manifest( $journal );
			} else {
				if ( $layout['state'] !== 'legacy' ) {
					throw new RuntimeException( 'ABECE_BOOKING_SOURCE_NAMESPACE_INVALID' );
				}
				$this->phase( 'before_backup' );
				$backup  = $snapshot->create_verified( $directory, $verification_db, $tables );
				$journal = array(
					'phase'           => 'backup_verified',
					'database'        => DB_NAME,
					'prefix'          => $this->wpdb->prefix,
					'manifest'        => $backup['manifest'],
					'manifest_sha256' => $backup['manifest_sha256'],
				);
				$this->write_option( DatabaseState::JOURNAL, $journal );
			}
			$old      = Tables::legacy( $this->wpdb );
			$new      = Tables::all( $this->wpdb );
			$map      = array_combine( array_values( $old ), array_values( $new ) );
			$original = array_intersect_key( $backup['tables'], array_flip( array_values( $old ) ) );
			if ( count( $original ) !== 24 ) {
				throw new RuntimeException( 'ABECE_BOOKING_BACKUP_APPLICATION_TABLES_INCOMPLETE' );
			}
			if ( $layout['state'] === 'legacy' ) {
				DatabaseSnapshot::compare( $original, DatabaseSnapshot::fingerprint( $this->wpdb, array_values( $old ) ) );
				$this->phase( 'before_rename' );
				$this->assert_connection();
				if ( ! $preflight['server']['rename_under_write_locks'] ) {
					// MariaDB has atomic RENAME, but refuses it inside LOCK TABLES.
					// Persistent web/cron gates and drained external workers remain the write barrier.
					$gate->drain( 0 );
					$sql_lock_seconds += microtime( true ) - $locked_at;
					$snapshot->unlock();
					if ( ! $gate->active() ) {
						throw new RuntimeException( 'ABECE_BOOKING_GATE_LOST_BEFORE_RENAME' );
					}
					$gate->drain( 0 );
					$this->assert_connection();
				}
				$parts = array();
				foreach ( $map as $source => $target ) {
					$parts[] = $this->wpdb->prepare( '%i TO %i', $source, $target );
				}
				$rename_started = microtime( true );
				if ( $this->wpdb->query( 'RENAME TABLE ' . implode( ',', $parts ) ) === false ) {
					throw new RuntimeException( 'ABECE_BOOKING_ATOMIC_RENAME_FAILED' );
				}
				$rename_seconds = microtime( true ) - $rename_started;
				if ( ! $preflight['server']['rename_under_write_locks'] ) {
					$lock_requested = microtime( true );
					$snapshot->lock( $snapshot->tables() );
					$locked_at          = microtime( true );
					$lock_wait_seconds += $locked_at - $lock_requested;
				}
				$this->phase( 'after_rename' );
			}
			$this->assert_connection();
			if ( DatabaseState::layout( $this->wpdb )['state'] !== 'current' ) {
				throw new RuntimeException( 'ABECE_BOOKING_MIXED_OR_INCOMPLETE_NAMESPACE' );
			}
			$current = DatabaseSnapshot::fingerprint( $this->wpdb, array_values( $new ) );
			DatabaseSnapshot::compare( $original, $current, $map );
			foreach ( Schema::current( $this->wpdb ) as $table => $sql ) {
				Schema::verify( $this->wpdb, $table, $sql );
			}
			$journal['phase'] = 'data_verified';
			$this->write_option( DatabaseState::JOURNAL, $journal );
			LegacyIdentifiers::migrate();
			$this->phase( 'before_version_commit' );
			$this->assert_connection();
			$this->write_option( Installer::VERSION_OPTION, 13 );
			$journal['phase'] = 'complete';
			$this->write_option( DatabaseState::JOURNAL, $journal );
			$this->phase( 'after_version_commit' );
			$this->assert_connection();
			if ( ! DatabaseState::ready( $this->wpdb ) ) {
				throw new RuntimeException( 'ABECE_BOOKING_SCHEMA13_COMMIT_FAILED' );
			}
			$sql_lock_seconds += microtime( true ) - $locked_at;
			$snapshot->unlock();
			$this->phase( 'before_gate_release' );
			$this->assert_connection();
			$gate->release();
			return array(
				'status'                 => 'PASS',
				'schema'                 => 13,
				'tables'                 => 24,
				'server'                 => $preflight['server'],
				'all_operational_rows_and_definitions_preserved' => true,
				'backup_import_verified' => true,
				'backup_manifest'        => $backup['manifest'],
				'backup_sql_sha256'      => $backup['sql_sha256'],
				'sql_write_lock_seconds' => $sql_lock_seconds,
				'lock_wait_seconds'      => $lock_wait_seconds,
				'rename_seconds'         => $rename_seconds,
				'drain_seconds'          => $drain_seconds,
				'maintenance_seconds'    => microtime( true ) - $gate_started,
				'total_seconds'          => microtime( true ) - $started,
			);
		} finally {
			$snapshot->unlock();
			$this->wpdb->get_var( $this->wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $this->lock_name ) );
			// On any failure the persistent gates remain active for diagnosis/resume/restore.
		}
	}

	/** @param array $grants Actual account grants. @return bool */
	private function has_permissions( array $grants ): bool {
		$database = preg_quote( '`' . DB_NAME . '`', '/' );
		foreach ( $grants as $grant ) {
			if ( ! preg_match( '/^GRANT (.+) ON (?:\*|' . $database . ')\.\* TO /i', $grant, $match ) ) {
				continue;
			}
			$rights = explode( ', ', strtoupper( $match[1] ) );
			if ( $rights === array( 'ALL PRIVILEGES' ) || ! array_diff( array( 'SELECT', 'INSERT', 'UPDATE', 'DELETE', 'CREATE', 'DROP', 'ALTER', 'LOCK TABLES' ), $rights ) ) {
				return true;
			}
		}
		return false;
	}

	/**
	 * Accept only explicit, internal constraints whose names survive RENAME unchanged.
	 * External and automatically renamed constraints need a separate operator review.
	 *
	 * @param list<string> $tables Complete application namespace.
	 * @return void
	 */
	private function verify_foreign_keys( array $tables ): void {
		if ( (int) $this->wpdb->get_var( 'SELECT @@SESSION.foreign_key_checks' ) !== 1 ) {
			throw new RuntimeException( 'ABECE_BOOKING_FOREIGN_KEY_CHECKS_DISABLED' );
		}
		$placeholders = implode( ',', array_fill( 0, count( $tables ), '%s' ) );
		$sql          = "SELECT CONSTRAINT_NAME,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,REFERENCED_TABLE_SCHEMA,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME IS NOT NULL AND ((TABLE_SCHEMA=%s AND TABLE_NAME IN ($placeholders)) OR (REFERENCED_TABLE_SCHEMA=%s AND REFERENCED_TABLE_NAME IN ($placeholders))) ORDER BY CONSTRAINT_NAME,ORDINAL_POSITION";
		$keys         = $this->wpdb->get_results( $this->wpdb->prepare( $sql, array_merge( array( DB_NAME ), $tables, array( DB_NAME ), $tables ) ), ARRAY_A );
		if ( $this->wpdb->last_error ) {
			throw new RuntimeException( 'ABECE_BOOKING_FOREIGN_KEY_INSPECTION_FAILED' );
		}
		$groups = array();
		foreach ( $keys as $key ) {
			if ( $key['TABLE_SCHEMA'] !== DB_NAME || $key['REFERENCED_TABLE_SCHEMA'] !== DB_NAME || ! in_array( $key['TABLE_NAME'], $tables, true ) || ! in_array( $key['REFERENCED_TABLE_NAME'], $tables, true ) || preg_match( '/^' . preg_quote( $key['TABLE_NAME'], '/' ) . '_ibfk_[0-9]+$/iD', $key['CONSTRAINT_NAME'] ) ) {
				throw new RuntimeException( 'ABECE_BOOKING_FOREIGN_KEY_REQUIRES_OPERATOR_REVIEW' );
			}
			$groups[ $key['CONSTRAINT_NAME'] ][] = $key;
		}
		foreach ( $groups as $columns ) {
			$joins = $present = array();
			foreach ( $columns as $column ) {
				$joins[]   = $this->wpdb->prepare( 's.%i=t.%i', $column['COLUMN_NAME'], $column['REFERENCED_COLUMN_NAME'] );
				$present[] = $this->wpdb->prepare( 's.%i IS NOT NULL', $column['COLUMN_NAME'] );
			}
			$sql = $this->wpdb->prepare( 'SELECT COUNT(*) FROM %i s LEFT JOIN %i t ON ', $columns[0]['TABLE_NAME'], $columns[0]['REFERENCED_TABLE_NAME'] ) . implode( ' AND ', $joins ) . ' WHERE ' . implode( ' AND ', $present ) . $this->wpdb->prepare( ' AND t.%i IS NULL', $columns[0]['REFERENCED_COLUMN_NAME'] );
			if ( (int) $this->wpdb->get_var( $sql ) !== 0 || $this->wpdb->last_error ) {
				throw new RuntimeException( 'ABECE_BOOKING_FOREIGN_KEY_REFERENCE_INVALID' );
			}
		}
	}

	/** @return array<string, mixed> */
	private function journal(): array {
		$raw = $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT option_value FROM %i WHERE option_name=%s', $this->wpdb->options, DatabaseState::JOURNAL ) );
		if ( $this->wpdb->last_error ) {
			throw new RuntimeException( 'ABECE_BOOKING_JOURNAL_READ_FAILED' );
		}
		$journal = $raw === null ? array() : maybe_unserialize( $raw );
		if ( ! is_array( $journal ) || ( $journal && ( ( $journal['database'] ?? '' ) !== DB_NAME || ( $journal['prefix'] ?? '' ) !== $this->wpdb->prefix ) ) ) {
			throw new RuntimeException( 'ABECE_BOOKING_JOURNAL_IDENTITY_INVALID' );
		}
		return $journal;
	}

	/** @param array $journal Owned migration journal. @return array<string, mixed> */
	private function verified_manifest( array $journal ): array {
		$path = $journal['manifest'] ?? '';
		DatabaseSnapshot::private_directory( dirname( $path ) );
		if ( ! is_file( $path ) || ! hash_equals( (string) ( $journal['manifest_sha256'] ?? '' ), hash_file( 'sha256', $path ) ) ) {
			throw new RuntimeException( 'ABECE_BOOKING_BACKUP_MANIFEST_HASH_INVALID' );
		}
		$backup = json_decode( file_get_contents( $path ), true, 512, JSON_THROW_ON_ERROR );
		if ( ( $backup['database'] ?? '' ) !== DB_NAME || ( $backup['prefix'] ?? '' ) !== $this->wpdb->prefix || ( $backup['restored_and_verified'] ?? false ) !== true ) {
			throw new RuntimeException( 'ABECE_BOOKING_VERIFIED_BACKUP_IDENTITY_INVALID' );
		}
		foreach ( array( 'sql', 'records' ) as $kind ) {
			if ( dirname( $backup[ $kind ] ) !== dirname( $path ) || ! is_file( $backup[ $kind ] ) || ! hash_equals( $backup[ $kind . '_sha256' ], hash_file( 'sha256', $backup[ $kind ] ) ) ) {
				throw new RuntimeException( 'ABECE_BOOKING_BACKUP_BYTES_CHANGED' );
			}
		}
		$backup['manifest'] = $path;
		return $backup;
	}

	/** @param string $name Fixed owned option. @param mixed $value Journal/version value. @return void */
	private function write_option( string $name, mixed $value ): void {
		if ( ! in_array( $name, array( DatabaseState::JOURNAL, Installer::VERSION_OPTION ), true ) ) {
			throw new RuntimeException( 'ABECE_BOOKING_MIGRATION_OPTION_INVALID' );
		}
		if ( $this->wpdb->query( $this->wpdb->prepare( "INSERT INTO %i(option_name,option_value,autoload) VALUES(%s,%s,'no') ON DUPLICATE KEY UPDATE option_value=VALUES(option_value)", $this->wpdb->options, $name, maybe_serialize( $value ) ) ) === false ) {
			throw new RuntimeException( 'ABECE_BOOKING_MIGRATION_OPTION_WRITE_FAILED' );
		}
		wp_cache_delete( $name, 'options' );
		wp_cache_delete( 'alloptions', 'options' );
		wp_cache_delete( 'notoptions', 'options' );
	}

	/** @return void */
	private function assert_connection(): void {
		$id    = (int) $this->wpdb->get_var( 'SELECT CONNECTION_ID()' );
		$owner = (int) $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT IS_USED_LOCK(%s)', $this->lock_name ) );
		if ( $id !== $this->connection || $owner !== $id || $this->wpdb->last_error ) {
			throw new RuntimeException( 'ABECE_BOOKING_MIGRATION_CONNECTION_CHANGED' );
		}
	}

	/** @param string $phase Fixed workflow event, also used by failure-injection tests. @return void */
	private function phase( string $phase ): void {
		do_action( 'abece_booking_schema13_phase', $phase );
	}
}