/*
 * 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.query.optimizer;

import static org.teiid.query.optimizer.TestOptimizer.*;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.junit.Test;
import org.teiid.core.TeiidComponentException;
import org.teiid.core.TeiidProcessingException;
import org.teiid.query.metadata.QueryMetadataInterface;
import org.teiid.query.optimizer.TestOptimizer.ComparisonMode;
import org.teiid.query.optimizer.capabilities.BasicSourceCapabilities;
import org.teiid.query.optimizer.capabilities.FakeCapabilitiesFinder;
import org.teiid.query.optimizer.capabilities.SourceCapabilities.Capability;
import org.teiid.query.processor.ProcessorPlan;
import org.teiid.query.unittest.RealMetadataFactory;

@SuppressWarnings("nls")
public class TestInlineView  {
    
    public static FakeCapabilitiesFinder getInliveViewCapabilitiesFinder() {
    	FakeCapabilitiesFinder capFinder = new FakeCapabilitiesFinder();
        BasicSourceCapabilities caps = getTypicalCapabilities();
        caps.setCapabilitySupport(Capability.QUERY_FROM_INLINE_VIEWS, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_COUNT, true);
        caps.setCapabilitySupport(Capability.QUERY_AGGREGATES_MAX, true);
        caps.setCapabilitySupport(Capability.QUERY_SELECT_DISTINCT, true);
        caps.setCapabilitySupport(Capability.QUERY_FROM_JOIN_OUTER, true);
        caps.setCapabilitySupport(Capability.QUERY_UNION, true);
        caps.setCapabilitySupport(Capability.QUERY_SET_ORDER_BY, true);
        caps.setCapabilitySupport(Capability.QUERY_SEARCHED_CASE, true);
        caps.setFunctionSupport("concat", true); //$NON-NLS-1$
        caps.setFunctionSupport("convert", true); //$NON-NLS-1$
        caps.setFunctionSupport("case", true); //$NON-NLS-1$
        caps.setFunctionSupport("+", true); //$NON-NLS-1$
        capFinder.addCapabilities("BQT1", caps); //$NON-NLS-1$
        capFinder.addCapabilities("BQT2", caps); //$NON-NLS-1$
        return capFinder;
    }

	@Test public void testANSIJoinInlineView()  throws Exception {
		runTest(createANSIJoinInlineView());
	}	
	
	@Test public void testInlineView()  throws Exception {
		runTest(createInlineView());
	}
	
	@Test public void testInlineViewWithDistinctAndOrderBy() throws Exception {
		runTest(createInlineViewWithDistinctAndOrderBy());
	}	
	
	@Test public void testInlineViewOfVirtual() throws Exception{
		runTest(createInlineViewOfVirtual());
	}
	
	@Test public void testInlineViewWithOuterOrderAndGroup() throws Exception {
		runTest(createInlineViewWithOuterOrderAndGroup());
	}
	
	@Test public void testInlineViewsInUnions() throws Exception {
		runTest(crateInlineViewsInUnions());
	}
	
	@Test public void testUnionInInlineView() throws Exception{
		runTest(createUnionInInlineView());
	}	
	
	public static InlineViewCase createANSIJoinInlineView()  throws Exception {
		String userQuery = "select q1.a from (select count(bqt1.smalla.intkey) as a, bqt1.smalla.intkey from bqt1.smalla group by bqt1.smalla.intkey) q1 left outer join bqt1.smallb on q1.a = bqt1.smallb.intkey where q1.intkey = 1"; //$NON-NLS-1$
		String optimizedQuery = "SELECT v_0.c_0 FROM (SELECT COUNT(g_0.IntKey) AS c_0 FROM BQT1.SmallA AS g_0 WHERE g_0.IntKey = 1 GROUP BY g_0.IntKey) AS v_0 LEFT OUTER JOIN BQT1.SmallB AS g_1 ON v_0.c_0 = g_1.IntKey"; //$NON-NLS-1$
		
		List<List<?>> expectedResults = new ArrayList<List<?>>();
		expectedResults.add(Arrays.asList(1));
				
		Set<String> sourceQueries = new HashSet<String>();
        sourceQueries.add("oracle"); //$NON-NLS-1$ 
        sourceQueries.add("db2"); //$NON-NLS-1$ 
        sourceQueries.add("sybase"); //$NON-NLS-1$ 
        sourceQueries.add("sqlserver"); //$NON-NLS-1$ 
        sourceQueries.add("mysql"); //$NON-NLS-1$
        sourceQueries.add("postgres"); //$NON-NLS-1$
        return new InlineViewCase("testANSIJoinInlineView", userQuery, optimizedQuery, //$NON-NLS-1$
				sourceQueries, expectedResults);
        
	}
	
	public static InlineViewCase createInlineView()  throws Exception {
		String userQuery = "select bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb.aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa from (select count(bqt1.smalla.intkey) as aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa, bqt1.smalla.intkey from bqt1.smalla group by bqt1.smalla.intkey) bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb, bqt1.smallb " + //$NON-NLS-1$
				"where bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb.intkey = 1 and bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb.aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa = bqt1.smallb.intkey"; //$NON-NLS-1$
		String optimizedQuery = "SELECT v_0.c_0 FROM (SELECT COUNT(g_0.IntKey) AS c_0 FROM BQT1.SmallA AS g_0 WHERE g_0.IntKey = 1 GROUP BY g_0.IntKey) AS v_0, BQT1.SmallB AS g_1 WHERE v_0.c_0 = g_1.IntKey"; //$NON-NLS-1$
		
		List<List<?>> expectedResults = new ArrayList<List<?>>();
		expectedResults.add(Arrays.asList(1));
				
		Set<String> sourceQueries = new HashSet<String>();
        sourceQueries.add("oracle"); //$NON-NLS-1$ 
        sourceQueries.add("db2"); //$NON-NLS-1$ 
        sourceQueries.add("sybase"); //$NON-NLS-1$ 
        sourceQueries.add("sqlserver"); //$NON-NLS-1$ 
        sourceQueries.add("mysql"); //$NON-NLS-1$
        sourceQueries.add("postgres"); //$NON-NLS-1$
        return new InlineViewCase("testInlineView", userQuery, optimizedQuery,  //$NON-NLS-1$
				sourceQueries, expectedResults);        
	}	
	
	public static InlineViewCase createInlineViewWithDistinctAndOrderBy() throws Exception {
		String userQuery = "select Q1.a from (select distinct count(bqt1.smalla.intkey) as a, bqt1.smalla.intkey from bqt1.smalla group by bqt1.smalla.intkey order by bqt1.smalla.intkey) q1 inner join bqt1.smallb as q2 on q1.intkey = q2.intkey where q1.a = 1 and q1.a + q1.intkey = 2"; //$NON-NLS-1$
        String optimizedQuery = "SELECT v_0.c_1 FROM (SELECT g_0.IntKey AS c_0, COUNT(g_0.IntKey) AS c_1 FROM BQT1.SmallA AS g_0 GROUP BY g_0.IntKey HAVING (COUNT(g_0.IntKey) = 1) AND ((COUNT(g_0.IntKey) + g_0.IntKey) = 2)) AS v_0, BQT1.SmallB AS g_1 WHERE v_0.c_0 = g_1.IntKey"; //$NON-NLS-1$

        List<List<?>> expectedResults = new ArrayList<List<?>>();
		expectedResults.add(Arrays.asList(1));
				
        Set<String> sourceQueries = new HashSet<String>();
        sourceQueries.add("oracle"); //$NON-NLS-1$ 
        sourceQueries.add("db2"); //$NON-NLS-1$ 
        sourceQueries.add("sybase"); //$NON-NLS-1$ 
        sourceQueries.add("sqlserver"); //$NON-NLS-1$ 
        sourceQueries.add("mysql"); //$NON-NLS-1$
        sourceQueries.add("postgres"); //$NON-NLS-1$
        return new InlineViewCase("testInlineViewWithDistinctAndOrderBy", userQuery, optimizedQuery, //$NON-NLS-1$
				sourceQueries, expectedResults);
        
	}	
	
	public static InlineViewCase createInlineViewOfVirtual() throws Exception{
		String userQuery = "select q1.A from (select count(intkey) as a, intkey, stringkey from vqt.smalla group by intkey, stringkey) q1 inner join vqt.smallb as q2 on q1.intkey = q2.a12345 where q1.a = 2"; //$NON-NLS-1$
		String optimizedQuery = "SELECT v_0.c_1 FROM (SELECT g_0.IntKey AS c_0, COUNT(g_0.IntKey) AS c_1 FROM BQT1.SmallA AS g_0 GROUP BY g_0.IntKey, g_0.StringKey HAVING COUNT(g_0.IntKey) = 2) AS v_0, BQT1.SmallA AS g_1 WHERE convert(v_0.c_0, string) = Concat(g_1.StringKey, g_1.StringNum)"; //$NON-NLS-1$

		List<List<?>> expectedResults = new ArrayList<List<?>>();
				
        Set<String> sourceQueries = new HashSet<String>();
        sourceQueries.add("oracle"); //$NON-NLS-1$ 
        sourceQueries.add("db2"); //$NON-NLS-1$ 
        sourceQueries.add("sybase"); //$NON-NLS-1$ 
        sourceQueries.add("sqlserver"); //$NON-NLS-1$ 
        sourceQueries.add("mysql"); //$NON-NLS-1$
        sourceQueries.add("postgres"); //$NON-NLS-1$
        return new InlineViewCase("testInlineViewOfVirtual", userQuery, optimizedQuery, //$NON-NLS-1$
				sourceQueries, expectedResults);        
	}	
	
	public static InlineViewCase createInlineViewWithOuterOrderAndGroup() throws Exception {
		String userQuery = "select count(Q1.a) b from (select distinct count(bqt1.smalla.intkey) as a, bqt1.smalla.intkey from bqt1.smalla group by bqt1.smalla.intkey order by bqt1.smalla.intkey) q1 inner join bqt1.smallb as q2 on q1.intkey = q2.intkey where q1.a = 1 and q1.a + q1.intkey = 2 group by Q1.a order by b"; //$NON-NLS-1$
		String optimizedQuery = "SELECT COUNT(v_0.c_1) AS c_0 FROM (SELECT g_0.IntKey AS c_0, COUNT(g_0.IntKey) AS c_1 FROM BQT1.SmallA AS g_0 GROUP BY g_0.IntKey HAVING (COUNT(g_0.IntKey) = 1) AND ((COUNT(g_0.IntKey) + g_0.IntKey) = 2)) AS v_0, BQT1.SmallB AS g_1 WHERE v_0.c_0 = g_1.IntKey GROUP BY v_0.c_1 ORDER BY c_0"; //$NON-NLS-1$

		List<List<?>> expectedResults = new ArrayList<List<?>>();
		expectedResults.add(Arrays.asList(1));
				
        Set<String> sourceQueries = new HashSet<String>();
        sourceQueries.add("oracle"); //$NON-NLS-1$ 
        sourceQueries.add("db2"); //$NON-NLS-1$ 
        sourceQueries.add("sybase"); //$NON-NLS-1$ 
        sourceQueries.add("sqlserver"); //$NON-NLS-1$ 
        sourceQueries.add("mysql"); //$NON-NLS-1$
        sourceQueries.add("postgres"); //$NON-NLS-1$
        return new InlineViewCase("testInlineViewWithOuterOrderAndGroup", userQuery, optimizedQuery, //$NON-NLS-1$
				sourceQueries, expectedResults);        
	}
	
	public static InlineViewCase crateInlineViewsInUnions() throws Exception {
		String userQuery = "select q1.a from (select count(bqt1.smalla.intkey) as a, bqt1.smalla.intkey from bqt1.smalla group by bqt1.smalla.intkey) q1 left outer join bqt1.smallb on q1.a = bqt1.smallb.intkey where q1.intkey = 1 union all (select count(Q1.a) b from (select distinct count(bqt1.smalla.intkey) as a, bqt1.smalla.intkey from bqt1.smalla group by bqt1.smalla.intkey order by bqt1.smalla.intkey) q1 inner join bqt1.smallb as q2 on q1.intkey = q2.intkey where q1.a = 1 and q1.a + q1.intkey = 2 group by Q1.a order by b)"; //$NON-NLS-1$
		String optimizedQuery = "SELECT v_1.c_0 FROM (SELECT COUNT(g_2.IntKey) AS c_0 FROM BQT1.SmallA AS g_2 WHERE g_2.IntKey = 1 GROUP BY g_2.IntKey) AS v_1 LEFT OUTER JOIN BQT1.SmallB AS g_3 ON v_1.c_0 = g_3.IntKey UNION ALL SELECT COUNT(v_0.c_1) AS c_0 FROM (SELECT g_0.IntKey AS c_0, COUNT(g_0.IntKey) AS c_1 FROM BQT1.SmallA AS g_0 GROUP BY g_0.IntKey HAVING (COUNT(g_0.IntKey) = 1) AND ((COUNT(g_0.IntKey) + g_0.IntKey) = 2)) AS v_0, BQT1.SmallB AS g_1 WHERE v_0.c_0 = g_1.IntKey GROUP BY v_0.c_1"; //$NON-NLS-1$

		List<List<?>> expectedResults = new ArrayList<List<?>>();
		expectedResults.add(Arrays.asList(1));
        expectedResults.add(Arrays.asList(2));
				
        Set<String> sourceQueries = new HashSet<String>();
        sourceQueries.add("oracle"); //$NON-NLS-1$ 
        sourceQueries.add("db2"); //$NON-NLS-1$ 
        sourceQueries.add("sybase"); //$NON-NLS-1$ 
        sourceQueries.add("sqlserver"); //$NON-NLS-1$ 
        sourceQueries.add("mysql"); //$NON-NLS-1$
        sourceQueries.add("postgres"); //$NON-NLS-1$
		return new InlineViewCase("testInlineViewsInUnions", userQuery, optimizedQuery, //$NON-NLS-1$
				sourceQueries, expectedResults);
		
	}
	
	public static InlineViewCase createUnionInInlineView() throws Exception{
		
	    String userQuery = "select t1.intkey from (select case when q1.a=1 then 2 else 1 end as a from (select count(bqt1.smalla.intkey) as a, bqt1.smalla.intkey from bqt1.smalla group by bqt1.smalla.intkey) q1 left outer join bqt1.smallb on q1.a = bqt1.smallb.intkey where q1.intkey = 1 union all (select count(Q1.a) b from (select distinct count(bqt1.smalla.intkey) as a, bqt1.smalla.intkey from bqt1.smalla group by bqt1.smalla.intkey order by bqt1.smalla.intkey) q1 inner join bqt1.smallb as q2 on q1.intkey = q2.intkey where q1.a = 1 and q1.a + q1.intkey = 2 group by Q1.a order by b)) as q3, bqt1.smallb as t1 where q3.a = t1.intkey order by t1.intkey"; //$NON-NLS-1$
		String optimizedQuery = "SELECT g_4.IntKey AS c_0 FROM (SELECT CASE WHEN v_1.c_0 = 1 THEN 2 ELSE 1 END AS c_0 FROM (SELECT COUNT(g_2.IntKey) AS c_0 FROM BQT1.SmallA AS g_2 WHERE g_2.IntKey = 1 GROUP BY g_2.IntKey) AS v_1 LEFT OUTER JOIN BQT1.SmallB AS g_3 ON v_1.c_0 = g_3.IntKey UNION ALL SELECT COUNT(v_0.c_1) AS c_0 FROM (SELECT g_0.IntKey AS c_0, COUNT(g_0.IntKey) AS c_1 FROM BQT1.SmallA AS g_0 GROUP BY g_0.IntKey HAVING (COUNT(g_0.IntKey) = 1) AND ((COUNT(g_0.IntKey) + g_0.IntKey) = 2)) AS v_0, BQT1.SmallB AS g_1 WHERE v_0.c_0 = g_1.IntKey GROUP BY v_0.c_1) AS v_2, BQT1.SmallB AS g_4 WHERE v_2.c_0 = g_4.IntKey ORDER BY c_0"; //$NON-NLS-1$

		List<List<?>> expectedResults = new ArrayList<List<?>>();
		expectedResults.add(Arrays.asList(1));
        expectedResults.add(Arrays.asList(2));
				
        Set<String> sourceQueries = new HashSet<String>();
        sourceQueries.add("oracle"); //$NON-NLS-1$ 
        /*
         * fails in db2 since the intkey column is in the database as a decimal
         */
        //sourceQueries.add("db2"); //$NON-NLS-1$ 
        sourceQueries.add("sybase"); //$NON-NLS-1$ 
        sourceQueries.add("sqlserver"); //$NON-NLS-1$ 
        sourceQueries.add("mysql"); //$NON-NLS-1$
        sourceQueries.add("postgres"); //$NON-NLS-1$
		return new InlineViewCase("testUnionInInlineView", userQuery, optimizedQuery, //$NON-NLS-1$
				sourceQueries, expectedResults);
		
	}	
	
	protected void runTest(InlineViewCase testCase) throws Exception {
		FakeCapabilitiesFinder capFinder = getInliveViewCapabilitiesFinder();
    	QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached();
    	
		ProcessorPlan plan = TestOptimizer.helpPlan(testCase.userQuery, metadata, null, capFinder, new String[] {testCase.optimizedQuery}, TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING); 

        TestOptimizer.checkNodeTypes(plan, TestOptimizer.FULL_PUSHDOWN);    
	}	
	
    @Test public void testAliasCreationWithInlineView() throws TeiidComponentException, TeiidProcessingException {
        FakeCapabilitiesFinder capFinder = getInliveViewCapabilitiesFinder();
        QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached();
        
        ProcessorPlan plan = helpPlan("select a, b from (select distinct count(intNum) a, count(stringKey), bqt1.smalla.intkey as b from bqt1.smalla group by bqt1.smalla.intkey) q1 order by q1.a", //$NON-NLS-1$
                metadata, null, capFinder, new String[] {"SELECT COUNT(g_0.IntNum) AS c_0, g_0.IntKey AS c_1 FROM BQT1.SmallA AS g_0 GROUP BY g_0.IntKey ORDER BY c_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$

        checkNodeTypes(plan, FULL_PUSHDOWN);    
    }
    
    @Test public void testAliasPreservationWithInlineView() throws TeiidComponentException, TeiidProcessingException {
        FakeCapabilitiesFinder capFinder = getInliveViewCapabilitiesFinder();
        QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached();
        
        ProcessorPlan plan = helpPlan("select q1.a + 1, q1.b from (select count(bqt1.smalla.intNum) as a, bqt1.smalla.intkey as b from bqt1.smalla group by bqt1.smalla.intNum, bqt1.smalla.intkey order by b) q1 where q1.a = 1", //$NON-NLS-1$
                metadata, null, capFinder, new String[] {"SELECT (v_0.c_0 + 1), v_0.c_1 FROM (SELECT COUNT(g_0.IntNum) AS c_0, g_0.IntKey AS c_1 FROM BQT1.SmallA AS g_0 GROUP BY g_0.IntNum, g_0.IntKey HAVING COUNT(g_0.IntNum) = 1) AS v_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$

        checkNodeTypes(plan, FULL_PUSHDOWN);    
    }
    
    /**
     * Order by's will be added to the atomic queries
     */
    @Test public void testCrossSourceInlineView() throws Exception {
        FakeCapabilitiesFinder capFinder = getInliveViewCapabilitiesFinder();
        QueryMetadataInterface metadata = RealMetadataFactory.exampleBQTCached();
        
        ProcessorPlan plan = helpPlan("select * from (select count(bqt1.smalla.intkey) as a, bqt1.smalla.intkey from bqt1.smalla group by bqt1.smalla.intkey) q1 inner join (select count(bqt2.smallb.intkey) as a, bqt2.smallb.intkey from bqt2.smallb group by bqt2.smallb.intkey) as q2 on q1.intkey = q2.intkey where q1.a = 1", //$NON-NLS-1$
                metadata, null, capFinder, new String[] {"SELECT v_0.c_0, v_0.c_1 FROM (SELECT g_0.IntKey AS c_0, COUNT(g_0.IntKey) AS c_1 FROM BQT2.SmallB AS g_0 GROUP BY g_0.IntKey) AS v_0 ORDER BY c_0", 
        		"SELECT v_0.c_0, v_0.c_1 FROM (SELECT g_0.IntKey AS c_0, COUNT(g_0.IntKey) AS c_1 FROM BQT1.SmallA AS g_0 GROUP BY g_0.IntKey HAVING COUNT(g_0.IntKey) = 1) AS v_0 ORDER BY c_0"}, ComparisonMode.EXACT_COMMAND_STRING); //$NON-NLS-1$

        checkNodeTypes(plan, new int[] {
                2,      // Access
                0,      // DependentAccess
                0,      // DependentSelect
                0,      // DependentProject
                0,      // DupRemove
                0,      // Grouping
                0,      // Join
                1,      // MergeJoin
                0,      // Null
                0,      // PlanExecution
                1,      // Project
                0,      // Select
                0,      // Sort
                0       // UnionAll
            });    
    }

}
