package tableDefinition;

import java.util.ListIterator;

import dataWarehousingTools.DashboardFile;
import dataWarehousingTools.LogFile;


public class GenerateUnloadDataScript
{
	public GenerateUnloadDataScript
	(
		String databaseProduct,
		String schemaName, 
		String tableName, 
		ColumnList columnList, 
		String outputDirectory, 
		LogFile logFile
	)
	{
		ListIterator<ColumnElement> iterator;
		ColumnElement columnElement;
		DashboardFile unloadDataFile;
		String originalColumnName;
		String columnName;
		int maxLengthColumnName;
		String dataType;
		StringBuffer line;
		String byteValue;         // the function used to convert a decimal number to an ASCII/UTF-8 charatcer

		System.out.println("Database Product = " + databaseProduct);
		
		unloadDataFile = new DashboardFile(outputDirectory + "unload_" + tableName + ".sql");
		
		unloadDataFile.PutLine("/* Copy data from table " + tableName + " with special characters escaped. */");
		unloadDataFile.PutLine("/* This file is for unloading data from Oracle: one query for headings, one for data  */");
		unloadDataFile.PutLine("/* If the data is being unloaded from PostgreSQL use the copy command instead. */");
		unloadDataFile.PutLine("/* If the data is being unloaded from Microsoft SQL Server or MySQL change CHR() to CHAR(). */");

		if (databaseProduct.equals("Microsoft SQL Server") || databaseProduct.equals("MySQL"))
			byteValue = new String("char");
		else
			byteValue = new String("chr");
		
		maxLengthColumnName = 0;
		for (iterator = columnList.listIterator(); iterator.hasNext();)
		{
			columnElement = (ColumnElement) iterator.next();
			
			if (columnElement.getColumnName().length() > maxLengthColumnName)
				maxLengthColumnName = columnElement.getColumnName().length();
		}

		line = new StringBuffer("select '");          // column headings
		
		for (iterator = columnList.listIterator(); iterator.hasNext();)
		{
			columnElement = (ColumnElement) iterator.next();
			columnName = new String(columnElement.getColumnName());
			line.append(columnName);
			if (iterator.hasNext())
				line.append(',');
		}
    	if (databaseProduct.equals("PostgreSQL"))
		    line.append("';");
    	else if (databaseProduct.equals("Oracle"))    	
		    line.append("' from dual;");

		unloadDataFile.PutLine(line.toString());
		
		unloadDataFile.PutLine("select ");
		
		for (iterator = columnList.listIterator(); iterator.hasNext();)
		{
			columnElement = (ColumnElement) iterator.next();
			originalColumnName = new String(columnElement.getOriginalColumnName());
			columnName = new String(columnElement.getColumnName());
			if (!columnName.equals(originalColumnName))
				columnName = new String("\"" + originalColumnName + "\"");
			dataType = new String(columnElement.getDataType().toUpperCase());
			
			line = new StringBuffer("");
			
	        if
	        (
				dataType.equalsIgnoreCase("CHAR") || 
				dataType.equalsIgnoreCase("NCHAR") || 
				dataType.equalsIgnoreCase("NVARCHAR") ||
				dataType.equalsIgnoreCase("VARCHAR")
	        )
	        {
	        	if (databaseProduct.equals("PostgreSQL"))
	        	{
		        	line = new StringBuffer
		        	(
		        		"    " + 	        	
				        "case " +
				          "when position(',' in coalesce(" + columnName + ", '')) > 0 " +
				          "then " +
				            "'\"' || " +
				            "replace " +
				            "(" +
				              "replace " + 
				              "(" +
				                "replace " + 
				                "(" +
				                  "replace " +
				                  "(" +
				                    "replace(" + columnName + ", " + byteValue + "(10), '\\n')" +
				                    ", " + byteValue + "(13), '\\r'" +
				                  "), " + byteValue + "(12), '\\f'" +
				                "), " + byteValue + "(9), '\\t'" +
				              "), '\"', '\"\"'" +
				            ") || " +
				            "'\"' " +
				          "else " +
				            "replace " +
				            "(" +
				              "replace " +
				              "(" +
				                "replace " + 
				                "(" +
				                  "replace(coalesce(" + columnName + ", ''), " + byteValue + "(10), '\\n')" + 
				                  ", " + byteValue + "(13), '\\r'" +
				                "), " + byteValue + "(12), '\\f'" +
					          "), " + byteValue + "(9), '\\t'" +
					        ") " +
				          "end "
                    );	        		
	        	}
	        	else if (databaseProduct.equals("Oracle"))
	        	{
		        	line = new StringBuffer
		        	(
		        		"    " + 	        	
				        "case " +
				          "when instr(nvl(" + columnName + ", ' '), ',') > 0 " +
				          "then " +
				            "'\"' || " +
				            "replace " +
				            "(" +
				              "replace " + 
				              "(" +
				                "replace " + 
				                "(" +
				                  "replace " +
				                  "(" +
				                    "replace(" + columnName + ", " + byteValue + "(10), '\\n')" +
				                    ", " + byteValue + "(13), '\\r'" +
				                  "), " + byteValue + "(12), '\\f'" +
				                "), " + byteValue + "(9), '\\t'" +
				              "), '\"', '\"\"'" +
				            ") || " +
				            "'\"' " +
				          "else " +
				            "replace " +
				            "(" +
				              "replace " + 
				              "(" +
				                "replace " +
				                "(" +
				                  "replace(nvl(" + columnName + ", ' '), " + byteValue + "(10), '\\n')" + 
				                  ", " + byteValue + "(13), '\\r'" +
				                "), " + byteValue + "(12), '\\f'" +
					          "), " + byteValue + "(9), '\\t'" +
					        ")" +
				          "end "
                    );	        		
	        	}
	        }
	        else
				line = new StringBuffer
	            (
	            	"    case when " + columnName + " is null then '' else cast(" + columnName + " as varchar) end "
	            );
		
	        if (iterator.hasNext())
	        {
	        	line.append(" || ',' || ");
	        }
        	unloadDataFile.PutLine(line.toString());
		}
		
		unloadDataFile.PutLine("from");
		unloadDataFile.PutLine("    " + schemaName + "." + tableName + ";");
		unloadDataFile.close();
	}
}