const express = require("express");
const mysql = require("mysql2/promise");
const fs = require("fs");
const router = express.Router();

async function route() {
  const [id, pw] = fs
    .readFileSync("server/libs/sql.pvdata", "utf8")
    .split("\r\n");
  const connection = await mysql.createConnection({
    host: "localhost",
    user: id,
    password: pw,
    database: "db",
  });

  // (userID, date) => schedules
  router.get("/schedules_date", 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.color
      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();
    }
  });

  // (ID) => null | name  //unused
  router.get("/subjects", async (req, res) => {
    console.log("/db/subjects");
    try {
      const queryString = `
      SELECT name FROM subjects sbj
        WHERE sbj.ID = ${req.query.ID}`;
      const [results] = await connection.query(queryString);
      res.send(results.length && results[0].name);
    } catch (e) {
      console.log(e);
      res.end();
    }
  });

  // (loginID) => null | ID(str)
  router.get("/users", async (req, res) => {
    console.log("/db/users");
    try {
      const queryString = `
      SELECT ID FROM users us
        WHERE us.loginID = '${req.query.loginID}'`;
      const [results] = await connection.query(queryString);
      res.send(results.length ? results[0].ID.toString() : null);
    } catch (e) {
      console.log(e);
      res.end();
    }
  });

  // (loginID, loginPW) => null | "correct"
  router.get("/users/check", async (req, res) => {
    console.log("/db/users/check");
    try {
      const queryString = `
      SELECT loginPW FROM users us
        WHERE us.loginID = '${req.query.loginID}'`;
      const [results] = await connection.query(queryString);
      res.send(results[0].loginPW === req.query.loginPW ? "correct" : null);
    } catch (e) {
      console.log(e);
      res.end();
    }
  });

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

  router.get("*", (req, res) => {
    console.log("/db/*");
    res.end();
  });
}

route();

module.exports = router;