/**
 * Marks Controller
 * Bulk CSV marks upload + read endpoints.
 *
 * Endpoints:
 *   POST /api/marks/bulk   (teacher/admin only) — multipart upload, field "file"
 *                           Parses CSV (assignment_id,student_id,score,feedback)
 *                           Validates per row; upserts into submissions.
 *                           Returns { inserted, updated, errors, total_rows }
 *   GET  /api/marks        — list submissions (filters: assignment_id, student_id)
 *                           Students see their own only.
 */

const express = require('express');
const router = express.Router();
const path = require('path');
const multer = require('multer');
const { parse } = require('csv-parse/sync');
const { v4: uuidv4 } = require('uuid');
const AuditService = require('../services/AuditService');

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' });
  }
};

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

// multer in-memory upload, 5MB cap, permissive CSV/text mime allowlist
const upload = multer({
  storage: multer.memoryStorage(),
  limits: { fileSize: 5 * 1024 * 1024 },
  fileFilter: (req, file, cb) => {
    const ok = [
      'text/csv',
      'application/csv',
      'application/vnd.ms-excel',          // often sent by browsers for .csv
      'text/plain',
      'application/octet-stream',          // generic fallback for CSV
      '',
    ].includes(file.mimetype);
    if (!ok) return cb(new Error(`Unsupported mime type: ${file.mimetype}`));
    cb(null, true);
  },
});

// ============ BULK UPLOAD ============

/**
 * @openapi
 * /api/marks/bulk:
 *   post:
 *     summary: Bulk upload marks via CSV
 *     description: |
 *       Accepts a multipart upload under the `file` field (max 5 MB).
 *       Parses the CSV with header row
 *       `assignment_id, student_id, score, feedback`. Each row is
 *       validated against pre-loaded assignments and students,
 *       then upserted into `submissions` inside a single transaction.
 *       Returns counts of inserts/updates plus per-row errors.
 *       Restricted to admin or teacher roles.
 *     tags:
 *       - Marks
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         multipart/form-data:
 *           schema:
 *             type: object
 *             required:
 *               - file
 *             properties:
 *               file:
 *                 type: string
 *                 format: binary
 *                 description: CSV file with header row assignment_id, student_id, score, feedback
 *     responses:
 *       200:
 *         description: Bulk upload result
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 inserted: { type: integer }
 *                 updated: { type: integer }
 *                 errors:
 *                   type: array
 *                   items: { type: object }
 *                 total_rows: { type: integer }
 *       400:
 *         description: Missing file, CSV parse error, or per-row validation failures
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Admin or Teacher only }
 *       500: { description: Server error }
 */
router.post('/bulk', auth, adminOrTeacher, (req, res, next) => {
  upload.single('file')(req, res, (err) => {
    if (err) return res.status(400).json({ error: err.message });
    next();
  });
}, async (req, res) => {
  try {
    if (!req.file) {
      return res.status(400).json({ error: 'No file uploaded under field "file"' });
    }

    let rows;
    try {
      rows = parse(req.file.buffer, {
        columns: true,
        skip_empty_lines: true,
        trim: true,
        bom: true,
      });
    } catch (e) {
      return res.status(400).json({ error: 'CSV parse error: ' + e.message });
    }

    if (!Array.isArray(rows) || rows.length === 0) {
      return res.status(400).json({ error: 'CSV contained no data rows' });
    }

    // Explicit header check. csv-parse with `columns: true` would otherwise
    // silently fall through to row-level errors (every row says "missing
    // assignment_id") which is unhelpful when the teacher uploaded the
    // wrong export. Return a clear 400 naming the expected columns.
    const EXPECTED_HEADERS = ['assignment_id', 'student_id', 'score', 'feedback'];
    const actualHeaders = Object.keys(rows[0] || {});
    const missing = EXPECTED_HEADERS.filter(h => !actualHeaders.includes(h));
    if (missing.length > 0) {
      return res.status(400).json({
        error: `CSV header is missing required column(s): ${missing.join(', ')}`,
        expected: EXPECTED_HEADERS,
        actual: actualHeaders,
      });
    }

    // Pre-cache assignments + students to avoid N+1 lookups
    const assignmentIds = [...new Set(rows.map(r => r.assignment_id).filter(Boolean))];
    const studentIds    = [...new Set(rows.map(r => r.student_id).filter(Boolean))];

    const assignmentMap = {};
    if (assignmentIds.length > 0) {
      const placeholders = assignmentIds.map(() => '?').join(',');
      const stmt = db.prepare(`SELECT id, max_score FROM assignments WHERE id IN (${placeholders}) AND is_deleted = 0`);
      for (const a of stmt.all(...assignmentIds)) assignmentMap[String(a.id)] = a;
    }

    const studentMap = {};
    if (studentIds.length > 0) {
      const placeholders = studentIds.map(() => '?').join(',');
      const stmt = db.prepare(`SELECT id, role FROM users WHERE id IN (${placeholders}) AND is_deleted = 0`);
      for (const s of stmt.all(...studentIds)) studentMap[String(s.id)] = s;
    }

    // Prepared statements for upsert
    const findExisting = db.prepare(`
      SELECT id FROM submissions
      WHERE assignment_id = ? AND student_id = ? AND is_deleted = 0
    `);
    const insertSubmission = db.prepare(`
      INSERT INTO submissions (
        uid, assignment_id, student_id, status, grade, feedback,
        graded_by, graded_at, sync_status
      ) VALUES (?, ?, ?, 'graded', ?, ?, ?, datetime('now', 'localtime'), 'pending')
    `);
    const updateSubmission = db.prepare(`
      UPDATE submissions SET
        grade      = ?,
        feedback   = ?,
        graded_by  = ?,
        graded_at  = datetime('now', 'localtime'),
        status     = 'graded',
        updated_at = datetime('now', 'localtime'),
        sync_status = 'pending'
      WHERE id = ?
    `);

    const errors = [];
    let inserted = 0;
    let updated = 0;

    // All-or-nothing-on-error-per-row: wrap the whole loop in a transaction
    // so partial valid rows don't leave dangling state if the caller wants
    // atomicity. (Per-row errors are still reported individually.)
    const tx = db.transaction((records) => {
      let ins = 0, upd = 0, errs = [];
      for (let i = 0; i < records.length; i++) {
        const r = records[i];
        const rowNum = i + 2; // header is row 1
        try {
          const assignmentId = r.assignment_id;
          const studentId    = r.student_id;
          const scoreRaw     = r.score;
          const feedback     = r.feedback ?? null;

          if (!assignmentId) throw new Error('missing assignment_id');
          if (!studentId)    throw new Error('missing student_id');
          if (scoreRaw === undefined || scoreRaw === null || scoreRaw === '')
            throw new Error('missing score');

          const score = Number(scoreRaw);
          if (!Number.isFinite(score)) throw new Error(`score is not numeric: ${scoreRaw}`);

          const a = assignmentMap[String(assignmentId)];
          if (!a) throw new Error(`assignment_id ${assignmentId} not found`);

          const s = studentMap[String(studentId)];
          if (!s) throw new Error(`student_id ${studentId} not found`);
          if (s.role !== 'student') throw new Error(`user ${studentId} is not a student`);

          if (typeof a.max_score === 'number' && score > a.max_score) {
            throw new Error(`score ${score} exceeds assignment.max_score ${a.max_score}`);
          }
          if (score < 0) throw new Error(`score ${score} is negative`);

          const existing = findExisting.get(assignmentId, studentId);
          if (existing) {
            updateSubmission.run(score, feedback, req.user.id, existing.id);
            upd++;
          } else {
            const uid = uuidv4();
            insertSubmission.run(uid, assignmentId, studentId, score, feedback, req.user.id);
            ins++;
          }
        } catch (e) {
          errs.push({ row: rowNum, reason: e.message, data: r });
        }
      }
      return { ins, upd, errs };
    });

    const result = tx(rows);
    inserted = result.ins;
    updated = result.upd;
    errors.push(...result.errs);

    // Best-effort audit (do not fail the response on audit error)
    try {
      await AuditService.log(req, 'BULK_UPLOAD_MARKS', {
        entityType: 'submission',
        entityId: null,
        newData: {
          total_rows: rows.length,
          inserted,
          updated,
          error_count: errors.length,
          filename: req.file.originalname,
        },
      });
    } catch (auditErr) {
      // AuditService.log already swallows internally; this catch is a safety net.
      console.error('Audit log failed (non-fatal):', auditErr.message);
    }

    res.json({
      inserted,
      updated,
      errors,
      total_rows: rows.length,
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ LIST MARKS ============

/**
 * @openapi
 * /api/marks:
 *   get:
 *     summary: List marks (submissions)
 *     description: |
 *       Returns graded submissions joined with assignment, student,
 *       and grader names. Filterable by `assignment_id` and
 *       `student_id`. Students only see their own submissions.
 *     tags:
 *       - Marks
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: assignment_id
 *         schema: { type: integer }
 *       - in: query
 *         name: student_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('/', auth, (req, res) => {
  try {
    const { assignment_id, student_id } = req.query;
    let query = `
      SELECT s.*, a.title AS assignment_title, a.max_score,
             u.first_name AS student_first_name, u.last_name AS student_last_name,
             g.first_name AS grader_first_name, g.last_name  AS grader_last_name
      FROM submissions s
      JOIN assignments a ON s.assignment_id = a.id
      JOIN users u ON s.student_id = u.id
      LEFT JOIN users g ON s.graded_by = g.id
      WHERE s.is_deleted = 0
    `;
    const params = [];

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

    // Students can only see their own
    if (req.user.role === 'student') {
      query += ' AND s.student_id = ?';
      params.push(req.user.id);
    }

    query += ' ORDER BY s.graded_at DESC, s.created_at DESC';
    const rows = db.prepare(query).all(...params);
    res.json(rows);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

module.exports = router;