/*
 * 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-4995
package org.teiid.translator.excel;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.teiid.core.plugin.CorePlugin;
import org.teiid.core.types.*;
import org.teiid.language.Select;
import org.teiid.metadata.RuntimeMetadata;
import org.teiid.translator.*;

import javax.resource.ResourceException;
import java.io.*;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.SQLXML;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

public class ExcelExecution implements ResultSetExecution {
	@SuppressWarnings("unused")
	private ExecutionContext executionContext; 
	@SuppressWarnings("unused")
	private RuntimeMetadata metadata; 
	private FileConnection connection;
    
    // Execution state
	private Iterator<Row> rowIterator;
	private Row currentRow;
	private File[] xlsFiles;
	private AtomicInteger fileCount = new AtomicInteger();
	private ExcelQueryVisitor visitor;
	private FormulaEvaluator evaluator;
	private FileInputStream xlsFileStream;
	private Class<?>[] expectedColumnTypes;
	private Workbook workbook;
	private DataFormatter dataFormatter;
	private boolean allColumnsAsString;

	public ExcelExecution(Select query, ExecutionContext executionContext,
			RuntimeMetadata metadata, FileConnection connection)
			throws TranslatorException {

		this.executionContext = executionContext;
        this.metadata = metadata;
        this.connection = connection;
        this.expectedColumnTypes = query.getColumnTypes();
        this.visitor = new ExcelQueryVisitor();
		this.visitor.visitNode(query);
		
		if (!visitor.exceptions.isEmpty()) {
			throw visitor.exceptions.get(0);
		}
    }
    
    @Override
    public void execute() throws TranslatorException {
    	try {
			this.xlsFiles = FileConnection.Util.getFiles(this.visitor.getXlsPath(), this.connection);
			this.rowIterator = readXLSFile(xlsFiles[fileCount.getAndIncrement()]);
		} catch (ResourceException e) {
			throw new TranslatorException(e);
		}    
    }

	private Iterator<Row> readXLSFile(File xlsFile) throws TranslatorException {
		try {
			this.xlsFileStream = new FileInputStream(xlsFile);
			String extension = ExcelMetadataProcessor.getFileExtension(xlsFile);
			boolean useEventAPI = false;
			if (this.visitor.getUseEventApi() == ExcelMetadataProcessor.UseEventApi.AUTO || this.visitor.getUseEventApi() == ExcelMetadataProcessor.UseEventApi.FALSE) {
				if (extension.equalsIgnoreCase("xls")) { //$NON-NLS-1$
					this.workbook = new HSSFWorkbook(this.xlsFileStream);
				} else if (extension.equalsIgnoreCase("xlsx") ||
						extension.equalsIgnoreCase("xlsm")) { //$NON-NLS-1$
					if (this.visitor.getUseEventApi().isUse()) {
						this.workbook = ExcelMetadataProcessor.getStreamReader(this.visitor.getRowCacheSize(), this.visitor.getBufferSize(), this.xlsFileStream);
						useEventAPI = true;
					} else {
						this.workbook = new XSSFWorkbook(this.xlsFileStream);
					}
				} else {
					throw new TranslatorException(ExcelPlugin.Event.TEIID23000, ExcelPlugin.Util.gs(ExcelPlugin.Event.TEIID23000));
				}
			} else {
				this.workbook = ExcelMetadataProcessor.getStreamReader(this.visitor.getRowCacheSize(), this.visitor.getBufferSize(), this.xlsFileStream);
				useEventAPI = true;
			}
			Sheet sheet = workbook.getSheet(this.visitor.getSheetName());
			this.evaluator = useEventAPI ? null : workbook.getCreationHelper().createFormulaEvaluator();
			Iterator<Row> rowIter = sheet.iterator();
			
			// skip up to the first data row
			if (this.visitor.getFirstDataRowNumber() > 0 && rowIter != null) {
				while(rowIter.hasNext()) {
					this.currentRow = rowIter.next();
					if (this.currentRow.getRowNum() >= this.visitor.getFirstDataRowNumber()) {
						break;
					}
				}
			}
			return rowIter;
		} catch (IOException e) {
			throw new TranslatorException(e);
		}
	}

    @Override
    public List<?> next() throws TranslatorException, DataNotAvailableException {
        while (hasNext()) {
        	Row row = nextRow();
        	// when the first cell number is -1, then it is empty row, skip it
        	if (row.getFirstCellNum() == -1) {
        		continue;
        	}
			if (row.getRowNum() < this.visitor.getFirstDataRowNumber())
        		continue;														
        	
        	if (!this.visitor.allows(row.getRowNum())) {
        		continue;
        	}
        	return projectRow(row);
        }
        return null;
    }
    
    private boolean hasNext() throws TranslatorException {
    	if (this.currentRow != null) {
    		return true;
    	}
    	
    	boolean hasNext = false;
    	if (this.rowIterator != null) {
    		hasNext = this.rowIterator.hasNext();
    	}
    	
    	if (!hasNext) {
    		this.rowIterator = null;
    		File nextXlsFile = getNextXLSFile();
    		if (nextXlsFile != null) {
    			this.rowIterator = readXLSFile(nextXlsFile);
    			hasNext = this.rowIterator.hasNext();
    		}
    	}
    	return hasNext;
    }
    
    private File getNextXLSFile() {
    	if (this.xlsFiles.length > this.fileCount.get()) {
    		close();
    		return this.xlsFiles[this.fileCount.getAndIncrement()];
    	}
    	return null;
    }
    
    private Row nextRow() {
    	if (this.currentRow != null) {
    		Row row = this.currentRow;
    		this.currentRow = null;
    		return row;
    	}
    	Row row = null;
        if (this.rowIterator != null && this.rowIterator.hasNext()) {
        	row = this.rowIterator.next();
        }
    	return row;
    }

    /**
     * @param row
     */
    List<Object> projectRow(Row row) throws TranslatorException {

		if (this.visitor.getProjectedColumns().size() == 1 && this.visitor.getProjectedColumns().get(0) == -1
				&& this.visitor.getTable().getColumns().size() == 1) {
			return null;
		}

        ArrayList output = new ArrayList(this.visitor.getProjectedColumns().size());
        
        int id = row.getRowNum()+1;
        
        int i = -1;
        for (int index:this.visitor.getProjectedColumns()) {
        	
        	i++;
        	// check if the row is ROW_ID
        	if (index == -1) {
        		output.add(id);
        		continue;
        	}
        	
        	Cell cell = this.visitor.getUseEventApi().isUse() ? row.getCell(index-1) : row.getCell(index-1, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
        	if (cell == null) {
        		output.add(null);
        		continue;
        	}

        	if (evaluator != null) {
		        cell = this.evaluator.evaluateInCell(cell);
	        } else {
		        CellType cType = cell.getCellType();
		        if (cType == CellType.FORMULA) {
					handleFormulaTypeCell(cell, output, i);
					continue;
		        }
	        }
	        switch (cell.getCellType()) {
                case NUMERIC:
                    output.add(convertFromExcelType(cell.getNumericCellValue(), cell, this.expectedColumnTypes[i]));
                    break;
                case STRING:
				case BLANK:
                    output.add(convertFromExcelType(cell.getStringCellValue(), this.expectedColumnTypes[i]));
                    break;
                case BOOLEAN:
					output.add(convertFromExcelType(cell.getBooleanCellValue(), this.expectedColumnTypes[i]));
                    break;
                default:
                	output.add(null);
                    break;
            }   
        }
        
        return output;    
    }

	private void handleFormulaTypeCell(Cell cell, List<Object> output, int i) throws TranslatorException {
		switch (cell.getCachedFormulaResultType()) {
			case BOOLEAN:
				output.add(convertFromExcelType(cell.getBooleanCellValue(), this.expectedColumnTypes[i]));
				break;
			case NUMERIC:
				output.add(convertFromExcelType(cell.getNumericCellValue(), cell, this.expectedColumnTypes[i]));
				break;
			case STRING:
			case BLANK:
				output.add(convertFromExcelType(cell.getStringCellValue(), this.expectedColumnTypes[i]));
				break;
			case ERROR:
				output.add(cell.getStringCellValue());
				break;
			default:
				output.add(null);
				break;
		}
	}

	Object convertFromExcelType(final Double value, Cell cell, final Class<?> expectedType) throws TranslatorException {
		Object value2 = convertFromExcelType2(value, cell, expectedType);
		if (value2 != null && String.class.equals(expectedType))
			value2 = value2.toString();
		return value2;
	}

	Object convertFromExcelType2(final Double value, Cell cell, final Class<?> expectedType) throws TranslatorException {
		if (value == null) {
			return null;
		}
		validateDoubleConverting(value, cell, expectedType);

		if (expectedType == String.class && dataFormatter != null) {
			final String dateRegex = "^\\d{1,2}/\\d{1,2}/\\d{2,4}";
			String strDate = dataFormatter.formatCellValue(cell);
			if (strDate.matches(dateRegex + ".*")) {
				SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
				try {
					Date date = cell.getDateCellValue();
					return strDate.replaceAll(dateRegex, sdf.format(date).split(" ")[0]);
				} catch (Exception ignored) { }
			}
			return dataFormatter.formatCellValue(cell);
		}

		if (expectedType.isAssignableFrom(Double.class)
				|| expectedType == String.class && allColumnsAsString) {
			return value;
		}
		else if (expectedType.isAssignableFrom(Timestamp.class)) {
			Date date = cell.getDateCellValue();
			return new Timestamp(date.getTime());
		}
		else if (expectedType.isAssignableFrom(java.sql.Date.class)) {
			Date date = cell.getDateCellValue();
			return new java.sql.Date(date.getTime());
		}
		else if (expectedType.isAssignableFrom(java.sql.Time.class)) {
			Date date = cell.getDateCellValue();
			return new java.sql.Time(date.getTime());
		}

		Object val = value;

		if (DateUtil.isCellDateFormatted(cell)) {
			Date date = cell.getDateCellValue();
			val = new Timestamp(date.getTime());
		}

		try {
			return DataTypeManager.transformValue(val, expectedType);
		} catch (TransformationException e) {
			throw new TranslatorException(e);
		}
    }

	private void validateDoubleConverting(final Double value, final Cell cell, final Class<?> expectedType) throws TranslatorException {
		if (allColumnsAsString) {
			return;
		}
		if (DateUtil.isCellDateFormatted(cell)) {
			ExcelNumberFormat nf = ExcelNumberFormat.from(cell, null);
			String type = ExcelMetadataProcessor.findTypeFromDateFormat(nf.getIdx(), nf.getFormat());
			String expectedTypeName = expectedType.getName();
			expectedTypeName = expectedTypeName.substring(expectedTypeName.lastIndexOf(".") + 1);
			if (!type.equalsIgnoreCase(expectedTypeName)) {
				Object val = value;
				switch (type) {
					case TypeFacility.RUNTIME_NAMES.DATE:
						val = new java.sql.Date(cell.getDateCellValue().getTime());
						break;
					case TypeFacility.RUNTIME_NAMES.TIME:
						val = new java.sql.Time(cell.getDateCellValue().getTime());
						break;
					case TypeFacility.RUNTIME_NAMES.TIMESTAMP:
						val = new Timestamp(cell.getDateCellValue().getTime());
						break;
				}
				throwTranslatorException(val, expectedType);
			}
		} else if (expectedType == DataTypeManager.DefaultDataClasses.DATE
				|| expectedType == DataTypeManager.DefaultDataClasses.TIME
				|| expectedType == DataTypeManager.DefaultDataClasses.TIMESTAMP
				|| expectedType == DataTypeManager.DefaultDataClasses.BOOLEAN
				|| expectedType == DataTypeManager.DefaultDataClasses.STRING) {
			throwTranslatorException(value, expectedType);
		}
	}

	Object convertFromExcelType(final Boolean value, final Class<?> expectedType) throws TranslatorException {
		Object value2 = convertFromExcelType2(value, expectedType);
		if (value2 != null && String.class.equals(expectedType))
			value2 = value2.toString();
		return value2;
	}

	Object convertFromExcelType2(final Boolean value, final Class<?> expectedType) throws TranslatorException {
		if (value == null) {
			return null;
		}

		if (expectedType == DataTypeManager.DefaultDataClasses.DOUBLE
				|| expectedType == DataTypeManager.DefaultDataClasses.STRING && !allColumnsAsString) {
			throwTranslatorException(value, expectedType);
		}

		if (expectedType.isAssignableFrom(Boolean.class)) {
			return value;
		}

		try {
			return DataTypeManager.transformValue(value, expectedType);
		} catch (TransformationException e) {
			throw new TranslatorException(e);
		}
 	}
    
    static Object convertFromExcelType(final String value, final Class<?> expectedType) throws TranslatorException {
		if (value == null || value.isEmpty()) {
			return null;
		}

		if (expectedType == DataTypeManager.DefaultDataClasses.BOOLEAN) {
			throwTranslatorException(value, expectedType);
		}

		if (expectedType.isAssignableFrom(String.class)) {
			return value;
		}

		if (expectedType.isAssignableFrom(Blob.class)) {
			return new BlobType(new BlobImpl(new InputStreamFactory() {
				@Override
				public InputStream getInputStream() throws IOException {
					return new ByteArrayInputStream(value.getBytes());
				}

			}));
		} else if (expectedType.isAssignableFrom(Clob.class)) {
			return new ClobType(new ClobImpl(value.toCharArray()));
		} else if (expectedType.isAssignableFrom(SQLXML.class)) {
			return new XMLType(new SQLXMLImpl(value.getBytes()));
		} else if (DataTypeManager.isTransformable(String.class, expectedType)) {
			try {
				return DataTypeManager.transformValue(value, expectedType);
			} catch (TransformationException e) {
				throw new TranslatorException(e);
			}
		} else {
			throw new TranslatorException(ExcelPlugin.Event.TEIID23003, ExcelPlugin.Util.gs(ExcelPlugin.Event.TEIID23003, expectedType.getName()));
		}
	}    
    
	private static void throwTranslatorException(final Object value, final Class<?> expectedType) throws TranslatorException {
		Object[] params = new Object[]{value.getClass(), expectedType, String.valueOf(value)};
		throw new TranslatorException(
				new TransformationException(CorePlugin.Event.TEIID10076, CorePlugin.Util.gs(CorePlugin.Event.TEIID10076, params))
		);
	}

    @Override
    public void close() {
    	try {
		    if (this.workbook != null)
			    this.workbook.close();
			this.xlsFileStream.close();
		} catch (IOException e) {
		}
    }

    @Override
    public void cancel() throws TranslatorException {
		close();
    }
	public void setDataFormatter(DataFormatter dataFormatter) {
		this.dataFormatter = dataFormatter;
	}

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