/*
 * 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.translator.jdbc.netezza;

import static org.junit.Assert.*;

import org.junit.BeforeClass;
import org.junit.Test;
import org.mockito.Mockito;
import org.teiid.cdk.unittest.FakeTranslationFactory;
import org.teiid.language.Command;
import org.teiid.translator.ExecutionContext;
import org.teiid.translator.TranslatorException;
import org.teiid.translator.jdbc.TranslatedCommand;

@SuppressWarnings("nls")
public class TestNetezzaTranslatorCapabilities {

    private static NetezzaExecutionFactory TRANSLATOR;

    @BeforeClass public static void oneTimeSetup() throws TranslatorException { 
        TRANSLATOR = new NetezzaExecutionFactory();        
        TRANSLATOR.start();
    }
    
    public void helpTestVisitor(String input, String expectedOutput) throws TranslatorException {
        // Convert from sql to objects
        Command obj = FakeTranslationFactory.getInstance().getBQTTranslationUtility().parseCommand(input);
        
        TranslatedCommand tc = new TranslatedCommand(Mockito.mock(ExecutionContext.class), TRANSLATOR);
        tc.translateCommand(obj);
        
        
        // Check stuff
        assertEquals("Did not get correct sql", expectedOutput, tc.getSql());             
    }


    /////////BASIC TEST CASES FOR CAPABILITIES/////////////
    /////////////////////////////////////////////////
    @Test
    public void testRowLimit() throws Exception {
        String input = "select intkey from bqt1.smalla limit 100"; 
        String output = "SELECT SmallA.IntKey FROM SmallA LIMIT 100";  

        helpTestVisitor(
            input, 
            output);

    }
    @Test
    public void testSelectDistinct() throws Exception {
        String input = "select distinct intkey from bqt1.smalla limit 100"; 
        String output = "SELECT DISTINCT SmallA.IntKey FROM SmallA LIMIT 100";  

        helpTestVisitor(
            input, 
            output);
    }
    @Test
    public void testSelectExpression() throws Exception {
        String input = "select  intkey, intkey + longnum / 2 as test from bqt1.smalla"; 
        String output = "SELECT SmallA.IntKey, (SmallA.IntKey + (SmallA.LongNum / 2)) AS test FROM SmallA";  

        helpTestVisitor(
            input, 
            output);
    }
    
    public void testBetweenCriteria() throws Exception {
        String input = "select  intkey, intnum from bqt1.smalla where intnum BETWEEN 2 AND 10"; 
        String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum >= 2 AND SmallA.IntNum <= 10";  

        helpTestVisitor(
            input, 
            output);
    }
    public void testCompareCriteriaEquals() throws Exception {
        String input = "select  intkey, intnum from bqt1.smalla where intnum = 10"; 
        String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum = 10";  

        helpTestVisitor(
            input, 
            output);
    }
    public void testCompareCriteriaOrdered() throws Exception {
        String input = "select  intkey, intnum from bqt1.smalla where intnum < 10"; 
        String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum < 10";  

        helpTestVisitor(
            input, 
            output);
    }
    public void testLikeCriteria() throws Exception {
        String input = "select  intkey, intnum from bqt1.smalla where stringkey like '4%'"; 
        String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.StringKey LIKE '4%'";  

        helpTestVisitor(
            input, 
            output);
    }
    public void testLikeWithEscapeCriteria() throws Exception {
        String input = "select  intkey, intnum from bqt1.smalla where stringkey like '4\\%'"; 
        String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.StringKey LIKE '4\\%'";  

        helpTestVisitor(
            input, 
            output);
    }

    public void testInCriteria() throws Exception {
        String input = "select  intkey, intnum from bqt1.smalla where stringkey IN ('10', '11', '12')"; 
        String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.StringKey IN ('10', '11', '12')";  

        helpTestVisitor(
            input, 
            output);
    }
    public void testInCriteriaSubQuery() throws Exception {
        String input = "select  intkey, intnum from bqt1.smalla where stringkey IN (select stringkey from bqt1.smalla where intkey < 10)"; 
        String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.StringKey IN (SELECT SmallA.StringKey FROM SmallA WHERE SmallA.IntKey < 10)";  

        helpTestVisitor(
            input, 
            output);
    }

    public void testIsNullCriteria() throws Exception {
        String input = "select  intkey, intnum from bqt1.smalla where intnum IS NULL"; 
        String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum IS NULL";  

        helpTestVisitor(
            input, 
            output);
    }
    public void testOrCriteria() throws Exception {
        String input = "select  intkey, intnum from bqt1.smalla where intnum < 2 OR intnum > 10"; 
        String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum < 2 OR SmallA.IntNum > 10";  

        helpTestVisitor(
            input, 
            output);
    }
    @Test public void testIsNotNullCriteria() throws Exception {
        String input = "select  intkey, intnum from bqt1.smalla where intnum IS NOT NULL"; 
        String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum IS NOT NULL";  

        helpTestVisitor(
            input, 
            output);
    }

    @Test public void testExistsCriteria() throws Exception {
        String input = "select  intkey, intnum from bqt1.smalla where exists (select intkey from bqt1.smallb)"; 
        String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE EXISTS (SELECT SmallB.IntKey FROM SmallB LIMIT 1)";  

        helpTestVisitor(
            input, 
            output);
    }
    @Test public void testHavingClauseCriteria() throws Exception {
        String input = "SELECT INTKEY FROM BQT1.SMALLA GROUP BY INTKEY HAVING INTKEY = (SELECT INTKEY FROM BQT1.SMALLA WHERE STRINGKEY = 20)"; 
        String output = "SELECT SmallA.IntKey FROM SmallA GROUP BY SmallA.IntKey HAVING SmallA.IntKey = (SELECT SmallA.IntKey FROM SmallA WHERE SmallA.StringKey = '20' LIMIT 2)";  

        helpTestVisitor(
            input, 
            output);
    }
    
    @Test public void testScalarSubQuery() throws Exception {
        String input = "select intkey, intnum from bqt1.smalla where intnum < (0.01 * (select sum(intnum) from bqt1.smalla ))"; 
        String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA WHERE SmallA.IntNum < (0.01 * (SELECT SUM(SmallA.IntNum) FROM SmallA))";  

        helpTestVisitor(
            input, 
            output);
    }

    @Test public void testSimpleCaseExpression() throws Exception {
        String input = "SELECT stringnum,  intnum,  CASE  BOOLEANVALUE  WHEN 'true'  then 'true' WHEN false THEN 'FALSE' ELSE 'GOOD' END    FROM bqt1.smalla;"; 
        String output = "SELECT SmallA.StringNum, SmallA.IntNum, CASE WHEN SmallA.BooleanValue = 1 THEN 'true' WHEN SmallA.BooleanValue = 0 THEN 'FALSE' ELSE 'GOOD' END FROM SmallA";  

        helpTestVisitor(
            input, 
            output);
    }

    @Test public void testSearchedCaseExpression() throws Exception {
        String input = "SELECT AVG(CASE WHEN intnum > 10 THEN intnum ELSE intkey END) \"Average\" FROM bqt1.smalla"; 
        String output = "SELECT AVG(CASE WHEN SmallA.IntNum > 10 THEN SmallA.IntNum ELSE SmallA.IntKey END) AS Average FROM SmallA";  

        helpTestVisitor(
            input, 
            output);
    }
 
//    @Test public void testQuantifiedCompareSOMEorANY() throws Exception {
//        String input = "SELECT INTKEY, BYTENUM FROM BQT1.SmallA WHERE BYTENUM = ANY (SELECT BYTENUM FROM BQT1.SmallA WHERE BYTENUM >= '-108')"; 
//        String output = "SELECT SmallA.IntKey, SmallA.ByteNum FROM SmallA WHERE SmallA.ByteNum = SOME (SELECT SmallA.ByteNum FROM SmallA WHERE SmallA.ByteNum >= -108)";  
//
//        helpTestVisitor(
//            input, 
//            output);
//    }
      
    @Test public void testQuantifiedCompareALL() throws Exception {
        String input = "SELECT INTKEY, STRINGKEY FROM BQT1.SMALLA WHERE STRINGKEY = ALL (SELECT STRINGKEY FROM BQT1.SMALLA WHERE INTKEY = 40)"; 
        String output = "SELECT SmallA.IntKey, SmallA.StringKey FROM SmallA WHERE SmallA.StringKey = ALL (SELECT SmallA.StringKey FROM SmallA WHERE SmallA.IntKey = 40)";  

        helpTestVisitor(
            input, 
            output);
    }
    
    @Test public void testSelfJoin() throws Exception {
        String input = "SELECT x.intnum, y.intkey  FROM bqt1.smalla x, bqt1.smalla y   WHERE x.stringnum = y.intnum;"; 
        String output = "SELECT x.IntNum, y.IntKey FROM SmallA AS x, SmallA AS y WHERE x.StringNum = cast(y.IntNum AS varchar(4000))";  

        helpTestVisitor(
            input, 
            output);
    }

    @Test public void testLimitWithNestedInlineView() throws Exception {
        String input = "select max(intkey), stringkey from (select intkey, stringkey from bqt1.smalla order by intkey limit 100) x group by stringkey"; 
        String output = "SELECT MAX(x.intkey), x.stringkey FROM (SELECT SmallA.IntKey, SmallA.StringKey FROM SmallA ORDER BY SmallA.IntKey LIMIT 100) AS x GROUP BY x.stringkey"; 
               
        helpTestVisitor( input, output);   
    }
    
    @Test public void testAggregatesAndEnhancedNumeric() throws Exception {
        String input = "select count(*), min(intkey), max(intkey), sum(intkey), avg(intkey), count(intkey), STDDEV_SAMP(intkey), STDDEV_POP(intkey), VAR_SAMP(intkey), VAR_POP(intkey) from bqt1.smalla"; 
        String output = "SELECT COUNT(*), MIN(SmallA.IntKey), MAX(SmallA.IntKey), SUM(SmallA.IntKey), AVG(SmallA.IntKey), COUNT(SmallA.IntKey), STDDEV_SAMP(SmallA.IntKey), STDDEV_POP(SmallA.IntKey), VAR_SAMP(SmallA.IntKey), VAR_POP(SmallA.IntKey) FROM SmallA"; 
               
        helpTestVisitor( input, output);   
    }
    @Test public void testAggregatesDistinct() throws Exception {
        String input = "select avg(DISTINCT intnum) from bqt1.smalla"; 
        String output = "SELECT AVG(DISTINCT SmallA.IntNum) FROM SmallA"; 
               
        helpTestVisitor( input, output);   
    }

    
    @Test public void testExceptAsMinus() throws Exception {
        String input = "select intkey, intnum from bqt1.smalla except select intnum, intkey from bqt1.smallb"; 
        String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA EXCEPT SELECT SmallB.IntNum, SmallB.IntKey FROM SmallB"; 
               
        helpTestVisitor( input, output);   
    }
    
    @Test public void testUnionAsPlus() throws Exception {
        String input = "select intkey, intnum from bqt1.smalla union select intnum, intkey from bqt1.smallb"; 
        String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA UNION SELECT SmallB.IntNum, SmallB.IntKey FROM SmallB"; 
               
        helpTestVisitor( input, output);   
    }
    @Test public void testUnionAllAsPlus() throws Exception {
        String input = "select intkey, intnum from bqt1.smalla union all select intnum, intkey from bqt1.smallb"; 
        String output = "SELECT SmallA.IntKey, SmallA.IntNum FROM SmallA UNION ALL SELECT SmallB.IntNum, SmallB.IntKey FROM SmallB"; 
               
        helpTestVisitor( input, output);   
    }
    
    @Test public void testUnionAllAsPlusWithAggregates() throws Exception {
        String input = "select intkey, Sum(intnum) from bqt1.smalla group by intkey union all select intnum, intkey from bqt1.smallb"; 
        String output = "SELECT SmallA.IntKey, SUM(SmallA.IntNum) FROM SmallA GROUP BY SmallA.IntKey UNION ALL SELECT SmallB.IntNum, SmallB.IntKey AS IntKey FROM SmallB"; 
               
        helpTestVisitor( input, output);   
    }


    @Test public void testintersect() throws Exception {
         String input = "select intkey from bqt1.smalla where intkey < 20 INTERSECT select intkey from bqt1.smalla where intkey > 10";
         String output = "SELECT SmallA.IntKey FROM SmallA WHERE SmallA.IntKey < 20 INTERSECT SELECT SmallA.IntKey FROM SmallA WHERE SmallA.IntKey > 10"; 
        
        
        helpTestVisitor( input, output);   
    }

   
    @Test public void testUnionOrderBy() throws Exception {
        String input = "(select intkey from bqt1.smalla) union select intnum from bqt1.smalla order by intkey"; 
        String output = "SELECT SmallA.IntKey FROM SmallA UNION SELECT SmallA.IntNum FROM SmallA ORDER BY intkey";
               
        helpTestVisitor( input, output);   

    }

    @Test public void testIntersectOrderBy() throws Exception {
        String input = "(select intkey from bqt1.smalla) intersect select intnum from bqt1.smalla order by intkey"; 
        String output = "SELECT SmallA.IntKey FROM SmallA INTERSECT SELECT SmallA.IntNum FROM SmallA ORDER BY intkey";
               
        helpTestVisitor( input, output);   

    }
    
    @Test public void testExceptOrderBy() throws Exception {
        String input = "(select intkey from bqt1.smalla) except select intnum from bqt1.smalla order by intkey"; 
        String output = "SELECT SmallA.IntKey FROM SmallA EXCEPT SELECT SmallA.IntNum FROM SmallA ORDER BY intkey";
               
        helpTestVisitor( input, output);   

    }

    
    @Test public void testRowLimitOFFSET() throws Exception {
        String input = "select intkey from bqt1.smalla limit 20, 30"; 
        String output = "SELECT SmallA.IntKey FROM SmallA LIMIT 30 OFFSET 20"; 
               
        helpTestVisitor( input, output);        
    }   
    
    
    @Test public void testOrderByNullsFirstLast() throws Exception {
        String input = "select intkey,  longnum from  bqt1.smalla order by longnum NULLS LAST"; 
        String output = "SELECT SmallA.IntKey, SmallA.LongNum FROM SmallA ORDER BY SmallA.LongNum NULLS LAST"; 
               
        helpTestVisitor( input, output);        
    }   

    @Test public void testOrderByUnRelated() throws Exception {
        String input = "select intkey,  longnum from  bqt1.smalla order by floatnum"; 
        String output = "SELECT SmallA.IntKey, SmallA.LongNum FROM SmallA ORDER BY SmallA.FloatNum"; 
               
        helpTestVisitor( input, output);        
    }   
    
    
    @Test public void testInnerJoin() throws Exception {
        String input = "SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA, BQT2.SmallB WHERE BQT1.SmallA.IntKey = BQT2.SmallB.IntKey AND BQT1.SmallA.IntKey >= 0 AND BQT2.SmallB.IntKey >= 0 ORDER BY BQT1.SmallA.IntKey"; 
        String output = "SELECT SmallA.IntKey FROM SmallA, SmallB WHERE SmallA.IntKey = SmallB.IntKey AND SmallA.IntKey >= 0 AND SmallB.IntKey >= 0 ORDER BY SmallA.IntKey"; 
               
        helpTestVisitor( input, output);        
    }   

    
    @Test public void testOuterJoin() throws Exception {
        String input = "SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA, BQT2.SmallB WHERE BQT1.SmallA.IntKey = BQT2.SmallB.IntKey AND BQT1.SmallA.IntKey >= 0 AND BQT2.SmallB.IntKey >= 0 ORDER BY BQT1.SmallA.IntKey"; 
        String output = "SELECT SmallA.IntKey FROM SmallA, SmallB WHERE SmallA.IntKey = SmallB.IntKey AND SmallA.IntKey >= 0 AND SmallB.IntKey >= 0 ORDER BY SmallA.IntKey"; 
               
        helpTestVisitor( input, output);        
    }   

    @Test public void testFullOuterJoin() throws Exception {
        String input = "SELECT BQT1.SmallA.IntNum, BQT2.SmallB.IntNum FROM BQT1.SmallA FULL OUTER JOIN BQT2.SmallB ON BQT1.SmallA.IntNum = BQT2.SmallB.IntNum ORDER BY BQT1.SmallA.IntNum"; 
        String output = "SELECT SmallA.IntNum, SmallB.IntNum FROM SmallA FULL OUTER JOIN SmallB ON SmallA.IntNum = SmallB.IntNum ORDER BY SmallA.IntNum"; 
               
        helpTestVisitor( input, output);        
    }   

    @Test public void testRightOuterJoin() throws Exception {
        String input = "SELECT BQT1.SmallA.IntNum, BQT2.SmallB.IntNum FROM BQT1.SmallA RIGHT OUTER JOIN BQT2.SmallB ON BQT1.SmallA.IntNum = BQT2.SmallB.IntNum ORDER BY BQT2.SmallB.IntNum";
        String output= "SELECT SmallA.IntNum, SmallB.IntNum FROM SmallB LEFT OUTER JOIN SmallA ON SmallA.IntNum = SmallB.IntNum ORDER BY SmallB.IntNum";
               
        helpTestVisitor( input, output);        
    }   
    @Test public void testLeftOuterJoin() throws Exception {
        String input = "SELECT BQT1.SmallA.IntNum, BQT2.SmallB.IntNum FROM BQT1.SmallA LEFT OUTER JOIN BQT2.SmallB ON BQT1.SmallA.IntNum = BQT2.SmallB.IntNum ORDER BY BQT1.SmallA.IntNum"; 
        String output = "SELECT SmallA.IntNum, SmallB.IntNum FROM SmallA LEFT OUTER JOIN SmallB ON SmallA.IntNum = SmallB.IntNum ORDER BY SmallA.IntNum"; 
               
        helpTestVisitor( input, output);        
    }  
    
    @Test public void testLikeRegex() throws Exception {
        String input = "SELECT BQT1.SmallA.IntNum FROM BQT1.SmallA where stringkey like_regex 'a.*' and stringkey not like_regex 'ab.*'"; 
        String output = "SELECT SmallA.IntNum FROM SmallA WHERE REGEXP_LIKE(SmallA.StringKey, 'a.*') AND NOT(REGEXP_LIKE(SmallA.StringKey, 'ab.*'))"; 
               
        helpTestVisitor( input, output);        
    }   
    
}
