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

// patrolModel.js
// 일일순회점검 시스템 모델

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

const PatrolModel = {
  // ==================== 순회점검 세션 ====================

  // 세션 생성 또는 조회
  getOrCreateSession: async (patrolDate, patrolTime, categoryId, inspectorId) => {
    const db = await getDb();

    // 기존 세션 확인
    const [existingRows] = await db.query(`
      SELECT session_id, status, started_at, completed_at
      FROM daily_patrol_sessions
      WHERE patrol_date = ? AND patrol_time = ? AND category_id = ?
    `, [patrolDate, patrolTime, categoryId]);

    if (existingRows.length > 0) {
      return existingRows[0];
    }

    // 새 세션 생성
    const [result] = await db.query(`
      INSERT INTO daily_patrol_sessions (patrol_date, patrol_time, category_id, inspector_id, started_at)
      VALUES (?, ?, ?, ?, CURTIME())
    `, [patrolDate, patrolTime, categoryId, inspectorId]);

    return {
      session_id: result.insertId,
      status: 'in_progress',
      started_at: new Date().toTimeString().slice(0, 8)
    };
  },

  // 세션 조회
  getSession: async (sessionId) => {
    const db = await getDb();
    const [rows] = await db.query(`
      SELECT s.*, u.name AS inspector_name, wc.category_name
      FROM daily_patrol_sessions s
      LEFT JOIN users u ON s.inspector_id = u.user_id
      LEFT JOIN workplace_categories wc ON s.category_id = wc.category_id
      WHERE s.session_id = ?
    `, [sessionId]);
    return rows[0] || null;
  },

  // 세션 목록 조회
  getSessions: async (filters = {}) => {
    const db = await getDb();
    let query = `
      SELECT s.*, u.name AS inspector_name, wc.category_name,
        (SELECT COUNT(*) FROM patrol_check_records WHERE session_id = s.session_id AND is_checked = 1) AS checked_count,
        (SELECT COUNT(*) FROM patrol_check_records WHERE session_id = s.session_id) AS total_count
      FROM daily_patrol_sessions s
      LEFT JOIN users u ON s.inspector_id = u.user_id
      LEFT JOIN workplace_categories wc ON s.category_id = wc.category_id
      WHERE 1=1
    `;
    const params = [];

    if (filters.patrol_date) {
      query += ' AND s.patrol_date = ?';
      params.push(filters.patrol_date);
    }
    if (filters.patrol_time) {
      query += ' AND s.patrol_time = ?';
      params.push(filters.patrol_time);
    }
    if (filters.category_id) {
      query += ' AND s.category_id = ?';
      params.push(filters.category_id);
    }
    if (filters.status) {
      query += ' AND s.status = ?';
      params.push(filters.status);
    }

    query += ' ORDER BY s.patrol_date DESC, s.patrol_time DESC';

    if (filters.limit) {
      query += ' LIMIT ?';
      params.push(parseInt(filters.limit));
    }

    const [rows] = await db.query(query, params);
    return rows;
  },

  // 세션 완료 처리
  completeSession: async (sessionId) => {
    const db = await getDb();
    await db.query(`
      UPDATE daily_patrol_sessions
      SET status = 'completed', completed_at = CURTIME(), updated_at = NOW()
      WHERE session_id = ?
    `, [sessionId]);
    return true;
  },

  // 세션 메모 업데이트
  updateSessionNotes: async (sessionId, notes) => {
    const db = await getDb();
    await db.query(`
      UPDATE daily_patrol_sessions
      SET notes = ?, updated_at = NOW()
      WHERE session_id = ?
    `, [notes, sessionId]);
    return true;
  },

  // ==================== 체크리스트 항목 ====================

  // 체크리스트 항목 조회 (공장/작업장별 필터링)
  getChecklistItems: async (categoryId = null, workplaceId = null) => {
    const db = await getDb();
    let query = `
      SELECT *
      FROM patrol_checklist_items
      WHERE is_active = 1
        AND (workplace_id IS NULL OR workplace_id = ?)
        AND (category_id IS NULL OR category_id = ?)
      ORDER BY check_category, display_order, check_item
    `;
    const [rows] = await db.query(query, [workplaceId, categoryId]);
    return rows;
  },

  // 체크리스트 항목 CRUD
  createChecklistItem: async (data) => {
    const db = await getDb();
    const [result] = await db.query(`
      INSERT INTO patrol_checklist_items (workplace_id, category_id, check_category, check_item, description, display_order, is_required)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `, [data.workplace_id, data.category_id, data.check_category, data.check_item, data.description, data.display_order || 0, data.is_required !== false]);
    return result.insertId;
  },

  updateChecklistItem: async (itemId, data) => {
    const db = await getDb();
    const fields = [];
    const params = [];

    ['workplace_id', 'category_id', 'check_category', 'check_item', 'description', 'display_order', 'is_required', 'is_active'].forEach(key => {
      if (data[key] !== undefined) {
        fields.push(`${key} = ?`);
        params.push(data[key]);
      }
    });

    if (fields.length === 0) return false;

    params.push(itemId);
    await db.query(`UPDATE patrol_checklist_items SET ${fields.join(', ')}, updated_at = NOW() WHERE item_id = ?`, params);
    return true;
  },

  deleteChecklistItem: async (itemId) => {
    const db = await getDb();
    await db.query('UPDATE patrol_checklist_items SET is_active = 0, updated_at = NOW() WHERE item_id = ?', [itemId]);
    return true;
  },

  // ==================== 체크 기록 ====================

  // 작업장별 체크 기록 조회
  getCheckRecords: async (sessionId, workplaceId = null) => {
    const db = await getDb();
    let query = `
      SELECT r.*, ci.check_category, ci.check_item, ci.is_required
      FROM patrol_check_records r
      JOIN patrol_checklist_items ci ON r.check_item_id = ci.item_id
      WHERE r.session_id = ?
    `;
    const params = [sessionId];

    if (workplaceId) {
      query += ' AND r.workplace_id = ?';
      params.push(workplaceId);
    }

    query += ' ORDER BY ci.check_category, ci.display_order';

    const [rows] = await db.query(query, params);
    return rows;
  },

  // 체크 기록 저장 (upsert)
  saveCheckRecord: async (sessionId, workplaceId, checkItemId, isChecked, checkResult = null, note = null) => {
    const db = await getDb();
    await db.query(`
      INSERT INTO patrol_check_records (session_id, workplace_id, check_item_id, is_checked, check_result, note, checked_at)
      VALUES (?, ?, ?, ?, ?, ?, NOW())
      ON DUPLICATE KEY UPDATE
        is_checked = VALUES(is_checked),
        check_result = VALUES(check_result),
        note = VALUES(note),
        checked_at = NOW()
    `, [sessionId, workplaceId, checkItemId, isChecked, checkResult, note]);
    return true;
  },

  // 여러 체크 기록 일괄 저장
  saveCheckRecords: async (sessionId, workplaceId, records) => {
    const db = await getDb();
    for (const record of records) {
      await db.query(`
        INSERT INTO patrol_check_records (session_id, workplace_id, check_item_id, is_checked, check_result, note, checked_at)
        VALUES (?, ?, ?, ?, ?, ?, NOW())
        ON DUPLICATE KEY UPDATE
          is_checked = VALUES(is_checked),
          check_result = VALUES(check_result),
          note = VALUES(note),
          checked_at = NOW()
      `, [sessionId, workplaceId, record.check_item_id, record.is_checked, record.check_result, record.note]);
    }
    return true;
  },

  // ==================== 작업장 물품 현황 ====================

  // 작업장 물품 조회
  getWorkplaceItems: async (workplaceId, activeOnly = true) => {
    const db = await getDb();
    let query = `
      SELECT wi.*, u.name AS created_by_name, it.type_name, it.icon, it.color
      FROM workplace_items wi
      LEFT JOIN users u ON wi.created_by = u.user_id
      LEFT JOIN item_types it ON wi.item_type = it.type_code
      WHERE wi.workplace_id = ?
    `;
    if (activeOnly) {
      query += ' AND wi.is_active = 1';
    }
    query += ' ORDER BY wi.created_at DESC';

    const [rows] = await db.query(query, [workplaceId]);
    return rows;
  },

  // 물품 추가
  createWorkplaceItem: async (data) => {
    const db = await getDb();
    const [result] = await db.query(`
      INSERT INTO workplace_items
      (workplace_id, patrol_session_id, project_id, item_type, item_name, quantity, x_percent, y_percent, width_percent, height_percent, created_by)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `, [
      data.workplace_id,
      data.patrol_session_id,
      data.project_id,
      data.item_type,
      data.item_name,
      data.quantity || 1,
      data.x_percent,
      data.y_percent,
      data.width_percent,
      data.height_percent,
      data.created_by
    ]);
    return result.insertId;
  },

  // 물품 수정
  updateWorkplaceItem: async (itemId, data, userId) => {
    const db = await getDb();
    const fields = [];
    const params = [];

    ['item_type', 'item_name', 'quantity', 'x_percent', 'y_percent', 'width_percent', 'height_percent', 'is_active', 'project_id'].forEach(key => {
      if (data[key] !== undefined) {
        fields.push(`${key} = ?`);
        params.push(data[key]);
      }
    });

    if (fields.length === 0) return false;

    fields.push('updated_by = ?', 'updated_at = NOW()');
    params.push(userId, itemId);

    await db.query(`UPDATE workplace_items SET ${fields.join(', ')} WHERE item_id = ?`, params);
    return true;
  },

  // 물품 삭제 (비활성화)
  deleteWorkplaceItem: async (itemId, userId) => {
    const db = await getDb();
    await db.query('UPDATE workplace_items SET is_active = 0, updated_by = ?, updated_at = NOW() WHERE item_id = ?', [userId, itemId]);
    return true;
  },

  // 물품 영구 삭제
  hardDeleteWorkplaceItem: async (itemId) => {
    const db = await getDb();
    await db.query('DELETE FROM workplace_items WHERE item_id = ?', [itemId]);
    return true;
  },

  // ==================== 물품 유형 ====================

  // 물품 유형 목록 조회
  getItemTypes: async () => {
    const db = await getDb();
    const [rows] = await db.query('SELECT * FROM item_types WHERE is_active = 1 ORDER BY display_order');
    return rows;
  },

  // ==================== 대시보드/통계 ====================

  // 오늘 순회점검 현황
  getTodayPatrolStatus: async (categoryId = null) => {
    const db = await getDb();
    const today = new Date().toISOString().slice(0, 10);

    let query = `
      SELECT s.session_id, s.patrol_time, s.status, s.inspector_id, u.name AS inspector_name,
        s.started_at, s.completed_at,
        (SELECT COUNT(*) FROM patrol_check_records WHERE session_id = s.session_id AND is_checked = 1) AS checked_count,
        (SELECT COUNT(*) FROM patrol_check_records WHERE session_id = s.session_id) AS total_count
      FROM daily_patrol_sessions s
      LEFT JOIN users u ON s.inspector_id = u.user_id
      WHERE s.patrol_date = ?
    `;
    const params = [today];

    if (categoryId) {
      query += ' AND s.category_id = ?';
      params.push(categoryId);
    }

    query += ' ORDER BY s.patrol_time';

    const [rows] = await db.query(query, params);
    return rows;
  },

  // 작업장별 점검 현황 (세션 기준)
  getWorkplaceCheckStatus: async (sessionId) => {
    const db = await getDb();
    const [rows] = await db.query(`
      SELECT w.workplace_id, w.workplace_name,
        COUNT(DISTINCT r.check_item_id) AS checked_count,
        (SELECT COUNT(*) FROM patrol_checklist_items WHERE is_active = 1) AS total_items,
        MAX(r.checked_at) AS last_check_time
      FROM workplaces w
      LEFT JOIN patrol_check_records r ON w.workplace_id = r.workplace_id AND r.session_id = ?
      WHERE w.is_active = 1
      GROUP BY w.workplace_id
      ORDER BY w.workplace_name
    `, [sessionId]);
    return rows;
  }
};

module.exports = PatrolModel;