/dev/maverickAgainst the grain software and startups

  @Test
  void buildSelectSql() {
    def sql = new SqlBuilder();
    Select select = sql.select ("table1") {
      join("table2", type: "INNER") {
        using(table1: "col1", table2: "col1")
      }
      join("table3", type: "OUTER") {
        using(table1: "col2", table2: "col2")
        using(table1: "col3", table2: "col3")
      }
      where("table1.col1 = 'test'")
      groupBy(table1: "col1", table2: "col1")
      orderBy(table1: "col1", table2: "col1")
    }
    assertNotNull select, "Select object should not be null"
    println "SELECT: " + select.getSql()
    assertNotNull select.getSql(), "Generated SQL SELECT expression should not be null"
    assertFalse select.getSql().trim() == "", "Generated SQL SELECT expression should not be empty"
  }

/**
 * SqlBuilder is a DSL to build portable SQL using groovy internal DSL facilities.  It allows you to specify
 * SQL queries using groovy expressions vs. string concatenation/interpolation as it's usually done.
 *
 *
 * @author Ilya Sterin
 */
public class SqlBuilder extends FactoryBuilderSupport {

  // Register expression factories
  {
    registerFactory('select', new SelectFactory())
    registerFactory('join', new JoinFactory())
    registerFactory('using', new UsingFactory())
    registerFactory('where', new WhereFactory())
    registerFactory('groupBy', new GroupAndOrderFactory())
    registerFactory('orderBy', new GroupAndOrderFactory())
  }
}

/**
 * Select class encapsulates the select clause node of the SQL query
 * and acts as a parent node to all select queries.
 */
private class Select {

  def table,        // Name of the main select table
      tables  = [], // All tables join tables and select table
      joins   = [], // Collection of all joins
      where,        // Where clause
      groupBy = [], // Collection of group by columns
      orderBy = []  // Collection of order by columns

  /**
   * Builds and returns the SQL query based on the DSL expressions
   */
  String getSql() {
    def sql = "SELECT * FROM $table"
    joins.each {Join join ->
      sql += " ${join.type} JOIN ${join.table} ON"
      join.using.eachWithIndex {Using using, idx ->
        if (idx > 0) sql += " AND"
        sql += " ${using.lhs} ${using.op} ${using.rhs}"
      }
    }
    where.eachWithIndex {Where where, i ->
      if (i == 0) sql += " WHERE ";
      sql += "${where.clause}"
    }
    if (groupBy) sql += " GROUP BY ${groupBy.join(', ')}";
    if (orderBy) sql += " ORDER BY ${orderBy.join(', ')}";
    return sql
  }

}

///////// Private classes to support the builder functionality /////////////

private class Join {
  def table
  def type = "INNER"
  def using = []
}

private class Using {
  def lhs, rhs, op = "="

  def methodMissing(String name, args) {
    println "Looking for ${name}"
  }
}

private class Where {
  def clause
}

///////// Factories that construct the appropriate objects based on the DSL expressions //////////////

private class SelectFactory extends AbstractFactory {

  public Object newInstance(FactoryBuilderSupport factoryBuilderSupport, name, value, Map map) {
    return new Select(table: value, tables: [value])
  }

  public void setChild(FactoryBuilderSupport factoryBuilderSupport, Object parent, Object child) {
    println "Child ${child}"
    if (child instanceof Join) {
      println "Adding a join"
      parent.joins << child
    }
    if (child instanceof Where) {
      println "Adding a where"
      parent.where = child
    }
  }
}

private class JoinFactory extends AbstractFactory {

  public Object newInstance(FactoryBuilderSupport factoryBuilderSupport, name, value, Map map) {
    Join join = new Join(table: value)
    return join
  }

  public void setChild(FactoryBuilderSupport factoryBuilderSupport, Object parent, Object child) {
    super.setChild(factoryBuilderSupport, parent, child)
    if (child instanceof Using) {
      parent.using << child
    }
  }

  public void setParent(FactoryBuilderSupport factoryBuilderSupport, Object parent, Object child) {
    super.setParent(factoryBuilderSupport, parent, child);
    parent.tables << child.table
  }
}

private class UsingFactory extends AbstractFactory {

  public Object newInstance(FactoryBuilderSupport factoryBuilderSupport, name, op, Map map) {
    Using using = new Using()
    if (op) using.op = op
    map.each {k, v ->
      if (!using.lhs) {
        using.lhs = "${k}.${v}"
      }
      else if (!using.rhs) {
        using.rhs = "${k}.${v}"
      }
    }
    map.clear()
    return using
  }

  public boolean onHandleNodeAttributes(FactoryBuilderSupport factoryBuilderSupport, Object o, Map map) {
    return true;
  }

  public boolean isLeaf() {
    return true;
  }
}

private class WhereFactory extends AbstractFactory {

  public Object newInstance(FactoryBuilderSupport factoryBuilderSupport, name, value, Map map) {
    return new Where(clause: value)
  }

  public boolean isLeaf() {
    return true;
  }
}


private class GroupAndOrderFactory extends AbstractFactory {

  public Object newInstance(FactoryBuilderSupport factoryBuilderSupport, name, value, Map map) {
    def fqCols = [];
    map.each {k, v ->
      fqCols << "${k}.${v}"
    }
    return [name: name, cols: fqCols];
  }

  public void setParent(FactoryBuilderSupport factoryBuilderSupport, Object parent, Object child) {
    super.setParent(factoryBuilderSupport, parent, child);
    if (child.name == "groupBy") {
      parent.groupBy += child.cols
    }
    if (child.name == "orderBy") {
      parent.orderBy += child.cols
    }
  }

  public boolean onHandleNodeAttributes(FactoryBuilderSupport factoryBuilderSupport, Object o, Map map) {
    return true;
  }

  public boolean isLeaf() {
    return true;
  }
}