Web_BLV_OA_Exam_Prod/api/models/examTask.ts

import { v4 as uuidv4 } from 'uuid';
import { get, query, run, all } from '../database';

export interface ExamTask {
  id: string;
  name: string;
  subjectId: string;
  startAt: string;
  endAt: string;
  createdAt: string;
  selectionConfig?: string; // JSON string
}

export interface UserExamTask extends ExamTask {
  subjectName: string;
  totalScore: number;
  timeLimitMinutes: number;
  usedAttempts: number;
  maxAttempts: number;
  bestScore: number;
}

export interface ExamTaskUser {
  id: string;
  taskId: string;
  userId: string;
  createdAt: string;
}

export interface TaskWithSubject extends ExamTask {
  subjectName: string;
  userCount: number;
}

export interface TaskReport {
  taskId: string;
  taskName: string;
  subjectName: string;
  totalUsers: number;
  completedUsers: number;
  averageScore: number;
  topScore: number;
  lowestScore: number;
  details: Array<{
    userId: string;
    userName: string;
    userPhone: string;
    score: number | null;
    completedAt: string | null;
  }>;
}

export interface ActiveTaskStat {
  taskId: string;
  taskName: string;
  subjectName: string;
  totalUsers: number;
  completedUsers: number;
  completionRate: number;
  passRate: number;
  excellentRate: number;
  startAt: string;
  endAt: string;
}

export class ExamTaskModel {
  private static buildActiveTaskStat(input: {
    taskId: string;
    taskName: string;
    subjectName: string;
    totalScore: number;
    startAt: string;
    endAt: string;
    report: TaskReport;
  }): ActiveTaskStat {
    const { report } = input;

    const completionRate =
      report.totalUsers > 0
        ? Math.round((report.completedUsers / report.totalUsers) * 100)
        : 0;

    const passingUsers = report.details.filter((d) => {
      if (d.score === null) return false;
      return d.score / input.totalScore >= 0.6;
    }).length;

    const passRate =
      report.totalUsers > 0 ? Math.round((passingUsers / report.totalUsers) * 100) : 0;

    const excellentUsers = report.details.filter((d) => {
      if (d.score === null) return false;
      return d.score / input.totalScore >= 0.8;
    }).length;

    const excellentRate =
      report.totalUsers > 0
        ? Math.round((excellentUsers / report.totalUsers) * 100)
        : 0;

    return {
      taskId: input.taskId,
      taskName: input.taskName,
      subjectName: input.subjectName,
      totalUsers: report.totalUsers,
      completedUsers: report.completedUsers,
      completionRate,
      passRate,
      excellentRate,
      startAt: input.startAt,
      endAt: input.endAt,
    };
  }

  static async findAll(): Promise<(TaskWithSubject & {
    completedUsers: number;
    passRate: number;
    excellentRate: number;
  })[]> {
    // 1. 先获取所有任务的基本信息
    const baseTasks = await all(`
      SELECT 
        t.id,
        t.name,
        t.subject_id as subjectId,
        t.start_at as startAt,
        t.end_at as endAt,
        t.created_at as createdAt,
        s.name as subjectName,
        COUNT(DISTINCT etu.user_id) as userCount,
        s.total_score as totalScore
      FROM exam_tasks t
      JOIN exam_subjects s ON t.subject_id = s.id
      LEFT JOIN exam_task_users etu ON t.id = etu.task_id
      GROUP BY t.id
      ORDER BY t.created_at DESC
    `);

    // 2. 为每个任务计算完成人数、合格率和优秀率
    const tasksWithStats: any[] = [];
    for (const task of baseTasks) {
      // 获取该任务的详细报表数据
      const report = await this.getReport(task.id);
      
      // 计算合格率（得分率60%以上）
      const passingUsers = report.details.filter((d: any) => {
        if (d.score === null) return false;
        return (d.score / task.totalScore) >= 0.6;
      }).length;
      
      const passRate = report.totalUsers > 0 
        ? Math.round((passingUsers / report.totalUsers) * 100) 
        : 0;
      
      // 计算优秀率（得分率80%以上）
      const excellentUsers = report.details.filter((d: any) => {
        if (d.score === null) return false;
        return (d.score / task.totalScore) >= 0.8;
      }).length;
      
      const excellentRate = report.totalUsers > 0 
        ? Math.round((excellentUsers / report.totalUsers) * 100) 
        : 0;
      
      tasksWithStats.push({
        ...task,
        completedUsers: report.completedUsers,
        passRate,
        excellentRate
      });
    }
    
    return tasksWithStats;
  }

  static async getActiveTasksWithStats(): Promise<ActiveTaskStat[]> {
    const now = new Date().toISOString();
    
    // 1. 获取当前时间有效的任务，包括开始和结束时间
    const activeTasks = await all(`
      SELECT 
        t.id, t.name as taskName, s.name as subjectName, s.total_score as totalScore,
        t.start_at as startAt, t.end_at as endAt
      FROM exam_tasks t
      JOIN exam_subjects s ON t.subject_id = s.id
      WHERE t.start_at <= ? AND t.end_at >= ?
      ORDER BY t.created_at DESC
    `, [now, now]);
    
    const stats: ActiveTaskStat[] = [];
    
    for (const task of activeTasks) {
      // 2. 获取每个任务的详细报告数据
      const report = await this.getReport(task.id);
      
      // 3. 计算完成率
      const completionRate = report.totalUsers > 0 
        ? Math.round((report.completedUsers / report.totalUsers) * 100) 
        : 0;
      
      // 4. 计算合格率（得分率60%以上）
      const passingUsers = report.details.filter(d => {
        if (d.score === null) return false;
        return (d.score / task.totalScore) >= 0.6;
      }).length;
      
      const passRate = report.totalUsers > 0 
        ? Math.round((passingUsers / report.totalUsers) * 100) 
        : 0;
      
      // 5. 计算优秀率（得分率80%以上）
      const excellentUsers = report.details.filter(d => {
        if (d.score === null) return false;
        return (d.score / task.totalScore) >= 0.8;
      }).length;
      
      const excellentRate = report.totalUsers > 0 
        ? Math.round((excellentUsers / report.totalUsers) * 100) 
        : 0;
      
      stats.push({
        taskId: task.id,
        taskName: task.taskName,
        subjectName: task.subjectName,
        totalUsers: report.totalUsers,
        completedUsers: report.completedUsers,
        completionRate,
        passRate,
        excellentRate,
        startAt: task.startAt,
        endAt: task.endAt
      });
    }
    
    return stats;
  }

  static async getHistoryTasksWithStatsPaged(
    page: number,
    limit: number,
  ): Promise<{ data: ActiveTaskStat[]; total: number }> {
    const now = new Date().toISOString();
    const offset = (page - 1) * limit;

    const totalRow = await get(
      `SELECT COUNT(*) as total FROM exam_tasks t WHERE t.end_at < ?`,
      [now],
    );
    const total = Number(totalRow?.total || 0);

    const tasks = await all(
      `
      SELECT 
        t.id, t.name as taskName, s.name as subjectName, s.total_score as totalScore,
        t.start_at as startAt, t.end_at as endAt
      FROM exam_tasks t
      JOIN exam_subjects s ON t.subject_id = s.id
      WHERE t.end_at < ?
      ORDER BY t.end_at DESC
      LIMIT ? OFFSET ?
    `,
      [now, limit, offset],
    );

    const data: ActiveTaskStat[] = [];
    for (const task of tasks) {
      const report = await this.getReport(task.id);
      data.push(
        this.buildActiveTaskStat({
          taskId: task.id,
          taskName: task.taskName,
          subjectName: task.subjectName,
          totalScore: Number(task.totalScore) || 0,
          startAt: task.startAt,
          endAt: task.endAt,
          report,
        }),
      );
    }

    return { data, total };
  }

  static async getUpcomingTasksWithStatsPaged(
    page: number,
    limit: number,
  ): Promise<{ data: ActiveTaskStat[]; total: number }> {
    const now = new Date().toISOString();
    const offset = (page - 1) * limit;

    const totalRow = await get(
      `SELECT COUNT(*) as total FROM exam_tasks t WHERE t.start_at > ?`,
      [now],
    );
    const total = Number(totalRow?.total || 0);

    const tasks = await all(
      `
      SELECT 
        t.id, t.name as taskName, s.name as subjectName, s.total_score as totalScore,
        t.start_at as startAt, t.end_at as endAt
      FROM exam_tasks t
      JOIN exam_subjects s ON t.subject_id = s.id
      WHERE t.start_at > ?
      ORDER BY t.start_at ASC
      LIMIT ? OFFSET ?
    `,
      [now, limit, offset],
    );

    const data: ActiveTaskStat[] = [];
    for (const task of tasks) {
      const report = await this.getReport(task.id);
      data.push(
        this.buildActiveTaskStat({
          taskId: task.id,
          taskName: task.taskName,
          subjectName: task.subjectName,
          totalScore: Number(task.totalScore) || 0,
          startAt: task.startAt,
          endAt: task.endAt,
          report,
        }),
      );
    }

    return { data, total };
  }

  static async getAllTasksWithStatsPaged(
    input: {
      page: number;
      limit: number;
      status?: 'completed' | 'ongoing' | 'notStarted';
      endAtStart?: string;
      endAtEnd?: string;
    },
  ): Promise<{ data: Array<ActiveTaskStat & { status: '已完成' | '进行中' | '未开始' }>; total: number }> {
    const nowIso = new Date().toISOString();
    const nowMs = Date.now();
    const offset = (input.page - 1) * input.limit;

    const whereParts: string[] = [];
    const params: any[] = [];

    if (input.status === 'completed') {
      whereParts.push('t.end_at < ?');
      params.push(nowIso);
    } else if (input.status === 'ongoing') {
      whereParts.push('t.start_at <= ? AND t.end_at >= ?');
      params.push(nowIso, nowIso);
    } else if (input.status === 'notStarted') {
      whereParts.push('t.start_at > ?');
      params.push(nowIso);
    }

    if (input.endAtStart) {
      whereParts.push('t.end_at >= ?');
      params.push(input.endAtStart);
    }
    if (input.endAtEnd) {
      whereParts.push('t.end_at <= ?');
      params.push(input.endAtEnd);
    }

    const whereClause = whereParts.length > 0 ? `WHERE ${whereParts.join(' AND ')}` : '';

    const totalRow = await get(`SELECT COUNT(*) as total FROM exam_tasks t ${whereClause}`, params);
    const total = Number(totalRow?.total || 0);

    const tasks = await all(
      `
      SELECT 
        t.id, t.name as taskName, s.name as subjectName, s.total_score as totalScore,
        t.start_at as startAt, t.end_at as endAt
      FROM exam_tasks t
      JOIN exam_subjects s ON t.subject_id = s.id
      ${whereClause}
      ORDER BY t.end_at DESC
      LIMIT ? OFFSET ?
    `,
      [...params, input.limit, offset],
    );

    const data: Array<ActiveTaskStat & { status: '已完成' | '进行中' | '未开始' }> = [];
    for (const task of tasks) {
      const report = await this.getReport(task.id);
      const stat = this.buildActiveTaskStat({
        taskId: task.id,
        taskName: task.taskName,
        subjectName: task.subjectName,
        totalScore: Number(task.totalScore) || 0,
        startAt: task.startAt,
        endAt: task.endAt,
        report,
      });

      const startMs = new Date(task.startAt).getTime();
      const endMs = new Date(task.endAt).getTime();
      const status: '已完成' | '进行中' | '未开始' =
        Number.isFinite(endMs) && endMs < nowMs
          ? '已完成'
          : Number.isFinite(startMs) && startMs > nowMs
            ? '未开始'
            : '进行中';

      data.push({ ...stat, status });
    }

    return { data, total };
  }

  static async findById(id: string): Promise<ExamTask | null> {
    const sql = `SELECT id, name, subject_id as subjectId, start_at as startAt, end_at as endAt, created_at as createdAt, selection_config as selectionConfig FROM exam_tasks WHERE id = ?`;
    const row = await get(sql, [id]);
    return row || null;
  }

  static async create(data: {
    name: string;
    subjectId: string;
    startAt: string;
    endAt: string;
    userIds: string[];
    selectionConfig?: string;
  }): Promise<ExamTask> {
    if (!data.name.trim()) throw new Error('任务名称不能为空');
    if (!data.userIds.length) throw new Error('至少选择一位用户');

    const subject = await import('./examSubject').then(({ ExamSubjectModel }) => ExamSubjectModel.findById(data.subjectId));
    if (!subject) throw new Error('科目不存在');

    const id = uuidv4();
    const sqlTask = `
      INSERT INTO exam_tasks (id, name, subject_id, start_at, end_at, selection_config)
      VALUES (?, ?, ?, ?, ?, ?)
    `;

    const sqlTaskUser = `
      INSERT INTO exam_task_users (id, task_id, user_id)
      VALUES (?, ?, ?)
    `;

    await run(sqlTask, [id, data.name.trim(), data.subjectId, data.startAt, data.endAt, data.selectionConfig || null]);

    for (const userId of data.userIds) {
      await run(sqlTaskUser, [uuidv4(), id, userId]);
    }

    return (await this.findById(id)) as ExamTask;
  }

  static async update(id: string, data: {
    name: string;
    subjectId: string;
    startAt: string;
    endAt: string;
    userIds: string[];
    selectionConfig?: string;
  }): Promise<ExamTask> {
    const existing = await this.findById(id);
    if (!existing) throw new Error('任务不存在');

    if (!data.name.trim()) throw new Error('任务名称不能为空');
    if (!data.userIds.length) throw new Error('至少选择一位用户');

    const subject = await import('./examSubject').then(({ ExamSubjectModel }) => ExamSubjectModel.findById(data.subjectId));
    if (!subject) throw new Error('科目不存在');

    await run(`UPDATE exam_tasks SET name = ?, subject_id = ?, start_at = ?, end_at = ?, selection_config = ? WHERE id = ?`, [
      data.name.trim(),
      data.subjectId,
      data.startAt,
      data.endAt,
      data.selectionConfig || null,
      id
    ]);

    await run(`DELETE FROM exam_task_users WHERE task_id = ?`, [id]);

    for (const userId of data.userIds) {
      await run(`INSERT INTO exam_task_users (id, task_id, user_id) VALUES (?, ?, ?)`, [uuidv4(), id, userId]);
    }

    return (await this.findById(id)) as ExamTask;
  }

  static async delete(id: string): Promise<void> {
    const existing = await this.findById(id);
    if (!existing) throw new Error('任务不存在');

    await run(`DELETE FROM exam_tasks WHERE id = ?`, [id]);
  }

  static async getTaskUsers(taskId: string): Promise<string[]> {
    const rows = await all(`
      SELECT user_id as userId
      FROM exam_task_users
      WHERE task_id = ?
    `, [taskId]);

    return rows.map(row => row.userId);
  }

  static async getReport(taskId: string): Promise<TaskReport> {
    const task = await this.findById(taskId);
    if (!task) throw new Error('任务不存在');

    const subject = await import('./examSubject').then(({ ExamSubjectModel }) => ExamSubjectModel.findById(task.subjectId));
    if (!subject) throw new Error('科目不存在');

    const sqlUsers = `
      SELECT 
        u.id as userId,
        u.name as userName,
        u.phone as userPhone,
        qr.total_score as score,
        qr.created_at as completedAt
      FROM exam_task_users etu
      JOIN users u ON etu.user_id = u.id
      LEFT JOIN quiz_records qr ON u.id = qr.user_id AND qr.task_id = ?
      WHERE etu.task_id = ?
    `;

    const rows = await query(sqlUsers, [taskId, taskId]);

    const details = rows.map((r) => ({
      userId: r.userId,
      userName: r.userName,
      userPhone: r.userPhone,
      score: r.score !== null ? r.score : null,
      completedAt: r.completedAt || null
    }));

    const completedUsers = details.filter((d) => d.score !== null).length;
    const scores = details.map((d) => d.score).filter((s) => s !== null) as number[];

    return {
      taskId,
      taskName: task.name,
      subjectName: subject.name,
      totalUsers: details.length,
      completedUsers,
      averageScore: scores.length > 0 ? scores.reduce((a, b) => a + b, 0) / scores.length : 0,
      topScore: scores.length > 0 ? Math.max(...scores) : 0,
      lowestScore: scores.length > 0 ? Math.min(...scores) : 0,
      details
    };
  }

  static async generateQuizQuestions(taskId: string, userId: string): Promise<{
    questions: Awaited<ReturnType<typeof import('./question').QuestionModel.getRandomQuestions>>;
    totalScore: number;
    timeLimitMinutes: number;
  }> {
    const task = await this.findById(taskId);
    if (!task) throw new Error('任务不存在');

    const now = new Date();
    if (now < new Date(task.startAt) || now > new Date(task.endAt)) {
      throw new Error('当前时间不在任务有效范围内');
    }

    const isAssigned = await get(
      `SELECT 1 FROM exam_task_users WHERE task_id = ? AND user_id = ?`,
      [taskId, userId]
    );
    if (!isAssigned) throw new Error('用户未被分派到此任务');

    const attemptRow = await get(
      `SELECT COUNT(*) as count FROM quiz_records WHERE user_id = ? AND task_id = ?`,
      [userId, taskId],
    );
    const usedAttempts = Number(attemptRow?.count) || 0;
    if (usedAttempts >= 3) throw new Error('考试次数已用尽');

    const subject = await import('./examSubject').then(({ ExamSubjectModel }) => ExamSubjectModel.findById(task.subjectId));
    if (!subject) throw new Error('科目不存在');

    const { ExamSubjectModel } = await import('./examSubject');
    return await ExamSubjectModel.generateQuizQuestions(subject);
  }

  static async getUserTasks(userId: string): Promise<UserExamTask[]> {
    const now = new Date().toISOString();
    const rows = await all(`
      SELECT 
        t.id,
        t.name,
        t.subject_id as subjectId,
        t.start_at as startAt,
        t.end_at as endAt,
        t.created_at as createdAt,
        s.name as subjectName,
        s.total_score as totalScore,
        s.duration_minutes as timeLimitMinutes,
        COALESCE(q.usedAttempts, 0) as usedAttempts,
        3 as maxAttempts,
        COALESCE(q.bestScore, 0) as bestScore
      FROM exam_tasks t
      INNER JOIN exam_task_users tu ON t.id = tu.task_id
      INNER JOIN exam_subjects s ON t.subject_id = s.id
      LEFT JOIN (
        SELECT task_id, COUNT(*) as usedAttempts, MAX(total_score) as bestScore
        FROM quiz_records
        WHERE user_id = ?
        GROUP BY task_id
      ) q ON q.task_id = t.id
      WHERE tu.user_id = ? AND t.start_at <= ? AND t.end_at >= ?
      ORDER BY t.start_at ASC, t.end_at ASC
    `, [userId, userId, now, now]);

    return rows.map(row => ({
      id: row.id,
      name: row.name,
      subjectId: row.subjectId,
      startAt: row.startAt,
      endAt: row.endAt,
      createdAt: row.createdAt,
      subjectName: row.subjectName,
      totalScore: Number(row.totalScore) || 0,
      timeLimitMinutes: Number(row.timeLimitMinutes) || 0,
      usedAttempts: Number(row.usedAttempts) || 0,
      maxAttempts: Number(row.maxAttempts) || 3,
      bestScore: Number(row.bestScore) || 0,
    }));
  }
}