/**
 * Dashboard Controller
 * Role-specific dashboard data aggregation for the four primary roles
 * (admin, teacher, student, parent). Each endpoint returns a single
 * payload shaped to match the frontend dashboard component.
 *
 * Mounted at /api/dashboard in server/src/index.js.
 */

const express = require('express');
const router = express.Router();
const path = require('path');
const Database = require('better-sqlite3');
const { letterFor, loadDefaultScaleKey } = require('../utils/gradeScales');

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

// Utility: return "today" as YYYY-MM-DD in local time
const todayISO = () => new Date().toISOString().split('T')[0];

// Utility: format a Date as "HH:MM" for dashboard display
const formatTime = (d) => {
  if (!d) return '';
  const date = new Date(d);
  return date.toLocaleTimeString('en-US', { hour: '2-digit', minute: '2-digit', hour12: false });
};

// Utility: format a Date as relative time ("2 hours ago", "Yesterday")
const formatRelative = (d) => {
  if (!d) return '';
  const date = new Date(d);
  const now = new Date();
  const diffMs = now - date;
  const diffMin = Math.floor(diffMs / 60000);
  const diffHr = Math.floor(diffMin / 60);
  const diffDay = Math.floor(diffHr / 24);
  if (diffMin < 1) return 'Just now';
  if (diffMin < 60) return `${diffMin} min ago`;
  if (diffHr < 24) return `${diffHr} hour${diffHr > 1 ? 's' : ''} ago`;
  if (diffDay === 1) return 'Yesterday';
  if (diffDay < 7) return `${diffDay} days ago`;
  return date.toLocaleDateString('en-US', { month: 'short', day: 'numeric' });
};

// Pick a color class for an activity status badge
const statusColor = (status) => {
  const s = (status || '').toLowerCase();
  if (s.includes('success') || s.includes('paid') || s.includes('active') || s.includes('present') || s.includes('completed')) {
    return { bg: 'bg-emerald-50 dark:bg-emerald-950/30', text: 'text-emerald-600 dark:text-emerald-400' };
  }
  if (s.includes('pending') || s.includes('partial') || s.includes('late') || s.includes('warning')) {
    return { bg: 'bg-blue-50 dark:bg-blue-950/30', text: 'text-blue-600 dark:text-blue-400' };
  }
  if (s.includes('urgent') || s.includes('failed') || s.includes('absent') || s.includes('rejected')) {
    return { bg: 'bg-red-50 dark:bg-red-950/30', text: 'text-red-600 dark:text-red-400' };
  }
  return { bg: 'bg-orange-50 dark:bg-orange-950/30', text: 'text-orange-600 dark:text-orange-400' };
};

// Resolve the school's education level into the dashboard count-tile
// nomenclature (feature/shapeshift). Primary / Secondary / High School
// share "Subject" terminology; Tertiary uses "Course". The default
// is "secondary" so a fresh DB that pre-dates the multi-config seed
// still gets sensible labels.
const resolveEducationLevel = (database) => {
  try {
    const row = database.prepare(`
      SELECT level FROM education_level_configs
      WHERE is_default = 1 AND is_active = 1 AND is_deleted = 0
      LIMIT 1
    `).get() || database.prepare(`
      SELECT level FROM education_level_configs
      WHERE is_active = 1 AND is_deleted = 0
      ORDER BY sort_order ASC LIMIT 1
    `).get();
    if (row?.level === 'tertiary') return 'tertiary';
    if (row?.level && ['primary', 'secondary', 'high_school'].includes(row.level)) return row.level;
  } catch (err) {
    // table not seeded yet — fall through
  }
  try {
    const legacy = database.prepare(
      "SELECT value FROM school_settings WHERE key = 'education_level' AND is_deleted = 0"
    ).get();
    if (legacy?.value === 'tertiary') return 'tertiary';
  } catch (err) {
    // school_settings missing — fall through
  }
  return 'secondary';
};

const dashboardLabels = (database) => {
  const level = resolveEducationLevel(database);
  const unit = level === 'tertiary' ? 'Course' : 'Subject';
  return {
    education_level: level,
    marking: `${unit} Marking`,
    average: `${unit} Average`,
    below_average: `Below ${unit} Average`,
    unit_singular: unit.toLowerCase(),
    unit_plural: level === 'tertiary' ? 'courses' : 'subjects',
  };
};

// =========================================================================
// ROLE-AWARE ANALYTICS HELPERS (added 2026-06-25)
// All queries below are READ-ONLY. They use indexed columns and GROUP BY for
// 500+ student schools. Endpoints MUST respond in <500ms.
// =========================================================================

/**
 * Admin analytics — call once and reuse the result across consumers.
 */
function getAdminAnalytics(database) {
  // active_enrollments
  const activeEnrollments = database.prepare(`
    SELECT COUNT(*) AS c FROM enrollments
    WHERE status = 'active' AND is_deleted = 0
  `).get().c;

  // attendance_rate_30d — percentage of present over the last 30 days
  const attRow = database.prepare(`
    SELECT
      COUNT(*) AS total,
      SUM(CASE WHEN status = 'present' THEN 1 ELSE 0 END) AS present
    FROM attendance
    WHERE date >= date('now', '-30 days')
  `).get();
  const attendanceRate30d = attRow.total > 0
    ? Math.round((attRow.present / attRow.total) * 1000) / 10
    : 0;

  // grade_distribution — buckets across all graded exams (last 90 days for relevance)
  // Buckets: A (>=80), B (70-79), C (60-69), D (50-59), F (<50)
  const distRows = database.prepare(`
    SELECT
      SUM(CASE WHEN pct >= 80 THEN 1 ELSE 0 END) AS a,
      SUM(CASE WHEN pct >= 70 AND pct < 80 THEN 1 ELSE 0 END) AS b,
      SUM(CASE WHEN pct >= 60 AND pct < 70 THEN 1 ELSE 0 END) AS c,
      SUM(CASE WHEN pct >= 50 AND pct < 60 THEN 1 ELSE 0 END) AS d,
      SUM(CASE WHEN pct <  50 THEN 1 ELSE 0 END) AS f,
      COUNT(*) AS total
    FROM (
      SELECT
        CASE WHEN total_marks > 0
             THEN (marks * 100.0 / total_marks)
             ELSE NULL END AS pct
      FROM grades
      WHERE is_deleted = 0
        AND date(graded_at) >= date('now', '-90 days')
    ) WHERE pct IS NOT NULL
  `).get();
  const gradeDistribution = {
    A: distRows.a || 0,
    B: distRows.b || 0,
    C: distRows.c || 0,
    D: distRows.d || 0,
    F: distRows.f || 0,
    total: distRows.total || 0,
  };

  // programs_count — academic programs / subjects on offer.
  // The legacy `subjects_new` table was the dedup target during the P0
  // sync dedupe (WT-E) and has since been folded into the canonical
  // `subjects` table. We check sqlite_master for the legacy table's
  // presence and fall back to `subjects` if it's gone — that way the
  // dashboard works against both the post-WT-E and pre-WT-E schemas.
  const hasSubjectsNew = database.prepare(
    `SELECT 1 FROM sqlite_master WHERE type='table' AND name='subjects_new'`
  ).get();
  const programsCount = hasSubjectsNew
    ? database.prepare(`SELECT COUNT(*) AS c FROM subjects_new WHERE is_deleted = 0`).get().c
    : database.prepare(`SELECT COUNT(*) AS c FROM subjects WHERE is_deleted = 0`).get().c;

  // classes_count
  const classesCount = database.prepare(`
    SELECT COUNT(*) AS c FROM classes WHERE is_deleted = 0
  `).get().c;

  // users_by_role — count per role, excluding deleted users
  const roleRows = database.prepare(`
    SELECT role, COUNT(*) AS c
    FROM users
    WHERE is_deleted = 0
    GROUP BY role
  `).all();
  const usersByRole = {};
  for (const r of roleRows) usersByRole[r.role] = r.c;

  return {
    active_enrollments: activeEnrollments,
    attendance_rate_30d: attendanceRate30d,
    grade_distribution: gradeDistribution,
    programs_count: programsCount,
    classes_count: classesCount,
    users_by_role: usersByRole,
  };
}

/**
 * Teacher analytics — keyed by course.teacher_id == teacherId.
 *
 * per_course: [{course_id, avg_completion, median_score, students_below_threshold}]
 * overall_students_count: distinct students across the teacher's courses
 * pending_grading_count: submissions (graded_at IS NULL AND status='submitted')
 */
function getTeacherAnalytics(database, teacherId) {
  // Courses taught by this teacher
  const courses = database.prepare(`
    SELECT id, name FROM courses
    WHERE teacher_id = ? AND is_deleted = 0
    ORDER BY id
  `).all(teacherId);

  const perCourse = [];
  for (const c of courses) {
    // avg_completion — AVG(course_enrolments.progress) for students in this course
    const completionRow = database.prepare(`
      SELECT AVG(progress) AS avg_progress, COUNT(*) AS enrolled
      FROM course_enrolments
      WHERE course_id = ? AND is_deleted = 0
    `).get(c.id);
    const avgCompletion = completionRow.avg_progress != null
      ? Math.round(completionRow.avg_progress * 10) / 10
      : 0;

    // median_score + students_below_threshold (<50) from course_grades.
    // course_grades.score is on a 0-100 scale (no separate total_marks column).
    const scoreRows = database.prepare(`
      SELECT score AS pct
      FROM course_grades
      WHERE course_id = ? AND is_deleted = 0
        AND score IS NOT NULL
      ORDER BY pct
    `).all(c.id);

    let medianScore = null;
    let belowThreshold = 0;
    if (scoreRows.length > 0) {
      const pcts = scoreRows.map(r => r.pct);
      const mid = Math.floor(pcts.length / 2);
      medianScore = pcts.length % 2 === 0
        ? Math.round(((pcts[mid - 1] + pcts[mid]) / 2) * 10) / 10
        : Math.round(pcts[mid] * 10) / 10;
      belowThreshold = pcts.filter(p => p < 50).length;
    }

    perCourse.push({
      course_id: c.id,
      course_name: c.name,
      avg_completion: avgCompletion,
      median_score: medianScore,
      students_below_threshold: belowThreshold,
      enrolled: completionRow.enrolled || 0,
    });
  }

  // overall_students_count — distinct students across all teacher's courses
  const overallStudentsRow = database.prepare(`
    SELECT COUNT(DISTINCT ce.student_id) AS c
    FROM course_enrolments ce
    JOIN courses co ON ce.course_id = co.id
    WHERE co.teacher_id = ? AND ce.is_deleted = 0 AND co.is_deleted = 0
  `).get(teacherId);
  const overallStudentsCount = overallStudentsRow.c || 0;

  // pending_grading_count — submissions where grade is NULL but submitted
  const pendingGradingRow = database.prepare(`
    SELECT COUNT(*) AS c
    FROM submissions s
    JOIN assignments a ON s.assignment_id = a.id
    JOIN courses co ON a.course_id = co.id
    WHERE co.teacher_id = ?
      AND s.is_deleted = 0
      AND (s.grade IS NULL OR s.graded_at IS NULL)
      AND s.submitted_at IS NOT NULL
  `).get(teacherId);
  const pendingGradingCount = pendingGradingRow.c || 0;

  return {
    per_course: perCourse,
    overall_students_count: overallStudentsCount,
    pending_grading_count: pendingGradingCount,
  };
}

/**
 * Student analytics — keyed by user.id (student).
 *
 * per_course: [{course_id, average}] — average percent across course_grades
 * pending_assignments: assignments in courses the student is enrolled in, due
 *   within next 14 days, not yet submitted.
 * recent_marks: last 5 graded marks (alias for backward compat)
 * gamification_points: SUM(crossword_sessions.points_earned)
 * leaderboard_rank: rank by total gamification points across all students
 */
function getStudentAnalytics(database, studentId) {
  // per_course — average score per course the student is enrolled in
  // course_grades.score is on a 0-100 scale.
  const perCourse = database.prepare(`
    SELECT
      co.id AS course_id,
      co.name AS course_name,
      AVG(cg.score) AS average
    FROM course_enrolments ce
    JOIN courses co ON ce.course_id = co.id
    LEFT JOIN course_grades cg
      ON cg.course_id = co.id AND cg.student_id = ce.student_id AND cg.is_deleted = 0
    WHERE ce.student_id = ? AND ce.is_deleted = 0 AND co.is_deleted = 0
    GROUP BY co.id
    ORDER BY co.id
  `).all(studentId).map(r => ({
    course_id: r.course_id,
    course_name: r.course_name,
    average: r.average != null ? Math.round(r.average * 10) / 10 : null,
  }));

  // pending_assignments — assignments due in next 14 days for courses the
  // student is enrolled in, with no submission yet.
  const pendingAssignmentsRow = database.prepare(`
    SELECT COUNT(*) AS c
    FROM assignments a
    JOIN course_enrolments ce ON ce.course_id = a.course_id
    LEFT JOIN submissions s
      ON s.assignment_id = a.id AND s.student_id = ce.student_id AND s.is_deleted = 0
    WHERE ce.student_id = ?
      AND ce.status = 'active'
      AND ce.is_deleted = 0
      AND a.is_deleted = 0
      AND (a.is_published = 1 OR a.is_published IS NULL)
      AND date(a.due_date) >= date('now')
      AND date(a.due_date) <= date('now', '+14 days')
      AND s.id IS NULL
  `).get(studentId);
  const pendingAssignments = pendingAssignmentsRow.c || 0;

  // recent_marks — last 5 grades (general exam grades)
  const recentMarks = database.prepare(`
    SELECT g.id, g.marks, g.total_marks, g.grade, g.exam_type, g.graded_at,
           s.name AS subject_name
    FROM grades g
    JOIN subjects s ON g.subject_id = s.id
    WHERE g.student_id = ? AND g.is_deleted = 0
    ORDER BY g.graded_at DESC
    LIMIT 5
  `).all(studentId).map(g => ({
    id: g.id,
    subject: g.subject_name,
    marks: g.marks,
    total_marks: g.total_marks,
    grade: g.grade,
    exam_type: g.exam_type,
    percent: g.total_marks > 0 ? Math.round((g.marks / g.total_marks) * 100) : 0,
    graded_at: g.graded_at,
  }));

  // gamification_points — SUM of crossword_sessions.points_earned
  const gamRow = database.prepare(`
    SELECT COALESCE(SUM(points_earned), 0) AS pts
    FROM crossword_sessions
    WHERE student_id = ? AND is_deleted = 0
  `).get(studentId);
  const gamificationPoints = gamRow.pts || 0;

  // leaderboard_rank — rank of this student vs all students by gamification
  // points. A student who has not earned any points yet has no rank (everyone
  // tied at zero makes "Rank #1 of 50" a meaningless, misleading display).
  // The frontend should treat `rank: null` as "not yet ranked" and the
  // `total_students` figure as the size of the student cohort.
  const rankRow = database.prepare(`
    WITH student_points AS (
      SELECT u.id,
             COALESCE(SUM(cs.points_earned), 0) AS pts
      FROM users u
      LEFT JOIN crossword_sessions cs
        ON cs.student_id = u.id AND cs.is_deleted = 0
      WHERE u.role = 'student' AND u.is_deleted = 0
      GROUP BY u.id
    )
    SELECT
      pts AS my_points,
      (SELECT COUNT(*) FROM student_points) AS total_students,
      CASE
        WHEN pts > 0 THEN
          (SELECT COUNT(*) FROM student_points WHERE pts > sp.pts) + 1
        ELSE NULL
      END AS rank
    FROM student_points sp
    WHERE sp.id = ?
  `).get(studentId);
  const leaderboardRank = rankRow ? rankRow.rank : null;

  return {
    per_course: perCourse,
    pending_assignments: pendingAssignments,
    recent_marks: recentMarks,
    gamification_points: gamificationPoints,
    leaderboard_rank: leaderboardRank,
    leaderboard_total_students: rankRow ? rankRow.total_students : 0,
  };
}

/**
 * Parent analytics — keyed by user.id (parent).
 *
 * per_dependent: [{student_id, name, upcoming_deadlines, attendance_flags,
 *                  fee_status, grade_progression}]
 */
function getParentAnalytics(database, parentId) {
  const children = database.prepare(`
    SELECT u.id, u.first_name, u.last_name
    FROM users u
    JOIN parent_students ps ON u.id = ps.student_id
    WHERE ps.parent_id = ? AND u.is_deleted = 0 AND ps.is_deleted = 0
    ORDER BY u.last_name, u.first_name
  `).all(parentId);

  const perDependent = [];
  for (const child of children) {
    // upcoming_deadlines — assignments due in next 14 days for student's courses
    const deadlinesRow = database.prepare(`
      SELECT COUNT(*) AS c
      FROM assignments a
      JOIN course_enrolments ce ON ce.course_id = a.course_id
      LEFT JOIN submissions s
        ON s.assignment_id = a.id AND s.student_id = ce.student_id AND s.is_deleted = 0
      WHERE ce.student_id = ?
        AND ce.is_deleted = 0
        AND a.is_deleted = 0
        AND (a.is_published = 1 OR a.is_published IS NULL)
        AND date(a.due_date) >= date('now')
        AND date(a.due_date) <= date('now', '+14 days')
        AND s.id IS NULL
    `).get(child.id);
    const upcomingDeadlines = deadlinesRow.c || 0;

    // attendance_flags — last 30 days, present/absent counts + flag if <75%
    const attRow = database.prepare(`
      SELECT
        COUNT(*) AS total,
        SUM(CASE WHEN status = 'present' THEN 1 ELSE 0 END) AS present,
        SUM(CASE WHEN status = 'absent' THEN 1 ELSE 0 END) AS absent,
        SUM(CASE WHEN status = 'late' THEN 1 ELSE 0 END) AS late
      FROM attendance
      WHERE student_id = ? AND date >= date('now', '-30 days')
    `).get(child.id);
    const attendancePercent = attRow.total > 0
      ? Math.round((attRow.present / attRow.total) * 1000) / 10
      : 0;
    const attendanceFlags = {
      percent: attendancePercent,
      total: attRow.total || 0,
      present: attRow.present || 0,
      absent: attRow.absent || 0,
      late: attRow.late || 0,
      flag: attendancePercent < 75 ? 'low_attendance' : (attRow.absent > 5 ? 'excessive_absences' : 'ok'),
    };

    // fee_status — total outstanding balance + status
    const feeRow = database.prepare(`
      SELECT
        COALESCE(SUM(amount - paid_amount), 0) AS outstanding,
        SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count,
        SUM(CASE WHEN status IN ('pending','partial') THEN 1 ELSE 0 END) AS pending_count,
        COUNT(*) AS total_count
      FROM student_fees
      WHERE student_id = ? AND is_deleted = 0
    `).get(child.id);
    const feeStatus = {
      outstanding: Math.round(feeRow.outstanding * 100) / 100,
      paid_count: feeRow.paid_count || 0,
      pending_count: feeRow.pending_count || 0,
      total_count: feeRow.total_count || 0,
      flag: feeRow.outstanding > 0 ? 'outstanding' : 'clear',
    };

    // grade_progression — average mark% across last 3 months vs prior 3 months
    const recentAvgRow = database.prepare(`
      SELECT AVG(CASE WHEN total_marks > 0 THEN marks * 100.0 / total_marks ELSE NULL END) AS avg
      FROM grades
      WHERE student_id = ? AND is_deleted = 0
        AND date(graded_at) >= date('now', '-3 months')
    `).get(child.id);
    const priorAvgRow = database.prepare(`
      SELECT AVG(CASE WHEN total_marks > 0 THEN marks * 100.0 / total_marks ELSE NULL END) AS avg
      FROM grades
      WHERE student_id = ? AND is_deleted = 0
        AND date(graded_at) >= date('now', '-6 months')
        AND date(graded_at) <  date('now', '-3 months')
    `).get(child.id);
    const recentAvg = recentAvgRow.avg != null ? Math.round(recentAvgRow.avg * 10) / 10 : null;
    const priorAvg = priorAvgRow.avg != null ? Math.round(priorAvgRow.avg * 10) / 10 : null;
    const trend = (recentAvg != null && priorAvg != null)
      ? Math.round((recentAvg - priorAvg) * 10) / 10
      : null;
    const gradeProgression = {
      recent_avg_percent: recentAvg,
      prior_avg_percent: priorAvg,
      trend,
      direction: trend == null ? 'unknown' : (trend > 1 ? 'improving' : (trend < -1 ? 'declining' : 'stable')),
    };

    perDependent.push({
      student_id: child.id,
      name: `${child.first_name} ${child.last_name}`,
      upcoming_deadlines: upcomingDeadlines,
      attendance_flags: attendanceFlags,
      fee_status: feeStatus,
      grade_progression: gradeProgression,
    });
  }

  return { per_dependent: perDependent };
}

// ============================================================
// /stats  — backwards-compatible alias for the admin dashboard
// ============================================================
/**
 * @openapi
 * /api/dashboard/stats:
 *   get:
 *     summary: Dashboard statistics (admin/principal)
 *     description: |
 *       Returns the admin dashboard payload (counters, recent activity,
 *       attendance chart, enrollment by class, role-aware analytics).
 *       Backwards-compatible alias for `/api/dashboard/admin`.
 *     tags:
 *       - Dashboard
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200: { description: Stats object }
 *       401: { description: Unauthorized }
 *       403: { description: Admin or Principal access required }
 */
router.get('/stats', auth, (req, res) => {
  if (!['school_admin', 'systems_admin', 'principal'].includes(req.user.role)) {
    return res.status(403).json({ error: 'Admin or Principal access required' });
  }
  return getAdminStats(req, res);
});

// ============================================================
// /admin  — School Admin / Principal / Systems Admin dashboard
// ============================================================
/**
 * @openapi
 * /api/dashboard/admin:
 *   get:
 *     summary: Admin dashboard payload
 *     description: |
 *       Returns counters, recent activity, attendance chart, enrollment by
 *       class, and role-aware analytics. Restricted to admin and principal
 *       roles.
 *     tags:
 *       - Dashboard
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200: { description: Admin dashboard payload }
 *       401: { description: Unauthorized }
 *       403: { description: Admin or Principal access required }
 */
router.get('/admin', auth, (req, res) => {
  // Restricted to admin / principal (matches the documented contract and the
  // `/api/dashboard/stats` alias). Bursar, HR, teacher, student, parent and
  // other non-admin staff must use their own dashboard endpoint — falling
  // back here silently leaked school-wide counters and audit activity to
  // every authenticated user.
  if (!['school_admin', 'systems_admin', 'principal'].includes(req.user.role)) {
    return res.status(403).json({ error: 'Admin or Principal access required' });
  }
  return getAdminStats(req, res);
});

function getAdminStats(req, res) {
  try {
    const database = getDb();
    const today = todayISO();

    // Counters
    const totalStudents = database.prepare("SELECT COUNT(*) as c FROM users WHERE role = 'student' AND is_deleted = 0").get().c;
    const totalTeachers = database.prepare("SELECT COUNT(*) as c FROM users WHERE role = 'teacher' AND is_deleted = 0").get().c;
    const totalClasses = database.prepare("SELECT COUNT(*) as c FROM classes WHERE is_deleted = 0").get().c;

    // Revenue (sum of all payments)
    const revenueRow = database.prepare(`
      SELECT COALESCE(SUM(amount), 0) as total
      FROM payments WHERE is_deleted = 0
    `).get();
    const totalRevenue = Math.round(revenueRow.total);

    // Today's attendance
    const attRow = database.prepare(`
      SELECT
        COUNT(*) as total,
        SUM(CASE WHEN status = 'present' THEN 1 ELSE 0 END) as present
      FROM attendance WHERE date = ?
    `).get(today);
    const todayAttendance = {
      present: attRow.present || 0,
      total: attRow.total || 0,
    };

    // Pending fees (sum of unpaid balances)
    const pendingRow = database.prepare(`
      SELECT COALESCE(SUM(amount - paid_amount), 0) as pending
      FROM student_fees
      WHERE status IN ('pending', 'partial') AND is_deleted = 0
    `).get();
    const pendingFees = Math.round(pendingRow.pending);

    // Recent activity (audit log + recent payments + recent messages)
    const recentAudit = database.prepare(`
      SELECT al.id, al.action, al.entity_type, al.user_email, al.created_at,
             u.first_name, u.last_name
      FROM audit_logs al
      LEFT JOIN users u ON al.user_id = u.id
      ORDER BY al.created_at DESC
      LIMIT 8
    `).all();

    const recentActivity = recentAudit.map(a => {
      const initial = a.first_name && a.last_name
        ? (a.first_name[0] + a.last_name[0]).toUpperCase()
        : (a.user_email || 'SY')[0] + (a.user_email || 'YS')[1];
      const entityName = a.first_name && a.last_name
        ? `${a.first_name} ${a.last_name}`
        : a.user_email || 'System';

      const actionLabels = {
        'CREATE_USER': 'New user created',
        'UPDATE_USER': 'User profile updated',
        'MARK_ATTENDANCE': 'Attendance marked',
        'ASSIGN_FEE': 'Fee assigned',
        'SEND_MESSAGE': 'Message sent',
        'CREATE_EVENT': 'Calendar event created',
        'PUBLISH_GRADES': 'Grades published',
        'UPDATE_SETTINGS': 'System settings updated',
        'LOGIN': 'User login',
        'REGISTER_USER': 'New registration',
      };

      const colors = statusColor('info');
      return {
        entity: entityName,
        id: a.entity_type ? `#${a.entity_type.toUpperCase()}-${a.id}` : `#${a.id}`,
        action: actionLabels[a.action] || a.action,
        time: formatRelative(a.created_at),
        status: 'INFO',
        initial,
        bgColor: colors.bg,
        textColor: colors.text,
      };
    });

    // Attendance for the past 7 school days
    const attendanceByDay = database.prepare(`
      SELECT date,
        COUNT(*) as total,
        SUM(CASE WHEN status = 'present' THEN 1 ELSE 0 END) as present
      FROM attendance
      WHERE date >= date(\'now\', '-7 days')
      GROUP BY date
      ORDER BY date ASC
    `).all();

    // Enrollment by class
    const enrollmentByClass = database.prepare(`
      SELECT c.name, c.section,
        (SELECT COUNT(*) FROM enrollments WHERE class_id = c.id AND status = 'active' AND is_deleted = 0) as count
      FROM classes c
      WHERE c.is_deleted = 0
      ORDER BY c.name, c.section
    `).all();

    // Role-aware analytics (added 2026-06-25)
    const analytics = getAdminAnalytics(database);

    res.json({
      stats: {
        totalStudents,
        totalTeachers,
        totalClasses,
        totalRevenue,
        todayAttendance,
        pendingFees,
      },
      recentActivity,
      attendanceByDay,
      enrollmentByClass,
      // New analytics fields
      active_enrollments: analytics.active_enrollments,
      attendance_rate_30d: analytics.attendance_rate_30d,
      grade_distribution: analytics.grade_distribution,
      programs_count: analytics.programs_count,
      classes_count: analytics.classes_count,
      users_by_role: analytics.users_by_role,
      // Shapeshift labels (feature/shapeshift): tiles that say
      // "Programs" / "Per-Subject Grade Distribution" should use these.
      labels: dashboardLabels(database),
    });
  } catch (err) {
    console.error('ADMIN DASHBOARD ERROR:', err);
    res.status(500).json({ error: err.message });
  }
}

// ============================================================
// /teacher  — Teacher dashboard (uses req.user.id)
// ============================================================
/**
 * @openapi
 * /api/dashboard/teacher:
 *   get:
 *     summary: Teacher dashboard payload
 *     description: |
 *       Returns the calling teacher's classes, subjects, unread messages,
 *       today's attendance summary, and per-course analytics. Scoped to
 *       `req.user.id` so each teacher sees only their own data.
 *
 *       The `labels` block carries the education-level-aware nomenclature
 *       for the count tile headers (Marking, Average, Below Average).
 *       Use `labels.marking`, `labels.average`, `labels.below_average`
 *       instead of hardcoding those strings. `education_level` reports
 *       the resolved level (`primary`, `secondary`, `high_school`,
 *       `tertiary`).
 *     tags:
 *       - Dashboard
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Teacher dashboard payload
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 labels:
 *                   type: object
 *                   description: |
 *                     Shapeshift labels derived from the school's
 *                     education level. The frontend should render these
 *                     on the count tiles instead of hardcoded text.
 *                   properties:
 *                     education_level:
 *                       type: string
 *                       enum: [primary, secondary, high_school, tertiary]
 *                       example: secondary
 *                     marking:
 *                       type: string
 *                       example: Subject Marking
 *                     average:
 *                       type: string
 *                       example: Subject Average
 *                     below_average:
 *                       type: string
 *                       example: Below Subject Average
 *                     unit_singular:
 *                       type: string
 *                       example: subject
 *                     unit_plural:
 *                       type: string
 *                       example: subjects
 *                 marking:
 *                   type: integer
 *                   description: Count of submissions awaiting grading.
 *                 average:
 *                   type: number
 *                   description: Overall average score across the teacher's courses.
 *                 below_average:
 *                   type: integer
 *                   description: Count of students below the threshold.
 *                 todayStats:
 *                   type: object
 *                   description: Counts surfaced on the dashboard tiles.
 *       401: { description: Unauthorized }
 *       403: { description: Teacher access required }
 */
router.get('/teacher', auth, (req, res) => {
  if (req.user.role !== 'teacher') {
    return res.status(403).json({ error: 'Teacher access required' });
  }
  try {
    const database = getDb();
    const teacherId = req.user.id;

    // Classes where this teacher is the class_teacher
    let myClasses = database.prepare(`
      SELECT c.id, c.name, c.section,
        (SELECT COUNT(*) FROM enrollments e WHERE e.class_id = c.id AND e.status = 'active' AND e.is_deleted = 0) as student_count
      FROM classes c
      WHERE c.class_teacher_id = ? AND c.is_deleted = 0
      ORDER BY c.name, c.section
    `).all(teacherId);

    // If teacher is not assigned as class_teacher, fall back to subjects they teach
    if (myClasses.length === 0) {
      myClasses = database.prepare(`
        SELECT DISTINCT c.id, c.name, c.section,
          (SELECT COUNT(*) FROM enrollments e WHERE e.class_id = c.id AND e.status = 'active' AND e.is_deleted = 0) as student_count
        FROM classes c
        JOIN subjects s ON s.class_id = c.id
        WHERE s.teacher_id = ? AND c.is_deleted = 0
        ORDER BY c.name, c.section
      `).all(teacherId);
    }

    // Subjects this teacher teaches
    const mySubjects = database.prepare(`
      SELECT s.id, s.name, s.code, s.class_id, c.name as class_name, c.section
      FROM subjects s
      LEFT JOIN classes c ON s.class_id = c.id
      WHERE s.teacher_id = ? AND s.is_deleted = 0
      ORDER BY s.name
    `).all(teacherId);

    // Unread messages addressed to this teacher
    const recentMessages = database.prepare(`
      SELECT m.id, m.subject, m.body, m.created_at, m.priority,
        u.first_name as sender_first, u.last_name as sender_last, u.role as sender_role
      FROM messages m
      LEFT JOIN users u ON m.sender_id = u.id
      WHERE (m.recipient_id = ? OR (m.is_announcement = 1))
        AND m.is_deleted = 0
      ORDER BY m.created_at DESC
      LIMIT 5
    `).all(teacherId);

    const unreadCount = database.prepare(`
      SELECT COUNT(*) as c FROM messages
      WHERE recipient_id = ? AND is_read = 0 AND is_deleted = 0
    `).get(teacherId).c;

    // Students across all teacher's classes (de-duped)
    const totalStudents = myClasses.reduce((sum, c) => sum + (c.student_count || 0), 0);

    // Today's attendance stats across teacher's classes
    const today = todayISO();
    const todaysAttendance = database.prepare(`
      SELECT
        COUNT(*) as marked,
        SUM(CASE WHEN status = 'present' THEN 1 ELSE 0 END) as present,
        SUM(CASE WHEN status = 'absent' THEN 1 ELSE 0 END) as absent
      FROM attendance a
      WHERE a.date = ?
        AND a.class_id IN (
          SELECT id FROM classes WHERE class_teacher_id = ? AND is_deleted = 0
          UNION
          SELECT DISTINCT class_id FROM subjects WHERE teacher_id = ? AND class_id IS NOT NULL
        )
    `).get(today, teacherId, teacherId);

    // Pending tasks: assignments due in the next 7 days
    const pendingTasks = database.prepare(`
      SELECT COUNT(*) as c FROM assignments a
      WHERE a.is_deleted = 0
        AND date(a.due_date) <= date(\'now\', '+7 days')
        AND date(a.due_date) >= date(\'now\')
    `).get().c;

    // Role-aware analytics (added 2026-06-25)
    const teacherAnalytics = getTeacherAnalytics(database, teacherId);

    // Calculate overall average score
    const avgScoreRow = database.prepare(`
      SELECT AVG(cg.score) AS avg_score
      FROM course_grades cg
      JOIN courses c ON cg.course_id = c.id
      WHERE c.teacher_id = ? AND cg.is_deleted = 0 AND cg.score IS NOT NULL
    `).get(teacherId);
    const overallAverage = avgScoreRow.avg_score != null ? Math.round(avgScoreRow.avg_score * 10) / 10 : 0;

    // Calculate below average / threshold total count
    const belowThresholdTotal = (teacherAnalytics.per_course || []).reduce(
      (sum, c) => sum + (c.students_below_threshold || 0),
      0
    );

    res.json({
      myClasses: myClasses.map(c => ({
        id: c.id,
        name: `${c.name}${c.section ? ' ' + c.section : ''}`,
        students: c.student_count || 0,
      })),
      mySubjects: mySubjects.map(s => ({
        id: s.id,
        name: s.name,
        code: s.code,
        className: s.class_name ? `${s.class_name}${s.section ? ' ' + s.section : ''}` : null,
      })),
      todayStats: {
        totalStudents,
        periodsToday: myClasses.length * 4, // rough estimate
        studentsToMark: totalStudents,
        pendingTasks,
        unreadMessages: unreadCount,
        marking: teacherAnalytics.pending_grading_count,
        average: overallAverage,
        belowAverage: belowThresholdTotal,
      },
      recentMessages: recentMessages.map(m => ({
        id: m.id,
        subject: m.subject,
        body: m.body,
        sender: m.sender_first && m.sender_last
          ? `${m.sender_first} ${m.sender_last}`
          : 'Administration',
        sender_role: m.sender_role || 'admin',
        time: formatRelative(m.created_at),
      })),
      todaysAttendance: {
        marked: todaysAttendance.marked || 0,
        present: todaysAttendance.present || 0,
        absent: todaysAttendance.absent || 0,
      },
      // Education-level-aware labels for the count tiles (shapeshift).
      // Frontend should render labels.marking / labels.average /
      // labels.below_average on the KPI tiles rather than hardcoding
      // "Marking" / "Average" / "Below Average".
      labels: dashboardLabels(database),
      // New analytics fields
      per_course: teacherAnalytics.per_course,
      overall_students_count: teacherAnalytics.overall_students_count,
      pending_grading_count: teacherAnalytics.pending_grading_count,
      marking: teacherAnalytics.pending_grading_count,
      average: overallAverage,
      below_average: belowThresholdTotal,
    });
  } catch (err) {
    console.error('TEACHER DASHBOARD ERROR:', err);
    res.status(500).json({ error: err.message });
  }
});

// ============================================================
// /student  — Student dashboard (uses req.user.id)
// ============================================================
/**
 * @openapi
 * /api/dashboard/student:
 *   get:
 *     summary: Student dashboard payload
 *     description: |
 *       Returns the calling student's attendance stats, classes, subjects,
 *       fee summary, upcoming exams, recent grades, gamification points,
 *       and leaderboard rank. Scoped to `req.user.id`.
 *     tags:
 *       - Dashboard
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200: { description: Student dashboard payload }
 *       401: { description: Unauthorized }
 *       403: { description: Student access required }
 */
router.get('/student', auth, (req, res) => {
  if (req.user.role !== 'student') {
    return res.status(403).json({ error: 'Student access required' });
  }
  try {
    const database = getDb();
    const studentId = req.user.id;

    // Attendance stats (last 30 days)
    const attRow = database.prepare(`
      SELECT
        COUNT(*) as total,
        SUM(CASE WHEN status = 'present' THEN 1 ELSE 0 END) as present,
        SUM(CASE WHEN status = 'absent' THEN 1 ELSE 0 END) as absent,
        SUM(CASE WHEN status = 'late' THEN 1 ELSE 0 END) as late
      FROM attendance
      WHERE student_id = ? AND date >= date(\'now\', '-30 days')
    `).get(studentId);
    const attendancePercent = attRow.total > 0
      ? Math.round((attRow.present / attRow.total) * 1000) / 10
      : 0;

    // Attendance by day (last 5 school days)
    const attendanceByDay = database.prepare(`
      SELECT date, status
      FROM attendance
      WHERE student_id = ? AND date >= date(\'now\', '-7 days')
      ORDER BY date DESC
      LIMIT 5
    `).all(studentId);

    // Classes (via enrollment)
    const myClasses = database.prepare(`
      SELECT c.id, c.name, c.section
      FROM enrollments e
      JOIN classes c ON e.class_id = c.id
      WHERE e.student_id = ? AND e.is_deleted = 0
    `).all(studentId);

    // Subjects for those classes
    const mySubjects = database.prepare(`
      SELECT s.id, s.name, s.code
      FROM subjects s
      WHERE s.class_id IN (SELECT class_id FROM enrollments WHERE student_id = ? AND is_deleted = 0)
        AND s.is_deleted = 0
    `).all(studentId);

    // Fee summary
    const fees = database.prepare(`
      SELECT amount, paid_amount, status
      FROM student_fees
      WHERE student_id = ? AND is_deleted = 0
    `).all(studentId);
    const pendingFees = fees
      .filter(f => f.status !== 'paid')
      .reduce((sum, f) => sum + (f.amount - f.paid_amount), 0);
    const paidFees = fees
      .filter(f => f.status === 'paid')
      .reduce((sum, f) => sum + f.paid_amount, 0);

    // Unread messages
    const unreadMessages = database.prepare(`
      SELECT COUNT(*) as c FROM messages
      WHERE recipient_id = ? AND is_read = 0 AND is_deleted = 0
    `).get(studentId).c;

    // Upcoming exams (next 14 days)
    const upcomingExams = database.prepare(`
      SELECT es.id, es.start_time, es.duration_minutes,
        s.name as subject_name, c.name as class_name, c.section
      FROM exam_schedules es
      JOIN subjects s ON es.subject_id = s.id
      JOIN classes c ON es.class_id = c.id
      JOIN enrollments e ON e.class_id = c.id
      WHERE e.student_id = ? AND e.is_deleted = 0
        AND es.is_deleted = 0
        AND datetime(es.start_time) >= datetime(\'now\')
        AND datetime(es.start_time) <= datetime(\'now\', '+14 days')
      ORDER BY es.start_time ASC
      LIMIT 5
    `).all(studentId);

    // Recent grades (last 5)
    const recentGrades = database.prepare(`
      SELECT g.id, g.marks, g.total_marks, g.grade, g.exam_type, g.graded_at,
        s.name as subject_name
      FROM grades g
      JOIN subjects s ON g.subject_id = s.id
      WHERE g.student_id = ? AND g.is_deleted = 0
      ORDER BY g.graded_at DESC
      LIMIT 5
    `).all(studentId);

    // Pending assignments (any upcoming, not filtered by class since assignments use course_id)
    const pendingAssignments = database.prepare(`
      SELECT COUNT(*) as c FROM assignments a
      WHERE a.is_deleted = 0
        AND date(a.due_date) >= date(\'now\')
        AND date(a.due_date) <= date(\'now\', '+14 days')
    `).get().c;

    // Role-aware analytics (added 2026-06-25)
    const studentAnalytics = getStudentAnalytics(database, studentId);

    res.json({
      attendancePercent,
      attendanceStats: {
        present: attRow.present || 0,
        absent: attRow.absent || 0,
        late: attRow.late || 0,
        total: attRow.total || 0,
      },
      attendanceByDay: attendanceByDay.map(a => ({
        date: a.date,
        status: a.status,
        present: a.status === 'present' ? 100 : a.status === 'late' ? 50 : 0,
      })),
      activeClasses: myClasses.length,
      myClasses: myClasses.map(c => `${c.name}${c.section ? ' ' + c.section : ''}`),
      mySubjects: mySubjects.map(s => s.name),
      feesSummary: {
        pending: Math.round(pendingFees * 100) / 100,
        paid: Math.round(paidFees * 100) / 100,
      },
      unreadMessages,
      pendingAssignments,
      upcomingExams: upcomingExams.map(e => ({
        id: e.id,
        subject: e.subject_name,
        className: `${e.class_name}${e.section ? ' ' + e.section : ''}`,
        startTime: e.start_time,
        durationMinutes: e.duration_minutes,
        formattedDate: new Date(e.start_time).toLocaleDateString('en-US', { weekday: 'long', month: 'long', day: 'numeric' }),
        formattedTime: formatTime(e.start_time),
      })),
      recentGrades: recentGrades.map(g => ({
        id: g.id,
        subject: g.subject_name,
        marks: g.marks,
        totalMarks: g.total_marks,
        grade: g.grade,
        examType: g.exam_type,
        percent: Math.round((g.marks / g.total_marks) * 100),
        time: formatRelative(g.graded_at),
      })),
      // New analytics fields (per spec)
      per_course: studentAnalytics.per_course,
      pending_assignments_count: studentAnalytics.pending_assignments,
      recent_marks: studentAnalytics.recent_marks,
      gamification_points: studentAnalytics.gamification_points,
      leaderboard_rank: studentAnalytics.leaderboard_rank,
      leaderboard_total_students: studentAnalytics.leaderboard_total_students,
      // Shapeshift labels (feature/shapeshift): "GPA by Course" etc.
      labels: dashboardLabels(database),
    });
  } catch (err) {
    console.error('STUDENT DASHBOARD ERROR:', err);
    res.status(500).json({ error: err.message });
  }
});

// ============================================================
// /parent  — Parent dashboard (uses req.user.id)
// ============================================================
/**
 * @openapi
 * /api/dashboard/parent:
 *   get:
 *     summary: Parent dashboard payload
 *     description: |
 *       Returns the calling parent's children (with attendance and grade
 *       summaries), fee roll-up across all children, recent messages, and
 *       upcoming school events. Scoped to `req.user.id`.
 *     tags:
 *       - Dashboard
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200: { description: Parent dashboard payload }
 *       401: { description: Unauthorized }
 *       403: { description: Parent access required }
 */
router.get('/parent', auth, (req, res) => {
  if (req.user.role !== 'parent') {
    return res.status(403).json({ error: 'Parent access required' });
  }
  try {
    const database = getDb();
    const parentId = req.user.id;

    // Children linked to this parent
    const children = database.prepare(`
      SELECT u.id, u.first_name, u.last_name, u.email, u.gender, u.date_of_birth, u.profile_image,
        c.id as class_id, c.name as class_name, c.section
      FROM users u
      JOIN parent_students ps ON u.id = ps.student_id
      LEFT JOIN enrollments e ON e.student_id = u.id AND e.is_deleted = 0
      LEFT JOIN classes c ON e.class_id = c.id
      WHERE ps.parent_id = ? AND u.is_deleted = 0 AND ps.is_deleted = 0
      ORDER BY u.last_name, u.first_name
    `).all(parentId);

    // Per-child stats: attendance and recent grades
    const childrenWithStats = children.map(child => {
      const att = database.prepare(`
        SELECT
          COUNT(*) as total,
          SUM(CASE WHEN status = 'present' THEN 1 ELSE 0 END) as present
        FROM attendance
        WHERE student_id = ? AND date >= date(\'now\', '-30 days')
      `).get(child.id);
      const attendancePercent = att.total > 0
        ? Math.round((att.present / att.total) * 1000) / 10
        : 0;

      // Average grade across all subjects
      const avgRow = database.prepare(`
        SELECT AVG(CASE WHEN total_marks > 0 THEN (marks * 100.0 / total_marks) ELSE NULL END) as avg
        FROM grades WHERE student_id = ? AND is_deleted = 0
      `).get(child.id);
      const avg = avgRow.avg ? Math.round(avgRow.avg * 10) / 10 : 0;
      const childBand = letterFor(avg, loadDefaultScaleKey(database));

      return {
        id: child.id,
        first_name: child.first_name,
        last_name: child.last_name,
        full_name: `${child.first_name} ${child.last_name}`,
        email: child.email,
        gender: child.gender,
        className: child.class_name
          ? `${child.class_name}${child.section ? ' ' + child.section : ''}`
          : null,
        attendancePercent,
        averageGrade: avg,
        letterGrade: childBand.letter,
        gradeScale: childBand.scale,
        gradeScaleLabel: childBand.label,
      };
    });

    // Fees summary across all children
    const fees = database.prepare(`
      SELECT sf.amount, sf.paid_amount, sf.status, u.first_name, u.last_name
      FROM student_fees sf
      JOIN users u ON sf.student_id = u.id
      JOIN parent_students ps ON ps.student_id = u.id
      WHERE ps.parent_id = ? AND sf.is_deleted = 0 AND ps.is_deleted = 0
    `).all(parentId);
    const pendingFees = fees
      .filter(f => f.status !== 'paid')
      .reduce((sum, f) => sum + (f.amount - f.paid_amount), 0);
    const paidFees = fees
      .filter(f => f.status === 'paid')
      .reduce((sum, f) => sum + f.paid_amount, 0);

    // Recent messages (to this parent or announcements)
    const messages = database.prepare(`
      SELECT m.id, m.subject, m.body, m.created_at, m.priority,
        u.first_name as sender_first, u.last_name as sender_last, u.role as sender_role,
        m.is_announcement
      FROM messages m
      LEFT JOIN users u ON m.sender_id = u.id
      WHERE (m.recipient_id = ? OR m.is_announcement = 1) AND m.is_deleted = 0
      ORDER BY m.created_at DESC
      LIMIT 6
    `).all(parentId);

    // Upcoming events (next 30 days)
    const upcomingEvents = database.prepare(`
      SELECT id, title, description, event_date, event_type, venue
      FROM events
      WHERE is_deleted = 0
        AND event_date >= date(\'now\')
        AND event_date <= date(\'now\', '+30 days')
      ORDER BY event_date ASC
      LIMIT 5
    `).all();

    // Role-aware analytics (added 2026-06-25)
    const parentAnalytics = getParentAnalytics(database, parentId);

    res.json({
      children: childrenWithStats,
      childrenCount: childrenWithStats.length,
      feesSummary: {
        pending: Math.round(pendingFees * 100) / 100,
        paid: Math.round(paidFees * 100) / 100,
        total: Math.round((pendingFees + paidFees) * 100) / 100,
      },
      messages: messages.map(m => ({
        id: m.id,
        subject: m.subject,
        body: m.body,
        sender: m.sender_first && m.sender_last
          ? `${m.sender_first} ${m.sender_last}`
          : 'School Administration',
        sender_role: m.sender_role || 'admin',
        priority: m.priority,
        isAnnouncement: !!m.is_announcement,
        created_at: m.created_at,
      })),
      upcomingEvents: upcomingEvents.map(e => ({
        id: e.id,
        title: e.title,
        description: e.description,
        date: e.event_date,
        type: e.event_type,
        venue: e.venue,
        formattedDate: new Date(e.event_date).toLocaleDateString('en-US', { weekday: 'short', month: 'short', day: 'numeric' }),
      })),
      // New analytics field (per spec)
      per_dependent: parentAnalytics.per_dependent,
      // Shapeshift labels (feature/shapeshift): the parent child cards
      // and any "Average" tile should consume these.
      labels: dashboardLabels(database),
    });
  } catch (err) {
    console.error('PARENT DASHBOARD ERROR:', err);
    res.status(500).json({ error: err.message });
  }
});

// ============================================================
// /recent-activity  — Unified activity feed (used by Admin Dashboard)
// ============================================================
/**
 * @openapi
 * /api/dashboard/recent-activity:
 *   get:
 *     summary: Recent activity feed
 *     description: Returns the most recent audit log entries, decorated with actor name and relative time.
 *     tags:
 *       - Dashboard
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: limit
 *         schema:
 *           type: integer
 *           default: 10
 *           maximum: 50
 *         description: Maximum number of entries to return (capped at 50)
 *     responses:
 *       200: { description: Activity entries }
 *       401: { description: Unauthorized }
 */
router.get('/recent-activity', auth, (req, res) => {
  try {
    const database = getDb();
    const limit = Math.min(parseInt(req.query.limit) || 10, 50);

    const activities = database.prepare(`
      SELECT al.id, al.action, al.entity_type, al.user_email, al.created_at,
             u.first_name, u.last_name
      FROM audit_logs al
      LEFT JOIN users u ON al.user_id = u.id
      ORDER BY al.created_at DESC
      LIMIT ?
    `).all(limit);

    res.json(activities.map(a => {
      const initial = a.first_name && a.last_name
        ? (a.first_name[0] + a.last_name[0]).toUpperCase()
        : (a.user_email || 'SY').substring(0, 2).toUpperCase();
      return {
        id: a.id,
        entity: a.first_name && a.last_name
          ? `${a.first_name} ${a.last_name}`
          : a.user_email || 'System',
        action: a.action,
        entityType: a.entity_type,
        time: formatRelative(a.created_at),
        status: 'INFO',
        initial,
        ...statusColor('info'),
      };
    }));
  } catch (err) {
    console.error('RECENT ACTIVITY ERROR:', err);
    res.status(500).json({ error: err.message });
  }
});

// ============================================================
// PHASE 2 ANALYTICS (added 2026-06-26)
// Aggregated data endpoints per the "Next Gen Learning" Phase 2 spec:
//   - admin/infrastructure-health
//   - admin/grade-distribution-by-subject
//   - teacher/outliers
//   - teacher/student/:id
//   - student/upcoming-deadlines
//   - parent/upcoming-deadlines/:student_id
//   - parent/fees/:student_id
// All endpoints MUST respond in <500ms.
// ============================================================

const STAFF_ROLES_FOR_RBAC = ['school_admin', 'systems_admin', 'principal', 'teacher', 'hr'];

// Admin: infrastructure health (DB size, table row counts, sync queue depth).
/**
 * @openapi
 * /api/dashboard/admin/infrastructure-health:
 *   get:
 *     summary: Infrastructure health snapshot
 *     description: |
 *       Returns DB file size, row counts for core tables, and the sync
 *       queue depth (pending vs synced rows). Restricted to staff roles.
 *     tags:
 *       - Dashboard
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200: { description: Infrastructure health snapshot }
 *       401: { description: Unauthorized }
 *       403: { description: Staff access required }
 */
router.get('/admin/infrastructure-health', auth, (req, res) => {
  if (!STAFF_ROLES_FOR_RBAC.includes(req.user.role)) {
    return res.status(403).json({ error: 'Staff access required' });
  }
  try {
    const database = getDb();
    const fs = require('fs');

    // DB file size on disk
    let dbSizeBytes = null;
    try { dbSizeBytes = fs.statSync(dbPath).size; } catch (_) {}

    // Total rows by core tables (top 12)
    const tablesOfInterest = [
      'users', 'classes', 'subjects', 'subjects_new', 'courses',
      'enrollments', 'attendance', 'grades', 'assignments', 'submissions',
      'student_fees', 'payments', 'crossword_games', 'crossword_sessions',
    ];
    const tableRows = {};
    for (const t of tablesOfInterest) {
      try {
        const row = database.prepare(`SELECT COUNT(*) AS c FROM ${t}`).get();
        tableRows[t] = row.c || 0;
      } catch (_) {
        tableRows[t] = null;
      }
    }

    // Sync queue depth: rows with sync_status = 'pending'. The legacy
    // `subjects_new` table may not exist post-WT-E (it was deduped into
    // `subjects`); the per-table try/catch above already handles that for
    // the tableRows map, but the aggregate query below needs its own
    // guard so a missing subjects_new doesn't 500 the whole dashboard.
    const hasSubjectsNew = database.prepare(
      `SELECT 1 FROM sqlite_master WHERE type='table' AND name='subjects_new'`
    ).get();
    const subjectsNewSubquery = hasSubjectsNew
      ? `(SELECT COUNT(*) FROM subjects_new   WHERE sync_status='%STATUS%' AND is_deleted=0) +`
      : `(SELECT 0) +`;
    const syncQueue = (() => {
      try {
        const sql = `
          SELECT
            (SELECT COUNT(*) FROM users          WHERE sync_status='pending' AND is_deleted=0) +
            (SELECT COUNT(*) FROM classes        WHERE sync_status='pending' AND is_deleted=0) +
            ${subjectsNewSubquery.replace('%STATUS%', 'pending')}
            (SELECT COUNT(*) FROM courses        WHERE sync_status='pending' AND is_deleted=0) +
            (SELECT COUNT(*) FROM enrollments    WHERE sync_status='pending' AND is_deleted=0) +
            (SELECT COUNT(*) FROM attendance     WHERE sync_status='pending' AND is_deleted=0) +
            (SELECT COUNT(*) FROM grades         WHERE sync_status='pending' AND is_deleted=0) +
            (SELECT COUNT(*) FROM assignments    WHERE sync_status='pending' AND is_deleted=0) +
            (SELECT COUNT(*) FROM submissions    WHERE sync_status='pending' AND is_deleted=0) +
            (SELECT COUNT(*) FROM student_fees   WHERE sync_status='pending' AND is_deleted=0) AS total_pending,
            (SELECT COUNT(*) FROM users          WHERE sync_status='synced'   AND is_deleted=0) +
            (SELECT COUNT(*) FROM classes        WHERE sync_status='synced'   AND is_deleted=0) +
            ${subjectsNewSubquery.replace('%STATUS%', 'synced')}
            (SELECT COUNT(*) FROM courses        WHERE sync_status='synced'   AND is_deleted=0) +
            (SELECT COUNT(*) FROM enrollments    WHERE sync_status='synced'   AND is_deleted=0) +
            (SELECT COUNT(*) FROM attendance     WHERE sync_status='synced'   AND is_deleted=0) +
            (SELECT COUNT(*) FROM grades         WHERE sync_status='synced'   AND is_deleted=0) +
            (SELECT COUNT(*) FROM assignments    WHERE sync_status='synced'   AND is_deleted=0) +
            (SELECT COUNT(*) FROM submissions    WHERE sync_status='synced'   AND is_deleted=0) +
            (SELECT COUNT(*) FROM student_fees   WHERE sync_status='synced'   AND is_deleted=0) AS total_synced
        `;
        return database.prepare(sql).get();
      } catch (_) {
        // Subjects_new gone (or any other table). Return zeros; the
        // tableRows map above already gives per-table counts.
        return { total_pending: 0, total_synced: 0 };
      }
    })();

    res.json({
      db_size_bytes: dbSizeBytes,
      db_size_mb: dbSizeBytes != null ? Math.round(dbSizeBytes / 1024 / 1024 * 100) / 100 : null,
      table_rows: tableRows,
      sync_queue: {
        pending: syncQueue.total_pending || 0,
        synced: syncQueue.total_synced || 0,
      },
      timestamp: new Date().toISOString(),
    });
  } catch (err) {
    console.error('INFRA HEALTH ERROR:', err);
    res.status(500).json({ error: err.message });
  }
});

// Admin: per-subject grade distribution (the spec's "across all programs" view).
/**
 * @openapi
 * /api/dashboard/admin/grade-distribution-by-subject:
 *   get:
 *     summary: Grade distribution by subject
 *     description: |
 *       Per-subject A/B/C/D/F bucket counts and average percent across all
 *       graded exams.
 *     tags:
 *       - Dashboard
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: limit
 *         schema:
 *           type: integer
 *           default: 10
 *           maximum: 30
 *         description: Maximum number of subjects to return (capped at 30)
 *     responses:
 *       200: { description: Per-subject grade distribution rows }
 *       401: { description: Unauthorized }
 *       403: { description: Staff access required }
 */
router.get('/admin/grade-distribution-by-subject', auth, (req, res) => {
  if (!STAFF_ROLES_FOR_RBAC.includes(req.user.role)) {
    return res.status(403).json({ error: 'Staff access required' });
  }
  try {
    const database = getDb();
    const limit = Math.min(parseInt(req.query.limit) || 10, 30);

    const rows = database.prepare(`
      SELECT
        s.id AS subject_id,
        s.name AS subject_name,
        SUM(CASE WHEN pct >= 80 THEN 1 ELSE 0 END) AS a,
        SUM(CASE WHEN pct >= 70 AND pct < 80 THEN 1 ELSE 0 END) AS b,
        SUM(CASE WHEN pct >= 60 AND pct < 70 THEN 1 ELSE 0 END) AS c,
        SUM(CASE WHEN pct >= 50 AND pct < 60 THEN 1 ELSE 0 END) AS d,
        SUM(CASE WHEN pct <  50 THEN 1 ELSE 0 END) AS f,
        COUNT(*) AS total,
        ROUND(AVG(pct), 1) AS avg_pct
      FROM (
        SELECT g.subject_id,
               CASE WHEN g.total_marks > 0 THEN (g.marks * 100.0 / g.total_marks) ELSE NULL END AS pct
        FROM grades g
        WHERE g.is_deleted = 0 AND g.total_marks > 0
      ) g
      JOIN subjects s ON s.id = g.subject_id
      WHERE pct IS NOT NULL
      GROUP BY s.id
      HAVING total > 0
      ORDER BY total DESC
      LIMIT ?
    `).all(limit);

    // Shapeshift-aware mapping: aliases `subject_id` / `subject_name` to
    // `unit_id` / `unit_name` so the consumer doesn't need to know whether
    // the underlying rows are subjects (Primary/Secondary/HS) or courses
    // (Tertiary). The `labels` block (also returned) carries the matching
    // terminology. Backwards-compatible: keeps `subject_id` / `subject_name`.
    res.json(rows.map(r => ({
      subject_id: r.subject_id,
      unit_id: r.subject_id,
      subject_name: r.subject_name,
      unit_name: r.subject_name,
      distribution: { A: r.a, B: r.b, C: r.c, D: r.d, F: r.f },
      total: r.total,
      avg_pct: r.avg_pct,
    })));
  } catch (err) {
    console.error('GRADE DIST BY SUBJECT ERROR:', err);
    res.status(500).json({ error: err.message });
  }
});

// Teacher: outliers list — students scoring below 50% in any course the teacher owns.
/**
 * @openapi
 * /api/dashboard/teacher/outliers:
 *   get:
 *     summary: Outlier students for a teacher
 *     description: |
 *       Returns students in the calling teacher's courses whose average
 *       score is below 50%. Optional `course_id` filter restricts to a
 *       single course.
 *     tags:
 *       - Dashboard
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: course_id
 *         schema:
 *           type: integer
 *         description: Restrict outliers to a single course
 *     responses:
 *       200: { description: Outlier students with course info }
 *       401: { description: Unauthorized }
 *       403: { description: Teacher access required }
 */
router.get('/teacher/outliers', auth, (req, res) => {
  if (req.user.role !== 'teacher') {
    return res.status(403).json({ error: 'Teacher access required' });
  }
  try {
    const database = getDb();
    const teacherId = req.user.id;
    const courseId = req.query.course_id ? parseInt(req.query.course_id) : null;

    let rows;
    if (courseId) {
      rows = database.prepare(`
        SELECT cg.student_id, cg.course_id, c.name AS course_name,
               ROUND(AVG(cg.score), 1) AS avg_score,
               COUNT(cg.id) AS grades_count
        FROM course_grades cg
        JOIN courses c ON cg.course_id = c.id
        WHERE cg.course_id = ? AND c.teacher_id = ?
          AND cg.is_deleted = 0 AND cg.score IS NOT NULL
        GROUP BY cg.student_id, cg.course_id
        HAVING avg_score < 50
        ORDER BY avg_score ASC
      `).all(courseId, teacherId);
    } else {
      rows = database.prepare(`
        SELECT cg.student_id, cg.course_id, c.name AS course_name,
               ROUND(AVG(cg.score), 1) AS avg_score,
               COUNT(cg.id) AS grades_count
        FROM course_grades cg
        JOIN courses c ON cg.course_id = c.id
        WHERE c.teacher_id = ?
          AND cg.is_deleted = 0 AND cg.score IS NOT NULL
        GROUP BY cg.student_id, cg.course_id
        HAVING avg_score < 50
        ORDER BY avg_score ASC
      `).all(teacherId);
    }

    // Enrich with student names
    const studentIds = [...new Set(rows.map(r => r.student_id))];
    const studentMap = {};
    if (studentIds.length > 0) {
      const placeholders = studentIds.map(() => '?').join(',');
      const students = database.prepare(
        `SELECT id, first_name, last_name FROM users WHERE id IN (${placeholders})`
      ).all(...studentIds);
      for (const s of students) studentMap[s.id] = s;
    }

    res.json(rows.map(r => ({
      student_id: r.student_id,
      student_name: studentMap[r.student_id]
        ? `${studentMap[r.student_id].first_name} ${studentMap[r.student_id].last_name}`
        : `Student #${r.student_id}`,
      course_id: r.course_id,
      course_name: r.course_name,
      avg_score: r.avg_score,
      grades_count: r.grades_count,
    })));
  } catch (err) {
    console.error('TEACHER OUTLIERS ERROR:', err);
    res.status(500).json({ error: err.message });
  }
});

// Teacher: per-student drill-down for one of their courses.
/**
 * @openapi
 * /api/dashboard/teacher/student/{id}:
 *   get:
 *     summary: Per-student drill-down for one of the teacher's courses
 *     description: |
 *       Returns student profile, attendance (last 30 days), grades history,
 *       course grades for the calling teacher's courses, submission
 *       summary, and gamification points. Refuses if the student shares
 *       no course with the teacher.
 *     tags:
 *       - Dashboard
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema:
 *           type: integer
 *         description: Student user ID
 *     responses:
 *       200: { description: Per-student payload }
 *       401: { description: Unauthorized }
 *       403: { description: Teacher access required or student not in your courses }
 *       404: { description: Student not found }
 */
router.get('/teacher/student/:id', auth, (req, res) => {
  if (req.user.role !== 'teacher') {
    return res.status(403).json({ error: 'Teacher access required' });
  }
  try {
    const database = getDb();
    const teacherId = req.user.id;
    const studentId = parseInt(req.params.id);

    // Verify the teacher has at least one course with this student
    const sharedCourse = database.prepare(`
      SELECT c.id, c.name FROM courses c
      JOIN course_enrolments ce ON ce.course_id = c.id
      WHERE c.teacher_id = ? AND ce.student_id = ?
        AND c.is_deleted = 0 AND ce.is_deleted = 0
      LIMIT 1
    `).get(teacherId, studentId);
    if (!sharedCourse) {
      return res.status(403).json({ error: 'Student is not enrolled in any of your courses' });
    }

    // Student profile
    const student = database.prepare(
      `SELECT id, first_name, last_name, email FROM users WHERE id = ? AND role = 'student' AND is_deleted = 0`
    ).get(studentId);
    if (!student) return res.status(404).json({ error: 'Student not found' });

    // Attendance (last 30 days)
    const att = database.prepare(`
      SELECT
        COUNT(*) AS total,
        SUM(CASE WHEN status = 'present' THEN 1 ELSE 0 END) AS present,
        SUM(CASE WHEN status = 'absent'  THEN 1 ELSE 0 END) AS absent,
        SUM(CASE WHEN status = 'late'    THEN 1 ELSE 0 END) AS late
      FROM attendance WHERE student_id = ? AND date >= date('now', '-30 days')
    `).get(studentId);

    // Grades history (last 10, all subjects)
    const gradesHistory = database.prepare(`
      SELECT g.id, s.name AS subject, g.marks, g.total_marks, g.exam_type, g.graded_at,
             ROUND(CASE WHEN g.total_marks > 0 THEN (g.marks * 100.0 / g.total_marks) ELSE 0 END, 1) AS pct
      FROM grades g JOIN subjects s ON g.subject_id = s.id
      WHERE g.student_id = ? AND g.is_deleted = 0
      ORDER BY g.graded_at DESC LIMIT 10
    `).all(studentId);

    // Course grades (this teacher's courses)
    const courseGrades = database.prepare(`
      SELECT cg.course_id, c.name AS course_name,
             ROUND(AVG(cg.score), 1) AS avg_score,
             COUNT(cg.id) AS count
      FROM course_grades cg JOIN courses c ON cg.course_id = c.id
      WHERE cg.student_id = ? AND c.teacher_id = ? AND cg.is_deleted = 0
      GROUP BY cg.course_id
    `).all(studentId, teacherId);

    // Submissions summary
    const submissions = database.prepare(`
      SELECT COUNT(*) AS total,
             SUM(CASE WHEN status = 'graded' THEN 1 ELSE 0 END) AS graded,
             SUM(CASE WHEN status IN ('submitted','pending') THEN 1 ELSE 0 END) AS pending
      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.teacher_id = ? AND s.is_deleted = 0 AND a.is_deleted = 0
    `).get(studentId, teacherId);

    // Gamification (crossword points)
    const gam = database.prepare(`
      SELECT COALESCE(SUM(points_earned), 0) AS points
      FROM crossword_sessions WHERE student_id = ? AND is_deleted = 0
    `).get(studentId);

    res.json({
      student: {
        id: student.id,
        name: `${student.first_name} ${student.last_name}`,
        email: student.email,
      },
      shared_course: sharedCourse,
      attendance: {
        total: att.total || 0,
        present: att.present || 0,
        absent: att.absent || 0,
        late: att.late || 0,
        percent: att.total > 0 ? Math.round((att.present / att.total) * 1000) / 10 : 0,
      },
      grades_history: gradesHistory,
      course_grades: courseGrades,
      submissions: {
        total: submissions.total || 0,
        graded: submissions.graded || 0,
        pending: submissions.pending || 0,
      },
      gamification_points: gam.points || 0,
    });
  } catch (err) {
    console.error('TEACHER STUDENT DRILL-DOWN ERROR:', err);
    res.status(500).json({ error: err.message });
  }
});

// Student: upcoming deadlines list (next 14 days, in enrolled courses, not yet submitted).
/**
 * @openapi
 * /api/dashboard/student/upcoming-deadlines:
 *   get:
 *     summary: Student's upcoming assignment deadlines
 *     description: |
 *       Returns assignments in the student's enrolled courses due in the
 *       next 14 days that have not yet been submitted, with urgency tier.
 *       Scoped to `req.user.id`.
 *     tags:
 *       - Dashboard
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200: { description: Upcoming deadline rows }
 *       401: { description: Unauthorized }
 *       403: { description: Student access required }
 */
router.get('/student/upcoming-deadlines', auth, (req, res) => {
  if (req.user.role !== 'student') {
    return res.status(403).json({ error: 'Student access required' });
  }
  try {
    const database = getDb();
    const labels = dashboardLabels(database);
    const studentId = req.user.id;
    const rows = database.prepare(`
      SELECT a.id, a.title, a.description, a.due_date, a.max_score,
             c.id AS course_id, c.name AS course_name,
             s.name AS subject_name,
             u.first_name || ' ' || u.last_name AS teacher_name,
             CAST(julianday(date(a.due_date)) - julianday(date('now')) AS INTEGER) AS days_until_due
      FROM assignments a
      JOIN courses c ON a.course_id = c.id
      JOIN course_enrolments ce ON ce.course_id = c.id
      LEFT JOIN subjects s ON c.subject_id = s.id
      LEFT JOIN users u ON c.teacher_id = u.id
      LEFT JOIN submissions sb
        ON sb.assignment_id = a.id AND sb.student_id = ce.student_id AND sb.is_deleted = 0
      WHERE ce.student_id = ?
        AND ce.status = 'active' AND ce.is_deleted = 0
        AND a.is_deleted = 0
        AND (a.is_published = 1 OR a.is_published IS NULL)
        AND date(a.due_date) >= date('now')
        AND date(a.due_date) <= date('now', '+14 days')
        AND sb.id IS NULL
      ORDER BY a.due_date ASC
    `).all(studentId);

    // Shapeshift (feature/shapeshift): emit `unit_name` alongside the
    // legacy `course_name` / `subject_name` so the frontend can pick the
    // right one without round-tripping the school-settings endpoint.
    res.json(rows.map(r => ({
      ...r,
      unit_name: r.subject_name || r.course_name,
      _labels: labels,
    })));
  } catch (err) {
    console.error('STUDENT DEADLINES ERROR:', err);
    res.status(500).json({ error: err.message });
  }
});

// Parent: upcoming deadlines for one of their children.
/**
 * @openapi
 * /api/dashboard/parent/upcoming-deadlines/{student_id}:
 *   get:
 *     summary: Upcoming deadlines for one of the parent's children
 *     description: |
 *       Returns assignments due in the next 14 days for the linked student
 *       that have not yet been submitted. Refuses if the caller is not
 *       linked to that student via `parent_students`.
 *     tags:
 *       - Dashboard
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: student_id
 *         required: true
 *         schema:
 *           type: integer
 *         description: Student user ID
 *     responses:
 *       200: { description: Upcoming deadline rows }
 *       401: { description: Unauthorized }
 *       403: { description: Parent access required or not linked to this student }
 */
router.get('/parent/upcoming-deadlines/:student_id', auth, (req, res) => {
  if (req.user.role !== 'parent') {
    return res.status(403).json({ error: 'Parent access required' });
  }
  try {
    const database = getDb();
    const labels = dashboardLabels(database);
    const parentId = req.user.id;
    const studentId = parseInt(req.params.student_id);

    // Verify parent_students link
    const link = database.prepare(
      'SELECT 1 FROM parent_students WHERE parent_id = ? AND student_id = ? AND is_deleted = 0'
    ).get(parentId, studentId);
    if (!link) return res.status(403).json({ error: 'Not linked to this student' });

    const rows = database.prepare(`
      SELECT a.id, a.title, a.description, a.due_date, a.max_score,
             c.id AS course_id, c.name AS course_name,
             s.name AS subject_name,
             CAST(julianday(date(a.due_date)) - julianday(date('now')) AS INTEGER) AS days_until_due
      FROM assignments a
      JOIN courses c ON a.course_id = c.id
      JOIN course_enrolments ce ON ce.course_id = c.id
      LEFT JOIN subjects s ON c.subject_id = s.id
      LEFT JOIN submissions sb
        ON sb.assignment_id = a.id AND sb.student_id = ce.student_id AND sb.is_deleted = 0
      WHERE ce.student_id = ?
        AND ce.status = 'active' AND ce.is_deleted = 0
        AND a.is_deleted = 0
        AND (a.is_published = 1 OR a.is_published IS NULL)
        AND date(a.due_date) >= date('now')
        AND date(a.due_date) <= date('now', '+14 days')
        AND sb.id IS NULL
      ORDER BY a.due_date ASC
    `).all(studentId);

    res.json(rows.map(r => ({
      ...r,
      unit_name: r.subject_name || r.course_name,
      _labels: labels,
    })));
  } catch (err) {
    console.error('PARENT DEADLINES ERROR:', err);
    res.status(500).json({ error: err.message });
  }
});

// Parent: fees breakdown for one of their children.
/**
 * @openapi
 * /api/dashboard/parent/fees/{student_id}:
 *   get:
 *     summary: Fee breakdown for one of the parent's children
 *     description: |
 *       Returns the per-fee rows for the linked student plus an aggregate
 *       summary (total, paid, outstanding, paid/pending/overdue counts).
 *       Refuses if the caller is not linked to that student.
 *     tags:
 *       - Dashboard
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: student_id
 *         required: true
 *         schema:
 *           type: integer
 *         description: Student user ID
 *     responses:
 *       200: { description: Fee rows and summary }
 *       401: { description: Unauthorized }
 *       403: { description: Parent access required or not linked to this student }
 */
router.get('/parent/fees/:student_id', auth, (req, res) => {
  if (req.user.role !== 'parent') {
    return res.status(403).json({ error: 'Parent access required' });
  }
  try {
    const database = getDb();
    const parentId = req.user.id;
    const studentId = parseInt(req.params.student_id);

    const link = database.prepare(
      'SELECT 1 FROM parent_students WHERE parent_id = ? AND student_id = ? AND is_deleted = 0'
    ).get(parentId, studentId);
    if (!link) return res.status(403).json({ error: 'Not linked to this student' });

    const fees = database.prepare(`
      SELECT sf.id, sf.amount, sf.paid_amount, sf.status, sf.due_date, sf.created_at,
             sf.description, sf.term, sf.academic_year,
             CASE WHEN sf.amount > 0 THEN ROUND((sf.paid_amount * 100.0 / sf.amount), 1) ELSE 0 END AS paid_percent,
             CASE WHEN sf.status = 'paid' THEN 0
                  WHEN sf.amount > sf.paid_amount THEN (sf.amount - sf.paid_amount)
                  ELSE 0 END AS outstanding
      FROM student_fees sf
      WHERE sf.student_id = ? AND sf.is_deleted = 0
      ORDER BY sf.due_date ASC, sf.id ASC
    `).all(studentId);

    const summary = {
      total_fees: fees.reduce((s, f) => s + (f.amount || 0), 0),
      total_paid: fees.reduce((s, f) => s + (f.paid_amount || 0), 0),
      total_outstanding: fees.reduce((s, f) => s + (f.outstanding || 0), 0),
      paid_count: fees.filter(f => f.status === 'paid').length,
      pending_count: fees.filter(f => f.status === 'pending' || f.status === 'partial').length,
      overdue_count: fees.filter(f => f.outstanding > 0 && f.due_date && new Date(f.due_date) < new Date()).length,
    };

    res.json({
      summary,
      items: fees,
    });
  } catch (err) {
    console.error('PARENT FEES ERROR:', err);
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;
