package edu.washington.escience.myriad.util;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

import edu.washington.escience.myriad.DbException;
import edu.washington.escience.myriad.MyriaConstants;
import edu.washington.escience.myriad.RelationKey;
import edu.washington.escience.myriad.Schema;
import edu.washington.escience.myriad.TupleBatch;
import edu.washington.escience.myriad.Type;
import edu.washington.escience.myriad.accessmethod.SQLiteAccessMethod;

/**
 * Util methods for SQLite.
 * */
public final class SQLiteUtils {
  /**
   * Generates a SQLite CREATE TABLE statement for a table of the given Schema and name.
   * 
   * @param schema the Schema of the table to be created.
   * @param relationKey the name of the table to be created.
   * @return a SQLite CREATE TABLE statement for a table of the given Schema and name.
   */
  public static String createStatementFromSchema(final Schema schema, final RelationKey relationKey) {
    final StringBuilder sb = new StringBuilder();
    sb.append("CREATE TABLE ").append(relationKey.toString(MyriaConstants.STORAGE_SYSTEM_SQLITE)).append(" (");
    for (int i = 0; i < schema.numColumns(); ++i) {
      if (i > 0) {
        sb.append(", ");
      }
      sb.append(schema.getColumnName(i)).append(' ').append(typeToSQLiteType(schema.getColumnType(i)));
    }
    sb.append(");");
    return sb.toString();
  }

  /**
   * Generates a SQLite CREATE TABLE statement for a table of the given Schema and name.
   * 
   * @param schema the Schema of the table to be created.
   * @param relationKey the table to be created.
   * @return a SQLite CREATE TABLE statement for a table of the given Schema and name.
   */
  public static String createIfNotExistsStatementFromSchema(final Schema schema, final RelationKey relationKey) {
    final StringBuilder sb = new StringBuilder();
    sb.append("CREATE TABLE IF NOT EXISTS ").append(relationKey.toString(MyriaConstants.STORAGE_SYSTEM_SQLITE)).append(
        " (\n");
    for (int i = 0; i < schema.numColumns(); ++i) {
      if (i > 0) {
        sb.append(",\n");
      }
      sb.append("    ").append(schema.getColumnName(i)).append(' ').append(typeToSQLiteType(schema.getColumnType(i)));
    }
    sb.append(");");
    return sb.toString();
  }

  /**
   * insert a TupleBatch into SQLite.
   * 
   * @param inputSchema the schema of the input TupleBatch
   * @param relationKey the relation to insert into
   * @param dbFilePath the location of the SQLite DB file
   * @param data the data.
   * @throws DbException if there is an error in the database.
   * */
  public static void insertIntoSQLite(final Schema inputSchema, final RelationKey relationKey, final String dbFilePath,
      final TupleBatch data) throws DbException {

    final List<String> fieldNames = inputSchema.getColumnNames();
    final String[] placeHolders = new String[inputSchema.numColumns()];
    for (int i = 0; i < inputSchema.numColumns(); ++i) {
      placeHolders[i] = "?";
    }

    SQLiteAccessMethod.tupleBatchInsert(dbFilePath, "insert into "
        + relationKey.toString(MyriaConstants.STORAGE_SYSTEM_SQLITE) + " ( " + StringUtils.join(fieldNames, ',')
        + " ) values ( " + StringUtils.join(placeHolders, ',') + " )", data);
  }

  /**
   * Generates a SQLite INSERT statement for a table of the given Schema and name.
   * 
   * @param schema the Schema of the table to be created.
   * @param relationKey the key of the table to be created.
   * @return a SQLite INSERT statement for a table of the given Schema and name.
   */
  public static String insertStatementFromSchema(final Schema schema, final RelationKey relationKey) {
    final StringBuilder sb = new StringBuilder();
    sb.append("INSERT INTO ").append(relationKey.toString(MyriaConstants.STORAGE_SYSTEM_SQLITE)).append(" (");
    sb.append(StringUtils.join(schema.getColumnNames(), ','));
    sb.append(") VALUES (");
    for (int i = 0; i < schema.numColumns(); ++i) {
      if (i > 0) {
        sb.append(',');
      }
      sb.append('?');
    }
    sb.append(");");
    return sb.toString();
  }

  /**
   * Helper utility for creating SQLite CREATE TABLE statements.
   * 
   * @param type a Myriad column type.
   * @return the name of the SQLite type that matches the given Myriad type.
   */
  public static String typeToSQLiteType(final Type type) {
    switch (type) {
      case BOOLEAN_TYPE:
        return "BOOLEAN";
      case DOUBLE_TYPE:
        return "DOUBLE";
      case FLOAT_TYPE:
        return "DOUBLE";
      case INT_TYPE:
        return "INTEGER";
      case LONG_TYPE:
        return "INTEGER";
      case STRING_TYPE:
        return "TEXT";
      default:
        throw new UnsupportedOperationException("Type " + type + " is not supported");
    }
  }

  /**
   * util classes are not instantiable.
   * */
  private SQLiteUtils() {
  }

  /**
   * Creates a SQLite "DROP TABLE IF EXISTS" statement.
   * 
   * @param relationKey the table to be dropped.
   * @return "DROP TABLE IF EXISTS <tt>relationKey.getCanonicalName()</tt>;"
   */
  public static String dropTableIfExistsStatement(final RelationKey relationKey) {
    return "DROP TABLE IF EXISTS " + relationKey.toString(MyriaConstants.STORAGE_SYSTEM_SQLITE) + ";";
  }
}

@@ -1238,32 +1238,44 @@
       sb.append(
+'%5B').append(
 schema.getColumn
@@ -1282,35 +1282,36 @@
 Name(i)).append(
-' '
+%22%5D %22
 ).append(typeToS
@@ -1366,32 +1366,71 @@
 b.append(%22);%22);%0A
+    System.out.println(sb.toString());%0A
     return sb.to
@@ -2172,16 +2172,17 @@
 nd(%22    
+%5B
 %22).appen
@@ -2219,11 +2219,12 @@
 end(
-' '
+%22%5D %22
 ).ap
@@ -2291,32 +2291,71 @@
 b.append(%22);%22);%0A
+    System.out.println(sb.toString());%0A
     return sb.to
@@ -3229,17 +3229,17 @@
 E) + %22 (
- 
+%5B
 %22 + Stri
@@ -3263,19 +3263,21 @@
 dNames, 
-','
+%22%5D,%5B%22
 )%0A      
@@ -3281,16 +3281,17 @@
      + %22
+%5D
  ) value
@@ -3906,32 +3906,33 @@
 ITE)).append(%22 (
+%5B
 %22);%0A    sb.appen
@@ -3975,19 +3975,21 @@
 ames(), 
-','
+%22%5D,%5B%22
 ));%0A    
@@ -3999,16 +3999,17 @@
 append(%22
+%5D
 ) VALUES

Fix when creating a table/inserting to escape using [ ]