index.js

const { faker } = require('@faker-js/faker');
const mysql = require('mysql2');
const express = require("express");
const app = express();
const path = require("path");
app.set("view engine", "ejs");
const port = 8080;
const methodOverride = require("method-override");
const { v4: uuidv4 } = require('uuid');

app.set("views", path.join(__dirname, "/views"));
app.use(methodOverride("_method"));
app.use(express.urlencoded({ extended: true }));
app.use(express.json());

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    database: 'First_sql',
    password: 'S713713t+'
});

// insted of object return array
let createRandomUser = () => {
    return [
        faker.string.uuid(),
        faker.internet.username(),
        faker.internet.email(),
        faker.internet.password(),
    ];
}

//Home page
app.get("/", (req, res) => {
    let q = `SELECT count(id) FROM temp`;
    try {
        connection.query(q, (err, result) => {
            if (err) throw err;
            let re = result[0]["count(id)"];
            console.log(re);
            res.render("home.ejs", { re });
        });
    } catch (err) {
        console.log(err);
        res.send("SOME ERROR OCCURED IN DB");
    }
});

// show users
app.get("/show", (req, res) => {
    let q = "SELECT * FROM temp;";
    try {
        connection.query(q, (error, result) => {
            if (error) throw error;

            res.render("show.ejs", { result });
        });
    } catch (err) {
        console.log(err);
        res.send("THE DB have some error");
    }
});

//EDIT ROUTE
app.get("/user/:id/edit", (req, res) => {
    let { id } = req.params;
    let q = `SELECT * FROM temp WHERE id = '${id}';`;
    try {
        connection.query(q, (err, result) => {
            if (err) throw err;
            console.log(result);
            let user = result[0];
            res.render("edit.ejs", { user });
        });
    } catch (error) {
        console.log(error);
        res.send('SOME ERROR IN DB');
    }
});

app.patch("/user/:id", (req, res) => {
    let { id } = req.params;
    let { password: formpassword, username: newUsername } = req.body;
    let q = `SELECT * FROM temp WHERE id = '${id}';`;
    try {
        connection.query(q, (err, result) => {
            if (err) throw err;
            let user = result[0];
            if (formpassword != user.password) {
                res.send("WRONG PASSWORD TRY AGAIN");
            } else {
                let q2 = `UPDATE temp SET username = '${newUsername}' WHERE id = '${user.id}'`;
                connection.query(q2, (erro, resl) => {
                    if (erro) throw erro;
                    res.redirect("/show");
                });
            }
        });
    } catch (error) {
        console.log(error);
        res.send('SOME ERROR IN DB');
    }
});

// insert data
app.get("/user/new", (req, res) => {
    res.render("user.ejs");
});

app.post("/user", (req, res) => {
    let { username, email, password } = req.body;
    let id = uuidv4();
    let vec = [[id, username, email, password]];
    let q = `INSERT INTO temp (id, username, email, password) VALUES ?`;
    try {
        connection.query(q, [vec], (err, result) => {
            if (err) throw err;
            res.redirect("/show");
        });
    } catch (err) {
        console.log(err);
        res.send("SOME ERROR IN DB");
    }
});

//delete data
app.get("/user/:id/delete", (req, res) => {
    let { id } = req.params;
    console.log(id);
    let q = `SELECT username FROM temp WHERE id = '${id}'`;
    try {
        connection.query(q, (err, result) => {
            if(err) throw err;
            let uname = result[0].username;
            res.render("delete.ejs", { id, uname});
        });
    } catch (err) {
        console.log(err);
        res.send(err);
    }
});

app.delete("/user/:id", (req, res) => {
    let { id } = req.params;
    let {email, password } = req.body;
    let q = `SELECT * FROM temp WHERE id = '${id}'`;
    try {
        connection.query(q, (err, result) => {
            if(err) throw err;
            console.log(result);
            if(result[0].email != email || result[0].password != password){
                res.send("WRONG EMAILID OR PASSWORD IS ENTERED");
            } else {
                let q2 = `DELETE FROM temp WHERE id = '${id}'`;
                connection.query(q2, (erro, result2) => {
                    if (erro) throw erro;
                    res.redirect("/show");
                });
            }
        });
    } catch (err) {
        console.log(err);
        res.send(err);
    }
});

app.listen(port, () => {
    console.log("The app is listing now");
});