package lu.tudor.santec.gecamed.billing.test.converter;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.logging.Handler;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import lu.tudor.santec.gecamed.billing.test.BillingRulesFixture;
import lu.tudor.santec.gecamed.core.utils.FileUtils;

import org.apache.commons.lang3.StringEscapeUtils;
import org.apache.log4j.ConsoleAppender;
import org.apache.log4j.Layout;
import org.apache.log4j.Level;
import org.apache.log4j.Logger;
import org.apache.log4j.PatternLayout;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.bridge.SLF4JBridgeHandler;

/**
 * @author jens.ferring(at)tudor.lu
 * 
 * @version
 * <br>$Log: BillingRulesTestcaseConverter.java,v $
 */

public class BillingRulesTestcaseConverter
{
	/* ======================================== */
	// CONSTANTS
	/* ======================================== */
	
	private static final String	PLACEHOLDER_TEMPLATE_TEST		= "<!--ADD_TEST-->";
	private static final String	PLACEHOLDER_TEMPLATE_SOURCE_ROW	= "<!--ADD_SOURCE_ROW-->";
	private static final String	PLACEHOLDER_TEMPLATE_RESULT_ROW	= "<!--ADD_RESULT_ROW-->";
	
	private static final String	PLACEHOLDER_TEST_CATEGORY		= "[TEST_CATEGORY]";
	private static final String	PLACEHOLDER_TEST_NAME			= "[TEST_NAME]";
	
	private static final String PLACEHOLDER_SOURCE_INSURANCE_ACRONYM		= "[INSURANCE_ACRONYM]";
	private static final String PLACEHOLDER_SOURCE_TPP_ACRONYM				= "[TPP_ACRONYM]";
	private static final String PLACEHOLDER_SOURCE_1CLASS_REQUIRED			= "[1CLASS_REQUIRED]";
	private static final String PLACEHOLDER_SOURCE_HOSP_CLASS				= "[HOSPITALIZATION_CLASS]";
	private static final String PLACEHOLDER_SOURCE_OPTIONS					= "[OPTIONS]";
	private static final String PLACEHOLDER_SOURCE_DATE						= "[DATE]";
	private static final String PLACEHOLDER_SOURCE_TIME						= "[TIME]";
	private static final String PLACEHOLDER_SOURCE_KEY_VALUE				= "[KEY_VALUE]";
	private static final String PLACEHOLDER_SOURCE_COEFFICIENT				= "[COEFFICIENT]";
	private static final String PLACEHOLDER_SOURCE_CODE						= "[CODE]";
	private static final String PLACEHOLDER_SOURCE_SUFFIXES					= "[SUFFIXES]";
	private static final String PLACEHOLDER_SOURCE_QUANTITY					= "[QUANTITY]";

	private static final String PLACEHOLDER_RESULT_CODE			= "[CODE]";
	private static final String PLACEHOLDER_RESULT_SUFFIXES		= "[SUFFIXES]";
	private static final String PLACEHOLDER_RESULT_QUANTITY		= "[QUANTITY]";
	private static final String PLACEHOLDER_RESULT_AMOUNT		= "[AMOUNT]";
	private static final String PLACEHOLDER_RESULT_TPP_ACRONYM	= "[TPP_ACRONYM]";
	private static final String PLACEHOLDER_RESULT_COMMENT		= "[COMMENT]";
	
	
	
	/* ======================================== */
	// MEMBERS
	/* ======================================== */
	
	/** the logger Object for this class */
	private static Logger logger;
	
	private static DateFormat	dateFormatter	= new SimpleDateFormat("yyyy-MM-dd");
	private static DateFormat	timeFormatter	= new SimpleDateFormat("HH:mm");
	
	
	// property values
	private int sheetTestCases;
	
	private int	columnTestCategory;
	private int columnTestLabel;
	private int columnSourceInsuranceAcronym;
	private int columnSourceOptions;
	private int columnSourceTPPAcronym;
	private int columnSource1stClassRequired;
	private int columnSourceHospClass;
	private int columnSourceDate;
	private int columnSourceTime;
	private int columnSourceKeyValue;
	private int columnSourceCoefficient;
	private int columnSourceCode;
	private int columnSourceSuffixes;
	private int columnSourceQuantity;
	private int columnResultCode;
	private int columnResultSuffixes;
	private int columnResultQuantity;
	private int columnResultAmount;
	private int columnResultTppAcronym;
	private int columnResultComment;
	
	private int rowDefaults;
	private int rowStart;
	private int rowEnd;
	private int rowCurrent;
	
	// templates
	private String	templateHtml;
	private String	templateTest;
	private String	templateSourceRow;
	private String	templateResultRow;
	private String	excelFilePath;
	
	private HSSFWorkbook	workbook;
	private HSSFSheet		sheet;
	
	
	
	/* ======================================== */
	// CONSTRUCTORS
	/* ======================================== */
	
	public BillingRulesTestcaseConverter ()
	{
//		initLogger();
		loadTemplates();
	}
	
	
	
	/* ======================================== */
	// CLASS BODY
	/* ======================================== */
	
	public void convertExcel (String propertiesFilePath, File outputHtml) throws IOException
	{
		String	resultHtml	= templateHtml;
		String	test		= null;
		String	sourceRows;
		String	resultRows;
		
		loadProperties(propertiesFilePath);
		workbook	= new HSSFWorkbook(BillingRulesFixture.class.getResourceAsStream(excelFilePath));
		sheet		= workbook.getSheetAt(sheetTestCases);
		
		logger.info("Reading default values");
		String	defaultInsurance		= getCellValue(rowDefaults, columnSourceInsuranceAcronym);
		String	defaultOptions			= getCellValue(rowDefaults, columnSourceOptions).toUpperCase();
		String	defaultTpp				= getCellValue(rowDefaults, columnSourceTPPAcronym);
		String	default1stClassRequired	= getCellValue(rowDefaults, columnSource1stClassRequired);
		String	defaultHospClass		= getCellValue(rowDefaults, columnSourceHospClass);
		String	defaultDate				= getCellValue(rowDefaults, columnSourceDate);
		String	defaultTime				= getCellValue(rowDefaults, columnSourceTime);
//		String	defaultCode				= getCellValue(rowDefaults, columnSourceCode);
		String	defaultKeyValue			= getCellValue(rowDefaults, columnSourceKeyValue);
		String	defaultQuantity			= getCellValue(rowDefaults, columnSourceQuantity);
		
		String	category;
		String	lastCategory			= null;
		String	comment;
		
		
		logger.info("Reading data ...");
		rowCurrent	= rowStart-1;
		while (true)
		{
			rowCurrent++;
			
			if (isRowEmpty())
			{
				if (test == null && rowCurrent > rowStart)
					// 2 empty rows in a row, end here
					break;
				else if (test == null)
					// this is the first test and the first row was empty
					continue;
				
				// add the last converted test to the test cases
				test		= test.replace(PLACEHOLDER_TEMPLATE_SOURCE_ROW, "")
						.replace(PLACEHOLDER_TEMPLATE_RESULT_ROW, "");
				resultHtml	= resultHtml.replace(PLACEHOLDER_TEMPLATE_TEST, test);
				// reset the data
				test		= null;
				
				if (rowCurrent >= rowEnd && rowEnd > 0)
					// end row defined and reached and the last invoice is complete
					break;
			}
			else
			{
				sourceRows	= templateSourceRow;
				resultRows	= templateResultRow;
				
				// add the act source values
				sourceRows	= replace(sourceRows, PLACEHOLDER_SOURCE_HOSP_CLASS,	columnSourceHospClass,	test != null ? null : defaultHospClass);
				sourceRows	= replace(sourceRows, PLACEHOLDER_SOURCE_DATE,			columnSourceDate,		test != null ? null : defaultDate);
				sourceRows	= replace(sourceRows, PLACEHOLDER_SOURCE_TIME,			columnSourceTime,		test != null ? null : defaultTime);
				sourceRows	= replace(sourceRows, PLACEHOLDER_SOURCE_CODE,			columnSourceCode,		true);
				sourceRows	= replace(sourceRows, PLACEHOLDER_SOURCE_KEY_VALUE,		columnSourceKeyValue,	test != null ? null : defaultKeyValue);
				sourceRows	= replace(sourceRows, PLACEHOLDER_SOURCE_COEFFICIENT,	columnSourceCoefficient);
				sourceRows	= replace(sourceRows, PLACEHOLDER_SOURCE_SUFFIXES,		columnSourceSuffixes,	true);
				sourceRows	= replace(sourceRows, PLACEHOLDER_SOURCE_QUANTITY,		columnSourceQuantity,	defaultQuantity);
				
				// add the expected result values
				resultRows	= replace(resultRows, PLACEHOLDER_RESULT_CODE,			columnResultCode,		true);
				resultRows	= replace(resultRows, PLACEHOLDER_RESULT_SUFFIXES,		columnResultSuffixes,	true);
				resultRows	= replace(resultRows, PLACEHOLDER_RESULT_QUANTITY,		columnResultQuantity);
				resultRows	= replace(resultRows, PLACEHOLDER_RESULT_AMOUNT,		columnResultAmount);
				comment		= getCellValue(rowCurrent, columnResultComment);
				resultRows	= resultRows.replace(PLACEHOLDER_RESULT_COMMENT, (comment == null || comment.trim().length() == 0) 
						? "" : " title=\""+comment+"\"");
				
				if (test == null)
				{
					// prepare a new test, replace the unique data
					test		= templateTest;
					category	= getCellValue(rowCurrent,	columnTestCategory);
					if (category == null || category.length() <= 0)
					{
						if (lastCategory == null)
							lastCategory = searchLastValue(rowCurrent, columnTestCategory, rowDefaults);
						category	= lastCategory;
					}
					lastCategory	= category;
					test		= test.replace(PLACEHOLDER_TEST_CATEGORY, category);
					
					test		= replace(test, PLACEHOLDER_TEST_NAME,		columnTestLabel);
					
					// add the invoice source values
					sourceRows	= replace(sourceRows, PLACEHOLDER_SOURCE_INSURANCE_ACRONYM,	columnSourceInsuranceAcronym,	defaultInsurance);
					sourceRows	= sourceRows.replace(PLACEHOLDER_SOURCE_OPTIONS, getOptions(defaultOptions));
					sourceRows	= replace(sourceRows, PLACEHOLDER_SOURCE_TPP_ACRONYM,		columnSourceTPPAcronym,			defaultTpp);
					sourceRows	= replace(sourceRows, PLACEHOLDER_SOURCE_1CLASS_REQUIRED,	columnSource1stClassRequired,	default1stClassRequired);
					// add the invoice result value(s)
					resultRows	= replace(resultRows, PLACEHOLDER_RESULT_TPP_ACRONYM,		columnResultTppAcronym);
				}
				else
				{
					// remove the invoice source values
					sourceRows	= sourceRows.replace(PLACEHOLDER_SOURCE_INSURANCE_ACRONYM,	"");
					sourceRows	= sourceRows.replace(PLACEHOLDER_SOURCE_OPTIONS,			"");
					sourceRows	= sourceRows.replace(PLACEHOLDER_SOURCE_TPP_ACRONYM,		"");
					sourceRows	= sourceRows.replace(PLACEHOLDER_SOURCE_1CLASS_REQUIRED,	"");
					// add the invoice result value(s)
					resultRows	= resultRows.replace(PLACEHOLDER_RESULT_TPP_ACRONYM,		"");
				}
				// add the source row to the test
				test		= test.replace(PLACEHOLDER_TEMPLATE_SOURCE_ROW, sourceRows);
				// add the result row to the test
				test		= test.replace(PLACEHOLDER_TEMPLATE_RESULT_ROW, resultRows);
			}
		}
		
		logger.info("Data from Excel file successfully read. Now writing into file \"" + outputHtml.getPath() + "\"");
		
		// write the converted HTML into the output file
		FileUtils.writeFile(resultHtml.getBytes("UTF-8"), outputHtml);
		logger.info("DONE!");
	}
	
	
	
	private String searchLastValue (int startRow, int endRow, int column)
	{
		String	value	= null;
		int		row		= startRow;
		
		while (value == null && row >= endRow)
		{
			value	= getCellValue(row, column);
			row--;
		}
		
		if (value == null)
			return "";
		else
			return value;
	}



	/* ======================================== */
	// HELP METHODS
	/* ======================================== */
	
	private static void initLogger ()
	{
		if (logger != null)
			return;
		
		/* ---------------------------------------- */
		// INIT THE org.apache.log4j.Logger
		/* ---------------------------------------- */
		
		try
		{
//			// create an initial logger
//			File	logfile	= new File(LOGFILE);
//			logfile.getParentFile().mkdirs();
			
			Logger.getRootLogger().removeAllAppenders();
			
			// define the logging layouts
			Layout fileLayout = new PatternLayout("%d{yyyy-MM-dd HH:mm:ss,SSS} %c %M (line %L)%n%p: %m%n");
			Layout consoleLayout = fileLayout;
			
			// set the logger
			ConsoleAppender consoleAppender = new ConsoleAppender(consoleLayout);
			Logger.getRootLogger().addAppender(consoleAppender);
			
//			RollingFileAppender fileAppender = new RollingFileAppender(
//					fileLayout,
//					logfile.getAbsolutePath(),
//					true);
//			fileAppender.setMaxFileSize("5MB");
//			fileAppender.setMaxBackupIndex(3);
//			fileAppender.setEncoding("UTF-8");
//			fileAppender.setName("GECAMed default Logger");
//			BasicConfigurator.configure(fileAppender);
			
			// define level for logging levels
			Logger.getRootLogger().setLevel(Level.INFO);
			
			logger = Logger.getLogger(BillingRulesTestcaseConverter.class.getName());
			logger.log(Level.INFO, "org.apache.log4j works!");
		}
		catch (Exception e)
		{
			logger = Logger.getLogger(BillingRulesTestcaseConverter.class.getName());
			logger.log(Level.ERROR, "Error initializing log4j Logger", e);
		}
		
		
		/* ---------------------------------------- */
		// INIT THE java.util.Logger
		/* ---------------------------------------- */
		
		try 
		{
			SLF4JBridgeHandler.uninstall();
			for (Handler h : java.util.logging.Logger.getLogger("").getHandlers())
				java.util.logging.Logger.getLogger("").removeHandler(h);
			SLF4JBridgeHandler.install();
			
			java.util.logging.Logger.getLogger(BillingRulesTestcaseConverter.class.getName()).info("java.util.logging works!");
		}
		catch (Exception e) 
		{
			logger.log(Level.WARN, "setting up java.util.logging.Logger failed" ,e);
		}
	}
	
	
	private void loadProperties (String propertiesFilePath)
	{
		try
		{
			Properties p = new Properties();
			p.load(BillingRulesFixture.class.getResourceAsStream(propertiesFilePath));
			
			excelFilePath	= p.getProperty("excel.filename");
			
			sheetTestCases	= getIntValue(p, "excel.sheet.testcases");
			
			columnTestCategory				= getIntValue(p, "excel.column.source.category");
			columnTestLabel					= getIntValue(p, "excel.column.source.label");
			columnSourceInsuranceAcronym	= getIntValue(p, "excel.column.source.insuranceAcronym");
			columnSourceOptions				= getIntValue(p, "excel.column.source.options");
			columnSourceTPPAcronym			= getIntValue(p, "excel.column.source.tppAcronym");
			columnSource1stClassRequired	= getIntValue(p, "excel.column.source.1classRequired");
			columnSourceHospClass			= getIntValue(p, "excel.column.source.hospitalizationClass");
			columnSourceDate				= getIntValue(p, "excel.column.source.date");
			columnSourceTime				= getIntValue(p, "excel.column.source.time");
			columnSourceKeyValue			= getIntValue(p, "excel.column.source.keyValue");
			columnSourceCoefficient			= getIntValue(p, "excel.column.source.coefficient");
			columnSourceCode				= getIntValue(p, "excel.column.source.code");
			columnSourceSuffixes			= getIntValue(p, "excel.column.source.suffixes");
			columnSourceQuantity			= getIntValue(p, "excel.column.source.quantity");
			columnResultCode				= getIntValue(p, "excel.column.result.code");
			columnResultSuffixes			= getIntValue(p, "excel.column.result.suffixes");
			columnResultQuantity			= getIntValue(p, "excel.column.result.quantity");
			columnResultAmount				= getIntValue(p, "excel.column.result.amount");
			columnResultTppAcronym			= getIntValue(p, "excel.column.result.tppAcronym");
			
			rowDefaults	= getIntValue(p, "excel.row.defaults");
			rowStart	= getIntValue(p, "excel.row.start");
			rowEnd	= getIntValue(p, "excel.row.end");
		}
		catch (Exception e)
		{
			logger.error("Error while loading the properties", e);
		}
	}
	
	
	private void loadTemplates ()
	{
		try
		{
			templateHtml		= readFile(BillingRulesFixture.class.getResourceAsStream("resources/BillingRules_template.html"));
			templateTest		= readFile(BillingRulesFixture.class.getResourceAsStream("resources/BillingRules_test_template.xml"));
			templateSourceRow	= readFile(BillingRulesFixture.class.getResourceAsStream("resources/BillingRules_sourcerow_template.xml"));
			templateResultRow	= readFile(BillingRulesFixture.class.getResourceAsStream("resources/BillingRules_resultrow_template.xml"));
		}
		catch (Exception e)
		{
			logger.error("Error while trying to load the templates.", e);
		}
	}
	
	
	private static String readFile (InputStream is) throws IOException
	{
		BufferedInputStream	input	= null;
		byte[]				buffer;
		
		try
		{
			if (is instanceof BufferedInputStream)
				input = (BufferedInputStream) is;
			else
				input = new BufferedInputStream(is);
			
			buffer	= new byte[input.available()];
			input.read(buffer);
			input.close();
		}
		finally
		{
			if (input != null)
				input.close();
		}
		
		return new String(buffer, "UTF-8");
	}
	
	
	private static int getIntValue (Properties p, String key)
	{
		String value = p.getProperty(key);
		
		if (value == null)
		{
			logger.warn("Property \""+key+"\" not set.");
			return -1;
		}
		
		try
		{
			return Integer.parseInt(value);
		}
		catch (NumberFormatException e)
		{
			logger.warn("Value \""+key+"\" cannot be parsed to an int value.", e);
			return -1;
		}
	}
	
	
	private String getOptions (String defaultOptions)
	{
		String	optionChanges	= getCellValue(rowCurrent, columnSourceOptions);
		String	options			= defaultOptions;
		Pattern optionPattern	= Pattern.compile("^([A-Z0-9]*[A-Z])(\\d+)$", Pattern.CASE_INSENSITIVE);
		Matcher	optionMatcher;
		String	optionDigitPattern;
		
		if (optionChanges == null || optionChanges.trim().length() == 0)
			return defaultOptions;
		
		for (String option : optionChanges.split(","))
		{
			if (option == null)
				continue;
			option	= option.trim();
			if (option.length() == 0)
				continue;
			
			optionMatcher			= optionPattern.matcher(option);
			
			if (!optionMatcher.matches())
			{
				logger.error("Wrong option string: " + option + "\nOption string must match the pattern " + optionPattern.pattern());
			}
			
			optionDigitPattern	= "(?<="+optionMatcher.group(1)+")(\\d+)";
			options				= options.replaceFirst(optionDigitPattern, optionMatcher.group(2));
		}
		
//		System.out.println("Options changed from " + defaultOptions + " to " + options + " (" + optionChanges + ")");
		
		return options;
	}
	
	
	private String getCellValue (int rowNumber, int columnNumber)
	{
		HSSFRow		row		= sheet.getRow(rowNumber);
		if (row == null)
			return null;
		
		HSSFCell	cell	= row.getCell(columnNumber);
		if (cell == null)
			return null;
		
		String result;
		switch (cell.getCellType())
		{
			case HSSFCell.CELL_TYPE_BLANK:
				result = null;
				break;

//			case HSSFCell.CELL_TYPE_BOOLEAN:
//				break;

			case HSSFCell.CELL_TYPE_ERROR:
				logger.error("Cell("+rowNumber+", "+columnNumber+") has an error");
				result = null;
				break;

			case HSSFCell.CELL_TYPE_FORMULA:
				switch (cell.getCachedFormulaResultType())
				{
					case HSSFCell.CELL_TYPE_NUMERIC:
						result = String.valueOf(cell.getNumericCellValue());
						break;
					
					default:
						result = cell.getStringCellValue();
				}
				break;

			case HSSFCell.CELL_TYPE_NUMERIC:
				if (columnNumber == columnSourceQuantity
						|| columnNumber == columnResultQuantity)
					// get quantity
					result = String.valueOf((int) cell.getNumericCellValue());
				else if (columnNumber == columnSourceDate)
					// get date
					result = dateFormatter.format(cell.getDateCellValue());
				else if (columnNumber == columnSourceTime)
					// get time
					result = timeFormatter.format(cell.getDateCellValue());
				else
					// get 
					result = String.valueOf(cell.getNumericCellValue());
				break;
				
			case HSSFCell.CELL_TYPE_STRING:
				result = cell.getStringCellValue();
				break;
				
			default:
				result = cell.toString();
		}
		
		return result == null ? null : StringEscapeUtils.escapeXml(result);
	}



	private String replace (String template, String placeholder, int column)
	{
		return replace(template, placeholder, column, null, false);
	}
	
	
	private String replace (String template, String placeholder, int column, boolean toUppderCase)
	{
		return replace(template, placeholder, column, null, toUppderCase);
	}
	
	
	private String replace (String template, String placeholder, int column, String defaultValue)
	{
		return replace(template, placeholder, column, defaultValue, false);
	}
	
	
	private String replace (String template, String placeholder, int column, String defaultValue, boolean toUppderCase)
	{
		String value = getCellValue(rowCurrent, column);
		
		
		if (value == null || value.trim().length() <= 0)
		{
			if (defaultValue != null && defaultValue.trim().length() > 0)
				value = defaultValue;
			else
				value = "";
		}
		
		return template.replace(placeholder, toUppderCase ? value.toUpperCase() : value);
	}
	
	
	private boolean isRowEmpty ()
	{
		String	value = getCellValue(rowCurrent, columnSourceCode);
		
		return value == null || value.trim().length() <= 0;
	}
	
	
	
	/* ======================================== */
	// MAIN
	/* ======================================== */
	
	public static void main (String[] args) throws Exception
	{
		String propertiesFilePath;
		
		if (args.length > 0)
			propertiesFilePath = args[0];
		else
			propertiesFilePath = "resources/converter.properties";
		
		
		initLogger();
		String	outputPath	= System.getProperty("user.dir") + File.separator + "src" + File.separator 
				+ BillingRulesFixture.class.getPackage().getName().replace(".", File.separator) + File.separator
				+ BillingRulesFixture.class.getSimpleName().replace("Fixture", ".html");
		File	outputFile	= new File(outputPath);
		
		int	i = 0;
		while (outputFile.exists()
				&& !outputFile.delete())
		{
			outputFile	= new File(outputPath.replace(
					"BillingRules.html", 
					"BillingRules" + ("_"+ ++i) + ".html"));
		}
		
		new BillingRulesTestcaseConverter().convertExcel(
				propertiesFilePath, outputFile);
	}
}
