text: const inquirer = require("inquirer");
  const mysql = require("mysql");
  const cTable = require("console.table");
  
  const util = require("util");
  
  const connection = mysql.createConnection({
    host: "localhost",
    port: 3306,
    user: "root",
    password: "password",
    database: "employees_db",
  });
  
  connection.connect(function (err) {
    if (err) throw err;
    console.log("connected as id " + connection.threadId);
    runTracker();
  });
  
  connection.query = util.promisify(connection.query);
  
  function runTracker() {
    inquirer
      .prompt([
        {
          type: "list",
          name: "selection",
          message: "What would you like to do?",
          choices: [
            "Add a new Department",
            "Add a new Role",
            "Add a new Employee",
            "View All Departments",
            "View All Roles",
            "View All Employees",
            "Delete a Department",
            "Delete a Role",
            "Delete an Employee",
            "Update Employee Role",
            "Exit",
          ],
        },
      ])
      .then(({ selection }) => {
        switch (selection) {
          case "Add a new Department":
            addDepartment();
            break;
          case "Add a new Role":
            addRole();
            break;
          case "Add a new Employee":
            addEmployee();
            break;
          case "View All Departments":
            viewDepartments();
            break;
          case "View All Roles":
            viewRoles();
            break;
          case "View All Employees":
            viewEmployees();
            break;
          case "Delete a Department":
            deleteDepartment();
            break;
          case "Delete a Role":
            deleteRole();
            break;
          case "Delete an Employee":
            deleteEmployee();
            break;
          case "Update Employee Role":
            updateEmployeeRole();
            break;
          case "Exit":
            exit();
          default:
            console.log("Hope you have a wonderful day!");
        }
      });
  }
  
  // Add Department Section
  
  const addDepartment = () => {
    console.log("add department here");
    inquirer
      .prompt({
        name: "department",
        message: "What Department would you like to add?",
        type: "input",
      })
      .then(({ department }) => {
        connection.query(
          `INSERT INTO department SET ?`,
          {
            name: department,
          },
          (err) => {
            if (err) throw err;
            console.log(`New Department: ${department} was added!`);
            viewDepartments();
          }
        );
      });
  };
  
  // Add Role Section
  
  function addRole() {
    connection.query("SELECT * FROM department", (err, res) => {
      if (err) throw err;
      inquirer
        .prompt([
          {
            name: "title",
            type: "input",
            message: "What is the role's title?",
          },
          {
            name: "salary",
            type: "input",
            message: "What is the role's salary?",
          },
          {
            name: "deptName",
            type: "list",
            message: "What department does this role belong to?",
            choices: function () {
              let choiceArray = [];
              for (let i = 0; i < res.length; i++) {
                choiceArray.push({ name: res[i].name, value: res[i].id });
              }
              return choiceArray;
            },
          },
        ])
        .then((userInput) => {
          connection.query(
            "INSERT INTO role SET ?",
            {
              title: userInput.title,
              salary: userInput.salary,
              department_id: userInput.deptName,
            },
            function (err, res) {
              if (err) throw err;
              runTracker();
            }
          );
        });
    });
  }
  
  // Add Employee Section
  
  const addEmployee = () => {
    console.log("Adding a new employee.");
    let addNewEmployee = {};
    connection.query("SELECT * FROM role", (err, res) => {
      if (err) throw err;
      console.log(res);
      const departmentChoices = res.map((row) => ({
        value: row.id,
        name: row.name,
      }));
  
      return inquirer
        .prompt([
          {
            type: "input",
            name: "first_name",
            message: "What is the employee's first name?",
          },
          {
            type: "input",
            name: "last_name",
            message: "What is the employee's last name?",
          },
          {
            type: "list",
            name: "allRoles",
            message: "What is the employee's role?",
            choices: function () {
              let rolesArray = [];
              for (let i = 0; i < res.length; i++) {
                rolesArray.push(res[i].title);
              }
              return rolesArray;
            },
          },
          {
            type: "list",
            name: "whichDepartment",
            message: "What department is the employee in?",
            choices: departmentChoices,
          },
        ])
        .then((response) => {
          {
            addNewEmployee.first_name = response.first_name;
            addNewEmployee.last_name = response.last_name;
            connection.query(
              `SELECT * FROM role WHERE title = ?`,
              response.allRoles,
              (err, res) => {
                if (err) throw err;
                addNewEmployee.role_id = res[0].id;
              }
            );
  
            connection.query("SELECT * FROM employee", (err, res) => {
              if (err) throw err;
              inquirer
                .prompt([
                  {
                    type: "list",
                    name: "manager_name",
                    message: "Who is the employee's manager?",
                    choices: function () {
                      let managerArray = [];
                      for (let i = 0; i < res.length; i++) {
                        managerArray.push(res[i].first_name);
                      }
                      return managerArray;
                    },
                  },
                ])
                .then(
                  (response) => {
                    connection.query(
                      "SELECT id FROM employee WHERE first_name = ?",
                      response.manager_name,
                      (err, res) => {
                        if (err) throw err;
                        addNewEmployee.manager_id = res[0].id;
  
                        connection.query(
                          "INSERT INTO employee SET ?",
                          addNewEmployee,
                          (err, res) => {
                            if (err) throw err;
                            runTracker();
                          }
                        );
                      }
                    );
                  },
                  function (err, res) {
                    if (err) throw err;
                    console.log("New employee added!");
                  }
                );
              runTracker();
            });
          }
        })
        .catch((err) => {
          console.log(err);
        });
    });
  };
  
  // View Department Section
  
  viewDepartments = () => {
    console.log("View all departments.");
    connection.query(`SELECT * FROM department`, (err, res) => {
      if (err) throw err;
      console.table(res);
      console.log("-----------------");
      runTracker();
    });
  };
  
  // View Role Section
  
  viewRoles = () => {
    console.log("View all roles.");
    connection.query(`SELECT * FROM role`, (err, res) => {
      if (err) throw err;
      console.table(res);
      console.log("-----------------");
      runTracker();
    });
  };
  
  // View Employee Section
  
  const viewEmployees = () => {
    connection.query("select * from employee;", (err, res) => {
      console.table(res);
      console.log("-----------------");
      runTracker();
    });
  };
  
  // Delete a Department Section
  
  function deleteDepartment() {
    connection.query("SELECT * FROM department", function (err, results) {
      if (err) throw err;
      inquirer
        .prompt([
          {
            name: "removeDept",
            type: "list",
            choices: function () {
              let choiceArray = [];
              for (var i = 0; i < results.length; i++) {
                choiceArray.push(results[i].name);
              }
              return choiceArray;
            },
            message: "Which department would you like to remove?",
          },
        ])
        .then(function (answer) {
          let query = "DELETE FROM department WHERE name = ?;";
          connection.query(query, answer.removeDept, function (err, res) {
            if (err) throw err;
            console.log("Department successfully deleted");
            runTracker();
          });
        });
    });
  }
  
  // Delete a Role Section
  function deleteRole() {
    connection.query("SELECT * FROM role", function (err, results) {
      if (err) throw err;
      inquirer
        .prompt([
          {
            name: "removeRole",
            type: "list",
            choices: function () {
              let choiceArray = [];
              for (var i = 0; i < results.length; i++) {
                choiceArray.push(results[i].title);
              }
              return choiceArray;
            },
            message: "Which role would you like to remove?",
          },
        ])
        .then(function (answer) {
          let query = "DELETE FROM role WHERE title = ?;";
          connection.query(query, answer.removeRole, function (err, res) {
            if (err) throw err;
            console.log("Role successfully deleted");
            runTracker();
          });
        });
    });
  }
  
  // Delete an Employee Section
  function deleteEmployee() {
    connection.query("SELECT * FROM employee", function (err, results) {
      if (err) throw err;
      inquirer
        .prompt([
          {
            name: "removeEmployee",
            type: "list",
            choices: function () {
              let choiceArray = [];
              for (var i = 0; i < results.length; i++) {
                choiceArray.push(results[i].first_name);
              }
              return choiceArray;
            },
            message: "Which employee would you like to remove?",
          },
        ])
        .then(function (answer) {
          let query = "DELETE FROM employee WHERE first_name = ?;";
          connection.query(query, answer.removeEmployee, function (err, res) {
            if (err) throw err;
            console.log("Employee successfully deleted");
            runTracker();
          });
        });
    });
  }
  
  // Update Employee Role Section
  
  function updateEmployeeRole() {
    let newRole = {};
  
    connection.query(
      "SELECT employee.id, employee.first_name, employee.last_name, role.title, role.salary, department.name AS department, e2.first_name AS manager FROM employee LEFT JOIN employee AS e2 ON e2.id = employee.manager_id JOIN role ON employee.role_id = role.id JOIN department ON role.department_id = department.id ORDER BY employee.id",
      (err, res) => {
        if (err) throw err;
        inquirer
          .prompt([
            {
              name: "updateEmployee",
              type: "list",
              message: "Which employee would you like to update?",
              choices: function () {
                let choiceArray = [];
                for (var i = 0; i < res.length; i++) {
                  choiceArray.push(res[i].first_name);
                }
                return choiceArray;
              },
            },
          ])
          .then(function (userInput) {
            newRole.first_name = userInput.updateEmployee;
  
            connection.query("SELECT * FROM role", (err, res) => {
              if (err) throw err;
              inquirer
                .prompt([
                  {
                    name: "updateRole",
                    type: "list",
                    message:
                      "What would you like you to change their role title to?",
                    choices: function () {
                      let choiceArray = [];
                      for (var i = 0; i < res.length; i++) {
                        choiceArray.push(res[i].title);
                      }
                      return choiceArray;
                    },
                  },
                ])
                .then(function (userInput) {
                  connection.query(
                    "SELECT * FROM role WHERE title = ?",
                    userInput.updateRole,
                    (err, res) => {
                      if (err) throw err;
  
                      newRole.role_id = res[0].id;
  
                      connection.query(
                        "UPDATE employee SET role_id = ? WHERE first_name = ?",
                        [newRole.role_id, newRole.first_name],
                        (err, res) => {
                          if (err) throw err;
                          runTracker();
                        }
                      );
                    }
                  );
                });
            });
          });
      }
    );
  }
  
  // Exit/Quit Section
  
  function exit() {
    connection.end();
  }
  