Views.js

//  *** DEBUG START ***
//  Remove comments for testing in NODE
export { DERIVEDTABLE, VirtualFields, VirtualField, SelectTables, TableFields, TableField, CalculatedField, SqlServerFunctions, DerivedTable };
import { Table } from './Table.js';
import { Sql, BindData, TableExtract } from './Sql.js';
import { SqlParse } from './SimpleParser.js';
import { JoinTables } from './JoinTables.js';
//  *** DEBUG END ***/
const DERIVEDTABLE = "::DERIVEDTABLE::";
/** 
 * @classdesc 
 * Perform SQL SELECT operations to retrieve requested data. 
 */
class SelectTables {
    /**
     * @param {Object} ast - Abstract Syntax Tree
     * @param {Map<String,Table>} tableInfo - Map of table info.
     * @param {BindData} bindVariables - List of bind data.
     */
    constructor(ast, tableInfo, bindVariables) {
        /** @property {String} - primary table name. */
        this.primaryTable = ast.FROM.table;
        /** @property {Object} - AST of SELECT fields */
        this.astFields = ast.SELECT;
        /** @property {Map<String,Table>} tableInfo - Map of table info. */
        this.tableInfo = tableInfo;
        /** @property {BindData} - Bind variable data. */
        this.bindVariables = bindVariables;
        /** @property {TableFields} */
        this.tableFields = new TableFields();
        /** @property {Table} - Primary table info. */
        this.primaryTableInfo = tableInfo.get(this.primaryTable.toUpperCase());
        /** @property {JoinTables} - Join table object. */
        this.dataJoin = new JoinTables()
            .setTableFields(this.tableFields)
            .setTableInfo(this.tableInfo)
            .setBindVariables(bindVariables)
            .setPrimaryTableInfo(this.primaryTableInfo);
        if (!tableInfo.has(this.primaryTable.toUpperCase()))
            throw new Error(`Invalid table name: ${this.primaryTable}`);
        //  Keep a list of all possible fields from all tables.
        this.tableFields.loadVirtualFields(this.primaryTable, tableInfo);
    }
    /**
     * Update internal FIELDS list to indicate those fields that are in the SELECT fields - that will be returned in data.
     * @param {Object} ast
     * @returns {void} 
     */
    updateSelectedFields(ast) {
        let astFields = ast.SELECT;
        const tableInfo = !this.dataJoin.isDerivedTable() ? this.primaryTableInfo : this.dataJoin.derivedTable.tableInfo;
        //  Expand any 'SELECT *' fields and add the actual field names into 'astFields'.
        astFields = VirtualFields.expandWildcardFields(tableInfo, astFields);
        //  Define the data source of each field in SELECT field list.
        this.tableFields.updateSelectFieldList(astFields, 0, false);
        //  These are fields REFERENCED, but not actually in the SELECT FIELDS.
        //  So columns referenced by GROUP BY, ORDER BY and not in SELECT.
        //  These temp columns need to be removed after processing.
        if (typeof ast["GROUP BY"] !== 'undefined') {
            this.tableFields.updateSelectFieldList(ast["GROUP BY"], this.tableFields.getNextSelectColumnNumber(), true);
        }
        if (typeof ast["ORDER BY"] !== 'undefined') {
            this.tableFields.updateSelectFieldList(ast["ORDER BY"], this.tableFields.getNextSelectColumnNumber(), true);
        }
    }
    /**
     * Process any JOIN condition.
     * @param {Object} ast - Abstract Syntax Tree
     * @returns {void}
     */
    join(ast) {
        if (typeof ast.JOIN !== 'undefined')
            this.dataJoin.load(ast);
    }
    /**
     * Retrieve filtered record ID's.
     * @param {Object} ast - Abstract Syntax Tree
     * @returns {Number[]} - Records ID's that match WHERE condition.
     */
    whereCondition(ast) {
        let sqlData = [];
        let conditions = {};
        if (typeof ast.WHERE !== 'undefined') {
            conditions = ast.WHERE;
        }
        else if (typeof ast["GROUP BY"] === 'undefined' && typeof ast.HAVING !== 'undefined') {
            //  This will work in mySql as long as select field is in having clause.
            conditions = ast.HAVING;
        }
        else {
            //  Entire table is selected.  
            conditions = { operator: "=", left: "\"A\"", right: "\"A\"" };
        }
        if (typeof conditions.logic === 'undefined')
            sqlData = this.resolveCondition("OR", [conditions]);
        else
            sqlData = this.resolveCondition(conditions.logic, conditions.terms);
        return sqlData;
    }
    /**
    * Recursively resolve WHERE condition and then apply AND/OR logic to results.
    * @param {String} logic - logic condition (AND/OR) between terms
    * @param {Object} terms - terms of WHERE condition (value compared to value)
    * @returns {Number[]} - record ID's 
    */
    resolveCondition(logic, terms) {
        const recordIDs = [];
        for (const cond of terms) {
            if (typeof cond.logic === 'undefined') {
                recordIDs.push(this.getRecordIDs(cond));
            }
            else {
                recordIDs.push(this.resolveCondition(cond.logic, cond.terms));
            }
        }
        let result = [];
        if (logic === "AND") {
            result = recordIDs.reduce((a, b) => a.filter(c => b.includes(c)));
        }
        if (logic === "OR") {
            //  OR Logic
            let tempArr = [];
            for (const arr of recordIDs) {
                tempArr = tempArr.concat(arr);
            }
            result = Array.from(new Set(tempArr));
        }
        return result;
    }
    /**
    * Find record ID's where condition is TRUE.
    * @param {Object} condition - WHERE test condition
    * @returns {Number[]} - record ID's which are true.
    */
    getRecordIDs(condition) {
        /** @type {Number[]} */
        const recordIDs = [];
        const leftFieldConditions = this.resolveFieldCondition(condition.left);
        const rightFieldConditions = this.resolveFieldCondition(condition.right);
        const conditionFunction = FieldComparisons.getComparisonFunction(condition.operator);
        /** @type {Table} */
        this.masterTable = this.dataJoin.isDerivedTable() ? this.dataJoin.getJoinedTableInfo() : this.primaryTableInfo;
        const calcSqlField = new CalculatedField(this.masterTable, this.primaryTableInfo, this.tableFields);
        for (let masterRecordID = 1; masterRecordID < this.masterTable.tableData.length; masterRecordID++) {
            let leftValue = SelectTables.getConditionValue(leftFieldConditions, calcSqlField, masterRecordID);
            let rightValue = SelectTables.getConditionValue(rightFieldConditions, calcSqlField, masterRecordID);
            if (leftValue instanceof Date || rightValue instanceof Date) {
                leftValue = SelectTables.dateToMs(leftValue);
                rightValue = SelectTables.dateToMs(rightValue);
            }
            if (conditionFunction(leftValue, rightValue))
                recordIDs.push(masterRecordID);
        }
        return recordIDs;
    }
    /**
     * Evaulate value on left/right side of condition
     * @param {ResolvedFieldCondition} fieldConditions - the value to be found will come from:
     * * constant data
     * * field data
     * * calculated field
     * * sub-query 
     * @param {CalculatedField} calcSqlField - data to resolve the calculated field.
     * @param {Number} masterRecordID - current record in table to grab field data from
     * @returns {any} - resolve value.
     */
    static getConditionValue(fieldConditions, calcSqlField, masterRecordID) {
        let fieldValue = fieldConditions.constantData;
        if (fieldConditions.columnNumber >= 0) {
            fieldValue = fieldConditions.fieldConditionTableInfo.tableData[masterRecordID][fieldConditions.columnNumber];
        }
        else if (fieldConditions.calculatedField !== "") {
            if (fieldConditions.calculatedField.toUpperCase() === "NULL") {
                fieldValue = "NULL";
            }
            else {
                fieldValue = calcSqlField.evaluateCalculatedField(fieldConditions.calculatedField, masterRecordID);
            }
        }
        else if (fieldConditions.subQuery !== null) {
            const arrayResult = fieldConditions.subQuery.select(masterRecordID, calcSqlField);
            if (typeof arrayResult !== 'undefined' && arrayResult !== null && arrayResult.length > 0)
                fieldValue = arrayResult[0][0];
        }
        return fieldValue;
    }
    /**
     * Retrieve the data for the record ID's specified for ALL SELECT fields.
     * @param {Number[]} recordIDs - record ID's which are SELECTed.
     * @returns {any[][]} - double array of select data.  No column title is included here.
     */
    getViewData(recordIDs) {
        const virtualData = [];
        const calcSqlField = new CalculatedField(this.masterTable, this.primaryTableInfo, this.tableFields);
        const subQuery = new CorrelatedSubQuery(this.tableInfo, this.tableFields, this.bindVariables);
        for (const masterRecordID of recordIDs) {
            const newRow = [];
            for (const field of this.tableFields.getSelectFields()) {
                if (field.tableInfo !== null)
                    newRow.push(field.getData(masterRecordID));
                else if (field.subQueryAst !== null) {
                    const result = subQuery.select(masterRecordID, calcSqlField, field.subQueryAst);
                    newRow.push(result[0][0]);
                }
                else if (field.calculatedFormula !== "") {
                    const result = calcSqlField.evaluateCalculatedField(field.calculatedFormula, masterRecordID);
                    newRow.push(result);
                }
            }
            virtualData.push(newRow);
        }
        return virtualData;
    }
    /**
     * Returns the entire string in UPPER CASE - except for anything between quotes.
     * @param {String} srcString - source string to convert.
     * @returns {String} - converted string.
     */
    static toUpperCaseExceptQuoted(srcString) {
        let finalString = "";
        let inQuotes = "";
        for (let i = 0; i < srcString.length; i++) {
            let ch = srcString.charAt(i);
            if (inQuotes === "") {
                if (ch === '"' || ch === "'")
                    inQuotes = ch;
                ch = ch.toUpperCase();
            }
            else if (ch === inQuotes) {
                inQuotes = "";
            }
            finalString += ch;
        }
        return finalString;
    }
    /**
     * Parse input string for 'func' and then parse if found.
     * @param {String} functionString - Select field which may contain a function.
     * @param {String} func - Function name to parse for.
     * @returns {String[]} - Parsed function string.
     *   * null if function not found, 
     *   * string array[0] - original string, e.g. **sum(quantity)**
     *   * string array[1] - function parameter, e.g. **quantity**
     */
    static parseForFunctions(functionString, func) {
        const args = [];
        const expMatch = "%1\\s*\\(";
        const matchStr = new RegExp(expMatch.replace("%1", func));
        const startMatchPos = functionString.search(matchStr);
        if (startMatchPos !== -1) {
            const searchStr = functionString.substring(startMatchPos);
            let i = searchStr.indexOf("(");
            const startLeft = i;
            let leftBracket = 1;
            for (i = i + 1; i < searchStr.length; i++) {
                const ch = searchStr.charAt(i);
                if (ch === "(") leftBracket++;
                if (ch === ")") leftBracket--;
                if (leftBracket === 0) {
                    args.push(searchStr.substring(0, i + 1));
                    args.push(searchStr.substring(startLeft + 1, i));
                    return args;
                }
            }
        }
        return null;
    }
    /**
     * Parse the input for a calculated field.
     * String split on comma, EXCEPT if comma is within brackets (i.e. within an inner function)
     * or within a string like ", "
     * @param {String} paramString - Search and parse this string for parameters.
     * @returns {String[]} - List of function parameters.
     */
    static parseForParams(paramString, startBracket = "(", endBracket = ")") {
        const args = [];
        let bracketCount = 0;
        let inQuotes = "";
        let start = 0;
        for (let i = 0; i < paramString.length; i++) {
            const ch = paramString.charAt(i);
            if (ch === "," && bracketCount === 0 && inQuotes === "") {
                args.push(paramString.substring(start, i));
                start = i + 1;
            }
            else {
                bracketCount += SelectTables.functionBracketCounter(ch, startBracket, endBracket);
            }
            inQuotes = SelectTables.checkIfWithinString(ch, inQuotes);
        }
        const lastStr = paramString.substring(start);
        if (lastStr !== "")
            args.push(lastStr);
        return args;
    }
    /**
     * Track net brackets encountered in string.
     * @param {String} ch 
     * @param {String} startBracket 
     * @param {String} endBracket 
     * @returns {Number}
     */
    static functionBracketCounter(ch, startBracket, endBracket) {
        if (ch === startBracket)
            return 1;
        else if (ch === endBracket)
            return -1;
        return 0;
    }
    /**
     * Track if current ch(ar) is within quotes.
     * @param {String} ch 
     * @param {String} inQuotes 
     * @returns {String} - Returns empty string if not within a string constant.
     * If it is within a string, it will return either a single or double quote so we can
     * determine when the string ends (it will match the starting quote.)
     */
    static checkIfWithinString(ch, inQuotes) {
        if (inQuotes === "") {
            if (ch === '"' || ch === "'")
                return ch;
        }
        else if (ch === inQuotes) {
            return "";
        }
        return inQuotes;
    }
    /**
     * Compress the table data so there is one record per group (fields in GROUP BY).
     * The other fields MUST be aggregate calculated fields that works on the data in that group.
     * @param {Object} ast - Abstract Syntax Tree
     * @param {any[][]} viewTableData - Table data.
     * @returns {any[][]} - Aggregated table data.
     */
    groupBy(ast, viewTableData) {
        let groupedTableData = viewTableData;
        if (typeof ast['GROUP BY'] !== 'undefined') {
            groupedTableData = this.groupByFields(ast['GROUP BY'], viewTableData);
            if (typeof ast.HAVING !== 'undefined') {
                groupedTableData = this.having(ast.HAVING, groupedTableData);
            }
        }
        //  If any conglomerate field functions (SUM, COUNT,...)
        //  we summarize all records into ONE.
        else if (this.tableFields.getConglomerateFieldCount() > 0) {
            const compressedData = [];
            const conglomerate = new ConglomerateRecord(this.tableFields.getSelectFields());
            compressedData.push(conglomerate.squish(viewTableData));
            groupedTableData = compressedData;
        }
        return groupedTableData;
    }
    /**
    * Group table data by group fields.
    * @param {any[]} astGroupBy - AST group by fields.
    * @param {any[][]} selectedData - table data
    * @returns {any[][]} - compressed table data
    */
    groupByFields(astGroupBy, selectedData) {
        if (selectedData.length === 0)
            return selectedData;
        //  Sort the least important first, and most important last.
        astGroupBy.reverse();
        for (const orderField of astGroupBy) {
            const selectColumn = this.tableFields.getSelectFieldColumn(orderField.name);
            if (selectColumn !== -1) {
                SelectTables.sortByColumnASC(selectedData, selectColumn);
            }
        }
        const groupedData = [];
        let groupRecords = [];
        const conglomerate = new ConglomerateRecord(this.tableFields.getSelectFields());
        let lastKey = this.createGroupByKey(selectedData[0], astGroupBy);
        for (const row of selectedData) {
            const newKey = this.createGroupByKey(row, astGroupBy);
            if (newKey !== lastKey) {
                groupedData.push(conglomerate.squish(groupRecords));
                lastKey = newKey;
                groupRecords = [];
            }
            groupRecords.push(row);
        }
        if (groupRecords.length > 0)
            groupedData.push(conglomerate.squish(groupRecords));
        return groupedData;
    }
    /**
     * Create a composite key that is comprised from all field data in group by clause.
     * @param {any[]} row  - current row of data.
     * @param {any[]} astGroupBy - group by fields
     * @returns {String} - group key
     */
    createGroupByKey(row, astGroupBy) {
        let key = "";
        for (const orderField of astGroupBy) {
            const selectColumn = this.tableFields.getSelectFieldColumn(orderField.name);
            if (selectColumn !== -1)
                key += row[selectColumn].toString();
        }
        return key;
    }
    /**
    * Take the compressed data from GROUP BY and then filter those records using HAVING conditions.
    * @param {Object} astHaving - AST HAVING conditons
    * @param {any[][]} selectedData - compressed table data (from group by)
    * @returns {any[][]} - filtered data using HAVING conditions.
    */
    having(astHaving, selectedData) {
        //  Add in the title row for now
        selectedData.unshift(this.tableFields.getColumnNames());
        //  Create our virtual GROUP table with data already selected.
        const groupTable = new Table(this.primaryTable).loadArrayData(selectedData);
        /** @type {Map<String, Table>} */
        const tableMapping = new Map();
        tableMapping.set(this.primaryTable.toUpperCase(), groupTable);
        //  Set up for our SQL.
        const inSQL = new Sql().setTables(tableMapping);
        //  Fudge the HAVING to look like a SELECT.
        const astSelect = {};
        astSelect.FROM = { table: this.primaryTable, as: '' };
        astSelect.SELECT = [{ name: "*" }];
        astSelect.WHERE = astHaving;
        return inSQL.execute(astSelect);
    }
    /**
     * Take select data and sort by columns specified in ORDER BY clause.
     * @param {Object} ast - Abstract Syntax Tree for SELECT
     * @param {any[][]} selectedData - Table data to sort.  On function return, this array is sorted.
     */
    orderBy(ast, selectedData) {
        if (typeof ast['ORDER BY'] === 'undefined')
            return;
        const astOrderby = ast['ORDER BY']
        //  Sort the least important first, and most important last.
        const reverseOrderBy = astOrderby.reverse();
        for (const orderField of reverseOrderBy) {
            const selectColumn = this.tableFields.getSelectFieldColumn(orderField.name);
            if (selectColumn === -1) {
                throw new Error(`Invalid ORDER BY: ${orderField.name}`);
            }
            if (orderField.order.toUpperCase() === "DESC") {
                SelectTables.sortByColumnDESC(selectedData, selectColumn);
            }
            else {
                SelectTables.sortByColumnASC(selectedData, selectColumn);
            }
        }
    }
    /**
     * Removes temporary fields from return data.  These temporary fields were needed to generate
     * the final table data, but are not included in the SELECT fields for final output.
     * @param {any[][]} viewTableData - table data that may contain temporary columns.
     * @returns {any[][]} - table data with temporary columns removed.
     */
    removeTempColumns(viewTableData) {
        const tempColumns = this.tableFields.getTempSelectedColumnNumbers();
        if (tempColumns.length === 0)
            return viewTableData;
        for (const row of viewTableData) {
            for (const col of tempColumns) {
                row.splice(col, 1);
            }
        }
        return viewTableData;
    }
    /**
     * @param {Object} ast 
     * @param {any[][]} viewTableData 
     * @returns {any[][]}
     */
    static limit(ast, viewTableData) {
        if (typeof ast.LIMIT !== 'undefined') {
            const maxItems = ast.LIMIT.nb;
            if (viewTableData.length > maxItems)
                viewTableData.splice(maxItems);
        }
        return viewTableData;
    }
    /**
     * Sort the table data from lowest to highest using the data in colIndex for sorting.
     * @param {any[][]} tableData - table data to sort.
     * @param {Number} colIndex - column index which indicates which column to use for sorting.
     * @returns {any[][]} - sorted table data.
     */
    static sortByColumnASC(tableData, colIndex) {
        tableData.sort(sortFunction);
        /**
         * 
         * @param {any} a 
         * @param {any} b 
         * @returns {Number}
         */
        function sortFunction(a, b) {
            if (a[colIndex] === b[colIndex]) {
                return 0;
            }
            return (a[colIndex] < b[colIndex]) ? -1 : 1;
        }
        return tableData;
    }
    /**
     * Sort the table data from highest to lowest using the data in colIndex for sorting.
     * @param {any[][]} tableData - table data to sort.
     * @param {Number} colIndex - column index which indicates which column to use for sorting.
     * @returns {any[][]} - sorted table data.
     */
    static sortByColumnDESC(tableData, colIndex) {
        tableData.sort(sortFunction);
        /**
         * 
         * @param {any} a 
         * @param {any} b 
         * @returns {Number}
         */
        function sortFunction(a, b) {
            if (a[colIndex] === b[colIndex]) {
                return 0;
            }
            return (a[colIndex] > b[colIndex]) ? -1 : 1;
        }
        return tableData;
    }
    /**
     * @typedef {Object} ResolvedFieldCondition
     * @property {Table} fieldConditionTableInfo
     * @property {Number} columnNumber - use column data from this column, unless -1.
     * @property {String} constantData - constant data used for column, unless null.
     * @property {String} calculatedField - calculation of data for column, unless empty.
     * @property {CorrelatedSubQuery} subQuery - use this correlated subquery object if not null. 
     * 
     */
    /**
     * Determine what the source of value is for the current field condition.
     * @param {Object} fieldCondition - left or right portion of condition
     * @returns {ResolvedFieldCondition}
     */
    resolveFieldCondition(fieldCondition) {
        /** @type {String} */
        let constantData = null;
        /** @type {Number} */
        let columnNumber = -1;
        /** @type {Table} */
        let fieldConditionTableInfo = null;
        /** @type {String} */
        let calculatedField = "";
        /** @type {CorrelatedSubQuery} */
        let subQuery = null;
        if (typeof fieldCondition.SELECT !== 'undefined') {
            //  Maybe a SELECT within...
            [subQuery, constantData] = this.resolveSubQuery(fieldCondition);
        }
        else if (SelectTables.isStringConstant(fieldCondition))
            //  String constant
            constantData = SelectTables.extractStringConstant(fieldCondition);
        else if (fieldCondition.startsWith('?')) {
            //  Bind variable data.
            constantData = this.resolveBindData(fieldCondition);
        }
        else if (!isNaN(fieldCondition)) {
            //  Literal number.
            constantData = fieldCondition;
        }
        else if (this.tableFields.hasField(fieldCondition)) {
            //  Table field.
            columnNumber = this.tableFields.getFieldColumn(fieldCondition);
            fieldConditionTableInfo = this.tableFields.getTableInfo(fieldCondition);
        }
        else {
            //  Calculated field?
            calculatedField = fieldCondition;
        }
        return { fieldConditionTableInfo, columnNumber, constantData, calculatedField, subQuery };
    }
    /**
     * Handle subquery.  If correlated subquery, return object to handle, otherwise resolve and return constant data.
     * @param {Object} fieldCondition - left or right portion of condition
     * @returns {any[]}
     */
    resolveSubQuery(fieldCondition) {
        /** @type {CorrelatedSubQuery} */
        let subQuery = null;
        /** @type {String} */
        let constantData = null;
        if (SelectTables.isCorrelatedSubQuery(fieldCondition)) {
            subQuery = new CorrelatedSubQuery(this.tableInfo, this.tableFields, this.bindVariables, fieldCondition);
        }
        else {
            const subQueryTableInfo = SelectTables.getSubQueryTableSet(fieldCondition, this.tableInfo);
            const inData = new Sql()
                .setTables(subQueryTableInfo)
                .setBindValues(this.bindVariables)
                .execute(fieldCondition);
            constantData = inData.join(",");
        }
        return [subQuery, constantData];
    }
    /**
     * Get constant bind data
     * @param {String} fieldCondition - left or right portion of condition
     * @returns {any}
     */
    resolveBindData(fieldCondition) {
        //  Bind variable data.
        const constantData = this.bindVariables.get(fieldCondition);
        if (typeof constantData === 'undefined') {
            if (fieldCondition === '?') {
                throw new Error("Bind variable naming is ?1, ?2... where ?1 is first bind data point in list.")
            }
            else {
                throw new Error(`Bind variable ${fieldCondition} was not found`);
            }
        }
        return constantData;
    }
    /**
     * 
     * @param {Object} ast 
     * @returns {Boolean}
     */
    static isCorrelatedSubQuery(ast) {
        const tableSet = new Map();
        TableExtract.extractAstTables(ast, tableSet);
        const tableSetCorrelated = new Map();
        if (typeof ast.WHERE !== 'undefined') {
            TableExtract.getTableNamesWhereCondition(ast.WHERE, tableSetCorrelated);
        }
        // @ts-ignore
        for (const tableName of tableSetCorrelated.keys()) {
            let isFound = false;
            // @ts-ignore
            for (const outerTable of tableSet.keys()) {
                if (outerTable === tableName || tableSet.get(outerTable) === tableName) {
                    isFound = true;
                    break;
                }
            }
            if (!isFound) {
                return true;
            }
        }
        return false;
    }
    /**
     * Create a set of tables that are used in sub-query.
     * @param {Object} ast - Sub-query AST.
     * @param {Map<String,Table>} tableInfo - Master set of tables used for entire select.
     * @returns {Map<String,Table>} - table set for sub-query.
     */
    static getSubQueryTableSet(ast, tableInfo) {
        const tableSubSet = new Map();
        const selectTables = TableExtract.getReferencedTableNamesFromAst(ast);
        for (const found of selectTables) {
            if (found[0] !== "" && !tableSubSet.has(found[0])) {
                tableSubSet.set(found[0], tableInfo.get(found[0]));
            }
            if (found[1] !== "" && !tableSubSet.has(found[1])) {
                tableSubSet.set(found[1], tableInfo.get(found[1]));
            }
        }
        return tableSubSet;
    }
    /**
     * Is the string a constant in the SELECT condition.  
     * @param {String} value - condition to test
     * @returns {Boolean} - Is this string a constant.
     */
    static isStringConstant(value) {
        return value.startsWith('"') && value.endsWith('"') || value.startsWith("'") && value.endsWith("'");
    }
    /**
     * Extract the string literal out of condition.  This removes surrounding quotes.
     * @param {String} value - String that encloses literal string data.
     * @returns {String} - String with quotes removed.
     */
    static extractStringConstant(value) {
        if (value.startsWith('"') && value.endsWith('"'))
            return value.replace(/"/g, '');
        if (value.startsWith("'") && value.endsWith("'"))
            return value.replace(/'/g, '');
        return value;
    }
    /**
     * Convert input into milliseconds.
     * @param {any} value - date as as Date or String.
     * @returns {Number} - date as ms.
     */
    static dateToMs(value) {
        let year = 0;
        let month = 0;
        let dayNum = 0;
        if (value instanceof Date) {
            year = value.getFullYear();
            month = value.getMonth();
            dayNum = value.getDate();
        }
        else if (typeof value === "string") {
            const dateParts = value.split("/");
            if (dateParts.length === 3) {
                year = Number(dateParts[2]);
                month = Number(dateParts[0]) - 1;
                dayNum = Number(dateParts[1]);
            }
        }
        const newDate = new Date(Date.UTC(year, month, dayNum, 12, 0, 0, 0));
        return newDate.getTime();
    }
    /**
     * Return a list of column titles for this table.
     * @param {String} columnTableNameReplacement
     * @returns {String[]} - column titles
     */
    getColumnTitles(columnTableNameReplacement) {
        return this.tableFields.getColumnTitles(columnTableNameReplacement);
    }
}
/**
 * @classdesc
 * Finds a function to be used for doing data comparisons.
 * The WHERE condition needs to execute the exact same data comparison for all records, so
 * there is no need to find (through the switch) what to execute for every record.
 */
class FieldComparisons {
    /**
     * Returns a function to be used for data comparisons.
     * @param {String} operator SQL comparison operator.
     * @returns {function}
     }}
     */
    static getComparisonFunction(operator) {
        switch (operator.toUpperCase()) {
            case "=":
                return (leftValue, rightValue) => { return leftValue == rightValue };         // skipcq: JS-0050
            case ">":
                return (leftValue, rightValue) => { return leftValue > rightValue };
            case "<":
                return (leftValue, rightValue) => { return leftValue < rightValue };
            case ">=":
                return (leftValue, rightValue) => { return leftValue >= rightValue };
            case "<=":
                return (leftValue, rightValue) => { return leftValue <= rightValue };
            case "<>":
                return (leftValue, rightValue) => { return leftValue != rightValue };         // skipcq: JS-0050
            case "!=":
                return (leftValue, rightValue) => { return leftValue != rightValue };         // skipcq: JS-0050
            case "LIKE":
                return (leftValue, rightValue) => { return FieldComparisons.likeCondition(leftValue, rightValue) };
            case "NOT LIKE":
                return (leftValue, rightValue) => { return FieldComparisons.notLikeCondition(leftValue, rightValue) };
            case "IN":
                return (leftValue, rightValue) => { return FieldComparisons.inCondition(leftValue, rightValue) };
            case "NOT IN":
                return (leftValue, rightValue) => { return !(FieldComparisons.inCondition(leftValue, rightValue)) };
            case "IS NOT":
                return (leftValue, rightValue) => { return !(FieldComparisons.isCondition(leftValue, rightValue)) };
            case "IS":
                return (leftValue, rightValue) => { return FieldComparisons.isCondition(leftValue, rightValue) };
            case "EXISTS":
                return (leftValue, rightValue) => { return FieldComparisons.existsCondition(rightValue) };
            case "NOT EXISTS":
                return (leftValue, rightValue) => { return !(FieldComparisons.existsCondition(rightValue)) };
            default:
                throw new Error(`Invalid Operator: ${operator}`);
        }
    }
    /**
     * Compare strings in LIKE condition
     * @param {String} leftValue - string for comparison
     * @param {String} rightValue - string with wildcard
     * @returns {Boolean} - Do strings match?
     */
    static likeCondition(leftValue, rightValue) {
        if ((leftValue === null || rightValue === null) && !(leftValue === null && rightValue === null)) {
            return false;
        }
        return FieldComparisons.likeConditionMatch(leftValue, rightValue) !== -1;
    }
    /**
     * Compare strings in NOT LIKE condition
     * @param {String} leftValue - string for comparison
     * @param {String} rightValue - string with wildcard
     * @returns {Boolean} - Do strings NOT match?
     */
    static notLikeCondition(leftValue, rightValue) {
        if ((leftValue === null || rightValue === null) && !(leftValue === null && rightValue === null)) {
            return false;
        }
        return FieldComparisons.likeConditionMatch(leftValue, rightValue) === -1;
    }
    /**
     * Compare strings in (NOT) LIKE condition
     * @param {String} leftValue - string for comparison
     * @param {String} rightValue - string with wildcard
     * @returns {Number} - Found position (not found === -1)
     */
    static likeConditionMatch(leftValue, rightValue) {
        // @ts-ignore
        const expanded = `^${rightValue.replace(/%/g, ".*").replace(/_/g, ".")}`;
        const result = leftValue.search(expanded);
        return result;
    }
    /**
     * Check if leftValue is contained in list in rightValue
     * @param {any} leftValue - value to find in right value
     * @param {String} rightValue - list of comma separated values
     * @returns {Boolean} - Is contained IN list.
     */
    static inCondition(leftValue, rightValue) {
        let items = [];
        if (typeof rightValue === 'string') {
            items = rightValue.split(",");
        }
        else {
            //  select * from table WHERE IN (select number from table)
            // @ts-ignore
            items = [rightValue.toString()];
        }
        items = items.map(a => a.trim());
        let index = items.indexOf(leftValue);
        if (index === -1 && typeof leftValue === 'number') {
            index = items.indexOf(leftValue.toString());
        }
        return index !== -1;
    }
    /**
     * If leftValue is empty (we will consider that as NULL), condition will be true
     * @param {any} leftValue - test this value for NULL
     * @param {any} rightValue - 'NULL' considered as NULL.
     * @returns {Boolean} - Is leftValue NULL (like).
     */
    static isCondition(leftValue, rightValue) {
        return (leftValue === "" && rightValue === "NULL");
    }
    /**
     * Test if input is not empty
     * @param {*} rightValue - value to check if empty
     * @returns - true if NOT empty
     */
    static existsCondition(rightValue) {
        return rightValue !== '';
    }
}
/** 
 * @classdesc 
 * Evaulate calculated fields in SELECT statement.  This is achieved by converting the request 
 * into javascript and then using 'Function' to evaulate it.  
 */
class CalculatedField {
    /**
     * 
     * @param {Table} masterTable - JOINed table (unless not joined, then primary table)
     * @param {Table} primaryTable - First table in SELECT
     * @param {TableFields} tableFields - All fields from all tables
     */
    constructor(masterTable, primaryTable, tableFields) {
        /** @property {Table} */
        this.masterTable = masterTable;
        /** @property {Table} */
        this.primaryTable = primaryTable;
        /** @property {Map<String,String>} - Map key=calculated field in SELECT, value=javascript equivalent code */
        this.sqlServerFunctionCache = new Map();
        /** @property {TableField[]} */
        this.masterFields = tableFields.allFields.filter((vField) => this.masterTable === vField.tableInfo);
        /** @property {Map<String, TableField>} */
        this.mapMasterFields = new Map();
        this.masterFields.forEach(fld => this.mapMasterFields.set(fld.fieldName, fld));
    }
    /**
     * Get data from the table for the requested field name and record number
     * @param {String} fldName - Name of field to get data for.
     * @param {Number} masterRecordID - The row number in table to extract data from.
     * @returns {any} - Data from table.  undefined if not found.
     */
    getData(fldName, masterRecordID) {
        const vField = this.mapMasterFields.get(fldName);
        if (typeof vField === 'undefined')
            return vField;
        return vField.getData(masterRecordID);
    }
    /**
     * Evaluate the calculated field for the current table record and return a value.
     * @param {String} calculatedFormula - calculation from SELECT statement
     * @param {Number} masterRecordID - current record ID.
     * @returns {any} - Evaluated data from calculation.
     */
    evaluateCalculatedField(calculatedFormula, masterRecordID) {
        let result = "";
        // e.g.  special case.  count(*)
        if (calculatedFormula === "*") {
            return "*";
        }
        const functionString = this.sqlServerCalcFields(calculatedFormula, masterRecordID);
        try {
            result = new Function(functionString)();
        }
        catch (ex) {
            if (calculatedFormula !== '') {
                throw new Error(`Invalid select field: ${calculatedFormula}`);
            }
            throw new Error(`Calculated Field Error: ${ex.message}.  ${functionString}`);
        }
        return result;
    }
    /**
     * The program is attempting to build some javascript code which we can then execute to 
     * find the value of the calculated field.  There are two parts.
     * 1)  Build LET statements to assign to all possible field name variants,
     * 2)  Add the 'massaged' calculated field so that it can be run in javascript.
     * @param {String} calculatedFormula - calculation from SELECT statement
     * @param {Number} masterRecordID - current table record ID.
     * @returns {String} - String to be executed.  It is valid javascript lines of code.
     */
    sqlServerCalcFields(calculatedFormula, masterRecordID) {
        //  Working on a calculated field.
        const objectsDeclared = new Map();
        const variablesDeclared = new Map();
        let myVars = "";
        for (/** @type {TableField} */ const vField of this.masterFields) {
            //  Get the DATA from this field.  We then build a series of LET statments
            //  and we assign that data to the field name that might be found in a calculated field.
            let varData = vField.getData(masterRecordID);
            if (typeof varData === "string") {
                varData = `'${varData.replace(/'/g, "\\'")}'`;
            }
            else if (varData instanceof Date) {
                varData = `'${varData}'`;
            }
            myVars += this.createAssignmentStatments(vField, objectsDeclared, variablesDeclared, varData);
        }
        const functionString = this.sqlServerFunctions(calculatedFormula);
        return `${myVars} return ${functionString}`;
    }
    /**
     * Creates a javascript code block.  For the current field (vField), a variable is assigned the appropriate
     * value from 'varData'.  For example, if the column was 'ID' and the table was 'BOOKS'.
     * ```
     * "let BOOKS = {};BOOKS.ID = '9';"
     * ```
     * If the BOOKS object had already been declared, later variables would just be:
     * ```
     * "BOOKS.NAME = 'To Kill a Blue Jay';"
     * ```
     * @param {TableField} vField - current field that LET statements will be assigning to.
     * @param {Map<String, Boolean>} objectsDeclared - tracks if TABLE name was been encountered yet.
     * @param {Map<String, Boolean>} variablesDeclared - tracks if variables has already been assigned.
     * @param {String} varData - the data from the table that will be assigned to the variable.
     * @returns {String} - the javascript code block.
     */
    createAssignmentStatments(vField, objectsDeclared, variablesDeclared, varData) {
        let myVars = "";
        for (const aliasName of vField.aliasNames) {
            if ((this.primaryTable.tableName !== vField.tableInfo.tableName && aliasName.indexOf(".") === -1)) {
                continue;
            }
            if (aliasName.indexOf(".") === -1) {
                if (!variablesDeclared.has(aliasName)) {
                    myVars += `let ${aliasName} = ${varData};`;
                    variablesDeclared.set(aliasName, true);
                }
            }
            else {
                const parts = aliasName.split(".");
                if (!objectsDeclared.has(parts[0])) {
                    myVars += `let ${parts[0]} = {};`;
                    objectsDeclared.set(parts[0], true);
                }
                myVars += `${aliasName} = ${varData};`;
            }
        }
        return myVars;
    }
    /**
     * 
     * @param {String} calculatedFormula 
     * @returns {String}
     */
    sqlServerFunctions(calculatedFormula) {
        //  If this calculated field formula has already been put into the required format,
        //  pull this out of our cache rather than redo.
        if (this.sqlServerFunctionCache.has(calculatedFormula))
            return this.sqlServerFunctionCache.get(calculatedFormula);
        const func = new SqlServerFunctions();
        const functionString = func.convertToJs(calculatedFormula, this.masterFields);
        //  No need to recalculate for each row.
        this.sqlServerFunctionCache.set(calculatedFormula, functionString);
        return functionString;
    }
}
/** 
 * @classdesc
 * Correlated Sub-Query requires special lookups for every record in the primary table. */
class CorrelatedSubQuery {
    /**
     * 
     * @param {Map<String, Table>} tableInfo - Map of table info.
     * @param {TableFields} tableFields - Fields from all tables.
     * @param {BindData}  bindData - List of bind data.
     * @param {Object} defaultSubQuery - Select AST
     */
    constructor(tableInfo, tableFields, bindData, defaultSubQuery = null) {
        /** @property {Map<String, Table>} - Map of table info. */
        this.tableInfo = tableInfo;
        /** @property {TableFields} - Fields from all tables.*/
        this.tableFields = tableFields;
        /** @property {BindData} */
        this.bindVariables = bindData;
        /** @property {Object} - AST can be set here and skipped in select() statement. */
        this.defaultSubQuery = defaultSubQuery;
    }
    /**
     * Perform SELECT on sub-query using data from current record in outer table.
     * @param {Number} masterRecordID - Current record number in outer table.
     * @param {CalculatedField} calcSqlField - Calculated field object.
     * @param {Object} ast - Sub-query AST.
     * @returns {any[][]} - double array of selected table data.
     */
    select(masterRecordID, calcSqlField, ast = this.defaultSubQuery) {
        const innerTableInfo = this.tableInfo.get(ast.FROM.table.toUpperCase());
        if (typeof innerTableInfo === 'undefined')
            throw new Error(`No table data found: ${ast.FROM.table}`);
        //  Add BIND variable for all matching fields in WHERE.
        const tempAst = JSON.parse(JSON.stringify(ast));
        const tempBindVariables = new BindData();
        tempBindVariables.addList(this.bindVariables.getBindDataList());
        this.replaceOuterFieldValueInCorrelatedWhere(calcSqlField, masterRecordID, tempAst, tempBindVariables);
        const inData = new Sql()
            .setTables(this.tableInfo)
            .setBindValues(tempBindVariables)
            .execute(tempAst);
        return inData;
    }
    /**
     * If we find the field name in the AST, just replace with '?' and add to bind data variable list.
     * @param {CalculatedField} calcSqlField - List of fields in outer query.  If any are found in subquery, the value of that field for the current record is inserted into subquery before it is executed.
     * @param {Number} masterRecordID - current record number in outer query.
     * @param {Object} tempAst - AST for subquery.  Any field names found from outer query will be replaced with bind place holder '?'.
     * @param {BindData} bindData
     */
    replaceOuterFieldValueInCorrelatedWhere(calcSqlField, masterRecordID, tempAst, bindData) {
        const where = tempAst.WHERE;
        if (typeof where === 'undefined')
            return;
        if (typeof where.logic === 'undefined')
            this.traverseWhere(calcSqlField, [where], masterRecordID, bindData);
        else
            this.traverseWhere(calcSqlField, where.terms, masterRecordID, bindData);
    }
    /**
     * Search the WHERE portion of the subquery to find all references to the table in the outer query.
     * @param {CalculatedField} calcSqlField - List of fields in outer query.
     * @param {Object} terms - terms of WHERE.  It is modified with bind variable placeholders when outer table fields are located.
     * @param {Number} masterRecordID
     * @param {BindData} bindData
     */
    traverseWhere(calcSqlField, terms, masterRecordID, bindData) {
        for (const cond of terms) {
            if (typeof cond.logic === 'undefined') {
                let result = calcSqlField.masterFields.find(item => item.fieldName === cond.left.toUpperCase());
                if (typeof result !== 'undefined') {
                    cond.left = bindData.add(calcSqlField.getData(cond.left.toUpperCase(), masterRecordID));
                }
                result = calcSqlField.masterFields.find(item => item.fieldName === cond.right.toUpperCase());
                if (typeof result !== 'undefined') {
                    cond.right = bindData.add(calcSqlField.getData(cond.right.toUpperCase(), masterRecordID));
                }
            }
            else {
                this.traverseWhere(calcSqlField, [cond.terms], masterRecordID, bindData);
            }
        }
    }
}
/** 
 * @classdesc
 * Tracks all fields in a table (including derived tables when there is a JOIN). 
 */
class VirtualFields {
    constructor() {
        /** @property {Map<String, VirtualField>} - Map to field for fast access. Field name is key. */
        this.virtualFieldMap = new Map();
        /** @property {VirtualField[]} - List of all fields for table. */
        this.virtualFieldList = [];
    }
    /**
     * Adds info for one field into master list of fields for table.
     * @param {VirtualField} field - Information for one field in the table.
     * @param {Boolean} checkForDuplicates - throws error if adding a duplicate field name.
     */
    add(field, checkForDuplicates = false) {
        if (checkForDuplicates && this.virtualFieldMap.has(field.fieldName)) {
            throw new Error(`Duplicate field name: ${field.fieldName}`);
        }
        this.virtualFieldMap.set(field.fieldName, field);
        this.virtualFieldList.push(field);
    }
    /**
     * Returns a list of all fields in table.
     * @returns {VirtualField[]}
     */
    getAllVirtualFields() {
        return this.virtualFieldList;
    }
    /**
     * When the wildcard '*' is found in the SELECT, it will add all fields in table to the AST used in the SELECT.
     * @param {Table} masterTableInfo - The wildcard '*' (if found) will add fields from THIS table to the AST.
     * @param {any[]} astFields - existing SELECT fields list.
     * @returns {any[]} - original AST field list PLUS expanded list of fields if '*' was encountered.
     */
    static expandWildcardFields(masterTableInfo, astFields) {
        for (let i = 0; i < astFields.length; i++) {
            if (astFields[i].name === "*") {
                //  Replace wildcard will actual field names from master table.
                const masterTableFields = [];
                const allExpandedFields = masterTableInfo.getAllExtendedNotationFieldNames();
                for (const virtualField of allExpandedFields) {
                    const selField = { name: virtualField };
                    masterTableFields.push(selField);
                }
                astFields.splice(i, 1, ...masterTableFields);
                break;
            }
        }
        return astFields;
    }
}
/** 
 * @classdesc 
 * Defines all possible table fields including '*' and long/short form (i.e. table.column). 
 */
class VirtualField {                        //  skipcq: JS-0128
    /**
     * @param {String} fieldName - field name
     */
    constructor(fieldName) {
        /** @property {String} - field name */
        this._fieldName = fieldName;
    }
    get fieldName() {
        return this._fieldName;
    }
}
/**  
 * @classdesc
 * The JOIN creates a new logical table. 
 */
class DerivedTable {                     //  skipcq: JS-0128
    constructor() {
        /** @property {Table} */
        this.tableInfo = null;
        /** @property  {TableField} */
        this.leftField = null;
        /** @property  {TableField} */
        this.rightField = null;
        /** @property  {Number[][]} */
        this.leftRecords = null;
        /** @property  {Boolean} */
        this.isOuterJoin = null;
    }
    /**
     * Left side of join condition.
     * @param {TableField} leftField 
     * @returns {DerivedTable}
     */
    setLeftField(leftField) {
        this.leftField = leftField;
        return this;
    }
    /**
     * Right side of join condition
     * @param {TableField} rightField 
     * @returns {DerivedTable}
     */
    setRightField(rightField) {
        this.rightField = rightField;
        return this;
    }
    /**
     * 
     * @param {Number[][]} leftRecords - first index is record ID of left table, second index is a list of the matching record ID's in right table.
     * @returns {DerivedTable} 
     */
    setLeftRecords(leftRecords) {
        this.leftRecords = leftRecords;
        return this;
    }
    /**
     * Indicate if outer or inner join.
     * @param {Boolean} isOuterJoin - true for outer, false for inner
     * @returns {DerivedTable}
     */
    setIsOuterJoin(isOuterJoin) {
        this.isOuterJoin = isOuterJoin;
        return this;
    }
    /**
     * Create derived table from the two tables that are joined.
     * @returns {DerivedTable}
     */
    createTable() {
        const columnCount = this.rightField.tableInfo.getColumnCount();
        const emptyRightRow = Array(columnCount).fill(null);
        const joinedData = [DerivedTable.getCombinedColumnTitles(this.leftField, this.rightField)];
        for (let i = 1; i < this.leftField.tableInfo.tableData.length; i++) {
            if (typeof this.leftRecords[i] !== "undefined") {
                if (typeof this.rightField.tableInfo.tableData[this.leftRecords[i][0]] === "undefined")
                    joinedData.push(this.leftField.tableInfo.tableData[i].concat(emptyRightRow));
                else {
                    const maxJoin = this.leftRecords[i].length;
                    for (let j = 0; j < maxJoin; j++) {
                        joinedData.push(this.leftField.tableInfo.tableData[i].concat(this.rightField.tableInfo.tableData[this.leftRecords[i][j]]));
                    }
                }
            }
        }
        /** @type {Table} */
        this.tableInfo = new Table(DERIVEDTABLE).loadArrayData(joinedData);
        return this;
    }
    /**
    * Is this a derived table - one that has been joined.
    * @returns {Boolean}
    */
    isDerivedTable() {
        return this.tableInfo !== null;
    }
    /**
     * Get derived table info.
     * @returns {Table}
     */
    getTableData() {
        return this.tableInfo;
    }
    /**
     * Create title row from LEFT and RIGHT table.
     * @param {TableField} leftField 
     * @param {TableField} rightField 
     * @returns {String[]}
     */
    static getCombinedColumnTitles(leftField, rightField) {
        const titleRow = leftField.tableInfo.getAllExtendedNotationFieldNames();
        const rightFieldNames = rightField.tableInfo.getAllExtendedNotationFieldNames();
        return titleRow.concat(rightFieldNames);
    }
}
/** 
 * @classdesc
 * Convert SQL CALCULATED fields into javascript code that can be evaulated and converted to data. 
 */
class SqlServerFunctions {
    /**
     * Convert SQL formula to javascript code.
     * @param {String} calculatedFormula - contains SQL formula and parameter(s)
     * @param {TableField[]} masterFields - table fields
     * @returns {String} - javascript code
     */
    convertToJs(calculatedFormula, masterFields) {
        const sqlFunctions = ["ABS", "ADDDATE", "CASE", "CEILING", "CHARINDEX", "COALESCE", "CONCAT", "CONCAT_WS", "CONVERT", "CURDATE",
            "DAY", "DATEDIFF", "FLOOR", "IF", "LEFT", "LEN", "LENGTH", "LOG", "LOG10", "LOWER",
            "LTRIM", "MONTH", "NOW", "POWER", "RAND", "REPLICATE", "REVERSE", "RIGHT", "ROUND", "RTRIM",
            "SPACE", "STUFF", "SUBSTR", "SUBSTRING", "SQRT", "TRIM", "UPPER", "YEAR"];
        /** @property {String} - regex to find components of CASE statement. */
        this.matchCaseWhenThenStr = /WHEN(.*?)THEN(.*?)(?=WHEN|ELSE|$)|ELSE(.*?)(?=$)/;
        /** @property {String} - Original CASE statement. */
        this.originalCaseStatement = "";
        /** @property {String} - Existing state of function string when CASE encountered. */
        this.originalFunctionString = "";
        /** @property {Boolean} - when working on each WHEN/THEN in CASE, is this the first one encountered. */
        this.firstCase = true;
        /** @property {String[]} */
        this.referencedTableColumns = [];
        let functionString = SelectTables.toUpperCaseExceptQuoted(calculatedFormula);
        for (const func of sqlFunctions) {
            let args = SelectTables.parseForFunctions(functionString, func);
            [args, functionString] = this.caseStart(func, args, functionString);
            while (args !== null && args.length > 0) {
                // Split on COMMA, except within brackets.
                const parms = typeof args[1] === 'undefined' ? [] : SelectTables.parseForParams(args[1]);
                let replacement = "";
                try {
                    replacement = this[func.toLocaleLowerCase()](parms, args, masterFields);
                }
                catch (ex) {
                    throw new Error(`Internal Error. Function is missing. ${func}`);
                }
                functionString = functionString.replace(args[0], replacement);
                args = this.parseFunctionArgs(func, functionString);
            }
            functionString = this.caseEnd(func, functionString);
        }
        return functionString;
    }
    /**
     * 
     * @returns {String[]}
     */
    getReferencedColumns() {
        return this.referencedTableColumns;
    }
    //  START SQL SUPPORTED FUNCTIONS
    //  Supported SQL functions entered here!!.  If a new function is to be added, add a new function below
    //  which returns a STRING that can be executed as a Javascript statement.
    abs(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `Math.abs(${parms[0]})`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    adddate(parms) {                            //  skipcq: JS-0105
        return SqlServerFunctions.adddate(parms);
    }
    /**
     * @param {String[]} _parms 
     * @param {String[]} args 
     * @returns {String}
     */
    case(_parms, args) {
        return this.caseWhen(args);
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    ceiling(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `Math.ceil(${parms[0]})`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    charindex(parms) {                          //  skipcq: JS-0105
        return SqlServerFunctions.charIndex(parms);
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    coalesce(parms) {                           //  skipcq: JS-0105
        return SqlServerFunctions.coalesce(parms);
    }
    /**
     * @param {String[]} parms 
     * @param {String[]} _args 
     * @param {TableField[]} masterFields 
     * @returns {String}
     */
    concat(parms, _args, masterFields) {         //  skipcq: JS-0105
        return SqlServerFunctions.concat(parms, masterFields);
    }
    /**
     * @param {String[]} parms 
     * @param {String[]} _args 
     * @param {TableField[]} masterFields 
     * @returns {String}
     */
    concat_ws(parms, _args, masterFields) {      //  skipcq: JS-0105
        return SqlServerFunctions.concat_ws(parms, masterFields);
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    convert(parms) {                            //  skipcq: JS-0105
        return SqlServerFunctions.convert(parms);
    }
    /**
     * @returns {String}
     */
    curdate() {                                 //  skipcq: JS-0105
        return "new Date().toLocaleString().split(',')[0]";
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    datediff(parms) {                            //  skipcq: JS-0105
        return SqlServerFunctions.datediff(parms);
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    day(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `new Date(${parms[0]}).getDate()`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    floor(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `Math.floor(${parms[0]})`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    if(parms) {                                       //  skipcq: JS-0105
        const ifCond = SqlParse.sqlCondition2JsCondition(parms[0]);
        return `${ifCond} ? ${parms[1]} : ${parms[2]};`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    left(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `${parms[0]}.substring(0,${parms[1]})`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    len(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `${parms[0]}.length`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    length(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `${parms[0]}.length`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    log(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `Math.log2(${parms[0]})`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    log10(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `Math.log10(${parms[0]})`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    lower(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `${parms[0]}.toLowerCase()`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    ltrim(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `${parms[0]}.trimStart()`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    month(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `new Date(${parms[0]}).getMonth() + 1`;
    }
    /**
     * @returns {String}
     */
    now() {                                     //  skipcq: JS-0105
        return "new Date().toLocaleString()";
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    power(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `Math.pow(${parms[0]},${parms[1]})`;
    }
    /**
     * @returns {String}
     */
    rand() {                                    //  skipcq: JS-0105
        return "Math.random()";
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    replicate(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `${parms[0]}.toString().repeat(${parms[1]})`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    reverse(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `${parms[0]}.toString().split("").reverse().join("")`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    right(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `${parms[0]}.toString().slice(${parms[0]}.length - ${parms[1]})`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    round(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `Math.round(${parms[0]})`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    rtrim(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `${parms[0]}.toString().trimEnd()`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    space(parms) {                                  //  skipcq: JS-0105
        return `' '.repeat(${parms[0]})`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    stuff(parms) {                                  //  skipcq: JS-0105
        return `${parms[0]}.toString().substring(0,${parms[1]}-1) + ${parms[3]} + ${parms[0]}.toString().substring(${parms[1]} + ${parms[2]} - 1)`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    substr(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `${parms[0]}.toString().substring(${parms[1]} - 1, ${parms[1]} + ${parms[2]} - 1)`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    substring(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `${parms[0]}.toString().substring(${parms[1]} - 1, ${parms[1]} + ${parms[2]} - 1)`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    sqrt(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `Math.sqrt(${parms[0]})`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    trim(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `${parms[0]}.toString().trim()`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    upper(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `${parms[0]}.toString().toUpperCase()`;
    }
    /**
     * @param {String[]} parms 
     * @returns {String}
     */
    year(parms) {
        this.referencedTableColumns.push(parms[0]);
        return `new Date(${parms[0]}).getFullYear()`;
    }
    //  END SQL SUPPORTED FUNCTIONS
    /**
     * Search for SELECT function arguments for specified 'func' only.  Special case for 'CASE'.  It breaks down one WHEN condition at a time.
     * @param {String} func - an SQL function name.
     * @param {String} functionString - SELECT SQL string to search
     * @returns {String[]}
     */
    parseFunctionArgs(func, functionString) {
        let args = [];
        if (func === "CASE")
            args = this.matchCaseWhenThenStr.exec(functionString);
        else
            args = SelectTables.parseForFunctions(functionString, func);
        return args;
    }
    /**
     * Find the position of a substring within a field - in javascript code.
     * @param {any[]} parms - 
     * * parms[0] - string to search for
     * * parms[1] - field name
     * * parms[2] - start to search from this position (starts at 1)
     * @returns {String} - javascript code to find substring position.
     */
    static charIndex(parms) {
        let replacement = "";
        if (typeof parms[2] === 'undefined')
            replacement = `${parms[1]}.toString().indexOf(${parms[0]}) + 1`;
        else
            replacement = `${parms[1]}.toString().indexOf(${parms[0]},${parms[2]} -1) + 1`;
        return replacement;
    }
    /**
     * Returns first non-empty value in a list, in javascript code.
     * @param {any[]} parms - coalesce parameters - no set limit for number of inputs.
     * @returns {String} - javascript to solve
     */
    static coalesce(parms) {
        let replacement = "";
        for (const parm of parms) {
            replacement += `${parm} !== '' ? ${parm} : `;
        }
        replacement += "''";
        return replacement;
    }
    /**
     * 
     * @param {any[]} parms 
     * @param {TableField[]} masterFields 
     * @returns {String}
     */
    static concat(parms, masterFields) {
        parms.unshift("''");
        return SqlServerFunctions.concat_ws(parms, masterFields);
    }
    /**
     * Concatenate all data and use separator between concatenated fields.
     * @param {any[]} parms - 
     * * parm[0] - separator string
     * * parms... - data to concatenate.
     * @param {TableField[]} masterFields - fields in table.
     * @returns {String} - javascript to concatenate all data.
     */
    static concat_ws(parms, masterFields) {
        if (parms.length === 0) {
            return "";
        }
        let replacement = "";
        const separator = parms[0];
        let concatFields = [];
        for (let i = 1; i < parms.length; i++) {
            if (parms[i].trim() === "*") {
                const allTableFields = TableField.getAllExtendedAliasNames(masterFields);
                concatFields = concatFields.concat(allTableFields);
            }
            else {
                concatFields.push(parms[i]);
            }
        }
        for (const field of concatFields) {
            if (replacement !== "") {
                replacement += ` + ${separator} + `;
            }
            replacement += `${field}`;
        }
        return replacement;
    }
    /**
     * Convert data to another type.
     * @param {any[]} parms - 
     * * parm[0] - value to convert
     * * parms[1] -  data type.
     * @returns {String} - javascript to convert data to specified type.
     */
    static convert(parms) {
        let replacement = "";
        const dataType = parms[1].toUpperCase().trim();
        switch (dataType) {
            case "SIGNED":
                replacement = `isNaN(parseInt(${parms[0]}, 10))?0:parseInt(${parms[0]}, 10)`;
                break;
            case "DECIMAL":
                replacement = `isNaN(parseFloat(${parms[0]}))?0:parseFloat(${parms[0]})`;
                break;
            case "CHAR":
                replacement = `${parms[0]}.toString()`;
                break;
            default:
                throw new Error(`Unrecognized data type ${dataType} in CONVERT`);
        }
        return replacement;
    }
    /**
     * Add number of days to a date and return JS code to return this date.
     * @param {any[]} parms 
     * parms[0] - A date.
     * parms[1] - Number of days to add to the date.
     * @returns {String}
     */
    static adddate(parms) {
        if (parms.length < 2) {
            throw new Error("ADDDATE expecting at least two parameters");
        }
        const parm1 = `(new Date(${parms[0]})).getTime()`;
        const parm2 = `(${parms[1]} * (1000 * 3600 * 24))`;
        const totalMs = `(${parm1} + ${parm2})`;
        return `new Date(${totalMs})`;
    }
    /**
     * DATEDIFF(date1, date2) = date1 - date2 (as days)
     * @param {any[]} parms 
     * @returns {String}
     */
    static datediff(parms) {
        if (parms.length !== 2) {
            throw new Error("DATEDIFF expecting two parameters");
        }
        let parm1 = `(new Date(${parms[0]}).getTime())/(1000 * 3600 * 24)`;
        let parm2 = `(new Date(${parms[1]}).getTime())/(1000 * 3600 * 24)`;
        parm1 = `Math.floor(${parm1})`;
        parm2 = `Math.floor(${parm2})`;
        return `${parm1} - ${parm2}`;
    }
    /**
     * When examining the SQL Select CASE, parse for next WHEN,END condition.
     * @param {String} func - current function worked on.  If <> 'CASE', ignore.
     * @param {any[]} args - default return value. 
     * @param {String} functionString 
     * @returns {any[]}
     */
    caseStart(func, args, functionString) {
        let caseArguments = args;
        let caseString = functionString;
        if (func === "CASE") {
            caseArguments = /CASE(.*?)END/i.exec(functionString);
            if (caseArguments !== null && caseArguments.length > 1) {
                this.firstCase = true;
                this.originalFunctionString = functionString;
                this.originalCaseStatement = caseArguments[0];
                caseString = caseArguments[1];
                caseArguments = caseArguments[1].match(this.matchCaseWhenThenStr);
            }
        }
        return [caseArguments, caseString];
    }
    /**
     * Convert SQL CASE to javascript executeable code to solve case options.
     * @param {any[]} args - current CASE WHEN strings.
     * * args[0] - entire WHEN ... THEN ...
     * * args[1] - parsed string after WHEN, before THEN
     * * args[2] - parse string after THEN
     * @returns {String} - js code to handle this WHEN case.
     */
    caseWhen(args) {
        let replacement = "";
        if (args.length > 2) {
            if (typeof args[1] === 'undefined' && typeof args[2] === 'undefined') {
                replacement = `else return ${args[3]};`;
            }
            else {
                if (this.firstCase) {
                    replacement = "(() => {if (";
                    this.firstCase = false;
                }
                else
                    replacement = "else if (";
                replacement += `${SqlParse.sqlCondition2JsCondition(args[1])}) return ${args[2]} ;`;
            }
        }
        return replacement;
    }
    /**
     * Finish up the javascript code to handle the select CASE.
     * @param {String} func - current function being processed.  If <> 'CASE', ignore.
     * @param {String} funcString - current SQL/javascript string in the process of being converted to js.
     * @returns {String} - updated js code
     */
    caseEnd(func, funcString) {
        let functionString = funcString;
        if (func === "CASE" && this.originalFunctionString !== "") {
            functionString += "})();";      //  end of lambda.
            functionString = this.originalFunctionString.replace(this.originalCaseStatement, functionString);
        }
        return functionString;
    }
}
/** 
 * @classdesc
 * Used to create a single row from multiple rows for GROUP BY expressions. 
 */
class ConglomerateRecord {
    /**
     * @param {TableField[]} virtualFields 
     */
    constructor(virtualFields) {
        /** @property {TableField[]} */
        this.selectVirtualFields = virtualFields;
    }
    /**
     * Compress group records to a single row by applying appropriate aggregate functions.
     * @param {any[][]} groupRecords - a group of table data records to compress.
     * @returns {any[]} - compressed record.
     * * If column is not an aggregate function, value from first row of group records is selected. (should all be the same)
     * * If column has aggregate function, that function is applied to all rows from group records.
     */
    squish(groupRecords) {
        const row = [];
        if (groupRecords.length === 0)
            return row;
        let i = 0;
        for (/** @type {TableField} */ const field of this.selectVirtualFields) {
            if (field.aggregateFunction === "")
                row.push(groupRecords[0][i]);
            else {
                row.push(ConglomerateRecord.aggregateColumn(field, groupRecords, i));
            }
            i++;
        }
        return row;
    }
    /**
     * Apply aggregate function to all rows on specified column and return result.
     * @param {TableField} field - field with aggregate function
     * @param {any[]} groupRecords - group of records we apply function to.
     * @param {Number} columnIndex - the column index where data is read from and function is applied on.
     * @returns {any} - value of aggregate function for all group rows.
     */
    static aggregateColumn(field, groupRecords, columnIndex) {
        let groupValue = 0;
        const aggregator = new AggregateTrack(field);
        for (const groupRow of groupRecords) {
            if (groupRow[columnIndex] === 'null')
                continue;
            const numericData = ConglomerateRecord.aggregateColumnToNumeric(groupRow[columnIndex]);
            switch (field.aggregateFunction) {
                case "SUM":
                    groupValue = aggregator.sum(numericData);
                    break;
                case "COUNT":
                    groupValue = aggregator.count(groupRow[columnIndex]);
                    break;
                case "MIN":
                    groupValue = aggregator.minCase(numericData);
                    break;
                case "MAX":
                    groupValue = aggregator.maxCase(numericData);
                    break;
                case "AVG":
                    aggregator.sum(numericData);
                    break;
                case "GROUP_CONCAT":
                    aggregator.addGroupConcatItem(groupRow[columnIndex]);
                    break;
                default:
                    throw new Error(`Invalid aggregate function: ${field.aggregateFunction}`);
            }
        }
        if (field.aggregateFunction === "AVG") {
            groupValue = aggregator.getAverage();
        }
        if (field.aggregateFunction === "GROUP_CONCAT") {
            return aggregator.getGroupConcat();
        }
        return groupValue;
    }
    /**
     * 
     * @param {any} columnData 
     * @returns {Number}
     */
    static aggregateColumnToNumeric(columnData) {
        /** @type {any} */
        let numericData = 0;
        if (columnData instanceof Date) {
            numericData = columnData;
        }
        else {
            numericData = Number(columnData);
            numericData = (isNaN(numericData)) ? 0 : numericData;
        }
        return numericData;
    }
}
/**
 * @classdesc Accumulator methods for the various aggregate functions.
 */
class AggregateTrack {
    constructor(field) {
        this.groupValue = 0;
        this.groupConcat = [];
        this.isDistinct = field.distinctSetting === "DISTINCT";
        this.distinctSet = new Set();
        this.first = true;
        this.avgCounter = 0;
    }
    /**
     * 
     * @param {Number} numericData 
     * @returns {Number}
     */
    minCase(numericData) {
        this.groupValue = this.first ? numericData : this.groupValue;
        this.first = false;
        this.groupValue = numericData < this.groupValue ? numericData : this.groupValue;
        return this.groupValue;
    }
    /**
     * 
     * @param {Number} numericData 
     * @returns {Number}
     */
    maxCase(numericData) {
        this.groupValue = this.first ? numericData : this.groupValue;
        this.first = false;
        this.groupValue = numericData > this.groupValue ? numericData : this.groupValue;
        return this.groupValue;
    }
    /**
     * 
     * @param {Number} numericData 
     * @returns {Number}
     */
    sum(numericData) {
        this.avgCounter++;
        this.groupValue += numericData;
        return this.groupValue;
    }
    /**
     * 
     * @returns {Number}
     */
    getAverage() {
        return this.groupValue / this.avgCounter;
    }
    /**
     * 
     * @param {any} columnData 
     * @returns {Number}
     */
    count(columnData) {
        this.groupValue++;
        if (this.isDistinct) {
            this.distinctSet.add(columnData);
            this.groupValue = this.distinctSet.size;
        }
        return this.groupValue;
    }
    /**
     * 
     * @param {any} columnData 
     * @returns {void}
     */
    addGroupConcatItem(columnData) {
        if (this.isDistinct) {
            this.distinctSet.add(columnData);
        }
        else {
            this.groupConcat.push(columnData);
        }
    }
    /**
     * All data from column returned as single string with items separated by comma.
     * @returns {String}
     */
    getGroupConcat() {
        if (this.isDistinct) {
            this.groupConcat = Array.from(this.distinctSet.keys());
        }
        this.groupConcat.sort((a, b) => {
            if (a > b) {
                return 1;
            }
            if (b > a) {
                return -1;
            }
            return 0;
        });
        return this.groupConcat.join();
    }
}
/** 
 * @classdesc
 * Fields from all tables. 
 * */
class TableFields {
    constructor() {
        /** @property {TableField[]} */
        this.allFields = [];
        /** @property {Map<String, TableField>} */
        this.fieldNameMap = new Map();
        /** @property {Map<String, TableField>} */
        this.tableColumnMap = new Map();
    }
    /**
     * Iterate through all table fields and create a list of these VirtualFields.
     * @param {String} primaryTable - primary FROM table name in select.
     * @param {Map<String,Table>} tableInfo - map of all loaded tables. 
     */
    loadVirtualFields(primaryTable, tableInfo) {
        /** @type {String} */
        let tableName = "";
        /** @type {Table} */
        let tableObject = null;
        // @ts-ignore
        for ([tableName, tableObject] of tableInfo.entries()) {
            const validFieldNames = tableObject.getAllFieldNames();
            for (const field of validFieldNames) {
                const tableColumn = tableObject.getFieldColumn(field);
                let virtualField = this.findTableField(tableName, tableColumn);
                if (virtualField !== null) {
                    virtualField.addAlias(field);
                }
                else {
                    virtualField = new TableField()
                        .setOriginalTable(tableName)
                        .setOriginalTableColumn(tableColumn)
                        .addAlias(field)
                        .setIsPrimaryTable(primaryTable.toUpperCase() === tableName.toUpperCase())
                        .setTableInfo(tableObject);
                    this.allFields.push(virtualField);
                }
                this.indexTableField(virtualField, primaryTable.toUpperCase() === tableName.toUpperCase());
            }
        }
        this.allFields.sort(TableFields.sortPrimaryFields);
    }
    /**
     * Sort function for table fields list.
     * @param {TableField} fldA 
     * @param {TableField} fldB 
     */
    static sortPrimaryFields(fldA, fldB) {
        let keyA = fldA.isPrimaryTable ? 0 : 1000;
        let keyB = fldB.isPrimaryTable ? 0 : 1000;
        keyA += fldA.originalTableColumn;
        keyB += fldB.originalTableColumn;
        if (keyA < keyB)
            return -1;
        else if (keyA > keyB)
            return 1;
        return 0;
    }
    /**
     * Set up mapping to quickly find field info - by all (alias) names, by table+column.
     * @param {TableField} field - field info.
     * @param {Boolean} isPrimaryTable - is this a field from the SELECT FROM TABLE.
     */
    indexTableField(field, isPrimaryTable = false) {
        for (const aliasField of field.aliasNames) {
            const fieldInfo = this.fieldNameMap.get(aliasField);
            if (typeof fieldInfo === 'undefined' || isPrimaryTable) {
                this.fieldNameMap.set(aliasField, field);
            }
        }
        //  This is something referenced in GROUP BY but is NOT in the SELECTED fields list.
        if (field.tempField && !this.fieldNameMap.has(field.columnName.toUpperCase())) {
            this.fieldNameMap.set(field.columnName.toUpperCase(), field);
        }
        if (field.originalTableColumn !== -1) {
            const key = `${field.originalTable}:${field.originalTableColumn}`;
            if (!this.tableColumnMap.has(key))
                this.tableColumnMap.set(key, field);
        }
    }
    /**
     * Quickly find field info for TABLE + COLUMN NUMBER (key of map)
     * @param {String} tableName - Table name to search for.
     * @param {Number} tableColumn - Column number to search for.
     * @returns {TableField} -located table info (null if not found).
     */
    findTableField(tableName, tableColumn) {
        const key = `${tableName}:${tableColumn}`;
        return !this.tableColumnMap.has(key) ? null : this.tableColumnMap.get(key);
    }
    /**
     * Is this field in our map.
     * @param {String} field - field name
     * @returns {Boolean} - found in map if true.
     */
    hasField(field) {
        return this.fieldNameMap.has(field.toUpperCase());
    }
    /**
     * Get field info.
     * @param {String} field - table column name to find 
     * @returns {TableField} - table info (undefined if not found)
     */
    getFieldInfo(field) {
        return this.fieldNameMap.get(field.toUpperCase());
    }
    /**
     * Get table associated with field name.
     * @param {String} field - field name to search for
     * @returns {Table} - associated table info (undefined if not found)
     */
    getTableInfo(field) {
        const fldInfo = this.getFieldInfo(field);
        return typeof fldInfo !== 'undefined' ? fldInfo.tableInfo : fldInfo;
    }
    /**
     * Get column number for field.
     * @param {String} field - field name
     * @returns {Number} - column number in table for field (-1 if not found)
     */
    getFieldColumn(field) {
        const fld = this.getFieldInfo(field);
        return fld !== null ? fld.tableColumn : -1;
    }
    /**
     * Get field column number.
     * @param {String} field - field name
     * @returns {Number} - column number.
     */
    getSelectFieldColumn(field) {
        const fld = this.getFieldInfo(field);
        if (typeof fld !== 'undefined' && fld.selectColumn !== -1) {
            return fld.selectColumn;
        }
        return -1;
    }
    /**
     * @typedef {Object} SelectFieldParameters
     * @property {Object} selField 
     * @property {Object} parsedField 
     * @property {String} columnTitle 
     * @property {Number} nextColumnPosition
     * @property {Boolean} isTempField
     */
    /**
     * Updates internal SELECTED (returned in data) field list.
     * @param {Object} astFields - AST from SELECT
     * @param {Number} nextColumnPosition
     * @param {Boolean} isTempField
     */
    updateSelectFieldList(astFields, nextColumnPosition, isTempField) {
        for (const selField of astFields) {
            const parsedField = this.parseAstSelectField(selField);
            const columnTitle = (typeof selField.as !== 'undefined' && selField.as !== "" ? selField.as : selField.name);
            /** @type {SelectFieldParameters} */
            const selectedFieldParms = {
                selField, parsedField, columnTitle, nextColumnPosition, isTempField
            };
            if (parsedField.calculatedField === null && this.hasField(parsedField.columnName)) {
                this.updateColumnAsSelected(selectedFieldParms);
                nextColumnPosition = selectedFieldParms.nextColumnPosition;
            }
            else if (parsedField.calculatedField !== null) {
                this.updateCalculatedAsSelected(selectedFieldParms);
                nextColumnPosition++;
            }
            else {
                this.updateConstantAsSelected(selectedFieldParms);
                nextColumnPosition++;
            }
        }
    }
    /**
     * 
     * @param {SelectFieldParameters} selectedFieldParms 
     * @returns {void}
     */
    updateColumnAsSelected(selectedFieldParms) {
        let fieldInfo = this.getFieldInfo(selectedFieldParms.parsedField.columnName);
        //  If GROUP BY field is in our SELECT field list - we can ignore.
        if (selectedFieldParms.isTempField && fieldInfo.selectColumn !== -1)
            return;
        if (selectedFieldParms.parsedField.aggregateFunctionName !== "" || fieldInfo.selectColumn !== -1) {
            //  A new SELECT field, not from existing.
            const newFieldInfo = new TableField();
            Object.assign(newFieldInfo, fieldInfo);
            fieldInfo = newFieldInfo;
            this.allFields.push(fieldInfo);
        }
        fieldInfo
            .setAggregateFunction(selectedFieldParms.parsedField.aggregateFunctionName)
            .setColumnTitle(selectedFieldParms.columnTitle)
            .setColumnName(selectedFieldParms.selField.name)
            .setDistinctSetting(selectedFieldParms.parsedField.fieldDistinct)
            .setSelectColumn(selectedFieldParms.nextColumnPosition)
            .setIsTempField(selectedFieldParms.isTempField);
        selectedFieldParms.nextColumnPosition++;
        this.indexTableField(fieldInfo);
    }
    /**
     * 
     * @param {SelectFieldParameters} selectedFieldParms 
     */
    updateCalculatedAsSelected(selectedFieldParms) {
        const fieldInfo = new TableField();
        this.allFields.push(fieldInfo);
        fieldInfo
            .setColumnTitle(selectedFieldParms.columnTitle)
            .setColumnName(selectedFieldParms.selField.name)
            .setSelectColumn(selectedFieldParms.nextColumnPosition)
            .setCalculatedFormula(selectedFieldParms.selField.name)
            .setSubQueryAst(selectedFieldParms.selField.subQuery)
            .setIsTempField(selectedFieldParms.isTempField);
        this.indexTableField(fieldInfo);
    }
    /**
     * 
     * @param {SelectFieldParameters} selectedFieldParms 
     */
    updateConstantAsSelected(selectedFieldParms) {
        const fieldInfo = new TableField();
        this.allFields.push(fieldInfo);
        fieldInfo
            .setCalculatedFormula(selectedFieldParms.parsedField.columnName)
            .setAggregateFunction(selectedFieldParms.parsedField.aggregateFunctionName)
            .setSelectColumn(selectedFieldParms.nextColumnPosition)
            .setColumnName(selectedFieldParms.selField.name)
            .setColumnTitle(selectedFieldParms.columnTitle)
            .setIsTempField(selectedFieldParms.isTempField);
        this.indexTableField(fieldInfo);
    }
    /**
     * Find next available column number in selected field list.
     * @returns {Number} - column number
     */
    getNextSelectColumnNumber() {
        let next = -1;
        for (const fld of this.getSelectFields()) {
            next = fld.selectColumn > next ? fld.selectColumn : next;
        }
        return next === -1 ? next : ++next;
    }
    /**
     * Return a list of temporary column numbers in select field list.
     * @returns {Number[]} - sorted list of temp column numbers.
     */
    getTempSelectedColumnNumbers() {
        /** @type {Number[]} */
        const tempCols = [];
        for (const fld of this.getSelectFields()) {
            if (fld.tempField) {
                tempCols.push(fld.selectColumn);
            }
        }
        tempCols.sort((a, b) => (b - a));
        return tempCols;
    }
    /**
     * Get a sorted list (by column number) of selected fields.
     * @returns {TableField[]} - selected fields
     */
    getSelectFields() {
        const selectedFields = this.allFields.filter((a) => a.selectColumn !== -1);
        selectedFields.sort((a, b) => a.selectColumn - b.selectColumn);
        return selectedFields;
    }
    /**
     * Get SELECTED Field names sorted list of column number.
     * @returns {String[]} - Table field names
     */
    getColumnNames() {
        const columnNames = [];
        this.getSelectFields().forEach(fld => columnNames.push(fld.columnName));
        return columnNames;
    }
    /**
     * Get column titles. If alias was set, that column would be the alias, otherwise it is column name.
     * @param {String} columnTableNameReplacement
     * @returns {String[]} - column titles
     */
    getColumnTitles(columnTableNameReplacement) {
        const columnTitles = [];
        for (const fld of this.getSelectFields()) {
            if (!fld.tempField) {
                let columnOutput = fld.columnTitle;
                //  When subquery table data becomes data for the derived table name, references to
                //  original table names in column output needs to be changed to new derived table name.
                if (columnTableNameReplacement !== null) {
                    const matchingTableIndex = columnOutput.toUpperCase().indexOf(`${fld.originalTable}.`);
                    columnOutput = matchingTableIndex === 0 ? columnTableNameReplacement + columnOutput.slice(matchingTableIndex + fld.originalTable.length) : columnOutput;
                }
                columnTitles.push(columnOutput);
            }
        }
        return columnTitles;
    }
    /**
     * Derived tables will cause an update to any TableField.  It updates with a new column number and new table (derived) info.
     * @param {DerivedTable} derivedTable - derived table info.
     */
    updateDerivedTableVirtualFields(derivedTable) {
        const derivedTableFields = derivedTable.tableInfo.getAllVirtualFields();
        let fieldNo = 0;
        for (const field of derivedTableFields) {
            if (this.hasField(field.fieldName)) {
                const originalField = this.getFieldInfo(field.fieldName);
                originalField.derivedTableColumn = fieldNo;
                originalField.tableInfo = derivedTable.tableInfo;
            }
            fieldNo++;
        }
    }
    /**
     * @typedef {Object} ParsedSelectField
     * @property {String} columnName
     * @property {String} aggregateFunctionName
     * @property {Object} calculatedField
     * @property {String} fieldDistinct
     */
    /**
     * Parse SELECT field in AST (may include functions or calculations)
     * @param {Object} selField 
     * @returns {ParsedSelectField}
     */
    parseAstSelectField(selField) {
        let columnName = selField.name;
        let aggregateFunctionName = "";
        let fieldDistinct = "";
        const calculatedField = (typeof selField.terms === 'undefined') ? null : selField.terms;
        if (calculatedField === null && !this.hasField(columnName)) {
            const functionNameRegex = /^\w+\s*(?=\()/;
            let matches = columnName.match(functionNameRegex)
            if (matches !== null && matches.length > 0)
                aggregateFunctionName = matches[0].trim();
            matches = SelectTables.parseForFunctions(columnName, aggregateFunctionName);
            if (matches !== null && matches.length > 1) {
                columnName = matches[1];
                // e.g.  select count(distinct field)    OR   select count(all field)
                [columnName, fieldDistinct] = TableFields.getSelectCountModifiers(columnName);
            }
        }
        return { columnName, aggregateFunctionName, calculatedField, fieldDistinct };
    }
    /**
     * Parse for any SELECT COUNT modifiers like 'DISTINCT' or 'ALL'.
     * @param {String} originalColumnName - column (e.g. 'distinct customer_id')
     * @returns {String[]} - [0] - parsed column name, [1] - count modifier
     */
    static getSelectCountModifiers(originalColumnName) {
        let fieldDistinct = "";
        let columnName = originalColumnName;
        //  e.g.  count(distinct field)
        const distinctParts = columnName.split(" ");
        if (distinctParts.length > 1) {
            const distinctModifiers = ["DISTINCT", "ALL"];
            if (distinctModifiers.includes(distinctParts[0].toUpperCase())) {
                fieldDistinct = distinctParts[0].toUpperCase();
                columnName = distinctParts[1];
            }
        }
        //  Edge case for group_concat(distinct(field))
        if (fieldDistinct === '') {
            const matches = SelectTables.parseForFunctions(columnName.toUpperCase(), "DISTINCT");
            if (matches !== null && matches.length > 1) {
                columnName = matches[1];
                fieldDistinct = "DISTINCT";
            }
        }
        return [columnName, fieldDistinct];
    }
    /**
     * Counts the number of conglomerate field functions in SELECT field list.
     * @returns {Number} - Number of conglomerate functions.
     */
    getConglomerateFieldCount() {
        return this.getSelectFields().filter(field => field.aggregateFunction !== "").length;
    }
}
/** 
 * @classdesc
 * Table column information. 
 */
class TableField {
    constructor() {
        /** @property {String} */
        this.originalTable = "";
        /** @property {Number} */
        this.originalTableColumn = -1;
        /** @property {String[]} */
        this.aliasNames = [];
        /** @property {String} */
        this.fieldName = "";
        /** @property {Number} */
        this.derivedTableColumn = -1;
        /** @property {Number} */
        this.selectColumn = -1;
        /** @property {Boolean} */
        this.tempField = false;
        /** @property {String} */
        this.calculatedFormula = "";
        /** @property {String} */
        this.aggregateFunction = "";
        /** @property {String} */
        this.columnTitle = "";
        /** @property {String} */
        this.columnName = "";
        /** @property {String} */
        this.distinctSetting = "";
        /** @property {Object} */
        this.subQueryAst = null;
        /** @property {Boolean} */
        this._isPrimaryTable = false;
        /** @property {Table} */
        this.tableInfo = null;
    }
    /**
     * Get field column number.
     * @returns {Number} - column number
     */
    get tableColumn() {
        return this.derivedTableColumn === -1 ? this.originalTableColumn : this.derivedTableColumn;
    }
    /**
     * Original table name before any derived table updates.
     * @param {String} table - original table name
     * @returns {TableField}
     */
    setOriginalTable(table) {
        this.originalTable = table.trim().toUpperCase();
        return this;
    }
    /**
     * Column name found in column title row.
     * @param {Number} column 
     * @returns {TableField}
     */
    setOriginalTableColumn(column) {
        this.originalTableColumn = column;
        return this;
    }
    /**
     * Alias name assigned to field in select statement.
     * @param {String} columnAlias - alias name
     * @returns {TableField}
     */
    addAlias(columnAlias) {
        const alias = columnAlias.trim().toUpperCase();
        if (this.fieldName === "" || alias.indexOf(".") !== -1) {
            this.fieldName = alias;
        }
        if (this.aliasNames.indexOf(alias) === -1) {
            this.aliasNames.push(alias);
        }
        return this;
    }
    /**
     * Set column number in table data for field.
     * @param {Number} column - column number.
     * @returns {TableField}
     */
    setSelectColumn(column) {
        this.selectColumn = column;
        return this;
    }
    /**
     * Fields referenced BUT not in final output.
     * @param {Boolean} value 
     * @returns {TableField}
     */
    setIsTempField(value) {
        this.tempField = value;
        return this;
    }
    /**
     * Aggregate function number used (e.g. 'SUM')
     * @param {String} value - aggregate function name or ''
     * @returns {TableField}
     */
    setAggregateFunction(value) {
        this.aggregateFunction = value.toUpperCase();
        return this;
    }
    /**
     * Calculated formula for field (e.g. 'CASE WHEN QUANTITY >= 100 THEN 1 ELSE 0 END')
     * @param {String} value 
     * @returns {TableField}
     */
    setCalculatedFormula(value) {
        this.calculatedFormula = value;
        return this;
    }
    /**
     * The AST from just the subquery in the SELECT.
     * @param {Object} ast - subquery ast.
     * @returns {TableField}
     */
    setSubQueryAst(ast) {
        this.subQueryAst = ast;
        return this;
    }
    /**
     * Set column TITLE.  If an alias is available, that is used - otherwise it is column name.
     * @param {String} columnTitle - column title used in output
     * @returns {TableField}
     */
    setColumnTitle(columnTitle) {
        this.columnTitle = columnTitle;
        return this;
    }
    /**
     * Set the columnname.
     * @param {String} columnName 
     * @returns {TableField}
     */
    setColumnName(columnName) {
        this.columnName = columnName;
        return this;
    }
    /**
     * Set any count modified like 'DISTINCT' or 'ALL'.
     * @param {String} distinctSetting 
     * @returns {TableField}
     */
    setDistinctSetting(distinctSetting) {
        this.distinctSetting = distinctSetting;
        return this
    }
    /**
     * Set if this field belongs to primary table (i.e. select * from table), rather than a joined tabled.
     * @param {Boolean} isPrimary - true if from primary table.
     * @returns {TableField}
     */
    setIsPrimaryTable(isPrimary) {
        this._isPrimaryTable = isPrimary;
        return this;
    }
    /**
     * Is this field in the primary table.
     * @returns {Boolean}
     */
    get isPrimaryTable() {
        return this._isPrimaryTable;
    }
    /**
     * Link this field to the table info.
     * @param {Table} tableInfo 
     * @returns {TableField}
     */
    setTableInfo(tableInfo) {
        this.tableInfo = tableInfo;
        return this;
    }
    /**
     * Retrieve field data for tableRow
     * @param {Number} tableRow - row to read data from
     * @returns {any} - data
     */
    getData(tableRow) {
        const columnNumber = this.derivedTableColumn === -1 ? this.originalTableColumn : this.derivedTableColumn;
        return this.tableInfo.tableData[tableRow][columnNumber];
    }
    /**
     * Search through list of fields and return a list of those that include the table name (e.g. TABLE.COLUMN vs COLUMN)
     * @param {TableField[]} masterFields 
     * @returns {String[]}
     */
    static getAllExtendedAliasNames(masterFields) {
        const concatFields = [];
        for (const vField of masterFields) {
            for (const aliasName of vField.aliasNames) {
                if (aliasName.indexOf(".") !== -1) {
                    concatFields.push(aliasName);
                }
            }
        }
        return concatFields;
    }
}