/*
 * 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-2392
// https://issues.jboss.org/browse/TEIID-3526
// https://issues.jboss.org/browse/TEIID-4631
// https://issues.jboss.org/browse/TEIID-4418
// https://issues.jboss.org/browse/TEIID-4532
// https://issues.redhat.com/browse/TEIID-4491
// https://issues.redhat.com/browse/TEIID-5918
// https://issues.redhat.com/browse/TEIID-6044
// https://issues.redhat.com/browse/TEIID-2850
// https://issues.redhat.com/browse/TEIID-3168
package org.teiid.translator.jdbc.sqlserver;

import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Disabled;
import org.junit.jupiter.api.Test;
import org.teiid.cdk.CommandBuilder;
import org.teiid.cdk.api.TranslationUtility;
import org.teiid.core.types.DataTypeManager;
import org.teiid.language.Command;
import org.teiid.metadata.Column;
import org.teiid.metadata.MetadataStore;
import org.teiid.metadata.Schema;
import org.teiid.metadata.Table;
import org.teiid.query.metadata.CompositeMetadataStore;
import org.teiid.query.metadata.QueryMetadataInterface;
import org.teiid.query.metadata.TransformationMetadata;
import org.teiid.query.unittest.RealMetadataFactory;
import org.teiid.translator.TranslatorException;
import org.teiid.translator.TypeFacility;
import org.teiid.translator.jdbc.FunctionModifier;
import org.teiid.translator.jdbc.TranslationHelper;

import java.util.List;

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


/**
 */
public class TestSqlServerConversionVisitor {

    private static SQLServerExecutionFactory trans = new SQLServerExecutionFactory();
    
    @BeforeAll
    public static void oneTimeSetup() throws TranslatorException {
        // EXTENSION
        // ISSUE: DVCORE-589
        // FIXED BY: Roman Pedchenko
        // DATE: 2013.08.06
    	trans.setTrimStrings(false);
        // END EXTENSION
        trans.start();
    }
	// EXTENSION
	// ISSUE: INFRA-161
	// FIXED BY: Dmitry Pogorelov
	// DATE: 2015.03.06
	// problem with testConvertDate test because of incorrect MSSQL version
    @BeforeEach
    // END EXTENSION
    public void setUp() throws Exception {
    	trans.setDatabaseVersion(SQLServerExecutionFactory.V_2005);
    }

    public String getTestVDB() {
        return TranslationHelper.PARTS_VDB;
    }

    public String getBQTVDB() {
        return TranslationHelper.BQT_VDB;
    }
    
    public void helpTestVisitor(String vdb, String input, String expectedOutput) throws TranslatorException {
    	TranslationHelper.helpTestVisitor(vdb, input, expectedOutput, trans);
    }

    @Test
    public void testModFunction() throws Exception {
        String input = "SELECT mod(CONVERT(PART_ID, INTEGER), 13) FROM parts"; //$NON-NLS-1$
        String output = "SELECT (cast(PARTS.PART_ID AS int) % 13) FROM PARTS";  //$NON-NLS-1$

        helpTestVisitor(getTestVDB(),
            input, 
            output);
    } 

    @Test
    public void testConcatFunction() throws Exception {
        String input = "SELECT concat(part_name, 'b') FROM PARTS"; //$NON-NLS-1$
        String output = "SELECT (PARTS.PART_NAME + N'b') FROM PARTS"; //$NON-NLS-1$
        
        helpTestVisitor(getTestVDB(),
            input, 
            output);
    }    

    @Test
    public void testDayOfMonthFunction() throws Exception {
        String input = "SELECT dayofmonth(convert(PARTS.PART_ID, date)) FROM PARTS"; //$NON-NLS-1$
        String output = "SELECT {fn dayofmonth(cast(PARTS.PART_ID AS datetime))} FROM PARTS"; //$NON-NLS-1$
    
        helpTestVisitor(getTestVDB(),
            input, 
            output);
    }

    @Test
    public void testRowLimit() throws Exception {
        String input = "select intkey from bqt1.smalla limit 100"; //$NON-NLS-1$
        String output = "SELECT TOP 100 SmallA.IntKey FROM SmallA"; //$NON-NLS-1$
               
        helpTestVisitor(getBQTVDB(),
            input, 
            output);        
    }

    // BUG: Incorrect syntax error in MS SQL Server in queries containing WITH, UNION ALL and LIMIT clauses
  	// ISSUE: DV-123
  	// SOLUTION: applied patch from TEIID-2864
  	// FIXED by Salvatore Raunich
  	// date 2014.02.25
    @Test
    public void testRowLimitAndCTE() throws Exception {
        String input = "with x (a) as (select intkey from bqt1.smalla) select a from x union all select a from x limit 100"; //$NON-NLS-1$
        String output = "WITH x (a) AS (SELECT SmallA.IntKey FROM SmallA) SELECT TOP 100 * FROM (SELECT x.a FROM x UNION ALL SELECT x.a FROM x) AS X"; //$NON-NLS-1$
               
        helpTestVisitor(getBQTVDB(),
            input, 
            output);        
    }
    // BUG END
    
    @Test
    public void testUnionLimitWithOrderBy() throws Exception {
        String input = "select intkey from bqt1.smalla union select intnum from bqt1.smalla order by intkey limit 100"; //$NON-NLS-1$
        String output = "SELECT TOP 100 * FROM (SELECT SmallA.IntKey FROM SmallA UNION SELECT SmallA.IntNum FROM SmallA) AS X ORDER BY intkey"; //$NON-NLS-1$
               
        helpTestVisitor(getBQTVDB(),
            input, 
            output);        
    }
    
    @Test public void testLimitWithOrderByUnrelated() throws Exception {
        String input = "select intkey from bqt1.smalla order by intnum limit 100"; //$NON-NLS-1$
        String output = "SELECT TOP 100 SmallA.IntKey FROM SmallA ORDER BY SmallA.IntNum"; //$NON-NLS-1$
               
        helpTestVisitor(getBQTVDB(),
            input, 
            output);        
    }
    
    @Test
    public void testDateFunctions() throws Exception {
        String input = "select dayName(timestampValue), dayOfWeek(timestampValue), quarter(timestampValue), week(timestampvalue) from bqt1.smalla"; //$NON-NLS-1$
        String output = "SELECT {fn dayname(SmallA.TimestampValue)}, {fn dayofweek(SmallA.TimestampValue)}, {fn quarter(SmallA.TimestampValue)}, DATEPART(ISO_WEEK, SmallA.TimestampValue) FROM SmallA"; //$NON-NLS-1$
               
        helpTestVisitor(getBQTVDB(),
            input, 
            output);        
    }
    
    @Test public void testConvert() throws Exception {
        String input = "select convert(timestampvalue, date), convert(timestampvalue, string), convert(datevalue, string) from bqt1.smalla"; //$NON-NLS-1$
        String output = "SELECT cast(SmallA.TimestampValue AS date), convert(varchar(34), SmallA.TimestampValue, 21), replace(convert(varchar, SmallA.DateValue, 102), '.', '-') FROM SmallA"; //$NON-NLS-1$
               
        helpTestVisitor(getBQTVDB(),
            input, 
            output);        
    }
    
    @Test public void testConvertDate() throws Exception {
        String input = "select stringkey from bqt1.smalla where BQT1.SmallA.DateValue IN (convert('2000-01-12', date), convert('2000-02-02', date))"; //$NON-NLS-1$
        String output = "SELECT SmallA.StringKey FROM SmallA WHERE SmallA.DateValue IN (CAST('2000-01-12 00:00:00.0' AS DATETIME), CAST('2000-02-02 00:00:00.0' AS DATETIME))"; //$NON-NLS-1$
               
        helpTestVisitor(getBQTVDB(),
            input, 
            output);        
    }
    
    @Test public void testConvertDate2008() throws Exception {
    	trans.setDatabaseVersion(SQLServerExecutionFactory.V_2008);
        String input = "select stringkey from bqt1.smalla where BQT1.SmallA.DateValue IN (convert('2000-01-12', date), convert('2000-02-02', date))"; //$NON-NLS-1$
        String output = "SELECT SmallA.StringKey FROM SmallA WHERE SmallA.DateValue IN (CAST('2000-01-12' AS DATE), CAST('2000-02-02' AS DATE))"; //$NON-NLS-1$
               
        helpTestVisitor(getBQTVDB(),
            input, 
            output);        
    }

    @Disabled("ignored in DVCORE-6869 because we decided to to get rid of the partial casting just for consistency")
    @Test public void testUniqueidentifier() throws Exception {
    	MetadataStore metadataStore = new MetadataStore();
    	Schema foo = RealMetadataFactory.createPhysicalModel("foo", metadataStore); //$NON-NLS-1$
        Table table = RealMetadataFactory.createPhysicalGroup("bar", foo); //$NON-NLS-1$
        String[] elemNames = new String[] {
            "x",  //$NON-NLS-1$
            "y"  //$NON-NLS-1$
        };
        String[] elemTypes = new String[] {
            DataTypeManager.DefaultDataTypes.STRING,
            DataTypeManager.DefaultDataTypes.STRING
        };
        List<Column> cols =RealMetadataFactory.createElements(table, elemNames, elemTypes);
        
        Column obj = cols.get(0);
        obj.setNativeType("uniqueidentifier"); //$NON-NLS-1$
        
        CompositeMetadataStore store = new CompositeMetadataStore(metadataStore);
        QueryMetadataInterface metadata = new TransformationMetadata(null, store, null, RealMetadataFactory.SFM.getSystemFunctions(), null);
        
        TranslationUtility tu = new TranslationUtility(metadata);
        Command command = tu.parseCommand("select max(x) from bar"); //$NON-NLS-1$
        TranslationHelper.helpTestVisitor("SELECT MAX(bar.x) FROM bar", trans, command); //$NON-NLS-1$

        command = tu.parseCommand("select * from (select max(x) as max from bar) x"); //$NON-NLS-1$
        TranslationHelper.helpTestVisitor("SELECT x.max FROM (SELECT MAX(bar.x) AS max FROM bar) x", trans, command); //$NON-NLS-1$

        command = tu.parseCommand("insert into bar (x) values ('a')"); //$NON-NLS-1$
        TranslationHelper.helpTestVisitor("INSERT INTO bar (x) VALUES ('a')", trans, command); //$NON-NLS-1$

        command = tu.parseCommand("select x from bar where x = 'abc'"); //$NON-NLS-1$
        TranslationHelper.helpTestVisitor("SELECT bar.x FROM bar WHERE bar.x = 'abc'", trans, command); //$NON-NLS-1$

        command = tu.parseCommand("select x from bar where x = y"); //$NON-NLS-1$
        TranslationHelper.helpTestVisitor("SELECT bar.x FROM bar WHERE cast(bar.x as char(36)) = bar.y", trans, command); //$NON-NLS-1$

        command = tu.parseCommand("select x || 'a' from bar"); //$NON-NLS-1$
        TranslationHelper.helpTestVisitor("SELECT (cast(bar.x as char(36)) + 'a') FROM bar", trans, command); //$NON-NLS-1$

        trans = new SQLServerExecutionFactory();
        trans.setDatabaseVersion(SQLServerExecutionFactory.V_2000);
        trans.start();

        command = tu.parseCommand("select max(x) from bar"); //$NON-NLS-1$
        TranslationHelper.helpTestVisitor("SELECT MAX(cast(bar.x as char(36))) FROM bar", trans, command); //$NON-NLS-1$
        
        command = tu.parseCommand("select * from (select max(x) as max from bar) x"); //$NON-NLS-1$
        TranslationHelper.helpTestVisitor("SELECT x.max FROM (SELECT MAX(cast(bar.x as char(36))) AS max FROM bar) x", trans, command); //$NON-NLS-1$

        command = tu.parseCommand("insert into bar (x) values ('a')"); //$NON-NLS-1$
        TranslationHelper.helpTestVisitor("INSERT INTO bar (x) VALUES ('a')", trans, command); //$NON-NLS-1$

        command = tu.parseCommand("select x from bar where x = 'abc'"); //$NON-NLS-1$
        TranslationHelper.helpTestVisitor("SELECT cast(bar.x as char(36)) FROM bar WHERE cast(bar.x as char(36)) = 'abc'", trans, command); //$NON-NLS-1$

    }
    
    @Test public void testRowLimitWithInlineViewOrderBy() throws Exception {
        String input = "select intkey from (select intkey from bqt1.smalla) as x order by intkey limit 100"; //$NON-NLS-1$
        String output = "SELECT TOP 100 v_0.c_0 FROM (SELECT g_0.IntKey AS c_0 FROM SmallA g_0) v_0 ORDER BY v_0.c_0"; //$NON-NLS-1$
               
		CommandBuilder commandBuilder = new CommandBuilder(RealMetadataFactory.exampleBQTCached());
        Command obj = commandBuilder.getCommand(input, true, true);
        TranslationHelper.helpTestVisitor(output, trans, obj);
    }
    
    @Test public void testWith() throws Exception {
        String input = "with x as /*+ no_inline */ (select intkey from bqt1.smalla) select intkey from x limit 100"; //$NON-NLS-1$
    	String output = "WITH x (IntKey) AS (SELECT g_0.IntKey FROM SmallA g_0) SELECT TOP 100 g_1.intkey AS c_0 FROM x g_1"; //$NON-NLS-1$

		CommandBuilder commandBuilder = new CommandBuilder(RealMetadataFactory.exampleBQTCached());
        Command obj = commandBuilder.getCommand(input, true, true);
        TranslationHelper.helpTestVisitor(output, trans, obj);
    }
    
    @Test public void testParseFormat() throws Exception {
        String input = "select parsetimestamp(smalla.timestampvalue, 'yyyy.MM.dd'), formattimestamp(smalla.timestampvalue, 'yy.MM.dd') from bqt1.smalla"; //$NON-NLS-1$
        String output = "SELECT CONVERT(DATETIME, convert(varchar(34), g_0.TimestampValue, 21), 102), CONVERT(VARCHAR, g_0.TimestampValue, 2) FROM SmallA g_0"; //$NON-NLS-1$
               
		CommandBuilder commandBuilder = new CommandBuilder(RealMetadataFactory.exampleBQTCached());
        Command obj = commandBuilder.getCommand(input, true, true);
        TranslationHelper.helpTestVisitor(output, trans, obj);
    }
    
    @Test public void testWithInsert() throws Exception {
        String input = "insert into bqt1.smalla (intkey) with a (x) as /*+ no_inline */ (select intnum from bqt1.smallb) select x from a"; //$NON-NLS-1$
        String output = "WITH a (x) AS (SELECT SmallB.IntNum FROM SmallB) INSERT INTO SmallA (IntKey) SELECT a.x FROM a"; //$NON-NLS-1$
               
		CommandBuilder commandBuilder = new CommandBuilder(RealMetadataFactory.exampleBQTCached());
        Command obj = commandBuilder.getCommand(input, true, true);
        TranslationHelper.helpTestVisitor(output, trans, obj);
    }
    
    @Test public void testLocate() throws Exception {
        String input = "select locate('a', stringkey, 2) from bqt1.smalla"; //$NON-NLS-1$
        String output = "SELECT CHARINDEX(CAST(N'a' AS NVARCHAR(4000)), g_0.StringKey, 2) FROM SmallA g_0"; //$NON-NLS-1$
               
		CommandBuilder commandBuilder = new CommandBuilder(RealMetadataFactory.exampleBQTCached());
        Command obj = commandBuilder.getCommand(input, true, true);
        TranslationHelper.helpTestVisitor(output, trans, obj);
    }
       
    /**
     * the first column expression needs converted to a single clause case
     * the second column condition in the case should not be altered
     * the third column nested boolean expression needs converted accounting for 3 level logic
     */
    @Test public void testBooleanExpression() throws Exception {
        String input = "select stringkey is null, case when stringkey = 'b' then 1 end, coalesce(intkey = 1, true) from bqt1.smalla"; //$NON-NLS-1$
        String output = "SELECT CASE WHEN g_0.StringKey IS NULL THEN 1 ELSE 0 END, CASE WHEN g_0.StringKey = N'b' THEN 1 END, isnull(CASE WHEN g_0.IntKey = 1 THEN 1 WHEN NOT (g_0.IntKey = 1) THEN 0 END, 1) FROM SmallA g_0"; //$NON-NLS-1$
               
        CommandBuilder commandBuilder = new CommandBuilder(RealMetadataFactory.exampleBQTCached());
        Command obj = commandBuilder.getCommand(input, true, true);
        TranslationHelper.helpTestVisitor(output, trans, obj);
    }
    
    @Disabled
    @Test
    public void testUpperLower() throws Exception {
        String input = "select upper(txt), lower(ntxt), upper(str) from tbl"; //$NON-NLS-1$
        String output = "SELECT upper(cast(tbl.txt AS varchar(max))), lower(cast(tbl.ntxt AS nvarchar(max))), upper(tbl.str) FROM tbl"; //$NON-NLS-1$
  
        TranslationHelper.helpTestVisitor("create foreign table tbl (txt string options (native_type 'text'), ntxt string options (native_type 'ntext'), str string)", input, output, trans);
    }

    @Test
    public void testUpperLowerView() throws Exception {
        String input = "select upper(txt) from (select txt from tbl) v"; //$NON-NLS-1$
        String output = "SELECT upper(v.txt) FROM (SELECT tbl.txt FROM tbl) v"; //$NON-NLS-1$

        TranslationHelper.helpTestVisitor("create foreign table tbl (txt string options (native_type 'text'), ntxt string options (native_type 'ntext'), str string)", input, output, trans);
    }

    @Test
 	public void testHashFunctions() throws Exception {
        String input = "select md5_binary(stringkey), sha2_256(stringkey) from bqt1.smalla"; //$NON-NLS-1$
        String output = "SELECT HASHBYTES('MD5', SmallA.StringKey), HASHBYTES('SHA2_256', SmallA.StringKey) FROM SmallA"; //$NON-NLS-1$

        SQLServerExecutionFactory trans1 = new SQLServerExecutionFactory();
        trans1.setDatabaseVersion(SQLServerExecutionFactory.V_2012);
        trans1.start();

        TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, trans1);
    }

    @Test public void testConvertSupport() throws Exception {
        assertTrue(trans.supportsConvert(TypeFacility.RUNTIME_CODES.OBJECT, TypeFacility.RUNTIME_CODES.TIME));
        assertTrue(trans.supportsConvert(TypeFacility.RUNTIME_CODES.OBJECT, TypeFacility.RUNTIME_CODES.INTEGER));
        assertFalse(trans.supportsConvert(TypeFacility.RUNTIME_CODES.OBJECT, TypeFacility.RUNTIME_CODES.CLOB));
    }

    @Test
    public void testVariantCast() throws Exception {
        assertTrue(trans.supportsConvert(FunctionModifier.OBJECT, FunctionModifier.STRING));
    }

    @Test
    public void testSupportConvertClobToString() throws Exception {
        SQLServerExecutionFactory ssef = new SQLServerExecutionFactory();
        assertTrue(ssef.supportsConvertFromClobToString());
    }

    @Test
    public void testNCharCast() throws Exception {
        String input = "select cast(txt as char) from tbl"; //$NON-NLS-1$
        String output = "SELECT cast(tbl.txt AS nchar(1)) FROM tbl"; //$NON-NLS-1$

        SQLServerExecutionFactory trans1 = new SQLServerExecutionFactory();
        trans1.setDatabaseVersion(SQLServerExecutionFactory.V_2008);
        trans1.start();

        TranslationHelper.helpTestVisitor("create foreign table tbl (txt string options (native_type 'ntext'))", input, output, trans);
    }

    @Test public void testUnicodeLiteral() throws Exception {
        String input = "select N'\u0FFF'"; //$NON-NLS-1$
        String output = "SELECT N'\u0FFF'"; //$NON-NLS-1$

        CommandBuilder commandBuilder = new CommandBuilder(RealMetadataFactory.exampleBQTCached());
        Command obj = commandBuilder.getCommand(input, true, true);
        TranslationHelper.helpTestVisitor(output, trans, obj);
    }
}
