const express = require("express")
const cors = require("cors")
const mysql = require('mysql2')

const app = express()

const connection = mysql.createConnection({
    host: 'sql7.freesqldatabase.com',
    user: 'sql7710295',
    database: 'sql7710295',
    password: '1Pc65yn7d2',
})

app.use(cors())

app.get("/users", (req, res) => {
    connection.query(
        'SELECT * FROM `kmt_users`',
        function(err, results, fields) {
          res.send(results);
        }
      );
})

app.get("/login", (req, res) => {
    const username = req.query.username;
    const password = req.query.password;
    connection.query(
        'SELECT * FROM `kmt_users` WHERE username =  "'+ username + '" and password= "' + password +'"',
        function(err, results, fields) {
          res.send(results);
        }
      );
})

app.get("/register", (req, res) => {
    const username = req.query.username;
    const password = req.query.password;
    connection.query(
        'insert into `kmt_users` (username, password) values ("'+username+'","'+password+'")',
        function(err, results, fields) {
          res.send(results);
        }
      );
})

app.get("/update_time_played", (req, res) => {
    const user = req.query.user;
    const level = req.query.level;
    const time = req.query.time;
    connection.query(
        'insert into kmt_time_played (user_id,level_id,time_played) values ('+user+','+level+','+time+')',
        function(err, results, fields) {
          res.send(results);
        }
      );
})

app.get("/update_score", (req, res) => {
    const user = req.query.user;
    const level = req.query.level;
    const score = req.query.score;
    connection.query(
        'insert into kmt_score (user_id,level_id,score) values ('+user+','+level+','+score+')',
        function(err, results, fields) {
          res.send(results);
        }
      );
})

app.get("/update_deaths", (req, res) => {
    const user = req.query.user;
    const level = req.query.level;
    const deaths = req.query.deaths;
    connection.query(
        'insert into kmt_deaths (user_id,level_id,deaths) values ('+user+','+level+','+deaths+')',
        function(err, results, fields) {
          res.send(results);
        }
      );
})

app.get("/get_data", (req, res) => {
    const user = req.query.user;
    var score=0;
    connection.query(
        'select sum(score) score from kmt_score where user_id= '+user,
        function(err, results, fields) {
          score=results[0].score;
            connection.query(
                'select sum(time_played) time from kmt_time_played where user_id= '+user,
                function(err, results, fields) {
                  time=results[0].time;
                    connection.query(
                        'select sum(deaths) deaths from kmt_deaths where user_id= '+user,
                        function(err, results, fields) {
                          deaths=results[0].deaths;
                          res.send({score:score,time:time,deaths:deaths });
                        }
                      );

                }
              );

        }
      );
})

app.get("/get_data2", (req, res) => {
    const user = req.query.user;
    connection.query(
        'select kmt_users.id,kmt_users.username , sum(score)score, sum(deaths)deaths, sum(time_played)time_played ' +
        'from kmt_users ' +
        'left join kmt_score on kmt_score.user_id = kmt_users.id ' +
        'left join kmt_deaths on kmt_deaths.user_id = kmt_users.id ' +
        'left join kmt_time_played on kmt_time_played.user_id = kmt_users.id ' +
        'where kmt_users.id = '+user,
        function(err, results, fields) {
          res.send(results);
        }
      );
})

app.listen(6900, () => {
    console.log("Server is ready for action")
})