var Connection = require('tedious').Connection;
var Request = require('tedious').Request;
var TYPES = require('tedious').TYPES;
var async = require('async');
var express = require('express');

// Create connection to database
var config = {
    userName: 'ladymey', // update me
    password: 'MeY0544808130', // update me
    server: 'ladymey.database.windows.net',
    options: {
        database: 'meyDB'
        , encrypt: true

    }
}

var connection = new Connection(config);

var app = express();
var server = app.listen(3000);

app.use(express.static('public'));

var socket = require('socket.io');
var io = socket(server);


io.sockets.on('connection', newConnection);

function newConnection(socket){
    console.log('new connection  ' + socket.id);
	var contactsDB = "";

	contactsDB = Read();

	console.log(contactsDB);

	//io.sockets.emit('load list', contactsDB);

    socket.on('new entry',insertEntry);
	socket.on('update entry',updateEntry);
	socket.on('delete entry',deleteEntry);

    function insertEntry(data)
    {
        console.log(data);
        Insert(data.name,data.phone);

        io.sockets.emit('load list', contactsDB);
    }
	function updateEntry(data)
    {
        console.log(data);
        Update(data.name,data.phone);

        io.sockets.emit('load list', contactsDB);
    }
	function deleteEntry(data)
    {
        console.log(data);
        Delete(data.name);

        // TODO: BRING ALL DB
        io.sockets.emit('load list', contactsDB);
    }
}

function Read() {
    console.log('Reading rows from the Table...');

    // Read all rows from table
    request = new Request(
        'SELECT Name, Phone FROM ContactsSchema.Contacts;',
        function(err, rowCount, rows) {
            if (err) {
                console.log(err);
            } else {
                console.log(rowCount + ' row(s) returned');
            }
        });

    // Print the rows read
    var result = "";
    request.on('row', function(columns) {
        columns.forEach(function(column) {
            if (column.value === null) {
                console.log('NULL');
            } else {
                result += column.value + " ";
            }
        });

		console.log(result);
        return result;

        //result = "";
    });

    // Execute SQL statement
    connection.execSql(request);
}

function Insert(name, Phone) {
    console.log("Inserting '" + name + "' into Table...");

    request = new Request(
        'INSERT INTO ContactsSchema.Contacts (Name, Phone) OUTPUT INSERTED.Id VALUES (@Name, @Phone);',
        function(err, rowCount, rows) {
            if (err) {
                console.log(err);
            } else {
                console.log(rowCount + ' row(s) inserted');
            }
        });
    request.addParameter('Name', TYPES.NVarChar, name);
    request.addParameter('Phone', TYPES.NVarChar, Phone);

    // Execute SQL statement
    connection.execSql(request);
}

function Update(name, Phone) {
    console.log("Updating Phone to '" + Phone + "' for '" + name + "'...");

    // Update the employee record requested
    request = new Request(
        'UPDATE ContactsSchema.Contacts SET Phone=@Phone WHERE Name = @Name;',
        function(err, rowCount, rows) {
            if (err) {
                console.log(err);
            } else {
                console.log(rowCount + ' row(s) updated');
            }
        });
    request.addParameter('Name', TYPES.NVarChar, name);
    request.addParameter('Phone', TYPES.NVarChar, Phone);

    // Execute SQL statement
    connection.execSql(request);
}

function Delete(name) {
    console.log("Deleting '" + name + "' from Table...");

    // Delete the employee record requested
    request = new Request(
        'DELETE FROM ContactsSchema.Contacts WHERE Name = @Name;',
        function(err, rowCount, rows) {
            if (err) {
                console.log(err);
            } else {
                console.log(rowCount + ' row(s) deleted');
            }
        });
    request.addParameter('Name', TYPES.NVarChar, name);

    // Execute SQL statement
    connection.execSql(request);
}

// Attempt to connect and execute queries if connection goes through
connection.on('connect', function(err) {
    if (err) {
        console.log(err);
    } else {
        console.log('Connected to Database...!');

    }

});