const pool = require("../config/db");

const getAllDepartments = async () => {
  const [rows] = await pool.query('SELECT * FROM departments');
  return rows;
};

const getDepartmentById = async (id) => {
  const [rows] = await pool.query('SELECT * FROM departments WHERE department_id = ?', [id]);
  return rows.length ? rows[0] : null;
};

const createDepartment = async (department_name, department_id = null) => {
  let id = department_id;
  if (id == null || id === '') {
    const [maxRow] = await pool.query('SELECT COALESCE(MAX(department_id), 0) + 1 AS next_id FROM departments');
    id = maxRow[0].next_id;
  }
  await pool.query('INSERT INTO departments (department_id, department_name) VALUES (?, ?)', [id, department_name]);
  return id;
};

const updateDepartment = async (id, department_name) => {
  const [result] = await pool.query('UPDATE departments SET department_name = ? WHERE department_id = ?', [department_name, id]);
  return result.affectedRows > 0;
};

const deleteDepartment = async (id) => {
  const [result] = await pool.query('DELETE FROM departments WHERE department_id = ?', [id]);
  return result.affectedRows > 0;
};

module.exports = {
  getAllDepartments,
  getDepartmentById,
  createDepartment,
  updateDepartment,
  deleteDepartment
};

