TK-FB-Project/api.hyungi.net/models/vacationBalanceModel.js

/**
 * vacationBalanceModel.js
 * 휴가 잔액 관련 데이터베이스 쿼리 모델
 */

const { getDb } = require('../dbPool');

const vacationBalanceModel = {
  /**
   * 특정 작업자의 모든 휴가 잔액 조회 (특정 연도)
   */
  async getByWorkerAndYear(workerId, year, callback) {
    try {
      const db = await getDb();
      const query = `
        SELECT
          vbd.*,
          vt.type_name,
          vt.type_code,
          vt.priority,
          vt.is_special
        FROM vacation_balance_details vbd
        INNER JOIN vacation_types vt ON vbd.vacation_type_id = vt.id
        WHERE vbd.worker_id = ? AND vbd.year = ?
        ORDER BY vt.priority ASC, vt.type_name ASC
      `;
      const [rows] = await db.query(query, [workerId, year]);
      callback(null, rows);
    } catch (error) {
      callback(error);
    }
  },

  /**
   * 특정 작업자의 특정 휴가 유형 잔액 조회
   */
  async getByWorkerTypeYear(workerId, vacationTypeId, year, callback) {
    try {
      const db = await getDb();
      const query = `
        SELECT
          vbd.*,
          vt.type_name,
          vt.type_code
        FROM vacation_balance_details vbd
        INNER JOIN vacation_types vt ON vbd.vacation_type_id = vt.id
        WHERE vbd.worker_id = ?
          AND vbd.vacation_type_id = ?
          AND vbd.year = ?
      `;
      const [rows] = await db.query(query, [workerId, vacationTypeId, year]);
      callback(null, rows);
    } catch (error) {
      callback(error);
    }
  },

  /**
   * 모든 작업자의 휴가 잔액 조회 (특정 연도)
   * - 연간 연차 현황 차트용
   */
  async getAllByYear(year, callback) {
    try {
      const db = await getDb();
      const query = `
        SELECT
          vbd.*,
          w.worker_name,
          w.employment_status,
          vt.type_name,
          vt.type_code,
          vt.priority
        FROM vacation_balance_details vbd
        INNER JOIN workers w ON vbd.worker_id = w.worker_id
        INNER JOIN vacation_types vt ON vbd.vacation_type_id = vt.id
        WHERE vbd.year = ?
          AND w.employment_status = 'employed'
        ORDER BY w.worker_name ASC, vt.priority ASC
      `;
      const [rows] = await db.query(query, [year]);
      callback(null, rows);
    } catch (error) {
      callback(error);
    }
  },

  /**
   * 휴가 잔액 생성
   */
  async create(balanceData, callback) {
    try {
      const db = await getDb();
      const query = `INSERT INTO vacation_balance_details SET ?`;
      const [rows] = await db.query(query, balanceData);
      callback(null, rows);
    } catch (error) {
      callback(error);
    }
  },

  /**
   * 휴가 잔액 수정
   */
  async update(id, updateData, callback) {
    try {
      const db = await getDb();
      const query = `UPDATE vacation_balance_details SET ? WHERE id = ?`;
      const [rows] = await db.query(query, [updateData, id]);
      callback(null, rows);
    } catch (error) {
      callback(error);
    }
  },

  /**
   * 휴가 잔액 삭제
   */
  async delete(id, callback) {
    try {
      const db = await getDb();
      const query = `DELETE FROM vacation_balance_details WHERE id = ?`;
      const [rows] = await db.query(query, [id]);
      callback(null, rows);
    } catch (error) {
      callback(error);
    }
  },

  /**
   * 작업자의 휴가 사용 일수 업데이트 (차감)
   * - 휴가 신청 승인 시 호출
   */
  async deductDays(workerId, vacationTypeId, year, daysToDeduct, callback) {
    try {
      const db = await getDb();
      const query = `
        UPDATE vacation_balance_details
        SET used_days = used_days + ?,
            updated_at = NOW()
        WHERE worker_id = ?
          AND vacation_type_id = ?
          AND year = ?
      `;
      const [rows] = await db.query(query, [daysToDeduct, workerId, vacationTypeId, year]);
      callback(null, rows);
    } catch (error) {
      callback(error);
    }
  },

  /**
   * 작업자의 휴가 사용 일수 복구 (취소)
   * - 휴가 신청 취소/거부 시 호출
   */
  async restoreDays(workerId, vacationTypeId, year, daysToRestore, callback) {
    try {
      const db = await getDb();
      const query = `
        UPDATE vacation_balance_details
        SET used_days = GREATEST(0, used_days - ?),
            updated_at = NOW()
        WHERE worker_id = ?
          AND vacation_type_id = ?
          AND year = ?
      `;
      const [rows] = await db.query(query, [daysToRestore, workerId, vacationTypeId, year]);
      callback(null, rows);
    } catch (error) {
      callback(error);
    }
  },

  /**
   * 특정 작업자의 사용 가능한 휴가 일수 확인
   * - 우선순위가 높은 순서대로 차감 가능 여부 확인
   */
  async getAvailableVacationDays(workerId, year, callback) {
    try {
      const db = await getDb();
      const query = `
        SELECT
          vbd.id,
          vbd.vacation_type_id,
          vt.type_name,
          vt.type_code,
          vt.priority,
          vbd.total_days,
          vbd.used_days,
          vbd.remaining_days
        FROM vacation_balance_details vbd
        INNER JOIN vacation_types vt ON vbd.vacation_type_id = vt.id
        WHERE vbd.worker_id = ?
          AND vbd.year = ?
          AND vbd.remaining_days > 0
        ORDER BY vt.priority ASC
      `;
      const [rows] = await db.query(query, [workerId, year]);
      callback(null, rows);
    } catch (error) {
      callback(error);
    }
  },

  /**
   * 작업자별 휴가 잔액 일괄 생성 (연도별)
   * - 매년 초 또는 입사 시 사용
   */
  async bulkCreate(balances, callback) {
    try {
      const db = await getDb();

      if (!balances || balances.length === 0) {
        return callback(new Error('생성할 휴가 잔액 데이터가 없습니다'));
      }

      const query = `INSERT INTO vacation_balance_details
        (worker_id, vacation_type_id, year, total_days, used_days, notes, created_by)
        VALUES ?`;

      const values = balances.map(b => [
        b.worker_id,
        b.vacation_type_id,
        b.year,
        b.total_days || 0,
        b.used_days || 0,
        b.notes || null,
        b.created_by
      ]);

      const [rows] = await db.query(query, [values]);
      callback(null, rows);
    } catch (error) {
      callback(error);
    }
  },

  /**
   * 근속년수 기반 연차 일수 계산 (한국 근로기준법)
   * @param {Date} hireDate - 입사일
   * @param {number} targetYear - 대상 연도
   * @returns {number} - 부여받을 연차 일수
   */
  calculateAnnualLeaveDays(hireDate, targetYear) {
    const hire = new Date(hireDate);
    const targetDate = new Date(targetYear, 0, 1);

    // 근속 월수 계산
    const monthsDiff = (targetDate.getFullYear() - hire.getFullYear()) * 12
                     + (targetDate.getMonth() - hire.getMonth());

    // 1년 미만: 월 1일
    if (monthsDiff < 12) {
      return Math.floor(monthsDiff);
    }

    // 1년 이상: 15일 기본 + 2년마다 1일 추가 (최대 25일)
    const yearsWorked = Math.floor(monthsDiff / 12);
    const additionalDays = Math.floor((yearsWorked - 1) / 2);

    return Math.min(15 + additionalDays, 25);
  },

  /**
   * 휴가 사용 시 우선순위에 따라 잔액에서 차감 (Promise 버전)
   * - 일일 근태 기록 저장 시 호출
   * @param {number} workerId - 작업자 ID
   * @param {number} year - 연도
   * @param {number} daysToDeduct - 차감할 일수 (1, 0.5, 0.25)
   * @returns {Promise<Object>} - 차감 결과
   */
  async deductByPriority(workerId, year, daysToDeduct) {
    const db = await getDb();

    // 우선순위순으로 잔여 일수가 있는 잔액 조회
    const [balances] = await db.query(`
      SELECT vbd.id, vbd.vacation_type_id, vbd.total_days, vbd.used_days,
             (vbd.total_days - vbd.used_days) as remaining_days,
             vt.type_code, vt.type_name, vt.priority
      FROM vacation_balance_details vbd
      INNER JOIN vacation_types vt ON vbd.vacation_type_id = vt.id
      WHERE vbd.worker_id = ? AND vbd.year = ?
        AND (vbd.total_days - vbd.used_days) > 0
      ORDER BY vt.priority ASC
    `, [workerId, year]);

    if (balances.length === 0) {
      // 잔액이 없어도 일단 기록은 저장 (경고만)
      console.warn(`[VacationBalance] 작업자 ${workerId}의 ${year}년 휴가 잔액이 없습니다`);
      return { success: false, message: '휴가 잔액이 없습니다', deducted: 0 };
    }

    let remaining = daysToDeduct;
    const deductions = [];

    for (const balance of balances) {
      if (remaining <= 0) break;

      const available = parseFloat(balance.remaining_days);
      const toDeduct = Math.min(remaining, available);

      if (toDeduct > 0) {
        await db.query(`
          UPDATE vacation_balance_details
          SET used_days = used_days + ?, updated_at = NOW()
          WHERE id = ?
        `, [toDeduct, balance.id]);

        deductions.push({
          balance_id: balance.id,
          type_code: balance.type_code,
          type_name: balance.type_name,
          deducted: toDeduct
        });

        remaining -= toDeduct;
      }
    }

    console.log(`[VacationBalance] 작업자 ${workerId}: ${daysToDeduct}일 차감 완료`, deductions);
    return { success: true, deductions, totalDeducted: daysToDeduct - remaining };
  },

  /**
   * 휴가 취소 시 우선순위 역순으로 복구 (Promise 버전)
   * @param {number} workerId - 작업자 ID
   * @param {number} year - 연도
   * @param {number} daysToRestore - 복구할 일수
   * @returns {Promise<Object>} - 복구 결과
   */
  async restoreByPriority(workerId, year, daysToRestore) {
    const db = await getDb();

    // 우선순위 역순으로 사용 일수가 있는 잔액 조회 (나중에 차감된 것부터 복구)
    const [balances] = await db.query(`
      SELECT vbd.id, vbd.vacation_type_id, vbd.used_days,
             vt.type_code, vt.type_name, vt.priority
      FROM vacation_balance_details vbd
      INNER JOIN vacation_types vt ON vbd.vacation_type_id = vt.id
      WHERE vbd.worker_id = ? AND vbd.year = ?
        AND vbd.used_days > 0
      ORDER BY vt.priority DESC
    `, [workerId, year]);

    let remaining = daysToRestore;
    const restorations = [];

    for (const balance of balances) {
      if (remaining <= 0) break;

      const usedDays = parseFloat(balance.used_days);
      const toRestore = Math.min(remaining, usedDays);

      if (toRestore > 0) {
        await db.query(`
          UPDATE vacation_balance_details
          SET used_days = used_days - ?, updated_at = NOW()
          WHERE id = ?
        `, [toRestore, balance.id]);

        restorations.push({
          balance_id: balance.id,
          type_code: balance.type_code,
          type_name: balance.type_name,
          restored: toRestore
        });

        remaining -= toRestore;
      }
    }

    console.log(`[VacationBalance] 작업자 ${workerId}: ${daysToRestore}일 복구 완료`, restorations);
    return { success: true, restorations, totalRestored: daysToRestore - remaining };
  },

  /**
   * 특정 ID로 휴가 잔액 조회
   */
  async getById(id, callback) {
    try {
      const db = await getDb();
      const query = `
        SELECT
          vbd.*,
          w.worker_name,
          vt.type_name,
          vt.type_code
        FROM vacation_balance_details vbd
        INNER JOIN workers w ON vbd.worker_id = w.worker_id
        INNER JOIN vacation_types vt ON vbd.vacation_type_id = vt.id
        WHERE vbd.id = ?
      `;
      const [rows] = await db.query(query, [id]);
      callback(null, rows);
    } catch (error) {
      callback(error);
    }
  }
};

module.exports = vacationBalanceModel;