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

    testfunc: function () {
        var db = wrapper.create({
            connectionLimit : 10,
            host     : 'thatguynex.com',
            user     : 'nexusvds',
            password : 'ituHqKJNoe',
            database : 'nexusvds_testing'
        });
        db.connect(function (err) {
            if (err) {
                console.error('error connecting: ' + err.stack);
                return;
              }
            console.log('Connected to SQL');
            db.createTable('users', 0, [
                {name:'username',type:'VARCHAR(32)', default:'testuser'},
                {name:'password',type:'VARCHAR(255)', default:'password1'}
            ], function(err, users){
                console.log('Creating new row..');
                var newuser = users.addRow({
                    username: 'Bobbert',
                    password: 'apassword'
                });
                newuser.username = "Bobbert"
                newuser.save((err) => {
                    if (err) {console.log(err)}
                    users.loadAll((err) => {
                        if (err) {console.log('SQL error on LoadAll\n' + err)}
                        console.log(users.keys)
                    });
                });
                //users.get();
                //console.log(db.tables)
            });
        });
    }
}
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);
            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]
        }
    },

    loadTables: function () {
        //load tables
    },
    tables: {},
    connection: {}
}

//TABLE CONSTRUCTOR
function table(name, cachetime, columns, database, memonly){
    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;
}
table.prototype.getKeys = function () {
    //Load row keys
}
table.prototype.get = function (column, data, cb, isUnique) {
    //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 (flag && !this.memonly){
        ret = [];
        var sql = "SELECT * FROM " + buffer.name + " WHERE " + column + "=" + toolkit.parseValue2SQL(data) + ";"
        buffer.database.connection.query(sql, function (err, result) {
            if (err) {cb(err);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 ( Object.getPrototypeOf(buffer.keys[key][icol]) === encryptedItem ){
                        buffer.keys[key][icol]['hash'] = 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) {
    console.log('adding row ' + key)
    var buffer = this;
    if (typeof data == 'undefined'){data = {}}
    if (typeof key == 'undefined') {key = shortid.generate();}
    this.keys[key] = new buffer.rowPrototype(this, key);
    console.log(this.keys[key]);
    Object.keys(data).forEach((i) => {
        if (Object.getPrototypeOf(buffer.keys[key][i]) === encryptedItem) {
            console.log('')
            buffer.keys[key][i].overwrite(data[i], (err) => {
                buffer.keys[key].save()
                if (typeof cb === 'function'){cb(buffer.keys[key])}
            })
        } else {
            buffer.keys[key][i] = data[i]
            buffer.keys[key].save()
            if (typeof cb === 'function'){cb(buffer.keys[key])}
        }
    })
}
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') {buffer.keys[result[i]['rowkey']][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) => {
                if (ii !== 'rowkey') {buffer.keys[result[0]['rowkey']][ii] = result[0][ii]}
            });
        });
    }
}
table.prototype.save = function (cb) {
    if (!this.memonly){
        //console.log(this);
        var tbuffer = this;
        var columnlist = '(rowkey VARCHAR(32)';
        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)}
        });
    }
}


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


//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 ) {
            return SqlString.escape(value.hash);
        } else if (typeof value == 'object') {
            //JSOn stuff
            return 'object'
        } else {
            //console.log('parse no type')
            return value
        }
    }
}