text: const BaseBuilder = require('./BaseBuilder')
  
  /**
   * FunctionBuilder is used to take a part of a CQN object as an input and to build an object representing a function
   * with SQL string and values.
   *
   */
  class FunctionBuilder extends BaseBuilder {
    get ReferenceBuilder () {
      const ReferenceBuilder = require('./ReferenceBuilder')
      Object.defineProperty(this, 'ReferenceBuilder', { value: ReferenceBuilder })
      return ReferenceBuilder
    }
  
    get SelectBuilder () {
      const SelectBuilder = require('./SelectBuilder')
      Object.defineProperty(this, 'SelectBuilder', { value: SelectBuilder })
      return SelectBuilder
    }
  
    build () {
      this._outputObj = {
        sql: [],
        values: []
      }
  
      this._handleFunction()
  
      this._outputObj.sql = this._outputObj.sql.join(' ')
      return this._outputObj
    }
  
    _functionName (element) {
      return (element.ref && element.ref[0]) || element.func
    }
  
    _functionArgs (element) {
      return (element.ref && element.ref[1].args) || element.args
    }
  
    _handleFunction () {
      const functionName = this._functionName(this._obj)
  
      let args = this._functionArgs(this._obj)
      if (functionName.toLowerCase().includes('contains')) {
        this._handleContains(args)
        return
      }
  
      if (functionName.toLowerCase() === 'concat') {
        this._handleConcat(args)
        return
      }
      this._outputObj.sql.push(functionName, '(')
      if (typeof args === 'string') {
        this._outputObj.sql.push(args, ')')
      } else {
        this._addFunctionArgs(args)
        this._outputObj.sql.push(')')
      }
    }
  
    _handleConcat (args) {
      const res = []
      for (const arg of args) {
        if (arg.ref) {
          const { sql, values } = new this.ReferenceBuilder(arg, this._options, this._csn).build()
          res.push(sql)
          this._outputObj.values.push(...values)
        } else if (arg.val) {
          this._outputObj.values.push(arg.val)
          res.push(this._options.placeholder)
        } else if (typeof arg === 'string') {
          res.push(arg)
        } else if (arg.func) {
          const { sql, values } = new FunctionBuilder(arg, this._options, this._csn).build()
          res.push(sql)
          this._outputObj.values.push(...values)
        }
      }
      this._outputObj.sql.push('(')
      this._outputObj.sql.push(res.join(' || '))
      this._outputObj.sql.push(')')
    }
  
    _handleContains (args) {
      const contains = this._obj.func
        ? !this._obj.func.toLowerCase().includes('not')
        : !this._obj.ref[0].toLowerCase().includes('not')
      const columns = this._columns(args)
      const params = this._obj.func ? args.slice(1) : this._obj.ref[1].args.slice(1)
  
      for (const param of params) {
        if (param === 'or' || param === 'and' || param === 'not') {
          this._outputObj.sql.push(param)
        } else {
          const searchText = param.val.toLowerCase()
          this._outputObj.sql.push('(')
          this._createLikeComparison(contains, columns, searchText)
          this._outputObj.sql.push(')')
        }
      }
    }
  
    _createLikeComparison (contains, columns, searchText) {
      const length = columns.length
      for (let i = 0; i < length; i++) {
        if (columns[i].ref) {
          const { sql } = new this.ReferenceBuilder(columns[i], this._options, this._csn).build()
          this._createLikeComparisonForColumn(contains, sql, searchText)
          if (i !== columns.length - 1 && columns[i + 1] !== ')') {
            this._outputObj.sql.push(contains ? 'OR' : 'AND')
          }
        }
      }
    }
  
    _createLikeComparisonForColumn (contains, sql, searchText) {
      if (!contains) {
        this._outputObj.sql.push('(')
        this._outputObj.sql.push(sql, 'IS NULL', 'OR')
      }
      this._outputObj.sql.push('lower', '(', sql, ')', contains ? 'LIKE' : 'NOT LIKE', '?', 'ESCAPE', "'^'")
      this._outputObj.values.push(`%${searchText.replace(/(\^|_|%)/g, '^$1')}%`)
      if (!contains) {
        this._outputObj.sql.push(')')
      }
    }
  
    _columns (args) {
      return args[0].xpr || args[0].list || [args[0]]
    }
  
    _addFunctionArgs (args) {
      const res = []
      for (const arg of args) {
        if (arg.ref) {
          const { sql, values } = new this.ReferenceBuilder(arg, this._options, this._csn).build()
          res.push(sql)
          this._outputObj.values.push(...values)
        } else if (arg.func) {
          const { sql, values } = new FunctionBuilder(arg, this._options, this._csn).build()
          res.push(sql)
          this._outputObj.values.push(...values)
        } else if (arg.SELECT) {
          const { sql, values } = new this.SelectBuilder(arg, this._options, this._csn).build(true)
          res.push(sql)
          this._outputObj.values.push(...values)
        } else if (arg.val) {
          this._outputObj.values.push(arg.val)
          res.push(this._options.placeholder)
        } else if (typeof arg === 'string') {
          res.push(arg)
        }
      }
      this._outputObj.sql.push(res.join(', '))
    }
  }
  
  module.exports = FunctionBuilder
  