{"size": 5100, "ext": "js", "max_stars_repo_path": "db/index.js", "max_stars_repo_name": "hyukjun-kwon/homework-12", "max_stars_repo_stars_event_min_datetime": null, "max_stars_repo_stars_event_max_datetime": null, "max_issues_repo_path": "db/index.js", "max_issues_repo_name": "hyukjun-kwon/homework-12", "max_issues_repo_issues_event_min_datetime": null, "max_issues_repo_issues_event_max_datetime": null, "max_forks_repo_path": "db/index.js", "max_forks_repo_name": "hyukjun-kwon/homework-12", "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": 26.4248704663, "max_line_length": 167, "alphanum_fraction": 0.5923529412}
const mysql = require('mysql');

const db = {};

const connection = mysql.createPool({
  host     : 'localhost',
  user     : 'root',
  password : 'myPass',
  database : 'employees'
});

db.budgetByDepartment = (departmentId) => {
  return new Promise((resolve, reject) => {
    connection.query(`
      SELECT SUM(r.salary)
      FROM employee e
      JOIN role r
        ON e.role_id = r.id
      WHERE r.department_id = ?
    `, [ departmentId ], (err, result) => {
      if(err) return reject(err);
      return resolve(result[0]['SUM(r.salary)']);
    });
  });
};

db.allEmployeesInfo = () => {
  return new Promise((resolve, reject) => {
    connection.query(`
    SELECT 
      e.id as "employee id", 
      e.first_name as "first name", 
      e.last_name as "last name", 
      r.title as "job title", 
      r.salary, 
      d.name as department 
    FROM employee e 
    JOIN role r 
      ON e.role_id=r.id 
    JOIN department d 
      ON r.department_id = d.id 
    ORDER BY e.id ASC`, (err, result) => {
      if(err) return reject(err);
      return resolve(result);
    })
  })
}

db.findAllEmployees = () => {
  return new Promise((resolve, reject) => {
    connection.query(`SELECT * FROM employee`, (err, result) => {
      if(err) return reject(err);
      return resolve(result);
    });
  });
};

db.findAllDepartments = () => {
  return new Promise((resolve, reject) => {
    connection.query(`SELECT * FROM department`, (err, result) => {
      if(err) return reject(err);
      return resolve(result);
    });
  });
};

db.findAllRoles = () => {
  return new Promise((resolve, reject) => {
    connection.query(`SELECT * FROM role`, (err, result) => {
      if(err) return reject(err);
      return resolve(result);
    });
  });
};

db.findAllEmployeesByDepartment = (departmentId) => {
  return new Promise((resolve, reject) => {
    connection.query(`
    SELECT 
      d.name, 
      e.first_name, 
      e.last_name 
    FROM employee e 
    JOIN role r 
      ON e.role_id=r.id 
    JOIN department d 
      ON d.id=r.department_id 
    WHERE r.department_id = ?`, [ departmentId ], (err, result) => {
      if(err) return reject(err);
      return resolve(result);
    });
  });
};

db.findAllEmployeesByManager = (managerId) => {
  return new Promise((resolve, reject) => {
    connection.query(`SELECT * FROM employee WHERE manager_id = ?`, [ managerId ], (err, result) => {
      if(err) return reject(err);
      return resolve(result);
    });
  });
};

db.removeEmployee = (employeeId) => {
  return new Promise((resolve, reject) => {
    connection.query(`DELETE FROM employee WHERE id = ?`, [ employeeId ], (err, result) => {
      if(err) return reject(err);
      return resolve(result);
    });
  });
};

db.updateEmployeeRole = (employeeId, roleId) => {
  return new Promise((resolve, reject) => {
    connection.query(`UPDATE employee SET role_id = ? WHERE id = ?`, [ roleId, employeeId ], (err, result) => {
      if(err) return reject(err);
      return resolve(result);
    });
  });
};

db.findAllPossibleManagers = (employeeId) => {
  return new Promise((resolve, reject) => {
    connection.query(`SELECT * FROM employee WHERE id != ?`, [ employeeId ], (err, result) => {
      if(err) return reject(err);
      return resolve(result);
    })
  })
}

db.updateEmployeeManager = (employeeId, managerId) => {
  return new Promise((resolve, reject) => {
    connection.query(`UPDATE employee SET manager_id = ? WHERE id = ?`, [ managerId, employeeId ], (err, result) => {
      if(err) return reject(err);
      return resolve(result);
    });
  });
};

db.createRole = (role) => {
  return new Promise((resolve, reject) => {
    connection.query(`
    INSERT INTO role (title, salary, department_id) 
    VALUES (?, ?, ?)`, [ role.title, role.salary, role.department_id ], (err, result) => {
      if(err) return reject(err);
      return resolve(result);
    });
  });
};

db.removeRole = (roleId) => {
  return new Promise((resolve, reject) => {
    connection.query(`DELETE FROM role WHERE id = ?`, [ roleId ], (err, result) => {
      if(err) return reject(err);
      return resolve(result);
    });
  });
};

db.createDepartment = (department) => {
  return new Promise((resolve, reject) => {
    connection.query(`INSERT INTO department (name) 
    VALUES (?)`, [ department ], (err, result) => {
      if(err) return reject(err);
      return resolve(result);
    });
  });
};

db.removeDepartment = (departmentId) => {
  return new Promise((resovle, reject) => {
    connection.query(`DELETE FROM department WHERE id = ?`, [ departmentId ], (err, result) => {
      if(err) return reject(err);
      return resolve(result);
    });
  });
};

db.createEmployee = (employee) => {
  return new Promise((resolve, reject) => {
    connection.query(`INSERT INTO employee 
    (first_name, last_name, role_id, manager_id) VALUES (?,?,?,?)`, [employee.first_name,employee.last_name, employee.role_id, employee.manager_id], (err, result) => {
      if(err) return reject(err);
      return resolve(result);
    });
  });
};




module.exports = db;
