const express = require("express");
const fileUpload = require("express-fileupload");
const path = require("path");
const bcrypt = require("bcrypt");
const con = require("./config/db");
const app = express();
const { raw } = require("mysql2");
const session = require("express-session");
const { log } = require("console");
const e = require("express");
const MemoryStore = require("memorystore")(session);

// set the public folder
app.use("/public", express.static(path.join(__dirname, "public")));
app.use(express.json());
app.use(fileUpload());
app.use(express.urlencoded({ extended: true }));
app.use(
  session({
    cookie: { maxAge: 24 * 60 * 60 * 1000 },
    secret: "iday",
    resave: false,
    saveUninitialized: true,

    store: new MemoryStore({
      checkPeriod: 24 * 60 * 60 * 1000, // prune expired entries every 24h
    }),
  })
);
// ------------- Change status --------------
app.post("/borrowstatus/:borrowid", function (req, res) {
  const borrowid = req.params.borrowid;
  const status = req.body;
  const sql = "UPDATE borrow SET statusborrow = ? WHERE borrowid = ?";
  con.query(sql, [status.status, borrowid], function (err, results) {
    if (err) {
      console.error(err);
      return res.status(500).send("Database server error");
    } else if (results.affectedRows != 1) {
      console.error("Row updated is not 1");
      return res.status(500).send("Update failed");
    } else {
      res.send("Update succesfully");
    }
  });
});

// ------------- GET all borrows --------------
app.get("/borrows", function (_req, res) {
  const sql =
    "SELECT borrow.*, product.*,user.email FROM borrow JOIN product ON borrow.id = product.id JOIN user ON borrow.userid = user.userid;";
  con.query(sql, function (err, results) {
    if (err) {
      console.error(err);
      return res.status(500).send("Database server error");
    }
    res.json(results);
  });
});

// ------------- get-currentID --------------
app.get("/borrows-currentId", function (_req, res) {
  const id = _req.session.userId;
  const sql =
    "SELECT borrow.*, product.*,user.email FROM borrow JOIN product ON borrow.id = product.id JOIN user ON borrow.userid = user.userid WHERE borrow.userid = ?;";
  con.query(sql, [id], function (err, results) {
    if (err) {
      console.error(err);
      return res.status(500).send("Database server error");
    }
    res.json(results);
  });
});

// ------------- Update product OLD --------------
// app.put("/updateproduct/:id", function (req, res) {
//   const id = req.params.id;
//   const updateProduct = req.body;
//   const sql = "UPDATE product SET ? WHERE id = ?";
//   con.query(sql, [updateProduct, id], function (err, results) {
//     if (err) {
//       console.error(err);
//       return res.status(500).send("Database server error");
//     }
//     if (results.affectedRows != 1) {
//       console.error("Row updated is not 1");
//       return res.status(500).send("Update failed");
//     }
//     res.send("Update succesfully");
//   });
// });

// ------------- Update a product --------------
app.put("/products/:id", function (req, res) {
  const id = req.params.id;
  const updateProduct = req.body;
  const sql = "UPDATE product SET ? WHERE id = ?";
  con.query(sql, [updateProduct, id], function (err, results) {
    if (err) {
      console.error(err);
      return res.status(500).send("Database server error");
    }
    if (results.affectedRows != 1) {
      console.error("Row updated is not 1");
      return res.status(500).send("Update failed");
    }
    res.send("Update succesfully");
  });
});
// ------------- GET all products --------------
app.get("/products", function (_req, res) {
  const sql = "SELECT * FROM product";
  con.query(sql, function (err, results) {
    if (err) {
      console.error(err);
      return res.status(500).send("Database server error");
    }
    res.json(results);
  });
});
// ------------- GET all products borrow--------------
app.get("/productss", function (_req, res) {
  const sql =
    "SELECT p.*, b.statusborrow FROM product p LEFT JOIN borrow b ON p.id = b.id WHERE p.statusproduct = 0";

  // const sql = "SELECT * FROM product WHERE statusproduct = 0";
  con.query(sql, function (err, results) {
    if (err) {
      console.error(err);
      return res.status(500).send("Database server error");
    }
    res.json(results);
  });
});

// ------------- Delete a product --------------
app.delete("/products/:id", function (req, res) {
  const id = req.params.id;
  const sql = "DELETE FROM product WHERE id = ?";
  con.query(sql, [id], function (err, results) {
    if (err) {
      console.error(err);
      return res.status(500).send("Database server error");
    }
    if (results.affectedRows != 1) {
      console.error("Row deleted is not 1");
      return res.status(500).send("Delete failed");
    }
    res.send("Delete succesfully");
  });
});

// ------------- Return a borrow --------------
app.put("/borrows/:borrowid", function (req, res) {
  const borrowid = req.params.borrowid;
  const status = req.body.status;
  const sql = "UPDATE borrow SET statusborrow = ? WHERE borrowid = ?";

  con.query(sql, [status, borrowid], function (err, results) {
    if (err) {
      console.error(err);
      return res.status(500).json({ error: "Database server error" });
    }
    if (results.affectedRows != 1) {
      console.error("Row updated is not 1");
      return res
        .status(500)
        .json({ error: "Update failed: No or multiple rows affected" });
    }
    res.json({ message: "Return successfully" });
  });
});

// ------------- Add a new product --------------
app.post("/products", function (req, res) {
  const newProduct = req.body;
  const sql = "INSERT INTO product SET ?";
  con.query(sql, newProduct, function (err, results) {
    if (err) {
      console.error(err);
      return res.status(500).send("Database server error");
    }
    if (results.affectedRows != 1) {
      console.error("Row added is not 1");
      return res.status(500).send("Add failed");
    }
    res.send("Add succesfully");
  });
});

// ------------- Available and Disable --------------
app.post("/onoffitem", (req, res) => {
  let sql = "UPDATE product SET statusproduct = ? WHERE id = ?";
  let params = [req.body.status, req.body.idproduct];
  con.query(sql, params, (err, ress) => {
    if (err) {
      res.status(500).send("DB ERROR");
      throw err;
    }
    res.send("ok");
  });
});

// ------------- Add Borrow --------------
app.post("/borrows", function (req, res) {
  const newBorrow = req.body;
  const sql =
    "INSERT INTO borrow (borrowdate, returndate, userid, id, statusborrow) VALUES (?, ?, ?, ?, 1)";
  con.query(
    sql,
    [
      newBorrow.borrowdate,
      newBorrow.returndate,
      newBorrow.userid,
      newBorrow.id,
    ],
    function (err, results) {
      if (err) {
        console.error(err);
        return res.status(500).send("Database server error");
      }
      if (results.affectedRows != 1) {
        console.error("Row added is not 1");
        return res.status(500).send("Add failed");
      }
      res.send("success");
    }
  );
});

///// lecturer request page
app.post("/appordis", (req, res) => {
  let sql;
  let params;
  if (req.body.tpyeApporDis) {
    sql = "UPDATE borrow SET statusborrow = ? WHERE borrowid = ?";
    params = [2, req.body.borrowid];
  } else {
    sql = "UPDATE borrow SET statusborrow = ? , reason = ? WHERE borrowid = ?";
    params = [3, req.body.reasons, req.body.borrowid];
  }

  con.query(sql, params, (err, resule) => {
    if (err) {
      res.status(500).send("DB ERROR");
      throw err;
    }

    res.send("ok");
  });
});

// ============= Create hashed password ==============
// === web sevices ===
app.get("/password/:raw", function (req, res) {
  const raw = req.params.raw;
  bcrypt.hash(raw, 10, function (err, hash) {
    if (err) {
      res.status(500).send("Hash error");
    } else {
      res.send(hash);
    }
  });
});

//---------------Get user info----------------
app.get("/user", function (req, res) {
  // res.send(req.session.username);
  res.json({
    userid: req.session.userId,
    username: req.session.username,
    email: req.session.email,
    role: req.session.userRole,
  });
});

// login user
app.post("/loginUser", function (req, res) {
  const { username, password } = req.body;
  const sql = "SELECT * FROM user WHERE username= ?";
  con.query(sql, [username], function (err, results) {
    if (err) {
      console.error(err);
      res.status(500).send("DB error");
    } else if (results.length != 1) {
      res.status(401).send("Wrong username and password");
    } else {
      //compare raw with hashed password
      bcrypt.compare(password, results[0].password, function (err, same) {
        if (err) {
          res.status(500).send("Password error");
        } else {
          if (same) {
            if (results[0].role == 3) {
              req.session.username = username;
              req.session.userId = results[0].userid;
              req.session.userRole = results[0].role;
              req.session.email = results[0].email; // เพิ่มบรรทัดนี้เพื่อเก็บอีเมลใน session
              res.send("/userBorrowlist");
            } else {
              res.status(401).send("you are not user");
            }
          } else {
            res.status(401).send("Wrong password");
          }
        }
      });
    }
  });
});

// ---------------Logout----------------
app.get("/logout", function (req, res) {
  req.session.destroy(function (err) {
    if (err) {
      return res.status(500).send("Session error");
    }
    res.redirect("/");
  });
});

// Login user
app.get("/loginUser", function (_req, res) {
  res.sendFile(path.join(__dirname, "/views/user/user_login.html"));
});

// user request status
app.get("/reQuest", function (_req, res) {
  res.sendFile(path.join(__dirname, "/views/user/user_request_statust.html"));
});
// User homepage
app.get("/userBorrowlist", function (_req, res) {
  res.sendFile(path.join(__dirname, "/views/user/user_borrow_list.html"));
});
// user register
app.get("/registerUser", function (_req, res) {
  res.sendFile(path.join(__dirname, "/views/user/user_register.html"));
});

app.post("/loginStaff", function (req, res) {
  const { username, password } = req.body;
  const sql = "SELECT * FROM user WHERE username= ?";
  con.query(sql, [username], function (err, results) {
    if (err) {
      console.error(err);
      res.status(500).send("DB error");
    } else if (results.length != 1) {
      res.status(401).send("Wrong username and password");
    } else {
      //compare raw with hashed password
      bcrypt.compare(password, results[0].password, function (err, same) {
        if (err) {
          res.status(500).send("Password error");
        } else {
          if (same) {
            if (results[0].role == 1) {
              req.session.username = username;
              req.session.userId = results[0].userid;
              req.session.userRole = results[0].role;
              res.send("/staffHomepage");
            } else {
              res.status(401).send("you are not staff");
            }
          } else {
            res.status(401).send("Wrong password");
          }
        }
      });
    }
  });
});

////GetDataBorrow
app.get("/GetDataBorrow", (req, res) => {
  let sql =
    // "SELECT borrow.*, product.*,user.email FROM borrow JOIN product ON borrow.id = product.id JOIN user ON borrow.userid = user.userid;";
    "SELECT borrow.*, user.email, product.name FROM borrow INNER JOIN user ON borrow.userid = user.userid INNER JOIN product ON borrow.id = product.id";
  con.query(sql, (err, resu) => {
    if (err) {
      res.status(500).send("DB");
    }
    let sql = "SELECT * FROM product";
    con.query(sql, (err2, resu2) => {
      if (err2) {
        res.status(500).send("DB");
      }

      res.send({ resu: resu, resu2: resu2 });
    });
  });
});

////Disable
app.get("/Disable", (req, res) => {
  const countQuery = "SELECT * FROM product WHERE statusproduct = '1'";
  con.query(countQuery, function (err, result) {
    if (err) {
      console.error(err);
      res.status(500).send("connecttion error");
    } else {
      res.send(result);
    }
  });
});

////Available
app.get("/Available", (req, res) => {
  const countQuery = "SELECT * FROM product WHERE statusproduct = '0'";
  con.query(countQuery, function (err, result) {
    if (err) {
      console.error(err);
      res.status(500).send("connecttion error");
    } else {
      res.send(result);
    }
  });
});

////Borrowing
app.get("/Borrowing", (req, res) => {
  const countQuery = "SELECT * FROM product WHERE statusproduct = '2'";
  con.query(countQuery, function (err, result) {
    if (err) {
      console.error(err);
      res.status(500).send("connecttion error");
    } else {
      res.send(result);
    }
  });
});

// staff sidebar
app.get("/sideStaff", function (_req, res) {
  res.sendFile(path.join(__dirname, "/views/staff/staff_sidebar.html"));
});
// staff login
app.get("/loginStaff", function (_req, res) {
  res.sendFile(path.join(__dirname, "/views/staff/staff_login.html"));
});
// staff homepage
app.get("/staffHomepage", function (_req, res) {
  res.sendFile(path.join(__dirname, "/views/staff/staff_homepage.html"));
});
// staff history
app.get("/staffHistory", function (_req, res) {
  res.sendFile(path.join(__dirname, "/views/staff/staff_history.html"));
});
// staff return
app.get("/staffReturn", function (_req, res) {
  res.sendFile(path.join(__dirname, "/views/staff/staff_return.html"));
});
// staff assetlist
app.get("/staffAsset", function (_req, res) {
  res.sendFile(
    path.join(__dirname, "/views/staff/dashboard/dash_assetlist.html")
  );
});
// staff availiable
app.get("/staffAva", function (_req, res) {
  res.sendFile(
    path.join(__dirname, "/views/staff/dashboard/dash_available.html")
  );
});
// staff disable
app.get("/staffDis", function (_req, res) {
  res.sendFile(
    path.join(__dirname, "/views/staff/dashboard/dash_disable.html")
  );
});
// staff borrowing
app.get("/staffBow", function (_req, res) {
  res.sendFile(
    path.join(__dirname, "/views/staff/dashboard/dash_borrowing.html")
  );
});
// staff additem
app.get("/staffAdd", function (_req, res) {
  res.sendFile(
    path.join(__dirname, "/views/staff/dashboard/dash_additem.html")
  );
});
// staff edit
app.get("/staffEdit", function (_req, res) {
  res.sendFile(path.join(__dirname, "/views/staff/dashboard/dash_edit.html"));
});

app.post("/loginLecturer", function (req, res) {
  const { username, password } = req.body;
  const sql = "SELECT * FROM user WHERE username= ?";
  con.query(sql, [username], function (err, results) {
    if (err) {
      console.error(err);
      res.status(500).send("DB error");
    } else if (results.length != 1) {
      res.status(401).send("Wrong username and password");
    } else {
      //compare raw with hashed password
      bcrypt.compare(password, results[0].password, function (err, same) {
        if (err) {
          res.status(500).send("Password error");
        } else {
          if (same) {
            if (results[0].role == 2) {
              req.session.username = username;
              req.session.userId = results[0].userid;
              req.session.userRole = results[0].role;
              res.send("/lecturerHomepage");
            } else {
              res.status(401).send("you are not lecturer");
            }
          } else {
            res.status(401).send("Wrong password");
          }
        }
      });
    }
  });
});

// lecturer login
app.get("/loginLecturer", function (_req, res) {
  res.sendFile(path.join(__dirname, "/views/leader/lecturer_login.html"));
});
// lecturer homepage
app.get("/lecturerHomepage", function (_req, res) {
  res.sendFile(path.join(__dirname, "/views/leader/lecturer_homepage.html"));
});
// lecturer sidebar
app.get("/sideLecturer", function (_req, res) {
  res.sendFile(path.join(__dirname, "/views/leader/lecturer_sidebar.html"));
});
// lecturer history
app.get("/lecHis", function (_req, res) {
  res.sendFile(path.join(__dirname, "/views/leader/lecturer_history.html"));
});
// lecturer approve
app.get("/lecApp", function (_req, res) {
  res.sendFile(
    path.join(__dirname, "/views/leader/dashboard/lecturer_approve.html")
  );
});
// lecturer asset
app.get("/lecAss", function (_req, res) {
  res.sendFile(
    path.join(__dirname, "/views/leader/dashboard/lecturer_asset.html")
  );
});
// lecturer disapprove
app.get("/lecdis", function (_req, res) {
  res.sendFile(
    path.join(__dirname, "/views/leader/dashboard/lecturer_disapprove.html")
  );
});
// lecturer returned
app.get("/lecReturned", function (_req, res) {
  res.sendFile(
    path.join(__dirname, "/views/leader/dashboard/lec_returned.html")
  );
});
// lecturer request
app.get("/lecRe", function (_req, res) {
  res.sendFile(
    path.join(__dirname, "/views/leader/dashboard/lecturer_request.html")
  );
});

// ------------ root service ----------
app.get("/", function (_req, res) {
  res.sendFile(path.join(__dirname, "/views/role_page.html"));
});

const PORT = 3000;
app.listen(PORT, function () {
  console.log("Server is runnint at port " + PORT);
});

// role page
app.get("/rolePage", function (_req, res) {
  res.sendFile(path.join(__dirname, "/views/role_page.html"));
});

app.post("/registerUser", function (req, res) {
  const { email, username, password, repassword, role } = req.body;

  // Check if email is already in use
  const emailCheckSQL = "SELECT email FROM user WHERE email = ?";
  con.query(emailCheckSQL, [email], function (err, emailResult) {
    if (err) {
      console.error(err);
      return res.status(500).send("Server error!");
    } else if (emailResult.length > 0) {
      return res.status(401).send("Email is already in use!");
    }

    // Hash password
    bcrypt.hash(password, 10, function (err, hash) {
      if (err) {
        return res.status(500).send("Hash error!");
      } else if (
        email == "" ||
        username == "" ||
        password == "" ||
        repassword == ""
      ) {
        res.status(402).send("Please enter information");
      } else {
        const usernameCheckSQL = "SELECT username FROM user WHERE username= ?";
        con.query(usernameCheckSQL, [username], function (err, usernameResult) {
          if (err) {
            console.error(err);
            res.status(500).send("Server error!");
          } else if (usernameResult.length > 0) {
            res.status(401).send("Username has already been used!");
          } else {
            // Check password
            if (password !== repassword) {
              return res.status(401).send("Password mismatch!");
            }
            // Correct data
            const sql =
              "INSERT INTO user (email, username, password, role) VALUES (?,?,?,3)";
            con.query(
              sql,
              [email, username, hash, role],
              function (err, result) {
                if (err) {
                  console.error(err);
                  res.status(500).send("Server error inserting data!");
                } else {
                  // Save user data in session after registration
                  req.session.user = { email, username };
                  res.send("/loginUser");
                }
              }
            );
          }
        });
      }
    });
  });
});