const express = require("express");
const app = express();
const mysql = require('mysql');


app.use(express.json())


const con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "bourne9172",
    database: "phoneLogBook"

});

con.connect(function(err) {
    if (err) throw err;
    console.log("Connected!");
});

app.get('/users', (req, res) => {
    let sqlQuery = "SELECT id, name, phone_number FROM users";
    con.query(sqlQuery, (err, result) => {
        if (err) throw err;
        res.send(result);
    })
});

app.get('/users/:id', (req, res) => {
    let sqlQuery = `SELECT * FROM users WHERE id = ${req.params.id}`;
    con.query(sqlQuery, (err, result) => {
        if (err) throw err;
        res.send(result);
    })
});

app.put('/users/insert', (req, res) => {

    const name = req.body.name;
    const phoneNumber = req.body.phoneNumber;
    const age = req.body.age;
    const gender = req.body.gender;

    let sqlInsert = `INSERT INTO users SET name= '${name}', phoneNumber= '${phoneNumber}' , age = ${age}, gender = '${gender}'`;

    con.query(sqlInsert, (err, result) => {
        if (err) throw err;
        let sqlQuery = `SELECT * FROM users WHERE id = ${result.insertId}`;
        con.query(sqlQuery, (err, result) => {
            if (err) throw err;
            res.status(201).send(result);
        })
    });

});

app.post('/users/:id', (req, res) => {

    const id = req.params.id;
    const name = req.body.name;
    const phoneNumber = req.body.phoneNumber;
    const age = req.body.age;
    const gender = req.body.gender;

    let sqlInsert = `UPDATE users SET name= '${name}', phoneNumber= '${phoneNumber}' , age = ${age}, gender = '${gender}' WHERE id = ${id}`;

    con.query(sqlInsert, (err, result) => {
        if (err) throw err;
        console.log(result);
        let sqlQuery = `SELECT * FROM users WHERE id = ${result.insertId}`;
        con.query(sqlQuery, (err, result) => {
            if (err) throw err;
            res.send(result);
        })
    });

});

app.delete('/users/:id', (req, res) => {

    const id = req.params.id;

    let sqlInsert = `DELETE FROM users WHERE id = ${id}`;

    con.query(sqlInsert, (err, result) => {
        if (err) throw err;
        res.status(200).send();
    });

});


app.listen(3001, () => {console.log("Server has started on port 3001")});