var express = require('express');
var router = express.Router();


/* GET users listing. */
router.get('/', function (req, res) {
    res.send(`Users List:` + getUserlist());
});

module.exports = router;

//Connect to Database and setting Up Tedious SQL
console.log('Attempting Database Connection In Users.JS...'.cyan);
var Connection = require('tedious').Connection;
var Request = require('tedious').Request;
var TYPES = require('tedious').TYPES;

var configNoColumn = {
    userName: '***********',
    password: '******',
    server: '*****.database.windows.net',
    options: {
        encrypt: true,
        database: '*************'
    }
};

var connectionNoColumn = new Connection(configNoColumn);

connectionNoColumn.on('connect', function (err) {
    // If no error, then good to proceed.
    console.log("Connected".green);
    //getUserlist();
});


function getUserlist() {

    request = new Request('SELECT * FROM Users',
        function (err) {
            if (err) {
                console.log(err);
            }
        });
    var result = "";
    var results = [];
    console.log('User List:'.yellow);
    request.on('row', function (columns) {

        columns.forEach(function (column) {
            if (column.value === null) {
                console.log('NULL');
            } else {
                result += column.value + " ";
            }
        });
        results.push(result)
        console.log(result.blue);
        result = "";
    });


    request.on('done', function (rowCount, more) {
        console.log(rowCount + ' rows returned');
        console.log(`Results Array:` + results.toString);
        for (var i = 0; i < results.length; i++) {
            console.log(results[i].cyan);
        }
        return results.toString();
    });
    connectionNoColumn.execSqlBatch(request);

}