/**
 * Courses Controller
 * Course/Subject management with enrollment
 */

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

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

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

// Auth middleware
const auth = (req, res, next) => {
  const token = req.headers.authorization?.split(' ')[1];
  if (!token) return res.status(401).json({ error: 'No token provided' });
  try {
    const jwt = require('jsonwebtoken');
    const { jwtSecret: JWT_SECRET } = require('../config');
    req.user = jwt.verify(token, JWT_SECRET);
    next();
  } catch (err) {
    res.status(401).json({ error: 'Invalid token' });
  }
};

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

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

// ============ COURSES (SUBJECTS) CRUD ============

/**
 * @openapi
 * /api/courses:
 *   get:
 *     summary: List courses
 *     description: |
 *       Returns active courses (subjects) joined to their class and
 *       teacher, with counts of enrolled students and recorded
 *       grades. Optional filters: `class_id`, `teacher_id`, and a
 *       free-text `search` term matched against name or code.
 *     tags:
 *       - Courses
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: class_id
 *         schema: { type: integer }
 *       - in: query
 *         name: teacher_id
 *         schema: { type: integer }
 *       - in: query
 *         name: search
 *         schema: { type: string }
 *     responses:
 *       200:
 *         description: Course list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *       500: { description: Server error }
 */
router.get('/', auth, (req, res) => {
  try {
    const { class_id, teacher_id, search } = req.query;
    
    let query = `
      SELECT s.*, 
        c.name as class_name,
        t.first_name || ' ' || t.last_name as teacher_name,
        (SELECT COUNT(*) FROM enrollments e WHERE e.class_id = s.class_id AND e.status = 'active' AND e.is_deleted = 0) as enrolled_students,
        (SELECT COUNT(*) FROM grades g WHERE g.subject_id = s.id AND g.is_deleted = 0) as total_grades
      FROM subjects s
      LEFT JOIN classes c ON s.class_id = c.id
      LEFT JOIN users t ON s.teacher_id = t.id
      WHERE s.is_deleted = 0
    `;
    const params = [];
    
    if (class_id) {
      query += ' AND s.class_id = ?';
      params.push(class_id);
    }
    
    if (teacher_id) {
      query += ' AND s.teacher_id = ?';
      params.push(teacher_id);
    }
    
    if (search) {
      query += ' AND (s.name LIKE ? OR s.code LIKE ?)';
      const searchTerm = `%${search}%`;
      params.push(searchTerm, searchTerm);
    }
    
    query += ' ORDER BY s.name';
    
    const courses = getDb().prepare(query).all(...params);
    res.json(courses);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/courses/{id}:
 *   get:
 *     summary: Get course by id
 *     description: |
 *       Returns a single course with its class and teacher, the
 *       active enrolled students, the most recent 50 grades, and the
 *       average percentage across all graded work.
 *     tags:
 *       - Courses
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Course detail
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       404: { description: Course not found }
 *       500: { description: Server error }
 */
router.get('/:id', auth, (req, res) => {
  try {
    const course = getDb().prepare(`
      SELECT s.*, 
        c.name as class_name,
        t.first_name || ' ' || t.last_name as teacher_name,
        t.email as teacher_email
      FROM subjects s
      LEFT JOIN classes c ON s.class_id = c.id
      LEFT JOIN users t ON s.teacher_id = t.id
      WHERE s.id = ? AND s.is_deleted = 0
    `).get(req.params.id);
    
    if (!course) {
      return res.status(404).json({ error: 'Course not found' });
    }
    
    // Get enrolled students
    const students = getDb().prepare(`
      SELECT u.id, u.uid, u.first_name, u.last_name, u.email, u.phone, e.roll_number
      FROM enrollments e
      JOIN users u ON e.student_id = u.id
      WHERE e.class_id = ? AND e.status = 'active' AND e.is_deleted = 0 AND u.is_deleted = 0
      ORDER BY e.roll_number, u.last_name
    `).all(course.class_id);
    
    // Get recent grades
    const grades = getDb().prepare(`
      SELECT g.*, u.first_name || ' ' || u.last_name as student_name
      FROM grades g
      JOIN users u ON g.student_id = u.id
      WHERE g.subject_id = ? AND g.is_deleted = 0
      ORDER BY g.graded_at DESC
      LIMIT 50
    `).all(req.params.id);
    
    // Calculate average
    const avgStats = getDb().prepare(`
      SELECT AVG(marks / total_marks * 100) as average_percentage
      FROM grades
      WHERE subject_id = ? AND is_deleted = 0 AND total_marks > 0
    `).get(req.params.id);
    
    res.json({
      ...course,
      enrolled_students_count: students.length,
      students,
      recent_grades: grades,
      average_percentage: avgStats.average_percentage || 0
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/courses:
 *   post:
 *     summary: Create course
 *     description: |
 *       Creates a new course (subject) under a class. Restricted to
 *       admin roles (`school_admin`, `systems_admin`).
 *     tags:
 *       - Courses
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - name
 *               - class_id
 *             properties:
 *               name: { type: string }
 *               code: { type: string }
 *               class_id: { type: integer }
 *               teacher_id: { type: integer }
 *               description: { type: string }
 *               credit_hours: { type: integer }
 *     responses:
 *       201:
 *         description: Created course
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Validation error }
 *       401: { description: Unauthorized }
 *       403: { description: Admin only }
 *       500: { description: Server error }
 */
router.post('/', auth, adminOnly, (req, res) => {
  try {
    const { name, code, class_id, teacher_id, description, credit_hours } = req.body;
    
    if (!name || !class_id) {
      return res.status(400).json({ error: 'Name and class are required' });
    }
    
    const uid = uuidv4();
    
    const result = getDb().prepare(`
      INSERT INTO subjects (uid, name, code, class_id, teacher_id, description, credit_hours)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `).run(uid, name, code, class_id, teacher_id, description, credit_hours || 1);
    
    const course = getDb().prepare(`
      SELECT s.*, c.name as class_name
      FROM subjects s
      LEFT JOIN classes c ON s.class_id = c.id
      WHERE s.id = ?
    `).get(result.lastInsertRowid);
    
    res.status(201).json(course);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/courses/{id}:
 *   put:
 *     summary: Update course
 *     description: |
 *       Updates course fields. Admins (`school_admin`,
 *       `systems_admin`) may edit any course; teachers may only
 *       edit courses they are assigned to.
 *     tags:
 *       - Courses
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               name: { type: string }
 *               code: { type: string }
 *               teacher_id: { type: integer }
 *               description: { type: string }
 *               credit_hours: { type: integer }
 *     responses:
 *       200:
 *         description: Updated course
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Not allowed for this course }
 *       404: { description: Course not found }
 *       500: { description: Server error }
 */
router.put('/:id', auth, adminOrTeacher, (req, res) => {
  try {
    const { name, code, teacher_id, description, credit_hours } = req.body;
    
    // Teachers can only update their assigned courses
    if (req.user.role === 'teacher') {
      const course = getDb().prepare('SELECT teacher_id FROM subjects WHERE id = ?').get(req.params.id);
      if (!course || course.teacher_id !== req.user.id) {
        return res.status(403).json({ error: 'You can only update your assigned courses' });
      }
    }
    
    getDb().prepare(`
      UPDATE subjects SET
        name = COALESCE(?, name),
        code = COALESCE(?, code),
        teacher_id = COALESCE(?, teacher_id),
        description = COALESCE(?, description),
        credit_hours = COALESCE(?, credit_hours),
        updated_at = CURRENT_TIMESTAMP,
        sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(name, code, teacher_id, description, credit_hours, req.params.id);
    
    const updated = getDb().prepare(`
      SELECT s.*, c.name as class_name
      FROM subjects s
      LEFT JOIN classes c ON s.class_id = c.id
      WHERE s.id = ?
    `).get(req.params.id);
    
    if (!updated) {
      return res.status(404).json({ error: 'Course not found' });
    }
    
    res.json(updated);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/courses/{id}:
 *   delete:
 *     summary: Soft-delete course
 *     description: |
 *       Marks the course as deleted (`is_deleted = 1`). Refuses if
 *       the course still has recorded grades. Admins may delete any
 *       course; teachers may only delete courses they are assigned
 *       to.
 *     tags:
 *       - Courses
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Deleted
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400: { description: Course has existing grades }
 *       401: { description: Unauthorized }
 *       403: { description: Not allowed for this course }
 *       500: { description: Server error }
 */
router.delete('/:id', auth, adminOrTeacher, (req, res) => {
  try {
    // Teachers can only delete their assigned courses
    if (req.user.role === 'teacher') {
      const course = getDb().prepare('SELECT teacher_id FROM subjects WHERE id = ?').get(req.params.id);
      if (!course || course.teacher_id !== req.user.id) {
        return res.status(403).json({ error: 'You can only archive your assigned courses' });
      }
    }

    // Check for existing grades
    const gradeCount = getDb().prepare('SELECT COUNT(*) as count FROM grades WHERE subject_id = ? AND is_deleted = 0').get(req.params.id).count;
    
    if (gradeCount > 0) {
      return res.status(400).json({ error: 'Cannot delete course with existing grades' });
    }
    
    getDb().prepare(`
      UPDATE subjects SET is_deleted = 1, updated_at = CURRENT_TIMESTAMP, sync_status = 'pending'
      WHERE id = ?
    `).run(req.params.id);
    
    res.json({ success: true, message: 'Course deleted successfully' });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ ENROLLMENT ============

/**
 * @openapi
 * /api/courses/{id}/enrollments:
 *   get:
 *     summary: List active students in course's class
 *     description: |
 *       Returns the students actively enrolled in the class that
 *       the given course belongs to, ordered by roll number and
 *       last name.
 *     tags:
 *       - Courses
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Enrollment list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *       404: { description: Course not found }
 *       500: { description: Server error }
 */
router.get('/:id/enrollments', auth, (req, res) => {
  try {
    const course = getDb().prepare('SELECT class_id FROM subjects WHERE id = ?').get(req.params.id);
    
    if (!course) {
      return res.status(404).json({ error: 'Course not found' });
    }
    
    const enrollments = getDb().prepare(`
      SELECT e.*, u.first_name, u.last_name, u.email
      FROM enrollments e
      JOIN users u ON e.student_id = u.id
      WHERE e.class_id = ? AND e.status = 'active' AND e.is_deleted = 0
      ORDER BY e.roll_number, u.last_name
    `).all(course.class_id);
    
    res.json(enrollments);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ MY COURSES (Teacher/Student view) ============

/**
 * @openapi
 * /api/courses/my/courses:
 *   get:
 *     summary: List my courses (teacher or admin)
 *     description: |
 *       Returns the caller's courses. Teachers receive only the
 *       courses they are assigned to; admins receive every active
 *       course along with the assigned teacher's name.
 *     tags:
 *       - Courses
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Course list scoped to caller
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Admin or Teacher only }
 *       500: { description: Server error }
 */
router.get('/my/courses', auth, adminOrTeacher, (req, res) => {
  try {
    if (req.user.role === 'teacher') {
      const courses = getDb().prepare(`
        SELECT s.*, c.name as class_name,
          (SELECT COUNT(*) FROM enrollments WHERE class_id = s.class_id AND status = 'active' AND is_deleted = 0) as student_count,
          (SELECT COUNT(*) FROM grades WHERE subject_id = s.id AND is_deleted = 0) as grade_count
        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(req.user.id);
      
      res.json(courses);
    } else {
      // Admin sees all
      const courses = getDb().prepare(`
        SELECT s.*, c.name as class_name,
          t.first_name || ' ' || t.last_name as teacher_name,
          (SELECT COUNT(*) FROM enrollments WHERE class_id = s.class_id AND status = 'active' AND is_deleted = 0) as student_count
        FROM subjects s
        LEFT JOIN classes c ON s.class_id = c.id
        LEFT JOIN users t ON s.teacher_id = t.id
        WHERE s.is_deleted = 0
        ORDER BY s.name
      `).all();
      
      res.json(courses);
    }
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

/**
 * @openapi
 * /api/courses/my/enrollments:
 *   get:
 *     summary: List my enrolled courses (student)
 *     description: |
 *       Returns the courses for the classes the calling student is
 *       actively enrolled in, along with the average percentage and
 *       grade count for the student in each course.
 *     tags:
 *       - Courses
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Enrolled courses for caller
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *       500: { description: Server error }
 */
router.get('/my/enrollments', auth, (req, res) => {
  try {
    const enrollments = getDb().prepare(`
      SELECT s.*, c.name as class_name,
        t.first_name || ' ' || t.last_name as teacher_name,
        (SELECT AVG(marks/total_marks*100) FROM grades g WHERE g.subject_id = s.id AND g.student_id = ? AND g.is_deleted = 0) as average_percentage,
        (SELECT COUNT(*) FROM grades g WHERE g.subject_id = s.id AND g.student_id = ? AND g.is_deleted = 0) as grade_count
      FROM subjects s
      JOIN enrollments e ON s.class_id = e.class_id
      LEFT JOIN classes c ON s.class_id = c.id
      LEFT JOIN users t ON s.teacher_id = t.id
      WHERE e.student_id = ? AND e.status = 'active' AND e.is_deleted = 0 AND s.is_deleted = 0
      GROUP BY s.id
      ORDER BY s.name
    `).all(req.user.id, req.user.id, req.user.id);
    
    res.json(enrollments);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ COURSE STATS ============

/**
 * @openapi
 * /api/courses/stats/overview:
 *   get:
 *     summary: Course statistics overview
 *     description: |
 *       Returns aggregate counts: total active courses, total active
 *       enrollments, overall average percentage, plus a per-class
 *       breakdown of course and student counts. Restricted to admin
 *       or teacher roles.
 *     tags:
 *       - Courses
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Aggregate stats
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Admin or Teacher only }
 *       500: { description: Server error }
 */
router.get('/stats/overview', auth, adminOrTeacher, (req, res) => {
  try {
    const stats = getDb().prepare(`
      SELECT 
        COUNT(*) as total_courses,
        SUM((SELECT COUNT(*) FROM enrollments WHERE class_id = subjects.class_id AND status = 'active' AND is_deleted = 0)) as total_enrollments,
        AVG((SELECT AVG(marks/total_marks*100) FROM grades WHERE subject_id = subjects.id AND is_deleted = 0 AND total_marks > 0)) as overall_average
      FROM subjects
      WHERE is_deleted = 0
    `).get();
    
    const byClass = getDb().prepare(`
      SELECT c.id, c.name, COUNT(s.id) as course_count,
        SUM((SELECT COUNT(*) FROM enrollments WHERE class_id = c.id AND status = 'active' AND is_deleted = 0)) as student_count
      FROM classes c
      LEFT JOIN subjects s ON c.id = s.class_id AND s.is_deleted = 0
      WHERE c.is_deleted = 0
      GROUP BY c.id
    `).all();
    
    res.json({ ...stats, by_class: byClass });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

module.exports = router;