import _ from "lodash"
import moment from "moment"
import { getExprExtension } from "./extensions"
import Schema from "./Schema"
import {
  AggrStatus,
  CaseExpr,
  EnumValue,
  Expr,
  FieldExpr,
  LiteralType,
  LocalizedString,
  ScalarExpr,
  SubqueryExpr,
  Variable
} from "./types"
import { WeakCache } from "./WeakCache"

// exprAggrStatus Weak cache is global to allow validator to be created and destroyed
const exprAggrStatusWeakCache = new WeakCache()

/** Categories for op items. Used for categorizing op items in the UI. */
export type OpItemCategory =
  | "logic"
  | "math"
  | "dates"
  | "location"
  | "text"
  | "advanced"

/** opItems are a list of ops for various types */
export interface OpItem {
  /** e.g. "=" */
  op: string
  /** e.g. "is" */
  name: string
  /** Optional description */
  desc?: string
  /** resulting type from op. e.g. "boolean" */
  resultType: LiteralType
  /** array of types of expressions required for arguments */
  exprTypes: LiteralType[]
  /** type of n more expressions (like "and" that takes n arguments) */
  moreExprType?: LiteralType
  /** true if name goes before LHS value */
  prefix?: boolean
  /** overrides name when displayed as prefix */
  prefixLabel?: string
  /** optional condition function on LHS expr that tests if applicable (for "within" which only applies to hierarchical tables) */
  lhsCond?: (lhs: Expr, exprUtils: ExprUtils) => boolean
  /** prefer rhs literal */
  rhsLiteral?: boolean
  /** Force rhs to be literal */
  rhsForceLiteral?: boolean
  /** string to put between exprs when prefix type */
  joiner?: string
  /** true if aggregating (e.g. sum) */
  aggr?: boolean
  /** placeholder for lhs expression */
  lhsPlaceholder?: string
  /** placeholder for rhs expression */
  rhsPlaceholder?: string
  /** Allows an optional final expression argument to specify the ordering field (date/datetime). Will use table ordering if not specified. */
  ordered?: boolean
  /** Optional array of enum values for each expression argument. 
   * If specified for an expression position, these enum values will be used instead of deriving from LHS.
   * Use null for positions where enum values should be derived normally. */
  exprEnumValues?: (EnumValue[] | null | undefined)[]
  /** If true, the RHS value is saved to localStorage and pre-filled on next use */
  rhsSticky?: boolean
  /** Category of op item. Used for categorizing op items in the UI. */
  category: OpItemCategory
}

// Define month enum values for fiscal year operations
const monthEnumValues: EnumValue[] = [
  { id: "01", name: { _base: "en", en: "January", fr: "Janvier", es: "Enero", ht: "Janvye", id: "Januari", sw: "Januari" } },
  { id: "02", name: { _base: "en", en: "February", fr: "Février", es: "Febrero", ht: "Fevriye", id: "Februari", sw: "Februari" } },
  { id: "03", name: { _base: "en", en: "March", fr: "Mars", es: "Marzo", ht: "Mas", id: "Maret", sw: "Machi" } },
  { id: "04", name: { _base: "en", en: "April", fr: "Avril", es: "Abril", ht: "Avril", id: "April", sw: "Aprili" } },
  { id: "05", name: { _base: "en", en: "May", fr: "Mai", es: "Mayo", ht: "Me", id: "Mei", sw: "Mei" } },
  { id: "06", name: { _base: "en", en: "June", fr: "Juin", es: "Junio", ht: "Jen", id: "Juni", sw: "Juni" } },
  { id: "07", name: { _base: "en", en: "July", fr: "Juillet", es: "Julio", ht: "Jiyè", id: "Juli", sw: "Julai" } },
  { id: "08", name: { _base: "en", en: "August", fr: "Août", es: "Agosto", ht: "Out", id: "Agustus", sw: "Agosti" } },
  { id: "09", name: { _base: "en", en: "September", fr: "Septembre", es: "Septiembre", ht: "Septanm", id: "September", sw: "Septemba" } },
  { id: "10", name: { _base: "en", en: "October", fr: "Octobre", es: "Octubre", ht: "Oktòb", id: "Oktober", sw: "Oktoba" } },
  { id: "11", name: { _base: "en", en: "November", fr: "Novembre", es: "Noviembre", ht: "Novanm", id: "November", sw: "Novemba" } },
  { id: "12", name: { _base: "en", en: "December", fr: "Décembre", es: "Diciembre", ht: "Desanm", id: "Desember", sw: "Desemba" } }
]

export default class ExprUtils {
  schema: Schema

  constructor(schema: Schema) {
    this.schema = schema
  }

  /**
   * Search can contain resultTypes, lhsExpr, op, aggr. lhsExpr is actual expression of lhs. resultTypes is optional array of result types
   * If search ordered is not true, excludes ordered ones
   * If prefix, only prefix
   * Results are array of opItems. */
  findMatchingOpItems(search: {
    resultTypes?: LiteralType[]
    lhsExpr?: Expr
    op?: string
    prefix?: boolean
    aggr?: boolean
  }): OpItem[] {
    // Narrow list if op specified
    let items: OpItem[]
    if (search.op) {
      items = groupedOpItems[search.op] || []
    } else {
      items = opItems
    }

    const lhsType = search.lhsExpr ? this.getExprType(search.lhsExpr) : null

    return _.filter(items, (opItem) => {
      if (search.resultTypes) {
        if (!search.resultTypes.includes(opItem.resultType)) {
          return false
        }
      }

      if (search.aggr != null && opItem.aggr !== search.aggr) {
        return false
      }

      if (search.prefix != null && opItem.prefix !== search.prefix) {
        return false
      }

      // Handle list of specified types
      if (search.lhsExpr) {
        if (lhsType) {
          // If no expressions allowed, return false
          if (opItem.exprTypes[0] == null && opItem.moreExprType == null) {
            return false
          }

          // If doesn't match first
          if (opItem.exprTypes[0] != null && opItem.exprTypes[0] !== lhsType) {
            return false
          }

          // If doesn't match more
          if (opItem.exprTypes[0] == null && opItem.moreExprType != null && opItem.moreExprType !== lhsType) {
            return false
          }
        }
      }

      // Check lhsCond
      if (search.lhsExpr && opItem.lhsCond && !opItem.lhsCond(search.lhsExpr, this)) {
        return false
      }

      return true
    })
  }

  /** Determine if op is aggregate */
  static isOpAggr(op: string): boolean {
    return aggrOpItems[op] || false
  }

  /** Determine if op is prefix */
  static isOpPrefix(op: string): boolean {
    return _.findWhere(opItems, { op, prefix: true }) != null
  }

  /** Follows a list of joins to determine final table */
  followJoins(startTable: string, joins: string[]): string {
    let t = startTable
    for (let j of joins) {
      const joinCol = this.schema.getColumn(t, j)!
      if (joinCol.type === "join") {
        t = joinCol.join!.toTable
      } else {
        t = joinCol.idTable!
      }
    }

    return t
  }

  /** Determines if an set of joins contains a multiple */
  isMultipleJoins(table: string, joins: string[]): boolean {
    let t = table
    for (let j of joins) {
      const joinCol = this.schema.getColumn(t, j)!
      if (joinCol.type === "join") {
        if (["1-n", "n-n"].includes(joinCol.join!.type)) {
          return true
        }

        t = joinCol.join!.toTable
      } else if (joinCol.type === "id") {
        t = joinCol.idTable!
      } else if (joinCol.type === "id[]") {
        return true
      } else {
        throw new Error(`Unsupported join type ${joinCol.type}`)
      }
    }

    return false
  }

  /** Return array of { id: <enum value>, name: <localized label of enum value> } */
  getExprEnumValues(expr: Expr): EnumValue[] | null {
    if (!expr) {
      return null
    }

    if (expr.type === "field") {
      const column = this.schema.getColumn(expr.table, expr.column)
      if (!column) {
        return null
      }

      // Prefer returning specified enumValues as expr might not cover all possibilities if it's an if/then, etc.
      if (column.enumValues) {
        return column.enumValues
      }

      return null
    }

    if (expr.type === "scalar") {
      if (expr.expr) {
        return this.getExprEnumValues(expr.expr)
      }
    }

    if (expr.type === "subquery") {
      if (expr.select) {
        return this.getExprEnumValues(expr.select)
      }
    }

    // "last", "last where", "previous", "first", "first where" are only ops to pass through enum values
    if (
      expr.type === "op" &&
      ["last", "last where", "previous", "first", "first where"].includes(expr.op) &&
      expr.exprs[0]
    ) {
      return this.getExprEnumValues(expr.exprs[0])
    }

    // Weeks of month has predefined values (1-5 as text)
    if (expr.type === "op" && expr.op === "weekofmonth") {
      return [
        { id: "1", name: { _base: "en", en: "1" } },
        { id: "2", name: { _base: "en", en: "2" } },
        { id: "3", name: { _base: "en", en: "3" } },
        { id: "4", name: { _base: "en", en: "4" } },
        { id: "5", name: { _base: "en", en: "5" } }
      ]
    }

    // Days of month has predefined values (01-31 as text)
    if (expr.type === "op" && expr.op === "dayofmonth") {
      return [
        { id: "01", name: { _base: "en", en: "01" } },
        { id: "02", name: { _base: "en", en: "02" } },
        { id: "03", name: { _base: "en", en: "03" } },
        { id: "04", name: { _base: "en", en: "04" } },
        { id: "05", name: { _base: "en", en: "05" } },
        { id: "06", name: { _base: "en", en: "06" } },
        { id: "07", name: { _base: "en", en: "07" } },
        { id: "08", name: { _base: "en", en: "08" } },
        { id: "09", name: { _base: "en", en: "09" } },
        { id: "10", name: { _base: "en", en: "10" } },
        { id: "11", name: { _base: "en", en: "11" } },
        { id: "12", name: { _base: "en", en: "12" } },
        { id: "13", name: { _base: "en", en: "13" } },
        { id: "14", name: { _base: "en", en: "14" } },
        { id: "15", name: { _base: "en", en: "15" } },
        { id: "16", name: { _base: "en", en: "16" } },
        { id: "17", name: { _base: "en", en: "17" } },
        { id: "18", name: { _base: "en", en: "18" } },
        { id: "19", name: { _base: "en", en: "19" } },
        { id: "20", name: { _base: "en", en: "20" } },
        { id: "21", name: { _base: "en", en: "21" } },
        { id: "22", name: { _base: "en", en: "22" } },
        { id: "23", name: { _base: "en", en: "23" } },
        { id: "24", name: { _base: "en", en: "24" } },
        { id: "25", name: { _base: "en", en: "25" } },
        { id: "26", name: { _base: "en", en: "26" } },
        { id: "27", name: { _base: "en", en: "27" } },
        { id: "28", name: { _base: "en", en: "28" } },
        { id: "29", name: { _base: "en", en: "29" } },
        { id: "30", name: { _base: "en", en: "30" } },
        { id: "31", name: { _base: "en", en: "31" } }
      ]
    }

    // Month has predefined values
    if (expr.type === "op" && expr.op === "month") {
      return monthEnumValues
    }

    // Week of year has predefined values (01-53 as text)
    if (expr.type === "op" && expr.op === "weekofyear") {
      return [
        { id: "01", name: { _base: "en", en: "01" } },
        { id: "02", name: { _base: "en", en: "02" } },
        { id: "03", name: { _base: "en", en: "03" } },
        { id: "04", name: { _base: "en", en: "04" } },
        { id: "05", name: { _base: "en", en: "05" } },
        { id: "06", name: { _base: "en", en: "06" } },
        { id: "07", name: { _base: "en", en: "07" } },
        { id: "08", name: { _base: "en", en: "08" } },
        { id: "09", name: { _base: "en", en: "09" } },
        { id: "10", name: { _base: "en", en: "10" } },
        { id: "11", name: { _base: "en", en: "11" } },
        { id: "12", name: { _base: "en", en: "12" } },
        { id: "13", name: { _base: "en", en: "13" } },
        { id: "14", name: { _base: "en", en: "14" } },
        { id: "15", name: { _base: "en", en: "15" } },
        { id: "16", name: { _base: "en", en: "16" } },
        { id: "17", name: { _base: "en", en: "17" } },
        { id: "18", name: { _base: "en", en: "18" } },
        { id: "19", name: { _base: "en", en: "19" } },
        { id: "20", name: { _base: "en", en: "20" } },
        { id: "21", name: { _base: "en", en: "21" } },
        { id: "22", name: { _base: "en", en: "22" } },
        { id: "23", name: { _base: "en", en: "23" } },
        { id: "24", name: { _base: "en", en: "24" } },
        { id: "25", name: { _base: "en", en: "25" } },
        { id: "26", name: { _base: "en", en: "26" } },
        { id: "27", name: { _base: "en", en: "27" } },
        { id: "28", name: { _base: "en", en: "28" } },
        { id: "29", name: { _base: "en", en: "29" } },
        { id: "30", name: { _base: "en", en: "30" } },
        { id: "31", name: { _base: "en", en: "31" } },
        { id: "32", name: { _base: "en", en: "32" } },
        { id: "33", name: { _base: "en", en: "33" } },
        { id: "34", name: { _base: "en", en: "34" } },
        { id: "35", name: { _base: "en", en: "35" } },
        { id: "36", name: { _base: "en", en: "36" } },
        { id: "37", name: { _base: "en", en: "37" } },
        { id: "38", name: { _base: "en", en: "38" } },
        { id: "39", name: { _base: "en", en: "39" } },
        { id: "40", name: { _base: "en", en: "40" } },
        { id: "41", name: { _base: "en", en: "41" } },
        { id: "42", name: { _base: "en", en: "42" } },
        { id: "43", name: { _base: "en", en: "43" } },
        { id: "44", name: { _base: "en", en: "44" } },
        { id: "45", name: { _base: "en", en: "45" } },
        { id: "46", name: { _base: "en", en: "46" } },
        { id: "47", name: { _base: "en", en: "47" } },
        { id: "48", name: { _base: "en", en: "48" } },
        { id: "49", name: { _base: "en", en: "49" } },
        { id: "50", name: { _base: "en", en: "50" } },
        { id: "51", name: { _base: "en", en: "51" } },
        { id: "52", name: { _base: "en", en: "52" } },
        { id: "53", name: { _base: "en", en: "53" } }
      ]
    }

    // Day of week has predefined values
    if (expr.type === "op" && expr.op === "dayofweek") {
      return [
        { id: "1", name: { _base: "en", en: "Sunday", es: "Domingo", fr: "Dimanche" } },
        { id: "2", name: { _base: "en", en: "Monday", es: "Lunes", fr: "Lundi" } },
        { id: "3", name: { _base: "en", en: "Tuesday", es: "Martes", fr: "Mardi" } },
        { id: "4", name: { _base: "en", en: "Wednesday", es: "Miércoles", fr: "Mercredi" } },
        { id: "5", name: { _base: "en", en: "Thursday", es: "Jueves", fr: "Jeudi" } },
        { id: "6", name: { _base: "en", en: "Friday", es: "Viernes", fr: "Vendredi" } },
        { id: "7", name: { _base: "en", en: "Saturday", es: "Sábado", fr: "Samedi" } }
      ]
    }

    // Case statements search for possible values
    if (expr.type === "case") {
      let enumValues: EnumValue[] = []

      for (let cse of expr.cases) {
        enumValues = enumValues.concat(this.getExprEnumValues(cse.then) || [])
      }
      enumValues = enumValues.concat(this.getExprEnumValues(expr.else) || [])
      return _.uniq(enumValues, (ev) => ev.id)
    }

    if (expr.type === "variable") {
      return _.findWhere(this.schema.getVariables(), { id: expr.variableId })?.enumValues || null
    }

    if (expr.type == "extension") {
      return getExprExtension(expr.extension).getExprEnumValues(expr, this.schema)
    }

    return null
  }

  /** Gets the id table of an expression of type id */
  getExprIdTable(expr: Expr): string | null {
    if (!expr) {
      return null
    }

    if (expr.type === "literal" && ["id", "id[]"].includes(expr.valueType)) {
      return expr.idTable!
    }

    if (expr.type === "id") {
      return expr.table
    }

    if (expr.type === "scalar") {
      return this.getExprIdTable(expr.expr)
    }

    if (expr.type === "subquery") {
      return this.getExprIdTable(expr.select)
    }

    // Handle fields
    if (expr.type === "field") {
      const column = this.schema.getColumn(expr.table, expr.column)!

      if (column?.type === "join") {
        return column.join!.toTable
      }

      if (["id", "id[]"].includes(column?.type!)) {
        return column.idTable!
      }

      return null
    }

    if (expr.type === "variable") {
      return _.findWhere(this.schema.getVariables(), { id: expr.variableId })?.idTable || null
    }

    if (expr.type == "extension") {
      return getExprExtension(expr.extension).getExprIdTable(expr, this.schema)
    }

    if (expr.type == "case" && expr.cases.length > 0) {
      return this.getExprIdTable(expr.cases[0].then)
    }

    // "last", "last where", "previous", "first", "first where" are only ops to pass through id table
    if (
      expr.type === "op" &&
      ["last", "last where", "previous", "first", "first where"].includes(expr.op) &&
      expr.exprs[0]
    ) {
      return this.getExprIdTable(expr.exprs[0])
    }

    return null
  }

  /** Gets the type of an expression */
  getExprType(expr: Expr): LiteralType | null {
    let type
    if (expr == null || !expr.type) {
      return null
    }

    switch (expr.type) {
      case "field":
        var column = this.schema.getColumn(expr.table, expr.column)
        if (column) {
          if (column.type === "join") {
            if (["1-1", "n-1"].includes(column.join!.type)) {
              return "id"
            } else {
              return "id[]"
            }
          }
          return column.type as LiteralType
        }
        return null
      case "id":
        return "id"
      case "scalar":
        // Legacy support:
        if (expr.aggr) {
          return this.getExprType({ type: "op", op: expr.aggr, table: expr.table, exprs: [expr.expr] })
        }

        return this.getExprType(expr.expr)
      case "op":
        // Check for single-type ops
        var matchingOpItems = this.findMatchingOpItems({ op: expr.op })
        var resultTypes = _.uniq(_.compact(_.pluck(matchingOpItems, "resultType")))
        if (resultTypes.length === 1) {
          return resultTypes[0]
        }

        // Get possible ops
        matchingOpItems = this.findMatchingOpItems({ op: expr.op, lhsExpr: expr.exprs[0] })

        // Get unique resultTypes
        resultTypes = _.uniq(_.compact(_.pluck(matchingOpItems, "resultType")))
        if (resultTypes.length === 1) {
          return resultTypes[0]
        }
        return null

      case "literal":
        return expr.valueType as LiteralType
      case "case":
        // Use type of first then that has value
        for (let cse of expr.cases) {
          type = this.getExprType(cse.then)
          if (type) {
            return type
          }
        }
        return this.getExprType(expr.else)
      case "build enumset":
        return "enumset"
      case "score":
        return "number"
      // case "count": // Deprecated
      //   return "count";
      case "variable":
        var variable = _.findWhere(this.schema.getVariables(), { id: expr.variableId })
        if (!variable) {
          return null
        }
        return variable.type
      case "subquery":
        return this.getExprType(expr.select)
      case "extension":
        return getExprExtension(expr.extension).getExprType(expr, this.schema)
      default:
        // TODO remove
        if ((expr.type as any) == "count") {
          return "count" as any
        }

        throw new Error(`Not implemented for ${expr.type}`)
    }
  }

  /** Determines the aggregation status of an expression. This is whether the expression is
   * aggregate (like sum, avg, etc) or individual (a regular field-containing expression) or
   * literal (which is neither, just a number or text).
   * Invisible second parameter is depth to prevent infinite recursion */
  getExprAggrStatus(expr: Expr, _depth?: number): AggrStatus | null {
    if (expr == null || !expr.type) {
      return null
    }

    const depth = _depth || 0
    if (depth > 100) {
      // Prevent infinite recursion by just returning null
      return null
    }

    // Gets the aggregation status of a series of expressions (takes highest always)
    const getListAggrStatus = (exprs: Expr[]) => {
      // If has no expressions, is literal
      if (exprs.length === 0) {
        return "literal"
      }

      // Get highest type
      const aggrStatuses = _.map(exprs, (subExpr) => this.getExprAggrStatus(subExpr, depth + 1))
      if (aggrStatuses.includes("aggregate")) {
        return "aggregate"
      }
      if (aggrStatuses.includes("individual")) {
        return "individual"
      }
      if (aggrStatuses.includes("literal")) {
        return "literal"
      }
      return null
    }

    switch (expr.type) {
      case "id":
      case "scalar":
      case "subquery":
        return "individual"
      case "field":
        var column = this.schema.getColumn(expr.table, expr.column)
        if (column && column.expr) {
          // This is a slow operation for complex columns. Use weak cache
          // to cache column expression aggregate status
          return exprAggrStatusWeakCache.cacheFunction([this.schema, column.expr], [this.schema.getVariables()], () => {
            return this.getExprAggrStatus(column!.expr!, depth + 1)
          })
        }

        return "individual"
      case "op":
        // If aggregate op
        if (ExprUtils.isOpAggr(expr.op)) {
          return "aggregate"
        }

        return getListAggrStatus(expr.exprs)
      case "literal":
        return "literal"
      case "case":
        // Gather all exprs
        var exprs = [expr.else]
        exprs = exprs.concat(_.map(expr.cases, (cs) => cs.when))
        exprs = exprs.concat(_.map(expr.cases, (cs) => cs.then))
        return getListAggrStatus(exprs)
      case "score":
        return this.getExprAggrStatus(expr.input, depth + 1)
      case "build enumset":
        // Gather all exprs
        exprs = _.values(expr.values)
        return getListAggrStatus(exprs)
      case "count":
      case "comparison":
      case "logical": // Deprecated
        return "individual"
      case "variable":
        var variable = _.findWhere(this.schema.getVariables(), { id: expr.variableId })
        if (!variable) {
          return "literal" // To prevent crash in cleaning, return something
        }
        if (variable.table) {
          return "individual"
        }
        return "literal"
      case "extension":
        return getExprExtension(expr.extension).getExprAggrStatus(expr, this.schema)
      // default:
      //   throw new Error(`Not implemented for ${expr.type}`);
    }
  }

  /** Determines if an set of joins are valid */
  areJoinsValid(table: string, joins: string[]): boolean {
    let t = table
    for (let j of joins) {
      const joinCol = this.schema.getColumn(t, j)
      if (!joinCol) {
        return false
      }

      if (["id", "id[]"].includes(joinCol.type)) {
        t = joinCol.idTable!
      } else if (joinCol.type === "join") {
        t = joinCol.join!.toTable
      } else {
        return false
      }
    }

    return true
  }

  // Gets the expression table
  getExprTable(expr: Expr) {
    if (!expr || expr.type == "literal") {
      return null
    }

    return expr.table
  }

  // Gets the types that can be formed by aggregating an expression
  getAggrTypes(expr: Expr) {
    const aggrOpItems = this.findMatchingOpItems({
      lhsExpr: expr,
      aggr: true
    })
    return _.uniq(_.pluck(aggrOpItems, "resultType"))
  }

  localizeString(name: LocalizedString | string | null | undefined, locale?: string | null): string {
    return ExprUtils.localizeString(name, locale)
  }

  // Localize a string that is { en: "english word", etc. }. Works with null and plain strings too, returning always a string ("" for null)
  static localizeString(name: LocalizedString | string | null | undefined, locale?: string | null): string {
    if (!name) {
      return ""
    }

    // Simple string
    if (typeof name === "string") {
      return name
    }

    if (locale && name[locale] != null) {
      return name[locale]
    }

    if (name._base && name[name._base] != null) {
      return name[name._base]
    }

    // Fall back to English
    if (name.en != null) {
      return name.en
    }

    return ""
  }

  // Combine n expressions together by and
  static andExprs(table: string | undefined, ...exprs: Expr[]): Expr {
    var exprsMapped = _.map(exprs, function (expr) {
      if (expr?.type === "op" && expr.op === "and") {
        return expr.exprs
      } else {
        return expr
      }
    })
    exprsMapped = _.compact(_.flatten(exprsMapped))
    if (exprsMapped.length === 0) {
      return null
    }
    if (exprsMapped.length === 1) {
      return exprsMapped[0] as Expr
    }

    return { type: "op", op: "and", table, exprs: exprsMapped as Expr[] }
  }

  /** Summarizes expression as text */
  summarizeExpr(expr: Expr, locale?: string, enumValues?: EnumValue[]): string {
    if (!expr) {
      return T({ text: "None", locale: locale })
    }

    switch (expr.type) {
      case "scalar":
        return this.summarizeScalarExpr(expr, locale, enumValues)
      case "subquery":
        return this.summarizeSubqueryExpr(expr, locale, enumValues)
      case "field":
        return this.localizeString(this.schema.getColumn(expr.table, expr.column)?.name, locale) || ""
      case "id":
        return this.localizeString(this.schema.getTable(expr.table)?.name, locale) || ""
      case "op":
        // Special case for contains/intersects with literal RHS
        if (expr.op === "contains" && expr.exprs[1]?.type === "literal" && expr.exprs[1]?.valueType === "enumset") {
          return T({
            text: "{0} includes all of {1}",
            locale,
            args: [
              this.summarizeExpr(expr.exprs[0], locale),
              this.stringifyLiteralValue("enumset", expr.exprs[1].value, locale, this.getExprEnumValues(expr.exprs[0]))
            ]
          })
        }

        if (expr.op === "intersects" && expr.exprs[1]?.type === "literal" && expr.exprs[1]?.valueType === "enumset") {
          return T({
            text: "{0} includes any of {1}",
            locale,
            args: [
              this.summarizeExpr(expr.exprs[0], locale),
              this.stringifyLiteralValue("enumset", expr.exprs[1].value, locale, this.getExprEnumValues(expr.exprs[0]))
            ]
          })
        }

        // Special case for = any with literal RHS
        if (expr.op === "= any" && expr.exprs[1]?.type === "literal" && expr.exprs[1]?.valueType === "enumset") {
          return T({
            text: "{0} is any of {1}",
            locale,
            args: [
              this.summarizeExpr(expr.exprs[0], locale),
              this.stringifyLiteralValue("enumset", expr.exprs[1].value, locale, this.getExprEnumValues(expr.exprs[0]))
            ]
          })
        }

        // Special case for = with literal RHS
        if (expr.op === "=" && expr.exprs[1]?.type === "literal" && expr.exprs[1]?.valueType === "enum") {
          return T({
            text: "{0} is {1}",
            locale,
            args: [
              this.summarizeExpr(expr.exprs[0], locale),
              this.stringifyLiteralValue("enum", expr.exprs[1].value, locale, this.getExprEnumValues(expr.exprs[0]))
            ]
          })
        }

        // Special case for <> with literal RHS
        if (expr.op === "<>" && expr.exprs[1]?.type === "literal" && expr.exprs[1]?.valueType === "enum") {
          return T({
            text: "{0} is not {1}",
            locale,
            args: [
              this.summarizeExpr(expr.exprs[0], locale),
              this.stringifyLiteralValue("enum", expr.exprs[1].value, locale, this.getExprEnumValues(expr.exprs[0]))
            ]
          })
        }

        // Special case for count
        if (expr.op === "count") {
          return T({
            text: "Number of {0}",
            locale,
            args: [this.localizeString(this.schema.getTable(expr.table!)?.name || "NOT FOUND", locale)]
          })
        }

        const opItem = this.findMatchingOpItems({ op: expr.op })[0]
        if (opItem) {
          if (opItem.prefix) {
            // Handle custom ordering summarization
            if (opItem.ordered) {
              const valueExpr = expr.exprs[0]
              const conditionExpr = opItem.exprTypes.length > 1 ? expr.exprs[1] : null // e.g., for "last where"
              const orderByExpr = expr.exprs.length > (conditionExpr ? 2 : 1) ? expr.exprs[conditionExpr ? 2 : 1] : null

              let summary = T({ text: opItem.prefixLabel || opItem.name, locale: locale }) + " "
              summary += valueExpr ? this.summarizeExpr(valueExpr, locale) : T({ text: opItem.lhsPlaceholder, locale: locale }) || T({ text: "None", locale: locale })

              if (conditionExpr) {
                summary += " " + (opItem.joiner ? T({ text: opItem.joiner, locale }) : "") + " "
                summary += this.summarizeExpr(conditionExpr, locale)
              } else if (opItem.rhsPlaceholder && !orderByExpr) {
                // Handle placeholder for "where" in "last where" if no condition and no orderby
                summary += " " + (opItem.joiner ? T({ text: opItem.joiner, locale }) : "") + " " + T({ text: opItem.rhsPlaceholder, locale: locale })
              }

              if (orderByExpr) {
                summary += " " + T({ text: "when ordered by", locale }) + " " + this.summarizeExpr(orderByExpr, locale)
              }
              return summary
            }

            // Default prefix summarization
            return (
              T({ text: (opItem.prefixLabel || opItem.name), locale: locale }) +
              " " +
              _.map(expr.exprs, (e, index) => {
                // Only use rhs placeholder if > 0
                if (index === 0) {
                  if (e) {
                    return this.summarizeExpr(e, locale)
                  } else {
                    return T({ text: opItem.lhsPlaceholder, locale: locale }) || T({ text: "None", locale: locale })
                  }
                } else {
                  if (e) {
                    // Use exprEnumValues if specified for this position
                    const exprEnumValues = opItem.exprEnumValues?.[index]
                    return this.summarizeExpr(e, locale, exprEnumValues ?? undefined)
                  } else {
                    return T({ text: opItem.rhsPlaceholder, locale: locale }) || T({ text: "None", locale: locale })
                  }
                }
              }).join(opItem.joiner ? ` ${T({ text: opItem.joiner, locale })} ` : ", ")
            )
          }

          if (expr.exprs.length === 1) {
            return this.summarizeExpr(expr.exprs[0], locale) + " " + T({ text: opItem.name, locale: locale })
          }

          return _.map(expr.exprs, (e) => this.summarizeExpr(e, locale)).join(" " + T({ text: opItem.name, locale: locale }) + " ")
        } else {
          return ""
        }

      case "case":
        return this.summarizeCaseExpr(expr, locale, enumValues)
      case "literal":
        return this.stringifyLiteralValue(expr.valueType, expr.value, locale, enumValues)
      case "score":
        return T({ text: "Score of {0}", locale, args: [this.summarizeExpr(expr.input, locale)] })
      case "build enumset":
        return T({ text: "Build Enumset", locale: locale })
      case "count":
        return T({ text: "Count", locale: locale }) // Deprecated
      case "variable":
        var variable = _.findWhere(this.schema.getVariables(), { id: expr.variableId })
        return variable ? this.localizeString(variable.name, locale) || "" : ""
      case "extension":
        return getExprExtension(expr.extension).summarizeExpr(expr, locale, this.schema)
      default:
        throw new Error(`Unsupported type ${expr.type}`)
    }
  }

  summarizeScalarExpr(expr: ScalarExpr, locale?: string, enumValues?: EnumValue[]) {
    let str = ""

    // Add joins
    let t = expr.table
    for (let join of expr.joins) {
      const joinCol = this.schema.getColumn(t, join)
      if (joinCol) {
        str += this.localizeString(joinCol.name, locale) + " > "
      } else {
        str += "NOT FOUND > "
        break
      }
      if (joinCol.type === "join") {
        t = joinCol.join!.toTable
      } else if (["id", "id[]"].includes(joinCol.type)) {
        t = joinCol.idTable!
      } else {
        str += "INVALID >"
        break
      }
    }

    // Special case for id type to be rendered as {last join name}
    if (expr.expr?.type === "id" && !expr.aggr) {
      str = str.substring(0, str.length - 3)
    } else {
      let innerExpr = expr.expr

      // Handle legacy
      if (expr.aggr) {
        innerExpr = { type: "op", op: expr.aggr, table: (expr as any).expr?.table, exprs: [expr.expr] }
      }

      str += this.summarizeExpr(innerExpr, locale, enumValues)
    }

    return str
  }

  summarizeSubqueryExpr(expr: SubqueryExpr, locale?: string, enumValues?: EnumValue[]) {
    // Create inner exprUtils for subquery
    const innerVariables = this.createSubqueryInnerVariables(expr)
    const innerExprUtils = new ExprUtils(this.schema.addVariables(innerVariables.concat(innerVariables), {}))

    let str = T({ text: "Subquery", locale: locale })
    if (expr.select) {
      str += " "
      if (expr.select.type === "literal" && expr.select.value === true) {
        str += T({ text: "Row exists", locale: locale })
      }
      else {
        str += innerExprUtils.summarizeExpr(expr.select, locale, enumValues)
      }
    }
    if (expr.from) {
      str += " " + T({ text: "in {0}", locale: locale, args: [innerExprUtils.localizeString(this.schema.getTable(expr.from)?.name, locale)] })
    }
    if (expr.where) {
      str += " " + T({ text: "where {0}", locale: locale, args: [innerExprUtils.summarizeExpr(expr.where, locale)] })
    }
    if (expr.orderBys && expr.orderBys.length > 0) {
      str += " " + T({ text: "order by {0}", locale: locale, args: [expr.orderBys.map((o) => innerExprUtils.summarizeExpr(o.expr, locale) + (o.dir == "desc" ? " reverse" : "")).join(", ")] })
    }

    return str
  }

  summarizeCaseExpr(expr: CaseExpr, locale?: string, enumValues?: EnumValue[]) {
    let str = T({ text: "If", locale: locale })
    for (let c of expr.cases) {
      str += " " + this.summarizeExpr(c.when, locale, enumValues)
      str += " " + T({ text: "Then", locale: locale }) + " " + this.summarizeExpr(c.then, locale, enumValues)
    }

    if (expr.else) {
      str += " " + T({ text: "Else", locale: locale }) + " " + this.summarizeExpr(expr.else, locale, enumValues)
    }

    return str
  }

  /** Creates inner variables for a subquery expression from its outer references.
   * Subquery expressions are not allowed to reference the outer query directly, 
   * so we need to create inner variables for them */
  createSubqueryInnerVariables(expr: SubqueryExpr): Variable[] {
    return expr.outerRefs.map((outerRef) => {
      return {
        id: outerRef.id,
        type: this.getExprType(outerRef.expr) || "text",
        name: { _base: "en", en: this.summarizeExpr(outerRef.expr) + " (from outer query)" },
        table: expr.table,
        enumValues: this.getExprEnumValues(outerRef.expr) ?? undefined,
        idTable: this.getExprIdTable(outerRef.expr) ?? undefined
      }
    })
  }

  /** Converts a literal value related to an expression to a string, using name of enums. preferEnumCodes tries to use code over name */
  stringifyExprLiteral(expr: Expr, literal: any, locale?: string, preferEnumCodes?: boolean): string {
    return this.stringifyLiteralValue(
      this.getExprType(expr) as LiteralType,
      literal,
      locale,
      this.getExprEnumValues(expr),
      preferEnumCodes
    )
  }

  /** 
   * Stringifies a literal value based on its type (e.g., "text", "number").
   * Note: This function does not intelligently handle 'id' and 'id[]' types, and will output the raw id value.
   */
  stringifyLiteralValue(
    type: LiteralType,
    value: any,
    locale?: string,
    enumValues?: EnumValue[] | null,
    preferEnumCodes?: boolean
  ) {
    if (value == null) {
      return "None" // TODO localize
    }

    switch (type) {
      case "text":
        return value
      case "number":
        return "" + value
      case "enum":
        // Get enumValues
        var item = _.findWhere(enumValues!, { id: value })
        if (item) {
          if (preferEnumCodes && item.code) {
            return item.code
          }
          return ExprUtils.localizeString(item.name, locale)
        }
        return "???"
      case "enumset":
        return _.map(value, (val) => {
          item = _.findWhere(enumValues!, { id: val })
          if (item) {
            if (preferEnumCodes && item.code) {
              return item.code
            }
            return ExprUtils.localizeString(item.name, locale)
          }
          return "???"
        }).join(", ")

      case "text[]":
        // Parse if string
        if (_.isString(value)) {
          value = JSON.parse(value || "[]")
        }

        if (Array.isArray(value)) {
          return value.join(", ")
        }

        return "???"

      case "date":
        return moment(value, moment.ISO_8601).format("ll")

      case "datetime":
        return moment(value, moment.ISO_8601).format("lll")

      case "geometry":
        if (value.type === "Point") {
          return `${value.coordinates[1]}, ${value.coordinates[0]}`
        }
        return value.type
    }

    if (value === true) {
      return "True"
    }

    if (value === false) {
      return "False"
    }

    return `${value}`
  }

  /** Get all comparison ops (id and name) for a given left hand side type DEPRECATED
   * @deprecated
   */
  getComparisonOps(lhsType: any) {
    const ops = []
    switch (lhsType) {
      case "number":
        ops.push({ id: "=", name: "equals" })
        ops.push({ id: ">", name: "is greater than" })
        ops.push({ id: ">=", name: "is greater or equal to" })
        ops.push({ id: "<", name: "is less than" })
        ops.push({ id: "<=", name: "is less than or equal to" })
        break
      case "text":
        ops.push({ id: "= any", name: "is one of" })
        ops.push({ id: "=", name: "is" })
        ops.push({ id: "~*", name: "matches" })
        break
      case "date":
      case "datetime":
        ops.push({ id: "between", name: "between" })
        ops.push({ id: ">", name: "after" })
        ops.push({ id: "<", name: "before" })
        break
      case "enum":
        ops.push({ id: "= any", name: "is one of" })
        ops.push({ id: "=", name: "is" })
        break
      case "boolean":
        ops.push({ id: "= true", name: "is true" })
        ops.push({ id: "= false", name: "is false" })
        break
    }

    ops.push({ id: "is null", name: "has no value" })
    ops.push({ id: "is not null", name: "has a value" })

    return ops
  }

  /** Get the right hand side type for a comparison DEPRECATED
   * @deprecated
   */
  getComparisonRhsType(lhsType: any, op: any) {
    if (["= true", "= false", "is null", "is not null"].includes(op)) {
      return null
    }

    if (["= any"].includes(op)) {
      if (lhsType === "enum") {
        return "enum[]"
      } else if (lhsType === "text") {
        return "text[]"
      } else {
        throw new Error("Invalid lhs type for op = any")
      }
    }

    if (op === "between") {
      if (lhsType === "date") {
        return "daterange"
      }
      if (lhsType === "datetime") {
        return "datetimerange"
      } else {
        throw new Error("Invalid lhs type for op between")
      }
    }

    return lhsType
  }

  /** Get a list of fields that are referenced in a an expression
   * Useful to know which fields and joins are used. Includes joins as fields
   * Invisible second parameter is depth to prevent infinite recursion
   */
  getReferencedFields(expr: Expr, _depth?: number): FieldExpr[] {
    let column, table
    let cols: FieldExpr[] = []

    if (!expr) {
      return cols
    }

    const depth = _depth || 0
    if (depth > 100) {
      throw new Error("Infinite recursion")
    }

    switch (expr.type) {
      case "field":
        cols.push(expr)
        column = this.schema.getColumn(expr.table, expr.column)
        if (column?.expr) {
          cols = cols.concat(this.getReferencedFields(column.expr, depth + 1))
        }
        break
      case "op":
        for (let subexpr of expr.exprs) {
          cols = cols.concat(this.getReferencedFields(subexpr, depth + 1))
        }
        break
      case "case":
        for (let subcase of expr.cases) {
          cols = cols.concat(this.getReferencedFields(subcase.when, depth + 1))
          cols = cols.concat(this.getReferencedFields(subcase.then, depth + 1))
        }
        cols = cols.concat(this.getReferencedFields(expr.else, depth + 1))
        break
      case "scalar":
        for (let join of expr.joins) {
          cols.push({ type: "field", table: expr.table, column: join })
          column = this.schema.getColumn(expr.table, join)
          // Handle gracefully
          if (!column) {
            break
          }

          if (column.type === "join") {
            table = column.join!.toTable
          } else if (["id", "id[]"].includes(column.type)) {
            table = column.idTable
          } else {
            break
          }
        }

        cols = cols.concat(this.getReferencedFields(expr.expr, depth + 1))
        break
      case "subquery":
        cols = cols.concat(this.getReferencedFields(expr.select, depth + 1))
        cols = cols.concat(this.getReferencedFields(expr.where, depth + 1))
        for (let orderBy of expr.orderBys) {
          cols = cols.concat(this.getReferencedFields(orderBy.expr, depth + 1))
        }
        break

      case "score":
        cols = cols.concat(this.getReferencedFields(expr.input, depth + 1))
        for (const value of _.values(expr.scores) as Expr[]) {
          cols = cols.concat(this.getReferencedFields(value, depth + 1))
        }
        break

      case "build enumset":
        for (const value of _.values(expr.values) as Expr[]) {
          cols = cols.concat(this.getReferencedFields(value, depth + 1))
        }
        break

      case "extension":
        cols = cols.concat(getExprExtension(expr.extension).getReferencedFields(expr, this.schema))
        break
    }

    return _.uniq(cols, (col) => col.table + "/" + col.column)
  }

  /** Replace variables with literal values */
  inlineVariableValues(expr: Expr, variableValues: { [variableId: string]: Expr }): Expr {
    // Replace every part of an object, including array members
    const mapObject = function (obj: any, replacer: (input: any) => any) {
      if (!obj) {
        return obj
      }
      if (_.isArray(obj)) {
        return _.map(obj, replacer)
      }
      if (_.isObject(obj)) {
        return _.mapValues(obj, replacer)
      }
      return obj
    }

    var replacer = (part: any) => {
      // Subqueries cannot be inlined
      if (_.isObject(part) && part.type === "subquery") {
        throw new Error("Subqueries cannot be inlined")
      }

      part = mapObject(part, replacer)
      if (part && part.type === "variable") {
        // Find variable
        const variable = _.findWhere(this.schema.getVariables(), { id: part.variableId })
        if (!variable) {
          throw new Error(`Variable ${part.variableId} not found`)
        }
        return mapObject(variableValues[variable.id] || null, replacer)
      }

      return part
    }

    return mapObject(expr, replacer)
  }
}

// # Get a list of column ids of expression table that are referenced in a an expression
// # Useful to know which fields and joins are used. Does not follow joins, beyond including
// # the first join (which is a column in the start table).
// # Function does not require a schema, so schema can be null/undefined in constructor
// getImmediateReferencedColumns: (expr) ->
//   cols = []

//   if not expr
//     return cols

//   switch expr.type
//     when "field"
//       cols.push(expr.column)
//     when "op"
//       for subexpr in expr.exprs
//         cols = cols.concat(@getImmediateReferencedColumns(subexpr))
//     when "case"
//       for subcase in expr.cases
//         cols = cols.concat(@getImmediateReferencedColumns(subcase.when))
//         cols = cols.concat(@getImmediateReferencedColumns(subcase.then))
//       cols = cols.concat(@getImmediateReferencedColumns(expr.else))

//   return _.uniq(cols)

// Setup op items
// opItems are a list of ops for various types
var opItems: OpItem[] = []

// Which op items are aggregate (key = op, value = true)
let aggrOpItems: { [op: string]: true } = {}

// opItems grouped by op
var groupedOpItems: { [op: string]: OpItem[] } = {}

// Adds an op item (particular combination of operands types with an operator)
// exprTypes is a list of types for expressions. moreExprType is the type of further N expressions, if allowed
const addOpItem = (item: OpItem) => {
  opItems.push(_.defaults(item, { prefix: false, rhsLiteral: true, aggr: false, ordered: false }))
  if (item.aggr) {
    aggrOpItems[item.op] = true
  }

  const list = groupedOpItems[item.op] || []
  list.push(item)
  groupedOpItems[item.op] = list
}

function createOpItems() {
  // Create a dummy T function for localization to ensure that the strings
  // are localized when scanning for localizable strings.
  const T = (str: string) => str

  // TODO n?
  addOpItem({ op: "= any", name: T("is any of"), resultType: "boolean", exprTypes: ["text", "text[]"], category: "text" })
  addOpItem({ op: "= any", name: T("is any of"), resultType: "boolean", exprTypes: ["enum", "enumset"], category: "logic" })

  addOpItem({ op: "contains", name: T("includes all of"), resultType: "boolean", exprTypes: ["enumset", "enumset"], category: "logic" })
  addOpItem({ op: "intersects", name: T("includes any of"), resultType: "boolean", exprTypes: ["enumset", "enumset"], category: "logic" })

  addOpItem({ op: "contains", name: T("includes all of"), resultType: "boolean", exprTypes: ["text[]", "text[]"], category: "text" })
  addOpItem({ op: "intersects", name: T("includes any of"), resultType: "boolean", exprTypes: ["text[]", "text[]"], category: "text" })

  // Add relative dates
  const relativeDateOps = [
    ["thisyear", T("is this year")],
    ["lastyear", T("is last year")],
    ["thismonth", T("is this month")],
    ["lastmonth", T("is last month")],
    ["today", T("is today")],
    ["yesterday", T("is yesterday")],
    ["last24hours", T("is in last 24 hours")],
    ["last7days", T("is in last 7 days")],
    ["last30days", T("is in last 30 days")],
    ["last365days", T("is in last 365 days")],
    ["last3months", T("is in last 3 months")],
    ["last6months", T("is in last 6 months")],
    ["last12months", T("is in last 12 months")],
    ["future", T("is in the future")],
    ["notfuture", T("is not in the future")]
  ]
  for (let relativeDateOp of relativeDateOps) {
    addOpItem({ op: relativeDateOp[0], name: relativeDateOp[1], resultType: "boolean", exprTypes: ["date"], category: "dates" })
    addOpItem({ op: relativeDateOp[0], name: relativeDateOp[1], resultType: "boolean", exprTypes: ["datetime"], category: "dates" })
  }

  // Add in ranges
  addOpItem({ op: "between", name: T("is between"), resultType: "boolean", exprTypes: ["date", "date", "date"], category: "dates" })
  addOpItem({ op: "between", name: T("is between"), resultType: "boolean", exprTypes: ["datetime", "datetime", "datetime"], category: "dates" })

  addOpItem({ op: "=", name: T("is"), resultType: "boolean", exprTypes: ["number", "number"], category: "math" })
  addOpItem({ op: "=", name: T("is"), resultType: "boolean", exprTypes: ["text", "text"], category: "text" })
  addOpItem({ op: "=", name: T("is"), resultType: "boolean", exprTypes: ["enum", "enum"], category: "logic" })
  addOpItem({ op: "=", name: T("is"), resultType: "boolean", exprTypes: ["date", "date"], category: "dates" })
  addOpItem({ op: "=", name: T("is"), resultType: "boolean", exprTypes: ["datetime", "datetime"], category: "dates" })
  addOpItem({ op: "=", name: T("is"), resultType: "boolean", exprTypes: ["boolean", "boolean"], category: "logic" })

  addOpItem({ op: "<>", name: T("is not"), resultType: "boolean", exprTypes: ["text", "text"], category: "text" })
  addOpItem({ op: "<>", name: T("is not"), resultType: "boolean", exprTypes: ["enum", "enum"], category: "logic" })
  addOpItem({ op: "<>", name: T("is not"), resultType: "boolean", exprTypes: ["date", "date"], category: "dates" })
  addOpItem({ op: "<>", name: T("is not"), resultType: "boolean", exprTypes: ["datetime", "datetime"], category: "dates" })
  addOpItem({ op: "<>", name: T("is not"), resultType: "boolean", exprTypes: ["boolean", "boolean"], category: "logic" })

  addOpItem({ op: "<>", name: T("is not"), resultType: "boolean", exprTypes: ["number", "number"], category: "math" })
  addOpItem({ op: ">", name: T("is greater than"), resultType: "boolean", exprTypes: ["number", "number"], category: "math" })
  addOpItem({ op: "<", name: T("is less than"), resultType: "boolean", exprTypes: ["number", "number"], category: "math" })
  addOpItem({ op: ">=", name: T("is greater than or equal to"), resultType: "boolean", exprTypes: ["number", "number"], category: "math" })
  addOpItem({ op: "<=", name: T("is less than or equal to"), resultType: "boolean", exprTypes: ["number", "number"], category: "math" })

  for (const type1 of ["date", "datetime"] as LiteralType[]) {
    for (const type2 of ["date", "datetime"] as LiteralType[]) {
      addOpItem({ op: ">", name: T("is after"), resultType: "boolean", exprTypes: [type1, type2], category: "dates" })
      addOpItem({ op: "<", name: T("is before"), resultType: "boolean", exprTypes: [type1, type2], category: "dates" })
      addOpItem({ op: ">=", name: T("is after or same as"), resultType: "boolean", exprTypes: [type1, type2], category: "dates" })
      addOpItem({ op: "<=", name: T("is before or same as"), resultType: "boolean", exprTypes: [type1, type2], category: "dates" })
    }
  }

  addOpItem({ op: "between", name: T("is between"), resultType: "boolean", exprTypes: ["number", "number", "number"], category: "math" })

  addOpItem({
    op: "round",
    name: T("Round"),
    desc: T("Round a number to closest whole number"),
    resultType: "number",
    exprTypes: ["number"],
    prefix: true,
    category: "math"
  })
  addOpItem({
    op: "floor",
    name: T("Floor"),
    desc: T("Round a number down"),
    resultType: "number",
    exprTypes: ["number"],
    prefix: true,
    category: "math"
  })
  addOpItem({
    op: "ceiling",
    name: T("Ceiling"),
    desc: T("Round a number up"),
    resultType: "number",
    exprTypes: ["number"],
    prefix: true,
    category: "math"
  })
  addOpItem({
    op: "abs",
    name: T("Absolute value"),
    desc: T("Get the absolute value of a number"),
    resultType: "number",
    exprTypes: ["number"],
    prefix: true,
    category: "math"
  })
  addOpItem({
    op: "sqrt",
    name: T("Square root"),
    desc: T("Get the square root of a number"),
    resultType: "number",
    exprTypes: ["number"],
    prefix: true,
    category: "math"
  })
  addOpItem({
    op: "cbrt",
    name: T("Cube root"),
    desc: T("Get the cube root of a number"),
    resultType: "number",
    exprTypes: ["number"],
    prefix: true,
    category: "math"
  })
  addOpItem({
    op: "power",
    name: T("Power"),
    desc: T("Raise a number to an exponent"),
    resultType: "number",
    exprTypes: ["number", "number"],
    prefix: true,
    rhsLiteral: false,
    joiner: "to the power of",
    category: "math"
  })
  addOpItem({
    op: "log10",
    name: T("Logarithm"),
    desc: T("Get the base-10 logarithm of a number"),
    resultType: "number",
    exprTypes: ["number"],
    prefix: true,
    category: "math"
  })
  addOpItem({
    op: "ln",
    name: T("Natural logarithm"),
    desc: T("Get the natural logarithm (base e) of a number"),
    resultType: "number",
    exprTypes: ["number"],
    prefix: true,
    category: "math"
  })
  addOpItem({
    op: "latitude",
    name: T("Latitude of"),
    desc: T("Get latitude in degrees of a location"),
    resultType: "number",
    exprTypes: ["geometry"],
    prefix: true,
    category: "location"
  })
  addOpItem({
    op: "longitude",
    name: T("Longitude of"),
    desc: T("Get longitude in degrees of a location"),
    resultType: "number",
    exprTypes: ["geometry"],
    prefix: true,
    category: "location"
  })
  addOpItem({
    op: "altitude",
    name: T("Altitude of"),
    desc: T("Get altitude of a location in meters"),
    resultType: "number",
    exprTypes: ["geometry"],
    prefix: true,
    category: "location"
  })
  addOpItem({
    op: "distance",
    name: T("Distance between"),
    desc: T("Get distance in meters between two locations"),
    resultType: "number",
    exprTypes: ["geometry", "geometry"],
    prefix: true,
    rhsLiteral: false,
    joiner: "and",
    category: "location"
  })

  // And/or is a list of booleans
  addOpItem({ op: "and", name: T("and"), resultType: "boolean", exprTypes: [], moreExprType: "boolean", category: "logic" })
  addOpItem({ op: "or", name: T("or"), resultType: "boolean", exprTypes: [], moreExprType: "boolean", category: "logic" })

  for (const op of ["+", "*"]) {
    addOpItem({ op, name: op, resultType: "number", exprTypes: [], moreExprType: "number", category: "math" })
  }

  addOpItem({ op: "-", name: "-", resultType: "number", exprTypes: ["number", "number"], category: "math" })
  addOpItem({ op: "/", name: "/", resultType: "number", exprTypes: ["number", "number"], category: "math" })

  for (const type1 of ["date", "datetime"] as LiteralType[]) {
    for (const type2 of ["date", "datetime"] as LiteralType[]) {
      // Date subtraction
      addOpItem({
        op: "days difference",
        name: T("Days between"),
        desc: T("Get the number of days between two dates"),
        resultType: "number",
        exprTypes: [type1, type2],
        prefix: true,
        rhsLiteral: false,
        joiner: "and",
        category: "dates"
      })

      addOpItem({
        op: "months difference",
        name: T("Months between"),
        desc: T("Get the number of months between two dates"),
        resultType: "number",
        exprTypes: [type1, type2],
        prefix: true,
        rhsLiteral: false,
        joiner: "and",
        category: "dates"
      })

      addOpItem({
        op: "years difference",
        name: T("Years between"),
        desc: T("Get the number of years between two dates"),
        resultType: "number",
        exprTypes: [type1, type2],
        prefix: true,
        rhsLiteral: false,
        joiner: "and",
        category: "dates"
      })
    }
  }

  for (const type1 of ["date", "datetime"] as LiteralType[]) {
    addOpItem({
      op: "days since",
      name: T("Days since"),
      desc: T("Get number of days from a date to the present"),
      resultType: "number",
      exprTypes: [type1],
      prefix: true,
      rhsLiteral: false,
      category: "dates"
    })


    addOpItem({
      op: "month",
      name: T("Month"),
      desc: T("Month of year"),
      resultType: "enum",
      exprTypes: [type1],
      prefix: true,
      rhsLiteral: false,
      category: "dates"
    })

    addOpItem({
      op: "yearmonth",
      name: T("Year and Month"),
      desc: T("Date of start of month"),
      resultType: "date",
      exprTypes: [type1],
      prefix: true,
      rhsLiteral: false,
      category: "dates"
    })

    addOpItem({
      op: "year",
      name: T("Year"),
      desc: T("Date of start of year"),
      resultType: "date",
      exprTypes: [type1],
      prefix: true,
      rhsLiteral: false,
      category: "dates"
    })

    addOpItem({
      op: "weekofmonth",
      name: T("Week of month"),
      desc: T("Week within the month"),
      resultType: "enum",
      exprTypes: [type1],
      prefix: true,
      rhsLiteral: false,
      category: "dates"
    })

    addOpItem({
      op: "dayofmonth",
      name: T("Day of month"),
      desc: T("Day within the month (1-31)"),
      resultType: "enum",
      exprTypes: [type1],
      prefix: true,
      rhsLiteral: false,
      category: "dates"
    })

    addOpItem({
      op: "yearquarter",
      name: T("Year/Quarter"),
      desc: T("Year and quarter of a date"),
      resultType: "text",
      exprTypes: [type1],
      prefix: true,
      rhsLiteral: false,
      category: "dates"
    })

    addOpItem({
      op: "yearweek",
      name: T("Year/Week"),
      desc: T("Year and week of a date"),
      resultType: "text",
      exprTypes: [type1],
      prefix: true,
      rhsLiteral: false,
      category: "dates"
    })

    addOpItem({
      op: "weekofyear",
      name: T("Week"),
      desc: T("Week of a date"),
      resultType: "enum",
      exprTypes: [type1],
      prefix: true,
      rhsLiteral: false,
      category: "dates"
    })

    addOpItem({
      op: "dayofweek",
      name: T("Day of week"),
      desc: T("Get the day of week (Sunday, Monday, etc)"),
      resultType: "enum",
      exprTypes: [type1],
      prefix: true,
      rhsLiteral: false,
      category: "dates"
    })

    addOpItem({
      op: "year number",
      name: T("Year Number"),
      desc: T("Get the year as a number (e.g. 2015)"),
      resultType: "number",
      exprTypes: [type1],
      prefix: true,
      category: "dates"
    })

    // Fiscal year operators
    addOpItem({
      op: "fiscalyear",
      name: T("Fiscal Year of"),
      desc: T("Get fiscal year (e.g. FY2024) based on fiscal year start month"),
      resultType: "text",
      exprTypes: [type1, "enum"],
      prefix: true,
      rhsLiteral: true,
      rhsForceLiteral: true,
      joiner: "with fiscal year starting in month",
      exprEnumValues: [null, monthEnumValues],
      rhsSticky: true,
      category: "dates"
    })

    addOpItem({
      op: "fiscalquarter",
      name: T("Fiscal Quarter of"),
      desc: T("Get fiscal quarter (e.g. FY2024/Q1) based on fiscal year start month"),
      resultType: "text",
      exprTypes: [type1, "enum"],
      prefix: true,
      rhsLiteral: true,
      rhsForceLiteral: true,
      joiner: "with fiscal year starting in month",
      exprEnumValues: [null, monthEnumValues],
      rhsSticky: true,
      category: "dates"
    })
  }


  addOpItem({
    op: "within",
    name: T("is within"),
    resultType: "boolean",
    exprTypes: ["id", "id"],
    lhsCond: (lhsExpr, exprUtils) => {
      const lhsIdTable = exprUtils.getExprIdTable(lhsExpr)
      if (lhsIdTable) {
        return (
          exprUtils.schema.getTable(lhsIdTable)?.ancestry != null ||
          exprUtils.schema.getTable(lhsIdTable)?.ancestryTable != null
        )
      }
      return false
    },
    category: "logic"
  })

  addOpItem({ op: "=", name: T("is"), resultType: "boolean", exprTypes: ["id", "id"], category: "logic" })
  addOpItem({ op: "<>", name: T("is not"), resultType: "boolean", exprTypes: ["id", "id"], category: "logic" })
  addOpItem({ op: "= any", name: T("is any of"), resultType: "boolean", exprTypes: ["id", "id[]"], category: "logic" })

  addOpItem({
    op: "sum",
    name: T("Total"),
    desc: T("Add all values together"),
    resultType: "number",
    exprTypes: ["number"],
    prefix: true,
    aggr: true,
    category: "math"
  })
  addOpItem({
    op: "avg",
    name: T("Average"),
    desc: T("Average all values together"),
    resultType: "number",
    exprTypes: ["number"],
    prefix: true,
    aggr: true,
    category: "math"
  })

  for (const type of ["number", "date", "datetime"] as LiteralType[]) {
    addOpItem({
      op: "min",
      name: T("Minimum"),
      desc: T("Get smallest value"),
      resultType: type,
      exprTypes: [type],
      prefix: true,
      aggr: true,
      category: type === "number" ? "math" : "dates"
    })
    addOpItem({
      op: "min where",
      name: T("Minimum where"),
      desc: T("Get smallest value that matches a condition"),
      resultType: type,
      exprTypes: [type, "boolean"],
      prefix: true,
      prefixLabel: T("Minimum"),
      aggr: true,
      rhsLiteral: false,
      joiner: "of",
      rhsPlaceholder: T("All"),
      category: type === "number" ? "math" : "dates"
    })

    addOpItem({
      op: "max",
      name: T("Maximum"),
      desc: T("Get largest value"),
      resultType: type,
      exprTypes: [type],
      prefix: true,
      aggr: true,
      category: type === "number" ? "math" : "dates"
    })
    addOpItem({
      op: "max where",
      name: T("Maximum where"),
      desc: T("Get largest value that matches a condition"),
      resultType: type,
      exprTypes: [type, "boolean"],
      prefix: true,
      prefixLabel: T("Maximum"),
      aggr: true,
      rhsLiteral: false,
      joiner: "of",
      rhsPlaceholder: T("All"),
      category: type === "number" ? "math" : "dates"
    })
  }

  addOpItem({
    op: "percent where",
    name: T("Percent where"),
    desc: T("Get percent of items that match a condition"),
    resultType: "number",
    exprTypes: ["boolean", "boolean"],
    prefix: true,
    aggr: true,
    rhsLiteral: false,
    joiner: "of",
    rhsPlaceholder: T("All"),
    category: "math"
  })
  addOpItem({
    op: "count where",
    name: T("Number where"),
    desc: T("Get number of items that match a condition"),
    resultType: "number",
    exprTypes: ["boolean"],
    prefix: true,
    aggr: true,
    category: "math"
  })
  addOpItem({
    op: "sum where",
    name: T("Total where"),
    desc: T("Add together only values that match a condition"),
    resultType: "number",
    exprTypes: ["number", "boolean"],
    prefix: true,
    prefixLabel: T("Total"),
    aggr: true,
    rhsLiteral: false,
    joiner: "where",
    rhsPlaceholder: T("All"),
    category: "math"
  })

  // Ordered aggregations. Each takes an optional ordering expr that cannot be null.
  // If ordering is not specified, the table ordering is used.
  for (const type of [
    "text",
    "number",
    "enum",
    "enumset",
    "boolean",
    "date",
    "datetime",
    "geometry",
    "id",
    "image",
    "imagelist",
    "id[]",
    "text[]"
  ] as LiteralType[]) {
    const category = "logic"
    
    addOpItem({
      op: "last",
      name: T("Latest"),
      desc: T("Get latest value when there are multiple"),
      resultType: type,
      exprTypes: [type],
      prefix: true,
      aggr: true,
      ordered: true,
      category
    })
    addOpItem({
      op: "last where",
      name: T("Latest where"),
      desc: T("Get latest value that matches a condition"),
      resultType: type,
      exprTypes: [type, "boolean"],
      prefix: true,
      prefixLabel: T("Latest"),
      aggr: true,
      rhsLiteral: false,
      joiner: "where",
      rhsPlaceholder: T("All"),
      ordered: true,
      category
    })
    addOpItem({
      op: "previous",
      name: T("Previous"),
      desc: T("Get 2nd latest value when there are multiple"),
      resultType: type,
      exprTypes: [type],
      prefix: true,
      aggr: true,
      ordered: true,
      category
    })
    addOpItem({
      op: "first",
      name: T("First"),
      desc: T("Get first value when there are multiple"),
      resultType: type,
      exprTypes: [type],
      prefix: true,
      aggr: true,
      ordered: true,
      category
    })
    addOpItem({
      op: "first where",
      name: T("First where"),
      desc: T("Get first value that matches a condition"),
      resultType: type,
      exprTypes: [type, "boolean"],
      prefix: true,
      prefixLabel: T("First"),
      aggr: true,
      rhsLiteral: false,
      joiner: "where",
      rhsPlaceholder: T("All"),
      ordered: true,
      category
    })
  }

  addOpItem({
    op: "within any",
    name: T("is within any of"),
    resultType: "boolean",
    exprTypes: ["id", "id[]"],
    lhsCond: (lhsExpr, exprUtils) => {
      const lhsIdTable = exprUtils.getExprIdTable(lhsExpr)
      if (lhsIdTable) {
        return (
          exprUtils.schema.getTable(lhsIdTable)?.ancestry != null ||
          exprUtils.schema.getTable(lhsIdTable)?.ancestryTable != null
        )
      }
      return false
    },
    category: "logic"
  })

  addOpItem({
    op: "array_agg",
    name: T("Make list of"),
    desc: T("Aggregates results into a list"),
    resultType: "text[]",
    exprTypes: ["text"],
    prefix: true,
    aggr: true,
    category: "text"
  })

  addOpItem({
    op: "concat",
    name: T("Concatenate"),
    desc: T("Combine several text strings together"),
    resultType: "text",
    exprTypes: ["text"],
    moreExprType: "text",
    prefix: true,
    joiner: " with ",
    category: "text"
  })

  addOpItem({ op: "contains", name: T("includes all of"), resultType: "boolean", exprTypes: ["id[]", "id[]"], category: "logic" })
  addOpItem({ op: "intersects", name: T("includes any of"), resultType: "boolean", exprTypes: ["id[]", "id[]"], category: "logic" })
  addOpItem({ op: "includes", name: T("includes"), resultType: "boolean", exprTypes: ["id[]", "id"], category: "logic" })

  addOpItem({
    op: "count",
    name: T("Total Number"),
    desc: T("Get total number of items"),
    resultType: "number",
    exprTypes: [],
    prefix: true,
    aggr: true,
    category: "math"
  })
  addOpItem({
    op: "percent",
    name: T("Percent of Total"),
    desc: T("Percent of all items"),
    resultType: "number",
    exprTypes: [],
    prefix: true,
    aggr: true,
    category: "math"
  })

  addOpItem({ op: "~*", name: T("matches regular expression case-insensitive"), resultType: "boolean", exprTypes: ["text", "text"], category: "text" })
  addOpItem({ op: "~", name: T("matches regular expression exact-case"), resultType: "boolean", exprTypes: ["text", "text"], category: "text" })
  addOpItem({
    op: "not",
    name: T("Not"),
    desc: T("Opposite of a value"),
    resultType: "boolean",
    exprTypes: ["boolean"],
    prefix: true,
    category: "logic"
  })
  for (const type of [
    "text",
    "number",
    "enum",
    "enumset",
    "boolean",
    "date",
    "datetime",
    "geometry",
    "image",
    "imagelist",
    "id",
    "id[]",
    "json",
    "dataurl",
    "file",
    "filelist"
  ] as LiteralType[]) {
    const category = type === "geometry" ? "location" :
                     (type === "date" || type === "datetime") ? "dates" :
                     type === "text" ? "text" :
                     type === "number" ? "math" : "logic"
    addOpItem({ op: "is null", name: T("is blank"), resultType: "boolean", exprTypes: [type], category })
    addOpItem({ op: "is not null", name: T("is not blank"), resultType: "boolean", exprTypes: [type], category })
  }

  for (const type of ["id", "text", "date", "enum"] as LiteralType[]) {
    const category = type === "date" ? "dates" :
                     type === "text" ? "text" :
                     type === "number" ? "math" : "logic"
    addOpItem({
      op: "count distinct",
      name: T("Number of unique"),
      desc: T("Count number of unique values"),
      resultType: "number",
      exprTypes: [type],
      prefix: true,
      aggr: true,
      category: "math"
    })
  }

  addOpItem({
    op: "length",
    name: T("Number of values in"),
    desc: T("Advanced: number of values selected in a multi-choice field"),
    resultType: "number",
    exprTypes: ["enumset"],
    prefix: true,
    category: "math"
  })
  addOpItem({
    op: "length",
    name: T("Number of values in"),
    desc: T("Advanced: number of images present"),
    resultType: "number",
    exprTypes: ["imagelist"],
    prefix: true,
    category: "math"
  })
  addOpItem({
    op: "length",
    name: T("Number of values in"),
    desc: T("Advanced: number of items present in a text list"),
    resultType: "number",
    exprTypes: ["text[]"],
    prefix: true,
    category: "math"
  })

  addOpItem({
    op: "line length",
    name: T("Length of line"),
    desc: T("Length of a line shape in meters"),
    resultType: "number",
    exprTypes: ["geometry"],
    prefix: true,
    category: "location"
  })

  addOpItem({
    op: "polygon area",
    name: T("Area of a polygon"),
    desc: T("Area of a polygon shape in meters squared"),
    resultType: "number",
    exprTypes: ["geometry"],
    prefix: true,
    category: "location"
  })

  addOpItem({
    op: "is latest",
    name: T("Is latest for each"),
    desc: T("Only include latest item for each of something"),
    resultType: "boolean",
    exprTypes: ["id", "boolean"],
    prefix: true,
    ordered: true,
    aggr: false,
    rhsLiteral: false,
    joiner: "where",
    rhsPlaceholder: T("All"),
    category: "logic"
  })

  addOpItem({
    op: "current date",
    name: T("Today"),
    desc: T("Advanced: current date. Do not use in comparisons"),
    resultType: "date",
    exprTypes: [],
    prefix: true,
    category: "dates"
  })
  addOpItem({
    op: "current datetime",
    name: T("Now"),
    desc: T("Advanced: current datetime. Do not use in comparisons"),
    resultType: "datetime",
    exprTypes: [],
    prefix: true,
    category: "dates"
  })

  addOpItem({
    op: "to text",
    name: T("Convert to text"),
    desc: T("Advanced: convert a choice, text list, or number type to a text value"),
    resultType: "text",
    exprTypes: ["enum"],
    prefix: true,
    category: "text"
  })
  addOpItem({
    op: "to text",
    name: T("Convert to text"),
    desc: T("Advanced: convert a choice, text list, or number type to a text value"),
    resultType: "text",
    exprTypes: ["number"],
    prefix: true,
    category: "text"
  })
  addOpItem({
    op: "to text",
    name: T("Convert to text"),
    desc: T("Advanced: convert a choice, text list, or number type to a text value"),
    resultType: "text",
    exprTypes: ["text[]"],
    prefix: true,
    category: "text"
  })

  addOpItem({
    op: "to date",
    name: T("Convert to date"),
    desc: T("Convert a datetime to a date"),
    resultType: "date",
    exprTypes: ["datetime"],
    prefix: true,
    category: "dates"
  })

  addOpItem({
    op: "to number",
    name: T("Convert to number"),
    desc: T("Convert a text value to a number or null if not valid number"),
    resultType: "number",
    exprTypes: ["text"],
    prefix: true,
    category: "math"
  })

  addOpItem({
    op: "least",
    name: T("Least of"),
    desc: T("Takes the smallest of several numbers"),
    resultType: "number",
    exprTypes: ["number", "number"],
    moreExprType: "number",
    prefix: true,
    joiner: ", ",
    category: "math"
  })
  addOpItem({
    op: "greatest",
    name: T("Greatest of"),
    desc: T("Takes the largest of several numbers"),
    resultType: "number",
    exprTypes: ["number", "number"],
    moreExprType: "number",
    prefix: true,
    joiner: ", ",
    category: "math"
  })

  // Add to opItems array where other geometry operations are defined
  addOpItem({
    op: "centroid",
    name: T("Centroid"),
    desc: T("Get the center point of a geometry"),
    resultType: "geometry",
    exprTypes: ["geometry"],
    prefix: true,
    rhsLiteral: false,
    category: "location"
  })

  // Merge two geometries into one
  addOpItem({
    op: "geometry union",
    name: T("Merge shapes"),
    desc: T("Advanced: Combine two shapes into a single shape"),
    resultType: "geometry",
    exprTypes: ["geometry", "geometry"],
    prefix: true,
    rhsLiteral: false,
    joiner: "and",
    category: "location"
  })

  // Aggregate union of geometries
  addOpItem({
    op: "geometry union agg",
    name: T("Merge all shapes"),
    desc: T("Advanced: Combine multiple shapes into a single shape. Warning: this can be very slow for large numbers of shapes!"),
    resultType: "geometry",
    exprTypes: ["geometry"],
    prefix: true,
    aggr: true,
    category: "location"
  })

  // Intersection of two geometries
  addOpItem({
    op: "geometry intersect",
    name: T("Overlap between shapes"),
    desc: T("Advanced: Get the overlapping area between two shapes"),
    resultType: "geometry",
    exprTypes: ["geometry", "geometry"],
    prefix: true,
    rhsLiteral: false,
    joiner: "and",
    category: "location"
  })

  // Buffer geometry by distance
  addOpItem({
    op: "geometry buffer",
    name: T("Expand shape"),
    desc: T("Advanced: Expand a shape outward by a specified distance in meters"),
    resultType: "geometry",
    exprTypes: ["geometry", "number"],
    prefix: true,
    rhsLiteral: false,
    joiner: "by distance of (meters)",
    category: "location"
  })
}

createOpItems()