var mysql = require('mysql');

var serverDB = (function(connectionData) {
    var queries = {
        markRead    : "UPDATE messages SET readable = TRUE WHERE message_id = ?",
        getHistory  : "SELECT * FROM messages WHERE (date_time >= ?) AND ((from_login = ? AND to_login = ?) OR (from_login = ? AND to_login = ?) ) ORDER BY date_time",
        getUG       : "SELECT * FROM users; SELECT * FROM groups",
        login       : "SELECT 1 FROM users WHERE login = ? AND password = ?",
        userExists  : "SELECT 1 FROM users WHERE login = ?",
        insertMess  : "INSERT INTO messages SET ?",
    };

    var connData = connectionData;
    connData.multipleStatements = true;

    var mysqlConnection = mysql.createConnection (connectionData);
    mysqlConnection.connect ( function (error) {
        if (error)
            throw error;
        console.log ('Successfully connected to MySQL server.');
    });


    function _messageSetRead(id) {
        var query = util.format (queries.markRead, id);
        mysqlConnection.query (query, function (err) {
            if (err)
                throw err;
        });
    }

    function _getHistory (loginFrom, loginTo, dateAfter, callback) {
        mysqlConnection.query (
                queries.getHistory,
                [dateAfter, loginFrom, loginTo, loginTo, loginFrom],
                function (err, rows) {
                    if (err)
                        throw err;
                    callback (rows);
                }
        );
    }

    function _getUG(callback) {
        mysqlConnection.query (queries.getUG, function (err, result) {
            if (err)
                throw err;
            callback (result[0], result[1]);
        });
    }

    function _checkUser(login, password, callback){
        mysqlConnection.query (queries.login, [login, password], function (err, result) {
            if (err)
                throw err;
            callback (result.length !== 0);
        });
    }

    function _insertMessage (from, to, message, time, callback){
        var insertSet = {
            'from_login' : from,
            'to_login' : to,
            'message' : message,
            'date_time' : time
        };

        mysqlConnection.query (queries.insertMess, insertSet, function (err, result) {
            if (err)
                throw err;
            callback ();
        });
    }

    function _userExists (login, callback){
        mysqlConnection.query (queries.userExists, login, function (err, result) {
            if(err)
                throw err;
            callback (result.length !== 0);
        });
    }

    return {
        messageSetRead : _messageSetRead,
        getHistory : _getHistory,
        getUG : _getUG,
        checkUser : _checkUser,
        insertMessage : _insertMessage,
        userExists : _userExists
    };

    // ADMIN FUNCTIONS
})({
     host     : '178.62.178.14',
     user     : 'root',
     password : 'databasepassword',
     database : 'corporative_messenger'
});