/**
 * Online Exams Module - Complete Vertical Slice
 * Backend API Controller
 */

const { v4: uuidv4 } = require('uuid');
const express = require('express');
const path = require('path');
const router = express.Router();
const {
  letterFor,
  resolveScale,
  isValidScaleKey,
  loadDefaultScaleKey,
  DEFAULT_SCALE_KEY,
  SCALES
} = require('../utils/gradeScales');
// Admin-or-principal middleware for the exam-review workflow endpoints
// (publish / reject / list-pending). Reuses the shared helper so the
// RBAC pattern matches other controllers.
const { requireAdmin } = require('../middleware/requireRole');
// Notification helper: writes a row to the notifications table on
// reject so the teacher sees the reason in their inbox. There is no
// real email/SMS provider wired in yet — the service is a no-op
// stub for those channels.
const NotificationService = require('../services/NotificationService');

/**
 * Read the school-wide default grading scale. Falls back to the
 * resolver's DEFAULT_SCALE_KEY if the row is missing or invalid.
 */
const readDefaultScale = () => loadDefaultScaleKey(db);

/**
 * Decorate an exam_groups row with the resolved scale (key + label).
 * The full band letter is computed at attempt-submit time because it
 * needs the percentage; here we only expose the *scale* the exam will
 * use when grading.
 */
const decorateGroup = (group) => {
  if (!group) return group;
  const resolved = resolveScale({
    rowScale: group.grading_scale,
    defaultScale: readDefaultScale()
  });
  return {
    ...group,
    resolved_scale: resolved.key,
    resolved_scale_label: SCALES[resolved.key]?.label || resolved.key,
    is_default_scale: !group.grading_scale,
  };
};

const dbPath = process.env.DB_PATH || path.join(__dirname, '../../data/school.db');
const Database = require('better-sqlite3');
const db = new Database(dbPath);
db.pragma('foreign_keys = ON');

// Auth middleware
const auth = (req, res, next) => {
  const token = req.headers.authorization?.split(' ')[1];
  if (!token) return res.status(401).json({ error: 'No token provided' });
  try {
    req.user = require('jsonwebtoken').verify(token, require('../config').jwtSecret);
    next();
  } catch (err) {
    res.status(401).json({ error: 'Invalid token' });
  }
};

// Helper to set sync status
const setSyncPending = (table, id) => {
  try {
    db.prepare(`UPDATE ${table} SET sync_status = 'pending' WHERE id = ?`).run(id);
  } catch (e) {}
};

// Record an audit row for the exam-review workflow. The audit table
// is the source of truth for "who did what when" on a group — the
// reviewed_at / reviewed_by columns on exam_groups are the most-recent
// snapshot, but this is the full history.
const recordAudit = (examGroupId, scheduleId, action, actorId, reason) => {
  try {
    db.prepare(`
      INSERT INTO exam_review_audit
        (uid, exam_group_id, schedule_id, action, actor_id, reason, sync_status)
      VALUES (?, ?, ?, ?, ?, ?, 'pending')
    `).run(require('uuid').v4(), examGroupId, scheduleId || null, action, actorId, reason || null);
  } catch (e) {
    // Audit insert failure should not block the user's action — log
    // and continue. The reviewed_at/by on the group is the user-visible
    // record; this is the deep history.
    console.warn('[exams.audit] failed to record:', e.message);
  }
};

// ============ EXAM GROUPS ============

router.get('/', auth, (req, res) => {
  if (req.user.role === 'parent') {
    return res.status(403).json({ error: 'Parents cannot access online exams module' });
  }
  const groups = db.prepare('SELECT * FROM exam_groups WHERE is_deleted = 0').all();
  res.json(groups);
});

/**
 * @openapi
 * /api/exams/groups:
 *   get:
 *     summary: List exam groups
 *     description: |
 *       Returns active exam groups joined with counts of
 *       questions and schedules. Optional filters: `subject_id`
 *       (groups that have questions for the subject), `exam_type`,
 *       and `academic_year`. The implied role filter in SQL is
 *       permissive — any authenticated caller may read; gate
 *       upstream for non-staff access if needed.
 *     tags:
 *       - Exams
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: subject_id
 *         schema: { type: integer }
 *       - in: query
 *         name: exam_type
 *         schema: { type: string }
 *       - in: query
 *         name: academic_year
 *         schema: { type: string }
 *     responses:
 *       200:
 *         description: Exam group list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *   post:
 *     summary: Create exam group
 *     description: |
 *       Creates a new exam group (template). Default values are
 *       applied when omitted: duration 60 minutes, total marks
 *       100, passing marks 50, and max attempts 1.
 *     tags:
 *       - Exams
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               name: { type: string }
 *               description: { type: string }
 *               exam_type: { type: string }
 *               duration_minutes: { type: integer }
 *               total_marks: { type: integer }
 *               passing_marks: { type: integer }
 *               is_random_order: { type: boolean }
 *               show_results: { type: boolean }
 *               allow_review: { type: boolean }
 *               max_attempts: { type: integer }
 *               academic_year: { type: string }
 *               term: { type: string }
 *     responses:
 *       201:
 *         description: Created exam group
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 */
router.get('/groups', auth, (req, res) => {
  const { subject_id, exam_type, academic_year } = req.query;

  let query = `
    SELECT eg.*,
      (SELECT COUNT(*) FROM question_banks WHERE exam_group_id = eg.id AND is_deleted = 0) as question_count,
      (SELECT COUNT(*) FROM exam_schedules WHERE exam_group_id = eg.id AND is_deleted = 0) as schedule_count
    FROM exam_groups eg
    WHERE eg.is_deleted = 0
  `;
  const params = [];

  if (subject_id) { query += ' AND eg.id IN (SELECT exam_group_id FROM question_banks WHERE subject_id = ?)'; params.push(subject_id); }
  if (exam_type) { query += ' AND eg.exam_type = ?'; params.push(exam_type); }
  if (academic_year) { query += ' AND eg.academic_year = ?'; params.push(academic_year); }

  query += ' ORDER BY eg.created_at DESC';

  const groups = db.prepare(query).all(...params);
  const defaultScale = readDefaultScale();
  res.json(groups.map((g) => decorateGroup(g, defaultScale)));
});

/**
 * @openapi
 * /api/exams/groups/{id}:
 *   get:
 *     summary: Get exam group by id
 *     description: |
 *       Returns a single exam group with its question bank
 *       (ordered by difficulty) and any exam schedules joined
 *       with class and subject names.
 *     tags:
 *       - Exams
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Exam group detail
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       404: { description: Exam group not found }
 *   put:
 *     summary: Update exam group
 *     description: |
 *       Updates the mutable fields of an exam group. Unspecified
 *       fields are left unchanged (`COALESCE`).
 *     tags:
 *       - Exams
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               name: { type: string }
 *               description: { type: string }
 *               exam_type: { type: string }
 *               duration_minutes: { type: integer }
 *               total_marks: { type: integer }
 *               passing_marks: { type: integer }
 *               is_random_order: { type: boolean }
 *               show_results: { type: boolean }
 *               allow_review: { type: boolean }
 *               max_attempts: { type: integer }
 *     responses:
 *       200:
 *         description: Updated exam group
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *   delete:
 *     summary: Soft-delete exam group
 *     description: |
 *       Marks the exam group as deleted. Schedules, questions,
 *       and attempts referencing it remain in place; callers
 *       should remove or migrate those first when relevant.
 *     tags:
 *       - Exams
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Deleted
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 */
// NOTE: must be declared before /groups/:id so the literal path
// 'pending' isn't matched as an id parameter.
router.get('/groups/pending', auth, requireAdmin, (req, res) => {
  const groups = db.prepare(`
    SELECT eg.*,
      (SELECT COUNT(*) FROM question_banks WHERE exam_group_id = eg.id AND is_deleted = 0) as question_count
    FROM exam_groups eg
    WHERE eg.status = 'pending_review' AND eg.is_deleted = 0
    ORDER BY eg.created_at ASC
  `).all();
  res.json(groups);
});

router.get('/groups/:id', auth, (req, res) => {
  const group = db.prepare(`
    SELECT eg.*,
      (SELECT COUNT(*) FROM question_banks WHERE exam_group_id = eg.id AND is_deleted = 0) as question_count
    FROM exam_groups eg
    WHERE eg.id = ? AND eg.is_deleted = 0
  `).get(req.params.id);

  if (!group) return res.status(404).json({ error: 'Exam group not found' });

  // Get questions
  const questions = db.prepare(`
    SELECT * FROM question_banks
    WHERE exam_group_id = ? AND is_deleted = 0
    ORDER BY difficulty, id
  `).all(req.params.id);

  // Get schedules
  const schedules = db.prepare(`
    SELECT es.*, c.name as class_name, s.name as subject_name
    FROM exam_schedules es
    LEFT JOIN classes c ON es.class_id = c.id
    LEFT JOIN subjects s ON es.subject_id = s.id
    WHERE es.exam_group_id = ? AND es.is_deleted = 0
    ORDER BY es.start_time
  `).all(req.params.id);

  res.json({ ...decorateGroup(group, readDefaultScale()), questions, schedules });
});

router.post('/groups', auth, (req, res) => {
  const { name, description, exam_type, duration_minutes, total_marks, passing_marks, is_random_order, show_results, allow_review, max_attempts, academic_year, term, grading_scale } = req.body;

  if (grading_scale != null && !isValidScaleKey(grading_scale)) {
    return res.status(400).json({ error: `Invalid grading_scale: ${grading_scale}` });
  }

  const uid = uuidv4();

  try {
    // status='draft' so a teacher-created group doesn't immediately
    // surface in the admin review queue. Teachers must call
    // POST /api/exams/groups/:id/submit to push it to pending_review.
    // Admins who create a group directly can skip the submit step
    // (handled by an explicit override below).
    const initialStatus =
      req.user && (req.user.role === 'school_admin' || req.user.role === 'systems_admin' || req.user.role === 'principal')
        ? 'approved'
        : 'draft';

    const result = db.prepare(`
      INSERT INTO exam_groups (uid, name, description, exam_type, duration_minutes, total_marks, passing_marks, is_random_order, show_results, allow_review, max_attempts, academic_year, term, grading_scale, status)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(uid, name, description, exam_type, duration_minutes || 60, total_marks || 100, passing_marks || 50, is_random_order ? 1 : 0, show_results ? 1 : 0, allow_review ? 1 : 0, max_attempts || 1, academic_year, term, grading_scale || null, initialStatus);

    const group = db.prepare('SELECT * FROM exam_groups WHERE id = ?').get(result.lastInsertRowid);
    setSyncPending('exam_groups', result.lastInsertRowid);
    res.status(201).json(decorateGroup(group, readDefaultScale()));

  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

router.put('/groups/:id', auth, (req, res) => {
  const { name, description, exam_type, duration_minutes, total_marks, passing_marks, is_random_order, show_results, allow_review, max_attempts, grading_scale } = req.body;

  // grading_scale: undefined → leave unchanged; null → clear; string → validate+set
  let scaleSql = '';
  const scaleParams = [];
  if (grading_scale !== undefined) {
    if (grading_scale !== null && !isValidScaleKey(grading_scale)) {
      return res.status(400).json({ error: `Invalid grading_scale: ${grading_scale}` });
    }
    scaleSql = 'grading_scale = ?,';
    scaleParams.push(grading_scale);
  }

  try {
    db.prepare(`
      UPDATE exam_groups SET
        name = COALESCE(?, name),
        description = COALESCE(?, description),
        exam_type = COALESCE(?, exam_type),
        duration_minutes = COALESCE(?, duration_minutes),
        total_marks = COALESCE(?, total_marks),
        passing_marks = COALESCE(?, passing_marks),
        is_random_order = COALESCE(?, is_random_order),
        show_results = COALESCE(?, show_results),
        allow_review = COALESCE(?, allow_review),
        max_attempts = COALESCE(?, max_attempts),
        ${scaleSql}
        updated_at = CURRENT_TIMESTAMP,
        sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(name, description, exam_type, duration_minutes, total_marks, passing_marks, is_random_order, show_results, allow_review, max_attempts, ...scaleParams, req.params.id);

    const group = db.prepare('SELECT * FROM exam_groups WHERE id = ?').get(req.params.id);
    res.json(decorateGroup(group, readDefaultScale()));

  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

router.delete('/groups/:id', auth, (req, res) => {
  db.prepare(`
    UPDATE exam_groups SET is_deleted = 1, sync_status = 'pending', updated_at = CURRENT_TIMESTAMP
    WHERE id = ?
  `).run(req.params.id);
  res.json({ success: true });
});

// ============ EXAM REVIEW WORKFLOW ============
// Teacher submits a draft (or previously-rejected) group for admin
// review. Rejection reason is cleared so a stale reason from a prior
// review doesn't linger.
router.post('/groups/:id/submit', auth, (req, res) => {
  const group = db.prepare(
    'SELECT id, status FROM exam_groups WHERE id = ? AND is_deleted = 0'
  ).get(req.params.id);
  if (!group) return res.status(404).json({ error: 'Exam group not found' });
  if (group.status === 'pending_review') {
    return res.status(409).json({ error: 'Already pending review' });
  }
  if (group.status === 'approved') {
    return res.status(409).json({ error: 'Already approved' });
  }
  db.prepare(`
    UPDATE exam_groups
    SET status = 'pending_review',
        rejection_reason = NULL,
        reviewed_at = NULL,
        reviewed_by = NULL,
        sync_status = 'pending',
        updated_at = CURRENT_TIMESTAMP
    WHERE id = ?
  `).run(req.params.id);
  setSyncPending('exam_groups', req.params.id);
  recordAudit(req.params.id, null, 'submit', req.user.id, null);
  const updated = db.prepare('SELECT * FROM exam_groups WHERE id = ?').get(req.params.id);
  res.json(decorateGroup(updated, readDefaultScale()));
});

// Admin or principal rejects a pending group. Saves the reason for the
// teacher to see; sets the reviewer + timestamp.
router.post('/groups/:id/reject', auth, requireAdmin, (req, res) => {
  const reason = (req.body && typeof req.body.reason === 'string') ? req.body.reason.trim() : '';
  if (!reason) {
    return res.status(400).json({ error: 'rejection reason is required' });
  }
  const group = db.prepare(
    'SELECT id, status FROM exam_groups WHERE id = ? AND is_deleted = 0'
  ).get(req.params.id);
  if (!group) return res.status(404).json({ error: 'Exam group not found' });
  if (group.status === 'approved') {
    return res.status(409).json({ error: 'Cannot reject an already-approved group' });
  }
  db.prepare(`
    UPDATE exam_groups
    SET status = 'rejected',
        rejection_reason = ?,
        reviewed_at = CURRENT_TIMESTAMP,
        reviewed_by = ?,
        sync_status = 'pending',
        updated_at = CURRENT_TIMESTAMP
    WHERE id = ?
  `).run(reason, req.user.id, req.params.id);
  setSyncPending('exam_groups', req.params.id);
  recordAudit(req.params.id, null, 'reject', req.user.id, reason);
  // Fetch the updated row first so we can use its name in the
  // notification subject without re-querying.
  const updated = db.prepare('SELECT * FROM exam_groups WHERE id = ?').get(req.params.id);
  // Fire the teacher notification. The most-recent 'submit' audit row
  // is the teacher who actually submitted (handles re-submits correctly).
  // If no submit row exists, we skip — the audit will still show the
  // rejection in the group's history.
  const submitter = db.prepare(`
    SELECT actor_id FROM exam_review_audit
    WHERE exam_group_id = ? AND action = 'submit' AND is_deleted = 0
    ORDER BY created_at DESC, id DESC LIMIT 1
  `).get(req.params.id);
  if (submitter && submitter.actor_id) {
    NotificationService.send(db, {
      recipientId: submitter.actor_id,
      type: 'in_app',
      subject: `Exam "${updated.name}" was rejected`,
      body: reason,
      relatedResource: `exam_group:${req.params.id}`,
      metadata: { groupId: req.params.id, rejectedBy: req.user.id },
    });
  }
  res.json(decorateGroup(updated, readDefaultScale()));
});

// Any authenticated user fetches their own notifications (the inbox
// they would see in a notification bell). Newest first. ?unread=true
// filters to status != 'read'.
router.get('/notifications', auth, (req, res) => {
  const unreadOnly = req.query.unread === 'true';
  const where = unreadOnly
    ? "recipient_id = ? AND is_deleted = 0 AND status != 'read'"
    : "recipient_id = ? AND is_deleted = 0";
  const rows = db.prepare(`
    SELECT id, type, subject, body, related_resource, status,
           sent_at, read_at, created_at
    FROM notifications
    WHERE ${where}
    ORDER BY created_at DESC, id DESC
    LIMIT 200
  `).all(req.user.id);
  res.json(rows);
});

// Admin or principal fetches the full audit history for a group
// (who submitted/rejected/published and when). Returned newest-first.
router.get('/groups/:id/audit', auth, requireAdmin, (req, res) => {
  const group = db.prepare(
    'SELECT id FROM exam_groups WHERE id = ? AND is_deleted = 0'
  ).get(req.params.id);
  if (!group) return res.status(404).json({ error: 'Exam group not found' });
  const rows = db.prepare(`
    SELECT a.id, a.exam_group_id, a.schedule_id, a.action, a.actor_id, a.reason,
           a.metadata, a.created_at,
           u.first_name, u.last_name, u.email, u.role
    FROM exam_review_audit a
    LEFT JOIN users u ON u.id = a.actor_id
    WHERE a.exam_group_id = ? AND a.is_deleted = 0
    ORDER BY a.created_at DESC, a.id DESC
  `).all(req.params.id);
  res.json(rows);
});

// Admin or principal rejects many groups at once with a single reason.
// Each group is processed in its own transaction-equivalent block —
// failures on one group don't abort the rest.
//
// Response shape:
//   { succeeded: number[],
//     failed:    [{ id, reason }] }
//
// 200 even if some fail (partial success). 400 if the request itself
// is invalid (empty list, missing reason, non-array ids). 403 if the
// caller isn't admin/principal.
router.post('/groups/bulk-reject', auth, requireAdmin, (req, res) => {
  const reason = (req.body && typeof req.body.reason === 'string') ? req.body.reason.trim() : '';
  const groupIds = (req.body && Array.isArray(req.body.groupIds)) ? req.body.groupIds : null;

  if (!reason) {
    return res.status(400).json({ error: 'rejection reason is required' });
  }
  if (!groupIds || groupIds.length === 0) {
    return res.status(400).json({ error: 'groupIds must be a non-empty array' });
  }
  // Defensive cap: a single admin request shouldn't reject 10k groups
  // in one shot. 100 is generous for a manual queue-clearing action.
  if (groupIds.length > 100) {
    return res.status(400).json({ error: 'groupIds capped at 100 per request' });
  }
  // Coerce all ids to integers — guards against ['1; DROP TABLE...']
  // style payloads.
  const safeIds = groupIds.map(id => parseInt(id, 10)).filter(n => Number.isFinite(n) && n > 0);
  if (safeIds.length === 0) {
    return res.status(400).json({ error: 'no valid group ids' });
  }

  const succeeded = [];
  const failed = [];

  // Pre-load all the groups we're touching — one SELECT, then per-id
  // UPDATE. Avoids the N+1 query problem on the hot path.
  const placeholders = safeIds.map(() => '?').join(',');
  const groups = db.prepare(
    `SELECT id, status FROM exam_groups WHERE id IN (${placeholders}) AND is_deleted = 0`
  ).all(...safeIds);
  const byId = new Map(groups.map(g => [g.id, g]));

  for (const id of safeIds) {
    const g = byId.get(id);
    if (!g) {
      failed.push({ id, reason: 'Not found' });
      continue;
    }
    if (g.status === 'approved') {
      failed.push({ id, reason: 'Cannot reject an already-approved group' });
      continue;
    }
    try {
      db.prepare(`
        UPDATE exam_groups
        SET status = 'rejected',
            rejection_reason = ?,
            reviewed_at = CURRENT_TIMESTAMP,
            reviewed_by = ?,
            sync_status = 'pending',
            updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
      `).run(reason, req.user.id, id);
      setSyncPending('exam_groups', id);
      recordAudit(id, null, 'reject', req.user.id, reason);
      succeeded.push(id);
    } catch (e) {
      failed.push({ id, reason: e.message });
    }
  }

  res.json({ succeeded, failed });
});

// ============ QUESTION BANKS ============

/**
 * @openapi
 * /api/exams/questions:
 *   get:
 *     summary: List questions
 *     description: |
 *       Returns questions from the question bank. Optional
 *       filters: `exam_group_id`, `subject_id`, `difficulty`
 *       (e.g. `easy`, `medium`, `hard`).
 *     tags:
 *       - Exams
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: exam_group_id
 *         schema: { type: integer }
 *       - in: query
 *         name: subject_id
 *         schema: { type: integer }
 *       - in: query
 *         name: difficulty
 *         schema: { type: string }
 *     responses:
 *       200:
 *         description: Question list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *   post:
 *     summary: Create question
 *     description: |
 *       Creates a single question in the bank. `options` is
 *       stored as JSON when provided. Default difficulty is
 *       `medium` and default marks is `1`.
 *     tags:
 *       - Exams
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               exam_group_id: { type: integer }
 *               subject_id: { type: integer }
 *               question_type: { type: string }
 *               question: { type: string }
 *               options:
 *                 type: array
 *                 items: { type: string }
 *               correct_answer: { type: string }
 *               marks: { type: integer }
 *               difficulty: { type: string }
 *               tags: { type: string }
 *     responses:
 *       201:
 *         description: Created question
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 */
router.get('/questions', auth, (req, res) => {
  const { exam_group_id, subject_id, difficulty } = req.query;
  
  let query = 'SELECT * FROM question_banks WHERE is_deleted = 0';
  const params = [];
  
  if (exam_group_id) { query += ' AND exam_group_id = ?'; params.push(exam_group_id); }
  if (subject_id) { query += ' AND subject_id = ?'; params.push(subject_id); }
  if (difficulty) { query += ' AND difficulty = ?'; params.push(difficulty); }
  
  query += ' ORDER BY created_at DESC';
  
  const questions = db.prepare(query).all(...params);
  res.json(questions);
});

router.post('/questions', auth, (req, res) => {
  const { exam_group_id, subject_id, question_type, question, options, correct_answer, marks, difficulty, tags } = req.body;
  
  const uid = uuidv4();
  const optionsJson = options ? JSON.stringify(options) : null;
  
  try {
    const result = db.prepare(`
      INSERT INTO question_banks (uid, exam_group_id, subject_id, question_type, question, options, correct_answer, marks, difficulty, tags)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(uid, exam_group_id, subject_id, question_type, question, optionsJson, correct_answer, marks || 1, difficulty || 'medium', tags);
    
    const q = db.prepare('SELECT * FROM question_banks WHERE id = ?').get(result.lastInsertRowid);
    res.status(201).json(q);
    
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/exams/questions/bulk:
 *   post:
 *     summary: Bulk create questions
 *     description: |
 *       Inserts many questions in a single transaction and
 *       returns the assigned ids and uids.
 *     tags:
 *       - Exams
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - questions
 *             properties:
 *               questions:
 *                 type: array
 *                 items:
 *                   type: object
 *                   properties:
 *                     exam_group_id: { type: integer }
 *                     subject_id: { type: integer }
 *                     question_type: { type: string }
 *                     question: { type: string }
 *                     options:
 *                       type: array
 *                       items: { type: string }
 *                     correct_answer: { type: string }
 *                     marks: { type: integer }
 *                     difficulty: { type: string }
 *                     tags: { type: string }
 *     responses:
 *       201:
 *         description: Per-row outcomes
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 created: { type: integer }
 *                 questions:
 *                   type: array
 *                   items: { type: object }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 */
router.post('/questions/bulk', auth, (req, res) => {
  const { questions } = req.body;
  
  if (!Array.isArray(questions) || questions.length === 0) {
    return res.status(400).json({ error: 'Questions array required' });
  }
  
  const insert = db.prepare(`
    INSERT INTO question_banks (uid, exam_group_id, subject_id, question_type, question, options, correct_answer, marks, difficulty, tags)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `);
  
  const insertMany = db.transaction((questions) => {
    const inserted = [];
    for (const q of questions) {
      const uid = uuidv4();
      const optionsJson = q.options ? JSON.stringify(q.options) : null;
      insert.run(uid, q.exam_group_id, q.subject_id, q.question_type, q.question, optionsJson, q.correct_answer, q.marks || 1, q.difficulty || 'medium', q.tags);
      inserted.push({ uid, id: insert.lastInsertRowid });
    }
    return inserted;
  });
  
  try {
    const result = insertMany(questions);
    res.status(201).json({ created: result.length, questions: result });
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/exams/questions/{id}:
 *   put:
 *     summary: Update question
 *     description: |
 *       Updates the mutable fields of a question. `options` is
 *       re-serialized to JSON when provided.
 *     tags:
 *       - Exams
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               question: { type: string }
 *               options:
 *                 type: array
 *                 items: { type: string }
 *               correct_answer: { type: string }
 *               marks: { type: integer }
 *               difficulty: { type: string }
 *               tags: { type: string }
 *     responses:
 *       200:
 *         description: Updated question
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *   delete:
 *     summary: Soft-delete question
 *     description: Marks the question as deleted.
 *     tags:
 *       - Exams
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Deleted
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 */
router.put('/questions/:id', auth, (req, res) => {
  const { question, options, correct_answer, marks, difficulty, tags } = req.body;
  const optionsJson = options ? JSON.stringify(options) : null;
  
  try {
    db.prepare(`
      UPDATE question_banks SET
        question = COALESCE(?, question),
        options = COALESCE(?, options),
        correct_answer = COALESCE(?, correct_answer),
        marks = COALESCE(?, marks),
        difficulty = COALESCE(?, difficulty),
        tags = COALESCE(?, tags),
        updated_at = CURRENT_TIMESTAMP,
        sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(question, optionsJson, correct_answer, marks, difficulty, tags, req.params.id);
    
    const q = db.prepare('SELECT * FROM question_banks WHERE id = ?').get(req.params.id);
    res.json(q);
    
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

router.delete('/questions/:id', auth, (req, res) => {
  db.prepare(`
    UPDATE question_banks SET is_deleted = 1, sync_status = 'pending', updated_at = CURRENT_TIMESTAMP
    WHERE id = ?
  `).run(req.params.id);
  res.json({ success: true });
});

// ============ EXAM SCHEDULES ============

/**
 * @openapi
 * /api/exams/schedules:
 *   get:
 *     summary: List exam schedules
 *     description: |
 *       Returns active exam schedules joined with the exam group
 *       name and type, duration, class name, and subject name.
 *       Optional filters: `class_id`, `exam_group_id`, and
 *       `is_active` (`true` / `false`).
 *     tags:
 *       - Exams
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: class_id
 *         schema: { type: integer }
 *       - in: query
 *         name: exam_group_id
 *         schema: { type: integer }
 *       - in: query
 *         name: is_active
 *         schema: { type: boolean }
 *     responses:
 *       200:
 *         description: Schedule list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *   post:
 *     summary: Create exam schedule
 *     description: |
 *       Schedules an exam group for a specific class and subject
 *       between `start_time` and `end_time`. Students may only
 *       start attempts while the schedule is active and past its
 *       `start_time`.
 *     tags:
 *       - Exams
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               exam_group_id: { type: integer }
 *               class_id: { type: integer }
 *               subject_id: { type: integer }
 *               start_time: { type: string, format: date-time }
 *               end_time: { type: string, format: date-time }
 *               duration_minutes: { type: integer }
 *               instructions: { type: string }
 *     responses:
 *       201:
 *         description: Created schedule
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 */
router.get('/schedules', auth, (req, res) => {
  const { class_id, exam_group_id, is_active } = req.query;
  
  let query = `
    SELECT es.*, eg.name as exam_name, eg.exam_type, eg.duration_minutes,
      c.name as class_name, s.name as subject_name
    FROM exam_schedules es
    JOIN exam_groups eg ON es.exam_group_id = eg.id
    LEFT JOIN classes c ON es.class_id = c.id
    LEFT JOIN subjects s ON es.subject_id = s.id
    WHERE es.is_deleted = 0
  `;
  const params = [];
  
  if (class_id) { query += ' AND es.class_id = ?'; params.push(class_id); }
  if (exam_group_id) { query += ' AND es.exam_group_id = ?'; params.push(exam_group_id); }
  if (is_active !== undefined) { query += ' AND es.is_active = ?'; params.push(is_active ? 1 : 0); }
  
  query += ' ORDER BY es.start_time DESC';
  
  const schedules = db.prepare(query).all(...params);
  res.json(schedules);
});

router.post('/schedules', auth, (req, res) => {
  const { exam_group_id, class_id, subject_id, start_time, end_time, duration_minutes, instructions } = req.body;

  const uid = uuidv4();

  try {
    // is_active=0 by default: schedules are drafts until an admin
    // explicitly publishes them via /api/exams/schedules/:id/publish.
    // Students only see published (is_active=1) schedules in
    // /api/exams/available.
    const result = db.prepare(`
      INSERT INTO exam_schedules (uid, exam_group_id, class_id, subject_id, start_time, end_time, duration_minutes, instructions, is_active)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, 0)
    `).run(uid, exam_group_id, class_id, subject_id, start_time, end_time, duration_minutes, instructions);

    const schedule = db.prepare('SELECT * FROM exam_schedules WHERE id = ?').get(result.lastInsertRowid);
    setSyncPending('exam_schedules', result.lastInsertRowid);
    res.status(201).json(schedule);

  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

// Admin or principal publishes a schedule: flips is_active=1 so
// students see it in /api/exams/available, and marks the parent exam
// group as approved with the reviewer's id + timestamp. The two
// changes are independent updates — if either fails the other is not
// rolled back (the caller can retry the publish).
router.post('/schedules/:id/publish', auth, requireAdmin, (req, res) => {
  const schedule = db.prepare(
    'SELECT id, exam_group_id, is_active FROM exam_schedules WHERE id = ? AND is_deleted = 0'
  ).get(req.params.id);
  if (!schedule) return res.status(404).json({ error: 'Schedule not found' });
  if (schedule.is_active) {
    return res.status(409).json({ error: 'Schedule is already published' });
  }

  const publishSchedule = db.prepare(`
    UPDATE exam_schedules
    SET is_active = 1,
        sync_status = 'pending',
        updated_at = CURRENT_TIMESTAMP
    WHERE id = ?
  `);
  const approveGroup = db.prepare(`
    UPDATE exam_groups
    SET status = 'approved',
        rejection_reason = NULL,
        reviewed_at = CURRENT_TIMESTAMP,
        reviewed_by = ?,
        sync_status = 'pending',
        updated_at = CURRENT_TIMESTAMP
    WHERE id = ?
  `);

  try {
    publishSchedule.run(req.params.id);
    setSyncPending('exam_schedules', req.params.id);
    if (schedule.exam_group_id) {
      approveGroup.run(req.user.id, schedule.exam_group_id);
      setSyncPending('exam_groups', schedule.exam_group_id);
      recordAudit(schedule.exam_group_id, req.params.id, 'publish', req.user.id, null);
    }
  } catch (e) {
    return res.status(400).json({ error: e.message });
  }

  const updated = db.prepare('SELECT * FROM exam_schedules WHERE id = ?').get(req.params.id);
  res.json(updated);
});

// ============ EXAM ATTEMPTS ============

/**
 * @openapi
 * /api/exams/available:
 *   get:
 *     summary: Available exams (student)
 *     description: |
 *       Returns the scheduled exams the calling student is
 *       eligible to start: the schedule must be active, must have
 *       already started, and must target one of the student's
 *       currently enrolled classes. Each row includes the
 *       student's attempts taken (excluding in-progress) so the
 *       client can decide whether another attempt is allowed.
 *     tags:
 *       - Exams
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Available exam schedules
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 */
router.get('/available', auth, (req, res) => {
  const studentId = req.user.id;
  const now = new Date().toISOString();
  
  // Get student's enrolled classes
  const enrollments = db.prepare(`
    SELECT class_id FROM enrollments 
    WHERE student_id = ? AND status = 'active'
  `).all(studentId);
  const classIds = enrollments.map(e => e.class_id);
  
  if (classIds.length === 0) {
    return res.json([]);
  }
  
  // Get available scheduled exams
  const placeholders = classIds.map(() => '?').join(',');
  const schedules = db.prepare(`
    SELECT es.*, eg.name as exam_name, eg.exam_type, eg.duration_minutes, eg.total_marks, eg.max_attempts,
      s.name as subject_name,
      (SELECT COUNT(*) FROM exam_attempts WHERE exam_group_id = eg.id AND student_id = ? AND status != 'in-progress') as attempts_taken
    FROM exam_schedules es
    JOIN exam_groups eg ON es.exam_group_id = eg.id
    LEFT JOIN subjects s ON es.subject_id = s.id
    WHERE es.class_id IN (${placeholders})
      AND es.is_active = 1
      AND es.start_time <= ?
    ORDER BY es.start_time
  `).all(studentId, now, ...classIds);
  
  res.json(schedules);
});

/**
 * @openapi
 * /api/exams/attempts/start:
 *   post:
 *     summary: Start exam attempt
 *     description: |
 *       Starts a new exam attempt (or resumes the existing
 *       in-progress one) for the given exam group. The returned
 *       questions have `correct_answer` stripped for security.
 *       Question order respects the exam group's
 *       `is_random_order` setting.
 *
 *       Answering flow:
 *         1. `POST /api/exams/attempts/start` (this endpoint)
 *         2. `POST /api/exams/attempts/{attemptId}/answer` for
 *            each question
 *         3. `POST /api/exams/attempts/{attemptId}/submit` to
 *            grade and finalize.
 *     tags:
 *       - Exams
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - exam_group_id
 *             properties:
 *               exam_group_id: { type: integer }
 *               schedule_id: { type: integer }
 *     responses:
 *       201:
 *         description: New or resumed attempt with questions
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Maximum attempts reached }
 *       401: { description: Unauthorized }
 *       404: { description: Exam not found }
 */
router.post('/attempts/start', auth, (req, res) => {
  const studentId = req.user.id;
  const { exam_group_id, schedule_id } = req.body;
  
  // Check max attempts
  const existingAttempts = db.prepare(`
    SELECT COUNT(*) as count FROM exam_attempts 
    WHERE exam_group_id = ? AND student_id = ? AND status != 'in-progress'
  `).get(exam_group_id, studentId);
  
  const examGroup = db.prepare('SELECT * FROM exam_groups WHERE id = ?').get(exam_group_id);
  if (!examGroup) return res.status(404).json({ error: 'Exam not found' });
  
  if (existingAttempts.count >= examGroup.max_attempts) {
    return res.status(400).json({ error: 'Maximum attempts reached' });
  }
  
  // Check existing in-progress attempt
  const inProgress = db.prepare(`
    SELECT * FROM exam_attempts 
    WHERE exam_group_id = ? AND student_id = ? AND status = 'in-progress'
    ORDER BY started_at DESC LIMIT 1
  `).get(exam_group_id, studentId);
  
  if (inProgress) {
    // Return existing attempt
    const questions = db.prepare(`
      SELECT * FROM question_banks 
      WHERE exam_group_id = ? AND is_deleted = 0
      ORDER BY ${examGroup.is_random_order ? 'RANDOM()' : 'id'}
    `).all(exam_group_id);
    
    return res.json({ ...inProgress, questions });
  }
  
  // Create new attempt
  const uid = uuidv4();
  
  const result = db.prepare(`
    INSERT INTO exam_attempts (uid, student_id, exam_group_id, schedule_id, started_at)
    VALUES (?, ?, ?, ?, ?)
  `).run(uid, studentId, exam_group_id, schedule_id, new Date().toISOString());
  
  const attempt = db.prepare('SELECT * FROM exam_attempts WHERE id = ?').get(result.lastInsertRowid);
  
  // Get questions (without correct answers for security)
  const questions = db.prepare(`
    SELECT id, question_type, question, options, marks, difficulty
    FROM question_banks 
    WHERE exam_group_id = ? AND is_deleted = 0
    ORDER BY ${examGroup.is_random_order ? 'RANDOM()' : 'id'}
  `).all(exam_group_id);
  
  // Parse options for frontend
  const processedQuestions = questions.map(q => ({
    ...q,
    options: q.options ? JSON.parse(q.options) : null
  }));
  
  res.status(201).json({ ...attempt, questions: processedQuestions });
});

/**
 * @openapi
 * /api/exams/attempts/{attemptId}/answer:
 *   post:
 *     summary: Save exam answer
 *     description: |
 *       Upserts a single answer for the given attempt. Call once
 *       per question during the attempt.
 *
 *       Answering flow:
 *         1. `POST /api/exams/attempts/start`
 *         2. `POST /api/exams/attempts/{attemptId}/answer` (this
 *            endpoint, repeated for each question)
 *         3. `POST /api/exams/attempts/{attemptId}/submit` to
 *            grade and finalize.
 *     tags:
 *       - Exams
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: attemptId
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - question_id
 *               - answer
 *             properties:
 *               question_id: { type: integer }
 *               answer: { type: string }
 *     responses:
 *       200:
 *         description: Saved
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success: { type: boolean }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 */
router.post('/attempts/:attemptId/answer', auth, (req, res) => {
  const { question_id, answer } = req.body;
  const attemptId = req.params.attemptId;
  
  const uid = uuidv4();
  
  try {
    // Upsert answer
    const existing = db.prepare('SELECT id FROM exam_answers WHERE attempt_id = ? AND question_id = ?').get(attemptId, question_id);
    
    if (existing) {
      db.prepare(`
        UPDATE exam_answers SET answer = ?, answered_at = CURRENT_TIMESTAMP
        WHERE id = ?
      `).run(answer, existing.id);
    } else {
      db.prepare(`
        INSERT INTO exam_answers (uid, attempt_id, question_id, answer, answered_at)
        VALUES (?, ?, ?, ?, ?)
      `).run(uid, attemptId, question_id, answer, new Date().toISOString());
    }
    
    res.json({ success: true });
    
  } catch (error) {
    res.status(400).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/exams/attempts/{attemptId}/submit:
 *   post:
 *     summary: Submit exam attempt
 *     description: |
 *       Finalizes the attempt: grades each saved answer against
 *       the question's `correct_answer`, records
 *       `obtained_marks`, `percentage`, `time_spent_seconds`,
 *       and the request's IP and user-agent, then marks the
 *       attempt as `submitted`. The response includes all
 *       questions and a `correctAnswers` map for client-side
 *       review when the parent exam group allows it.
 *     tags:
 *       - Exams
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: attemptId
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Graded attempt with questions and correct answers
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       404: { description: Attempt not found }
 */
router.post('/attempts/:attemptId/submit', auth, (req, res) => {
  const attemptId = req.params.attemptId;
  
  // Get attempt
  const attempt = db.prepare(`
    SELECT ea.*, eg.correct_answer as exam_correct
    FROM exam_attempts ea
    JOIN exam_groups eg ON ea.exam_group_id = eg.id
    WHERE ea.id = ?
  `).get(attemptId);
  
  if (!attempt) return res.status(404).json({ error: 'Attempt not found' });
  
  // Get all questions and answers
  const questions = db.prepare('SELECT * FROM question_banks WHERE exam_group_id = ?').all(attempt.exam_group_id);
  const answers = db.prepare('SELECT * FROM exam_answers WHERE attempt_id = ?').all(attemptId);
  
  // Grade the exam
  let totalMarks = 0;
  let obtainedMarks = 0;
  
  const answerMap = {};
  answers.forEach(a => { answerMap[a.question_id] = a; });
  
  for (const question of questions) {
    totalMarks += question.marks || 0;
    const answer = answerMap[question.id];
    
    if (answer) {
      const correctAnswer = question.correct_answer;
      const studentAnswer = answer.answer;
      
      // Check if correct (case-insensitive for short answers)
      let isCorrect = false;
      if (question.question_type === 'multiple-choice' || question.question_type === 'true-false') {
        isCorrect = studentAnswer === correctAnswer;
      } else {
        isCorrect = studentAnswer?.toLowerCase().trim() === correctAnswer?.toLowerCase().trim();
      }
      
      // Update answer with grading
      db.prepare(`
        UPDATE exam_answers SET is_correct = ?, marks_obtained = ?
        WHERE id = ?
      `).run(isCorrect ? 1 : 0, isCorrect ? question.marks : 0, answer.id);
      
      if (isCorrect) {
        obtainedMarks += question.marks || 0;
      }
    }
  }
  
  const percentage = totalMarks > 0 ? (obtainedMarks / totalMarks) * 100 : 0;
  const timeSpent = Math.floor((new Date() - new Date(attempt.started_at)) / 1000);
  
  // Update attempt
  db.prepare(`
    UPDATE exam_attempts SET
      submitted_at = CURRENT_TIMESTAMP,
      time_spent_seconds = ?,
      total_marks = ?,
      obtained_marks = ?,
      percentage = ?,
      status = 'submitted',
      ip_address = ?,
      user_agent = ?,
      updated_at = CURRENT_TIMESTAMP,
      sync_status = 'pending'
    WHERE id = ?
  `).run(
    timeSpent,
    totalMarks,
    obtainedMarks,
    percentage,
    req.ip,
    req.get('user-agent'),
    attemptId
  );
  
  const updatedAttempt = db.prepare('SELECT * FROM exam_attempts WHERE id = ?').get(attemptId);

  // Resolve the final letter via the grading-scale chain:
  // exam_group.grading_scale → school default → zimsec_olevel.
  const examGroup = db.prepare(
    'SELECT grading_scale FROM exam_groups WHERE id = ?'
  ).get(attempt.exam_group_id);
  const defaultScale = readDefaultScale();
  const band = letterFor(percentage, examGroup?.grading_scale || defaultScale);

  res.json({
    ...updatedAttempt,
    questions: questions, // Include for review if allowed
    correctAnswers: questions.map(q => ({ question_id: q.id, correct_answer: q.correct_answer, marks: q.marks })),
    grade_letter: band.letter,
    grade_descriptor: band.descriptor,
    grade_points: band.points,
    scale: band.scale,
  });
});

/**
 * @openapi
 * /api/exams/attempts/{attemptId}:
 *   get:
 *     summary: Get attempt result
 *     description: |
 *       Returns the attempt joined with the parent exam group's
 *       `show_results` and `allow_review` flags, plus all
 *       answers (with question text, correct answer, marks, and
 *       parsed options).
 *     tags:
 *       - Exams
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: attemptId
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Attempt detail with answers
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       404: { description: Attempt not found }
 */
router.get('/attempts/:attemptId', auth, (req, res) => {
  const attempt = db.prepare(`
    SELECT ea.*, eg.name as exam_name, eg.show_results, eg.allow_review, eg.grading_scale
    FROM exam_attempts ea
    JOIN exam_groups eg ON ea.exam_group_id = eg.id
    WHERE ea.id = ?
  `).get(req.params.attemptId);

  if (!attempt) return res.status(404).json({ error: 'Attempt not found' });

  // Get answers with questions
  const answers = db.prepare(`
    SELECT qa.*, qb.question, qb.correct_answer, qb.marks as question_marks, qb.options as question_options
    FROM exam_answers qa
    JOIN question_banks qb ON qa.question_id = qb.id
    WHERE qa.attempt_id = ?
  `).all(req.params.attemptId);

  // Resolve the final letter (attempts store `percentage`; the letter is
  // derived at read time, so re-grading a school default doesn't require
  // a backfill).
  const band = letterFor(
    attempt.percentage,
    attempt.grading_scale || readDefaultScale()
  );

  res.json({
    ...attempt,
    answers: answers.map(a => ({
      ...a,
      question_options: a.question_options ? JSON.parse(a.question_options) : null
    })),
    grade_letter: band.letter,
    grade_descriptor: band.descriptor,
    grade_points: band.points,
    scale: band.scale,
  });
});

/**
 * @openapi
 * /api/exams/attempts:
 *   get:
 *     summary: My exam attempts (student)
 *     description: |
 *       Returns the calling student's most recent 50 exam
 *       attempts joined with the exam group's name, type, total
 *       marks, and passing marks. Optionally filtered by
 *       `exam_group_id`.
 *     tags:
 *       - Exams
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: exam_group_id
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Attempt history
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 */
router.get('/attempts', auth, (req, res) => {
  const studentId = req.user.id;
  const { exam_group_id } = req.query;
  
  let query = `
    SELECT ea.*, eg.name as exam_name, eg.exam_type, eg.total_marks, eg.passing_marks
    FROM exam_attempts ea
    JOIN exam_groups eg ON ea.exam_group_id = eg.id
    WHERE ea.student_id = ?
  `;
  const params = [studentId];
  
  if (exam_group_id) {
    query += ' AND ea.exam_group_id = ?';
    params.push(exam_group_id);
  }
  
  query += ' ORDER BY ea.started_at DESC LIMIT 50';
  
  const attempts = db.prepare(query).all(...params);
  res.json(attempts);
});

// ============ GRADING ============

/**
 * @openapi
 * /api/exams/results:
 *   get:
 *     summary: Exam results (teacher)
 *     description: |
 *       Returns submitted/graded attempts for an exam group with
 *       student details, ordered by percentage descending.
 *       Optional filters: `exam_group_id`, `class_id`. The
 *       implied role filter in SQL is permissive — any
 *       authenticated caller may read; gate upstream for
 *       student-only access if needed.
 *     tags:
 *       - Exams
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: exam_group_id
 *         schema: { type: integer }
 *       - in: query
 *         name: class_id
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Result list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 */
router.get('/results', auth, (req, res) => {
  const { exam_group_id, class_id } = req.query;
  
  let query = `
    SELECT ea.*, u.first_name, u.last_name, u.email,
      eg.name as exam_name, eg.total_marks, eg.passing_marks, eg.exam_type
    FROM exam_attempts ea
    JOIN users u ON ea.student_id = u.id
    JOIN exam_groups eg ON ea.exam_group_id = eg.id
    LEFT JOIN enrollments e ON ea.student_id = e.student_id
    WHERE ea.status IN ('submitted', 'graded')
  `;
  const params = [];
  
  if (exam_group_id) {
    query += ' AND ea.exam_group_id = ?';
    params.push(exam_group_id);
  }
  if (class_id) {
    query += ' AND e.class_id = ?';
    params.push(class_id);
  }
  
  query += ' ORDER BY ea.percentage DESC';
  
  const results = db.prepare(query).all(...params);
  res.json(results);
});

// ============ STATS ============

/**
 * @openapi
 * /api/exams/stats:
 *   get:
 *     summary: Exam statistics
 *     description: |
 *       Returns aggregate attempt stats (total, completed,
 *       average/highest/lowest percentage, passed/failed
 *       counts) plus per-difficulty question stats for the
 *       given exam group. Filtered by `exam_group_id` when
 *       provided.
 *     tags:
 *       - Exams
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: exam_group_id
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Aggregate stats with question breakdown
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 */
router.get('/stats', auth, (req, res) => {
  const { exam_group_id } = req.query;
  
  let whereClause = '';
  const params = [];
  
  if (exam_group_id) {
    whereClause = 'WHERE exam_group_id = ?';
    params.push(exam_group_id);
  }
  
  const stats = db.prepare(`
    SELECT 
      COUNT(*) as total_attempts,
      SUM(CASE WHEN status = 'submitted' OR status = 'graded' THEN 1 ELSE 0 END) as completed_attempts,
      AVG(percentage) as avg_percentage,
      MAX(percentage) as highest_percentage,
      MIN(percentage) as lowest_percentage,
      SUM(CASE WHEN percentage >= 50 THEN 1 ELSE 0 END) as passed,
      SUM(CASE WHEN percentage < 50 THEN 1 ELSE 0 END) as failed
    FROM exam_attempts
    ${whereClause}
  `).get(...params);
  
  // Question stats — only scoped when an exam_group_id was supplied
  const questionStats = exam_group_id
    ? db.prepare(`
        SELECT
          difficulty,
          COUNT(*) as total,
          AVG(marks) as avg_marks
        FROM question_banks
        WHERE exam_group_id = ? AND is_deleted = 0
        GROUP BY difficulty
      `).all(exam_group_id)
    : db.prepare(`
        SELECT
          difficulty,
          COUNT(*) as total,
          AVG(marks) as avg_marks
        FROM question_banks
        WHERE is_deleted = 0
        GROUP BY difficulty
      `).all();

  res.json({ ...stats, questionStats });
});

module.exports = router;