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

const app = express()

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

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")
})