package profileEngine;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;

public class Metadata
{
	private Connection metadataConnection;
	private int tableProfileId;
	private DateFormat dateFormat;
	private int columnProfileId;
	private String databaseProduct;


	public Metadata(Connection metadataConnection, String databaseProduct)
	{
		this.metadataConnection = metadataConnection;
		this.databaseProduct = databaseProduct;
    	dateFormat = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");

	}
	
	public int insertTableProfile
	(
		String databaseName,
		String schemaName,
		String tableName, 
		Calendar dateStatisticsCollected
	)
	{
		Statement statement = null;
		ResultSet resultSet = null;
		int id = -1;
		int rows_affected;
		
		try
		{
			statement = metadataConnection.createStatement();
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		
		try
		{
			rows_affected = statement.executeUpdate
			(
				"insert into table_profile " +
				"(" +
					"database_name, " +
				    "schema_name, " +                                             
				    "table_name, " +
				    "date_time_statistics_collected" +      
				")" +
				"values " +
				"(" +
				    "'" + databaseName + "', " +
				    "'" + schemaName + "', " +
				    "'" + tableName + "', " +
				    "'" + dateFormat.format(dateStatisticsCollected.getTime()) + "'" +
				")",
				Statement.RETURN_GENERATED_KEYS
			);
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}

			try 
			{
				resultSet = statement.getGeneratedKeys();
			} 
			catch (SQLException e1) 
			{
				e1.printStackTrace();
			}

		try 
		{
			if (resultSet.next())
				id = resultSet.getInt(1);
		} 
		catch (SQLException e1) 
		{
			e1.printStackTrace();
		}
		
//		System.out.println("Id of inserted table_profile row: " + id);

		try
		{
			statement.close();
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		tableProfileId = id;
		return id;
	}
	
	public int insertColumnProfile
	(
		    Calendar dateStatisticsCollected,
		    String columnName,
		    String dataType,
		    int dataLength,
		    String nullable
	)
	{
		int rows_affected = -1;
		int id = -1;
		StringBuffer sqlStatement;
		Statement statement = null;
		ResultSet resultSet = null;
		
		try
		{
			statement = metadataConnection.createStatement();
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		
		sqlStatement = new StringBuffer
		(
			"insert into column_profile " +
			"(" +
			    "table_profile_id, " +                                              
			    "date_time_statistics_collected, " +         
			    "column_name, " + 
			    "data_type, " +
			    "data_length, " +       
			    "nullable" +  
			")" +
			"values " +
			"(" +
			    tableProfileId + ", " +
			    "'" + dateFormat.format(dateStatisticsCollected.getTime()) + "', " +
			    "'" + columnName + "', " +
			    "'" + dataType + "', " +
			    dataLength + ", "
		);
		
		if (databaseProduct.equals("Microsoft SQL Server"))
		{
			if (nullable.equals("N"))
			{
				sqlStatement.append(" 0)");
			}
			else
			{
				sqlStatement.append(" 1)");
			}
		}
		else
		{
			sqlStatement.append("'" + nullable + "') ");
		}
		
		try
		{
			rows_affected = statement.executeUpdate(sqlStatement.toString(), Statement.RETURN_GENERATED_KEYS);
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		
		try 
		{
			resultSet = statement.getGeneratedKeys();
		} 
		catch (SQLException e1) 
		{
			e1.printStackTrace();
		}
		
		try 
		{
			if (resultSet.next())
				id = resultSet.getInt(1);
		} 
		catch (SQLException e1) 
		{
			e1.printStackTrace();
		}

//		System.out.println("Id of inserted column_profile row: " + id);
				
		try
		{
			statement.close();
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		
		columnProfileId = id;
		return id;
	}
	
	public void updateTableProfile(long rowCount)
	{
		Statement statement = null;
		int rowsUpdated;
		
		try
		{
			statement = metadataConnection.createStatement();
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		
		try
		{
			rowsUpdated = statement.executeUpdate
			(
				"update table_profile " +
				"set " +
				    "row_count = " + rowCount + " " +
				"where " +
				    "id = " + tableProfileId
			);
//			System.out.println("Rows updated: " + rowsUpdated);
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		
		try
		{
			statement.close();
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		
// Oracle		try
// Oracle		{
// Oracle			metadataConnection.commit();
// Oracle		} 
// Oracle		catch (SQLException e)
// Oracle		{
// Oracle			e.printStackTrace();
// Oracle		}
	}

	
	public void updateColumnProfile
	(
		int columnProfileId,
		String profileDataType,
	    long uniqueValueCount,
	    double minNumber,
	    String minDate,
	    String minString,
	    double maxNumber,
	    String maxDate,
	    String maxString,
	    int maxLength
	)
	{
		Statement statement = null;
		String sqlStatement = null;
		String minDateString;
		String maxDateString;
	    String limitedMinString;
	    String limitedMaxString;
	    String cleanedMinString;
	    String cleanedMaxString;

		
		try
		{
			statement = metadataConnection.createStatement();
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}

		if (profileDataType.equals("Number"))
		{
			sqlStatement = 
			    "update column_profile " +
			    "set " +
			        "unique_value_count = " + uniqueValueCount + ", " +
			        "min_number = " + minNumber + ", " +
			        "max_number = " + maxNumber + " " +
			    "where " +
			        "id = " + columnProfileId;
		}
		else if (profileDataType.equals("Date"))
		{
			if (minDate.equals("<null>"))
				minDateString = "null";
			else
				minDateString = "'" + minDate + "'";
// Oracle				minDateString = "to_date('" + minDate + "', 'DD-Mon-YYYY HH24:MI:SS')";
			
			if (maxDate.equals("<null>"))
					maxDateString = "null";
				else
					maxDateString = "'" + maxDate + "'";
// Oracle					maxDateString = "to_date('" + maxDate + "', 'DD-Mon-YYYY HH24:MI:SS')";
			
			sqlStatement = 
			    "update column_profile " +
			    "set " +
			        "unique_value_count = " + uniqueValueCount + ", " +
			        "min_date = " + minDateString + ", " +
			        "max_date = " + maxDateString + " " +
			    "where " +
			        "id = " + columnProfileId;
		}
		else if  (profileDataType.equals("String"))
		{
			if (minString.length() > 255)
				limitedMinString = minString.substring(0, 255);
			else
				limitedMinString = minString;
			cleanedMinString = new String(limitedMinString.replace("'", "''"));

			if (maxString.length() > 255)
				limitedMaxString = maxString.substring(0, 255);
			else
				limitedMaxString = maxString;
			cleanedMaxString = new String(limitedMaxString.replace("'", "''"));

			sqlStatement = 
			    "update column_profile " +
			    "set " +
			        "unique_value_count = " + uniqueValueCount + ", " +
			        "min_string = " + "'" + cleanedMinString + "', " +
			        "max_string = " + "'" + cleanedMaxString + "', " +
			        "max_length = " + maxLength + " " +
			    "where " +
			        "id = " + columnProfileId;
		}
		else if  (profileDataType.equals("Time"))
		{
			sqlStatement = 
			    "update column_profile " +
			    "set " +
			        "unique_value_count = " + uniqueValueCount + ", " +
			        "min_string = " + "'" + minString + "', " +
			        "max_string = " + "'" + maxString + "' " +
			    "where " +
			        "id = " + columnProfileId;
		}
		else if  (profileDataType.equals("Boolean"))
		{
			sqlStatement = 
			    "update column_profile " +
			    "set " +
			        "unique_value_count = " + uniqueValueCount + " " +
			    "where " +
			        "id = " + columnProfileId;
		}

//		System.out.println(sqlStatement);
		
		try
		{
			statement.executeUpdate(sqlStatement);
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		
		try
		{
			statement.close();
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		
// Oracle		try
// Oracle		{
// Oracle			metadataConnection.commit();
// Oracle		} 
// Oracle		catch (SQLException e)
// Oracle		{
// Oracle			e.printStackTrace();
// Oracle		}
	}

	
	public void updateColumnProfile
	(
		int columnProfileId,
	    long nullCount	
	)
	{
		Statement statement = null;
		
		try
		{
			statement = metadataConnection.createStatement();
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		
		try
		{
			statement.executeUpdate
			(
				    "update column_profile " +
				    "set " +
				        "null_count = " + nullCount + " " +
				    "where " +
				        "id = " + columnProfileId
			);
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		
		try
		{
			statement.close();
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		
// Oracle		try
// Oracle		{
// Oracle			metadataConnection.commit();
// Oracle		} 
// Oracle		catch (SQLException e)
// Oracle		{
// Oracle			e.printStackTrace();
// Oracle		}
	}
	
	
	public void insertColumnValueFrequency
	(
		int columnProfileId,
		String profileDataType,
		String displayValue,
		long frequency
	)
	{
		Statement statement = null;
		String sqlStatement = null;
		String quotedString;
		int limit;
		
		try
		{
			statement = metadataConnection.createStatement();
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}

		limit = displayValue.length();
		if (limit > 256)
			limit = 256;
		quotedString = new String
		(
				"'" +
				displayValue.substring(0, limit).replace("'", "''") +
				"'"
		);
		
		sqlStatement = 
		    "insert into frequency " +
		    "(" +
		        "column_profile_id, " +
		        "display_value, " +
		        "frequency " +
		    ")" +
		    "values " +
		    "(" +
		        columnProfileId + ", " +
		        quotedString + ", " +
		        frequency +
		    ")";
		
		try
		{
			statement.executeUpdate(sqlStatement);
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		
		try
		{
			statement.close();
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		
// Oracle		try
// Oracle		{
// Oracle			metadataConnection.commit();
// Oracle		} 
// Oracle		catch (SQLException e)
// Oracle		{
// Oracle			e.printStackTrace();
// Oracle		}
	}
	

	public void insertPattern
	(
			int columnProfileId, 
			String pattern, 
			long frequency
	) 
	{
		Statement statement = null;
		String sqlStatement = null;
		
		try
		{
			statement = metadataConnection.createStatement();
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}

		sqlStatement = 
		    "insert into pattern " +
		    "(" +
		        "column_profile_id, " +
		        "pattern, " +
		        "frequency " +
		    ")" +
		    "values " +
		    "(" +
		        columnProfileId + ", " +
		        "'" + pattern + "', " +
		        frequency +
		    ")";
		
		try
		{
			statement.executeUpdate(sqlStatement);
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		
		try
		{
			statement.close();
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		
// Oracle		try
// Oracle		{
// Oracle			metadataConnection.commit();
// Oracle		} 
// Oracle		catch (SQLException e)
// Oracle		{
// Oracle			e.printStackTrace();
// Oracle		}		
	}
}