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 TableList extends LinkedList<TableElement>
{

	private static final long serialVersionUID = 1L;
//	private Connection connection;

	
	public TableList(Connection connection, LogFile logFile, String databaseName, String schemaName, int databaseLinkId)
	{
		Statement statement = null;
		ResultSet resultSet = null;
		TableElement tableProfileElement;
		
		try
		{
			statement = connection.createStatement();
		}
		catch(SQLException sqle)
		{
			logFile.logMessage(sqle.toString());
		}

		try
		{
			resultSet = statement.executeQuery
			(
				"select " +
				    "id as table_profile_id, " +
					"database_name, " +
				    "schema_name, " +
				    "table_name, " +
				    "date_time_statistics_collected, " +
				    "row_count, " +
				    "created_at, " +
				    "updated_at " +
				"from " +
				    "table_profile " +
				"where " +
				    "(lower(database_name), lower(schema_name), lower(table_name), date_time_statistics_collected) in " +
				    "( " +
				        "select " +
				        	"lower(database_name), " +
				            "lower(schema_name), " +
				            "lower(table_name), " +
				            "max(date_time_statistics_collected) " +
				        "from " +
				            "table_profile " +
				        "where " +
				            "lower(database_name) = '" + databaseName + "' and " +
				            "lower(schema_name) = '" + schemaName + "' " +
				        "group by " +
				            "database_name, " +
				            "schema_name, " +
				            "table_name " +
				    ") " +
				"order by " +
				    "lower(database_name), " +
	                "lower(schema_name), " +
	                "lower(table_name)"
			);
		}
		catch(SQLException sqle)
		{
			logFile.logMessage(sqle.toString());
		}
		
		try 
		{
			while(resultSet.next())
			{
				tableProfileElement = new TableElement();
				tableProfileElement.setTableProfileId(resultSet.getInt("table_profile_id"));
				tableProfileElement.setDatabaseName(resultSet.getString("database_name"));
				tableProfileElement.setSchemaName(resultSet.getString("schema_name"));
				tableProfileElement.setTableName(resultSet.getString("table_name"));
 			    tableProfileElement.setDateTimeStatisticsCollected(resultSet.getTimestamp("date_time_statistics_collected"));
				tableProfileElement.setRowCount(resultSet.getInt("row_count"));
				this.add(tableProfileElement);
			}
		} catch (SQLException sqle) 
		{
			logFile.logMessage(sqle.toString());
		}
		
		try 
		{
			statement.close();
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
	}
}