/**
 * 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.migration;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.lang.StringUtils;

/**
 * This Utility class offers utility methods which may be of use to perform
 * common Java database migration task(s) (via Flyway).
 * <P>
 * NOTE: This class specifically CANNOT utilize Hibernate, because it is
 * used for database migrations which take place PRIOR to Hibernate loading.
 * However, as you'll see below, all methods are protected to ensure the rest
 * of the API cannot bypass Hibernate.
 * 
 * @author Tim Donohue
 */
public class MigrationUtils
{
    /**
     * Drop a given Database Constraint (based on the current database type).
     * Returns a "checksum" for this migration which can be used as part of
     * a Flyway Java migration
     *
     * @param connection the current Database connection
     * @param tableName the name of the table the constraint applies to
     * @param columnName the name of the column the constraint applies to
     * @param constraintSuffix Only used for PostgreSQL, whose constraint naming convention depends on a suffix (key, fkey, etc)
     * @return migration checksum as an Integer
     * @throws SQLException if a database error occurs
     */
    protected static Integer dropDBConstraint(Connection connection, String tableName, String columnName, String constraintSuffix)
            throws SQLException
    {
        Integer checksum = -1;

        // First, in order to drop the appropriate Database constraint, we
        // must determine the unique name of the constraint. As constraint
        // naming is DB specific, this is dependent on our DB Type
        String dbtype = connection.getMetaData().getDatabaseProductName();
        String constraintName = null;
        String constraintNameSQL = null;
        boolean cascade = false;
        switch(dbtype.toLowerCase())
        {
            case "postgres":
            case "postgresql":
                // In Postgres, constraints are always named:
                // {tablename}_{columnname(s)}_{suffix}
                // see: http://stackoverflow.com/a/4108266/3750035
                constraintName = StringUtils.lowerCase(tableName);
                if(!StringUtils.equals(constraintSuffix, "pkey"))
                {
                    constraintName += "_" + StringUtils.lowerCase(columnName);
                }

                constraintName += "_" + StringUtils.lowerCase(constraintSuffix);
                cascade = true;
                break;
            case "oracle":
                // In Oracle, constraints are listed in the USER_CONS_COLUMNS table
                constraintNameSQL = "SELECT CONSTRAINT_NAME " +
                                    "FROM USER_CONS_COLUMNS " +
                                    "WHERE TABLE_NAME = ? AND COLUMN_NAME = ?";
                cascade = true;
                break;
            case "h2":
                // In H2, constraints are listed in the "information_schema.constraints" table
                constraintNameSQL = "SELECT DISTINCT CONSTRAINT_NAME " +
                                    "FROM information_schema.constraints " +
                                    "WHERE table_name = ? AND column_list = ?";
                break;
            default:
                throw new SQLException("DBMS " + dbtype + " is unsupported in this migration.");
        }

        // If we have a SQL query to run for the constraint name, then run it
        if (constraintNameSQL!=null)
        {
            // Run the query to obtain the constraint name, passing it the parameters
            PreparedStatement statement = connection.prepareStatement(constraintNameSQL);
            statement.setString(1, StringUtils.upperCase(tableName));
            statement.setString(2, StringUtils.upperCase(columnName));
            try
            {
                ResultSet results = statement.executeQuery();
                if(results.next())
                {
                    constraintName = results.getString("CONSTRAINT_NAME");
                }
                results.close();
            }
            finally
            {
                statement.close();
            }
        }

        // As long as we have a constraint name, drop it
        if (constraintName!=null && !constraintName.isEmpty())
        {
            // This drop constaint SQL should be the same in all databases
            String dropConstraintSQL = "ALTER TABLE " + tableName + " DROP CONSTRAINT " + constraintName;
            if(cascade){
                dropConstraintSQL += " CASCADE";
            }

            try(PreparedStatement statement = connection.prepareStatement(dropConstraintSQL))
            {
                statement.execute();
            }
            // Return the size of the query we just ran
            // This will be our "checksum" for this Flyway migration (see getChecksum())
            checksum = dropConstraintSQL.length();
        }

        return checksum;
    }
    
    
    /**
     * Drop a given Database Table (based on the current database type).
     * Returns a "checksum" for this migration which can be used as part of
     * a Flyway Java migration
     * <P>
     * NOTE: Ideally, if you need to do a DROP TABLE, you should just create
     * a Flyway SQL migration. This method should ONLY be used if the table name
     * needs to be dynamically determined via Java.
     *
     * @param connection the current Database connection
     * @param tableName the name of the table to drop
     * @return migration checksum as an Integer
     * @throws SQLException if a database error occurs
     */
    protected static Integer dropDBTable(Connection connection, String tableName)
            throws SQLException
    {
        String dropTableSQL = null;
        Integer checksum = -1;

        // First, in order to drop the appropriate Database table, we must 
        // determine the query based on DB type
        String dbtype = connection.getMetaData().getDatabaseProductName();
        switch(dbtype.toLowerCase())
        {
            case "postgres":
            case "postgresql":
                dropTableSQL = "DROP TABLE IF EXISTS " + tableName + " CASCADE";
                break;
            case "oracle":
                dropTableSQL = "DROP TABLE " + tableName + " CASCADE CONSTRAINTS";
                break;
            case "h2":
                dropTableSQL = "DROP TABLE IF EXISTS " + tableName + " CASCADE";
                break;
            default:
                throw new SQLException("DBMS " + dbtype + " is unsupported in this migration.");
        }
        
        // If we have a SQL query to run, then run it
        if (dropTableSQL!=null)
        {
            try(PreparedStatement statement = connection.prepareStatement(dropTableSQL))
            {
                statement.execute();
            }
            // Return the size of the query we just ran
            // This will be our "checksum" for this Flyway migration (see getChecksum())
            checksum = dropTableSQL.length();
        }
        
        return checksum;
    }
    
    /**
     * Drop a given Database Sequence (based on the current database type).
     * Returns a "checksum" for this migration which can be used as part of
     * a Flyway Java migration
     * <P>
     * NOTE: Ideally, if you need to do a DROP SEQUENCE, you should just create
     * a Flyway SQL migration. This method should ONLY be used if the sequence name
     * needs to be dynamically determined via Java.
     *
     * @param connection the current Database connection
     * @param sequenceName the name of the sequence to drop
     * @return migration checksum as an Integer
     * @throws SQLException if a database error occurs
     */
    protected static Integer dropDBSequence(Connection connection, String sequenceName)
            throws SQLException
    {
        String dropSequenceSQL = null;
        Integer checksum = -1;

        String dbtype = connection.getMetaData().getDatabaseProductName();
        switch(dbtype.toLowerCase())
        {
            case "postgres":
            case "postgresql":
                dropSequenceSQL = "DROP SEQUENCE IF EXISTS " + sequenceName;
                break;
            case "oracle":
                dropSequenceSQL = "DROP SEQUENCE " + sequenceName ;
                break;
            case "h2":
                dropSequenceSQL = "DROP SEQUENCE IF EXISTS " + sequenceName;
                break;
            default:
                throw new SQLException("DBMS " + dbtype + " is unsupported in this migration.");
        }

        // If we have a SQL query to run, then run it
        if (dropSequenceSQL!=null)
        {
            try(PreparedStatement statement = connection.prepareStatement(dropSequenceSQL))
            {
                statement.execute();
            }
            // Return the size of the query we just ran
            // This will be our "checksum" for this Flyway migration (see getChecksum())
            checksum = dropSequenceSQL.length();
        }
        
        return checksum;
    }
    
    /**
     * Drop a given Database View (based on the current database type).
     * Returns a "checksum" for this migration which can be used as part of
     * a Flyway Java migration
     * <P>
     * NOTE: Ideally, if you need to do a DROP VIEW, you should just create
     * a Flyway SQL migration. This method should ONLY be used if the view name
     * needs to be dynamically determined via Java.
     *
     * @param connection the current Database connection
     * @param viewName the name of the view to drop
     * @return migration checksum as an Integer
     * @throws SQLException if a database error occurs
     */
    protected static Integer dropDBView(Connection connection, String viewName)
            throws SQLException
    {
        String dropViewSQL = null;
        Integer checksum = -1;

        String dbtype = connection.getMetaData().getDatabaseProductName();
        switch(dbtype.toLowerCase())
        {
            case "postgres":
            case "postgresql":
                dropViewSQL = "DROP VIEW IF EXISTS " + viewName + " CASCADE";
                break;
            case "oracle":
                dropViewSQL = "DROP VIEW " + viewName + " CASCADE CONSTRAINTS";
                break;
            case "h2":
                dropViewSQL = "DROP VIEW IF EXISTS " + viewName + " CASCADE";
                break;
            default:
                throw new SQLException("DBMS " + dbtype + " is unsupported in this migration.");
        }

        // If we have a SQL query to run, then run it
        if (dropViewSQL!=null)
        {
            try(PreparedStatement statement = connection.prepareStatement(dropViewSQL))
            {
                statement.execute();
            }
            // Return the size of the query we just ran
            // This will be our "checksum" for this Flyway migration (see getChecksum())
            checksum = dropViewSQL.length();
        }
        
        return checksum;
    }
}