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

require("dotenv").config();

const poolOptions = {
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_DATABASE,
  connectionLimit: process.env.DB_CONNECTION_LIMIT,
};
const pool = mysql2.createPool(poolOptions);

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, pool };