/**
 * 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 java.sql.Connection;
import java.sql.DatabaseMetaData;
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 migration task(s).
 *
 * @author Tim Donohue
 */
public class MigrationUtils
{
    /**
     * Drop a given Database Column 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
     * @return migration checksum as an Integer
     * @throws SQLException if a database error occurs
     */
    public static Integer dropDBConstraint(Connection connection, String tableName, String columnName)
            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
        DatabaseMetaData meta = connection.getMetaData();
        // NOTE: We use "findDbKeyword()" here as it won't cause
        // DatabaseManager.initialize() to be called (which in turn re-calls Flyway)
        String dbtype = DatabaseManager.findDbKeyword(meta);
        String constraintName = null;
        String constraintNameSQL = null;
        String schemaName = null;
        switch(dbtype)
        {
            case DatabaseManager.DBMS_POSTGRES:
                // In Postgres, column constraints are listed in the "information_schema.key_column_usage" view
                // See: http://www.postgresql.org/docs/9.4/static/infoschema-key-column-usage.html
                constraintNameSQL = "SELECT DISTINCT CONSTRAINT_NAME " +
                                    "FROM information_schema.key_column_usage " +
                                    "WHERE TABLE_NAME = ? AND COLUMN_NAME = ? AND TABLE_SCHEMA = ?";
                // For Postgres, we need to limit by the schema as well
                schemaName = DatabaseUtils.getSchemaName(connection);
                break;
            case DatabaseManager.DBMS_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 = ?";
                break;
            case DatabaseManager.DBMS_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.");
        }

        // Run the query to obtain the constraint name, passing it the parameters
        PreparedStatement statement = connection.prepareStatement(constraintNameSQL);
        statement.setString(1, DatabaseUtils.canonicalize(connection, tableName));
        statement.setString(2, DatabaseUtils.canonicalize(connection, columnName));
        // Also limit by database schema, if a schemaName has been set (only needed for PostgreSQL)
        if(schemaName!=null && !schemaName.isEmpty())
        {
            statement.setString(3, DatabaseUtils.canonicalize(connection, schemaName));
        }
        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())
        {
            // Canonicalize the constraintName
            constraintName = DatabaseUtils.canonicalize(connection, constraintName);
            // If constraintName starts with a $, surround with double quotes
            // (This is mostly for PostgreSQL, which sometimes names constraints $1, $2, etc)
            if(constraintName.startsWith("$"))
            {
                constraintName = "\"" + constraintName + "\"";
            }

            // This drop constaint SQL should be the same in all databases
            String dropConstraintSQL = "ALTER TABLE " + DatabaseUtils.canonicalize(connection, tableName) +
                                       " DROP CONSTRAINT " + constraintName;

            statement = connection.prepareStatement(dropConstraintSQL);
            try
            {
                statement.execute();
            }
            finally
            {
                statement.close();
            }
            // 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;
    }
}