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

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

  // (userID, date) => schedules
  schedules_dateRouter.get("/", async (req, res) => {
    // console.log("/db/schedules_date");
    try {
      const queryString = `
      SELECT sc.label, sc.type, sc.description, sc.url, sc.detail,
        sbj.name, us.nickname, us.color, sc.uid, "schedules_date" \`table\`
      FROM schedules_date 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.date = "${req.query.date}"
        AND sc.userID = ${req.query.userID}
        AND sc.status = 1`;
      const [results] = await connection.query(queryString);
      res.send(results);
    } catch (e) {
      console.log(e);
      res.end();
    }
  });

  // (userID, label, subjectID, type, description, url, date)
  schedules_dateRouter.post("/", async (req, res) => {
    console.log("post /db/schedules_date");
    try {
      let queryString = `
      INSERT INTO \`schedules_date\`
        (userID, label, subjectID, type, description, url, date, status)
      VALUES (${req.body.userID}, "${req.body.label}", ${req.body.subjectID}, "${req.body.type}",
        "${req.body.description}", "${req.body.url}", "${req.body.date}", 1);`;
      await connection.query(queryString);

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

      queryString = `      
      SELECT sc.label, sc.type, sc.description, sc.url, sc.detail,
      sbj.name, us.nickname, us.color, sc.uid, "schedules_date" \`table\`
      FROM schedules_date 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()`;
      [results] = await connection.query(queryString);
      res.send(results[0]);
    } catch (e) {
      console.log(e);
      res.end();
    }
  });
}
route();

module.exports = schedules_dateRouter;