//Start HTTP-Server
var express = require("express");
var url = require("url");
var http = require("http");
var fs = require("fs");
var connection = require('./db');

var port = 3000;
var app = express();
app.use(express.static(__dirname + "/client"));
http.createServer(app).listen(port);

//Variables
var user ={};
var Id	= null;
var todos =[];
var indexofuser=-1;

//clients requests todos
app.get("/todo", function (req, res) {

var IdToGet = req.query.userId;

	connection.query('SELECT * FROM ToDoItem WHERE ToDoListID IN (SELECT Id FROM ToDoList WHERE Owner ='+IdToGet+');', function(err, rows){
if(!err){
	res.end(JSON.stringify(rows, null,4));
	}
else	{
	console.log('Error while performing Query');
	}
});


});


//clients requests todoListID
app.get("/todoListID", function (req, res,next) {

var IdToGet =  parseInt(req.query.userId);
//console.log(IdToGet);
	connection.query('SELECT Id FROM ToDoList WHERE Owner = '+ IdToGet+' LIMIT 1;', function(err, rows){
if(!err){
	//console.log(JSON.stringify(rows).match( /\d+/)[0]);
	res.end(JSON.stringify(rows).match( /\d+/)[0]);


	}
else	{
	console.log(err);
	}
});


});

//clients want to register
app.get("/register", function (req, res,next) {
var newusername = req.query.registerinfo.split(" ")[0];
var newpassword = req.query.registerinfo.split(" ")[1];
var userid = null;
connection.query('SELECT MAX(Id) FROM User;',function(err, rows){
	rows = JSON.stringify(rows);
	userid = parseInt(rows.replace( /^\D+/g, '')) +1;
	connection.query('INSERT INTO User (Id, Name, Email, Username, Password) VALUES ('+userid+',"'+newusername+'",NULL,"'+newusername+'","'+newpassword +'");',function(err, rows){
		if(!err)
	{
			res.end(JSON.stringify(userid));
	}
		else
	{
			res.end("false")
			console.log(err);
	}
	});
});

connection.query('SELECT MAX(Id) FROM ToDoList;',function(err, rows){
	rows = JSON.stringify(rows);
	var todolistid = parseInt(rows.replace( /^\D+/g, '')) +1;
	connection.query('INSERT INTO ToDoList (Id, Name, CreationDate,Owner, IsPublic) VALUES ('+todolistid+',"MAIN",NULL,"'+userid+'",0);',function(err, rows){
		if(err)
	{		res.end("false")
			console.log(err);
	}
	});
});



});

//clients want to access
app.get("/access", function (req, res,next) {
console.log(">>>>>"+req.query.loginfoin+"<<<<<");
var username = req.query.loginfoin.split(" ")[0];
var password = req.query.loginfoin.split(" ")[1];
connection.query('select * from User where Username = "'+ username+'";', function(err, rows){
	if(!err)
{
		var User = JSON.parse(JSON.stringify(rows, null,4))[0];

		if(User === undefined )
		{
			res.end("false");
			return;
		}
		if(User.Password === password)
		{
			res.end(JSON.stringify(User.Id));
		}

		else
		{
			res.end("false");
		}
}
	else
{
		console.log(err);
}
});



});


//date formatter
function toServerDate(dateIn)
{
if(dateIn == "")
  {
    return null;
  }
 var date =  dateIn.split("/");
 var serverDate = date[2] + date[0] + date[1]+ "000000";
  return serverDate;
}

//add todo to the server
app.get("/addtodo", function (req, res) {
	console.log(req.query);
//console.log(toServerDate(req.query.deadline));
connection.query('INSERT INTO ToDoItem (Id,Title,Text,CreationDate,DueDate,Completed,CompletionDate,Priority,ToDoListID,ParentToDo) VALUES ('+req.query.id+',"'+req.query.todo+'","Text",NULL,'+toServerDate(req.query.deadline)+','+req.query.finished+',NULL,'+req.query.priority+','+req.query.ToDoListID+',NULL);',function(err, rows){
		if(err)
	{
			console.log(err);
	}
	});

});

//remove todo from the server
app.get("/removetodo", function (req, res) {
	var idToRemove =  parseInt(req.query.idvalue);

	connection.query('DELETE FROM ToDoItem WHERE Id= '+idToRemove +';',function(err, rows){
		if(err)
	{
			console.log(err);
	}
	});
});


//edit todo from the server
app.get("/edittodo", function (req, res) {

	connection.query('UPDATE ToDoItem SET Title= "'+req.query.todo +'", DueDate= NULL, Completed ='+ parseInt(req.query.finished)+', Priority ='+ parseInt(req.query.priority)+' WHERE Id ='+ parseInt(req.query.id) +';',function(err, rows){
		if(err)
	{
			console.log(err);
	}
	});
});

//function to write to json file. might not needed later as we use sql
function writeToFile()
{
	user.todolist=todos;
	database[indexofuser] = user;
	var json = JSON.stringify(database, null, 4);
	fs.writeFile('database.json', json, 'utf8', function(err) {});
}



//______________________DASHBOARD_________________________
//Query 1
app.get("/dashboard/query1", function (req, res) {
  var querynmbr = 1;
  var query = 'SELECT ToDoList.Name FROM ToDoList WHERE ToDoList.Owner = "1";';
  executeQuery(querynmbr, query, function(response){
    res.json(response);
  });
});

//Query 2
app.get("/dashboard/query2", function (req, res) {
  var querynmbr = 2;
  var query = 'SELECT Title FROM ToDoItem WHERE ToDoListID = "1";';
  executeQuery(querynmbr, query, function(response){
    res.json(response);
  });
});

//Query 3
app.get("/dashboard/query3", function (req, res) {
  var querynmbr = 3;
  var query = 'SELECT Title FROM ToDoItem WHERE ToDoListID = "1" LIMIT 5,5;';
  executeQuery(querynmbr, query, function(response){
    res.json(response);
  });
});

//Query 4
app.get("/dashboard/query4", function (req, res) {
  var querynmbr = 4;
  var query = 'SELECT Title FROM ToDoItem WHERE ToDoListID = "1" AND Completed="0" AND Priority="3" AND CreationDate BETWEEN "2014-10-24 00:00:01" AND "2014-12-05 23:59:59" LIMIT 0,5;';
  executeQuery(querynmbr, query, function(response){
    res.json(response);
  });
});

//Query 5
app.get("/dashboard/query5", function (req, res) {
  var querynmbr = 5;
  var query = 'SELECT Title FROM ToDoItem WHERE ParentToDo = "1";';
  executeQuery(querynmbr, query, function(response){
    res.json(response);
  });
});

//Query 6
app.get("/dashboard/query6", function (req, res) {
  var querynmbr = 6;
  var query = 'SELECT Tag.Text FROM Tag, ItemTag WHERE Tag.Id = ItemTag.TagId AND ItemTag.ToDoId="1";';
  executeQuery(querynmbr, query, function(response){
    res.json(response);
  });
});

//Query 7
app.get("/dashboard/query7", function (req, res) {
  var querynmbr = 7;
  var query = 'SELECT ToDoList.Name FROM ItemTag, ToDoItem, ToDoList WHERE ItemTag.ToDoId = ToDoItem.Id AND ToDoItem.ToDoListID = ToDoList.Id AND ItemTag.TagId="1" GROUP BY Name;';
  executeQuery(querynmbr, query, function(response){
    res.json(response);
  });
});

//Query 8
app.get("/dashboard/query8", function (req, res) {
  var querynmbr = 8;
  var query = 'SELECT ToDoItem.Completed, COUNT(*) AS "Amount" FROM ItemTag, ToDoItem WHERE ItemTag.ToDoId = ToDoItem.Id AND ItemTag.TagId="1" GROUP BY Completed;';
  executeQuery(querynmbr, query, function(response){
    res.json(response);
  });
});

//Query 9
app.get("/dashboard/query9", function (req, res) {
  var querynmbr = 9;
  var query = 'SELECT WEEK (ToDoItem.CompletionDate) AS Week, COUNT(*) AS Count FROM ToDoItem WHERE CompletionDate IS NOT NULL GROUP BY WEEK(CompletionDate);';
  executeQuery(querynmbr, query, function(response){
    res.json(response);
  });
});

//Query 10
app.get("/dashboard/query10", function (req, res) {
  var querynmbr = 10;
  var query = 'SELECT ToDoItem.Title, DATEDIFF(ToDoItem.CompletionDate, ToDoItem.CreationDate) AS Difference FROM ToDoItem, ItemTag WHERE ToDoItem.id = ItemTag.ToDoId AND ItemTag.TagId = "1" AND ToDoItem.CompletionDate IS NOT NULL ORDER BY DATEDIFF(ToDoItem.CompletionDate, ToDoItem.CreationDate)  LIMIT 0,10;';
  executeQuery(querynmbr, query, function(response){
    res.json(response);
  });
});

//Query 11
app.get("/dashboard/query11", function (req, res) {
  var querynmbr = 11;
  var query = 'SELECT SumOfTagID, COUNT(*) AS Amount FROM (SELECT SUM(TagId) AS SumOfTagID FROM ItemTag GROUP BY ToDoId) AS Subset GROUP BY SumOfTagID;';
  executeQuery(querynmbr, query, function(response){
    res.json(response);
  });
});

//Query 12
app.get("/dashboard/query12", function (req, res) {
  var querynmbr = 12;
  var query = 'SELECT AVG(Difference) AS AverageDays FROM (SELECT ToDoItem.Title, DATEDIFF(ToDoItem.CompletionDate,	ToDoItem.CreationDate) AS Difference FROM ToDoItem WHERE ToDoItem.ToDoListID="1" AND DATEDIFF(ToDoItem.CompletionDate, ToDoItem.CreationDate) > -1 AND ToDoItem.CompletionDate IS NOT NULL) AS subset;';
  executeQuery(querynmbr, query, function(response){
    res.json(response);
  });
});

//Query 13
app.get("/dashboard/query13", function (req, res) {
  var querynmbr = 13;
  var query = 'SELECT ToDoItem.Title FROM (SELECT AVG(Subset.Difference) AS AverageDays FROM (SELECT ToDoItem.Title, DATEDIFF(ToDoItem.CompletionDate, ToDoItem.CreationDate) AS Difference FROM ToDoItem WHERE ToDoItem.ToDoListID="1" AND DATEDIFF(ToDoItem.CompletionDate, ToDoItem.CreationDate) > -1 AND ToDoItem.CompletionDate IS NOT NULL )AS Subset)AS Subset2, ToDoItem WHERE DATEDIFF(ToDoItem.CompletionDate, ToDoItem.CreationDate) > Subset2.AverageDays AND ToDoItem.CompletionDate IS NOT NULL AND ToDoItem.ToDoListID="1";';
  executeQuery(querynmbr, query, function(response){
    res.json(response);
  });
});

//CustomQuery
app.get("/dashboard/customquery", function (req, res) {
  var querynmbr = "CustomQuery";
  var query = req.query.customquery;
  executeQuery(querynmbr, query, function(response){
    res.json(response);
  });
});

//Function to execute the database query!
function executeQuery(querynmbr, query, callback){
  console.log('Query ' + querynmbr+ ' initiated!')
  var response = {};
  try{
    connection.query(query, function(err, rows, fields) {
      if (!err) {
        console.log("- Query " + querynmbr + " executed correctly!");
        callback(rows);
      }
      else {
        response = {response: "Query Failure!"};
        console.log("- Query Failure!")
        callback(response);
      }
    });
  }
  catch(err) {
    response = {response: "MySQL-Server Connection Failure"};
    console.log("- MySQL-Server Connection Failure!")
    callback(response);
  }
};