import java.util.LinkedHashSet;

public class QueryBuilder {

  private String _select = "*";
  private String _tableName = "<table>";
  private QueryBuilder _from = null;
  private final LinkedHashSet<String> _joins = new LinkedHashSet<>();
  private Where _where;
  private String _orderBy = "";
  private int _index = 0;
  private int _limit = 0;
  private QueryBuilder _union = null;
  private boolean _union_all = false;

  /**
   * Atributos de seleccion de la consulta.
   *
   * @param select atributos
   * @return
   */
  public QueryBuilder select(String... select) {
    StringBuilder sb = new StringBuilder();
    for (int i = 0; i < select.length; i++) {
      if (i > 0) sb.append(',');
      sb.append(select[i]);
    }
    _select = sb.toString();
    return this;
  }

  /**
   * Genera la parte FROM de la consulta.
   *
   * @param tableName nombre de la tabla
   * @return
   */
  public QueryBuilder from(String tableName) {
    return from(tableName, null);
  }

  /**
   * Genera la parte FROM de la consulta.
   *
   * @param as nombre de alias
   * @param from subQuery
   * @return
   */
  public QueryBuilder from(String as, QueryBuilder from) {
    _tableName = as;
    _from = from;
    return this;
  }

  /**
   * Genera la parte JOIN de la consulta
   *
   * @param table table t2
   * @param condition t1.field = t2.field
   * @return
   */
  public QueryBuilder join(String table, String condition) {
    return join(table, condition, null);
  }

  /**
   * Genera la parte JOIN de la consulta
   *
   * @param table table t2
   * @param condition t1.field = t2.field
   * @param type left, inner
   * @return
   */
  public QueryBuilder join(String table, String condition, String type/*LEFT*/) {
    final StringBuilder join = new StringBuilder();
    if (type != null) join.append(type).append(" ");
    join.append("JOIN ")
      .append(table.trim())
      .append(" ON ")
      .append(condition.trim())
    ;
    _joins.add(join.toString());
    return this;
  }

  /**
   * Genera la parte ORDER BY de la consulta
   *
   * @param orderBy
   * @return
   */
  public QueryBuilder orderBy(String orderBy) {
    _orderBy = orderBy;
    return this;
  }

  public QueryBuilder limit(int limit) {
    return limit(0, limit);
  }

  public QueryBuilder limit(int index, int limit) {
    _index = index;
    _limit = limit;
    return this;
  }

  public QueryBuilder union(QueryBuilder union) {
    return _union(false, union);
  }

  public QueryBuilder unionAll(QueryBuilder union) {
    return _union(true, union);
  }

  private QueryBuilder _union(boolean all, QueryBuilder union) {
    _union_all = all;
    _union = union;
    return this;
  }

  @Override public String toString() {
    // SELECT:
    StringBuilder sql = new StringBuilder();
    sql.append("SELECT ").append(_select);

    // FROM:
    sql.append("\nFROM ");
    if (_from != null) {
      sql.append("(\n").append(_from.toString()).append("\n) AS ");
    }
    sql.append(_tableName);

    // JOIN:
    for (String join : _joins) {
      sql.append("\n").append(join);
    }

    // WHERE:
    if (_where != null) {
      sql.append("\nWHERE ").append(_where.toString());
    }

    // UNION:
    if (_union != null) {
      sql.append("\nUNION").append(_union_all ?" ALL\n" :"\n").append(_union.toString());
    }

    // ORDER BY:
    if (_orderBy != null && !_orderBy.isEmpty()) {
      sql.append("\nORDER BY ").append(_orderBy);
    }

    // LIMIT:
    if (_index > 0 && _limit > 0) {
      sql.append("\nLIMIT ").append(_index).append(",").append(_limit);
    } else if (_limit > 0) {
      sql.append("\nLIMIT ").append(_limit);
    }

    return sql.toString();
  }

  public Where where() {
    if (_where == null) _where = new Where(this);
    return _where;
  }

  public static class Where {
    private final QueryBuilder qs;
    private final StringBuilder sql = new StringBuilder();
    private int countClauses = 0;

    public Where(QueryBuilder querySelect) {
      qs = querySelect;
    }

    public Where and() {
      if (countClauses > 0) {
        sql.append("\nAND");
      }
      return this;
    }

    public Where or() {
      if (countClauses > 0) {
        sql.append("\nOR");
      }
      return this;
    }

    public Where not() {
      sql.append(" NOT");
      return this;
    }

    public Where clause(String columnName, String op, Object value) {
      sql.append(" ")
         .append(columnName.trim())
         .append(" ").append(op.trim()).append(" ")
         .append(toValue(value))
      ;
      countClauses++;
      return this;
    }

    public Where like(String columnName, Object value) {
      return clause(columnName, "LIKE", value);
    }

    public Where between(String columnName, Object low, Object high) {
      sql.append(" ")
         .append(columnName)
         .append(" BETWEEN ")
         .append(toValue(low))
         .append(" AND ")
         .append(toValue(high))
      ;
      countClauses++;
      return this;
    }

    public Where in(String columnName, Object... values) {
      sql.append(" ");
      sql.append(columnName);
      sql.append(" IN (");
      for (int i = 0; i < values.length; i++) {
        if (i > 0) sql.append(", ");
        sql.append(toValue(values[i]));
      }
      sql.append(")");
      countClauses++;
      return this;
    }

    public Where in(String columnName, QueryBuilder qs) {
      sql.append(" ")
         .append(columnName)
         .append(" IN (\n")
         .append(qs.toString())
         .append("\n)")
      ;
      countClauses++;
      return this;
    }

    public Where exists(QueryBuilder qs) {
      // EXISTS (SELECT * FROM `producto` WHERE `id` = 0 )
      sql.append(" EXISTS (\n")
         .append(qs.toString())
         .append("\n)")
      ;
      countClauses++;
      return this;
    }

    public Where str(String str) {
      sql.append(str);
      return this;
    }

    public QueryBuilder endWhere() {
      return qs;
    }

    @Override public String toString() {
      return sql.toString();
    }

    public static String toValue(Object value) {
      if (value == null) {
        return "NULL ";
      } else {
        String newValue = value.toString().replace("'", "\\'");
        return new StringBuilder(newValue.length() + 2)
                .append('\'')
                .append(newValue)
                .append('\'')
                .toString();
      }
    }
  }

  public static void main(String[] args) {
    QueryBuilder qs = new QueryBuilder()
            .select("*")
            .from("vmesacontrol")
            .where() // -->
            .and().between("CAST(fechaCaso as DATE)", "2018-04-14", "2018-06-30")
            .and().in("visible", 0, 1)
            .and().clause("idAgente", "=", 14)
            .and()
            .str("(\n")
            .like("cliente", "%ka%")
            .or().like("empresa", "%ka%")
            .str("\n)")
            .or().in("idCaso", new QueryBuilder()
                    .select("idCaso")
                    .from("vmesacontrol")
                    .where() // -->
                    .and().between("CAST(plazoPostergacion as DATE)", "2018-04-14", "2018-06-30")
                    .and().in("visible", 0, 1, -1)
                    .and().clause("idAgente", "=", 14)
                    .and()
                    .str("(\n")
                    .like("cliente", "%ka%")
                    .or().like("empresa", "%ka%")
                    .str("\n)")
                    .endWhere() // <--
            )
            .endWhere() // <--
            .orderBy("idCaso DESC");

    System.out.println(qs.toString());
  }
}