/*
 * OFFLINE BIBLE - DATABASE
 * Operations to build a MySQL database and populate it
 * with Bible data.
 *
 * You are free to modify or redistribute this code and
 * any derived works, but where possible please do so
 * without charge. Thanks.
 *
 * Nathan Kitchen (offlinebible.com)
 */
function GearsConfig(database)
{
  var _database = database;
  var _built = false;

  function build()
  {
    _database.execute("CREATE TABLE IF NOT EXISTS settings (key varchar(128), value text);");
    _built = true;
  }

  this.getItem = function(key, def)
  {
    if (!_built) { build(); }
    if (!_database) { return def; }
    var rs = _database.execute("SELECT value FROM settings WHERE key = ?;", [key]);
    return (rs.isValidRow() && rs.field(0) != undefined) ? rs.field(0): def;
  }

  this.setItem = function(key, value)
  {
    if (!_built) { build(); }
    if (!_database) { return false; }
    var rs = _database.execute("SELECT 1 FROM settings WHERE key = ?;", [key]);
    if (rs.isValidRow())
    {
       _database.execute("UPDATE settings SET value = ? WHERE key = ?;", [value, key]);
    }
    else
    {
      _database.execute("INSERT INTO settings VALUES (?, ?);", [key, value]);
    }
    return true;
  }

  this.removeItem = function(key)
  {
    if (!_built) { build(); }
    _database.execute("DELETE FROM settings WHERE key = ?;", [key]);
  }
}

function BibleDatabase()
{
  var _database = null;

  if (window.google && google.gears) { _database = google.gears.factory.create('beta.database'); }

  if (_database)
  {
    _database.open("OfflineBible");

    var _settings = new GearsConfig(_database);
    //if (typeof localStorage != "undefined") { _settings = localStorage; }
    //else if (typeof globalStorage != "undefined") { globalStorage[location.hostname]; }
    //else if (_database) { _settings = new GearsConfig(_database); }
  }

  this.executeSql = function(sql, parameters)
  {
    return _database.execute(sql, parameters);
  }

  this.isEmpty = function()
  {
    return true;
  }

  BibleDatabase.prototype.getManifest = function(name)
  {
    var rs = null;
    try
    {
      rs = _database.execute("SELECT name, url, major, minor, schema, force FROM manifests WHERE name=?;", [name]);

      if (rs.isValidRow())
      {
        return { "name": rs.field(0), "url": rs.field(1), "major": parseInt(rs.field(2)), "minor": parseInt(rs.field(3)), "schema": parseInt(rs.field(4)), "force": rs.field(5) };
      }
      else
      {
        return { "name": name, "url": null, "major": 0, "minor": 0, "schema": 0, "force": 0 };
      }
    }
    catch (err)
    {
      return { "name": name, "url": null, "major": 0, "minor": 0, "schema": 0, "force": 0 };
    }
    finally
    {
      if (rs) { rs.close(); }
    }
  }

  this.getConfig = function(key, def)
  {
    if (!_settings) { return def; }
    var item = _settings.getItem(key);
    return (item) ? item : def;
  }

  this.setConfig = function(key, value)
  {
    if (!_settings) { return false; }
    return _settings.setItem(key, value);
  }

  this.delConfig = function(key)
  {
    if (!_settings) { return false; }
    return _settings.removeItem(key);
  }

  function buildClause(count, term, separator)
  {
    var clause = "";
    for (var i=0; i<count; i++)
    {
      clause += term;
      if (i < count -1)
      {
        clause += separator;
      }
    }
    return clause;
  }

  function keywordJoins(termCount, sum)
  {
    var sumOccurences = "";
    var searchJoins = "";
    var keywordJoins = "";
    for (var i=0; i<termCount; i++)
    {
      var j = i + 1;
      if (sum)
      {
        sumOccurences += "SUM(sk" + j + ".occurences)";
        if (i < termCount - 1) { sumOccurences += " + "; }
      }
      searchJoins += "INNER JOIN kjv_search_keywords sk" + j + " ON sk" + j + ".verse_id = v.id ";
      keywordJoins += "INNER JOIN kjv_keywords k" + j + " ON sk" + j + ".term_id = k" + j + ".id AND k" + j + ".word = ? ";
    }
    return sumOccurences + " FROM kjv_verses v " + searchJoins + keywordJoins;
  }

  BibleDatabase.SPROCS =
    {
      "GetVersesByChapterId":     function () { return "SELECT verse_num, tokens FROM kjv_verses WHERE chapter_id = ?;"; },
      "KeywordSearchAll":         function (termCount) { var sproc = "SELECT [v].[id], [v].[raw], " + keywordJoins(termCount, true) + "GROUP BY [v].[id] LIMIT ? OFFSET ?;"; return sproc; },
      "KeywordSearchFilterOT":    function (termCount) { var sproc = "SELECT [v].[id], [v].[raw], " + keywordJoins(termCount, true) + "GROUP BY [v].[id] HAVING [v].[book_id] < 40 LIMIT ? OFFSET ?;"; return sproc; },
      "KeywordSearchFilterNT":    function (termCount) { var sproc = "SELECT [v].[id], [v].[raw], " + keywordJoins(termCount, true) + "GROUP BY [v].[id] HAVING [v].[book_id] > 39 LIMIT ? OFFSET ?;"; return sproc; },
      "KeywordSearchFilterBook":  function (termCount) { var sproc = "SELECT [v].[id], [v].[raw], " + keywordJoins(termCount, true) + "GROUP BY [v].[id] HAVING [v].[book_id] = ? LIMIT ? OFFSET ?;"; return sproc; },
      "KeywordSearchOccurences":  function (termCount) { var sproc = "SELECT [v].[book_id], COUNT(DISTINCT [v].[id]), " + keywordJoins(termCount, true) + "GROUP BY [v].[book_id]"; return sproc; },
      "KeywordSearchMatchesOT":   function (termCount) { var sproc = "SELECT COUNT(DISTINCT v.id) " + keywordJoins(termCount, false) + "WHERE [v].[book_id] < 40;"; return sproc; },
      "KeywordSearchMatchesNT":   function (termCount) { var sproc = "SELECT COUNT(DISTINCT v.id) " + keywordJoins(termCount, false) + "WHERE [v].[book_id] > 39;"; return sproc; },
      "KeywordSearchMatchesBook": function (termCount) { var sproc = "SELECT COUNT(DISTINCT v.id) " + keywordJoins(termCount, false) + "WHERE [v].[book_id] = ?;"; return sproc; }
    };
}