<?php
/**
 * @obfuscate_disable
 */
namespace Claromentis\Setup\Task;

use Claromentis\Core\Config\Config;
use Claromentis\Core\DAL;
use Claromentis\Setup\Exception\DatabaseCheckException;
use LogicException;
use Psr\Log\LoggerInterface;
use Psr\Log\NullLogger;

/**
 * Database verification task.
 *
 * Verifies database permissions required to install and upgrade applications.
 *
 * TODO: FRAM-538 Add integrity checks for character set and collation
 */
class DatabaseCheck
{
	protected Config $config;

	protected DAL\Interfaces\DbInterface $database;

	protected LoggerInterface $logger;

	/**
	 * The last error that occurred during the database checks.
	 */
	protected array $errors = [];

	/**
	 * Create a new database check task.
	 *
	 * @param Config $config
	 * @param DAL\Interfaces\DbInterface $database
	 */
	public function __construct(Config $config, DAL\Interfaces\DbInterface $database)
	{
		$this->config = $config;
		$this->database = $database;
		$this->setLogger(new NullLogger());
	}

	/**
	 * Set the logger.
	 *
	 * @param LoggerInterface $logger
	 */
	public function setLogger(LoggerInterface $logger)
	{
		$this->logger = $logger;
	}

	/**
	 * An error handler that logs any database errors.
	 *
	 * @param int $number
	 * @param string $string
	 * @param string $file
	 * @param int $line
	 */
	public function HandleError($number, $string, $file, $line)
	{
		// Error was suppressed with the @ operator
		if (error_reporting() === 0) {
			return;
		}

		$error = "$string in file $file line $line";
		$this->errors[] = $error;
		$this->logger->error($error);
	}

	/**
	 * Retrieve all errors logged during database checks.
	 *
	 * @return string[]
	 */
	public function GetErrors(): array
	{
		return $this->errors;
	}

	/**
	 * Run the database checks.
	 *
	 * @return bool
	 */
	public function Check(): bool
	{
		$this->logger->info('Checking database permissions...');

		// Set a temporary custom error handler to track any errors that occur
		set_error_handler([$this, 'HandleError']);
		$this->database->DisableTokenCheck();

		// Run each check
		if (!$this->CheckPermissions() || !empty($this->errors)) {
			// Restore the token check and error handler
			$this->database->EnableTokenCheck();
			restore_error_handler();

			// Announce the errors and bail
			$this->logger->error('Database checks failed - see errors above');

			return false;
		}

		// Log the success
		$this->logger->info('Database checks passed');

		// Restore the token check and error handler
		$this->database->EnableTokenCheck();
		restore_error_handler();

		return true;
	}

	/**
	 * Check database permissions for the configured user.
	 *
	 * @return bool Whether the database permissions are adequate
	 */
	protected function CheckPermissions(): bool
	{
		$database = $this->config->get('cfg_db_name');
		$username = $this->config->get('cfg_db_user');

		if (!is_string($database) || !is_string($username)) {
			throw new DatabaseCheckException("The configured database name and username must be strings");
		}

		if (!is_string($database) || !is_string($username)) {
			throw new DatabaseCheckException("The configured database name and username must be strings");
		}

		$result = true;

		switch ($this->database->type())
		{
			case 'mysql':
				$result = $this->CheckMySqlGrants($database, $username);
				break;
			case 'mssql':
			case 'mssql_nc':
			case 'mssql_odbc':
			case 'mssql_sybase':
				$result = $this->CheckSqlServerPermissions($database, $username);
				break;
			case 'sqlite':
				$result = $this->CheckSqlitePermissions();
				break;
			default:
				$this->logger->warning("No permission checks implemented for database type '{$this->database->type()}'");
		}

		return $result;
	}

	/**
	 * Check for adequate MySQL grants for the given database and username.
	 *
	 * @param string $database Database name
	 * @param string $username Database username
	 * @return bool Whether the database permissions are adequate
	 */
	protected function CheckMySqlGrants(string $database, string $username): bool
	{
		static $minimum = array(
			'SELECT', 'INSERT', 'UPDATE', 'DELETE', 'CREATE', 'ALTER',
			'DROP', 'INDEX'
		);

		$result = $this->database->query('SHOW GRANTS');

		// Bail if we haven't found any grants
		if (!$result->hasData()) {
			$this->logger->error("PROBLEM: No MySQL grants set for the configured database user '$username'");

			return false;
		}

		$all_databases = false;
		$allTables = false;
		$matchingGrantsCount = 0;
		$missingGrantsCount = 0;

		// Let's spin through the results and see what we've got
		while ($row = $result->fetchRow()) {
			$grantRow = $row[0];

			// If the grant string doesn't contain the configured database
			// and user, it's not relevant to our checks
			$grantMatchesDatabase = strpos($grantRow, "ON `$database`.");
			$grantMatchesAllDatabases = strpos($grantRow, '*.*');
			$grantMatchesUser = strpos($grantRow, "TO `$username`") || strpos($grantRow, "TO '$username'");

			if (!(($grantMatchesDatabase || $grantMatchesAllDatabases) && $grantMatchesUser)) {
//				$this->logger->debug("Non-matching grant: {$this->SanitizeGrant($grant)}");
				continue;
			}

			$matchingGrantsCount++;

			// We have a match; let's use a regex to read the grants
			preg_match("/GRANT\s+(.*)\s+ON/", $grantRow, $matches);

			// No grants found in this row; weird!
			if (!$matches[1]) {
				$this->logger->warning('WARNING: No MySQL grants found in matching row: ' . $grantRow);
				continue;
			}

			// We can skip the USAGE grant, it just denotes that the user
			// exists without any global privileges
			if ($matches[1] === 'USAGE') {
				$this->logger->info("Found usage grant: {$this->SanitizeGrant($grantRow)}");
				continue;
			}

			// Let's also use another regex to work out the scope of
			// the grants; wildcard, database-specific or table-specific
			// Extracts: "*.*", "`database`.*" or "`database`.`table`"
			$escapedDatabase = preg_quote($database, '/');
			preg_match("/(`$escapedDatabase`|\*)\.(`\w+`|\*)/", $grantRow, $scopeMatches);

			$scope = $scopeMatches[0];
			$scopeDatabase = $scopeMatches[1];
			$scopeTable = $scopeMatches[2];

			// Split the grants up into an array
			$grants = explode(', ', $matches[1]);

			// Determine whether the grants are adequate
			$diff = array_diff($minimum, $grants);

			// If the grants are adequate we can move on to the next row
			if (empty($diff) || $matches[1] === 'ALL PRIVILEGES') {
				if ($scopeDatabase === '*')
					$all_databases = true;

				if ($scopeTable === '*')
					$allTables = true;

				$this->logger->debug("MySQL user '$username' has the following grants for scope $scope: " . implode(', ', $grants));

				continue;
			}

			// Otherwise we have an inadequate set of grants
			$message = "PROBLEM: MySQL user '$username' lacks grants for scope $scope: " . implode(', ', $diff);

			$this->logger->error($message);

			$missingGrantsCount++;
		}

		// Bail if no grants match the database and user; no point reporting anything else
		if (!$matchingGrantsCount) {
			$this->logger->error("PROBLEM: MySQL user '$username' has no matching grants for the '$database' database");

			return false;
		}

		// Warn if we don't have grants for all tables
		if (!$allTables) {
			$this->logger->warning("WARNING: MySQL user '$username' may not have adequate grants for all tables of the '$database' database");
		}

		$adequate = ($all_databases || $allTables) && !$missingGrantsCount;

		if ($adequate) {
			$this->logger->info("MySQL user '$username' has sufficient grants for database '$database'");
		}

		return $adequate;
	}

	/**
	 * Redact passwords from a grants result.
	 *
	 * @param string $grant Grants result to hide the password from.
	 * @return string Grants result with the password redacted.
	 */
	protected function SanitizeGrant(string $grant): string
	{
		return preg_replace('/PASSWORD\s.*/', '<redacted>', $grant);
	}

	/**
	 * Check for adequate SQL Server permissions for the given database and
	 * username.
	 *
	 * @param string $database Database name
	 * @param string $username Database username
	 * @return bool Whether the database permissions are adequate
	 */
	protected function CheckSqlServerPermissions(string $database, string $username): bool
	{
		static $minimum_server = array(
			'CONNECT SQL'
		);

		static $minimum_database = array(
			'CONNECT', 'CREATE TABLE', 'CREATE SCHEMA',
			'SELECT', 'INSERT', 'UPDATE', 'DELETE', 'EXECUTE', 'ALTER',
			'ALTER ANY SCHEMA'
		);

		$server_permissions = array();
		$database_permissions = array();

		// Grab the user's server permissions
		$result = $this->database->query("SELECT * FROM fn_my_permissions(NULL, 'SERVER')");

		while ($row = $result->fetchRow())
			$server_permissions[] = $row[2];

		// Grab the user's database permissions
		$result = $this->database->query("SELECT * FROM fn_my_permissions(NULL, 'DATABASE')");

		while ($row = $result->fetchRow())
			$database_permissions[] = $row[2];

		// Log the permissions found for debugging
		$this->logger->debug("MSSQL user '$username' has SERVER permissions: " . (implode(', ', $server_permissions) ?: 'None'));
		$this->logger->debug("MSSQL user '$username' has DATABASE permissions for database '$database': " . (implode(', ', $database_permissions) ?: 'None'));

		// Diff the permissions with the minimum set we're after
		$server_diff = array_diff($minimum_server, $server_permissions);
		$database_diff = array_diff($minimum_database, $database_permissions);

		// Log an error if we lack some server permissions
		if (!empty($server_diff)) {
			$server_message = "PROBLEM: MSSQL user '$username' lacks SERVER permissions: " . implode(', ', $server_diff);

			$this->logger->error($server_message);
		}

		// Log an error if we lack some database permissions
		if (!empty($database_diff)) {
			$database_message = "PROBLEM: MSSQL user '$username' lacks DATABASE permissions for database '$database': " . implode(', ', $database_diff);

			$this->logger->error($database_message);
		}

		$adequate = empty($server_diff) && empty($database_diff);

		if ($adequate) {
			$this->logger->info("MSSQL user '$username' has sufficient SERVER and DATABASE permissions for database '$database'");
		}

		return $adequate;
	}

	/**
	 * Check for adequate filesystem permissions for SQLite databases.
	 *
	 * @return bool
	 */
	protected function CheckSqlitePermissions(): bool
	{
		if (!$this->database instanceof DAL\SQLite\SQLiteDb) {
			$this->logger->warning('WARNING: Skipping SQLite database checks; expected an instance of ' . DAL\SQLite\SQLiteDb::class . ', found ' . get_class($this->database) . ' instead');

			return false;
		}

		$filePath = $this->database->getName();

		if ($filePath === ':memory:') {
			$this->logger->info('Using in-memory SQLite database');

			return true;
		}

		$baseDirectoryPath = dirname($filePath);

		// Check whether base directory or database file is readable and writable
		$adequateBaseDirectory = is_readable($baseDirectoryPath) && is_writable($baseDirectoryPath);
		$adequateFile = is_readable($filePath) && is_writable($filePath);

		$adequate = $adequateBaseDirectory || $adequateFile;

		if ($adequate) {
			$this->logger->info('Sufficient filesystem permissions for SQLite database');
		} else {
			if (!$adequateBaseDirectory) {
				$this->logger->error("Insufficient filesystem permissions for SQLite base directory at path $baseDirectoryPath");
			}

			if (!$adequateFile) {
				$this->logger->error("Insufficient filesystem permissions for SQLite database file at path $filePath");
			}
		}

		return $adequate;
	}
}
