/*
 * DataBaseConnection.java
 *
 * Created on 2 de agosto de 2004, 10:21
 */

package com.fenalco.utilities.database;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.Properties;
import java.util.Vector;

/**
 * 
 * @author Andrés Romero Rodríguez
 * 
 * esta clase es la que se conecta a la base de datos, implementa un pool de
 * conexiones a la base de datos basandose en los parámetros pasados en el
 * archivo database.xml
 */
public class DataBaseConnection
{
	/**
	 * Un string que representa el nombre de la base de datos a la cual nos
	 * vamos a conectar
	 */
	private String m_sDB;

	/**
	 * IP de la base de datos
	 */
	private String m_sIP;

	/**
	 * Puerto al que nos vamos a conectar para acceder a la base de datos
	 */
	private int m_nPort;

	/**
	 * Usuario de la base de datos
	 */
	private String m_sUser;

	/**
	 * Contraseña para acceder a la base de datos
	 */
	private String m_sPassword;

	/**
	 * El nombre de la base de datos a la que nos vamos a conectar
	 */
	private String m_sDataBaseName;

	/**
	 * Número máximo de conexiones simultaneas que se tendrán a la base de datos
	 */
	private int m_nMaxConnections;

	/**
	 * El número de segundos para el hilo que va a limpiar las conexiones
	 */
	private int m_nTimeOut;

	/**
	 * Este es el pool de conexiones, un vector que almacena las conexiones que
	 * están disponibles
	 */
	private Vector freeConnectios;

	/**
	 * Entero que indica cuantas conexiones se están usando
	 */
	private int m_nCheckedOut;

	/**
	 * La unica referencia a esta clase que va a existir
	 */
	private static DataBaseConnection m_this;

	/**
	 * va a administrar el pool para que no crezca tanto
	 */
	private PoolManager poolManager;

	/**
	 * como esta clase es un singleton, el constructor es privado para que solo
	 * exista un objeto que realiza conexiones a la base de datos
	 */
	private DataBaseConnection( ) throws DataBaseConnectionException
	{
		try
		{
			Properties props = new Properties( );
			InputStream entrada = getClass( ).getResourceAsStream( "../config/config.properties" );
			if( entrada == null )
			{
				throw new DataBaseConnectionException( "File nof found: dataBase.properties" );
			}

			props.load( entrada );
			entrada.close( );

			m_sDB = props.getProperty( "database-engine" );
			m_sDB = m_sDB.toUpperCase( );
			m_sIP = props.getProperty( "database-ip" );
			m_nPort = Integer.parseInt( props.getProperty( "database-port" ) );
			m_sUser = props.getProperty( "database-user" );
			m_sPassword = props.getProperty( "database-password" );
			m_sDataBaseName = props.getProperty( "database-name" );
			m_nMaxConnections = Integer.parseInt( props.getProperty( "database-max-connections" ) );
			m_nTimeOut = Integer.parseInt( props.getProperty( "database-timeout" ) );

			freeConnectios = new Vector( );
			m_nCheckedOut = 0;
			poolManager = new PoolManager( this );
			poolManager.start( );
		}
		catch( Exception e )
		{
			System.out.println( "Error creating DataBaseAccess... --> " + e );
			throw new DataBaseConnectionException( "Error creating DataBaseConnection... --> " + e );
		}
	}

	/**
	 * esta función se utiliza para obtener una instancia de esta clase
	 */
	public static DataBaseConnection getInstance( ) throws DataBaseConnectionException
	{
		if( m_this == null )
		{
			m_this = new DataBaseConnection( );
		}

		return m_this;
	}

	/**
	 * este método se usa para devolver una conexión al pool cuando se ha
	 * terminado de utilizar
	 */
	public synchronized void freeConnection( Connection conn )
	{
		if( conn != null )
		{
			if( !freeConnectios.contains( conn ) )
			{
				try
				{
					if( !conn.isClosed() )
					{
						freeConnectios.addElement( conn );
						m_nCheckedOut--;
						notifyAll( );
					}
				}
				catch( SQLException e )
				{
					e.printStackTrace();
				}
			}
		}
		while( freeConnectios.size() + m_nCheckedOut > m_nMaxConnections )
		{
			Connection c = ( Connection )freeConnectios.remove( 0 );
			try
			{
				c.close( );
			}
			catch( SQLException e )
			{
				e.printStackTrace();
			}
		}
	}

	/**
	 * Este método retorna una conexión del pool, si todas las conexiones del
	 * pool ya están en uso, espera hasta que haya una disponible
	 */
	public synchronized Connection getConnection( ) throws EngineNotSupportedException, DataBaseConnectionException
	{
		Connection conn = null;

		if( freeConnectios.size( ) > 0 )
		{
			conn = ( Connection )freeConnectios.remove( 0 );

			try
			{
				if( conn.isClosed( ) )
				{
					//                    System.out.println( "removed bad connection from pool" );
					conn = getConnection( );
				}
			}
			catch( SQLException e )
			{
				System.out.println( "removed bad connection from pool" );
				conn = getConnection( );
			}
		}
		else if( m_nMaxConnections == 0 || m_nCheckedOut < m_nMaxConnections )
		{
			conn = newConnection( );
		}
		if( conn != null )
		{
			m_nCheckedOut++;
		}
		else
		{
			try
			{
				wait( );
			}
			catch( InterruptedException e )
			{

			}
			conn = getConnection( );
		}
		return conn;
	}

	/**
	 * Este método se usa para limpiar el pool, es decir, se cierran todas las
	 * conexiones y se eliminan del vector
	 */
	public synchronized void release( )
	{
		Enumeration allConnections = freeConnectios.elements( );
		while( allConnections.hasMoreElements( ) )
		{
			Connection conn = ( Connection )allConnections.nextElement( );
			try
			{
				conn.close( );
			}
			catch( SQLException e )
			{
				System.out.println( "Can't close connection" );
			}
		}
		freeConnectios.removeAllElements( );
	}

	/**
	 * este método se usa para crear una nueva conexión con la base de datos
	 */
	private Connection newConnection( ) throws EngineNotSupportedException, DataBaseConnectionException
	{
		Connection conn = null;
		if( m_sDB.equals( "POSTGRES" ) )
		{
			conn = newPostgresConnection( );
		}
		else if( m_sDB.equals( "ORACLE" ) )
		{
			conn = newOracleConnection( );
		}
		else if( m_sDB.equals( "SQL-SERVER" ) )
		{
			conn = newSQLServerConnection( );
		}
		else
		{
			throw new EngineNotSupportedException( m_sDB + "is not supported" );
		}
		return conn;
	}

	/**
	 * @return
	 */
	private Connection newSQLServerConnection( ) throws DataBaseConnectionException
	{
		Connection conn = null;
		try
		{
			Class.forName( "com.microsoft.jdbc.sqlserver.SQLServerDriver" );
			String url = "jdbc:microsoft:sqlserver://" + m_sIP + ":" + m_nPort + ";DatabaseName=" + m_sDataBaseName;
			Driver driver = DriverManager.getDriver( url );
			conn = DriverManager.getConnection( url, m_sUser, m_sPassword );
		}
		catch( Exception e )
		{
			e.printStackTrace();
			throw new DataBaseConnectionException( "Error connecting to " + m_sDB + " --> " + e );			
		}
		return conn;
	}

	private Connection newPostgresConnection( ) throws DataBaseConnectionException
	{
		Connection conn = null;

		try
		{
			Class.forName( "org.postgresql.Driver" );
			String url = "jdbc:postgresql://" + m_sIP + ":" + m_nPort + "/" + m_sDataBaseName;
			Driver driver = DriverManager.getDriver( url );
			conn = DriverManager.getConnection( url, m_sUser, m_sPassword );
		}
		catch( Exception e )
		{
			throw new DataBaseConnectionException( "Error connecting to " + m_sDB + " --> " + e );
		}
		return conn;
	}

	private Connection newOracleConnection( ) throws DataBaseConnectionException
	{
		Connection conn = null;

		try
		{
			Class.forName( "oracle.jdbc.OracleDriver" );
			String url = "jdbc:oracle:thin:@" + m_sIP + ":" + m_nPort + ":" + m_sDataBaseName;
			Driver driver = DriverManager.getDriver( url );
			conn = DriverManager.getConnection( url, m_sUser, m_sPassword );

			Statement st = conn.createStatement( );
			st.execute( "ALTER SESSION SET NLS_DATE_FORMAT = \"YYYY-MM-DD\"" );
			//            System.out.println( "ALTER SESSION SET NLS_DATE_FORMAT =
			// \'YYYY-MM-DD\'" );

		}
		catch( Exception e )
		{
			throw new DataBaseConnectionException( "Error connecting to " + m_sDB + " --> " + e );
		}
		return conn;
	}

	public Vector getFreeConnections( )
	{
		return freeConnectios;
	}

	/**
	 * Retorna el timeout para limpiar el pool
	 */
	public int getTimeOut( )
	{
		return m_nTimeOut;
	}

	public static void main( String[ ] args )
	{
		try
		{
			/*
			 * Vector r = SQLUtilities.query( "SELECT * FROM usuario" ); for(
			 * int i = 0; i < r.size(); i++ ) { Hashtable h = ( Hashtable
			 * )r.elementAt( i ); Enumeration en = h.keys(); while(
			 * en.hasMoreElements() ) { Object k = en.nextElement();
			 * System.out.println( k + " = " + h.get( k ) ); } }
			 */
			DataBaseConnection dbc = DataBaseConnection.getInstance( );
			Connection c = dbc.getConnection( );
			System.out.println( "Connection ok --> " + c );
			Statement st = c.createStatement( );
			System.out.println( "Statement ok --> " + st );
			ResultSet rs = st.executeQuery( "SELECT * FROM pais;" );
			while( rs.next() )
			{
				System.out.println( "pais = " + rs.getString( "pais_nombre" ) );
			}
			System.out.println( "Todo bien" );
			System.exit( 0 );
		}
		catch( Exception e )
		{
			e.printStackTrace( );
		}
	}

	public String getDataBase( )
	{
		return m_sDB;
	}
}