import { getSystemDbPath } from './path.js';
import { executeWithRetry } from './utils.js';
import { openDatabase } from './utils.js';

/**
 * 查询所有考试
 * @returns {Promise<Array>} 考试列表
 */
export async function getAllExams() {
  const db = await openDatabase(getSystemDbPath());
  return executeWithRetry(db, async () => {
    const sql = 'SELECT * FROM exam ORDER BY created_at DESC';
    return await db.allAsync(sql);
  });
}

/**
 * 根据ID查询考试
 * @param {number} id 考试ID
 * @returns {Promise<Object|null>} 考试数据或null
 */
export async function getExamById(id) {
  const db = await openDatabase(getSystemDbPath());
  return executeWithRetry(db, async () => {
    const sql = 'SELECT * FROM exam WHERE id = ?';
    return await db.getAsync(sql, [id]);
  });
}

/**
 * 查询ID最大的考试记录
 * @returns {Promise<Object|null>} 考试数据或null
 */
export async function getLastExam() {
  const db = await openDatabase(getSystemDbPath());
  return executeWithRetry(db, async () => {
    const sql = 'SELECT * FROM exam ORDER BY id DESC LIMIT 1';
    return await db.getAsync(sql);
  });
}

/**
 * 添加考试
 * @param {Object} examData 考试数据
 * @returns {Promise<Object>} 添加的考试
 */
export async function createExam(examData) {
  const { exam_name, exam_description, exam_examinee_type, exam_notice, exam_minutes, exam_minutes_min } = examData;
  const db = await openDatabase(getSystemDbPath());
  return executeWithRetry(db, async () => {
    const sql = 'INSERT INTO exam (exam_name, exam_description, exam_examinee_type, exam_notice, exam_minutes, exam_minutes_min) VALUES (?, ?, ?, ?, ?, ?)';
    const result = await db.runAsync(sql, [
      exam_name,
      exam_description || '',
      exam_examinee_type,
      exam_notice || '',
      exam_minutes,
      exam_minutes_min || 0
    ]);

    // 检查result是否存在，如果不存在则获取最后插入的ID
    let lastId;
    if (result && result.lastID) {
      lastId = result.lastID;
    } else {
      // 使用另一种方式获取最后插入的ID
      const idResult = await db.getAsync('SELECT last_insert_rowid() as id');
      lastId = idResult ? idResult.id : null;
    }

    if (!lastId) {
      throw new Error('无法获取插入的考试ID');
    }

    return { id: lastId, ...examData };
  });
}

/**
 * 更新考试
 * @param {number} id 考试ID
 * @param {Object} examData 更新的数据
 * @returns {Promise<boolean>} 是否更新成功
 */
export async function updateExam(id, examData) {
  const { exam_name, exam_description, exam_examinee_type, exam_notice, exam_minutes, exam_minutes_min } = examData;
  const db = await openDatabase(getSystemDbPath());
  return executeWithRetry(db, async () => {
    const sql = 'UPDATE exam SET exam_name = ?, exam_description = ?, exam_examinee_type = ?, exam_notice = ?, exam_minutes = ?, exam_minutes_min = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?';
    const result = await db.runAsync(sql, [
      exam_name,
      exam_description || '',
      exam_examinee_type,
      exam_notice || '',
      exam_minutes,
      exam_minutes_min || 0,
      id
    ]);

    // 检查result是否存在以及是否有changes属性
    if (!result || result.changes === undefined) {
      return false;
    }

    return result.changes > 0;
  });
}

/**
 * 删除考试
 * @param {number} id 考试ID
 * @returns {Promise<boolean>} 是否删除成功
 */
export async function deleteExam(id) {
  const db = await openDatabase(getSystemDbPath());
  return executeWithRetry(db, async () => {
    const sql = 'DELETE FROM exam WHERE id = ?';
    const result = await db.runAsync(sql, [id]);

    // 检查result是否存在以及是否有changes属性
    if (!result || result.changes === undefined) {
      return false;
    }

    return result.changes > 0;
  });
}