import java.sql.*;
import java.io.*;
import java.util.*;

/**
 *  The ConnectionPool class creates a collection of database connections.  The pool
 *  manages the connections and by keeping track of the connections that are available.
 *
 *  When the pool is constructed, the <code>initialPoolSize</code> parameter determines how many
 *  connections to create.  Afterwards, the <code>getConnection()</code> method will return an available
 *  connection or create one if needed.  However, extra connections will NOT be created if the
 *  pool size exceeds the <code>maxPoolSize</code> parameter.
 *
 *  When a client if finished using a connection, then they should "release" the connection by
 *  calling the <code>releaseConnection()</code> method.  This makes the connection available again to the
 *  connection pool.
 *
 *  <pre>
 *    Usage Example:
 *
 *		int initSize = 5;
 *		int maxSize = 15;
 *
 *     	myConnectionPool = ConnectionPool.getInstance(dbDriver, dbURL, userid, passwd,
 *											 initSize, maxSize);
 *
 *      Connection myConnection = myConnectionPool.getConnection();
 *
 *      // ... use the connection for SQL statements
 *
 *      // ... when you are finished, release the connection back to the pool
 *
 *		myConnectionPool.releaseConnection(myConnection);
 *
 *  </pre>
 *
 *  @author 570 Development Team
 *
 */
public class ConnectionPool
{
	// data members
	/**
	 *  Vector representing the pool of connections
	 */
	protected Vector connectionVector;

	/**
	 *  Name of database driver
	 */
	protected String dbDriver;

	/**
	 *  Database URL
	 */
	protected String dbUrl;

	/**
	 *  The user id
	 */
	protected String userId;

	/**
	 *  The password
	 */
	protected String password;

	/**
	 *  the initial size of the connection pool
	 */
	protected int initialPoolSize;

	/**
	 *  the max size of the connection pool
	 */
	protected int maxPoolSize;

	/**
	 *  instance of the connection pool
	 */
	private static ConnectionPool theInstance;

    /**
	 *  contem a tabela do banco de dados
	*/
    protected String tabela;

	public static ConnectionPool getInstance()
		throws ClassNotFoundException, SQLException
	{

		if (theInstance == null) {


		    System.out.println("VideoDB: " + "Building connection pool");

			theInstance = new ConnectionPool();

  		    System.out.println("VideoDB: " + "Connection pool ready!");
		}

		return theInstance;
	}

	/**
	 *  Constructor for the connection pool.
	 */
	public ConnectionPool()
		throws ClassNotFoundException, SQLException
	{
		
 		// read information from properties file
 		Properties props = loadProps();
		dbDriver = props.getProperty("driverName");
		dbUrl = props.getProperty("dbUrl");
		userId = props.getProperty("userId");
		password = props.getProperty("passWord");
		initialPoolSize = Integer.parseInt(props.getProperty("initialPoolSize"));
		maxPoolSize = Integer.parseInt(props.getProperty("maxPoolSize"));

		connectionVector = new Vector();

		System.out.println("VideoDB: " + "Starting connection pool at: " + new java.util.Date());
		System.out.println("VideoDB: " + "driver = " + dbDriver);
		System.out.println("VideoDB: " + "db url = " + dbUrl);
		System.out.println("VideoDB: " + "user id = " + userId);
		System.out.println("VideoDB: " + "password = " + password);
		System.out.println("VideoDB: " + "initial pool size = " + initialPoolSize);
		System.out.println("VideoDB: " + "max pool size = " + maxPoolSize);
		System.out.println("VideoDB: " + "");

		System.out.println("VideoDB: " + "Loading driver..." + dbDriver);
		try {
		   Class.forName(dbDriver);
        } catch (Exception e) {
           System.out.println("VideoDB: " + e);
        }  

		// fill the pool w/ managed connection objects
		Connection myConnection = null;
		ManagedConnection managedConnection = null;

		for (int i=0; i < initialPoolSize; i++)
		{
			System.out.println("VideoDB: " + "Creating connection # " + i + " to database " + dbUrl);
			myConnection = DriverManager.getConnection(dbUrl, userId, password);

			// now create the managed connection and set available flag to "true"
			managedConnection = new ManagedConnection(myConnection, true);
			connectionVector.addElement(managedConnection);
		}
	}

	/**
	 *  This method gets an available connection from the pool and returns it
	 *  to the caller.  This is accomplished w/ the following steps:
	 *
	 *  1.  Walk thru the connection vector
	 *  1a.    Return the first connection that is available
	 *
	 *  2.  If no connections available and vector smaller than pool size
	 *  2a.    Create new managed connection and add it the vector,
	 *  2b.    Return the newly created connection
	 *
	 *  @return Connection an available connection
	 *
	 *  @exception ConnectionUnavailableException if all connections are being used in the pool
	 *                                            and the pool size is maxed out
	 *
	 *  @exception SQLException an SQL exception occurred during the creation on new Connection
	 */
	public synchronized Connection getConnection()
		throws ConnectionUnavailableException, SQLException
	{
		ManagedConnection managedConnection = null;
		Connection myConnection = null;
		boolean found = false;

		Enumeration enum = connectionVector.elements();

		int counter = 0;
		while (enum.hasMoreElements())
		{
			managedConnection = (ManagedConnection) enum.nextElement();

			if (managedConnection.isAvailable())
			{
				// check it out and mark it as unavailable
				managedConnection.setAvailable(false);
				found = true;

				System.out.println("Checking Open connection: " + counter);

				break;
			}

			counter++;
		}

		// If we didn't find an available connection then
		// we need to create a new one and add it to the pool
		if (!found)
		{
			if ((connectionVector.size() < maxPoolSize))
			{
				myConnection = DriverManager.getConnection(dbUrl, userId, password);
				managedConnection = new ManagedConnection(myConnection, false);
				connectionVector.addElement(managedConnection);

				System.out.println("Creating new one.  Checking out: " + (connectionVector.size() - 1));
			}
			else
			{
				throw new ConnectionUnavailableException();
			}
		}

		return managedConnection.getConnection();
	}


	/**
	 *  This method releases a connection (ie placing it back in the pool) using
	 *  the following steps:
	 *
	 *  1.  walk thru the vector and find a match
	 *  2.  once found then mark the connection as available
	 */
	public synchronized void releaseConnection(Connection theConnection)
	{
		ManagedConnection managedConnection = null;
		Connection targetConnection = null;

		Enumeration enum = connectionVector.elements();

		int counter = 0;
		while (enum.hasMoreElements())
		{
			managedConnection = (ManagedConnection) enum.nextElement();
			targetConnection = managedConnection.getConnection();

			if (theConnection == targetConnection)
			{
				managedConnection.setAvailable(true);
				System.out.println("Checking Close connection: " + counter);
			}

			counter++;
		}
	}


	/**
	 *  Close all open database connections.
	 */
	public void destroy()
	{
		ManagedConnection managedConnection = null;
		Connection targetConnection = null;

		Enumeration enum = connectionVector.elements();

		int counter = 0;
		while (enum.hasMoreElements())
		{
			managedConnection = (ManagedConnection) enum.nextElement();
			targetConnection = managedConnection.getConnection();

			try
			{
				targetConnection.close();
				System.out.println("Closing connection: " + counter);
			}
			catch (SQLException e)
			{
				System.out.println("Problems closing connection: " + counter);
			}

			counter++;
		}
	}

	/**
	 *  Helper method to load the rain.ini properties file
	 *
	 *  @return the loaded properties
	 */
	protected Properties loadProps() {

		Properties defaultProps = new Properties();

		defaultProps.put("driverName", "sun.jdbc.odbc.JdbcOdbcDriver");
		defaultProps.put("dbUrl", "jdbc:odbc:RainForestDSN");
		defaultProps.put("userId", "");
		defaultProps.put("passWord", "");
		defaultProps.put("initialPoolSize", "5");
		defaultProps.put("maxPoolSize", "10");

		Properties theProps = new Properties(defaultProps);

		// display the past first
		java.net.URL theIniPath = getClass().getResource("rainforest.ini");
		System.out.println("VideoDB: " + "Loading ini file: " + theIniPath);

		// now load the ini file
		try {
			theProps.load(getClass().getResourceAsStream("rainforest.ini"));
			System.out.println("VideoDB: " + "Loaded props file successfully");
		}
		catch (Exception exc) {
			System.out.println("VideoDB: " + "Didn't find props file...using defaults");
			theProps.list(System.out);
		}

		return theProps;
	}

	/** 
	 * Executa um update SQL 
	 *
	 * @param  sentenca  Comando de Update a ser executada
	 * @return  Retorna a indicação se foi bem sussedido
	 */
	public int executeUpdate(String sentenca)
					  throws SQLException {
		Connection tempConn = null;
		int x;
		try {
			tempConn = getConnection();
			Statement myStmt = tempConn.createStatement();
			x =  myStmt.executeUpdate(sentenca);
			System.out.println(x);
			myStmt.close();
		} catch (SQLException e) {
			throw new SQLException("Erro no update (Classe Tabela, " +
								   "método executeUpdate) " + sentenca +
									e.getMessage());
		}
		finally {
			if (tempConn != null) {
				releaseConnection(tempConn);
			}
		}
		return x;
	}    
    
	/** 
	 * Remove as linhas que possui o campo
	 * com os respectivo valor
	 *
	 * @param campoChave Campo a ser pesquisado 
	 * @param valor      Valor para remoção 
	 * @return 		 Quantidade de linhas atualizadas 
	 */
	 
	public int deleta (String campoChave, String valor)
				throws SQLException {
		
		try {
			/* Montando o comando SQL*/
			String query = "DELETE FROM " + 
					    	tabela + " WHERE " + 
							campoChave + " = '" + valor + "'";						
			return executeUpdate(query);
		} catch (SQLException e) {
			throw new SQLException("Erro na classe=Tabela, " + 
								"método=deleta(String campoChave, " + 
								"String valor) " + e.getMessage());
		}
	}

	/** 
	 * Remove os registros que possui o campo
	 * com os respectivos valores.
	 *
	 * @param campoChave Campo a ser pesquisado.
	 * @param valores    Valores para remoção.
	 * @return 		 Quantidade de registros removidos.
	 */
	public int deleta (String campoChave, Vector valores)
				throws SQLException {

		StringBuffer query = new StringBuffer();
		try {
			/* Montando o comando SQL*/
			query.append("DELETE FROM " + tabela +
						 "   WHERE ");
			int total = valores.size() - 1;
			/* Laço para inserir os campos no comando SQL*/
    	    for (int i = 0 ; i < total ; i++) {
				query.append(campoChave + " = \"" +
							 valores.get(i).toString() + "\" OR ");
			}
			query.append(campoChave + " = \"" +
						 valores.get(total).toString() + "\"");
		} catch (Exception e) {
			throw new SQLException("Falha na remoção da(s) tupla(s) ");
		}
		return executeUpdate(query.toString());
	}


	/**
	 * Número de registro de uma determinada tabela.
	 * @return Número de registros.
	 */
	public int numeroRegistros () throws SQLException  {
		Connection tempConn = null;
		int total = 0;
		try {
			tempConn = getConnection();
			Statement myStmt = tempConn.createStatement();
			String Query = "select COUNT(*) from " + tabela;
		    ResultSet myRs =  myStmt.executeQuery(Query);
			/* Se a tabela estiver vazia total continua com (0) */
		    if (myRs.next()){
		       total = myRs.getInt(1);
		    }
			myRs.close();
			myStmt.close();
		} catch(SQLException e) {
		    throw new SQLException("Erro no metodo numeroRegistros "  + e.getMessage());
	    }
		finally {
			if (tempConn != null) {
				releaseConnection(tempConn);
			}
		}
		return total;
	} 

}