/*
 * 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$
 *  
 */
/*
 * EXTENSION
 * ISSUE: DVCORE-2521
 * FIXED BY: Roman Pedchenko
 * DATE: 2014-12-05
 * ADOPTED FROM: TEIID-2153, 2171, 2359, 2149, 2358, 2410, 2271, 2467, 2429, 2363, 2160, 2461, 2797, 2326, 1557
 * END EXTENSION
 */
/*
 * EXTENSION
 * ISSUE: DVCORE-2441
 * FIXED BY: Roman Pedchenko
 * DATE: 2014.10.23
 * ADOPTED FROM: https://issues.jboss.org/browse/TEIID-247, 2166, 2151, 2234, 2581, 2571, 2369, 1386, 2226, 2233, 2224, 2225, 2257
 * ADOPTED FROM: https://issues.jboss.org/browse/TEIID-2235, 2661, 2178, 2297
 * END EXTENSION
 */
// https://issues.redhat.com/browse/TEIID-2195
// https://issues.jboss.org/browse/TEIID-2181
// https://issues.jboss.org/browse/TEIID-4769
// https://issues.redhat.com/browse/TEIID-2450
// https://issues.redhat.com/browse/TEIID-2384
// https://issues.redhat.com/browse/TEIID-3300
// https://issues.redhat.com/browse/TEIID-3362
package org.teiid.systemmodel;

import static org.junit.jupiter.api.Assertions.*;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.ParameterMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

import org.junit.jupiter.api.AfterAll;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;
import org.teiid.adminapi.Model.Type;
import org.teiid.adminapi.impl.ModelMetaData;
import org.teiid.client.util.ResultsFuture;
import org.teiid.core.util.UnitTestUtil;
import org.teiid.jdbc.AbstractMMQueryTestCase;
import org.teiid.jdbc.AsynchPositioningException;
import org.teiid.jdbc.ContinuousStatementCallback;
import org.teiid.jdbc.FakeServer;
import org.teiid.jdbc.RequestOptions;
import org.teiid.jdbc.StatementCallback;
import org.teiid.jdbc.TeiidResultSet;
import org.teiid.jdbc.TeiidStatement;
import org.teiid.jdbc.TestMMDatabaseMetaData;


/**
 * Exercises each virtual table in the system model.
 */
@SuppressWarnings("nls")
public class TestSystemVirtualModel extends AbstractMMQueryTestCase {
	private static final String VDB = "PartsSupplier"; //$NON-NLS-1$
	
	private static FakeServer server;
    
    @BeforeAll public static void setup() throws Exception {
    	server = new FakeServer(true);
    	server.deployVDB(VDB, UnitTestUtil.getTestDataPath() + "/PartsSupplier.vdb");
		/*ModelMetaData mmd = new ModelMetaData();
	    mmd.setName("x");
	    mmd.setModelType(Type.VIRTUAL);
	    mmd.addSourceMetadata("DDL", "create view t as select 1");
	    ModelMetaData mmd1 = new ModelMetaData();
	    mmd1.setName("y");
	    mmd1.setModelType(Type.VIRTUAL);
	    mmd1.addSourceMetadata("DDL", "create view T as select 1");
	    server.deployVDB("test", mmd, mmd1);*/

	    ModelMetaData mmd2 = new ModelMetaData();
	    mmd2.setName("x");
	    mmd2.setModelType(Type.VIRTUAL);
	    mmd2.addSourceMetadata("DDL", "create view t (g geometry options (\"teiid_spatial:srid\" 3819)) as select null;");
	    server.deployVDB("test1", mmd2);
    }
    
    @AfterAll public static void teardown() throws Exception {
    	server.stop();
    }

	public TestSystemVirtualModel() {
		// this is needed because the result files are generated
		// with another tool which uses tab as delimiter
		super.DELIMITER = "\t"; //$NON-NLS-1$
	}
	
    @BeforeEach public void setUp() throws Exception {
    	this.internalConnection = server.createConnection("jdbc:datavirtuality:" + VDB); //$NON-NLS-1$ //$NON-NLS-2$	
   	}
   
    protected void checkResult(String testName, String query) throws Exception {
    	execute(query);
    	TestMMDatabaseMetaData.compareResultSet("TestSystemVirtualModel/" + testName, this.internalResultSet);
    }
    
	@Test public void testModels() throws Exception {
		checkResult("testSchemas", "select* from SYS.Schemas order by Name"); //$NON-NLS-1$ //$NON-NLS-2$
	}

	@Test public void testKeys() throws Exception {
		checkResult("testKeys", "select* from SYS.Keys order by Name"); //$NON-NLS-1$ //$NON-NLS-2$
	}

	@Test public void testGroups() throws Exception {
		checkResult("testTables", "select* from SYS.Tables order by Name"); //$NON-NLS-1$ //$NON-NLS-2$
	}

	@Test public void testDataTypes() throws Exception {
		checkResult("testDataTypes", "select * from SYS.DataTypes order by name"); //$NON-NLS-1$ //$NON-NLS-2$
		execute("select * from SYS.DataTypes where name = 'string'"); //$NON-NLS-1$ //$NON-NLS-2$
		assertRowCount(1);
		execute("select * from SYS.DataTypes where isstandard"); //$NON-NLS-1$ //$NON-NLS-2$
		assertTrue(getRowCount() >= 21);
	}

	@Test public void testProcedureParams() throws Exception {
		checkResult("testProcedureParams", "select * from SYS.ProcedureParams order by Name"); //$NON-NLS-1$ //$NON-NLS-2$
	}

	@Test public void testProcedures() throws Exception {
		checkResult("testProcedures", "select* from SYS.Procedures order by Name"); //$NON-NLS-1$ //$NON-NLS-2$
	}

	@Test public void testProperties() throws Exception {
		checkResult("testProperties", "select* from SYS.Properties"); //$NON-NLS-1$
	}

	@Test public void testVirtualDatabase() throws Exception {

		String[] expected = { "Name[string]	Version[string]	", "PartsSupplier	1", //$NON-NLS-1$ //$NON-NLS-2$

		};
		executeAndAssertResults("select* from SYS.VirtualDatabases", //$NON-NLS-1$
				expected);
	}

	@Test public void testKeyColumns() throws Exception {
		checkResult("testKeyColumns", "select* from SYS.KeyColumns order by Name, KeyName"); //$NON-NLS-1$ //$NON-NLS-2$
	}

	@Test public void testVDBResources() throws IOException, SQLException {
		execute("select * from vdbresources",new Object[] {}); //$NON-NLS-1$
		TestMMDatabaseMetaData.compareResultSet(this.internalResultSet);
	}

	@Test public void testColumns() throws Exception {
		checkResult("testColumns", "select* from SYS.Columns order by Name, uid"); //$NON-NLS-1$ //$NON-NLS-2$
	}

	@Test public void testTableType() throws Exception {

		String[] expected = { "Type[string]	", "Table\nView", }; //$NON-NLS-1$ //$NON-NLS-2$
		executeAndAssertResults(
				"select distinct Type from SYS.Tables order by Type", //$NON-NLS-1$
				expected);
	}

	@Test public void testTableIsSystem() throws Exception {
		checkResult("testTableIsSystem", "select Name from SYS.Tables where IsSystem = 'false' order by Name"); //$NON-NLS-1$ //$NON-NLS-2$
	}

	@Test public void testDefect12064() throws Exception {
		checkResult("testDefect12064", "select KeyName, RefKeyUID FROM SYS.KeyColumns WHERE RefKeyUID IS NULL order by KeyName"); //$NON-NLS-1$
	}

	@Test public void testReferenceKeyColumns() throws Exception {
		checkResult("testReferenceKeyColumns", "select* FROM SYS.ReferenceKeyColumns order by PKTABLE_NAME"); //$NON-NLS-1$ //$NON-NLS-2$
	}

	@Test public void testLogMsg() throws Exception {
		execute("call logMsg(level=>'DEBUG', context=>'org.teiid.foo', msg=>'hello world')"); //$NON-NLS-1$
	}
	
	@Test public void testLogMsg1() throws Exception {
		assertThrows(SQLException.class, () -> execute("call logMsg(level=>'foo', context=>'org.teiid.foo', msg=>'hello world')")); //$NON-NLS-1$
	}
	
	@Test public void testAsynch() throws Exception {
		Statement stmt = this.internalConnection.createStatement();
		TeiidStatement ts = stmt.unwrap(TeiidStatement.class);
		final ResultsFuture<Integer> result = new ResultsFuture<Integer>(); 
		ts.submitExecute("select * from sys.tables a, sys.tables b, sys.tables c", new StatementCallback() {
			int rowCount;
			@Override
			public void onRow(Statement s, ResultSet rs) {
				rowCount++;
				try {
					if (!rs.isLast()) {
						assertTrue(rs.unwrap(TeiidResultSet.class).available() > 0);
					}
					if (rowCount == 10000) {
						s.close();
					}
				} catch (AsynchPositioningException e) {
					try {
						assertEquals(0, rs.unwrap(TeiidResultSet.class).available());
					} catch (SQLException e1) {
						result.getResultsReceiver().exceptionOccurred(e1);
					}
				} catch (SQLException e) {
					result.getResultsReceiver().exceptionOccurred(e);
				}
			}
			
			@Override
			public void onException(Statement s, Exception e) {
				result.getResultsReceiver().exceptionOccurred(e);
			}
			
			@Override
			public void onComplete(Statement s) {
				result.getResultsReceiver().receiveResults(rowCount);
			}
		}, new RequestOptions());
		assertEquals(10000, result.get().intValue());
	}
	
	@Test public void testAsynchContinuousEmpty() throws Exception {
		Statement stmt = this.internalConnection.createStatement();
		TeiidStatement ts = stmt.unwrap(TeiidStatement.class);
		final ResultsFuture<Integer> result = new ResultsFuture<Integer>(); 
		ts.submitExecute("select * from SYS.Schemas where 1 = 0", new ContinuousStatementCallback() {
			
			int execCount;
			@Override
			public void onRow(Statement s, ResultSet rs) throws SQLException {
				fail();
			}
			
			@Override
			public void onException(Statement s, Exception e) {
				result.getResultsReceiver().exceptionOccurred(e);
			}
			
			@Override
			public void onComplete(Statement s) {
				result.getResultsReceiver().receiveResults(execCount);
			}
			
			@Override
			public void beforeNextExecution(Statement s) throws SQLException {
				execCount++;
				assertEquals(-1, s.getResultSet().unwrap(TeiidResultSet.class).available());
				if (execCount == 1024) {
					s.close();
				}
			}
		}, new RequestOptions().continuous(true));
		assertEquals(1024, result.get().intValue());
	}
	
	@Test public void testAsynchContinuousNonEmpty() throws Exception {
		Statement stmt = this.internalConnection.createStatement();
		TeiidStatement ts = stmt.unwrap(TeiidStatement.class);
		final ResultsFuture<Integer> result = new ResultsFuture<Integer>(); 
		ts.submitExecute("select 1", new ContinuousStatementCallback() {
			
			int execCount;
			@Override
			public void onRow(Statement s, ResultSet rs) throws SQLException {
				assertEquals(0, rs.unwrap(TeiidResultSet.class).available());
				s.close();
			}
			
			@Override
			public void onException(Statement s, Exception e) {
				result.getResultsReceiver().exceptionOccurred(e);
			}
			
			@Override
			public void onComplete(Statement s) {
				result.getResultsReceiver().receiveResults(execCount);
			}
			
			@Override
			public void beforeNextExecution(Statement s) throws SQLException {
				execCount++;
			}
		}, new RequestOptions().continuous(true));
		assertEquals(0, result.get().intValue());
	}
	
	@Test public void testAsynchContinuous() throws Exception {
		Statement stmt = this.internalConnection.createStatement();
		TeiidStatement ts = stmt.unwrap(TeiidStatement.class);
		final ResultsFuture<Integer> result = new ResultsFuture<Integer>(); 
		ts.submitExecute("select xmlelement(name x) from SYS.Schemas", new StatementCallback() {
			int rowCount;
			@Override
			public void onRow(Statement s, ResultSet rs) throws SQLException {
				rowCount++;
				if (rowCount == 1024) {
					s.close();
				}
			}
			
			@Override
			public void onException(Statement s, Exception e) {
				result.getResultsReceiver().exceptionOccurred(e);
			}
			
			@Override
			public void onComplete(Statement s) {
				result.getResultsReceiver().receiveResults(rowCount);
			}
		}, new RequestOptions().continuous(true));
		assertEquals(1024, result.get().intValue());
	}
	
	@Test public void testCallableParametersByName() throws Exception {
		CallableStatement cs = this.internalConnection.prepareCall("{? = call logMsg(?, ?, ?)}");
		ParameterMetaData pmd = cs.getParameterMetaData();
		assertEquals(3, pmd.getParameterCount());
		cs.registerOutParameter("logged", Types.BOOLEAN);
		//different case
		cs.setString("LEVEL", "DEBUG");
		try {
			//invalid param
			cs.setString("n", "");
			fail();
		} catch (SQLException e) {
		}
		cs.setString("context", "org.teiid.foo");
		cs.setString("msg", "hello world");
		cs.execute();
		assertEquals(cs.getBoolean(1), cs.getBoolean("logged"));
	}
	
	@Test public void testArrayAggType() throws Exception {
		String sql = "SELECT array_agg(name) from tables"; //$NON-NLS-1$
		checkResult("testArrayAggType", sql); //$NON-NLS-1$ //$NON-NLS-2$
	}
	
	@Test public void testPrefixSearches() throws Exception {
		this.execute("select name from schemas where ucase(name) >= 'BAZ_BAR' and ucase(name) <= 'A'");
		//should be 0 rows rather than an exception
		assertRowCount(0);

		this.execute("select name from schemas where upper(name) like 'ab[_'");
		//should be 0 rows rather than an exception
		assertRowCount(0);

		this.execute("select name from SYS.Columns WHERE TableName LIKE 'A\\_A\\_A\\_A\\_Z' ESCAPE '\\'");
		//should be 0 rows rather than an exception
		assertRowCount(0);

		ModelMetaData mmd = new ModelMetaData();
		mmd.setName("x");
		mmd.setModelType(Type.VIRTUAL);
		mmd.setSchemaSourceType("ddl");
		StringBuffer ddl = new StringBuffer();
		ddl.append("create view Z_Z_Z_Z_Z_Z_Z_Z_Z_Z_Z_Z_Z_Z_Z_Z_Z as select 1 as Z_Z_Z_Z_Z_Z_Z_Z_Z_Z_Z_Z_Z_Z_Z_Z_Z;\n");
		mmd.setSchemaText(ddl.toString());
		server.deployVDB("x", mmd);

		this.internalConnection.close();
		this.internalConnection = server.createConnection("jdbc:datavirtuality:x"); //$NON-NLS-1$ //$NON-NLS-2$

		this.execute("SELECT * FROM SYS.Columns e WHERE TableName LIKE 'Z%'");
		//should be 1 row rather than an empty result or exception
		assertRowCount(1);

		this.execute("SELECT * FROM SYS.Columns e WHERE TableName LIKE 'Z\\_Z%' ESCAPE '\\'");
		//should be 1 row rather than an empty result or exception
		assertRowCount(1);

		this.internalConnection.close();
		this.internalConnection = server.createConnection("jdbc:datavirtuality:" + VDB); //$NON-NLS-1$ //$NON-NLS-2$
	}

	@Test public void testFunctions() throws Exception {
		checkResult("testFunctions", "select * from functions"); //$NON-NLS-1$ //$NON-NLS-2$
	}

	@Test public void testFunctionParameters() throws Exception {
		checkResult("testFunctionParams", "select * from FunctionParams"); //$NON-NLS-1$ //$NON-NLS-2$
	}

	@Test public void testSpatial() throws Exception {
		checkResult("testSpatial", "select * from spatial_ref_sys"); //$NON-NLS-1$ //$NON-NLS-2$
	}

	@Test public void testGeometryColumns() throws Exception {
		this.internalConnection.close();
		this.internalConnection = server.createConnection("jdbc:datavirtuality:test1");
		checkResult("testGeometryColumns", "select * from GEOMETRY_COLUMNS"); //$NON-NLS-1$ //$NON-NLS-2$
	}

	@Test public void testArrayIterate() throws Exception {
		String sql = "select array_get(cast(x.col as string[]), 2) from (exec arrayiterate((('a', 'b'),('c','d')))) x"; //$NON-NLS-1$
		this.execute(sql);
		assertResults(new String[] {"expr1[string]", "b", "d"});

		sql = "select array_get(cast(x.col as string[]), 2) from (exec arrayiterate(null)) x"; //$NON-NLS-1$
		this.execute(sql);
		assertRowCount(0);

	}
}
