content: /*
   * Copyright (C) 2015 Actor LLC. <https://actor.im>
   */
  
  package im.actor.runtime.android.storage;
  
  import android.database.Cursor;
  import android.database.sqlite.SQLiteDatabase;
  
  import java.util.ArrayList;
  import java.util.List;
  
  import im.actor.runtime.storage.ListEngineRecord;
  import im.actor.runtime.storage.ListStorageDisplayEx;
  
  public class SQLiteList implements ListStorageDisplayEx {
  
      private SQLiteDatabase database;
      private String tableName;
      private boolean isTableChecked = false;
  
      public SQLiteList(SQLiteDatabase database, String tableName) {
          this.database = database;
          this.tableName = tableName;
      }
  
      private void checkTable() {
          if (isTableChecked) {
              return;
          }
          isTableChecked = true;
  
          if (!SQLiteHelpers.isTableExists(database, tableName)) {
              database.execSQL("CREATE TABLE IF NOT EXISTS \"" + tableName + "\" (" + //
                      "\"ID\" INTEGER NOT NULL," + // 0: id
                      "\"SORT_KEY\" INTEGER NOT NULL," + // 1: sortKey
                      "\"QUERY\" TEXT," + // 2: query
                      "\"BYTES\" BLOB NOT NULL," + // 3: bytes
                      "PRIMARY KEY(\"ID\"));");
  
              // Filter index
              database.execSQL("CREATE INDEX IF NOT EXISTS IDX_ID_QUERY_SORT ON \"" + tableName + "\" (\"QUERY\", \"SORT_KEY\");");
  
              // Standard index
              database.execSQL("CREATE INDEX IF NOT EXISTS IDX_ID_SORT ON \"" + tableName + "\" (\"SORT_KEY\");");
          }
      }
  
      @Override
      public void updateOrAdd(ListEngineRecord valueContainer) {
          checkTable();
  
          Object[] args = new Object[]{valueContainer.getKey(), valueContainer.getQuery() != null ? valueContainer.getQuery().toLowerCase() : null, valueContainer.getOrder(), valueContainer.getData()};
          database.execSQL("REPLACE INTO \"" + tableName + "\" (\"ID\",\"QUERY\",\"SORT_KEY\",\"BYTES\") VALUES (?,?,?,?)", args);
      }
  
      @Override
      public void updateOrAdd(List<ListEngineRecord> items) {
          checkTable();
  
          database.beginTransaction();
  
          try {
              for (ListEngineRecord record : items) {
                  Object[] args = new Object[]{record.getKey(), record.getQuery() != null ? record.getQuery().toLowerCase() : null, record.getOrder(), record.getData()};
                  database.execSQL("REPLACE INTO \"" + tableName + "\" (\"ID\",\"QUERY\",\"SORT_KEY\",\"BYTES\") VALUES (?,?,?,?)", args);
              }
              database.setTransactionSuccessful();
          } finally {
              database.endTransaction();
          }
      }
  
      @Override
      public void delete(long key) {
          checkTable();
  
          Object[] args = new Object[]{key};
          database.execSQL("DELETE FROM \"" + tableName + "\" WHERE \"ID\"=?", args);
      }
  
      @Override
      public void delete(long[] keys) {
          checkTable();
  
          database.beginTransaction();
          try {
              for (long key : keys) {
                  Object[] args = new Object[]{key};
                  database.execSQL("DELETE FROM \"" + tableName + "\" WHERE \"ID\"=?", args);
              }
              database.setTransactionSuccessful();
          } finally {
              database.endTransaction();
          }
      }
  
      @Override
      public void clear() {
          checkTable();
  
          database.execSQL("DELETE FROM \"" + tableName + "\"");
      }
  
      @Override
      public ListEngineRecord loadItem(long key) {
          checkTable();
  
          Cursor cursor = database.query("\"" + tableName + "\"", new String[]{"\"ID\"", "\"SORT_KEY\"",
                          "\"QUERY\"", "\"BYTES\""}, "\"ID\"=?",
                  new String[]{String.valueOf(key)}, null, null, null);
          if (cursor == null) {
              return null;
          }
          try {
              if (cursor.moveToFirst()) {
                  return new ListEngineRecord(key, cursor.getLong(cursor.getColumnIndex("SORT_KEY")),
                          cursor.getString(cursor.getColumnIndex("QUERY")),
                          cursor.getBlob(cursor.getColumnIndex("BYTES")));
              }
          } finally {
              cursor.close();
          }
          return null;
      }
  
      @Override
      public List<ListEngineRecord> loadAllItems() {
          return null;
      }
  
  
      public ListEngineRecord loadItemBySortKey(long key) {
          checkTable();
  
          Cursor cursor = database.query("\"" + tableName + "\"", new String[]{"\"ID\"", "\"SORT_KEY\"",
                          "\"QUERY\"", "\"BYTES\""}, "\"SORT_KEY\"=?",
                  new String[]{String.valueOf(key)}, null, null, null);
          if (cursor == null) {
              return null;
          }
          try {
              if (cursor.moveToFirst()) {
                  return new ListEngineRecord(key, cursor.getLong(cursor.getColumnIndex("SORT_KEY")),
                          cursor.getString(cursor.getColumnIndex("QUERY")),
                          cursor.getBlob(cursor.getColumnIndex("BYTES")));
              }
          } finally {
              cursor.close();
          }
          return null;
      }
  
      @Override
      public boolean isEmpty() {
          Cursor cursor = database.rawQuery("EXISTS (SELECT * FROM \"" + tableName + "\");", null);
          if (cursor != null) {
              try {
                  return cursor.getInt(0) > 0;
              } finally {
                  cursor.close();
              }
          }
          return false;
      }
  
      @Override
      public List<ListEngineRecord> loadForward(Long sortingKey, int limit) {
          checkTable();
  
          Cursor cursor;
          if (sortingKey == null) {
              cursor = database.query("\"" + tableName + "\"",
                      new String[]{"\"LIST_ID\"", "\"ID\"", "\"SORT_KEY\"", "\"QUERY\"", "\"BYTES\""},
                      null, null, null, null, "\"SORT_KEY\" DESC", String.valueOf(limit));
          } else {
              cursor = database.query("\"" + tableName + "\"",
                      new String[]{"\"ID\"", "\"SORT_KEY\"", "\"QUERY\"", "\"BYTES\""},
                      "\"SORT_KEY\" < ?",
                      new String[]{
                              String.valueOf(sortingKey)
                      }, null, null, "\"SORT_KEY\" DESC", String.valueOf(limit));
          }
  
          return loadSlice(cursor);
      }
  
      @Override
      public List<ListEngineRecord> loadBackward(Long sortingKey, int limit) {
          checkTable();
  
          Cursor cursor;
          if (sortingKey == null) {
              cursor = database.query("\"" + tableName + "\"",
                      new String[]{"\"LIST_ID\"", "\"ID\"", "\"SORT_KEY\"", "\"QUERY\"", "\"BYTES\""},
                      null, null, null, null, "\"SORT_KEY\" ASC", String.valueOf(limit));
          } else {
              cursor = database.query("\"" + tableName + "\"",
                      new String[]{"\"ID\"", "\"SORT_KEY\"", "\"QUERY\"", "\"BYTES\""},
                      "\"SORT_KEY\" > ?",
                      new String[]{
                              String.valueOf(sortingKey)
                      }, null, null, "\"SORT_KEY\" ASC", String.valueOf(limit));
          }
  
          return loadSlice(cursor);
      }
  
      @Override
      public List<ListEngineRecord> loadForward(String query, Long sortingKey, int limit) {
          checkTable();
          Cursor cursor;
          if (sortingKey == null) {
              cursor = database.query("\"" + tableName + "\"", new String[]{"\"LIST_ID\"", "\"ID\"", "\"SORT_KEY\"", "\"QUERY\"", "\"BYTES\""},
                      "\"QUERY\" LIKE ? OR \"QUERY\" LIKE ?", new String[]{
                              query + "%",
                              "% " + query + "%"
                      }, null, null, "SORT_KEY DESC", String.valueOf(limit));
          } else {
              cursor = database.query("\"" + tableName + "\"",
                      new String[]{"\"ID\"", "\"SORT_KEY\"", "\"QUERY\"", "\"BYTES\""},
                      "(\"QUERY\" LIKE ? OR \"QUERY\" LIKE ?) AND \"SORT_KEY\" < ?",
                      new String[]{
                              query + "%",
                              "% " + query + "%",
                              String.valueOf(sortingKey)
                      }, null, null, "\"SORT_KEY\" DESC", String.valueOf(limit));
          }
          return loadSlice(cursor);
      }
  
      @Override
      public List<ListEngineRecord> loadCenter(Long centerSortKey, int limit) {
          checkTable();
  
          Cursor cursor;
          if (centerSortKey == null) {
              cursor = database.query("\"" + tableName + "\"",
                      new String[]{"\"LIST_ID\"", "\"ID\"", "\"SORT_KEY\"", "\"QUERY\"", "\"BYTES\""},
                      null, null, null, null, "\"SORT_KEY\" DESC", String.valueOf(limit));
              return loadSlice(cursor);
          } else {
  
              ListEngineRecord centerItem = loadItemBySortKey(centerSortKey);
  
              ArrayList<ListEngineRecord> ret = new ArrayList<ListEngineRecord>();
              ret.addAll(loadBackward(centerSortKey, limit));
              if (centerItem != null) ret.add(centerItem);
              ret.addAll(loadForward(centerSortKey, limit));
              return ret;
          }
  
  
      }
  
      @Override
      public List<ListEngineRecord> loadBackward(String query, Long sortingKey, int limit) {
          checkTable();
          Cursor cursor;
          if (sortingKey == null) {
              cursor = database.query("\"" + tableName + "\"", new String[]{"\"LIST_ID\"", "\"ID\"", "\"SORT_KEY\"", "\"QUERY\"", "\"BYTES\""},
                      "\"QUERY\" LIKE ? OR \"QUERY\" LIKE ?", new String[]{
                              query + "%",
                              "% " + query + "%"
                      }, null, null, "SORT_KEY ASC", String.valueOf(limit));
          } else {
              cursor = database.query("\"" + tableName + "\"",
                      new String[]{"\"ID\"", "\"SORT_KEY\"", "\"QUERY\"", "\"BYTES\""},
                      "(\"QUERY\" LIKE ? OR \"QUERY\" LIKE ?) AND \"SORT_KEY\" > ?",
                      new String[]{
                              query + "%",
                              "% " + query + "%",
                              String.valueOf(sortingKey)
                      }, null, null, "\"SORT_KEY\" ASC", String.valueOf(limit));
          }
          return loadSlice(cursor);
      }
  
      @Override
      public int getCount() {
          checkTable();
  
          Cursor mCount = null;
          try {
              mCount = database.rawQuery("SELECT COUNT(*) FROM \"" + tableName + "\"", null);
              if (mCount.moveToFirst()) {
                  return mCount.getInt(0);
              }
          } finally {
              if (mCount != null) {
                  mCount.close();
              }
          }
          return 0;
      }
  
      private ArrayList<ListEngineRecord> loadSlice(Cursor cursor) {
          // int queryColumn = enableSearch ? cursor.getColumnIndex("QUERY") : -1;
          ArrayList<ListEngineRecord> res = new ArrayList<ListEngineRecord>();
          if (cursor != null) {
              int idColumn = cursor.getColumnIndex("ID");
              int sortColumn = cursor.getColumnIndex("SORT_KEY");
              int bytesColumn = cursor.getColumnIndex("BYTES");
              int queryColumn = cursor.getColumnIndex("QUERY");
              try {
                  if (cursor.moveToFirst()) {
                      do {
                          res.add(new ListEngineRecord(cursor.getLong(idColumn), cursor.getLong(sortColumn),
                                  cursor.getString(queryColumn),
                                  cursor.getBlob(bytesColumn)));
                      } while (cursor.moveToNext());
                  }
              } finally {
                  cursor.close();
              }
          }
  
          return res;
      }
  }