SyrupDB

const mysql = require('mysql');
var shortid = require('shortid');
//shortid.characters('0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ-_');
const objectAssign = require('object-assign');
var SqlString = require('sqlstring');
var escapeJSON = require('escape-json-node');
const bcrypt = require('bcrypt');
const saltRounds = 10;

var inherits = require('util').inherits;

var wrapper = {
    /* .create(sqlopts, callback)
       sqlopts = {
           host:
           username:
           password:
           database:
       }
    */
    create: function (sqlopts) {
        var buffer = objectAssign({}, syrupdb);
        buffer.sqlopts = sqlopts
        return buffer;
    }
}
module.exports = wrapper;

var syrupdb = {
    // .connect(callback(err))
    connect: function (cb, opt) {
        //connect to database
        this.connection = mysql.createPool(this.sqlopts);
        this.isConnected = true;
        //this.connection.connect(function(err) {
            cb(false);
        //});
    },
    sqlopts: {},

    createTable: function (name, cachetime, columns, cb) {
        if (typeof this.tables[name] == 'undefined') {
            this.tables[name] = new table(name, cachetime, columns, this, false);
            this.tables[name].save(() => {
                if (typeof cb !== 'undefined') {cb(false, this.tables[name]);}
            });
        }
    },

    createMemTable: function (name, columns) {
        if (typeof this.tables[name] == 'undefined') {
            this.tables[name] = new table(name, -1, columns, this, true);
            return this.tables[name]
        }
    },
    tables: {},
    connection: {}
}

//TABLE CONSTRUCTOR
function table(name, cachetime, columns, database, memonly){
    var buffer = this;
    if (typeof memonly == 'undefined') {memonly = false;}
    this.name = name;
    this.keys = {};
    this.cachetime = cachetime;
    this.columns = columns;
    this.database = database;
    this.memonly = memonly;
    this.rowPrototype = row;
    this.layout = {}
    Object.keys(columns).forEach((i) => {
        buffer.layout[columns[i]['name']] = columns[i];
        if (typeof buffer.layout[columns[i]['name']]['default'] == 'undefined') {buffer.layout[columns[i]['name']]['default'] = ''}
        //console.log('layout.' + columns[i]['name'] + ' = ')
        //console.log(columns[i])
    });
    //console.log('Layout for table ' + this.name)
    //console.log(this.layout)
}
table.prototype.getKeys = function () {
    //Load row keys
}
table.prototype.get = function (column, data, cb, isUnique) {
    //console.log('getting')
    //Search memory and then pull from SQL if can't find
    var buffer = this;
    var ret = [];
    var flag = true;
    if (typeof isUnique == 'undefined'){isUnique = true}
    if (isUnique){
        Object.keys(this.keys).forEach((i) => {
            if (buffer.keys[i][column] === data){
                ret.push(buffer.keys[i]);
                if (isUnique) {flag = false;cb(false, ret[0]);return false}
            }
        })
    }
    if (this.memonly && flag){
        if (isUnique) {cb(false, ret[0]);} else {cb(false, ret);}
    } else {
        if (flag){
            //console.log('moving to sql')
            ret = [];
            var sql = "SELECT * FROM " + buffer.name + " WHERE " + column + "=" + toolkit.parseValue2SQL(data) + ";"
            buffer.database.connection.query(sql, function (err, result) {
                if (result.length == 0) {cb(true);return}
                //load data into memory db for caching - TO DO
                Object.keys(result).forEach((irow) => {
                    var key = result[irow]['rowkey']
                    buffer.keys[ key ] = new buffer.rowPrototype(buffer, key, true);
                    Object.keys(result[irow]).forEach((icol) => {
                        if (icol !== 'rowkey'){
                            if ( Object.getPrototypeOf(buffer.keys[key][icol]) === encryptedItem.prototype ){
                                buffer.keys[key][icol]['hash'] = result[irow][icol];
                            } else if (typeof buffer.layout[icol]['default'] == 'object') {
                                buffer.keys[key][icol] = JSON.parse(result[irow][icol])
                            } else {
                                buffer.keys[key][icol] = result[irow][icol]
                            }
                        }
                    })
                    ret.push(buffer.keys[key])
                });
                //console.log('found in SQL')
                if (isUnique) {cb(false, ret[0]);} else {cb(false, ret);}
            });
        }
    }
}
table.prototype.addRow = function (data, key, cb) {
    var buffer = this;
    if (typeof data == 'undefined'){data = {}}
    if (typeof key == 'undefined') {key = shortid.generate();}
    if (key == -1) {key = shortid.generate();}
    this.keys[key] = new buffer.rowPrototype(this, key);
    //console.log(this.keys[key]);
    //console.log('Applying passed data..')
    var delay = false;
    Object.keys(data).forEach((i) => {
        //console.log(Object.getPrototypeOf(buffer.keys[key][i]));
        //console.log(i);

        if (Object.getPrototypeOf(buffer.keys[key][i]) === encryptedItem.prototype) {
            delay = true;
            //console.log('Encrypted item found for  ' + i)
            buffer.keys[key][i].overwrite(data[i], (err, value) => {
                    cb(buffer.keys[key]);
                    if (buffer.memonly == false) {buffer.keys[key].save()}
                    delay = false;
            })
        } else {
            //console.log('Regular item found for  ' + i)
            buffer.keys[key][i] = data[i]
        }
    })

    if (typeof cb === 'function' && !delay){
        cb(buffer.keys[key])
        if (buffer.memonly == false) {buffer.keys[key].save()}
    }
}
table.prototype.loadAll = function (cb) {
    if (!this.memonly){
        var buffer = this;
        var sql = "SELECT * FROM " + this.name
        this.database.connection.query(sql, function (err, result) {
            Object.keys(result).forEach((i) => {
                if (typeof buffer.keys[result[i]['rowkey']] == 'undefined') {
                    buffer.keys[result[i]['rowkey']] = new buffer.rowPrototype(buffer, result[i]['rowkey'], true);
                }
                Object.keys(result[i]).forEach((ii) => {
                    if (ii !== 'rowkey') {
                        var key = result[i]['rowkey'];
                        if (Object.getPrototypeOf(buffer.keys[key][ii]) === encryptedItem.prototype){
                            buffer.keys[key][ii].hash = result[i][ii];
                        } else if (typeof buffer.layout[ii]['default'] == 'object') {
                            buffer.keys[key][ii] = JSON.parse(result[i][ii])
                        } else {
                            buffer.keys[key][ii] = result[i][ii]
                        }
                    }
                });
            });
            if (typeof cb === 'function'){cb(err)}
        });
    }
}
table.prototype.loadRow = function (key) {
    if (!this.memonly){
        var buffer = this;
        var sql = "SELECT * FROM " + this.name + " WHERE rowkey='" + key + "'"
        this.database.connection.query(sql, function (err, result) {
            if (typeof buffer.keys[result[0]['rowkey']] == 'undefined') {
                buffer.keys[result[0]['rowkey']] = new buffer.rowPrototype(buffer, result[0]['rowkey'], true);
            }
            Object.keys(result[0]).forEach((ii) => {
                var key = result[0]['rowkey'];
                if (ii !== 'rowkey') {
                    if (Object.getPrototypeOf(buffer.keys[key][ii]) === encryptedItem.prototype){
                        buffer.keys[key][ii].hash = result[0][ii];
                    } else if (typeof buffer.layout[ii]['default'] == 'object') {
                        buffer.keys[key][ii] = JSON.parse(result[i][ii])
                    } else {
                        buffer.keys[key][ii] = result[0][ii];
                    }
                }
            });
        });
    }
}
table.prototype.save = function (cb) {
    if (!this.memonly){
        //console.log(this);
        var tbuffer = this;
        var columnlist = '(rowkey VARCHAR(64)';
        Object.keys(tbuffer.columns).forEach(function (i) {
            var cbuffer = tbuffer.columns[i];
            //if (cbuffer.encrypted){

            //} else {
                columnlist = columnlist + ', ' + cbuffer['name'] + ' ' + cbuffer['type'] + ' NULL'
                if (typeof cbuffer['default'] !== 'undefined'){
                    columnlist += ' DEFAULT ' + toolkit.parseValue2SQL(cbuffer['default'])
                }
            //}
        });
        columnlist = columnlist + ", INDEX `rowkey` (`rowkey`)) COLLATE='utf8_general_ci'"
        var sql = "CREATE TABLE IF NOT EXISTS " + tbuffer.name + columnlist + ";";
        //console.log(sql);
        tbuffer.database.connection.query(sql, function (err, result) {
            if (err) throw err;
            //console.log("Table created");
            if (typeof cb !== 'undefined') {cb();}
            return;
        });
    }
}
/*
CREATE TABLE `users` (
    `rowkey` VARCHAR(12) NULL,
    `username` VARCHAR(50) NULL DEFAULT 'player1',
    INDEX `rowkey` (`rowkey`)
)
COLLATE='utf8_general_ci'
;
*/

//ROW CONSTRUCTOR
function row(table, key, isLoading){
    var buffer = this
    buffer.rowkey = key;
    buffer._table = table;
    Object.keys(table.columns).forEach(function (i) {
        var cbuffer = table.columns[i];
        if (cbuffer.encrypted){
            buffer[cbuffer.name] = new encryptedItem();
        } else {
            buffer[table.columns[i]['name']] = table.columns[i]['default'];
        }
    });
    if (!isLoading && !table.memonly){
        var sql = 'INSERT INTO ' + table.name + ' (rowkey) VALUES ("' + key + '")'
        table.database.connection.query(sql, function (err, result) {
            //if (err) {console.log(key + ' Row alrady exists on SQL? (row create)\n' + err)}
            buffer.save()
        });
    }
}
row.prototype.save = function (cb) {
    if (!this._table.memonly){
        var buffer = this;
        var columns = ""
        Object.keys(buffer._table.columns).forEach(function (i) {
            if (columns !== "") {columns += ", "}
            columns += buffer._table.columns[i]['name'] + " = " + toolkit.parseValue2SQL( buffer[buffer._table.columns[i]['name']] );
        });

        var sql = "UPDATE " + buffer._table.name + " SET " + columns + " WHERE rowkey = '" + this.rowkey + "';"
        //console.log(sql);
        buffer._table.database.connection.query(sql, function (err, result) {
            //if (err) {console.log('Problem saving row ' + this.rowkey + '\n' + err)}
            if (typeof cb == 'function'){cb(err)}
        });
    }
}
row.prototype.unload = function (){
    delete this._table.keys[this.rowkey]
}
row.prototype.purge = function () {
    if (this._table.memonly) {
        delete this._table.keys[this.rowkey]
    } else {
        var buffer = this;
        sql = "DELETE FROM " + this._table.name + " WHERE rowkey='" + this.rowkey + "';"
        console.log(sql);
        this._table.database.connection.query(sql, function (err, result) {
            if (err) {console.log(err)}
            delete buffer._table.keys[buffer.rowkey]
        });
    }
}


//ENCRYPTED ITEM DEFINITION
var encryptedItem = function (){
    this.hash = '';
}
encryptedItem.prototype.comparehash = function (s_compare, cb){
    //console.log('comparing...')
    bcrypt.compare(s_compare, this.hash, function(err, res) {
        //console.log('compared - ' + res)
        cb(res);
    });
}
encryptedItem.prototype.overwrite = function (newstring, cb){
    var buffer = this;
    bcrypt.hash(newstring, saltRounds, function(err, hash) {
        if (err) {cb('Error hashing'); console.log('Error with hashing!')} else {
            //console.log(hash)
            buffer.hash = hash;
            cb(err, buffer);
        }
    });
}


//INTERNAL FUNCTIONS
var toolkit = {
    parseValue2SQL: function(value) {
        if (typeof value == 'string'){
            return SqlString.escape(value);
        } else if (typeof value == 'boolean') {
            if (value) {return 1;} else {return 0;}
        } else if ( Object.getPrototypeOf(value) === encryptedItem.prototype ) {
            return SqlString.escape(value.hash);
        } else if (typeof value == 'object') {
            return SqlString.escape( JSON.stringify(value) );
        } else {
            //console.log('parse no type')
            return value
        }
    }
}