/*
 * Copyright Red Hat, Inc. and/or its affiliates
 * and other contributors as indicated by the @author tags and
 * the COPYRIGHT.txt file distributed with this work.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.teiid.translator.excel;


import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.SQLXML;
import java.sql.Timestamp;
import java.util.Date;
import java.util.Iterator;
import java.util.concurrent.atomic.AtomicInteger;

import javax.resource.ResourceException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.teiid.core.types.BlobImpl;
import org.teiid.core.types.BlobType;
import org.teiid.core.types.ClobImpl;
import org.teiid.core.types.ClobType;
import org.teiid.core.types.DataTypeManager;
import org.teiid.core.types.InputStreamFactory;
import org.teiid.core.types.SQLXMLImpl;
import org.teiid.core.types.TransformationException;
import org.teiid.core.types.XMLType;
import org.teiid.core.util.TimestampWithTimezone;
import org.teiid.language.LanguageObject;
import org.teiid.metadata.RuntimeMetadata;
import org.teiid.translator.DataNotAvailableException;
import org.teiid.translator.Execution;
import org.teiid.translator.ExecutionContext;
import org.teiid.translator.FileConnection;
import org.teiid.translator.TranslatorException;


public class BaseExcelExecution implements Execution {
	@SuppressWarnings("unused")
	protected ExecutionContext executionContext; 
	@SuppressWarnings("unused")
	protected RuntimeMetadata metadata; 
	protected FileConnection connection;
    
    // Execution state
	protected Iterator<Row> rowIterator;
	private Row currentRow;
	private File[] xlsFiles;
	private AtomicInteger fileCount = new AtomicInteger();
	protected ExcelQueryVisitor visitor = new ExcelQueryVisitor();
	protected FormulaEvaluator evaluator;
	private DataFormatter dataFormatter;
    protected Workbook workbook;

	public BaseExcelExecution(ExecutionContext executionContext,
			RuntimeMetadata metadata, FileConnection connection) {
		this.executionContext = executionContext;
        this.metadata = metadata;
        this.connection = connection;
    }
	
	public void visit(LanguageObject command) throws TranslatorException {
	    this.visitor.visitNode(command);
        
        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, true);
			this.rowIterator = readXLSFile(xlsFiles[fileCount.getAndIncrement()]);
		} catch (ResourceException e) {
			throw new TranslatorException(e);
		}    
    }

	private Iterator<Row> readXLSFile(File xlsFile) throws TranslatorException {
		try (FileInputStream xlsFileStream = new FileInputStream(xlsFile);) {
		    xlsFileStream.getChannel().tryLock(0, Long.MAX_VALUE, true);
			String extension = ExcelMetadataProcessor.getFileExtension(xlsFile);
			if (extension.equalsIgnoreCase("xls")) { //$NON-NLS-1$
				workbook = new HSSFWorkbook(xlsFileStream);
			}
			else if (extension.equalsIgnoreCase("xlsx")) { //$NON-NLS-1$
				workbook = new XSSFWorkbook(xlsFileStream);
			}
			else {
				throw new TranslatorException(ExcelPlugin.Event.TEIID23000, ExcelPlugin.Util.gs(ExcelPlugin.Event.TEIID23000));
			}
			Sheet sheet = workbook.getSheet(this.visitor.getSheetName());
			this.evaluator = 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()) {
					Row row = rowIter.next();
					if (row.getRowNum() >= this.visitor.getFirstDataRowNumber()) {
					    this.currentRow = row;
					    break;
					}
				}
			}
			return rowIter;
		} catch (IOException e) {
			throw new TranslatorException(e);
		}
	}

    public Row nextRow() throws TranslatorException, DataNotAvailableException {
        while (true) {
        	Row row = nextRowInternal();
        	if (row == null) {
        	    return null;
        	}
        	
        	// when the first cell number is -1, then it is empty row, skip it
        	if (row.getFirstCellNum() == -1) {
        		continue;
        	}
        	
        	if (!this.visitor.allows(row.getRowNum())) {
        		continue;
        	}
        	return row;
        }
    }
    
    private Row nextRowInternal() throws TranslatorException {
        Row row = null;
        if (this.currentRow != null) {
            row = this.currentRow;
            this.currentRow = null;
            return row;
        }
    	
    	boolean hasNext = false;
    	if (this.rowIterator != null) {
    		hasNext = this.rowIterator.hasNext();
    	}
    	
    	while (!hasNext) {
    		this.rowIterator = null;
    		File nextXlsFile = getNextXLSFile();
    		if (nextXlsFile == null) {
    		    break;
    		}
			this.rowIterator = readXLSFile(nextXlsFile);
			hasNext = this.rowIterator.hasNext();
    	}
        if (hasNext) {
            row = this.rowIterator.next();
        }
        return row;
    }
    
    /**
     * @throws TranslatorException  
     */
    protected File getNextXLSFile() throws TranslatorException {
    	if (this.xlsFiles.length > this.fileCount.get()) {
    		return this.xlsFiles[this.fileCount.getAndIncrement()];
    	}
    	return null;
    }
    
    protected File getCurrentXLSFile() {
        if (this.xlsFiles.length >= this.fileCount.get()) {
            return this.xlsFiles[this.fileCount.get()-1];
        }
        return null;
    }
    
    Object convertFromExcelType(final boolean value, final Class<?> expectedType) throws TranslatorException {
        if (expectedType.isAssignableFrom(Boolean.class)) {
            return value;
        }
        
        try {
            return DataTypeManager.transformValue(value, expectedType);
        } catch (TransformationException e) {
            throw new TranslatorException(e);
        }
    }
    
    Object convertFromExcelType(final Double value, Cell cell, final Class<?> expectedType) throws TranslatorException {
		if (value == null) {
			return null;
		}

		if (expectedType.isAssignableFrom(Double.class)) {
			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 TimestampWithTimezone.createDate(date);
		}
		else if (expectedType.isAssignableFrom(java.sql.Time.class)) {
			Date date = cell.getDateCellValue();
			return TimestampWithTimezone.createTime(date);
		}
		
		if (expectedType == String.class && dataFormatter != null) {
		    return dataFormatter.formatCellValue(cell);
		}
		
		Object val = value;
		
		if (DateUtil.isCellDateFormatted(cell)) {
		    Date date = cell.getDateCellValue();
            val = new java.sql.Timestamp(date.getTime());
		}
		
		try {
			return DataTypeManager.transformValue(val, expectedType);
		} catch (TransformationException e) {
			throw new TranslatorException(e);
		}
    }
    
    static Object convertFromExcelType(final String value, final Class<?> expectedType) throws TranslatorException {
		if (value == null) {
			return null;
		}

		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));
		} 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()));
		}
	}    
    
    @Override
    public void close() {
    }

    @Override
    public void cancel() throws TranslatorException {

    }
    
    public void setDataFormatter(DataFormatter dataFormatter) {
        this.dataFormatter = dataFormatter;
    }
}
