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)));
        }
    };
}

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() {
        return this.query("SELECT apps.uid FROM apps LEFT JOIN app_info ON apps.id = app_info.app_id WHERE app_info.auto_start = 1");
    },
    
    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
        });
    },
    
    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);
        });
    }
});

AppCenterDAO.init();