context: using System;
  using System.Collections.Generic;
  using System.Collections.ObjectModel;
  using System.Globalization;
  using System.IO;
  using System.Runtime.Serialization;
  using System.Threading;
  
  using SQLite;
  
  using WPCordovaClassLib.Cordova;
  using WPCordovaClassLib.Cordova.Commands;
  using WPCordovaClassLib.Cordova.JSON;
  
  namespace Cordova.Extension.Commands
  {
  
      public class SQLitePlugin : BaseCommand
      {
          #region SQLitePlugin options
  
          [DataContract]
          public class SQLitePluginOpenOptions
          {
              [DataMember(IsRequired = true, Name = "name")]
              public string name { get; set; }
  
              [DataMember(IsRequired = false, Name = "bgType", EmitDefaultValue = false)]
              public int bgType = 0;
          }
  
          [DataContract]
          public class SQLitePluginCloseDeleteOptions
          {
              [DataMember(IsRequired = true, Name = "path")]
              public string name { get; set; }
          }
  
          [DataContract]
          public class SQLitePluginExecuteSqlBatchArgs
          {
              [DataMember(IsRequired = true, Name = "dbname")]
              public string name { get; set; }
          }
  
          [DataContract]
          public class SQLitePluginExecuteSqlBatchOptions
          {
              [DataMember(IsRequired = true, Name = "dbargs")]
              public SQLitePluginExecuteSqlBatchArgs dbargs { get; set; }
  
              [DataMember(IsRequired = true, Name = "executes")]
              public TransactionsCollection executes { get; set; }
          }
  
          [CollectionDataContract]
          public class TransactionsCollection : Collection<SQLitePluginTransaction>
          {
  
          }
  
          [DataContract]
          public class SQLitePluginTransaction
          {
              /// <summary>
              /// Identifier for transaction
              /// </summary>
              [DataMember(IsRequired = false, Name = "trans_id")]
              public string transId { get; set; }
  
              /// <summary>
              /// Callback identifer
              /// </summary>
              [DataMember(IsRequired = true, Name = "qid")]
              public string queryId { get; set; }
  
              /// <summary>
              /// Query string
              /// </summary>
              [DataMember(IsRequired = true, Name = "sql")]
              public string query { get; set; }
  
              /// <summary>
              /// Query parameters
              /// </summary>
              /// <remarks>TODO: broken in WP8 & 8.1; interprets ["1.5"] as [1.5]</remarks>
              [DataMember(IsRequired = true, Name = "params")]
              public object[] query_params { get; set; }
  
          }
  
          #endregion
  
          private readonly DatabaseManager databaseManager;
  
          public SQLitePlugin()
          {
              this.databaseManager = new DatabaseManager(this);
          }
  
          public void open(string options)
          {
              string mycbid = this.CurrentCommandCallbackId;
              //System.Diagnostics.Debug.WriteLine("SQLitePlugin.open() with cbid " + mycbid + " options:" + options);
  
              try
              {
                  String [] jsonOptions = JsonHelper.Deserialize<string[]>(options);
                  mycbid = jsonOptions[1];
  
                  var dbOptions = JsonHelper.Deserialize<SQLitePluginOpenOptions>(jsonOptions[0]);
                  this.databaseManager.Open(dbOptions.name, mycbid);
              }
              catch (Exception)
              {
                  DispatchCommandResult(new PluginResult(PluginResult.Status.JSON_EXCEPTION), mycbid);
              }
          }
  
          public void close(string options)
          {
              string mycbid = this.CurrentCommandCallbackId;
  
              try
              {
                  String[] jsonOptions = JsonHelper.Deserialize<string[]>(options);
                  mycbid = jsonOptions[1];
  
                  var dbOptions = JsonHelper.Deserialize<SQLitePluginCloseDeleteOptions>(jsonOptions[0]);
                  this.databaseManager.Close(dbOptions.name, mycbid);
              }
              catch (Exception)
              {
                  DispatchCommandResult(new PluginResult(PluginResult.Status.JSON_EXCEPTION), mycbid);
              }
          }
  
          public void delete(string options)
          {
              string mycbid = this.CurrentCommandCallbackId;
  
              try
              {
                  String[] jsonOptions = JsonHelper.Deserialize<string[]>(options);
                  mycbid = jsonOptions[1];
  
                  var dbOptions = JsonHelper.Deserialize<SQLitePluginCloseDeleteOptions>(jsonOptions[0]);
                  this.databaseManager.Delete(dbOptions.name, mycbid);
              }
              catch (Exception)
              {
                  DispatchCommandResult(new PluginResult(PluginResult.Status.JSON_EXCEPTION), mycbid);
              }
          }
  
          public void backgroundExecuteSqlBatch(string options)
          {
              string mycbid = this.CurrentCommandCallbackId;
  
              try
              {
                  String[] jsonOptions = JsonHelper.Deserialize<string[]>(options);
                  mycbid = jsonOptions[1];
  
                  SQLitePluginExecuteSqlBatchOptions batch = JsonHelper.Deserialize<SQLitePluginExecuteSqlBatchOptions>(jsonOptions[0]);
                  this.databaseManager.Query(batch.dbargs.name, batch.executes, mycbid);
              }
              catch (Exception)
              {
                  DispatchCommandResult(new PluginResult(PluginResult.Status.JSON_EXCEPTION), mycbid);
              }
          }
  
          /// <summary>
          /// Manage the collection of currently open databases and queue requests for them
          /// </summary>
          public class DatabaseManager
          {
              private readonly BaseCommand plugin;
              private readonly IDictionary<string, DBRunner> runners = new Dictionary<string, DBRunner>();
              private readonly object runnersLock = new object();
  
              public DatabaseManager(BaseCommand plugin)
              {
                  this.plugin = plugin;
              }
  
              public void Open(string dbname, string cbc)
              {
                  DBRunner runner;
                  lock (runnersLock)
                  {
                      if (!runners.TryGetValue(dbname, out runner))
                      {
                          runner = new DBRunner(this, dbname, cbc);
                          runner.Start();
                          runners[dbname] = runner;
                      }
                      else
                      {
                          // acknowledge open if it is scheduled after first query.
                          // Also works for re-opening a database, but means that one cannot close a single instance of a database.
                          this.Ok(cbc);
                      }
                  }
              }
  
              public void Query(string dbname, TransactionsCollection queries, string callbackId)
              {
                  lock (runnersLock)
                  {
                      DBRunner runner;
                      if (!runners.TryGetValue(dbname, out runner))
                      {
                          // query may start before open is scheduled
                          runner = new DBRunner(this, dbname, null);
                          runner.Start();
                          runners[dbname] = runner;
                      }
  
                      var query = new DBQuery(queries, callbackId);
                      runner.Enqueue(query);
                  }
              }
  
              public void Close(string dbname, string callbackId)
              {
                  lock (runnersLock)
                  {
                      DBRunner runner;
                      if (runners.TryGetValue(dbname, out runner))
                      {
                          var query = new DBQuery(false, callbackId);
                          runner.Enqueue(query);
  
                          // As we cannot determine the order in which query/open requests arrive,
                          // any such requests should start a new thread rather than being lost on the dying queue.
                          // Hence synchronoous wait for thread-end within request and within lock.
                          runner.WaitForStopped();
                          runners.Remove(dbname);
                      }
                      else
                      {
                          // closing a closed database is trivial
                          Ok(callbackId);
                      }
                  }
              }
  
              public void Delete(string dbname, string callbackId)
              {
                  var query = new DBQuery(false, callbackId);
                  lock (runnersLock)
                  {
                      DBRunner runner;
                      if (runners.TryGetValue(dbname, out runner))
                      {
                          runner.Enqueue(query);
  
                          // As we cannot determine the order in which query/open requests arrive,
                          // any such requests should start a new thread rather than being lost on the dying queue.
                          // Hence synchronoous wait for thread-end within request and within lock.
                          runner.WaitForStopped();
                          runners.Remove(dbname);
                      }
                      else
                      {
                          // Deleting a closed database, so no runner to queue the request on
                          // Must be inside lock so databases are not opened while being deleted
                          DBRunner.DeleteDatabaseNow(this, dbname, callbackId);
                      }
                  }
              }
  
              public void Finished(DBRunner runner)
              {
                  lock (runnersLock)
                  {
                      runners.Remove(runner.DatabaseName);
                  }
              }
  
              public void Ok(string callbackId, string result)
              {
                  this.plugin.DispatchCommandResult(new PluginResult(PluginResult.Status.OK, result), callbackId);
              }
  
              public void Ok(string callbackId)
              {
                  this.plugin.DispatchCommandResult(new PluginResult(PluginResult.Status.OK), callbackId);
              }
  
              public void Error(string callbackId, string message)
              {
                  this.plugin.DispatchCommandResult(new PluginResult(PluginResult.Status.ERROR, message), callbackId);
              }
          }
  
          /// <summary>
          /// Maintain a single database with a thread/queue pair to feed requests to it
          /// </summary>
          public class DBRunner
          {
              private DatabaseManager databases;
              private SQLiteConnection db;
              public string DatabaseName { get; private set; }
              private readonly string openCalllbackId;
              private readonly Thread thread;
              private readonly BlockingQueue<DBQuery> queue;
  
              public DBRunner(DatabaseManager databases, string dbname, string cbc)
              {
                  this.databases = databases;
                  this.DatabaseName = dbname;
                  this.queue = new BlockingQueue<DBQuery>();
                  this.openCalllbackId = cbc;
                  this.thread = new Thread(this.Run) { Name = dbname };
              }
  
              public void Start()
              {
                  this.thread.Start();
              }
  
              public void WaitForStopped()
              {
                  this.thread.Join();
              }
  
              public void Enqueue(DBQuery dbq)
              {
                  this.queue.Enqueue(dbq);
              }
  
              private void Run()
              {
                  try
                  {
                      // As a query can be requested for the callback for "open" has been completed,
                      // the thread may not be started by the open request, in which case no open callback id
                      this.db = new SQLiteConnection(this.DatabaseName);
  
                      if (openCalllbackId != null)
                      {
                          this.databases.Ok(openCalllbackId);
                      }
                  }
                  catch (Exception ex)
                  {
                      LogError("Failed to open database " + DatabaseName, ex);
                      if (openCalllbackId != null)
                      {
                          this.databases.Error(openCalllbackId, "Failed to open database " + ex.Message);
                      }
                      databases.Finished(this);
                      return;
                  }
  
                  DBQuery dbq = queue.Take();
                  while (!dbq.Stop)
                  {
                      executeSqlBatch(dbq);
                      dbq = queue.Take();
                  }
  
                  if (!dbq.Delete)
                  {
                      // close and callback
                      CloseDatabaseNow(dbq.CallbackId);
                  }
                  else
                  {
                      // close, then delete and callback
                      CloseDatabaseNow(null);
                      DeleteDatabaseNow(this.databases, DatabaseName, dbq.CallbackId);
                  }
              }
  
              public void CloseDatabaseNow(string callBackId)
              {
                  bool success = true;
                  string error = null;
  
                  try
                  {
                      this.db.Close();
                  }
                  catch (Exception e)
                  {
                      LogError("couldn't close " + this.DatabaseName, e);
                      success = false;
                      error = e.Message;
                  }
  
                  if (callBackId != null)
                  {
                      if (success)
                      {
                          this.databases.Ok(callBackId);
                      }
                      else
                      {
                          this.databases.Error(callBackId, error);
                      }
                  }
              }
  
              // Needs to be static to support deleting closed databases
              public static void DeleteDatabaseNow(DatabaseManager databases, string dbname, string callBackId)
              {
                  bool success = true;
                  string error = null;
                  try
                  {
                      var folderPath = Path.GetDirectoryName(dbname);
                      if (folderPath == "")
                      {
                          folderPath = Windows.Storage.ApplicationData.Current.LocalFolder.Path;
                      }
                      var fileName = Path.GetFileName(dbname);
  
                      if (!System.IO.File.Exists(Path.Combine(folderPath, fileName)))
                      {
                          databases.Error(callBackId, "Database does not exist: " + dbname);
                      }
  
                      var fileExtension = new[] { "", "-journal", "-wal", "-shm" };
                      foreach (var extension in fileExtension)
                      {
                          var fullPath = Path.Combine(folderPath, fileName + extension);
                          System.IO.File.Delete(fullPath);
                      }
                  }
                  catch (Exception ex)
                  {
                      error = ex.Message;
                      success = false;
                  }
  
                  if (success)
                  {
                      databases.Ok(callBackId);
                  }
                  else
                  {
                      databases.Error(callBackId, error);
                  }
              }
  
              public void executeSqlBatch(DBQuery dbq)
              {
                  string batchResultsStr = "";
  
                  // loop through the sql in the transaction
                  foreach (SQLitePluginTransaction transaction in dbq.Queries)
                  {
                      string resultString = "";
                      string errorMessage = "unknown";
                      bool needQuery = true;
  
                      // begin
                      if (transaction.query.StartsWith("BEGIN", StringComparison.OrdinalIgnoreCase))
                      {
                          needQuery = false;
  
                          try
                          {
                              this.db.BeginTransaction();
  
                              resultString = "\"rowsAffected\":0";
  
                          }
                          catch (Exception e)
                          {
                              errorMessage = e.Message;
                          }
  
                      }
  
                      // commit
                      if (transaction.query.StartsWith("COMMIT", StringComparison.OrdinalIgnoreCase))
                      {
                          needQuery = false;
  
                          try
                          {
                              this.db.Commit();
  
                              resultString = "\"rowsAffected\":0";
                          }
                          catch (Exception e)
                          {
                              errorMessage = e.Message;
                          }
                      }
  
                      // rollback
                      if (transaction.query.StartsWith("ROLLBACK", StringComparison.OrdinalIgnoreCase))
                      {
                          needQuery = false;
  
                          try
                          {
                              this.db.Rollback();
  
                              resultString = "\"rowsAffected\":0";
                          }
                          catch (Exception e)
                          {
                              errorMessage = e.Message;
                          }
  
                      }
  
                      // create/drop table
                      if (transaction.query.IndexOf("DROP TABLE", StringComparison.OrdinalIgnoreCase) > -1 || transaction.query.IndexOf("CREATE TABLE", StringComparison.OrdinalIgnoreCase) > -1)
                      {
                          needQuery = false;
  
                          try
                          {
                              var results = db.Execute(transaction.query, transaction.query_params);
  
                              resultString = "\"rowsAffected\":0";
                          }
                          catch (Exception e)
                          {
                              errorMessage = e.Message;
                          }
  
                      }
  
                      // insert/update/delete
                      if (transaction.query.StartsWith("INSERT", StringComparison.OrdinalIgnoreCase) ||
                          transaction.query.StartsWith("UPDATE", StringComparison.OrdinalIgnoreCase) ||
                          transaction.query.StartsWith("DELETE", StringComparison.OrdinalIgnoreCase))
                      {
                          needQuery = false;
  
                          try
                          {
                              // execute our query
                              var res = db.Execute(transaction.query, transaction.query_params);
  
                              // get the primary key of the last inserted row
                              var insertId = SQLite3.LastInsertRowid(db.Handle);
  
                              resultString = String.Format("\"rowsAffected\":{0}", res);
  
                              if (transaction.query.StartsWith("INSERT", StringComparison.OrdinalIgnoreCase))
                              {
                                  resultString += String.Format(",\"insertId\":{0}", insertId);
                              }
  
                          }
                          catch (Exception e)
                          {
                              errorMessage = e.Message;
                          }
                      }
  
                      if (needQuery)
                      {
                          try
                          {
                              var results = this.db.Query2(transaction.query, transaction.query_params);
  
                              string rowsString = "";
  
                              foreach (SQLiteQueryRow res in results)
                              {
                                  string rowString = "";
  
                                  if (rowsString.Length != 0) rowsString += ",";
  
                                  foreach (SQLiteQueryColumn column in res.column)
                                  {
                                      if (rowString.Length != 0) rowString += ",";
  
                                      if (column.Value != null)
                                      {
                                          if (column.Value.GetType().Equals(typeof(Int32)))
                                          {
  
gt:                                             rowString += String.Format("\"{0}\":{1}",