const express = require("express");
const mysql2 = require("mysql2/promise");
const { connectOption } = require("../libs/MySQL");
const userSubjectRouter = express.Router();

async function route() {
  const connection = await mysql2.createConnection(connectOption);

  // (userID) => [{userID, subjectID, nickname, status, color, name}, ...]
  userSubjectRouter.get("/", async (req, res) => {
    console.log("/db/user-subject");
    try {
      const queryString = `
      SELECT us.userID, us.subjectID, us.nickname, us.status, us.color, sub.name
      FROM \`user-subject\` us
      INNER JOIN subjects sub
        ON us.subjectID = sub.ID
      WHERE us.userID = ${req.query.userID}`;
      const [results] = await connection.query(queryString);
      res.send(results);
    } catch (e) {
      console.log(e);
      res.end();
    }
  });

  // (userID, subjectID, status)
  userSubjectRouter.put("/check", async (req, res) => {
    console.log("/db/user-subject/check");
    try {
      const queryString = `
      UPDATE \`user-subject\`
      SET status = ${req.body.status}
      WHERE userID = ${req.body.userID}
        AND subjectID = ${req.body.subjectID}`;
      await connection.query(queryString);
      res.end();
    } catch (e) {
      console.log(e);
      res.end();
    }
  });

  // (userID, subjectID, nickname, color)
  userSubjectRouter.put("/modify", async (req, res) => {
    console.log("/db/user-subject/modify");
    try {
      const queryString = `
        UPDATE \`user-subject\`
        SET color = '${req.body.color}',
          nickname = '${req.body.nickname}'
        WHERE userID = ${req.body.userID}
          AND subjectID = ${req.body.subjectID}`;
      await connection.query(queryString);
      res.end();
    } catch (e) {
      console.log(e);
      res.end();
    }
  });
}
route();

module.exports = userSubjectRouter;