/**
 * Assignments Controller
 * Assignment/homework management
 */

const express = require('express');
const router = express.Router();
const { v4: uuidv4 } = require('uuid');
const multer = require('multer');
const path = require('path');
const fs = require('fs');

// Configure Multer for student assignment uploads
const storage = multer.diskStorage({
  destination: function (req, file, cb) {
    const uploadDir = path.join(__dirname, '../../uploads');
    if (!fs.existsSync(uploadDir)){
        fs.mkdirSync(uploadDir, { recursive: true });
    }
    cb(null, uploadDir);
  },
  filename: function (req, file, cb) {
    const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
    cb(null, uniqueSuffix + path.extname(file.originalname));
  }
});

const upload = multer({ 
  storage: storage,
  limits: { fileSize: 20 * 1024 * 1024 } // 20MB limit
});

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

const getDb = () => {
  if (!db) {
    db = new Database(dbPath);
    db.pragma('foreign_keys = ON');
  }
  return db;
};

// 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 {
    const jwt = require('jsonwebtoken');
    const { jwtSecret: JWT_SECRET } = require('../config');
    req.user = jwt.verify(token, JWT_SECRET);
    next();
  } catch (err) {
    res.status(401).json({ error: 'Invalid token' });
  }
};

// Admin or teacher only
const adminOrTeacher = (req, res, next) => {
  if (!['systems_admin', 'school_admin', 'principal', 'teacher'].includes(req.user.role)) {
    return res.status(403).json({ error: 'Admin or Teacher access required' });
  }
  next();
};

// ============ ASSIGNMENTS CRUD ============

/**
 * @openapi
 * /api/assignments:
 *   get:
 *     summary: List assignments
 *     description: |
 *       Returns assignments joined with their course name, plus
 *       counts of submissions, graded submissions, and enrolled
 *       students. Optional filters: `course_id`, `status`
 *       (`draft` | `published` | `closed`), `due_before` and
 *       `due_after` (ISO timestamps). Teachers only see
 *       assignments for courses they teach; admins and
 *       principals see all.
 *     tags:
 *       - Assignments
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: course_id
 *         schema: { type: integer }
 *       - in: query
 *         name: status
 *         schema: { type: string }
 *       - in: query
 *         name: due_before
 *         schema: { type: string, format: date-time }
 *       - in: query
 *         name: due_after
 *         schema: { type: string, format: date-time }
 *     responses:
 *       200:
 *         description: Assignment list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *       500: { description: Server error }
 *   post:
 *     summary: Create assignment
 *     description: |
 *       Creates a new assignment in `draft` (unpublished) state.
 *       Restricted to admin or teacher roles. Use the
 *       `/api/assignments/{id}/publish` lifecycle endpoint to
 *       release it to students.
 *     tags:
 *       - Assignments
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - title
 *               - course_id
 *             properties:
 *               course_id: { type: integer }
 *               title: { type: string }
 *               description: { type: string }
 *               instructions: { type: string }
 *               due_date: { type: string, format: date-time }
 *               max_score: { type: integer }
 *               submission_type: { type: string }
 *               allow_late_submission: { type: boolean }
 *     responses:
 *       201:
 *         description: Created assignment
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: Admin or Teacher only }
 *       500: { description: Server error }
 */
router.get('/', auth, (req, res) => {
  try {
    const { course_id, status, due_before, due_after } = req.query;

    let query = `
      SELECT a.*,
        sub.name as course_name,
        (SELECT COUNT(*) FROM submissions WHERE assignment_id = a.id AND is_deleted = 0) as submission_count,
        (SELECT COUNT(*) FROM submissions WHERE assignment_id = a.id AND status = 'graded' AND is_deleted = 0) as graded_count,
        (SELECT COUNT(*) FROM course_enrolments WHERE course_id = a.course_id AND status = 'active' AND is_deleted = 0) as student_count
      FROM assignments a
      LEFT JOIN subjects sub ON a.course_id = sub.id
      WHERE a.is_deleted = 0
    `;
    const params = [];

    if (course_id) {
      query += ' AND a.course_id = ?';
      params.push(course_id);
    }
    if (status) {
      query += ' AND a.status = ?';
      params.push(status);
    }
    if (due_before) {
      query += ' AND a.due_date <= ?';
      params.push(due_before);
    }
    if (due_after) {
      query += ' AND a.due_date >= ?';
      params.push(due_after);
    }

    // Filter by teacher
    if (req.user.role === 'teacher') {
      query += ' AND (sub.teacher_id = ?)';
      params.push(req.user.id);
    }

    query += ' ORDER BY a.due_date DESC, a.created_at DESC';

    const assignments = getDb().prepare(query).all(...params);
    res.json(assignments);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/assignments/{id}/submission:
 *   get:
 *     summary: Get my submission for assignment
 *     description: |
 *       Returns the calling user's submission record for the
 *       given assignment, or `null` if none exists.
 *     tags:
 *       - Assignments
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Submission (or null)
 *         content:
 *           application/json:
 *             schema: { type: object, nullable: true }
 *       401: { description: Unauthorized }
 *       500: { description: Server error }
 */
router.get('/:id/submission', auth, (req, res) => {
  try {
    const submission = getDb().prepare('SELECT * FROM submissions WHERE assignment_id = ? AND student_id = ? AND is_deleted = 0').get(req.params.id, req.user.id);
    if (!submission) {
      return res.json(null);
    }

    // PR 3 §6.1 — also attach the submission's files (parent_kind='submission')
    // and any marked scripts the teacher has returned (parent_kind='marked_script').
    // The existing submissions.file_path stays for backward compatibility.
    const submissionFiles = getDb().prepare(`
      SELECT * FROM attachments
      WHERE parent_kind = 'submission' AND parent_id = ? AND is_deleted = 0
      ORDER BY uploaded_at DESC
    `).all(submission.id);

    const markedScripts = getDb().prepare(`
      SELECT * FROM attachments
      WHERE parent_kind = 'marked_script' AND parent_id = ? AND is_deleted = 0
      ORDER BY uploaded_at DESC
    `).all(submission.id);

    res.json({ ...submission, attachments: submissionFiles, marked_scripts: markedScripts });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/assignments/{id}:
 *   get:
 *     summary: Get assignment by id
 *     description: |
 *       Returns a single assignment with its course name,
 *       submission counts, and the full list of submissions
 *       joined with the student name and email.
 *     tags:
 *       - Assignments
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Assignment detail with submissions
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       404: { description: Assignment not found }
 *       500: { description: Server error }
 *   put:
 *     summary: Update assignment
 *     description: |
 *       Updates assignment fields. Admins and principals may
 *       edit any assignment; teachers may only edit assignments
 *       in courses they teach. Lifecycle transitions should
 *       normally go through the `/publish`, `/unpublish`, and
 *       `/close` endpoints, but a direct `status` here will keep
 *       `is_published` consistent with it.
 *     tags:
 *       - Assignments
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               title: { type: string }
 *               description: { type: string }
 *               instructions: { type: string }
 *               due_date: { type: string, format: date-time }
 *               max_score: { type: integer }
 *               status: { type: string }
 *               submission_type: { type: string }
 *               allow_late_submission: { type: boolean }
 *     responses:
 *       200:
 *         description: Updated assignment
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Only the course teacher can update this assignment }
 *       404: { description: Assignment not found }
 *       500: { description: Server error }
 *   delete:
 *     summary: Soft-delete assignment
 *     description: |
 *       Marks the assignment as deleted. Admins and principals
 *       may delete any assignment; teachers may only delete
 *       assignments in courses they teach.
 *     tags:
 *       - Assignments
 *     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 }
 *       403: { description: Only the course teacher can delete this assignment }
 *       404: { description: Assignment not found }
 *       500: { description: Server error }
 */
router.get('/:id', auth, (req, res) => {
  try {
    const assignment = getDb().prepare(`
      SELECT a.*,
        sub.name as course_name,
        (SELECT COUNT(*) FROM submissions WHERE assignment_id = a.id AND is_deleted = 0) as submission_count,
        (SELECT COUNT(*) FROM submissions WHERE assignment_id = a.id AND status = 'graded' AND is_deleted = 0) as graded_count,
        (SELECT COUNT(*) FROM course_enrolments WHERE course_id = a.course_id AND status = 'active' AND is_deleted = 0) as student_count
      FROM assignments a
      LEFT JOIN subjects sub ON a.course_id = sub.id
      WHERE a.id = ? AND a.is_deleted = 0
    `).get(req.params.id);

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

    // Get submissions
    const submissions = getDb().prepare(`
      SELECT s.*, u.first_name || ' ' || u.last_name as student_name, u.email as student_email
      FROM submissions s
      JOIN users u ON s.student_id = u.id
      WHERE s.assignment_id = ? AND s.is_deleted = 0
      ORDER BY s.submitted_at DESC
    `).all(req.params.id);

    // §A — File Attachments plan §3.5: also fetch per-submission attachments
    // (parent_kind='submission') in a single query and attach them to each
    // submission row. Keeps the legacy submissions.file_path field for
    // backward compatibility — old downloads still work, new code prefers
    // the attachments list.
    if (submissions.length > 0) {
      const submissionIds = submissions.map((s) => s.id);
      const placeholders = submissionIds.map(() => '?').join(',');
      const attachmentRows = getDb().prepare(`
        SELECT * FROM attachments
        WHERE parent_kind = 'submission' AND parent_id IN (${placeholders}) AND is_deleted = 0
        ORDER BY uploaded_at DESC
      `).all(...submissionIds);

      const byParent = new Map();
      for (const a of attachmentRows) {
        if (!byParent.has(a.parent_id)) byParent.set(a.parent_id, []);
        byParent.get(a.parent_id).push(a);
      }
      submissions.forEach((s) => {
        s.attachments = byParent.get(s.id) || [];
      });
    } else {
      submissions.forEach((s) => { s.attachments = []; });
    }

    res.json({ ...assignment, submissions });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

router.post('/', auth, adminOrTeacher, (req, res) => {
  try {
    const {
      course_id, title, description, instructions, due_date,
      max_score, submission_type, allow_late_submission,
    } = req.body;

    if (!title || !course_id) {
      return res.status(400).json({ error: 'Title and course are required' });
    }

    const uid = uuidv4();
    const allowLate = allow_late_submission ? 1 : 0;

    const result = getDb().prepare(`
      INSERT INTO assignments (uid, course_id, title, description, instructions, due_date, max_score, submission_type, allow_late_submission, status, is_published)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'draft', 0)
    `).run(uid, course_id, title, description, instructions, due_date, max_score || 100, submission_type || 'file', allowLate);

    const assignment = getDb().prepare(`
      SELECT a.*, sub.name as course_name
      FROM assignments a
      LEFT JOIN subjects sub ON a.course_id = sub.id
      WHERE a.id = ?
    `).get(result.lastInsertRowid);

    res.status(201).json(assignment);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

router.put('/:id', auth, adminOrTeacher, (req, res) => {
  try {
    const {
      title, description, instructions, due_date, max_score,
      status, submission_type, allow_late_submission,
    } = req.body;

    // Check permissions
    const assignment = getDb().prepare(`
      SELECT a.*, sub.teacher_id
      FROM assignments a
      JOIN subjects sub ON a.course_id = sub.id
      WHERE a.id = ?
    `).get(req.params.id);

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

    const isPrivileged = ['systems_admin', 'school_admin', 'principal'].includes(req.user.role);
    if (!isPrivileged && assignment.teacher_id !== req.user.id) {
      return res.status(403).json({ error: 'Only the course teacher can update this assignment' });
    }

    // Status transitions go through the lifecycle endpoints (/publish, /unpublish,
    // /close). When callers do pass `status` here, keep is_published in sync with it
    // so a direct PUT cannot leave a row flagged published but unlisted (or vice versa).
    const allowLate = allow_late_submission === undefined ? null : (allow_late_submission ? 1 : 0);
    const derivedIsPublished = status === undefined ? null : (status === 'published' ? 1 : 0);

    getDb().prepare(`
      UPDATE assignments SET
        title = COALESCE(?, title),
        description = COALESCE(?, description),
        instructions = COALESCE(?, instructions),
        due_date = COALESCE(?, due_date),
        max_score = COALESCE(?, max_score),
        status = COALESCE(?, status),
        is_published = COALESCE(?, is_published),
        submission_type = COALESCE(?, submission_type),
        allow_late_submission = COALESCE(?, allow_late_submission),
        updated_at = CURRENT_TIMESTAMP,
        sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(
      title, description, instructions, due_date, max_score,
      status, derivedIsPublished, submission_type, allowLate,
      req.params.id,
    );

    const updated = getDb().prepare(`
      SELECT a.*, sub.name as course_name
      FROM assignments a
      LEFT JOIN subjects sub ON a.course_id = sub.id
      WHERE a.id = ?
    `).get(req.params.id);

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

router.delete('/:id', auth, adminOrTeacher, (req, res) => {
  try {
    const assignment = getDb().prepare(`
      SELECT a.*, sub.teacher_id 
      FROM assignments a 
      JOIN subjects sub ON a.course_id = sub.id 
      WHERE a.id = ?
    `).get(req.params.id);

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

    const isPrivileged = ['systems_admin', 'school_admin', 'principal'].includes(req.user.role);
    if (!isPrivileged && assignment.teacher_id !== req.user.id) {
      return res.status(403).json({ error: 'Only the course teacher can delete this assignment' });
    }

    getDb().prepare(`
      UPDATE assignments SET is_deleted = 1, updated_at = CURRENT_TIMESTAMP, sync_status = 'pending'
      WHERE id = ?
    `).run(req.params.id);

    res.json({ success: true, message: 'Assignment deleted' });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ LIFECYCLE (publish / unpublish / close) ============
// Status transitions: draft -> published -> closed. Reverse (closed -> draft) is admin-only
// via PUT to avoid foot-gunning a graded assignment back into active circulation.

const transitionStatus = (req, res, nextStatus, setPublished) => {
  try {
    const assignment = getDb().prepare(`
      SELECT a.*, sub.teacher_id
      FROM assignments a
      JOIN subjects sub ON a.course_id = sub.id
      WHERE a.id = ? AND a.is_deleted = 0
    `).get(req.params.id);

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

    const isPrivileged = ['systems_admin', 'school_admin', 'principal'].includes(req.user.role);
    if (!isPrivileged && assignment.teacher_id !== req.user.id) {
      return res.status(403).json({ error: 'Only the course teacher can change this assignment' });
    }

    if (assignment.status === nextStatus) {
      return res.json(assignment);
    }

    if (assignment.status === 'closed' && nextStatus !== 'closed' && !isPrivileged) {
      return res.status(409).json({ error: 'Closed assignments cannot be reopened by the course teacher' });
    }

    getDb().prepare(`
      UPDATE assignments SET
        status = ?,
        is_published = ?,
        updated_at = CURRENT_TIMESTAMP,
        sync_status = 'pending'
      WHERE id = ?
    `).run(nextStatus, setPublished, req.params.id);

    const updated = getDb().prepare(`
      SELECT a.*, sub.name as course_name
      FROM assignments a
      LEFT JOIN subjects sub ON a.course_id = sub.id
      WHERE a.id = ?
    `).get(req.params.id);

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

/**
 * @openapi
 * /api/assignments/{id}/publish:
 *   post:
 *     summary: Publish assignment
 *     description: |
 *       Transitions an assignment to `published` and sets
 *       `is_published = 1` so the assignment becomes visible to
 *       students. Course teachers, admins, and principals may
 *       publish; reopening a `closed` assignment requires admin
 *       or principal privileges.
 *     tags:
 *       - Assignments
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Published assignment
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Only the course teacher can change this assignment }
 *       404: { description: Assignment not found }
 *       409: { description: Closed assignments cannot be reopened by the course teacher }
 *       500: { description: Server error }
 */
router.post('/:id/publish', auth, adminOrTeacher, (req, res) => transitionStatus(req, res, 'published', 1));
/**
 * @openapi
 * /api/assignments/{id}/unpublish:
 *   post:
 *     summary: Unpublish assignment
 *     description: |
 *       Transitions an assignment back to `draft` and sets
 *       `is_published = 0` so it is hidden from students again.
 *       Course teachers, admins, and principals may unpublish;
 *       reopening a `closed` assignment requires admin or
 *       principal privileges.
 *     tags:
 *       - Assignments
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Unpublished assignment
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Only the course teacher can change this assignment }
 *       404: { description: Assignment not found }
 *       409: { description: Closed assignments cannot be reopened by the course teacher }
 *       500: { description: Server error }
 */
router.post('/:id/unpublish', auth, adminOrTeacher, (req, res) => transitionStatus(req, res, 'draft', 0));
/**
 * @openapi
 * /api/assignments/{id}/close:
 *   post:
 *     summary: Close assignment
 *     description: |
 *       Transitions an assignment to `closed` and sets
 *       `is_published = 0` so no further submissions are
 *       accepted. Course teachers, admins, and principals may
 *       close an assignment.
 *     tags:
 *       - Assignments
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Closed assignment
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Only the course teacher can change this assignment }
 *       404: { description: Assignment not found }
 *       500: { description: Server error }
 */
router.post('/:id/close', auth, adminOrTeacher, (req, res) => transitionStatus(req, res, 'closed', 0));

// ============ SUBMISSIONS ============

/**
 * @openapi
 * /api/assignments/{id}/submit:
 *   post:
 *     summary: Submit assignment (student)
 *     description: |
 *       Submits (or re-submits) the calling student's work for an
 *       assignment. Accepts either an uploaded file via
 *       `multipart/form-data` (field `file`, max 20MB) or an
 *       existing `file_path` on the server, plus optional
 *       free-text `content`. If a submission already exists it is
 *       overwritten and marked `submitted` again.
 *     tags:
 *       - Assignments
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       content:
 *         multipart/form-data:
 *           schema:
 *             type: object
 *             properties:
 *               file:
 *                 type: string
 *                 format: binary
 *                 description: Submission file (max 20MB)
 *               content:
 *                 type: string
 *                 description: Optional text content
 *               file_path:
 *                 type: string
 *                 description: Existing server-side file path (alternative to upload)
 *     responses:
 *       200:
 *         description: Updated existing submission
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       201:
 *         description: Created submission
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       500: { description: Server error }
 */
router.post('/:id/submit', auth, upload.single('file'), (req, res) => {
  try {
    const { content } = req.body;
    let file_path = req.file ? req.file.filename : req.body.file_path || null;

    // Enforce the submission window. The DB stores due_date and
    // allow_late_submission but the submit handler previously never
    // consulted them, so late submissions were silently accepted even
    // when the assignment explicitly disallowed them. UAT TC-LMS-011
    // caught this. We compare the assignment's due_date against the
    // current time and reject with 403 when late submission is off.
    const aRow = getDb().prepare(`
      SELECT id, due_date, allow_late_submission, status
      FROM assignments WHERE id = ? AND is_deleted = 0
    `).get(req.params.id);
    if (!aRow) {
      return res.status(404).json({ error: 'Assignment not found' });
    }
    if (aRow.due_date) {
      const due = new Date(aRow.due_date);
      const now = new Date();
      if (!Number.isNaN(due.getTime()) && now > due && !aRow.allow_late_submission) {
        return res.status(403).json({
          error: 'Submission window has closed; allow_late_submission is off for this assignment',
          code: 'SUBMISSION_WINDOW_CLOSED',
          due_date: aRow.due_date,
          allow_late_submission: !!aRow.allow_late_submission,
        });
      }
    }

    // Check if already submitted
    const existing = getDb().prepare('SELECT id FROM submissions WHERE assignment_id = ? AND student_id = ? AND is_deleted = 0').get(req.params.id, req.user.id);

    if (existing) {
      // Update existing submission
      getDb().prepare(`
        UPDATE submissions SET
          content = COALESCE(?, content),
          file_path = COALESCE(?, file_path),
          submitted_at = CURRENT_TIMESTAMP,
          status = 'submitted',
          updated_at = CURRENT_TIMESTAMP,
          sync_status = 'pending'
        WHERE id = ?
      `).run(content, file_path, existing.id);

      const updated = getDb().prepare('SELECT * FROM submissions WHERE id = ?').get(existing.id);
      return res.json(updated);
    }

    const uid = uuidv4();
    const result = getDb().prepare(`
      INSERT INTO submissions (uid, assignment_id, student_id, content, file_path, status)
      VALUES (?, ?, ?, ?, ?, 'submitted')
    `).run(uid, req.params.id, req.user.id, content, file_path);

    const submission = getDb().prepare('SELECT * FROM submissions WHERE id = ?').get(result.lastInsertRowid);
    res.status(201).json(submission);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// PR 3 §13 follow-up — fan-out helper. Resolves the parents linked to a
// given student via the parent_students table and broadcasts the payload to
// each one. Errors here are swallowed (the grade-write is the source of
// truth; the realtime push is best-effort).
function broadcastToLinkedParents(wsHub, db, studentId, payload) {
  try {
    const rows = db.prepare(`
      SELECT parent_id FROM parent_students
      WHERE student_id = ? AND is_deleted = 0
    `).all(studentId);
    for (const r of rows) {
      wsHub.broadcastToUser(r.parent_id, payload);
    }
  } catch (err) {
    console.warn('[assignments] broadcastToLinkedParents failed:', err.message);
  }
}

const gradeHandler = (req, res) => {
  try {
    const grade = req.body.grade !== undefined ? req.body.grade : req.body.score;
    const { feedback } = req.body;

    if (grade === undefined) {
      return res.status(400).json({ error: 'Grade is required' });
    }

    getDb().prepare(`
      UPDATE submissions SET
        grade = ?,
        feedback = ?,
        status = 'graded',
        graded_by = ?,
        graded_at = CURRENT_TIMESTAMP,
        updated_at = CURRENT_TIMESTAMP,
        sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(grade, feedback, req.user.id, req.params.submissionId);

    const submission = getDb().prepare(`
      SELECT s.*, u.first_name || ' ' || u.last_name as student_name
      FROM submissions s
      JOIN users u ON s.student_id = u.id
      WHERE s.id = ?
    `).get(req.params.submissionId);

    // PR 3 §13 follow-up — real-time push to the student + linked parents
    // when a grade lands. Closes the loop on the StudentDashboard
    // marked-scripts callout: it now reflects new grades within seconds,
    // no reload required.
    const wsHub = req.app.get('wsHub');
    if (wsHub && submission) {
      const payload = {
        type: 'submission:graded',
        data: {
          kind: 'assignment',
          submission_id: submission.id,
          assignment_id: submission.assignment_id,
          student_id: submission.student_id,
          grade: submission.grade,
          max_score: submission.max_score,
          graded_at: submission.graded_at,
        },
      };
      wsHub.broadcastToUser(submission.student_id, payload);
      broadcastToLinkedParents(wsHub, getDb(), submission.student_id, payload);
    }

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

const returnHandler = (req, res) => {
  try {
    getDb().prepare(`
      UPDATE submissions SET
        status = 'returned',
        updated_at = CURRENT_TIMESTAMP,
        sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(req.params.submissionId);

    const submission = getDb().prepare(`
      SELECT s.*, u.first_name || ' ' || u.last_name as student_name
      FROM submissions s
      JOIN users u ON s.student_id = u.id
      WHERE s.id = ?
    `).get(req.params.submissionId);

    // PR 3 §13 follow-up — broadcast the marked-script event when a
    // submission transitions to 'returned'. The payload carries the
    // attached marked_scripts so the student's dashboard callout (and
    // the parent's Marked Scripts section) can render the new entry
    // without a page reload.
    const wsHub = req.app.get('wsHub');
    if (wsHub && submission) {
      const markedRows = getDb().prepare(`
        SELECT * FROM attachments
        WHERE parent_kind = 'marked_script' AND parent_id = ? AND is_deleted = 0
        ORDER BY uploaded_at DESC
      `).all(submission.id);
      const payload = {
        type: 'marked_script:returned',
        data: {
          kind: 'assignment',
          submission_id: submission.id,
          assignment_id: submission.assignment_id,
          student_id: submission.student_id,
          graded_at: submission.graded_at,
          marked_scripts: markedRows,
        },
      };
      wsHub.broadcastToUser(submission.student_id, payload);
      broadcastToLinkedParents(wsHub, getDb(), submission.student_id, payload);
    }

    res.json(submission);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
};
/**
 * @openapi
 * /api/assignments/{id}/submissions/{submissionId}/grade:
 *   put:
 *     summary: Grade submission
 *     description: |
 *       Records a grade on a student's submission and marks it
 *       `graded`. The grade may be supplied as either `grade` or
 *       `score` in the request body (the two keys are
 *       interchangeable). Restricted to admin or teacher roles.
 *     tags:
 *       - Assignments
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *       - in: path
 *         name: submissionId
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - grade
 *             properties:
 *               grade: { type: number }
 *               score: { type: number, description: Alias for grade }
 *               feedback: { type: string }
 *     responses:
 *       200:
 *         description: Graded submission
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Grade is required }
 *       401: { description: Unauthorized }
 *       403: { description: Admin or Teacher only }
 *       500: { description: Server error }
 *   post:
 *     summary: Grade submission (POST alias)
 *     description: |
 *       POST alias of the PUT above. Identical behavior and
 *       request body; useful for clients that cannot send a PUT
 *       to grade an assignment submission.
 *     tags:
 *       - Assignments
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *       - in: path
 *         name: submissionId
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - grade
 *             properties:
 *               grade: { type: number }
 *               score: { type: number, description: Alias for grade }
 *               feedback: { type: string }
 *     responses:
 *       200:
 *         description: Graded submission
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Grade is required }
 *       401: { description: Unauthorized }
 *       403: { description: Admin or Teacher only }
 *       500: { description: Server error }
 */
router.put('/:id/submissions/:submissionId/grade', auth, adminOrTeacher, gradeHandler);
router.post('/:id/submissions/:submissionId/grade', auth, adminOrTeacher, gradeHandler);
router.post('/:id/submissions/:submissionId/return', auth, adminOrTeacher, returnHandler);

/**
 * @openapi
 * /api/assignments/my/submissions:
 *   get:
 *     summary: My submissions (student)
 *     description: |
 *       Returns the calling student's submissions joined with
 *       assignment title, max score, and grade. Optionally
 *       filtered by `assignment_id`.
 *     tags:
 *       - Assignments
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: assignment_id
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Submission list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *       500: { description: Server error }
 */
router.get('/my/submissions', auth, (req, res) => {
  try {
    const { assignment_id, student_id } = req.query;

    // PR 3 §7.1 — parents may scope to one of their children, admins/teachers
    // may inspect any student. Otherwise the calling user is treated as the
    // student themselves.
    let targetStudentId = req.user.id;
    if (student_id && Number(student_id) !== req.user.id) {
      if (req.user.role === 'parent') {
        const linked = getDb().prepare(
          'SELECT 1 FROM parent_students WHERE parent_id = ? AND student_id = ? AND is_deleted = 0'
        ).get(req.user.id, Number(student_id));
        if (!linked) {
          return res.status(403).json({ error: 'Not authorised to view submissions for that student' });
        }
        targetStudentId = Number(student_id);
      } else if (['school_admin', 'systems_admin', 'principal', 'teacher'].includes(req.user.role)) {
        targetStudentId = Number(student_id);
      } else {
        return res.status(403).json({ error: 'Cannot view submissions for another student' });
      }
    }

    let query = `
      SELECT s.*, a.title as assignment_title, a.max_score, s.grade
      FROM submissions s
      JOIN assignments a ON s.assignment_id = a.id
      WHERE s.student_id = ? AND s.is_deleted = 0
    `;
    const params = [targetStudentId];

    if (assignment_id) {
      query += ' AND s.assignment_id = ?';
      params.push(assignment_id);
    }

    query += ' ORDER BY s.submitted_at DESC';

    const submissions = getDb().prepare(query).all(...params);
    if (submissions.length === 0) {
      return res.json([]);
    }

    // PR 3 §6.4 / §7.1 — attach attachments and marked_scripts to each row so
    // the dashboard callout and parent's marked-scripts section can render
    // without an N+1 fetch.
    const submissionIds = submissions.map((s) => s.id);
    const placeholders = submissionIds.map(() => '?').join(',');
    const fileRows = getDb().prepare(`
      SELECT * FROM attachments
      WHERE parent_kind = 'submission' AND parent_id IN (${placeholders}) AND is_deleted = 0
    `).all(...submissionIds);
    const markedRows = getDb().prepare(`
      SELECT * FROM attachments
      WHERE parent_kind = 'marked_script' AND parent_id IN (${placeholders}) AND is_deleted = 0
    `).all(...submissionIds);

    const byParent = (rows) => {
      const map = new Map();
      for (const r of rows) {
        if (!map.has(r.parent_id)) map.set(r.parent_id, []);
        map.get(r.parent_id).push(r);
      }
      return map;
    };
    const filesByParent = byParent(fileRows);
    const markedByParent = byParent(markedRows);
    submissions.forEach((s) => {
      s.attachments = filesByParent.get(s.id) || [];
      s.marked_scripts = markedByParent.get(s.id) || [];
    });

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

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

/**
 * @openapi
 * /api/assignments/stats/overview:
 *   get:
 *     summary: Assignment statistics
 *     description: |
 *       Returns aggregate counts: total assignments, active and
 *       closed counts, total submissions, and total graded
 *       submissions. Teachers only see stats for assignments in
 *       courses they teach; admins and principals see all.
 *       Restricted to admin or teacher roles.
 *     tags:
 *       - Assignments
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Aggregate stats
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Admin or Teacher only }
 *       500: { description: Server error }
 */
router.get('/stats/overview', auth, adminOrTeacher, (req, res) => {
  try {
    let whereClause = '';
    const params = [];

    if (req.user.role === 'teacher') {
      whereClause = ' AND (sub.teacher_id = ?)';
      params.push(req.user.id);
    }

    const stats = getDb().prepare(`
      SELECT
        COUNT(*) as total_assignments,
        SUM(CASE WHEN a.status = 'active' THEN 1 ELSE 0 END) as active,
        SUM(CASE WHEN a.status = 'closed' THEN 1 ELSE 0 END) as closed,
        SUM((SELECT COUNT(*) FROM submissions WHERE assignment_id = a.id)) as total_submissions,
        SUM((SELECT COUNT(*) FROM submissions WHERE assignment_id = a.id AND status = 'graded')) as total_graded
      FROM assignments a
      LEFT JOIN subjects sub ON a.course_id = sub.id
      WHERE a.is_deleted = 0 ${whereClause}
    `).get(...params);

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

module.exports = router;