package dataProfileWebSite;

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

import dataWarehousingTools.LogFile;

public class DatabaseList extends LinkedList<DatabaseElement>
{
	private static final long serialVersionUID = 1L;

	public DatabaseList(Connection connection, LogFile logFile)
	{
		Statement statement = null;
		ResultSet resultSet = null;
		DatabaseElement databaseElement;
		
		try
		{
			statement = connection.createStatement();
		}
		catch(SQLException sqle)
		{
			logFile.logMessage(sqle.toString());
		}

		try
		{
			resultSet = statement.executeQuery
			(
				"select " +
					"lower(database_name)                as database_name, " +
					"lower(schema_name)                  as schema_name, " +
				    "max(id)                             as database_link_id, " +		// risky - should use timestamp
				    "count(distinct table_name)          as table_count, " +
				    "max(date_time_statistics_collected) as latest_profile " +
				"from " +
				    "table_profile " +
				"group by " +
				    "lower(database_name), " +
				    "lower(schema_name) " +
				"order by " +
					"lower(database_name), " +
	                "lower(schema_name)"
			);

		}
		catch(SQLException sqle)
		{
			logFile.logMessage(sqle.toString());
		}

		
		try 
		{
			while(resultSet.next())
			{
				databaseElement = new DatabaseElement();
				databaseElement.setDatabaseName(resultSet.getString("database_name"));
				databaseElement.setSchemaName(resultSet.getString("schema_name"));
				databaseElement.setDatabaseLinkId(resultSet.getInt("database_link_id"));
				databaseElement.setTableCount(resultSet.getInt("table_count"));
				databaseElement.setLatestProfile(resultSet.getTimestamp("latest_profile"));
				this.add(databaseElement);
				
				logFile.logMessage(resultSet.getString("database_name") + " | " + resultSet.getString("schema_name") + " | " + resultSet.getInt("database_link_id"));
			}
		} catch (SQLException sqle) 
		{
			logFile.logMessage(sqle.toString());
		}
		
		try 
		{
			statement.close();
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
	}
}