TK-FB-Project/api.hyungi.net/routes/setupRoutes.js

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

// DB 설정 엔드포인트 (개발용 - 인증 없이 접근 가능)
// 월별 집계 테이블 설정
router.post('/setup-monthly-status', async (req, res) => {
  try {
    const db = await getDb();

    console.log('📊 월별 집계 테이블 생성 중...');

    // 1. 월별 작업자 상태 집계 테이블
    await db.execute(`
      CREATE TABLE IF NOT EXISTS monthly_worker_status (
          id INT PRIMARY KEY AUTO_INCREMENT,
          year INT NOT NULL COMMENT '연도',
          month INT NOT NULL COMMENT '월 (1-12)',
          worker_id INT NOT NULL COMMENT '작업자 ID',
          date DATE NOT NULL COMMENT '날짜',
          
          total_work_hours DECIMAL(5,2) DEFAULT 0.00 COMMENT '총 작업시간',
          actual_work_hours DECIMAL(5,2) DEFAULT 0.00 COMMENT '실제 작업시간 (휴가 제외)',
          vacation_hours DECIMAL(5,2) DEFAULT 0.00 COMMENT '휴가 시간',
          
          total_work_count INT DEFAULT 0 COMMENT '총 작업 건수',
          regular_work_count INT DEFAULT 0 COMMENT '정규 작업 건수',
          error_work_count INT DEFAULT 0 COMMENT '오류 작업 건수',
          
          work_status ENUM(
              'incomplete', 'partial', 'complete', 'overtime',
              'vacation-full', 'vacation-half', 'vacation-quarter', 'vacation-half-half',
              'error', 'overtime-warning'
          ) NOT NULL DEFAULT 'incomplete' COMMENT '작업 상태',
          
          has_vacation BOOLEAN DEFAULT FALSE COMMENT '휴가 여부',
          has_error BOOLEAN DEFAULT FALSE COMMENT '오류 여부',
          has_issues BOOLEAN DEFAULT FALSE COMMENT '문제 여부 (미입력/부분입력)',
          
          last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          
          UNIQUE KEY unique_worker_date (worker_id, date),
          KEY idx_year_month (year, month),
          KEY idx_worker_year_month (worker_id, year, month),
          KEY idx_status (work_status),
          KEY idx_has_issues (has_issues),
          KEY idx_has_error (has_error),
          
          FOREIGN KEY (worker_id) REFERENCES workers(worker_id) ON DELETE CASCADE
      ) COMMENT='월별 작업자 상태 집계 테이블'
    `);

    // 2. 월별 집계 요약 테이블
    await db.execute(`
      CREATE TABLE IF NOT EXISTS monthly_summary (
          id INT PRIMARY KEY AUTO_INCREMENT,
          year INT NOT NULL COMMENT '연도',
          month INT NOT NULL COMMENT '월 (1-12)',
          date DATE NOT NULL COMMENT '날짜',
          
          total_workers INT DEFAULT 0 COMMENT '총 작업자 수',
          working_workers INT DEFAULT 0 COMMENT '작업한 작업자 수',
          
          incomplete_workers INT DEFAULT 0 COMMENT '미입력 작업자 수',
          partial_workers INT DEFAULT 0 COMMENT '부분입력 작업자 수',
          complete_workers INT DEFAULT 0 COMMENT '완료 작업자 수',
          overtime_workers INT DEFAULT 0 COMMENT '연장근로 작업자 수',
          vacation_workers INT DEFAULT 0 COMMENT '휴가 작업자 수',
          error_workers INT DEFAULT 0 COMMENT '오류 작업자 수',
          
          total_work_hours DECIMAL(8,2) DEFAULT 0.00 COMMENT '총 작업시간',
          total_work_count INT DEFAULT 0 COMMENT '총 작업 건수',
          total_error_count INT DEFAULT 0 COMMENT '총 오류 건수',
          
          has_issues BOOLEAN DEFAULT FALSE COMMENT '문제 있음 (미입력/부분입력)',
          has_errors BOOLEAN DEFAULT FALSE COMMENT '오류 있음',
          
          last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          
          UNIQUE KEY unique_date (date),
          KEY idx_year_month (year, month),
          KEY idx_has_issues (has_issues),
          KEY idx_has_errors (has_errors)
      ) COMMENT='월별 일자별 요약 테이블 (캘린더 최적화용)'
    `);

    console.log('📊 집계 프로시저 생성 중...');

    // 3. 집계 업데이트 프로시저
    await db.execute(`DROP PROCEDURE IF EXISTS UpdateMonthlyWorkerStatus`);
    await db.execute(`
      CREATE PROCEDURE UpdateMonthlyWorkerStatus(
          IN p_date DATE,
          IN p_worker_id INT
      )
      BEGIN
          DECLARE v_year INT;
          DECLARE v_month INT;
          DECLARE v_total_hours DECIMAL(5,2);
          DECLARE v_actual_hours DECIMAL(5,2);
          DECLARE v_vacation_hours DECIMAL(5,2);
          DECLARE v_total_count INT;
          DECLARE v_regular_count INT;
          DECLARE v_error_count INT;
          DECLARE v_has_vacation BOOLEAN;
          DECLARE v_has_error BOOLEAN;
          DECLARE v_has_issues BOOLEAN;
          DECLARE v_status VARCHAR(20);
          
          SET v_year = YEAR(p_date);
          SET v_month = MONTH(p_date);
          
          SELECT 
              COALESCE(SUM(work_hours), 0),
              COALESCE(SUM(CASE WHEN project_id != 13 THEN work_hours ELSE 0 END), 0),
              COALESCE(SUM(CASE WHEN project_id = 13 THEN work_hours ELSE 0 END), 0),
              COUNT(*),
              COUNT(CASE WHEN project_id != 13 AND work_status_id != 2 THEN 1 END),
              COUNT(CASE WHEN work_status_id = 2 THEN 1 END),
              MAX(CASE WHEN project_id = 13 THEN 1 ELSE 0 END),
              MAX(CASE WHEN work_status_id = 2 THEN 1 ELSE 0 END)
          INTO 
              v_total_hours, v_actual_hours, v_vacation_hours,
              v_total_count, v_regular_count, v_error_count,
              v_has_vacation, v_has_error
          FROM daily_work_reports 
          WHERE report_date = p_date AND worker_id = p_worker_id;
          
          IF v_has_error THEN
              SET v_status = 'error';
              SET v_has_issues = FALSE;
          ELSEIF v_total_hours > 12 THEN
              SET v_status = 'overtime-warning';
              SET v_has_issues = TRUE;
          ELSEIF v_has_vacation AND v_vacation_hours > 0 THEN
              CASE v_vacation_hours
                  WHEN 8 THEN SET v_status = 'vacation-full';
                  WHEN 6 THEN SET v_status = 'vacation-half-half';
                  WHEN 4 THEN SET v_status = 'vacation-half';
                  WHEN 2 THEN SET v_status = 'vacation-quarter';
                  ELSE SET v_status = 'vacation-full';
              END CASE;
              SET v_has_issues = FALSE;
          ELSEIF v_total_hours > 8 THEN
              SET v_status = 'overtime';
              SET v_has_issues = FALSE;
          ELSEIF v_total_hours = 8 THEN
              SET v_status = 'complete';
              SET v_has_issues = FALSE;
          ELSEIF v_total_hours > 0 THEN
              SET v_status = 'partial';
              SET v_has_issues = TRUE;
          ELSE
              SET v_status = 'incomplete';
              SET v_has_issues = TRUE;
          END IF;
          
          INSERT INTO monthly_worker_status (
              year, month, worker_id, date,
              total_work_hours, actual_work_hours, vacation_hours,
              total_work_count, regular_work_count, error_work_count,
              work_status, has_vacation, has_error, has_issues
          ) VALUES (
              v_year, v_month, p_worker_id, p_date,
              v_total_hours, v_actual_hours, v_vacation_hours,
              v_total_count, v_regular_count, v_error_count,
              v_status, v_has_vacation, v_has_error, v_has_issues
          ) ON DUPLICATE KEY UPDATE
              total_work_hours = v_total_hours,
              actual_work_hours = v_actual_hours,
              vacation_hours = v_vacation_hours,
              total_work_count = v_total_count,
              regular_work_count = v_regular_count,
              error_work_count = v_error_count,
              work_status = v_status,
              has_vacation = v_has_vacation,
              has_error = v_has_error,
              has_issues = v_has_issues,
              last_updated = CURRENT_TIMESTAMP;
              
          CALL UpdateDailySummary(p_date);
      END
    `);

    await db.execute(`DROP PROCEDURE IF EXISTS UpdateDailySummary`);
    await db.execute(`
      CREATE PROCEDURE UpdateDailySummary(
          IN p_date DATE
      )
      BEGIN
          DECLARE v_year INT;
          DECLARE v_month INT;
          
          SET v_year = YEAR(p_date);
          SET v_month = MONTH(p_date);
          
          INSERT INTO monthly_summary (
              year, month, date,
              total_workers, working_workers,
              incomplete_workers, partial_workers, complete_workers,
              overtime_workers, vacation_workers, error_workers,
              total_work_hours, total_work_count, total_error_count,
              has_issues, has_errors
          )
          SELECT 
              v_year, v_month, p_date,
              COUNT(*) as total_workers,
              COUNT(CASE WHEN work_status != 'incomplete' THEN 1 END) as working_workers,
              COUNT(CASE WHEN work_status = 'incomplete' THEN 1 END) as incomplete_workers,
              COUNT(CASE WHEN work_status = 'partial' THEN 1 END) as partial_workers,
              COUNT(CASE WHEN work_status IN ('complete') THEN 1 END) as complete_workers,
              COUNT(CASE WHEN work_status = 'overtime' THEN 1 END) as overtime_workers,
              COUNT(CASE WHEN work_status LIKE 'vacation%' THEN 1 END) as vacation_workers,
              COUNT(CASE WHEN work_status = 'error' THEN 1 END) as error_workers,
              SUM(total_work_hours) as total_work_hours,
              SUM(total_work_count) as total_work_count,
              SUM(error_work_count) as total_error_count,
              MAX(has_issues) as has_issues,
              MAX(has_error) as has_errors
          FROM monthly_worker_status 
          WHERE date = p_date
          ON DUPLICATE KEY UPDATE
              total_workers = VALUES(total_workers),
              working_workers = VALUES(working_workers),
              incomplete_workers = VALUES(incomplete_workers),
              partial_workers = VALUES(partial_workers),
              complete_workers = VALUES(complete_workers),
              overtime_workers = VALUES(overtime_workers),
              vacation_workers = VALUES(vacation_workers),
              error_workers = VALUES(error_workers),
              total_work_hours = VALUES(total_work_hours),
              total_work_count = VALUES(total_work_count),
              total_error_count = VALUES(total_error_count),
              has_issues = VALUES(has_issues),
              has_errors = VALUES(has_errors),
              last_updated = CURRENT_TIMESTAMP;
      END
    `);

    console.log('📊 트리거 생성 중...');

    // 4. 트리거 생성
    await db.execute(`DROP TRIGGER IF EXISTS tr_daily_work_reports_insert`);
    await db.execute(`
      CREATE TRIGGER tr_daily_work_reports_insert
      AFTER INSERT ON daily_work_reports
      FOR EACH ROW
      BEGIN
          CALL UpdateMonthlyWorkerStatus(NEW.report_date, NEW.worker_id);
      END
    `);

    await db.execute(`DROP TRIGGER IF EXISTS tr_daily_work_reports_update`);
    await db.execute(`
      CREATE TRIGGER tr_daily_work_reports_update
      AFTER UPDATE ON daily_work_reports
      FOR EACH ROW
      BEGIN
          CALL UpdateMonthlyWorkerStatus(OLD.report_date, OLD.worker_id);
          
          IF OLD.report_date != NEW.report_date OR OLD.worker_id != NEW.worker_id THEN
              CALL UpdateMonthlyWorkerStatus(NEW.report_date, NEW.worker_id);
          END IF;
      END
    `);

    await db.execute(`DROP TRIGGER IF EXISTS tr_daily_work_reports_delete`);
    await db.execute(`
      CREATE TRIGGER tr_daily_work_reports_delete
      AFTER DELETE ON daily_work_reports
      FOR EACH ROW
      BEGIN
          CALL UpdateMonthlyWorkerStatus(OLD.report_date, OLD.worker_id);
      END
    `);

    console.log('📊 기존 데이터로 집계 테이블 초기화 중...');
    
    // 5. 기존 작업 데이터로 집계 테이블 초기화
    const [existingDates] = await db.execute(`
      SELECT DISTINCT report_date, worker_id
      FROM daily_work_reports 
      WHERE report_date >= '2025-01-01'
      ORDER BY report_date DESC, worker_id ASC
    `);
    
    let processedCount = 0;
    const batchSize = 50;
    
    for (let i = 0; i < existingDates.length; i += batchSize) {
      const batch = existingDates.slice(i, i + batchSize);
      
      for (const { report_date, worker_id } of batch) {
        try {
          await db.execute('CALL UpdateMonthlyWorkerStatus(?, ?)', [report_date, worker_id]);
          processedCount++;
        } catch (error) {
          console.warn(`집계 처리 실패: ${report_date}, worker ${worker_id}:`, error.message);
        }
      }
      
      if (i % 100 === 0) {
        console.log(`📊 집계 초기화 진행률: ${processedCount}/${existingDates.length}`);
      }
    }

    res.json({
      success: true,
      message: '월별 집계 시스템이 성공적으로 설정되었습니다.',
      data: {
        tables_created: [
          'monthly_worker_status',
          'monthly_summary'
        ],
        procedures_created: [
          'UpdateMonthlyWorkerStatus',
          'UpdateDailySummary'
        ],
        triggers_created: [
          'tr_daily_work_reports_insert',
          'tr_daily_work_reports_update',
          'tr_daily_work_reports_delete'
        ],
        initialized_records: processedCount,
        total_dates: existingDates.length
      }
    });

  } catch (error) {
    console.error('❌ 월별 집계 시스템 설정 오류:', error);
    res.status(500).json({
      success: false,
      message: '월별 집계 시스템 설정 중 오류가 발생했습니다.',
      error: error.message
    });
  }
});

router.post('/setup-attendance-db', async (req, res) => {
  try {
    console.log('🚀 근태 관리 DB 설정 API 호출됨');
    
    const db = await getDb();
    
    // 1. 근로 유형 테이블 생성
    console.log('📋 근로 유형 테이블 생성 중...');
    await db.execute(`
      CREATE TABLE IF NOT EXISTS work_attendance_types (
          id INT PRIMARY KEY AUTO_INCREMENT,
          type_code VARCHAR(20) NOT NULL UNIQUE COMMENT '근로 유형 코드',
          type_name VARCHAR(50) NOT NULL COMMENT '근로 유형명',
          description TEXT COMMENT '설명',
          is_active BOOLEAN DEFAULT TRUE COMMENT '활성 상태',
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      ) COMMENT='근로 유형 관리 테이블'
    `);

    // 2. 휴가 유형 테이블 생성
    console.log('🏖️ 휴가 유형 테이블 생성 중...');
    await db.execute(`
      CREATE TABLE IF NOT EXISTS vacation_types (
          id INT PRIMARY KEY AUTO_INCREMENT,
          type_code VARCHAR(20) NOT NULL UNIQUE COMMENT '휴가 유형 코드',
          type_name VARCHAR(50) NOT NULL COMMENT '휴가 유형명',
          hours_deduction DECIMAL(4,2) NOT NULL COMMENT '차감 시간',
          description TEXT COMMENT '설명',
          is_active BOOLEAN DEFAULT TRUE COMMENT '활성 상태',
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      ) COMMENT='휴가 유형 관리 테이블'
    `);

    // 3. 일일 근태 기록 테이블 생성
    console.log('📊 일일 근태 기록 테이블 생성 중...');
    await db.execute(`
      CREATE TABLE IF NOT EXISTS daily_attendance_records (
          id INT PRIMARY KEY AUTO_INCREMENT,
          record_date DATE NOT NULL COMMENT '기록 날짜',
          worker_id INT NOT NULL COMMENT '작업자 ID',
          work_attendance_type_id INT COMMENT '근로 유형 ID (정시, 연장, 부분, 휴가)',
          total_work_hours DECIMAL(4,2) DEFAULT 0.00 COMMENT '총 작업 시간',
          vacation_type_id INT COMMENT '휴가 유형 ID (연차, 반차 등)',
          is_overtime_approved BOOLEAN DEFAULT FALSE COMMENT '연장근로 승인 여부',
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          UNIQUE KEY unique_worker_date (worker_id, record_date),
          FOREIGN KEY (worker_id) REFERENCES workers(worker_id) ON DELETE CASCADE,
          FOREIGN KEY (work_attendance_type_id) REFERENCES work_attendance_types(id) ON DELETE SET NULL,
          FOREIGN KEY (vacation_type_id) REFERENCES vacation_types(id) ON DELETE SET NULL
      ) COMMENT='일일 근태 기록 테이블'
    `);

    // 4. 작업자별 휴가 잔여 관리 테이블 생성
    console.log('👥 작업자별 휴가 잔여 관리 테이블 생성 중...');
    await db.execute(`
      CREATE TABLE IF NOT EXISTS worker_vacation_balance (
          id INT PRIMARY KEY AUTO_INCREMENT,
          worker_id INT NOT NULL UNIQUE COMMENT '작업자 ID',
          annual_leave_total DECIMAL(5,2) DEFAULT 15.00 COMMENT '총 연차 일수',
          annual_leave_used DECIMAL(5,2) DEFAULT 0.00 COMMENT '사용 연차 일수',
          sick_leave_total DECIMAL(5,2) DEFAULT 10.00 COMMENT '총 병가 일수',
          sick_leave_used DECIMAL(5,2) DEFAULT 0.00 COMMENT '사용 병가 일수',
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          FOREIGN KEY (worker_id) REFERENCES workers(worker_id) ON DELETE CASCADE
      ) COMMENT='작업자별 휴가 잔여 관리 테이블'
    `);

    // 5. 기본 데이터 삽입
    console.log('📝 기본 데이터 삽입 중...');
    
    // 근로 유형 기본 데이터
    await db.execute(`
      INSERT IGNORE INTO work_attendance_types (type_code, type_name, description) VALUES
      ('REGULAR', '정시근로', '8시간 정규 근무'),
      ('OVERTIME', '연장근로', '8시간 초과 근무'),
      ('PARTIAL', '부분근로', '8시간 미만 근무'),
      ('VACATION', '휴가근로', '휴가와 함께하는 부분 근무')
    `);

    // 휴가 유형 기본 데이터
    await db.execute(`
      INSERT IGNORE INTO vacation_types (type_code, type_name, hours_deduction, description) VALUES
      ('ANNUAL_FULL', '연차', 8.0, '하루 전체 연차'),
      ('ANNUAL_HALF', '반차', 4.0, '반일 연차'),
      ('ANNUAL_QUARTER', '반반차', 2.0, '1/4일 연차'),
      ('SICK_FULL', '병가', 8.0, '하루 전체 병가'),
      ('SICK_HALF', '반일병가', 4.0, '반일 병가')
    `);
    
    res.json({
      success: true,
      message: '근태 관리 DB 설정이 완료되었습니다.',
      data: {
        tables_created: [
          'work_attendance_types',
          'vacation_types',
          'daily_attendance_records',
          'worker_vacation_balance'
        ],
        basic_data_inserted: true
      }
    });
    
  } catch (error) {
    console.error('❌ DB 설정 API 오류:', error);
    res.status(500).json({
      success: false,
      message: 'DB 설정 중 오류가 발생했습니다.',
      error: error.message
    });
  }
});

// 12시간 초과 상태 컬럼 추가
router.post('/add-overtime-warning', async (req, res) => {
  try {
    const db = await getDb();

    console.log('⚠️ 12시간 초과 상태 컬럼 추가 중...');

    // 1. monthly_summary 테이블에 컬럼 추가
    try {
      await db.execute(`
        ALTER TABLE monthly_summary 
        ADD COLUMN overtime_warning_workers INT DEFAULT 0 COMMENT '확인필요(12시간초과) 작업자 수' AFTER error_workers
      `);
      console.log('✅ overtime_warning_workers 컬럼 추가 완료');
    } catch (error) {
      if (error.code === 'ER_DUP_FIELDNAME') {
        console.log('ℹ️ overtime_warning_workers 컬럼이 이미 존재합니다.');
      } else {
        throw error;
      }
    }

    try {
      await db.execute(`
        ALTER TABLE monthly_summary 
        ADD COLUMN has_overtime_warning BOOLEAN DEFAULT FALSE COMMENT '확인필요 상태 있음' AFTER has_errors
      `);
      console.log('✅ has_overtime_warning 컬럼 추가 완료');
    } catch (error) {
      if (error.code === 'ER_DUP_FIELDNAME') {
        console.log('ℹ️ has_overtime_warning 컬럼이 이미 존재합니다.');
      } else {
        throw error;
      }
    }

    // 2. UpdateDailySummary 프로시저 업데이트
    await db.execute(`DROP PROCEDURE IF EXISTS UpdateDailySummary`);
    
    await db.execute(`
      CREATE PROCEDURE UpdateDailySummary(
          IN p_date DATE
      )
      BEGIN
          DECLARE v_year INT;
          DECLARE v_month INT;
          
          SET v_year = YEAR(p_date);
          SET v_month = MONTH(p_date);
          
          INSERT INTO monthly_summary (
              year, month, 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
          )
          SELECT 
              v_year, v_month, p_date,
              COUNT(*) as total_workers,
              COUNT(CASE WHEN work_status != 'incomplete' THEN 1 END) as working_workers,
              COUNT(CASE WHEN work_status = 'incomplete' THEN 1 END) as incomplete_workers,
              COUNT(CASE WHEN work_status = 'partial' THEN 1 END) as partial_workers,
              COUNT(CASE WHEN work_status IN ('complete', 'overtime', 'vacation-full', 'vacation-half', 'vacation-quarter', 'vacation-half-half') THEN 1 END) as complete_workers,
              COUNT(CASE WHEN work_status = 'overtime' THEN 1 END) as overtime_workers,
              COUNT(CASE WHEN work_status LIKE 'vacation%' THEN 1 END) as vacation_workers,
              COUNT(CASE WHEN work_status = 'error' THEN 1 END) as error_workers,
              COUNT(CASE WHEN work_status = 'overtime-warning' THEN 1 END) as overtime_warning_workers,
              SUM(total_work_hours) as total_work_hours,
              SUM(total_work_count) as total_work_count,
              SUM(error_work_count) as total_error_count,
              MAX(has_issues) as has_issues,
              MAX(has_error) as has_errors,
              MAX(CASE WHEN work_status = 'overtime-warning' THEN 1 ELSE 0 END) as has_overtime_warning
          FROM monthly_worker_status 
          WHERE date = p_date
          ON DUPLICATE KEY UPDATE
              total_workers = VALUES(total_workers),
              working_workers = VALUES(working_workers),
              incomplete_workers = VALUES(incomplete_workers),
              partial_workers = VALUES(partial_workers),
              complete_workers = VALUES(complete_workers),
              overtime_workers = VALUES(overtime_workers),
              vacation_workers = VALUES(vacation_workers),
              error_workers = VALUES(error_workers),
              overtime_warning_workers = VALUES(overtime_warning_workers),
              total_work_hours = VALUES(total_work_hours),
              total_work_count = VALUES(total_work_count),
              total_error_count = VALUES(total_error_count),
              has_issues = VALUES(has_issues),
              has_errors = VALUES(has_errors),
              has_overtime_warning = VALUES(has_overtime_warning),
              last_updated = CURRENT_TIMESTAMP;
      END
    `);
    console.log('✅ UpdateDailySummary 프로시저 업데이트 완료');

    res.json({
      success: true,
      message: '12시간 초과 상태 컬럼 추가 완료',
      columns_added: ['overtime_warning_workers', 'has_overtime_warning'],
      procedure_updated: 'UpdateDailySummary'
    });

  } catch (error) {
    console.error('❌ 12시간 초과 상태 설정 오류:', error);
    res.status(500).json({
      success: false,
      message: '12시간 초과 상태 설정 실패',
      error: error.message
    });
  }
});

// 기존 데이터를 월별 집계 테이블로 마이그레이션
router.post('/migrate-existing-data', async (req, res) => {
  try {
    const db = await getDb();

    console.log('🔄 기존 데이터 마이그레이션 시작...');

    // 1. 기존 데이터 범위 확인
    const [dateRange] = await db.execute(`
      SELECT 
        MIN(report_date) as min_date,
        MAX(report_date) as max_date,
        COUNT(*) as total_reports
      FROM daily_work_reports
    `);

    if (dateRange.length === 0 || !dateRange[0].min_date) {
      return res.json({
        success: true,
        message: '마이그레이션할 데이터가 없습니다.',
        migrated_count: 0
      });
    }

    const { min_date, max_date, total_reports } = dateRange[0];
    console.log(`📊 데이터 범위: ${min_date} ~ ${max_date} (총 ${total_reports}건)`);

    // 2. 기존 monthly_worker_status, monthly_summary 데이터 삭제
    await db.execute('DELETE FROM monthly_summary');
    await db.execute('DELETE FROM monthly_worker_status');
    console.log('🗑️ 기존 집계 데이터 삭제 완료');

    // 3. 날짜별로 작업자별 상태 재계산
    const [allDates] = await db.execute(`
      SELECT DISTINCT report_date, worker_id
      FROM daily_work_reports
      WHERE report_date BETWEEN ? AND ?
      ORDER BY report_date, worker_id
    `, [min_date, max_date]);

    console.log(`🔄 ${allDates.length}개 날짜-작업자 조합 처리 중...`);

    let processedCount = 0;
    for (const { report_date, worker_id } of allDates) {
      try {
        // UpdateMonthlyWorkerStatus 프로시저 호출
        await db.execute('CALL UpdateMonthlyWorkerStatus(?, ?)', [report_date, worker_id]);
        processedCount++;
        
        if (processedCount % 50 === 0) {
          console.log(`📈 진행률: ${processedCount}/${allDates.length} (${Math.round(processedCount/allDates.length*100)}%)`);
        }
      } catch (error) {
        console.error(`❌ ${report_date} ${worker_id} 처리 오류:`, error.message);
      }
    }

    // 4. 결과 확인
    const [workerStatusCount] = await db.execute('SELECT COUNT(*) as count FROM monthly_worker_status');
    const [summaryCount] = await db.execute('SELECT COUNT(*) as count FROM monthly_summary');

    console.log(`✅ 마이그레이션 완료:`);
    console.log(`   - monthly_worker_status: ${workerStatusCount[0].count}건`);
    console.log(`   - monthly_summary: ${summaryCount[0].count}건`);

    res.json({
      success: true,
      message: '기존 데이터 마이그레이션 완료',
      original_reports: total_reports,
      processed_combinations: processedCount,
      worker_status_records: workerStatusCount[0].count,
      summary_records: summaryCount[0].count,
      date_range: {
        from: min_date,
        to: max_date
      }
    });

  } catch (error) {
    console.error('❌ 데이터 마이그레이션 오류:', error);
    res.status(500).json({
      success: false,
      message: '데이터 마이그레이션 실패',
      error: error.message
    });
  }
});

// DB 상태 확인
router.get('/check-data-status', async (req, res) => {
  try {
    const db = await getDb();

    const [dailyReports] = await db.execute('SELECT COUNT(*) as count FROM daily_work_reports');
    const [workerStatus] = await db.execute('SELECT COUNT(*) as count FROM monthly_worker_status');
    const [monthlySummary] = await db.execute('SELECT COUNT(*) as count FROM monthly_summary');

    // 최근 데이터 확인
    const [recentData] = await db.execute(`
      SELECT 
        DATE(report_date) as date,
        COUNT(*) as reports
      FROM daily_work_reports 
      WHERE report_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
      GROUP BY DATE(report_date)
      ORDER BY report_date DESC
      LIMIT 5
    `);

    const [recentSummary] = await db.execute(`
      SELECT 
        date,
        total_workers,
        has_issues,
        has_errors,
        has_overtime_warning
      FROM monthly_summary 
      WHERE date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
      ORDER BY date DESC
      LIMIT 5
    `);

    res.json({
      success: true,
      data: {
        daily_work_reports: dailyReports[0].count,
        monthly_worker_status: workerStatus[0].count,
        monthly_summary: monthlySummary[0].count,
        recent_daily_reports: recentData,
        recent_summary: recentSummary,
        migration_needed: workerStatus[0].count === 0 && dailyReports[0].count > 0
      }
    });

  } catch (error) {
    console.error('❌ DB 상태 확인 오류:', error);
    res.status(500).json({
      success: false,
      message: 'DB 상태 확인 실패',
      error: error.message
    });
  }
});

module.exports = router;