//var re = /(?:SELECT\s+?([a-z][a-z0-9_,.()* ]*?)\s+){1}(?:FROM\s+([a-z_][a-z_$0-9@]+)\s+?){1}(?:JOIN\s+([a-z_][a-z_$0-9@]*?)\s+ON\s+([a-z][a-z0-9_,.]*?)\s+=\s+([a-z][a-z0-9_,.]*)\s+?)*(?:GROUP\sBY\s+?([a-z][a-z0-9_,.()*]+)\s*)?(?:ORDER\sBY\s+?([a-z][a-z0-9_,.]*)\s*?)?/i;

​

var query = 'SELECT bills.vendor.name, bills.person.first_name FROM bills JOIN persons ON bills.person=person.id JOIN vendors ON bills.vendor=vendors.id GROUP BY count(*) ORDER BY bill.id';

function updateQuery(query) {

  console.log(query.match(re))

  document.getElementById('result').value = new BuildMongoQuery(query)

}

​

document.getElementById('source').value = query

document.getElementById('result').value = new BuildMongoQuery(query);

​

function BuildMongoQuery(query) {

  this.query = query;

  this.tokens = [];

  this.mongoQuery = [];

  this.selects = {};

  this.from = '';

  var tokens = [

    'SELECT',

    'FROM',

    'JOIN',

    'WHERE',

    'GROUP BY',

    'ORDER BY'

  ]

  this.build = function() {

    if(!this.query)

      return new Error('query is required');

    this.parts = [];

    this.parseSql();

    //return this.addJoins().addSelect(this.parts[1]).From(this.parts[2]).addGroupBy(this.parts[3]).toString();

  }

  this.parseSql = function() {

    var re = /[A-Za-z_.]+\(.*?\)+|\(.*?\)+|"(?:[^"]|\"|"")*"+|'[^'](?:|\'|'')*'+|`(?:[^`]|``)*`+|[^ ,]+|,/ig

    this.tokens = this.query.match(re);

    console.log("Tokens", this.tokens)

    this.getSelectFromQuery();

    var afterFromIndex = this.tokens.indexOf("FROM") +1;

    var untilFromLength = (this.tokens.indexOf("JOIN") != -1 ? this.tokens.indexOf("JOIN") : this.tokens.indexOf("WHERE")) - afterFromIndex;

    this.addFrom(this.tokens.splice(afterFromIndex, untilFromLength).pop())

    var afterJoinIndex = this.tokens.indexOf("JOIN")+1;

    var untilJoinLength = (this.tokens.indexOf("WHERE") != -1 ? this.tokens.indexOf("WHERE") : this.tokens.indexOf("GROUP")) - afterJoinIndex;

    this.addJoins(this.tokens.splice(afterJoinIndex, untilJoinLength))

  }

​

  this.addGroupBy = function(group_by) {

      // TODO: $group statement build

    console.log("Group by: ", group_by)

    return this;

  }

  this.getSelectFromQuery = function() {

    var afterSelectIndex = this.tokens.indexOf("SELECT") +1;

    var untilSelectsLength = this.tokens.indexOf("FROM") - afterSelectIndex;

    this.addSelect(this.tokens.splice(afterSelectIndex, untilSelectsLength).filter(function(select){ return select != ","}));

  }

​

  this.addSelect = function(selected_attributes) {

    this.selects = selected_attributes;

      // TODO: $project statement build

      console.log("Selected attributes: ", selected_attributes)

      return this;

  }

​

  this.addFrom = function(from_table) {

      console.log("From table: ", from_table)

      this.from = from_table;

      return this;

  }

  this.addWhere = function(where_query) {

    this.selects = selected_attributes;

      // TODO: $project statement build

      console.log("Selected attributes: ", selected_attributes)

      return this;

  }

​

  this.addJoins = function(joins) {

      console.log("With joins: ", joins);

      joins = joins.reduce(function(acc, item) {

        if(item == "JOIN") {

          acc.push([])

        }

        else {

          if(item == "ON")

            return acc;

            var lastIdx = acc.length > 0 ? acc.length -1 : 0;

            acc[lastIdx] = acc[lastIdx] || [];

            acc[lastIdx].push(item)

        }

       return acc;

      },[]).map((join)=>{

        table_name = join[0];

        attrs = join[1].split("=")

        attrs = attrs.reduce((acc, attr) => {

​

          if(this.from && attr.indexOf(this.from) != -1)

            acc.localField= attr.split(".").pop();

          else

            acc.foreignField= attr.split(".").pop();

          return acc;

        },{})

        this.mongoQuery.push({

            $lookup: {

                "from" : table_name,

                "localField" : attrs.localField,

                "foreignField" : attrs.foreignField,

                "as" : attrs.localField

            }

        })

        this.mongoQuery.push( {

              $unwind: "$"+attrs.localField

            });

      })

      return this;

  }

  function getLastIndexBeforeNextToken(currentIndex) {

  }

  this.toString = function() {

    return "db.getCollection('"+this.from+"').aggregate(\n"+JSON.stringify(this.mongoQuery,null, 2)+"\n);"

  }

  return this.build();

}