TK-FB-Project/api.hyungi.net/controllers/patrolController.js

// patrolController.js
// 일일순회점검 시스템 컨트롤러

const PatrolModel = require('../models/patrolModel');

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

  // 세션 시작/조회
  getOrCreateSession: async (req, res) => {
    try {
      const { patrol_date, patrol_time, category_id } = req.body;
      const inspectorId = req.user.user_id;

      if (!patrol_date || !patrol_time || !category_id) {
        return res.status(400).json({ success: false, message: '필수 정보가 누락되었습니다.' });
      }

      const session = await PatrolModel.getOrCreateSession(patrol_date, patrol_time, category_id, inspectorId);
      res.json({ success: true, data: session });
    } catch (error) {
      console.error('세션 생성/조회 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

  // 세션 상세 조회
  getSession: async (req, res) => {
    try {
      const { sessionId } = req.params;
      const session = await PatrolModel.getSession(sessionId);

      if (!session) {
        return res.status(404).json({ success: false, message: '세션을 찾을 수 없습니다.' });
      }

      res.json({ success: true, data: session });
    } catch (error) {
      console.error('세션 조회 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

  // 세션 목록 조회
  getSessions: async (req, res) => {
    try {
      const { patrol_date, patrol_time, category_id, status, limit } = req.query;
      const sessions = await PatrolModel.getSessions({
        patrol_date,
        patrol_time,
        category_id,
        status,
        limit
      });
      res.json({ success: true, data: sessions });
    } catch (error) {
      console.error('세션 목록 조회 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

  // 세션 완료
  completeSession: async (req, res) => {
    try {
      const { sessionId } = req.params;
      await PatrolModel.completeSession(sessionId);
      res.json({ success: true, message: '순회점검이 완료되었습니다.' });
    } catch (error) {
      console.error('세션 완료 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

  // 세션 메모 업데이트
  updateSessionNotes: async (req, res) => {
    try {
      const { sessionId } = req.params;
      const { notes } = req.body;
      await PatrolModel.updateSessionNotes(sessionId, notes);
      res.json({ success: true, message: '메모가 저장되었습니다.' });
    } catch (error) {
      console.error('메모 저장 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

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

  // 체크리스트 항목 조회
  getChecklistItems: async (req, res) => {
    try {
      const { category_id, workplace_id } = req.query;
      const items = await PatrolModel.getChecklistItems(category_id, workplace_id);

      // 카테고리별로 그룹화
      const grouped = {};
      items.forEach(item => {
        if (!grouped[item.check_category]) {
          grouped[item.check_category] = [];
        }
        grouped[item.check_category].push(item);
      });

      res.json({ success: true, data: { items, grouped } });
    } catch (error) {
      console.error('체크리스트 항목 조회 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

  // 체크리스트 항목 추가
  createChecklistItem: async (req, res) => {
    try {
      const itemId = await PatrolModel.createChecklistItem(req.body);
      res.json({ success: true, data: { item_id: itemId }, message: '항목이 추가되었습니다.' });
    } catch (error) {
      console.error('항목 추가 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

  // 체크리스트 항목 수정
  updateChecklistItem: async (req, res) => {
    try {
      const { itemId } = req.params;
      await PatrolModel.updateChecklistItem(itemId, req.body);
      res.json({ success: true, message: '항목이 수정되었습니다.' });
    } catch (error) {
      console.error('항목 수정 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

  // 체크리스트 항목 삭제
  deleteChecklistItem: async (req, res) => {
    try {
      const { itemId } = req.params;
      await PatrolModel.deleteChecklistItem(itemId);
      res.json({ success: true, message: '항목이 삭제되었습니다.' });
    } catch (error) {
      console.error('항목 삭제 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

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

  // 작업장별 체크 기록 조회
  getCheckRecords: async (req, res) => {
    try {
      const { sessionId } = req.params;
      const { workplace_id } = req.query;
      const records = await PatrolModel.getCheckRecords(sessionId, workplace_id);
      res.json({ success: true, data: records });
    } catch (error) {
      console.error('체크 기록 조회 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

  // 체크 기록 저장
  saveCheckRecord: async (req, res) => {
    try {
      const { sessionId } = req.params;
      const { workplace_id, check_item_id, is_checked, check_result, note } = req.body;

      if (!workplace_id || !check_item_id) {
        return res.status(400).json({ success: false, message: '필수 정보가 누락되었습니다.' });
      }

      await PatrolModel.saveCheckRecord(sessionId, workplace_id, check_item_id, is_checked, check_result, note);
      res.json({ success: true, message: '저장되었습니다.' });
    } catch (error) {
      console.error('체크 기록 저장 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

  // 체크 기록 일괄 저장
  saveCheckRecords: async (req, res) => {
    try {
      const { sessionId } = req.params;
      const { workplace_id, records } = req.body;

      if (!workplace_id || !records || !Array.isArray(records)) {
        return res.status(400).json({ success: false, message: '필수 정보가 누락되었습니다.' });
      }

      await PatrolModel.saveCheckRecords(sessionId, workplace_id, records);
      res.json({ success: true, message: '저장되었습니다.' });
    } catch (error) {
      console.error('체크 기록 일괄 저장 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

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

  // 작업장 물품 조회
  getWorkplaceItems: async (req, res) => {
    try {
      const { workplaceId } = req.params;
      const { include_inactive } = req.query;
      const items = await PatrolModel.getWorkplaceItems(workplaceId, include_inactive !== 'true');
      res.json({ success: true, data: items });
    } catch (error) {
      console.error('물품 조회 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

  // 물품 추가
  createWorkplaceItem: async (req, res) => {
    try {
      const { workplaceId } = req.params;
      const data = { ...req.body, workplace_id: workplaceId, created_by: req.user.user_id };
      const itemId = await PatrolModel.createWorkplaceItem(data);
      res.json({ success: true, data: { item_id: itemId }, message: '물품이 추가되었습니다.' });
    } catch (error) {
      console.error('물품 추가 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

  // 물품 수정
  updateWorkplaceItem: async (req, res) => {
    try {
      const { itemId } = req.params;
      await PatrolModel.updateWorkplaceItem(itemId, req.body, req.user.user_id);
      res.json({ success: true, message: '물품이 수정되었습니다.' });
    } catch (error) {
      console.error('물품 수정 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

  // 물품 삭제
  deleteWorkplaceItem: async (req, res) => {
    try {
      const { itemId } = req.params;
      const { permanent } = req.query;

      if (permanent === 'true') {
        await PatrolModel.hardDeleteWorkplaceItem(itemId);
      } else {
        await PatrolModel.deleteWorkplaceItem(itemId, req.user.user_id);
      }
      res.json({ success: true, message: '물품이 삭제되었습니다.' });
    } catch (error) {
      console.error('물품 삭제 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

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

  // 물품 유형 목록
  getItemTypes: async (req, res) => {
    try {
      const types = await PatrolModel.getItemTypes();
      res.json({ success: true, data: types });
    } catch (error) {
      console.error('물품 유형 조회 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

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

  // 오늘 순회점검 현황
  getTodayStatus: async (req, res) => {
    try {
      const { category_id } = req.query;
      const status = await PatrolModel.getTodayPatrolStatus(category_id);
      res.json({ success: true, data: status });
    } catch (error) {
      console.error('오늘 현황 조회 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

  // 작업장별 점검 현황
  getWorkplaceCheckStatus: async (req, res) => {
    try {
      const { sessionId } = req.params;
      const status = await PatrolModel.getWorkplaceCheckStatus(sessionId);
      res.json({ success: true, data: status });
    } catch (error) {
      console.error('작업장별 점검 현황 조회 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

  // ==================== 작업장 상세 정보 (통합) ====================

  // 작업장 상세 정보 조회 (시설물, 안전신고, 부적합, 출입, TBM)
  getWorkplaceDetail: async (req, res) => {
    try {
      const { workplaceId } = req.params;
      const { date } = req.query; // 기본: 오늘
      const targetDate = date || new Date().toISOString().slice(0, 10);
      const { getDb } = require('../dbPool');
      const db = await getDb();

      // 1. 작업장 기본 정보 (카테고리 지도 이미지 포함)
      const [workplaceInfo] = await db.query(`
        SELECT w.*, wc.category_name, wc.layout_image as category_layout_image
        FROM workplaces w
        LEFT JOIN workplace_categories wc ON w.category_id = wc.category_id
        WHERE w.workplace_id = ?
      `, [workplaceId]);

      if (!workplaceInfo.length) {
        return res.status(404).json({ success: false, message: '작업장을 찾을 수 없습니다.' });
      }

      // 2. 설비 현황 (해당 작업장 - 원래 위치 또는 현재 위치)
      let equipments = [];
      try {
        const [eqResult] = await db.query(`
          SELECT e.equipment_id, e.equipment_name, e.equipment_code, e.equipment_type,
                 e.status, e.notes, e.workplace_id,
                 e.map_x_percent, e.map_y_percent, e.map_width_percent, e.map_height_percent,
                 e.is_temporarily_moved, e.current_workplace_id,
                 e.current_map_x_percent, e.current_map_y_percent,
                 e.current_map_width_percent, e.current_map_height_percent,
                 e.moved_at,
                 ow.workplace_name as original_workplace_name,
                 cw.workplace_name as current_workplace_name,
                 CASE
                   WHEN e.status IN ('maintenance', 'repair_needed', 'repair_external') THEN 1
                   WHEN e.is_temporarily_moved = 1 THEN 1
                   ELSE 0
                 END as needs_attention
          FROM equipments e
          LEFT JOIN workplaces ow ON e.workplace_id = ow.workplace_id
          LEFT JOIN workplaces cw ON e.current_workplace_id = cw.workplace_id
          WHERE (e.workplace_id = ? OR e.current_workplace_id = ?)
                AND e.status != 'inactive'
          ORDER BY needs_attention DESC, e.equipment_name
        `, [workplaceId, workplaceId]);
        equipments = eqResult;
      } catch (eqError) {
        console.log('설비 조회 스킵 (테이블 없음 또는 오류):', eqError.message);
      }

      // 3. 수리 요청 현황 (미완료) - 테이블 존재 여부 확인 후 조회
      let repairRequests = [];
      try {
        const [repairResult] = await db.query(`
          SELECT er.request_id, er.request_date, er.repair_category, er.description,
                 er.priority, er.status, e.equipment_name, e.equipment_code
          FROM equipment_repair_requests er
          JOIN equipments e ON er.equipment_id = e.equipment_id
          WHERE e.workplace_id = ? AND er.status NOT IN ('completed', 'cancelled')
          ORDER BY
            CASE er.priority WHEN 'emergency' THEN 1 WHEN 'high' THEN 2 WHEN 'normal' THEN 3 ELSE 4 END,
            er.request_date DESC
          LIMIT 10
        `, [workplaceId]);
        repairRequests = repairResult;
      } catch (repairError) {
        console.log('수리요청 조회 스킵 (테이블 없음 또는 오류):', repairError.message);
      }

      // 4. 안전 신고 및 부적합 사항 - 테이블 존재 여부 확인 후 조회
      let workIssues = [];
      try {
        const [issueResult] = await db.query(`
          SELECT wi.report_id, wi.issue_type, wi.title, wi.description,
                 wi.status, wi.severity, wi.created_at, wi.resolved_at,
                 wic.category_name, wic.issue_type as category_type,
                 u.name as reporter_name
          FROM work_issue_reports wi
          LEFT JOIN issue_report_categories wic ON wi.category_id = wic.category_id
          LEFT JOIN users u ON wi.reporter_id = u.user_id
          WHERE wi.workplace_id = ?
            AND wi.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
          ORDER BY wi.created_at DESC
          LIMIT 20
        `, [workplaceId]);
        workIssues = issueResult;
      } catch (issueError) {
        console.log('신고 조회 스킵 (테이블 없음 또는 오류):', issueError.message);
      }

      // 5. 오늘의 출입 기록 (해당 공장 카테고리)
      const categoryId = workplaceInfo[0].category_id;
      let visitRecords = [];
      try {
        const [visitResult] = await db.query(`
          SELECT vr.request_id, vr.visitor_name, vr.visitor_company, vr.visit_purpose,
                 vr.visit_date, vr.visit_time_from, vr.visit_time_to, vr.status,
                 vr.vehicle_number, vr.companion_count,
                 vp.purpose_name, u.name as requester_name
          FROM workplace_visit_requests vr
          LEFT JOIN visit_purpose_types vp ON vr.purpose_id = vp.purpose_id
          LEFT JOIN users u ON vr.requester_id = u.user_id
          WHERE vr.category_id = ? AND vr.visit_date = ? AND vr.status = 'approved'
          ORDER BY vr.visit_time_from
        `, [categoryId, targetDate]);
        visitRecords = visitResult;
      } catch (visitError) {
        console.log('출입기록 조회 스킵 (테이블 없음 또는 오류):', visitError.message);
      }

      // 6. 오늘의 TBM 세션 (해당 공장 카테고리)
      let tbmSessions = [];
      try {
        const [tbmResult] = await db.query(`
          SELECT ts.session_id, ts.session_date, ts.work_location, ts.status,
                 ts.work_content, ts.safety_measures, ts.team_size,
                 t.task_name, wt.name as work_type_name,
                 u.name as leader_name, w.worker_name as leader_worker_name
          FROM tbm_sessions ts
          LEFT JOIN tasks t ON ts.task_id = t.task_id
          LEFT JOIN work_types wt ON t.work_type_id = wt.id
          LEFT JOIN users u ON ts.leader_id = u.user_id
          LEFT JOIN workers w ON ts.leader_worker_id = w.worker_id
          WHERE ts.category_id = ? AND ts.session_date = ?
          ORDER BY ts.created_at DESC
        `, [categoryId, targetDate]);
        tbmSessions = tbmResult;
      } catch (tbmError) {
        console.log('TBM 조회 스킵 (테이블 없음 또는 오류):', tbmError.message);
      }

      // 7. TBM 팀원 정보 (세션별)
      let tbmWithTeams = [];
      try {
        tbmWithTeams = await Promise.all(tbmSessions.map(async (session) => {
          const [team] = await db.query(`
            SELECT tta.assignment_id, w.worker_name, w.occupation,
                   tta.attendance_status, tta.signature_image
            FROM tbm_team_assignments tta
            JOIN workers w ON tta.worker_id = w.worker_id
            WHERE tta.session_id = ?
            ORDER BY w.worker_name
          `, [session.session_id]);
          return { ...session, team };
        }));
      } catch (teamError) {
        console.log('TBM 팀원 조회 스킵:', teamError.message);
        tbmWithTeams = tbmSessions.map(s => ({ ...s, team: [] }));
      }

      // 8. 최근 순회점검 결과 (해당 작업장)
      let recentPatrol = [];
      try {
        const [patrolResult] = await db.query(`
          SELECT ps.session_id, ps.patrol_date, ps.patrol_time, ps.status,
                 ps.notes, u.name as inspector_name,
                 (SELECT COUNT(*) FROM patrol_check_records pcr
                  WHERE pcr.session_id = ps.session_id AND pcr.workplace_id = ?) as checked_count,
                 (SELECT COUNT(*) FROM patrol_check_records pcr
                  WHERE pcr.session_id = ps.session_id AND pcr.workplace_id = ?
                  AND pcr.check_result IN ('warning', 'bad')) as issue_count
          FROM daily_patrol_sessions ps
          LEFT JOIN users u ON ps.inspector_id = u.user_id
          WHERE ps.category_id = ? AND ps.patrol_date >= DATE_SUB(NOW(), INTERVAL 7 DAY)
          ORDER BY ps.patrol_date DESC, ps.patrol_time DESC
          LIMIT 5
        `, [workplaceId, workplaceId, categoryId]);
        recentPatrol = patrolResult;
      } catch (patrolError) {
        console.log('순회점검 조회 스킵 (테이블 없음 또는 오류):', patrolError.message);
      }

      res.json({
        success: true,
        data: {
          workplace: workplaceInfo[0],
          equipments: equipments,
          repairRequests: repairRequests,
          workIssues: {
            safety: workIssues.filter(i => i.category_type === 'safety'),
            nonconformity: workIssues.filter(i => i.category_type === 'nonconformity'),
            all: workIssues
          },
          visitRecords: visitRecords,
          tbmSessions: tbmWithTeams,
          recentPatrol: recentPatrol,
          summary: {
            equipmentCount: equipments.length,
            needsAttention: equipments.filter(e => e.needs_attention).length,
            pendingRepairs: repairRequests.length,
            openIssues: workIssues.filter(i => i.status !== 'closed').length,
            todayVisitors: visitRecords.reduce((sum, v) => sum + 1 + (v.companion_count || 0), 0),
            todayTbmSessions: tbmSessions.length
          }
        }
      });
    } catch (error) {
      console.error('작업장 상세 정보 조회 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

  // ==================== 구역 내 등록 물품/시설물 ====================

  // 구역 내 물품/시설물 목록 조회
  getZoneItems: async (req, res) => {
    try {
      const { workplaceId } = req.params;
      const { getDb } = require('../dbPool');
      const db = await getDb();

      // 테이블이 없으면 생성
      await db.query(`
        CREATE TABLE IF NOT EXISTS workplace_zone_items (
          item_id INT AUTO_INCREMENT PRIMARY KEY,
          workplace_id INT NOT NULL,
          item_name VARCHAR(200) NOT NULL COMMENT '물품/시설물 명칭',
          item_type VARCHAR(50) DEFAULT 'general' COMMENT '유형 (heavy_equipment, hazardous, storage, general 등)',
          description TEXT COMMENT '상세 설명',
          x_percent DECIMAL(5,2) NOT NULL COMMENT '영역 시작 X 좌표 (%)',
          y_percent DECIMAL(5,2) NOT NULL COMMENT '영역 시작 Y 좌표 (%)',
          width_percent DECIMAL(5,2) DEFAULT 10 COMMENT '영역 너비 (%)',
          height_percent DECIMAL(5,2) DEFAULT 10 COMMENT '영역 높이 (%)',
          color VARCHAR(20) DEFAULT '#3b82f6' COMMENT '표시 색상',
          warning_level VARCHAR(20) DEFAULT 'normal' COMMENT '주의 수준 (normal, caution, danger)',
          quantity INT DEFAULT 1 COMMENT '수량',
          unit VARCHAR(20) DEFAULT '개' COMMENT '단위',
          weight_kg DECIMAL(10,2) DEFAULT NULL COMMENT '중량 (kg)',
          is_active BOOLEAN DEFAULT TRUE,
          created_by INT,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          INDEX idx_workplace (workplace_id),
          INDEX idx_type (item_type)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='구역 내 등록 물품/시설물'
      `);

      // 새 컬럼 추가 (없으면)
      try {
        await db.query(`ALTER TABLE workplace_zone_items ADD COLUMN project_type VARCHAR(20) DEFAULT 'non_project'`);
      } catch (e) { /* 이미 존재 */ }
      try {
        await db.query(`ALTER TABLE workplace_zone_items ADD COLUMN project_id INT NULL`);
      } catch (e) { /* 이미 존재 */ }

      const [items] = await db.query(`
        SELECT zi.*, p.project_name
        FROM workplace_zone_items zi
        LEFT JOIN projects p ON zi.project_id = p.project_id
        WHERE zi.workplace_id = ? AND zi.is_active = TRUE
        ORDER BY zi.warning_level DESC, zi.item_name
      `, [workplaceId]);

      // 사진 테이블 존재 확인 및 사진 조회
      try {
        for (const item of items) {
          const [photos] = await db.query(`
            SELECT photo_id, photo_url, created_at
            FROM zone_item_photos
            WHERE item_id = ?
            ORDER BY created_at DESC
          `, [item.item_id]);
          item.photos = photos || [];
        }
      } catch (e) {
        // 사진 테이블이 없으면 무시
        items.forEach(item => item.photos = []);
      }

      res.json({ success: true, data: items });
    } catch (error) {
      console.error('구역 물품 목록 조회 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

  // 구역 현황 등록
  createZoneItem: async (req, res) => {
    try {
      const { workplaceId } = req.params;
      const { item_name, item_type, description, x_percent, y_percent, width_percent, height_percent,
              color, warning_level, project_type, project_id } = req.body;
      const createdBy = req.user?.user_id;
      const { getDb } = require('../dbPool');
      const db = await getDb();

      if (!item_name || x_percent === undefined || y_percent === undefined) {
        return res.status(400).json({ success: false, message: '필수 정보가 누락되었습니다.' });
      }

      // 테이블에 새 컬럼 추가 (없으면)
      try {
        await db.query(`ALTER TABLE workplace_zone_items ADD COLUMN project_type VARCHAR(20) DEFAULT 'non_project'`);
      } catch (e) { /* 이미 존재 */ }
      try {
        await db.query(`ALTER TABLE workplace_zone_items ADD COLUMN project_id INT NULL`);
      } catch (e) { /* 이미 존재 */ }

      const [result] = await db.query(`
        INSERT INTO workplace_zone_items
        (workplace_id, item_name, item_type, description, x_percent, y_percent, width_percent, height_percent,
         color, warning_level, project_type, project_id, created_by)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `, [workplaceId, item_name, item_type || 'working', description, x_percent, y_percent,
          width_percent || 5, height_percent || 5, color || '#3b82f6', warning_level || 'good',
          project_type || 'non_project', project_id || null, createdBy]);

      const newItemId = result.insertId;

      // 등록 이력 저장
      try {
        await db.query(`
          INSERT INTO zone_item_history (item_id, action_type, new_values, changed_by)
          VALUES (?, 'created', ?, ?)
        `, [newItemId, JSON.stringify({ item_name, item_type, warning_level, project_type }), createdBy]);
      } catch (e) { /* 테이블 없으면 무시 */ }

      res.json({
        success: true,
        data: { item_id: newItemId },
        message: '현황이 등록되었습니다.'
      });
    } catch (error) {
      console.error('구역 현황 등록 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

  // 구역 현황 수정
  updateZoneItem: async (req, res) => {
    try {
      const { itemId } = req.params;
      const { item_name, item_type, description, x_percent, y_percent, width_percent, height_percent,
              color, warning_level, project_type, project_id } = req.body;
      const userId = req.user?.user_id;
      const { getDb } = require('../dbPool');
      const db = await getDb();

      // 이력 테이블 생성 (없으면)
      await db.query(`
        CREATE TABLE IF NOT EXISTS zone_item_history (
          history_id INT AUTO_INCREMENT PRIMARY KEY,
          item_id INT NOT NULL,
          action_type VARCHAR(20) NOT NULL COMMENT 'created, updated, deleted',
          changed_fields TEXT COMMENT '변경된 필드 JSON',
          old_values TEXT COMMENT '이전 값 JSON',
          new_values TEXT COMMENT '새 값 JSON',
          changed_by INT,
          changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          INDEX idx_item (item_id),
          INDEX idx_date (changed_at)
        )
      `);

      // 기존 데이터 조회 (이력용)
      const [oldData] = await db.query(`SELECT * FROM workplace_zone_items WHERE item_id = ?`, [itemId]);
      const oldItem = oldData[0];

      // 업데이트
      await db.query(`
        UPDATE workplace_zone_items SET
          item_name = COALESCE(?, item_name),
          item_type = COALESCE(?, item_type),
          description = ?,
          x_percent = COALESCE(?, x_percent),
          y_percent = COALESCE(?, y_percent),
          width_percent = COALESCE(?, width_percent),
          height_percent = COALESCE(?, height_percent),
          color = COALESCE(?, color),
          warning_level = COALESCE(?, warning_level),
          project_type = COALESCE(?, project_type),
          project_id = ?
        WHERE item_id = ?
      `, [item_name, item_type, description, x_percent, y_percent, width_percent, height_percent,
          color, warning_level, project_type, project_id, itemId]);

      // 변경 이력 저장
      if (oldItem) {
        const changedFields = [];
        const oldValues = {};
        const newValues = {};

        const fieldMap = { item_name, item_type, description, warning_level, project_type, project_id };
        for (const [key, newVal] of Object.entries(fieldMap)) {
          if (newVal !== undefined && oldItem[key] !== newVal) {
            changedFields.push(key);
            oldValues[key] = oldItem[key];
            newValues[key] = newVal;
          }
        }

        if (changedFields.length > 0) {
          await db.query(`
            INSERT INTO zone_item_history (item_id, action_type, changed_fields, old_values, new_values, changed_by)
            VALUES (?, 'updated', ?, ?, ?, ?)
          `, [itemId, JSON.stringify(changedFields), JSON.stringify(oldValues), JSON.stringify(newValues), userId]);
        }
      }

      res.json({ success: true, message: '현황이 수정되었습니다.' });
    } catch (error) {
      console.error('구역 현황 수정 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

  // 구역 현황 사진 업로드
  uploadZoneItemPhoto: async (req, res) => {
    try {
      const { item_id } = req.body;
      const { getDb } = require('../dbPool');
      const db = await getDb();

      if (!req.file) {
        return res.status(400).json({ success: false, message: '파일이 없습니다.' });
      }

      // 사진 테이블 생성 (없으면)
      await db.query(`
        CREATE TABLE IF NOT EXISTS zone_item_photos (
          photo_id INT AUTO_INCREMENT PRIMARY KEY,
          item_id INT NOT NULL,
          photo_url VARCHAR(500) NOT NULL,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          INDEX idx_item_id (item_id)
        )
      `);

      const photoUrl = `/uploads/${req.file.filename}`;
      const [result] = await db.query(
        `INSERT INTO zone_item_photos (item_id, photo_url) VALUES (?, ?)`,
        [item_id, photoUrl]
      );

      res.json({
        success: true,
        data: { photo_id: result.insertId, photo_url: photoUrl },
        message: '사진이 업로드되었습니다.'
      });
    } catch (error) {
      console.error('사진 업로드 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

  // 구역 현황 삭제
  deleteZoneItem: async (req, res) => {
    try {
      const { itemId } = req.params;
      const userId = req.user?.user_id;
      const { getDb } = require('../dbPool');
      const db = await getDb();

      // 기존 데이터 조회 (이력용)
      const [oldData] = await db.query(`SELECT * FROM workplace_zone_items WHERE item_id = ?`, [itemId]);
      const oldItem = oldData[0];

      // 소프트 삭제
      await db.query(`UPDATE workplace_zone_items SET is_active = FALSE WHERE item_id = ?`, [itemId]);

      // 삭제 이력 저장
      if (oldItem) {
        await db.query(`
          INSERT INTO zone_item_history (item_id, action_type, old_values, changed_by)
          VALUES (?, 'deleted', ?, ?)
        `, [itemId, JSON.stringify({ item_name: oldItem.item_name, item_type: oldItem.item_type }), userId]);
      }

      res.json({ success: true, message: '현황이 삭제되었습니다.' });
    } catch (error) {
      console.error('구역 현황 삭제 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  },

  // 구역 현황 이력 조회
  getZoneItemHistory: async (req, res) => {
    try {
      const { itemId } = req.params;
      const { getDb } = require('../dbPool');
      const db = await getDb();

      const [history] = await db.query(`
        SELECT h.*, u.full_name as changed_by_name
        FROM zone_item_history h
        LEFT JOIN users u ON h.changed_by = u.user_id
        WHERE h.item_id = ?
        ORDER BY h.changed_at DESC
        LIMIT 50
      `, [itemId]);

      res.json({ success: true, data: history });
    } catch (error) {
      console.error('현황 이력 조회 오류:', error);
      res.status(500).json({ success: false, message: '서버 오류가 발생했습니다.' });
    }
  }
};

module.exports = PatrolController;