/*
CREATE TABLE public.users(
	id serial primary key,
	username varchar(200),
	password varchar(200),
	first_name varchar(200),
	last_name varchar(200),
);
*/
//Dependencies
var express = require('express');
var bodyParser = require('body-parser');
var app = express();
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({extended: true}));
var pg = require("pg");

//Server settings
var serverPort = 8081;

//Database settings
var dbPort = 5432;
var dbHost = 'localhost';
var dbUsername = 'postgres';
var dbPassword = 'postgres';
var dbName = 'nombreBasedeDatos';
/*------------------------------>METHODS<-----------------------------*/
app.get('/users', function(req, res){
	var conString = "pg://"+dbUsername+":"+dbPassword+"@"+dbHost+":"+dbPort+"/"+dbName;
    var client = new pg.Client(conString);
	client.connect();

	var query = client.query("SELECT u.username, u.first_name, u.last_name FROM public.users u");
	query.on("row", function(row, result){
		result.addRow(row);
	})
	query.on("end", function(result){
		res.send(result.rows);
	})
});

app.get('/users/offset/:offsetIndex', function(req, res){
	var conString = "pg://"+dbUsername+":"+dbPassword+"@"+dbHost+":"+dbPort+"/"+dbName;
    var client = new pg.Client(conString);
	client.connect();

	var query = client.query("SELECT u.username, u.first_name, u.last_name FROM public.users u");
	var offsetIndex = req.params.offsetIndex;
	console.log('El offset index es '+offsetIndex)
	query.on("row", function(row, result){
		result.addRow(row);
	})
	query.on("end", function(result){
		res.send(result.rows.slice(offsetIndex, offsetIndex+10));
	})
});

app.get('/user/:username', function(req, res){
	var conString = "pg://"+dbUsername+":"+dbPassword+"@"+dbHost+":"+dbPort+"/"+dbName;
    var client = new pg.Client(conString);
	client.connect();

	var query = client.query("SELECT u.username, u.first_name, u.last_name FROM public.users u WHERE u.username = '"+req.params.username+"'");
	query.on("row", function(row, result){
		result.addRow(row);
	})
	query.on("end", function(result){
		res.send(result.rows);
	})
});

app.post('/user', function(req, res){
	var conString = "pg://"+dbUsername+":"+dbPassword+"@"+dbHost+":"+dbPort+"/"+dbName;
    var client = new pg.Client(conString);
	client.connect();


	client.query("INSERT INTO users(username, password, first_name, last_name) values('"+req.body.username+"', '"+req.body.password+"', '"+req.body.first_name+"', '"+req.body.last_name+")")

	var query = client.query("SELECT u.username, u.first_name, u.last_name FROM public.users u WHERE u.username = '"+req.body.username+"'");
	query.on("row", function(row, result){
		result.addRow(row);
	})
	query.on("end", function(result){
		res.send(result.rows);
	})
});

app.put('/user', function(req, res){
	var conString = "pg://"+dbUsername+":"+dbPassword+"@"+dbHost+":"+dbPort+"/"+dbName;
    var client = new pg.Client(conString);
	client.connect();

	if(req.body.password != null){
		client.query("UPDATE public.users SET password ='"+req.body.password+"' WHERE username='"+req.body.username+"'");
	}

	if(req.body.first_name != null){
		client.query("UPDATE public.users SET first_name ='"+req.body.first_name+"' WHERE username='"+req.body.username+"'");
	}

	if(req.body.last_name != null){
		client.query("UPDATE public.users SET last_name ='"+req.body.last_name+"' WHERE username='"+req.body.username+"'");
	}

	var query = client.query("SELECT u.username, u.first_name, u.last_name FROM public.users u WHERE u.username = '"+req.body.username+"'");
	query.on("row", function(row, result){
		result.addRow(row);
	})
	query.on("end", function(result){
		res.send(result.rows);
	})
});

/*--------------------------------------------------------------------*/

var server = app.listen(serverPort, function(){
	var host = server.address().address
	var port = server.address().port

	console.log("Server listening at %s", serverPort)
});