/**
 * Course Grades Controller
 *
 * Course-level final grades (term/year aggregate, separate from the
 * per-assessment `grades` rows). Before this module shipped, course
 * grades were only read by the dashboard controller; there was no
 * CRUD surface. This controller is the canonical write path and
 * resolves the final letter via the grading-scale chain
 * (rowScale → defaultScale → 'zimsec_olevel').
 *
 * Endpoints:
 *   GET    /api/course-grades                list (auth)
 *   GET    /api/course-grades/:id            single (auth)
 *   POST   /api/course-grades                create (teacher/admin)
 *   PUT    /api/course-grades/:id            update (teacher/admin)
 *   DELETE /api/course-grades/:id            soft delete (admin)
 *
 * Mounted at /api/course-grades in server/src/index.js.
 *
 * Schema columns consumed: course_grades(student_id, course_id, score,
 * letter_grade, grading_scale, term, academic_year, remarks, graded_by,
 * graded_at, ...). `grading_scale` is the per-row override (NULL =
 * inherit school default).
 */

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

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

const {
  letterFor,
  resolveScale,
  isValidScaleKey,
  loadDefaultScaleKey,
  DEFAULT_SCALE_KEY,
  SCALES
} = require('../utils/gradeScales');

// Inline auth + RBAC, matching the codebase convention.
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' });
  }
};

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

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

/**
 * Read the school-wide default grading scale from school_settings.
 * Returns the resolver's DEFAULT_SCALE_KEY when no row is set or the value
 * is invalid (the schema is idempotent and never deletes the row, but
 * be defensive).
 */
const readDefaultScale = () => loadDefaultScaleKey(db);

/**
 * Decorate a course_grades row with the resolved band.
 * - `letter`           : primary display letter
 * - `descriptor`       : secondary text (e.g. "A*" for ZIMSEC O-Level)
 * - `points`           : ZIMSEC numeric points (1–9) or null
 * - `resolved_scale`   : the actual key used
 * - `grade_letter`     : legacy alias of `letter` (kept for older callers)
 */
const decorate = (row, defaultScale) => {
  if (!row) return row;
  const band = letterFor(row.score, row.grading_scale || defaultScale);
  return {
    ...row,
    letter: band.letter,
    descriptor: band.descriptor,
    points: band.points,
    resolved_scale: band.scale,
    resolved_scale_label: band.label,
    grade_letter: band.letter, // legacy alias
  };
};

// ============ LIST ============

/**
 * @openapi
 * /api/course-grades:
 *   get:
 *     summary: List course grades
 *     description: |
 *       Returns course grades joined with student + course names. Each
 *       row carries the resolved letter (via rowScale → school
 *       default). Filterable by student_id, course_id, term, and
 *       academic_year.
 *     tags:
 *       - Course Grades
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: student_id
 *         schema: { type: integer }
 *       - in: query
 *         name: course_id
 *         schema: { type: integer }
 *       - in: query
 *         name: term
 *         schema: { type: string }
 *       - in: query
 *         name: academic_year
 *         schema: { type: string }
 *     responses:
 *       200: { description: Course grade list with resolved letters }
 *       401: { description: Unauthorized }
 */
router.get('/', auth, (req, res) => {
  try {
    const { student_id, course_id, term, academic_year } = req.query;
    let query = `
      SELECT cg.*,
             u.first_name || ' ' || u.last_name AS student_name,
             c.name AS course_name
      FROM course_grades cg
      JOIN users u ON cg.student_id = u.id
      JOIN courses c ON cg.course_id = c.id
      WHERE cg.is_deleted = 0
    `;
    const params = [];
    if (student_id)    { query += ' AND cg.student_id = ?';    params.push(student_id); }
    if (course_id)     { query += ' AND cg.course_id = ?';     params.push(course_id); }
    if (term)          { query += ' AND cg.term = ?';          params.push(term); }
    if (academic_year) { query += ' AND cg.academic_year = ?'; params.push(academic_year); }

    if (req.user.role === 'student') {
      query += ' AND cg.student_id = ?';
      params.push(req.user.id);
    }

    query += ' ORDER BY cg.graded_at DESC';
    const rows = db.prepare(query).all(...params);
    const defaultScale = readDefaultScale();
    res.json(rows.map((r) => decorate(r, defaultScale)));
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ READ ONE ============

/**
 * @openapi
 * /api/course-grades/{id}:
 *   get:
 *     summary: Get a single course grade
 *     description: Returns the row joined with student/course names and the resolved letter.
 *     tags:
 *       - Course Grades
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200: { description: Course grade detail }
 *       404: { description: Not found }
 */
router.get('/:id', auth, (req, res) => {
  try {
    const row = db.prepare(`
      SELECT cg.*,
             u.first_name || ' ' || u.last_name AS student_name,
             c.name AS course_name
      FROM course_grades cg
      JOIN users u ON cg.student_id = u.id
      JOIN courses c ON cg.course_id = c.id
      WHERE cg.id = ? AND cg.is_deleted = 0
    `).get(req.params.id);
    if (!row) return res.status(404).json({ error: 'Course grade not found' });
    if (req.user.role === 'student' && row.student_id !== req.user.id) {
      return res.status(403).json({ error: 'Access denied' });
    }
    res.json(decorate(row, readDefaultScale()));
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ CREATE ============

/**
 * @openapi
 * /api/course-grades:
 *   post:
 *     summary: Create a course grade
 *     description: |
 *       Inserts a new course_grade. The stored `letter_grade` is the
 *       letter resolved from `score` + `grading_scale` (or school
 *       default if grading_scale is omitted). Restricted to
 *       admin/teacher roles.
 *     tags:
 *       - Course Grades
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required: [student_id, course_id, score]
 *             properties:
 *               student_id: { type: integer }
 *               course_id: { type: integer }
 *               score: { type: number, description: "0–100 percentage" }
 *               grading_scale: { type: string, description: "Override (NULL = school default)" }
 *               term: { type: string }
 *               academic_year: { type: string }
 *               remarks: { type: string }
 *     responses:
 *       201: { description: Created course grade }
 *       400: { description: Invalid input }
 *       403: { description: Admin/Teacher only }
 */
router.post('/', auth, adminOrTeacher, (req, res) => {
  try {
    const { student_id, course_id, score, grading_scale, term, academic_year, remarks } = req.body;
    if (!student_id || !course_id || score === undefined) {
      return res.status(400).json({ error: 'student_id, course_id, and score are required' });
    }
    if (grading_scale != null && !isValidScaleKey(grading_scale)) {
      return res.status(400).json({ error: `Invalid grading_scale: ${grading_scale}` });
    }

    const defaultScale = readDefaultScale();
    const band = letterFor(score, grading_scale || defaultScale);

    const uid = uuidv4();
    const result = db.prepare(`
      INSERT INTO course_grades (
        uid, student_id, course_id, score, letter_grade, grading_scale,
        term, academic_year, remarks, graded_by, graded_at, sync_status
      )
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, datetime('now', 'localtime'), 'pending')
    `).run(
      uid,
      student_id,
      course_id,
      score,
      band.letter,
      grading_scale || null,
      term,
      academic_year,
      remarks,
      req.user.id
    );

    const created = db.prepare(`
      SELECT cg.*,
             u.first_name || ' ' || u.last_name AS student_name,
             c.name AS course_name
      FROM course_grades cg
      JOIN users u ON cg.student_id = u.id
      JOIN courses c ON cg.course_id = c.id
      WHERE cg.id = ?
    `).get(result.lastInsertRowid);
    res.status(201).json(decorate(created, defaultScale));
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ UPDATE ============

/**
 * @openapi
 * /api/course-grades/{id}:
 *   put:
 *     summary: Update a course grade
 *     description: |
 *       Updates `score`, `grading_scale`, `term`, `academic_year`, or
 *       `remarks`. The stored `letter_grade` is recomputed from the
 *       new score + scale. Restricted to admin/teacher roles.
 *     tags:
 *       - Course Grades
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               score: { type: number }
 *               grading_scale: { type: string }
 *               term: { type: string }
 *               academic_year: { type: string }
 *               remarks: { type: string }
 *     responses:
 *       200: { description: Updated course grade }
 *       400: { description: Invalid input }
 *       403: { description: Admin/Teacher only }
 *       404: { description: Not found }
 */
router.put('/:id', auth, adminOrTeacher, (req, res) => {
  try {
    const existing = db.prepare(
      'SELECT * FROM course_grades WHERE id = ? AND is_deleted = 0'
    ).get(req.params.id);
    if (!existing) return res.status(404).json({ error: 'Course grade not found' });

    const { score, grading_scale, term, academic_year, remarks } = req.body;
    if (grading_scale != null && !isValidScaleKey(grading_scale)) {
      return res.status(400).json({ error: `Invalid grading_scale: ${grading_scale}` });
    }

    const defaultScale = readDefaultScale();
    const effectiveScale = grading_scale !== undefined
      ? (grading_scale || existing.grading_scale)
      : existing.grading_scale;
    const effectiveScore = score !== undefined ? score : existing.score;
    const band = letterFor(effectiveScore, effectiveScale || defaultScale);

    db.prepare(`
      UPDATE course_grades SET
        score         = COALESCE(?, score),
        grading_scale = ?,
        letter_grade  = ?,
        term          = COALESCE(?, term),
        academic_year = COALESCE(?, academic_year),
        remarks       = COALESCE(?, remarks),
        graded_by     = ?,
        graded_at     = datetime('now', 'localtime'),
        updated_at    = datetime('now', 'localtime'),
        sync_status   = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(
      score !== undefined ? score : null,
      grading_scale !== undefined ? grading_scale : existing.grading_scale,
      band.letter,
      term,
      academic_year,
      remarks,
      req.user.id,
      req.params.id
    );

    const updated = db.prepare(`
      SELECT cg.*,
             u.first_name || ' ' || u.last_name AS student_name,
             c.name AS course_name
      FROM course_grades cg
      JOIN users u ON cg.student_id = u.id
      JOIN courses c ON cg.course_id = c.id
      WHERE cg.id = ?
    `).get(req.params.id);
    res.json(decorate(updated, defaultScale));
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ DELETE ============

/**
 * @openapi
 * /api/course-grades/{id}:
 *   delete:
 *     summary: Soft-delete a course grade
 *     description: Marks the row as deleted (`is_deleted = 1`). Admin only.
 *     tags:
 *       - Course Grades
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200: { description: Deleted }
 *       403: { description: Admin only }
 */
router.delete('/:id', auth, adminOnly, (req, res) => {
  try {
    db.prepare(`
      UPDATE course_grades
      SET is_deleted = 1,
          updated_at = datetime('now', 'localtime'),
          sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(req.params.id);
    res.json({ success: true });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

module.exports = router;
