/**
 * Departments Controller
 * CRUD for the `departments` table — academic groups such as
 * 'Mathematics', 'Sciences', or 'Humanities' that own sets of subjects.
 * 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/departments in server/src/index.js.
 *
 * Note: this controller used to incorrectly serve the `classes` table.
 * It was rewritten to serve the actual `departments` table.
 */

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

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 department code
    try {
      db.exec(`ALTER TABLE departments ADD COLUMN code TEXT;`);
    } catch (e) {}

    // Backfill codes for existing departments if missing
    try {
      const depts = db.prepare(`SELECT id, name FROM departments WHERE code IS NULL OR code = ''`).all();
      if (depts.length > 0) {
        const updateCode = db.prepare(`UPDATE departments SET code = ? WHERE id = ?`);
        for (const dept of depts) {
          const cleaned = dept.name.trim().replace(/[^a-zA-Z\s]/g, '');
          const words = cleaned.split(/\s+/);
          let generatedCode = '';
          if (words.length >= 3) {
            generatedCode = words.map(w => w[0]).join('').toUpperCase();
          } else if (words.length === 2) {
            generatedCode = (words[0].substring(0, 2) + words[1].substring(0, 2)).toUpperCase();
          } else {
            const word = words[0];
            generatedCode = word.length >= 4 ? word.substring(0, 4).toUpperCase() : word.toUpperCase();
          }
          updateCode.run(generatedCode, dept.id);
        }
      }
    } catch (e) {
      console.error("Failed to backfill department codes:", e);
    }
  }
  return db;
};

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

// ============ DEPARTMENTS CRUD ============

/**
 * GET /api/departments
 * List all active departments with the head teacher's name and a count
 * of subjects assigned to each. Optional query: search.
 */
/**
 * @openapi
 * /api/departments:
 *   get:
 *     tags:
 *       - Departments
 *     summary: List all active departments
 *     description: Returns a list of all active departments with head teacher name, subject count, teacher count, and student count. Supports optional search filter.
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: search
 *         schema:
 *           type: string
 *         description: Search term to filter departments by name
 *     responses:
 *       '200':
 *         description: List of departments
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items:
 *                 $ref: '#/components/schemas/Department'
 *       '401':
 *         description: Unauthorized - No token provided or invalid token
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 error:
 *                   type: string
 *   post:
 *     tags:
 *       - Departments
 *     summary: Create a new department
 *     description: Creates a new department record. Admin access required.
 *     security:
 *       - bearerAuth: []
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             required:
 *               - name
 *             properties:
 *               name:
 *                 type: string
 *                 description: Department name (required)
 *               code:
 *                 type: string
 *                 description: Department code
 *               head_teacher_id:
 *                 type: integer
 *                 description: ID of the head teacher
 *               description:
 *                 type: string
 *                 description: Department description
 *               teacher_ids:
 *                 type: array
 *                 items:
 *                   type: integer
 *                 description: Array of teacher user IDs to assign to this department
 *     responses:
 *       '201':
 *         description: Department created successfully
 *         content:
 *           application/json:
 *             schema:
 *               $ref: '#/components/schemas/Department'
 *       '400':
 *         description: Validation error - Department name is required
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 error:
 *                   type: string
 *       '401':
 *         description: Unauthorized - No token provided or invalid token
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 error:
 *                   type: string
 *       '403':
 *         description: Forbidden - Admin or Principal access required
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 error:
 *                   type: string
 */
router.get('/', auth, adminOnly, (req, res) => {
  try {
    const { search } = req.query;
    let query = `
      SELECT d.*,
        (SELECT COUNT(*) FROM subjects_new WHERE department_id = d.id AND is_deleted = 0) as subject_count,
        (SELECT COUNT(*) FROM staff_records WHERE department = d.name AND is_active = 1 AND is_deleted = 0) as teacher_count,
        (SELECT COUNT(DISTINCT student_id) FROM enrollments e 
         JOIN subjects_new s ON e.class_id = s.class_id 
         WHERE s.department_id = d.id AND e.status = 'active') as student_count,
        t.first_name || ' ' || t.last_name as head_teacher_name
      FROM departments d
      LEFT JOIN users t ON d.head_teacher_id = t.id
      WHERE d.is_deleted = 0
    `;
    const params = [];
    if (search) {
      query += ' AND d.name LIKE ?';
      params.push(`%${search}%`);
    }
    query += ' ORDER BY d.name';
    res.json(getDb().prepare(query).all(...params));
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

/**
 * GET /api/departments/:id
 * Return a single department with its subjects.
 */
/**
 * @openapi
 * /api/departments/{id}:
 *   get:
 *     tags:
 *       - Departments
 *     summary: Get department by ID
 *     description: Returns a single department with its subjects and teachers
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema:
 *           type: integer
 *         description: Department ID
 *     responses:
 *       '200':
 *         description: Department details with subjects and teachers
 *         content:
 *           application/json:
 *             schema:
 *               $ref: '#/components/schemas/DepartmentDetail'
 *       '401':
 *         description: Unauthorized - No token provided or invalid token
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 error:
 *                   type: string
 *       '404':
 *         description: Department not found
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 error:
 *                   type: string
 *   put:
 *     tags:
 *       - Departments
 *     summary: Update department
 *     description: Updates an existing department. Admin access required. If department name changes, all staff_records referencing the old name are updated.
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema:
 *           type: integer
 *         description: Department ID
 *     requestBody:
 *       required: true
 *       content:
 *         application/json:
 *           schema:
 *             type: object
 *             properties:
 *               name:
 *                 type: string
 *                 description: Department name
 *               code:
 *                 type: string
 *                 description: Department code
 *               head_teacher_id:
 *                 type: integer
 *                 nullable: true
 *                 description: ID of the head teacher
 *               description:
 *                 type: string
 *                 nullable: true
 *                 description: Department description
 *               teacher_ids:
 *                 type: array
 *                 items:
 *                   type: integer
 *                 description: Array of teacher user IDs to assign to this department
 *     responses:
 *       '200':
 *         description: Department updated successfully
 *         content:
 *           application/json:
 *             schema:
 *               $ref: '#/components/schemas/Department'
 *       '401':
 *         description: Unauthorized - No token provided or invalid token
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 error:
 *                   type: string
 *       '403':
 *         description: Forbidden - Admin or Principal access required
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 error:
 *                   type: string
 *       '404':
 *         description: Department not found
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 error:
 *                   type: string
 *   delete:
 *     tags:
 *       - Departments
 *     summary: Delete department
 *     description: Soft deletes a department (sets is_deleted=1). Admin access required. Refuses if subjects still reference the department.
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: path
 *         name: id
 *         required: true
 *         schema:
 *           type: integer
 *         description: Department ID
 *     responses:
 *       '200':
 *         description: Department archived successfully
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 success:
 *                   type: boolean
 *                   example: true
 *                 message:
 *                   type: string
 *                   example: Department archived
 *       '400':
 *         description: Cannot archive department with active subjects
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 error:
 *                   type: string
 *                   example: Cannot archive department with active subjects. Reassign or archive those first.
 *       '401':
 *         description: Unauthorized - No token provided or invalid token
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 error:
 *                   type: string
 *       '403':
 *         description: Forbidden - Admin or Principal access required
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 error:
 *                   type: string
 *       '404':
 *         description: Department not found
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 error:
 *                   type: string
 */
router.get('/:id', auth, (req, res) => {
  try {
    const dept = getDb().prepare(`
      SELECT d.*,
        t.first_name || ' ' || t.last_name as head_teacher_name
      FROM departments d
      LEFT JOIN users t ON d.head_teacher_id = t.id
      WHERE d.id = ? AND d.is_deleted = 0
    `).get(req.params.id);
    if (!dept) return res.status(404).json({ error: 'Department not found' });

    const subjects = getDb().prepare(`
      SELECT s.*, c.name as class_name, c.section as class_section
      FROM subjects_new s
      LEFT JOIN classes c ON s.class_id = c.id
      WHERE s.department_id = ? AND s.is_deleted = 0
      ORDER BY s.name
    `).all(req.params.id);

    const teachers = getDb().prepare(`
      SELECT u.id, u.first_name, u.last_name, u.email, u.phone, sr.designation
      FROM staff_records sr
      JOIN users u ON sr.user_id = u.id
      WHERE sr.department = ? AND sr.is_active = 1 AND sr.is_deleted = 0 AND u.is_deleted = 0
      ORDER BY u.last_name, u.first_name
    `).all(dept.name);

    res.json({ ...dept, subjects, teachers });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

/**
 * POST /api/departments
 */
router.post('/', auth, adminOnly, (req, res) => {
  try {
    const { name, code, head_teacher_id, description, teacher_ids } = req.body;
    if (!name) return res.status(400).json({ error: 'Department name is required' });

    const db = getDb();
    const result = db.prepare(`
      INSERT INTO departments (uid, name, code, head_teacher_id, description, sync_status)
      VALUES (?, ?, ?, ?, ?, 'pending')
    `).run(uuidv4(), name, code || null, head_teacher_id || null, description || null);

    const deptId = result.lastInsertRowid;

    // Assign teachers to department
    if (Array.isArray(teacher_ids)) {
      const updateUser = db.prepare(`UPDATE users SET department_id = ?, sync_status = 'pending' WHERE id = ?`);
      const updateStaff = db.prepare(`UPDATE staff_records SET department = ?, sync_status = 'pending' WHERE user_id = ?`);
      for (const tId of teacher_ids) {
        updateUser.run(deptId, tId);
        updateStaff.run(name, tId);
      }
    }

    res.status(201).json(db.prepare('SELECT * FROM departments WHERE id = ?').get(deptId));
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

/**
 * PUT /api/departments/:id
 */
router.put('/:id', auth, adminOnly, (req, res) => {
  try {
    const { name, code, head_teacher_id, description, teacher_ids } = req.body;
    const db = getDb();
    
    // Get existing department info
    const dept = db.prepare('SELECT * FROM departments WHERE id = ? AND is_deleted = 0').get(req.params.id);
    if (!dept) return res.status(404).json({ error: 'Department not found' });

    const newName = name !== undefined ? name : dept.name;
    const newCode = code !== undefined ? code : dept.code;
    const newHead = head_teacher_id !== undefined ? head_teacher_id : dept.head_teacher_id;
    const newDesc = description !== undefined ? description : dept.description;

    db.prepare(`
      UPDATE departments SET
        name = ?,
        code = ?,
        head_teacher_id = ?,
        description = ?,
        updated_at = datetime(\'now\', \'localtime\'),
        sync_status = 'pending'
      WHERE id = ? AND is_deleted = 0
    `).run(newName, newCode || null, newHead || null, newDesc || null, req.params.id);

    // If department name changed, we must update all staff_records that refer to the old name
    if (newName !== dept.name) {
      db.prepare(`
        UPDATE staff_records
        SET department = ?, sync_status = 'pending'
        WHERE department = ? AND is_deleted = 0
      `).run(newName, dept.name);
    }

    // Update teacher assignments if teacher_ids is provided
    if (Array.isArray(teacher_ids)) {
      // 1. Remove users currently in this department who are NOT in the new list
      const currentTeachers = db.prepare(`SELECT id FROM users WHERE department_id = ? AND is_deleted = 0`).all(req.params.id);
      const currentIds = currentTeachers.map(t => t.id);
      const toRemove = currentIds.filter(id => !teacher_ids.includes(id));
      
      const removeUser = db.prepare(`UPDATE users SET department_id = NULL, sync_status = 'pending' WHERE id = ?`);
      const removeStaff = db.prepare(`UPDATE staff_records SET department = NULL, sync_status = 'pending' WHERE user_id = ?`);
      for (const tId of toRemove) {
        removeUser.run(tId);
        removeStaff.run(tId);
      }

      // 2. Add/update the new list of teachers
      const updateUser = db.prepare(`UPDATE users SET department_id = ?, sync_status = 'pending' WHERE id = ?`);
      const updateStaff = db.prepare(`UPDATE staff_records SET department = ?, sync_status = 'pending' WHERE user_id = ?`);
      for (const tId of teacher_ids) {
        updateUser.run(req.params.id, tId);
        updateStaff.run(newName, tId);
      }
    }

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

/**
 * DELETE /api/departments/:id
 * Soft delete. Refuses if subjects still reference the department.
 */
router.delete('/:id', auth, adminOnly, (req, res) => {
  try {
    const subjectCount = getDb().prepare(
      'SELECT COUNT(*) as c FROM subjects_new WHERE department_id = ? AND is_deleted = 0'
    ).get(req.params.id).c;
    if (subjectCount > 0) {
      return res.status(400).json({
        error: 'Cannot archive department with active subjects. Reassign or archive those first.',
      });
    }
    const result = getDb().prepare(`
      UPDATE departments
      SET is_deleted = 1, 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: 'Department not found' });
    res.json({ success: true, message: 'Department archived' });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

module.exports = router;
