var express = require("express");
var sqlite3 = require("sqlite3");
var bodyParser = require("body-parser");

var app = express();
var db = new sqlite3.Database('./Jeopardy.db');

var hat = require('hat'); //for generating tokens

app.use( bodyParser.urlencoded({ extended: true }));

app.use(bodyParser.json());


app.get("/", function(req,res) {
    db.get("select * from users",function(e,u){
        return res.json(u);
    })
    return res.send("Hello World");
})


app.post('/auth/signin', function(req, res) {
    var userID = req.body.userID;
    var password = req.body.password;

    if(userID == null || password == null) {
        return res.status(401).json({message: "invalid_credentials"});
    }

    var dbQuery = "select * from Users where UserID = ? and UserPassword = ?";
    var requestParams = [userID, password];

    db.get(dbQuery, requestParams, function(err, user) {
        if(err) {
            return res.status(500).json({message: "Internal server error"});
        }

        if(user == null) {
            return res.status(401).json({message: "invalid_credentials"});
        }
        var id = hat();
        var date = new Date();
        requestParams = [id, date, userID];
        var dbQuery1 = "UPDATE Users SET AuthToken = ? , AuthTokenIssued = ? WHERE UserID = ?"
        db.run(dbQuery1, requestParams, function(err, user) {
            if (err) {
                console.log(err);
                return res.status(500).json({message: "Internal server error"});
            }
            return res.status(200).json({message: "success", authToken: id});
        });
    });
});


app.get('/questions', function(req, res) {
    var auth = req.query.auth;
    if (auth === undefined) return res.status(400).json({message: "unauthorized access"});
    var dbQuery = "select * from Users where AuthToken = ?";
    var requestParams = [auth];
    db.get(dbQuery, requestParams, function(err, user) {
        if(err) {
            return res.status(500).json({message: "Internal server error"});
        }
        if(user == null) {
            return res.status(400).json({message: "unauthorized access"});
        }
        var time = parseInt(((new Date )- user.AuthTokenIssued)/1000/3600);
        if (time > 1) {
            return res.status(400).json({message: "auth token expired"});
        }
    });

    var categoryTitle = req.query.categoryTitle;
    var dollarValue = req.query.dollarValue;
    var questionText = req.query.questionText;
    var answerText = req.query.answerText;
    var showNumber = req.query.showNumber;
    var airDate = req.query.airDate;

    var dbQuery = "select * from Questions join Categories on Questions.CategoryCode = Categories.CategoryCode where ";
    var paramCount = 0;
    var params = [];

    if (categoryTitle != null) {

        if(paramCount > 0) {
            dbQuery = dbQuery + 'and ';
        }

        paramCount++;
        dbQuery = dbQuery + 'CategoryTitle = ? ';
        params.push(categoryTitle.toUpperCase());
    }

    if (dollarValue != null) {

        if(paramCount > 0) {
            dbQuery = dbQuery + 'and ';
        }

        paramCount++;
        dbQuery = dbQuery + 'DollarValue = ? ';
        dollarValue = "$" + dollarValue;
        params.push(dollarValue);
    }

    if (questionText) {

        if(paramCount > 0) {
            dbQuery = dbQuery + 'and ';
        }

        paramCount++;
        dbQuery = dbQuery + 'QuestionText like ? ' ;
        questionText = '%' + questionText + '%';
        params.push(questionText);
    }

    if (answerText) {

        if(paramCount > 0) {
            dbQuery = dbQuery + 'and ';
        }

        paramCount++;
        dbQuery = dbQuery + 'AnswerText = ? ';
        params.push(answerText);
    }

    if (showNumber) {

        if(paramCount > 0) {
            dbQuery = dbQuery + 'and ';
        }

        paramCount++;
        dbQuery = dbQuery + 'ShowNumber = ? ';
        params.push(showNumber);
    }

    if (airDate) {

        if(paramCount > 0) {
            dbQuery = dbQuery + 'and ';
        }

        paramCount++;
        dbQuery = dbQuery + 'AirDate = ? ';
        params.push(airDate);
    }

    dbQuery = dbQuery + 'order by AirDate desc';

    if(paramCount == 0) {
        dbQuery = "select * from Questions order by AirDate desc";
    }

    db.all(dbQuery, params, (err, questions) => {

        if(questions.length > 5000) {
            return res.status(400).json({message: "too_many_results"});
        }

        if (err) {
            console.log(err);
            return res.status(500).json({message: "Internal server error"});
        }

        return res.status(200).json(questions);
    });
});

var port = process.env.PORT || 8000;
app.listen(port, function() {
    console.log("Running server on port " + port);
});