package de.tbressler.waterrower.workout.db;

import de.tbressler.waterrower.workout.db.utils.DerbyUtils;
import de.tbressler.waterrower.workout.file.Workspace;
import de.tbressler.waterrower.workout.log.Log;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import static com.google.common.base.Joiner.on;
import static de.tbressler.waterrower.workout.db.utils.DerbyUtils.tableAlreadyExists;
import static de.tbressler.waterrower.workout.log.Log.DATABASE;
import static de.tbressler.waterrower.workout.log.Log.SQL;
import static java.lang.Class.forName;
import static java.sql.DriverManager.getConnection;

/**
 * Service which manages the database connection.
 *
 * As database a local embedded derby DBMS will be used. The database and tables will be created
 * during runtime if it not exists.
 *
 * @author Tobias Bressler
 * @version 1.0
 */
public class DatabaseConnectionService {

    /* JDBC driver class for derby. */
    private static final String JDBC_DERBY_DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";

    /* JDBC protocol asVarchar for derby. */
    private static final String PROTOCOL = "jdbc:derby:";

    /* Name of local database folder. */
    private final String db;


    /* The database connection. */
    private Connection connection;


    /**
     * Constructor.
     */
    public DatabaseConnectionService(Workspace workspace) {
        db = workspace.getDatabase();
        initJdbcDriver();
    }

    /* Init JDBC driver class. */
    private void initJdbcDriver() {
        try {
            Log.debug(DATABASE, "Initializing derby driver...");
            forName(JDBC_DERBY_DRIVER).newInstance();
            Log.debug(DATABASE, "Derby driver initialized.");
        } catch (InstantiationException | IllegalAccessException | ClassNotFoundException e) {
            Log.error("Couldn't initialize derby driver!", e);
        }
    }


    /**
     * Connect to local database.
     */
    public void connect() {
        try {
            Log.debug(DATABASE, "Connecting to local database...");
            connection = getConnection(PROTOCOL+ db +";create=true");
            Log.debug(DATABASE, "Successfully connected to local database.");
        } catch (SQLException e) {
            Log.error("Couldn't connect to derby database!", e);
        }
    }


    /**
     * Creates the given table, if the table doesn't exist.
     *
     * @param table The name of the table.
     * @param columns The columns of the table, in format "name datatype".
     * @return True if table was created or false if the table already exists.
     */
    public boolean createTable(String table, String...columns) {
        if (columns.length == 0)
            throw new IllegalArgumentException("No columns given!");

        try (Statement statement = connection.createStatement()) {

            Log.debug(DATABASE, "Creating database table '"+table+"', if table doesn't exists...");

            execute(statement, "CREATE TABLE "+table+" ("+ on(", ").join(columns)+")", null);

            Log.debug(DATABASE, "Database table '"+table+"' created.");

            return true;

        } catch(SQLException e) {
            if (tableAlreadyExists(e)) {
                Log.debug(DATABASE, "Database table '"+table+"' already exists.");
                return false;
            }
            Log.error("Couldn't create database table!", e);
            return false;
        }
    }


    /**
     * Insert data into the database.
     *
     * @param insert The INSERT interface.
     * @return The auto-generated key for the identity column.
     */
    public int insert(IDBInsert insert) {
        try (Statement statement = connection.createStatement()) {

            return execute(statement, insert.getQuery(), insert.getIdentityColumn());

        } catch(SQLException e) {
            Log.error("Couldn't execute insert statement!", e);
            return -1;
        }
    }

    /* Execute SQL statement. */
    private int execute(Statement statement, String sql, String autoColumn) throws SQLException {
        Log.debug(SQL, sql);

        // Execute statement without auto-generated key:
        if (autoColumn == null) {
            statement.execute(sql);
            return -1;
        }

        // Execute statement with auto-generated key:
        statement.execute(sql, new String[]{autoColumn.toUpperCase()});
        return getAutoGeneratedKey(statement);
    }

    /* Returns the auto generated key for the statement. */
    private int getAutoGeneratedKey(Statement statement) throws SQLException {
        int key = 0;

        ResultSet resultSet = statement.getGeneratedKeys();
        if (resultSet == null) {
            Log.warn(SQL, "No auto generated key found.");
            return -1;
        }

        while(resultSet.next())
            key = resultSet.getInt(1);

        Log.debug(SQL, "The auto generated key is '"+key+"'.");

        return key;
    }


    /**
     * Execute a query at the database with the help of the DAO.
     *
     * @param select The SELECT interface.
     * @return The result or null.
     */
    public <T> T select(IDBSelect<T> select) {
        try (Statement statement = connection.createStatement()) {

            if (select.getLimit() > 0)
                statement.setMaxRows(select.getLimit());

            ResultSet resultSet = executeQuery(statement, select.getQuery());

            return select.getResult(resultSet);

        } catch (SQLException e) {
            Log.error("Couldn't execute SQL statement!", e);
            return null;
        }
    }

    /**
     * Execute a query at the database with the help of the DAO.
     *
     * @param update The UPDATE interface.
     * @return The result or null.
     */
    public void update(IDBUpdate update) {
        try (Statement statement = connection.createStatement()) {

            execute(statement, update.getQuery(), null);

        } catch(SQLException e) {
            Log.error("Couldn't execute UPDATE statement!", e);
        }
    }

    /**
     * Execute a query at the database with the help of the DAO.
     *
     * @param delete The DELETE interface.
     * @return The result or null.
     */
    public void delete(IDBDelete delete) {
        try (Statement statement = connection.createStatement()) {

            execute(statement, delete.getQuery(), null);

        } catch(SQLException e) {
            Log.error("Couldn't execute DELETE statement!", e);
        }
    }

    /* Execute SQL statement. */
    private ResultSet executeQuery(Statement statement, String sql) throws SQLException {
        Log.debug(SQL, sql);
        return statement.executeQuery(sql);
    }


    /**
     * Disconnects and shuts down the database.
     */
    public void disconnect() {
        try {
            // Close connection.
            Log.debug(DATABASE, "Disconnecting local database...");
            connection.close();
            Log.debug(DATABASE, "Local database disconnected. Shutting down database...");
            getConnection(PROTOCOL+";shutdown=true");
            connection = null;
        } catch (SQLException e) {
            if (DerbyUtils.isDerbyShutdown(e)) {
                Log.debug(DATABASE, "Shutdown of database was successful.");
                return;
            }
            Log.error("Couldn't disconnect/shutdown derby database!", e);
        }
    }

}