package elife.gadgets.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Hashtable;
import java.util.Map;

public class ConnectionManager{
    private ConnectionPool connectionPool;
    private Map<Long, ConnectionPool> connectionPools = new Hashtable<Long, ConnectionPool>();
    private static ConnectionManager instance = null;

    public static ConnectionManager getInstance() throws SQLException{
        if(instance == null){
            synchronized(ConnectionManager.class){
                instance = new ConnectionManager();
            }
        }
        return instance;
    }
    private ConnectionManager() throws SQLException{

            String host = "localhost";
            String database = "elife_sac_map";
            String username = "root";
            String password = "";

            this.connectionPool = new ConnectionPool(host, database, username, password);

    }

    public synchronized ConnectionPool getConnectionPool(String username, String password) throws SQLException{
        long client = 0;
        Connection connection = this.connectionPool.getConnection();
        try{
            String sql = "SELECT client_id FROM user WHERE username = ? AND password = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, password);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                client = resultSet.getLong("client_id");
            }
            resultSet.close();
            preparedStatement.close();
        }
        catch(SQLException ex){
            connection.rollback();
            throw ex;
        }
        finally{
            this.connectionPool.free(connection);
        }
        return this.getConnectionPool(client);
    }
    public synchronized ConnectionPool getConnectionPool(long client) throws SQLException{
        ConnectionPool connectionPool = null;
        if(client > 0){
            connectionPool = this.connectionPools.get(client);
            if(connectionPool == null){
                connectionPool = this.newConnectionPool(client);
                if(connectionPool != null){
                    this.connectionPools.put(client, connectionPool);
                }
            }
        }
        /*if(connectionPool == null){
            throw new SQLException("Client database not found!\nclient = "+client);
        }*/
        return connectionPool;
    }
    private ConnectionPool newConnectionPool(long client) throws SQLException{
        ConnectionPool connectionPool = null;
        Connection connection = this.connectionPool.getConnection();
        try{
            String sql = "SELECT * FROM client WHERE id = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1, client);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                String host = resultSet.getString("host");
                String database = resultSet.getString("database");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                connectionPool = new ConnectionPool(host, database, username, password);
            }
            resultSet.close();
            preparedStatement.close();
        }
        catch(SQLException ex){
            connection.rollback();
            throw ex;
        }
        finally{
            this.connectionPool.free(connection);
        }
        return connectionPool;
    }
}