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

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

  // (userID, date) => schedules
  schedules_timeRouter.get("/", async (req, res) => {
    // console.log("/db/schedules_time");
    try {
      const queryString = `
      SELECT sc.label, sc.type, sc.description, sc.url, sc.detail, sbj.name, us.nickname, us.color, sc.uid, sc.startTime, sc.endTime
      FROM schedules_time 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
      ORDER BY sc.endTime`;
      const [results] = await connection.query(queryString);
      res.send(results);
    } catch (e) {
      console.log(e);
      res.end();
    }
  });
}
route();

module.exports = schedules_timeRouter;