/*
 * 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-3282
// https://issues.jboss.org/browse/TEIID-3526
// https://issues.jboss.org/browse/TEIID-2343
// https://issues.jboss.org/browse/TEIID-3046
// https://issues.jboss.org/browse/TEIID-5161
// 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
/*
 */
package org.teiid.translator.jdbc.sqlserver;

import com.datavirtuality.dv.core.interfaces.DVCaller;
import org.teiid.core.types.ClobType;
import org.teiid.core.types.DataTypeManager;
import org.teiid.core.util.NamesUtil;
import org.teiid.core.util.StringUtil;
import org.teiid.language.*;
import org.teiid.logging.LogConstants;
import org.teiid.logging.LogManager;
import org.teiid.metadata.Column;
import org.teiid.metadata.MetadataFactory;
import org.teiid.metadata.RuntimeMetadata;
import org.teiid.translator.*;
import org.teiid.translator.TypeFacility.RUNTIME_CODES;
import org.teiid.translator.jdbc.*;
import org.teiid.translator.jdbc.JDBCMetadataProcessor.ITypeConverter;
import org.teiid.translator.jdbc.sybase.SybaseExecutionFactory;

import java.math.BigDecimal;
import java.sql.Date;
import java.sql.*;
import java.text.DecimalFormat;
import java.text.DecimalFormatSymbols;
import java.util.*;

/**
 * Updated to assume the use of the DataDirect, 2005 driver, or later.
 */
@Translator(name="sqlserver", description="A translator for Microsoft SQL Server Database")
public class SQLServerExecutionFactory extends SybaseExecutionFactory implements MultilInsertExecutionFactoryIntf {
	private final class UpperLowerFunctionModifier extends AliasModifier {
		private UpperLowerFunctionModifier(String alias) {
			super(alias);
		}

		@Override
		public List<?> translate(Function function) {
			if (function.getParameters().get(0) instanceof ColumnReference) {
				ColumnReference cr = (ColumnReference) function.getParameters().get(0);
				if (cr.getMetadataObject() != null) {
					String nativeType = cr.getMetadataObject().getNativeType();
					// This is a trick followed for the tables created via Select into and simple create tables without the
					// metadata being refreshed as the native type is not stored for them
					if (nativeType == null && cr.getType().equals(ClobType.class)) {
						nativeType = "Text";
					}
					if (nativeType != null && StringUtil.indexOfIgnoreCase(nativeType, "char") == -1) { //$NON-NLS-1$
						Function cast = ConvertModifier.createConvertFunction(getLanguageFactory(), cr,
								(StringUtil.startsWithIgnoreCase(nativeType.trim(), "n") ? "n" : "") + "varchar(max)"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
						cast.setName("cast"); //$NON-NLS-1$
						function.getParameters().set(0, cast);
					}
				}
			}
			return super.translate(function);
		}
	}
	// for MultilInsertExecutionImpl
	protected String uploadMode = MultilInsertExecutionFactoryIntf.UploadMode.STANDARD.name();
	private Integer maxChunkSize = 1048576; // actually mssql supports queries max long of 268435456 bytes, that is 256 mb
	private Integer numberOfThreads = 3;

	public static final String V_2005 = "2005"; //$NON-NLS-1$
	public static final String V_2008 = "2008"; //$NON-NLS-1$
	public static final String V_2000 = "2000"; //$NON-NLS-1$
    public static final String V_2012 = "2012"; //$NON-NLS-1$
    public static final String V_2022 = "2022"; //$NON-NLS-1$

	public static final Version SIX_5 = Version.getVersion("6.5"); //$NON-NLS-1$
    public static final Version SEVEN_0 = Version.getVersion("7.0"); //$NON-NLS-1$
	public static final Version EIGHT_0 = Version.getVersion("8.0"); //$NON-NLS-1$
    public static final Version NINE_0 = Version.getVersion("9.0"); //$NON-NLS-1$
    public static final Version TEN_0 = Version.getVersion("10.0"); //$NON-NLS-1$
    public static final Version ELEVEN_0 = Version.getVersion("11.0"); //$NON-NLS-1$
	public static final Version TWELVE_0 = Version.getVersion("12.0"); //$NON-NLS-1$
	public static final Version THIRTEEN_0 = Version.getVersion("13.0"); //$NON-NLS-1$
    public static final Version FOURTEEN_0 = Version.getVersion("14.0"); //$NON-NLS-1$
	public static final Version FIFTEEN_0 = Version.getVersion("15.0"); //$NON-NLS-1$
    public static final Version SIXTEEN_0 = Version.getVersion("16.0"); //$NON-NLS-1$
	private Version compatibleVersion = TWELVE_0;

	static final class UniqueIdentifier {}

	private static final String TABLE = "TABLE";

	public SQLServerExecutionFactory() {
		setMaxInCriteriaSize(2000);
		setMaxDependentInPredicates(1);
		setSupportsMd5Function(true);
		setSupportsNativeQueries(true);
		setSupportsElementaryOlapOperations(true);
		setSupportsAdvancedOlapOperations(true);
		setSupportsAggregatesSum(true);
		setSupportsAggregatesAvg(true);
		setSupportsAggregatesMin(true);
		setSupportsAggregatesMax(true);
		setSupportsAggregatesCount(true);
		setSupportsAggregatesCountStar(true);
		setSupportsAggregatesDistinct(true);
		setSupportsElementaryOlapOperations(true);
		setSupportsOrderByNullOrdering(false);
		setSupportsFullOuterJoins(true);
		setMaxStringSize(2147483647);
	}

	@Override
	public void initCapabilities(Connection connection) throws TranslatorException {
		try(Statement s = connection.createStatement()) {
			try(ResultSet rs = s.executeQuery(String.format("select compatibility_level from sys.databases where name ='%s'", connection.getCatalog()))) {
				if (rs.next()) {
					int compatibilityLevel = rs.getInt(1);
					switch (compatibilityLevel) {
						case 65:
							compatibleVersion = SIX_5;
							break;
						case 70:
							compatibleVersion = SEVEN_0;
							break;
						case 80:
							compatibleVersion = EIGHT_0;
							break;
						case 90:
							compatibleVersion = NINE_0;
							break;
						case 100:
							compatibleVersion = TEN_0;
							break;
						case 110:
							compatibleVersion = ELEVEN_0;
							break;
						case 120:
							compatibleVersion = TWELVE_0;
							break;
						case 130:
							compatibleVersion = THIRTEEN_0;
							break;
						case 140:
							compatibleVersion = FOURTEEN_0;
							break;
						case 150:
							compatibleVersion = FIFTEEN_0;
							break;
						case 160:
							compatibleVersion = SIXTEEN_0;
							break;
					}
				}
			}
		} catch (SQLException e) {
			LogManager.logDetail(LogConstants.CTX_CONNECTOR, e, "Could not determine the compatibility level"); //$NON-NLS-1$
		}
		super.initCapabilities(connection);
		if (supportsStringAgg == null && getVersion().compareTo(FOURTEEN_0) >= 0) {
			setSupportsStringAgg(true);
			setSupportsDistinctInStringAgg(false);
		}
	}

	@Override
	public void start() throws TranslatorException {
		super.start();
		registerFunctionModifier(SourceSystemFunctions.WEEK, new FunctionModifier() {

			@Override
			public List<?> translate(Function function) {
				return Arrays.asList("DATEPART(ISO_WEEK, ", function.getParameters().get(0), ")"); //$NON-NLS-1$ //$NON-NLS-2$
			}
		});
		registerFunctionModifier(SourceSystemFunctions.LOCATE, new FunctionModifier() {
			@Override
			public List<?> translate(Function function) {
				final List<Expression> pars = function.getParameters();
				List<Object> objs = new ArrayList<>();
				objs.add("CHARINDEX(CAST(");
				objs.add(pars.get(0));
				objs.add(" AS NVARCHAR(4000))");
				for (int i = 1; i < pars.size(); i++) {
					objs.add(", ");
					objs.add(pars.get(i));
				}
				objs.add(")");
				return objs;
			}
		});
		registerFunctionModifier(SourceSystemFunctions.MD5_BINARY, new TemplateFunctionModifier("HASHBYTES('MD5', ", 0, ")")); //$NON-NLS-1$ //$NON-NLS-2$
		registerFunctionModifier(SourceSystemFunctions.SHA1, new TemplateFunctionModifier("HASHBYTES('SHA1', ", 0, ")")); //$NON-NLS-1$ //$NON-NLS-2$
		registerFunctionModifier(SourceSystemFunctions.SHA2_256, new TemplateFunctionModifier("HASHBYTES('SHA2_256', ", 0, ")")); //$NON-NLS-1$ //$NON-NLS-2$
		registerFunctionModifier(SourceSystemFunctions.SHA2_512, new TemplateFunctionModifier("HASHBYTES('SHA2_512', ", 0, ")")); //$NON-NLS-1$ //$NON-NLS-2$
		registerFunctionModifier(SourceSystemFunctions.POWER, new EscapeSyntaxModifier() {

			@Override
			public List<?> translate(Function function) {
				return Arrays.asList("power(cast(", function.getParameters().get(0), " as numeric(38,20)),", function.getParameters().get(1), ")");
			}
		});

		registerFunctionModifier(SourceSystemFunctions.NOW, new AliasModifier("getdate"));

		FunctionModifier curdateModifier = (FunctionModifier) DVCaller.call(132);
        if (curdateModifier != null) {
        	registerFunctionModifier(SourceSystemFunctions.CURDATE, curdateModifier);
        }

		DVCaller.call(85, this);
		MultilInsertExecutionFactoryIntf.Util.setupUploadMode(this);
        registerFunctionModifier(SourceSystemFunctions.UCASE, new UpperLowerFunctionModifier("upper")); //$NON-NLS-1$ 
        registerFunctionModifier(SourceSystemFunctions.LCASE, new UpperLowerFunctionModifier("lower")); //$NON-NLS-1$
		registerFunctionModifier("string_agg", new StringAggFunctionModifier());
	}

	@Override
	protected void populateDateFormats() {
		formatMap.put("MM/dd/yy", 1); //$NON-NLS-1$
		formatMap.put("yy.MM.dd", 2); //$NON-NLS-1$
		formatMap.put("dd/MM/yy", 3); //$NON-NLS-1$
		formatMap.put("dd.MM.yy", 4); //$NON-NLS-1$
		formatMap.put("dd-MM-yy", 5); //$NON-NLS-1$
		formatMap.put("dd MMM yy", 6); //$NON-NLS-1$
		formatMap.put("MMM dd, yy", 7); //$NON-NLS-1$
		formatMap.put("MM-dd-yy", 10); //$NON-NLS-1$
		formatMap.put("yy/MM/dd", 11); //$NON-NLS-1$
		formatMap.put("yyMMdd", 12); //$NON-NLS-1$
		for (Map.Entry<String, Integer> entry : new HashSet<Map.Entry<String, Integer>>(formatMap.entrySet())) {
			formatMap.put(entry.getKey().replace("yy", "yyyy"), entry.getValue() + 100); //$NON-NLS-1$ //$NON-NLS-2$
		}

		formatMap.put("MMM d yyyy hh:mma", 100); //$NON-NLS-1$
		formatMap.put("HH:mm:ss", 8); //$NON-NLS-1$
		formatMap.put("MMM d yyyy hh:mm:ss:SSSa", 109); //$NON-NLS-1$
		formatMap.put("dd MMM yyyy HH:mm:ss:SSS", 113); //$NON-NLS-1$
		formatMap.put("kk:MM:ss:SSS", 14); //$NON-NLS-1$
		formatMap.put("yyyy-MM-dd HH:mm:ss", 120); //$NON-NLS-1$
		formatMap.put("yyyy-MM-dd HH:mm:ss.SSS", 121); //$NON-NLS-1$
		formatMap.put("yyyy-MM-dd'T'HH:mm:ss.SSS", 126); //$NON-NLS-1$
		//formatMap.put("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'", 127); //$NON-NLS-1$
	}

	@Override
	protected void populateConvertTypeMappings(ConvertModifier convertModifier) {
		convertModifier.addTypeMapping("bigint", FunctionModifier.LONG);
		convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.DATE, new FunctionModifier() {
			@Override
			public List<?> translate(Function function) {
				return Collections.singletonList(function);
			}
		});
		convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.TIME, new FunctionModifier() {
			@Override
			public List<?> translate(Function function) {
                return Collections.singletonList(function);
			}
		});
	}

	@Override
	protected List<Object> convertDateToString(Function function) {
		return Arrays.asList("replace(convert(varchar, ", function.getParameters().get(0), ", 102), '.', '-')"); //$NON-NLS-1$ //$NON-NLS-2$
	}

	@Override
	protected List<?> convertTimestampToString(Function function) {
		return Arrays.asList("convert(varchar(34), ", function.getParameters().get(0), ", 21)"); //$NON-NLS-1$ //$NON-NLS-2$
	}

    @Override
    public List<?> translate(LanguageObject obj, ExecutionContext context, boolean isUpdate) {
		// <<< in scope of DVCORE-6869 we decided to get rid of the partial casting just for consistency
		//
//		if (getVersion().compareTo(SEVEN_0) > 0) {
//			if (obj instanceof SortSpecification) {
//				SortSpecification ss = (SortSpecification)obj;
//				if (ss.getExpression() instanceof ColumnReference && isUniqueIdentifier(((ColumnReference)ss.getExpression()))) {
//					((ColumnReference)ss.getExpression()).setType(UniqueIdentifier.class); //prevent the cast
//				}
//			}
//			if (obj instanceof DerivedColumn) {
//				DerivedColumn dc = (DerivedColumn)obj;
//				if (dc.getExpression() instanceof ColumnReference && isUniqueIdentifier(((ColumnReference)dc.getExpression()))) {
//					((ColumnReference)dc.getExpression()).setType(UniqueIdentifier.class); //prevent the cast
//				}
//			} else if (obj instanceof Comparison) {
//				Comparison comp = (Comparison)obj;
//				if (comp.getOperator() == Comparison.Operator.EQ
//						&& comp.getLeftExpression() instanceof ColumnReference && isUniqueIdentifier((ColumnReference)comp.getLeftExpression())
//						&& (comp.getRightExpression() instanceof Literal || comp.getRightExpression() instanceof Parameter)) {
//					((ColumnReference)comp.getLeftExpression()).setType(UniqueIdentifier.class); //prevent the cast
//				}
//				else if (comp.getOperator() == Comparison.Operator.EQ
//						&& isUniqueIdentifier(comp.getLeftExpression())
//						&& isUniqueIdentifier(comp.getRightExpression())) {
//					((ColumnReference)comp.getLeftExpression()).setType(UniqueIdentifier.class); //prevent the cast
//					((ColumnReference)comp.getRightExpression()).setType(UniqueIdentifier.class); //prevent the cast
//				}
//			} else if (obj instanceof In) {
//				In in = (In)obj;
//				if (isUniqueIdentifier(in.getLeftExpression())) {
//					boolean ok = true;
//					for (Expression ex : in.getRightExpressions()) {
//						if (ex instanceof Literal || ex instanceof Parameter)
//							continue;
//						ok = false;
//						break;
//					}
//					if (ok)
//						((ColumnReference)in.getLeftExpression()).setType(UniqueIdentifier.class); //prevent the cast
//				}
//			} else if (obj instanceof AggregateFunction) {
//				AggregateFunction f = (AggregateFunction)obj;
//				for (Expression p : f.getParameters()) {
//					if (p instanceof ColumnReference && isUniqueIdentifier(((ColumnReference)p))) {
//						((ColumnReference)p).setType(UniqueIdentifier.class); //prevent the cast
//					}
//				}
//			}
//		}
		if (obj instanceof Insert)	{
			Insert f = (Insert) obj;
			for (Expression p : f.getColumns()) {
				if (p instanceof ColumnReference && isUniqueIdentifier(((ColumnReference) p))) {
					((ColumnReference) p).setType(UniqueIdentifier.class); //prevent the cast
				}
			}
		}
    	if (obj instanceof ColumnReference) {
    		ColumnReference elem = (ColumnReference)obj;
			if (isUniqueIdentifier(elem) && castUuidToString) {
				return Arrays.asList("cast(", elem, " as char(36))"); //$NON-NLS-1$ //$NON-NLS-2$
			}
    	} else if (obj instanceof AggregateFunction) {
    		AggregateFunction af = (AggregateFunction)obj;
    		if (af.getName().equals(AggregateFunction.STDDEV_POP)) {
    			af.setName("STDDEVP"); //$NON-NLS-1$
    		} else if (af.getName().equals(AggregateFunction.STDDEV_SAMP)) {
    			af.setName("STDDEV"); //$NON-NLS-1$
    		} else if (af.getName().equals(AggregateFunction.VAR_POP)) {
    			af.setName("VARP"); //$NON-NLS-1$
    		} else if (af.getName().equals(AggregateFunction.VAR_SAMP)) {
    			af.setName("VAR"); //$NON-NLS-1$
    		}
    	} else if (obj instanceof SortSpecification) {
    		if (((SortSpecification) obj).getNullOrdering() == null)
    			return null;
    		// actually MS doesn't support OrderbyNull (i.e. NULLS FIRST/LAST) as we set setSupportsOrderByNullOrdering(false) at the constructor,
    		// and CriteriaCapabilityValidatorVisitor.visit(OrderByItem) knows about it and checks it againt defaults, but anyway it is explicitly
    		// specified, the normal code renders it. this is why we cut it off.
    		SortSpecification obj1 = new SortSpecification(((SortSpecification) obj).getOrdering(), ((SortSpecification) obj).getExpression());
    		return Arrays.asList(obj1);
		} else if (obj instanceof DerivedColumn) {
			DerivedColumn dc = (DerivedColumn) obj;
			if (dc.getExpression() instanceof ColumnReference && isHierarchyid((ColumnReference) dc.getExpression())) {
				return Arrays.asList(dc, ".ToString()");
			}
		}
    	return super.translate(obj, context, isUpdate);
    }

	@Override
	public String translateGetStringLiteralPrefix() {
		return "N";
	}

	private boolean isUniqueIdentifier(ColumnReference elem) {
		return TypeFacility.RUNTIME_TYPES.STRING.equals(elem.getType()) && elem.getMetadataObject() != null && "uniqueidentifier".equalsIgnoreCase(elem.getMetadataObject().getNativeType()); //$NON-NLS-1$
	}

	private boolean isHierarchyid(ColumnReference elem) {
		return TypeFacility.RUNTIME_TYPES.STRING.equals(elem.getType()) && elem.getMetadataObject() != null && "hierarchyid".equalsIgnoreCase(elem.getMetadataObject().getNativeType()); //$NON-NLS-1$
	}

	private boolean isUniqueIdentifier(Expression ex) {
		if (!(ex instanceof ColumnReference))
			return false;
		return isUniqueIdentifier((ColumnReference) ex);
	}

	@Override
	public List<?> translateConversionString(Condition obj, ExecutionContext context, boolean isUpdate,
											 List<?> converted) {
		if (obj instanceof Comparison) {
			if (isUniqueIdentifier(((Comparison) obj).getLeftExpression()) || isUniqueIdentifier(((Comparison) obj).getRightExpression()))
				return null;
		}
		if (obj instanceof Like) {
			if (isUniqueIdentifier(((Like) obj).getLeftExpression()) || isUniqueIdentifier(((Like) obj).getRightExpression()))
				return null;
		}
		return converted;
	}

    @Override
    public List<String> getSupportedFunctions() {
        List<String> supportedFunctions = new ArrayList<String>();
        supportedFunctions.add("ABS"); //$NON-NLS-1$
		supportedFunctions.addAll(super.getDefaultSupportedFunctions());
		supportedFunctions.add("ACOS"); //$NON-NLS-1$
        supportedFunctions.add("ASIN"); //$NON-NLS-1$
        supportedFunctions.add("ATAN"); //$NON-NLS-1$
        supportedFunctions.add("ATAN2"); //$NON-NLS-1$
        supportedFunctions.add("COS"); //$NON-NLS-1$
        supportedFunctions.add("COT"); //$NON-NLS-1$
        supportedFunctions.add("DEGREES"); //$NON-NLS-1$
        supportedFunctions.add("EXP"); //$NON-NLS-1$
        supportedFunctions.add("FLOOR"); //$NON-NLS-1$
        supportedFunctions.add("LOG"); //$NON-NLS-1$
        supportedFunctions.add("LOG10"); //$NON-NLS-1$
        supportedFunctions.add("MOD"); //$NON-NLS-1$
        supportedFunctions.add("PI"); //$NON-NLS-1$
        supportedFunctions.add("POWER"); //$NON-NLS-1$
        supportedFunctions.add("RADIANS"); //$NON-NLS-1$
        supportedFunctions.add("SIGN"); //$NON-NLS-1$
        supportedFunctions.add("SIN"); //$NON-NLS-1$
        supportedFunctions.add("SQRT"); //$NON-NLS-1$
        supportedFunctions.add("TAN"); //$NON-NLS-1$
        supportedFunctions.add("ASCII"); //$NON-NLS-1$
        supportedFunctions.add("CHAR"); //$NON-NLS-1$
        supportedFunctions.add("CHR"); //$NON-NLS-1$
        supportedFunctions.add("CONCAT"); //$NON-NLS-1$
        supportedFunctions.add("||"); //$NON-NLS-1$
        //supportedFunctons.add("INITCAP"); //$NON-NLS-1$
        supportedFunctions.add("LCASE"); //$NON-NLS-1$
        supportedFunctions.add("LEFT"); //$NON-NLS-1$
        supportedFunctions.add("LENGTH"); //$NON-NLS-1$
        //supportedFunctons.add("LOCATE"); //$NON-NLS-1$
        supportedFunctions.add(SourceSystemFunctions.LOCATE);
        supportedFunctions.add("LOWER"); //$NON-NLS-1$
        //supportedFunctons.add("LPAD"); //$NON-NLS-1$
        supportedFunctions.add("LTRIM"); //$NON-NLS-1$
        supportedFunctions.add("REPEAT"); //$NON-NLS-1$
	    //rand is evaulated deterministically by SQL Server
	    //if (getVersion().compareTo(TEN_0) >= 0) {
	        //supportedFunctions.add("RAND"); //$NON-NLS-1$
	    //}
        supportedFunctions.add("REPLACE"); //$NON-NLS-1$
        supportedFunctions.add("RIGHT"); //$NON-NLS-1$
        //supportedFunctons.add("RPAD"); //$NON-NLS-1$
        supportedFunctions.add("RTRIM"); //$NON-NLS-1$
        supportedFunctions.add("SPACE"); //$NON-NLS-1$
        supportedFunctions.add("SUBSTRING"); //$NON-NLS-1$
        //supportedFunctons.add("TRANSLATE"); //$NON-NLS-1$
        supportedFunctions.add("UCASE"); //$NON-NLS-1$
        supportedFunctions.add("UPPER"); //$NON-NLS-1$
        //supportedFunctons.add("CURDATE"); //$NON-NLS-1$
        //supportedFunctons.add("CURTIME"); //$NON-NLS-1$
        supportedFunctions.add("DAYNAME"); //$NON-NLS-1$
        supportedFunctions.add("DAYOFMONTH"); //$NON-NLS-1$
        supportedFunctions.add("DAYOFWEEK"); //$NON-NLS-1$
        supportedFunctions.add("DAYOFYEAR"); //$NON-NLS-1$
        supportedFunctions.add("HOUR"); //$NON-NLS-1$
        supportedFunctions.add("MINUTE"); //$NON-NLS-1$
        supportedFunctions.add("MONTH"); //$NON-NLS-1$
        supportedFunctions.add("MONTHNAME"); //$NON-NLS-1$
        //supportedFunctions.add("NOW"); //$NON-NLS-1$
        supportedFunctions.add("QUARTER"); //$NON-NLS-1$
        supportedFunctions.add("SECOND"); //$NON-NLS-1$
        supportedFunctions.add("TIMESTAMPADD"); //$NON-NLS-1$
        supportedFunctions.add("TIMESTAMPDIFF"); //$NON-NLS-1$
        supportedFunctions.add("WEEK"); //$NON-NLS-1$
        supportedFunctions.add("YEAR"); //$NON-NLS-1$
        supportedFunctions.add("CAST"); //$NON-NLS-1$
        supportedFunctions.add("CONVERT"); //$NON-NLS-1$
        supportedFunctions.add("IFNULL"); //$NON-NLS-1$
        supportedFunctions.add("NVL");      //$NON-NLS-1$ 
        supportedFunctions.add(SourceSystemFunctions.FORMATTIMESTAMP);
        supportedFunctions.add(SourceSystemFunctions.PARSETIMESTAMP);
	    if (getVersion().compareTo(SIXTEEN_0) >= 0) {
		}
	    if (getVersion().compareTo(TEN_0) >= 0) {
		    supportedFunctions.add(SourceSystemFunctions.SHA2_256);
		    supportedFunctions.add(SourceSystemFunctions.SHA2_512);
	    }

	    if (getVersion().compareTo(NINE_0) >= 0) {
		    supportedFunctions.add(SourceSystemFunctions.MD5_BINARY);
		    supportedFunctions.add(SourceSystemFunctions.SHA1);
	    }

        return supportedFunctions;
    }

    @Override
    public boolean supportsInlineViews() {
        return true;
    }

    @Override
    public boolean supportsFunctionsInGroupBy() {
        return true;
    }
    @Override
    public boolean supportsRowLimit() {
        return true;
    }

    @Override
    public boolean supportsIntersect() {
    	return true;
    }
    @Override
    public boolean supportsExcept() {
    	return true;
    };

    @Override
    public int getMaxFromGroups() {
        return DEFAULT_MAX_FROM_GROUPS;
    }

    @Override
    public boolean supportsAggregatesEnhancedNumeric() {
    	return true;
    }

    @Override
    public boolean nullPlusNonNullIsNull() {
    	return true;
    }

    @Override
    public boolean booleanNullable() {
    	return true;
    }

	@Override
	public void setDatabaseVersion(String version) {
		if (version != null) {
			if (version.equals(V_2000)) {
				setDatabaseVersion(SEVEN_0);
				return;
			} else if (version.equals(V_2005)) {
				setDatabaseVersion(NINE_0);
				return;
			} else if (version.equals(V_2008)) {
				setDatabaseVersion(TEN_0);
				return;
			} else if (version.equals(V_2012)) {
				setDatabaseVersion(ELEVEN_0);
				return;
			} else if (version.equals(V_2022)) {
				setDatabaseVersion(SIXTEEN_0);
				return;
			}
		}
		super.setDatabaseVersion(version);
	}

	@Override
	public Version getCompatibleVersion() {
		return compatibleVersion;
	}

	@Override
    public void bindValue(PreparedStatement stmt, Object param, Class<?> paramType, int i) throws SQLException {
    	if (java.util.Date.class.isAssignableFrom(paramType) && param != null) {
    		java.util.Date date = (java.util.Date) param;
    		if (date.after(get10kMinusOneDate()))
    			param = null;
    	}
		if (java.lang.Double.class.isAssignableFrom(paramType) && param != null) {
			java.lang.Double d = ((Number) param).doubleValue();
			param = pretranslateDouble(d);
		}
		if (java.lang.Float.class.isAssignableFrom(paramType) && param != null) {
			java.lang.Float f = ((Number) param).floatValue();
			param = pretranslateFloat(f);
		}
    	super.bindValue(stmt, param, paramType, i);
    }

    @Override
    public String translateLiteralDate(Date dateValue) {
    	if (getVersion().compareTo(TEN_0) >= 0) {
    		return super.translateLiteralDate(dateValue);
    	}
    	return super.translateLiteralTimestamp(new Timestamp(dateValue.getTime()));
    }

    @Override
    public String translateLiteralTime(Time timeValue) {
    	return "CAST('" + formatDateValue(timeValue) +"' AS TIME)";
    }

    @Override
    public String translateLiteralTimestamp(Timestamp timestampValue) {
    	return "CAST('" + formatDateValue(timestampValue) +"' AS DATETIME2)";
    }

    @Override
    public boolean hasTimeType() {
    	return getVersion().compareTo(TEN_0) >= 0;
    }

    @Override
    public boolean supportsCommonTableExpressions() {
    	return true;
    }

	@Override
	public boolean supportsRecursiveCommonTableExpressions() {
		return getVersion().compareTo(TEN_0) >= 0;
	}

    @Override
    public boolean supportsSubqueryCommonTableExpressions() {
       	return false;
    }

    @Override
    protected boolean supportsCrossJoin() {
    	return true;
    }

    @Override
    public boolean supportsSelectWithoutFrom() {
    	return true;
    }

    @Override
    public boolean supportsWindowDistinctAggregates() {
    	return false;
    }

    @Override
    public boolean supportsFormatLiteral(String literal,
    		org.teiid.translator.ExecutionFactory.Format format) {
    	if (format == Format.NUMBER) {
    		return false; //TODO: add support
    	}
    	return formatMap.containsKey(literal);
    }

    @Override
    public boolean supportsOnlyFormatLiterals() {
    	return true;
    }

    @Override
    protected JDBCMetadataProcessor createMetadataProcessor() {
    	 return new JDBCMulticatalogProcessor() {

             @Override
             protected Column addColumn(ResultSet columns, TableInfo tableInfo, MetadataFactory metadataFactory, int rsColumns, String fullTableName, Set<String> fullNames, int initialCapacity)
                     throws SQLException {
                 Column c = super.addColumn(columns, tableInfo, metadataFactory, rsColumns, fullTableName, fullNames, initialCapacity);
                 //The ms jdbc driver does not correctly report the auto incremented column
                 if (c != null && !c.isAutoIncremented() && c.getNativeType() != null && StringUtil.endsWithIgnoreCase(c.getNativeType(), " identity")) { //$NON-NLS-1$
                     c.setAutoIncremented(true);
                 }
                 return c;
             }

			 @Override
			 protected void getProcedureColumns(MetadataFactory metadataFactory, DatabaseMetaData metadata, Collection<String> schemaPatterns, Map<String, ProcInfo> procMap) throws SQLException {
				 super.getProcedureColumns(metadataFactory, metadata, schemaPatterns, procMap);
				 //noinspection SqlNoDataSourceInspection
				 try (PreparedStatement p = metadata.getConnection().prepareStatement("SELECT " +
						 "  TABLE_CATALOG," + // 1
						 "  TABLE_SCHEMA," + // 2
						 "  TABLE_NAME," + // 3
						 "  COLUMN_NAME," + // 4
						 "  3," + // 5
						 "  case" + // 6
						 "    when DATA_TYPE='tinyint' then " + Types.TINYINT +
						 "    when DATA_TYPE='bit' then " + Types.BIT +
						 "    when DATA_TYPE='smallint' then " + Types.SMALLINT +
						 "    when DATA_TYPE='int' then " + Types.INTEGER +
						 "    when DATA_TYPE='bigint' then " + Types.BIGINT +
						 "    when DATA_TYPE='float' then " + Types.DOUBLE +
						 "    when DATA_TYPE='real' then " + Types.REAL +
						 "    when DATA_TYPE='smalldatetime' then " + Types.TIMESTAMP +
						 "    when DATA_TYPE='timestamp' then " + Types.TIMESTAMP +
						 "    when DATA_TYPE='datetime' then " + Types.TIMESTAMP +
						 "    when DATA_TYPE='datetime2' then " + Types.TIMESTAMP +
						 "    when DATA_TYPE='datetimeoffset' then " + Types.TIMESTAMP +
						 "    when DATA_TYPE='date' then " + Types.DATE +
						 "    when DATA_TYPE='time' then " + Types.TIME +
						 "    when DATA_TYPE='smallmoney' then " + Types.DECIMAL +
						 "    when DATA_TYPE='money' then " + Types.DECIMAL +
						 "    when DATA_TYPE='char' then " + Types.CHAR +
						 "    when DATA_TYPE='varchar' and CHARACTER_OCTET_LENGTH!=-1 then " + Types.VARCHAR +
						 "    when DATA_TYPE='varchar' and CHARACTER_OCTET_LENGTH=-1 then " + Types.LONGVARCHAR +
						 "    when DATA_TYPE='nvarchar' and CHARACTER_OCTET_LENGTH!=-1 then " + Types.NVARCHAR +
						 "    when DATA_TYPE='nvarchar' and CHARACTER_OCTET_LENGTH=-1 then " + Types.LONGNVARCHAR +
						 "    when DATA_TYPE='text' then " + Types.CLOB +
						 "    when DATA_TYPE='ntext' then " + Types.NCLOB +
						 "    when DATA_TYPE='binary' then " + Types.BINARY +
						 "    when DATA_TYPE='varbinary' and CHARACTER_OCTET_LENGTH!=-1 then " + Types.VARBINARY +
						 "    when DATA_TYPE='varbinary' and CHARACTER_OCTET_LENGTH=-1 then " + Types.LONGVARBINARY +
						 "    when DATA_TYPE='image' then " + Types.LONGVARBINARY +
						 "    when DATA_TYPE='decimal' then " + Types.DECIMAL +
						 "    when DATA_TYPE='numeric' then " + Types.NUMERIC +
						 "    when DATA_TYPE='xml' then " + Types.LONGVARCHAR +
						 "  end," +
						 "  DATA_TYPE," + // 7
						 "  NUMERIC_PRECISION," + // 8
						 "  CHARACTER_OCTET_LENGTH," + // 9
						 "  NUMERIC_SCALE," + // 10
						 "  NUMERIC_PRECISION_RADIX," + // 11
						 "  case IS_NULLABLE when 'YES' then 1 else 0 end," + // 12
						 "  null," + // 13
						 "  null," + // 14
						 "  null," + // 15
						 "  null," + // 16
						 "  null," + // 17
						 "  ORDINAL_POSITION" + // 18
						 " FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS " +
						 "ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION")) {
					 try (ResultSet rs = p.executeQuery()) {
						 int count = rs.getMetaData().getColumnCount();
						 while (rs.next()) {
							 getProcedureColumn(metadataFactory, procMap, rs, count);
						 }
					 }
				 }
			 }
         };
    }

    @Override
	public UpdateExecution createUpdateExecution(Command command, ExecutionContext executionContext, RuntimeMetadata metadata, Connection connection) throws TranslatorException {
		UpdateExecution updateExecution = MultilInsertExecutionFactoryIntf.Util.createUpdateExecution(this, command, executionContext, metadata, connection);
		if (updateExecution != null)
			return updateExecution;
		return new SQLServerJDBCUpdateExecution(command, connection, executionContext, this);
	}

	@Override
	public boolean returnsSingleUpdateCount() {
		return true;
	}

	private boolean singleTransactionInsert = true;

	@TranslatorProperty(description="Commits update only after all data is loaded.", display="Use Streaming Update")
	public boolean isSingleTransactionInsert() {
		return singleTransactionInsert;
	}

	public void setSingleTransactionInsert(boolean singleTransactionInsert) {
		this.singleTransactionInsert = singleTransactionInsert;
	}

	static class SQLServerJDBCUpdateExecution extends JDBCUpdateExecution {
		private boolean singleTransactionInsert;

		public SQLServerJDBCUpdateExecution(Command command, Connection connection, ExecutionContext context, SQLServerExecutionFactory env) {
			super(command, connection, context, env);
			this.singleTransactionInsert = env.isSingleTransactionInsert();
		}

		@Override
		public void executeTranslatedCommand(TranslatedCommand translatedComm) throws TranslatorException {
			if (!singleTransactionInsert) {
				super.executeTranslatedCommand(translatedComm);
			}
			boolean commitType = false;
			boolean rolledBack = false;
			try {
				commitType = connection.getAutoCommit();
			} catch (SQLException e) {
				throw new TranslatorException(e, TranslatorJdbcPlugin.Util.gs(TranslatorJdbcPlugin.Event.DVCORE94092));
			}
			if (commitType) {
				try {
					connection.setAutoCommit(false);
				} catch (SQLException e) {
					throw new TranslatorException(e, TranslatorJdbcPlugin.Util.gs(TranslatorJdbcPlugin.Event.DVCORE94093));
				}
			}
			try {
				super.executeTranslatedCommand(translatedComm);
			} catch (TranslatorException e) {
				if (commitType)
					try {
						connection.rollback();
						rolledBack = true;
					} catch (SQLException e1) {
						e1.printStackTrace();
					}
				throw e;
			} finally {
				if (commitType) {
					try {
						if (!rolledBack)
							connection.commit();
						connection.setAutoCommit(true);
					} catch (SQLException e) {
						LogManager.logDetail(LogConstants.CTX_CONNECTOR, e, "Exception closing"); //$NON-NLS-1$
					}
				}
			}
		}
	}

	@Override
    protected boolean setFetchSizeOnCallableStatements() {
    	return true;
    }

	@Override
	public boolean useStreamsForLobs() {
		return true;
	}

	@Override
	protected boolean usesDatabaseVersion() {
		return true;
	}

	@Override
	public boolean supportsWindowWithoutOrderBy() {
    	return false;
	}

	@TranslatorProperty(display="Supports Order By String", description="True, if source supports strings in ORDER BY clause", advanced=true)
	public boolean supportsOrderByString() {
		return supportsOrderByString != null ? supportsOrderByString : true;
	}

	public ITypeConverter getTypeConverter() {
		return new MSSQLITypeConverter();
	}

	protected class MSSQLITypeConverter extends ITypeConverter {
		@Override
		public String getRuntimeType(int type, String typeName, int precision, int scale) {
			switch (type) {
				case -11: //uuid
					return DataTypeManager.DefaultDataTypes.STRING;
				case -154:
					return DataTypeManager.DefaultDataTypes.TIME;
				case 11:
					return DataTypeManager.DefaultDataTypes.TIMESTAMP;
				case 9:
					return DataTypeManager.DefaultDataTypes.DATE;
			}
			if ("DATETIMEOFFSET".equalsIgnoreCase(typeName))
				return DataTypeManager.DefaultDataTypes.TIMESTAMP;
			if ("NCHAR".equalsIgnoreCase(typeName)) {
				if (precision == 1)
					return DataTypeManager.DefaultDataTypes.CHAR;
				return DataTypeManager.DefaultDataTypes.STRING;
			}
			if ("HIERARCHYID".equalsIgnoreCase(typeName)) {
				return DataTypeManager.DefaultDataTypes.STRING;
			}
			return null;
		}

		@Override
		public void tuneColumn(Column column, int type, String typeName, int precision, int scale) {
			if ("NVARCHAR".equalsIgnoreCase(typeName) && precision == 2147483647)
				column.setLength(getMaxStringSize());
			else if ("TEXT".equalsIgnoreCase(typeName) || "NTEXT".equalsIgnoreCase(typeName))
				column.setLength(2147483647);
			super.tuneColumn(column, type, typeName, precision, scale);
		}

		@Override
		public boolean filterProcedureColumn(String columnName, int type, String typeName, int precision, int scale) {
				if (TABLE.equalsIgnoreCase(typeName))
				return false;
			return super.filterProcedureColumn(columnName, type, typeName, precision, scale);
		}

		@Override
		public String getProcedureColumnRuntimeType(int type, String typeName, int precision, int scale) {
			if ("uniqueidentifier".equalsIgnoreCase(typeName))
				return DataTypeManager.DefaultDataTypes.STRING;
			if ("sql_variant".equalsIgnoreCase(typeName))
				return DataTypeManager.DefaultDataTypes.OBJECT;
			return super.getProcedureColumnRuntimeType(type, typeName, precision, scale);
		}
	}

	@Override
	public boolean supportsConvert(int fromType, int toType) {
		if ((fromType == RUNTIME_CODES.VARBINARY && toType == RUNTIME_CODES.LONG)
				|| (fromType == RUNTIME_CODES.LONG && toType == RUNTIME_CODES.VARBINARY) ||
				(fromType == TypeFacility.RUNTIME_CODES.OBJECT && this.convertModifier.hasTypeMapping(toType)))
			return true;
		return super.supportsConvert(fromType, toType);
	}

	public ArrayEmulation supportsArrayEmulation() {
		return ArrayEmulation.BINARY;
	}

	@Override
	public List<?> translateCommand(Command command, ExecutionContext context) {
	   	if (command instanceof Insert) {
	   		Insert insert = (Insert)command;
	   		if (insert.getValueSource() instanceof QueryExpression) {
	   			QueryExpression qe = (QueryExpression)insert.getValueSource();
	   			if (qe.getWith() != null) {
	   				With with = qe.getWith();
	   				qe.setWith(null);
	   				return Arrays.asList(with, insert);
	   			}
	   		}
	   	}
		if (command instanceof Call) {
			Call call = (Call) command;
			List<Object> l = new ArrayList<>(Arrays.asList("select * from ", NamesUtil.addBrackets(call.getProcedureName()), "("));
			boolean comma = false;
			for (Argument param : call.getArguments()) {
				if (param.getDirection() == Argument.Direction.IN || param.getDirection() == Argument.Direction.OUT || param.getDirection() == Argument.Direction.INOUT) {
					if (comma)
						l.add(",");
					else
						comma = true;
					l.add(param);
				}
			}
			l.add(")");
			return l;
		}
	   	return super.translateCommand(command, context);
	}

	@Override
	public boolean supportsConvertFromClobToString() {
    	if (supportsConvertFromClobToString == null)
			return true;
		return super.supportsConvertFromClobToString();
    }

	@TranslatorProperty(display = "Data upload mode", description = "Data upload mode", advanced = true)
	public String uploadMode() {
		return uploadMode;
	}

	public void setUploadMode(String uploadMode) throws TranslatorException {
		this.uploadMode = uploadMode;
	}

	@TranslatorProperty(display = "The maximun size of a chunk", description = "The maximun chunk size of the insert statement", advanced = true)
	public Integer maxChunkSize() {
		return this.maxChunkSize;
	}

	public void setMaxChunkSize(Integer maxChunkSize) {
		this.maxChunkSize = maxChunkSize;
	}

	@TranslatorProperty(display = "The number of parallel thread", description = "The number of parallel thread to use in multithread mode", advanced = true)
	public Integer numberOfThreads() {
		return this.numberOfThreads;
	}

	public void setNumberOfThreads(Integer numberOfThreads) {
		this.numberOfThreads = numberOfThreads;
	}

	@Override
	public Integer maxRowCount() {
		return 1000;
	}

	@Override
	public String valueToString(ColumnReference col, Object value) throws TranslatorException {
		if (value instanceof Boolean) {
			value = (Boolean) value ? 1 : 0;
		}
		return MultilInsertExecutionFactoryIntf.Util.defaultValueToString(this, col, value);
	}

	@Override
	public String quoteValue(String value) {
		return "'" + value.replace("'", "''") + "'";
	}

	@Override
	public String quoteBinary(String value) {
		return "0x" + value;
	}

	private static DecimalFormat BINARY_DOUBLE_FORMAT = new DecimalFormat("0.################E0",
			new DecimalFormatSymbols(Locale.ENGLISH));
	public Object translateLiteralFloat(float obj) {
		if (Math.abs(BigDecimal.valueOf(obj).scale()) > 36)
			return BINARY_DOUBLE_FORMAT.format(obj);
		return obj;
	}

	public Object translateLiteralDouble(double obj) {
		if (Math.abs(BigDecimal.valueOf(obj).scale()) > 36)
			return BINARY_DOUBLE_FORMAT.format(obj);
		return obj;
	}

	boolean castUuidToString = true;
	@TranslatorProperty(display="Cast uniqueidentifier to string", description="True by default. Cast uniqueidentifier to string.", advanced=true)
	public boolean castUuidToString() {
		return castUuidToString;
	}
	public void setCastUuidToString(boolean castUuidToString) {
		this.castUuidToString = castUuidToString;
	}
	}
