server.js

import express from 'express';
import pkg from "pg";
import xlsx from 'xlsx';
import bodyParser from "body-parser";

const app = express();
const port = 3000;
const { Client } = pkg;

app.use(bodyParser.json()); // Parses JSON data
app.use(bodyParser.urlencoded({ extended: true }));


const db = new Client({
  user: "postgres",
  host: "localhost",
  database: "csbs",
  password: "9036",
  port: 5433, //5433
});

db.connect();
app.use(express.static("public"));
// app.set("views", __dirname + "/miniProject/views");

app.set("view engine", "ejs");

// app.set('view engine', 'ejs');

app.get('/', async (req, res) => {


  // const student = await db.query("SELECT * FROM student");
  // res.render("student.ejs",{
  //   student:student.rows
  // });

  // res.render("teacher-login.ejs")
  // console.log(student.rows);
  // res.render("teacherDashboard.ejs");
  // res.render("login.ejs",{issue: ""})
  res.render("home.ejs")

});

// app.post('/login', (req, res) => {
//   const { name, password } = req.body;

//   if (name === 'chirag' && password === 'byetatatata') {
//     res.send('<h2>Welcome, Chirag! 🎉</h2>');
//   } else {
//     res.send('<h2>Invalid credentials. Please try again.</h2><a href="/">Back</a>');
//   }
// });

app.get("/sortAttendance", async (req, res) => {
  const student = await db.query("SELECT * FROM student ORDER BY attendance DESC");
  res.render("attendance.ejs", {
    student: student.rows
  });
  // console.log(student.rows);
});
app.get("/sortCGPA", async (req, res) => {
  const student = await db.query("SELECT * FROM student ORDER BY cgpa DESC");
  res.render("cgpa.ejs", {
    student: student.rows
  });
  console.log(student.rows);
});
app.get("/addStudent", (req, res) => {
  res.render("studentForm"); // This sends the EJS form to the user
});
app.get("/login",(req,res)=>{
  res.render("login.ejs" ,{issue: ""});
})

app.post("/submitStudent", async (req, res) => {
  try {
    // Extract form data from the request body
    const { usn, name, attendance, cgpa } = req.body;

    // Insert into PostgreSQL
    const result = await db.query(
      "INSERT INTO student (usn, stname, attendance, cgpa) VALUES ($1, $2, $3, $4)",
      [usn, name, attendance, cgpa]
    );

    console.log("Data inserted:", result);

    // Redirect to a success page or re-render the form
    res.redirect("/"); // Redirect to the form again
  } catch (error) {
    console.error("Error inserting data:", error);
    res.status(500).send("Internal Server Error");
  }
});

app.post("/singleStudent", async (req, res) => {
  const usn = req.body.usn;
  const result = await db.query("SELECT * FROM student WHERE student.usn=$1", [usn])
  const name = result.rows
  res.send(result.rows)
})


app.listen(port, () => {
  console.log(`Server running at http://localhost:${port}`);
});


//for teacher's profile
app.get('/profile', (req, res) => {
  res.render('teacherDashboard.ejs');
});

//for llogin
app.post("/login", async (req, res) => {
  let username = req.body.username.toLowerCase();
  let pass = req.body.password.toLowerCase();
  if (username == "dsce" && pass == "csbs") {
    const student = await db.query("SELECT * FROM student");
    res.render("student.ejs", {
      student: student.rows
    });


  }
  else {
    res.render("login.ejs", { issue: "Entered wrong username or password" });
  }
})
//let re = document.getElementById("info").ele

//Second code:

// import express from 'express';
// import pkg from "pg";
// import xlsx from 'xlsx';
// import bodyParser from "body-parser";
// import path from "path";
// import { fileURLToPath } from "url";

// const app = express();
// const port = 3000;
// const { Client } = pkg;

// // Define __dirname manually for ES Modules
// const __filename = fileURLToPath(import.meta.url);
// const __dirname = path.dirname(__filename);

// app.use(bodyParser.json()); // Parses JSON data
// app.use(bodyParser.urlencoded({ extended: true }));

// const db = new Client({
//   user: "postgres",
//   host: "localhost",
//   database: "csbs",
//   password: "9036",
//   port: 5433, //5433
// });

// db.connect();
// app.use(express.static("public"));

// // ✅ Fix __dirname path issue
// app.set("views", path.join(__dirname, "miniProject", "views"));
// app.set("view engine", "ejs");

// // ✅ Fix res.render() path issue (remove "/")
// app.get('/', async (req, res) => {
//   try {
//     const student = await db.query("SELECT * FROM student");
//     res.render("student", { student: student.rows });
//     console.log(student.rows);
//   } catch (error) {
//     console.error("Error fetching students:", error);
//     res.status(500).send("Internal Server Error");
//   }
// });

// app.listen(port, () => {
//   console.log(`Server running at http://localhost:${port}`);
// });