Worker-Management-System/backend/models/database.js

const mysql = require('mysql2/promise');

// 데이터베이스 연결 풀 생성
const pool = mysql.createPool({
    host: process.env.DB_HOST || 'localhost',
    port: process.env.DB_PORT || 3306,
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || 'rootpassword',
    database: process.env.DB_NAME || 'worker_management',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0,
    acquireTimeout: 60000,
    timeout: 60000,
    reconnect: true,
    charset: 'utf8mb4'
});

// 데이터베이스 연결 테스트
async function testConnection() {
    try {
        const connection = await pool.getConnection();
        console.log('✅ 데이터베이스 연결 성공');
        connection.release();
        return true;
    } catch (error) {
        console.error('❌ 데이터베이스 연결 실패:', error.message);
        return false;
    }
}

// 쿼리 실행 함수
async function executeQuery(sql, params = []) {
    try {
        const [rows] = await pool.execute(sql, params);
        return rows;
    } catch (error) {
        console.error('쿼리 실행 오류:', error);
        throw error;
    }
}

// 트랜잭션 실행 함수
async function executeTransaction(queries) {
    const connection = await pool.getConnection();
    
    try {
        await connection.beginTransaction();
        
        const results = [];
        for (const { sql, params } of queries) {
            const [result] = await connection.execute(sql, params);
            results.push(result);
        }
        
        await connection.commit();
        return results;
    } catch (error) {
        await connection.rollback();
        throw error;
    } finally {
        connection.release();
    }
}

// 페이지네이션 쿼리 함수
async function executePagedQuery(sql, params = [], page = 1, limit = 10) {
    const offset = (page - 1) * limit;
    
    // 전체 개수 조회
    const countSql = `SELECT COUNT(*) as total FROM (${sql}) as count_query`;
    const [countResult] = await pool.execute(countSql, params);
    const total = countResult[0].total;
    
    // 페이지 데이터 조회
    const pagedSql = `${sql} LIMIT ? OFFSET ?`;
    const [rows] = await pool.execute(pagedSql, [...params, limit, offset]);
    
    return {
        data: rows,
        pagination: {
            page,
            limit,
            total,
            totalPages: Math.ceil(total / limit),
            hasNext: page < Math.ceil(total / limit),
            hasPrev: page > 1
        }
    };
}

// 데이터베이스 초기화 시 연결 테스트
testConnection();

module.exports = {
    pool,
    executeQuery,
    executeTransaction,
    executePagedQuery,
    testConnection
};