const mysql2 = require("mysql2/promise");
const fs = require("fs");

const [id, pw] = fs
  .readFileSync("server/libs/sql.pvdata", "utf8")
  .split("\r\n");
const connectOption = {
  host: "extended-calendar-db.cihyg6ssq3la.ap-northeast-2.rds.amazonaws.com",
  user: id,
  password: pw,
  database: "db",
};
const pool = mysql2.createPool(connectOption);

async function jcalToSQL(jcal, userID) {
  const connection = await pool.getConnection(async (con) => con);
  try {
    await connection.beginTransaction();
    const commonCols = [
      "userID",
      "uid",
      "label",
      "subjectID",
      "type",
      "description",
      "url",
      "detail",
    ];
    const dateScheCols = [...commonCols, "date"];
    const timeScheCols = [...commonCols, "date", "startTime", "endTime"];
    const dateQueryString =
      "INSERT IGNORE INTO schedules_date (" +
      dateScheCols.join(",") +
      ") VALUES ?";
    const timeQueryString =
      "INSERT IGNORE INTO schedules_time (" +
      timeScheCols.join(",") +
      ") VALUES ?";

    const dateSchedules = [];
    const timeSchedules = [];
    for (const sche of jcal) {
      sche.userID = userID;
      sche.type = "assignment";
      if (sche.scheType === "date")
        dateSchedules.push(dateScheCols.map((col) => sche[col]));
      else if (sche.scheType === "time")
        timeSchedules.push(timeScheCols.map((col) => sche[col]));
      else console.log("unexpected scheType", sche);
    }

    let [result] = await connection.query(dateQueryString, [dateSchedules]);
    console.log("ICAL schedules_date | " + result.affectedRows);
    [result] = await connection.query(timeQueryString, [timeSchedules]);
    console.log("ICAL schedules_time | " + result.affectedRows);
    await connection.commit();
  } catch (e) {
    console.log(e);
    await connection.rollback();
  } finally {
    connection.release();
  }
}

// iCal의 소유주를 DB에 등록 후 userID get
// subjectID가 subjects, userID가 users에 있어야함

module.exports = { jcalToSQL, connectOption, pool };