/*
 * 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-3089
// https://issues.jboss.org/browse/TEIID-4995
// https://issues.redhat.com/browse/TEIID-3117
package org.teiid.translator.excel;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertTrue;

import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.TimeZone;

import org.junit.jupiter.api.Test;
import org.mockito.Mockito;
import org.teiid.cdk.api.TranslationUtility;
import org.teiid.core.util.TimestampWithTimezone;
import org.teiid.core.util.UnitTestUtil;
import org.teiid.language.Command;
import org.teiid.language.QueryExpression;
import org.teiid.query.metadata.TransformationMetadata;
import org.teiid.query.unittest.RealMetadataFactory;
import org.teiid.translator.ExecutionContext;
import org.teiid.translator.FileConnection;
import org.teiid.translator.ResultSetExecution;

@SuppressWarnings("nls")
public class TestExcelExecution {

	private ArrayList helpExecute(String ddl, FileConnection connection, String query) throws Exception {
		return helpExecute(ddl, connection, query, false, false);
	}

	private ArrayList helpExecute(String ddl, FileConnection connection, String query, boolean format, boolean allColumnsAsString) throws Exception {
		ExcelExecutionFactory translator = new ExcelExecutionFactory();
		translator.setFormatStrings(format);
		translator.setAllColumnsAsString(allColumnsAsString);
    	translator.start();
    	
    	TransformationMetadata metadata = RealMetadataFactory.fromDDL(ddl, "vdb", "excel");
    	TranslationUtility utility = new TranslationUtility(metadata);
		
		Command cmd = utility.parseCommand(query);
		ExecutionContext context = Mockito.mock(ExecutionContext.class);
		
		ResultSetExecution execution = translator.createResultSetExecution((QueryExpression)cmd, context, utility.createRuntimeMetadata(), connection);
		execution.execute();
		
		ArrayList results = new ArrayList();
		while (true) {
			List<?> row = execution.next();
			if (row == null) {
				break;
			}
			results.add(row);
		}
		return results;
	}
		
	@Test
	public void testExecutionNoDataNumberXLS() throws Exception {
		String ddl = "CREATE FOREIGN TABLE Sheet1 (\n" + 
				"	ROW_ID integer PRIMARY KEY OPTIONS (SEARCHABLE 'All_Except_Like', \"teiid_excel:CELL_NUMBER\" 'ROW_ID'),\n" + 
				"	column1 string OPTIONS (SEARCHABLE 'Unsearchable', \"teiid_excel:CELL_NUMBER\" '7'),\n" + 
				"	column2 string OPTIONS (SEARCHABLE 'Unsearchable', \"teiid_excel:CELL_NUMBER\" '8'),\n" + 
				"	column3 string OPTIONS (SEARCHABLE 'Unsearchable', \"teiid_excel:CELL_NUMBER\" '9')\n" + 
				") OPTIONS (\"teiid_excel:FILE\" 'names.xls', \"teiid_excel:USE_EVENT_API\" 'FALSE');";

    	FileConnection connection = Mockito.mock(FileConnection.class);
    	Mockito.when(connection.getFile("names.xls")).thenReturn(UnitTestUtil.getTestDataFile("names.xls"));

    	ArrayList results = helpExecute(ddl, connection, "select * from Sheet1", false, true);
    	assertEquals("[[13, FirstName, LastName, Age], [14, John, Doe, 44.0], [15, Jane, Smith, 40.0], [16, Matt, Liek, 13.0], [17, Sarah, Byne, 10.0], [18, Rocky, Dog, 3.0], [19, Total, null, 110.0]]", results.toString());

		results = helpExecute(ddl, connection, "select * from Sheet1", true, true);
		assertEquals("[[13, FirstName, LastName, Age], [14, John, Doe, 44], [15, Jane, Smith, 40], [16, Matt, Liek, 13], [17, Sarah, Byne, 10], [18, Rocky, Dog, 3], [19, Total, null, 110]]", results.toString());
	}
	
	@Test
	public void testExecutionNoDataNumberXLSX() throws Exception {
		String ddl = "CREATE FOREIGN TABLE Sheet1 (\n" + 
				"	ROW_ID integer PRIMARY KEY OPTIONS (SEARCHABLE 'All_Except_Like', \"teiid_excel:CELL_NUMBER\" 'ROW_ID'),\n" + 
				"	column1 string OPTIONS (SEARCHABLE 'Unsearchable', \"teiid_excel:CELL_NUMBER\" '1'),\n" + 
				"	column2 string OPTIONS (SEARCHABLE 'Unsearchable', \"teiid_excel:CELL_NUMBER\" '2'),\n" + 
				"	column3 string OPTIONS (SEARCHABLE 'Unsearchable', \"teiid_excel:CELL_NUMBER\" '3')\n" + 
				") OPTIONS (\"teiid_excel:FILE\" 'names.xlsx', \"teiid_excel:USE_EVENT_API\" 'FALSE');";

    	FileConnection connection = Mockito.mock(FileConnection.class);
    	Mockito.when(connection.getFile("names.xlsx")).thenReturn(UnitTestUtil.getTestDataFile("names.xlsx"));

    	ArrayList results = helpExecute(ddl, connection, "select * from Sheet1", false, true);
    	assertEquals("[[1, FirstName, LastName, Age], [2, John, Doe, 2.0], [3, Jane, Smith, null], [4, Matt, Liek, 13.0], [5, Sarah, Byne, 10.0], [6, Rocky, Dog, 3.0]]", results.toString());
	}

	@Test
	public void testExecutionWithDataNumberXLS() throws Exception {
		String ddl = "CREATE FOREIGN TABLE Sheet1 (\n" + 
				"	ROW_ID integer PRIMARY KEY OPTIONS (SEARCHABLE 'All_Except_Like', \"teiid_excel:CELL_NUMBER\" 'ROW_ID'),\n" + 
				"	column1 string OPTIONS (SEARCHABLE 'Unsearchable', \"teiid_excel:CELL_NUMBER\" '7'),\n" + 
				"	column2 string OPTIONS (SEARCHABLE 'Unsearchable', \"teiid_excel:CELL_NUMBER\" '8'),\n" + 
				"	column3 string OPTIONS (SEARCHABLE 'Unsearchable', \"teiid_excel:CELL_NUMBER\" '9')\n" + 
				") OPTIONS (\"teiid_excel:FILE\" 'names.xls', \"teiid_excel:FIRST_DATA_ROW_NUMBER\" '18', \"teiid_excel:USE_EVENT_API\" 'FALSE');";

    	FileConnection connection = Mockito.mock(FileConnection.class);
    	Mockito.when(connection.getFile("names.xls")).thenReturn(UnitTestUtil.getTestDataFile("names.xls"));

    	ArrayList results = helpExecute(ddl, connection, "select * from Sheet1", false, true);
    	assertEquals("[[18, Rocky, Dog, 3.0], [19, Total, null, 110.0]]", results.toString());

		results = helpExecute(ddl, connection, "select * from Sheet1", true, true);
		assertEquals("[[18, Rocky, Dog, 3], [19, Total, null, 110]]", results.toString());
	}
	
	@Test
	public void testExecutionWithDataNumberXLSX() throws Exception {
		String ddl = "CREATE FOREIGN TABLE Sheet1 (\n" + 
				"	ROW_ID integer PRIMARY KEY OPTIONS (SEARCHABLE 'All_Except_Like', \"teiid_excel:CELL_NUMBER\" 'ROW_ID'),\n" + 
				"	column1 string OPTIONS (SEARCHABLE 'Unsearchable', \"teiid_excel:CELL_NUMBER\" '1'),\n" + 
				"	column2 string OPTIONS (SEARCHABLE 'Unsearchable', \"teiid_excel:CELL_NUMBER\" '2'),\n" + 
				"	column3 string OPTIONS (SEARCHABLE 'Unsearchable', \"teiid_excel:CELL_NUMBER\" '3')\n" + 
				") OPTIONS (\"teiid_excel:FILE\" 'names.xlsx', \"teiid_excel:FIRST_DATA_ROW_NUMBER\" '6', \"teiid_excel:USE_EVENT_API\" 'FALSE');";

    	FileConnection connection = Mockito.mock(FileConnection.class);
    	Mockito.when(connection.getFile("names.xlsx")).thenReturn(UnitTestUtil.getTestDataFile("names.xlsx"));

    	ArrayList results = helpExecute(ddl, connection, "select * from Sheet1", false, true);
    	assertEquals("[[6, Rocky, Dog, 3.0]]", results.toString());
	}	
	
	@Test
	public void testExecutionWithDataNumberWithHeaderXLS() throws Exception {
		String ddl = "CREATE FOREIGN TABLE Sheet1 (\n" + 
				"	ROW_ID integer PRIMARY KEY OPTIONS (SEARCHABLE 'All_Except_Like', \"teiid_excel:CELL_NUMBER\" 'ROW_ID'),\n" + 
				"	FirstName string OPTIONS (SEARCHABLE 'Unsearchable', \"teiid_excel:CELL_NUMBER\" '7'),\n" + 
				"	LastName string OPTIONS (SEARCHABLE 'Unsearchable', \"teiid_excel:CELL_NUMBER\" '8'),\n" + 
				"	Age double OPTIONS (SEARCHABLE 'Unsearchable', \"teiid_excel:CELL_NUMBER\" '9')\n" + 
				") OPTIONS (\"teiid_excel:FILE\" 'names.xls', \"teiid_excel:FIRST_DATA_ROW_NUMBER\" '18', \"teiid_excel:USE_EVENT_API\" 'FALSE');";

    	FileConnection connection = Mockito.mock(FileConnection.class);
    	Mockito.when(connection.getFile("names.xls")).thenReturn(UnitTestUtil.getTestDataFile("names.xls"));

    	ArrayList results = helpExecute(ddl, connection, "select * from Sheet1");
    	assertEquals("[[18, Rocky, Dog, 3.0], [19, Total, null, 110.0]]", results.toString());
	}

	static String commonDDL = "CREATE FOREIGN TABLE Sheet1 (\n" + 
			"	ROW_ID integer PRIMARY KEY OPTIONS (SEARCHABLE 'All_Except_Like', \"teiid_excel:CELL_NUMBER\" 'ROW_ID'),\n" + 
			"	FirstName string OPTIONS (SEARCHABLE 'Unsearchable', \"teiid_excel:CELL_NUMBER\" '7'),\n" + 
			"	LastName string OPTIONS (SEARCHABLE 'Unsearchable', \"teiid_excel:CELL_NUMBER\" '8'),\n" + 
			"	Age double OPTIONS (SEARCHABLE 'Unsearchable', \"teiid_excel:CELL_NUMBER\" '9')\n" +
			") OPTIONS (\"teiid_excel:FILE\" 'names.xls', \"teiid_excel:FIRST_DATA_ROW_NUMBER\" '14', \"teiid_excel:USE_EVENT_API\" 'FALSE');";
	
	@Test
	public void testExecutionEquals() throws Exception {
    	FileConnection connection = Mockito.mock(FileConnection.class);
    	Mockito.when(connection.getFile("names.xls")).thenReturn(UnitTestUtil.getTestDataFile("names.xls"));

    	ArrayList results = helpExecute(commonDDL, connection, "select FirstName from Sheet1 WHERE ROW_ID=16");
    	assertEquals("[[Matt]]", results.toString());
	}
	
	@Test
	public void testExecutionGT() throws Exception {
    	FileConnection connection = Mockito.mock(FileConnection.class);
    	Mockito.when(connection.getFile("names.xls")).thenReturn(UnitTestUtil.getTestDataFile("names.xls"));

    	ArrayList results = helpExecute(commonDDL, connection, "select FirstName from Sheet1 WHERE ROW_ID>16");
    	assertEquals("[[Sarah], [Rocky], [Total]]", results.toString());
	}		
	
	@Test
	public void testExecutionGE() throws Exception {
    	FileConnection connection = Mockito.mock(FileConnection.class);
    	Mockito.when(connection.getFile("names.xls")).thenReturn(UnitTestUtil.getTestDataFile("names.xls"));

    	ArrayList results = helpExecute(commonDDL, connection, "select FirstName from Sheet1 WHERE ROW_ID>=16");
    	assertEquals("[[Matt], [Sarah], [Rocky], [Total]]", results.toString());
	}	
	
	@Test
	public void testExecutionLT() throws Exception {
    	FileConnection connection = Mockito.mock(FileConnection.class);
    	Mockito.when(connection.getFile("names.xls")).thenReturn(UnitTestUtil.getTestDataFile("names.xls"));

    	ArrayList results = helpExecute(commonDDL, connection, "select FirstName from Sheet1 WHERE ROW_ID < 16");
    	assertEquals("[[John], [Jane]]", results.toString());
	}	
	
	@Test
	public void testExecutionLE() throws Exception {
    	FileConnection connection = Mockito.mock(FileConnection.class);
    	Mockito.when(connection.getFile("names.xls")).thenReturn(UnitTestUtil.getTestDataFile("names.xls"));

    	ArrayList results = helpExecute(commonDDL, connection, "select FirstName from Sheet1 WHERE ROW_ID <= 16");
    	assertEquals("[[John], [Jane], [Matt]]", results.toString());
	}
	
	@Test
	public void testExecutionNE() throws Exception {
    	FileConnection connection = Mockito.mock(FileConnection.class);
    	Mockito.when(connection.getFile("names.xls")).thenReturn(UnitTestUtil.getTestDataFile("names.xls"));

    	ArrayList results = helpExecute(commonDDL, connection, "select FirstName from Sheet1 WHERE ROW_ID != 16");
    	assertEquals("[[John], [Jane], [Sarah], [Rocky], [Total]]", results.toString());
	}	
	
	@Test
	public void testExecutionLimit() throws Exception {
    	FileConnection connection = Mockito.mock(FileConnection.class);
    	Mockito.when(connection.getFile("names.xls")).thenReturn(UnitTestUtil.getTestDataFile("names.xls"));

    	ArrayList results = helpExecute(commonDDL, connection, "select FirstName from Sheet1 LIMIT 3,1");
    	assertEquals("[[Sarah]]", results.toString());
	}
	
	@Test
	public void testExecutionLimit2() throws Exception {
		FileConnection connection = Mockito.mock(FileConnection.class);
		Mockito.when(connection.getFile("names.xls")).thenReturn(UnitTestUtil.getTestDataFile("names.xls"));
		ArrayList results = helpExecute(commonDDL, connection, "select FirstName from Sheet1 LIMIT 1");
		assertEquals("[[John]]", results.toString());
	}

	@Test
	public void testExecutionAnd() throws Exception {
    	FileConnection connection = Mockito.mock(FileConnection.class);
    	Mockito.when(connection.getFile("names.xls")).thenReturn(UnitTestUtil.getTestDataFile("names.xls"));

    	ArrayList results = helpExecute(commonDDL, connection, "select FirstName from Sheet1 WHERE ROW_ID > 16 and ROW_ID < 18");
    	assertEquals("[[Sarah]]", results.toString());
	}
	
	@Test
	public void testExecutionIN() throws Exception {
    	FileConnection connection = Mockito.mock(FileConnection.class);
    	Mockito.when(connection.getFile("names.xls")).thenReturn(UnitTestUtil.getTestDataFile("names.xls"));

    	ArrayList results = helpExecute(commonDDL, connection, "select FirstName from Sheet1 WHERE ROW_ID IN (13, 18)");
    	assertEquals("[[John], [Total]]", results.toString());
	}

	@Test
	public void testTime() throws Exception {
		FileConnection connection = Mockito.mock(FileConnection.class);
		Mockito.when(connection.getFile("names.xls")).thenReturn(UnitTestUtil.getTestDataFile("names.xlsx"));
		String ddl = commonDDL.replace(") OPTIONS (", ",\"time\" time OPTIONS (SEARCHABLE 'Unsearchable', \"teiid_excel:CELL_NUMBER\" '4')\n) OPTIONS (");
		ddl = ddl.replace("14", "2");

		ArrayList results = helpExecute(ddl, connection, "select \"time\" from Sheet1", true, true);
		//typed as string with formatting - Excel format
		assertEquals("[10:12:14]", results.get(0).toString().toUpperCase(Locale.ROOT));

		ddl = ddl.replace("\"time\" time", "\"time\" string");
		try {
			results = helpExecute(ddl, connection, "select \"time\" from Sheet1", true, false);
		} catch (Exception e) {
			assertTrue(e.getMessage().contains("Invalid conversion from type"));
		}
		
		TimestampWithTimezone.resetCalendar(TimeZone.getTimeZone("America/New_York")); //$NON-NLS-1$
		try {
			results = helpExecute(ddl, connection, "select \"time\" from Sheet1", false, true);
			//typed as string without formatting
			assertEquals("[0.42516203703703703]", results.get(0).toString());
		} finally {
			TimestampWithTimezone.resetCalendar(null);
		}
	}

}
