text: //dependencies
  const mysql = require("mysql");
  const inquirer = require("inquirer");
  const figlet = require("figlet");
  
  //figleting the APP Name
  figlet("Employee \n \n Manager", (err, data) => {
      if (err) throw err;
      console.log(data);
  })
  
  //database connection
  const connection = mysql.createConnection({
      host: "localhost",
      port: 3306,
      user: "root",
      password: "root",
      database: "employee_management_db"
  });
  
  connection.connect(err => {
      if (err) throw err;
      console.log('connection established!');
      start();
  });
  
  // Main Function that calls all other functions to add Departments roles employees, View them and update them as well
  function start() {
      inquirer.prompt({
          name: "action",
          type: "list",
          message: "What would you like to do?",
          choices: [
              "View all departments",
              "View all roles",
              "View all employees",
              "Add a department",
              "Add a role",
              "Add an employee",
              "Update employee role",
              "Exit"
          ]
      }).then((answer) => {
          switch (answer.action) {
              case "View all departments":
                  viewDepartments();
                  break;
  
              case "View all roles":
                  viewRoles();
                  break;
  
              case "View all employees":
                  viewEmployees();
                  break;
  
              case "Add a department":
                  addDepartment();
                  break;
  
              case "Add a role":
                  addRole();
                  break;
  
              case "Add an employee":
                  addEmployee();
                  break;
  
              case "Update employee role":
                  update();
                  break;
  
              case "Exit":
                  connection.end();
                  break;
          }
      });
  }
  
  // Functions that are called above
  
  
  // View all departments
  function viewDepartments() {
      connection.query("SELECT * FROM department", (err, data) => {
          if (err) throw err;
          console.log("Displaying Departments:");
          console.table(data);
          start();
      });
  }
  
  //View all roles
  function viewRoles() {
      connection.query("SELECT * FROM role", (err, data) => {
          if (err) throw err;
          console.log("Displaying Roles:");
          console.table(data);
          start();
      });
  }
  
  //View all employees
  function viewEmployees() {
      connection.query("SELECT * FROM employee", (err, data) => {
          if (err) throw err;
          console.log("Displaying Employees:");
          console.table(data);
          start();
      });
  }
  
  //Add a department
  function addDepartment() {
      inquirer.prompt([
          {
              name: "department",
              type: "input",
              message: "What is the New Departments Name?",
              validate: (value) => {
                  if (value) {
                      return true;
                  } else {
                      console.log("Please Enter Department Name.");
                  }
              }
          },
      ]).then(answer => {
          connection.query(
              "INSERT INTO department SET ?",
              {
                  name: answer.department
              },
              (err) => {
                  if (err) throw err;
                  console.log(`${answer.department} has been added to Departments!`);
                  start();
              }
          );
      });
  }
  
  //Add a role
  function addRole() {
      const sql = "SELECT * FROM department";
      connection.query(sql, (err, results) => {
          if (err) throw err;
  
          inquirer.prompt([
              {
                  name: "title",
                  type: "input",
                  message: "What is the New Role?",
                  validate: (value) => {
                      if (value) {
                          return true;
                      } else {
                          console.log("Please Enter Role Title.");
                      }
                  }
              },
              {
                  name: "salary",
                  type: "input",
                  message: "What is this Role's Salary?",
                  validate: (value) => {
                      if (isNaN(value) === false) {
                          return true;
                      }
                      console.log("Please Enter Salary");
                  }
              },
              {
                  name: "department",
                  type: "rawlist",
                  choices: () => {
                      let choiceArray = [];
                      for (let i = 0; i < results.length; i++) {
                          choiceArray.push(results[i].name);
                      }
                      return choiceArray;
                  },
                  message: "What department does this Role Belong To?",
              }
          ]).then(answer => {
              let chosenDept;
              for (let i = 0; i < results.length; i++) {
                  if (results[i].name === answer.department) {
                      chosenDept = results[i];
                  }
              }
  
              connection.query(
                  "INSERT INTO role SET ?",
                  {
                      title: answer.title,
                      salary: answer.salary,
                      department_id: chosenDept.id
                  },
                  (err) => {
                      if (err) throw err;
                      console.log(`${answer.title} has been added!`);
                      start();
                  }
              )
          });
      });
  }
  
  //Add an employee
  function addEmployee() {
      const sql = "SELECT * FROM employee, role";
      connection.query(sql, (err, results) => {
          if (err) throw err;
  
          inquirer.prompt([
              {
                  name: "firstName",
                  type: "input",
                  message: "Employee's first name?",
                  validate: (value) => {
                      if (value) {
                          return true;
                      } else {
                          console.log("Please enter First Name.");
                      }
                  }
              },
              {
                  name: "lastName",
                  type: "input",
                  message: "Employee's Last Name?",
                  validate: (value) => {
                      if (value) {
                          return true;
                      } else {
                          console.log("Please enter Last Name.");
                      }
                  }
              },
              {
                  name: "role",
                  type: "rawlist",
                  choices: () => {
                      let choiceArray = [];
                      for (let i = 0; i < results.length; i++) {
                          choiceArray.push(results[i].title);
                      }
                      //remove duplicates
                      let cleanChoiceArray = [...new Set(choiceArray)];
                      return cleanChoiceArray;
                  },
                  message: "What is the Employee's Role?"
              }
          ]).then(answer => {
              let chosenRole;
  
              for (let i = 0; i < results.length; i++) {
                  if (results[i].title === answer.role) {
                      chosenRole = results[i];
                  }
              }
  
              connection.query(
                  "INSERT INTO employee SET ?",
                  {
                      first_name: answer.firstName,
                      last_name: answer.lastName,
                      role_id: chosenRole.id,
                  },
                  (err) => {
                      if (err) throw err;
                      console.log(`${answer.firstName} ${answer.lastName} has been added as a/an ${answer.role}`);
                      start();
                  }
              )
          });
      });
  }
  
  //Update employee role
  function update() {
      connection.query("SELECT * FROM employee, role", (err, results) => {
          if (err) throw err;
  
          inquirer.prompt([
              {
                  name: "employee",
                  type: "rawlist",
                  choices: () => {
                      let choiceArray = [];
                      for (let i = 0; i < results.length; i++) {
                          choiceArray.push(results[i].last_name);
                      }
                      //remove duplicates
                      let cleanChoiceArray = [...new Set(choiceArray)];
                      return cleanChoiceArray;
                  },
                  message: "Which employee would you like to update?"
              },
              {
                  name: "role",
                  type: "rawlist",
                  choices: () => {
                      let choiceArray = [];
                      for (let i = 0; i < results.length; i++) {
                          choiceArray.push(results[i].title);
                      }
                      //remove duplicates
                      let cleanChoiceArray = [...new Set(choiceArray)];
                      return cleanChoiceArray;
                  },
                  message: "What is the employee's new role?"
              }
          ]).then(answer => {
              let chosenEe;
              let chosenRole;
  
              for (let i = 0; i < results.length; i++) {
                  if (results[i].last_name === answer.employee) {
                      chosenEe = results[i];
                  }
              }
  
              for (let i = 0; i < results.length; i++) {
                  if (results[i].title === answer.role) {
                      chosenRole = results[i];
                  }
              }
  
              connection.query(
                  "UPDATE employee SET ? WHERE ?",
                  [
                      {
                          role_id: chosenRole,
                      },
                      {
                          last_name: chosenEe,
                      }
                  ],
                  (err) => {
                      if (err) throw err;
                      console.log(`Role has been updated!`);
                      start();
                  }
              )
          })
      })
  }
  
  