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

const express = require('express');
const router = express.Router();
const { pool } = require('../config/db');

// 获取仪表盘统计数据
router.get('/stats', async (req, res) => {
  try {
    // 获取职位统计
    const [jobsStats] = await pool.query(`
      SELECT 
        COUNT(*) AS totalJobs,
        SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS activeJobs,
        SUM(pending_resumes) AS totalPendingResumes,
        SUM(passed_resumes) AS totalPassedResumes
      FROM jobs
      WHERE status != 'deleted'
    `);
    
    // 获取候选人统计
    const [candidatesStats] = await pool.query(`
      SELECT 
        COUNT(*) AS totalCandidates,
        SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pendingCandidates,
        SUM(CASE WHEN status = 'passed' THEN 1 ELSE 0 END) AS passedCandidates,
        SUM(CASE WHEN status = 'rejected' THEN 1 ELSE 0 END) AS rejectedCandidates,
        SUM(CASE WHEN status = 'interviewed' THEN 1 ELSE 0 END) AS interviewedCandidates,
        AVG(match_score) AS averageMatchScore
      FROM candidates
    `);
    
    // 计算通过率
    const passRate = candidatesStats[0].totalCandidates > 0 
      ? Math.round(
          (candidatesStats[0].passedCandidates / candidatesStats[0].totalCandidates) * 100
        )
      : 0;
    
    res.json({
      ...jobsStats[0],
      ...candidatesStats[0],
      passRate,
      averageMatchScore: Math.round(candidatesStats[0].averageMatchScore || 0)
    });
    
  } catch (error) {
    console.error('Get dashboard stats error:', error);
    res.status(500).json({ error: 'Server error' });
  }
});

// 获取最近活动
router.get('/recent-activity', async (req, res) => {
  try {
    // 合并候选人状态变更和面试安排作为最近活动
    const [activities] = await pool.query(`
      (
        SELECT 
          csh.id,
          c.name AS candidate_name,
          j.title AS job_title,
          CONCAT(csh.old_status, ' → ', csh.new_status) AS activity_type,
          'status_change' AS activity_category,
          csh.change_time AS activity_time
        FROM candidate_status_history csh
        JOIN candidates c ON csh.candidate_id = c.id
        JOIN jobs j ON c.job_id = j.id
        ORDER BY csh.change_time DESC
        LIMIT 5
      )
      UNION ALL
      (
        SELECT 
          ci.id,
          c.name AS candidate_name,
          j.title AS job_title,
          CONCAT('面试安排: ', i.title) AS activity_type,
          'interview' AS activity_category,
          ci.interview_time AS activity_time
        FROM candidate_interviews ci
        JOIN candidates c ON ci.candidate_id = c.id
        JOIN jobs j ON c.job_id = j.id
        JOIN interviews i ON ci.interview_id = i.id
        ORDER BY ci.interview_time DESC
        LIMIT 5
      )
      ORDER BY activity_time DESC
      LIMIT 10
    `);
    
    res.json(activities);
  } catch (error) {
    console.error('Get recent activity error:', error);
    res.status(500).json({ error: 'Server error' });
  }
});

module.exports = router;