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

const express = require('express');
const router = express.Router();
const { pool } = require('../config/db');
const crypto = require('crypto');
const { successResponse, errorResponse } = require('../utils/apiResponse');

// 辅助方法：格式化技能数据
function formatSkills(skillsStr) {
  if (!skillsStr) return [];
  
  try {
    // 处理多行JSON对象的情况
    if (skillsStr.trim().startsWith('{')) {
      const skillObjects = skillsStr.split('\n')
        .filter(line => line.trim())
        .map(line => {
          try {
            return JSON.parse(line.trim());
          } catch {
            return null;
          }
        })
        .filter(Boolean);
      
      return skillObjects;
    }
    
    // 处理标准JSON数组
    const parsed = JSON.parse(skillsStr);
    return Array.isArray(parsed) ? parsed : [parsed];
  } catch {
    return [];
  }
}

// 辅助方法：格式化亮点数据
function formatHighlights(highlightsStr) {
  if (!highlightsStr) return [];
  
  try {
    // 处理一个字一行的情况
    if (highlightsStr.includes('\n') && highlightsStr.length < 100) {
      return highlightsStr.split('\n')
        .map(line => line.trim())
        .filter(line => line);
    }
    
    // 处理标准JSON数组
    const parsed = JSON.parse(highlightsStr);
    return Array.isArray(parsed) ? parsed : [parsed];
  } catch {
    return [];
  }
}

// 格式化候选人数据
function formatCandidate(candidate) {
  return {
    id: candidate.id,
    name: candidate.name,
    jobId: candidate.job_id || candidate.jobId,
    jobTitle: candidate.job_title || candidate.jobTitle,
    matchScore: candidate.match_score || candidate.matchScore,
    status: candidate.status,
    highlights: formatHighlights(candidate.highlights),
    concerns: candidate.concerns ? JSON.parse(candidate.concerns) : [],
    summary: candidate.summary,
    resumeText: candidate.resume_text || candidate.resumeText,
    education: candidate.education,
    experience: candidate.experience,
    skills: candidate.skills ? formatSkills(candidate.skills) : [],
    submittedAt: candidate.submitted_at || candidate.submittedAt,
    reviewedAt: candidate.reviewed_at || candidate.reviewedAt || null
  };
}

// 获取所有候选人
router.get('/', async (req, res) => {
  try {
    const { status, jobId } = req.query;
    
    let 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 1=1
    `;
    
    const params = [];
    
    if (status) {
      query += ' AND c.status = ?';
      params.push(status);
    }
    
    if (jobId) {
      query += ' AND c.job_id = ?';
      params.push(jobId);
    }
    
    query += ' ORDER BY c.submitted_at DESC';
    
    const [candidates] = await pool.query(query, params);
    
    // 获取候选人技能（兼容两种方式）
    for (const candidate of candidates) {
      try {
        // 如果skills字段有数据则使用，否则查询关联表
        if (!candidate.skills) {
          const [skills] = await pool.query(`
            SELECT s.name, cs.proficiency 
            FROM candidate_skills cs
            JOIN skills s ON cs.skill_id = s.id
            WHERE cs.candidate_id = ?
          `, [candidate.id]);
          candidate.skills = skills || [];
        }
      } catch (error) {
        console.error(`获取候选人技能错误 ${candidate.id}:`, error);
        candidate.skills = [];
      }
    }
    
    const formattedCandidates = candidates.map(candidate => ({
      ...formatCandidate(candidate),
      // 保留原始技能查询结果
      skills: candidate.skills && candidate.skills.length > 0 && typeof candidate.skills[0] === 'object' 
        ? candidate.skills 
        : formatSkills(candidate.skills)
    }));
    
    successResponse(res, formattedCandidates);
  } catch (error) {
    console.error('获取候选人列表错误:', error);
    errorResponse(res, error);
  }
});

// 获取特定候选人
router.get('/:id', async (req, res) => {
  try {
    const [candidates] = await pool.query(`
      SELECT 
        c.*,
        j.title AS jobTitle
      FROM candidates c
      LEFT JOIN jobs j ON c.job_id = j.id
      WHERE c.id = ?
    `, [req.params.id]);
    
    if (candidates.length === 0) {
      return errorResponse(res, '候选人不存在', 404);
    }
    
    // 获取技能
    const [skills] = await pool.query(`
      SELECT s.name, cs.proficiency 
      FROM candidate_skills cs
      JOIN skills s ON cs.skill_id = s.id
      WHERE cs.candidate_id = ?
    `, [req.params.id]);
    
    const candidateData = formatCandidate(candidates[0]);
    candidateData.skills = skills.length > 0 ? skills : formatSkills(candidates[0].skills);
    
    successResponse(res, candidateData);
  } catch (error) {
    console.error('获取候选人错误:', error);
    errorResponse(res, error);
  }
});

// 创建新候选人
router.post('/', async (req, res) => {
  try {
    const {
      name,
      jobId,
      jobTitle,
      matchScore,
      highlights = [],
      concerns = [],
      summary = '',
      resumeText,
      education,
      experience,
      skills = []
    } = req.body;
    
    const id = crypto.randomUUID();
    const now = new Date();
    
    await pool.query(
      `INSERT INTO candidates (
        id, name, job_id, job_title, match_score, status,
        highlights, concerns, summary, resume_text, education,
        experience, skills, submitted_at
      ) VALUES (?, ?, ?, ?, ?, 'pending', ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        id, name, jobId, jobTitle, matchScore,
        JSON.stringify(highlights),
        JSON.stringify(concerns),
        summary, resumeText, education, experience,
        JSON.stringify(skills),
        now
      ]
    );
    
    // 添加技能关联
    if (skills && skills.length > 0) {
      const skillPromises = skills.map(skill => 
        pool.query(
          `INSERT INTO candidate_skills 
           (candidate_id, skill_id, proficiency)
           VALUES (?, ?, ?)`,
          [id, skill.id, skill.proficiency || 'intermediate']
        )
      );
      await Promise.all(skillPromises);
    }
    
    // 更新职位待筛选简历数
    await pool.query(
      `UPDATE jobs 
       SET pending_resumes = pending_resumes + 1, updated_at = ?
       WHERE id = ?`,
      [now, jobId]
    );
    
    const [newCandidate] = await pool.query(
      'SELECT * FROM candidates WHERE id = ?',
      [id]
    );
    
    const [candidateSkills] = await pool.query(
      `SELECT s.name, cs.proficiency 
       FROM candidate_skills cs
       JOIN skills s ON cs.skill_id = s.id
       WHERE cs.candidate_id = ?`,
      [id]
    );
    
    const responseData = formatCandidate(newCandidate[0]);
    responseData.skills = candidateSkills;
    
    successResponse(res, responseData, 201);
  } catch (error) {
    console.error('创建候选人错误:', error);
    errorResponse(res, error);
  }
});

// 更新候选人
router.put('/:id', async (req, res) => {
    console.log('收到更新请求:', req.params.id, req.body);
  try {
    const { id } = req.params;
    const updates = req.body;
    
    // 获取当前数据
    const [current] = await pool.query(
      'SELECT * FROM candidates WHERE id = ?',
      [id]
    );
    
    if (current.length === 0) {
      return errorResponse(res, '候选人不存在', 404);
    }
    
    const currentData = current[0];
    
    // 构建更新字段
    const fields = [];
    const params = [];
    
    const updateFields = {
      name: updates.name,
      job_id: updates.jobId,
      job_title: updates.jobTitle,
      match_score: updates.matchScore,
      status: updates.status,
      highlights: updates.highlights ? JSON.stringify(updates.highlights) : undefined,
      concerns: updates.concerns ? JSON.stringify(updates.concerns) : undefined,
      summary: updates.summary,
      resume_text: updates.resumeText,
      education: updates.education,
      experience: updates.experience,
      skills: updates.skills ? JSON.stringify(updates.skills) : undefined
    };
    
    Object.entries(updateFields).forEach(([key, value]) => {
      if (value !== undefined && value !== currentData[key]) {
        fields.push(`${key} = ?`);
        params.push(value);
      }
    });
    
    if (fields.length === 0) {
      return errorResponse(res, '没有需要更新的字段', 400);
    }
    
    params.push(id);
    
    await pool.query(
      `UPDATE candidates SET ${fields.join(', ')} WHERE id = ?`,
      params
    );
    
    // 更新技能关联
    if (updates.skills) {
      await pool.query(
        'DELETE FROM candidate_skills WHERE candidate_id = ?',
        [id]
      );
      
      if (updates.skills.length > 0) {
        const skillPromises = updates.skills.map(skill => 
          pool.query(
            `INSERT INTO candidate_skills 
             (candidate_id, skill_id, proficiency)
             VALUES (?, ?, ?)`,
            [id, skill.id, skill.proficiency || 'intermediate']
          )
        );
        await Promise.all(skillPromises);
      }
    }
    
    // 获取更新后的数据
    const [updated] = await pool.query(
      'SELECT * FROM candidates WHERE id = ?',
      [id]
    );
    
    const [skills] = await pool.query(
      `SELECT s.name, cs.proficiency 
       FROM candidate_skills cs
       JOIN skills s ON cs.skill_id = s.id
       WHERE cs.candidate_id = ?`,
      [id]
    );
    
    const responseData = formatCandidate(updated[0]);
    responseData.skills = skills;
    
    successResponse(res, responseData);
  } catch (error) {
    console.error('更新候选人错误:', error);
    errorResponse(res, error);
  }
});

// 更新候选人状态
router.put('/:id/status', async (req, res) => {
  try {
    const { id } = req.params;
    const { status, reason } = req.body;
    const userId = req.user?.id || 'system';
    
    if (!['pending', 'passed', 'rejected', 'interviewed'].includes(status)) {
      return errorResponse(res, '无效的状态值', 400);
    }
    
    // 获取当前状态和职位ID
    const [current] = await pool.query(
      'SELECT status, job_id FROM candidates WHERE id = ?',
      [id]
    );
    
    if (current.length === 0) {
      return errorResponse(res, '候选人不存在', 404);
    }
    
    const oldStatus = current[0].status;
    const jobId = current[0].job_id;
    const now = new Date();
    
    // 更新状态
    await pool.query(
      `UPDATE candidates 
       SET status = ?, reviewed_at = ?
       WHERE id = ?`,
      [status, now, id]
    );
    
    // 记录状态变更历史
    await pool.query(
      `INSERT INTO candidate_status_history (
        id, candidate_id, old_status, new_status, 
        changed_by, change_reason, change_time
      ) VALUES (?, ?, ?, ?, ?, ?, ?)`,
      [crypto.randomUUID(), id, oldStatus, status, userId, reason || '状态更新', now]
    );
    
    // 更新职位统计
    if (status === 'passed' && oldStatus !== 'passed') {
      await pool.query(
        `UPDATE jobs 
         SET pending_resumes = pending_resumes - 1,
             passed_resumes = passed_resumes + 1, 
             updated_at = ?
         WHERE id = ?`,
        [now, jobId]
      );
    } else if (oldStatus === 'passed' && status !== 'passed') {
      await pool.query(
        `UPDATE jobs 
         SET passed_resumes = passed_resumes - 1,
             updated_at = ?
         WHERE id = ?`,
        [now, jobId]
      );
    }
    
    // 返回更新后的候选人数据
    const [updated] = await pool.query(
      'SELECT * FROM candidates WHERE id = ?',
      [id]
    );
    
    const [skills] = await pool.query(
      `SELECT s.name, cs.proficiency 
       FROM candidate_skills cs
       JOIN skills s ON cs.skill_id = s.id
       WHERE cs.candidate_id = ?`,
      [id]
    );
    
    const responseData = formatCandidate(updated[0]);
    responseData.skills = skills;
    
    successResponse(res, responseData);
  } catch (error) {
    console.error('更新候选人状态错误:', error);
    errorResponse(res, error);
  }
});

// 批量筛选候选人
router.post('/batch-screen', async (req, res) => {
  try {
    const { candidateIds } = req.body;
    const userId = req.user?.id || 'system';
    
    if (!Array.isArray(candidateIds) || candidateIds.length === 0) {
      return errorResponse(res, '无效的候选人ID列表', 400);
    }
    
    const now = new Date();
    
    // 获取当前候选人数据
    const [candidates] = await pool.query(
      `SELECT id, status, job_id, match_score 
       FROM candidates 
       WHERE id IN (?)`,
      [candidateIds]
    );
    
    if (candidates.length === 0) {
      return errorResponse(res, '未找到候选人', 404);
    }
    
    // 按职位分组统计
    const jobUpdates = {};
    candidates.forEach(candidate => {
      const newStatus = candidate.match_score >= 75 ? 'passed' : 'rejected';
      
      if (!jobUpdates[candidate.job_id]) {
        jobUpdates[candidate.job_id] = {
          pendingDecrease: 0,
          passedIncrease: 0
        };
      }
      
      jobUpdates[candidate.job_id].pendingDecrease += 1;
      if (newStatus === 'passed') {
        jobUpdates[candidate.job_id].passedIncrease += 1;
      }
    });
    
    // 批量更新候选人状态
    await pool.query(
      `UPDATE candidates 
       SET status = CASE 
         WHEN match_score >= 75 THEN 'passed' 
         ELSE 'rejected' 
       END,
       reviewed_at = ?
       WHERE id IN (?)`,
      [now, candidateIds]
    );
    
    // 记录状态变更历史
    const historyRecords = candidates.map(candidate => {
      const newStatus = candidate.match_score >= 75 ? 'passed' : 'rejected';
      return [
        crypto.randomUUID(),
        candidate.id,
        candidate.status,
        newStatus,
        userId,
        '批量筛选',
        now
      ];
    });
    
    await pool.query(
      `INSERT INTO candidate_status_history (
        id, candidate_id, old_status, new_status, 
        changed_by, change_reason, change_time
      ) VALUES ?`,
      [historyRecords]
    );
    
    // 更新职位统计
    const jobUpdatePromises = Object.entries(jobUpdates).map(([jobId, stats]) => {
      return pool.query(
        `UPDATE jobs 
         SET pending_resumes = pending_resumes - ?,
             passed_resumes = passed_resumes + ?,
             updated_at = ?
         WHERE id = ?`,
        [stats.pendingDecrease, stats.passedIncrease, now, jobId]
      );
    });
    
    await Promise.all(jobUpdatePromises);
    
    // 返回更新后的候选人列表
    const [updatedCandidates] = await pool.query(
      `SELECT * FROM candidates WHERE id IN (?)`,
      [candidateIds]
    );
    
    // 获取所有候选人的技能
    const [allSkills] = await pool.query(
      `SELECT cs.candidate_id, s.name, cs.proficiency 
       FROM candidate_skills cs
       JOIN skills s ON cs.skill_id = s.id
       WHERE cs.candidate_id IN (?)`,
      [candidateIds]
    );
    
    const skillsMap = allSkills.reduce((map, skill) => {
      if (!map[skill.candidate_id]) {
        map[skill.candidate_id] = [];
      }
      map[skill.candidate_id].push({
        name: skill.name,
        proficiency: skill.proficiency
      });
      return map;
    }, {});
    
    const responseData = updatedCandidates.map(candidate => {
      const formatted = formatCandidate(candidate);
      formatted.skills = skillsMap[candidate.id] || formatSkills(candidate.skills);
      return formatted;
    });
    
    successResponse(res, responseData);
  } catch (error) {
    console.error('批量筛选候选人错误:', error);
    errorResponse(res, error);
  }
});

// 删除候选人
router.delete('/:id', async (req, res) => {
  try {
    // 先获取候选人信息以便更新职位统计
    const [candidate] = await pool.query(
      'SELECT job_id, status FROM candidates WHERE id = ?',
      [req.params.id]
    );
    
    if (candidate.length === 0) {
      return errorResponse(res, '候选人不存在', 404);
    }
    
    const jobId = candidate[0].job_id;
    const status = candidate[0].status;
    const now = new Date();
    
    // 删除候选人
    const [result] = await pool.query(
      'DELETE FROM candidates WHERE id = ?',
      [req.params.id]
    );
    
    if (result.affectedRows === 0) {
      return errorResponse(res, '删除候选人失败', 500);
    }
    
    // 更新职位统计
    if (status === 'pending') {
      await pool.query(
        `UPDATE jobs 
         SET pending_resumes = pending_resumes - 1, 
             updated_at = ?
         WHERE id = ?`,
        [now, jobId]
      );
    } else if (status === 'passed') {
      await pool.query(
        `UPDATE jobs 
         SET passed_resumes = passed_resumes - 1, 
             updated_at = ?
         WHERE id = ?`,
        [now, jobId]
      );
    }
    
    successResponse(res, { id: req.params.id, deleted: true });
  } catch (error) {
    console.error('删除候选人错误:', error);
    errorResponse(res, error);
  }
});

// 获取候选人技能
router.get('/:id/skills', async (req, res) => {
  try {
    const [skills] = await pool.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, skills);
  } catch (error) {
    console.error('获取候选人技能错误:', error);
    errorResponse(res, error);
  }
});

// 获取候选人状态历史
router.get('/:id/status-history', async (req, res) => {
  try {
    const [history] = await pool.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, history);
  } catch (error) {
    console.error('获取候选人状态历史错误:', error);
    errorResponse(res, error);
  }
});

module.exports = router;