/*
* Copyright (c) 2016 Tecom LLC
* All rights reserved
*
* Исключительное право (c) 2016 принадлежит ООО Теком
* Все права защищены
*/
package com.tecomgroup.energetics.server.database.migration;

import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.MalformedURLException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
import java.util.Properties;

import org.apache.commons.io.IOUtils;
import org.flywaydb.core.Flyway;
import org.flywaydb.core.api.FlywayException;
import org.flywaydb.core.api.MigrationInfo;
import org.flywaydb.core.api.MigrationInfoService;
import org.hibernate.tool.hbm2ddl.ImportSqlCommandExtractor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.tecomgroup.energetics.server.database.domain.TEM_Agent;
import com.tecomgroup.energetics.server.database.utils.AbstractPlaceholderResolver;
import com.tecomgroup.energetics.server.database.utils.AgentProperties;
import com.tecomgroup.energetics.server.database.utils.Utils;
import com.tecomgroup.energetics.server.system.AgentClassLoader;
import com.tecomgroup.energetics.server.system.Constants;

/**
 * Allows to manage the database, i.e. create schema,
 * migrate to the latest version, install agent, get
 * the actual version, validate and so on.
 *
 * @author ariskin.a
 */
public class Database {

    private static class AgentDirectoryPlaceholderResolver extends AbstractPlaceholderResolver {

        private static String homeDir;

        static {
            homeDir = System.getenv(Constants.NMS_HOME);
        }

        @Override
        protected String resolvePlaceholder(String placeholderName) {
            if (Constants.NMS_HOME.equals(placeholderName)) {
                return homeDir;
            }
            else {
                return null;
            }
        }

    }

    private static class AgentPlaceholderResolver extends AbstractPlaceholderResolver {

        private final static String AGENT_ID_PLACEHOLDER = "agent_id";
        private final static String AGENT_NAME_PLACEHOLDER = "agent_name";

        private TEM_Agent agent;

        public AgentPlaceholderResolver(TEM_Agent agent) {
            this.agent = agent;
        }

        @Override
        protected String resolvePlaceholder(String placeholderName) {
            if (AGENT_ID_PLACEHOLDER.equals(placeholderName)) {
                return agent.getId().toString();
            }
            else if (AGENT_NAME_PLACEHOLDER.equals(placeholderName)) {
                return agent.getName().toString();
            }
            else {
                return null;
            }
        }

    }

    private static final Logger LOGGER = LoggerFactory.getLogger(Database.class);

    private static final String[] CREATE_SCRIPTS = new String[] {
        "schema_create_pre.sql",
        "schema_create.sql",
        "schema_create_post.sql"
    };

    private static final String[] DROP_SCRIPTS = new String[] {
        "schema_drop_pre.sql",
        "schema_drop.sql",
        "schema_drop_post.sql"
    };

    private static final String IMPORT_SCRIPT = "import.sql";

    private static final String RESOURCE_FOLDER_SQL_CORE = "sql";

    private static final String CORE_SQL_MIGRATIONS_PREFIX = "migration";
    private static final String CORE_JAVA_MIGRATIONS_PACKAGE = "com.tecomgroup.energetics.server.database.migration";
    private static final String COMMON_SQL_MIGRATIONS_FOLDER = "common";
    private static final String PLACEHOLDER_PREFIX = "$${";
    //private static final String SCHEMA_VERSION_CORE_TABLE = "schema_version";
    private static final String SCHEMA_VERSION_AGENT_TABLE_TEMPLATE = "schema_version_%s";

    private final DatabaseConfiguration databaseConfig;

    public Database(DatabaseConfiguration databaseConfig) {
        this.databaseConfig = databaseConfig;
    }

    public static void createSchema(DatabaseConfiguration databaseConfig, String[] agentJarPaths) throws Exception {
        Database database = new Database(databaseConfig);
        database.createSchema(agentJarPaths);
    }

    public void createSchema(String[] agentJarPaths) throws Exception {
        Connection connection = getConnection();
        try {
            AgentClassLoader classLoader = getAgentClassLoaderByJars(agentJarPaths);
            try {
                createSchema(classLoader, connection);
            }
            finally {
                classLoader.close();
            }
        }
        finally {
            connection.close();
        }
    }

    private void createSchema(AgentClassLoader classLoader, Connection connection) throws Exception {
        LOGGER.info("Creating database schema...");

        String dialectFolder = Utils.getFolderByDialect(databaseConfig.getDialect());
        LOGGER.debug("Dialect folder: {}", dialectFolder);

        Class<? extends ImportSqlCommandExtractor> commandExtractorClass = getCommandExtractorClassOrDefault();
        LOGGER.debug("Command extractor class: {}", commandExtractorClass.getCanonicalName());
        ImportSqlCommandExtractor commandExtractor = commandExtractorClass.newInstance();

        String encoding = getEncodingOrDefault();
        LOGGER.debug("Encoding: {}", encoding);

        createSchemaCore(classLoader, connection, dialectFolder, commandExtractor, encoding);

        Flyway flywayCore = createFlywayCore(classLoader);
        internalBaselineToLatestVersion(flywayCore);

        for (String agentName : classLoader.getAgentNames()) {
            installAgent(classLoader, connection, agentName, dialectFolder, commandExtractor, encoding);
        }

        LOGGER.info("Database schema created");
    }

    private void createSchemaCore(AgentClassLoader classLoader, Connection connection, String dialectFolder, ImportSqlCommandExtractor commandExtractor, String encoding) throws Exception {
        LOGGER.info("Creating schema for core...");
        String resourceFolder = RESOURCE_FOLDER_SQL_CORE;
        internalCreateSchema(classLoader, connection, resourceFolder, dialectFolder, commandExtractor, encoding);
    }

    private void createSchemaAgent(AgentClassLoader classLoader, Connection connection, TEM_Agent agent, String dialectFolder, ImportSqlCommandExtractor commandExtractor, String encoding) throws Exception {
        LOGGER.info("Creating schema for agent {}...", agent.getName());
        String resourceFolder = getAgentSqlResourceFolder(classLoader, agent.getName());
        internalCreateSchema(classLoader, connection, resourceFolder, dialectFolder, commandExtractor, encoding, new AgentPlaceholderResolver(agent));
    }

    private void internalCreateSchema(AgentClassLoader classLoader, Connection connection, String resourceFolder, String dialectFolder,
            ImportSqlCommandExtractor commandExtractor, String encoding) throws Exception {
        internalCreateSchema(classLoader, connection, resourceFolder, dialectFolder, commandExtractor, encoding, null);
    }

    private void internalCreateSchema(AgentClassLoader classLoader, Connection connection, String resourceFolder, String dialectFolder,
            ImportSqlCommandExtractor commandExtractor, String encoding, AbstractPlaceholderResolver placeholderResolver) throws Exception {
        LOGGER.debug("Creating schema, resource folder: {}, dialect folder: {}", resourceFolder, dialectFolder);

        LOGGER.debug("Loading create commands...");
        String[] createCommands = loadCommands(classLoader, commandExtractor, resourceFolder, dialectFolder, CREATE_SCRIPTS, encoding);
        if (placeholderResolver != null) {
            LOGGER.debug("Using placeholder resolver {}...", placeholderResolver.getClass().getCanonicalName());
            for (int i = 0; i < createCommands.length; ++i) {
                String createCommand = createCommands[i];
                String replacedCreateCommand = placeholderResolver.replacePlaceholders(createCommand);
                if (!replacedCreateCommand.equals(createCommand)) {
                    LOGGER.trace("Create command: '{}' -> '{}'", createCommand, replacedCreateCommand);
                }
                createCommands[i] = replacedCreateCommand;
            }
        }
        if (LOGGER.isTraceEnabled()) {
            LOGGER.trace("Create commands:");
            for (String createCommand : createCommands) {
                LOGGER.trace("{}", createCommand);
            }
        }

        LOGGER.info("Executing create commands...");
        List<Exception> createExceptions = executeCommands(connection, createCommands);
        if (createExceptions.size() > 0) {
            LOGGER.error("Exceptions during execution of create scripts:");
            for (Exception ex : createExceptions) {
                LOGGER.error("Exception", ex);
            }
            throw new Exception("Failed to execute schema create scripts");
        }
        LOGGER.info("Create commands executed");

        LOGGER.debug("Loading import script...");
        String importScript = loadScriptAsString(classLoader, resourceFolder, dialectFolder, IMPORT_SCRIPT, encoding);
        if (importScript != null) {
            if (placeholderResolver != null) {
                LOGGER.debug("Using placeholder resolver {}...", placeholderResolver.getClass().getCanonicalName());
                String replacedImportScript = placeholderResolver.replacePlaceholders(importScript);
                if (!replacedImportScript.equals(importScript)) {
                    LOGGER.trace("Import script: '{}' -> '{}", importScript, replacedImportScript);
                }
                importScript = replacedImportScript;
            }
            LOGGER.trace("Import script: {}", importScript);

            LOGGER.info("Executing import script...");
            try {
                if (!importScript.trim().isEmpty()) {
                    executeScript(connection, importScript);
                }
                else {
                    LOGGER.debug("Import script is empty");
                }
            }
            catch (SQLException ex) {
                throw new Exception("Failed to execute import script", ex);
            }
            LOGGER.info("Import script executed");
        }
    }

    public static void dropSchema(DatabaseConfiguration databaseConfig) throws Exception {
        Database database = new Database(databaseConfig);
        database.dropSchema();
    }

    public void dropSchema() throws Exception {
        Connection connection = getConnection();
        try {
            AgentClassLoader classLoader = getAgentClassLoaderByInstalledAgents(connection);
            try {
                dropSchema(classLoader, connection);
            }
            finally {
                classLoader.close();
            }
        }
        finally {
            connection.close();
        }
    }

    private void dropSchema(AgentClassLoader classLoader, Connection connection) throws Exception {
        LOGGER.info("Dropping database schema...");

        String dialectFolder = Utils.getFolderByDialect(databaseConfig.getDialect());
        LOGGER.debug("Dialect folder: {}", dialectFolder);

        Class<? extends ImportSqlCommandExtractor> commandExtractorClass = getCommandExtractorClassOrDefault();
        LOGGER.debug("Command extractor class: {}", commandExtractorClass.getCanonicalName());
        ImportSqlCommandExtractor commandExtractor = commandExtractorClass.newInstance();

        String encoding = getEncodingOrDefault();
        LOGGER.debug("Encoding: {}", encoding);

        List<TEM_Agent> installedAgents = getInstalledAgents(connection);
        for (TEM_Agent installedAgent : installedAgents) {
            uninstallAgent(classLoader, connection, installedAgent, dialectFolder, commandExtractor, encoding);
        }

        dropSchemaCore(classLoader, connection, dialectFolder, commandExtractor, encoding);

        LOGGER.info("Database schema dropped");
    }

    private void dropSchemaCore(AgentClassLoader classLoader, Connection connection, String dialectFolder, ImportSqlCommandExtractor commandExtractor, String encoding) throws Exception {
        LOGGER.info("Dropping schema for core...");
        String resourceFolder = RESOURCE_FOLDER_SQL_CORE;
        internalDropSchema(classLoader, connection, resourceFolder, dialectFolder, commandExtractor, encoding);
    }

    private void dropSchemaAgent(AgentClassLoader classLoader, Connection connection, final TEM_Agent agent, String dialectFolder, ImportSqlCommandExtractor commandExtractor, String encoding) throws Exception {
        LOGGER.info("Dropping schema for agent {}...", agent.getName());
        String resourceFolder = getAgentSqlResourceFolder(classLoader, agent.getName());
        internalDropSchema(classLoader, connection, resourceFolder, dialectFolder, commandExtractor, encoding, new AgentPlaceholderResolver(agent));
    }

    private void internalDropSchema(AgentClassLoader classLoader, Connection connection, String resourceFolder, String dialectFolder,
            ImportSqlCommandExtractor commandExtractor, String encoding) throws Exception {
        internalDropSchema(classLoader, connection, resourceFolder, dialectFolder, commandExtractor, encoding, null);
    }

    private void internalDropSchema(AgentClassLoader classLoader, Connection connection, String resourceFolder, String dialectFolder,
            ImportSqlCommandExtractor commandExtractor, String encoding, AbstractPlaceholderResolver placeholderResolver) throws Exception {
        LOGGER.debug("Dropping schema, resource folder: {}, dialect folder: {}", resourceFolder, dialectFolder);

        LOGGER.debug("Loading drop commands...");
        String[] dropCommands = loadCommands(classLoader, commandExtractor, resourceFolder, dialectFolder, DROP_SCRIPTS, encoding);
        if (placeholderResolver != null) {
            LOGGER.debug("Using placeholder resolver {}...", placeholderResolver.getClass().getCanonicalName());
            for (int i = 0; i < dropCommands.length; ++i) {
                String dropCommand = dropCommands[i];
                String replacedDropCommand = placeholderResolver.replacePlaceholders(dropCommand);
                if (!replacedDropCommand.equals(dropCommand)) {
                    LOGGER.trace("Create command: '{}' -> '{}'", dropCommand, replacedDropCommand);
                }
                dropCommands[i] = replacedDropCommand;
            }
        }
        if (LOGGER.isTraceEnabled()) {
            LOGGER.trace("Drop commands:");
            for (String dropCommand : dropCommands) {
                LOGGER.trace("{}", dropCommand);
            }
        }

        LOGGER.info("Executing drop commands...");
        List<Exception> dropExceptions = executeCommands(connection, dropCommands);
        LOGGER.info("Drop commands executed");
        if (LOGGER.isDebugEnabled()) {
            for (Exception dropException : dropExceptions) {
                LOGGER.debug("Drop exception", dropException);
            }
        }

        /*
        try {
            dropCoreSchemaVersionTable(connection);
        }
        catch (SQLException ex) {
            LOGGER.debug("Failed to drop core schema version table", ex);
        }
        */
    }

    public static void upgradeSchema(DatabaseConfiguration databaseConfig, String[] agentJarPaths) throws Exception {
        Database database = new Database(databaseConfig);
        database.upgradeSchema(agentJarPaths);
    }

    public void upgradeSchema(String[] agentJarPaths) throws Exception {
        Connection connection = getConnection();
        try {
            AgentClassLoader classLoader = getAgentClassLoaderByJars(agentJarPaths);
            try {
                upgradeSchema(classLoader, connection);
            }
            finally {
                classLoader.close();
            }
        }
        finally {
            connection.close();
        }
    }

    private void upgradeSchema(AgentClassLoader classLoader, Connection connection) throws Exception {
        LOGGER.info("Upgrading database schema...");

        String dialectFolder = Utils.getFolderByDialect(databaseConfig.getDialect());
        LOGGER.debug("Dialect folder: {}", dialectFolder);

        Class<? extends ImportSqlCommandExtractor> commandExtractorClass = getCommandExtractorClassOrDefault();
        LOGGER.debug("Command extractor class: {}", commandExtractorClass.getCanonicalName());
        ImportSqlCommandExtractor commandExtractor = commandExtractorClass.newInstance();

        String encoding = getEncodingOrDefault();
        LOGGER.debug("Encoding: {}", encoding);

        Flyway flywayCore = createFlywayCore(classLoader);
        migrateSchema(flywayCore);

        for (String agentName : classLoader.getAgentNames()) {
            installOrUpgradeAgent(classLoader, connection, agentName, dialectFolder, commandExtractor, encoding);
        }

        LOGGER.info("Database schema upgraded");
    }

    public static void installAgent(DatabaseConfiguration databaseConfig, String agentJarPath) throws Exception {
        Database database = new Database(databaseConfig);
        database.installAgent(agentJarPath);
    }

    public void installAgent(String agentJarPath) throws Exception {
        Connection connection = getConnection();
        try {
            AgentClassLoader classLoader = getAgentClassLoaderByJar(agentJarPath);
            try {
                String agentName;
                try {
                    agentName = classLoader.getAgentNames().iterator().next();
                }
                catch (NoSuchElementException ex) {
                    String errorMsg = String.format("Failed to get agent name from JAR {}. Probably, you specified not an agent JAR.", agentJarPath);
                    LOGGER.error("{}", errorMsg);
                    throw new Exception(errorMsg);
                }

                installAgent(classLoader, connection, agentName);
            }
            finally {
                classLoader.close();
            }
        }
        finally {
            connection.close();
        }
    }

    private void installAgent(AgentClassLoader classLoader, Connection connection, String agentName) throws Exception {
        LOGGER.info("Installing agent {}...", agentName);

        String dialectFolder = Utils.getFolderByDialect(databaseConfig.getDialect());
        LOGGER.debug("Dialect folder: {}", dialectFolder);

        Class<? extends ImportSqlCommandExtractor> commandExtractorClass = getCommandExtractorClassOrDefault();
        LOGGER.debug("Command extractor class: {}", commandExtractorClass.getCanonicalName());
        ImportSqlCommandExtractor commandExtractor = commandExtractorClass.newInstance();

        String encoding = getEncodingOrDefault();
        LOGGER.debug("Encoding: {}", encoding);

        installAgent(classLoader, connection, agentName, dialectFolder, commandExtractor, encoding);

        LOGGER.info("Agent {} installed", agentName);
    }

    private void installAgent(AgentClassLoader classLoader, Connection connection, String agentName, String dialectFolder, ImportSqlCommandExtractor commandExtractor, String encoding) throws Exception {
        LOGGER.debug("Installing agent {}...", agentName);

        if (!isAgentsTableExists(connection)) {
            throw new Exception("Agents table not exists");
        }

        TEM_Agent agent = getInstalledAgentByName(connection, agentName);
        if (agent != null) {
            throw new Exception(String.format("Agent %s is already installed", agentName));
        }

        agent = insertAgentIntoAgentsTable(classLoader, connection, agentName, dialectFolder);

        createSchemaAgent(classLoader, connection, agent, dialectFolder, commandExtractor, encoding);

        createAgentSchemaVersionTable(classLoader, connection, agent.getName());
    }

    private TEM_Agent insertAgentIntoAgentsTable(AgentClassLoader classLoader, Connection connection, String agentName, String dialectFolder) throws Exception {
        LOGGER.debug("Inserting agent {} into agents table...", agentName);

        Properties agentProperties = getAgentProperties(classLoader, agentName);
        String agentVersion = agentProperties.getProperty(AgentProperties.AGENT_VERSION_PROPERTY);
        LOGGER.debug("Agent version: {}", agentVersion);
        String agentDescription = agentProperties.getProperty(AgentProperties.AGENT_DESCRIPTION_PROPERTY);
        LOGGER.debug("Agent version: {}", agentVersion);
        Class<?> deviceClass = classLoader.getDeviceClassByAgentName(agentName);
        LOGGER.debug("Device class: {}", deviceClass.getCanonicalName());
        String agentDirectory = String.format("${NMS_HOME}/agents/%s", agentName); // assume this directory name is used for an agent
        LOGGER.debug("Agent directory: {}", agentDirectory);
        String agentDisplayName = agentProperties.getProperty(AgentProperties.AGENT_DISPLAY_NAME_PROPERTY);
        LOGGER.debug("Agent display name: {}", agentDisplayName);
        Class<?> agentClass = classLoader.getAgentClassByAgentName(agentName);
        LOGGER.debug("Agent class: {}", agentClass.getCanonicalName());

        String insertQuery = getInsertAgentQuery(dialectFolder);
        LOGGER.debug("Insert query: {}", insertQuery);
        PreparedStatement insertAgentStatement = connection.prepareStatement(insertQuery, Statement.RETURN_GENERATED_KEYS);
        insertAgentStatement.setString(1, agentVersion);
        insertAgentStatement.setString(2, agentDescription);
        insertAgentStatement.setString(3, deviceClass.getCanonicalName());
        insertAgentStatement.setString(4, agentDirectory);
        insertAgentStatement.setString(5, agentDisplayName);
        insertAgentStatement.setDate(6, new java.sql.Date(new Date().getTime()));
        insertAgentStatement.setString(7, agentName);
        insertAgentStatement.setString(8, agentClass.getCanonicalName());

        int insertedRows = insertAgentStatement.executeUpdate();
        LOGGER.debug("Inserted {} rows", insertedRows);
        if (insertedRows <= 0) {
            throw new Exception("Failed to insert data into agents table");
        }

        ResultSet generatedKeys = insertAgentStatement.getGeneratedKeys();
        if (generatedKeys.next()) {
            Long insertedId = generatedKeys.getLong(1);
            LOGGER.debug("Inserted ID: {}", insertedId);

            TEM_Agent insertedAgent = getInstalledAgentById(connection, insertedId);
            return insertedAgent;
        }
        else {
            // For unknown reason, we could't obtain ID of the inserted record.
            // So, we try to get agent by its name. This won't work if we implement
            // feature with different agent versions.
            // For now, just warn.
            LOGGER.warn("Failed to get inserted ID.");

            TEM_Agent insertedAgent = getInstalledAgentByName(connection, agentName);
            return insertedAgent;
        }
    }

    private void createAgentSchemaVersionTable(AgentClassLoader classLoader, Connection connection, String agentName) throws Exception {
        LOGGER.info("Creating schema version table for agent {}...", agentName);
        Flyway flywayAgent = createFlywayAgent(classLoader, agentName);
        internalBaselineToLatestVersion(flywayAgent);
    }

    public static void uninstallAgent(DatabaseConfiguration databaseConfig, Long agentId) throws Exception {
        Database database = new Database(databaseConfig);
        database.uninstallAgent(agentId);
    }

    public void uninstallAgent(Long agentId) throws Exception {
        Connection connection = getConnection();
        try {
            if (!isAgentsTableExists(connection)) {
                throw new Exception("Agents table not exists");
            }

            TEM_Agent agent = getInstalledAgentById(connection, agentId);
            if (agent == null) {
                String errorMsg = String.format("Failed to find agent with ID %d", agentId);
                LOGGER.error(errorMsg);
                throw new Exception(errorMsg);
            }

            AgentClassLoader classLoader = getAgentClassLoaderByDirectory(agent.getDirectory());
            try {
                uninstallAgent(classLoader, connection, agent);
            }
            finally {
                classLoader.close();
            }
        }
        finally {
            connection.close();
        }
    }

    public static void uninstallAgent(DatabaseConfiguration databaseConfig, String agentName) throws Exception {
        Database database = new Database(databaseConfig);
        database.uninstallAgent(agentName);
    }

    public void uninstallAgent(String agentName) throws Exception {
        Connection connection = getConnection();
        try {
            if (!isAgentsTableExists(connection)) {
                throw new Exception("Agents table not exists");
            }

            TEM_Agent agent = getInstalledAgentByName(connection, agentName);
            if (agent == null) {
                String errorMsg = String.format("Failed to find agent with name %s", agentName);
                LOGGER.error(errorMsg);
                throw new Exception(errorMsg);
            }

            AgentClassLoader classLoader = getAgentClassLoaderByDirectory(agent.getDirectory());
            try {
                uninstallAgent(classLoader, connection, agent);
            }
            finally {
                classLoader.close();
            }
        }
        finally {
            connection.close();
        }
    }

    private void uninstallAgent(AgentClassLoader classLoader, Connection connection, TEM_Agent agent) throws Exception {
        LOGGER.info("Uninstalling agent {} (ID {})...", agent.getName(), agent.getId());

        String dialectFolder = Utils.getFolderByDialect(databaseConfig.getDialect());
        LOGGER.debug("Dialect folder: {}", dialectFolder);

        Class<? extends ImportSqlCommandExtractor> commandExtractorClass = getCommandExtractorClassOrDefault();
        LOGGER.debug("Command extractor class: {}", commandExtractorClass.getCanonicalName());
        ImportSqlCommandExtractor commandExtractor = commandExtractorClass.newInstance();

        String encoding = getEncodingOrDefault();
        LOGGER.debug("Encoding: {}", encoding);

        uninstallAgent(classLoader, connection, agent, dialectFolder, commandExtractor, encoding);

        LOGGER.debug("Agent {} (ID {}) uninstalled", agent.getName(), agent.getId());
    }

    private void uninstallAgent(AgentClassLoader classLoader, Connection connection, TEM_Agent agent, String dialectFolder, ImportSqlCommandExtractor commandExtractor, String encoding) throws Exception {
        LOGGER.debug("Uninstalling agent {} (ID {})...", agent.getName(), agent.getId());

        try {
            dropAgentSchemaVersionTable(connection, agent.getName());
        }
        catch (SQLException ex) {
            LOGGER.error("Failed to drop schema version table for agent {}", agent.getName(), ex);
        }

        dropSchemaAgent(classLoader, connection, agent, dialectFolder, commandExtractor, encoding);

        if (isAgentsTableExists(connection)) {
            try {
                deleteAgentFromAgentsTable(connection, agent.getId());
            }
            catch (SQLException ex) {
                LOGGER.error("Failed to delete agent from agents table", ex);
            }
        }
    }

    /*
    private void dropCoreSchemaVersionTable(Connection connection) throws SQLException {
        LOGGER.debug("Dropping schema version table for core...");
        Statement dropCoreSchemaVersionTableStatement = connection.createStatement();
        String queryDropCoreSchemaVersionTable = String.format("DROP TABLE \"%s\"", SCHEMA_VERSION_CORE_TABLE);
        LOGGER.debug("Drop table SQL statement: {}", queryDropCoreSchemaVersionTable);
        dropCoreSchemaVersionTableStatement.execute(queryDropCoreSchemaVersionTable);
    }
    */

    private void dropAgentSchemaVersionTable(Connection connection, String agentName) throws SQLException {
        LOGGER.debug("Dropping schema version table for agent {}...", agentName);
        String agentSchemaVersionTableName = getAgentSchemaVersionTableName(agentName);
        LOGGER.debug("Schema version table name: {}", agentSchemaVersionTableName);
        Statement dropAgentSchemaVersionTableStatement = connection.createStatement();
        String queryDropAgentSchemaVersionTable = String.format("DROP TABLE \"%s\"", agentSchemaVersionTableName);
        LOGGER.debug("Drop table SQL statement: {}", queryDropAgentSchemaVersionTable);
        dropAgentSchemaVersionTableStatement.execute(queryDropAgentSchemaVersionTable);
    }

    private static final String queryDeleteAgent = "DELETE FROM tem_agent WHERE id=?";

    private void deleteAgentFromAgentsTable(Connection connection, Long agentId) throws SQLException {
        LOGGER.debug("Delete agent with ID {} from agents table...", agentId);
        PreparedStatement deleteAgentStatement = connection.prepareStatement(queryDeleteAgent);
        deleteAgentStatement.setLong(1, agentId);
        deleteAgentStatement.execute();
    }

    public static void upgradeAgent(DatabaseConfiguration databaseConfig, String agentJarPath) throws Exception {
        Database database = new Database(databaseConfig);
        database.upgradeAgent(agentJarPath);
    }

    public void upgradeAgent(String agentJarPath) throws Exception {
        Connection connection = getConnection();
        try {
            AgentClassLoader classLoader = getAgentClassLoaderByJar(agentJarPath);
            try {
                String agentName;
                try {
                    agentName = classLoader.getAgentNames().iterator().next();
                }
                catch (NoSuchElementException ex) {
                    String errorMsg = String.format("Failed to get agent name from JAR {}. Probably, you specified not an agent JAR.", agentJarPath);
                    LOGGER.error("{}", errorMsg);
                    throw new Exception(errorMsg);
                }

                upgradeAgent(classLoader, connection, agentName);
            }
            finally {
                classLoader.close();
            }
        }
        finally {
            connection.close();
        }
    }

    private void upgradeAgent(AgentClassLoader classLoader, Connection connection, String agentName) throws Exception {
        LOGGER.debug("Upgrading agent {}...", agentName);

        if (!isAgentsTableExists(connection)) {
            throw new Exception("Agents table not exists");
        }

        TEM_Agent agent = getInstalledAgentByName(connection, agentName);
        if (agent == null) {
            throw new Exception(String.format("Agent %s is not installed", agentName));
        }

        internalUpgradeAgent(classLoader, connection, agentName);
    }

    private void internalUpgradeAgent(AgentClassLoader classLoader, Connection connection, String agentName) throws Exception {
        updateAgentInAgentsTable(classLoader, connection, agentName);

        upgradeAgentSchema(classLoader, agentName);

        // assume that no changed required in schema version table for agent
    }

    private void upgradeAgentSchema(AgentClassLoader classLoader, String agentName) throws Exception {
        LOGGER.debug("Upgrading schema for agent {}...", agentName);

        Flyway flywayAgent = createFlywayAgent(classLoader, agentName);
        migrateSchema(flywayAgent);
    }

    private static final String queryUpdateAgentByName = "UPDATE tem_agent " +
            "SET agentversion=?, description=?, deviceclass=?, displayname=?, agentclass=? WHERE name=?";

    private void updateAgentInAgentsTable(AgentClassLoader classLoader, Connection connection, String agentName) throws Exception {
        LOGGER.debug("Updating agent {} in agents table...", agentName);

        Properties agentProperties = getAgentProperties(classLoader, agentName);
        String agentVersion = agentProperties.getProperty(AgentProperties.AGENT_VERSION_PROPERTY);
        LOGGER.debug("Agent version: {}", agentVersion);
        String agentDescription = agentProperties.getProperty(AgentProperties.AGENT_DESCRIPTION_PROPERTY);
        LOGGER.debug("Agent version: {}", agentVersion);
        Class<?> deviceClass = classLoader.getDeviceClassByAgentName(agentName);
        LOGGER.debug("Device class: {}", deviceClass.getCanonicalName());
        String agentDisplayName = agentProperties.getProperty(AgentProperties.AGENT_DISPLAY_NAME_PROPERTY);
        LOGGER.debug("Agent display name: {}", agentDisplayName);
        Class<?> agentClass = classLoader.getAgentClassByAgentName(agentName);
        LOGGER.debug("Agent class: {}", agentClass.getCanonicalName());

        PreparedStatement updateAgentStatement = connection.prepareStatement(queryUpdateAgentByName);
        updateAgentStatement.setString(1, agentVersion);
        updateAgentStatement.setString(2, agentDescription);
        updateAgentStatement.setString(3, deviceClass.getCanonicalName());
        updateAgentStatement.setString(4, agentDisplayName);
        updateAgentStatement.setString(5, agentClass.getCanonicalName());
        updateAgentStatement.setString(6, agentName);

        int updatedRows = updateAgentStatement.executeUpdate();
        LOGGER.debug("Updated {} rows", updatedRows);
        if (updatedRows <= 0) {
            throw new Exception("Failed to update data in agents table");
        }
        else if (updatedRows > 1) {
            LOGGER.warn("Updated more than one ({}) rows in agents table", updatedRows);
        }
    }

    public static void installOrUpgradeAgent(DatabaseConfiguration databaseConfig, String agentJarPath) throws Exception {
        Database database = new Database(databaseConfig);
        database.installOrUpgradeAgent(agentJarPath);
    }

    public void installOrUpgradeAgent(String agentJarPath) throws Exception {
        Connection connection = getConnection();
        try {
            AgentClassLoader classLoader = getAgentClassLoaderByJar(agentJarPath);
            try {
                String agentName;
                try {
                    agentName = classLoader.getAgentNames().iterator().next();
                }
                catch (NoSuchElementException ex) {
                    String errorMsg = String.format("Failed to get agent name from JAR {}. Probably, you specified not an agent JAR.", agentJarPath);
                    LOGGER.error("{}", errorMsg);
                    throw new Exception(errorMsg);
                }

                installOrUpgradeAgent(classLoader, connection, agentName);
            }
            finally {
                classLoader.close();
            }
        }
        finally {
            connection.close();
        }
    }

    private void installOrUpgradeAgent(AgentClassLoader classLoader, Connection connection, String agentName) throws Exception {
        LOGGER.debug("Installing/upgrading agent {}...", agentName);

        String dialectFolder = Utils.getFolderByDialect(databaseConfig.getDialect());
        LOGGER.debug("Dialect folder: {}", dialectFolder);

        Class<? extends ImportSqlCommandExtractor> commandExtractorClass = getCommandExtractorClassOrDefault();
        LOGGER.debug("Command extractor class: {}", commandExtractorClass.getCanonicalName());
        ImportSqlCommandExtractor commandExtractor = commandExtractorClass.newInstance();

        String encoding = getEncodingOrDefault();
        LOGGER.debug("Encoding: {}", encoding);

        installOrUpgradeAgent(classLoader, connection, agentName, dialectFolder, commandExtractor, encoding);

        LOGGER.debug("Agent {} installed/upgraded", agentName);
    }

    private void installOrUpgradeAgent(AgentClassLoader classLoader, Connection connection, String agentName, String dialectFolder,
            ImportSqlCommandExtractor commandExtractor, String encoding) throws Exception {
        if (!isAgentsTableExists(connection)) {
            throw new Exception("Agents table not exists");
        }

        TEM_Agent installedAgent = getInstalledAgentByName(connection, agentName);
        if (installedAgent != null) {
            LOGGER.debug("Agent {} is already installed, upgrading it...");
            internalUpgradeAgent(classLoader, connection, agentName);
        }
        else {
            LOGGER.debug("Agent {} is not installed, installing it...");
            installAgent(classLoader, connection, agentName, dialectFolder, commandExtractor, encoding);
        }
    }

    public static DatabaseVersion getVersion(DatabaseConfiguration databaseConfig) throws Exception {
        Database database = new Database(databaseConfig);
        DatabaseVersion version = database.getVersion();
        return version;
    }

    /**
     * Get the database version.
     * Retrieves the actual and the latest versions for core and agents.
     *
     * @return version
     * @throws MigrationException
     */
    public DatabaseVersion getVersion() throws Exception {
        Connection connection = getConnection();
        try {
            AgentClassLoader classLoader = getAgentClassLoaderByInstalledAgents(connection);
            try {
                DatabaseVersion version = getVersion(classLoader);
                return version;
            }
            finally {
                classLoader.close();
            }
        }
        finally {
            connection.close();
        }
    }

    public DatabaseVersion getVersion(AgentClassLoader classLoader) throws Exception {
        LOGGER.info("Getting database version...");

        Flyway flywayCore = createFlywayCore(classLoader);
        Map<String, Flyway> flywayAgents = createFlywaysAgents(classLoader);

        String coreActualVersion = getMigrationsVersion(flywayCore.info().applied());
        String coreLatestVersion;

        MigrationInfo[] corePendingMigrations = flywayCore.info().pending();
        if (corePendingMigrations == null || corePendingMigrations.length == 0) {
            coreLatestVersion = coreActualVersion;
        }
        else {
            coreLatestVersion = getMigrationsVersion(corePendingMigrations);
        }
        ComponentVersions coreVersions = new ComponentVersions(coreActualVersion, coreLatestVersion);
        LOGGER.debug("Core actual version: {}", coreActualVersion != null ? coreActualVersion.toString() : null);
        LOGGER.debug("Core latest version: {}", coreLatestVersion != null ? coreLatestVersion.toString() : null);

        Map<String, ComponentVersions> allAgentVersions = new HashMap<>();
        for (Map.Entry<String, Flyway> flywayAgentEntry : flywayAgents.entrySet()) {
            String agentName = flywayAgentEntry.getKey();
            Flyway flywayAgent = flywayAgentEntry.getValue();

            String agentActualVersion = getMigrationsVersion(flywayAgent.info().applied());
            String agentLatestVersion;

            MigrationInfo[] agentPendingMigrations = flywayAgent.info().pending();
            if (agentPendingMigrations == null || agentPendingMigrations.length == 0) {
                agentLatestVersion = agentActualVersion;
            }
            else {
                agentLatestVersion = getMigrationsVersion(agentPendingMigrations);
            }
            ComponentVersions agentVersions = new ComponentVersions(agentActualVersion, agentLatestVersion);
            LOGGER.debug("Agent {} actual version: {}", agentName, agentActualVersion != null ? agentActualVersion.toString() : null);
            LOGGER.debug("Agent {} latest version: {}", agentName, agentLatestVersion != null ? agentLatestVersion.toString() : null);

            allAgentVersions.put(agentName, agentVersions);
        }

        return new DatabaseVersion(coreVersions, allAgentVersions);
    }

    public static DatabaseMigrations getMigrations(DatabaseConfiguration databaseConfig) throws Exception {
        Database database = new Database(databaseConfig);
        DatabaseMigrations migrations = database.getMigrations();
        return migrations;
    }

    /**
     * Return all migrations.
     *
     * @return migrations
     * @throws MigrationException
     */
    public DatabaseMigrations getMigrations() throws Exception {
        Connection connection = getConnection();
        try {
            AgentClassLoader classLoader = getAgentClassLoaderByInstalledAgents(connection);
            try {
                DatabaseMigrations migrations = getMigrations(classLoader);
                return migrations;
            }
            finally {
                classLoader.close();
            }
        }
        finally {
            connection.close();
        }
    }

    public DatabaseMigrations getMigrations(AgentClassLoader classLoader) throws Exception {
        LOGGER.info("Getting database migrations...");

        Flyway flywayCore = createFlywayCore(classLoader);
        Map<String, Flyway> flywayAgents = createFlywaysAgents(classLoader);

        List<Migration> coreMigrations = getMigrations(flywayCore);
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("Core migrations:");
            for (Migration coreMigration : coreMigrations) {
                LOGGER.debug("{}", coreMigration.toString());
            }
        }

        Map<String, List<Migration>> allAgentMigrations = new HashMap<>();
        for (Map.Entry<String, Flyway> flywayAgentEntry : flywayAgents.entrySet()) {
            String agentName = flywayAgentEntry.getKey();
            Flyway flywayAgent = flywayAgentEntry.getValue();

            List<Migration> agentMigrations = getMigrations(flywayAgent);
            if (LOGGER.isDebugEnabled()) {
                LOGGER.debug("Agent {} migrations:", agentName);
                for (Migration agentMigration : agentMigrations) {
                    LOGGER.debug("{}", agentMigration.toString());
                }
            }

            allAgentMigrations.put(agentName, agentMigrations);
        }

        return new DatabaseMigrations(coreMigrations, allAgentMigrations);
    }

    public static void clean(DatabaseConfiguration databaseConfig) throws Exception {
        Database database = new Database(databaseConfig);
        database.clean();
    }

    /**
     * Cleans the database.
     * !!! REMOVES ALL THE TABLES FROM THE DATABASE !!!
     *
     * @throws MigrationException
     */
    public void clean() throws Exception {
        Connection connection = getConnection();
        try {
            AgentClassLoader classLoader = getAgentClassLoaderByInstalledAgents(connection);
            try {
                clean(classLoader);
            }
            finally {
                classLoader.close();
            }
        }
        finally {
            connection.close();
        }
    }

    public void clean(AgentClassLoader classLoader) throws Exception {
        LOGGER.info("Cleaning the database...");

        Flyway flywayCore = createFlywayCore(classLoader);
        Map<String, Flyway> flywayAgents = createFlywaysAgents(classLoader);

        try {
            for (Map.Entry<String, Flyway> flywayAgentEntry : flywayAgents.entrySet()) {
                String agentName = flywayAgentEntry.getKey();
                Flyway flywayAgent = flywayAgentEntry.getValue();

                LOGGER.info("Cleaning database for agent {}...", agentName);
                flywayAgent.clean();
            }

            LOGGER.info("Cleaning database for core...");
            flywayCore.clean();

            LOGGER.info("Database successfully cleaned");
        }
        catch (FlywayException ex) {
            LOGGER.error("Failed to clean database", ex);
            throw new MigrationException("Failed to clean database", ex);
        }
    }

    public static void baseline(DatabaseConfiguration databaseConfig, String baselineVersion, String baselineDescription) throws Exception {
        Database database = new Database(databaseConfig);
        database.baseline(baselineVersion, baselineDescription);
    }

    /**
     * Baseline the database.
     * This method creates initial migration based on the current database tables.
     *
     * @param baselineVersion initial database version
     * @param baselineDescription initial description
     * @throws MigrationException
     */
    public void baseline(String baselineVersion, String baselineDescription) throws Exception {
        Connection connection = getConnection();
        try {
            AgentClassLoader classLoader = getAgentClassLoaderByInstalledAgents(connection);
            try {
                baseline(classLoader, baselineVersion, baselineDescription);
            }
            finally {
                classLoader.close();
            }
        }
        finally {
            connection.close();
        }
    }

    public void baseline(AgentClassLoader classLoader, String baselineVersion, String baselineDescription) throws Exception {
        LOGGER.info("Baselining the database to version {}...", baselineVersion);

        Flyway flywayCore = createFlywayCore(classLoader);
        Map<String, Flyway> flywayAgents = createFlywaysAgents(classLoader);

        try {
            LOGGER.info("Baselining core to version {}...", baselineVersion);
            internalBaseline(flywayCore, baselineVersion, baselineDescription);

            for (Map.Entry<String, Flyway> flywayAgentEntry : flywayAgents.entrySet()) {
                String agentName = flywayAgentEntry.getKey();
                Flyway flywayAgent = flywayAgentEntry.getValue();

                LOGGER.info("Baselining agent {} to version {}...", agentName, baselineVersion);
                internalBaseline(flywayAgent, baselineVersion, baselineDescription);
            }

            LOGGER.info("Database successfully baselined");
        }
        catch (FlywayException ex) {
            LOGGER.error("Failed to baseline database", ex);
            throw new MigrationException("Failed to baseline database", ex);
        }
    }

    public static void baselineToLatestVersion(DatabaseConfiguration databaseConfig) throws Exception {
        Database database = new Database(databaseConfig);
        database.baselineToLatestVersion();
    }

    public void baselineToLatestVersion() throws Exception {
        Connection connection = getConnection();
        try {
            AgentClassLoader classLoader = getAgentClassLoaderByInstalledAgents(connection);
            try {
                baselineToLatestVersion(classLoader);
            }
            finally {
                classLoader.close();
            }
        }
        finally {
            connection.close();
        }
    }

    /**
     * Baseline the database to the latest available version.
     *
     * @throws MigrationException
     */
    public void baselineToLatestVersion(AgentClassLoader classLoader) throws Exception {
        LOGGER.info("Baselining the database to the latest version...");

        Flyway flywayCore = createFlywayCore(classLoader);
        Map<String, Flyway> flywayAgents = createFlywaysAgents(classLoader);

        try {
            LOGGER.info("Baselining core to latest version...");
            internalBaselineToLatestVersion(flywayCore);

            for (Map.Entry<String, Flyway> flywayAgentEntry : flywayAgents.entrySet()) {
                String agentName = flywayAgentEntry.getKey();
                Flyway flywayAgent = flywayAgentEntry.getValue();

                LOGGER.info("Baselining agent {} to latest version...", agentName);
                internalBaselineToLatestVersion(flywayAgent);
            }

            LOGGER.info("Database successfully baselined to the latest version");
        }
        catch (FlywayException ex) {
            LOGGER.error("Failed to baseline database", ex);
            throw new MigrationException("Failed to baseline database", ex);
        }
    }

    private void internalBaseline(Flyway flyway, String baselineVersion, String baselineDescription) {
        LOGGER.debug("Baselining to version {}...", baselineVersion);
        flyway.setBaselineVersionAsString(baselineVersion);
        flyway.setBaselineDescription(baselineDescription);
        flyway.baseline();
    }

    private void internalBaselineToLatestVersion(Flyway flyway) {
        String latestVersion = getMigrationsVersion(flyway.info().pending());
        LOGGER.debug("Baselining to latest version {}...", latestVersion);
        flyway.setBaselineVersionAsString(latestVersion);
        flyway.baseline();
    }

    public static boolean validate(DatabaseConfiguration databaseConfig) throws Exception {
        Database database = new Database(databaseConfig);
        boolean isValid = database.validate();
        return isValid;
    }

    /**
     * Validate the database.
     *
     * @return true if database is valid, false otherwise
     * @throws MigrationException
     */
    public boolean validate() throws Exception {
        Connection connection = getConnection();
        try {
            AgentClassLoader classLoader = getAgentClassLoaderByInstalledAgents(connection);
            try {
                boolean isValid = validate(classLoader);
                return isValid;
            }
            finally {
                classLoader.close();
            }
        }
        finally {
            connection.close();
        }
    }

    public boolean validate(AgentClassLoader classLoader) throws Exception {
        LOGGER.debug("Validating the database");

        Flyway flywayCore = createFlywayCore(classLoader);
        Map<String, Flyway> flywayAgents = createFlywaysAgents(classLoader);

        try {
            LOGGER.debug("Validating core...");
            flywayCore.validate();

            for (Map.Entry<String, Flyway> flywayAgentEntry : flywayAgents.entrySet()) {
                String agentName = flywayAgentEntry.getKey();
                Flyway flywayAgent = flywayAgentEntry.getValue();

                LOGGER.info("Validating agent {}...", agentName);
                flywayAgent.validate();
            }

            LOGGER.debug("Database successfully validated");
            return true;
        }
        catch (FlywayException ex) {
            LOGGER.error("Validate failed", ex);
            return false;
        }
    }

    public static void repair(DatabaseConfiguration databaseConfig) throws Exception {
        Database database = new Database(databaseConfig);
        database.repair();
    }

    /**
     * Repair the database. This will perform the following actions:
     * <ul>
     * <li>Remove any failed migrations on databases without DDL transactions (User objects left behind must still be cleaned up manually)</li>
     * <li>Correct wrong checksums</li>
     * </ul>
     *
     * @throws MigrationException
     */
    public void repair() throws Exception {
        Connection connection = getConnection();
        try {
            AgentClassLoader classLoader = getAgentClassLoaderByInstalledAgents(connection);
            try {
                repair(classLoader);
            }
            finally {
                classLoader.close();
            }
        }
        finally {
            connection.close();
        }
    }

    public void repair(AgentClassLoader classLoader) throws Exception {
        LOGGER.info("Repairing the database");

        Flyway flywayCore = createFlywayCore(classLoader);
        Map<String, Flyway> flywayAgents = createFlywaysAgents(classLoader);

        try {
            LOGGER.debug("Repairing core...");
            flywayCore.repair();

            for (Map.Entry<String, Flyway> flywayAgentEntry : flywayAgents.entrySet()) {
                String agentName = flywayAgentEntry.getKey();
                Flyway flywayAgent = flywayAgentEntry.getValue();

                LOGGER.info("Repairing agent {}...", agentName);
                flywayAgent.repair();
            }

            LOGGER.info("Database successfully repaired");
        }
        catch (FlywayException ex) {
            LOGGER.error("Repair failed", ex);
            throw new MigrationException("Failed to repair database", ex);
        }
    }

    private void migrateSchema(Flyway flyway) throws MigrationException {
        LOGGER.debug("Repairing and migrating schema...");
        try {
            flyway.repair();
            flyway.migrate();
        }
        catch (FlywayException ex) {
            LOGGER.error("Failed to migrate to the latest version", ex);
            throw new MigrationException("Failed to migrate to the latest version", ex);
        }
    }

    /**
     * Return the locations for the database migration files.
     * This method returns the following migration locations:
     * - common SQL migration location (which
     * contains migrations for all supported databases);
     * - SQL migration location that are specific for DB used (for example,
     * postgres);
     * - Java migration package.
     *
     * @return migration locations
     * @throws MigrationException
     */
    private String[] getMigrationLocations(LocationConfiguration locationConfiguration) throws MigrationException {
        if (locationConfiguration == null) {
            return null;
        }

        try {
            String dialectFolder = Utils.getFolderByDialect(databaseConfig.getDialect());
            return new String[] {
                    String.format("%s.%s", locationConfiguration.getSqlMigrationsPrefix(), COMMON_SQL_MIGRATIONS_FOLDER),
                    String.format("%s.%s", locationConfiguration.getSqlMigrationsPrefix(), dialectFolder),
                    locationConfiguration.getJavaMigrationsPackage()
        };
        }
        catch (ClassNotFoundException ex) {
            throw new MigrationException(String.format("Dialect class %s not found", databaseConfig.getDialect()), ex);
        }
    }

    private Migration getMigration(MigrationInfo migrationInfo) throws MigrationException {
        Migration migration = new Migration();
        migration.setDescription(migrationInfo.getDescription());
        migration.setInstallationDate(migrationInfo.getInstalledOn());
        migration.setState(getMigrationState(migrationInfo.getState()));
        migration.setType(getMigrationType(migrationInfo.getType()));
        migration.setVersion(migrationInfo.getVersion().getVersion());
        return migration;
    }

    private MigrationState getMigrationState(org.flywaydb.core.api.MigrationState state) throws MigrationException {
        switch (state) {
            case PENDING:
                return MigrationState.PENDING;
            case ABOVE_TARGET:
            case BELOW_BASELINE:
            case IGNORED:
                return MigrationState.IGNORED;
            case BASELINE:
            case MISSING_SUCCESS:
            case SUCCESS:
            case OUT_OF_ORDER:
            case FUTURE_SUCCESS:
                return MigrationState.SUCCESS;
            case MISSING_FAILED:
            case FAILED:
            case FUTURE_FAILED:
                return MigrationState.FAILED;
            case OUTDATED:
            case SUPERSEEDED:
                return MigrationState.OUTDATED;
            default:
                throw new MigrationException("Unknown migration state: " + state);
        }
    }

    private MigrationType getMigrationType(org.flywaydb.core.api.MigrationType type) throws MigrationException {
        switch (type) {
            case BASELINE:
            case SCHEMA:
                return MigrationType.INITIAL;
            case JDBC:
            case SPRING_JDBC:
                return MigrationType.JAVA;
            case CUSTOM:
                return MigrationType.CUSTOM;
            case SQL:
                return MigrationType.SQL;
            default:
                throw new MigrationException("Unknown migration type: " + type);
        }
    }

    private String getMigrationsVersion(MigrationInfo[] migrations) {
        MigrationInfo latestMigration = getLatestMigration(migrations);
        if (latestMigration == null) {
            LOGGER.debug("No migrations found.");
            return null;
        }

        String latestMigrationVersion = latestMigration.getVersion().getVersion();
        LOGGER.debug("The latest migration has the version {}", latestMigrationVersion);

        return latestMigrationVersion;
    }

    private MigrationInfo getLatestMigration(MigrationInfo[] migrations) {
        if (migrations == null || migrations.length == 0) {
            return null;
        }
        LOGGER.debug("{} migrations found", migrations.length);
        List<MigrationInfo> migrationsList = Arrays.asList(migrations);
        Collections.sort(migrationsList);
        return migrationsList.get(migrationsList.size() - 1);
    }

    private List<Migration> getMigrations(Flyway flyway) throws MigrationException {
        LOGGER.trace("getMigrations()");
        try {
            MigrationInfoService infoService = flyway.info();
            MigrationInfo[] migrationInfos = infoService.all();
            List<Migration> migrations = new ArrayList<>();
            LOGGER.debug("{} migrations", migrationInfos.length);
            for (MigrationInfo migrationInfo : migrationInfos) {
                Migration migration = getMigration(migrationInfo);
                LOGGER.debug("{}", migration.toString());
                migrations.add(migration);
            }
            return migrations;
        }
        catch (FlywayException ex) {
            throw new MigrationException("Failed to get migrations", ex);
        }
    }

    private Flyway createFlywayCore(AgentClassLoader classLoader) throws MigrationException {
        LOGGER.debug("Creating flyway object for core...");
        Flyway flywayCore = createFlyway(classLoader, null, null);
        return flywayCore;
    }

    private Flyway createFlywayAgent(AgentClassLoader classLoader, String agentName) throws Exception {
        LOGGER.debug("Creating flyway object for agent {}...", agentName);
        AgentConfiguration agentConfig = getAgentConfiguration(classLoader, agentName);
        Flyway flywayAgent = createFlyway(classLoader, agentName, agentConfig);
        return flywayAgent;
    }

    private Map<String, Flyway> createFlywaysAgents(AgentClassLoader classLoader) throws Exception {
        Map<String, Flyway> flywayAgents = new HashMap<String, Flyway>();
        for (String agentName : classLoader.getAgentNames()) {
            Flyway flywayAgent = createFlywayAgent(classLoader, agentName);
            flywayAgents.put(agentName, flywayAgent);
        }
        return flywayAgents;
    }

    /**
     * Create flyway instance for core or agent
     *
     * @param agentConfiguration agent configuration if creating flyway for agent, or null
     * if creating for core
     * @return flyway
     * @throws MigrationException
     */
    private Flyway createFlyway(AgentClassLoader classLoader, String agentName, AgentConfiguration agentConfiguration) throws MigrationException {
        Flyway flyway = new Flyway();

        flyway.setClassLoader(classLoader);

        flyway.setDataSource(databaseConfig.getConnectionString(), databaseConfig.getUsername(), databaseConfig.getPassword());

        /*
         * Change the default placeholder prefix from the default ${ to $${
         * This is required because some our import scripts have ${...} (for
         * example, ${NMS_HOME}) that should not be expanded, and Flyway
         * complains that the placeholder cannot be resolved.
         */
        flyway.setPlaceholderPrefix(PLACEHOLDER_PREFIX);

        String encoding = getEncodingOrDefault();
        flyway.setEncoding(encoding);

        String[] locations;
        if (agentConfiguration != null) {
            locations = getMigrationLocations(agentConfiguration.getLocationConfiguration());
        }
        else {
            locations = getMigrationLocations(new LocationConfiguration(CORE_SQL_MIGRATIONS_PREFIX, CORE_JAVA_MIGRATIONS_PACKAGE));
        }
        flyway.setLocations(locations);

        String schema = databaseConfig.getSchema();
        if (schema != null) {
            flyway.setSchemas(schema);
        }

        if (agentName != null) {
            String tableName = getAgentSchemaVersionTableName(agentName);
            flyway.setTable(tableName);
        }

        boolean outOfOrder = getOutOfOrderOrDefault();
        flyway.setOutOfOrder(outOfOrder);

        boolean validateOnMigrate = getValidateOnMigrateOrDefault();
        flyway.setValidateOnMigrate(validateOnMigrate);

        /*
         * Without this property set to true, initial migrate will fail for agent
         * because schema is not empty.
         */
        flyway.setBaselineOnMigrate(true);

        return flyway;
    }

    public static String getAgentSchemaVersionTableName(String agentName) {
        String tableName = String.format(SCHEMA_VERSION_AGENT_TABLE_TEMPLATE, agentName);
        return tableName;
    }

    private Connection getConnection() throws SQLException {
        return getConnection(databaseConfig.getConnectionString(), databaseConfig.getUsername(), databaseConfig.getPassword());
    }

    private Connection getConnection(String connectionString, String username, String password) throws SQLException {
        Connection connection = DriverManager.getConnection(connectionString, username, password);
        return connection;
    }

    private AgentClassLoader getAgentClassLoaderByJar(String agentJarPath) throws MalformedURLException {
        AgentClassLoader classLoader = new AgentClassLoader();
        classLoader.addAgentFile(agentJarPath);
        return classLoader;
    }

    private AgentClassLoader getAgentClassLoaderByJars(String[] agentJarPaths) throws MalformedURLException {
        AgentClassLoader classLoader = new AgentClassLoader();
        classLoader.addAgentFiles(agentJarPaths);
        return classLoader;
    }

    private AgentClassLoader getAgentClassLoaderByDirectory(String agentDirectory) {
        AgentClassLoader classLoader = new AgentClassLoader();
        AgentDirectoryPlaceholderResolver placeholderResolver = new AgentDirectoryPlaceholderResolver();
        String resolvedAgentDirectory = placeholderResolver.replacePlaceholders(agentDirectory);
        LOGGER.debug("Resovled agent directory: {} -> {}", agentDirectory, resolvedAgentDirectory);
        classLoader.addAgentFolder(resolvedAgentDirectory);
        return classLoader;
    }

    private AgentClassLoader getAgentClassLoaderByInstalledAgents(Connection connection) throws SQLException {
        AgentClassLoader classLoader = new AgentClassLoader();
        AgentDirectoryPlaceholderResolver placeholderResolver = new AgentDirectoryPlaceholderResolver();
        List<TEM_Agent> installedAgents = getInstalledAgents(connection);
        for (TEM_Agent installedAgent : installedAgents) {
            String agentDirectory = installedAgent.getDirectory();
            String resolvedAgentDirectory = placeholderResolver.replacePlaceholders(agentDirectory);
            LOGGER.debug("Resolved agent directory: {} -> {}", agentDirectory, resolvedAgentDirectory);
            classLoader.addAgentFolder(resolvedAgentDirectory);
        }
        return classLoader;
    }

    private static final String queryGetInstalledAgents = "SELECT id, agentversion, description, deviceclass, directory, displayname, installationdate, name, agentclass FROM tem_agent";

    private List<TEM_Agent> getInstalledAgents(Connection connection) throws SQLException {
        if (!isAgentsTableExists(connection)) {
            return new ArrayList<TEM_Agent>();
        }

        Statement statement = connection.createStatement();
        ResultSet agentsResultSet = statement.executeQuery(queryGetInstalledAgents);
        List<TEM_Agent> result = new ArrayList<TEM_Agent>();
        while (agentsResultSet.next()) {
            TEM_Agent agent = getAgentFromResultSet(agentsResultSet);
            result.add(agent);
        }
        return result;
    }

    private static final String queryGetInstalledAgentById = "SELECT id, agentversion, description, deviceclass, directory, displayname, installationdate, name, agentclass FROM tem_agent WHERE id=?";

    private TEM_Agent getInstalledAgentById(Connection connection, Long agentId) throws SQLException {
        PreparedStatement statement = connection.prepareStatement(queryGetInstalledAgentById);
        statement.setLong(1, agentId);
        ResultSet agentResultSet = statement.executeQuery();
        if (agentResultSet.next()) {
            TEM_Agent agent = getAgentFromResultSet(agentResultSet);
            return agent;
        }
        else {
            return null;
        }
    }

    private static final String queryGetInstalledAgentByName = "SELECT id, agentversion, description, deviceclass, directory, displayname, installationdate, name, agentclass FROM tem_agent WHERE name=?";

    private TEM_Agent getInstalledAgentByName(Connection connection, String agentName) throws SQLException {
        PreparedStatement statement = connection.prepareStatement(queryGetInstalledAgentByName);
        statement.setString(1, agentName);
        ResultSet agentResultSet = statement.executeQuery();
        if (agentResultSet.next()) {
            TEM_Agent agent = getAgentFromResultSet(agentResultSet);
            return agent;
        }
        else {
            return null;
        }
    }

    private TEM_Agent getAgentFromResultSet(ResultSet resultSet) throws SQLException {
        Long id = resultSet.getLong("id");
        String agentVersion = resultSet.getString("agentversion");
        String description = resultSet.getString("description");
        String deviceClass = resultSet.getString("deviceclass");
        String directory = resultSet.getString("directory");
        String displayName = resultSet.getString("displayname");
        Date installationDate = resultSet.getDate("installationdate");
        String name = resultSet.getString("name");
        String agentClass = resultSet.getString("agentclass");

        TEM_Agent agent = new TEM_Agent();
        agent.setId(id);
        agent.setAgentVersion(agentVersion);
        agent.setDescription(description);
        agent.setDeviceClass(deviceClass);
        agent.setDirectory(directory);
        agent.setDisplayName(displayName);
        agent.setInstallationDate(installationDate);
        agent.setName(name);
        agent.setAgentClass(agentClass);

        return agent;
    }

    private static final String queryInsertAgent_postgres = "INSERT INTO tem_agent(id, version, agentversion, description, deviceclass, directory, displayname, installationdate, name, agentclass) " +
            "VALUES(nextval('id_sequence'), 0, ?, ?, ?, ?, ?, ?, ?, ?)";

    private static final String queryInsertAgent_hsqldb = "INSERT INTO tem_agent(id, version, agentversion, description, deviceclass, directory, displayname, installationdate, name, agentclass) " +
            "VALUES(nextval('id_sequence'), 0, ?, ?, ?, ?, ?, ?, ?, ?)";

    private String getInsertAgentQuery(String dialectFolder) throws Exception {
        if (Utils.FOLDER_POSTGRES.equals(dialectFolder)) {
            return queryInsertAgent_postgres;
        }
        else if (Utils.FOLDER_HSQLDB.equals(dialectFolder)) {
            return queryInsertAgent_hsqldb;
        }
        else if (Utils.FOLDER_ORACLE.equals(dialectFolder)) {
            throw new Exception(String.format("Inserting into agents table is not implemented for dialect %s", dialectFolder));
        }
        else {
            throw new Exception(String.format("Dialect %s not supported", dialectFolder));
        }
    }

    private Properties getAgentProperties(AgentClassLoader classLoader, String agentName) throws Exception {
        Properties properties = classLoader.getPropertiesByAgentName(agentName);
        if (properties == null) {
            throw new Exception(String.format("Properties for agent %s not found", agentName));
        }
        return properties;
    }

    private AgentConfiguration getAgentConfiguration(AgentClassLoader classLoader, String agentName) throws Exception {
        Properties agentProperties = getAgentProperties(classLoader, agentName);

        String sqlMigrationsPrefix = agentProperties.getProperty(AgentProperties.AGENT_SQL_MIGRATIONS_PREFIX_PROPERTY);
        LOGGER.debug("Agent {} SQL migrations prefix: {}", agentName, sqlMigrationsPrefix);
        String javaMigrationsPackage = agentProperties.getProperty(AgentProperties.AGENT_JAVA_MIGRATIONS_PACKAGE_PROPERTY);
        LOGGER.debug("Agent {} Java migrations package: {}", agentName, javaMigrationsPackage);

        AgentConfiguration agentConfig = new AgentConfiguration(new LocationConfiguration(sqlMigrationsPrefix, javaMigrationsPackage));
        return agentConfig;
    }

    private String getAgentSqlResourceFolder(AgentClassLoader classLoader, String agentName) throws Exception {
        Properties agentProperties = getAgentProperties(classLoader, agentName);
        String resourceFolder = agentProperties.getProperty(AgentProperties.AGENT_SQL_FOLDER_PROPERTY);
        LOGGER.debug("Agent {} SQL folder: {}", agentName, resourceFolder);
        return resourceFolder;
    }

    private String[] loadCommands(ClassLoader classLoader, ImportSqlCommandExtractor commandExtractor,
            String resourceFolder, String dialectFolder, String[] scriptFileNames, String encoding) throws IOException {
        List<String> allScriptCommands = new ArrayList<String>();
        for (String scriptFileName : scriptFileNames) {
            String[] scriptCommands = loadCommands(classLoader, commandExtractor, resourceFolder, dialectFolder, scriptFileName, encoding);
            if (scriptCommands != null) {
                allScriptCommands.addAll(Arrays.asList(scriptCommands));
            }
        }
        return allScriptCommands.toArray(new String[allScriptCommands.size()]);
    }

    private String[] loadCommands(ClassLoader classLoader, ImportSqlCommandExtractor commandExtractor,
            String resourceFolder, String dialectFolder, String scriptFileName, String encoding) throws IOException {
        LOGGER.debug("Loading commands from file {}", scriptFileName);

        String scriptFilePath = getScriptFilePath(resourceFolder, dialectFolder, scriptFileName);
        LOGGER.debug("Loading commands from resource {}...", scriptFilePath);

        try (InputStream is = classLoader.getResourceAsStream(scriptFilePath)) {
            if (is == null) {
                LOGGER.info("Resource {} not found", scriptFilePath);
                return null;
            }

            LOGGER.info("Resource {} found", scriptFilePath);

            InputStreamReader reader = new InputStreamReader(is, encoding);
            LOGGER.debug("Extracting commands from resource {}", scriptFilePath);
            String[] commands = commandExtractor.extractCommands(reader);
            LOGGER.debug("Extracted {} commands from resource {}", commands.length, scriptFilePath);
            return commands;
        }
    }

    private String loadScriptAsString(ClassLoader classLoader, String resourceFolder, String dialectFolder,
            String scriptFileName, String encoding) throws IOException {
        LOGGER.debug("Loading script from file {}", scriptFileName);

        String scriptFilePath = getScriptFilePath(resourceFolder, dialectFolder, scriptFileName);
        LOGGER.debug("Loading script from resource {}...", scriptFilePath);

        try (InputStream is = classLoader.getResourceAsStream(scriptFilePath)) {
            if (is == null) {
                LOGGER.info("Resource {} not found", scriptFilePath);
                return null;
            }

            LOGGER.info("Resource {} found", scriptFilePath);

            String script = IOUtils.toString(is, encoding);
            return script;
        }
    }

    private String getScriptFilePath(String resourceFolder, String dialectFolder, String scriptFileName) {
        return String.format("%s/%s/%s", resourceFolder, dialectFolder, scriptFileName);
    }

    private List<Exception> executeCommands(Connection connection, String[] commands) throws SQLException {
        List<Exception> exceptions = new ArrayList<Exception>();
        if (commands != null) {
            Statement statement = connection.createStatement();
            for (String command : commands) {
                try {
                    LOGGER.debug("Executing command {}", command);
                    statement.execute(command);
                }
                catch (SQLException ex) {
                    exceptions.add(ex);
                }
            }
        }
        return exceptions;
    }

    private void executeScript(Connection connection, String script) throws SQLException {
        Statement statement = connection.createStatement();
        statement.execute(script);
    }

    private Class<? extends ImportSqlCommandExtractor> getCommandExtractorClassOrDefault() {
        Class<? extends ImportSqlCommandExtractor> commandExtractorClass = databaseConfig.getCommandExtractorClass();
        if (commandExtractorClass == null) {
            commandExtractorClass = DatabaseConfiguration.DEFAULT_COMMAND_EXTRACTOR_CLASS;
        }
        return commandExtractorClass;
    }

    private String getEncodingOrDefault() {
        String encoding = databaseConfig.getEncoding();
        if (encoding == null) {
            encoding = DatabaseConfiguration.DEFAULT_ENCODING;
        }
        return encoding;
    }

    private boolean getOutOfOrderOrDefault() {
        Boolean outOfOrder = databaseConfig.isOutOfOrder();
        if (outOfOrder != null) {
            return outOfOrder.booleanValue();
        }
        else {
            return DatabaseConfiguration.DEFAULT_OUT_OF_ORDER;
        }
    }

    private boolean getValidateOnMigrateOrDefault() {
        Boolean validateOnMigrate = databaseConfig.isValidateOnMigrate();
        if (validateOnMigrate != null) {
            return validateOnMigrate.booleanValue();
        }
        else {
            return DatabaseConfiguration.DEFAULT_VALIDATE_ON_MIGRATE;
        }
    }

    /**
     * Checks if table exists.
     * We just try to select from agents table, and if unsuccessful, return false.
     * First version of this method tried to get connection metadata, but we must
     * know the schema name for that, and getting current schema name is not easy
     * using JDBC.
     *
     * @param connection
     * @return
     */
    private boolean isAgentsTableExists(Connection connection) {
        try {
            Statement statement = connection.createStatement();
            statement.executeQuery(queryGetInstalledAgents);
            LOGGER.debug("Agents table exists");
            return true;
        }
        catch (SQLException ex) {
            LOGGER.debug("Failed to get installed agents", ex);
            return false;
        }
    }

}