RESTAPI_SERVER/database/user-queries.js

const { getConnection } = require('./connection');

// 모든 사용자 조회
async function getAllUsers() {
  let connection;
  try {
    connection = await getConnection();
    const result = await connection.execute(
      `SELECT USER_ID, USER_NAME, DEPT_CODE, REG_DATE 
       FROM USER_INFO_ORA 
       ORDER BY REG_DATE DESC`
    );
    
    return result.rows.map(row => ({
      USER_ID: row[0],
      USER_NAME: row[1],
      DEPT_CODE: row[2],
      REG_DATE: row[3]
    }));
  } catch (err) {
    console.error('사용자 목록 조회 실패:', err);
    throw err;
  } finally {
    if (connection) {
      await connection.close();
    }
  }
}

// 특정 사용자 조회
async function getUserById(userId) {
  let connection;
  try {
    connection = await getConnection();
    const result = await connection.execute(
      `SELECT USER_ID, USER_NAME, DEPT_CODE, REG_DATE 
       FROM USER_INFO_ORA 
       WHERE USER_ID = :userId`,
      [userId]
    );
    
    if (result.rows.length === 0) {
      return null;
    }
    
    const row = result.rows[0];
    return {
      USER_ID: row[0],
      USER_NAME: row[1],
      DEPT_CODE: row[2],
      REG_DATE: row[3]
    };
  } catch (err) {
    console.error('사용자 조회 실패:', err);
    throw err;
  } finally {
    if (connection) {
      await connection.close();
    }
  }
}

// 사용자 생성
async function createUser(userData) {
  let connection;
  try {
    connection = await getConnection();
    
    const result = await connection.execute(
      `INSERT INTO USER_INFO_ORA (USER_ID, USER_NAME, DEPT_CODE, REG_DATE) 
       VALUES (:userId, :userName, :deptCode, SYSDATE)`,
      {
        userId: userData.USER_ID,
        userName: userData.USER_NAME,
        deptCode: userData.DEPT_CODE
      },
      { autoCommit: true }
    );
    
    return result.rowsAffected;
  } catch (err) {
    console.error('사용자 생성 실패:', err);
    throw err;
  } finally {
    if (connection) {
      await connection.close();
    }
  }
}

// 사용자 정보 수정
async function updateUser(userId, userData) {
  let connection;
  try {
    connection = await getConnection();
    
    const result = await connection.execute(
      `UPDATE USER_INFO_ORA 
       SET USER_NAME = :userName, DEPT_CODE = :deptCode 
       WHERE USER_ID = :userId`,
      {
        userId: userId,
        userName: userData.USER_NAME,
        deptCode: userData.DEPT_CODE
      },
      { autoCommit: true }
    );
    
    return result.rowsAffected;
  } catch (err) {
    console.error('사용자 수정 실패:', err);
    throw err;
  } finally {
    if (connection) {
      await connection.close();
    }
  }
}

// 사용자 삭제
async function deleteUser(userId) {
  let connection;
  try {
    connection = await getConnection();
    
    const result = await connection.execute(
      `DELETE FROM USER_INFO_ORA WHERE USER_ID = :userId`,
      [userId],
      { autoCommit: true }
    );
    
    return result.rowsAffected;
  } catch (err) {
    console.error('사용자 삭제 실패:', err);
    throw err;
  } finally {
    if (connection) {
      await connection.close();
    }
  }
}

// USER_INFO_ORA 테이블 생성
async function createUserTable() {
  let connection;
  try {
    connection = await getConnection();
    
    // 테이블이 존재하는지 확인
    const checkTable = await connection.execute(
      `SELECT COUNT(*) as count FROM user_tables WHERE table_name = 'USER_INFO_ORA'`
    );
    
    if (checkTable.rows[0][0] === 0) {
      // 테이블 생성
      await connection.execute(
        `CREATE TABLE USER_INFO_ORA (
          USER_ID VARCHAR2(100) PRIMARY KEY,
          USER_NAME VARCHAR2(100) NOT NULL,
          DEPT_CODE VARCHAR2(100),
          REG_DATE DATE DEFAULT SYSDATE
        )`,
        [],
        { autoCommit: true }
      );
      
      console.log('USER_INFO_ORA 테이블이 생성되었습니다.');
      
      // 샘플 데이터 삽입
      await connection.execute(
        `INSERT INTO USER_INFO_ORA (USER_ID, USER_NAME, DEPT_CODE) VALUES 
         ('user001', '홍길동', 'IT001')`,
        [],
        { autoCommit: true }
      );
      
      await connection.execute(
        `INSERT INTO USER_INFO_ORA (USER_ID, USER_NAME, DEPT_CODE) VALUES 
         ('user002', '김철수', 'HR001')`,
        [],
        { autoCommit: true }
      );
      
      console.log('샘플 사용자 데이터가 추가되었습니다.');
    } else {
      console.log('USER_INFO_ORA 테이블이 이미 존재합니다.');
    }
  } catch (err) {
    console.error('USER_INFO_ORA 테이블 생성 실패:', err);
    throw err;
  } finally {
    if (connection) {
      await connection.close();
    }
  }
}

module.exports = {
  getAllUsers,
  getUserById,
  createUser,
  updateUser,
  deleteUser,
  createUserTable
};