package com.nolanlawson.cordova.sqlite;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
import android.os.Handler;
import android.os.HandlerThread;
import android.util.Log;

import org.apache.cordova.CallbackContext;
import org.apache.cordova.CordovaPlugin;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import java.io.File;
import java.util.HashMap;
import java.util.Map;

/**
 * Author: Nolan Lawson
 * License: Apache 2
 */
public class SQLitePlugin extends CordovaPlugin {

  private static final boolean DEBUG_MODE = false;

  private static final String TAG = SQLitePlugin.class.getSimpleName();

  private static final Object[][] EMPTY_ROWS = new Object[][]{};
  private static final String[] EMPTY_COLUMNS = new String[]{};
  private static final SQLitePLuginResult EMPTY_RESULT = new SQLitePLuginResult(EMPTY_ROWS, EMPTY_COLUMNS, 0, 0, null);

  private static final Map<String, SQLiteDatabase> DATABASES = new HashMap<String, SQLiteDatabase>();

  private final Handler backgroundHandler = createBackgroundHandler();

  private Handler createBackgroundHandler() {
    HandlerThread thread = new HandlerThread("SQLitePlugin BG Thread");
    thread.start();
    return new Handler(thread.getLooper());
  }

  @Override
  public boolean execute(String action, JSONArray args, CallbackContext callbackContext) throws JSONException {
    debug("execute(%s)", action);
    this.run(args, callbackContext);
    return true;
  }

  private void run(final JSONArray args, final CallbackContext callbackContext) {
    backgroundHandler.post(new Runnable() {
      @Override
      public void run() {
        try {
          runInBackground(args, callbackContext);
        } catch (Throwable e) {
          e.printStackTrace(); // should never happen
          callbackContext.error(e.getMessage());
        }
      }
    });
  }

  private void runInBackground(JSONArray args, CallbackContext callbackContext) throws JSONException {
    SQLitePLuginResult[] pluginResults = execInBackgroundAndReturnResults(args);
    callbackContext.success(pluginResultsToString(pluginResults));
  }

  private SQLitePLuginResult[] execInBackgroundAndReturnResults(JSONArray args) throws JSONException {

    String dbName = args.getString(0);
    JSONArray queries = args.getJSONArray(1);
    boolean readOnly = args.getBoolean(2);
    int numQueries = queries.length();
    SQLitePLuginResult[] results = new SQLitePLuginResult[numQueries];
    SQLiteDatabase db = getDatabase(dbName);

    for (int i = 0; i < numQueries; i++) {
      JSONArray sqlQuery = queries.getJSONArray(i);
      String sql = sqlQuery.getString(0);
      String[] bindArgs = jsonArrayToStringArray(sqlQuery.getJSONArray(1));
      try {
        if (isSelect(sql)) {
          results[i] = doSelectInBackgroundAndPossiblyThrow(sql, bindArgs, db);
        } else { // update/insert/delete
          if (readOnly) {
            results[i] = new SQLitePLuginResult(EMPTY_ROWS, EMPTY_COLUMNS, 0, 0, new ReadOnlyException());
          } else {
            results[i] = doUpdateInBackgroundAndPossiblyThrow(sql, bindArgs, db);
          }
        }
      } catch (Throwable e) {
        if (DEBUG_MODE) {
          e.printStackTrace();
        }
        results[i] = new SQLitePLuginResult(EMPTY_ROWS, EMPTY_COLUMNS, 0, 0, e);
      }
    }
    return results;
  }

  // do a update/delete/insert operation
  private SQLitePLuginResult doUpdateInBackgroundAndPossiblyThrow(String sql, String[] bindArgs,
                                                                  SQLiteDatabase db) {
    debug("\"run\" query: %s", sql);
    SQLiteStatement statement = null;
    try {
      statement = db.compileStatement(sql);
      debug("compiled statement");
      if (bindArgs != null) {
        bindAllArguments(statement, bindArgs);
      }
      debug("bound args");
      if (isInsert(sql)) {
        debug("type: insert");
        long insertId = statement.executeInsert();
        int rowsAffected = insertId >= 0 ? 1 : 0;
        return new SQLitePLuginResult(EMPTY_ROWS, EMPTY_COLUMNS, rowsAffected, insertId, null);
      } else if (isDelete(sql) || isUpdate(sql)) {
        debug("type: update/delete");
        int rowsAffected = statement.executeUpdateDelete();
        return new SQLitePLuginResult(EMPTY_ROWS, EMPTY_COLUMNS, rowsAffected, 0, null);
      } else {
        // in this case, we don't need rowsAffected or insertId, so we can have a slight
        // perf boost by just executing the query
        debug("type: drop/create/etc.");
        statement.execute();
        return EMPTY_RESULT;
      }
    } finally {
      if (statement != null) {
        statement.close();
      }
    }
  }

  // do a select operation
  private SQLitePLuginResult doSelectInBackgroundAndPossiblyThrow(String sql, String[] bindArgs,
                                                                  SQLiteDatabase db) {
    debug("\"all\" query: %s", sql);
    Cursor cursor = null;
    try {
      cursor = db.rawQuery(sql, bindArgs);
      int numRows = cursor.getCount();
      if (numRows == 0) {
        return EMPTY_RESULT;
      }
      int numColumns = cursor.getColumnCount();
      Object[][] rows = new Object[numRows][];
      String[] columnNames = cursor.getColumnNames();
      for (int i = 0; cursor.moveToNext(); i++) {
        Object[] row = new Object[numColumns];
        for (int j = 0; j < numColumns; j++) {
          row[j] = getValueFromCursor(cursor, j, cursor.getType(j));
        }
        rows[i] = row;
      }
      debug("returning %d rows", numRows);
      return new SQLitePLuginResult(rows, columnNames, 0, 0, null);
    } finally {
      if (cursor != null) {
        cursor.close();
      }
    }
  }

  private Object getValueFromCursor(Cursor cursor, int index, int columnType) {
    switch (columnType) {
      case Cursor.FIELD_TYPE_FLOAT:
        return cursor.getFloat(index);
      case Cursor.FIELD_TYPE_INTEGER:
        return cursor.getInt(index);
      case Cursor.FIELD_TYPE_BLOB:
        // convert byte[] to binary string; it's good enough, because
        // WebSQL doesn't support blobs anyway
        return new String(cursor.getBlob(index));
      case Cursor.FIELD_TYPE_STRING:
        return cursor.getString(index);
    }
    return null;
  }

  private SQLiteDatabase getDatabase(String name) {
    SQLiteDatabase database = DATABASES.get(name);
    if (database == null) {
      if (":memory:".equals(name)) {
        database = SQLiteDatabase.openOrCreateDatabase(name, null);
      } else {
        File file = new File(cordova.getActivity().getFilesDir(), name);
        database = SQLiteDatabase.openOrCreateDatabase(file, null);
      }
      DATABASES.put(name, database);
    }
    return database;
  }

  private static void debug(String line, Object... format) {
    if (DEBUG_MODE) {
      Log.d(TAG, String.format(line, format));
    }
  }

  private static String pluginResultsToString(SQLitePLuginResult[] results) throws JSONException {
    // Instead of converting to a json array, we convert directly to a string
    // because the perf ends up being better, since Cordova will just stringify
    // the JSONArray anyway.
    StringBuilder sb = new StringBuilder("[");
    for (int i = 0; i < results.length; i++) {
      if (i > 0) {
        sb.append(',');
      }
      appendPluginResult(results[i], sb);
    }
    return sb.append(']').toString();
  }

  private static void appendPluginResult(SQLitePLuginResult result, StringBuilder sb) throws JSONException {
    sb.append('[');
    if (result.error == null) {
      sb.append("null");
    } else {
      sb.append(JSONObject.quote(result.error.getMessage()));
    }
    sb.append(',')
        .append(JSONObject.numberToString(result.insertId))
        .append(',')
        .append(JSONObject.numberToString(result.rowsAffected))
        .append(',');

    // column names
    sb.append('[');
    for (int i = 0; i < result.columns.length; i++) {
      if (i > 0) {
        sb.append(',');
      }
      sb.append(JSONObject.quote(result.columns[i]));
    }
    sb.append("],[");
    // rows
    for (int i = 0; i < result.rows.length; i++) {
      if (i > 0) {
        sb.append(',');
      }
      Object[] values = result.rows[i];
      // row content
      sb.append('[');
      for (int j = 0; j < values.length; j++) {
        if (j > 0) {
          sb.append(',');
        }
        Object value = values[j];
        if (value == null) {
          sb.append("null");
        } else if (value instanceof String) {
          sb.append(JSONObject.quote((String)value));
        } else if (value instanceof Boolean) {
          sb.append(value.toString());
        } else {
          sb.append(JSONObject.numberToString((Number)value));
        }
      }
      sb.append(']');
    }
    sb.append("]]");

    debug("returning json: %s", sb);
  }

  private static boolean isSelect(String str) {
    return startsWithCaseInsensitive(str, "select");
  }
  private static boolean isInsert(String str) {
    return startsWithCaseInsensitive(str, "insert");
  }
  private static boolean isUpdate(String str) {
    return startsWithCaseInsensitive(str, "update");
  }
  private static boolean isDelete(String str) {
    return startsWithCaseInsensitive(str, "delete");
  }

  // identify an "insert"/"select" query more efficiently than with a Pattern
  private static boolean startsWithCaseInsensitive(String str, String substr) {
    int i = -1;
    int len = str.length();
    while (++i < len) {
      char ch = str.charAt(i);
      if (!Character.isWhitespace(ch)) {
        break;
      }
    }

    int j = -1;
    int substrLen = substr.length();
    while (++j < substrLen) {
      if (j + i >= len) {
        return false;
      }
      char ch = str.charAt(j + i);
      if (Character.toLowerCase(ch) != substr.charAt(j)) {
        return false;
      }
    }
    return true;
  }

  private static String[] jsonArrayToStringArray(JSONArray jsonArray) throws JSONException {
    int len = jsonArray.length();
    String[] res = new String[len];
    for (int i = 0; i < len; i++) {
      res[i] = !jsonArray.isNull(i) ? jsonArray.getString(i) : null;
    }
    return res;
  }

  private static class SQLitePLuginResult {
    public final Object[][] rows;
    public final String[] columns;
    public final int rowsAffected;
    public final long insertId;
    public final Throwable error;

    public SQLitePLuginResult(Object[][] rows, String[] columns,
                              int rowsAffected, long insertId, Throwable error) {
      this.rows = rows;
      this.columns = columns;
      this.rowsAffected = rowsAffected;
      this.insertId = insertId;
      this.error = error;
    }
  }

  private static class ReadOnlyException extends Exception {
    public ReadOnlyException() {
      super("could not prepare statement (23 not authorized)");
    }
  }

  private static void bindAllArguments(SQLiteStatement statement, String[] bindArgs) {
    for (int i = 0; i < bindArgs.length; i++) {
      if (bindArgs[i] == null) {
        statement.bindNull(i + 1);
      } else {
        statement.bindString(i + 1, bindArgs[i]);
      }
    }
  }
}