var EXPORTED_SYMBOLS = ['AppCenterDAO'];

var APPCENTER_VERSION = 1;
var DATABASE_FILE = 'livemargins_appcenter.sqlite';
var SQLBATCH_CREATE_FILE = 'resource://livemargins/sql/sqlbatch_create.sql';
var LM3_DATABASE_FILE ='favpart_manager_2.9.0.sqlite';
var VERSION = 1;

const Cu = Components.utils;
const Cc = Components.classes;
const Ci = Components.interfaces;

Cu.import("resource://gre/modules/XPCOMUtils.jsm");

XPCOMUtils.defineLazyGetter(this, "utils", function() {
  let obj = { };
  Cu["import"]("resource://livemargins/utils.jsm", obj);
  return obj.utils;
});

XPCOMUtils.defineLazyGetter(this, "BaseDAO", function() {
  let obj = { };
  Cu["import"]("resource://livemargins/basedao.jsm", obj);
  return obj.BaseDAO;
});

XPCOMUtils.defineLazyGetter(this, "logger", function() {
  return utils.logger('LM4.AppCenterDAO');
});

XPCOMUtils.defineLazyGetter(this, "dirService", function() {
  return Cc['@mozilla.org/file/directory_service;1'].getService(Ci.nsIProperties);
});

XPCOMUtils.defineLazyGetter(this, "storageService", function() {
  return Cc['@mozilla.org/storage/service;1'].getService(Ci.mozIStorageService);
});

if (!Function.prototype.bind) {
  Function.prototype.bind = function (context) {
    var __method = this, args = Array.prototype.slice.call(arguments, 1);
    return function() {
      return __method.apply(context, args.concat(Array.prototype.slice.call(arguments, 0)));
    }
  };
}

function makeObjAccessible(obj) {
  return utils.exposeReadOnly(obj);
}

var file = utils.getCreateProFile(['appcenter', DATABASE_FILE]);
var lm3File = utils.getCreateProFile([LM3_DATABASE_FILE]);
var AppCenterDAO = new BaseDAO(file);
var oldLm3DAO = new BaseDAO(lm3File);
AppCenterDAO = utils.extend(AppCenterDAO, {
  init: function() {
    var self = this;
    function _createTables() {
      if (file.fileSize == 0) {
        var sqlBatch = utils.getContentFromURL(SQLBATCH_CREATE_FILE);
        self.connection.executeSimpleSQL(sqlBatch);
        if(lm3File.exists() && lm3File.fileSize > 0) {
          var uuidGenerator = Components.classes["@mozilla.org/uuid-generator;1"].getService(Components.interfaces.nsIUUIDGenerator);
          var sql = "SELECT id, name, widgetUrl, widgetArgs, screenX, screenY, strftime('%s', timeAdd) * 1000 addTime, strftime('%s', timeMod) * 1000 modTime, strftime('%s', lastOpen) * 1000 lastOpened, bookmark, startWithFirefox, windowStates, iconUrl FROM widgets WHERE channel  != 1";
          var results = oldLm3DAO.query(sql, []);
          for (var j = 0; j < results.length; j++) {
            var uuid = uuidGenerator.generateUUID();
            var uuidString = uuid.toString();
            var widgetUrl = results[j]['widgetUrl'];
            var lm3type = 0;
            if (widgetUrl == "chrome://livemargins/content/playerWindow.xul") {
              lm3type = 1;
            } else if (widgetUrl == "chrome://livemargins/content/videoPlayer.xul") {
              lm3type = 3;
            }

            var app = {
              appUID    : uuidString,
              appName   : results[j]['name'],
              app_args  : results[j]['widgetArgs'],
              type    : lm3type,
              description : '',
              bookmark  : results[j]['bookmark'],
              iconUrl   : results[j]['iconUrl'],
              createTime  : results[j]['addTime'],
              is_local  : 1
            };
            AppCenterDAO.addAppFromLM3(app);

            var appInfo = {
              uid     : uuidString,
              app_id    : self.getIDFromAppUID(uuidString),
              screen_x  : results[j]['screenX'],
              screen_y  : results[j]['screenY'],
              auto_start  : results[j]['startWithFirefox'],
              last_opened : results[j]['lastOpened'],
              last_modified : results[j]['modTime'],
              app_status  : results[j]['windowStates'],
              thumbnail   : ''
            };
            AppCenterDAO.addOrUpdateAppState(appInfo);
          }
        }
      } else {
        // List all files match pattern: update_\d.sql, \d is the sql update version.
        // Compare metadata.version and pref sqlversion, execute all update sql between them.
        // For example, if metadata.version is 1, adn pref sqlversion is 3,
        // Then two update sql will be exected: update_2.sql and update_3.sql
        try {
          var sqlVersion = utils.getPref("sqlversion");
          var curVersion = self.getVersion();
          for (var i = curVersion + 1; i <= sqlVersion; i++) {
            var sqlUpdateFile = 'resource://livemargins/sql/update_' + i + '.sql';
            var sqlBatch = utils.getContentFromURL(sqlUpdateFile);
            self.connection.executeSimpleSQL(sqlBatch);
          }
        } catch (e) {
          dump(e);
        }
      }
    }

    try {
      _createTables();
    } catch (e) {
      Components.utils.reportError("AppCenter database appears to have been corrupted - resetting it." +
                this.connection.lastErrorString);
      if (file.exists()) {
        file.remove(false);
      }
      _createTables();
    }
  },

  getVersion: function() {
    var value = this.getMetadata('version');
    if (!value) {
      return 0;
    } else {
      return parseInt(value);
    }
  },

  getMetadata: function(key) {
    var data = this.query('SELECT value FROM metadata WHERE key = :key', {
      key: key
    });

    if (data.length == 1) {
      return data[0].value;
    }

    return null;
  },

  setMetadata: function(key, value) {
    var sql = 'INSERT OR REPLACE INTO metadata (`key`, `value`) VALUES (:key, :value)';
    return this.executeSQL(sql, {
      key: key,
      value: value
    });
  },

  appExists: function(appUID) {
    var results = this.query('SELECT COUNT(*) as count FROM apps WHERE uid = :uid;', {
      uid: appUID
    });

    return results[0].count == 1;
  },


  getIDFromAppUID: function(appUID) {
    var appInfo = this.query('SELECT id FROM apps WHERE apps.uid = :uid', {
      uid: appUID
    });

    if (appInfo.length == 0) {
      return null;
    }

    return appInfo[0].id;
  },

  getInfoIDFromAppID: function(appID) {
    var appInfo = this.query('SELECT id FROM app_info WHERE apps.app_id = :app_id', {
      app_id: appID
    });

    if (appInfo.length == 0) {
      return null;
    }

    return appInfo[0].id;
  },

  getAppPositionByUId: function(uid) {
    var app = this.query('SELECT position FROM apps WHERE uid = :uid', {
      uid: uid
    });

    if (app.length == 0) {
      return null;
    }

    return app[0].position;
  },

  getAppArgsByUID: function(uid) {
    var appArgs = this.query("SELECT app_args FROM apps WHERE uid = :uid", {
      uid: uid
    });

    if (appArgs.length == 0) {
      return null;
    }

    return appArgs[0].app_args;
  },

  getAppByUID: function(appUID) {
    var app = this.query('SELECT * FROM apps WHERE uid = :uid', {
      uid: appUID
    });

    return app.length == 0 ? null : app[0];
  },

  getAppsAutoStart: function() {
    let sql = "SELECT apps.uid FROM apps LEFT JOIN app_info ON apps.id = app_info.app_id WHERE app_info.auto_start = 1";
    return this.query(sql);
  },

  getNonBuiltInAppByUID: function(appUID) {
    var app = this.query('SELECT * FROM apps WHERE type != 0 AND uid = :uid', {
      uid: appUID
    })

    return app.length == 0 ? null : app[0];
  },

  getAppInfoById: function(id) {
    var appInfo = this.query('SELECT * FROM app_info WHERE app_id = :app_id', {
      app_id: id
    });

    return appInfo.length == 0 ? null : appInfo[0];
  },

  getAppInfoByUID: function(appUID) {
    var appInfo = this.query('SELECT app_info.*, apps.id as app_id FROM apps LEFT JOIN app_info ON apps.id = app_info.app_id WHERE apps.uid = :uid', {
      uid: appUID
    });
    return appInfo.length == 0 ? null : appInfo[0];
  },

  increateAppVisits: function(appUID) {
    return this.executeSQL("UPDATE APPS SET visits = visits + 1 WHERE uid = :uid", {
      uid: appUID
    });
  },

  getAppName: function(appUID) {
    var apps = this.query('SELECT name FROM apps WHERE uid = :uid', {
      uid: appUID
    });

    return apps.length == 0 ? null : apps[0].name;
  },

  getAppType: function(appUID) {
    var types = this.query('SELECT type FROM apps WHERE uid = :uid', {
      uid: appUID
    });

    return types.length == 0 ? -1 : types[0].type;
  },

  changeAppName: function(appUID, name) {
    return this.executeSQL('UPDATE apps SET name = :name WHERE uid = :uid', {
      uid: appUID,
      name: name
    });
  },

  deleteAppByUID: function(appUID) {
    var id = this.getIDFromAppUID(appUID);
    if (!id) {
      return;
    }

    this.executeSQL('DELETE FROM app_info WHERE app_id = :app_id', {
      app_id: id
    });

    return this.executeSQL('DELETE FROM apps WHERE uid = :uid', {
      uid: appUID
    });
  },

  resetAppOpenedTime: function() {
    return this.executeSQL('UPDATE app_info SET last_opened = NULL');
  },

  resetOpenedTimeByUID: function(uid) {
    var id = this.getIDFromAppUID(uid);
    if (!id) {
      return;
    }

    return this.executeSQL('UPDATE app_info SET last_opened = NULL WHERE app_id = :app_id', {
      app_id: id
    });
  },

  getAppTypeByUID: function(appUID) {
    var types = this.query('SELECT type FROM apps WHERE uid = :uid', {
      uid: appUID
    });

    return types.length != 0 ? types[0].type : null;
  },

  addApp: function(appInfo) {
    var sql = [];
    sql.push('INSERT INTO apps');
    sql.push(' (`uid`, `name`, `app_args`, `type`, `description`, `bookmark`, `icon_url`, `small_icon`, `create_time`, `is_local`, `position`) ');
    sql.push(' SELECT ');
    sql.push(' :uid, :name, :app_args, :type, :description, :bookmark, :icon_url, :small_icon, strftime(\'%s\',\'now\') * 1000, :is_local, MAX(position) + 1 FROM apps ');

    return this.executeSQL(sql.join(''), {
      uid:   appInfo.appUID,
      name:  appInfo.appName,
      app_args:  JSON.stringify(appInfo.appArgs),
      type:    appInfo.type,
      description: appInfo.description,
      bookmark:  appInfo.bookmark,
      icon_url:  appInfo.iconUrl,
      is_local:  appInfo.is_local,
      small_icon:  appInfo.smallIcon
    });
  },

  updateApp: function(app) {
    var sql = [];
    sql.push("UPDATE apps SET ");
    sql.push(" app_args=:app_args, icon_url=:icon_url, small_icon=:small_icon, icon_data_url=:icon_data_url");
    sql.push(" WHERE uid = :uid");

    return this.executeSQL(sql.join(''), {
      uid:       app.uid,
      app_args:    app.app_args,
      icon_url:    app.icon_url,
      icon_data_url: app.icon_data_url,
      small_icon:  app.small_icon
    });
  },

  updateAppIntent: function(intent) {
    logger.trace('updateAppIntent: ' + JSON.stringify(intent));
    var existed = this.query('SELECT labelStr FROM app_intent WHERE action = :action AND type = :type', {
      action: intent.action,
      type: intent.type
    });
    if (existed.length) {
      if (!existed[0].labeStr && intent.labelStr) {
        this.executeSQL('UPDATE app_intent SET labelStr = :labelStr WHERE action = :action AND type = :type', {
          action: intent.action,
          labelStr: intent.labelStr,
          type: intent.type
        });
      }
      if (intent.icon && !/^http/.test(intent.icon)) {
        this.executeSQL('UPDATE app_intent SET icon = :icon WHERE action = :action AND type = :type', {
          action: intent.action,
          icon: intent.icon,
          type: intent.type
        });
      }
      return true;
    } else {
      var sort_key = this.query('SELECT MAX(sort_key) FROM app_intent WHERE type = :type', {
        type: intent.type
      });
      sort_key = sort_key[0]["MAX(sort_key)"] + 1;
      intent.sort_key = sort_key;
      return this.executeSQL('INSERT INTO app_intent (`action`, `message`, `type`, `label`, `labelStr`, `icon`, `sort_key`) VALUES (:action, :message, :type, :label, :labelStr, :icon, :sort_key)', intent);
    }
  },

  updateAppIntents: function(appInfo) {
    for (var message in appInfo.intent) {
      for (var i = 0, l = appInfo.intent[message].length; i < l; i++) {
        var types = appInfo.intent[message][i].type;
        for (var j = 0, m = types.length; j < m; j++) {
          var type = types[j];
          this.updateAppIntent({
            action: appInfo.appUID,
            message: message,
            type: type,
            label: appInfo.intent[message][i].label,
            labelStr: appInfo.intent[message][i].labelStr || "",
            icon: appInfo.smallIcon,
          });
        }
      }
    }
  },

  addAppFromLM3: function(appInfo) {
    var sql = [];
    sql.push('INSERT INTO apps');
    sql.push(' (`uid`, `name`, `app_args`, `type`, `description`, `bookmark`, `icon_url`, `create_time`, `is_local`) ');
    sql.push(' VALUES ');
    sql.push(' (:uid, :name, :app_args, :type, :description, :bookmark, :icon_url, :create_time, :is_local) ');
    return this.executeSQL(sql.join(''), {
      uid:   appInfo.appUID,
      name:  appInfo.appName,
      app_args:  appInfo.app_args,
      type:    appInfo.type,
      description: appInfo.description,
      bookmark:  appInfo.bookmark,
      icon_url:  appInfo.iconUrl,
      create_time : appInfo.createTime,
      is_local:  appInfo.is_local
    });
  },

  getAllNonBuiltInApps: function() {
    return this.query('SELECT * FROM apps WHERE type != 0');
  },

  getAllApps: function() {
    return this.query('SELECT * FROM apps');
  },

  getBookmarkedApps: function(start, size) {
    if (isNaN(start) || isNaN(size)) {
      throw Error("args should be an number");
    }
    return this.query('SELECT * FROM apps WHERE bookmark = 1 ORDER BY position, id LIMIT ' + start + ', ' + size);
  },

  getMostVisitedApps: function(size) {
    if (isNaN(size)) {
      throw Error("args should be an number");
    }
    return this.query("SELECT * FROM apps ORDER BY visits DESC, id LIMIT 0, " + size);
  },

  getAllBookmarkedApps: function() {
    return this.query('SELECT * FROM apps WHERE bookmark = 1 ORDER BY position, id');
  },

  getBookmarkedAppCount: function() {
    return this.query("SELECT COUNT(*) as count FROM apps WHERE bookmark = 1")[0].count;
  },

  getAllRecentApps: function() {
    var sql = [];
    sql.push('SELECT apps.*, app_info.last_opened as last_opened FROM app_info');
    sql.push('JOIN apps');
    sql.push('ON apps.id = app_info.app_id AND last_opened IS NOT NULL');
    sql.push('ORDER BY last_opened DESC');

    return this.query(sql.join(' '));
  },

  getRecentApps: function(start, size) {
    if (isNaN(start) || isNaN(size)) {
      throw Error("args should be an number");
    }

    var sql = [];
    sql.push('SELECT apps.*, app_info.last_opened as last_opened FROM app_info');
    sql.push('JOIN apps');
    sql.push('ON apps.id = app_info.app_id AND last_opened IS NOT NULL');
    sql.push('ORDER BY last_opened DESC');
    sql.push('LIMIT ' + start + ', ' + size);
    return this.query(sql.join(' '));
  },

  getRecentAppsCount: function() {
    var result = this.query("SELECT COUNT(*) as total FROM app_info WHERE last_opened IS NOT NULL");
    return result[0].total;
  },

  getAppBookmarkFlag: function(uid) {
    var bookmarks = this.query('SELECT bookmark FROM apps WHERE uid = :uid', {
      uid: uid
    });

    return bookmarks.length != 0 ? bookmarks[0].bookmark : null;
  },

  updateAppBookmark: function(uid, bookmark) {
    return this.executeSQL('UPDATE apps SET bookmark = :bookmark WHERE uid = :uid', {
      uid: uid,
      bookmark: bookmark
    });
  },

  updateAppArgs: function(uid, app_args) {
    return this.executeSQL("UPDATE apps SET app_args = :app_args WHERE uid = :uid", {
      uid: uid,
      app_args: app_args
    });
  },

  updateIconDataUrl: function(uid, iconDataUrl) {
    return this.executeSQL("UPDATE apps SET icon_data_url = :icon_data_url WHERE uid = :uid", {
      uid: uid,
      icon_data_url: iconDataUrl
    });
  },

  setAppAutoStart: function(uid, autoStart) {
    var id = this.getIDFromAppUID(uid);
    if (!id) {
      return;
    }

    return this.executeSQL("UPDATE app_info SET auto_start = :auto_start WHERE app_id = :app_id", {
      auto_start: autoStart ? 1 : 0,
      app_id: id
    });
  },

  clearAppAutoStart: function() {
    return this.executeSQL("UPDATE app_info SET auto_start = 0");
  },

  updateAppLocal: function(uid, isLocal) {
    return this.executeSQL('UPDATE apps SET is_local = :is_local WHERE uid = :uid', {
      uid: uid,
      is_local: isLocal
    });
  },

  addOrUpdateAppState: function(appInfo) {
    var _info = AppCenterDAO.getAppInfoByUID(appInfo.uid);
    if (!_info) {
      return;
    }

    var args = null;
    var sql = [];

    if (!_info.id) {
      sql.push('INSERT INTO app_info ')
      sql.push('(app_id, app_args, screen_x, screen_y, auto_start, last_opened, last_modified, app_status, thumbnail)');
      sql.push(' VALUES ');
      sql.push('(:app_id, :app_args, :screen_x, :screen_y, :auto_start, :last_opened, :last_modified, :app_status, :thumbnail)');

      args = utils.extend({
        app_id:    _info.app_id,
        app_args:    null,
        screen_x:    0,
        screen_y:    0,
        auto_start:  0,
        last_opened:   new Date().getTime(),
        last_modified: null,
        app_status:  null,
        thumbnail:   null
      }, appInfo);

      args = {
        app_id:    args.app_id,
        app_args:    args.app_args && typeof args.app_args == "object" ? JSON.stringify(args.app_args) : args.app_args,
        screen_x:    args.screen_x,
        screen_y:    args.screen_y,
        auto_start:  args.auto_start,
        last_opened:   args.last_opened,
        last_modified: args.last_modified,
        app_status:  args.app_status && typeof args.app_status == "object" ? JSON.stringify(args.app_status) : args.app_status,
        thumbnail:   args.thumbnail
      };
    } else {
      sql.push('UPDATE app_info ')
      sql.push('SET ');
      sql.push('app_args = :app_args, screen_x = :screen_x, ')
      sql.push('screen_y = :screen_y, auto_start = :auto_start, ');
      sql.push('last_opened = :last_opened, last_modified = :last_modified, app_status = :app_status, thumbnail = :thumbnail');
      sql.push(' WHERE app_id = :app_id');

      args = utils.extend(_info, appInfo);
      // params have to be exactly same as params in SQL statements.
      // Or unknow error would be thrown when executing SQL
      args  = {
        app_id: args.app_id,
        app_args: args.app_args && typeof args.app_args == "object" ? JSON.stringify(args.app_args) : args.app_args,
        screen_x: args.screen_x,
        screen_y: args.screen_y,
        auto_start: args.auto_start,
        last_opened: args.last_opened,
        last_modified: args.last_modified,
        app_status: args.app_status && typeof args.app_status == "object" ? JSON.stringify(args.app_status) : args.app_status,
        thumbnail: args.thumbnail
      };
    }
    AppCenterDAO.executeSQL(sql.join(''), args);
  },

  keepHistoryList: function(size) {
    // Keep history list less than size
    var HISTORY_LIST_SIZE = 10;
    AppCenterDAO.executeSQL("UPDATE app_info SET last_opened = NULL WHERE id IN (SELECT id FROM app_info  WHERE last_opened > 0 ORDER BY last_opened DESC LIMIT " + parseInt(size) + ", 1000)");
  },

  moveAppByUID: function(uid, oriPos, newPos) {
    this.moveApp(this.getIDFromAppUID(uid), oriPos, newPos)
  },

  moveApp: function(app_id, newPos) {
    if (isNaN(app_id) || isNaN(newPos)) {
      return;
    }

    var sqls = [];

    // create temp table
    sqls.push('CREATE TEMP TABLE IF NOT EXISTS app_reindex_tmp ( ' +
      'id INTEGER PRIMARY KEY AUTOINCREMENT, ' +
      'app_id INTEGER ' +
      ')');

    // insert apps id into temp table to generate position number
    if (newPos <= 1) {
      sqls.push('INSERT INTO app_reindex_tmp (app_id) SELECT id FROM apps WHERE id = ' + app_id);
      sqls.push('INSERT INTO app_reindex_tmp (app_id) SELECT id FROM apps WHERE id != ' + app_id + ' ORDER BY position;');
    } else {
      sqls.push('INSERT INTO app_reindex_tmp (app_id) SELECT id FROM apps WHERE position <= ' + newPos + ' AND id != ' + app_id + ' ORDER BY position;');
      sqls.push('INSERT INTO app_reindex_tmp (app_id) SELECT id FROM apps WHERE id = ' + app_id);
      sqls.push('INSERT INTO app_reindex_tmp (app_id) SELECT id FROM apps WHERE position > ' + newPos + ' AND id != ' + app_id + ' ORDER BY position;')
    }

    // create index on app_id
    sqls.push('CREATE INDEX IF NOT EXISTS app_reindex_tmp_index ' +
      'ON app_reindex_tmp(app_id)');

    // create trigger to update position
    sqls.push('CREATE TEMP TRIGGER IF NOT EXISTS app_reindex_tmp_trigger ' +
      'BEFORE DELETE ON app_reindex_tmp ' +
      'FOR EACH ROW ' +
      'BEGIN ' +
      'UPDATE apps SET position = OLD.id WHERE id = OLD.app_id; ' +
      'END ');

    // delete from temp table and make clearing task.
    sqls.push('DELETE FROM app_reindex_tmp;');
    sqls.push('DROP TRIGGER app_reindex_tmp_trigger; ');
    sqls.push('DROP INDEX app_reindex_tmp_index;');
    sqls.push('DROP TABLE app_reindex_tmp; ');

    this.executeAync(sqls, function(aError) {
      logger.error(aError.message);
    }, function(aReason) {
      logger.info(aReason);
    });
  },

  getIntentsByType: function(intent_type, limit) {
    if (limit == undefined) {
      limit = -1;
    }
    var data = this.query('SELECT action, message, type, label, labelStr, icon FROM app_intent WHERE type = :type ORDER BY sort_key LIMIT :limit', {
      type: intent_type,
      limit: limit
    });

    return data;
  },

  replaceTextIntent: function(index, intent_action) {
    // 'text/*' decides display orders for 'text/html' etc.
    var sort_key = this.query('SELECT sort_key FROM app_intent WHERE type = :type AND action = :action', {
      action: intent_action,
      type: 'text/*'
    });
    if (sort_key.length) {
      sort_key = sort_key[0].sort_key;
      this.executeSQL("UPDATE app_intent SET sort_key = :temp_key WHERE type = :type AND sort_key = :sort_key", {
        sort_key: index,
        temp_key: -1,
        type: 'text/*'
      });
      this.executeSQL("UPDATE app_intent SET sort_key = :sort_key WHERE type = :type AND action = :action", {
        action: intent_action,
        sort_key: index,
        type: 'text/*'
      });
      this.executeSQL("UPDATE app_intent SET sort_key = :sort_key WHERE type = :type AND sort_key = :temp_key", {
        temp_key: -1,
        sort_key: sort_key,
        type: 'text/*'
      });

    }
  }
});

AppCenterDAO.init();

