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

// models/tbmModel.js - TBM 시스템 모델
const { getDb } = require('../dbPool');

const TbmModel = {
  // ==================== TBM 세션 관련 ====================

  /**
   * TBM 세션 생성
   */
  createSession: async (sessionData, callback) => {
    try {
      const db = await getDb();
      const sql = `
        INSERT INTO tbm_sessions
        (session_date, leader_id, project_id, work_type_id, task_id, work_location, created_by)
        VALUES (?, ?, ?, ?, ?, ?, ?)
      `;

      const values = [
        sessionData.session_date,
        sessionData.leader_id,
        sessionData.project_id || null,
        sessionData.work_type_id || null,
        sessionData.task_id || null,
        sessionData.work_location || null,
        sessionData.created_by
      ];

      const [result] = await db.query(sql, values);
      callback(null, result);
    } catch (err) {
      callback(err);
    }
  },

  /**
   * 특정 날짜의 TBM 세션 조회
   */
  getSessionsByDate: async (date, callback) => {
    try {
      const db = await getDb();
      const sql = `
        SELECT
          s.*,
          w.worker_name as leader_name,
          w.job_type as leader_job_type,
          u.username as created_by_username,
          u.name as created_by_name,
          COUNT(DISTINCT ta.worker_id) as team_member_count,
          -- 첫 번째 팀원의 작업 정보 가져오기
          first_ta.project_id,
          first_ta.work_type_id,
          first_ta.task_id,
          first_ta.workplace_id,
          first_p.project_name,
          first_wt.name as work_type_name,
          first_t.task_name,
          first_wp.workplace_name as work_location
        FROM tbm_sessions s
        LEFT JOIN workers w ON s.leader_id = w.worker_id
        LEFT JOIN users u ON s.created_by = u.user_id
        LEFT JOIN tbm_team_assignments ta ON s.session_id = ta.session_id
        -- 첫 번째 팀원 정보 (가장 먼저 등록된 작업)
        LEFT JOIN (
          SELECT * FROM tbm_team_assignments
          WHERE (session_id, assignment_id) IN (
            SELECT session_id, MIN(assignment_id)
            FROM tbm_team_assignments
            GROUP BY session_id
          )
        ) first_ta ON s.session_id = first_ta.session_id
        LEFT JOIN projects first_p ON first_ta.project_id = first_p.project_id
        LEFT JOIN work_types first_wt ON first_ta.work_type_id = first_wt.id
        LEFT JOIN tasks first_t ON first_ta.task_id = first_t.task_id
        LEFT JOIN workplaces first_wp ON first_ta.workplace_id = first_wp.workplace_id
        WHERE s.session_date = ?
        GROUP BY s.session_id
        ORDER BY s.session_id DESC
      `;

      const [rows] = await db.query(sql, [date]);
      callback(null, rows);
    } catch (err) {
      callback(err);
    }
  },

  /**
   * TBM 세션 상세 조회
   */
  getSessionById: async (sessionId, callback) => {
    try {
      const db = await getDb();
      const sql = `
        SELECT
          s.*,
          w.worker_name as leader_name,
          w.job_type as leader_job_type,
          w.phone_number as leader_phone,
          p.project_name,
          p.job_no,
          p.site,
          wt.name as work_type_name,
          wt.category as work_type_category,
          t.task_name,
          t.description as task_description,
          u.username as created_by_username,
          u.name as created_by_name
        FROM tbm_sessions s
        LEFT JOIN workers w ON s.leader_id = w.worker_id
        LEFT JOIN projects p ON s.project_id = p.project_id
        LEFT JOIN work_types wt ON s.work_type_id = wt.id
        LEFT JOIN tasks t ON s.task_id = t.task_id
        LEFT JOIN users u ON s.created_by = u.user_id
        WHERE s.session_id = ?
      `;

      const [rows] = await db.query(sql, [sessionId]);
      callback(null, rows);
    } catch (err) {
      callback(err);
    }
  },

  /**
   * TBM 세션 수정
   */
  updateSession: async (sessionId, sessionData, callback) => {
    try {
      const db = await getDb();
      const sql = `
        UPDATE tbm_sessions
        SET
          project_id = ?,
          work_location = ?,
          status = ?,
          updated_at = NOW()
        WHERE session_id = ?
      `;

      const values = [
        sessionData.project_id,
        sessionData.work_location,
        sessionData.status,
        sessionId
      ];

      const [result] = await db.query(sql, values);
      callback(null, result);
    } catch (err) {
      callback(err);
    }
  },

  /**
   * TBM 세션 완료 처리
   */
  completeSession: async (sessionId, endTime, callback) => {
    try {
      const db = await getDb();
      const sql = `
        UPDATE tbm_sessions
        SET
          status = 'completed',
          end_time = ?,
          updated_at = NOW()
        WHERE session_id = ?
      `;

      const [result] = await db.query(sql, [endTime, sessionId]);
      callback(null, result);
    } catch (err) {
      callback(err);
    }
  },

  // ==================== 팀 구성 관련 ====================

  /**
   * 팀원 추가 (작업자별 상세 정보 포함)
   */
  addTeamMember: async (assignmentData, callback) => {
    try {
      const db = await getDb();
      const sql = `
        INSERT INTO tbm_team_assignments
        (session_id, worker_id, assigned_role, work_detail, is_present, absence_reason,
         project_id, work_type_id, task_id, workplace_category_id, workplace_id)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE
          assigned_role = VALUES(assigned_role),
          work_detail = VALUES(work_detail),
          is_present = VALUES(is_present),
          absence_reason = VALUES(absence_reason),
          project_id = VALUES(project_id),
          work_type_id = VALUES(work_type_id),
          task_id = VALUES(task_id),
          workplace_category_id = VALUES(workplace_category_id),
          workplace_id = VALUES(workplace_id)
      `;

      const values = [
        assignmentData.session_id,
        assignmentData.worker_id,
        assignmentData.assigned_role,
        assignmentData.work_detail,
        assignmentData.is_present !== undefined ? assignmentData.is_present : true,
        assignmentData.absence_reason,
        assignmentData.project_id || null,
        assignmentData.work_type_id || null,
        assignmentData.task_id || null,
        assignmentData.workplace_category_id || null,
        assignmentData.workplace_id || null
      ];

      const [result] = await db.query(sql, values);
      callback(null, result);
    } catch (err) {
      callback(err);
    }
  },

  /**
   * 팀 구성 일괄 추가 (작업자별 상세 정보 포함)
   */
  addTeamMembers: async (sessionId, members, callback) => {
    try {
      if (!members || members.length === 0) {
        return callback(null, { affectedRows: 0 });
      }

      const db = await getDb();
      const values = members.map(m => [
        sessionId,
        m.worker_id,
        m.assigned_role || null,
        m.work_detail || null,
        m.is_present !== undefined ? m.is_present : true,
        m.absence_reason || null,
        m.project_id || null,
        m.work_type_id || null,
        m.task_id || null,
        m.workplace_category_id || null,
        m.workplace_id || null
      ]);

      const sql = `
        INSERT INTO tbm_team_assignments
        (session_id, worker_id, assigned_role, work_detail, is_present, absence_reason,
         project_id, work_type_id, task_id, workplace_category_id, workplace_id)
        VALUES ?
      `;

      const [result] = await db.query(sql, [values]);
      callback(null, result);
    } catch (err) {
      callback(err);
    }
  },

  /**
   * TBM 세션의 팀 구성 조회 (작업자별 상세 정보 포함)
   */
  getTeamMembers: async (sessionId, callback) => {
    try {
      const db = await getDb();
      const sql = `
        SELECT
          ta.*,
          w.worker_name,
          w.job_type,
          w.phone_number,
          w.department,
          p.project_name,
          wt.name as work_type_name,
          t.task_name,
          wc.category_name AS workplace_category_name,
          wp.workplace_name
        FROM tbm_team_assignments ta
        INNER JOIN workers w ON ta.worker_id = w.worker_id
        LEFT JOIN projects p ON ta.project_id = p.project_id
        LEFT JOIN work_types wt ON ta.work_type_id = wt.id
        LEFT JOIN tasks t ON ta.task_id = t.task_id
        LEFT JOIN workplace_categories wc ON ta.workplace_category_id = wc.category_id
        LEFT JOIN workplaces wp ON ta.workplace_id = wp.workplace_id
        WHERE ta.session_id = ?
        ORDER BY ta.assigned_at DESC
      `;

      const [rows] = await db.query(sql, [sessionId]);
      callback(null, rows);
    } catch (err) {
      callback(err);
    }
  },

  /**
   * 팀원 제거
   */
  removeTeamMember: async (sessionId, workerId, callback) => {
    try {
      const db = await getDb();
      const sql = `
        DELETE FROM tbm_team_assignments
        WHERE session_id = ? AND worker_id = ?
      `;

      const [result] = await db.query(sql, [sessionId, workerId]);
      callback(null, result);
    } catch (err) {
      callback(err);
    }
  },

  /**
   * 세션의 모든 팀원 삭제
   */
  clearAllTeamMembers: async (sessionId, callback) => {
    try {
      const db = await getDb();
      const sql = `
        DELETE FROM tbm_team_assignments
        WHERE session_id = ?
      `;

      const [result] = await db.query(sql, [sessionId]);
      callback(null, result);
    } catch (err) {
      callback(err);
    }
  },

  // ==================== 안전 체크리스트 관련 ====================

  /**
   * 모든 안전 체크 항목 조회
   */
  getAllSafetyChecks: async (callback) => {
    try {
      const db = await getDb();
      const sql = `
        SELECT *
        FROM tbm_safety_checks
        WHERE is_active = 1
        ORDER BY check_category, display_order
      `;

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

  /**
   * 카테고리별 안전 체크 항목 조회
   */
  getSafetyChecksByCategory: async (category, callback) => {
    try {
      const db = await getDb();
      const sql = `
        SELECT *
        FROM tbm_safety_checks
        WHERE check_category = ? AND is_active = 1
        ORDER BY display_order
      `;

      const [rows] = await db.query(sql, [category]);
      callback(null, rows);
    } catch (err) {
      callback(err);
    }
  },

  /**
   * TBM 세션의 안전 체크 기록 조회
   */
  getSafetyRecords: async (sessionId, callback) => {
    try {
      const db = await getDb();
      const sql = `
        SELECT
          sr.*,
          sc.check_category,
          sc.check_item,
          sc.description,
          sc.is_required,
          u.username as checked_by_username,
          u.name as checked_by_name
        FROM tbm_safety_records sr
        INNER JOIN tbm_safety_checks sc ON sr.check_id = sc.check_id
        LEFT JOIN users u ON sr.checked_by = u.user_id
        WHERE sr.session_id = ?
        ORDER BY sc.check_category, sc.display_order
      `;

      const [rows] = await db.query(sql, [sessionId]);
      callback(null, rows);
    } catch (err) {
      callback(err);
    }
  },

  /**
   * 안전 체크 기록 저장/업데이트
   */
  saveSafetyRecord: async (recordData, callback) => {
    try {
      const db = await getDb();
      const sql = `
        INSERT INTO tbm_safety_records
        (session_id, check_id, is_checked, notes, checked_by, checked_at)
        VALUES (?, ?, ?, ?, ?, NOW())
        ON DUPLICATE KEY UPDATE
          is_checked = VALUES(is_checked),
          notes = VALUES(notes),
          checked_by = VALUES(checked_by),
          checked_at = NOW()
      `;

      const values = [
        recordData.session_id,
        recordData.check_id,
        recordData.is_checked,
        recordData.notes,
        recordData.checked_by
      ];

      const [result] = await db.query(sql, values);
      callback(null, result);
    } catch (err) {
      callback(err);
    }
  },

  /**
   * 안전 체크 일괄 저장
   */
  saveSafetyRecords: async (sessionId, records, checkedBy, callback) => {
    try {
      if (!records || records.length === 0) {
        return callback(null, { affectedRows: 0 });
      }

      const db = await getDb();
      const values = records.map(r => [
        sessionId,
        r.check_id,
        r.is_checked,
        r.notes || null,
        checkedBy
      ]);

      const sql = `
        INSERT INTO tbm_safety_records
        (session_id, check_id, is_checked, notes, checked_by, checked_at)
        VALUES ?
        ON DUPLICATE KEY UPDATE
          is_checked = VALUES(is_checked),
          notes = VALUES(notes),
          checked_by = VALUES(checked_by),
          checked_at = NOW()
      `;

      const [result] = await db.query(sql, [values]);
      callback(null, result);
    } catch (err) {
      callback(err);
    }
  },

  // ==================== 작업 인계 관련 ====================

  /**
   * 작업 인계 생성
   */
  createHandover: async (handoverData, callback) => {
    try {
      const db = await getDb();
      const sql = `
        INSERT INTO team_handovers
        (session_id, from_leader_id, to_leader_id, handover_date, handover_time,
         reason, handover_notes, worker_ids)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
      `;

      const values = [
        handoverData.session_id,
        handoverData.from_leader_id,
        handoverData.to_leader_id,
        handoverData.handover_date,
        handoverData.handover_time,
        handoverData.reason,
        handoverData.handover_notes,
        JSON.stringify(handoverData.worker_ids || [])
      ];

      const [result] = await db.query(sql, values);
      callback(null, result);
    } catch (err) {
      callback(err);
    }
  },

  /**
   * 작업 인계 확인
   */
  confirmHandover: async (handoverId, confirmedBy, callback) => {
    try {
      const db = await getDb();
      const sql = `
        UPDATE team_handovers
        SET
          is_confirmed = 1,
          confirmed_at = NOW(),
          confirmed_by = ?
        WHERE handover_id = ?
      `;

      const [result] = await db.query(sql, [confirmedBy, handoverId]);
      callback(null, result);
    } catch (err) {
      callback(err);
    }
  },

  /**
   * 특정 날짜의 작업 인계 목록 조회
   */
  getHandoversByDate: async (date, callback) => {
    try {
      const db = await getDb();
      const sql = `
        SELECT
          h.*,
          w1.worker_name as from_leader_name,
          w2.worker_name as to_leader_name,
          u.username as confirmed_by_username,
          u.name as confirmed_by_name
        FROM team_handovers h
        INNER JOIN workers w1 ON h.from_leader_id = w1.worker_id
        INNER JOIN workers w2 ON h.to_leader_id = w2.worker_id
        LEFT JOIN users u ON h.confirmed_by = u.user_id
        WHERE h.handover_date = ?
        ORDER BY h.handover_time DESC
      `;

      const [rows] = await db.query(sql, [date]);
      callback(null, rows);
    } catch (err) {
      callback(err);
    }
  },

  /**
   * 인수자가 받은 미확인 인계 건 조회
   */
  getPendingHandovers: async (toLeaderId, callback) => {
    try {
      const db = await getDb();
      const sql = `
        SELECT
          h.*,
          w1.worker_name as from_leader_name,
          w1.phone_number as from_leader_phone,
          s.work_location
        FROM team_handovers h
        INNER JOIN workers w1 ON h.from_leader_id = w1.worker_id
        LEFT JOIN tbm_sessions s ON h.session_id = s.session_id
        WHERE h.to_leader_id = ? AND h.is_confirmed = 0
        ORDER BY h.handover_date DESC, h.handover_time DESC
      `;

      const [rows] = await db.query(sql, [toLeaderId]);
      callback(null, rows);
    } catch (err) {
      callback(err);
    }
  },

  // ==================== 통계 및 리포트 ====================

  /**
   * 특정 기간의 TBM 통계
   */
  getTbmStatistics: async (startDate, endDate, callback) => {
    try {
      const db = await getDb();
      const sql = `
        SELECT
          DATE(session_date) as date,
          COUNT(DISTINCT session_id) as session_count,
          COUNT(DISTINCT leader_id) as leader_count,
          SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed_count
        FROM tbm_sessions
        WHERE session_date BETWEEN ? AND ?
        GROUP BY DATE(session_date)
        ORDER BY date DESC
      `;

      const [rows] = await db.query(sql, [startDate, endDate]);
      callback(null, rows);
    } catch (err) {
      callback(err);
    }
  },

  /**
   * 리더별 TBM 진행 현황
   */
  getLeaderStatistics: async (startDate, endDate, callback) => {
    try {
      const db = await getDb();
      const sql = `
        SELECT
          s.leader_id,
          w.worker_name as leader_name,
          COUNT(DISTINCT s.session_id) as total_sessions,
          SUM(CASE WHEN s.status = 'completed' THEN 1 ELSE 0 END) as completed_sessions,
          COUNT(DISTINCT ta.worker_id) as total_team_members
        FROM tbm_sessions s
        INNER JOIN workers w ON s.leader_id = w.worker_id
        LEFT JOIN tbm_team_assignments ta ON s.session_id = ta.session_id
        WHERE s.session_date BETWEEN ? AND ?
        GROUP BY s.leader_id
        ORDER BY total_sessions DESC
      `;

      const [rows] = await db.query(sql, [startDate, endDate]);
      callback(null, rows);
    } catch (err) {
      callback(err);
    }
  },

  /**
   * 작업보고서가 작성되지 않은 TBM 세션의 팀 배정 조회
   * @param {number|null} userId - 조회할 사용자 ID (null이면 모든 TBM 조회 - 관리자용)
   */
  getIncompleteWorkReports: async (userId, callback) => {
    try {
      const db = await getDb();

      // WHERE 조건 동적 생성
      let whereClause = `
        WHERE dwr.id IS NULL
          AND s.status = 'draft'
      `;
      const params = [];

      // userId가 있으면 created_by 조건 추가 (일반 사용자)
      if (userId !== null && userId !== undefined) {
        whereClause = `
          WHERE s.created_by = ?
            AND dwr.id IS NULL
            AND s.status = 'draft'
        `;
        params.push(userId);
      }

      const sql = `
        SELECT
          ta.assignment_id,
          ta.session_id,
          ta.worker_id,
          ta.project_id,
          ta.work_type_id,
          ta.task_id,
          ta.workplace_category_id,
          ta.workplace_id,
          s.session_date,
          s.status as session_status,
          s.created_by,
          w.worker_name,
          w.job_type,
          p.project_name,
          wt.name as work_type_name,
          t.task_name,
          wp.workplace_name,
          wc.category_name,
          creator.name as created_by_name
        FROM tbm_team_assignments ta
        INNER JOIN tbm_sessions s ON ta.session_id = s.session_id
        INNER JOIN workers w ON ta.worker_id = w.worker_id
        LEFT JOIN users creator ON s.created_by = creator.user_id
        LEFT JOIN projects p ON ta.project_id = p.project_id
        LEFT JOIN work_types wt ON ta.work_type_id = wt.id
        LEFT JOIN tasks t ON ta.task_id = t.task_id
        LEFT JOIN workplaces wp ON ta.workplace_id = wp.workplace_id
        LEFT JOIN workplace_categories wc ON ta.workplace_category_id = wc.category_id
        LEFT JOIN daily_work_reports dwr ON ta.assignment_id = dwr.tbm_assignment_id
        ${whereClause}
        ORDER BY s.session_date DESC, ta.assignment_id ASC
      `;

      const [rows] = await db.query(sql, params);
      callback(null, rows);
    } catch (err) {
      callback(err);
    }
  },

  // ========== 안전 체크리스트 확장 메서드 ==========

  /**
   * 유형별 안전 체크 항목 조회
   * @param {string} checkType - 체크 유형 (basic, weather, task)
   * @param {Object} options - 추가 옵션 (weatherCondition, taskId)
   */
  getSafetyChecksByType: async (checkType, options = {}, callback) => {
    try {
      const db = await getDb();
      let sql = `
        SELECT sc.*,
               wc.condition_name as weather_condition_name,
               wc.icon as weather_icon,
               t.task_name
        FROM tbm_safety_checks sc
        LEFT JOIN weather_conditions wc ON sc.weather_condition = wc.condition_code
        LEFT JOIN tasks t ON sc.task_id = t.task_id
        WHERE sc.is_active = 1 AND sc.check_type = ?
      `;
      const params = [checkType];

      if (checkType === 'weather' && options.weatherCondition) {
        sql += ' AND sc.weather_condition = ?';
        params.push(options.weatherCondition);
      }

      if (checkType === 'task' && options.taskId) {
        sql += ' AND sc.task_id = ?';
        params.push(options.taskId);
      }

      sql += ' ORDER BY sc.check_category, sc.display_order';

      const [rows] = await db.query(sql, params);
      callback(null, rows);
    } catch (err) {
      callback(err);
    }
  },

  /**
   * 날씨 조건별 안전 체크 항목 조회 (복수 조건)
   * @param {string[]} conditions - 날씨 조건 배열 ['rain', 'wind']
   */
  getSafetyChecksByWeather: async (conditions, callback) => {
    try {
      const db = await getDb();

      if (!conditions || conditions.length === 0) {
        return callback(null, []);
      }

      const placeholders = conditions.map(() => '?').join(',');
      const sql = `
        SELECT sc.*,
               wc.condition_name as weather_condition_name,
               wc.icon as weather_icon
        FROM tbm_safety_checks sc
        LEFT JOIN weather_conditions wc ON sc.weather_condition = wc.condition_code
        WHERE sc.is_active = 1
          AND sc.check_type = 'weather'
          AND sc.weather_condition IN (${placeholders})
        ORDER BY sc.weather_condition, sc.display_order
      `;

      const [rows] = await db.query(sql, conditions);
      callback(null, rows);
    } catch (err) {
      callback(err);
    }
  },

  /**
   * 작업별 안전 체크 항목 조회 (복수 작업)
   * @param {number[]} taskIds - 작업 ID 배열
   */
  getSafetyChecksByTasks: async (taskIds, callback) => {
    try {
      const db = await getDb();

      if (!taskIds || taskIds.length === 0) {
        return callback(null, []);
      }

      const placeholders = taskIds.map(() => '?').join(',');
      const sql = `
        SELECT sc.*,
               t.task_name,
               wt.name as work_type_name
        FROM tbm_safety_checks sc
        LEFT JOIN tasks t ON sc.task_id = t.task_id
        LEFT JOIN work_types wt ON t.work_type_id = wt.id
        WHERE sc.is_active = 1
          AND sc.check_type = 'task'
          AND sc.task_id IN (${placeholders})
        ORDER BY sc.task_id, sc.display_order
      `;

      const [rows] = await db.query(sql, taskIds);
      callback(null, rows);
    } catch (err) {
      callback(err);
    }
  },

  /**
   * TBM 세션에 맞는 필터링된 안전 체크 항목 조회
   * 기본 + 날씨 + 작업별 체크항목 통합 조회
   * @param {number} sessionId - TBM 세션 ID
   * @param {string[]} weatherConditions - 날씨 조건 배열 (optional)
   */
  getFilteredSafetyChecks: async (sessionId, weatherConditions = [], callback) => {
    try {
      const db = await getDb();

      // 1. 세션 정보에서 작업 ID 목록 조회
      const [assignments] = await db.query(`
        SELECT DISTINCT task_id
        FROM tbm_team_assignments
        WHERE session_id = ? AND task_id IS NOT NULL
      `, [sessionId]);

      const taskIds = assignments.map(a => a.task_id);

      // 2. 기본 체크항목 조회
      const [basicChecks] = await db.query(`
        SELECT sc.*, 'basic' as section_type
        FROM tbm_safety_checks sc
        WHERE sc.is_active = 1 AND sc.check_type = 'basic'
        ORDER BY sc.check_category, sc.display_order
      `);

      // 3. 날씨별 체크항목 조회
      let weatherChecks = [];
      if (weatherConditions && weatherConditions.length > 0) {
        const wcPlaceholders = weatherConditions.map(() => '?').join(',');
        const [rows] = await db.query(`
          SELECT sc.*, wc.condition_name as weather_condition_name, wc.icon as weather_icon,
                 'weather' as section_type
          FROM tbm_safety_checks sc
          LEFT JOIN weather_conditions wc ON sc.weather_condition = wc.condition_code
          WHERE sc.is_active = 1
            AND sc.check_type = 'weather'
            AND sc.weather_condition IN (${wcPlaceholders})
          ORDER BY sc.weather_condition, sc.display_order
        `, weatherConditions);
        weatherChecks = rows;
      }

      // 4. 작업별 체크항목 조회
      let taskChecks = [];
      if (taskIds.length > 0) {
        const taskPlaceholders = taskIds.map(() => '?').join(',');
        const [rows] = await db.query(`
          SELECT sc.*, t.task_name, wt.name as work_type_name,
                 'task' as section_type
          FROM tbm_safety_checks sc
          LEFT JOIN tasks t ON sc.task_id = t.task_id
          LEFT JOIN work_types wt ON t.work_type_id = wt.id
          WHERE sc.is_active = 1
            AND sc.check_type = 'task'
            AND sc.task_id IN (${taskPlaceholders})
          ORDER BY sc.task_id, sc.display_order
        `, taskIds);
        taskChecks = rows;
      }

      // 5. 기존 체크 기록 조회
      const [existingRecords] = await db.query(`
        SELECT check_id, is_checked, notes
        FROM tbm_safety_records
        WHERE session_id = ?
      `, [sessionId]);

      const recordMap = {};
      existingRecords.forEach(r => {
        recordMap[r.check_id] = { is_checked: r.is_checked, notes: r.notes };
      });

      // 6. 기록과 병합
      const mergeWithRecords = (checks) => {
        return checks.map(check => ({
          ...check,
          is_checked: recordMap[check.check_id]?.is_checked || false,
          notes: recordMap[check.check_id]?.notes || null
        }));
      };

      const result = {
        basic: mergeWithRecords(basicChecks),
        weather: mergeWithRecords(weatherChecks),
        task: mergeWithRecords(taskChecks),
        totalCount: basicChecks.length + weatherChecks.length + taskChecks.length,
        weatherConditions: weatherConditions
      };

      callback(null, result);
    } catch (err) {
      callback(err);
    }
  },

  /**
   * 안전 체크 항목 생성 (관리자용)
   */
  createSafetyCheck: async (checkData, callback) => {
    try {
      const db = await getDb();
      const sql = `
        INSERT INTO tbm_safety_checks
        (check_category, check_type, weather_condition, task_id, check_item, description, is_required, display_order)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
      `;

      const values = [
        checkData.check_category,
        checkData.check_type || 'basic',
        checkData.weather_condition || null,
        checkData.task_id || null,
        checkData.check_item,
        checkData.description || null,
        checkData.is_required !== false,
        checkData.display_order || 0
      ];

      const [result] = await db.query(sql, values);
      callback(null, { insertId: result.insertId });
    } catch (err) {
      callback(err);
    }
  },

  /**
   * 안전 체크 항목 수정 (관리자용)
   */
  updateSafetyCheck: async (checkId, checkData, callback) => {
    try {
      const db = await getDb();
      const sql = `
        UPDATE tbm_safety_checks
        SET check_category = ?,
            check_type = ?,
            weather_condition = ?,
            task_id = ?,
            check_item = ?,
            description = ?,
            is_required = ?,
            display_order = ?,
            is_active = ?,
            updated_at = NOW()
        WHERE check_id = ?
      `;

      const values = [
        checkData.check_category,
        checkData.check_type || 'basic',
        checkData.weather_condition || null,
        checkData.task_id || null,
        checkData.check_item,
        checkData.description || null,
        checkData.is_required !== false,
        checkData.display_order || 0,
        checkData.is_active !== false,
        checkId
      ];

      const [result] = await db.query(sql, values);
      callback(null, { affectedRows: result.affectedRows });
    } catch (err) {
      callback(err);
    }
  },

  /**
   * 안전 체크 항목 삭제 (비활성화)
   */
  deleteSafetyCheck: async (checkId, callback) => {
    try {
      const db = await getDb();
      // 실제 삭제 대신 비활성화
      const sql = `UPDATE tbm_safety_checks SET is_active = 0 WHERE check_id = ?`;

      const [result] = await db.query(sql, [checkId]);
      callback(null, { affectedRows: result.affectedRows });
    } catch (err) {
      callback(err);
    }
  }
};

module.exports = TbmModel;