/*
 * 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.
 */
package org.teiid.dqp.internal.process;

import static org.junit.Assert.*;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLXML;
import java.sql.Statement;
import java.util.logging.ConsoleHandler;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.sql.DataSource;

import org.h2.jdbcx.JdbcConnectionPool;
import org.h2.jdbcx.JdbcDataSource;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import org.teiid.core.util.UnitTestUtil;
import org.teiid.dqp.internal.datamgr.ConnectorManager;
import org.teiid.dqp.internal.datamgr.ConnectorManagerRepository;
import org.teiid.jdbc.FakeServer;
import org.teiid.translator.ExecutionFactory;
import org.teiid.translator.TranslatorException;
import org.teiid.translator.jdbc.h2.H2ExecutionFactory;
@SuppressWarnings("nls")
public class TestRelate {

	private static boolean writeResults = false;
	private static boolean DEBUG = false;
	private static FakeServer server;
    
    @BeforeClass public static void oneTimeSetUp() throws Exception {
    	server = new FakeServer(true);
    	JdbcDataSource h2ds = new JdbcDataSource();
    	h2ds.setURL("jdbc:h2:zip:"+UnitTestUtil.getTestDataFile("relate/test.zip").getAbsolutePath()+"!/test;");
    	final DataSource ds = JdbcConnectionPool.create(h2ds);
		ExecutionFactory h2 = new H2ExecutionFactory();
		h2.start();
		ConnectorManagerRepository cmr = new ConnectorManagerRepository();
		ConnectorManager cm = new ConnectorManager("source", "bar", h2) {
			@Override
			public Object getConnectionFactory() throws TranslatorException {
				return ds;
			}
		};
		cmr.addConnectorManager("source", cm);
		server.setConnectorManagerRepository(cmr);
		server.deployVDB("VehicleRentalsVDB", UnitTestUtil.getTestDataPath()+"/relate/VehicleRentalsVDB.vdb");
		if (DEBUG) {
	    	Logger logger = Logger.getLogger("org.teiid");
	    	logger.setLevel(Level.FINER);
	    	ConsoleHandler handler = new ConsoleHandler();
	    	handler.setLevel(Level.FINER);
	    	logger.addHandler(handler);
    	}
    }
    
    @AfterClass public static void oneTimeTearDown() throws Exception {
    	server.stop();
    }
    
	private void compareResults(SQLXML[] docs)
	throws SQLException, IOException {
		StackTraceElement ste = new Exception().getStackTrace()[1];
    	String testName = ste.getMethodName();
    	testName = "relate/" + testName; //$NON-NLS-1$
        File actual = new File(UnitTestUtil.getTestDataPath() + "/" +testName+".expected"); //$NON-NLS-1$ //$NON-NLS-2$
        if (writeResults) {
	        PrintWriter writer = new PrintWriter(actual);
			for (SQLXML xml : docs) {
				writer.write(xml.getString());
				writer.write('\n');
			}
			writer.close();
			return;
        }
        BufferedReader br = new BufferedReader(new FileReader(actual));
		for (int i = 0; i < docs.length; i++) {
			assertEquals(br.readLine(), docs[i].getString());
		}
	}

    @Test public void testCase3365() throws Exception{
    	SQLXML[] docs = relate(false, null, null, null, 
    			"rentals.rentsVehicle", "rentals.company", "rentals.vehicle", 
    			"rentals.company.companyId", "'*:rentals/*:company/@companyID'", "STRING", "rentals.rentsVehicle.companyId", 
    			"rentals.rentsVehicle.vehicleId", "'*:rentals/*:rentsVehicle/@vehicleID'", "STRING", "rentals.vehicle.vehicleId", 
    			"VehicleRentalsDoc.rentalsDocumentWithLocation",
    			null, 
    			null, 
    			null, 
    			null, 
    			null, null, null, 
    			null, null, null);
    	compareResults(docs);
    }
    
    @Test public void testCase3365_crit() throws Exception{
    	SQLXML[] docs = relate(false, null, null, null, 
    			"rentals.rentsVehicle", "rentals.company", "rentals.vehicle", 
    			"rentals.company.companyId", "'*:rentals/*:company/@companyID'", "STRING", "rentals.rentsVehicle.companyId", 
    			"rentals.rentsVehicle.vehicleId", "'*:rentals/*:rentsVehicle/@vehicleID'", "STRING", "rentals.vehicle.vehicleId", 
    			"VehicleRentalsDoc.rentalsDocumentWithLocation",
    			null, 
    			"company.companyid = 'CID1'", 
    			null, 
    			null, 
    			null, null, null, 
    			null, null, null);
    	compareResults(docs);
    }
    
    @Test public void testCase3365_critNestedSrc() throws Exception {
    	SQLXML[] docs = relate(false, null, null, null, 
    			"rentals.rentsVehicle", "rentals.company", "rentals.vehicle", 
    			"rentals.company.companyId", "'*:rentals/*:company/@companyID'", "STRING", "rentals.rentsVehicle.companyId", 
    			"rentals.rentsVehicle.vehicleId", "'*:rentals/*:rentsVehicle/@vehicleID'", "STRING", "rentals.vehicle.vehicleId", 
    			"VehicleRentalsDoc.rentalsDocumentWithLocation",
    			null, 
    			"location = 'Pittsburgh'", 
    			null, 
    			null, 
    			null, null, null, 
    			null, null, null);
    	compareResults(docs);
    }
    
    @Test public void testCase3365_critNestedSrcContext() throws Exception {
    	SQLXML[] docs = relate(false, null, null, null, 
    			"rentals.rentsVehicle", "rentals.company", "rentals.vehicle", 
    			"rentals.company.companyId", "'*:rentals/*:company/@companyID'", "STRING", "rentals.rentsVehicle.companyId", 
    			"rentals.rentsVehicle.vehicleId", "'*:rentals/*:rentsVehicle/@vehicleID'", "STRING", "rentals.vehicle.vehicleId", 
    			"VehicleRentalsDoc.rentalsDocumentWithLocation",
    			null, 
    			null, 
    			null, 
    			null, 
    			null, "context(location, location) = 'Pittsburgh'", null, 
    			null, null, null);
    	compareResults(docs);
    }
    
    @Test public void testCase3365_critNestedSrcCombinationContext() throws Exception {
    	SQLXML[] docs = relate(false, null, null, null, 
    			"rentals.rentsVehicle", "rentals.company", "rentals.vehicle", 
    			"rentals.company.companyId", "'*:rentals/*:company/@companyID'", "STRING", "rentals.rentsVehicle.companyId", 
    			"rentals.rentsVehicle.vehicleId", "'*:rentals/*:rentsVehicle/@vehicleID'", "STRING", "rentals.vehicle.vehicleId", 
    			"VehicleRentalsDoc.rentalsDocumentWithLocation",
    			null, 
    			"location = 'Pittsburgh'", 
    			null, 
    			null, 
    			null, "context(location, location) = 'Pittsburgh'", null, 
    			null, null, null);
    	compareResults(docs);
    }
    
    @Test public void testCase3365_critNestedTgt() throws Exception {
    	SQLXML[] docs = relate(false, null, null, null, 
    			"rentals.rentsVehicle", "rentals.company", "rentals.vehicle", 
    			"rentals.company.companyId", "'*:rentals/*:company/@companyID'", "STRING", "rentals.rentsVehicle.companyId", 
    			"rentals.rentsVehicle.vehicleId", "'*:rentals/*:rentsVehicle/@vehicleID'", "STRING", "rentals.vehicle.vehicleId", 
    			"VehicleRentalsDoc.rentalsDocumentWithLocation",
    			null, 
    			null, 
    			"color = 'Black'", 
    			null, 
    			null, null, null, 
    			null, null, null);
    	compareResults(docs);
    }
    
    @Test public void testCase3365_compoundCritNestedTgt() throws Exception {
    	SQLXML[] docs = relate(false, null, null, null, 
    			"rentals.rentsVehicle", "rentals.company", "rentals.vehicle", 
    			"rentals.company.companyId", "'*:rentals/*:company/@companyID'", "STRING", "rentals.rentsVehicle.companyId", 
    			"rentals.rentsVehicle.vehicleId", "'*:rentals/*:rentsVehicle/@vehicleID'", "STRING", "rentals.vehicle.vehicleId", 
    			"VehicleRentalsDoc.rentalsDocumentWithLocation",
    			null, 
    			null, 
    			"color='Black' or color='Puce'", 
    			null, 
    			null, null, null, 
    			null, null, null);
    	compareResults(docs);
    }
    
    @Test public void testSharesDoc() throws Exception {
    	SQLXML[] docs = relate(true, null, null, null, 
    			"rentals.hasSharingAgreement", "rentals.company", "rentals.company", 
    			"rentals.company.companyId", "'*:rentals/*:company/@companyID'", "STRING", "rentals.hasSharingAgreement.primaryPartyID", 
    			"rentals.hasSharingAgreement.secondaryPartyID", "'*:rentals/*:hasSharingAgreement/@secondaryPartyID'", "STRING", "rentals.company.companyId", 
    			"VehicleRentalsDoc.companiesDocument",
    			null, 
    			null, 
    			null, 
    			null, 
    			null, null, null, 
    			null, null, null);
    	compareResults(docs);
    }
    
    @Test public void testSharesDocWithCritTgt() throws Exception {
    	SQLXML[] docs = relate(true, null, null, null, 
    			"rentals.hasSharingAgreement", "rentals.company", "rentals.company", 
    			"rentals.company.companyId", "'*:rentals/*:company/@companyID'", "STRING", "rentals.hasSharingAgreement.primaryPartyID", 
    			"rentals.hasSharingAgreement.secondaryPartyID", "'*:rentals/*:hasSharingAgreement/@secondaryPartyID'", "STRING", "rentals.company.companyId", 
    			"VehicleRentalsDoc.companiesDocument",
    			null, 
    			null, 
    			"rentals.company.name like 'B%'", 
    			null, 
    			null, null, null, 
    			null, null, null);
    	compareResults(docs);
    }
    
    @Test public void testSharesDocWithCritTgtContext() throws Exception {
    	SQLXML[] docs = relate(true, null, null, null, 
    			"rentals.hasSharingAgreement", "rentals.company", "rentals.company", 
    			"rentals.company.companyId", "'*:rentals/*:company/@companyID'", "STRING", "rentals.hasSharingAgreement.primaryPartyID", 
    			"rentals.hasSharingAgreement.secondaryPartyID", "'*:rentals/*:hasSharingAgreement/@secondaryPartyID'", "STRING", "rentals.company.companyId", 
    			"VehicleRentalsDoc.companiesDocument",
    			null, 
    			null, 
    			"rentals.company.name like 'B%'", 
    			"rentals.company.name like 'B%'", 
    			null, null, null, 
    			null, null, null);
    	compareResults(docs);
    }
    
    @Test public void testSharesDocEspaceQuestion3() throws Exception {
    	SQLXML[] docs = relate(true, null, null, null, 
    			"rentals.hasSharingAgreement", "rentals.company", "rentals.company", 
    			"rentals.company.companyId", "'*:rentals/*:company/@companyID'", "STRING", "rentals.hasSharingAgreement.primaryPartyID", 
    			"rentals.hasSharingAgreement.secondaryPartyID", "'*:rentals/*:hasSharingAgreement/@secondaryPartyID'", "STRING", "rentals.company.companyId", 
    			"VehicleRentalsDoc.companiesDocument",
    			null, 
    			"company.locations.location = 'Pittsburgh'", 
    			null, 
    			null, 
    			null, null, null, 
    			null, null, null);
    	compareResults(docs);
    }
    
    public SQLXML[] relate(
    		//distinct is only meaningful for self-relationships
    		boolean distinct,
    		//select args to limit the xml projection
    		//there is an assumption that the source/relationship select must project key values
    		String relationshipSelect, String sourceSelect, String targetSelect,  
    		//relevant contexts, the same as expected by the legacy relate function
    		String relationshipContext, String sourceContext, String targetContext, 
    		//break down of the relationship predicates
    		String sourceKey, 
     		  String sourceKeyPath,
    		  String sourceKeyType,
    		  String sourceFKey,
    		String targetFKey, 
    		String targetFKeyPath,
    		  String targetFKeyType,
    		  String targetKey,
    		//target document
    		String xmlDocument,
    		//explicit/implicit relationship context criteria - should not use the context function 
    		String relationshipCriteria,
    		//relateSource/implicit/explicit source context criteria - should not use the context function
    		String relateSourceCriteria, 
    		//relateTarget criteria - should not use the context function
    		String relateTargetCriteria, 
    		//relateTarget context criteria - should not use the context function
    		String relateTargetContextCriteria,
    		//subcontext criteria, logically applied after the relate operation - should use context function(s), and should not be specified against a root context
    		String relationshipContextCriteria,
    		String sourceContextCriteria,
    		String targetContextCriteria,
    		//order bys
    		String relationshipOrderBy,
    		String sourceOrderBy,
    		String targetOrderBy
    		) throws Exception {
    	if (sourceSelect == null) {
    		sourceSelect = sourceContext + ".*";
    	}
    	if (relationshipSelect == null) {
    		relationshipSelect = relationshipContext + ".*";
    	}
    	if (targetSelect == null) {
    		targetSelect = targetContext + ".*";
    	}
    	Connection conn = server.createConnection("jdbc:teiid:VehicleRentalsVDB"); //$NON-NLS-1$
    	if (DEBUG) {
    		conn.createStatement().execute("SET SHOWPLAN DEBUG");
    	}
    	SQLXML[] result = new SQLXML[3];
    	//source query
    	String query = String.format("SELECT %s FROM %s WHERE CONTEXT(%s, %s) IN (SELECT %s FROM %s WHERE %s IN (SELECT %s FROM %s", sourceSelect, xmlDocument, sourceContext, sourceKey, sourceFKey, relationshipContext, targetFKey, targetKey, targetContext);
    	if (relateTargetCriteria != null) {
    		query += (" WHERE " + relateTargetCriteria);
    	}
    	query += "))";
    	if (relateSourceCriteria != null) {
    		query += String.format(" AND (CONTEXT(%s, %s) = null OR %s)", sourceContext, sourceKey, relateSourceCriteria);
    	}
    	if (relationshipCriteria != null) {
    		query += String.format(" AND CONTEXT(%s, %s) IN (SELECT %s FROM %s WHERE AND %s)", sourceContext, sourceKey, sourceFKey, relationshipContext, relationshipCriteria);
    	}
    	if (sourceContextCriteria != null) {
    		query += (" AND " + sourceContextCriteria);
    	}
    	if (sourceOrderBy != null) {
    		query += (" ORDER BY " + sourceOrderBy);
    	}
    	PreparedStatement sourcePs = conn.prepareStatement(query);
    	ResultSet sourceRs = sourcePs.executeQuery();
    	sourceRs.next();
    	SQLXML sourceXml = sourceRs.getSQLXML(1);
    	result[0] = sourceXml;
    	
    	Statement ddlStmt = conn.createStatement();
    	
    	ddlStmt.execute("CREATE LOCAL TEMPORARY TABLE #st_source (source_key STRING, PRIMARY KEY (source_key))");
    	//source key extraction
    	String sourceStagingQuery = String.format("INSERT INTO #st_source (source_key) SELECT DISTINCT source_key FROM XMLTABLE(%s PASSING cast(? AS xml) COLUMNS source_key %s PATH '.') x", sourceKeyPath, sourceKeyType);
    	PreparedStatement ps = conn.prepareStatement(sourceStagingQuery);
    	ps.setSQLXML(1, sourceXml);
    	ps.execute();
    	ps.close();
    	
    	//relationship query
    	String relQuery = String.format("SELECT %s FROM %s WHERE CONTEXT(%s, %s) IN /*+ DJ */ (SELECT source_key from #st_source) AND CONTEXT(%s, %s) IN (SELECT %s FROM %s", relationshipSelect, xmlDocument, relationshipContext, sourceFKey, relationshipContext, targetFKey, targetKey, targetContext);
    	if (relateTargetContextCriteria != null) {
    		relQuery += (" WHERE " + relateTargetContextCriteria);
    	}
    	relQuery += ")";
    	if (relationshipCriteria != null) {
    		relQuery += String.format(" AND (CONTEXT(%s, %s) = null OR %s)", relationshipContext, sourceFKey, relationshipCriteria);
    	}
    	if (relationshipContextCriteria != null) {
    		relQuery += (" AND " + relationshipContextCriteria);
    	}
    	if (relationshipOrderBy != null) {
    		relQuery += (" ORDER BY " + relationshipOrderBy);
    	}
    	PreparedStatement relStmt = conn.prepareStatement(relQuery);
    	ResultSet relRs = relStmt.executeQuery();
    	relRs.next();
    	SQLXML relXml = relRs.getSQLXML(1);
    	result[1] = relXml;
    	
    	ddlStmt.execute("CREATE LOCAL TEMPORARY TABLE #st_rel (target_key STRING, PRIMARY KEY (target_key))");
    	//target key extraction
    	String relStagingQuery = String.format("INSERT INTO #st_rel (target_key) SELECT DISTINCT target_key FROM XMLTABLE(%s PASSING cast(? AS xml) COLUMNS target_key %s PATH '.') x", targetFKeyPath, targetFKeyType);
    	PreparedStatement psRel = conn.prepareStatement(relStagingQuery);
    	psRel.setSQLXML(1, relXml);
    	psRel.execute();
    	psRel.close();
    	
    	//target query
    	String targetQuery = String.format("SELECT %s FROM %s WHERE CONTEXT(%s, %s) IN /*+ DJ */ (SELECT target_key FROM #st_rel", targetSelect, xmlDocument, targetContext, targetKey);
    	if (distinct && targetContext.equalsIgnoreCase(sourceContext)) {
    		targetQuery += " EXCEPT SELECT source_key FROM #st_source";
    	}
    	targetQuery += ")";
    	if (targetContextCriteria != null) {
    		targetQuery += (" AND " + targetContextCriteria);
    	}
    	if (targetOrderBy != null) {
    		targetQuery += (" ORDER BY " + targetOrderBy);
    	}
    	PreparedStatement targetStmt = conn.prepareStatement(targetQuery);
    	ResultSet taretRs = targetStmt.executeQuery();
    	taretRs.next();
    	SQLXML targetXml = taretRs.getSQLXML(1);
    	result[2] = targetXml;
    	
    	ddlStmt.execute("drop table #st_source");
    	ddlStmt.execute("drop table #st_rel");
    	
    	return result;
    }
    
}
