<?php
/**
 * Database utility class.
 *
 * @package Ealab\Deployer
 */

namespace Ealab\Deployer\Utility;

use Dakwamine\Component\ComponentBasedObject;
use Dakwamine\Component\Exception\UnmetDependencyException;
use Ealab\Deployer\Exception\WpDeployException;
use PhpMyAdmin\SqlParser\Parser;
use wpdb;

/**
 * Database utility class.
 */
class Database extends ComponentBasedObject {
	/**
	 * The prefix used for all the tracking database tables.
	 */
	public const PLUGIN_TABLE_NAME_PREFIX = 'dploy_';

	/**
	 * The name of the table used to track the SQL changes.
	 */
	public const TRACK_QUEUE_TABLE_NAME = 'dploy_track_queue';

	/**
	 * The prefix used for the tracking metadata tables.
	 */
	public const TRACKING_METADATA_TABLE_NAME_PREFIX = 'dploy_tmd_';

	/**
	 * Hashed column names, keyed by their original name.
	 *
	 * Use the getHashedColumnName() method to get the hashed name.
	 *
	 * @var array<string,string>
	 */
	private static array $hashed_column_names = array();

	/**
	 * Hashed table names, keyed by their original name.
	 *
	 * Use the getHashedTableName() method to get the hashed name.
	 *
	 * @var array<string,string>
	 */
	private static array $hashed_table_names = array();

	/**
	 * Hashed trigger names, keyed by their original name.
	 *
	 * Use the getHashedTriggerName() method to get the hashed name.
	 *
	 * @var array<string,string>
	 */
	private static array $hashed_trigger_names = array();

	/**
	 * The main instance. Use the getInstance() method to get it.
	 *
	 * @var Database|null
	 */
	private static ?Database $instance = null;

	/**
	 * The results cache.
	 *
	 * @var ResultsCache
	 */
	private static ResultsCache $results_cache;

	/**
	 * The tracking database handle.
	 *
	 * @var wpdb|null
	 */
	private static ?wpdb $tracking_database_handle = null;

	/**
	 * Checks if a column exists in a table.
	 *
	 * @param string    $table_name The table name.
	 * @param string    $column_name The column name.
	 * @param wpdb|null $wpdb Optionally set a wpdb instance to use. Useful when another database than the default one
	 *     is used.
	 *
	 * @return bool
	 *   True if the column exists, false otherwise.
	 *
	 * @throws WpDeployException Cannot get the column names.
	 */
	public static function columnExists( string $table_name, string $column_name, ?wpdb $wpdb = null ): bool {
		$database = $wpdb ?? static::getTrackingDatabaseHandle();

		$columns = static::getAllColumnNames( $table_name, $database );

		return in_array( $column_name, $columns, true );
	}

	/**
	 * Performs a dbDelta operation.
	 *
	 * @param string    $sql The SQL to execute.
	 * @param bool      $append_default_charset_collation Whether to append the default charset and collation to the
	 *     SQL.
	 * @param wpdb|null $wpdb Optionally set a wpdb instance to use. Useful when another database than the default one
	 *     is used.
	 *
	 * @return array The result of the dbDelta operation. This is helpful to check the changes performed to
	 *         the database. Note: always check this result to make sure that the given SQL is idempotent with
	 *         the current database state after the update was performed.
	 *         When idempotent, the result should be an empty array.
	 */
	public static function doDbDelta( string $sql, bool $append_default_charset_collation = true, ?wpdb $wpdb = null ): array {
		// Recommended by: https://codex.wordpress.org/Creating_Tables_with_Plugins.
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		if ( $append_default_charset_collation ) {
			// Remove the last semicolon if any.
			$sql = rtrim( $sql, ';' );

			$sql .= ' ' . ( $wpdb ?? static::getTrackingDatabaseHandle() )->get_charset_collate() . ';';
		}

		return dbDelta( $sql );
	}

	/**
	 * Drops a table.
	 *
	 * @param string    $table_name The table name.
	 * @param bool      $prepend_prefix Whether to prepend the default table prefix to the table name.
	 * @param wpdb|null $wpdb Optionally set a wpdb instance to use. Useful when another database than the default one
	 *     is used.
	 *
	 * @return bool|null True if the table was dropped, null if it does not exist. False otherwise.
	 */
	public static function dropTable( string $table_name, bool $prepend_prefix = true, ?wpdb $wpdb = null ): ?bool {
		$database = $wpdb ?? static::getTrackingDatabaseHandle();

		$full = ( $prepend_prefix ? $database->prefix : '' ) . $table_name;

		if ( ! static::tableExists( $full, $database ) ) {
			return null;
		}

		return $database->query( $database->prepare( 'DROP TABLE IF EXISTS %i', $full ) );
	}

	/**
	 * Gets all column names.
	 *
	 * @param string    $table_name The table name.
	 * @param wpdb|null $wpdb Optionally set a wpdb instance to use. Useful when another database than the default one
	 *     is used.
	 *
	 * @return array The column names. May be empty if the table does not exist.
	 *
	 * @throws WpDeployException Could not get the column names.
	 */
	public static function getAllColumnNames( string $table_name, ?wpdb $wpdb = null ): array {
		$cache_key = $table_name;

		$cached_result = static::getResultsCache()->getCachedResults( 'getAllColumnNames()', $cache_key );

		if ( ! empty( $cached_result ) ) {
			return $cached_result;
		}

		$database = $wpdb ?? static::getTrackingDatabaseHandle();

		if ( ! static::tableExists( $table_name, $database ) ) {
			return array();
		}

		$result = $database->get_col( $database->prepare( 'DESCRIBE %i', $table_name ) );

		static::getResultsCache()->setCachedResults( 'getAllColumnNames()', $cache_key, $result );

		return $result;
	}

	/**
	 * Gets all column names to be inserted into statements prefixed by their table name.
	 *
	 * E.g: "table1.column1 AS 'table1.column1', table1.column2 AS 'table1.column2', table2.column1 AS
	 * 'table2.column1'".
	 *
	 * @param array       $table_names The table names.
	 * @param string|null $query The query to append the column names to. To use in prepared statements.
	 * @param array|null  $arguments The arguments for prepared statement of the query.
	 * @param bool        $strip_db_prefix_from_table_name Whether to strip the database prefix from the table name.
	 * @param wpdb|null   $wpdb Optionally set a wpdb instance to use. Useful when another database than the default
	 *     one is used.
	 *
	 * @throws WpDeployException If an empty table name is provided.
	 */
	public static function getAllColumnNamesPrefixedByTableName( array $table_names, ?string &$query = '', ?array &$arguments = array(), bool $strip_db_prefix_from_table_name = true, ?wpdb $wpdb = null ): void {
		$query     = $query ?? '';
		$arguments = $arguments ?? array();

		// This will allow to retrieve an already cached result if the same table names are provided.
		sort( $table_names );

		$cache_key = md5( json_encode( $table_names ) );

		$cached_result = static::getResultsCache()->getCachedResults( 'getAllColumnNamesPrefixedByTableName()', $cache_key );

		if ( ! empty( $cached_result ) ) {
			$query .= $cached_result['query'];

			$arguments = array_merge( $arguments, $cached_result['arguments'] );

			return;
		}

		if ( empty( $table_names ) ) {
			// No need to cache this empty result.
			return;
		}

		$database = $wpdb ?? static::getTrackingDatabaseHandle();

		// This will prefix the column names to avoid collisions
		// between the metadata and the data columns.
		$column_names = array();

		foreach ( $table_names as $table_name ) {
			if ( empty( $table_name ) ) {
				throw new WpDeployException( 'Empty table name.' );
			}

			if ( $strip_db_prefix_from_table_name ) {
				$final_table_name = str_replace( $database->prefix, '', $table_name );
			} else {
				$final_table_name = $table_name;
			}

			$result = static::getAllColumnNames( $table_name, $database );

			if ( empty( $result ) ) {
				// The table does not exist, or there are no columns.
				continue;
			}

			foreach ( $result as $column_name ) {
				$column_names[] = '%i.%i AS %s';

				$arguments[] = $table_name;
				$arguments[] = $column_name;
				$arguments[] = $final_table_name . '.' . $column_name;
			}
		}

		$result = implode( ', ', $column_names );

		$to_cache = array(
			'query'     => $result,
			'arguments' => $arguments,
		);

		static::getResultsCache()->setCachedResults( 'getAllColumnNamesPrefixedByTableName()', $cache_key, $to_cache );

		$query .= $result;
	}

	/**
	 * Get All database tables, even TEMPORARY, VIEW tables, and tables with other prefixes.
	 *
	 * @param wpdb|null $wpdb Optionally set a wpdb instance to use.
	 *                        Useful when another database than the default one is used.
	 *
	 * @return string[] The table names.
	 */
	public static function getAllTables( ?wpdb $wpdb = null ): array {
		$database = $wpdb ?? static::getDatabaseHandle();

		$all_array = $database->get_results( 'SHOW TABLES', ARRAY_N );

		$all = array();

		foreach ( $all_array as $key => $val ) {
			array_push( $all, $val[0] );
		}

		return $all;
	}

	/**
	 * Gets the database handle.
	 *
	 * @return wpdb
	 *   The database handle.
	 */
	public static function getDatabaseHandle(): wpdb {
		global $wpdb;

		return $wpdb;
	}

	/**
	 * Get Database prefix
	 *
	 * @param wpdb|null $wpdb Optionally set a wpdb instance to use.
	 *                        Useful when another database than the default one is used.
	 *
	 * @return string The database prefix.
	 */
	public static function getDatabasePrefix( ?wpdb $wpdb = null ): string {
		$database = $wpdb ?? static::getDatabaseHandle();

		return $database->prefix;
	}

	/**
	 * Gets the hashed local ID column name.
	 *
	 * @param string $column_name The column name.
	 *
	 * @return string The hashed local ID column name.
	 *
	 * @deprecated nommer le champ local_id tout court, il n'y aura qu'une seule colonne pour les local ID.
	 */
	public static function getHashedLocalIdColumnName( string $column_name ): string {
		if ( empty( static::$hashed_column_names[ $column_name ] ) ) {
			// This will produce up to 64 characters.
			static::$hashed_column_names[ $column_name ] = 'local_id_' . substr( $column_name, 0, 15 ) . sha1( $column_name );
		}

		return static::$hashed_column_names[ $column_name ];
	}

	/**
	 * The table name is hashed to avoid too long tracking metadata table names.
	 *
	 * @param string    $unprefixed_table_name The table name without prefix.
	 * @param bool      $with_prefix Whether to prepend the default table prefix to the hashed table name.
	 * @param wpdb|null $wpdb Optionally set a wpdb instance to use. Useful when another database than the default one
	 *     is used.
	 *
	 * @return string The hashed table name.
	 *
	 * @throws WpDeployException Error on building the hashed table name.
	 */
	public static function getHashedTableName( string $unprefixed_table_name, bool $with_prefix = true, ?wpdb $wpdb = null ): string {
		if ( empty( static::$hashed_table_names[ $unprefixed_table_name ] ) ) {
			$database = ( $wpdb ?? static::getTrackingDatabaseHandle() );

			// The hashed table name is built like this:
			// - DB prefix, up to 26 characters
			// - Tracking metadata table name prefix (dploy_tmd_), 10 characters
			// - The real unprefixed table name, cut to 15 characters (can be less if the table name is shorter)
			// - '_' separator, 1 character
			// - Hash of the real unprefixed table name, 12 characters
			// The max total length is 26+10+15+1+12=64 characters.
			// The total length can be less if the DB prefix or the table name are shorter.
			if ( 26 < strlen( $database->prefix ) ) {
				throw new WpDeployException( _dgettext( 'deployer-core', 'The database prefix is too long. It must be 26 characters or less.' ) );
			}

			$table_name_first_part = $database->prefix . static::TRACKING_METADATA_TABLE_NAME_PREFIX . substr( $unprefixed_table_name, 0, 15 );

			$full_name = $table_name_first_part . '_' . substr( md5( $unprefixed_table_name ), 0, 12 );

			if ( 64 < strlen( $full_name ) ) {
				// i18n: Error message when the system tried to name a database table, but the name could not fit in the 64 characters length.
				throw new WpDeployException( _dgettext( 'deployer-core', 'The hashed table name is too long.' ) );
			}

			static::$hashed_table_names[ $unprefixed_table_name ] = $full_name;
		}

		if ( ! $with_prefix ) {
			// Strip the prefix.
			return substr( static::$hashed_table_names[ $unprefixed_table_name ], strlen( ( $wpdb ?? static::getTrackingDatabaseHandle() )->prefix ) );
		}

		return static::$hashed_table_names[ $unprefixed_table_name ];
	}

	/**
	 * Gets the hashed trigger name.
	 *
	 * The trigger name is hashed to avoid too long trigger names.
	 *
	 * @param string    $unprefixed_trigger_name The trigger name without DB prefix.
	 * @param wpdb|null $wpdb Optionally set a wpdb instance to use. Useful when another database than the default one
	 *     is used.
	 *
	 * @return string The hashed trigger name.
	 *
	 * @throws WpDeployException Error on generation.
	 */
	public static function getHashedTriggerName( string $unprefixed_trigger_name, ?wpdb $wpdb = null ): string {
		if ( empty( static::$hashed_trigger_names[ $unprefixed_trigger_name ] ) ) {
			$database = ( $wpdb ?? static::getTrackingDatabaseHandle() );

			// The hashed trigger name is built like this:
			// - DB prefix, up to 26 characters
			// - Plugin prefix (dploy_), 6 characters
			// - The unprefixed trigger name, cut to 19 characters (can be less if the identifier is shorter)
			// - '_' separator, 1 character
			// - Hash of the real unprefixed trigger name, 12 characters
			// The max total length is 26+6+19+1+12=64 characters.
			// The total length can be less if the DB prefix or the trigger name are shorter.
			if ( 26 < strlen( $database->prefix ) ) {
				throw new WpDeployException( 'The database prefix is too long. It must be 26 characters or less.' );
			}

			$trigger_name_first_part = $database->prefix . static::PLUGIN_TABLE_NAME_PREFIX . substr( $unprefixed_trigger_name, 0, 19 );

			$full_name = $trigger_name_first_part . '_' . substr( md5( $unprefixed_trigger_name ), 0, 12 );

			if ( 64 < strlen( $full_name ) ) {
				throw new WpDeployException( 'The hashed trigger name is too long.' );
			}

			static::$hashed_trigger_names[ $unprefixed_trigger_name ] = $full_name;
		}

		return static::$hashed_trigger_names[ $unprefixed_trigger_name ];
	}

	/**
	 * Gets the main instance of the class.
	 *
	 * @return static The main instance of the class.
	 *
	 * @throws WpDeployException Error on getting the main instance.
	 */
	public static function getInstance(): ?Database {
		if ( empty( static::$instance ) ) {
			try {
				static::$instance = static::getRootComponentByClassName( static::class, true );
			} catch ( UnmetDependencyException $e ) {
				throw new WpDeployException( 'Error on getting the main instance of the class.', 0, $e );
			}
		}

		return static::$instance;
	}

	/**
	 * Gets the definition of the primary keys columns.
	 *
	 * @param string    $table_name The table name to inspect.
	 * @param wpdb|null $wpdb Optionally set a wpdb instance to use. Useful when another database than the default one
	 *     is used.
	 *
	 * @return array Array of columns that are primary keys.
	 */
	public static function getPrimaryKeysColumnsDefinition( string $table_name, ?wpdb $wpdb = null ): array {
		$database = $wpdb ?? static::getTrackingDatabaseHandle();

		if ( ! static::tableExists( $table_name, $database ) ) {
			return array();
		}

		$primary_keys = $database->get_results(
			$database->prepare(
				<<<'SQL'
SELECT * FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` = %s AND `TABLE_NAME` = %s AND COLUMN_KEY = 'PRI';
SQL,
				$database->dbname,
				$table_name
			),
			ARRAY_A
		);

		if ( empty( $primary_keys ) ) {
			return array();
		}

		return $primary_keys;
	}

	/**
	 * Gets the table names.
	 *
	 * @param array|null $options Optionally set options for the table names. Available options:
	 *   - 'without_prefix' (bool): Whether to strip the prefix from the table names.
	 *   - 'without_dsc_tables' (bool): Whether to exclude the Deploy & Sync Content tables.
	 * @param wpdb|null  $wpdb Optionally set a wpdb instance to use. Useful when another database than the default one
	 *     is used.
	 *
	 * @return string[] The table names.
	 */
	public static function getTableNames( ?array $options = null, ?wpdb $wpdb = null ): array {
		$database = $wpdb ?? static::getTrackingDatabaseHandle();

		// This will exclude views tables and return only the tables from the current instance.
		$table_names = $database->get_col(
			$database->prepare(
				<<<SQL
SELECT `table_name` FROM `information_schema`.`TABLES` WHERE `TABLES`.`TABLE_TYPE` = 'BASE TABLE' AND `TABLES`.`TABLE_SCHEMA` = %s AND `TABLES`.`TABLE_NAME` LIKE %s;
SQL,
				$database->dbname,
				$database->esc_like( $database->prefix ) . '%'
			)
		);

		if ( ! empty( $options['without_dsc_tables'] ) ) {
			// Filter out the tables of our plugin.
			$table_names = array_filter(
				$table_names,
				function ( $table_name ) {
					$unprefixed = Database::removeDatabasePrefix( $table_name );

					return 0 !== strpos( $unprefixed, Database::PLUGIN_TABLE_NAME_PREFIX );
				}
			);
		}

		if ( ! empty( $options['without_prefix'] ) ) {
			$table_names = array_map(
				function ( $table_name ) use ( $database ) {
					return static::removeDatabasePrefix( $table_name, $database );
				},
				$table_names
			);
		}

		return $table_names;
	}

	/**
	 * Gets the database handle used by the tracking methods.
	 *
	 * @return wpdb The database handle.
	 */
	public static function getTrackingDatabaseHandle(): wpdb {
		if ( ! empty( static::$tracking_database_handle ) ) {
			return static::$tracking_database_handle;
		}

		$database = static::getDatabaseHandle();

		if ( $database instanceof WpdbWrapper ) {
			static::$tracking_database_handle = clone $database;

			// Reset the result to avoid side effects,
			// such as closing the mysql result resource of a previous query
			// that may still be processed by the original $wpdb instance.
			static::$tracking_database_handle->result = null;

			return static::$tracking_database_handle;
		}

		// This happens when some code is performed before the WpdbWrapper is initialized.
		// This should almost not happen in normal circumstances because the wrapper is initialized very early.
		return $database;
	}

	/**
	 * Get Plugins database tables.
	 *
	 * @param wpdb|null $wpdb Optionally set a wpdb instance to use.
	 *                        Useful when another database than the default one is used.
	 *
	 * @return string[] The table names.
	 */
	public static function getPluginsTables( ?wpdb $wpdb = null ): array {
		$database = $wpdb ?? static::getDatabaseHandle();

		return array_diff(
			static::getTableNames(
				array( 'without_dsc_tables' => true ),
				$database
			),
			static::getWpCoreTables( null, $database )
		);
	}

	/**
	 * Gets the results cache.
	 *
	 * @return ResultsCache The results cache.
	 *
	 * @throws WpDeployException If the results cache could not be instantiated.
	 */
	protected static function getResultsCache(): ResultsCache {
		if ( empty( static::$results_cache ) ) {
			try {
				static::$results_cache = static::getInstance()->getComponentByClassName( ResultsCache::class, true );
			} catch ( UnmetDependencyException $e ) {
				// This should not happen.
				throw new WpDeployException( 'Could not instantiate the results cache.' );
			}
		}

		return static::$results_cache;
	}

	/**
	 * Get WP Core database tables.
	 *
	 * @param array<string,mixed> $options Options:
	 *     "without_prefix", boolean: set false to not include the DB prefix. Defaults to true.
	 * @param wpdb|null           $wpdb Optionally set a wpdb instance to use.
	 *                        Useful when another database than the default one is used.
	 *
	 * @return string[] The WP core table names.
	 */
	public static function getWpCoreTables( ?array $options = null, ?wpdb $wpdb = null ): array {
		// List of core WordPress tables.
		// TODO: there are more core tables. Add them (multisite tables).
		$core_tables_array = array(
			'commentmeta',
			'comments',
			'links',
			'options',
			'postmeta',
			'posts',
			'termmeta',
			'terms',
			'term_relationships',
			'term_taxonomy',
			'usermeta',
			'users',
		);

		if ( empty( $options['without_prefix'] ) ) {
			$database     = $wpdb ?? static::getDatabaseHandle();
			$table_prefix = $database->prefix;

			$core_tables_array = array_map( fn( $i ) => $table_prefix . $i, $core_tables_array );
		}

		return $core_tables_array;
	}

	/**
	 * Parses a SQL query.
	 *
	 * @param string $query The SQL query. Preferably in MySQL dialect.
	 *
	 * @return Parser The parser.
	 */
	public static function parseSqlQuery( string $query ) {
		return new Parser( $query );
	}

	/**
	 * Prepends the database prefix to a table name.
	 *
	 * @param string    $table_name The table name to prepend the prefix to.
	 * @param wpdb|null $wpdb Optionally set a wpdb instance to use. Useful when another database than the default one
	 *     is used.
	 *
	 * @return string The table name with the database prefix.
	 */
	public static function prependDatabasePrefix( string $table_name, ?wpdb $wpdb = null ): string {
		$database = $wpdb ?? static::getTrackingDatabaseHandle();

		return $database->prefix . $table_name;
	}

	/**
	 * Removes the database prefix.
	 *
	 * @param string    $table_name The table name.
	 * @param wpdb|null $wpdb Optionally set a wpdb instance to use. Useful when another database than the default one
	 *     is used.
	 *
	 * @return string The table name without the database prefix.
	 */
	public static function removeDatabasePrefix( string $table_name, ?wpdb $wpdb = null ): string {
		$database = $wpdb ?? static::getTrackingDatabaseHandle();

		if ( 0 !== strpos( $table_name, $database->prefix ) ) {
			// The given table name is not prefixed by the current database prefix.
			return $table_name;
		}

		return substr( $table_name, strlen( $database->prefix ) );
	}

	/**
	 * Removes the given prefix from the input string.
	 *
	 * See also ::removeDatabasePrefix().
	 *
	 * @param string $input_string The input string.
	 * @param string $prefix_to_remove The prefix to remove.
	 *
	 * @return string The input string without the prefix.
	 */
	public static function removePrefix( string $input_string, string $prefix_to_remove ): string {
		// Check if the input string starts with the specified prefix.
		if ( 0 === strpos( $input_string, $prefix_to_remove ) ) {
			// Remove the prefix from the input string.
			return substr( $input_string, strlen( $prefix_to_remove ) );
		}

		// If the input string doesn't start with the prefix, return the original string.
		return $input_string;
	}

	/**
	 * Checks if a table exists.
	 *
	 * @param string    $table_name The table name.
	 * @param wpdb|null $wpdb Optionally set a wpdb instance to use.
	 *                        Useful when another database than the default one is used.
	 *
	 * @return bool True if the table exists, false otherwise.
	 */
	public static function tableExists( string $table_name, ?wpdb $wpdb = null ): bool {
		$database = $wpdb ?? static::getTrackingDatabaseHandle();

		$sql = $database->prepare(
			<<<'SQL'
SELECT `TABLE_NAME`
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE `TABLE_SCHEMA`=%s
  AND `TABLE_NAME`=%s;
SQL,
			DB_NAME,
			$table_name
		);

		$tables = $database->get_col( $sql );

		return in_array( $table_name, $tables, true );
	}

}
