ai-interview/recruitment-backend/controllers/candidates.js

const db = require('../database/db');
const { successResponse, errorResponse } = require('../utils/apiResponse');

class CandidateController {
  static async getAllCandidates(req, res) {
    try {
      const [rows] = await db.query(`
        SELECT 
          c.id, 
          c.name, 
          c.job_id as jobId, 
          c.job_title as jobTitle, 
          c.match_score as matchScore, 
          c.status, 
          c.highlights, 
          c.concerns, 
          c.summary, 
          c.resume_text as resumeText, 
          c.education, 
          c.experience, 
          c.skills, 
          c.submitted_at as submittedAt, 
          c.reviewed_at as reviewedAt,
          j.title as jobTitle
        FROM candidates c
        LEFT JOIN jobs j ON c.job_id = j.id
      `);
      
      const candidates = rows.map(row => ({
        ...row,
        highlights: row.highlights ? JSON.parse(row.highlights) : [],
        concerns: row.concerns ? JSON.parse(row.concerns) : [],
        skills: row.skills ? JSON.parse(row.skills) : [],
        submittedAt: row.submittedAt,
        reviewedAt: row.reviewedAt || null
      }));
      
      successResponse(res, candidates);
    } catch (err) {
      errorResponse(res, err);
    }
  }

  static async getCandidatesByJob(req, res) {
    try {
      const [rows] = await db.query(`
        SELECT 
          c.id, 
          c.name, 
          c.job_id as jobId, 
          c.job_title as jobTitle, 
          c.match_score as matchScore, 
          c.status, 
          c.highlights, 
          c.concerns, 
          c.summary, 
          c.resume_text as resumeText, 
          c.education, 
          c.experience, 
          c.skills, 
          c.submitted_at as submittedAt, 
          c.reviewed_at as reviewedAt
        FROM candidates c
        WHERE c.job_id = ?
      `, [req.params.jobId]);
      
      const candidates = rows.map(row => ({
        ...row,
        highlights: row.highlights ? JSON.parse(row.highlights) : [],
        concerns: row.concerns ? JSON.parse(row.concerns) : [],
        skills: row.skills ? JSON.parse(row.skills) : [],
        submittedAt: row.submittedAt,
        reviewedAt: row.reviewedAt || null
      }));
      
      successResponse(res, candidates);
    } catch (err) {
      errorResponse(res, err);
    }
  }

  static async getCandidate(req, res) {
    try {
      const [rows] = await db.query(`
        SELECT 
          c.id, 
          c.name, 
          c.job_id as jobId, 
          c.job_title as jobTitle, 
          c.match_score as matchScore, 
          c.status, 
          c.highlights, 
          c.concerns, 
          c.summary, 
          c.resume_text as resumeText, 
          c.education, 
          c.experience, 
          c.skills, 
          c.submitted_at as submittedAt, 
          c.reviewed_at as reviewedAt,
          j.title as jobTitle
        FROM candidates c
        LEFT JOIN jobs j ON c.job_id = j.id
        WHERE c.id = ?
      `, [req.params.id]);
      
      if (rows.length === 0) {
        return errorResponse(res, 'Candidate not found', 404);
      }
      
      const candidate = {
        ...rows[0],
        highlights: rows[0].highlights ? JSON.parse(rows[0].highlights) : [],
        concerns: rows[0].concerns ? JSON.parse(rows[0].concerns) : [],
        skills: rows[0].skills ? JSON.parse(rows[0].skills) : [],
        submittedAt: rows[0].submittedAt,
        reviewedAt: rows[0].reviewedAt || null
      };
      
      successResponse(res, candidate);
    } catch (err) {
      errorResponse(res, err);
    }
  }

  static async createCandidate(req, res) {
    try {
      const { 
        name, 
        jobId, 
        jobTitle, 
        matchScore, 
        status = 'pending', 
        highlights = [], 
        concerns = [], 
        summary = '', 
        resumeText, 
        education, 
        experience, 
        skills = [] 
      } = req.body;
      
      const [result] = await db.query(`
        INSERT INTO candidates (
          id, 
          name, 
          job_id, 
          job_title, 
          match_score, 
          status, 
          highlights, 
          concerns, 
          summary, 
          resume_text, 
          education, 
          experience, 
          skills, 
          submitted_at
        ) VALUES (UUID(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
      `, [
        name, 
        jobId, 
        jobTitle, 
        matchScore, 
        status, 
        JSON.stringify(highlights), 
        JSON.stringify(concerns), 
        summary, 
        resumeText, 
        education, 
        experience, 
        JSON.stringify(skills)
      ]);
      
      const [newCandidate] = await db.query(`
        SELECT * FROM candidates WHERE id = ?
      `, [result.insertId]);
      
      successResponse(res, {
        ...newCandidate[0],
        highlights: newCandidate[0].highlights ? JSON.parse(newCandidate[0].highlights) : [],
        concerns: newCandidate[0].concerns ? JSON.parse(newCandidate[0].concerns) : [],
        skills: newCandidate[0].skills ? JSON.parse(newCandidate[0].skills) : [],
        submittedAt: newCandidate[0].submitted_at,
        reviewedAt: newCandidate[0].reviewed_at || null
      }, 201);
    } catch (err) {
      errorResponse(res, err);
    }
  }

  static async updateCandidate(req, res) {
    try {
      const { 
        name, 
        jobId, 
        jobTitle, 
        matchScore, 
        status, 
        highlights, 
        concerns, 
        summary, 
        resumeText, 
        education, 
        experience, 
        skills 
      } = req.body;
      
      const updates = [];
      const params = [];
      
      if (name) { updates.push('name = ?'); params.push(name); }
      if (jobId) { updates.push('job_id = ?'); params.push(jobId); }
      if (jobTitle) { updates.push('job_title = ?'); params.push(jobTitle); }
      if (matchScore) { updates.push('match_score = ?'); params.push(matchScore); }
      if (status) { updates.push('status = ?'); params.push(status); }
      if (highlights) { updates.push('highlights = ?'); params.push(JSON.stringify(highlights)); }
      if (concerns) { updates.push('concerns = ?'); params.push(JSON.stringify(concerns)); }
      if (summary) { updates.push('summary = ?'); params.push(summary); }
      if (resumeText) { updates.push('resume_text = ?'); params.push(resumeText); }
      if (education) { updates.push('education = ?'); params.push(education); }
      if (experience) { updates.push('experience = ?'); params.push(experience); }
      if (skills) { updates.push('skills = ?'); params.push(JSON.stringify(skills)); }
      
      if (updates.length === 0) {
        return errorResponse(res, 'No valid fields to update', 400);
      }
      
      params.push(req.params.id);
      
      await db.query(`
        UPDATE candidates 
        SET ${updates.join(', ')} 
        WHERE id = ?
      `, params);
      
      const [updatedCandidate] = await db.query(`
        SELECT * FROM candidates WHERE id = ?
      `, [req.params.id]);
      
      if (updatedCandidate.length === 0) {
        return errorResponse(res, 'Candidate not found', 404);
      }
      
      successResponse(res, {
        ...updatedCandidate[0],
        highlights: updatedCandidate[0].highlights ? JSON.parse(updatedCandidate[0].highlights) : [],
        concerns: updatedCandidate[0].concerns ? JSON.parse(updatedCandidate[0].concerns) : [],
        skills: updatedCandidate[0].skills ? JSON.parse(updatedCandidate[0].skills) : [],
        submittedAt: updatedCandidate[0].submitted_at,
        reviewedAt: updatedCandidate[0].reviewed_at || null
      });
    } catch (err) {
      errorResponse(res, err);
    }
  }

 static async updateCandidateStatus(req, res) {
  try {
    const { status } = req.body;
    console.log(`准备更新候选人状态: ID=${req.params.id}, 新状态=${status}`); // 调试
    
    if (!['pending', 'passed', 'rejected', 'interviewed'].includes(status)) {
      return errorResponse(res, 'Invalid status value', 400);
    }

    // 获取当前状态用于记录历史
    const [currentCandidate] = await db.query(`
      SELECT status FROM candidates WHERE id = ?
    `, [req.params.id]);
    
    if (currentCandidate.length === 0) {
      console.log('未找到候选人:', req.params.id); // 调试
      return errorResponse(res, 'Candidate not found', 404);
    }

    const oldStatus = currentCandidate[0].status;
    console.log(`当前状态: ${oldStatus}, 将更新为: ${status}`); // 调试

    // 执行更新
    const [result] = await db.query(`
      UPDATE candidates 
      SET status = ?, reviewed_at = NOW() 
      WHERE id = ?
    `, [status, req.params.id]);
    
    console.log('更新结果:', result); // 调试
    console.log('影响行数:', result.affectedRows); // 调试

    if (result.affectedRows === 0) {
      console.log('更新未影响任何行'); // 调试
      return errorResponse(res, 'No rows updated', 404);
    }

    // 记录状态变更历史
    await db.query(`
      INSERT INTO candidate_status_history (
        candidate_id, old_status, new_status, changed_by, change_reason
      ) VALUES (?, ?, ?, ?, ?)
    `, [
      req.params.id, 
      oldStatus, 
      status, 
      req.user?.username || 'system', 
      req.body.reason || 'Status updated'
    ]);

    // 获取更新后的数据
    const [updatedCandidate] = await db.query(`
      SELECT * FROM candidates WHERE id = ?
    `, [req.params.id]);
    
    console.log('更新后的候选人:', updatedCandidate[0]); // 调试
    
    successResponse(res, {
      ...updatedCandidate[0],
      highlights: JSON.parse(updatedCandidate[0].highlights || '[]'),
      concerns: JSON.parse(updatedCandidate[0].concerns || '[]'),
      skills: JSON.parse(updatedCandidate[0].skills || '[]')
    });
    
  } catch (err) {
    console.error('更新状态出错:', err); // 调试
    errorResponse(res, err);
  }
}

  static async batchScreenCandidates(req, res) {
    try {
      const { candidateIds } = req.body;
      
      if (!Array.isArray(candidateIds) || candidateIds.length === 0) {
        return errorResponse(res, 'Invalid candidate IDs', 400);
      }
      
      // 这里可以添加实际的批量筛选逻辑
      // 例如调用AI服务进行批量评分等
      
      // 模拟批量更新状态
      await db.query(`
        UPDATE candidates 
        SET status = 'passed', reviewed_at = NOW() 
        WHERE id IN (?)
      `, [candidateIds]);
      
      // 获取更新后的候选人列表
      const [updatedCandidates] = await db.query(`
        SELECT * FROM candidates WHERE id IN (?)
      `, [candidateIds]);
      
      const formattedCandidates = updatedCandidates.map(candidate => ({
        ...candidate,
        highlights: candidate.highlights ? JSON.parse(candidate.highlights) : [],
        concerns: candidate.concerns ? JSON.parse(candidate.concerns) : [],
        skills: candidate.skills ? JSON.parse(candidate.skills) : [],
        submittedAt: candidate.submitted_at,
        reviewedAt: candidate.reviewed_at || null
      }));
      
      successResponse(res, formattedCandidates);
    } catch (err) {
      errorResponse(res, err);
    }
  }

  static async deleteCandidate(req, res) {
    try {
      const [result] = await db.query(`
        DELETE FROM candidates WHERE id = ?
      `, [req.params.id]);
      
      if (result.affectedRows === 0) {
        return errorResponse(res, 'Candidate not found', 404);
      }
      
      successResponse(res, { id: req.params.id, deleted: true });
    } catch (err) {
      errorResponse(res, err);
    }
  }

  static async getCandidateSkills(req, res) {
    try {
      const [rows] = await db.query(`
        SELECT 
          s.id, 
          s.name, 
          s.category, 
          cs.proficiency
        FROM candidate_skills cs
        JOIN skills s ON cs.skill_id = s.id
        WHERE cs.candidate_id = ?
      `, [req.params.id]);
      
      successResponse(res, rows);
    } catch (err) {
      errorResponse(res, err);
    }
  }

  static async getCandidateStatusHistory(req, res) {
    try {
      const [rows] = await db.query(`
        SELECT 
          id, 
          old_status as oldStatus, 
          new_status as newStatus, 
          changed_by as changedBy, 
          change_reason as changeReason, 
          change_time as changeTime
        FROM candidate_status_history
        WHERE candidate_id = ?
        ORDER BY change_time DESC
      `, [req.params.id]);
      
      successResponse(res, rows);
    } catch (err) {
      errorResponse(res, err);
    }
  }
}

module.exports = CandidateController;