/*
 * JBoss, Home of Professional Open Source.
 * See the COPYRIGHT.txt file distributed with this work for information
 * regarding copyright ownership.  Some portions may be licensed
 * to Red Hat, Inc. under one or more contributor license agreements.
 * 
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 * 
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 * 
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
 * 02110-1301 USA.
 * Modified by
 * @author  Data Virtuality GmbH
 * @date    $Date$
 *  
 */
// https://issues.jboss.org/browse/TEIID-2904
// https://issues.jboss.org/browse/TEIID-4995
// https://issues.redhat.com/browse/TEIID-3117
package org.teiid.translator.excel;

import com.datavirtuality.dv.core.interfaces.DVCaller;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.teiid.logging.LogConstants;
import org.teiid.logging.LogManager;
import org.teiid.metadata.Column;
import org.teiid.metadata.Column.SearchType;
import org.teiid.metadata.ExtensionMetadataProperty;
import org.teiid.metadata.MetadataFactory;
import org.teiid.metadata.Table;
import org.teiid.translator.*;
import org.teiid.translator.TranslatorProperty.PropertyType;

import javax.resource.ResourceException;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.regex.Pattern;
public class ExcelMetadataProcessor implements MetadataProcessor<FileConnection> {

	@ExtensionMetadataProperty(applicable=Table.class, datatype=String.class, display="Excel File Name", description="Excel File name, use file name pattern if one than one file in the parent directory", required=true)
	public static final String FILE = MetadataFactory.EXCEL_URI+"FILE"; //$NON-NLS-1$

	@ExtensionMetadataProperty(applicable=Column.class, datatype=Integer.class, display="Cell Number", description="Cell number, where the column information is defined. If column name is ROW_ID, define it as -1", required=true)
	public static final String CELL_NUMBER = MetadataFactory.EXCEL_URI+"CELL_NUMBER"; //$NON-NLS-1$

	@ExtensionMetadataProperty(applicable=Column.class, datatype=Integer.class, display="First Data Number", description="First Row Number, where data rows start")
	public static final String FIRST_DATA_ROW_NUMBER = MetadataFactory.EXCEL_URI+"FIRST_DATA_ROW_NUMBER"; //$NON-NLS-1$

	@ExtensionMetadataProperty(applicable=Table.class, datatype=String.class, display="Event API", description="Used mode")
	public static final String USE_EVENT_API_PROPERTY = MetadataFactory.EXCEL_URI+"USE_EVENT_API";

	@ExtensionMetadataProperty(applicable=Table.class, datatype=Integer.class, display="Row cache size", description="Row cache size used in Event API mode")
	public static final String ROW_CACHE_SIZE_PROPERTY = MetadataFactory.EXCEL_URI+"ROW_CACHE_SIZE";

	@ExtensionMetadataProperty(applicable=Table.class, datatype=Integer.class, display="Buffer size", description="Buffer size used in Event API mode")
	public static final String BUFFER_SIZE_PROPERTY = MetadataFactory.EXCEL_URI+"BUFFER_SIZE";

	public static final String ROW_ID = "ROW_ID"; //$NON-NLS-1$

	public static final int ROW_CACHE_SIZE = 100; // number of rows to keep in memory (defaults to 10)
	public static final int BUFFER_SIZE = 4096;   // buffer size to use when reading InputStream to file (defaults to 1024)

	//from org.apache.poi.ss.usermodel.DateUtil
	private static final Pattern date_ptrn1 = Pattern.compile("^\\[\\$\\-.*?\\]");
	private static final Pattern date_ptrn2 = Pattern.compile("^\\[[a-zA-Z]+\\]");
	private static final Pattern date_ptrn3a = Pattern.compile("[yYmMdDhHsS]");
	private static final Pattern date_ptrn3b = Pattern.compile("^[\\[\\]yYmMdDhHsS\\-T/???,. :\"\\\\]+0*[ampAMP/]*$");
	private static final Pattern date_ptrn4 = Pattern.compile("^\\[([hH]+|[mM]+|[sS]+)\\]");
	private static final Pattern date_ptrn5 = Pattern.compile("^\\[DBNum(1|2|3)\\]");
	private static final Pattern systemTimePattern = Pattern.compile("^\\[\\$\\-.*?\\]([hH]+:[mM]+:[sS]+\\s*[aAmM]*\\/*[pPmM]*)");

	private String excelFileName;
	private int headerRowNumber = 0;
	private boolean hasHeader = false;
	private int dataRowNumber = 0;
	private boolean hasDataRowNumber = false;
	private boolean allColumnsAsString = false;
	private UseEventApi useEventApi = UseEventApi.AUTO;
	private int rowCacheSize = ROW_CACHE_SIZE;
	private int bufferSize = BUFFER_SIZE;

	public enum UseEventApi {
		TRUE,
		FALSE,
		AUTO;

		public boolean isUse() {
			return this == UseEventApi.TRUE || this == UseEventApi.AUTO;
		}
	}

	public void process(MetadataFactory mf, FileConnection conn) throws TranslatorException {
		if (this.excelFileName == null) {
			throw new TranslatorException(ExcelPlugin.Event.TEIID23004, ExcelPlugin.Util.gs(ExcelPlugin.Event.TEIID23004, "importer.ExcelFileName")); //$NON-NLS-1$
		}
		try {
			File xlsFile = conn.getFile(this.excelFileName);
			if (xlsFile.isDirectory() || !xlsFile.exists()) {
				throw new TranslatorException(ExcelPlugin.Event.TEIID23005, ExcelPlugin.Util.gs(ExcelPlugin.Event.TEIID23005, xlsFile.getName()));
			}
			
			String extension = getFileExtension(xlsFile);
			FileInputStream xlsFileStream = new FileInputStream(xlsFile);
			Workbook workbook = null;
			try {
				if (this.useEventApi == UseEventApi.AUTO || this.useEventApi == UseEventApi.FALSE) {
					if (extension.equalsIgnoreCase("xls")) { //$NON-NLS-1$
						workbook = new HSSFWorkbook(xlsFileStream);
					} else if (extension.equalsIgnoreCase("xlsx") ||
							extension.equalsIgnoreCase("xlsm")) { //$NON-NLS-1$
						if (this.useEventApi.isUse()) {
							workbook = getStreamReader(this.rowCacheSize, this.bufferSize, xlsFileStream);
						} else
							workbook = new XSSFWorkbook(xlsFileStream);
					}
				} else
					workbook = getStreamReader(this.rowCacheSize, this.bufferSize, xlsFileStream);
				int sheetCount = workbook.getNumberOfSheets();
				for (int i = 0; i < sheetCount; i++) {
					Sheet sheet = workbook.getSheetAt(i);
					addTable(mf, sheet, xlsFile.getName());
				}
			} finally {
				if (workbook != null)
					workbook.close();
				xlsFileStream.close();
			}
		} catch (ResourceException e) {
			throw new TranslatorException(e);
		} catch (IOException e) {
			throw new TranslatorException(e);
		}
	}

	public static Workbook getStreamReader(int rowCacheSize, int bufferSize, FileInputStream xlsFileStream) {
		//DVCORE-5412
		ZipSecureFile.setMinInflateRatio(0.009);
		return (Workbook) DVCaller.call(122, rowCacheSize, bufferSize, xlsFileStream);
	}

	public static boolean isFullHeader(Row row) {
		for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
			if (row.getCell(i) == null) {
				return false;
			}
		}
		return true;
	}

	private void addTable(MetadataFactory mf, Sheet sheet, String xlsName) {
		int firstRowNumber = -1;
		int firstCellNumber = -1;
		Row headerRow = null;
		Row originalHeaderRow = null;
		Row rowWithMaxColumns = null;
		int maxColumns = 0;
		Map<Integer, Row> rowsMap = new HashMap();
		int count = 0;
		if (this.useEventApi.isUse()) {
			// looking for a header
			Iterator<Row> iter = sheet.rowIterator();
			if (this.hasHeader && !iter.hasNext()) {
				LogManager.logInfo(LogConstants.CTX_CONNECTOR, ExcelPlugin.Util.gs(ExcelPlugin.Event.TEIID23006, xlsName));
				return;
			}
			while(iter.hasNext()) {
				Row row = iter.next();
				if (this.hasHeader) {
					if (row.getRowNum() == this.headerRowNumber) {
						originalHeaderRow = row;
						firstRowNumber = row.getRowNum();
						headerRow = row;
						maxColumns = row.getLastCellNum() - row.getFirstCellNum();
						firstCellNumber = headerRow.getFirstCellNum();
						if (firstCellNumber == -1) {
							LogManager.logInfo(LogConstants.CTX_CONNECTOR, ExcelPlugin.Util.gs(ExcelPlugin.Event.TEIID23006, xlsName));
							return;
						}
					}
					if (row.getLastCellNum() - row.getFirstCellNum() > maxColumns) {
						maxColumns = row.getLastCellNum() - row.getFirstCellNum();
						headerRow = row;
						rowWithMaxColumns = row;
						firstCellNumber = Math.min(firstCellNumber, row.getFirstCellNum());
					}
					rowsMap.put(++count, row);
				} else {
					headerRow = row;
					originalHeaderRow = row;
					firstRowNumber = row.getRowNum();
					firstCellNumber = headerRow.getFirstCellNum();
					break;
				}
			}
		} else {
			firstRowNumber = sheet.getFirstRowNum();
			if (this.hasHeader) {
				headerRow = sheet.getRow(this.headerRowNumber);
				firstRowNumber = this.headerRowNumber;
				firstCellNumber = headerRow != null ? headerRow.getFirstCellNum() : -1;
				if (firstCellNumber == -1) {
					LogManager.logInfo(LogConstants.CTX_CONNECTOR, ExcelPlugin.Util.gs(ExcelPlugin.Event.TEIID23006, xlsName));
					return;
				}
			}

			if (headerRow == null) {
				while (firstCellNumber == -1) {
					headerRow = sheet.getRow(firstRowNumber++);
					if (headerRow == null) {
						continue;
					}
					firstCellNumber = headerRow.getFirstCellNum();
				}
			}
		}
		
		// create a table for each sheet
		AtomicInteger columnCount = new AtomicInteger();
		Table table = mf.addTable(sheet.getSheetName());
		table.setNameInSource(sheet.getSheetName());
		table.setProperty(ExcelMetadataProcessor.FILE, xlsName);
		table.setProperty(ExcelMetadataProcessor.USE_EVENT_API_PROPERTY, this.useEventApi.name());
		table.setProperty(ExcelMetadataProcessor.ROW_CACHE_SIZE_PROPERTY, Integer.toString(this.rowCacheSize));
		table.setProperty(ExcelMetadataProcessor.BUFFER_SIZE_PROPERTY, Integer.toString(this.bufferSize));
		
		// add implicit row_id column based on row number from excel sheet 
		Column column = mf.addColumn(ROW_ID, TypeFacility.RUNTIME_NAMES.INTEGER, table);
		column.setSearchType(SearchType.All_Except_Like);
		column.setProperty(CELL_NUMBER, ROW_ID);
		mf.addPrimaryKey("PK0", Arrays.asList(ROW_ID), table); //$NON-NLS-1$
		column.setUpdatable(false);

		int lastCellNumber = headerRow != null ? headerRow.getLastCellNum() : -1;

		int dataRowNumber;
		if (this.hasDataRowNumber) {
			// adjust for zero index
			table.setProperty(ExcelMetadataProcessor.FIRST_DATA_ROW_NUMBER, String.valueOf(this.dataRowNumber+1));
			dataRowNumber = this.dataRowNumber;
		}
		else if (this.hasHeader) {
			// +1 zero based, +1 to skip header
			table.setProperty(ExcelMetadataProcessor.FIRST_DATA_ROW_NUMBER, String.valueOf(firstRowNumber+2));
			dataRowNumber = firstRowNumber+1;
		}
		else {
			// for this.useEventApi==false +1 already occurred because of the increment above
			dataRowNumber = this.useEventApi.isUse() ? firstRowNumber + 1 : firstRowNumber;
			table.setProperty(ExcelMetadataProcessor.FIRST_DATA_ROW_NUMBER, String.valueOf(dataRowNumber));
		}

		if ((headerRow == null || headerRow != originalHeaderRow) && this.hasHeader && maxColumns != 0) {
			lastCellNumber = maxColumns;
			firstCellNumber = 0;
			for (int j = firstCellNumber; j < lastCellNumber; j++) {
				Cell dataCell = rowWithMaxColumns.getCell(j);
				String headerType = getCellType(dataCell, false);
				String columnName = null;
				if (originalHeaderRow != null && originalHeaderRow.getCell(j) != null) {
					columnName = originalHeaderRow.getCell(j).getStringCellValue();
				} else {
					columnName = "column" + columnCount.incrementAndGet() + "(" + headerType + ")";
				}
				addColumnToTable(mf, columnName, table, j, dataCell, dataCell);
			}
			return;
		}

		Row dataRow = null;
		if (this.useEventApi.isUse()) {
			// looking for a data row
			Iterator<Row> iter = sheet.rowIterator();
			while(iter.hasNext()) {
				Row row = iter.next();
				if (row.getRowNum() == dataRowNumber) {
					dataRow = row;
					break;
				}
			}
		} else
			dataRow = sheet.getRow(dataRowNumber);
		
		if (firstCellNumber != -1) {
			for (int j = firstCellNumber; j < lastCellNumber; j++) {
				Cell headerCell = headerRow.getCell(j);
				Cell dataCell = dataRow != null ? dataRow.getCell(j) : null;
				// if the cell value is null; then advance the data row cursor to to find it
				if (dataCell == null || CellType.BLANK.equals(dataCell.getCellType())) {
					Iterator<Row> iter = null;
					if (this.useEventApi.isUse()) {
						iter = sheet.rowIterator();
						while(iter.hasNext()) {
							if (iter.next().getRowNum() == dataRowNumber) {
								break;
							}
						}
					}
					for (int rowNo = dataRowNumber + 1; rowNo < dataRowNumber + 10000; rowNo++) {
						Row row = null;
						if (this.useEventApi.isUse()) {
							if (iter.hasNext()) {
								row = iter.next();
							} else {
								row = rowsMap.get(rowNo);
							}
						} else {
							row = sheet.getRow(rowNo);
						}
						if (row != null) {
							dataCell = row.getCell(j);
							if (dataCell != null && !CellType.BLANK.equals(dataCell.getCellType())) {
								break;
							}
						} else {
							break;
						}
					}
				}
				String headerType = getCellType(headerCell, false);
				addColumnToTable(mf,cellName(headerCell, columnCount, headerType), table, j, headerCell, dataCell);
			}
		}
	}

	private void addColumnToTable(MetadataFactory mf, String name, Table table, int j, Cell headerCell, Cell dataCell) {
		Column column = mf.addColumn(name, cellType(headerCell, dataCell), table);
		column.setSearchType(SearchType.Unsearchable);
		column.setProperty(ExcelMetadataProcessor.CELL_NUMBER, String.valueOf(j + 1));
	}


	private String cellType(Cell headerCell, Cell dataCell) {
		if (this.hasHeader && dataCell != null) {
			return getCellType(dataCell, this.allColumnsAsString);
		}
		return getCellType(headerCell, this.allColumnsAsString);
	}

	private String cellName(Cell headerCell, AtomicInteger count, String headerType) {
		if (this.hasHeader && headerCell != null) {
			if (CellType.NUMERIC.equals(headerCell.getCellType())) {
				if (TypeFacility.RUNTIME_NAMES.TIMESTAMP.equals(headerType)) {
					Date date = headerCell.getDateCellValue();
					return String.valueOf(new Timestamp(date.getTime()));
				}
				if (TypeFacility.RUNTIME_NAMES.TIME.equals(headerType)) {
					Date date = headerCell.getDateCellValue();
					return String.valueOf(new java.sql.Time(date.getTime()));
				}
				if (TypeFacility.RUNTIME_NAMES.DATE.equals(headerType)) {
					SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
					Date date = headerCell.getDateCellValue();
					return sdf.format(date);
				}
				return String.valueOf(headerCell.getNumericCellValue());
			} else if (CellType.BOOLEAN.equals(headerCell.getCellType()))
				return String.valueOf(headerCell.getBooleanCellValue());
			else {
				String cellName = headerCell.getStringCellValue();
				if (cellName != null && cellName.length() > 0)
					return cellName;
			}
		}
		return "column"+count.incrementAndGet(); //$NON-NLS-1$
	}

	@TranslatorProperty(display="Header Row Number", description="Row number that contains the header information")
 	public int getHeaderRowNumber() {
		return headerRowNumber;
	}

	public void setHeaderRowNumber(int headerRowNumber) {
		if (headerRowNumber < 1) {
			return;
		}
		//adjust for zero index
		this.hasHeader = true;
		this.headerRowNumber = headerRowNumber-1;
	}

	@TranslatorProperty(display="Data Row Number", category=PropertyType.IMPORT, description="Row number from which data rows start from")
 	public int getDataRowNumber() {
		return dataRowNumber;
	}

	public void setDataRowNumber(int dataRowNumber) {
		//adjust for zero index
		this.hasDataRowNumber = true;
		this.dataRowNumber = dataRowNumber-1;
	}

	@TranslatorProperty(display="Excel File", category=PropertyType.IMPORT, description="Name of the Excel file to read metadata from", required=true)
 	public String getExcelFileName() {
		return excelFileName;
	}

	public void setExcelFileName(String fileName) {
		this.excelFileName = fileName;
	}

	@TranslatorProperty(display="All columns as String", category=PropertyType.IMPORT, description="Load all columns as string")
	public boolean getAllColumnsAsString() {
		return allColumnsAsString;
	}

	public void setAllColumnsAsString(boolean allColumnsAsString) {
		this.allColumnsAsString = allColumnsAsString;
	}

	@TranslatorProperty(display="Event API", category=PropertyType.IMPORT, description="Event API used at the moment")
	public String getUseEventApi() {
		return useEventApi.name();
	}

	public void setUseEventApi(String useEventApi) throws TranslatorException {
		try {
			this.useEventApi = UseEventApi.valueOf(useEventApi.toUpperCase());
		} catch (Exception e) {
			throw new TranslatorException(ExcelPlugin.Util.gs(ExcelPlugin.Event.DVCORE94088));
		}
	}

	@TranslatorProperty(display="Row cache size", category=PropertyType.IMPORT, description="Row cache size")
	public int getRowCacheSize() {
		return rowCacheSize;
	}

	public void setRowCacheSize(int rowCacheSize) {
		this.rowCacheSize = rowCacheSize;
	}

	@TranslatorProperty(display="Buffer size", category=PropertyType.IMPORT, description="Buffer size used in Event API mode")
	public int getBufferSize() {
		return bufferSize;
	}

	public void setBufferSize(int bufferSize) {
		this.bufferSize = bufferSize;
	}

	static String getFileExtension(File xlsFile) {
		int idx = xlsFile.getName().lastIndexOf('.');
		String extension = "xls"; //$NON-NLS-1$
		if (idx > 0) {
		    extension = xlsFile.getName().substring(idx+1);
		}
		return extension;
	}

	private String getCellType(Cell cell, boolean allColumnsAsString) {
		if (cell == null || allColumnsAsString) {
			return TypeFacility.RUNTIME_NAMES.STRING;
		}
		switch (cell.getCellType()) {
		case FORMULA:
			return handleFormulaTypeCells(cell);
		case STRING:
		case BLANK:
			return TypeFacility.RUNTIME_NAMES.STRING;
		case BOOLEAN:
			return TypeFacility.RUNTIME_NAMES.BOOLEAN;
		default:
			if (DateUtil.isCellDateFormatted(cell))
				return getExactDateFormat(cell);
			if (cell.getCellStyle() != null) {
				if ("@".equals(cell.getCellStyle().getDataFormatString())) {
					return TypeFacility.RUNTIME_NAMES.STRING;
				}
			}
			return TypeFacility.RUNTIME_NAMES.DOUBLE;
		}
	}

	private String handleFormulaTypeCells(Cell cell) {
		if (CellType.ERROR.equals(cell.getCachedFormulaResultType()) ||
				CellType.STRING.equals(cell.getCachedFormulaResultType()) ||
				CellType.BLANK.equals(cell.getCachedFormulaResultType()))
			return TypeFacility.RUNTIME_NAMES.STRING;

		if (CellType.BOOLEAN.equals(cell.getCachedFormulaResultType()))
			return TypeFacility.RUNTIME_NAMES.BOOLEAN;

		if (DateUtil.isCellDateFormatted(cell))
			return getExactDateFormat(cell);

		return TypeFacility.RUNTIME_NAMES.DOUBLE;
	}

	private String getExactDateFormat(Cell cell) {
		ExcelNumberFormat nf = ExcelNumberFormat.from(cell, null);
		return findTypeFromDateFormat(nf.getIdx(), nf.getFormat());
	}

	/**
	 * logic from org.apache.poi.ss.usermodel.DateUtil class, method isADateFormat
	 * @param formatIndex
	 * @param formatString
	 * @return
	 */
	public static String findTypeFromDateFormat(int formatIndex, String formatString) {
		String type = isInternalDateFormat(formatIndex);
		if (type != null) {
			return type;
		} else if (formatString != null && formatString.length() != 0) {
			String fs = formatString;
			int length = formatString.length();
			StringBuilder sb = new StringBuilder(length);

			int separatorIndex;
			for(separatorIndex = 0; separatorIndex < length; ++separatorIndex) {
				char c = fs.charAt(separatorIndex);
				if (separatorIndex < length - 1) {
					char nc = fs.charAt(separatorIndex + 1);
					if (c == '\\') {
						switch(nc) {
							case ' ':
							case ',':
							case '-':
							case '.':
							case '\\':
								continue;
						}
					} else if (c == ';' && nc == '@') {
						++separatorIndex;
						continue;
					}
				}

				sb.append(c);
			}

			fs = sb.toString();
			if (date_ptrn4.matcher(fs).matches() || systemTimePattern.matcher(fs).matches()) {
				return TypeFacility.RUNTIME_NAMES.TIME;
			} else {
				fs = date_ptrn5.matcher(fs).replaceAll("");
				fs = date_ptrn1.matcher(fs).replaceAll("");
				fs = date_ptrn2.matcher(fs).replaceAll("");
				separatorIndex = fs.indexOf(59);
				if (0 < separatorIndex && separatorIndex < fs.length() - 1) {
					fs = fs.substring(0, separatorIndex);
				}

				if (!date_ptrn3a.matcher(fs).find()) {
					return TypeFacility.RUNTIME_NAMES.DATE;
				} else if (date_ptrn3b.matcher(fs).matches()) {
					return TypeFacility.RUNTIME_NAMES.TIMESTAMP;
				}
			}
		}

		return TypeFacility.RUNTIME_NAMES.TIMESTAMP;
	}

	/**
	 * http://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/BuiltinFormats.html
	 * @param format
	 * @return data type
	 */
	public static String isInternalDateFormat(int format) {
		switch(format) {
			case 14:
			case 15:
			case 16:
			case 17:
				return TypeFacility.RUNTIME_NAMES.DATE;
			case 18:
			case 19:
			case 20:
			case 21:
			case 45:
			case 46:
			case 47:
				return TypeFacility.RUNTIME_NAMES.TIME;
			case 22:
				return TypeFacility.RUNTIME_NAMES.TIMESTAMP;
			default:
				return null;
		}
	}
}
