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

// models/monthlyStatusModel.js
// 월별 작업자 상태 집계 모델

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

class MonthlyStatusModel {
  // 월별 일자별 요약 조회 (캘린더용)
  static async getMonthlySummary(year, month) {
    const db = await getDb();

    try {
      const [rows] = await db.execute(`
        SELECT 
          date,
          total_workers,
          working_workers,
          incomplete_workers,
          partial_workers,
          complete_workers,
          overtime_workers,
          vacation_workers,
          error_workers,
          overtime_warning_workers,
          total_work_hours,
          total_work_count,
          total_error_count,
          has_issues,
          has_errors,
          has_overtime_warning,
          last_updated
        FROM monthly_summary 
        WHERE year = ? AND month = ?
        ORDER BY date ASC
      `, [year, month]);

      return rows;
    } catch (error) {
      console.error('월별 요약 조회 오류:', error);
      throw error;
    }
  }

  // 특정 날짜의 작업자별 상태 조회 (모달용)
  // ✅ 리팩토링: 집계 테이블 대신 daily_work_reports에서 직접 조회 (중복 문제 완전 해결)
  static async getDailyWorkerStatus(date) {
    const db = await getDb();

    try {
      // daily_work_reports에서 직접 집계하여 조회 (중복 없음 보장)
      const [rows] = await db.query(`
        SELECT 
          w.worker_id,
          w.worker_name,
          w.job_type,
          YEAR(?) as year,
          MONTH(?) as month,
          ? as date,
          COALESCE(SUM(dwr.work_hours), 0) as total_work_hours,
          COALESCE(SUM(CASE WHEN dwr.project_id != 13 THEN dwr.work_hours ELSE 0 END), 0) as actual_work_hours,
          COALESCE(SUM(CASE WHEN dwr.project_id = 13 THEN dwr.work_hours ELSE 0 END), 0) as vacation_hours,
          COUNT(dwr.id) as total_work_count,
          COUNT(CASE WHEN dwr.project_id != 13 AND dwr.work_status_id != 2 THEN 1 END) as regular_work_count,
          COUNT(CASE WHEN dwr.work_status_id = 2 THEN 1 END) as error_work_count,
          CASE 
            WHEN MAX(CASE WHEN dwr.work_status_id = 2 THEN 1 ELSE 0 END) = 1 THEN 'error'
            WHEN SUM(dwr.work_hours) > 12 THEN 'overtime-warning'
            WHEN SUM(dwr.work_hours) > 8 THEN 'overtime'
            WHEN SUM(dwr.work_hours) = 8 THEN 'complete'
            WHEN SUM(dwr.work_hours) > 0 THEN 'partial'
            ELSE 'incomplete'
          END as work_status,
          MAX(CASE WHEN dwr.project_id = 13 THEN 1 ELSE 0 END) as has_vacation,
          MAX(CASE WHEN dwr.work_status_id = 2 THEN 1 ELSE 0 END) as has_error,
          CASE 
            WHEN SUM(dwr.work_hours) < 8 AND SUM(dwr.work_hours) > 0 THEN 1
            ELSE 0
          END as has_issues,
          MAX(dwr.created_at) as last_updated
        FROM workers w
        LEFT JOIN daily_work_reports dwr ON w.worker_id = dwr.worker_id AND dwr.report_date = ?
        WHERE w.status = 'active'
        GROUP BY w.worker_id, w.worker_name, w.job_type
        ORDER BY w.worker_name ASC
      `, [date, date, date, date]);

      return rows;
    } catch (error) {
      console.error('일별 작업자 상태 조회 오류:', error);
      throw error;
    }
  }

  // 월별 집계 데이터 강제 재계산 (관리용)
  static async recalculateMonth(year, month) {
    const db = await getDb();

    try {
      // 해당 월의 모든 날짜와 작업자 조합을 찾아서 재계산
      const [workDates] = await db.execute(`
        SELECT DISTINCT report_date, worker_id
        FROM daily_work_reports 
        WHERE YEAR(report_date) = ? AND MONTH(report_date) = ?
      `, [year, month]);

      let updatedCount = 0;

      for (const { report_date, worker_id } of workDates) {
        await db.execute('CALL UpdateMonthlyWorkerStatus(?, ?)', [report_date, worker_id]);
        updatedCount++;
      }

      console.log(`✅ ${year}년 ${month}월 집계 재계산 완료: ${updatedCount}건`);
      return { success: true, updatedCount };

    } catch (error) {
      console.error('월별 집계 재계산 오류:', error);
      throw error;
    }
  }

  // 특정 날짜 집계 강제 업데이트
  static async updateDateSummary(date, workerId = null) {
    const db = await getDb();

    try {
      if (workerId) {
        // 특정 작업자만 업데이트
        await db.execute('CALL UpdateMonthlyWorkerStatus(?, ?)', [date, workerId]);
      } else {
        // 해당 날짜의 모든 작업자 업데이트
        const [workers] = await db.execute(`
          SELECT DISTINCT worker_id
          FROM daily_work_reports 
          WHERE report_date = ?
        `, [date]);

        for (const { worker_id } of workers) {
          await db.execute('CALL UpdateMonthlyWorkerStatus(?, ?)', [date, worker_id]);
        }
      }

      return { success: true };
    } catch (error) {
      console.error('날짜별 집계 업데이트 오류:', error);
      throw error;
    }
  }

  // 집계 테이블 상태 확인
  static async getStatusInfo() {
    const db = await getDb();

    try {
      const [summaryCount] = await db.execute(`
        SELECT 
          COUNT(*) as total_days,
          MIN(date) as earliest_date,
          MAX(date) as latest_date,
          MAX(last_updated) as last_update
        FROM monthly_summary
      `);

      const [workerStatusCount] = await db.execute(`
        SELECT 
          COUNT(*) as total_records,
          COUNT(DISTINCT worker_id) as unique_workers,
          COUNT(DISTINCT date) as unique_dates,
          MAX(last_updated) as last_update
        FROM monthly_worker_status
      `);

      return {
        summary: summaryCount[0],
        workerStatus: workerStatusCount[0]
      };
    } catch (error) {
      console.error('집계 테이블 상태 확인 오류:', error);
      throw error;
    }
  }
}

module.exports = MonthlyStatusModel;