/**
 * The contents of this file are subject to the license and copyright
 * detailed in the LICENSE and NOTICE files at the root of the source
 * tree and available online at
 *
 * http://www.dspace.org/license/
 */
package org.dspace.storage.rdbms;

import static org.dspace.storage.rdbms.DatabaseUtils.getSchemaName;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;

import org.flywaydb.core.api.FlywayException;

/**
 * Database utility class specific to Postgres.
 * This class contains tools and methods which are useful in determining
 * the status of a PostgreSQL database backend.  It's a companion class
 * to DatabaseUtils, but PostgreSQL specific.
 *
 * @author Tim Donohue
 */
public class PostgresUtils {
    // PostgreSQL pgcrypto extention name, and required versions of Postgres & pgcrypto
    public static final String PGCRYPTO = "pgcrypto";
    public static final Double PGCRYPTO_VERSION = 1.1;
    public static final Double POSTGRES_VERSION = 9.4;

    /**
     * Default constructor
     */
    private PostgresUtils() { }

    /**
     * Get version of pgcrypto extension available. The extension is "available"
     * if it's been installed via operating system tools/packages. It also
     * MUST be installed in the DSpace database (see getPgcryptoInstalled()).
     * <P>
     * The pgcrypto extension is required for Postgres databases
     *
     * @param connection database connection
     * @return version number or null if not available
     */
    protected static Double getPgcryptoAvailableVersion(Connection connection) {
        Double version = null;

        String checkPgCryptoAvailable = "SELECT default_version AS version FROM pg_available_extensions WHERE name=?";

        // Run the query to obtain the version of 'pgcrypto' available
        try (PreparedStatement statement = connection.prepareStatement(checkPgCryptoAvailable)) {
            statement.setString(1, PGCRYPTO);
            try (ResultSet results = statement.executeQuery()) {
                if (results.next()) {
                    version = results.getDouble("version");
                }
            }
        } catch (SQLException e) {
            throw new FlywayException("Unable to determine whether 'pgcrypto' extension is available.", e);
        }

        return version;
    }

    /**
     * Get version of pgcrypto extension installed in the DSpace database.
     * <P>
     * The pgcrypto extension is required for Postgres databases to support
     * UUIDs.
     *
     * @param connection database connection
     * @return version number or null if not installed
     */
    protected static Double getPgcryptoInstalledVersion(Connection connection) {
        Double version = null;

        String checkPgCryptoInstalled = "SELECT extversion AS version FROM pg_extension WHERE extname=?";

        // Run the query to obtain the version of 'pgcrypto' installed on this database
        try (PreparedStatement statement = connection.prepareStatement(checkPgCryptoInstalled)) {
            statement.setString(1, PGCRYPTO);
            try (ResultSet results = statement.executeQuery()) {
                if (results.next()) {
                    version = results.getDouble("version");
                }
            }
        } catch (SQLException e) {
            throw new FlywayException("Unable to determine whether 'pgcrypto' extension is installed.", e);
        }

        return version;
    }

    /**
     * Check if the pgcrypto extension is BOTH installed AND up-to-date.
     * <P>
     * This requirement is only needed for PostgreSQL databases.
     * It doesn't matter what schema pgcrypto is installed in, as long as it exists.
     *
     * @return true if everything is installed and up-to-date. False otherwise.
     */
    public static boolean isPgcryptoUpToDate() {
        // Get our configured dataSource
        DataSource dataSource = DatabaseUtils.getDataSource();

        try (Connection connection = dataSource.getConnection()) {
            Double pgcryptoInstalled = getPgcryptoInstalledVersion(connection);

            // Check if installed & up-to-date in this DSpace database
            if (pgcryptoInstalled != null && pgcryptoInstalled.compareTo(PGCRYPTO_VERSION) >= 0) {
                return true;
            }

            return false;
        } catch (SQLException e) {
            throw new FlywayException("Unable to determine whether 'pgcrypto' extension is up-to-date.", e);
        }
    }

    /**
     * Check if the pgcrypto extension is installed into a particular schema
     * <P>
     * This allows us to check if pgcrypto needs to be REMOVED prior to running
     * a 'clean' on this database. If pgcrypto is in the same schema as the
     * dspace database, a 'clean' will require removing pgcrypto FIRST.
     *
     * @param schema name of schema
     * @return true if pgcrypto is in this schema. False otherwise.
     */
    public static boolean isPgcryptoInSchema(String schema) {
        // Get our configured dataSource
        DataSource dataSource = DatabaseUtils.getDataSource();

        try (Connection connection = dataSource.getConnection()) {
            // Check if pgcrypto is installed in the current database schema.
            String pgcryptoInstalledInSchema = "SELECT extversion FROM pg_extension,pg_namespace " +
                "WHERE pg_extension.extnamespace=pg_namespace.oid " +
                "AND extname=? " +
                "AND nspname=?;";
            Double pgcryptoVersion = null;
            try (PreparedStatement statement = connection.prepareStatement(pgcryptoInstalledInSchema)) {
                statement.setString(1, PGCRYPTO);
                statement.setString(2, schema);
                try (ResultSet results = statement.executeQuery()) {
                    if (results.next()) {
                        pgcryptoVersion = results.getDouble("extversion");
                    }
                }
            }

            // If a pgcrypto version returns, it's installed in this schema
            if (pgcryptoVersion != null) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException e) {
            throw new FlywayException(
                "Unable to determine whether 'pgcrypto' extension is installed in schema '" + schema + "'.", e);
        }
    }


    /**
     * Check if the current user has permissions to run a clean on existing
     * database.
     * <P>
     * Mostly this just checks if you need to remove pgcrypto, and if so,
     * whether you have permissions to do so.
     *
     * @param connection database connection
     * @return true if permissions valid, false otherwise
     */
    protected static boolean checkCleanPermissions(Connection connection) {
        try {
            // get username of our db user
            String username = connection.getMetaData().getUserName();

            // Check their permissions. Are they a 'superuser'?
            String checkSuperuser = "SELECT rolsuper FROM pg_roles WHERE rolname=?;";
            boolean superuser = false;
            try (PreparedStatement statement = connection.prepareStatement(checkSuperuser)) {
                statement.setString(1, username);
                try (ResultSet results = statement.executeQuery()) {
                    if (results.next()) {
                        superuser = results.getBoolean("rolsuper");
                    }
                }
            } catch (SQLException e) {
                throw new FlywayException("Unable to determine if user '" + username + "' is a superuser.", e);
            }

            // If user is a superuser, then "clean" can be run successfully
            if (superuser) {
                return true;
            } else {
                // Otherwise, we'll need to see which schema 'pgcrypto' is installed in

                // Get current schema name
                String schema = getSchemaName(connection);

                // If pgcrypto is installed in this schema, then superuser privileges are needed to remove it
                if (isPgcryptoInSchema(schema)) {
                    return false;
                } else {
                    // otherwise, a 'clean' can be run by anyone
                    return true;
                }
            }
        } catch (SQLException e) {
            throw new FlywayException("Unable to determine if DB user has 'clean' privileges.", e);
        }
    }
}
