app.get("/list/apis", async (req, res) => {
  try {
    const results = await util.promisify(connection.query).bind(connection)(
      "SELECT * FROM todo ORDER BY `index_number`" // Use ORDER BY `index_number`
    );

    res.json({ results });
  } catch (e) {
    res.status(500).send({ e });
  }
});

app.post("/add-todos", async (req, res) => {
  // value of todo task
  const todo = req.body.todo;

  try {
  // Get and return the maximum value of `index_number`
  // if there is no data in the table, return 0
    const results = await util.promisify(connection.query).bind(connection)(
      `SELECT IFNULL((SELECT index_number FROM todo ORDER BY index_number DESC LIMIT 1) ,0) as max_index_number;`
    );
  // Add a new task
  // Put the contents of the task and the value obtained in the above query + 1024 into VALUES
    await util.promisify(connection.query).bind(connection)(
      `INSERT INTO todo(todo, index_number) VALUES('${todo}', ${results[0].max_index_number}+1024)`
    );
    res.redirect("/");
  } catch (e) {
    res.status(500).send({ e });
  }
});

app.post("/order-todos/:id", async (req, res) => {
  const id = req.params.id;
  // index_number of the task above the dragged and dropped task
  let prevElIndexNumber = req.body.prevElIndexNumber;
  // index_number of the task under the dragged and dropped task
  let nextElIndexNumber = req.body.nextElIndexNumber;
  // a variable containing the index_number of the dragged and dropped task
  let currElIndexNumber;

  // prevElIndexNumber === undefined, this is happended when the drag-and-drop task is at the top of the to-do list.
  // Since there is no upper task, set the index_number of the lower task - 512 as the currElIndexNumber
  if (prevElIndexNumber === undefined) {
    currElIndexNumber = nextElIndexNumber - 512;
  // nextElIndexNumber === undefined, this is happended when the dragged-and-dropped task is at the bottom of the to-do list
  // Set the index_number of the task above + 512 as the currElIndexNumber
  } else if (nextElIndexNumber === undefined) {
    currElIndexNumber = prevElIndexNumber + 512;
  // If there are tasks both above and below the dragged-and-dropped task, then
  // currElIndexNumber = (index_number of the top task + index_number of the bottom task)/2
  } else {
    currElIndexNumber = Math.floor((prevElIndexNumber + nextElIndexNumber) / 2);
  }

    try {
    // Update currElIndexNumber as the index_number of the new task
    await util.promisify(connection.query).bind(connection)(
      `UPDATE todo SET index_number = ${currElIndexNumber} where id = ${id}`
    );

    // When index_number overlaps
    if (
      Math.abs(currElIndexNumber - prevElIndexNumber) <= 1 ||
      Math.abs(currElIndexNumber - nextElIndexNumber) <= 1
    ) {
      // Get index_number in ascending order from 1~ (= orderedData), then update the table
      const orderedData = await util
        .promisify(connection.query)
        .bind(connection)(
        `SELECT *, ROW_NUMBER() OVER (ORDER BY index_number) as orderedData FROM todo;`
      );
      await Promise.all(
        orderedData.map(async (element) => {
          await util.promisify(connection.query).bind(connection)(
            `UPDATE todo SET index_number = ${element.orderedData}*1024 where id = ${element.id}`
          );
        })
      );
    }
    res.end();
  } catch (e) {
    res.status(500).send({ e });
  }
});

// fetch api and display all stored datas
const wrapper = document.getElementById("wrapper");
window.onload = async () => {
  try {
    // fetch all data of todo
    await fetch("http://localhost:3000/list-todos")
      .then(async (allToDo) => {
        return await allToDo.json();
      })
      .then((datas) => {
        datas.results.forEach((el) => {
          const todoEl = document.createElement("div");
          todoEl.classList.add("item");
          const taskId = el.id;
          const text = el.todo;

          todoEl.setAttribute("taskId", taskId);
          todoEl.innerHTML = `<span class="txt" onClick="startEditToDo(this, ${taskId})">${text}</span><i class="trash fa fa-trash" onClick="deleteToDo(this.parentNode, ${taskId})"></i><i class="icon fa fa-bars"></i>`;
          // changePostion() after dragend
          todoEl.addEventListener("dragend", () => {
            changePosition(todoEl, taskId);
          });
          wrapper.appendChild(todoEl);
        });
      });
  } catch (e) {
    console.log(e);
  }
};

async function changePosition(currEl, currElId) {
  let prevElIndexNumber;
  let nextElIndexNumber;

  try {
    // Get index_number if there is a task on top of the dragged and dropped task
    // if not, undefined
    if (currEl.previousSibling !== null) {
      const prevElId = currEl.previousSibling.getAttribute("taskId");

      await fetch("http://localhost:3000/read-todos/" + prevElId)
        .then(async (data) => {
          return await data.json();
        })
        .then((json) => {
          prevElIndexNumber = json.results[0].index_number;
        });
    }

    // Get index_number if there is a task under the drag & drop task
    // if not, undefined
    if (currEl.nextSibling != null) {
      const nextElId = currEl.nextSibling.getAttribute("taskId");
      await fetch("http://localhost:3000/read-todos/" + nextElId)
        .then(async (data) => {
          return await data.json();
        })
        .then((json) => {
          nextElIndexNumber = json.results[0].index_number;
        });
    }

    // HTTP Request
    const updateUrl = "http://localhost:3000/order-todos/" + currElId;

    await fetch(updateUrl, {
      method: "POST",
      headers: {
        "Content-type": "application/json",
      },
      body: JSON.stringify({ prevElIndexNumber, nextElIndexNumber }),
    });
  } catch (e) {
    console.log(e);
  }
}