/**
 * Subjects Controller
 * CRUD for the `subjects_new` table — the canonical subject records with
 * department and class associations. Architecture rules:
 *   - Sync-aware writes: every INSERT/UPDATE sets sync_status='pending',
 *     updates updated_at, mints a new uid (v4)
 *   - Soft delete: is_deleted = 1 instead of DELETE
 *   - DB access via better-sqlite3 with the getDb() helper
 *   - All routes are auth-protected; writes are admin-only
 *
 * Mounted at /api/subjects in server/src/index.js.
 */

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

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');
    
    // Auto-migrate tables to support syllabus files (DEPRECATED - new uploads use the attachments table)
    try {
      db.exec(`ALTER TABLE subjects_new ADD COLUMN syllabus_url TEXT;`);
    } catch (e) {}
    try {
      db.exec(`ALTER TABLE subjects_new ADD COLUMN syllabus_name TEXT;`);
    } catch (e) {}
  }
  return db;
};

// Configure Multer for syllabus file uploads
const storage = multer.diskStorage({
  destination: function (req, file, cb) {
    const uploadDir = path.join(__dirname, '../../uploads');
    if (!fs.existsSync(uploadDir)){
        fs.mkdirSync(uploadDir, { recursive: true });
    }
    cb(null, uploadDir);
  },
  filename: function (req, file, cb) {
    const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
    cb(null, uniqueSuffix + path.extname(file.originalname));
  }
});

const upload = multer({ 
  storage: storage,
  limits: { fileSize: 10 * 1024 * 1024 } // 10MB limit
});

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 or Principal access required' });
  }
  next();
};

// Teachers can create/update the subjects they teach; admins can create any.
// PUT/DELETE still admin-only — those affect the global subject catalog.
const teacherOrAdminOnly = (req, res, next) => {
  if (
    ['school_admin', 'systems_admin', 'principal', 'teacher'].includes(req.user.role)
  ) {
    return next();
  }
  return res.status(403).json({ error: 'Teacher or Admin access required' });
};

// ============ SUBJECTS CRUD ============

/**
 * GET /api/subjects
 * List all active subjects with joined department and class names so the
 * frontend can render rich rows without extra lookups.
 * Optional query: department_id, class_id, teacher_id, search.
 *
 * Role behaviour:
 *   - admin / principal: see all subjects
 *   - teacher: by default sees only subjects they teach (teacher_id = self),
 *     unless ?teacher_id= is supplied (admins use this to inspect anyone)
 *   - other roles: see all (read-only)
 */
/**
 * @openapi
 * /api/subjects:
 *   get:
 *     summary: List all active subjects
 *     description: Retrieve all active subjects with joined department and class names. Teachers see only their own subjects unless they have elevated roles (admin/principal).
 *     tags:
 *       - Subjects
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: department_id
 *         schema:
 *           type: integer
 *         description: Filter by department ID
 *       - in: query
 *         name: class_id
 *         schema:
 *           type: integer
 *         description: Filter by class ID
 *       - in: query
 *         name: teacher_id
 *         schema:
 *           type: integer
 *         description: Filter by teacher ID
 *       - in: query
 *         name: search
 *         schema:
 *           type: string
 *         description: Search by subject name or code
 *     responses:
 *       '200':
 *         description: List of subjects
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items:
 *                 $ref: '#/components/schemas/Subject'
 *       '401':
 *         $ref: '#/components/responses/Unauthorized'
 *   post:
 *     summary: Create a new subject
 *     description: Add a subject to the catalog. Teachers are auto-assigned as the teacher of subjects they create. Accepts multipart/form-data for syllabus file upload.
 *     tags:
 *       - Subjects
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         multipart/form-data:
 *           schema:
 *             $ref: '#/components/schemas/SubjectCreate'
 *     responses:
 *       '201':
 *         description: Subject created successfully
 *         content:
 *           application/json:
 *             schema:
 *               $ref: '#/components/schemas/Subject'
 *       '400':
 *         $ref: '#/components/responses/BadRequest'
 *       '401':
 *         $ref: '#/components/responses/Unauthorized'
 *       '403':
 *         $ref: '#/components/responses/Forbidden'
 *       '500':
 *         $ref: '#/components/responses/InternalError'
 */
router.get('/', auth, (req, res) => {
  try {
    const { department_id, class_id, teacher_id, search } = req.query;
    let query = `
      SELECT s.*,
        d.name as department_name,
        c.name as class_name, c.section as class_section,
        t.first_name || ' ' || t.last_name as teacher_name
      FROM subjects_new s
      LEFT JOIN departments d ON s.department_id = d.id
      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 (department_id) { query += ' AND s.department_id = ?'; params.push(department_id); }
    if (class_id) { query += ' AND s.class_id = ?'; params.push(class_id); }

    // Teacher scoping: teachers see only their own subjects unless they
    // explicitly pass teacher_id= or are admin/principal.
    const isElevated = ['school_admin', 'systems_admin', 'principal'].includes(req.user.role);
    if (teacher_id) {
      query += ' AND s.teacher_id = ?';
      params.push(teacher_id);
    } else if (req.user.role === 'teacher') {
      query += ' AND s.teacher_id = ?';
      params.push(req.user.id);
    } else if (req.user.role === 'student') {
      // PR 3 §6.3 — students only see subjects for classes they're actively
      // enrolled in. Keeps the Resources hub focused on the learner's own
      // coursework instead of the whole catalog.
      query += ` AND s.class_id IN (
        SELECT class_id FROM enrollments WHERE student_id = ? AND is_deleted = 0 AND status = 'active'
      )`;
      params.push(req.user.id);
    } else if (req.user.role === 'parent') {
      // PR 3 §7.2 — parents see subjects for any class one of their children
      // is actively enrolled in.
      query += ` AND s.class_id IN (
        SELECT class_id FROM enrollments WHERE student_id IN (
          SELECT student_id FROM parent_students WHERE parent_id = ? AND is_deleted = 0
        ) AND is_deleted = 0 AND status = 'active'
      )`;
      params.push(req.user.id);
    }
    void isElevated; // explicit admin/principal already unrestricted above

    if (search) {
      query += ' AND (s.name LIKE ? OR s.code LIKE ?)';
      const term = `%${search}%`;
      params.push(term, term);
    }
    query += ' ORDER BY s.name';
    res.json(getDb().prepare(query).all(...params));
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

/**
 * GET /api/subjects/:id
 */
/**
 * @openapi
 * /api/subjects/{id}:
 *   get:
 *     summary: Get a subject by ID
 *     tags:
 *       - Subjects
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema:
 *           type: integer
 *         description: Subject ID
 *     responses:
 *       '200':
 *         description: Subject details
 *         content:
 *           application/json:
 *             schema:
 *               $ref: '#/components/schemas/Subject'
 *       '401':
 *         $ref: '#/components/responses/Unauthorized'
 *       '404':
 *         $ref: '#/components/responses/NotFound'
 *       '500':
 *         $ref: '#/components/responses/InternalError'
 *   put:
 *     summary: Update a subject
 *     description: Modify an existing subject. Teachers can only edit subjects they teach and are restricted to syllabus changes only. Admins can update all fields. Accepts multipart/form-data for syllabus file upload.
 *     tags:
 *       - Subjects
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema:
 *           type: integer
 *         description: Subject ID
 *     requestBody:
 *       required: true
 *       content:
 *         multipart/form-data:
 *           schema:
 *             $ref: '#/components/schemas/SubjectUpdate'
 *     responses:
 *       '200':
 *         description: Subject updated successfully
 *         content:
 *           application/json:
 *             schema:
 *               $ref: '#/components/schemas/Subject'
 *       '401':
 *         $ref: '#/components/responses/Unauthorized'
 *       '403':
 *         $ref: '#/components/responses/Forbidden'
 *       '404':
 *         $ref: '#/components/responses/NotFound'
 *       '500':
 *         $ref: '#/components/responses/InternalError'
 *   delete:
 *     summary: Archive a subject
 *     description: Soft delete a subject (sets is_deleted=1). Requires admin or principal access. Cannot delete if subject has active courses attached.
 *     tags:
 *       - Subjects
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema:
 *           type: integer
 *         description: Subject ID
 *     responses:
 *       '200':
 *         description: Subject archived successfully
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success:
 *                   type: boolean
 *                   example: true
 *                 message:
 *                   type: string
 *                   example: Subject archived
 *       '400':
 *         description: Cannot archive subject with active courses
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 error:
 *                   type: string
 *                   example: Cannot archive subject with active courses. Reassign or archive those first.
 *       '401':
 *         $ref: '#/components/responses/Unauthorized'
 *       '403':
 *         $ref: '#/components/responses/Forbidden'
 *       '404':
 *         $ref: '#/components/responses/NotFound'
 *       '500':
 *         $ref: '#/components/responses/InternalError'
 */
router.get('/:id', auth, (req, res) => {
  try {
    const subject = getDb().prepare(`
      SELECT s.*,
        d.name as department_name,
        c.name as class_name, c.section as class_section,
        t.first_name || ' ' || t.last_name as teacher_name
      FROM subjects_new s
      LEFT JOIN departments d ON s.department_id = d.id
      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 (!subject) return res.status(404).json({ error: 'Subject not found' });
    res.json(subject);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

/**
 * POST /api/subjects
 */
router.post('/', auth, teacherOrAdminOnly, upload.single('syllabus'), (req, res) => {
  try {
    const { name, code, department_id, class_id, teacher_id, credits, description } = req.body;
    if (!name) return res.status(400).json({ error: 'Subject name is required' });

    let syllabus_url = null;
    let syllabus_name = null;
    if (req.file) {
      syllabus_url = `/uploads/${req.file.filename}`;
      syllabus_name = req.file.originalname;
    }

    const deptId = department_id ? parseInt(department_id) : null;
    const clsId = class_id ? parseInt(class_id) : null;
    // Teachers are auto-assigned as the teacher of any subject they create,
    // unless they explicitly hand it to someone else (admin-only operation).
    let tchrId = teacher_id ? parseInt(teacher_id) : null;
    if (req.user.role === 'teacher' && !tchrId) {
      tchrId = req.user.id;
    }
    const creds = credits ? parseInt(credits) : 0;

    const result = getDb().prepare(`
      INSERT INTO subjects_new (uid, name, code, department_id, class_id, teacher_id, credits, description, syllabus_url, syllabus_name, sync_status)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending')
    `).run(
      uuidv4(),
      name,
      code || null,
      deptId,
      clsId,
      tchrId,
      creds,
      description || null,
      syllabus_url,
      syllabus_name
    );
    res.status(201).json(getDb().prepare('SELECT * FROM subjects_new WHERE id = ?').get(result.lastInsertRowid));
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

/**
 * PUT /api/subjects/:id
 */
router.put('/:id', auth, teacherOrAdminOnly, upload.single('syllabus'), (req, res) => {
  try {
    const { name, code, department_id, class_id, teacher_id, credits, description, delete_syllabus } = req.body;
    
    // Check if subject exists
    const subject = getDb().prepare('SELECT * FROM subjects_new WHERE id = ? AND is_deleted = 0').get(req.params.id);
    if (!subject) return res.status(404).json({ error: 'Subject not found' });

    const isTeacher = req.user.role === 'teacher';
    if (isTeacher && subject.teacher_id !== req.user.id) {
      return res.status(403).json({ error: 'Forbidden: You can only edit your own assigned subjects' });
    }

    let syllabus_url = subject.syllabus_url;
    let syllabus_name = subject.syllabus_name;

    if (delete_syllabus === 'true') {
      syllabus_url = null;
      syllabus_name = null;
    } else if (req.file) {
      syllabus_url = `/uploads/${req.file.filename}`;
      syllabus_name = req.file.originalname;
    }

    // Force teachers to retain existing record properties, allowing only syllabus changes
    const deptId = isTeacher ? subject.department_id : (department_id !== undefined ? (department_id ? parseInt(department_id) : null) : subject.department_id);
    const clsId = isTeacher ? subject.class_id : (class_id !== undefined ? (class_id ? parseInt(class_id) : null) : subject.class_id);
    const tchrId = isTeacher ? subject.teacher_id : (teacher_id !== undefined ? (teacher_id ? parseInt(teacher_id) : null) : subject.teacher_id);
    const creds = isTeacher ? subject.credits : (credits !== undefined ? (credits ? parseInt(credits) : 0) : subject.credits);
    const desc = isTeacher ? subject.description : (description !== undefined ? (description ? description : null) : subject.description);
    const sName = isTeacher ? subject.name : (name !== undefined ? name : subject.name);
    const sCode = isTeacher ? subject.code : (code !== undefined ? (code ? code : null) : subject.code);

    getDb().prepare(`
      UPDATE subjects_new SET
        name = ?,
        code = ?,
        department_id = ?,
        class_id = ?,
        teacher_id = ?,
        credits = ?,
        description = ?,
        syllabus_url = ?,
        syllabus_name = ?,
        updated_at = CURRENT_TIMESTAMP,
        sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(sName, sCode, deptId, clsId, tchrId, creds, desc, syllabus_url, syllabus_name, req.params.id);

    res.json(getDb().prepare('SELECT * FROM subjects_new WHERE id = ?').get(req.params.id));
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

/**
 * DELETE /api/subjects/:id
 * Soft delete. Refuses if the subject still has active courses attached.
 */
router.delete('/:id', auth, adminOnly, (req, res) => {
  try {
    const courseCount = getDb().prepare(
      'SELECT COUNT(*) as c FROM courses WHERE subject_id = ? AND is_deleted = 0'
    ).get(req.params.id).c;
    if (courseCount > 0) {
      return res.status(400).json({
        error: 'Cannot archive subject with active courses. Reassign or archive those first.',
      });
    }
    const result = getDb().prepare(`
      UPDATE subjects_new
      SET is_deleted = 1, updated_at = CURRENT_TIMESTAMP, sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(req.params.id);
    if (result.changes === 0) return res.status(404).json({ error: 'Subject not found' });
    res.json({ success: true, message: 'Subject archived' });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;
