/**
 * Course Enrolments Controller
 * Mirrors the 4-state workflow on `course_enrolments`:
 *   request_status: pending | approved | rejected | withdrawn
 *
 * Endpoints:
 *   GET    /api/course-enrolments                       list (filtered; students see own only)
 *   POST   /api/course-enrolments                       student self-enrol -> pending
 *                                                     admin/teacher force-enrol -> approved
 *   GET    /api/course-enrolments/pending               admin/teacher only
 *   POST   /api/course-enrolments/:id/approve           admin/teacher only
 *   POST   /api/course-enrolments/:id/reject            admin/teacher only (sets rejection_reason)
 *   POST   /api/course-enrolments/:id/withdraw          student (own row only) or admin/teacher
 *   DELETE /api/course-enrolments/:id                   admin-only soft delete
 */

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

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

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

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

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

/**
 * @openapi
 * /api/course-enrolments:
 *   get:
 *     summary: List course enrolments
 *     description: |
 *       Returns course enrolments, filterable by `student_id`,
 *       `course_id`, `status`, and `request_status`. Students only
 *       see their own rows; staff see all.
 *     tags:
 *       - Course Enrolments
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: student_id
 *         schema: { type: integer }
 *       - in: query
 *         name: course_id
 *         schema: { type: integer }
 *       - in: query
 *         name: status
 *         schema: { type: string }
 *       - in: query
 *         name: request_status
 *         schema: { type: string }
 *     responses:
 *       200:
 *         description: Course enrolment list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *       500: { description: Server error }
 *   post:
 *     summary: Enrol a student in a course
 *     description: |
 *       Creates a new `course_enrolments` row. A student self-enrolling
 *       produces a `pending` request that requires admin/teacher
 *       approval; staff (admin, systems_admin, principal, teacher)
 *       force-enrol produce an immediately `approved` row. Idempotent
 *       on duplicate `(student_id, course_id)`.
 *     tags:
 *       - Course Enrolments
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - course_id
 *             properties:
 *               course_id: { type: integer }
 *               student_id:
 *                 type: integer
 *                 description: Staff-only override; defaults to caller's id for students
 *     responses:
 *       201:
 *         description: Created enrolment
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       400:
 *         description: Validation error
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       403: { description: Students may only enrol themselves }
 *       404: { description: Course or student not found }
 *       500: { description: Server error }
 */
router.get('/', auth, (req, res) => {
  try {
    const { student_id, course_id, status, request_status } = req.query;
    let query = 'SELECT * FROM course_enrolments WHERE is_deleted = 0';
    const params = [];

    if (student_id) { query += ' AND student_id = ?'; params.push(student_id); }
    if (course_id)  { query += ' AND course_id = ?';  params.push(course_id); }
    if (status)     { query += ' AND status = ?';     params.push(status); }
    if (request_status) { query += ' AND request_status = ?'; params.push(request_status); }

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

    query += ' ORDER BY created_at DESC';
    const list = getDb().prepare(query).all(...params);
    res.json(list);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ LIST PENDING ============

/**
 * @openapi
 * /api/course-enrolments/pending:
 *   get:
 *     summary: List pending course enrolments
 *     description: |
 *       Returns course enrolments with `request_status = 'pending'`
 *       (or NULL), joined with student and course details. Restricted
 *       to admin or teacher roles.
 *     tags:
 *       - Course Enrolments
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Pending enrolment list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Admin or Teacher only }
 *       500: { description: Server error }
 */
router.get('/pending', auth, adminOrTeacher, (req, res) => {
  try {
    const rows = getDb().prepare(`
      SELECT ce.*, u.first_name AS student_first_name, u.last_name AS student_last_name,
             u.email AS student_email,
             c.name AS course_name
      FROM course_enrolments ce
      JOIN users u ON ce.student_id = u.id
      JOIN courses c ON ce.course_id = c.id
      WHERE ce.is_deleted = 0
        AND (ce.request_status IS NULL OR ce.request_status = 'pending')
      ORDER BY COALESCE(ce.requested_at, ce.created_at) DESC
    `).all();
    res.json(rows);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

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

router.post('/', auth, (req, res) => {
  try {
    const { course_id } = req.body;
    if (!course_id) {
      return res.status(400).json({ error: 'course_id is required' });
    }

    const database = getDb();

    let resolvedStudentId = req.body.student_id;
    let isSelfEnrol = false;
    if (req.user.role === 'student') {
      if (resolvedStudentId && Number(resolvedStudentId) !== Number(req.user.id)) {
        return res.status(403).json({ error: 'Students may only enrol themselves' });
      }
      resolvedStudentId = req.user.id;
      isSelfEnrol = true;
    } else if (!['school_admin', 'systems_admin', 'principal', 'teacher'].includes(req.user.role)) {
      return res.status(403).json({ error: 'Insufficient role to enrol students' });
    } else if (!resolvedStudentId) {
      return res.status(400).json({ error: 'student_id is required for staff-initiated enrolment' });
    }

    const courseExists = database.prepare('SELECT id FROM courses WHERE id = ? AND is_deleted = 0').get(course_id);
    if (!courseExists) {
      return res.status(404).json({ error: 'Course not found' });
    }

    const studentExists = database.prepare("SELECT id, role FROM users WHERE id = ? AND is_deleted = 0").get(resolvedStudentId);
    if (!studentExists) {
      return res.status(404).json({ error: 'Student user not found' });
    }
    if (studentExists.role !== 'student') {
      return res.status(400).json({ error: 'User is not a student' });
    }

    const existing = database.prepare(`
      SELECT * FROM course_enrolments
      WHERE student_id = ? AND course_id = ? AND is_deleted = 0
      ORDER BY id DESC LIMIT 1
    `).get(resolvedStudentId, course_id);

    if (existing) {
      return res.json({ ...existing, _idempotent: true });
    }

    const uid = uuidv4();
    const now = new Date().toISOString();
    const requestStatus = isSelfEnrol ? 'pending' : 'approved';
    const status = isSelfEnrol ? 'dropped' : 'active';
    const approvedBy = isSelfEnrol ? null : req.user.id;
    const approvedAt = isSelfEnrol ? null : now;
    const requestedAt = isSelfEnrol ? now : null;

    const result = database.prepare(`
      INSERT INTO course_enrolments (
        uid, student_id, course_id, status,
        request_status, requested_at, approved_by, approved_at,
        progress, sync_status
      )
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, 0, 'pending')
    `).run(
      uid, resolvedStudentId, course_id, status,
      requestStatus, requestedAt, approvedBy, approvedAt
    );

    const created = database.prepare('SELECT * FROM course_enrolments WHERE id = ?').get(result.lastInsertRowid);
    res.status(201).json(created);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ APPROVE ============

/**
 * @openapi
 * /api/course-enrolments/{id}/approve:
 *   post:
 *     summary: Approve a course enrolment
 *     description: |
 *       Approves the course enrolment: sets `request_status` to
 *       `approved`, activates the row, records the approver, and
 *       clears any rejection reason. Restricted to admin or teacher
 *       roles.
 *     tags:
 *       - Course Enrolments
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Approved enrolment
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Admin or Teacher only }
 *       404: { description: Course enrolment not found }
 *       500: { description: Server error }
 */
router.post('/:id/approve', auth, adminOrTeacher, (req, res) => {
  try {
    const database = getDb();
    const existing = database.prepare('SELECT * FROM course_enrolments WHERE id = ? AND is_deleted = 0').get(req.params.id);
    if (!existing) {
      return res.status(404).json({ error: 'Course enrolment not found' });
    }

    database.prepare(`
      UPDATE course_enrolments
      SET request_status   = 'approved',
          status           = 'active',
          approved_by      = ?,
          approved_at      = datetime('now', 'localtime'),
          rejection_reason = NULL,
          updated_at       = datetime('now', 'localtime'),
          sync_status      = 'pending'
      WHERE id = ?
    `).run(req.user.id, req.params.id);

    const updated = database.prepare('SELECT * FROM course_enrolments WHERE id = ?').get(req.params.id);
    res.json(updated);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ REJECT ============

/**
 * @openapi
 * /api/course-enrolments/{id}/reject:
 *   post:
 *     summary: Reject a course enrolment
 *     description: |
 *       Rejects the course enrolment: sets `request_status` to
 *       `rejected`, drops the row, clears the approver, and stores
 *       an optional rejection reason from the request body.
 *       Restricted to admin or teacher roles.
 *     tags:
 *       - Course Enrolments
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: false
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               reason: { type: string }
 *     responses:
 *       200:
 *         description: Rejected enrolment
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Admin or Teacher only }
 *       404: { description: Course enrolment not found }
 *       500: { description: Server error }
 */
router.post('/:id/reject', auth, adminOrTeacher, (req, res) => {
  try {
    const { rejection_reason } = req.body || {};
    const database = getDb();
    const existing = database.prepare('SELECT * FROM course_enrolments WHERE id = ? AND is_deleted = 0').get(req.params.id);
    if (!existing) {
      return res.status(404).json({ error: 'Course enrolment not found' });
    }

    database.prepare(`
      UPDATE course_enrolments
      SET request_status   = 'rejected',
          status           = 'dropped',
          rejection_reason = ?,
          approved_by      = NULL,
          approved_at      = NULL,
          updated_at       = datetime('now', 'localtime'),
          sync_status      = 'pending'
      WHERE id = ?
    `).run(rejection_reason || null, req.params.id);

    const updated = database.prepare('SELECT * FROM course_enrolments WHERE id = ?').get(req.params.id);
    res.json(updated);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ WITHDRAW ============

/**
 * @openapi
 * /api/course-enrolments/{id}/withdraw:
 *   post:
 *     summary: Withdraw a course enrolment
 *     description: |
 *       Withdraws the course enrolment: sets `request_status` to
 *       `withdrawn` and drops the row. Students may only withdraw
 *       their own enrolment; staff (admin, systems_admin, principal,
 *       teacher) may withdraw any.
 *     tags:
 *       - Course Enrolments
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     requestBody:
 *       required: false
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               reason: { type: string }
 *     responses:
 *       200:
 *         description: Withdrawn enrolment
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Students may only withdraw their own enrolment }
 *       404: { description: Course enrolment not found }
 *       500: { description: Server error }
 */
router.post('/:id/withdraw', auth, (req, res) => {
  try {
    const database = getDb();
    const existing = database.prepare('SELECT * FROM course_enrolments WHERE id = ? AND is_deleted = 0').get(req.params.id);
    if (!existing) {
      return res.status(404).json({ error: 'Course enrolment not found' });
    }

    const isStaff = ['school_admin', 'systems_admin', 'principal', 'teacher'].includes(req.user.role);
    const isOwner = Number(existing.student_id) === Number(req.user.id);
    if (!isStaff && !isOwner) {
      return res.status(403).json({ error: 'Students may only withdraw their own enrolment' });
    }

    database.prepare(`
      UPDATE course_enrolments
      SET request_status = 'withdrawn',
          status         = 'dropped',
          updated_at     = datetime('now', 'localtime'),
          sync_status    = 'pending'
      WHERE id = ?
    `).run(req.params.id);

    const updated = database.prepare('SELECT * FROM course_enrolments WHERE id = ?').get(req.params.id);
    res.json(updated);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ SOFT DELETE ============

/**
 * @openapi
 * /api/course-enrolments/{id}:
 *   delete:
 *     summary: Soft-delete a course enrolment
 *     description: |
 *       Soft-deletes the enrolment (`is_deleted = 1`) and marks it
 *       as `dropped`. Restricted to admin roles
 *       (`school_admin`, `systems_admin`, `principal`).
 *     tags:
 *       - Course Enrolments
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema: { type: integer }
 *     responses:
 *       200:
 *         description: Removed
 *         content:
 *           application/json:
 *             schema: { type: object }
 *       401: { description: Unauthorized }
 *       403: { description: Admin only }
 *       404: { description: Course enrolment not found }
 *       500: { description: Server error }
 */
router.delete('/:id', auth, adminOnly, (req, res) => {
  try {
    const result = getDb().prepare(`
      UPDATE course_enrolments
      SET is_deleted = 1, status = 'dropped',
          updated_at = datetime('now', 'localtime'),
          sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(req.params.id);

    if (result.changes === 0) {
      return res.status(404).json({ error: 'Course enrolment not found' });
    }
    res.json({ success: true, message: 'Course enrolment removed' });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

module.exports = router;