package com.parallels.pba.autotests.lib.database;

import com.google.common.base.Charsets;
import com.google.common.io.Files;
import com.google.common.io.Resources;
import com.parallels.autotests.framework.logger.Logger;
import com.parallels.pba.autotests.general.testNameAppender.PBATestListener;
import com.parallels.pba.autotests.lib.constants.Constants;
import org.apache.commons.io.FileUtils;
import org.apache.derby.jdbc.EmbeddedDriver;

import java.io.*;
import java.net.URL;
import java.sql.*;

/**
 * Defines object "Database" and all respective operations.
 */
public class Database implements Constants {
    private final static String DATABASE_PATH = "./db";
    private final static String SQL_RESOURCE_NAME = "pba-test.sql";
    private final static String LAST_MODIFIED_FILE_PATH = "./last-modified.txt";
    private static Database testDataStore = new Database();
    /**
     * Database connection
     */
    private Connection connection;
    /**
     * SQL-statement for database management
     */
    private PreparedStatement preparedStatement;
    /**
     * SQL-query results
     */
    private ResultSet resultSet;

    /**
     * @return the testDataStore
     */
    private static Database getTestDataStore() {
        if (!testDataStore.isConnected()) {
            testDataStore.establishConnection();
        }
        return testDataStore;
    }

    public static Result executeQuery(String query) {
        if (!Database.getTestDataStore().isConnected()) {
            throw new RuntimeException("No connection with test data store.");
        }
        try {
            getTestDataStore().setPreparedStatement(getTestDataStore().getConnection().prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY));
            getTestDataStore().setResultSet(getTestDataStore().getPreparedStatement().executeQuery());
            getTestDataStore().getResultSet().first();
        } catch (SQLException sqlException) {
            throw new RuntimeException(sqlException);
        }

        return new Result(getTestDataStore().getResultSet());
    }

    /**
     * Executes SQL-query on specified connection return result in result set.
     */
    public static Result executeQuery(String query, Integer id) {
        executeQuery(query, id, null);
        try {
            getTestDataStore().getResultSet().first();
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage());
        }

        return new Result(getTestDataStore().getResultSet());
    }

    public static Result executeQuery(String query, String mark) {
        executeQuery(query, null, mark);
        return new Result(getTestDataStore().getResultSet());
    }

    private static void executeQuery(String query, Integer id, String mark) {
        if (!Database.getTestDataStore().isConnected()) {
            throw new RuntimeException("No connection with test data store.");
        }
        try {
            query += " AND LOWER(test_name) = LOWER(?)";
            getTestDataStore().setPreparedStatement(getTestDataStore().getConnection().prepareStatement(
                    query, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY));

            if (mark != null) {
                getTestDataStore().getPreparedStatement().setString(1, mark);
                getTestDataStore().getPreparedStatement().setString(2, PBATestListener.getCurrentTestName());
            } else if (id == null) {
                getTestDataStore().getPreparedStatement().setInt(1, 1);
                getTestDataStore().getPreparedStatement().setString(2, DEFAULT_TEST_NAME);
            } else {
                getTestDataStore().getPreparedStatement().setInt(1, id);
                getTestDataStore().getPreparedStatement().setString(2, PBATestListener.getCurrentTestName());
            }
            getTestDataStore().setResultSet(getTestDataStore().getPreparedStatement().executeQuery());
        } catch (SQLException e) {
            throw new RuntimeException(e.getMessage());
        }
    }

    public static Result getObjectById(String tableName, Integer id) {
        if (!Database.getTestDataStore().isConnected()) {
            throw new RuntimeException("No connection with test data store.");
        }

        executeQuery(String.format("SELECT * FROM %s WHERE id = ?", tableName), id);
        return new Result(getTestDataStore().getResultSet());
    }

    public void establishConnection() {
        if (!isConnected()) {
            try {
                EmbeddedDriver.class.newInstance();
            } catch (Exception e) {
                throw new RuntimeException("Can't register Derby JDBC driver. Reason: " + e.getMessage());
            }
            String currentRequest = "";
            try {
                Logger.debug("Creating connection to tests database");
                if (!isDatabaseResetRequired()) {
                    connection = DriverManager.getConnection(String.format("jdbc:derby:%s;create=true", DATABASE_PATH));
                    setConnection(connection);
                    return;
                }
                if (new File(DATABASE_PATH).exists()) {
                    FileUtils.deleteDirectory(new File(DATABASE_PATH));
                }
                connection = DriverManager.getConnection(String.format("jdbc:derby:%s;create=true", DATABASE_PATH));
                setConnection(connection);
                InputStream bis = (InputStream) Resources.getResource(SQL_RESOURCE_NAME).getContent();
                InputStreamReader isr = new InputStreamReader(bis);
                BufferedReader reader = new BufferedReader(isr);
                String line;
                StringBuilder sb = new StringBuilder();
                Statement statement = connection.createStatement();
                Logger.debug("Database creation started");
                while ((line = reader.readLine()) != null) {
                    if (!line.isEmpty()) {
                        sb.append(line);
                        if (line.trim().endsWith(";")) {
                            currentRequest = sb.toString().replaceAll("\\\\r\\\\n", "\r\n");
                            statement.execute(currentRequest.subSequence(0, currentRequest.length() - 1).toString());
                            sb = new StringBuilder();
                        }
                    }
                }
                Logger.debug("Database successfully created");
                statement.close();
            } catch (Exception e) {
                throw new RuntimeException(String.format("Error while executing SQL query: '%s': '%s'", currentRequest, e.getMessage()));
            }
        }
    }

    private boolean isDatabaseResetRequired() {
        long sqlSourceLastModified = 0;

        try {
            URL sqlSourceUrl = Resources.getResource(SQL_RESOURCE_NAME);
            sqlSourceLastModified = sqlSourceUrl.openConnection().getLastModified();
            if (new File(DATABASE_PATH).exists() && new File(LAST_MODIFIED_FILE_PATH).exists()) {
                long lastModified = Long.parseLong(Files.toString(new File(LAST_MODIFIED_FILE_PATH), Charsets.UTF_8));
                if (sqlSourceLastModified == lastModified) {
                    return false;
                }
            }
        } catch (Exception e) {
            Logger.debug(String.format("Test database should be recreated since '%s'", e.getMessage()));
        }
        // writing last modified date into temporary file
        try {
            Writer writer = new BufferedWriter(new FileWriter(LAST_MODIFIED_FILE_PATH, false));
            writer.write(String.valueOf(sqlSourceLastModified));
            writer.close();
            return true;
        } catch (Exception exception) {
            throw new RuntimeException(exception);
        }
    }

    /**
     * Defines if connection to  database is established or not.
     *
     * @return true - connection with Derby database is established, false -
     *         connection with Derby database is not established.
     */
    public Boolean isConnected() {
        return getConnection() != null;
    }

    /**
     * @return the connection
     */
    public Connection getConnection() {
        return connection;
    }

    /**
     * @param connection the connection to set
     */
    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    /**
     * @return the preparedStatement
     */
    public PreparedStatement getPreparedStatement() {
        return preparedStatement;
    }

    /**
     * @param preparedStatement the preparedStatement to set
     */
    public void setPreparedStatement(PreparedStatement preparedStatement) {
        this.preparedStatement = preparedStatement;
    }

    /**
     * @return the resultSet
     */
    public ResultSet getResultSet() {
        return resultSet;
    }

    /**
     * @param resultSet the resultSet to set
     */
    public void setResultSet(ResultSet resultSet) {
        this.resultSet = resultSet;
    }
}