index.js

const { faker } = require("@faker-js/faker");
const mysql = require('mysql2');
const express = require("express");
const app = express();
const path = require("path");
const methodoverride = require("method-override");
const { error } = require("console");

app.use(methodoverride("_method"));
app.use(express.urlencoded({extended:true}));
app.set("view engine" , "ejs");
app.set("views" , path.join(__dirname,"/views"));

const connection = mysql.createConnection({

    host:'localhost',
    user : 'root',   
    database : 'delta_app',
    password : 'Root@123'
});

let  getRandomUser = ()  =>{
  return [
    faker.string.uuid(),
    faker.internet.username(), // before version 9.1.0, use userName()
    faker.internet.email(),
     faker.internet.password(),
 ];
};

let q = "INSERT INTO user (id, username , email , password) VALUES ?";

//home route

app.get("/" , (req , res) => {
  let q = `SELECT count(*) FROM user`;
  connection.query(q, (err, result) => {
    if (err) {
        console.error("Error executing query:", err);
       res.send("Some error in database" , err);
        return;
    }
    let count = result[0]["count(*)"];
    res.render("home.ejs" ,{ count });
    console.log("Inserted rows:", result[0]["count(*)"]);
});
});

//show route
app.get("/user" , (req , res) => {
   let q = `SELECT * FROM user`;
    connection.query(q, (err, users) => {
    if (err) {
        console.error("Error executing query:", err);
       res.send("Some error in database" , err);
        return;
     };
     res.render("showuser.ejs" , { users } ) ;
});
});

//edit route

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

})

// update route

app.patch("/user/:id" , (req,res) => {
  let {id} = req.params;
  let {password : formpass , username : newUsername} =req.body;
  newUsername = newUsername.trim();
  let q = `SELECT * FROM user WHERE id = ?`;

 try {
  connection.query(q , [id], (err , result) => {
    if(err) {
      console.error(err);
      return res.status(404).send("Database error");
    }
    if(result.length === 0 ){
      return res.status(404).send("user not found");
    }
    let user = result[0];
    if(formpass != user.password){
       return res.status(401).send("WRONG PASSWORD");
    }
    const q2 = "UPDATE user SET username = ? WHERE id = ?";
      connection.query(q2 , [newUsername , id] , (err,result) => {
        if (err) {
          console.error(err);
          return res.status(404).send("Database update error");
        }
      })
      return res.redirect("/user");
  });
  }catch(err){
   console.log(err);
   res.send("Some error in DB");
  }
});

//new user route
app.get("/user/new", (req, res) => {
    res.render("newuser.ejs");   // renders the above form
});

app.post("/user", (req, res) => {
    const { username, email, password } = req.body;
    const id = faker.string.uuid();  // generate unique id

    const q = "INSERT INTO user (id, username, email, password) VALUES (?, ?, ?, ?)";
    connection.query(q, [id, username.trim(), email.trim(), password], (err, result) => {
        if (err) {
            console.error(err);
            return res.status(500).send("Error inserting user");
        }
        res.redirect("/user");   // after adding, show all users
    });
});

//Delete route

app.delete("/user/:id" ,(req,res) => {
  const {id} = req.params;
  const {password} = req.body;

  const q = `SELECT * FROM user WHERE id = ?`;

  connection.query(q , [id] , (err,result) => {
   
    if (err) return res.status(500).send("Database Error");
        if (result.length === 0) return res.status(404).send("User not found");

    const user = result[0];

    if(password != user.password){
      res.status(401).send("Wrong Password");
    }
    
     const q2 = `DELETE FROM user WHERE id = ?`;
     connection.query(q2,[id],(err) => {
        if(err){
          console.error(err);
        }
         res.redirect("/user");
     });
  });
});

app.listen("8080" , (req,res) => {
  console.log("Listening to the server 8080");
});