package tableDefinition;

import java.util.ListIterator;

import dataWarehousingTools.DashboardFile;

public class GenerateCreateScript
{
	public GenerateCreateScript(String tableName, ColumnList columnList, String outputDirectory)
	{
		ListIterator<ColumnElement> iterator;
		ColumnElement columnElement;
		String postgresDataType;
		int maxLengthColumnName;
		int maxLengthDataTypeName;
		int expectedColumnNumber;
		String columnName;
		int dataLength;
		boolean nullable;
		int scale;
		DashboardFile createScriptFile;
		StandardiseName standardiseName;
		String standardTableName;
		String standardColumnName;
		StringBuffer previousColumn;          // to get commas in the right place
		boolean previousColumnIsNakedNumber;  // true if previous column needs naked-number comment
		
		standardiseName = new StandardiseName();
				
//		System.out.println("Tests:");
//		System.out.println("Abc123_X: " + standardiseName.get("Abc123_X"));
//		System.out.println("source_code: " + standardiseName.get("source_code"));
//		System.out.println("junk/&rubbish!: " + standardiseName.get("junk/&rubbish"));
//		System.out.println(" A?b@c'1-2:3_X: " + standardiseName.get(" A?b@c'1-2:3_X"));
//		System.out.println("\"Ab;§`/_Y\"': " + standardiseName.get("\"Ab;§`/_Y\"'"));
//		System.out.println("  Ab c12 3_X   : " + standardiseName.get("  Ab c12 3_X   "));
			
        standardTableName = standardiseName.get(tableName);
		createScriptFile = new DashboardFile(outputDirectory + "create_" + standardTableName + ".sql");
		
		createScriptFile.PutLine("create table " + standardTableName);
		createScriptFile.PutLine("(");
		
		previousColumn = new StringBuffer("");
		previousColumnIsNakedNumber = false;
		
		maxLengthColumnName = 6;        // Start with minimum length as length of title "Column"
		maxLengthDataTypeName = 9;      // Start with minimum length as length of title "Data Type"

		for (iterator = columnList.listIterator(); iterator.hasNext();)
		{
			columnElement = (ColumnElement) iterator.next();
			
			if (columnElement.getColumnName().length() > maxLengthColumnName)
				maxLengthColumnName = columnElement.getColumnName().length();
			if (columnElement.getDataType().length() > maxLengthDataTypeName)
				maxLengthDataTypeName = columnElement.getDataType().length();
		}
		
		previousColumn = new StringBuffer("");
		expectedColumnNumber = 0; 
	
		for (iterator = columnList.listIterator(); iterator.hasNext();)
		{
			columnElement = (ColumnElement) iterator.next();
			
			expectedColumnNumber++;
			if (columnElement.getColumnNumber() != expectedColumnNumber)
				System.out.println("Column number " + columnElement.getColumnNumber() + " expected " + expectedColumnNumber);

			columnName = new String(columnElement.getColumnName());
			dataLength = columnElement.getDataLength();
			scale = columnElement.getScale();
			nullable = columnElement.getNullable();
			postgresDataType = new String(columnElement.getPostgresDataType());

			if (previousColumn.length() > 0)
			{
				if (previousColumnIsNakedNumber)
				{
					createScriptFile.PutLine(previousColumn.toString() + ",     -- Please supply precision and scale.");
					previousColumnIsNakedNumber = false;
				}
				else
					createScriptFile.PutLine(previousColumn.toString() + ",");
			}

			standardColumnName = standardiseName.get(columnName);
			previousColumn = new StringBuffer("    " + pad(standardColumnName, maxLengthColumnName));
			previousColumn.append("  " + postgresDataType);
			
			
			if (postgresDataType.equals("varchar"))
			{
	        	previousColumn.append("(" + dataLength + ")");
			}
			else if (postgresDataType.equals("numeric"))
			{
	        	if (dataLength > 0) 
	        	{
	      			previousColumn.append("(" + dataLength + "," + scale + ")");
	        	}
	        	else
	        	{
	        		if (scale < 0)
	        			previousColumnIsNakedNumber = true;
	        	}
			}
        	if (!nullable)
        	{
        		previousColumn.append("    not null");
        	}
		}
        createScriptFile.PutLine(previousColumn.toString());
        createScriptFile.PutLine(");");
        createScriptFile.close();
	}	

	private String pad(String s, int length)
	{
		StringBuffer padding;
		int i;
		
		padding = new StringBuffer();
		for (i = 0; i < length - s.length(); i++)
			padding.append(" ");
		
		return s + padding.toString();
	}
}