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

async function route() {
  // (userID, day) => schedules
  schedules_repeatRouter.get("/", async (req, res) => {
    // console.log("/db/schedules_repeat");
    const connection = await pool.getConnection(async (con) => con);
    try {
      const queryString = `
      SELECT sc.label, sc.type, sc.description, sc.uid, sc.url, sc.detail, sbj.name, 
        us.nickname, us.color, sc.startTime, sc.endTime, "schedules_repeat" \`table\`
      FROM schedules_repeat sc
      INNER JOIN \`user-subject\` us
        ON sc.userID = us.userID
        AND sc.subjectID = us.subjectID
        AND us.status = 1
      INNER JOIN subjects sbj
        ON sc.subjectID = sbj.ID
      WHERE sc.day = "${req.query.day}"
        AND sc.userID = ${req.query.userID}
        AND sc.status = 1
      ORDER BY sc.startTime`;
      const [results] = await connection.query(queryString);
      res.send(results);
    } catch (e) {
      console.log(e);
      res.end();
    } finally {
      connection.release();
    }
  });

  // (userID, label, subjectID, type, desciption, url, status, day, startTime, endTime)
  schedules_repeatRouter.post("/", async (req, res) => {
    console.log("/db/schedules_repeat");
    const connection = await pool.getConnection(async (con) => con);
    try {
      await connection.beginTransaction();
      const queryString = `
      INSERT INTO schedules_repeat (userID, label, subjectID, type, status, day, startTime, endTime)
      VALUE (${req.body.userID}, "${req.body.label}", ${req.body.subjectID}, "${req.body.type}",
          1, ${req.body.day}, "${req.body.startTime}", "${req.body.endTime}");`;

      queryString = `
      UPDATE schedules_repeat
      SET uid = -ID
      WHERE ID = LAST_INSERT_ID()`;
      await connection.query(queryString);

      queryString = `
      SELECT sc.label, sc.type, sc.description, sc.uid, sc.url, sc.detail, sbj.name, 
        us.nickname, us.color, sc.startTime, sc.endTime, "schedules_repeat" \`table\`
      FROM schedules_repeat sc
      INNER JOIN \`user-subject\` us
        ON sc.userID = us.userID
        AND sc.subjectID = us.subjectID
        AND us.status = 1
      INNER JOIN subjects sbj
        ON sc.subjectID = sbj.ID
      WHERE sc.ID = LAST_INSERT_ID()
      ORDER BY sc.startTime`;
      [results] = await connection.query(queryString);
      await connection.commit();
      res.send(results[0]);
    } catch (e) {
      console.log(e);
      await connection.rollback();
      res.end();
    } finally {
      connection.release();
    }
  });
}
route();

module.exports = schedules_repeatRouter;