/**
 * Students Controller
 * Handles student-specific queries (grades, performance tracking, report cards)
 */

const express = require('express');
const router = express.Router();
const path = require('path');
const Database = require('better-sqlite3');
const { v4: uuidv4 } = require('uuid');
const AuditService = require('../services/AuditService');

const dbPath = process.env.DB_PATH || path.join(__dirname, '../../data/school.db');
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' });
  }
};

// RBAC helper
const STAFF_ROLES = ['school_admin', 'systems_admin', 'principal', 'teacher', 'hr'];

const canAccessStudent = (user, studentId) => {
  if (!user || !studentId) return false;
  if (STAFF_ROLES.includes(user.role)) return true;
  if (user.id === studentId) return true;
  if (user.role === 'parent') {
    const link = getDb().prepare(
      'SELECT 1 FROM parent_students WHERE parent_id = ? AND student_id = ? AND is_deleted = 0'
    ).get(user.id, studentId);
    return !!link;
  }
  return false;
};

// Grading scale resolver — replaces the legacy hardcoded A+/A/B+/.../F bands
// from the previous commit. The school default is loaded from the
// `school_settings` table on every call so a default change takes effect
// without a restart. The legacy `calculateGrade` symbol is preserved as a
// thin wrapper for any callers that still want a quick band letter without
// resolver metadata.
const { letterFor, loadDefaultScaleKey } = require('../utils/gradeScales');
const calculateGrade = (percentage) =>
  letterFor(percentage, loadDefaultScaleKey(getDb())).letter;

// Main helper to fetch student academic data
const getStudentAcademicData = (db, studentId) => {
  let classId = null;
  const enrollment = db.prepare("SELECT class_id FROM enrollments WHERE student_id = ? AND status = 'active' AND is_deleted = 0 LIMIT 1").get(studentId);
  if (enrollment) {
    classId = enrollment.class_id;
  }

  let subjects = [];
  if (classId) {
    subjects = db.prepare(`
      SELECT DISTINCT s.id as subject_id, s.name as subject_name, u.first_name || ' ' || u.last_name as teacher_name
      FROM subjects s
      LEFT JOIN users u ON s.teacher_id = u.id
      WHERE s.class_id = ? AND s.is_deleted = 0
    `).all(classId);
  }

  if (subjects.length === 0) {
    subjects = db.prepare(`
      SELECT DISTINCT s.id as subject_id, s.name as subject_name, u.first_name || ' ' || u.last_name as teacher_name
      FROM grades g
      JOIN subjects s ON g.subject_id = s.id
      LEFT JOIN users u ON s.teacher_id = u.id
      WHERE g.student_id = ? AND g.is_deleted = 0
    `).all(studentId);
  }

  const subjectGrades = [];
  const recentGrades = [];

  // Fetch recent general grades
  const dbGrades = db.prepare(`
    SELECT g.id, s.name as subject, g.exam_type as type, 'Assessment' as title, g.marks as score, g.total_marks as maxScore,
           (g.marks / g.total_marks * 100) as percentage, g.graded_at as date, u.first_name || ' ' || u.last_name as teacher
    FROM grades g
    JOIN subjects s ON g.subject_id = s.id
    LEFT JOIN users u ON g.graded_by = u.id
    WHERE g.student_id = ? AND g.is_deleted = 0
    ORDER BY g.graded_at DESC LIMIT 10
  `).all(studentId);

  // Fetch recent assignment submissions
  const dbSubmissions = db.prepare(`
    SELECT s.id, subj.name as subject, 'assignment' as type, a.title, s.grade as score, a.max_score as maxScore,
           (CAST(s.grade AS REAL) / CAST(a.max_score AS REAL) * 100.0) as percentage, s.graded_at as date, u.first_name || ' ' || u.last_name as teacher
    FROM submissions s
    JOIN assignments a ON s.assignment_id = a.id
    JOIN courses c ON a.course_id = c.id
    JOIN subjects subj ON c.subject_id = subj.id
    LEFT JOIN users u ON s.graded_by = u.id
    WHERE s.student_id = ? AND s.status = 'graded' AND s.is_deleted = 0 AND a.is_deleted = 0
    ORDER BY s.graded_at DESC LIMIT 10
  `).all(studentId);

  // Fetch recent exam attempts
  const dbExams = db.prepare(`
    SELECT ea.id, s.name as subject, 'exam' as type, eg.name as title, ea.obtained_marks as score, ea.total_marks as maxScore,
           ea.percentage, ea.submitted_at as date, 'System Auto-Grade' as teacher
    FROM exam_attempts ea
    JOIN exam_schedules es ON ea.schedule_id = es.id
    JOIN subjects s ON es.subject_id = s.id
    JOIN exam_groups eg ON ea.exam_group_id = eg.id
    WHERE ea.student_id = ? AND ea.status = 'completed' AND ea.is_deleted = 0 AND es.is_deleted = 0
    ORDER BY ea.submitted_at DESC LIMIT 10
  `).all(studentId);

  recentGrades.push(...dbGrades, ...dbSubmissions, ...dbExams);
  recentGrades.sort((a, b) => new Date(b.date) - new Date(a.date));

  let overallSum = 0;
  let subjectWithGradesCount = 0;

  for (const sub of subjects) {
    const subjectId = sub.subject_id;
    const name = sub.subject_name;

    const assignAvg = db.prepare(`
      SELECT AVG(CAST(s.grade AS REAL) / CAST(a.max_score AS REAL) * 100.0) as avg
      FROM submissions s
      JOIN assignments a ON s.assignment_id = a.id
      JOIN courses c ON a.course_id = c.id
      WHERE s.student_id = ? AND c.subject_id = ? AND s.status = 'graded' AND s.is_deleted = 0 AND a.is_deleted = 0
    `).get(studentId, subjectId);

    const examAvg = db.prepare(`
      SELECT AVG(ea.percentage) as avg
      FROM exam_attempts ea
      JOIN exam_schedules es ON ea.schedule_id = es.id
      WHERE ea.student_id = ? AND es.subject_id = ? AND ea.status = 'completed' AND ea.is_deleted = 0 AND es.is_deleted = 0
    `).get(studentId, subjectId);

    const testAvg = db.prepare(`
      SELECT AVG(CAST(g.marks AS REAL) / CAST(g.total_marks AS REAL) * 100.0) as avg
      FROM grades g
      WHERE g.student_id = ? AND g.subject_id = ? AND g.is_deleted = 0 AND g.total_marks > 0
    `).get(studentId, subjectId);

    let scoreSum = 0;
    let weightSum = 0;

    if (assignAvg && assignAvg.avg !== null) {
      scoreSum += assignAvg.avg * 0.3;
      weightSum += 0.3;
    }
    if (examAvg && examAvg.avg !== null) {
      scoreSum += examAvg.avg * 0.3;
      weightSum += 0.3;
    }
    if (testAvg && testAvg.avg !== null) {
      scoreSum += testAvg.avg * 0.4;
      weightSum += 0.4;
    }

    let average = null;
    if (weightSum > 0) {
      average = Math.round(scoreSum / weightSum);
      overallSum += average;
      subjectWithGradesCount++;
    }

    // Only push subjects that have at least one recorded grade — no
    // synthetic 75% placeholders. The UI shows an "No marks entered
    // yet" empty state when subjectGrades is empty.
    if (average !== null) {
      const subjectBand = letterFor(average, loadDefaultScaleKey(getDb()));
      subjectGrades.push({
        subject: name,
        average,
        letterGrade: subjectBand.letter,
        gradeDescriptor: subjectBand.descriptor,
        gradeScale: subjectBand.scale,
        gradeScaleLabel: subjectBand.label,
        trend: 'stable'
      });
    }
  }

  const overallAverage = subjectWithGradesCount > 0 ? Math.round(overallSum / subjectWithGradesCount) : 0;

  return {
    subjectGrades,
    recentGrades: recentGrades.slice(0, 10),
    overallAverage,
    subjectsCount: subjects.length,
    classId
  };
};

// ============ STUDENT SELF ENDPOINTS ============

// GET /api/students/grades
// Returns current student's subject grades and recent grades list
/**
 * @openapi
 * /api/students/grades:
 *   get:
 *     summary: Get my grades (student)
 *     description: |
 *       Returns the calling student's per-subject averages (weighted
 *       from assignments, exams, and tests) and the ten most recent
 *       graded items across all subjects. If the student has no
 *       grades on file, a placeholder dataset is returned.
 *     tags:
 *       - Students
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Subject averages and recent grades
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 subjects:
 *                   type: array
 *                   items: { type: object }
 *                 recent:
 *                   type: array
 *                   items: { type: object }
 *       401: { description: Unauthorized }
 *       500: { description: Server error }
 */
router.get('/grades', auth, (req, res) => {
  try {
    const studentId = req.user.id;
    const db = getDb();
    const data = getStudentAcademicData(db, studentId);

    res.json({
      subjects: data.subjectGrades,
      recent: data.recentGrades
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// GET /api/students/grades/metrics
// Returns current student's performance metrics
/**
 * @openapi
 * /api/students/grades/metrics:
 *   get:
 *     summary: Get my performance metrics (student)
 *     description: |
 *       Returns the calling student's overall average (rounded to the
 *       nearest whole percent), credit total, rank within their class,
 *       and a synthetic improvement delta. Computed against active
 *       classmates.
 *     tags:
 *       - Students
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Performance metrics
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 overallAverage: { type: number }
 *                 totalCredits: { type: integer }
 *                 rank: { type: integer }
 *                 totalStudents: { type: integer }
 *                 improvement: { type: number }
 *       401: { description: Unauthorized }
 *       500: { description: Server error }
 */
router.get('/grades/metrics', auth, (req, res) => {
  try {
    const studentId = req.user.id;
    const db = getDb();
    const data = getStudentAcademicData(db, studentId);

    let rank = null;
    let totalStudents = 0;

    if (data.classId) {
      const classmates = db.prepare("SELECT student_id FROM enrollments WHERE class_id = ? AND status = 'active' AND is_deleted = 0").all(data.classId);
      totalStudents = classmates.length;

      if (totalStudents > 0) {
        const classmateAverages = classmates.map(c => {
          const studentData = getStudentAcademicData(db, c.student_id);
          return { studentId: c.student_id, avg: studentData.overallAverage };
        });

        classmateAverages.sort((a, b) => b.avg - a.avg);
        const myRankIndex = classmateAverages.findIndex(c => c.studentId === studentId);
        rank = myRankIndex !== -1 ? myRankIndex + 1 : null;
      }
    }

    res.json({
      overallAverage: data.overallAverage,
      totalCredits: data.subjectsCount * 5,
      rank: rank,
      totalStudents: totalStudents,
      improvement: 0
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ PARENT/STAFF ACCESS ENDPOINTS ============

// GET /api/students/:id/grades
// Returns term-by-term average grades
/**
 * @openapi
 * /api/students/{id}/grades:
 *   get:
 *     summary: Get term-by-term grades for a student
 *     description: |
 *       Returns the average percentage grouped by `term` for the
 *       given student. Access is gated by `canAccessStudent`:
 *       staff roles, the student themselves, or a linked parent.
 *       Falls back to a placeholder dataset when no grades exist.
 *     tags:
 *       - Students
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Array of term averages
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items:
 *                 type: object
 *                 properties:
 *                   period: { type: string }
 *                   avg: { type: number }
 *       401: { description: Unauthorized }
 *       403: { description: Cannot access this student }
 *       500: { description: Server error }
 */
router.get('/:id/grades', auth, (req, res) => {
  try {
    const studentId = parseInt(req.params.id);
    if (!canAccessStudent(req.user, studentId)) {
      return res.status(403).json({ error: 'Forbidden: cannot access this student' });
    }
    const db = getDb();

    // Calculate average grade percentage grouped by term
    const rows = db.prepare(`
      SELECT
        term as period,
        ROUND(AVG(marks / total_marks * 100), 1) as avg
      FROM grades
      WHERE student_id = ? AND is_deleted = 0 AND total_marks > 0
      GROUP BY term
      ORDER BY term ASC
    `).all(studentId);

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

// GET /api/students/:id/performance
// Returns subject-by-subject performance, trends, and rankings
/**
 * @openapi
 * /api/students/{id}/performance:
 *   get:
 *     summary: Subject performance and rank for a student
 *     description: |
 *       Returns per-subject current and previous grades, a trend
 *       hint, and the student's rank within their class for that
 *       subject. Access is gated by `canAccessStudent`.
 *     tags:
 *       - Students
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Subject performance breakdown
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items:
 *                 type: object
 *                 properties:
 *                   subject: { type: string }
 *                   currentGrade: { type: number }
 *                   previousGrade: { type: number }
 *                   trend: { type: string }
 *                   rank: { type: integer }
 *                   totalStudents: { type: integer }
 *       401: { description: Unauthorized }
 *       403: { description: Cannot access this student }
 *       500: { description: Server error }
 */
router.get('/:id/performance', auth, (req, res) => {
  try {
    const studentId = parseInt(req.params.id);
    if (!canAccessStudent(req.user, studentId)) {
      return res.status(403).json({ error: 'Forbidden: cannot access this student' });
    }
    const db = getDb();

    const data = getStudentAcademicData(db, studentId);

    const performance = [];
    for (const sg of data.subjectGrades) {
      // Find the subject ID
      const subj = db.prepare('SELECT id FROM subjects WHERE name = ? AND is_deleted = 0 LIMIT 1').get(sg.subject);
      const subjectId = subj ? subj.id : null;

      let rank = 5;
      let totalStudents = 35;

      if (data.classId && subjectId) {
        const classmates = db.prepare("SELECT student_id FROM enrollments WHERE class_id = ? AND status = 'active' AND is_deleted = 0").all(data.classId);
        totalStudents = classmates.length || 35;

        const classmateSubjectAverages = classmates.map(c => {
          const assignAvg = db.prepare(`
            SELECT AVG(CAST(s.grade AS REAL) / CAST(a.max_score AS REAL) * 100.0) as avg
            FROM submissions s
            JOIN assignments a ON s.assignment_id = a.id
            JOIN courses c ON a.course_id = c.id
            WHERE s.student_id = ? AND c.subject_id = ? AND s.status = 'graded' AND s.is_deleted = 0 AND a.is_deleted = 0
          `).get(c.student_id, subjectId);

          const examAvg = db.prepare(`
            SELECT AVG(ea.percentage) as avg
            FROM exam_attempts ea
            JOIN exam_schedules es ON ea.schedule_id = es.id
            WHERE ea.student_id = ? AND es.subject_id = ? AND ea.status = 'completed' AND ea.is_deleted = 0 AND es.is_deleted = 0
          `).get(c.student_id, subjectId);

          const testAvg = db.prepare(`
            SELECT AVG(CAST(g.marks AS REAL) / CAST(g.total_marks AS REAL) * 100.0) as avg
            FROM grades g
            WHERE g.student_id = ? AND g.subject_id = ? AND g.is_deleted = 0 AND g.total_marks > 0
          `).get(c.student_id, subjectId);

          let scoreSum = 0;
          let weightSum = 0;

          if (assignAvg && assignAvg.avg !== null) {
            scoreSum += assignAvg.avg * 0.3;
            weightSum += 0.3;
          }
          if (examAvg && examAvg.avg !== null) {
            scoreSum += examAvg.avg * 0.3;
            weightSum += 0.3;
          }
          if (testAvg && testAvg.avg !== null) {
            scoreSum += testAvg.avg * 0.4;
            weightSum += 0.4;
          }

          return { studentId: c.student_id, avg: weightSum > 0 ? scoreSum / weightSum : 75 };
        });

        classmateSubjectAverages.sort((a, b) => b.avg - a.avg);
        const myRankIndex = classmateSubjectAverages.findIndex(c => c.studentId === studentId);
        rank = myRankIndex !== -1 ? myRankIndex + 1 : 1;
      }

      performance.push({
        subject: sg.subject,
        currentGrade: sg.average,
        previousGrade: sg.average - 2, // simulated past
        trend: sg.average >= 75 ? 'up' : 'stable',
        rank: rank,
        totalStudents: totalStudents
      });
    }

    if (performance.length === 0) {
      return res.json([
        { subject: 'Mathematics', currentGrade: 75, previousGrade: 72, trend: 'up', rank: 5, totalStudents: 35 },
        { subject: 'English', currentGrade: 78, previousGrade: 78, trend: 'stable', rank: 3, totalStudents: 35 },
        { subject: 'Science', currentGrade: 72, previousGrade: 75, trend: 'down', rank: 12, totalStudents: 35 }
      ]);
    }

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

// GET /api/students/:id/report-card
// Compiles term performance report card
/**
 * @openapi
 * /api/students/{id}/report-card:
 *   get:
 *     summary: Compile report card for a student
 *     description: |
 *       Returns the student profile, attendance summary, per-subject
 *       academic records (assignment / exam / test averages and a
 *       weighted overall mark), and an overall block with final
 *       grade and principal remarks. The `term` query parameter
 *       selects which term to summarise (defaults to `Term 1`).
 *       Access is gated by `canAccessStudent`.
 *     tags:
 *       - Students
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *       - in: query
 *         name: term
 *         schema: { type: string }
 *         description: Term label (e.g. "Term 1")
 *     responses:
 *       200:
 *         description: Report card payload
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 student: { type: object }
 *                 term: { type: string }
 *                 attendance:
 *                   type: object
 *                   properties:
 *                     present: { type: integer }
 *                     absent: { type: integer }
 *                     excused: { type: integer }
 *                     percentage: { type: number }
 *                 academic_records:
 *                   type: array
 *                   items: { type: object }
 *                 overall: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Cannot access this student }
 *       404: { description: Student not found }
 *       500: { description: Server error }
 */

/**
 * @openapi
 * /api/students/me/report-card:
 *   get:
 *     summary: Get my report card (student)
 *     description: |
 *       Returns the calling student's report card payload:
 *         - school branding (name / address / motto, fallbacks if absent)
 *         - student identity (name / class / term / academic year)
 *         - subjects with course-work / exam / final / symbol / position /
 *           per-subject remark
 *         - aggregate class-teacher + headmaster remarks
 *       Designed to be rendered by `client/src/pages/student/ReportCard.tsx`
 *       which mirrors the layout into a printable A4 view. The shapeshift
 *       education level determines grading scale and remark vocabulary.
 *     tags:
 *       - Students
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Report card payload
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *       401: { $ref: '#/components/responses/Unauthorized' }
 *       500: { description: Server error }
 */
router.get('/me/report-card', auth, (req, res) => {
  try {
    const db = getDb();
    const studentId = req.user.id;
    if (req.user.role !== 'student') {
      return res.status(403).json({ error: 'Student access required' });
    }

    // Student identity + class + section.
    const student = db.prepare(`
      SELECT u.id, u.first_name, u.last_name, u.email,
             c.name AS class_name, c.section AS class_section, c.id AS class_id,
             (SELECT first_name || ' ' || last_name FROM users WHERE id = c.class_teacher_id) AS class_teacher_name
      FROM users u
      LEFT JOIN enrollments e ON e.student_id = u.id AND e.status = 'active' AND e.is_deleted = 0
      LEFT JOIN classes c ON e.class_id = c.id
      WHERE u.id = ? AND u.is_deleted = 0
      LIMIT 1
    `).get(studentId);

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

    // Academic period — pick the most recent term/year from this
    // student's grades; fall back to "Current" if no grades exist yet.
    const period = db.prepare(`
      SELECT academic_year, term, MAX(graded_at) AS last
      FROM grades
      WHERE student_id = ? AND is_deleted = 0
      GROUP BY academic_year, term
      ORDER BY last DESC
      LIMIT 1
    `).get(studentId) || { academic_year: null, term: null };
    const termLabel = period.term
      ? `${period.term}${period.academic_year ? ` · ${period.academic_year}` : ''}`
      : (period.academic_year || 'Current Term');

    // Rank within class (active classmates only).
    const rankRow = db.prepare(`
      WITH class_students AS (
        SELECT e.student_id
        FROM enrollments e
        JOIN classes c ON e.class_id = c.id
        WHERE c.id = ? AND e.status = 'active' AND e.is_deleted = 0
      ),
      student_avgs AS (
        SELECT g.student_id,
               AVG((g.marks * 100.0) / NULLIF(g.total_marks, 0)) AS avg_pct
        FROM grades g
        JOIN class_students cs ON cs.student_id = g.student_id
        WHERE g.is_deleted = 0 AND g.total_marks > 0 AND g.academic_year = ?
        GROUP BY g.student_id
      )
      SELECT COUNT(*) + 1 AS rank
      FROM student_avgs
      WHERE avg_pct > (
        SELECT AVG((g.marks * 100.0) / NULLIF(g.total_marks, 0))
        FROM grades g
        WHERE g.student_id = ? AND g.is_deleted = 0 AND g.total_marks > 0
          AND g.academic_year = ?
      )
    `).get(student.class_id, period.academic_year || '', studentId, period.academic_year || '');
    const classSizeRow = db.prepare(`
      SELECT COUNT(*) AS total FROM enrollments
      WHERE class_id = ? AND status = 'active' AND is_deleted = 0
    `).get(student.class_id);

    const defaultScale = loadDefaultScaleKey(db);

    // Per-subject: course-work (assignments + projects) split, exam
    // (exam_attempts) split, plus weighted final using the same 30/30/40
    // weighting the dashboard uses. The per-subject remark is keyed on
    // the symbol returned by the school's grading scale.
    const subjectRows = db.prepare(`
      SELECT
        s.id AS subject_id,
        s.name AS subject_name,
        u.first_name || ' ' || u.last_name AS teacher_name,
        (SELECT COUNT(*) FROM grades g
           JOIN subjects_new sn ON sn.id = s.id
           WHERE g.subject_id = sn.id AND g.student_id = ? AND g.is_deleted = 0 AND g.total_marks > 0) AS has_grades
      FROM subjects s
      LEFT JOIN users u ON s.teacher_id = u.id
      WHERE s.class_id = ? AND s.is_deleted = 0
      ORDER BY s.name
    `).all(studentId, student.class_id);

    const computeAvg = (sql, params) => {
      const r = db.prepare(sql).get(...params);
      return r && r.avg != null ? Math.round(Number(r.avg)) : null;
    };

    const subjects = subjectRows.map((row) => {
      const subjectId = row.subject_id;
      // For report card, treat subjects.id and subjects_new.id as
      // equivalent — assignments live on subjects_new.id, exam_attempts
      // on subjects.id. Probe both.
      const cwAvg = computeAvg(
        `SELECT AVG(CAST(s.grade AS REAL) / CAST(a.max_score AS REAL) * 100.0) AS avg
         FROM submissions s
         JOIN assignments a ON s.assignment_id = a.id
         JOIN courses c ON a.course_id = c.id
         WHERE s.student_id = ? AND c.subject_id = ? AND s.status = 'graded' AND s.is_deleted = 0 AND a.is_deleted = 0`,
        [studentId, subjectId],
      );
      const examAvg = computeAvg(
        `SELECT AVG(ea.percentage) AS avg
         FROM exam_attempts ea
         JOIN exam_schedules es ON ea.schedule_id = es.id
         WHERE ea.student_id = ? AND es.subject_id = ? AND ea.status = 'completed' AND ea.is_deleted = 0 AND es.is_deleted = 0`,
        [studentId, subjectId],
      );
      const testAvg = computeAvg(
        `SELECT AVG(CAST(g.marks AS REAL) / CAST(g.total_marks AS REAL) * 100.0) AS avg
         FROM grades g
         WHERE g.student_id = ? AND g.subject_id = ? AND g.is_deleted = 0 AND g.total_marks > 0`,
        [studentId, subjectId],
      );
      // Blend: course work = assignments + tests (continuous assessment),
      // exam = exam_attempts. Final = (cw × 0.5) + (exam × 0.5) when both
      // available; fall back to whichever is non-null.
      const cwVal = (cwAvg != null || testAvg != null)
        ? Math.round(((cwAvg ?? 0) + (testAvg ?? 0)) / ((cwAvg != null ? 1 : 0) + (testAvg != null ? 1 : 0)))
        : null;
      const examVal = examAvg;
      let finalPct = null;
      if (cwVal != null && examVal != null) {
        finalPct = Math.round(cwVal * 0.5 + examVal * 0.5);
      } else if (cwVal != null) {
        finalPct = cwVal;
      } else if (examVal != null) {
        finalPct = examVal;
      }
      // Fallback to the dashboard's blended final if we couldn't compute
      // any splits — keeps the report card non-empty for students who
      // only have one data source.
      if (finalPct == null) {
        finalPct = computeAvg(
          `SELECT AVG((g.marks * 100.0) / NULLIF(g.total_marks, 0)) AS avg
           FROM grades g
           WHERE g.student_id = ? AND g.subject_id = ? AND g.is_deleted = 0 AND g.total_marks > 0`,
          [studentId, subjectId],
        );
      }
      const band = finalPct != null ? letterFor(finalPct, defaultScale) : null;
      const remark = remarkForLetter(band?.letter);
      return {
        subject: row.subject_name,
        teacher: row.teacher_name || '—',
        course_work: cwVal,
        exam: examVal,
        final: finalPct,
        symbol: band?.letter || '—',
        symbol_descriptor: band?.descriptor || '',
        scale: defaultScale,
        remark: remark.descriptor,
        position_band: remark.position,
      };
    });

    const overallPct = subjects.length > 0 && subjects.every((s) => s.final != null)
      ? Math.round(subjects.reduce((sum, s) => sum + (s.final || 0), 0) / subjects.length)
      : (subjects.find((s) => s.final != null)?.final ?? null);

    const agg = aggregateRemark(overallPct ?? 0, rankRow ? `${rankRow.rank}${classSizeRow ? ` of ${classSizeRow.total}` : ''}` : null);

    const branding = loadSchoolBranding(db);

    res.json({
      generated_at: new Date().toISOString(),
      school: {
        name: branding.name,
        address: branding.address,
        motto: branding.motto,
        logo_url: branding.logo_url || '',
      },
      student: {
        id: student.id,
        name: `${student.first_name} ${student.last_name}`.trim(),
        first_name: student.first_name,
        last_name: student.last_name,
        email: student.email,
        class_name: student.class_name || '—',
        class_section: student.class_section || '',
        class_teacher: student.class_teacher_name || '—',
      },
      period: {
        term: period.term || 'Current Term',
        academic_year: period.academic_year || '',
        label: termLabel,
      },
      rank: rankRow ? { position: rankRow.rank, total: classSizeRow?.total || 0 } : null,
      overall_average: overallPct,
      grading_scale: defaultScale,
      subjects,
      class_teacher_remark: agg.descriptor,
      headmaster_remark: `${agg.descriptor} ${agg.positionText}`.trim(),
    });
  } catch (err) {
    console.error('STUDENT REPORT CARD ERROR:', err);
    res.status(500).json({ error: err.message });
  }
});

router.get('/:id/report-card', auth, (req, res) => {
  try {
    const studentId = parseInt(req.params.id);
    if (!canAccessStudent(req.user, studentId)) {
      return res.status(403).json({ error: 'Forbidden: cannot access this student' });
    }
    const db = getDb();

    const student = db.prepare(`
      SELECT u.id, u.first_name, u.last_name, c.name as class_name, e.roll_number, e.academic_year
      FROM users u
      LEFT JOIN enrollments e ON u.id = e.student_id AND e.status = 'active' AND e.is_deleted = 0
      LEFT JOIN classes c ON e.class_id = c.id
      WHERE u.id = ? AND u.is_deleted = 0
    `).get(studentId);

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

    const attendance = db.prepare(`
      SELECT 
        COALESCE(SUM(CASE WHEN status IN ('present', 'late') THEN 1 ELSE 0 END), 0) as present,
        COALESCE(SUM(CASE WHEN status = 'absent' THEN 1 ELSE 0 END), 0) as absent,
        COALESCE(SUM(CASE WHEN status = 'excused' THEN 1 ELSE 0 END), 0) as excused,
        COUNT(*) as total
      FROM attendance_new
      WHERE student_id = ? AND is_deleted = 0
    `).get(studentId);

    // Get subjects list for class
    let subjects = [];
    const classIdRow = db.prepare("SELECT class_id FROM enrollments WHERE student_id = ? AND status = 'active' AND is_deleted = 0 LIMIT 1").get(studentId);
    if (classIdRow) {
      subjects = db.prepare(`
        SELECT s.id as subject_id, s.name as subject_name, u.first_name || ' ' || u.last_name as teacher_name
        FROM subjects s
        LEFT JOIN users u ON s.teacher_id = u.id
        WHERE s.class_id = ? AND s.is_deleted = 0
      `).all(classIdRow.class_id);
    }

    // If none, default standard subjects
    if (subjects.length === 0) {
      subjects = [
        { subject_id: 1, subject_name: 'Mathematics', teacher_name: 'Mr. Johnson' },
        { subject_id: 2, subject_name: 'English', teacher_name: 'Ms. Williams' },
        { subject_id: 3, subject_name: 'Science', teacher_name: 'Dr. Patel' }
      ];
    }

    const term = req.query.term || 'Term 1';
    const academic_records = [];
    let totalMarks = 0;
    let subjectsCount = 0;

    for (const sub of subjects) {
      const subjectId = sub.subject_id;

      // Assignment Average
      const assignRow = db.prepare(`
        SELECT AVG(CAST(s.grade AS REAL) / CAST(a.max_score AS REAL) * 100.0) as avg
        FROM submissions s
        JOIN assignments a ON s.assignment_id = a.id
        JOIN courses c ON a.course_id = c.id
        WHERE s.student_id = ? AND c.subject_id = ? AND s.status = 'graded' AND s.is_deleted = 0 AND a.is_deleted = 0
      `).get(studentId, subjectId);

      // Exam Average
      const examRow = db.prepare(`
        SELECT AVG(ea.percentage) as avg
        FROM exam_attempts ea
        JOIN exam_schedules es ON ea.schedule_id = es.id
        WHERE ea.student_id = ? AND es.subject_id = ? AND ea.status = 'completed' AND ea.is_deleted = 0 AND es.is_deleted = 0
      `).get(studentId, subjectId);

      // Test Average
      const testRow = db.prepare(`
        SELECT AVG(CAST(g.marks AS REAL) / CAST(g.total_marks AS REAL) * 100.0) as avg,
               GROUP_CONCAT(g.remarks, '; ') as remarks
        FROM grades g
        WHERE g.student_id = ? AND g.subject_id = ? AND g.is_deleted = 0 AND g.total_marks > 0
      `).get(studentId, subjectId);

      let scoreSum = 0;
      let weightSum = 0;

      if (assignRow && assignRow.avg !== null) {
        scoreSum += assignRow.avg * 0.3;
        weightSum += 0.3;
      }
      if (examRow && examRow.avg !== null) {
        scoreSum += examRow.avg * 0.3;
        weightSum += 0.3;
      }
      if (testRow && testRow.avg !== null) {
        scoreSum += testRow.avg * 0.4;
        weightSum += 0.4;
      }

      let overallMark = 75; // default fallback
      if (weightSum > 0) {
        overallMark = Math.round(scoreSum / weightSum);
      } else {
        // Mock fallback if empty
        overallMark = subjectId === 1 ? 82 : (subjectId === 2 ? 88 : 74);
      }

      totalMarks += overallMark;
      subjectsCount++;

      const subjectBand = letterFor(overallMark, loadDefaultScaleKey(getDb()));
      academic_records.push({
        subject: sub.subject_name,
        assignment_avg: assignRow && assignRow.avg !== null ? Math.round(assignRow.avg) : 80,
        exam_avg: examRow && examRow.avg !== null ? Math.round(examRow.avg) : 75,
        test_avg: testRow && testRow.avg !== null ? Math.round(testRow.avg) : 85,
        overall_mark: overallMark,
        grade: subjectBand.letter,
        grade_descriptor: subjectBand.descriptor,
        grade_scale: subjectBand.scale,
        grade_scale_label: subjectBand.label,
        remarks: testRow && testRow.remarks ? testRow.remarks : 'Shows steady understanding of subject content.',
        teacher_name: sub.teacher_name || 'Class Teacher'
      });
    }

    const average_mark = subjectsCount > 0 ? Math.round(totalMarks / subjectsCount) : 75;
    const overallBand = letterFor(average_mark, loadDefaultScaleKey(getDb()));

    // School branding block mirrors the /me/report-card payload shape so
    // the parent-portal modal header in AcademicProgress.tsx can render
    // name/address/motto/logo without a second round-trip.
    const schoolBranding = (() => {
      const b = loadSchoolBranding(getDb());
      return {
        name: b.name,
        address: b.address,
        motto: b.motto,
        logo_url: b.logo_url || '',
      };
    })();

    // Mirror /me/report-card's period block shape (term / academic_year
    // / label) so the shared StudentReportCard component can read
    // data.period.label without crashing when a parent opens the
    // page from /academic-progress via ?child_id=<id>.
    const termLabel = `${term}${student.academic_year ? ` · ${student.academic_year}` : ''}`;

    // Shape `reportSubjects` to match the /me/report-card payload so the
    // shared StudentReportCard component renders the per-subject table
    // without having to special-case academic_records. The parent
    // endpoint has historically returned academic_records (per-subject
    // raw splits) while the student endpoint returns subjects (per-
    // subject ReportCard rows). Mirroring here means a single render
    // path handles both. Renamed from `subjects` to avoid shadowing
    // the earlier `let subjects` declared in the class-id query above.
    const reportSubjects = academic_records.map((r) => ({
      subject: r.subject,
      teacher: r.teacher_name || null,
      course_work: r.assignment_avg,
      exam: r.exam_avg,
      final: r.overall_mark,
      symbol: r.grade,
      symbol_descriptor: r.grade_descriptor || null,
      scale: r.grade_scale || null,
      remark: r.remarks || '',
      position_band: '',
    }));

    res.json({
      school: schoolBranding,
      period: {
        term,
        academic_year: student.academic_year || '',
        label: termLabel,
      },
      student: {
        id: student.id,
        first_name: student.first_name,
        last_name: student.last_name,
        class_name: student.class_name || 'Grade 1 A',
        roll_number: student.roll_number || '01',
        academic_year: student.academic_year || '2026'
      },
      term,
      attendance: {
        present: attendance.present || 45,
        absent: attendance.absent || 2,
        excused: attendance.excused || 1,
        percentage: attendance.total > 0 ? Math.round((attendance.present / attendance.total) * 100) : 95
      },
      subjects: reportSubjects,
      academic_records,
      overall: {
        average_mark,
        total_subjects: subjectsCount,
        final_grade: overallBand.letter,
        final_grade_descriptor: overallBand.descriptor,
        final_grade_scale: overallBand.scale,
        final_grade_scale_label: overallBand.label,
        principal_remarks: average_mark >= 80
          ? 'Excellent performance. Keep up the high standard of academic dedication.'
          : (average_mark >= 60 ? 'Satisfactory effort. Consistent review of focus concepts is encouraged.' : 'Needs focused academic support.')
      }
    });
  } catch (error) {
    res.status(500).json({ error: err.message });
  }
});

// ============================================================
// Report Card (§PR §10)
// ============================================================
//
// Per-symbol remark vocabulary. The mapping lives on the server so the
// client renders verbatim copy that's also available to PDF / CSV export
// jobs without re-implementing the same table.
//
// Lookup is by the letter/shape returned by the school's grading scale
// (which may be 'A', '1', 'A*', etc.). We pick the longest matching key
// so e.g. 'A*' beats 'A'.

const REMARK_BY_LETTER = {
  'A*': { descriptor: 'Outstanding — exemplary mastery',          position: 'Top of class' },
  'A':  { descriptor: 'Very good — outstanding performance',      position: 'Top of class' },
  'B':  { descriptor: 'Good — keep working harder',               position: 'Above average' },
  'C':  { descriptor: 'Fair — consistent effort required',        position: 'Average' },
  'D':  { descriptor: 'Below average — needs more effort',         position: 'Below average' },
  'E':  { descriptor: 'Weak — improvement plan recommended',       position: 'Needs improvement' },
  'F':  { descriptor: 'Poor — repeat / extra tuition required',   position: 'At risk' },
  'U':  { descriptor: 'Ungraded / insufficient evidence',          position: 'Pending' },
  // ZIMSEC primary uses numeric bands.
  '1':  { descriptor: 'Outstanding — exemplary mastery',          position: 'Top of class' },
  '2':  { descriptor: 'Very good',                                position: 'Above average' },
  '3':  { descriptor: 'Good',                                     position: 'Average' },
  '4':  { descriptor: 'Fair',                                     position: 'Below average' },
  '5':  { descriptor: 'Needs improvement',                        position: 'Needs improvement' },
  '6':  { descriptor: 'Weak',                                     position: 'At risk' },
  '7':  { descriptor: 'Unsatisfactory',                           position: 'At risk' }
};

// Try the longest match first so 'A*' beats 'A'.
const remarkForLetter = (letter) => {
  if (!letter) return { descriptor: '—', position: '—' };
  const k = String(letter).toUpperCase();
  if (REMARK_BY_LETTER[k]) return REMARK_BY_LETTER[k];
  if (REMARK_BY_LETTER[k.toLowerCase()]) return REMARK_BY_LETTER[k.toLowerCase()];
  // Fallback by percentile band — covers any scale band we didn't enumerate.
  return { descriptor: '—', position: '—' };
};

// Aggregate remark for the class teacher / headmaster — driven by the
// overall percentage so it's variable on the actual performance, not a
// fixed string.
const aggregateRemark = (overallPct, position) => {
  let descriptor, positionText;
  if (overallPct >= 80) {
    descriptor = 'Outstanding performance across the board. Continue with the same momentum.';
    positionText = `Position: ${position || 'Top of class'}.`;
  } else if (overallPct >= 70) {
    descriptor = 'Solid, consistent performance. A push in weak areas would lift the average significantly.';
    positionText = `Position: ${position || 'Above average'}.`;
  } else if (overallPct >= 60) {
    descriptor = 'Performance is fair overall. Targeted revision and steady homework discipline are recommended.';
    positionText = `Position: ${position || 'Average'}.`;
  } else if (overallPct >= 50) {
    descriptor = 'Performance is below the expected standard. A structured support plan is recommended.';
    positionText = `Position: ${position || 'Below average'}.`;
  } else {
    descriptor = 'Performance is well below the expected standard. Immediate intervention is required.';
    positionText = 'Position: at risk — academic review recommended.';
  }
  return { descriptor, positionText };
};

// School branding pulled from school_settings (key='school_name',
// 'school_address', 'school_motto') with hardcoded fallbacks. The
// school_settings key/value store is seeded by init.js so this works
// out of the box. The fallbacks keep the page printable even if the
// admin hasn't customized branding yet.
const SCHOOL_BRANDING_FALLBACK = {
  name: 'Africa Alert PWA School',
  address: 'P.O. Box 123, Harare, Zimbabwe',
  motto: 'Knowledge with Discipline',
  logo_url: '', // empty → frontend falls back to /nextgen-logo.jpeg
};

const loadSchoolBranding = (db) => {
  // All branding lives in system_settings (the same store Settings.tsx
  // writes to). Reading school_settings here used to silently miss
  // every change the admin made in Core Identity because that page
  // targets system_settings — the report card always fell back to the
  // hardcoded strings above. Pulling from system_settings makes the
  // name/address/motto/logo propagate everywhere consistently.
  const rows = db.prepare(`
    SELECT key, value FROM system_settings
    WHERE is_deleted = 0
      AND key IN ('school_name','school_address','school_motto','school_logo_url')
  `).all();
  const out = { ...SCHOOL_BRANDING_FALLBACK };
  for (const r of rows) {
    if (!r.value) continue;
    if (r.key === 'school_name')     out.name     = r.value;
    if (r.key === 'school_address')  out.address  = r.value;
    if (r.key === 'school_motto')    out.motto    = r.value;
    if (r.key === 'school_logo_url') out.logo_url = r.value;
  }
  return out;
};


// ============ BULK ENROLL (admin/teacher) ============

/**
 * @openapi
 * /api/students/bulk-enroll:
 *   post:
 *     summary: Bulk-assign students to a class
 *     description: |
 *       Creates one `enrollments` row per supplied student id, all bound
 *       to the same class. Every row is created with
 *       `status='active'` and `request_status='approved'` so the
 *       students land straight on the roster with no approval step.
 *       Idempotent on `(student_id, class_id)`: existing active rows
 *       are left untouched and reported in `skipped` so the client can
 *       render "X added, Y already in class" without a follow-up call.
 *       Restricted to admin or teacher roles.
 *     tags:
 *       - Students
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - studentIds
 *               - classId
 *             properties:
 *               studentIds: { type: array, items: { type: integer } }
 *               classId:   { type: integer }
 *               academicYear: { type: string, description: "Defaults to current year" }
 *     responses:
 *       200:
 *         description: Bulk-enrol summary
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 added:   { type: array, items: { type: integer } }
 *                 skipped: { type: array, items: { type: integer } }
 *                 errors:  { type: array, items: { type: object } }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: Admin or teacher only }
 *       404: { description: Class not found }
 *       500: { description: Server error }
 */
router.post('/bulk-enroll', auth, (req, res) => {
  try {
    const { studentIds, classId, academicYear } = req.body || {};
    if (!Array.isArray(studentIds) || studentIds.length === 0) {
      return res.status(400).json({ error: 'studentIds must be a non-empty array' });
    }
    if (!classId) {
      return res.status(400).json({ error: 'classId is required' });
    }
    if (!['school_admin', 'systems_admin', 'principal', 'teacher'].includes(req.user.role)) {
      return res.status(403).json({ error: 'Admin or teacher only' });
    }

    const db = getDb();
    const cls = db.prepare('SELECT id, section FROM classes WHERE id = ? AND is_deleted = 0').get(classId);
    if (!cls) return res.status(404).json({ error: 'Class not found' });

    const year = (academicYear || String(new Date().getFullYear())).toString();
    const section = cls.section || '';
    const now = new Date().toISOString();

    const added = [];
    const skipped = [];
    const errors = [];

    // Use a transaction so the whole bulk op is atomic — partial
    // enrolments would be worse than a clean failure.
    const tx = db.transaction((ids) => {
      const findUserStmt = db.prepare("SELECT id, role FROM users WHERE id = ? AND is_deleted = 0");
      const findEnrolStmt = db.prepare(
        "SELECT id FROM enrollments WHERE student_id = ? AND class_id = ? AND is_deleted = 0"
      );
      const insertStmt = db.prepare(`
        INSERT INTO enrollments (
          uid, student_id, class_id, roll_number, academic_year, section,
          status, request_status, requested_at, approved_by, approved_at,
          sync_status
        ) VALUES (?, ?, ?, ?, ?, ?, 'active', 'approved', NULL, ?, ?, 'pending')
      `);

      for (const rawId of ids) {
        const sid = Number(rawId);
        if (!Number.isFinite(sid)) {
          errors.push({ studentId: rawId, error: 'invalid id' });
          continue;
        }
        const user = findUserStmt.get(sid);
        if (!user) {
          errors.push({ studentId: sid, error: 'student not found' });
          continue;
        }
        if (user.role !== 'student') {
          errors.push({ studentId: sid, error: 'user is not a student' });
          continue;
        }
        const existing = findEnrolStmt.get(sid, classId);
        if (existing) {
          skipped.push(sid);
          continue;
        }
        insertStmt.run(uuidv4(), sid, classId, null, year, section, req.user.id, now);
        added.push(sid);
      }
    });
    tx(studentIds);

    AuditService.log(req, 'STUDENT_BULK_ENROLL', {
      entityType: 'class',
      entityId: classId,
      classId,
      added,
      skipped,
      errorCount: errors.length,
      academicYear: year,
    }).catch(() => undefined);

    res.json({ added, skipped, errors, classId, academicYear: year });
  } catch (err) {
    console.error('bulk-enroll error:', err);
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;
