<?php
/**
 * Database installer.
 *
 * @package FormPilot
 * @since   1.0.0
 */

declare( strict_types = 1 );

namespace FormPilot\Database;

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

/**
 * Creates, upgrades and removes the plugin tables.
 *
 * Every method operates on the current site, so multisite callers are
 * responsible for switching blogs before invoking them.
 *
 * @since 1.0.0
 */
final class Installer {

	/**
	 * Transient guarding against concurrent installs.
	 */
	private const LOCK_TRANSIENT = 'formpilot_installing';

	/**
	 * Lifetime of the install lock, in seconds.
	 */
	private const LOCK_TIMEOUT = 300;

	/**
	 * Token of the install lock this request holds, if any.
	 *
	 * @var string
	 */
	private static string $lock_token = '';

	/**
	 * Creates or updates every plugin table.
	 *
	 * `dbDelta()` compares the desired schema with the live one and issues the
	 * required `CREATE`/`ALTER` statements, which makes this method safe to run
	 * on both fresh installs and upgrades.
	 *
	 * @return array<int, string> The queries dbDelta reported as executed.
	 */
	public static function install(): array {
		/*
		 * Taking the lock is one operation, not a read followed by a write.
		 *
		 * `get_transient()` then `set_transient()` is a check-then-act: two upgrade
		 * requests arriving together both saw no lock and both ran `dbDelta`.
		 * `add_option()` is atomic — the option name column is unique, so exactly
		 * one caller creates the row — and it is the same primitive the notification
		 * queue uses, for the same reason.
		 */
		if ( ! self::lock() ) {
			return array();
		}

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

		$installed_version = self::installed_version();
		$queries           = array();

		/*
		 * The lock is released whatever happens. It used to be released only on the
		 * happy path, so a `dbDelta()` or migration that threw left the transient
		 * set for its full five minutes — during which `install()` answered with an
		 * empty array while `maybe_upgrade()` still reported success, and the site
		 * ran against a half-migrated schema with nothing saying so.
		 */
		try {
			foreach ( Schema::statements() as $sql ) {
				$queries = array_merge( $queries, array_values( (array) dbDelta( $sql ) ) );
			}

			self::run_migrations( $installed_version );

			update_option( Schema::DB_VERSION_OPTION, Schema::DB_VERSION );
		} finally {
			self::unlock();
		}

		/**
		 * Fires after the plugin tables have been created or updated.
		 *
		 * @since 1.0.0
		 *
		 * @param string $installed_version Schema version present before this run. Empty on a fresh install.
		 * @param string $target_version    Schema version that has just been written.
		 */
		do_action( 'formpilot_database_installed', $installed_version, Schema::DB_VERSION );

		return $queries;
	}

	/**
	 * Runs the installer when the stored schema version is out of date.
	 *
	 * @return bool True when an install or upgrade was performed.
	 */
	public static function maybe_upgrade(): bool {
		if ( ! self::needs_upgrade() ) {
			return false;
		}

		self::install();

		return true;
	}

	/**
	 * Determines whether the schema is missing or outdated.
	 *
	 * @return bool True when {@see Installer::install()} should run.
	 */
	public static function needs_upgrade(): bool {
		if ( version_compare( self::installed_version(), Schema::DB_VERSION, '<' ) ) {
			return true;
		}

		return array() !== self::missing_tables();
	}

	/**
	 * Returns the schema version stored for the current site.
	 *
	 * @return string The stored version, or an empty string when never installed.
	 */
	public static function installed_version(): string {
		return (string) get_option( Schema::DB_VERSION_OPTION, '' );
	}

	/**
	 * Returns the plugin tables that do not exist yet.
	 *
	 * @return array<int, string> Prefixed names of the missing tables.
	 */
	public static function missing_tables(): array {
		global $wpdb;

		$missing = array();

		foreach ( Schema::tables() as $table ) {
			$table_name = Schema::table( $table );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema check, not cacheable.
			$found = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table_name ) ) );

			/*
			 * Without regard to case. With lower_case_table_names set - the default
			 * on Windows and macOS - MySQL stores and returns table names in lower
			 * case, so a site whose prefix has capitals saw every table reported
			 * missing: the installer re-ran on every admin page load, and the Tools
			 * screen said the tables did not exist.
			 */
			if ( ! is_string( $found ) || 0 !== strcasecmp( $found, $table_name ) ) {
				$missing[] = $table_name;
			}
		}

		return $missing;
	}

	/**
	 * Drops every plugin table.
	 *
	 * Only called from the uninstall routine, once the site owner opted into
	 * full data removal.
	 */
	public static function drop_tables(): void {
		global $wpdb;

		/*
		 * Child tables first so partially failing drops never orphan referenced
		 * rows.
		 *
		 * Taken from `Schema::tables()` rather than listed again here. The
		 * hand-written list left out `formpilot_webhook_log` — the fifth table, added
		 * later — so an uninstall with "delete data" ticked left behind a table
		 * holding a full copy of every payload the site had ever sent, for ever,
		 * with nothing left that knew it was there.
		 */
		$known  = Schema::tables();
		$order  = array( Schema::SUBMISSION_META, Schema::WEBHOOK_LOG, Schema::SUBMISSIONS, Schema::FIELDS, Schema::FORMS );
		$tables = array_values( array_unique( array_merge( array_intersect( $order, $known ), $known ) ) );

		foreach ( $tables as $table ) {
			$table_name = esc_sql( Schema::table( $table ) );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Opt-in uninstall of the plugin's own tables; identifiers cannot be prepared and the name is built from a hardcoded list.
			$wpdb->query( "DROP TABLE IF EXISTS {$table_name}" );
		}

		delete_option( Schema::DB_VERSION_OPTION );
	}

	/**
	 * Runs the data migrations required between two schema versions.
	 *
	 * `dbDelta()` already handles column and index changes, so this only covers
	 * data transformations that SQL structure alone cannot express.
	 *
	 * @param string $from Schema version currently installed. Empty on a fresh install.
	 */
	private static function run_migrations( string $from ): void {
		if ( '' === $from ) {
			// Fresh install: dbDelta created the final schema, nothing to migrate.
			return;
		}

		/**
		 * Filters the registered data migrations.
		 *
		 * Keys are the schema version a migration upgrades *to*, values are
		 * callables receiving no arguments. Migrations run in version order and
		 * only when the installed version is older than their key.
		 *
		 * @since 1.0.0
		 *
		 * @param array<string, callable> $migrations Migration callbacks keyed by target version.
		 */
		$migrations = (array) apply_filters( 'formpilot_database_migrations', self::migrations() );

		uksort( $migrations, 'version_compare' );

		foreach ( $migrations as $version => $callback ) {
			if ( ! is_callable( $callback ) ) {
				continue;
			}

			if ( version_compare( $from, (string) $version, '>=' ) ) {
				continue;
			}

			call_user_func( $callback );
		}
	}

	/**
	 * Returns the migrations shipped with the plugin.
	 *
	 * `dbDelta()` only adds and widens, so anything it cannot express lives
	 * here: dropped indexes, dropped columns, and data rewrites.
	 *
	 * @return array<string, callable> Migration callbacks keyed by target version.
	 */
	private static function migrations(): array {
		return array(
			'1.1.0' => static function (): void {
				/*
				 * `meta_key` alone was indexed, but no query filters on it: the
				 * composite (submission_id, meta_key) serves every lookup and a
				 * lone key on a low cardinality column only costs writes.
				 */
				self::drop_index( Schema::SUBMISSION_META, 'meta_key' );
			},
		);
	}

	/**
	 * Drops an index when it is present.
	 *
	 * @param string $table Unprefixed table identifier.
	 * @param string $index Index name.
	 *
	 * @return bool True when the index was dropped.
	 */
	public static function drop_index( string $table, string $index ): bool {
		global $wpdb;

		$table_name = esc_sql( self::known_table( $table ) );
		$index      = preg_replace( '/[^a-zA-Z0-9_]/', '', $index );

		if ( '' === $table_name || ! is_string( $index ) || '' === $index ) {
			return false;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Identifiers cannot be prepared; the table is allowlisted and the index sanitised above.
		$exists = $wpdb->get_var( $wpdb->prepare( "SHOW INDEX FROM {$table_name} WHERE Key_name = %s", $index ) );

		if ( null === $exists ) {
			return false;
		}

		$index_name = esc_sql( $index );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- A versioned migration: dbDelta() cannot drop an index. See above.
		return false !== $wpdb->query( "ALTER TABLE {$table_name} DROP INDEX `{$index_name}`" );
	}

	/**
	 * Drops a column when it is present.
	 *
	 * @param string $table  Unprefixed table identifier.
	 * @param string $column Column name.
	 *
	 * @return bool True when the column was dropped.
	 */
	public static function drop_column( string $table, string $column ): bool {
		global $wpdb;

		$table_name = esc_sql( self::known_table( $table ) );
		$column     = preg_replace( '/[^a-zA-Z0-9_]/', '', $column );

		if ( '' === $table_name || ! is_string( $column ) || '' === $column ) {
			return false;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Identifiers cannot be prepared; the table is allowlisted and the column sanitised above.
		$exists = $wpdb->get_var( $wpdb->prepare( "SHOW COLUMNS FROM {$table_name} LIKE %s", $column ) );

		if ( null === $exists ) {
			return false;
		}

		$column_name = esc_sql( $column );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- A versioned migration: dbDelta() cannot drop a column. See above.
		return false !== $wpdb->query( "ALTER TABLE {$table_name} DROP COLUMN `{$column_name}`" );
	}

	/**
	 * Resolves one of the plugin's own tables to its prefixed name.
	 *
	 * `drop_index()` and `drop_column()` are public, and the migration hook hands
	 * them to other plugins, so the table is a parameter somebody else chose. A
	 * table name cannot be bound as a placeholder, which leaves an allowlist as the
	 * only thing standing between that parameter and the SQL: anything that is not
	 * one of the tables {@see Schema::tables()} declares is refused.
	 *
	 * @param string $table Unprefixed table identifier.
	 *
	 * @return string The prefixed name, or an empty string for an unknown table.
	 */
	private static function known_table( string $table ): string {
		if ( ! in_array( $table, Schema::tables(), true ) ) {
			return '';
		}

		return Schema::table( $table );
	}

	/**
	 * Acquires the install lock, or reports that somebody else holds it.
	 *
	 * Through {@see Lock}, where the database decides who wins. This used
	 * `add_option()`, which reads a cacheable value first and then upserts, so
	 * two upgrade requests arriving together could both take it. A lock older
	 * than its timeout belonged to a request that died, and is taken over.
	 *
	 * @return bool True when this caller now holds the lock.
	 */
	private static function lock(): bool {
		self::$lock_token = Lock::acquire( self::LOCK_TRANSIENT, self::LOCK_TIMEOUT );

		return '' !== self::$lock_token;
	}

	/**
	 * Releases the install lock.
	 */
	private static function unlock(): void {
		Lock::release( self::LOCK_TRANSIENT, self::$lock_token );

		self::$lock_token = '';

		// Left over from the transient the lock used to be; harmless, but tidy.
		delete_transient( self::LOCK_TRANSIENT );
	}
}