class SQLBuilder
  {
    constructor(dbFlavour)
    {
      this.sql='';
      this.dbFlavour=dbFlavour;
    }
    select(columns)
    {
      this.sql+=' ##STATEMENT##SELECT ';
      let colSQL='';
      if(columns!='*')
        {
          columns.forEach(col=>{
            colSQL+=` ##COLUMN##${col} ,`;
          });

          this.sql+=colSQL.substring(0,colSQL.lastIndexOf(','));
        }
      else
        {
          this.sql+=` ##COLUMN##${columns} `;
        }

      return this;
    }

    fromTable(table)
    {
      this.sql+=` ##GRAMMAR##FROM ##TABLE##${table.name} ##GRAMMAR##AS ##TABLE_ALIAS##${table.alias} `;
      return this;
    }

    fromInnerQuery(query)
    {
      let innerQuery=query.query.toStringOmitSemiColon();
      this.sql+=` ##GRAMMAR##FROM ##INNERQUERY##(${innerQuery}) ${query.alias} `;
      return this;
    }

    join(table)
    {
      this.sql+=` ##GRAMMAR##JOIN ##TABLE##${table.name} ##GRAMMAR##AS ##TABLE_ALIAS##${table.alias} `;
      return this;
    }

    on(source,destination)
    {
      this.sql+=` ##GRAMMAR##ON ##JOIN_SOURCE_OPERATOR##${source} ##JOIN_OPERATOR##= ${destination} `;
      return this;
    }

    where(operand,operator,value)
    {
      let whereSQL='';
      let operatorValue=this._getOperatorInSQL(operator.trim());
      if(value==='?')
        {
          whereSQL+=` ##GRAMMAR##WHERE ##OPERAND##${operand} ##OPERATOR##${operatorValue} ##VALUE##${value} `;
        }
      else
        {
          let val=this._getFormatForValue(value);

          whereSQL+=` ##GRAMMAR##WHERE ##OPERAND##${operand} ##OPERATOR##${operatorValue} ##VALUE##${val} `;
        }

      this.sql+=whereSQL;
      return this;
    }

    groupBy(columns)
    {
       let colSQL=' ##GRAMMAR##GROUP BY ';
      columns.forEach(col=>{
            colSQL+=` ##COLUMN##${col} ,`;
          });
      this.sql+=colSQL.substring(0,colSQL.lastIndexOf(','));
      return this;
    }

    orderBy(columns)
    {
       let colSQL=' ##GRAMMAR##ORDER BY ';
      columns.forEach(col=>{
            colSQL+=` ##COLUMN##${col} ,`;
          });
      this.sql+=colSQL.substring(0,colSQL.lastIndexOf(','));
      return this;
    }

    parameters(params)
    {
      let count=0;
      let valueTokenizedStatements=this.sql.split('?');
      this.sql='';
      valueTokenizedStatements.forEach((v)=>{
        this.sql+=v+this._getFormatForValue(params[count]);
        count++;
      });

      if(params.length!=(count-1))
        {
          this.sql+=this._getFormatForValue(params[params.length-1]);
        }
      return this;
    }

    _getOperatorInSQL(operator){
      let op=' = ';
      switch(operator)
        {
          case 'GT':
            op=' > ';
            break;
          case 'GE':
            op=' >= ';
            break;
          case 'LE':
            op=' <= ';
            break;
          case 'LT':
            op=' < ';
            break;
          case 'EQ':
            op=' = ';
            break;
          case 'NE':
            op=' <> ';
            break;
          case 'IN':
            op=' IN ';
            break;
          case'NI':
            op=' NOT IN ';
            break;
        }
      return op;
    }


    _getFormatForValue(value)
    {
      let val='';
      switch(typeof value)
            {
               case "string":
                val=`'${value}'`;
                  break;
              case "number":
                val=`${value}`;
                break;
              case "boolean":
                val=(value)?'TRUE':'FALSE';
                break;
              case "object":
                if(Array.isArray(value))
                  {
                    let arrValues=' ( ';
                    value.forEach(ele=>{
                      arrValues+=' '+this._getFormatForValue(ele)+', ';
                    });
                    arrValues=arrValues.substring(0,arrValues.lastIndexOf(','));
                    arrValues+=' ) ';
                    val+=arrValues;
                  }
                else
                  {
                    val=`'${value.toString()}'`;
                  }

                break;
              default:
                val='';
            }
      return val;
    }


    toString()
    {
      return this.sql.replace(/(##\w+##)/gm,'')+';';
    }

    toStringOmitSemiColon()
    {
        return this.sql.replace(/(##\w+##)/gm,'');
    }

  }

const innerquery=new SQLBuilder().select(['emp.name','emp.title']).fromTable({name:'employees',alias:'emp'}).join({name:'salary',alias:'s'}).on('emp.id','s.empid').where('emp.age','EQ','?').groupBy(['emp.name','emp.age']).orderBy(['emp.name']).parameters([40]);
const query=new SQLBuilder().select(['emp.name','emp.title']).fromInnerQuery({query:innerquery,alias:'inn'}).join({name:'salary',alias:'s'}).on('inn.id','s.empid').where('emp.age','EQ','?').groupBy(['emp.name','emp.age']).orderBy(['emp.name']).parameters([40]).toString();
console.log(query);