package dataWarehousingTools;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.ListIterator;

public class ConnectionList extends LinkedList<ConnectionElement>
{
	private static final long serialVersionUID = 1L;
	private Connection metadataConnection;
	private LogFile logFile;

	public ConnectionList(Connection metadataConnection, LogFile logFile) 
	{
		this.metadataConnection = metadataConnection;
		this.logFile = logFile;
	}
	
	private Connection LoadAndConnect
	(
			String driverClass, 
			int connectionId, 
			String connectionName, 
			String connectionString, 
			String userName, 
			String password
	)
	{
		Connection connection = null;
		
		try
		{
//			System.out.println("Trying to connect with driver class: " + driverClass);			
			Class.forName (driverClass); 
		}
		catch (ClassNotFoundException cnfe)
		{
			logFile.logMessage
			(
				"Failed to load driver for " + 
				connectionId + 
				", " +
				connectionName +
				".  " + 
				cnfe.toString()
			);
		}
		
		try	
		{
			if (driverClass.contains("microsoft.sqlserver"))
			{
//				System.out.println("Microsoft SQL Server connection");
				connection = DriverManager.getConnection
				(
					connectionString
				);
			}
			else
			{
//				System.out.println("Connection to a database other than Microsoft SQL Server");
				connection = DriverManager.getConnection
				(
					connectionString,
					userName,
					password
				);
			}
		}
		catch (SQLException sqle)
		{
			logFile.logMessage
			(
				"Failed to connect to " + 
				connectionId + 
				", " +
				connectionName +
				", " +
				connectionString +
				".  " + 
				sqle.toString()
			);
		}
		
		return connection;
	}
	
	public Connection getConnection(int connectionId)
	{
		Connection connection = null;
		ListIterator<ConnectionElement> connectionIterator;
		ConnectionElement connectionElement;

		Statement statement = null;
		ResultSet resultSet = null;

	    String connectionName = null;
	    String driverClass = null;
	    String connectionString = null;
	    String userName = null;
	    String password = null;

		
		for (connectionIterator = this.listIterator(); connectionIterator.hasNext(); )
		{
			connectionElement = (ConnectionElement) connectionIterator.next();
			if (connectionElement.getConnectionID() == connectionId)
				return connectionElement.getConnection();
		}

		try
		{
			statement = metadataConnection.createStatement();
		}
		catch(SQLException sqle)
		{
			logFile.logMessage("Failed to create statement on metadata connection");
		}

		try
		{
			resultSet = statement.executeQuery
		    (
		    	"select " +
			        "connection_name, " +
		        	"driver_class, " +
		        	"connection_string, " +
		        	"user_name, " +
		        	"password " +
		    	"from " +
		    	    "connection " +
		    	"where " +
		    	    "id = " + connectionId
            );
		}
		catch(SQLException sqle)
		{
			logFile.logMessage
			(
				"Failed to select connection " + 
				connectionId + 
				" from metadata table connection.  " + 
				sqle.toString()
			);
		}
		
		try
		{
			resultSet.next();
		    connectionName = new String(resultSet.getString("connection_name"));
		    driverClass = new String(resultSet.getString("driver_class"));
		    connectionString = new String(resultSet.getString("connection_string"));
		    userName = new String(resultSet.getString("user_name"));
		    password = new String(resultSet.getString("password"));
		} 
		catch (SQLException sqle)
		{
			logFile.logMessage
			(
				"Failed to find connection " + 
				connectionId + 
				" in metadata table connection.  " + 
				sqle.toString()
			);
		}

		try
		{
			statement.close();
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		
		connection = LoadAndConnect
		(
			driverClass, 
			connectionId, 
			connectionName, 
			connectionString, 
			userName, 
			password
		);
		
		connectionElement = new ConnectionElement();
		connectionElement.setConnectionID(connectionId);
		connectionElement.setConnectionName(connectionName);
		connectionElement.setConnection(connection);
		
		this.add(connectionElement);
		
		return connection;
	}
	
	public Connection getConnection(String connectionName)
	{
		Connection connection = null;
		ListIterator<ConnectionElement> connectionIterator;
		ConnectionElement connectionElement;

		Statement statement = null;
		ResultSet resultSet = null;


	    int connectionId = -1;
	    String driverClass = null;
	    String connectionString = null;
	    String userName = null;
	    String password = null;

		
		for (connectionIterator = this.listIterator(); connectionIterator.hasNext(); )
		{
			connectionElement = (ConnectionElement) connectionIterator.next();
			if (connectionElement.getConnectionName().equalsIgnoreCase(connectionName))
				return connectionElement.getConnection();
		}

		try
		{
			statement = metadataConnection.createStatement();
		}
		catch(SQLException sqle)
		{
			logFile.logMessage("Failed to create statement on metadata connection");
		}

		try
		{
			resultSet = statement.executeQuery
		    (
		    	"select " +
			        "id, " +
		        	"driver_class, " +
		        	"connection_string, " +
		        	"user_name, " +
		        	"password " +
		    	"from " +
		    	    "connection " +
		    	"where " +
		    	    "upper(connection_name) = upper('" + connectionName + "')"
            );
		}
		catch(SQLException sqle)
		{
			logFile.logMessage
			(
				"Failed to select connection " + 
				connectionName + 
				" from metadata table connection.  " + 
				sqle.toString()
			);
		}
		
		try
		{
			resultSet.next();
		    connectionId = resultSet.getInt("id");
		    driverClass = new String(resultSet.getString("driver_class"));
		    connectionString = new String(resultSet.getString("connection_string"));
		    userName = new String(resultSet.getString("user_name"));
		    password = new String(resultSet.getString("password"));
		} 
		catch (SQLException sqle)
		{
			logFile.logMessage
			(
				"Failed to find connection " + 
				connectionName + 
				" in metadata table connection.  " + 
				sqle.toString()
			);
		}

		try
		{
			statement.close();
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
				
		connection = LoadAndConnect
		(
			driverClass, 
			connectionId, 
			connectionName, 
			connectionString, 
			userName, 
			password
		);
		
		connectionElement = new ConnectionElement();
		connectionElement.setConnectionID(connectionId);
		connectionElement.setConnectionName(connectionName);
		connectionElement.setConnection(connection);
		
		this.add(connectionElement);
		
		return connection;
	}
	
	public int getConnectionId(String connectionName)
	{
		ListIterator<ConnectionElement> connectionIterator;
		ConnectionElement connectionElement;

		for (connectionIterator = this.listIterator(); connectionIterator.hasNext(); )
		{
			connectionElement = (ConnectionElement) connectionIterator.next();
			if (connectionElement.getConnectionName().equalsIgnoreCase(connectionName))
				return connectionElement.getConnectionID();
		}
		return -1;
	}
}