/*
 * 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-2159
// https://issues.jboss.org/browse/TEIID-2572
// https://issues.redhat.com/browse/TEIID-2815
// https://issues.redhat.com/browse/TEIID-3869
// https://issues.redhat.com/browse/TEIID-4646 (partially, only this class)
// https://issues.redhat.com/browse/TEIID-5499
package org.teiid.query.function;

import com.datavirtuality.dv.core.interfaces.DVCaller;
import java.util.ArrayList;
import java.util.Collection;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.TreeSet;

import java.util.concurrent.ConcurrentHashMap;
import org.teiid.api.exception.query.InvalidFunctionException;
import org.teiid.api.exception.query.QueryResolverException;
import org.teiid.core.types.DataTypeManager;
import org.teiid.core.types.Transform;
import org.teiid.metadata.AggregateAttributes;
import org.teiid.metadata.FunctionMethod;
import org.teiid.metadata.FunctionParameter;
import org.teiid.query.QueryPlugin;
import org.teiid.query.function.metadata.FunctionCategoryConstants;
import org.teiid.query.resolver.util.ResolverUtil;
import org.teiid.query.sql.symbol.AggregateSymbol;
import org.teiid.query.sql.symbol.AggregateSymbol.Type;
import org.teiid.query.sql.symbol.Constant;
import org.teiid.query.sql.symbol.Expression;
import org.teiid.query.sql.symbol.Function;
import org.teiid.translator.SourceSystemFunctions;

import static org.teiid.metadata.BaseColumn.NullType.*;


/**
 * The function library is the primary way for the system to find out what
 * functions are available, resolve function signatures, and invoke system
 * and user-defined functions.
 */
public class FunctionLibrary {

	// Special type conversion functions
	public static final String CONVERT = "convert"; //$NON-NLS-1$
	public static final String CAST = "cast"; //$NON-NLS-1$

    // Special lookup function
    public static final String LOOKUP = "lookup"; //$NON-NLS-1$

    // Special user function
    public static final String USER = "user"; //$NON-NLS-1$
    // Special environment variable lookup function
    public static final String ENV = "env"; //$NON-NLS-1$
    public static final String SESSION_ID = "session_id"; //$NON-NLS-1$
    public static final String REQUEST_ID = "request_id"; //$NON-NLS-1$

    // Special pseudo-functions only for XML queries
    public static final String CONTEXT = "context"; //$NON-NLS-1$
    public static final String ROWLIMIT = "rowlimit"; //$NON-NLS-1$
    public static final String ROWLIMITEXCEPTION = "rowlimitexception"; //$NON-NLS-1$
    
    // Misc.
    public static final String DECODESTRING = "decodestring"; //$NON-NLS-1$
    public static final String DECODEINTEGER = "decodeinteger"; //$NON-NLS-1$
    public static final String COMMAND_PAYLOAD = "commandpayload"; //$NON-NLS-1$
    
    public static final String CONCAT = "CONCAT"; //$NON-NLS-1$
    public static final String CONCAT2 = "CONCAT2"; //$NON-NLS-1$
    public static final String CONCAT_OPERATOR = "||"; //$NON-NLS-1$
    public static final String SUBSTRING = "substring"; //$NON-NLS-1$
    public static final String NVL = "NVL"; //$NON-NLS-1$
    public static final String IFNULL = "IFNULL"; //$NON-NLS-1$
    
    public static final String FROM_UNIXTIME = "from_unixtime"; //$NON-NLS-1$
    public static final String TIMESTAMPADD = "timestampadd"; //$NON-NLS-1$
    
    public static final String PARSETIME = "parsetime"; //$NON-NLS-1$
    public static final String PARSEDATE = "parsedate"; //$NON-NLS-1$
    public static final String FORMATTIME = "formattime"; //$NON-NLS-1$
    public static final String FORMATDATE = "formatdate"; //$NON-NLS-1$
    
    public static final String NULLIF = "nullif"; //$NON-NLS-1$
    public static final String COALESCE = "coalesce"; //$NON-NLS-1$

    public static final String SPACE = "space"; //$NON-NLS-1$
	// EXTENSION
	// ISSUE: DVCORE-2441
	// FIXED BY: Roman Pedchenko
	// DATE: 2014.10.20
	// ADOPTED FROM: https://issues.jboss.org/browse/TEIID-2111
	public static final String ARRAY_GET = "array_get"; //$NON-NLS-1$
	// END EXTENSION
	public static final String JSONARRAY = "jsonarray"; //$NON-NLS-1$
	// EXTENSION
	// ISSUE: DVCORE-399
	// FIXED BY: Roman Pedchenko
	// DATE: 2013.05.06
    public static final String LASTUPDATED = "lastUpdated";
	// END EXTENSION
    // EXTENSION
	// ISSUE: DVCORE-2365
	// FIXED BY: Roman Pedchenko
	// DATE: 2014.09.23
    public static final String ASLIST = "asList";
    public static final String ASARRAY = "asArray";
	// END EXTENSION
	
    // Function tree for system functions (never reloaded)
    private FunctionTree systemFunctions;

    // Function tree for user-defined functions
    private Map<String, FunctionTree> userFunctions = new ConcurrentHashMap<>();

	/**
	 * Construct the function library.  This should be called only once by the
	 * FunctionLibraryManager.
	 */
	public FunctionLibrary(FunctionTree systemFuncs, FunctionTree... userFuncs) {
        systemFunctions = systemFuncs;
		if (userFuncs != null) {
			for (FunctionTree ft : userFuncs) {
				userFunctions.put(ft.getSchemaName(), ft);
	}
		}
	}

	public FunctionTree getSystemFunctions() {
		return systemFunctions;
	}

	public Map<String, FunctionTree> getUserFunctions() {
		return userFunctions;
	}

    /**
     * Get all function categories, sorted in alphabetical order
     * @return List of function category names, sorted in alphabetical order
     */
    public List<String> getFunctionCategories() {
        // Remove category duplicates
        TreeSet<String> categories = new TreeSet<String>();
        categories.addAll( systemFunctions.getCategories() );
        if (this.userFunctions != null) {
	        for (FunctionTree tree: this.userFunctions.values()) {
	        	categories.addAll(tree.getCategories());
        }
        }

        ArrayList<String> categoryList = new ArrayList<String>(categories);
        return categoryList;
    }

    /**
     * Get all function in a category.
     * @param category Category name
     * @return List of {@link FunctionMethod}s in a category
     */
    public List<FunctionMethod> getFunctionsInCategory(String category) {
	    List<FunctionMethod> forms = new ArrayList<FunctionMethod>();
	    forms.addAll(systemFunctions.getFunctionsInCategory(category));
        if (this.userFunctions != null) {
	        for (FunctionTree tree: this.userFunctions.values()) {
		        forms.addAll(tree.getFunctionsInCategory(category));
        }
        }
        return forms;
    }

	public Collection<FunctionMethod> findSystemFunctions(String canonicalName) {
		return systemFunctions.findFunctionMethods(canonicalName);
	}

    /**
     * Find function form based on function name and # of arguments.
     * @param name Function name
     * @param numArgs Number of arguments
     * @return Corresponding form or null if not found
     */
    public boolean hasFunctionMethod(String name, int numArgs) {
	    List<FunctionMethod> methods = systemFunctions.findFunctionMethods(name, numArgs);
	    if (!methods.isEmpty()) {
		    return true;
	    }
	    if(this.userFunctions != null) {
        	for (FunctionTree tree: this.userFunctions.values()) {
		        methods = tree.findFunctionMethods(name, numArgs);
		        if (!methods.isEmpty()) {
			        return true;
        	}
        }
        }
		Boolean result = (Boolean) DVCaller.call(219, name, numArgs);
		if (result != null)
			return result;
	    return false;
    }

	public List<FunctionMethod> findFunctionMethods(String name, int numArgs) {
		List<FunctionMethod> methods = new ArrayList<>();
		List<FunctionMethod> systemMethods = systemFunctions.findFunctionMethods(name, numArgs);
		if (systemMethods != null) {
			methods.addAll(systemMethods);
		}
		if(this.userFunctions != null) {
			for (FunctionTree tree: this.userFunctions.values()) {
				List<FunctionMethod> userMethods = tree.findFunctionMethods(name, numArgs);
				if (userMethods != null) {
					methods.addAll(userMethods);
				}
			}
		}
		if (methods.isEmpty()) {
			List<FunctionMethod> schemaMethods = (List<FunctionMethod>) DVCaller.call(253, name, numArgs);
			if (schemaMethods != null) {
				methods.addAll(schemaMethods);
			}
		}
		return methods;
	}

	/**
	 * Find a function descriptor given a name and the types of the arguments.
	 * This method matches based on case-insensitive function name and
     * an exact match of the number and types of parameter arguments.
     * @param name Name of the function to resolve
     * @param types Array of classes representing the types
     * @return Descriptor if found, null if not found
	 */
	public FunctionDescriptor findFunction(String name, Class<?>[] types) {
        // First look in system functions
        FunctionDescriptor descriptor = systemFunctions.getFunction(name, types);

        // If that fails, check the user defined functions
        if(descriptor == null && this.userFunctions != null) {
        	for (FunctionTree tree: this.userFunctions.values()) {
        		descriptor = tree.getFunction(name, types);
        		if (descriptor != null) {
        			break;
        	}
        }
        }

		if (descriptor == null) {
			descriptor = (FunctionDescriptor) DVCaller.call(221, name, types);
		}

        return descriptor;
	}

	/**
	 * Get the conversions that are needed to call the named function with arguments
	 * of the given type.  In the case of an exact match, the list will contain all nulls.
	 * In other cases the list will contain one or more non-null values where the value
	 * is a conversion function that can be used to convert to the proper types for
	 * executing the function.
     * @param name Name of function
	 * @param returnType
	 * @param args 
	 * @param types Existing types passed to the function
     * @return Null if no conversion could be found, otherwise an array of conversions
     * to apply to each argument.  The list should match 1-to-1 with the parameters.
     * Parameters that do not need a conversion are null; parameters that do are
     * FunctionDescriptors.
	 * @throws InvalidFunctionException 
	 * @throws QueryResolverException 
	 */
	public FunctionDescriptor[] determineNecessaryConversions(String name, Class<?> returnType, Expression[] args, Class<?>[] types, boolean hasUnknownType) throws InvalidFunctionException {
		// Check for no args - no conversion necessary
		if(types.length == 0) {
			return null;
		}

        //First find existing functions with same name and same number of parameters
        final Collection<FunctionMethod> functions = new LinkedList<FunctionMethod>();
        functions.addAll( this.systemFunctions.findFunctionMethods(name, types.length) );
        if (this.userFunctions != null) {
	        for (FunctionTree tree: this.userFunctions.values()) {
	        	functions.addAll( tree.findFunctionMethods(name, types.length) );
			}
        }
		// udfs are only resolved for fully qualified names (eg. 'test_tables_ms.upper', but not for 'upper' without schema)
		List<FunctionMethod> udfs = name.contains(".") ? (List<FunctionMethod>) DVCaller.call(220, name, types.length) : null;
		if (udfs != null)
			functions.addAll(udfs);
        
        //Score each match, reject any where types can not be converted implicitly       
        //Score of current method (lower score means better match with less converts
        //Current best score (lower score is best.  Higher score results in more implicit conversions
        int bestScore = Integer.MAX_VALUE;
        boolean ambiguous = false;
        FunctionMethod result = null;
                
        for (FunctionMethod nextMethod : functions) {
            int currentScore = 0; 
            final List<FunctionParameter> methodTypes = nextMethod.getInputParameters();
            //Holder for current signature with converts where required
            
            //Iterate over the parameters adding conversions where required or failing when
            //no implicit conversion is possible
            int i = 0;
            for(; i < types.length; i++) {
// EXTENSION
// ISSUE: DVCORE-2441
// FIXED BY: Roman Pedchenko
// DATE: 2014.10.20
// ADOPTED FROM: https://issues.jboss.org/browse/TEIID-2111
//             	//treat all varags as the same type
// END EXTENSION
                final String tmpTypeName = methodTypes.get(Math.min(i, methodTypes.size() - 1)).getType();
                Class<?> targetType = DataTypeManager.getDataTypeClass(tmpTypeName);

                Class<?> sourceType = types[i];
                if (sourceType == null) {
                    currentScore++;
                    continue;
                }
                // EXTENSION
                // ISSUE: DVCORE-2441
                // FIXED BY: Roman Pedchenko
                // DATE: 2014.10.20
                // ADOPTED FROM: https://issues.jboss.org/browse/TEIID-2111
                if (sourceType.isArray()) {
                    if (isVarArgArrayParam(nextMethod, types, i, targetType)) {
                		//vararg array parameter
                		continue;
                	}
                    //treat the array as object type until proper type handling is added
                	sourceType = DataTypeManager.DefaultDataClasses.OBJECT;
                }
                // END EXTENSION
				try {
					Transform t = getConvertFunctionDescriptor(sourceType, targetType);
					if (t != null) {
		                if (t.isExplicit()) {
		                	if (!(args[i] instanceof Constant) || ResolverUtil.convertConstant(DataTypeManager.getDataTypeName(sourceType), tmpTypeName, (Constant)args[i]) == null) {
		                		break;
            }
		                	currentScore++;
		                } else {
		                	currentScore++;
		                }
					}
				} catch (InvalidFunctionException e) {
					break;
				}
            }
            
            //If the method is valid match and it is the current best score, capture those values as current best match
            if (i != types.length || currentScore > bestScore) {
                continue;
            }
            
            if (hasUnknownType) {
            	if (returnType != null) {
            		try {
						Transform t = getConvertFunctionDescriptor(DataTypeManager.getDataTypeClass(nextMethod.getReturnParameter().getType()), returnType);
						if (t != null) {
							if (t.isExplicit()) {
								//there still may be a common type, but use any other valid conversion over this one
								currentScore += types.length + 1;
							} else {
								currentScore++;
							}
						}
					} catch (InvalidFunctionException e) {
						//there still may be a common type, but use any other valid conversion over this one
						currentScore += (types.length * types.length);
					}
            	}
                ambiguous = currentScore == bestScore;
            }
            
            if (currentScore < bestScore) {

                if (currentScore == 0) {
                    //this must be an exact match
                    return null;
                }    
                
                bestScore = currentScore;
                result = nextMethod;
            }            
        }
        
        if (ambiguous || result == null) {
             throw new InvalidFunctionException(QueryPlugin.Event.TEIID30418);
        }
        
		return getConverts(result, types);
	}
	
	private FunctionDescriptor[] getConverts(FunctionMethod method, Class<?>[] types) {
        final List<FunctionParameter> methodTypes = method.getInputParameters();
        FunctionDescriptor[] result = new FunctionDescriptor[types.length];
        for(int i = 0; i < types.length; i++) {
        	//treat all varags as the same type
            final String tmpTypeName = methodTypes.get(Math.min(i, methodTypes.size() - 1)).getType();
            Class<?> targetType = DataTypeManager.getDataTypeClass(tmpTypeName);

            Class<?> sourceType = types[i];
            if (sourceType == null) {
                result[i] = findTypedConversionFunction(DataTypeManager.DefaultDataClasses.NULL, targetType);
            } else if (sourceType != targetType){
            	// EXTENSION
            	// ISSUE: DVCORE-2441
            	// FIXED BY: Roman Pedchenko
            	// DATE: 2014.10.20
            	// ADOPTED FROM: https://issues.jboss.org/browse/TEIID-2111
            	if (isVarArgArrayParam(method, types, i, targetType)) {
            		//vararg array parameter
            		continue;
            	}
            	// END EXTENSION
            	result[i] = findTypedConversionFunction(sourceType, targetType);
        }
        }
        return result;
	}
	// EXTENSION
	// ISSUE: DVCORE-2441
	// FIXED BY: Roman Pedchenko
	// DATE: 2014.10.20
	// ADOPTED FROM: https://issues.jboss.org/browse/TEIID-2111

	public boolean isVarArgArrayParam(FunctionMethod method, Class<?>[] types,
                                      int i, Class<?> targetType) {
	// EXTENSION
	// ISSUE: DVCORE-2441
	// FIXED BY: Roman Pedchenko
	// DATE: 2014.10.23
	// ADOPTED FROM: https://issues.jboss.org/browse/TEIID-247, 2166, 2151, 2234, 2581, 2571, 2369, 1386, 2226, 2233, 2224, 2225, 2257
	// 	return i == types.length - 1 && method.isVarArgs() && i == method.getInputParameterCount() - 1 
	// 			&& types[i].getComponentType() == targetType;
	// }
		return i == types.length - 1 && method.isVarArgs() && i == method.getInputParameterCount() - 1 
				&& types[i].isArray() && targetType.isAssignableFrom(types[i].getComponentType());
	}
	// END EXTENSION
	// END EXTENSION
	
	private Transform getConvertFunctionDescriptor(Class<?> sourceType, Class<?> targetType) throws InvalidFunctionException {
        //If exact match no conversion necessary
        if(sourceType.equals(targetType)) {
            return null;
	}
        Transform result = DataTypeManager.getTransform(sourceType, targetType);
        //Else see if an implicit conversion is possible.
        if(result == null){
             throw new InvalidFunctionException(QueryPlugin.Event.TEIID30419);
        }
        return result;
	}

    /**
     * Find conversion function and set return type to proper type.   
     * @param sourceType The source type class
     * @param targetType The target type class
     * @return A CONVERT function descriptor or null if not possible
     */
    // EXTENSION
    // ISSUE: DVCORE-2441
    // FIXED BY: Roman Pedchenko
    // DATE: 2014.10.20
    // ADOPTED FROM: https://issues.jboss.org/browse/TEIID-2111
    // public FunctionDescriptor findTypedConversionFunction(Class<?> sourceType, Class<?> targetType) {
    public FunctionDescriptor findTypedConversionFunction(Class<?> sourceType, Class<?> targetType) {
    	//TODO: should array to string be prohibited?    	
    // END EXTENSION
        FunctionDescriptor fd = findFunction(CONVERT, new Class[] {sourceType, DataTypeManager.DefaultDataClasses.STRING});
        if (fd != null) {
            return copyFunctionChangeReturnType(fd, targetType);
        }
        return null;
    }

	/**
	 * Return a copy of the given FunctionDescriptor with the sepcified return type.
	 * @param fd FunctionDescriptor to be copied.
	 * @param returnType The return type to apply to the copied FunctionDescriptor.
	 * @return The copy of FunctionDescriptor.
	 */
    public FunctionDescriptor copyFunctionChangeReturnType(FunctionDescriptor fd, Class<?> returnType) {
        if(fd != null) {
        	FunctionDescriptor fdImpl = fd;
            // EXTENSION
            // ISSUE: DVCORE-2441
            // FIXED BY: Roman Pedchenko
            // DATE: 2014.10.20
            // ADOPTED FROM: https://issues.jboss.org/browse/TEIID-2111
            // FunctionDescriptor copy = (FunctionDescriptor)fdImpl.clone();
            FunctionDescriptor copy = fdImpl.clone();
            // END EXTENSION
            copy.setReturnType(returnType);
            return copy;
        }
        return fd;
    }
    
    public static boolean isConvert(Function function) {
        Expression[] args = function.getArgs();
        String funcName = function.getName();
        
        return args.length == 2 && (funcName.equalsIgnoreCase(FunctionLibrary.CONVERT) || funcName.equalsIgnoreCase(FunctionLibrary.CAST));
    }

	/**
	 * Return a list of the most general forms of built-in aggregate functions.
	 * <br/>count(*) - is not included
	 * <br/>textagg - is not included due to its non standard syntax
	 *
	 * @param includeAnalytic - true to include analytic functions that must be windowed
	 * @return
	 */
	public List<FunctionMethod> getBuiltInAggregateFunctions(boolean includeAnalytic) {
		List<FunctionMethod> result = builtInAggregateFunctions(includeAnalytic);
		if (this.systemFunctions != null) {
			FunctionDescriptor stExtent = this.systemFunctions.getFunction(SourceSystemFunctions.ST_EXTENT, new Class[] {DataTypeManager.DefaultDataClasses.GEOMETRY});
			result.add(stExtent.getMethod());
		}
		return result;
	}

	public FunctionTree getUserFunctionTree(String schema) {
		return userFunctions.get(schema);
	}

	public void addUserFunctionTree(FunctionTree functionTree) {
		userFunctions.put(functionTree.getSchemaName(), functionTree);
	}
	
	public FunctionTree getExtendedSystemFunctions() {
		List<FunctionMethod> extendedSystemFunctions = additionalSystemFunctions();
		extendedSystemFunctions.addAll(systemFunctions.getFunctionsByUuid().values()); // add regular system functions
		return new FunctionTree("extended-system-functions", new UDFSource(extendedSystemFunctions), false);
	}

	public static List<FunctionMethod> builtInAggregateFunctions(boolean includeAnalytic) {
		ArrayList<FunctionMethod> result = new ArrayList<FunctionMethod>();
		for (Type type : AggregateSymbol.Type.values()) {
			AggregateAttributes aa = new AggregateAttributes();
			String returnType = null;
			String[] argTypes = null;
			String description = null;
			aa.setAllowsDistinct(true);
			switch (type) {
				case TEXTAGG:
				case USER_DEFINED:
					continue;
				case DENSE_RANK:
					description = "Assigns a number to each unique ordering value within each partition starting at 1, such that the next rank is sequential";
				case RANK:
					description = description != null ? description : "Assigns a number to each unique ordering value within each partition starting at 1, such that the next rank is equal to the count of prior rows";
				case ROW_NUMBER:
					if (!includeAnalytic) {
						continue;
					}
					description = description != null ? description : "Functionally same as COUNT(*) with the same window specification. Assigns a number to each row in a partition starting at 1";
					aa.setAllowsDistinct(false);
					aa.setAnalytic(true);
					returnType = DataTypeManager.DefaultDataTypes.INTEGER;
					argTypes = new String[] {};
					break;
				case ANY:
					description = "Returns TRUE if any value in the group is TRUE (excluding null)";
				case SOME:
					description = description != null ? description : "Returns TRUE if any value in the group is TRUE (excluding null)";
				case EVERY:
					description = description != null ? description : "Returns TRUE if every value in the group is TRUE (excluding null)";
					returnType = DataTypeManager.DefaultDataTypes.BOOLEAN;
					argTypes = new String[] {DataTypeManager.DefaultDataTypes.BOOLEAN};
					break;
				case COUNT:
					description = "Counts number of values (excluding nulls) in a group";
					returnType = DataTypeManager.DefaultDataTypes.LONG;
					argTypes = new String[] {DataTypeManager.DefaultDataTypes.OBJECT};
					break;
				case MAX:
					description = "Maximum value in a group (excluding null)";
				case MIN:
					description = description != null ? description : "Minimum value in a group (excluding null)";
				case AVG:
					description = description != null ? description : "Average of values (excluding nulls) in a group";
				case SUM:
					description = description != null ? description : "Sum of values (excluding nulls) in a group";
					returnType = DataTypeManager.DefaultDataTypes.OBJECT;
					argTypes = new String[] {DataTypeManager.DefaultDataTypes.OBJECT};
					break;
				case STDDEV_POP:
					description = "Standard deviation (excluding null) logically equals SQRT(VAR_POP(x))";
				case STDDEV_SAMP:
					description = description != null ? description : "Sample standard deviation (excluding null) logically equals SQRT(VAR_SAMP(x))";
				case VAR_POP:
					description = description != null ? description : "Biased variance (excluding null) logically equals (sum(x^2) - sum(x)^2/count(x))/count(x); returns a double; null if count = 0";
				case VAR_SAMP:
					description = description != null ? description : "Sample variance (excluding null) logically equals (sum(x^2) - sum(x)^2/count(x))/(count(x) - 1); returns a double; null if count < 2";
					returnType = DataTypeManager.DefaultDataTypes.DOUBLE;
					argTypes = new String[] {DataTypeManager.DefaultDataTypes.DOUBLE};
					break;
				case STRING_AGG:
					description = "Creates a lob result from the concatenation of param1 using the delimiter param2";
					returnType = DataTypeManager.DefaultDataTypes.OBJECT;
					argTypes = new String[] {DataTypeManager.DefaultDataTypes.OBJECT};
					aa.setAllowsOrderBy(true);
					break;
				case ARRAY_AGG:
					description = "Creates an array with a base type matching the given expression";
					returnType = DataTypeManager.DefaultDataTypes.OBJECT;
					argTypes = new String[] {DataTypeManager.getDataTypeName(DataTypeManager.getArrayType(DataTypeManager.DefaultDataClasses.OBJECT))};
					aa.setAllowsOrderBy(true);
					aa.setAllowsDistinct(false);
					break;
				case JSONARRAY_AGG:
					description = "Creates a JSON array result as a Clob including null value";
					returnType = DataTypeManager.DefaultDataTypes.JSON;
					argTypes = new String[] {DataTypeManager.DefaultDataTypes.OBJECT};
					aa.setAllowsOrderBy(true);
					aa.setAllowsDistinct(false);
					break;
				case XMLAGG:
					description = "XML concatenation of all XML expressions in a group (excluding null)";
					returnType = DataTypeManager.DefaultDataTypes.XML;
					argTypes = new String[] {DataTypeManager.DefaultDataTypes.XML};
					aa.setAllowsOrderBy(true);
					aa.setAllowsDistinct(false);
					break;
				case FIRST_VALUE:
					description = "Returns param1 evaluated at the row that is the first row of the window frame";
				case LAST_VALUE:
					description = description != null ? description : "Returns param2 evaluated at the row that is the last row of the window frame";
					returnType = DataTypeManager.DefaultDataTypes.OBJECT;
					argTypes = new String[] {DataTypeManager.DefaultDataTypes.OBJECT};
					break;
				case LEAD:
					description = "Returns param1 evaluated at the row that is param2 (offset) rows before the current row within the partition; if there is no such row, instead returns param3 (default) (which must be of the same type as param1). Both param2 and param3 are evaluated for the current row. If omitted, param2 defaults to 1 and param3 to null";
				case LAG:
					description = description != null ? description : "Returns param1 evaluated at the row that is param2 (offset) rows after the current row within the partition; if there is no such row, instead returns param3 (default) (which must be of the same type as param1). Both param2 and param3 are evaluated for the current row. If omitted, offset param2 defaults to 1 and param3 to null";
					returnType = DataTypeManager.DefaultDataTypes.OBJECT;
					argTypes = new String[] {DataTypeManager.DefaultDataTypes.OBJECT, DataTypeManager.DefaultDataTypes.INTEGER, DataTypeManager.DefaultDataTypes.OBJECT};
					break;
			}
			FunctionMethod fm = FunctionMethod.createFunctionMethod(type.name(), description, FunctionCategoryConstants.AGGREGATE, returnType, argTypes);
			fm.setAggregateAttributes(aa);
			result.add(fm);
		}
		return result;
	}

	public static List<FunctionMethod> additionalSystemFunctions() {
		// add all aggregate and window functions
		List<FunctionMethod> additionalSystemFunctions = builtInAggregateFunctions(true);
		additionalSystemFunctions.forEach(f -> f.setNameInSource(null));
		additionalSystemFunctions.add(new FunctionMethod("COUNT", "Counts the number of values (including nulls and duplicates) in a group.", FunctionCategoryConstants.AGGREGATE, new FunctionParameter[] {
				new FunctionParameter("*", "*", "", No_Nulls)},
				new FunctionParameter("result", "long", "The number of values (including nulls and duplicates) in a group.", Nullable)));
		additionalSystemFunctions.add(new FunctionMethod("ALL", "Returns TRUE if every value in the group is TRUE (excluding null)", FunctionCategoryConstants.AGGREGATE, new FunctionParameter[] {
				new FunctionParameter("param1", "boolean", null, Unknown)},
				new FunctionParameter("result", "boolean", null, Unknown)));
		additionalSystemFunctions.add(new FunctionMethod("TEXTAGG", "CSV text aggregation of all expressions in each row of a group.", FunctionCategoryConstants.AGGREGATE, new FunctionParameter[] {
				new FunctionParameter("expression", "clob", "FOR (expression [as name], ...)", No_Nulls),
				new FunctionParameter("DELIMITER", "string", "Sets the field delimiter character to use. Default: ,", Nullable),
				new FunctionParameter("QUOTE", "string", "Sets the quote (or qualifier) character used to wrap field values. Default: \". May be set to nothing by specifying QUOTE ''", Nullable),
				new FunctionParameter("HEADER", "string", "If specified, the result contains the header row as the first line - the header line will be present even if there are no rows in a group.", Nullable),
				new FunctionParameter("ENCODING", "string", "[ENCODING id]", Nullable)},
				new FunctionParameter("result", "string", "CSV text aggregation", Nullable)));
		// JSON functions
		additionalSystemFunctions.add(new FunctionMethod("JSONOBJECT", "This function returns a JSON object constructed from given values.", FunctionCategoryConstants.JSON, new FunctionParameter[] {
				new FunctionParameter("value", "object", "Any vararg list of objects, each convertible to a JSON value with optional name, given in format: value [as name] ...", Nullable)},
				new FunctionParameter("result", "clob", "A clob marked as being valid JSON constructed from given values.", Nullable)));
		// XML functions
		additionalSystemFunctions.add(new FunctionMethod("XMLELEMENT", "This function returns an XML element with the given name and content.", FunctionCategoryConstants.XML, new FunctionParameter[] {
				new FunctionParameter("name", "string", "Name", No_Nulls),
				new FunctionParameter("NSP", "object", "XMLNAMESPACES ((uri AS prefix | DEFAULT uri | NO DEFAULT))+", Nullable),
				new FunctionParameter("ATTR", "object", "XMLATTRIBUTES (exp [AS name] [, exp [AS name]]*)", Nullable),
				new FunctionParameter("content", "object", "Content", Nullable)},
				new FunctionParameter("result", "xml", "An XML element with the given name and content", Nullable)));
		additionalSystemFunctions.add(new FunctionMethod("XMLFOREST", "This function returns a concatenation of XML elements for each content item.", FunctionCategoryConstants.XML, new FunctionParameter[] {
				new FunctionParameter("NSP", "object", "XMLNAMESPACES ((uri AS prefix | DEFAULT uri | NO DEFAULT))+", Nullable),
				new FunctionParameter("content", "object", "Content", Nullable)},
				new FunctionParameter("result", "xml", "Concatenation of XML elements for each content item", Nullable)));
		additionalSystemFunctions.add(new FunctionMethod("XMLPARSE", "This function returns an XML type representation of the string value expression.", FunctionCategoryConstants.XML, new FunctionParameter[] {
				new FunctionParameter("expr", "object", "Expr in {string, clob, blob} preceded with (DOCUMENT|CONTENT)", No_Nulls),
				new FunctionParameter("WELLFORMED", "object", "If specified, validation is skipped", Nullable)},
				new FunctionParameter("result", "xml", "XML type representation of the string value expression", Nullable)));
		additionalSystemFunctions.add(new FunctionMethod("XMLQUERY", "This function is part of the SQL/XML 2006 specification and returns the XML result from evaluating the given XQuery.", FunctionCategoryConstants.XML, new FunctionParameter[] {
				new FunctionParameter("NSP", "object", "XMLNAMESPACES ((uri AS prefix | DEFAULT uri | NO DEFAULT))+", Nullable),
				new FunctionParameter("xquery", "string", "XQuery string", No_Nulls),
				new FunctionParameter("PASSING", "object", "Used to provide a context item which does not have a name and named global variable values: PASSING exp [AS name] [, exp [AS name]]*", Nullable),
				new FunctionParameter("ON EMPTY", "object", "Used to specify the result when the evaluated sequence is empty: (NULL|EMPTY) ON EMPTY", Nullable),},
				new FunctionParameter("result", "xml", "XML result from evaluating the given XQuery", Nullable)));
		additionalSystemFunctions.add(new FunctionMethod("XMLSERIALIZE", "This function returns a character-type representation of the XML expression.", FunctionCategoryConstants.XML, new FunctionParameter[] {
				new FunctionParameter("xml", "xml", "XML document or content optionally preceded with (DOCUMENT|CONTENT)", No_Nulls),
				new FunctionParameter("datatype", "object", "Character type (string, varchar, clob) datatype used for return value", Nullable)},
				new FunctionParameter("result", "string", "Character-type representation of the XML expression", Nullable)));
		additionalSystemFunctions.add(new FunctionMethod("XMLTABLE", "Uses XQuery to produce tabular output.", FunctionCategoryConstants.XML, new FunctionParameter[] {
				new FunctionParameter("name", "string", "Name", No_Nulls),
				new FunctionParameter("NSP", "object", "XMLNAMESPACES ((uri AS prefix | DEFAULT uri | NO DEFAULT))+", Nullable),
				new FunctionParameter("xquery", "string", "XQuery string", No_Nulls),
				new FunctionParameter("PASSING", "object", "used to provide a context item which does not have a name and named global variable values: PASSING exp [AS name] [, exp [AS name]]*", Nullable),
				new FunctionParameter("NSP", "object", "XMLNAMESPACES ((uri AS prefix | DEFAULT uri | NO DEFAULT))+", Nullable)},
				new FunctionParameter("result", "table", "Result in tabular form", Nullable)));
		// add table functions
		additionalSystemFunctions.add(new FunctionMethod("TABLE", "Applies tabular output to the given subquery.", FunctionCategoryConstants.SYSTEM, new FunctionParameter[] {
				new FunctionParameter("subquery", "string", "Subquery", No_Nulls)},
				new FunctionParameter("result", "table", "Result in tabular form", Nullable)));
		additionalSystemFunctions.add(new FunctionMethod("LATERAL", "Applies tabular output to the given subquery (ANSI standard keyword).", FunctionCategoryConstants.SYSTEM, new FunctionParameter[] {
				new FunctionParameter("subquery", "string", "Subquery", No_Nulls)},
				new FunctionParameter("result", "table", "Result in tabular form", Nullable)));
		additionalSystemFunctions.add(new FunctionMethod("ARRAYTABLE", "Processes an array input to produce tabular output.", FunctionCategoryConstants.SYSTEM, new FunctionParameter[] {
				new FunctionParameter("expression", "object[]", "The array to process", No_Nulls),
				new FunctionParameter("COLUMNS", "object", "Columns to project in format: COLUMN := name datatype", Nullable)},
				new FunctionParameter("result", "table", "Result in tabular form", Nullable)));
		additionalSystemFunctions.add(new FunctionMethod("JSONTABLE", "Uses JsonPath to produce tabular output.", FunctionCategoryConstants.SYSTEM, new FunctionParameter[] {
				new FunctionParameter("value", "clob", "A clob containing a valid JSON document.", No_Nulls),
				new FunctionParameter("path", "string", "A valid JsonPath", No_Nulls),
				new FunctionParameter("nullLeafOnMissing", "boolean", "If FALSE (default), a path that evaluates to a leaf that is missing will throw an exception. If TRUE, a null value will be returned", Nullable),
				new FunctionParameter("COLUMNS", "object", "Columns to project in format: COLUMN := name datatype", Nullable)},
				new FunctionParameter("result", "table", "Result in tabular form", Nullable)));
		additionalSystemFunctions.add(new FunctionMethod("OBJECTTABLE", "Processes an object input to produce tabular output.", FunctionCategoryConstants.SYSTEM, new FunctionParameter[] {
				new FunctionParameter("lang", "string", "Case-sensitive language name of the scripts to be processed.", Nullable),
				new FunctionParameter("name", "string", "Identifier that will bind the val expression value into the script context", Nullable),
				new FunctionParameter("rowScript", "string", "The script to create the row values", No_Nulls),
				new FunctionParameter("rowScript", "string", "The script to create the row values", No_Nulls),
				new FunctionParameter("colName/colType", "string", "ID/data type of the column, which can optionally be defaulted with the DEFAULT clause expression defaultExpr", No_Nulls),
				new FunctionParameter("colScript", "string", "The script that evaluates to the column value", No_Nulls)},
				new FunctionParameter("result", "table", "Result in tabular form", Nullable)));
		additionalSystemFunctions.add(new FunctionMethod("TEXTTABLE", "Processes character input to produce tabular output.", FunctionCategoryConstants.SYSTEM, new FunctionParameter[] {
				new FunctionParameter("expression", "clob", "Text content to process, should be convertible to CLOB", No_Nulls),
				new FunctionParameter("SELECTOR", "string", "Specifies that delimited lines should only match if the line begins with the selector string followed by a delimiter.", Nullable),
				new FunctionParameter("COLUMNS", "object", "Columns to project in format: COLUMN := name datatype", Nullable),
				new FunctionParameter("NO ROW DELIMITER", "object", "Indicates that fixed parsing should not assume the presence of newline row delimiters", Nullable),
				new FunctionParameter("ROW DELIMITER", "string", "Sets the row delimiter/new line to an alternate character.", Nullable),
				new FunctionParameter("DELIMITER", "string", "Sets the field delimiter character to use. Default: ,", Nullable),
				new FunctionParameter("QUOTE", "string", "Sets the quote (or qualifier) character used to wrap field values. Default: \". May be set to nothing by specifying QUOTE ''", Nullable),
				new FunctionParameter("ESCAPE", "string", "Sets the escape character to use if no quoting character is in use. This is used in situations where the delimiter or newline characters are escaped with a preceding character, e.g. \\", Nullable),
				new FunctionParameter("HEADER", "string", "Specifies the text line number (counting every new line) on which the column names occur. All lines before the header will be skipped. If HEADER is specified, the header line will be used to determine the TEXTTABLE column position by case-insensitive name matching. This is especially useful when only a subset of the columns is needed. If the HEADER value is not specified, it defaults to 1. If HEADER is not specified, then columns are expected to match positionally with the text contents. If the HEADER option for a column is specified, then that will be used as the expected header name.", Nullable),
				new FunctionParameter("SKIP", "long", "Specifies the number of text lines (counting every new line) to skip before parsing the contents.", Nullable),
				new FunctionParameter("A FOR ORDINALITY", "integer", "This  column is typed as an integer and will return the 1-based item number as its value", Nullable),
				new FunctionParameter("WIDTH", "integer", "Indicates the fixed-width length of a column in characters, not bytes. With the default ROW DELIMITER, a CR NL sequence counts as a single character", Nullable),
				new FunctionParameter("TRIM", "object", "When specified on the TEXTTABLE, will affect all column and header values. When specified on a column, the fixed or unqualified text value not be trimmed of a leading and trailing whitespace", Nullable),
				new FunctionParameter("SKIPERRORS[maxerrors]", "object", "", Nullable),
				new FunctionParameter("ERRORLOG", "string", "Failed lines are written into the given table name", Nullable)},
				new FunctionParameter("result", "table", "Result in tabular form", Nullable)));
		return additionalSystemFunctions;
	}
}
