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

async function route() {
  // (userID) => [{userID, subjectID, nickname, status, color, name}, ...]
  userSubjectRouter.get("/", async (req, res) => {
    console.log("/db/user-subject");
    const connection = await pool.getConnection(async (con) => con);
    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();
    } finally {
      connection.release();
    }
  });

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

  // (userID, subjectID, nickname, color)
  userSubjectRouter.put("/modify", async (req, res) => {
    console.log("/db/user-subject/modify");
    const connection = await pool.getConnection(async (con) => con);
    try {
      await connection.beginTransaction();
      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);
      await connection.commit();
      res.end();
    } catch (e) {
      console.log(e);
      await connection.rollback();
      res.end();
    } finally {
      connection.release();
    }
  });

  // (userID, [subjectID, ...])
  userSubjectRouter.post("/s", async (req, res) => {
    console.log("post /db/user-subject/s");
    const connection = await pool.getConnection(async (con) => con);
    const colors = [
      "3ADF00",
      "0040FF",
      "FF0000",
      "FFFF00",
      "FF00FF",
      "FF8000",
      "6E6E6E",
      "8000FF",
      "B40431",
      "2EFEF7",
    ];
    try {
      await connection.beginTransaction();
      for (const i in req.body.subList) {
        const queryString = `
        INSERT INTO \`user-subject\`
          (userID, subjectID, status, color)
        VALUES (${req.body.userID}, ${req.body.subList[i].subjectID}, 1, '${colors[i]}')`;
        await connection.query(queryString);
      }
      await connection.commit();
      res.end();
    } catch (e) {
      console.log(e);
      await connection.rollback();
      res.end();
    } finally {
      connection.release();
    }
  });
}

route();

module.exports = userSubjectRouter;