cod-api/db/user.db.js

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

// Get all users
const getAllUsersDb = async (searchParams = {}) => {
  let queryParams = [];

  // Pagination
  if (searchParams.limit) {
    const page = Number(searchParams.page ?? 1) - 1;
    queryParams = [Number(searchParams.limit ?? 10), page];
  }

  // Search
  const { whereOrConditions, whereParamOr } = pool.buildStringOrIlike(
    [
      "u.user_fullname",
      "u.user_name",
      "u.user_email",
      "r.role_name"
    ],
    searchParams.criteria,
    queryParams
  );

  queryParams = whereParamOr ? whereParamOr : queryParams;

  // Filter
  const { whereConditions, whereParamAnd } = pool.buildFilterQuery(
    [
      { column: "u.user_fullname", param: searchParams.fullname, type: "string" },
      { column: "u.user_name", param: searchParams.username, type: "string" },
      { column: "u.user_email", param: searchParams.email, type: "string" },
      { column: "r.role_name", param: searchParams.role, type: "string" },
    ],
    queryParams
  );

  queryParams = whereParamAnd ? whereParamAnd : queryParams;

  const queryText = `
    SELECT 
      COUNT(*) OVER() AS total_data,
      u.user_id, u.user_fullname, u.user_name, u.user_email, u.user_phone,
      u.is_active, u.is_sa, u.is_approve, u.approved_by,
      approver.user_fullname AS approved_by_name,
      u.approved_at, u.created_at, u.updated_at, u.deleted_at,
      u.updated_by, u.deleted_by,
      r.role_id, r.role_name, r.role_description, r.role_level
    FROM m_users u
    LEFT JOIN m_roles r ON u.role_id = r.role_id
    LEFT JOIN m_users approver ON u.approved_by = approver.user_id
    WHERE u.deleted_at IS NULL
      ${whereConditions.length > 0 ? ` AND ${whereConditions.join(' AND ')}` : ''}
      ${whereOrConditions ? whereOrConditions : ''}
    ORDER BY u.user_id ASC
     ${searchParams.limit ? `OFFSET $2 * $1 ROWS FETCH NEXT $1 ROWS ONLY` : ''}
  `;

  const result = await pool.query(queryText, queryParams);

  const total =
    result?.recordset.length > 0
      ? parseInt(result.recordset[0].total_data, 10)
      : 0;

  return { data: result.recordset, total };
};

// Get user by ID
const getUserByIdDb = async (id) => {
  const queryText = `
    SELECT 
      u.user_id, u.user_fullname, u.user_name, u.user_email, u.user_phone,
      u.is_active, u.is_sa, u.is_approve, u.approved_by,
      approver.user_fullname AS approved_by_name,
      u.approved_at, u.created_at, u.updated_at, u.deleted_at,
      u.updated_by, u.deleted_by,
      r.role_id, r.role_name, r.role_description, r.role_level
    FROM m_users u
    LEFT JOIN m_roles r ON u.role_id = r.role_id
    LEFT JOIN m_users approver ON u.approved_by = approver.user_id
    WHERE u.user_id = $1 AND u.deleted_at IS NULL
  `;
  const result = await pool.query(queryText, [id]);
  return result.recordset[0];
};

// Get user by email
const getUserByUserEmailDb = async (email) => {
  const queryText = `
    SELECT 
      u.user_id, u.user_fullname, u.user_name, u.user_email, u.user_phone,
      u.user_password, u.is_active, u.is_sa, u.is_approve, u.role_id,
      r.role_name, r.role_description, r.role_level
    FROM m_users u
    LEFT JOIN m_roles r ON u.role_id = r.role_id
    WHERE u.user_email = $1 AND u.deleted_at IS NULL
  `;
  const result = await pool.query(queryText, [email]);
  return result.recordset[0];
};

// Get user by username
const getUserByUsernameDb = async (username) => {
  const queryText = `
    SELECT 
      u.user_id, u.user_fullname, u.user_name, u.user_email, u.user_phone,
      u.user_password, u.is_active, u.is_sa, u.is_approve, u.role_id,
      r.role_name, r.role_description, r.role_level
    FROM m_users u
    LEFT JOIN m_roles r ON u.role_id = r.role_id
    WHERE u.user_name = $1 AND u.deleted_at IS NULL
  `;
  const result = await pool.query(queryText, [username]);
  return result.recordset[0];
};

// Create user
const createUserDb = async (data) => {
  const { query: queryText, values } = pool.buildDynamicInsert("m_users", data);
  const result = await pool.query(queryText, values);
  const insertedId = result.recordset[0]?.inserted_id;
  return insertedId ? await getUserByIdDb(insertedId) : null;
};

// Update user
const updateUserDb = async (userId, data) => {
  const { query: queryText, values } = pool.buildDynamicUpdate("m_users", data, {
    user_id: userId,
  });
  await pool.query(`${queryText} AND deleted_at IS NULL`, values);
  return getUserByIdDb(userId);
};

// Approve user
const approveUserDb = async (userId, approverId) => {
  const queryText = `
    UPDATE m_users
    SET 
      is_approve = 2,
      approved_by = $1,
      approved_at = CURRENT_TIMESTAMP,
      updated_by = $1,
      updated_at = CURRENT_TIMESTAMP
    WHERE user_id = $2 AND deleted_at IS NULL
  `;
  await pool.query(queryText, [approverId, userId]);
  return true;
};

// Reject user
const rejectUserDb = async (userId, approverId) => {
  const queryText = `
    UPDATE m_users
    SET 
      is_approve = 0,
      approved_by = $1,
      approved_at = CURRENT_TIMESTAMP,
      updated_by = $1,
      updated_at = CURRENT_TIMESTAMP
    WHERE user_id = $2 AND deleted_at IS NULL
  `;
  await pool.query(queryText, [approverId, userId]);
  return true;
}

// Change user password
const changeUserPasswordDb = async (userId, newPassword) => {
  const queryText = `
    UPDATE m_users
    SET user_password = $1, updated_at = CURRENT_TIMESTAMP
    WHERE user_id = $2 AND deleted_at IS NULL
  `;
  await pool.query(queryText, [newPassword, userId]);
  return true;
};

// Soft delete user
const deleteUserDb = async (userId, deletedBy) => {
  const queryText = `
    UPDATE m_users
    SET 
      deleted_at = CURRENT_TIMESTAMP,
      deleted_by = $1,
      is_active = 0
    WHERE user_id = $2 
      AND deleted_at IS NULL
  `;
  await pool.query(queryText, [deletedBy, userId]);
  return true;
};

module.exports = {
  getAllUsersDb,
  getUserByIdDb,
  getUserByUserEmailDb,
  getUserByUsernameDb,
  createUserDb,
  updateUserDb,
  approveUserDb,
  rejectUserDb,
  changeUserPasswordDb,
  deleteUserDb,
};