/**
 * cohorts.controller.js — student cohort CRUD + linking.
 *
 * Phase 1 PR 2 (2026-07-22). Implements the canonical ZIMSEC/IGCSE/AS/A2
 * cohort concept. Endpoints:
 *
 *   GET    /api/cohorts                            — list with filters
 *   GET    /api/cohorts/:id                        — detail (cohort + classes + students + exam_groups)
 *   POST   /api/cohorts                            — create (admin only)
 *   PUT    /api/cohorts/:id                        — update (admin only)
 *   DELETE /api/cohorts/:id                        — soft delete (admin only)
 *   POST   /api/cohorts/:id/students               — bulk add students
 *   DELETE /api/cohorts/:id/students/:studentId    — remove student
 *   POST   /api/cohorts/:id/classes                — link a class
 *   DELETE /api/cohorts/:id/classes/:classId       — unlink a class
 *   POST   /api/cohorts/:id/exam-groups            — link an exam paper set
 *   DELETE /api/cohorts/:id/exam-groups/:examGroupId — unlink
 *
 * Auth: read = school_admin | systems_admin | principal. Write = admin.
 */

'use strict';

const express = require('express');
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');
  }
  return db;
};

const router = express.Router();

// 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 requireAdmin = (req, res, next) => {
  if (!['school_admin', 'systems_admin'].includes(req.user.role)) {
    return res.status(403).json({ error: 'Admin access required' });
  }
  next();
};

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

const AuditService = require('../services/AuditService');

const SUPPORTED_PROGRAMMES = new Set(['zimsec', 'igcse', 'as', 'a2', 'primary', 'other']);
const COHORT_STATUSES = new Set(['active', 'inactive', 'transferred', 'graduated']);

// Helper: shape a cohort row for the response
function shapeCohort(row) {
  if (!row) return null;
  return {
    id: row.id,
    uid: row.uid,
    name: row.name,
    programme: row.programme,
    level: row.level,
    academic_year: row.academic_year,
    description: row.description,
    start_date: row.start_date,
    end_date: row.end_date,
    is_active: !!row.is_active,
    student_count: row.student_count ?? undefined,
    class_count: row.class_count ?? undefined,
    exam_group_count: row.exam_group_count ?? undefined,
    created_at: row.created_at,
    updated_at: row.updated_at,
  };
}

// ---------- GET /api/cohorts -----------------------------------------------

router.get('/', auth, canRead, (req, res) => {
  try {
    const { programme, academic_year, is_active, search } = req.query;
    const where = ['c.is_deleted = 0'];
    const params = [];
    if (programme) { where.push('c.programme = ?'); params.push(programme); }
    if (academic_year) { where.push('c.academic_year = ?'); params.push(academic_year); }
    if (is_active !== undefined) { where.push('c.is_active = ?'); params.push(is_active === 'true' ? 1 : 0); }
    if (search) { where.push('(c.name LIKE ? OR c.description LIKE ?)'); const term = `%${search}%`; params.push(term, term); }

    const rows = getDb().prepare(`
      SELECT c.*,
        (SELECT COUNT(*) FROM cohort_students cs WHERE cs.cohort_id = c.id AND cs.is_deleted = 0 AND cs.status = 'active') AS student_count,
        (SELECT COUNT(*) FROM cohort_classes cc WHERE cc.cohort_id = c.id AND cc.is_deleted = 0) AS class_count,
        (SELECT COUNT(*) FROM cohort_exam_groups ce WHERE ce.cohort_id = c.id AND ce.is_deleted = 0) AS exam_group_count
      FROM student_cohorts c
      WHERE ${where.join(' AND ')}
      ORDER BY c.programme, c.level, c.name
    `).all(...params);
    res.json({ cohorts: rows.map(shapeCohort) });
  } catch (err) {
    console.error('cohorts.list error:', err);
    res.status(500).json({ error: 'Internal Server Error', message: err.message });
  }
});

// ---------- GET /api/cohorts/:id -------------------------------------------

router.get('/:id', auth, canRead, (req, res) => {
  try {
    const id = Number(req.params.id);
    const row = getDb().prepare(`
      SELECT c.*,
        (SELECT COUNT(*) FROM cohort_students cs WHERE cs.cohort_id = c.id AND cs.is_deleted = 0 AND cs.status = 'active') AS student_count,
        (SELECT COUNT(*) FROM cohort_classes cc WHERE cc.cohort_id = c.id AND cc.is_deleted = 0) AS class_count,
        (SELECT COUNT(*) FROM cohort_exam_groups ce WHERE ce.cohort_id = c.id AND ce.is_deleted = 0) AS exam_group_count
      FROM student_cohorts c
      WHERE c.id = ? AND c.is_deleted = 0
    `).get(id);
    if (!row) return res.status(404).json({ error: 'Cohort not found' });

    const students = getDb().prepare(`
      SELECT u.id, u.uid, u.email, u.first_name, u.last_name, cs.status, cs.joined_at
      FROM cohort_students cs
      JOIN users u ON u.id = cs.student_id
      WHERE cs.cohort_id = ? AND cs.is_deleted = 0
      ORDER BY u.last_name, u.first_name
    `).all(id);

    const classes = getDb().prepare(`
      SELECT c.id, c.uid, c.name, c.section, c.capacity, c.class_teacher_id,
        u.first_name || ' ' || u.last_name AS class_teacher_name
      FROM cohort_classes cc
      JOIN classes c ON c.id = cc.class_id
      LEFT JOIN users u ON u.id = c.class_teacher_id
      WHERE cc.cohort_id = ? AND cc.is_deleted = 0
      ORDER BY c.name, c.section
    `).all(id);

    const examGroups = getDb().prepare(`
      SELECT eg.id, eg.uid, eg.name, eg.exam_type, eg.academic_year, eg.term
      FROM cohort_exam_groups ce
      JOIN exam_groups eg ON eg.id = ce.exam_group_id
      WHERE ce.cohort_id = ? AND ce.is_deleted = 0
      ORDER BY eg.name
    `).all(id);

    res.json({
      ...shapeCohort(row),
      students,
      classes,
      exam_groups: examGroups,
    });
  } catch (err) {
    console.error('cohorts.get error:', err);
    res.status(500).json({ error: 'Internal Server Error', message: err.message });
  }
});

// ---------- POST /api/cohorts ----------------------------------------------

router.post('/', auth, requireAdmin, async (req, res) => {
  try {
    const { name, programme, level = null, academic_year = null, description = null, start_date = null, end_date = null, is_active = true } = req.body;
    if (!name) return res.status(400).json({ error: 'name is required' });
    if (!programme || !SUPPORTED_PROGRAMMES.has(programme)) {
      return res.status(400).json({ error: `programme must be one of: ${Array.from(SUPPORTED_PROGRAMMES).join(', ')}` });
    }

    const uid = uuidv4();
    const result = getDb().prepare(`
      INSERT INTO student_cohorts (uid, name, programme, level, academic_year, description, start_date, end_date, is_active)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(uid, name, programme, level, academic_year, description, start_date, end_date, is_active ? 1 : 0);
    const row = getDb().prepare('SELECT * FROM student_cohorts WHERE id = ?').get(result.lastInsertRowid);

    await AuditService.log(req, 'CREATE_COHORT', {
      entityType: 'cohort',
      entityId: row.id,
      name,
      programme,
      level,
      academic_year,
    });

    res.status(201).json(shapeCohort(row));
  } catch (err) {
    console.error('cohorts.create error:', err);
    res.status(500).json({ error: 'Internal Server Error', message: err.message });
  }
});

// ---------- PUT /api/cohorts/:id -----------------------------------------

router.put('/:id', auth, requireAdmin, async (req, res) => {
  try {
    const id = Number(req.params.id);
    const existing = getDb().prepare('SELECT * FROM student_cohorts WHERE id = ? AND is_deleted = 0').get(id);
    if (!existing) return res.status(404).json({ error: 'Cohort not found' });

    const fields = ['name', 'programme', 'level', 'academic_year', 'description', 'start_date', 'end_date', 'is_active'];
    const updates = [];
    const values = [];
    for (const f of fields) {
      if (f in req.body) {
        if (f === 'programme' && !SUPPORTED_PROGRAMMES.has(req.body[f])) {
          return res.status(400).json({ error: `programme must be one of: ${Array.from(SUPPORTED_PROGRAMMES).join(', ')}` });
        }
        updates.push(`${f} = ?`);
        values.push(f === 'is_active' ? (req.body[f] ? 1 : 0) : req.body[f]);
      }
    }
    if (updates.length === 0) {
      return res.status(400).json({ error: 'No updatable fields provided' });
    }
    updates.push("updated_at = datetime('now','localtime')", "sync_status = 'pending'");
    values.push(id);
    getDb().prepare(`UPDATE student_cohorts SET ${updates.join(', ')} WHERE id = ?`).run(...values);
    const row = getDb().prepare('SELECT * FROM student_cohorts WHERE id = ?').get(id);

    await AuditService.log(req, 'UPDATE_COHORT', {
      entityType: 'cohort',
      entityId: id,
      fields: Object.keys(req.body),
    });

    res.json(shapeCohort(row));
  } catch (err) {
    console.error('cohorts.update error:', err);
    res.status(500).json({ error: 'Internal Server Error', message: err.message });
  }
});

// ---------- DELETE /api/cohorts/:id (soft) ---------------------------------

router.delete('/:id', auth, requireAdmin, async (req, res) => {
  try {
    const id = Number(req.params.id);
    const existing = getDb().prepare('SELECT * FROM student_cohorts WHERE id = ? AND is_deleted = 0').get(id);
    if (!existing) return res.status(404).json({ error: 'Cohort not found' });

    getDb().prepare(`
      UPDATE student_cohorts
      SET is_deleted = 1, is_active = 0, updated_at = datetime('now','localtime'), sync_status = 'pending'
      WHERE id = ?
    `).run(id);

    // Also soft-delete the M:N links
    getDb().prepare('UPDATE cohort_students SET is_deleted = 1, updated_at = datetime(\'now\',\'localtime\') WHERE cohort_id = ?').run(id);
    getDb().prepare('UPDATE cohort_classes SET is_deleted = 1, updated_at = datetime(\'now\',\'localtime\') WHERE cohort_id = ?').run(id);
    getDb().prepare('UPDATE cohort_exam_groups SET is_deleted = 1, updated_at = datetime(\'now\',\'localtime\') WHERE cohort_id = ?').run(id);

    await AuditService.log(req, 'DELETE_COHORT', { entityType: 'cohort', entityId: id, name: existing.name });
    res.json({ ok: true, id });
  } catch (err) {
    console.error('cohorts.delete error:', err);
    res.status(500).json({ error: 'Internal Server Error', message: err.message });
  }
});

// ---------- POST /api/cohorts/:id/students ---------------------------------

router.post('/:id/students', auth, requireAdmin, async (req, res) => {
  try {
    const id = Number(req.params.id);
    const { student_ids } = req.body;
    if (!Array.isArray(student_ids) || student_ids.length === 0) {
      return res.status(400).json({ error: 'student_ids (non-empty array) is required' });
    }
    const cohort = getDb().prepare('SELECT id FROM student_cohorts WHERE id = ? AND is_deleted = 0').get(id);
    if (!cohort) return res.status(404).json({ error: 'Cohort not found' });

    // Verify all students exist + are students
    const placeholders = student_ids.map(() => '?').join(',');
    const validStudents = getDb().prepare(`SELECT id FROM users WHERE id IN (${placeholders}) AND role = 'student' AND is_deleted = 0`).all(...student_ids);
    if (validStudents.length !== student_ids.length) {
      return res.status(400).json({ error: 'One or more student_ids are invalid' });
    }

    const stmt = getDb().prepare(`
      INSERT INTO cohort_students (uid, cohort_id, student_id, status)
      VALUES (?, ?, ?, 'active')
      ON CONFLICT (cohort_id, student_id) DO NOTHING
    `);
    const added = [];
    const transaction = getDb().transaction(() => {
      for (const sid of student_ids) {
        const r = stmt.run(uuidv4(), id, sid);
        if (r.changes > 0) added.push(sid);
      }
    });
    transaction();

    await AuditService.log(req, 'ADD_COHORT_STUDENTS', {
      entityType: 'cohort',
      entityId: id,
      studentIds: added,
    });
    res.status(201).json({ ok: true, added, count: added.length });
  } catch (err) {
    console.error('cohorts.addStudents error:', err);
    res.status(500).json({ error: 'Internal Server Error', message: err.message });
  }
});

router.delete('/:id/students/:studentId', auth, requireAdmin, async (req, res) => {
  try {
    const id = Number(req.params.id);
    const studentId = Number(req.params.studentId);
    const r = getDb().prepare(`
      UPDATE cohort_students SET is_deleted = 1, updated_at = datetime('now','localtime')
      WHERE cohort_id = ? AND student_id = ? AND is_deleted = 0
    `).run(id, studentId);
    if (r.changes === 0) return res.status(404).json({ error: 'Student is not in this cohort' });
    await AuditService.log(req, 'REMOVE_COHORT_STUDENT', { entityType: 'cohort', entityId: id, studentId });
    res.json({ ok: true });
  } catch (err) {
    console.error('cohorts.removeStudent error:', err);
    res.status(500).json({ error: 'Internal Server Error', message: err.message });
  }
});

// ---------- POST /api/cohorts/:id/classes ---------------------------------

router.post('/:id/classes', auth, requireAdmin, async (req, res) => {
  try {
    const id = Number(req.params.id);
    const { class_id } = req.body;
    if (!class_id) return res.status(400).json({ error: 'class_id is required' });
    const cohort = getDb().prepare('SELECT id FROM student_cohorts WHERE id = ? AND is_deleted = 0').get(id);
    if (!cohort) return res.status(404).json({ error: 'Cohort not found' });
    const cls = getDb().prepare('SELECT id FROM classes WHERE id = ? AND is_deleted = 0').get(class_id);
    if (!cls) return res.status(400).json({ error: 'Invalid class_id' });
    try {
      getDb().prepare('INSERT INTO cohort_classes (uid, cohort_id, class_id) VALUES (?, ?, ?)').run(uuidv4(), id, class_id);
    } catch (e) {
      if (/UNIQUE constraint failed/.test(e.message)) {
        return res.status(409).json({ error: 'Class is already linked to this cohort' });
      }
      throw e;
    }
    await AuditService.log(req, 'LINK_COHORT_CLASS', { entityType: 'cohort', entityId: id, classId: class_id });
    res.status(201).json({ ok: true, cohort_id: id, class_id });
  } catch (err) {
    console.error('cohorts.linkClass error:', err);
    res.status(500).json({ error: 'Internal Server Error', message: err.message });
  }
});

router.delete('/:id/classes/:classId', auth, requireAdmin, async (req, res) => {
  try {
    const id = Number(req.params.id);
    const classId = Number(req.params.classId);
    const r = getDb().prepare(`
      UPDATE cohort_classes SET is_deleted = 1, updated_at = datetime('now','localtime')
      WHERE cohort_id = ? AND class_id = ? AND is_deleted = 0
    `).run(id, classId);
    if (r.changes === 0) return res.status(404).json({ error: 'Class is not linked to this cohort' });
    await AuditService.log(req, 'UNLINK_COHORT_CLASS', { entityType: 'cohort', entityId: id, classId });
    res.json({ ok: true });
  } catch (err) {
    console.error('cohorts.unlinkClass error:', err);
    res.status(500).json({ error: 'Internal Server Error', message: err.message });
  }
});

// ---------- POST /api/cohorts/:id/exam-groups -----------------------------

router.post('/:id/exam-groups', auth, requireAdmin, async (req, res) => {
  try {
    const id = Number(req.params.id);
    const { exam_group_id } = req.body;
    if (!exam_group_id) return res.status(400).json({ error: 'exam_group_id is required' });
    const cohort = getDb().prepare('SELECT id FROM student_cohorts WHERE id = ? AND is_deleted = 0').get(id);
    if (!cohort) return res.status(404).json({ error: 'Cohort not found' });
    const eg = getDb().prepare('SELECT id FROM exam_groups WHERE id = ? AND is_deleted = 0').get(exam_group_id);
    if (!eg) return res.status(400).json({ error: 'Invalid exam_group_id' });
    try {
      getDb().prepare('INSERT INTO cohort_exam_groups (uid, cohort_id, exam_group_id) VALUES (?, ?, ?)').run(uuidv4(), id, exam_group_id);
    } catch (e) {
      if (/UNIQUE constraint failed/.test(e.message)) {
        return res.status(409).json({ error: 'Exam group is already linked to this cohort' });
      }
      throw e;
    }
    await AuditService.log(req, 'LINK_COHORT_EXAM_GROUP', { entityType: 'cohort', entityId: id, examGroupId: exam_group_id });
    res.status(201).json({ ok: true, cohort_id: id, exam_group_id });
  } catch (err) {
    console.error('cohorts.linkExamGroup error:', err);
    res.status(500).json({ error: 'Internal Server Error', message: err.message });
  }
});

router.delete('/:id/exam-groups/:examGroupId', auth, requireAdmin, async (req, res) => {
  try {
    const id = Number(req.params.id);
    const egId = Number(req.params.examGroupId);
    const r = getDb().prepare(`
      UPDATE cohort_exam_groups SET is_deleted = 1, updated_at = datetime('now','localtime')
      WHERE cohort_id = ? AND exam_group_id = ? AND is_deleted = 0
    `).run(id, egId);
    if (r.changes === 0) return res.status(404).json({ error: 'Exam group is not linked to this cohort' });
    await AuditService.log(req, 'UNLINK_COHORT_EXAM_GROUP', { entityType: 'cohort', entityId: id, examGroupId: egId });
    res.json({ ok: true });
  } catch (err) {
    console.error('cohorts.unlinkExamGroup error:', err);
    res.status(500).json({ error: 'Internal Server Error', message: err.message });
  }
});

module.exports = router;

module.exports.SUPPORTED_PROGRAMMES = SUPPORTED_PROGRAMMES;
