{"size": 3558, "ext": "js", "max_stars_repo_path": "queries.js", "max_stars_repo_name": "TopGek99/employee-tracker", "max_stars_repo_stars_event_min_datetime": null, "max_stars_repo_stars_event_max_datetime": null, "max_issues_repo_path": "queries.js", "max_issues_repo_name": "TopGek99/employee-tracker", "max_issues_repo_issues_event_min_datetime": null, "max_issues_repo_issues_event_max_datetime": null, "max_forks_repo_path": "queries.js", "max_forks_repo_name": "TopGek99/employee-tracker", "max_forks_repo_forks_event_min_datetime": null, "max_forks_repo_forks_event_max_datetime": null}
{"max_stars_count": null, "max_issues_count": null, "max_forks_count": null, "avg_line_length": 47.44, "max_line_length": 175, "alphanum_fraction": 0.7698145025}
const updateManagersQuery = `UPDATE employee
SET ismanager = true, manager_id = null
WHERE role_id = 1 OR role_id = 3 OR role_id = 5 OR role_id = 7;`;

const allEmployeesQuery = `SELECT employee.id,employee.first_name,employee.last_name,role_.title,department.name_,role_.salary,CONCAT(manager.first_name," ",manager.last_name)
    AS manager
    FROM employee
    INNER JOIN role_ ON employee.role_id = role_.id
    INNER JOIN department ON role_.department_id = department.id
    JOIN employee AS manager ON manager.id = employee.manager_id
    UNION
    SELECT employee.id,employee.first_name,employee.last_name,role_.title,department.name_,role_.salary,employee.manager_id AS manager FROM employee
    INNER JOIN role_ ON employee.role_id = role_.id
    INNER JOIN department ON role_.department_id = department.id AND employee.manager_id IS NULL
    ORDER BY id ASC;`;

const employeesByDepQuery = (department) => {
  return `SELECT employee.id,employee.first_name,employee.last_name,role_.title,role_.salary,CONCAT(manager.first_name," ",manager.last_name)
AS manager
FROM employee
INNER JOIN role_ ON employee.role_id = role_.id
INNER JOIN department ON role_.department_id = department.id  AND department.name_ = "${department}"
JOIN employee AS manager ON manager.id = employee.manager_id
UNION
SELECT employee.id,employee.first_name,employee.last_name,role_.title,role_.salary,employee.manager_id AS manager FROM employee
INNER JOIN role_ ON employee.role_id = role_.id
INNER JOIN department ON role_.department_id = department.id  AND department.name_ = "${department}" AND employee.manager_id IS NULL
ORDER BY id ASC;`;
};

const employeesByManagerQuery = (manager_id) => {
  return `SELECT employee.id,employee.first_name,employee.last_name,role_.title,department.name_,role_.salary,CONCAT(manager.first_name," ",manager.last_name)
    AS manager
    FROM employee
    INNER JOIN role_ ON employee.role_id = role_.id
    INNER JOIN department ON role_.department_id = department.id
    JOIN employee AS manager ON manager.id = employee.manager_id AND employee.manager_id = ${manager_id};`;
};

const getManagersByDepQuery = (department_id) => {
  return `SELECT employee.id,CONCAT(employee.first_name," ",employee.last_name)
    AS manager
    FROM employee
    INNER JOIN role_ ON employee.role_id = role_.id
    INNER JOIN department ON role_.department_id = department.id AND employee.manager_id IS NULL AND department.id = ${department_id};`;
};

const addEmployeeQuery = (roles, managers, initialAnswers, managerAnswer) => {
  return `INSERT INTO employee (first_name,last_name,role_id,manager_id,ismanager) VALUES ('${
    initialAnswers.first_name
  }','${initialAnswers.last_name}',${
    roles.find((role) => role.title == initialAnswers.role).id
  },${
    managers.find((manager) => manager.manager == managerAnswer.manager).id
  },${
    managers.find((manager) => manager.manager == managerAnswer.manager).id ==
    null
  })`;
};

const addRoleQuery = (departments, answers) => {
  return `INSERT INTO role_ (title,salary,department_id)
VALUES ('${answers.role_title}',${answers.role_salary},${
    departments.find((department) => department.name_ == answers.department).id
  })`;
};

exports.updateManagersQuery = updateManagersQuery;
exports.allEmployeesQuery = allEmployeesQuery;
exports.employeesByDepQuery = employeesByDepQuery;
exports.employeesByManagerQuery = employeesByManagerQuery;
exports.getManagersByDepQuery = getManagersByDepQuery;
exports.addEmployeeQuery = addEmployeeQuery;
exports.addRoleQuery = addRoleQuery;
