package com.strategyprocess.database;

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.concurrent.BlockingQueue;
import java.util.concurrent.LinkedBlockingDeque;
import java.util.concurrent.TimeUnit;

import com.strategyprocess.config.APIConfiguration;
import com.strategyprocess.config.DatabaseConfiguration;
import com.strategyprocess.logging.LogSystem;
import com.strategyprocess.thread.ThreadManager;

public class MySQLPool {

    private String host = DatabaseConfiguration.get().getProperty("host");
    private String port = DatabaseConfiguration.get().getProperty("port");
    private String database = DatabaseConfiguration.get().getProperty("database");
    private String username = DatabaseConfiguration.get().getProperty("username");
    private String password = DatabaseConfiguration.get().getProperty("password");

    BlockingQueue<Connection> connectionPoolQueue = new LinkedBlockingDeque<>();

    private static MySQLPool pool;

    public static MySQLPool get() {
        if(pool == null) {
            pool = new MySQLPool();
        }
        return pool;
    }

    public MySQLPool() {
        ThreadManager.get().getScheduledThreadPool().scheduleAtFixedRate(new Runnable() {

            @Override
            public void run() {
                try {
                    checkConnectionAmount();
                } catch (SQLException ex) {
                    LogSystem.get().getLogger().warn("Could not delete old users...", ex);
                }
            }

        }, 10, 10, TimeUnit.MINUTES);
    }

    private void checkConnectionAmount() throws SQLException{
        if(connectionPoolQueue.size() > APIConfiguration.databaseConnectionAmountMax) {
            int amountToRemove = connectionPoolQueue.size() - APIConfiguration.databaseConnectionAmountMax;
            for(int i = 0; i < amountToRemove; i++) {
                Connection con = connectionPoolQueue.poll();
                if(con != null) {
                    con.close();
                }
            }
        }
    }

    //Do not add to pool, because somebody gets it immediately
    private Connection createConnection() {
        try {

            Connection con = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + database, username, password);
            con.setAutoCommit(false);

            LogSystem.get().info("[MySQL] Created new connection!");

            return con;

        } catch (SQLException e) {

            LogSystem.get().error("[MySQL] Cant connect!", e);
            return null;
        }
    }

    private boolean isConnectionValid(Connection con) {
        try {

            if (con != null) {

                Statement st = con.createStatement();
                st.executeQuery("SELECT 1");
                st.close();

                return true;
            } else {
                return false;
            }
        } catch (Exception ex) {
            LogSystem.get().info("[MySQL] A connection is unvalid...");
            return false;
        }
    }

    public Connection takeConnection() throws SQLException {
        Connection con = null;
        int tries = 0;

        while(con == null && tries < APIConfiguration.databaseConnectAttempts) {
            con = getOrCreateConnection();

            tries++;
        }

        if(con == null) {
            throw new SQLException("no connection could be established");
        }

        return con;
    }

    public void returnConnectionAndRollbackUncommitedChanges(Connection con){
        if(con != null) {
            try {
                rollback(con);
            } catch (SQLException e) {
                //Do not add connection to pool
                return;
            }
            connectionPoolQueue.add(con);
        }
    }

    private Connection getOrCreateConnection() {
        while(true) {
            if(connectionPoolQueue.isEmpty()) {
                return createConnection();
            }

            Connection possibleCon = null;

            try {
                possibleCon = connectionPoolQueue.poll(100, TimeUnit.MILLISECONDS);
            } catch (InterruptedException e) {}


            if(possibleCon == null) {
                return createConnection();
            }

            if(isConnectionValid(possibleCon)) {
                return possibleCon;
            }
            else {
                //Do not re-add to pool, because it was unvalid
            }
        }
    }

    public PreparedStatement createStatement(Connection con, String query) throws SQLException {
        return con.prepareStatement(query);
    }

    public ResultSet select(PreparedStatement statement) throws SQLException{
        LogSystem.get().debug("[MySQL] Executing: " + statement.toString());

        return statement.executeQuery();
    }

    public int execute(PreparedStatement statement) throws SQLException {
        LogSystem.get().debug("[MySQL] Executing: " + statement.toString());

        statement.executeUpdate();

        ResultSet result = statement.getGeneratedKeys();
        int resultInt = -1;

        if(result.next()) {
            resultInt = result.getInt(1);

            LogSystem.get().debug("[MySQL] Inserted with key: " + resultInt);
        }

        statement.close();

        LogSystem.get().debug("[MySQL] Statement closed.");

        return resultInt;
    }

    public int execute(Connection con, String query) throws SQLException {
        LogSystem.get().debug("[MySQL] Executing: " + query);

        PreparedStatement statement = createStatement(con, query);

        statement.executeUpdate();

        ResultSet result = statement.getGeneratedKeys();
        int resultInt = -1;

        if(result.next()) {
            resultInt = result.getInt(1);

            LogSystem.get().debug("[MySQL] Inserted with key: " + resultInt);
        }

        statement.close();

        LogSystem.get().debug("[MySQL] Statement closed.");

        return resultInt;
    }

    public int[] executeBatch(PreparedStatement statement) throws SQLException{
        LogSystem.get().debug("[MySQL] Executing batch...");

        int[] returnValue = statement.executeBatch();

        statement.close();

        LogSystem.get().debug("[MySQL] Statement closed.");

        return returnValue;
    }

    public void commit(Connection con) throws SQLException{
        con.commit();
    }

    public void rollback(Connection con) throws SQLException{
        con.rollback();
    }

    public void closeAllIdleConnections() {
        for(Connection con : connectionPoolQueue) {
            try {
                con.close();
            } catch (SQLException e) {}
        }
    }

}