import { createServer } from 'node:http';
import { readFile, writeFile, mkdir } from 'node:fs/promises';
import { existsSync } from 'node:fs';
import { extname, join, normalize } from 'node:path';
import { createHash, pbkdf2Sync, randomBytes, timingSafeEqual, randomUUID } from 'node:crypto';
import { fileURLToPath } from 'node:url';
import nodemailer from 'nodemailer';
import PDFDocument from 'pdfkit';

const rootDir = fileURLToPath(new URL('.', import.meta.url));
const publicDir = join(rootDir, 'public');
const dataDir = process.env.DATA_DIR || join(rootDir, 'data');
const dbPath = join(dataDir, 'database.json');
const sqlitePath = join(dataDir, 'database.sqlite');
const port = Number(process.env.PORT || 3000);

const mimeTypes = {
  '.html': 'text/html; charset=utf-8',
  '.css': 'text/css; charset=utf-8',
  '.js': 'text/javascript; charset=utf-8',
  '.mjs': 'text/javascript; charset=utf-8',
  '.json': 'application/json; charset=utf-8',
  '.webmanifest': 'application/manifest+json; charset=utf-8',
  '.svg': 'image/svg+xml',
  '.png': 'image/png',
  '.jpg': 'image/jpeg',
  '.jpeg': 'image/jpeg',
};

let db = null;
let sqlite = null;
let sqliteAvailable = false;
let writeQueue = Promise.resolve();
const rateLimits = new Map();
const menuPermissions = ['overview', 'managers', 'sessions', 'institute', 'courses', 'students', 'messages', 'teachers', 'employees', 'payments', 'studentPayments', 'teacherPayments', 'employeePayments', 'referencePayments', 'otherPayments', 'resultSms', 'recordedClasses', 'quizzes', 'results', 'analytics', 'activity', 'profile'];
const softwareVersion = '1.7.9';
const sessionScopedCollections = ['batches', 'notifications', 'teacherPayments', 'employeePayments', 'referencePayments', 'otherPayments', 'shareholderLedger', 'recordedClasses', 'manualResults', 'quizzes', 'attempts', 'payments'];

const requiredOtpTemplate = '(Retina) Your Retina OTP is: {otp}. Please do not share your OTP with anyone.';

function defaultSessionName(date = new Date()) {
  const year = date.getFullYear();
  return `${year}-${String((year + 1) % 100).padStart(2, '0')}`;
}

function defaultInstituteProfile() {
  return {
    name: 'Coaching Management System',
    tagline: 'Student quiz and exam management',
    registrationNo: '',
    phone: '01796997871',
    email: 'admin@example.com',
    website: '',
    address: '',
    logoDataUrl: '',
    faviconDataUrl: '',
    primaryColor: '#2563eb',
    secondaryColor: '#38bdf8',
    receiptNote: 'Thank you for your payment.',
    admissionTerms: '',
    supportContact: '',
    smsSettings: defaultSmsSettings(),
    updatedAt: now(),
  };
}

function defaultSmsSettings() {
  return {
    enabled: false,
    providerName: 'REVE SMS',
    preset: 'reve',
    requestUrl: '',
    method: 'POST',
    requestType: 'json',
    apiKey: '',
    secretKey: '',
    senderId: '',
    apiKeyParam: 'apikey',
    secretKeyParam: 'secretkey',
    senderParam: 'callerID',
    toParam: 'toUser',
    messageParam: 'messageContent',
    hashParam: 'hash',
    hashAlgorithm: 'none',
    hashTemplate: '',
    extraParams: '',
    toggles: {
      loginOtp: false,
      studentPayment: false,
      teacherPayment: false,
      employeePayment: false,
      referencePayment: false,
      resultSms: false,
    },
    templates: {
      loginOtp: requiredOtpTemplate,
      studentPayment: 'Payment received: {amount} for {course}. Receipt: {receipt}. Due: {due}.',
      teacherPayment: 'Teacher payment recorded: paid {paid}, due {due}, month {month}.',
      employeePayment: 'Employee payment recorded: paid {paid}, due {due}, month {month}.',
      referencePayment: 'Reference payment recorded: paid {paid}, due {due}, student {student}.',
      resultSms: 'Result of {exam}; {subject}; Name: {name}; Roll: {roll}; Mark: {mark}; Neg: {negativeMark}; Position: {position}; Highest: {highest}.',
      quizResult: 'Result: {exam}. Name: {name}. Roll: {roll}. Score: {percentage}%. {status}.',
    },
    updatedAt: now(),
  };
}

function defaultManagerRole() {
  return {
    id: 'default-exam-manager',
    name: 'Exam Manager',
    description: 'Default role for existing managers.',
    menuAccess: ['overview', 'students', 'courses', 'quizzes', 'results', 'analytics', 'profile'],
    status: 'active',
    system: true,
    createdAt: now(),
    updatedAt: now(),
  };
}

function sanitizeColor(value, fallback) {
  const color = String(value || '').trim();
  return /^#[0-9a-fA-F]{6}$/.test(color) ? color : fallback;
}

function sanitizeImageDataUrl(value) {
  const dataUrl = String(value || '').trim();
  if (!dataUrl) return '';
  if (dataUrl.length > 260_000) return '';
  return /^data:image\/(png|jpeg|jpg|svg\+xml);base64,[a-zA-Z0-9+/=]+$/.test(dataUrl) ? dataUrl : '';
}

function mergeSmsSettings(settings = {}) {
  const defaults = defaultSmsSettings();
  const merged = {
    ...defaults,
    ...settings,
    enabled: Boolean(settings.enabled),
    method: ['GET', 'POST'].includes(String(settings.method || '').toUpperCase()) ? String(settings.method).toUpperCase() : defaults.method,
    requestType: ['json', 'form', 'query'].includes(settings.requestType) ? settings.requestType : defaults.requestType,
    hashAlgorithm: ['none', 'md5', 'sha256'].includes(settings.hashAlgorithm) ? settings.hashAlgorithm : defaults.hashAlgorithm,
    toggles: { ...defaults.toggles, ...(settings.toggles || {}) },
    templates: { ...defaults.templates, ...(settings.templates || {}) },
  };
  merged.templates.loginOtp = normalizeOtpTemplate(merged.templates.loginOtp);
  return merged;
}

function normalizeOtpTemplate(template = '') {
  const text = String(template || '').trim();
  const valid = /^\([^)]+\)\s+Your\s+.+\s+OTP\s+is:\s+\{otp\}\.\s+(.+)$/i.test(text)
    && (
      /Please do not share your OTP with anyone\.$/i.test(text)
      || /This OTP will be expired within 5 minutes\.$/i.test(text)
    );
  return valid ? text : requiredOtpTemplate;
}

function sanitizeSmsSettings(input = {}, current = defaultSmsSettings()) {
  const submittedSecret = String(input.secretKey || '').trim();
  const templates = Object.fromEntries(Object.entries(defaultSmsSettings().templates).map(([key, value]) => [key, String(input.templates?.[key] ?? value).trim() || value]));
  templates.loginOtp = normalizeOtpTemplate(templates.loginOtp);
  const merged = mergeSmsSettings({
    ...current,
    enabled: input.enabled === true || input.enabled === 'true',
    providerName: String(input.providerName || '').trim() || 'Custom SMS',
    preset: String(input.preset || '').trim() || 'custom',
    requestUrl: String(input.requestUrl || '').trim(),
    method: String(input.method || current.method || 'POST').toUpperCase(),
    requestType: String(input.requestType || current.requestType || 'json').trim(),
    apiKey: String(input.apiKey || '').trim() || current.apiKey || '',
    secretKey: submittedSecret && !submittedSecret.includes('******') ? submittedSecret : current.secretKey || '',
    senderId: String(input.senderId || '').trim(),
    apiKeyParam: String(input.apiKeyParam || '').trim() || 'apikey',
    secretKeyParam: String(input.secretKeyParam || '').trim() || 'secretkey',
    senderParam: String(input.senderParam || '').trim() || 'callerID',
    toParam: String(input.toParam || '').trim() || 'toUser',
    messageParam: String(input.messageParam || '').trim() || 'messageContent',
    hashParam: String(input.hashParam || '').trim(),
    hashAlgorithm: String(input.hashAlgorithm || current.hashAlgorithm || 'none').trim(),
    hashTemplate: String(input.hashTemplate || '').trim(),
    extraParams: String(input.extraParams || '').trim(),
    toggles: Object.fromEntries(Object.entries(defaultSmsSettings().toggles).map(([key]) => [key, input.toggles?.[key] === true || input.toggles?.[key] === 'true'])),
    templates,
  });
  return merged;
}

function maskSecret(value) {
  const text = String(value || '');
  if (!text) return '';
  return text.length <= 6 ? '******' : `${text.slice(0, 2)}******${text.slice(-2)}`;
}

function publicSmsSettings(settings, { includeSecrets = false } = {}) {
  const merged = mergeSmsSettings(settings);
  return {
    ...merged,
    secretKey: includeSecrets ? merged.secretKey : maskSecret(merged.secretKey),
  };
}

function now() {
  return new Date().toISOString();
}

function hashPassword(password) {
  const salt = randomBytes(16).toString('hex');
  const hash = pbkdf2Sync(password, salt, 120000, 32, 'sha256').toString('hex');
  return `${salt}:${hash}`;
}

function verifyPassword(password, stored) {
  const [salt, savedHash] = String(stored || '').split(':');
  if (!salt || !savedHash) return false;
  const hash = pbkdf2Sync(password, salt, 120000, 32, 'sha256');
  const saved = Buffer.from(savedHash, 'hex');
  return saved.length === hash.length && timingSafeEqual(saved, hash);
}

async function ensureDb() {
  await mkdir(dataDir, { recursive: true });
  if (process.env.DISABLE_SQLITE !== 'true') {
    try {
      const { DatabaseSync } = await import('node:sqlite');
      sqlite = new DatabaseSync(sqlitePath);
      sqlite.exec('CREATE TABLE IF NOT EXISTS app_state (id INTEGER PRIMARY KEY CHECK (id = 1), data TEXT NOT NULL, updated_at TEXT NOT NULL)');
      sqliteAvailable = true;
      const row = sqlite.prepare('SELECT data FROM app_state WHERE id = 1').get();
      if (row) {
        db = JSON.parse(row.data);
        migrateDb();
        await saveDb();
        return;
      }
    } catch (error) {
      sqlite = null;
      sqliteAvailable = false;
      console.warn(`SQLite unavailable, using JSON storage: ${error.message}`);
    }
  }

  if (existsSync(dbPath)) {
    db = JSON.parse(await readFile(dbPath, 'utf8'));
    migrateDb();
    await saveDb();
    return;
  }

  if (!existsSync(dbPath)) {
    const adminId = randomUUID();
    const studentId = randomUUID();
    const quizId = randomUUID();
    const courseId = randomUUID();
    const seeded = {
      courses: [
        {
          id: courseId,
          name: 'Demo Course',
          description: 'Default course for the demo student.',
          status: 'active',
          createdAt: now(),
          updatedAt: now(),
        },
      ],
      users: [
        {
          id: adminId,
          role: 'admin',
          name: 'Admin User',
          mobile: '01796997871',
          email: 'admin@example.com',
          passwordHash: hashPassword('admin123'),
          status: 'active',
          createdAt: now(),
        },
        {
          id: studentId,
          role: 'student',
          name: 'Demo Student',
          mobile: '01700000000',
          rollNumber: 'DEMO-001',
          studentMobile: '01700000000',
          guardianMobile: '',
          college: 'Demo College',
          course: 'Demo Course',
          courseIds: [courseId],
          email: 'student@example.com',
          passwordHash: hashPassword('student123'),
          status: 'active',
          createdAt: now(),
        },
      ],
      quizzes: [
        {
          id: quizId,
          title: 'Sample Admission Quiz',
          description: 'A short demo quiz for testing the student exam flow.',
          durationMinutes: 10,
          passMark: 60,
          negativeMarking: 0,
          retakeLimit: 0,
          scheduleStart: '',
          scheduleEnd: '',
          randomizeQuestions: false,
          randomizeAnswers: false,
          resultVisibility: 'immediate',
          certificateEnabled: true,
          status: 'published',
          assignedStudentIds: [studentId],
          assignedCourseIds: [courseId],
          questions: [
            {
              id: randomUUID(),
              type: 'mcq',
              text: 'What is 2 + 2?',
              options: ['3', '4', '5', '6'],
              correctIndex: 1,
              correctText: '',
              points: 1,
            },
            {
              id: randomUUID(),
              type: 'mcq',
              text: 'Which one is a programming language?',
              options: ['HTML', 'CSS', 'JavaScript', 'HTTP'],
              correctIndex: 2,
              correctText: '',
              points: 1,
            },
          ],
          createdAt: now(),
          updatedAt: now(),
        },
      ],
      questionBank: [],
      subjects: [],
      rollSettings: {
        year: new Date().getFullYear(),
        rollPrefix: String(new Date().getFullYear()).slice(-2),
        serialPadding: 2,
        rollLimit: 99,
      },
      attempts: [],
      drafts: [],
      emailLogs: [],
      smsLogs: [],
      loginOtps: [],
      sessions: [],
      passwordResets: [],
      activity: [],
      sessionsList: [{ id: 'default-session', name: defaultSessionName(), status: 'active', createdAt: now(), updatedAt: now() }],
      activeSessionId: 'default-session',
      payments: [],
      batches: [],
      notifications: [],
      roles: [defaultManagerRole()],
      teachers: [],
      employees: [],
      teacherPayments: [],
      employeePayments: [],
      referencePayments: [],
      otherPayments: [],
      shareholders: [],
      shareholderLedger: [],
      recordedClasses: [],
      manualResults: [],
      institute: defaultInstituteProfile(),
    };
    db = seeded;
    await saveDb();
    return;
  }
}

function migrateDb() {
  db.institute = { ...defaultInstituteProfile(), ...(db.institute || {}) };
  db.institute.smsSettings = mergeSmsSettings(db.institute.smsSettings);
  db.sessionsList ||= [{ id: 'default-session', name: defaultSessionName(), status: 'active', createdAt: now(), updatedAt: now() }];
  if (!db.sessionsList.length) db.sessionsList.push({ id: 'default-session', name: defaultSessionName(), status: 'active', createdAt: now(), updatedAt: now() });
  for (const session of db.sessionsList) {
    if (session.name === 'Default Session') session.name = defaultSessionName(new Date(session.createdAt || now()));
  }
  db.activeSessionId ||= db.sessionsList[0].id;
  db.roles ||= [defaultManagerRole()];
  if (!db.roles.length) db.roles.push(defaultManagerRole());
  db.teachers ||= [];
  db.employees ||= [];
  db.teacherPayments ||= [];
  db.employeePayments ||= [];
  db.referencePayments ||= [];
  db.otherPayments ||= [];
  db.shareholders ||= [];
  db.shareholderLedger ||= [];
  db.recordedClasses ||= [];
  db.manualResults ||= [];
  db.courses ||= [];
  db.questionBank ||= [];
  db.subjects ||= [];
  db.rollSettings = {
    year: new Date().getFullYear(),
    rollPrefix: String(new Date().getFullYear()).slice(-2),
    serialPadding: 2,
    rollLimit: 99,
    ...(db.rollSettings || {}),
  };
  db.emailLogs ||= [];
  db.smsLogs ||= [];
  db.loginOtps ||= [];
  db.activity ||= [];
  db.sessions ||= [];
  db.attempts ||= [];
  db.drafts ||= [];
  db.passwordResets ||= [];
  db.payments ||= [];
  db.batches ||= [];
  db.notifications ||= [];
  db.receiptSequence ||= 0;
  db.paymentSequence ||= 0;
  for (const [index, course] of (db.courses || []).entries()) {
    course.defaultFee ??= 0;
    course.code ||= String(index + 1).padStart(2, '0');
  }
  for (const [index, batch] of (db.batches || []).entries()) {
    batch.code ||= String(index + 1).padStart(2, '0');
    batch.rollStartNumber = Math.max(1, Math.floor(Number(batch.rollStartNumber || 1)));
    batch.rollNextNumber = Math.max(batch.rollStartNumber, Math.floor(Number(batch.rollNextNumber || batch.rollStartNumber || 1)));
    batch.rollLimit = Math.max(1, Math.floor(Number(batch.rollLimit || db.rollSettings.rollLimit || 99)));
  }
  for (const user of db.users || []) {
    if (user.role === 'student') user.sessionId ||= db.activeSessionId;
    user.failedLogins ??= 0;
    user.lockedUntil ??= '';
      user.mobile ??= user.studentMobile || '';
      user.sscResult ??= '';
      user.sscPassingYear ??= '';
      user.hscResult ??= '';
      user.hscPassingYear ??= '';
      user.photoDataUrl ??= '';
    if (user.role === 'admin' && !user.mobile) user.mobile = '01796997871';
    if (user.role === 'manager') {
      user.roleId ||= db.roles[0]?.id || defaultManagerRole().id;
    }
    if (user.role === 'student') {
      user.rollNumber ??= '';
      user.registrationNumber ??= user.rollNumber || '';
      user.studentMobile ??= '';
      user.guardianMobile ??= '';
      user.college ??= '';
      user.course ??= '';
      user.courseIds ??= [];
      user.email ??= '';
      user.admissionDate ??= '';
      user.academicYear ??= '';
      user.courseFees ??= [];
      user.batchEnrollments ??= [];
      if (!user.courseIds.length && user.course) {
        let course = db.courses.find((item) => item.name.toLowerCase() === user.course.toLowerCase());
        if (!course) {
          course = {
            id: randomUUID(),
            name: user.course,
            description: '',
            status: 'active',
            createdAt: now(),
            updatedAt: now(),
          };
          db.courses.push(course);
        }
        user.courseIds = [course.id];
      }
      user.courseFees = user.courseIds.map((courseId) => {
        const existing = user.courseFees.find((item) => item.courseId === courseId);
        const course = db.courses.find((item) => item.id === courseId);
        return {
          courseId,
          fee: Math.max(0, Number(existing?.fee ?? course?.defaultFee ?? 0)),
          discount: Math.max(0, Number(existing?.discount || 0)),
        };
      });
      user.batchEnrollments = user.batchEnrollments.filter((item) => (
        user.courseIds.includes(item.courseId)
        && db.batches.some((batch) => batch.id === item.batchId && batch.courseId === item.courseId)
      ));
      if (user.name === 'Demo Student') {
        user.rollNumber ||= 'DEMO-001';
        user.studentMobile ||= '01700000000';
        user.mobile ||= user.studentMobile;
      }
    }
  }
  for (const quiz of db.quizzes || []) {
    quiz.sessionId ||= db.activeSessionId;
    quiz.category ??= (quiz.questions || []).find((question) => question.category)?.category || '';
    quiz.negativeMarking ??= 0;
    quiz.retakeLimit ??= 0;
    quiz.scheduleStart ??= '';
    quiz.scheduleEnd ??= '';
    quiz.randomizeQuestions ??= false;
    quiz.randomizeAnswers ??= false;
    quiz.resultVisibility ??= 'immediate';
    quiz.certificateEnabled ??= true;
    quiz.assignedCourseIds ??= [];
    quiz.questions = (quiz.questions || []).map((question) => ({
      type: 'mcq',
      correctText: '',
      points: 1,
      explanation: question.explanation || question.tag || '',
      ...question,
    }));
  }
  for (const question of db.questionBank || []) {
    question.explanation ??= question.tag || '';
  }
  for (const collection of sessionScopedCollections) {
    for (const item of db[collection] || []) item.sessionId ||= db.activeSessionId;
  }
}

function saveDb() {
  writeQueue = writeQueue.then(async () => {
    const payload = JSON.stringify(db, null, 2);
    if (sqliteAvailable && sqlite) {
      sqlite.prepare('INSERT OR REPLACE INTO app_state (id, data, updated_at) VALUES (1, ?, ?)').run(payload, now());
    }
    await writeFile(dbPath, payload);
  });
  return writeQueue;
}

function logActivity(userId, message) {
  const user = db.users?.find((item) => item.id === userId);
  db.activity.unshift({
    id: randomUUID(),
    userId,
    userName: user?.name || '',
    userRole: user?.role || '',
    sessionId: db.activeSessionId || '',
    message,
    createdAt: now(),
  });
  db.activity = db.activity.slice(0, 100);
}

function sendJson(res, status, payload) {
  res.writeHead(status, {
    ...securityHeaders(),
    'content-type': 'application/json; charset=utf-8',
    'cache-control': 'no-store',
  });
  res.end(JSON.stringify(payload));
}

function activeSession() {
  return db.sessionsList.find((session) => session.id === db.activeSessionId) || db.sessionsList[0];
}

function slugifyFilePart(value) {
  return String(value || '')
    .trim()
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, '-')
    .replace(/^-+|-+$/g, '')
    .slice(0, 48);
}

function exportFileName(base, extension, url = null) {
  const session = slugifyFilePart(activeSession()?.name || 'session');
  const date = new Date().toISOString().slice(0, 10);
  const filterParts = [];
  if (url) {
    ['courseId', 'batchId', 'due', 'status', 'method', 'month', 'category', 'examName'].forEach((key) => {
      const rawValue = url.searchParams.get(key);
      if (!rawValue) return;
      const label = key.endsWith('Id')
        ? (key === 'courseId' ? db.courses.find((item) => item.id === rawValue)?.name : db.batches.find((item) => item.id === rawValue)?.name)
        : rawValue;
      const part = slugifyFilePart(label);
      if (part) filterParts.push(part);
    });
  }
  return [slugifyFilePart(base), session, ...filterParts.slice(0, 3), date].filter(Boolean).join('-') + `.${extension}`;
}

function inActiveSession(item) {
  return !item?.sessionId || item.sessionId === db.activeSessionId;
}

function stampSession(item) {
  item.sessionId ||= db.activeSessionId;
  return item;
}

function auditFields(user) {
  return {
    createdBy: user?.id || '',
    createdByName: user?.name || '',
    updatedBy: user?.id || '',
    updatedByName: user?.name || '',
  };
}

function sessionBackup(sessionId = db.activeSessionId) {
  const students = db.users.filter((user) => user.role === 'student' && user.sessionId === sessionId);
  const studentIds = new Set(students.map((student) => student.id));
  return {
    type: 'session',
    version: softwareVersion,
    exportedAt: now(),
    session: db.sessionsList.find((session) => session.id === sessionId),
    data: {
      students,
      batches: db.batches.filter((item) => item.sessionId === sessionId),
      quizzes: db.quizzes.filter((item) => item.sessionId === sessionId),
      attempts: db.attempts.filter((item) => item.sessionId === sessionId || studentIds.has(item.studentId)),
      payments: db.payments.filter((item) => item.sessionId === sessionId || studentIds.has(item.studentId)),
      teacherPayments: db.teacherPayments.filter((item) => item.sessionId === sessionId),
      employeePayments: db.employeePayments.filter((item) => item.sessionId === sessionId),
      referencePayments: db.referencePayments.filter((item) => item.sessionId === sessionId || studentIds.has(item.studentId)),
      otherPayments: db.otherPayments.filter((item) => item.sessionId === sessionId),
      shareholders: db.shareholders,
      shareholderLedger: db.shareholderLedger.filter((item) => item.sessionId === sessionId),
      recordedClasses: db.recordedClasses.filter((item) => item.sessionId === sessionId),
    },
  };
}

function fullBackup() {
  return { type: 'full', version: softwareVersion, exportedAt: now(), data: db };
}

function sessionFinanceRows(sessionId = db.activeSessionId) {
  const session = db.sessionsList.find((item) => item.id === sessionId) || activeSession();
  const rows = [];
  for (const payment of db.payments.filter((item) => item.sessionId === sessionId && item.status !== 'cancelled').map(publicPayment)) {
    rows.push(['Student Payment', 'Income', payment.courseName, payment.studentName, payment.amount, 0, payment.amount, payment.paidAt, payment.enteredByName || payment.receivedByName || '', session.name]);
  }
  for (const payment of db.referencePayments.filter((item) => item.sessionId === sessionId).map(publicReferencePayment)) {
    rows.push(['Reference Commission', 'Expense', payment.referrerName, payment.studentName, payment.paid || 0, payment.due || 0, payment.commissionAmount || 0, payment.paidAt || payment.createdAt, payment.enteredByName || '', session.name]);
  }
  for (const payment of db.teacherPayments.filter((item) => item.sessionId === sessionId).map(publicTeacherPayment)) {
    rows.push(['Teacher Payment', 'Expense', payment.subject || payment.courseName, payment.teacherName, payment.paid || 0, payment.due || 0, payment.payable || 0, payment.paidAt, payment.enteredByName || '', session.name]);
  }
  for (const payment of db.employeePayments.filter((item) => item.sessionId === sessionId).map(publicEmployeePayment)) {
    rows.push(['Employee Payment', 'Expense', payment.designation || 'Salary', payment.employeeName, payment.paid || 0, payment.due || 0, payment.payable || 0, payment.paidAt, payment.enteredByName || '', session.name]);
  }
  for (const payment of db.otherPayments.filter((item) => item.sessionId === sessionId).map(publicOtherPayment)) {
    rows.push([payment.category || 'Others', 'Expense', payment.title || payment.category || 'Expense', '', payment.amount || 0, 0, payment.amount || 0, payment.paidAt, payment.enteredByName || '', session.name]);
  }
  return rows;
}

function manualResultRows(sessionId = db.activeSessionId) {
  return db.manualResults.filter((item) => item.sessionId === sessionId).map((item) => ({ ...item, source: 'manual' }));
}

function progressReportForStudent(studentId) {
  const student = db.users.find((item) => item.id === studentId && item.role === 'student');
  if (!student) return null;
  const attempts = db.attempts.filter((attempt) => {
    if (attempt.studentId !== student.id) return false;
    const quiz = db.quizzes.find((item) => item.id === attempt.quizId);
    return !quiz?.scheduleEnd || new Date(quiz.scheduleEnd) <= new Date();
  }).map(enrichAttempt);
  const manual = db.manualResults.filter((result) => result.studentId === student.id || String(result.roll || '').toLowerCase() === String(student.rollNumber || '').toLowerCase() || String(result.mobile || '') === String(student.studentMobile || ''));
  const allMarks = [
    ...attempts.map((attempt) => Number(attempt.earned ?? attempt.correct ?? 0)),
    ...manual.map((result) => Number(result.mark || 0)),
  ];
  const average = allMarks.length ? Math.round(allMarks.reduce((sum, mark) => sum + mark, 0) / allMarks.length) : 0;
  return {
    student: publicUser(student),
    finance: studentFinancialSummary(student.id),
    attempts,
    manualResults: manual,
    summary: {
      totalResults: allMarks.length,
      average,
      best: allMarks.length ? Math.max(...allMarks) : 0,
    },
  };
}

function securityHeaders() {
  return {
    'x-content-type-options': 'nosniff',
    'x-frame-options': 'DENY',
    'referrer-policy': 'same-origin',
    'permissions-policy': 'camera=(), microphone=(), geolocation=()',
  };
}

function cookieSecurity() {
  return process.env.COOKIE_SECURE === 'true' ? '; Secure' : '';
}

function readCookies(req) {
  const cookies = {};
  for (const part of String(req.headers.cookie || '').split(';')) {
    const [key, ...valueParts] = part.trim().split('=');
    if (key) cookies[key] = decodeURIComponent(valueParts.join('='));
  }
  return cookies;
}

function sessionTokenFromRequest(req) {
  const auth = String(req.headers.authorization || '');
  const bearer = auth.match(/^Bearer\s+(.+)$/i)?.[1];
  return bearer || readCookies(req).sms_session || '';
}

function sessionFromRequest(req) {
  const token = sessionTokenFromRequest(req);
  if (!token) return null;
  const session = db.sessions.find((item) => item.token === token && new Date(item.expiresAt) > new Date());
  if (!session) return null;
  return session;
}

function sessionUser(req) {
  const session = sessionFromRequest(req);
  if (!session) return null;
  return db.users.find((user) => user.id === session.userId && user.status === 'active') || null;
}

function publicUser(user) {
  if (!user) return null;
  const managerRole = user.role === 'manager' ? db.roles.find((role) => role.id === user.roleId) : null;
  return {
    id: user.id,
    role: user.role,
    name: user.name,
    mobile: user.mobile || user.studentMobile || '',
    rollNumber: user.rollNumber || '',
    registrationNumber: user.registrationNumber || user.rollNumber || '',
    studentMobile: user.studentMobile || '',
    guardianMobile: user.guardianMobile || '',
    college: user.college || '',
    course: user.course || '',
    courseIds: Array.isArray(user.courseIds) ? user.courseIds : [],
    courseFees: Array.isArray(user.courseFees) ? user.courseFees : [],
    batchEnrollments: Array.isArray(user.batchEnrollments) ? user.batchEnrollments : [],
    sscResult: user.sscResult || '',
    sscPassingYear: user.sscPassingYear || '',
    hscResult: user.hscResult || '',
    hscPassingYear: user.hscPassingYear || '',
    photoDataUrl: user.photoDataUrl || '',
    reference: user.reference || null,
    admissionDate: user.admissionDate || '',
    academicYear: user.academicYear || '',
    email: user.email,
    roleId: user.roleId || '',
    roleName: managerRole?.name || (user.role === 'admin' ? 'Super Admin' : ''),
    menuAccess: user.role === 'admin' ? menuPermissions : managerRole?.menuAccess || [],
    status: user.status,
    createdAt: user.createdAt,
  };
}

function publicRole(role) {
  return {
    id: role.id,
    name: role.name,
    description: role.description || '',
    menuAccess: Array.isArray(role.menuAccess) ? role.menuAccess : [],
    status: role.status || 'active',
    system: Boolean(role.system),
    createdAt: role.createdAt,
    updatedAt: role.updatedAt,
  };
}

function publicBatch(batch) {
  const course = db.courses.find((item) => item.id === batch.courseId);
  const studentCount = db.users.filter((user) => (
    user.role === 'student'
    && (user.batchEnrollments || []).some((item) => item.batchId === batch.id)
  )).length;
  return {
    ...batch,
    courseName: course?.name || 'Unknown Course',
    studentCount,
    rollPreview: batchRollPreview(batch),
  };
}

function validateBatchEnrollments(courseIds, submitted, currentStudentId = '') {
  const enrollments = [];
  for (const item of Array.isArray(submitted) ? submitted : []) {
    const courseId = String(item.courseId || '');
    const batchId = String(item.batchId || '');
    if (!courseIds.includes(courseId) || !batchId) continue;
    const batch = db.batches.find((entry) => entry.id === batchId && entry.courseId === courseId);
    if (!batch || batch.status !== 'active') throw new Error('Select an active batch for the enrolled course');
    const assignedCount = db.users.filter((user) => (
      user.id !== currentStudentId
      && user.role === 'student'
      && (user.batchEnrollments || []).some((entry) => entry.batchId === batch.id)
    )).length;
    if (batch.capacity > 0 && assignedCount >= batch.capacity) {
      throw new Error(`${batch.name} has reached its capacity`);
    }
    if (!enrollments.some((entry) => entry.courseId === courseId)) enrollments.push({ courseId, batchId });
  }
  return enrollments;
}

function normalizedCode(value, fallback = '01', width = 2) {
  const cleaned = String(value || '').replace(/[^a-zA-Z0-9]+/g, '').toUpperCase();
  return (cleaned || fallback).padStart(width, '0').slice(-Math.max(width, cleaned.length || fallback.length));
}

function rollYear(settings = db.rollSettings) {
  const configured = Math.floor(Number(settings?.year || new Date().getFullYear()));
  return configured > 1900 ? configured : new Date().getFullYear();
}

function rollPrefix(settings = db.rollSettings) {
  return normalizedCode(settings?.rollPrefix, String(rollYear(settings)).slice(-2), 2);
}

function batchSerialLimit(batch) {
  const configured = Math.max(1, Math.floor(Number(batch?.rollLimit || db.rollSettings?.rollLimit || 99)));
  const capacity = Math.floor(Number(batch?.capacity || 0));
  return Math.min(99, capacity > 0 ? capacity : configured);
}

function batchRollPreview(batch, serialOverride) {
  if (!batch) return { rollNumber: '', registrationNumber: '' };
  const year = rollYear();
  const course = db.courses.find((item) => item.id === batch.courseId);
  const courseCode = normalizedCode(course?.code, '01', 2);
  const batchCode = normalizedCode(batch.code, '01', 2);
  const serial = Math.max(1, Math.floor(Number(serialOverride ?? batch.rollNextNumber ?? batch.rollStartNumber ?? 1)));
  const serialText = String(serial).padStart(Math.max(1, Math.floor(Number(db.rollSettings?.serialPadding || 2))), '0');
  return {
    rollNumber: `${rollPrefix()}${batchCode}${serialText}`,
    registrationNumber: `${year}${courseCode}${batchCode}${serialText}`,
    serial,
  };
}

function rollPreview() {
  const batch = db.batches.find((item) => item.status === 'active') || db.batches[0];
  return batchRollPreview(batch);
}

function nextStudentRoll(courseIds = [], batchEnrollments = []) {
  const enrollment = (batchEnrollments || []).find((item) => item.batchId)
    || (courseIds || []).map((courseId) => ({ courseId, batchId: db.batches.find((batch) => batch.courseId === courseId && batch.status === 'active')?.id })).find((item) => item.batchId);
  const batch = enrollment ? db.batches.find((item) => item.id === enrollment.batchId && item.courseId === enrollment.courseId) : null;
  if (!batch) {
    const fallback = `REG-${String(db.users.filter((user) => user.role === 'student').length + 1).padStart(4, '0')}`;
    return { rollNumber: fallback, registrationNumber: fallback };
  }
  batch.rollStartNumber = Math.max(1, Math.floor(Number(batch.rollStartNumber || 1)));
  batch.rollNextNumber = Math.max(batch.rollStartNumber, Math.floor(Number(batch.rollNextNumber || batch.rollStartNumber || 1)));
  batch.rollLimit = Math.max(1, Math.floor(Number(batch.rollLimit || db.rollSettings?.rollLimit || 99)));
  const limit = batchSerialLimit(batch);
  let preview = batchRollPreview(batch, batch.rollNextNumber);
  while (
    preview.serial <= limit
    && db.users.some((user) => (
      String(user.rollNumber || '').toLowerCase() === preview.rollNumber.toLowerCase()
      || String(user.registrationNumber || '').toLowerCase() === preview.registrationNumber.toLowerCase()
    ))
  ) {
    batch.rollNextNumber += 1;
    preview = batchRollPreview(batch, batch.rollNextNumber);
  }
  if (preview.serial > limit) throw new Error(`${batch.name} roll serial limit reached`);
  batch.rollNextNumber = preview.serial + 1;
  return { rollNumber: preview.rollNumber, registrationNumber: preview.registrationNumber };
}

function notificationRecipients(input = {}) {
  const audience = String(input.audienceType || 'all');
  let students = db.users.filter((user) => user.role === 'student' && inActiveSession(user) && user.status === 'active');
  if (audience === 'course') {
    const courseIds = new Set(Array.isArray(input.courseIds) ? input.courseIds.map(String) : [String(input.courseId || '')].filter(Boolean));
    students = students.filter((student) => (student.courseIds || []).some((courseId) => courseIds.has(courseId)));
  } else if (audience === 'batch') {
    const batchIds = new Set(Array.isArray(input.batchIds) ? input.batchIds.map(String) : [String(input.batchId || '')].filter(Boolean));
    students = students.filter((student) => (student.batchEnrollments || []).some((item) => batchIds.has(item.batchId)));
  } else if (audience === 'selected') {
    const selected = new Set(Array.isArray(input.studentIds) ? input.studentIds.map(String) : []);
    students = students.filter((student) => selected.has(student.id));
  }
  return students;
}

function dueAmountForStudent(student, courseIds = []) {
  const selected = new Set((courseIds || []).map(String).filter(Boolean));
  const finance = studentFinancialSummary(student.id);
  if (!selected.size) return finance.totalDue || 0;
  return money((finance.enrollments || [])
    .filter((item) => selected.has(item.courseId))
    .reduce((sum, item) => sum + Number(item.due || 0), 0));
}

function dueReminderRecipients(input = {}) {
  const courseIds = Array.isArray(input.courseIds) ? input.courseIds.map(String).filter(Boolean) : [];
  const batchIds = Array.isArray(input.batchIds) ? input.batchIds.map(String).filter(Boolean) : [];
  const minDue = money(input.minDue || 0);
  return db.users
    .filter((user) => user.role === 'student' && inActiveSession(user) && user.status === 'active')
    .filter((student) => {
      if (courseIds.length && !(student.courseIds || []).some((courseId) => courseIds.includes(courseId))) return false;
      if (batchIds.length && !(student.batchEnrollments || []).some((entry) => batchIds.includes(entry.batchId))) return false;
      return dueAmountForStudent(student, courseIds) >= minDue && dueAmountForStudent(student, courseIds) > 0;
    });
}

function notificationVisibleToStudent(notification, student) {
  if (!notification || !student) return false;
  if (notification.recipientIds?.includes(student.id)) return true;
  if (notification.audienceType === 'all') return true;
  if (notification.audienceType === 'course') {
    const courseIds = new Set(Array.isArray(notification.courseIds) ? notification.courseIds : [notification.courseId].filter(Boolean));
    return (student.courseIds || []).some((courseId) => courseIds.has(courseId));
  }
  if (notification.audienceType === 'batch') {
    const batchIds = new Set(Array.isArray(notification.batchIds) ? notification.batchIds : [notification.batchId].filter(Boolean));
    return (student.batchEnrollments || []).some((item) => batchIds.has(item.batchId));
  }
  return false;
}

function money(value) {
  return Math.max(0, Math.round((Number(value) || 0) * 100) / 100);
}

function signedMoney(value) {
  return Math.round((Number(value) || 0) * 100) / 100;
}

function studentFinancialSummary(studentId) {
  const student = db.users.find((item) => item.id === studentId && item.role === 'student');
  if (!student) return null;
  const enrollments = (student.courseIds || []).map((courseId) => {
    const course = db.courses.find((item) => item.id === courseId);
    const configured = (student.courseFees || []).find((item) => item.courseId === courseId);
    const fee = money(course?.defaultFee ?? 0);
    const discount = Math.min(fee, money(configured?.discount || 0));
    const payments = db.payments.filter((payment) => (
      payment.studentId === studentId
      && payment.courseId === courseId
      && payment.status !== 'cancelled'
    ));
    const paid = money(payments.reduce((sum, payment) => sum + Number(payment.amount || 0), 0));
    return {
      courseId,
      courseName: course?.name || 'Unknown Course',
      fee,
      discount,
      payable: money(fee - discount),
      paid,
      due: money(Math.max(0, fee - discount - paid)),
    };
  });
  return {
    studentId,
    enrollments,
    totalFee: money(enrollments.reduce((sum, item) => sum + item.fee, 0)),
    totalDiscount: money(enrollments.reduce((sum, item) => sum + item.discount, 0)),
    totalPayable: money(enrollments.reduce((sum, item) => sum + item.payable, 0)),
    totalPaid: money(enrollments.reduce((sum, item) => sum + item.paid, 0)),
    totalDue: money(enrollments.reduce((sum, item) => sum + item.due, 0)),
  };
}

function publicPayment(payment) {
  const student = db.users.find((item) => item.id === payment.studentId);
  const course = db.courses.find((item) => item.id === payment.courseId);
  const creator = db.users.find((item) => item.id === (payment.receivedBy || payment.createdBy));
  return {
    ...payment,
    studentName: student?.name || 'Unknown Student',
    studentRoll: student?.rollNumber || '',
    courseName: course?.name || 'Unknown Course',
    enteredByName: payment.receivedByName || creator?.name || '',
  };
}

function hasMenuAccess(user, permission) {
  if (!permission || user?.role === 'admin') return true;
  if (user?.role !== 'manager') return false;
  const role = db.roles.find((item) => item.id === user.roleId && item.status === 'active');
  return Boolean(role?.menuAccess?.includes(permission));
}

function requireMenuAccess(req, res, permission) {
  const user = requireAdmin(req, res);
  if (!user) return null;
  if (!hasMenuAccess(user, permission)) {
    sendJson(res, 403, { error: 'You do not have access to this menu' });
    return null;
  }
  return user;
}

function teacherPaymentSummary(payment) {
  const classEntries = Array.isArray(payment.classEntries) ? payment.classEntries : [];
  const payable = money(classEntries.reduce((sum, item) => sum + (Number(item.classes || 0) * Number(item.rate || payment.rate || 0)), 0));
  const paid = money(payment.paidAmount || 0);
  return { payable, paid, due: money(Math.max(0, payable - paid)), totalClasses: classEntries.reduce((sum, item) => sum + Number(item.classes || 0), 0) };
}

function publicTeacher(teacher) {
  return {
    id: teacher.id,
    name: teacher.name,
    mobile: teacher.mobile || '',
    email: teacher.email || '',
    subject: teacher.subject || '',
    courseIds: teacher.courseIds || [],
    batchIds: teacher.batchIds || [],
    rate: money(teacher.rate || 0),
    note: teacher.note || '',
    status: teacher.status || 'active',
    createdAt: teacher.createdAt,
    updatedAt: teacher.updatedAt,
  };
}

function publicEmployee(employee) {
  return {
    id: employee.id,
    name: employee.name,
    mobile: employee.mobile || '',
    email: employee.email || '',
    designation: employee.designation || '',
    salary: money(employee.salary || 0),
    note: employee.note || '',
    status: employee.status || 'active',
    createdAt: employee.createdAt,
    updatedAt: employee.updatedAt,
  };
}

function publicTeacherPayment(payment) {
  const teacher = db.teachers.find((item) => item.id === payment.teacherId);
  const course = db.courses.find((item) => item.id === payment.courseId);
  const batch = db.batches.find((item) => item.id === payment.batchId);
  const creator = db.users.find((item) => item.id === payment.createdBy);
  return {
    ...payment,
    teacherName: teacher?.name || 'Unknown Teacher',
    teacherMobile: teacher?.mobile || '',
    courseName: course?.name || 'No Course',
    batchName: batch?.name || 'No Batch',
    enteredByName: creator?.name || payment.createdByName || '',
    ...teacherPaymentSummary(payment),
  };
}

function employeePaymentSummary(payment) {
  const payable = money(Number(payment.salaryAmount || 0) + Number(payment.allowance || 0) + Number(payment.bonus || 0) - Number(payment.deduction || 0) - Number(payment.advance || 0));
  const paid = money(payment.paidAmount || 0);
  return { payable, paid, due: money(Math.max(0, payable - paid)) };
}

function publicEmployeePayment(payment) {
  const employee = db.employees.find((item) => item.id === payment.employeeId);
  const creator = db.users.find((item) => item.id === payment.createdBy);
  return {
    ...payment,
    employeeName: employee?.name || 'Unknown Employee',
    employeeMobile: employee?.mobile || '',
    designation: employee?.designation || '',
    enteredByName: creator?.name || payment.createdByName || '',
    ...employeePaymentSummary(payment),
  };
}

function publicReferencePayment(payment) {
  const student = db.users.find((item) => item.id === payment.studentId);
  const creator = db.users.find((item) => item.id === payment.createdBy);
  return {
    ...payment,
    studentName: student?.name || '',
    studentRoll: student?.rollNumber || '',
    enteredByName: creator?.name || payment.createdByName || '',
    payable: money(payment.commissionAmount || 0),
    paid: money(payment.paidAmount || 0),
    due: money(Math.max(0, Number(payment.commissionAmount || 0) - Number(payment.paidAmount || 0))),
  };
}

function publicOtherPayment(payment) {
  const creator = db.users.find((item) => item.id === payment.createdBy);
  return {
    ...payment,
    amount: money(payment.amount || 0),
    enteredByName: creator?.name || payment.createdByName || '',
  };
}

const shareholderLedgerTypes = {
  opening_balance: { label: 'Opening Balance', sign: 1 },
  capital_investment: { label: 'Capital Investment', sign: 1 },
  reference_commission: { label: 'Reference Commission', sign: 1 },
  session_profit_share: { label: 'Session Profit Share', sign: 1 },
  paid_for_institute: { label: 'Paid for Institute', sign: 1 },
  withdrawal: { label: 'Withdrawal / Advance', sign: -1 },
  expense_charged: { label: 'Expense Charged', sign: -1 },
  adjustment_positive: { label: 'Positive Adjustment', sign: 1 },
  adjustment_negative: { label: 'Negative Adjustment', sign: -1 },
};

function shareholderLedgerType(type) {
  return shareholderLedgerTypes[type] ? type : 'adjustment_positive';
}

function signedShareholderAmount(type, amount) {
  const key = shareholderLedgerType(type);
  return signedMoney(Math.abs(Number(amount || 0)) * shareholderLedgerTypes[key].sign);
}

function shareholderBalance(shareholderId, sessionId = db.activeSessionId) {
  return signedMoney(db.shareholderLedger
    .filter((item) => item.shareholderId === shareholderId && item.sessionId === sessionId)
    .reduce((sum, item) => sum + Number(item.amount || 0), 0));
}

function publicShareholder(item, sessionId = db.activeSessionId) {
  const ledger = db.shareholderLedger.filter((entry) => entry.shareholderId === item.id && entry.sessionId === sessionId);
  const withdrawal = ledger.filter((entry) => Number(entry.amount || 0) < 0).reduce((sum, entry) => sum + Math.abs(Number(entry.amount || 0)), 0);
  const income = ledger.filter((entry) => Number(entry.amount || 0) > 0).reduce((sum, entry) => sum + Number(entry.amount || 0), 0);
  return {
    ...item,
    sharePercentage: money(item.sharePercentage || 0),
    openingBalance: signedMoney(item.openingBalance || 0),
    sessionIncome: money(income),
    sessionWithdrawal: money(withdrawal),
    sessionBalance: shareholderBalance(item.id, sessionId),
  };
}

function publicShareholderLedger(entry) {
  const shareholder = db.shareholders.find((item) => item.id === entry.shareholderId);
  const creator = db.users.find((item) => item.id === entry.createdBy);
  return {
    ...entry,
    shareholderName: shareholder?.name || '',
    shareholderMobile: shareholder?.mobile || '',
    typeLabel: shareholderLedgerTypes[entry.type]?.label || entry.type,
    amount: signedMoney(entry.amount || 0),
    rawAmount: money(entry.rawAmount ?? Math.abs(Number(entry.amount || 0))),
    enteredByName: creator?.name || entry.createdByName || '',
  };
}

function createShareholderLedgerEntry(input, staff, source = {}) {
  const type = shareholderLedgerType(input.type);
  const rawAmount = money(input.rawAmount ?? input.amount ?? 0);
  return {
    id: randomUUID(),
    sessionId: input.sessionId || db.activeSessionId,
    shareholderId: input.shareholderId,
    type,
    title: String(input.title || shareholderLedgerTypes[type].label).trim(),
    rawAmount,
    amount: signedShareholderAmount(type, rawAmount),
    method: ['cash', 'bank', 'mobile_banking'].includes(input.method) ? input.method : 'cash',
    reference: String(input.reference || '').trim(),
    note: String(input.note || '').trim(),
    entryDate: String(input.entryDate || input.paidAt || '').trim() || now(),
    sourceType: source.sourceType || input.sourceType || 'manual',
    sourceId: source.sourceId || input.sourceId || '',
    createdBy: staff?.id || input.createdBy || '',
    createdAt: now(),
    updatedAt: now(),
  };
}

function publicRecordedClass(item) {
  return {
    id: item.id,
    title: item.title,
    subject: item.subject || '',
    courseIds: item.courseIds || [],
    batchIds: item.batchIds || [],
    videoUrl: item.videoUrl || '',
    visibility: item.visibility || 'assigned',
    status: item.status || 'published',
    note: item.note || '',
    createdAt: item.createdAt,
    updatedAt: item.updatedAt,
  };
}

function quizSubject(quiz) {
  return quiz.category || (quiz.questions || []).find((question) => question.category)?.category || quiz.title || '';
}

function rankedAttemptsForQuiz(quizId) {
  const attempts = db.attempts.filter((attempt) => !quizId || attempt.quizId === quizId);
  const grouped = new Map();
  for (const attempt of attempts) {
    if (!grouped.has(attempt.quizId)) grouped.set(attempt.quizId, []);
    grouped.get(attempt.quizId).push(attempt);
  }
  const rankMap = new Map();
  for (const quizAttempts of grouped.values()) {
    const ranked = [...quizAttempts].sort((left, right) => {
      const markDifference = Number(right.earned ?? right.correct ?? 0) - Number(left.earned ?? left.correct ?? 0);
      if (markDifference) return markDifference;
      return String(left.submittedAt || '').localeCompare(String(right.submittedAt || ''));
    });
    let previousMark = null;
    let position = 0;
    ranked.forEach((attempt, index) => {
      const mark = Number(attempt.earned ?? attempt.correct ?? 0);
      if (previousMark === null || mark !== previousMark) position = index + 1;
      previousMark = mark;
      rankMap.set(attempt.id, position);
    });
  }
  return { attempts, rankMap };
}

function resultExportRows(quizId = '', filters = {}) {
  const { attempts, rankMap } = rankedAttemptsForQuiz(quizId);
  const highestByQuiz = new Map();
  for (const attempt of attempts) {
    const mark = Number(attempt.earned ?? attempt.correct ?? 0);
    highestByQuiz.set(attempt.quizId, Math.max(Number(highestByQuiz.get(attempt.quizId) || 0), mark));
  }
  const rows = attempts.map((attempt) => {
    const student = db.users.find((user) => user.id === attempt.studentId) || {};
    const quiz = db.quizzes.find((item) => item.id === attempt.quizId) || {};
    const courseId = (student.courseIds || []).find((id) => (quiz.assignedCourseIds || []).includes(id)) || '';
    const batchEnrollment = (student.batchEnrollments || []).find((item) => item.courseId === courseId)
      || (student.batchEnrollments || [])[0]
      || {};
    const negativeMark = (attempt.details || []).reduce((total, detail) => total + Math.abs(Math.min(0, Number(detail.awarded || 0))), 0);
    return {
      mobile: student.studentMobile || student.mobile || '',
      name: attempt.studentName || student.name || '',
      roll: student.rollNumber || '',
      batch: (student.batchEnrollments || []).map((item) => db.batches.find((batch) => batch.id === item.batchId)?.name).filter(Boolean).join(', '),
      college: student.college || '',
      subject: quizSubject(quiz),
      quizTitle: attempt.quizTitle || quiz.title || '',
      mark: Number(attempt.earned ?? attempt.correct ?? 0),
      negativeMark: negativeMark || '',
      position: rankMap.get(attempt.id) || '',
      highest: highestByQuiz.get(attempt.quizId) || 0,
      total: Number(attempt.total || 0),
      percentage: Number(attempt.percentage || 0),
      status: attempt.passed ? 'Passed' : 'Failed',
      submittedAt: attempt.submittedAt || '',
      courseId,
      batchId: batchEnrollment.batchId || '',
    };
  });
  const manualRows = manualResultRows(filters.sessionId || db.activeSessionId).map((result) => ({
    mobile: result.mobile || '',
    name: result.name || '',
    roll: result.roll || '',
    batch: result.batch || '',
    college: result.college || '',
    subject: result.subject || '',
    quizTitle: result.examName || '',
    mark: result.mark || 0,
    negativeMark: result.negativeMark ?? '',
    position: result.position || '',
    highest: result.highest || '',
    total: '',
    percentage: '',
    status: '',
    submittedAt: result.createdAt || '',
    courseId: result.courseId || '',
    batchId: result.batchId || '',
  }));
  return [...rows, ...manualRows].filter((row) => (
    (!filters.examName || String(row.quizTitle || '').toLowerCase().includes(String(filters.examName).toLowerCase()))
    && (!filters.courseId || row.courseId === filters.courseId)
    && (!filters.batchId || row.batchId === filters.batchId || row.batch === filters.batchId)
  ));
}

function renderResultSms(template, row) {
  return String(template || 'Result of {exam}; {subject}; Name: {name}; Roll: {roll}; Mark: {mark}; Neg: {negativeMark}; Position: {position}; Highest: {highest}.')
    .replaceAll('{exam}', row.quizTitle || '')
    .replaceAll('{subject}', row.subject || '')
    .replaceAll('{name}', row.name || '')
    .replaceAll('{roll}', row.roll || '')
    .replaceAll('{mobile}', row.mobile || '')
    .replaceAll('{batch}', row.batch || '')
    .replaceAll('{college}', row.college || '')
    .replaceAll('{mark}', String(row.mark ?? ''))
    .replaceAll('{negativeMark}', String(row.negativeMark ?? ''))
    .replaceAll('{position}', String(row.position ?? ''))
    .replaceAll('{highest}', String(row.highest ?? ''));
}

function publicInstituteProfile(options = {}) {
  const profile = {
    ...defaultInstituteProfile(),
    ...(db.institute || {}),
  };
  profile.smsSettings = publicSmsSettings(profile.smsSettings, options);
  return profile;
}

function institutePdfHeader(doc, title) {
  const institute = publicInstituteProfile();
  doc.fontSize(20).fillColor('#172033').text(institute.name || 'Student Management System', { align: 'center' });
  if (institute.tagline) doc.fontSize(10).fillColor('#64748b').text(institute.tagline, { align: 'center' });
  if (institute.phone || institute.email || institute.address) {
    doc.fontSize(8).fillColor('#64748b').text([institute.phone, institute.email, institute.address].filter(Boolean).join(' | '), { align: 'center' });
  }
  doc.moveDown(0.8);
  doc.fontSize(16).fillColor('#172033').text(title, { align: 'center' });
  doc.moveDown();
}

function nextReceiptNumber() {
  db.receiptSequence += 1;
  return `RCP-${new Date().getFullYear()}-${String(db.receiptSequence).padStart(6, '0')}`;
}

function csrfTokenFor(req) {
  return sessionFromRequest(req)?.csrfToken || '';
}

function clientIp(req) {
  return String(req.headers['x-forwarded-for'] || req.socket.remoteAddress || 'unknown').split(',')[0].trim();
}

function checkRateLimit(key, limit = 60, windowMs = 60_000) {
  const current = Date.now();
  const item = rateLimits.get(key) || { count: 0, resetAt: current + windowMs };
  if (current > item.resetAt) {
    item.count = 0;
    item.resetAt = current + windowMs;
  }
  item.count += 1;
  rateLimits.set(key, item);
  return item.count <= limit;
}

function assertCsrf(req, res) {
  if (!['POST', 'PUT', 'PATCH', 'DELETE'].includes(req.method)) return true;
  if (req.url.startsWith('/api/login') || req.url.startsWith('/api/mobile/login') || req.url.startsWith('/api/mobile/verify-otp') || req.url.startsWith('/api/password-reset/')) return true;
  const expected = csrfTokenFor(req);
  const received = String(req.headers['x-csrf-token'] || '');
  if (!expected || received !== expected) {
    sendJson(res, 403, { error: 'Security token mismatch. Refresh and try again.' });
    return false;
  }
  return true;
}

async function bodyJson(req) {
  let body = '';
  for await (const chunk of req) {
    body += chunk;
    if (body.length > 1_000_000) {
      throw new Error('Request body too large');
    }
  }
  return body ? JSON.parse(body) : {};
}

function requireAuth(req, res) {
  const user = sessionUser(req);
  if (!user) {
    sendJson(res, 401, { error: 'Authentication required' });
    return null;
  }
  return user;
}

function requireAdmin(req, res) {
  const user = requireAuth(req, res);
  if (!user) return null;
  if (!['admin', 'manager'].includes(user.role)) {
    sendJson(res, 403, { error: 'Admin or manager access required' });
    return null;
  }
  return user;
}

function requireSuperAdmin(req, res) {
  const user = requireAuth(req, res);
  if (!user) return null;
  if (user.role !== 'admin') {
    sendJson(res, 403, { error: 'Admin access required' });
    return null;
  }
  return user;
}

function isStaff(user) {
  return ['admin', 'manager'].includes(user?.role);
}

function stripQuizAnswers(quiz) {
  const stripped = {
    ...quiz,
    questions: quiz.questions.map(({ correctIndex, correctText, ...question }) => question),
  };
  return stripRandomizedAnswers(stripped);
}

function scoreAttempt(quiz, answers) {
  let earned = 0;
  let possible = 0;
  let needsReview = false;
  const details = quiz.questions.map((question) => {
    const points = Math.max(1, Number(question.points || 1));
    possible += points;
    const answer = answers[question.id];
    let isCorrect = false;
    let awarded = 0;
    let selectedIndex = null;

    if (question.type === 'written') {
      needsReview = true;
    } else if (question.type === 'short') {
      const expected = String(question.correctText || '').trim().toLowerCase();
      const actual = String(answer || '').trim().toLowerCase();
      isCorrect = expected !== '' && actual === expected;
    } else {
      selectedIndex = Number(answer);
      isCorrect = selectedIndex === Number(question.correctIndex);
    }

    if (isCorrect) {
      awarded = points;
    } else if (question.type !== 'written' && Number(quiz.negativeMarking || 0) > 0 && answer !== undefined && answer !== null && answer !== '') {
      awarded = -Math.abs(Number(quiz.negativeMarking || 0));
    }

    earned += awarded;
    return {
      questionId: question.id,
      questionText: question.text,
      options: question.options || [],
      type: question.type || 'mcq',
      answer: answer ?? '',
      selectedIndex: Number.isInteger(selectedIndex) ? selectedIndex : null,
      correctIndex: question.correctIndex,
      correctText: question.correctText || '',
      explanation: question.explanation || '',
      isCorrect,
      awarded,
      points,
    };
  });
  const total = possible;
  const correct = details.filter((detail) => detail.isCorrect).length;
  const percentage = total ? Math.max(0, Math.round((earned / total) * 100)) : 0;
  return {
    correct,
    total,
    earned,
    percentage,
    passed: percentage >= Number(quiz.passMark || 0),
    details,
    reviewStatus: needsReview ? 'pending' : 'graded',
  };
}

function enrichAttempt(attempt) {
  const quiz = db.quizzes.find((item) => item.id === attempt.quizId);
  if (!quiz) return attempt;
  return {
    ...attempt,
    details: (attempt.details || []).map((detail) => {
      const question = quiz.questions.find((item) => item.id === detail.questionId);
      return {
        ...detail,
        questionText: detail.questionText || question?.text || '',
        options: detail.options || question?.options || [],
        correctText: detail.correctText || question?.correctText || '',
        correctIndex: detail.correctIndex ?? question?.correctIndex,
        explanation: detail.explanation || question?.explanation || '',
      };
    }),
  };
}

function validateQuizInput(input, { requireCourses = true } = {}) {
  const questions = Array.isArray(input.questions) ? input.questions : [];
  const cleanedQuestions = questions
    .map((question) => {
      const type = ['mcq', 'true_false', 'short', 'written'].includes(question.type) ? question.type : 'mcq';
      const options = type === 'true_false'
        ? ['True', 'False']
        : Array.isArray(question.options)
          ? question.options.map((option) => String(option || '').trim()).filter(Boolean).slice(0, 6)
          : [];
      return {
        id: question.id || randomUUID(),
        type,
        text: String(question.text || '').trim(),
        options,
        correctIndex: Number(question.correctIndex || 0),
        correctText: String(question.correctText || '').trim(),
        points: Math.max(1, Number(question.points || 1)),
        category: String(question.category || '').trim(),
        explanation: String(question.explanation || question.tag || '').trim(),
      };
    })
    .filter((question) => {
      if (!question.text) return false;
      if (question.type === 'written') return true;
      if (question.type === 'short') return question.correctText !== '';
      return question.options.length >= 2 && question.correctIndex >= 0 && question.correctIndex < question.options.length;
    });

  if (!String(input.title || '').trim()) {
    return { error: 'Quiz title is required' };
  }
  if (cleanedQuestions.length === 0) {
    return { error: 'At least one valid question is required' };
  }

  const assignedCourseIds = Array.isArray(input.assignedCourseIds)
    ? [...new Set(input.assignedCourseIds.filter((id) => db.courses.some((course) => course.id === id && course.status === 'active')))]
    : [];
  if (requireCourses && assignedCourseIds.length === 0) {
    return { error: 'Select at least one active course' };
  }

  return {
    quiz: {
      title: String(input.title).trim(),
      category: String(input.category || '').trim(),
      description: String(input.description || '').trim(),
      durationMinutes: Math.max(1, Math.min(240, Number(input.durationMinutes || 30))),
      passMark: Math.max(0, Math.min(100, Number(input.passMark || 50))),
      negativeMarking: Math.max(0, Math.min(10, Number(input.negativeMarking || 0))),
      retakeLimit: Math.max(0, Math.min(50, Number(input.retakeLimit || 0))),
      scheduleStart: String(input.scheduleStart || '').trim(),
      scheduleEnd: String(input.scheduleEnd || '').trim(),
      randomizeQuestions: Boolean(input.randomizeQuestions),
      randomizeAnswers: Boolean(input.randomizeAnswers),
      resultVisibility: input.resultVisibility === 'hidden' ? 'hidden' : 'immediate',
      certificateEnabled: Boolean(input.certificateEnabled),
      status: input.status === 'draft' ? 'draft' : 'published',
      assignedStudentIds: [],
      assignedCourseIds,
      questions: cleanedQuestions,
    },
  };
}

function stripRandomizedAnswers(quiz) {
  const copy = structuredClone(quiz);
  if (copy.randomizeQuestions) copy.questions = shuffle(copy.questions);
  copy.questions = copy.questions.map((question) => {
    if (!Array.isArray(question.options) || question.options.length < 2) return question;
    const options = question.options.map((text, index) => ({ text, value: index }));
    return {
      ...question,
      options: copy.randomizeAnswers ? shuffle(options) : options,
    };
  });
  return copy;
}

function shuffle(items) {
  return [...items].sort(() => Math.random() - 0.5);
}

function quizAvailableForStudent(quiz, studentId) {
  if (!quiz || quiz.status !== 'published') return false;
  const assignedCourseIds = Array.isArray(quiz.assignedCourseIds) ? quiz.assignedCourseIds : [];
  if (!assignedCourseIds.length) return false;
  const student = db.users.find((item) => item.id === studentId && item.role === 'student' && item.status === 'active');
  const courseAssigned = (student?.courseIds || []).some((courseId) => (
    assignedCourseIds.includes(courseId)
    && db.courses.some((course) => course.id === courseId && course.status === 'active')
  ));
  if (!courseAssigned) return false;
  const time = Date.now();
  if (quiz.scheduleStart && new Date(quiz.scheduleStart).getTime() > time) return false;
  if (quiz.scheduleEnd && new Date(quiz.scheduleEnd).getTime() < time) return false;
  return true;
}

function quizPreviouslyAvailableForStudent(quiz, studentId) {
  if (!quiz || quiz.status !== 'published') return false;
  const student = db.users.find((item) => item.id === studentId && item.role === 'student' && item.status === 'active');
  const assignedCourseIds = Array.isArray(quiz.assignedCourseIds) ? quiz.assignedCourseIds : [];
  if (!assignedCourseIds.length || !(student?.courseIds || []).some((courseId) => assignedCourseIds.includes(courseId))) return false;
  return Boolean(quiz.scheduleEnd && new Date(quiz.scheduleEnd).getTime() < Date.now());
}

function recordedClassAvailableForStudent(item, studentId) {
  if (!item || item.status !== 'published') return false;
  const student = db.users.find((user) => user.id === studentId && user.role === 'student' && user.status === 'active');
  if (!student) return false;
  if (item.visibility === 'all') return true;
  const courseMatch = (student.courseIds || []).some((courseId) => (item.courseIds || []).includes(courseId));
  const batchMatch = (student.batchEnrollments || []).some((entry) => (item.batchIds || []).includes(entry.batchId));
  return courseMatch || batchMatch;
}

function attemptsFor(studentId, quizId) {
  return db.attempts.filter((attempt) => attempt.studentId === studentId && attempt.quizId === quizId);
}

function ensureCourseByName(name) {
  const normalizedName = String(name || '').trim();
  if (!normalizedName) return null;
  let course = db.courses.find((item) => item.name.toLowerCase() === normalizedName.toLowerCase());
  if (!course) {
    course = {
      id: randomUUID(),
      name: normalizedName,
      description: '',
      status: 'active',
      createdAt: now(),
      updatedAt: now(),
    };
    db.courses.push(course);
  }
  return course;
}

function csvEscape(value) {
  return `"${String(value ?? '').replaceAll('"', '""')}"`;
}

function xmlEscape(value) {
  return String(value ?? '')
    .replaceAll('&', '&amp;')
    .replaceAll('<', '&lt;')
    .replaceAll('>', '&gt;')
    .replaceAll('"', '&quot;')
    .replaceAll("'", '&apos;');
}

function crc32(buffer) {
  let crc = 0xffffffff;
  for (const byte of buffer) {
    crc ^= byte;
    for (let bit = 0; bit < 8; bit += 1) {
      crc = (crc >>> 1) ^ ((crc & 1) ? 0xedb88320 : 0);
    }
  }
  return (crc ^ 0xffffffff) >>> 0;
}

function zipDateTime(date = new Date()) {
  const year = Math.max(1980, date.getFullYear());
  return {
    time: (date.getHours() << 11) | (date.getMinutes() << 5) | Math.floor(date.getSeconds() / 2),
    date: ((year - 1980) << 9) | ((date.getMonth() + 1) << 5) | date.getDate(),
  };
}

function createZip(entries) {
  const localParts = [];
  const centralParts = [];
  let offset = 0;
  const timestamp = zipDateTime();

  for (const entry of entries) {
    const name = Buffer.from(entry.name, 'utf8');
    const data = Buffer.isBuffer(entry.data) ? entry.data : Buffer.from(entry.data, 'utf8');
    const checksum = crc32(data);
    const localHeader = Buffer.alloc(30);
    localHeader.writeUInt32LE(0x04034b50, 0);
    localHeader.writeUInt16LE(20, 4);
    localHeader.writeUInt16LE(0x0800, 6);
    localHeader.writeUInt16LE(0, 8);
    localHeader.writeUInt16LE(timestamp.time, 10);
    localHeader.writeUInt16LE(timestamp.date, 12);
    localHeader.writeUInt32LE(checksum, 14);
    localHeader.writeUInt32LE(data.length, 18);
    localHeader.writeUInt32LE(data.length, 22);
    localHeader.writeUInt16LE(name.length, 26);
    localHeader.writeUInt16LE(0, 28);
    localParts.push(localHeader, name, data);

    const centralHeader = Buffer.alloc(46);
    centralHeader.writeUInt32LE(0x02014b50, 0);
    centralHeader.writeUInt16LE(20, 4);
    centralHeader.writeUInt16LE(20, 6);
    centralHeader.writeUInt16LE(0x0800, 8);
    centralHeader.writeUInt16LE(0, 10);
    centralHeader.writeUInt16LE(timestamp.time, 12);
    centralHeader.writeUInt16LE(timestamp.date, 14);
    centralHeader.writeUInt32LE(checksum, 16);
    centralHeader.writeUInt32LE(data.length, 20);
    centralHeader.writeUInt32LE(data.length, 24);
    centralHeader.writeUInt16LE(name.length, 28);
    centralHeader.writeUInt16LE(0, 30);
    centralHeader.writeUInt16LE(0, 32);
    centralHeader.writeUInt16LE(0, 34);
    centralHeader.writeUInt16LE(0, 36);
    centralHeader.writeUInt32LE(0, 38);
    centralHeader.writeUInt32LE(offset, 42);
    centralParts.push(centralHeader, name);
    offset += localHeader.length + name.length + data.length;
  }

  const centralDirectory = Buffer.concat(centralParts);
  const end = Buffer.alloc(22);
  end.writeUInt32LE(0x06054b50, 0);
  end.writeUInt16LE(0, 4);
  end.writeUInt16LE(0, 6);
  end.writeUInt16LE(entries.length, 8);
  end.writeUInt16LE(entries.length, 10);
  end.writeUInt32LE(centralDirectory.length, 12);
  end.writeUInt32LE(offset, 16);
  end.writeUInt16LE(0, 20);
  return Buffer.concat([...localParts, centralDirectory, end]);
}

function spreadsheetColumn(index) {
  let column = '';
  let number = index + 1;
  while (number > 0) {
    const remainder = (number - 1) % 26;
    column = String.fromCharCode(65 + remainder) + column;
    number = Math.floor((number - 1) / 26);
  }
  return column;
}

function spreadsheetCell(value, rowIndex, columnIndex, style = 0) {
  const reference = `${spreadsheetColumn(columnIndex)}${rowIndex}`;
  if (typeof value === 'number' && Number.isFinite(value)) {
    return `<c r="${reference}" s="${style}"><v>${value}</v></c>`;
  }
  return `<c r="${reference}" s="${style}" t="inlineStr"><is><t>${xmlEscape(value)}</t></is></c>`;
}

function createResultsWorkbook(quiz, attempts) {
  const rankedAttempts = [...attempts].sort((left, right) => {
    const markDifference = Number(right.earned ?? right.correct ?? 0) - Number(left.earned ?? left.correct ?? 0);
    if (markDifference) return markDifference;
    return String(left.submittedAt || '').localeCompare(String(right.submittedAt || ''));
  });
  let previousMark = null;
  let rank = 0;
  const rows = rankedAttempts.map((attempt, index) => {
    const obtainedMark = Number(attempt.earned ?? attempt.correct ?? 0);
    if (previousMark === null || obtainedMark !== previousMark) rank = index + 1;
    previousMark = obtainedMark;
    const negativeMark = (attempt.details || []).reduce(
      (total, detail) => total + Math.abs(Math.min(0, Number(detail.awarded || 0))),
      0,
    );
    return [
      rank,
      attempt.studentName || '',
      Number(attempt.total || 0),
      obtainedMark,
      Number(attempt.percentage || 0),
      attempt.passed ? 'Passed' : 'Failed',
      attempt.reviewStatus || 'graded',
      attempt.submittedAt ? new Date(attempt.submittedAt).toLocaleString('en-GB') : '',
      negativeMark,
    ];
  });
  const data = [
    ['Ranking', 'Student Name', 'Total Mark', 'Obtained Mark', 'Percentage', 'Pass/Fail', 'Review', 'Submitted Time', 'Negative Mark'],
    ...rows,
  ];
  const sheetRows = data.map((row, rowIndex) => {
    const cells = row.map((value, columnIndex) => spreadsheetCell(value, rowIndex + 1, columnIndex, rowIndex === 0 ? 1 : 0)).join('');
    return `<row r="${rowIndex + 1}">${cells}</row>`;
  }).join('');
  const lastCell = `I${Math.max(1, data.length)}`;
  const sheetXml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <dimension ref="A1:${lastCell}"/>
  <sheetViews><sheetView workbookViewId="0"><pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/></sheetView></sheetViews>
  <cols>
    <col min="1" max="1" width="10" customWidth="1"/>
    <col min="2" max="2" width="24" customWidth="1"/>
    <col min="3" max="7" width="15" customWidth="1"/>
    <col min="8" max="8" width="23" customWidth="1"/>
    <col min="9" max="9" width="15" customWidth="1"/>
  </cols>
  <sheetData>${sheetRows}</sheetData>
  <autoFilter ref="A1:${lastCell}"/>
</worksheet>`;
  const workbookXml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
  <sheets><sheet name="${xmlEscape(String(quiz.title || 'Quiz Results').slice(0, 31))}" sheetId="1" r:id="rId1"/></sheets>
</workbook>`;
  const stylesXml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <fonts count="2"><font><sz val="11"/><name val="Calibri"/></font><font><b/><color rgb="FFFFFFFF"/><sz val="11"/><name val="Calibri"/></font></fonts>
  <fills count="3"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill><fill><patternFill patternType="solid"><fgColor rgb="FF2563EB"/><bgColor indexed="64"/></patternFill></fill></fills>
  <borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders>
  <cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>
  <cellXfs count="2"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/><xf numFmtId="0" fontId="1" fillId="2" borderId="0" xfId="0" applyFont="1" applyFill="1"/></cellXfs>
  <cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>
</styleSheet>`;
  return createZip([
    {
      name: '[Content_Types].xml',
      data: `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
  <Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
  <Default Extension="xml" ContentType="application/xml"/>
  <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
  <Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
  <Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>
</Types>`,
    },
    {
      name: '_rels/.rels',
      data: `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
</Relationships>`,
    },
    { name: 'xl/workbook.xml', data: workbookXml },
    {
      name: 'xl/_rels/workbook.xml.rels',
      data: `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
  <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>
</Relationships>`,
    },
    { name: 'xl/styles.xml', data: stylesXml },
    { name: 'xl/worksheets/sheet1.xml', data: sheetXml },
  ]);
}

function createTableWorkbook(headers, rows) {
  const data = [headers, ...rows];
  const sheetRows = data.map((row, rowIndex) => `<row r="${rowIndex + 1}">${row.map((value, columnIndex) => spreadsheetCell(value, rowIndex + 1, columnIndex, rowIndex === 0 ? 1 : 0)).join('')}</row>`).join('');
  const lastCell = `${spreadsheetColumn(Math.max(0, headers.length - 1))}${Math.max(1, data.length)}`;
  const sheetXml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><dimension ref="A1:${lastCell}"/><sheetData>${sheetRows}</sheetData></worksheet>`;
  const workbookXml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><sheets><sheet name="Report" sheetId="1" r:id="rId1"/></sheets></workbook>`;
  const stylesXml = `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><fonts count="2"><font><sz val="11"/><name val="Calibri"/></font><font><b/><sz val="11"/><name val="Calibri"/></font></fonts><fills count="1"><fill><patternFill patternType="none"/></fill></fills><borders count="1"><border/></borders><cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs><cellXfs count="2"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/><xf numFmtId="0" fontId="1" fillId="0" borderId="0"/></cellXfs></styleSheet>`;
  return createZip([
    { name: '[Content_Types].xml', data: `<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"><Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Default Extension="xml" ContentType="application/xml"/><Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/><Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/><Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/></Types>` },
    { name: '_rels/.rels', data: `<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/></Relationships>` },
    { name: 'xl/workbook.xml', data: workbookXml },
    { name: 'xl/_rels/workbook.xml.rels', data: `<?xml version="1.0" encoding="UTF-8" standalone="yes"?><Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/></Relationships>` },
    { name: 'xl/worksheets/sheet1.xml', data: sheetXml },
    { name: 'xl/styles.xml', data: stylesXml },
  ]);
}

function smtpConfigured() {
  return Boolean(process.env.SMTP_HOST && process.env.SMTP_USER && process.env.SMTP_PASS);
}

function smsConfigured() {
  const settings = mergeSmsSettings(db?.institute?.smsSettings);
  return Boolean(settings.enabled && settings.requestUrl);
}

function smsLoginOtpEnabled() {
  const settings = mergeSmsSettings(db?.institute?.smsSettings);
  return Boolean(settings.enabled && settings.toggles.loginOtp);
}

function otpCode() {
  return String(Math.floor(100000 + Math.random() * 900000));
}

function smsGatewayUrl(to, message) {
  const settings = mergeSmsSettings(db?.institute?.smsSettings);
  return String(settings.requestUrl || '')
    .replaceAll('{to}', encodeURIComponent(to))
    .replaceAll('{message}', encodeURIComponent(message));
}

function smsFeatureKey(type) {
  return {
    login_otp: 'loginOtp',
    student_payment: 'studentPayment',
    teacher_payment: 'teacherPayment',
    employee_payment: 'employeePayment',
    reference_payment: 'referencePayment',
    result_sms: 'resultSms',
    quiz_result: 'resultSms',
  }[type] || '';
}

function renderTemplate(template, values = {}) {
  return String(template || '').replace(/\{([a-zA-Z0-9_]+)\}/g, (_, key) => String(values[key] ?? ''));
}

function parseExtraParams(text = '') {
  const params = {};
  for (const line of String(text || '').split(/\r?\n/)) {
    const trimmed = line.trim();
    if (!trimmed || trimmed.startsWith('#')) continue;
    const separator = trimmed.includes('=') ? '=' : ':';
    const index = trimmed.indexOf(separator);
    if (index <= 0) continue;
    params[trimmed.slice(0, index).trim()] = trimmed.slice(index + 1).trim();
  }
  return params;
}

function smsPayload(settings, to, message) {
  const values = {
    apiKey: settings.apiKey,
    secretKey: settings.secretKey,
    senderId: settings.senderId,
    to,
    message,
  };
  const payload = {};
  if (settings.apiKeyParam && settings.apiKey) payload[settings.apiKeyParam] = settings.apiKey;
  if (settings.secretKeyParam && settings.secretKey) payload[settings.secretKeyParam] = settings.secretKey;
  if (settings.senderParam && settings.senderId) payload[settings.senderParam] = settings.senderId;
  if (settings.toParam) payload[settings.toParam] = to;
  if (settings.messageParam) payload[settings.messageParam] = message;
  if (settings.hashParam && settings.hashAlgorithm !== 'none' && settings.hashTemplate) {
    const raw = renderTemplate(settings.hashTemplate, values);
    payload[settings.hashParam] = createHash(settings.hashAlgorithm).update(raw).digest('hex');
  }
  return { ...payload, ...parseExtraParams(settings.extraParams) };
}

async function sendSms(to, message, type = 'general', meta = {}) {
  const settings = mergeSmsSettings(db?.institute?.smsSettings);
  const feature = smsFeatureKey(type);
  const log = {
    id: randomUUID(),
    to: String(to || '').trim() || 'not configured',
    message: String(message || '').trim(),
    type,
    provider: settings.providerName || 'SMS Gateway',
    status: to ? 'logged' : 'skipped',
    meta,
    createdAt: now(),
  };
  if (!settings.enabled || (feature && !settings.toggles[feature])) {
    log.status = 'disabled';
  } else if (!to) {
    log.status = 'skipped';
  } else if (!settings.requestUrl) {
    log.status = 'logged';
    log.error = 'SMS gateway URL is not configured';
  } else {
    try {
      const method = settings.method;
      const endpoint = smsGatewayUrl(to, message);
      const payload = smsPayload(settings, to, message);
      const options = { method };
      let requestUrl = endpoint;
      if (method === 'GET' || settings.requestType === 'query') {
        const separator = requestUrl.includes('?') ? '&' : '?';
        requestUrl += separator + new URLSearchParams(payload).toString();
      } else if (settings.requestType === 'form') {
        options.headers = { 'content-type': 'application/x-www-form-urlencoded' };
        options.body = new URLSearchParams(payload).toString();
      } else {
        options.headers = { 'content-type': 'application/json' };
        options.body = JSON.stringify(payload);
      }
      const response = await fetch(requestUrl, options);
      log.status = response.ok ? 'sent' : 'failed';
      log.providerStatus = response.status;
      log.providerResponse = String(await response.text().catch(() => '')).slice(0, 500);
      if (!response.ok) log.error = `SMS gateway returned ${response.status}`;
    } catch (error) {
      log.status = 'failed';
      log.error = error.message || 'SMS send failed';
    }
  }
  db.smsLogs.unshift(log);
  db.smsLogs = db.smsLogs.slice(0, 300);
  return log;
}

function smsTemplate(key) {
  return mergeSmsSettings(db?.institute?.smsSettings).templates[key] || defaultSmsSettings().templates[key] || '';
}

async function sendResultEmail(to, subject, body) {
  if (!to) {
    db.emailLogs.unshift({
      id: randomUUID(),
      to: 'not configured',
      subject,
      body,
      status: 'skipped',
      createdAt: now(),
    });
    db.emailLogs = db.emailLogs.slice(0, 200);
    return;
  }
  const log = {
    id: randomUUID(),
    to,
    subject,
    body,
    status: 'logged',
    createdAt: now(),
  };

  if (smtpConfigured()) {
    try {
      const transporter = nodemailer.createTransport({
        host: process.env.SMTP_HOST,
        port: Number(process.env.SMTP_PORT || 587),
        secure: process.env.SMTP_SECURE === 'true',
        auth: {
          user: process.env.SMTP_USER,
          pass: process.env.SMTP_PASS,
        },
      });
      await transporter.sendMail({
        from: process.env.SMTP_FROM || process.env.SMTP_USER,
        to,
        subject,
        text: body,
      });
      log.status = 'sent';
    } catch (error) {
      log.status = 'failed';
      log.error = error.message || 'SMTP send failed';
    }
  }

  db.emailLogs.unshift(log);
  db.emailLogs = db.emailLogs.slice(0, 200);
}

async function createLoginSessionResponse(req, res, user) {
  const token = randomUUID() + randomUUID();
  const csrfToken = randomUUID() + randomUUID();
  const expiresAt = new Date(Date.now() + 1000 * 60 * 60 * 12).toISOString();
  user.failedLogins = 0;
  user.lockedUntil = '';
  db.sessions.push({ token, csrfToken, userId: user.id, expiresAt, createdAt: now() });
  logActivity(user.id, 'Logged in');
  await saveDb();
  res.writeHead(200, {
    'content-type': 'application/json; charset=utf-8',
    ...securityHeaders(),
    'set-cookie': `sms_session=${encodeURIComponent(token)}; HttpOnly; SameSite=Lax; Path=/; Max-Age=43200${cookieSecurity()}`,
  });
  res.end(JSON.stringify({ user: publicUser(user), institute: publicInstituteProfile(), csrfToken }));
}

async function createMobileSessionResponse(res, user) {
  const token = randomUUID() + randomUUID();
  const csrfToken = randomUUID() + randomUUID();
  const expiresAt = new Date(Date.now() + 1000 * 60 * 60 * 24 * 30).toISOString();
  user.failedLogins = 0;
  user.lockedUntil = '';
  db.sessions.push({ token, csrfToken, userId: user.id, expiresAt, createdAt: now(), device: 'android' });
  logActivity(user.id, 'Logged in from Android app');
  await saveDb();
  sendJson(res, 200, {
    authToken: token,
    csrfToken,
    expiresAt,
    user: publicUser(user),
    institute: publicInstituteProfile(),
  });
}

function createResultPdfBuffer(attempt) {
  return new Promise((resolve) => {
    const chunks = [];
    const doc = new PDFDocument({ margin: 48 });
    doc.on('data', (chunk) => chunks.push(chunk));
    doc.on('end', () => resolve(Buffer.concat(chunks)));
    institutePdfHeader(doc, 'Quiz Result Report');
    doc.fontSize(13).text(`Student: ${attempt.studentName}`);
    doc.text(`Quiz: ${attempt.quizTitle}`);
    doc.text(`Score: ${attempt.earned ?? attempt.correct}/${attempt.total}`);
    doc.text(`Percentage: ${attempt.percentage}%`);
    doc.text(`Status: ${attempt.passed ? 'Passed' : 'Failed'}`);
    doc.text(`Review: ${attempt.reviewStatus || 'graded'}`);
    doc.text(`Submitted: ${formatCertificateDate(attempt.submittedAt)}`);
    doc.end();
  });
}

function createCertificatePdfBuffer(attempt) {
  return new Promise((resolve) => {
    const chunks = [];
    const doc = new PDFDocument({ size: 'A4', layout: 'landscape', margin: 56 });
    const institute = publicInstituteProfile();
    doc.on('data', (chunk) => chunks.push(chunk));
    doc.on('end', () => resolve(Buffer.concat(chunks)));
    doc.rect(36, 36, 770, 523).lineWidth(6).stroke('#176b87');
    doc.fontSize(18).fillColor('#172033').text(institute.name || 'Student Management System', 0, 72, { align: 'center' });
    doc.fontSize(34).fillColor('#172033').text('Certificate of Achievement', 0, 105, { align: 'center' });
    doc.moveDown();
    doc.fontSize(16).text('This certifies that', { align: 'center' });
    doc.moveDown();
    doc.fontSize(30).fillColor('#c95f2d').text(attempt.studentName, { align: 'center' });
    doc.moveDown();
    doc.fontSize(16).fillColor('#172033').text(`successfully completed ${attempt.quizTitle}`, { align: 'center' });
    doc.moveDown();
    doc.text(`Score: ${attempt.percentage}%`, { align: 'center' });
    doc.moveDown();
    doc.fontSize(11).fillColor('#68758a').text(`Certificate ID ${attempt.id.slice(0, 8).toUpperCase()} · ${formatCertificateDate(attempt.submittedAt)}`, { align: 'center' });
    doc.end();
  });
}

function createStudentProfilePdfBuffer(student) {
  return new Promise((resolve) => {
    const chunks = [];
    const doc = new PDFDocument({ margin: 48 });
    const finance = studentFinancialSummary(student.id);
    doc.on('data', (chunk) => chunks.push(chunk));
    doc.on('end', () => resolve(Buffer.concat(chunks)));
    institutePdfHeader(doc, 'Student Information Form');
    [
      ['Name', student.name],
      ['Roll / Registration', student.rollNumber],
      ['Student Mobile', student.studentMobile],
      ['Guardian Mobile', student.guardianMobile || '-'],
      ['College', student.college || '-'],
      ['Email', student.email || '-'],
      ['Admission Date', student.admissionDate || '-'],
      ['Academic Year', student.academicYear || '-'],
      ['Courses', finance?.enrollments.map((item) => item.courseName).join(', ') || '-'],
      ['Total Payable', finance?.totalPayable || 0],
      ['Total Paid', finance?.totalPaid || 0],
      ['Total Due', finance?.totalDue || 0],
    ].forEach(([label, value]) => {
      doc.font('Helvetica-Bold').text(`${label}: `, { continued: true });
      doc.font('Helvetica').text(String(value ?? '-'));
    });
    doc.end();
  });
}

function createReceiptPdfBuffer(payment) {
  return new Promise((resolve) => {
    const chunks = [];
    const doc = new PDFDocument({ margin: 48 });
    const institute = publicInstituteProfile();
    const item = publicPayment(payment);
    const finance = studentFinancialSummary(payment.studentId);
    const enrollment = finance?.enrollments.find((entry) => entry.courseId === payment.courseId);
    doc.on('data', (chunk) => chunks.push(chunk));
    doc.on('end', () => resolve(Buffer.concat(chunks)));
    institutePdfHeader(doc, 'Payment Receipt');
    [
      ['Receipt No.', item.receiptNumber],
      ['Student', `${item.studentName} (${item.studentRoll || 'No Roll'})`],
      ['Course', item.courseName],
      ['Amount Paid', item.amount],
      ['Payment Method', item.method],
      ['Reference', item.reference || '-'],
      ['Payment Date', formatCertificateDate(item.paidAt)],
      ['Remaining Due', enrollment?.due || 0],
      ['Received By', item.receivedByName || '-'],
      ['Note', item.note || '-'],
      ['Institute Note', institute.receiptNote || '-'],
    ].forEach(([label, value]) => {
      doc.font('Helvetica-Bold').fillColor('#172033').text(`${label}: `, { continued: true });
      doc.font('Helvetica').text(String(value ?? '-'));
    });
    doc.moveDown(3);
    doc.text('Authorized Signature', { align: 'right' });
    doc.end();
  });
}

function createReportPdfBuffer(title, headers, rows, subtitle = '') {
  return new Promise((resolve) => {
    const chunks = [];
    const doc = new PDFDocument({ size: 'A4', layout: headers.length > 6 ? 'landscape' : 'portrait', margin: 32 });
    doc.on('data', (chunk) => chunks.push(chunk));
    doc.on('end', () => resolve(Buffer.concat(chunks)));
    institutePdfHeader(doc, title);
    doc.fontSize(9).fillColor('#64748b').text(`Generated ${formatCertificateDate(now())}`, { align: 'center' });
    if (subtitle) doc.text(subtitle, { align: 'center' });
    doc.moveDown();
    doc.font('Helvetica-Bold').fillColor('#172033').fontSize(8).text(headers.join('  |  '));
    doc.moveDown(0.4);
    for (const row of rows) {
      if (doc.y > doc.page.height - 48) doc.addPage();
      doc.font('Helvetica').fontSize(8).text(row.map((value) => String(value ?? '-')).join('  |  '));
      doc.moveDown(0.3);
    }
    doc.end();
  });
}

function filteredStudents(url) {
  const courseId = url.searchParams.get('courseId') || '';
  const batchId = url.searchParams.get('batchId') || '';
  const due = url.searchParams.get('due') || '';
  const status = url.searchParams.get('status') || '';
  return db.users.filter((student) => {
    if (student.role !== 'student') return false;
    const finance = studentFinancialSummary(student.id);
    if (courseId && !(student.courseIds || []).includes(courseId)) return false;
    if (batchId && !(student.batchEnrollments || []).some((item) => item.batchId === batchId)) return false;
    if (status && student.status !== status) return false;
    if (due === 'due' && Number(finance.totalDue || 0) <= 0) return false;
    if (due === 'paid' && Number(finance.totalDue || 0) > 0) return false;
    if (due === 'partial' && !(Number(finance.totalPaid || 0) > 0 && Number(finance.totalDue || 0) > 0)) return false;
    return true;
  });
}

function filteredPayments(url) {
  const courseId = url.searchParams.get('courseId') || '';
  const batchId = url.searchParams.get('batchId') || '';
  const method = url.searchParams.get('method') || '';
  const status = url.searchParams.get('status') || '';
  const from = url.searchParams.get('from') || '';
  const to = url.searchParams.get('to') || '';
  return db.payments.filter((payment) => {
    const student = db.users.find((item) => item.id === payment.studentId);
    if (courseId && payment.courseId !== courseId) return false;
    if (batchId && !(student?.batchEnrollments || []).some((item) => item.batchId === batchId)) return false;
    if (method && payment.method !== method) return false;
    if (status && payment.status !== status) return false;
    if (from && String(payment.paidAt).slice(0, 10) < from) return false;
    if (to && String(payment.paidAt).slice(0, 10) > to) return false;
    return true;
  });
}

function filteredTeacherPayments(url) {
  const teacherId = url.searchParams.get('teacherId') || '';
  const courseId = url.searchParams.get('courseId') || '';
  const batchId = url.searchParams.get('batchId') || '';
  const month = url.searchParams.get('month') || '';
  const subject = String(url.searchParams.get('subject') || '').trim().toLowerCase();
  const status = url.searchParams.get('status') || '';
  return db.teacherPayments.map(publicTeacherPayment).filter((payment) => {
    if (teacherId && payment.teacherId !== teacherId) return false;
    if (courseId && payment.courseId !== courseId) return false;
    if (batchId && payment.batchId !== batchId) return false;
    if (month && payment.month !== month) return false;
    if (subject && !String(payment.subject || '').toLowerCase().includes(subject)) return false;
    if (status === 'due' && Number(payment.due || 0) <= 0) return false;
    if (status === 'paid' && Number(payment.due || 0) > 0) return false;
    if (status === 'partial' && !(Number(payment.paid || 0) > 0 && Number(payment.due || 0) > 0)) return false;
    return true;
  });
}

function filteredEmployeePayments(url) {
  const employeeId = url.searchParams.get('employeeId') || '';
  const month = url.searchParams.get('month') || '';
  const method = url.searchParams.get('method') || '';
  const status = url.searchParams.get('status') || '';
  return db.employeePayments.map(publicEmployeePayment).filter((payment) => {
    if (employeeId && payment.employeeId !== employeeId) return false;
    if (month && payment.month !== month) return false;
    if (method && payment.method !== method) return false;
    if (status === 'due' && Number(payment.due || 0) <= 0) return false;
    if (status === 'paid' && Number(payment.due || 0) > 0) return false;
    if (status === 'partial' && !(Number(payment.paid || 0) > 0 && Number(payment.due || 0) > 0)) return false;
    return true;
  });
}

function filteredReferencePayments(url) {
  const studentId = url.searchParams.get('studentId') || '';
  const referrerType = String(url.searchParams.get('referrerType') || '').trim().toLowerCase();
  const method = url.searchParams.get('method') || '';
  const status = url.searchParams.get('status') || '';
  return db.referencePayments.map(publicReferencePayment).filter((payment) => {
    if (studentId && payment.studentId !== studentId) return false;
    if (referrerType && String(payment.referrerType || '').toLowerCase() !== referrerType) return false;
    if (method && payment.method !== method) return false;
    if (status === 'due' && Number(payment.due || 0) <= 0) return false;
    if (status === 'paid' && Number(payment.due || 0) > 0) return false;
    if (status === 'partial' && !(Number(payment.paid || 0) > 0 && Number(payment.due || 0) > 0)) return false;
    return true;
  });
}

function filteredOtherPayments(url) {
  const category = String(url.searchParams.get('category') || '').trim().toLowerCase();
  const method = url.searchParams.get('method') || '';
  const from = url.searchParams.get('from') || '';
  const to = url.searchParams.get('to') || '';
  return db.otherPayments.map(publicOtherPayment).filter((payment) => {
    if (category && String(payment.category || '').toLowerCase() !== category) return false;
    if (method && payment.method !== method) return false;
    if (from && String(payment.paidAt).slice(0, 10) < from) return false;
    if (to && String(payment.paidAt).slice(0, 10) > to) return false;
    return true;
  });
}

function filteredShareholderLedger(url) {
  const shareholderId = url.searchParams.get('shareholderId') || '';
  const type = url.searchParams.get('type') || '';
  const from = url.searchParams.get('from') || '';
  const to = url.searchParams.get('to') || '';
  return db.shareholderLedger.filter((entry) => entry.sessionId === db.activeSessionId).map(publicShareholderLedger).filter((entry) => {
    if (shareholderId && entry.shareholderId !== shareholderId) return false;
    if (type && entry.type !== type) return false;
    if (from && String(entry.entryDate).slice(0, 10) < from) return false;
    if (to && String(entry.entryDate).slice(0, 10) > to) return false;
    return true;
  });
}

function sessionNetProfit(sessionId = db.activeSessionId) {
  const rows = sessionFinanceRows(sessionId);
  const income = rows.filter((row) => row[1] === 'Income').reduce((sum, row) => sum + Number(row[6] || row[4] || 0), 0);
  const expense = rows.filter((row) => row[1] === 'Expense').reduce((sum, row) => sum + Number(row[6] || row[4] || 0), 0);
  return { income: money(income), expense: money(expense), profit: money(income - expense) };
}

function shareholderClosingRows(sessionId = db.activeSessionId) {
  return db.shareholders.map((shareholder) => {
    const entries = db.shareholderLedger.filter((entry) => entry.sessionId === sessionId && entry.shareholderId === shareholder.id);
    const byType = (type) => entries.filter((entry) => entry.type === type).reduce((sum, entry) => sum + Math.abs(Number(entry.amount || 0)), 0);
    const adjustments = entries
      .filter((entry) => ['adjustment_positive', 'adjustment_negative'].includes(entry.type))
      .reduce((sum, entry) => sum + Number(entry.amount || 0), 0);
    return {
      shareholder: publicShareholder(shareholder, sessionId),
      opening: signedMoney(entries.filter((entry) => entry.type === 'opening_balance').reduce((sum, entry) => sum + Number(entry.amount || 0), 0)),
      investment: money(byType('capital_investment')),
      commission: money(byType('reference_commission')),
      profitShare: money(byType('session_profit_share')),
      paidForInstitute: money(byType('paid_for_institute')),
      withdrawal: money(byType('withdrawal')),
      expenseCharged: money(byType('expense_charged')),
      adjustments: signedMoney(adjustments),
      balance: shareholderBalance(shareholder.id, sessionId),
    };
  });
}

async function handleApi(req, res, url) {
  try {
    if (req.method === 'GET' && url.pathname === '/api/health') {
      sendJson(res, 200, {
        ok: true,
        storage: sqliteAvailable ? 'sqlite' : 'json',
        uptime: process.uptime(),
        time: now(),
      });
      return;
    }

    if (!checkRateLimit(`${clientIp(req)}:${url.pathname}`, 180, 60_000)) {
      sendJson(res, 429, { error: 'Too many requests. Please try again shortly.' });
      return;
    }
    if (!assertCsrf(req, res)) return;

    if (req.method === 'POST' && url.pathname === '/api/login') {
      if (!checkRateLimit(`login:${clientIp(req)}`, 10, 15 * 60_000)) {
        sendJson(res, 429, { error: 'Too many login attempts. Try again later.' });
        return;
      }
      const input = await bodyJson(req);
      const login = String(input.email || '').trim().toLowerCase();
      const candidates = db.users.filter((item) => {
        if (item.status !== 'active') return false;
        return String(item.mobile || '').toLowerCase() === login
          || String(item.studentMobile || '').toLowerCase() === login
          || String(item.email || '').toLowerCase() === login
          || String(item.rollNumber || '').toLowerCase() === login
          || String(item.id || '').toLowerCase() === login
      });
      const user = candidates.find((item) => verifyPassword(String(input.password || ''), item.passwordHash)) || candidates[0];
      if (user?.lockedUntil && new Date(user.lockedUntil) > new Date()) {
        sendJson(res, 423, { error: 'Account temporarily locked. Try again later.' });
        return;
      }
      if (!user || !verifyPassword(String(input.password || ''), user.passwordHash)) {
        for (const candidate of candidates) {
          candidate.failedLogins = Number(candidate.failedLogins || 0) + 1;
          if (candidate.failedLogins >= 5) {
            candidate.lockedUntil = new Date(Date.now() + 15 * 60_000).toISOString();
            candidate.failedLogins = 0;
          }
        }
        if (candidates.length) await saveDb();
        sendJson(res, 401, { error: 'Invalid mobile/student ID or password' });
        return;
      }
      if (smsLoginOtpEnabled()) {
        const code = otpCode();
        const otpToken = randomUUID() + randomUUID();
        db.loginOtps = [
          {
            token: otpToken,
            userId: user.id,
            codeHash: hashPassword(code),
            expiresAt: new Date(Date.now() + 5 * 60_000).toISOString(),
            usedAt: '',
            createdAt: now(),
          },
          ...db.loginOtps.filter((item) => item.userId !== user.id || item.usedAt),
        ].slice(0, 200);
        const mobile = user.studentMobile || user.mobile || '';
        await sendSms(mobile, renderTemplate(smsTemplate('loginOtp'), { otp: code, name: user.name, mobile }), 'login_otp', { userId: user.id });
        await saveDb();
        sendJson(res, 200, {
          requiresOtp: true,
          otpToken,
          maskedMobile: mobile ? `${mobile.slice(0, 3)}*****${mobile.slice(-2)}` : '',
          message: smsConfigured() ? 'OTP sent to mobile.' : 'OTP generated and saved in SMS log. Configure SMS Gateway Settings to send real SMS.',
          devOtp: smsConfigured() ? undefined : code,
        });
        return;
      }
      await createLoginSessionResponse(req, res, user);
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/login/verify-otp') {
      const input = await bodyJson(req);
      const otp = db.loginOtps.find((item) => (
        item.token === String(input.otpToken || '').trim()
        && !item.usedAt
        && new Date(item.expiresAt) > new Date()
      ));
      if (!otp || !verifyPassword(String(input.otp || ''), otp.codeHash)) {
        sendJson(res, 401, { error: 'Invalid or expired OTP' });
        return;
      }
      const user = db.users.find((item) => item.id === otp.userId && item.status === 'active');
      if (!user) {
        sendJson(res, 404, { error: 'User not found' });
        return;
      }
      otp.usedAt = now();
      await createLoginSessionResponse(req, res, user);
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/mobile/login') {
      if (!checkRateLimit(`mobile-login:${clientIp(req)}`, 10, 15 * 60_000)) {
        sendJson(res, 429, { error: 'Too many login attempts. Try again later.' });
        return;
      }
      const input = await bodyJson(req);
      const login = String(input.email || input.identifier || '').trim().toLowerCase();
      const candidates = db.users.filter((item) => {
        if (item.status !== 'active') return false;
        return String(item.mobile || '').toLowerCase() === login
          || String(item.studentMobile || '').toLowerCase() === login
          || String(item.email || '').toLowerCase() === login
          || String(item.rollNumber || '').toLowerCase() === login
          || String(item.id || '').toLowerCase() === login;
      });
      const user = candidates.find((item) => verifyPassword(String(input.password || ''), item.passwordHash)) || candidates[0];
      if (user?.lockedUntil && new Date(user.lockedUntil) > new Date()) {
        sendJson(res, 423, { error: 'Account temporarily locked. Try again later.' });
        return;
      }
      if (!user || !verifyPassword(String(input.password || ''), user.passwordHash)) {
        for (const candidate of candidates) {
          candidate.failedLogins = Number(candidate.failedLogins || 0) + 1;
          if (candidate.failedLogins >= 5) {
            candidate.lockedUntil = new Date(Date.now() + 15 * 60_000).toISOString();
            candidate.failedLogins = 0;
          }
        }
        if (candidates.length) await saveDb();
        sendJson(res, 401, { error: 'Invalid mobile/student ID or password' });
        return;
      }
      if (user.role !== 'student') {
        sendJson(res, 403, { error: 'Only student accounts can use the Android app.' });
        return;
      }
      if (smsLoginOtpEnabled()) {
        const code = otpCode();
        const otpToken = randomUUID() + randomUUID();
        db.loginOtps = [
          {
            token: otpToken,
            userId: user.id,
            codeHash: hashPassword(code),
            expiresAt: new Date(Date.now() + 5 * 60_000).toISOString(),
            usedAt: '',
            createdAt: now(),
            source: 'android',
          },
          ...db.loginOtps.filter((item) => item.userId !== user.id || item.usedAt),
        ].slice(0, 200);
        const mobile = user.studentMobile || user.mobile || '';
        await sendSms(mobile, renderTemplate(smsTemplate('loginOtp'), { otp: code, name: user.name, mobile }), 'login_otp', { userId: user.id });
        await saveDb();
        sendJson(res, 200, {
          requiresOtp: true,
          otpToken,
          maskedMobile: mobile ? `${mobile.slice(0, 3)}*****${mobile.slice(-2)}` : '',
          message: smsConfigured() ? 'OTP sent to mobile.' : 'OTP generated and saved in SMS log. Configure SMS Gateway Settings to send real SMS.',
          devOtp: smsConfigured() ? undefined : code,
        });
        return;
      }
      await createMobileSessionResponse(res, user);
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/mobile/verify-otp') {
      const input = await bodyJson(req);
      const otp = db.loginOtps.find((item) => (
        item.token === String(input.otpToken || '').trim()
        && !item.usedAt
        && new Date(item.expiresAt) > new Date()
      ));
      if (!otp || !verifyPassword(String(input.otp || ''), otp.codeHash)) {
        sendJson(res, 401, { error: 'Invalid or expired OTP' });
        return;
      }
      const user = db.users.find((item) => item.id === otp.userId && item.status === 'active');
      if (!user || user.role !== 'student') {
        sendJson(res, 403, { error: 'Only student accounts can use the Android app.' });
        return;
      }
      otp.usedAt = now();
      await createMobileSessionResponse(res, user);
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/logout') {
      const token = sessionTokenFromRequest(req);
      db.sessions = db.sessions.filter((session) => session.token !== token);
      await saveDb();
      res.writeHead(200, {
        'content-type': 'application/json; charset=utf-8',
        ...securityHeaders(),
        'set-cookie': `sms_session=; HttpOnly; SameSite=Lax; Path=/; Max-Age=0${cookieSecurity()}`,
      });
      res.end(JSON.stringify({ ok: true }));
      return;
    }

    if (req.method === 'GET' && url.pathname === '/api/me') {
      sendJson(res, 200, { user: publicUser(sessionUser(req)), institute: publicInstituteProfile(), csrfToken: csrfTokenFor(req) });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/password-reset/request') {
      const input = await bodyJson(req);
      const identifier = String(input.email || input.identifier || '').trim().toLowerCase();
      const user = db.users.find((item) => item.status === 'active' && (
        String(item.email || '').toLowerCase() === identifier
        || String(item.mobile || '') === identifier
        || String(item.studentMobile || '') === identifier
      ));
      let response = { ok: true, message: 'If the account exists, reset instructions have been sent.' };
      if (user) {
        const token = randomUUID() + randomUUID();
        const code = otpCode();
        const resetUrl = `${req.headers['x-forwarded-proto'] || 'http'}://${req.headers.host}/?reset=${encodeURIComponent(token)}`;
        db.passwordResets = [
          { token, userId: user.id, codeHash: hashPassword(code), expiresAt: new Date(Date.now() + 15 * 60_000).toISOString(), usedAt: '', createdAt: now() },
          ...db.passwordResets.filter((item) => item.userId !== user.id || item.usedAt),
        ].slice(0, 200);
        if (user.email) await sendResultEmail(user.email, 'Password reset OTP', `Your password reset OTP is ${code}. Use this link within 15 minutes: ${resetUrl}`);
        await sendSms(user.studentMobile || user.mobile, `(Retina) Your Retina OTP is: ${code}. Please do not share your OTP with anyone.`, 'general', { passwordReset: true, userId: user.id });
        logActivity(user.id, 'Requested password reset');
        await saveDb();
        response = {
          ...response,
          resetToken: token,
          maskedMobile: (user.studentMobile || user.mobile) ? `${String(user.studentMobile || user.mobile).slice(0, 3)}*****${String(user.studentMobile || user.mobile).slice(-2)}` : '',
          emailSent: Boolean(user.email),
          devOtp: smsConfigured() ? undefined : code,
        };
      }
      sendJson(res, 200, response);
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/password-reset/confirm') {
      const input = await bodyJson(req);
      const token = String(input.token || '').trim();
      const password = String(input.password || '');
      const reset = db.passwordResets.find((item) => item.token === token && !item.usedAt && new Date(item.expiresAt) > new Date());
      if (!reset) {
        sendJson(res, 400, { error: 'Reset link is invalid or expired' });
        return;
      }
      if (reset.codeHash && !verifyPassword(String(input.otp || ''), reset.codeHash)) {
        sendJson(res, 401, { error: 'Invalid reset OTP' });
        return;
      }
      if (password.length < 6) {
        sendJson(res, 400, { error: 'Password must be at least 6 characters' });
        return;
      }
      const user = db.users.find((item) => item.id === reset.userId);
      if (!user) {
        sendJson(res, 404, { error: 'User not found' });
        return;
      }
      user.passwordHash = hashPassword(password);
      user.failedLogins = 0;
      user.lockedUntil = '';
      reset.usedAt = now();
      db.sessions = db.sessions.filter((session) => session.userId !== user.id);
      logActivity(user.id, 'Reset password');
      await saveDb();
      sendJson(res, 200, { ok: true });
      return;
    }

    if (req.method === 'GET' && url.pathname === '/api/dashboard') {
      const user = requireAuth(req, res);
      if (!user) return;
      if (isStaff(user)) {
        const sessionStudents = db.users.filter((item) => item.role === 'student' && inActiveSession(item));
        const sessionStudentIds = new Set(sessionStudents.map((student) => student.id));
        const sessionQuizzes = db.quizzes.filter(inActiveSession);
        sendJson(res, 200, {
          version: softwareVersion,
          sessions: db.sessionsList,
          activeSession: activeSession(),
          institute: publicInstituteProfile({ includeSecrets: user.role === 'admin' }),
          courses: db.courses,
          subjects: db.subjects,
          rollSettings: { ...db.rollSettings, preview: rollPreview() },
          batches: db.batches.filter(inActiveSession).map(publicBatch),
          students: sessionStudents.map((student) => ({
            ...publicUser(student),
            finance: studentFinancialSummary(student.id),
          })),
          teachers: db.teachers.map(publicTeacher).sort((a, b) => a.name.localeCompare(b.name)),
          employees: db.employees.map(publicEmployee).sort((a, b) => a.name.localeCompare(b.name)),
          managers: user.role === 'admin' ? db.users.filter((item) => item.role === 'manager').map(publicUser) : [],
          roles: user.role === 'admin' ? db.roles.map(publicRole) : db.roles.filter((role) => role.id === user.roleId).map(publicRole),
          menuPermissions,
          quizzes: sessionQuizzes,
          attempts: db.attempts.filter((attempt) => inActiveSession(attempt) || sessionStudentIds.has(attempt.studentId)).map(enrichAttempt),
          payments: db.payments.filter((payment) => inActiveSession(payment) || sessionStudentIds.has(payment.studentId)).map(publicPayment).sort((a, b) => String(b.paidAt).localeCompare(String(a.paidAt))),
          teacherPayments: db.teacherPayments.filter(inActiveSession).map(publicTeacherPayment).sort((a, b) => String(b.updatedAt || b.createdAt).localeCompare(String(a.updatedAt || a.createdAt))),
          employeePayments: db.employeePayments.filter(inActiveSession).map(publicEmployeePayment).sort((a, b) => String(b.updatedAt || b.createdAt).localeCompare(String(a.updatedAt || a.createdAt))),
          referencePayments: db.referencePayments.filter((payment) => inActiveSession(payment) || sessionStudentIds.has(payment.studentId)).map(publicReferencePayment).sort((a, b) => String(b.updatedAt || b.createdAt).localeCompare(String(a.updatedAt || a.createdAt))),
          otherPayments: db.otherPayments.filter(inActiveSession).map(publicOtherPayment).sort((a, b) => String(b.paidAt || b.createdAt).localeCompare(String(a.paidAt || a.createdAt))),
          shareholders: db.shareholders.map((item) => publicShareholder(item)).sort((a, b) => a.name.localeCompare(b.name)),
          shareholderLedger: db.shareholderLedger.filter(inActiveSession).map(publicShareholderLedger).sort((a, b) => String(b.entryDate || b.createdAt).localeCompare(String(a.entryDate || a.createdAt))),
          recordedClasses: db.recordedClasses.filter(inActiveSession).map(publicRecordedClass).sort((a, b) => String(b.updatedAt || b.createdAt).localeCompare(String(a.updatedAt || a.createdAt))),
          notifications: db.notifications.filter(inActiveSession).sort((a, b) => String(b.createdAt).localeCompare(String(a.createdAt))).slice(0, 100),
          manualResults: db.manualResults.filter(inActiveSession).sort((a, b) => String(b.createdAt).localeCompare(String(a.createdAt))),
          questionBank: db.questionBank,
          emailLogs: db.emailLogs.slice(0, 50),
          smsLogs: db.smsLogs.slice(0, 80),
          activity: db.activity.slice(0, 100),
        });
        return;
      }
      const liveExams = db.quizzes
        .filter((quiz) => quizAvailableForStudent(quiz, user.id) && !attemptsFor(user.id, quiz.id).some((attempt) => attempt.mode !== 'practice'))
        .map((quiz) => stripQuizAnswers(quiz));
      const previousExams = db.quizzes
        .filter((quiz) => quizPreviouslyAvailableForStudent(quiz, user.id))
        .map((quiz) => stripQuizAnswers(quiz));
      const attempts = db.attempts
        .filter((attempt) => attempt.studentId === user.id)
        .map(enrichAttempt)
        .map((attempt) => {
          const quiz = db.quizzes.find((item) => item.id === attempt.quizId);
          if (quiz?.resultVisibility === 'hidden') {
            return {
              ...attempt,
              correct: 0,
              earned: 0,
              total: 0,
              percentage: 0,
              passed: false,
              details: [],
              reviewStatus: 'hidden',
            };
          }
          return attempt;
        });
      sendJson(res, 200, {
        institute: publicInstituteProfile(),
        courses: db.courses.filter((course) => (user.courseIds || []).includes(course.id)),
        batches: db.batches
          .filter((batch) => (user.batchEnrollments || []).some((item) => item.batchId === batch.id))
          .map(publicBatch),
        assigned: [...liveExams, ...previousExams],
        liveExams,
        previousExams,
        attempts,
        finance: studentFinancialSummary(user.id),
        payments: db.payments
          .filter((payment) => payment.studentId === user.id)
          .map(publicPayment)
          .sort((a, b) => String(b.paidAt).localeCompare(String(a.paidAt))),
        recordedClasses: db.recordedClasses.filter((item) => recordedClassAvailableForStudent(item, user.id)).map(publicRecordedClass),
        notifications: db.notifications
          .filter((item) => inActiveSession(item) && notificationVisibleToStudent(item, user))
          .sort((a, b) => String(b.createdAt).localeCompare(String(a.createdAt))),
        progressReport: progressReportForStudent(user.id),
        subjects: db.subjects,
      });
      return;
    }

    if (req.method === 'PATCH' && url.pathname === '/api/institute-profile') {
      const admin = requireSuperAdmin(req, res);
      if (!admin) return;
      const input = await bodyJson(req);
      const current = publicInstituteProfile({ includeSecrets: true });
      const name = String(input.name || '').trim();
      const phone = String(input.phone || '').trim();
      if (!name || !phone) {
        sendJson(res, 400, { error: 'Institute name and phone are required' });
        return;
      }
      db.institute = {
        ...current,
        name,
        tagline: String(input.tagline || '').trim(),
        registrationNo: String(input.registrationNo || '').trim(),
        phone,
        email: String(input.email || '').trim().toLowerCase(),
        website: String(input.website || '').trim(),
        address: String(input.address || '').trim(),
        logoDataUrl: sanitizeImageDataUrl(input.logoDataUrl) || current.logoDataUrl || '',
        faviconDataUrl: sanitizeImageDataUrl(input.faviconDataUrl) || current.faviconDataUrl || '',
        primaryColor: sanitizeColor(input.primaryColor, current.primaryColor || '#2563eb'),
        secondaryColor: sanitizeColor(input.secondaryColor, current.secondaryColor || '#38bdf8'),
        receiptNote: String(input.receiptNote || '').trim(),
        admissionTerms: String(input.admissionTerms || '').trim(),
        supportContact: String(input.supportContact || '').trim(),
        smsSettings: input.smsSettings ? sanitizeSmsSettings(input.smsSettings, current.smsSettings) : current.smsSettings,
        updatedAt: now(),
      };
      logActivity(admin.id, `Updated institute profile ${db.institute.name}`);
      await saveDb();
      sendJson(res, 200, { institute: publicInstituteProfile({ includeSecrets: true }) });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/sms/test') {
      const admin = requireSuperAdmin(req, res);
      if (!admin) return;
      const input = await bodyJson(req);
      const mobile = String(input.mobile || '').trim();
      const message = String(input.message || 'SMS gateway test message.').trim();
      if (!mobile) {
        sendJson(res, 400, { error: 'Test mobile number is required' });
        return;
      }
      const log = await sendSms(mobile, message, 'general', { test: true, userId: admin.id });
      await saveDb();
      sendJson(res, 200, { log });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/sessions') {
      const admin = requireSuperAdmin(req, res);
      if (!admin) return;
      const input = await bodyJson(req);
      const name = String(input.name || '').trim();
      if (!name) {
        sendJson(res, 400, { error: 'Session name is required' });
        return;
      }
      if (db.sessionsList.some((session) => session.name.toLowerCase() === name.toLowerCase())) {
        sendJson(res, 409, { error: 'Session already exists' });
        return;
      }
      const session = { id: randomUUID(), name, status: 'active', createdAt: now(), updatedAt: now() };
      db.sessionsList.push(session);
      db.activeSessionId = session.id;
      logActivity(admin.id, `Created and switched to session ${session.name}`);
      await saveDb();
      sendJson(res, 201, { session, activeSession: session });
      return;
    }

    if (req.method === 'PATCH' && url.pathname === '/api/sessions/active') {
      const admin = requireSuperAdmin(req, res);
      if (!admin) return;
      const input = await bodyJson(req);
      const session = db.sessionsList.find((item) => item.id === input.sessionId && item.status !== 'inactive');
      if (!session) {
        sendJson(res, 404, { error: 'Session not found' });
        return;
      }
      db.activeSessionId = session.id;
      session.updatedAt = now();
      logActivity(admin.id, `Switched active session to ${session.name}`);
      await saveDb();
      sendJson(res, 200, { activeSession: session });
      return;
    }

    if (req.method === 'GET' && url.pathname === '/api/backup/session.json') {
      const admin = requireSuperAdmin(req, res);
      if (!admin) return;
      const sessionId = url.searchParams.get('sessionId') || db.activeSessionId;
      const backup = sessionBackup(sessionId);
      logActivity(admin.id, `Downloaded session backup ${backup.session?.name || sessionId}`);
      await saveDb();
      res.writeHead(200, { ...securityHeaders(), 'content-type': 'application/json; charset=utf-8', 'content-disposition': `attachment; filename="session-backup-${backup.session?.name || sessionId}.json"` });
      res.end(JSON.stringify(backup, null, 2));
      return;
    }

    if (req.method === 'GET' && url.pathname === '/api/backup/full.json') {
      const admin = requireSuperAdmin(req, res);
      if (!admin) return;
      logActivity(admin.id, 'Downloaded full software backup');
      await saveDb();
      res.writeHead(200, { ...securityHeaders(), 'content-type': 'application/json; charset=utf-8', 'content-disposition': 'attachment; filename="full-software-backup.json"' });
      res.end(JSON.stringify(fullBackup(), null, 2));
      return;
    }

    if (req.method === 'GET' && ['/api/export/session-finance.csv', '/api/export/session-finance.pdf', '/api/export/session-finance.xlsx'].includes(url.pathname)) {
      const admin = requireSuperAdmin(req, res);
      if (!admin) return;
      const rows = sessionFinanceRows(url.searchParams.get('sessionId') || db.activeSessionId)
        .filter((row) => !url.searchParams.get('category') || row[0] === url.searchParams.get('category'));
      const headers = ['Category', 'Type', 'Description', 'Person', 'Paid', 'Due', 'Total', 'Date', 'Entered By', 'Session'];
      if (url.pathname.endsWith('.xlsx')) {
        const workbook = createTableWorkbook(headers, rows);
        res.writeHead(200, { ...securityHeaders(), 'content-type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'content-disposition': 'attachment; filename="session-finance-report.xlsx"', 'content-length': workbook.length });
        res.end(workbook);
      } else if (url.pathname.endsWith('.pdf')) {
        const buffer = await createReportPdfBuffer('Session Income Expense Report', headers, rows);
        res.writeHead(200, { ...securityHeaders(), 'content-type': 'application/pdf', 'content-disposition': 'attachment; filename="session-finance-report.pdf"', 'content-length': buffer.length });
        res.end(buffer);
      } else {
        res.writeHead(200, { ...securityHeaders(), 'content-type': 'text/csv; charset=utf-8', 'content-disposition': 'attachment; filename="session-finance-report.csv"' });
        res.end([headers.map(csvEscape).join(','), ...rows.map((row) => row.map(csvEscape).join(','))].join('\n'));
      }
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/backup/restore') {
      const admin = requireSuperAdmin(req, res);
      if (!admin) return;
      const input = await bodyJson(req);
      const backup = input.backup || input;
      if (backup.type === 'full' && backup.data?.users && backup.data?.sessionsList) {
        db = backup.data;
        migrateDb();
        logActivity(admin.id, 'Restored full software backup');
        await saveDb();
        sendJson(res, 200, { ok: true, type: 'full' });
        return;
      }
      if (backup.type === 'session' && backup.session && backup.data) {
        const session = { ...backup.session, id: backup.session.id || randomUUID(), restoredAt: now(), updatedAt: now() };
        db.sessionsList = db.sessionsList.filter((item) => item.id !== session.id);
        db.sessionsList.push(session);
        const data = backup.data;
        const replaceCollection = (name, items = []) => {
          db[name] = [...(db[name] || []).filter((item) => item.sessionId !== session.id), ...items.map((item) => ({ ...item, sessionId: session.id }))];
        };
        db.users = [...db.users.filter((user) => user.role !== 'student' || user.sessionId !== session.id), ...(data.students || []).map((student) => ({ ...student, sessionId: session.id }))];
        for (const collection of sessionScopedCollections) replaceCollection(collection, data[collection]);
        db.activeSessionId = session.id;
        migrateDb();
        logActivity(admin.id, `Restored session backup ${session.name}`);
        await saveDb();
        sendJson(res, 200, { ok: true, type: 'session', activeSession: session });
        return;
      }
      sendJson(res, 400, { error: 'Invalid backup file' });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/roles') {
      const admin = requireSuperAdmin(req, res);
      if (!admin) return;
      const input = await bodyJson(req);
      const name = String(input.name || '').trim();
      if (!name) {
        sendJson(res, 400, { error: 'Role name is required' });
        return;
      }
      if (db.roles.some((role) => role.name.toLowerCase() === name.toLowerCase())) {
        sendJson(res, 409, { error: 'Role name already exists' });
        return;
      }
      const role = {
        id: randomUUID(),
        sessionId: db.activeSessionId,
        name,
        description: String(input.description || '').trim(),
        menuAccess: Array.isArray(input.menuAccess) ? input.menuAccess.filter((key) => menuPermissions.includes(key)) : [],
        status: input.status === 'inactive' ? 'inactive' : 'active',
        system: false,
        createdAt: now(),
        updatedAt: now(),
      };
      db.roles.push(role);
      logActivity(admin.id, `Created role ${role.name}`);
      await saveDb();
      sendJson(res, 201, { role: publicRole(role) });
      return;
    }

    if (req.method === 'PATCH' && url.pathname.startsWith('/api/roles/')) {
      const admin = requireSuperAdmin(req, res);
      if (!admin) return;
      const roleId = url.pathname.split('/').pop();
      const role = db.roles.find((item) => item.id === roleId);
      if (!role) {
        sendJson(res, 404, { error: 'Role not found' });
        return;
      }
      const input = await bodyJson(req);
      if (input.name !== undefined) {
        const name = String(input.name || '').trim();
        if (!name) {
          sendJson(res, 400, { error: 'Role name is required' });
          return;
        }
        if (db.roles.some((item) => item.id !== role.id && item.name.toLowerCase() === name.toLowerCase())) {
          sendJson(res, 409, { error: 'Role name already exists' });
          return;
        }
        role.name = name;
      }
      if (input.description !== undefined) role.description = String(input.description || '').trim();
      if (input.menuAccess !== undefined) role.menuAccess = Array.isArray(input.menuAccess) ? input.menuAccess.filter((key) => menuPermissions.includes(key)) : [];
      if (input.status !== undefined) role.status = input.status === 'inactive' ? 'inactive' : 'active';
      role.updatedAt = now();
      logActivity(admin.id, `Updated role ${role.name}`);
      await saveDb();
      sendJson(res, 200, { role: publicRole(role) });
      return;
    }

    if (req.method === 'DELETE' && url.pathname.startsWith('/api/roles/')) {
      const admin = requireSuperAdmin(req, res);
      if (!admin) return;
      const roleId = url.pathname.split('/').pop();
      const role = db.roles.find((item) => item.id === roleId);
      if (!role) {
        sendJson(res, 404, { error: 'Role not found' });
        return;
      }
      if (role.system || db.users.some((user) => user.role === 'manager' && user.roleId === role.id)) {
        sendJson(res, 409, { error: 'Role is assigned or system protected. Deactivate it instead.' });
        return;
      }
      db.roles = db.roles.filter((item) => item.id !== role.id);
      logActivity(admin.id, `Deleted role ${role.name}`);
      await saveDb();
      sendJson(res, 200, { ok: true });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/courses') {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const input = await bodyJson(req);
      const name = String(input.name || '').trim();
      if (!name) {
        sendJson(res, 400, { error: 'Course name is required' });
        return;
      }
      if (db.courses.some((course) => course.name.toLowerCase() === name.toLowerCase())) {
        sendJson(res, 409, { error: 'Course name already exists' });
        return;
      }
      const course = {
        id: randomUUID(),
        name,
        code: normalizedCode(input.code, String(db.courses.length + 1).padStart(2, '0'), 2),
        description: String(input.description || '').trim(),
        defaultFee: money(input.defaultFee),
        status: 'active',
        createdAt: now(),
        updatedAt: now(),
      };
      db.courses.push(course);
      logActivity(admin.id, `Created course ${course.name}`);
      await saveDb();
      sendJson(res, 201, { course });
      return;
    }

    if (req.method === 'PATCH' && url.pathname.startsWith('/api/courses/')) {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const courseId = url.pathname.split('/').pop();
      const course = db.courses.find((item) => item.id === courseId);
      if (!course) {
        sendJson(res, 404, { error: 'Course not found' });
        return;
      }
      const input = await bodyJson(req);
      if (input.name !== undefined) {
        const name = String(input.name || '').trim();
        if (!name) {
          sendJson(res, 400, { error: 'Course name is required' });
          return;
        }
        if (db.courses.some((item) => item.id !== course.id && item.name.toLowerCase() === name.toLowerCase())) {
          sendJson(res, 409, { error: 'Course name already exists' });
          return;
        }
        course.name = name;
      }
      if (input.description !== undefined) course.description = String(input.description || '').trim();
      if (input.code !== undefined) course.code = normalizedCode(input.code, course.code || '01', 2);
      if (input.defaultFee !== undefined) course.defaultFee = money(input.defaultFee);
      if (input.status !== undefined) course.status = input.status === 'inactive' ? 'inactive' : 'active';
      course.updatedAt = now();
      logActivity(admin.id, `Updated course ${course.name}`);
      await saveDb();
      sendJson(res, 200, { course });
      return;
    }

    if (req.method === 'DELETE' && url.pathname.startsWith('/api/courses/')) {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const courseId = url.pathname.split('/').pop();
      const course = db.courses.find((item) => item.id === courseId);
      if (!course) {
        sendJson(res, 404, { error: 'Course not found' });
        return;
      }
      const enrolledStudents = db.users.filter((student) => student.role === 'student' && (student.courseIds || []).includes(courseId)).length;
      const assignedQuizzes = db.quizzes.filter((quiz) => (quiz.assignedCourseIds || []).includes(courseId)).length;
      const recordedPayments = db.payments.filter((payment) => payment.courseId === courseId).length;
      const courseBatches = db.batches.filter((batch) => batch.courseId === courseId).length;
      if (enrolledStudents || assignedQuizzes || recordedPayments || courseBatches) {
        sendJson(res, 409, { error: `Course is used by ${enrolledStudents} students, ${assignedQuizzes} quizzes, ${courseBatches} batches and ${recordedPayments} payments. Deactivate it instead.` });
        return;
      }
      db.courses = db.courses.filter((item) => item.id !== courseId);
      logActivity(admin.id, `Deleted course ${course.name}`);
      await saveDb();
      sendJson(res, 200, { ok: true });
      return;
    }

    if (req.method === 'PATCH' && url.pathname === '/api/roll-settings') {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const input = await bodyJson(req);
      db.rollSettings = {
        ...db.rollSettings,
        year: rollYear({ year: input.year }),
        rollPrefix: rollPrefix({ ...db.rollSettings, rollPrefix: input.rollPrefix, year: input.year }),
        serialPadding: Math.min(4, Math.max(1, Math.floor(Number(input.serialPadding || 2)))),
        rollLimit: Math.min(99, Math.max(1, Math.floor(Number(input.rollLimit || 99)))),
      };
      logActivity(admin.id, 'Updated roll auto-generation settings');
      await saveDb();
      sendJson(res, 200, { rollSettings: { ...db.rollSettings, preview: rollPreview() } });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/subjects') {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const input = await bodyJson(req);
      const name = String(input.name || '').trim();
      if (!name) {
        sendJson(res, 400, { error: 'Subject name is required' });
        return;
      }
      if (db.subjects.some((item) => item.name.toLowerCase() === name.toLowerCase())) {
        sendJson(res, 409, { error: 'Subject already exists' });
        return;
      }
      const subject = { id: randomUUID(), name, code: String(input.code || '').trim(), status: 'active', createdAt: now(), updatedAt: now() };
      db.subjects.push(subject);
      await saveDb();
      sendJson(res, 201, { subject });
      return;
    }

    if (req.method === 'PATCH' && url.pathname.startsWith('/api/subjects/')) {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const subject = db.subjects.find((item) => item.id === url.pathname.split('/').pop());
      if (!subject) {
        sendJson(res, 404, { error: 'Subject not found' });
        return;
      }
      const input = await bodyJson(req);
      if (input.name !== undefined) subject.name = String(input.name || '').trim() || subject.name;
      if (input.code !== undefined) subject.code = String(input.code || '').trim();
      if (input.status !== undefined) subject.status = input.status === 'inactive' ? 'inactive' : 'active';
      subject.updatedAt = now();
      await saveDb();
      sendJson(res, 200, { subject });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/batches') {
      const staff = requireAdmin(req, res);
      if (!staff) return;
      const input = await bodyJson(req);
      const name = String(input.name || '').trim();
      const course = db.courses.find((item) => item.id === input.courseId);
      if (!name || !course) {
        sendJson(res, 400, { error: 'Batch name and course are required' });
        return;
      }
      if (db.batches.some((batch) => batch.courseId === course.id && batch.name.toLowerCase() === name.toLowerCase())) {
        sendJson(res, 409, { error: 'Batch name already exists for this course' });
        return;
      }
      const rollStartNumber = Math.max(1, Math.floor(Number(input.rollStartNumber || 1)));
      const batch = {
        id: randomUUID(),
        name,
        code: normalizedCode(input.code, String(db.batches.filter((item) => item.courseId === course.id).length + 1).padStart(2, '0'), 2),
        courseId: course.id,
        startDate: String(input.startDate || '').trim(),
        endDate: String(input.endDate || '').trim(),
        schedule: String(input.schedule || '').trim(),
        capacity: Math.max(0, Math.floor(Number(input.capacity) || 0)),
        rollStartNumber,
        rollNextNumber: Math.max(rollStartNumber, Math.floor(Number(input.rollNextNumber || rollStartNumber))),
        rollLimit: Math.min(99, Math.max(1, Math.floor(Number(input.rollLimit || db.rollSettings?.rollLimit || 99)))),
        status: input.status === 'inactive' ? 'inactive' : 'active',
        createdAt: now(),
        updatedAt: now(),
      };
      db.batches.push(batch);
      logActivity(staff.id, `Created batch ${batch.name}`);
      await saveDb();
      sendJson(res, 201, { batch: publicBatch(batch) });
      return;
    }

    if (req.method === 'PATCH' && /^\/api\/batches\/[^/]+\/students$/.test(url.pathname)) {
      const staff = requireAdmin(req, res);
      if (!staff) return;
      const batchId = url.pathname.split('/')[3];
      const batch = db.batches.find((item) => item.id === batchId);
      if (!batch) {
        sendJson(res, 404, { error: 'Batch not found' });
        return;
      }
      const input = await bodyJson(req);
      const requestedIds = [...new Set(Array.isArray(input.studentIds) ? input.studentIds : [])];
      const eligible = requestedIds.filter((studentId) => db.users.some((student) => (
        student.id === studentId
        && student.role === 'student'
        && (student.courseIds || []).includes(batch.courseId)
      )));
      if (batch.capacity > 0 && eligible.length > batch.capacity) {
        sendJson(res, 400, { error: `Batch capacity is ${batch.capacity}` });
        return;
      }
      for (const student of db.users.filter((item) => item.role === 'student')) {
        student.batchEnrollments ||= [];
        student.batchEnrollments = student.batchEnrollments.filter((item) => item.courseId !== batch.courseId);
        if (eligible.includes(student.id)) {
          student.batchEnrollments.push({ courseId: batch.courseId, batchId: batch.id });
        }
      }
      logActivity(staff.id, `Updated students in batch ${batch.name}`);
      await saveDb();
      sendJson(res, 200, { batch: publicBatch(batch) });
      return;
    }

    if (req.method === 'PATCH' && url.pathname.startsWith('/api/batches/')) {
      const staff = requireAdmin(req, res);
      if (!staff) return;
      const batchId = url.pathname.split('/').pop();
      const batch = db.batches.find((item) => item.id === batchId);
      if (!batch) {
        sendJson(res, 404, { error: 'Batch not found' });
        return;
      }
      const input = await bodyJson(req);
      if (input.name !== undefined) {
        const name = String(input.name || '').trim();
        if (!name) {
          sendJson(res, 400, { error: 'Batch name is required' });
          return;
        }
        batch.name = name;
      }
      if (input.courseId !== undefined && input.courseId !== batch.courseId) {
        const course = db.courses.find((item) => item.id === input.courseId);
        const assigned = db.users.some((student) => (
          student.role === 'student'
          && (student.batchEnrollments || []).some((item) => item.batchId === batch.id)
        ));
        if (!course || assigned) {
          sendJson(res, 409, { error: assigned ? 'Remove students before changing the batch course' : 'Course not found' });
          return;
        }
        batch.courseId = course.id;
      }
      if (input.startDate !== undefined) batch.startDate = String(input.startDate || '').trim();
      if (input.code !== undefined) batch.code = normalizedCode(input.code, batch.code || '01', 2);
      if (input.endDate !== undefined) batch.endDate = String(input.endDate || '').trim();
      if (input.schedule !== undefined) batch.schedule = String(input.schedule || '').trim();
      if (input.capacity !== undefined) batch.capacity = Math.max(0, Math.floor(Number(input.capacity) || 0));
      if (input.rollStartNumber !== undefined) batch.rollStartNumber = Math.max(1, Math.floor(Number(input.rollStartNumber || 1)));
      if (input.rollNextNumber !== undefined) batch.rollNextNumber = Math.max(Number(batch.rollStartNumber || 1), Math.floor(Number(input.rollNextNumber || batch.rollStartNumber || 1)));
      if (input.rollLimit !== undefined) batch.rollLimit = Math.min(99, Math.max(1, Math.floor(Number(input.rollLimit || db.rollSettings?.rollLimit || 99))));
      if (input.status !== undefined) batch.status = input.status === 'inactive' ? 'inactive' : 'active';
      batch.updatedAt = now();
      logActivity(staff.id, `Updated batch ${batch.name}`);
      await saveDb();
      sendJson(res, 200, { batch: publicBatch(batch) });
      return;
    }

    if (req.method === 'DELETE' && url.pathname.startsWith('/api/batches/')) {
      const staff = requireAdmin(req, res);
      if (!staff) return;
      const batchId = url.pathname.split('/').pop();
      const batch = db.batches.find((item) => item.id === batchId);
      if (!batch) {
        sendJson(res, 404, { error: 'Batch not found' });
        return;
      }
      const assigned = db.users.filter((student) => (
        student.role === 'student'
        && (student.batchEnrollments || []).some((item) => item.batchId === batch.id)
      )).length;
      if (assigned) {
        sendJson(res, 409, { error: `Batch has ${assigned} students. Remove them or deactivate the batch.` });
        return;
      }
      db.batches = db.batches.filter((item) => item.id !== batch.id);
      logActivity(staff.id, `Deleted batch ${batch.name}`);
      await saveDb();
      sendJson(res, 200, { ok: true });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/notifications') {
      const staff = requireAdmin(req, res);
      if (!staff) return;
      const input = await bodyJson(req);
      const title = String(input.title || '').trim();
      const body = String(input.body || '').trim();
      if (!title || !body) {
        sendJson(res, 400, { error: 'Message title and body are required' });
        return;
      }
      const audienceType = ['all', 'course', 'batch', 'selected'].includes(input.audienceType) ? input.audienceType : 'all';
      const courseIds = Array.isArray(input.courseIds) ? input.courseIds.map(String).filter((id) => db.courses.some((course) => course.id === id)) : [String(input.courseId || '')].filter(Boolean);
      const batchIds = Array.isArray(input.batchIds) ? input.batchIds.map(String).filter((id) => db.batches.some((batch) => batch.id === id)) : [String(input.batchId || '')].filter(Boolean);
      const recipients = notificationRecipients({ ...input, audienceType });
      if (!recipients.length) {
        sendJson(res, 400, { error: 'No active student found for this message' });
        return;
      }
      const notification = {
        id: randomUUID(),
        sessionId: db.activeSessionId,
        title,
        body,
        audienceType,
        courseIds,
        batchIds,
        courseId: courseIds[0] || '',
        batchId: batchIds[0] || '',
        recipientIds: recipients.map((student) => student.id),
        sendSms: Boolean(input.sendSms),
        smsSent: 0,
        createdBy: staff.id,
        createdByName: staff.name,
        createdAt: now(),
      };
      db.notifications.unshift(notification);
      if (notification.sendSms) {
        for (const student of recipients) {
          const result = await sendSms(student.studentMobile || student.mobile, `${title}: ${body}`, 'notification', {
            notificationId: notification.id,
            studentId: student.id,
          });
          if (result?.sent) notification.smsSent += 1;
        }
      }
      logActivity(staff.id, `Sent notification "${title}" to ${recipients.length} students`);
      await saveDb();
      sendJson(res, 201, { notification, count: recipients.length, smsSent: notification.smsSent });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/due-reminders') {
      const staff = requireMenuAccess(req, res, 'messages');
      if (!staff) return;
      const input = await bodyJson(req);
      const courseIds = Array.isArray(input.courseIds) ? input.courseIds.map(String).filter((id) => db.courses.some((course) => course.id === id)) : [];
      const batchIds = Array.isArray(input.batchIds) ? input.batchIds.map(String).filter((id) => db.batches.some((batch) => batch.id === id)) : [];
      const title = String(input.title || 'Payment Due Reminder').trim();
      const template = String(input.template || 'Dear {name}, your due amount is Tk {due}. Please pay soon. - {institute}').trim();
      const recipients = dueReminderRecipients({ ...input, courseIds, batchIds });
      if (!recipients.length) {
        sendJson(res, 400, { error: 'No due student found for this reminder' });
        return;
      }
      let smsSent = 0;
      const created = [];
      for (const student of recipients) {
        const due = dueAmountForStudent(student, courseIds);
        const body = renderTemplate(template, {
          name: student.name,
          roll: student.rollNumber || '',
          mobile: student.studentMobile || student.mobile || '',
          due,
          institute: db.institute?.name || 'Institute',
        });
        const notification = {
          id: randomUUID(),
          sessionId: db.activeSessionId,
          title,
          body,
          audienceType: 'due',
          courseIds,
          batchIds,
          courseId: courseIds[0] || '',
          batchId: batchIds[0] || '',
          recipientIds: [student.id],
          sendSms: Boolean(input.sendSms),
          smsSent: 0,
          dueAmount: due,
          createdBy: staff.id,
          createdByName: staff.name,
          createdAt: now(),
        };
        db.notifications.unshift(notification);
        created.push(notification);
        if (notification.sendSms) {
          const result = await sendSms(student.studentMobile || student.mobile, `${title}: ${body}`, 'notification', {
            notificationId: notification.id,
            studentId: student.id,
            dueAmount: due,
          });
          if (result?.sent) {
            notification.smsSent = 1;
            smsSent += 1;
          }
        }
      }
      logActivity(staff.id, `Sent due reminders to ${recipients.length} students`);
      await saveDb();
      sendJson(res, 201, { notifications: created, count: recipients.length, smsSent });
      return;
    }

    if (req.method === 'PATCH' && url.pathname === '/api/profile') {
      const user = requireAuth(req, res);
      if (!user) return;
      const input = await bodyJson(req);
      if (input.name !== undefined) user.name = String(input.name || '').trim() || user.name;
      if (input.email !== undefined) {
        const email = String(input.email || '').trim().toLowerCase();
        if (email && db.users.some((item) => item.id !== user.id && String(item.email || '').toLowerCase() === email)) {
          sendJson(res, 409, { error: 'Email already exists' });
          return;
        }
        user.email = email;
      }
      if (user.role === 'student') {
        if (input.college !== undefined) user.college = String(input.college || '').trim();
        for (const key of ['sscResult', 'sscPassingYear', 'hscResult', 'hscPassingYear']) {
          if (input[key] !== undefined) user[key] = String(input[key] || '').trim();
        }
        if (input.photoDataUrl !== undefined) user.photoDataUrl = sanitizeImageDataUrl(input.photoDataUrl) || user.photoDataUrl || '';
      }
      if (input.mobile !== undefined) {
        const mobile = String(input.mobile || '').trim();
        if (!mobile) {
          sendJson(res, 400, { error: 'Mobile number is required' });
          return;
        }
        const duplicate = db.users.some((item) => item.id !== user.id && (
          String(item.mobile || '') === mobile || String(item.studentMobile || '') === mobile
        ));
        if (duplicate) {
          sendJson(res, 409, { error: 'Mobile number already exists' });
          return;
        }
        user.mobile = mobile;
        if (user.role === 'student') user.studentMobile = mobile;
      }
      if (input.currentPassword || input.newPassword) {
        if (!verifyPassword(String(input.currentPassword || ''), user.passwordHash)) {
          sendJson(res, 400, { error: 'Current password is incorrect' });
          return;
        }
        if (String(input.newPassword || '').length < 6) {
          sendJson(res, 400, { error: 'New password must be at least 6 characters' });
          return;
        }
        user.passwordHash = hashPassword(String(input.newPassword));
      }
      logActivity(user.id, 'Updated profile');
      await saveDb();
      sendJson(res, 200, { user: publicUser(user) });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/managers') {
      const admin = requireSuperAdmin(req, res);
      if (!admin) return;
      const input = await bodyJson(req);
      const name = String(input.name || '').trim();
      const mobile = String(input.mobile || '').trim();
      const email = String(input.email || '').trim().toLowerCase();
      const password = String(input.password || '').trim();
      const role = db.roles.find((item) => item.id === input.roleId && item.status === 'active') || db.roles[0];
      if (!name || !mobile || !password) {
        sendJson(res, 400, { error: 'Name, mobile and password are required' });
        return;
      }
      const duplicate = db.users.some((user) => (
        String(user.mobile || '') === mobile
        || String(user.studentMobile || '') === mobile
        || (email && String(user.email || '').toLowerCase() === email)
      ));
      if (duplicate) {
        sendJson(res, 409, { error: 'Mobile or email already exists' });
        return;
      }
      const manager = {
        id: randomUUID(),
        role: 'manager',
        name,
        mobile,
        email,
        roleId: role?.id || '',
        passwordHash: hashPassword(password),
        status: 'active',
        createdAt: now(),
      };
      db.users.push(manager);
      logActivity(admin.id, `Added exam manager ${manager.mobile}`);
      await saveDb();
      sendJson(res, 201, { manager: publicUser(manager) });
      return;
    }

    if (req.method === 'PATCH' && url.pathname.startsWith('/api/managers/')) {
      const admin = requireSuperAdmin(req, res);
      if (!admin) return;
      const managerId = url.pathname.split('/').pop();
      const manager = db.users.find((user) => user.id === managerId && user.role === 'manager');
      if (!manager) {
        sendJson(res, 404, { error: 'Manager not found' });
        return;
      }
      const input = await bodyJson(req);
      if (input.name !== undefined) manager.name = String(input.name || '').trim() || manager.name;
      if (input.mobile !== undefined) {
        const mobile = String(input.mobile || '').trim();
        if (!mobile) {
          sendJson(res, 400, { error: 'Mobile number is required' });
          return;
        }
        const duplicate = db.users.some((user) => user.id !== manager.id && (
          String(user.mobile || '') === mobile || String(user.studentMobile || '') === mobile
        ));
        if (duplicate) {
          sendJson(res, 409, { error: 'Mobile number already exists' });
          return;
        }
        manager.mobile = mobile;
      }
      if (input.email !== undefined) manager.email = String(input.email || '').trim().toLowerCase();
      if (input.roleId !== undefined) {
        const role = db.roles.find((item) => item.id === input.roleId && item.status === 'active');
        if (!role) {
          sendJson(res, 400, { error: 'Select an active role' });
          return;
        }
        manager.roleId = role.id;
      }
      if (input.status !== undefined) manager.status = input.status === 'inactive' ? 'inactive' : 'active';
      if (input.password) manager.passwordHash = hashPassword(String(input.password));
      logActivity(admin.id, `Updated exam manager ${manager.mobile}`);
      await saveDb();
      sendJson(res, 200, { manager: publicUser(manager) });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/students') {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const input = await bodyJson(req);
      const email = String(input.email || '').trim().toLowerCase();
      const name = String(input.name || '').trim();
      const studentMobile = String(input.studentMobile || '').trim();
      if (!name || !studentMobile) {
        sendJson(res, 400, { error: 'Name and student mobile are required' });
        return;
      }
      if (email && db.users.some((user) => String(user.email || '').toLowerCase() === email)) {
        sendJson(res, 409, { error: 'Email already exists' });
        return;
      }
      if (db.users.some((user) => String(user.studentMobile || '') === studentMobile)) {
        sendJson(res, 409, { error: 'Student mobile already exists' });
        return;
      }
      const courseIds = Array.isArray(input.courseIds)
        ? input.courseIds.filter((id) => db.courses.some((course) => course.id === id))
        : [];
      let batchEnrollments = [];
      try {
        batchEnrollments = validateBatchEnrollments(courseIds, input.batchEnrollments);
      } catch (error) {
        sendJson(res, 400, { error: error.message });
        return;
      }
      let generatedRoll;
      try {
        generatedRoll = String(input.rollNumber || '').trim()
          ? { rollNumber: String(input.rollNumber || '').trim(), registrationNumber: String(input.registrationNumber || input.rollNumber || '').trim() }
          : nextStudentRoll(courseIds, batchEnrollments);
      } catch (error) {
        sendJson(res, 400, { error: error.message });
        return;
      }
      const rollNumber = generatedRoll.rollNumber;
      const registrationNumber = generatedRoll.registrationNumber || generatedRoll.rollNumber;
      if (db.users.some((user) => String(user.rollNumber || '').toLowerCase() === rollNumber.toLowerCase() || String(user.registrationNumber || '').toLowerCase() === registrationNumber.toLowerCase())) {
        sendJson(res, 409, { error: 'Roll/registration number already exists' });
        return;
      }
      const submittedFees = Array.isArray(input.courseFees) ? input.courseFees : [];
      const courseFees = courseIds.map((courseId) => {
        const course = db.courses.find((item) => item.id === courseId);
        const submitted = submittedFees.find((item) => item.courseId === courseId);
        const fee = money(course?.defaultFee ?? 0);
        return {
          courseId,
          fee,
          discount: Math.min(fee, money(submitted?.discount || 0)),
        };
      });
      const initialPayments = (Array.isArray(input.initialPayments) ? input.initialPayments : [])
        .filter((item) => courseIds.includes(item.courseId))
        .map((item) => ({ courseId: item.courseId, amount: money(item.amount) }))
        .filter((item) => item.amount > 0);
      for (const payment of initialPayments) {
        const fee = courseFees.find((item) => item.courseId === payment.courseId);
        if (!fee || payment.amount > money(fee.fee - fee.discount)) {
          sendJson(res, 400, { error: 'Initial deposit cannot exceed the payable course fee' });
          return;
        }
      }
      const student = {
        id: randomUUID(),
        sessionId: db.activeSessionId,
        role: 'student',
        name,
        rollNumber,
        registrationNumber,
        studentMobile,
        mobile: studentMobile,
        guardianMobile: String(input.guardianMobile || '').trim(),
        college: String(input.college || '').trim(),
        sscResult: String(input.sscResult || '').trim(),
        sscPassingYear: String(input.sscPassingYear || '').trim(),
        hscResult: String(input.hscResult || '').trim(),
        hscPassingYear: String(input.hscPassingYear || '').trim(),
        photoDataUrl: sanitizeImageDataUrl(input.photoDataUrl) || '',
        course: courseIds.map((id) => db.courses.find((course) => course.id === id)?.name).filter(Boolean).join(', '),
        courseIds,
        courseFees,
        batchEnrollments,
        reference: {
          type: String(input.referenceType || input.reference?.type || '').trim(),
          name: String(input.referenceName || input.reference?.name || '').trim(),
          mobile: String(input.referenceMobile || input.reference?.mobile || '').trim(),
          commissionAmount: money(input.referenceCommissionAmount ?? input.reference?.commissionAmount ?? 0),
          note: String(input.referenceNote || input.reference?.note || '').trim(),
        },
        admissionDate: String(input.admissionDate || '').trim(),
        academicYear: activeSession()?.name || '',
        email,
        passwordHash: hashPassword(String(input.password || studentMobile)),
        status: 'active',
        createdAt: now(),
      };
      db.users.push(student);
      if (student.reference?.name && student.reference?.commissionAmount > 0) {
        db.referencePayments.push({
          id: randomUUID(),
          studentId: student.id,
          referrerType: student.reference.type,
          referrerName: student.reference.name,
          referrerMobile: student.reference.mobile,
          commissionAmount: student.reference.commissionAmount,
          paidAmount: 0,
          method: 'cash',
          reference: '',
          note: student.reference.note,
          paidAt: '',
          status: 'due',
          createdBy: admin.id,
          createdAt: now(),
          updatedAt: now(),
        });
      }
      const receipts = initialPayments.map((item) => {
        const payment = {
          id: randomUUID(),
          sessionId: db.activeSessionId,
          receiptNumber: nextReceiptNumber(),
          studentId: student.id,
          courseId: item.courseId,
          amount: item.amount,
          method: 'cash',
          reference: '',
          note: 'Initial admission deposit',
          paidAt: now(),
          status: 'completed',
          receivedBy: admin.id,
          receivedByName: admin.name,
          createdAt: now(),
          updatedAt: now(),
        };
        db.payments.push(payment);
        return publicPayment(payment);
      });
      logActivity(admin.id, `Added student ${student.email}`);
      await saveDb();
      sendJson(res, 201, { student: publicUser(student), receipts });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/students/import') {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const input = await bodyJson(req);
      const rows = String(input.csv || '').split(/\r?\n/).map((line) => line.trim()).filter(Boolean);
      const created = [];
      const skipped = [];
      for (const row of rows) {
        const [nameRaw, rollRaw, studentMobileRaw, guardianMobileRaw, collegeRaw, courseRaw, emailRaw, passwordRaw] = row.split(',').map((part) => String(part || '').trim());
        const email = String(emailRaw || '').toLowerCase();
        const rollNumber = String(rollRaw || '').trim();
        const studentMobile = String(studentMobileRaw || '').trim();
        const password = passwordRaw || studentMobile || 'student123';
        const duplicate = (email && db.users.some((user) => String(user.email || '').toLowerCase() === email))
          || db.users.some((user) => String(user.rollNumber || '').toLowerCase() === rollNumber.toLowerCase())
          || db.users.some((user) => String(user.studentMobile || '') === studentMobile);
        if (!nameRaw || !rollNumber || !studentMobile || duplicate) {
          skipped.push(row);
          continue;
        }
        const importedCourse = ensureCourseByName(courseRaw);
        const student = {
          id: randomUUID(),
          role: 'student',
          name: nameRaw,
          rollNumber,
          registrationNumber: rollNumber,
          studentMobile,
          mobile: studentMobile,
          guardianMobile: guardianMobileRaw || '',
          college: collegeRaw || '',
          course: courseRaw || '',
          courseIds: importedCourse ? [importedCourse.id] : [],
          courseFees: importedCourse ? [{ courseId: importedCourse.id, fee: money(importedCourse.defaultFee), discount: 0 }] : [],
          admissionDate: '',
          academicYear: activeSession()?.name || '',
          sscResult: '',
          sscPassingYear: '',
          hscResult: '',
          hscPassingYear: '',
          photoDataUrl: '',
          email,
          passwordHash: hashPassword(password),
          status: 'active',
          createdAt: now(),
        };
        db.users.push(student);
        created.push(publicUser(student));
      }
      logActivity(admin.id, `Imported ${created.length} students`);
      await saveDb();
      sendJson(res, 201, { created, skipped });
      return;
    }

    if (req.method === 'PATCH' && url.pathname.startsWith('/api/students/')) {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const studentId = url.pathname.split('/').pop();
      const student = db.users.find((user) => user.id === studentId && user.role === 'student');
      if (!student) {
        sendJson(res, 404, { error: 'Student not found' });
        return;
      }
      const input = await bodyJson(req);
      if (input.name !== undefined) student.name = String(input.name || '').trim() || student.name;
      if (input.rollNumber !== undefined) {
        const rollNumber = String(input.rollNumber || '').trim();
        if (!rollNumber) {
          sendJson(res, 400, { error: 'Roll/registration number is required' });
          return;
        }
        if (db.users.some((item) => item.id !== student.id && String(item.rollNumber || '').toLowerCase() === rollNumber.toLowerCase())) {
          sendJson(res, 409, { error: 'Roll/registration number already exists' });
          return;
        }
        student.rollNumber = rollNumber;
        student.registrationNumber ||= rollNumber;
      }
      if (input.registrationNumber !== undefined) {
        const registrationNumber = String(input.registrationNumber || '').trim();
        if (registrationNumber && db.users.some((item) => item.id !== student.id && String(item.registrationNumber || '').toLowerCase() === registrationNumber.toLowerCase())) {
          sendJson(res, 409, { error: 'Registration number already exists' });
          return;
        }
        if (registrationNumber) student.registrationNumber = registrationNumber;
      }
      if (input.studentMobile !== undefined) {
        const studentMobile = String(input.studentMobile || '').trim();
        if (!studentMobile) {
          sendJson(res, 400, { error: 'Student mobile is required' });
          return;
        }
        if (db.users.some((item) => item.id !== student.id && (
          String(item.studentMobile || '') === studentMobile || String(item.mobile || '') === studentMobile
        ))) {
          sendJson(res, 409, { error: 'Student mobile already exists' });
          return;
        }
        student.studentMobile = studentMobile;
        student.mobile = student.studentMobile;
      }
      if (input.guardianMobile !== undefined) student.guardianMobile = String(input.guardianMobile || '').trim();
      if (input.college !== undefined) student.college = String(input.college || '').trim();
      for (const key of ['sscResult', 'sscPassingYear', 'hscResult', 'hscPassingYear']) {
        if (input[key] !== undefined) student[key] = String(input[key] || '').trim();
      }
      if (input.photoDataUrl !== undefined) student.photoDataUrl = sanitizeImageDataUrl(input.photoDataUrl) || student.photoDataUrl || '';
      if (input.admissionDate !== undefined) student.admissionDate = String(input.admissionDate || '').trim();
      student.academicYear = student.academicYear || activeSession()?.name || '';
      if (input.courseIds !== undefined) {
        student.courseIds = Array.isArray(input.courseIds)
          ? input.courseIds.filter((id) => db.courses.some((course) => course.id === id))
          : [];
        student.course = student.courseIds
          .map((id) => db.courses.find((course) => course.id === id)?.name)
          .filter(Boolean)
          .join(', ');
        student.courseFees = student.courseIds.map((courseId) => {
          const existing = (student.courseFees || []).find((item) => item.courseId === courseId);
          const course = db.courses.find((item) => item.id === courseId);
          return {
            courseId,
            fee: money(existing?.fee ?? course?.defaultFee ?? 0),
            discount: money(existing?.discount || 0),
          };
        });
        student.batchEnrollments = (student.batchEnrollments || []).filter((item) => student.courseIds.includes(item.courseId));
      }
      if (input.courseFees !== undefined) {
        student.courseFees = student.courseIds.map((courseId) => {
          const submitted = Array.isArray(input.courseFees)
            ? input.courseFees.find((item) => item.courseId === courseId)
            : null;
          const existing = (student.courseFees || []).find((item) => item.courseId === courseId);
          const course = db.courses.find((item) => item.id === courseId);
          const fee = money(course?.defaultFee ?? 0);
          return {
            courseId,
            fee,
            discount: Math.min(fee, money(submitted?.discount ?? existing?.discount ?? 0)),
          };
        });
      }
      if (input.batchEnrollments !== undefined) {
        try {
          student.batchEnrollments = validateBatchEnrollments(student.courseIds || [], input.batchEnrollments, student.id);
        } catch (error) {
          sendJson(res, 400, { error: error.message });
          return;
        }
      }
      if (input.email !== undefined) {
        const email = String(input.email || '').trim().toLowerCase();
        if (email && db.users.some((item) => item.id !== student.id && String(item.email || '').toLowerCase() === email)) {
          sendJson(res, 409, { error: 'Email already exists' });
          return;
        }
        student.email = email;
      }
      if (input.reference !== undefined || input.referenceName !== undefined || input.referenceCommissionAmount !== undefined) {
        student.reference = {
          type: String(input.referenceType || input.reference?.type || student.reference?.type || '').trim(),
          name: String(input.referenceName || input.reference?.name || student.reference?.name || '').trim(),
          mobile: String(input.referenceMobile || input.reference?.mobile || student.reference?.mobile || '').trim(),
          commissionAmount: money(input.referenceCommissionAmount ?? input.reference?.commissionAmount ?? student.reference?.commissionAmount ?? 0),
          note: String(input.referenceNote || input.reference?.note || student.reference?.note || '').trim(),
        };
      }
      if (input.status !== undefined) student.status = input.status === 'inactive' ? 'inactive' : 'active';
      if (input.password) student.passwordHash = hashPassword(String(input.password));
      logActivity(admin.id, `Updated student ${student.email}`);
      await saveDb();
      sendJson(res, 200, { student: publicUser(student) });
      return;
    }

    if (req.method === 'DELETE' && url.pathname.startsWith('/api/students/')) {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const studentId = url.pathname.split('/').pop();
      const student = db.users.find((user) => user.id === studentId && user.role === 'student');
      if (!student) {
        sendJson(res, 404, { error: 'Student not found' });
        return;
      }
      student.status = 'inactive';
      logActivity(admin.id, `Deactivated student ${student.email}`);
      await saveDb();
      sendJson(res, 200, { student: publicUser(student) });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/teachers') {
      const staff = requireMenuAccess(req, res, 'teachers');
      if (!staff) return;
      const input = await bodyJson(req);
      const name = String(input.name || '').trim();
      const mobile = String(input.mobile || '').trim();
      if (!name || !mobile) {
        sendJson(res, 400, { error: 'Teacher name and mobile are required' });
        return;
      }
      if (db.teachers.some((teacher) => teacher.mobile === mobile)) {
        sendJson(res, 409, { error: 'Teacher mobile already exists' });
        return;
      }
      const teacher = {
        id: randomUUID(),
        name,
        mobile,
        email: String(input.email || '').trim(),
        subject: String(input.subject || '').trim(),
        courseIds: Array.isArray(input.courseIds) ? input.courseIds.filter((id) => db.courses.some((course) => course.id === id)) : [],
        batchIds: Array.isArray(input.batchIds) ? input.batchIds.filter((id) => db.batches.some((batch) => batch.id === id)) : [],
        rate: money(input.rate || 0),
        note: String(input.note || '').trim(),
        status: 'active',
        createdAt: now(),
        updatedAt: now(),
      };
      db.teachers.push(teacher);
      logActivity(staff.id, `Created teacher ${teacher.name}`);
      await saveDb();
      sendJson(res, 201, { teacher: publicTeacher(teacher) });
      return;
    }

    if (req.method === 'PATCH' && url.pathname.startsWith('/api/teachers/')) {
      const staff = requireMenuAccess(req, res, 'teachers');
      if (!staff) return;
      const teacher = db.teachers.find((item) => item.id === url.pathname.split('/').pop());
      if (!teacher) {
        sendJson(res, 404, { error: 'Teacher not found' });
        return;
      }
      const input = await bodyJson(req);
      if (input.name !== undefined) teacher.name = String(input.name || '').trim() || teacher.name;
      if (input.mobile !== undefined) {
        const mobile = String(input.mobile || '').trim();
        if (!mobile) {
          sendJson(res, 400, { error: 'Teacher mobile is required' });
          return;
        }
        if (db.teachers.some((item) => item.id !== teacher.id && item.mobile === mobile)) {
          sendJson(res, 409, { error: 'Teacher mobile already exists' });
          return;
        }
        teacher.mobile = mobile;
      }
      if (input.email !== undefined) teacher.email = String(input.email || '').trim();
      if (input.subject !== undefined) teacher.subject = String(input.subject || '').trim();
      if (input.courseIds !== undefined) teacher.courseIds = Array.isArray(input.courseIds) ? input.courseIds.filter((id) => db.courses.some((course) => course.id === id)) : [];
      if (input.batchIds !== undefined) teacher.batchIds = Array.isArray(input.batchIds) ? input.batchIds.filter((id) => db.batches.some((batch) => batch.id === id)) : [];
      if (input.rate !== undefined) teacher.rate = money(input.rate || 0);
      if (input.note !== undefined) teacher.note = String(input.note || '').trim();
      if (input.status !== undefined) teacher.status = ['active', 'inactive'].includes(input.status) ? input.status : teacher.status;
      teacher.updatedAt = now();
      logActivity(staff.id, `Updated teacher ${teacher.name}`);
      await saveDb();
      sendJson(res, 200, { teacher: publicTeacher(teacher) });
      return;
    }

    if (req.method === 'DELETE' && url.pathname.startsWith('/api/teachers/')) {
      const staff = requireMenuAccess(req, res, 'teachers');
      if (!staff) return;
      const teacher = db.teachers.find((item) => item.id === url.pathname.split('/').pop());
      if (!teacher) {
        sendJson(res, 404, { error: 'Teacher not found' });
        return;
      }
      teacher.status = 'inactive';
      teacher.updatedAt = now();
      await saveDb();
      sendJson(res, 200, { teacher: publicTeacher(teacher) });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/employees') {
      const staff = requireMenuAccess(req, res, 'employees');
      if (!staff) return;
      const input = await bodyJson(req);
      const name = String(input.name || '').trim();
      const mobile = String(input.mobile || '').trim();
      if (!name || !mobile) {
        sendJson(res, 400, { error: 'Employee name and mobile are required' });
        return;
      }
      if (db.employees.some((employee) => employee.mobile === mobile)) {
        sendJson(res, 409, { error: 'Employee mobile already exists' });
        return;
      }
      const employee = {
        id: randomUUID(),
        name,
        mobile,
        email: String(input.email || '').trim(),
        designation: String(input.designation || '').trim(),
        salary: money(input.salary || 0),
        note: String(input.note || '').trim(),
        status: 'active',
        createdAt: now(),
        updatedAt: now(),
      };
      db.employees.push(employee);
      logActivity(staff.id, `Created employee ${employee.name}`);
      await saveDb();
      sendJson(res, 201, { employee: publicEmployee(employee) });
      return;
    }

    if (req.method === 'PATCH' && url.pathname.startsWith('/api/employees/')) {
      const staff = requireMenuAccess(req, res, 'employees');
      if (!staff) return;
      const employee = db.employees.find((item) => item.id === url.pathname.split('/').pop());
      if (!employee) {
        sendJson(res, 404, { error: 'Employee not found' });
        return;
      }
      const input = await bodyJson(req);
      if (input.name !== undefined) employee.name = String(input.name || '').trim() || employee.name;
      if (input.mobile !== undefined) {
        const mobile = String(input.mobile || '').trim();
        if (!mobile) {
          sendJson(res, 400, { error: 'Employee mobile is required' });
          return;
        }
        if (db.employees.some((item) => item.id !== employee.id && item.mobile === mobile)) {
          sendJson(res, 409, { error: 'Employee mobile already exists' });
          return;
        }
        employee.mobile = mobile;
      }
      if (input.email !== undefined) employee.email = String(input.email || '').trim();
      if (input.designation !== undefined) employee.designation = String(input.designation || '').trim();
      if (input.salary !== undefined) employee.salary = money(input.salary || 0);
      if (input.note !== undefined) employee.note = String(input.note || '').trim();
      if (input.status !== undefined) employee.status = ['active', 'inactive'].includes(input.status) ? input.status : employee.status;
      employee.updatedAt = now();
      logActivity(staff.id, `Updated employee ${employee.name}`);
      await saveDb();
      sendJson(res, 200, { employee: publicEmployee(employee) });
      return;
    }

    if (req.method === 'DELETE' && url.pathname.startsWith('/api/employees/')) {
      const staff = requireMenuAccess(req, res, 'employees');
      if (!staff) return;
      const employee = db.employees.find((item) => item.id === url.pathname.split('/').pop());
      if (!employee) {
        sendJson(res, 404, { error: 'Employee not found' });
        return;
      }
      employee.status = 'inactive';
      employee.updatedAt = now();
      await saveDb();
      sendJson(res, 200, { employee: publicEmployee(employee) });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/payments') {
      const staff = requireMenuAccess(req, res, 'studentPayments');
      if (!staff) return;
      const input = await bodyJson(req);
      const student = db.users.find((item) => item.id === input.studentId && item.role === 'student');
      const course = db.courses.find((item) => item.id === input.courseId);
      if (!student || !course || !(student.courseIds || []).includes(course.id)) {
        sendJson(res, 400, { error: 'Select an enrolled student and course' });
        return;
      }
      const amount = money(input.amount);
      const finance = studentFinancialSummary(student.id);
      const enrollment = finance?.enrollments.find((item) => item.courseId === course.id);
      if (!amount) {
        sendJson(res, 400, { error: 'Payment amount must be greater than zero' });
        return;
      }
      if (!enrollment || amount > enrollment.due) {
        sendJson(res, 400, { error: `Payment cannot exceed the current due of ${enrollment?.due || 0}` });
        return;
      }
      const method = ['cash', 'bank', 'mobile_banking'].includes(input.method) ? input.method : 'cash';
      const payment = {
        id: randomUUID(),
        sessionId: db.activeSessionId,
        receiptNumber: nextReceiptNumber(),
        studentId: student.id,
        courseId: course.id,
        amount,
        method,
        reference: String(input.reference || '').trim(),
        note: String(input.note || '').trim(),
        paidAt: String(input.paidAt || '').trim() || now(),
        status: 'completed',
        receivedBy: staff.id,
        receivedByName: staff.name,
        createdAt: now(),
        updatedAt: now(),
      };
      db.payments.push(payment);
      logActivity(staff.id, `Received ${amount} from ${student.name} (${payment.receiptNumber})`);
      const updatedFinance = studentFinancialSummary(student.id);
      await sendSms(student.studentMobile || student.mobile, renderTemplate(smsTemplate('studentPayment'), {
        name: student.name,
        roll: student.rollNumber || '',
        mobile: student.studentMobile || student.mobile || '',
        amount,
        course: course.name,
        receipt: payment.receiptNumber,
        due: updatedFinance?.enrollments.find((item) => item.courseId === course.id)?.due || 0,
      }), 'student_payment', { paymentId: payment.id, studentId: student.id });
      await saveDb();
      sendJson(res, 201, { payment: publicPayment(payment), finance: updatedFinance });
      return;
    }

    if (req.method === 'PATCH' && url.pathname.startsWith('/api/payments/')) {
      const admin = requireSuperAdmin(req, res);
      if (!admin) return;
      const paymentId = url.pathname.split('/').pop();
      const payment = db.payments.find((item) => item.id === paymentId);
      if (!payment) {
        sendJson(res, 404, { error: 'Payment not found' });
        return;
      }
      const input = await bodyJson(req);
      if (input.status === 'cancelled') {
        if (payment.status === 'cancelled') {
          sendJson(res, 409, { error: 'Payment is already cancelled' });
          return;
        }
        payment.status = 'cancelled';
        payment.cancelReason = String(input.cancelReason || '').trim() || 'Cancelled by admin';
        payment.cancelledAt = now();
        payment.cancelledBy = admin.id;
      } else {
        if (input.amount !== undefined) {
          const amount = money(input.amount);
          const finance = studentFinancialSummary(payment.studentId);
          const enrollment = finance?.enrollments.find((item) => item.courseId === payment.courseId);
          const maximum = money(Number(enrollment?.due || 0) + Number(payment.amount || 0));
          if (!amount || amount > maximum) {
            sendJson(res, 400, { error: `Payment amount must be between 0.01 and ${maximum}` });
            return;
          }
          payment.amount = amount;
        }
        if (input.method !== undefined) {
          payment.method = ['cash', 'bank', 'mobile_banking'].includes(input.method) ? input.method : payment.method;
        }
        if (input.reference !== undefined) payment.reference = String(input.reference || '').trim();
        if (input.note !== undefined) payment.note = String(input.note || '').trim();
        if (input.paidAt !== undefined) payment.paidAt = String(input.paidAt || '').trim() || payment.paidAt;
      }
      payment.updatedAt = now();
      logActivity(admin.id, `${payment.status === 'cancelled' ? 'Cancelled' : 'Updated'} payment ${payment.receiptNumber}`);
      await saveDb();
      sendJson(res, 200, { payment: publicPayment(payment), finance: studentFinancialSummary(payment.studentId) });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/teacher-payments') {
      const staff = requireMenuAccess(req, res, 'teacherPayments');
      if (!staff) return;
      const input = await bodyJson(req);
      const teacher = db.teachers.find((item) => item.id === input.teacherId && item.status === 'active');
      if (!teacher) {
        sendJson(res, 400, { error: 'Select an active teacher profile' });
        return;
      }
      const classEntries = (Array.isArray(input.classEntries) ? input.classEntries : [])
        .map((entry) => ({
          date: String(entry.date || '').trim(),
          subject: String(entry.subject || input.subject || '').trim(),
          classes: Math.max(0, Number(entry.classes || 0)),
          rate: money(entry.rate || input.rate || 0),
          note: String(entry.note || '').trim(),
        }))
        .filter((entry) => entry.date && entry.subject && entry.classes > 0 && entry.rate > 0);
      if (!classEntries.length) {
        sendJson(res, 400, { error: 'Add at least one valid class date entry' });
        return;
      }
      const draft = {
        id: randomUUID(),
        sessionId: db.activeSessionId,
        teacherId: teacher.id,
        courseId: String(input.courseId || '').trim(),
        batchId: String(input.batchId || '').trim(),
        month: String(input.month || '').trim(),
        subject: String(input.subject || classEntries[0]?.subject || '').trim(),
        rate: money(input.rate || classEntries[0]?.rate || 0),
        classEntries,
        paidAmount: money(input.paidAmount || 0),
        method: ['cash', 'bank', 'mobile_banking'].includes(input.method) ? input.method : 'cash',
        reference: String(input.reference || '').trim(),
        note: String(input.note || '').trim(),
        paidAt: String(input.paidAt || '').trim() || now(),
        status: 'active',
        createdBy: staff.id,
        createdAt: now(),
        updatedAt: now(),
      };
      const summary = teacherPaymentSummary(draft);
      if (draft.paidAmount > summary.payable) {
        sendJson(res, 400, { error: `Paid amount cannot exceed payable ${summary.payable}` });
        return;
      }
      db.teacherPayments.push(draft);
      logActivity(staff.id, `Recorded teacher payment for ${teacher.name}`);
      if (draft.paidAmount > 0) {
        const summary = teacherPaymentSummary(draft);
        await sendSms(teacher.mobile, renderTemplate(smsTemplate('teacherPayment'), {
          name: teacher.name,
          mobile: teacher.mobile || '',
          paid: draft.paidAmount,
          payable: summary.payable,
          due: summary.due,
          month: draft.month,
          subject: draft.subject,
        }), 'teacher_payment', { paymentId: draft.id, teacherId: teacher.id });
      }
      await saveDb();
      sendJson(res, 201, { teacherPayment: publicTeacherPayment(draft) });
      return;
    }

    if (req.method === 'PATCH' && url.pathname.startsWith('/api/teacher-payments/')) {
      const staff = requireMenuAccess(req, res, 'teacherPayments');
      if (!staff) return;
      const paymentId = url.pathname.split('/').pop();
      const payment = db.teacherPayments.find((item) => item.id === paymentId);
      if (!payment) {
        sendJson(res, 404, { error: 'Teacher payment not found' });
        return;
      }
      const input = await bodyJson(req);
      if (input.teacherId !== undefined) {
        const teacher = db.teachers.find((item) => item.id === input.teacherId && item.status === 'active');
        if (!teacher) {
          sendJson(res, 400, { error: 'Select an active teacher profile' });
          return;
        }
        payment.teacherId = teacher.id;
      }
      if (input.courseId !== undefined) payment.courseId = String(input.courseId || '').trim();
      if (input.batchId !== undefined) payment.batchId = String(input.batchId || '').trim();
      if (input.month !== undefined) payment.month = String(input.month || '').trim();
      if (input.subject !== undefined) payment.subject = String(input.subject || '').trim();
      if (input.rate !== undefined) payment.rate = money(input.rate || 0);
      if (input.classEntries !== undefined) {
        payment.classEntries = (Array.isArray(input.classEntries) ? input.classEntries : [])
          .map((entry) => ({
            date: String(entry.date || '').trim(),
            subject: String(entry.subject || input.subject || payment.subject || '').trim(),
            classes: Math.max(0, Number(entry.classes || 0)),
            rate: money(entry.rate || input.rate || payment.rate || 0),
            note: String(entry.note || '').trim(),
          }))
          .filter((entry) => entry.date && entry.subject && entry.classes > 0 && entry.rate > 0);
      }
      if (input.paidAmount !== undefined) payment.paidAmount = money(input.paidAmount || 0);
      if (input.method !== undefined) payment.method = ['cash', 'bank', 'mobile_banking'].includes(input.method) ? input.method : payment.method;
      if (input.reference !== undefined) payment.reference = String(input.reference || '').trim();
      if (input.note !== undefined) payment.note = String(input.note || '').trim();
      if (input.paidAt !== undefined) payment.paidAt = String(input.paidAt || '').trim() || payment.paidAt;
      const summary = teacherPaymentSummary(payment);
      if (!payment.classEntries.length || payment.paidAmount > summary.payable) {
        sendJson(res, 400, { error: 'Teacher payment has invalid class entries or overpayment' });
        return;
      }
      payment.updatedAt = now();
      logActivity(staff.id, `Updated teacher payment for ${publicTeacherPayment(payment).teacherName}`);
      await saveDb();
      sendJson(res, 200, { teacherPayment: publicTeacherPayment(payment) });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/employee-payments') {
      const staff = requireMenuAccess(req, res, 'employeePayments');
      if (!staff) return;
      const input = await bodyJson(req);
      const employee = db.employees.find((item) => item.id === input.employeeId && item.status === 'active');
      if (!employee) {
        sendJson(res, 400, { error: 'Select an active employee profile' });
        return;
      }
      const payment = {
        id: randomUUID(),
        sessionId: db.activeSessionId,
        employeeId: employee.id,
        month: String(input.month || '').trim(),
        salaryAmount: money(input.salaryAmount || employee.salary || 0),
        allowance: money(input.allowance || 0),
        bonus: money(input.bonus || 0),
        deduction: money(input.deduction || 0),
        advance: money(input.advance || 0),
        paidAmount: money(input.paidAmount || 0),
        method: ['cash', 'bank', 'mobile_banking'].includes(input.method) ? input.method : 'cash',
        reference: String(input.reference || '').trim(),
        note: String(input.note || '').trim(),
        paidAt: String(input.paidAt || '').trim() || now(),
        status: 'active',
        createdBy: staff.id,
        createdAt: now(),
        updatedAt: now(),
      };
      const summary = employeePaymentSummary(payment);
      if (!payment.month || summary.payable < 0 || payment.paidAmount > summary.payable) {
        sendJson(res, 400, { error: 'Employee payment month or amount is invalid' });
        return;
      }
      db.employeePayments.push(payment);
      logActivity(staff.id, `Recorded employee payment for ${employee.name}`);
      if (payment.paidAmount > 0) {
        const summary = employeePaymentSummary(payment);
        await sendSms(employee.mobile, renderTemplate(smsTemplate('employeePayment'), {
          name: employee.name,
          mobile: employee.mobile || '',
          designation: employee.designation || '',
          paid: payment.paidAmount,
          payable: summary.payable,
          due: summary.due,
          month: payment.month,
        }), 'employee_payment', { paymentId: payment.id, employeeId: employee.id });
      }
      await saveDb();
      sendJson(res, 201, { employeePayment: publicEmployeePayment(payment) });
      return;
    }

    if (req.method === 'PATCH' && url.pathname.startsWith('/api/employee-payments/')) {
      const staff = requireMenuAccess(req, res, 'employeePayments');
      if (!staff) return;
      const payment = db.employeePayments.find((item) => item.id === url.pathname.split('/').pop());
      if (!payment) {
        sendJson(res, 404, { error: 'Employee payment not found' });
        return;
      }
      const input = await bodyJson(req);
      if (input.employeeId !== undefined) {
        const employee = db.employees.find((item) => item.id === input.employeeId && item.status === 'active');
        if (!employee) {
          sendJson(res, 400, { error: 'Select an active employee profile' });
          return;
        }
        payment.employeeId = employee.id;
      }
      if (input.month !== undefined) payment.month = String(input.month || '').trim();
      for (const key of ['salaryAmount', 'allowance', 'bonus', 'deduction', 'advance', 'paidAmount']) {
        if (input[key] !== undefined) payment[key] = money(input[key] || 0);
      }
      if (input.method !== undefined) payment.method = ['cash', 'bank', 'mobile_banking'].includes(input.method) ? input.method : payment.method;
      if (input.reference !== undefined) payment.reference = String(input.reference || '').trim();
      if (input.note !== undefined) payment.note = String(input.note || '').trim();
      if (input.paidAt !== undefined) payment.paidAt = String(input.paidAt || '').trim() || payment.paidAt;
      const summary = employeePaymentSummary(payment);
      if (!payment.month || payment.paidAmount > summary.payable) {
        sendJson(res, 400, { error: 'Employee payment month or amount is invalid' });
        return;
      }
      payment.updatedAt = now();
      logActivity(staff.id, `Updated employee payment for ${publicEmployeePayment(payment).employeeName}`);
      await saveDb();
      sendJson(res, 200, { employeePayment: publicEmployeePayment(payment) });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/reference-payments') {
      const staff = requireMenuAccess(req, res, 'referencePayments');
      if (!staff) return;
      const input = await bodyJson(req);
      const student = db.users.find((item) => item.id === input.studentId && item.role === 'student');
      const commissionAmount = money(input.commissionAmount || student?.reference?.commissionAmount || 0);
      if (!student || !commissionAmount) {
        sendJson(res, 400, { error: 'Select a student and commission amount' });
        return;
      }
      const payment = {
        id: randomUUID(),
        sessionId: db.activeSessionId,
        studentId: student.id,
        referrerType: String(input.referrerType || student.reference?.type || '').trim(),
        referrerName: String(input.referrerName || student.reference?.name || '').trim(),
        referrerMobile: String(input.referrerMobile || student.reference?.mobile || '').trim(),
        shareholderId: String(input.shareholderId || '').trim(),
        commissionAmount,
        paidAmount: money(input.paidAmount || 0),
        method: ['cash', 'bank', 'mobile_banking'].includes(input.method) ? input.method : 'cash',
        reference: String(input.reference || '').trim(),
        note: String(input.note || '').trim(),
        paidAt: String(input.paidAt || '').trim() || now(),
        status: 'active',
        createdBy: staff.id,
        createdAt: now(),
        updatedAt: now(),
      };
      if (payment.paidAmount > payment.commissionAmount) {
        sendJson(res, 400, { error: 'Paid amount cannot exceed commission' });
        return;
      }
      db.referencePayments.push(payment);
      if (payment.shareholderId && db.shareholders.some((item) => item.id === payment.shareholderId)) {
        const ledgerAmount = money(payment.paidAmount || payment.commissionAmount || 0);
        if (ledgerAmount > 0) {
          db.shareholderLedger.push(createShareholderLedgerEntry({
            shareholderId: payment.shareholderId,
            type: 'reference_commission',
            title: `Reference commission - ${student.name}`,
            rawAmount: ledgerAmount,
            method: payment.method,
            reference: payment.reference,
            note: payment.note,
            entryDate: payment.paidAt,
          }, staff, { sourceType: 'reference_payment', sourceId: payment.id }));
        }
      }
      if (payment.paidAmount > 0) {
        const publicPayment = publicReferencePayment(payment);
        await sendSms(payment.referrerMobile, renderTemplate(smsTemplate('referencePayment'), {
          name: payment.referrerName,
          mobile: payment.referrerMobile,
          student: student.name,
          roll: student.rollNumber || '',
          paid: payment.paidAmount,
          commission: payment.commissionAmount,
          due: publicPayment.due,
        }), 'reference_payment', { paymentId: payment.id, studentId: student.id });
      }
      await saveDb();
      sendJson(res, 201, { referencePayment: publicReferencePayment(payment) });
      return;
    }

    if (req.method === 'PATCH' && url.pathname.startsWith('/api/reference-payments/')) {
      const staff = requireMenuAccess(req, res, 'referencePayments');
      if (!staff) return;
      const payment = db.referencePayments.find((item) => item.id === url.pathname.split('/').pop());
      if (!payment) {
        sendJson(res, 404, { error: 'Reference payment not found' });
        return;
      }
      const input = await bodyJson(req);
      for (const key of ['referrerType', 'referrerName', 'referrerMobile', 'reference', 'note']) {
        if (input[key] !== undefined) payment[key] = String(input[key] || '').trim();
      }
      if (input.shareholderId !== undefined) payment.shareholderId = String(input.shareholderId || '').trim();
      if (input.commissionAmount !== undefined) payment.commissionAmount = money(input.commissionAmount || 0);
      if (input.paidAmount !== undefined) payment.paidAmount = money(input.paidAmount || 0);
      if (input.method !== undefined) payment.method = ['cash', 'bank', 'mobile_banking'].includes(input.method) ? input.method : payment.method;
      if (input.paidAt !== undefined) payment.paidAt = String(input.paidAt || '').trim() || payment.paidAt;
      payment.updatedAt = now();
      await saveDb();
      sendJson(res, 200, { referencePayment: publicReferencePayment(payment) });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/other-payments') {
      const staff = requireMenuAccess(req, res, 'otherPayments');
      if (!staff) return;
      const input = await bodyJson(req);
      const amount = money(input.amount || 0);
      if (!amount) {
        sendJson(res, 400, { error: 'Expense amount is required' });
        return;
      }
      const payment = {
        id: randomUUID(),
        sessionId: db.activeSessionId,
        category: String(input.category || 'Others').trim(),
        title: String(input.title || '').trim(),
        amount,
        method: ['cash', 'bank', 'mobile_banking'].includes(input.method) ? input.method : 'cash',
        reference: String(input.reference || '').trim(),
        note: String(input.note || '').trim(),
        paidAt: String(input.paidAt || '').trim() || now(),
        createdBy: staff.id,
        createdAt: now(),
        updatedAt: now(),
      };
      db.otherPayments.push(payment);
      await saveDb();
      sendJson(res, 201, { otherPayment: publicOtherPayment(payment) });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/shareholders') {
      const staff = requireMenuAccess(req, res, 'payments');
      if (!staff) return;
      const input = await bodyJson(req);
      const name = String(input.name || '').trim();
      const sharePercentage = money(input.sharePercentage || 0);
      if (!name) {
        sendJson(res, 400, { error: 'Shareholder name is required' });
        return;
      }
      const existingShare = db.shareholders.filter((item) => (item.status || 'active') === 'active').reduce((sum, item) => sum + Number(item.sharePercentage || 0), 0);
      if (existingShare + sharePercentage > 100) {
        sendJson(res, 400, { error: 'Active shareholder percentage cannot exceed 100%' });
        return;
      }
      const shareholder = {
        id: randomUUID(),
        name,
        mobile: String(input.mobile || '').trim(),
        email: String(input.email || '').trim(),
        sharePercentage,
        openingBalance: signedMoney(input.openingBalance || 0),
        note: String(input.note || '').trim(),
        status: input.status === 'inactive' ? 'inactive' : 'active',
        createdBy: staff.id,
        createdAt: now(),
        updatedAt: now(),
      };
      db.shareholders.push(shareholder);
      if (shareholder.openingBalance) {
        db.shareholderLedger.push(createShareholderLedgerEntry({
          shareholderId: shareholder.id,
          type: Number(shareholder.openingBalance) >= 0 ? 'opening_balance' : 'adjustment_negative',
          title: 'Opening balance',
          rawAmount: Math.abs(Number(shareholder.openingBalance || 0)),
          entryDate: now(),
          note: shareholder.note,
        }, staff, { sourceType: 'shareholder_opening', sourceId: shareholder.id }));
      }
      await saveDb();
      sendJson(res, 201, { shareholder: publicShareholder(shareholder) });
      return;
    }

    if (req.method === 'PATCH' && url.pathname.startsWith('/api/shareholders/')) {
      const staff = requireMenuAccess(req, res, 'payments');
      if (!staff) return;
      const shareholder = db.shareholders.find((item) => item.id === url.pathname.split('/').pop());
      if (!shareholder) {
        sendJson(res, 404, { error: 'Shareholder not found' });
        return;
      }
      const input = await bodyJson(req);
      if (input.name !== undefined) shareholder.name = String(input.name || '').trim() || shareholder.name;
      if (input.mobile !== undefined) shareholder.mobile = String(input.mobile || '').trim();
      if (input.email !== undefined) shareholder.email = String(input.email || '').trim();
      if (input.sharePercentage !== undefined) {
        const nextShare = money(input.sharePercentage || 0);
        const otherShare = db.shareholders.filter((item) => item.id !== shareholder.id && (item.status || 'active') === 'active').reduce((sum, item) => sum + Number(item.sharePercentage || 0), 0);
        if ((input.status || shareholder.status) !== 'inactive' && otherShare + nextShare > 100) {
          sendJson(res, 400, { error: 'Active shareholder percentage cannot exceed 100%' });
          return;
        }
        shareholder.sharePercentage = nextShare;
      }
      if (input.note !== undefined) shareholder.note = String(input.note || '').trim();
      if (input.status !== undefined) shareholder.status = input.status === 'inactive' ? 'inactive' : 'active';
      shareholder.updatedAt = now();
      await saveDb();
      sendJson(res, 200, { shareholder: publicShareholder(shareholder) });
      return;
    }

    if (req.method === 'DELETE' && url.pathname.startsWith('/api/shareholders/')) {
      const staff = requireMenuAccess(req, res, 'payments');
      if (!staff) return;
      const id = url.pathname.split('/').pop();
      const shareholder = db.shareholders.find((item) => item.id === id);
      if (!shareholder) {
        sendJson(res, 404, { error: 'Shareholder not found' });
        return;
      }
      if (db.shareholderLedger.some((entry) => entry.shareholderId === id)) {
        shareholder.status = 'inactive';
        shareholder.updatedAt = now();
      } else {
        db.shareholders = db.shareholders.filter((item) => item.id !== id);
      }
      logActivity(staff.id, `Deleted/deactivated shareholder ${shareholder.name}`);
      await saveDb();
      sendJson(res, 200, { ok: true });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/shareholder-ledger') {
      const staff = requireMenuAccess(req, res, 'payments');
      if (!staff) return;
      const input = await bodyJson(req);
      const shareholder = db.shareholders.find((item) => item.id === input.shareholderId);
      const amount = money(input.amount || input.rawAmount || 0);
      if (!shareholder || !amount) {
        sendJson(res, 400, { error: 'Select shareholder and enter amount' });
        return;
      }
      const entry = createShareholderLedgerEntry({ ...input, rawAmount: amount }, staff);
      db.shareholderLedger.push(entry);
      await saveDb();
      sendJson(res, 201, { shareholderLedger: publicShareholderLedger(entry) });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/shareholder-profit-share') {
      const staff = requireMenuAccess(req, res, 'payments');
      if (!staff) return;
      const sessionId = db.activeSessionId;
      if (db.shareholderLedger.some((item) => item.sessionId === sessionId && item.type === 'session_profit_share' && item.sourceType === 'session_profit')) {
        sendJson(res, 400, { error: 'Session profit share was already generated for this session' });
        return;
      }
      const activeShareholders = db.shareholders.filter((item) => (item.status || 'active') === 'active' && Number(item.sharePercentage || 0) > 0);
      const summary = sessionNetProfit(sessionId);
      if (!activeShareholders.length || summary.profit <= 0) {
        sendJson(res, 400, { error: 'No positive profit or active shareholder share found' });
        return;
      }
      const entries = activeShareholders.map((shareholder) => createShareholderLedgerEntry({
        shareholderId: shareholder.id,
        type: 'session_profit_share',
        title: `Session profit share (${shareholder.sharePercentage}%)`,
        rawAmount: money(Number(summary.profit) * Number(shareholder.sharePercentage || 0) / 100),
        entryDate: now(),
        note: `Income ${summary.income}, Expense ${summary.expense}`,
      }, staff, { sourceType: 'session_profit', sourceId: sessionId }));
      db.shareholderLedger.push(...entries.filter((entry) => Number(entry.rawAmount || 0) > 0));
      await saveDb();
      sendJson(res, 201, { entries: entries.map(publicShareholderLedger), summary });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/recorded-classes') {
      const staff = requireMenuAccess(req, res, 'recordedClasses');
      if (!staff) return;
      const input = await bodyJson(req);
      const title = String(input.title || '').trim();
      const videoUrl = String(input.videoUrl || '').trim();
      if (!title || !videoUrl) {
        sendJson(res, 400, { error: 'Title and video URL are required' });
        return;
      }
      const item = {
        id: randomUUID(),
        sessionId: db.activeSessionId,
        title,
        subject: String(input.subject || '').trim(),
        courseIds: Array.isArray(input.courseIds) ? input.courseIds.filter((id) => db.courses.some((course) => course.id === id)) : [],
        batchIds: Array.isArray(input.batchIds) ? input.batchIds.filter((id) => db.batches.some((batch) => batch.id === id)) : [],
        videoUrl,
        visibility: input.visibility === 'all' ? 'all' : 'assigned',
        status: input.status === 'draft' ? 'draft' : 'published',
        note: String(input.note || '').trim(),
        createdBy: staff.id,
        createdAt: now(),
        updatedAt: now(),
      };
      db.recordedClasses.push(item);
      await saveDb();
      sendJson(res, 201, { recordedClass: publicRecordedClass(item) });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/manual-results') {
      const staff = requireMenuAccess(req, res, 'results');
      if (!staff) return;
      const input = await bodyJson(req);
      const examName = String(input.examName || '').trim();
      const parseCsvLine = (line) => {
        const cells = [];
        let current = '';
        let quoted = false;
        for (let i = 0; i < line.length; i += 1) {
          const char = line[i];
          if (char === '"' && line[i + 1] === '"') {
            current += '"';
            i += 1;
          } else if (char === '"') {
            quoted = !quoted;
          } else if (char === ',' && !quoted) {
            cells.push(current.trim());
            current = '';
          } else {
            current += char;
          }
        }
        cells.push(current.trim());
        return cells;
      };
      const rows = Array.isArray(input.rows)
        ? input.rows
        : String(input.csv || '').split(/\r?\n/).map(parseCsvLine);
      if (!examName || !rows.length) {
        sendJson(res, 400, { error: 'Exam name and result rows are required' });
        return;
      }
      const created = [];
      const skipped = [];
      const headers = (rows[0] || []).map((cell) => String(cell || '').trim().toLowerCase().replace(/[\s_-]+/g, ''));
      const hasHeader = headers.some((cell) => ['roll', 'rool', 'rollnumber', 'mark', 'marks', 'negativemark', 'negative'].includes(cell));
      const headerIndex = (names) => headers.findIndex((cell) => names.includes(cell));
      const valueByHeader = (item, names) => {
        const index = headerIndex(names);
        return index >= 0 ? item[index] : '';
      };
      for (const row of (hasHeader ? rows.slice(1) : rows)) {
        const item = Array.isArray(row) ? row : [];
        if (!item.length || item.every((cell) => !cell)) continue;
        let [mobile, name, roll, batch, college, subject, mark, negativeMark, position, highest] = item;
        if (hasHeader) {
          mobile = valueByHeader(item, ['mobile', 'studentmobile', 'phone']);
          name = valueByHeader(item, ['name', 'studentname']);
          roll = valueByHeader(item, ['roll', 'rool', 'rollnumber', 'reg', 'registration', 'registrationnumber']);
          batch = valueByHeader(item, ['batch', 'batchname']);
          college = valueByHeader(item, ['college']);
          subject = valueByHeader(item, ['subject']) || input.subject || '';
          mark = valueByHeader(item, ['mark', 'marks', 'score']);
          negativeMark = valueByHeader(item, ['negativemark', 'negative', 'neg', 'minusmark']);
          position = valueByHeader(item, ['position', 'rank']);
          highest = valueByHeader(item, ['highest', 'highestmark']);
        }
        if (!hasHeader && item.length <= 5) {
          [roll, mark, negativeMark = '', position = '', highest = ''] = item;
          mobile = '';
          name = '';
          batch = '';
          college = '';
          subject = input.subject || '';
        }
        if (String(mobile || roll || '').toLowerCase() === 'mobile' || String(roll || '').toLowerCase() === 'roll') continue;
        const student = db.users.find((user) => user.role === 'student' && (
          String(user.studentMobile || '') === String(mobile || '')
          || String(user.mobile || '') === String(mobile || '')
          || String(user.rollNumber || '').toLowerCase() === String(roll || '').toLowerCase()
          || String(user.registrationNumber || '').toLowerCase() === String(roll || '').toLowerCase()
        ));
        if (!student && roll) {
          skipped.push(String(roll).trim());
          continue;
        }
        const enrollment = student?.batchEnrollments?.find((entry) => !input.batchId || entry.batchId === input.batchId);
        const batchInfo = db.batches.find((entry) => entry.id === (input.batchId || enrollment?.batchId));
        const result = {
          id: randomUUID(),
          sessionId: db.activeSessionId,
          examName,
          courseId: String(input.courseId || enrollment?.courseId || '').trim(),
          batchId: String(input.batchId || enrollment?.batchId || '').trim(),
          studentId: student?.id || '',
          mobile: String(mobile || student?.studentMobile || '').trim(),
          name: String(name || student?.name || '').trim(),
          roll: String(roll || student?.rollNumber || '').trim(),
          batch: String(batch || batchInfo?.name || '').trim(),
          college: String(college || student?.college || '').trim(),
          subject: String(subject || input.subject || '').trim(),
          mark: money(mark || 0),
          negativeMark: negativeMark === undefined || negativeMark === '' ? '' : money(negativeMark),
          position: String(position || '').trim(),
          highest: highest === undefined || highest === '' ? '' : money(highest),
          createdBy: staff.id,
          createdByName: staff.name,
          createdAt: now(),
        };
        db.manualResults.push(result);
        created.push(result);
      }
      const group = db.manualResults.filter((item) => (
        item.sessionId === db.activeSessionId
        && item.examName === examName
        && String(item.subject || '') === String(input.subject || created[0]?.subject || '')
      ));
      const highestMark = group.reduce((max, item) => Math.max(max, Number(item.mark || 0)), 0);
      const rankMap = new Map([...group].sort((a, b) => Number(b.mark || 0) - Number(a.mark || 0)).map((item, index) => [item.id, index + 1]));
      for (const item of group) {
        item.highest = highestMark;
        item.position = String(rankMap.get(item.id) || '');
      }
      logActivity(staff.id, `Uploaded ${created.length} manual results for ${examName}`);
      await saveDb();
      sendJson(res, 201, { created, skipped });
      return;
    }

    if (req.method === 'GET' && url.pathname.startsWith('/api/students/') && url.pathname.endsWith('/progress.json')) {
      const staff = requireAdmin(req, res);
      if (!staff) return;
      const studentId = url.pathname.split('/')[3];
      const report = progressReportForStudent(studentId);
      if (!report) {
        sendJson(res, 404, { error: 'Student not found' });
        return;
      }
      sendJson(res, 200, { report });
      return;
    }

    if (req.method === 'PATCH' && url.pathname.startsWith('/api/recorded-classes/')) {
      const staff = requireMenuAccess(req, res, 'recordedClasses');
      if (!staff) return;
      const item = db.recordedClasses.find((entry) => entry.id === url.pathname.split('/').pop());
      if (!item) {
        sendJson(res, 404, { error: 'Recorded class not found' });
        return;
      }
      const input = await bodyJson(req);
      if (input.title !== undefined) item.title = String(input.title || '').trim() || item.title;
      if (input.subject !== undefined) item.subject = String(input.subject || '').trim();
      if (input.videoUrl !== undefined) item.videoUrl = String(input.videoUrl || '').trim() || item.videoUrl;
      if (input.courseIds !== undefined) item.courseIds = Array.isArray(input.courseIds) ? input.courseIds.filter((id) => db.courses.some((course) => course.id === id)) : [];
      if (input.batchIds !== undefined) item.batchIds = Array.isArray(input.batchIds) ? input.batchIds.filter((id) => db.batches.some((batch) => batch.id === id)) : [];
      if (input.visibility !== undefined) item.visibility = input.visibility === 'all' ? 'all' : 'assigned';
      if (input.status !== undefined) item.status = ['published', 'draft', 'inactive'].includes(input.status) ? input.status : item.status;
      if (input.note !== undefined) item.note = String(input.note || '').trim();
      item.updatedAt = now();
      await saveDb();
      sendJson(res, 200, { recordedClass: publicRecordedClass(item) });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/quizzes') {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const input = await bodyJson(req);
      const validated = validateQuizInput(input);
      if (validated.error) {
        sendJson(res, 400, { error: validated.error });
        return;
      }
      const quiz = {
        id: randomUUID(),
        sessionId: db.activeSessionId,
        ...validated.quiz,
        createdAt: now(),
        updatedAt: now(),
      };
      db.quizzes.push(quiz);
      logActivity(admin.id, `Created quiz ${quiz.title}`);
      await saveDb();
      sendJson(res, 201, { quiz });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/quizzes/import') {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const input = await bodyJson(req);
      const parsed = JSON.parse(String(input.json || '{}'));
      const list = Array.isArray(parsed) ? parsed : [parsed];
      const created = [];
      for (const item of list) {
        const validated = validateQuizInput(item);
        if (validated.error) continue;
        const quiz = { id: randomUUID(), ...validated.quiz, createdAt: now(), updatedAt: now() };
        db.quizzes.push(quiz);
        created.push(quiz);
      }
      logActivity(admin.id, `Imported ${created.length} quizzes`);
      await saveDb();
      sendJson(res, 201, { created });
      return;
    }

    if (req.method === 'POST' && /^\/api\/quizzes\/[^/]+\/duplicate$/.test(url.pathname)) {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const quizId = url.pathname.split('/')[3];
      const source = db.quizzes.find((item) => item.id === quizId);
      if (!source) {
        sendJson(res, 404, { error: 'Quiz not found' });
        return;
      }
      const quiz = {
        ...structuredClone(source),
        id: randomUUID(),
        sessionId: db.activeSessionId,
        title: `${source.title} Copy`,
        status: 'draft',
        questions: source.questions.map((question) => ({ ...structuredClone(question), id: randomUUID() })),
        createdAt: now(),
        updatedAt: now(),
      };
      db.quizzes.push(quiz);
      logActivity(admin.id, `Duplicated quiz ${source.title}`);
      await saveDb();
      sendJson(res, 201, { quiz });
      return;
    }

    if (req.method === 'PUT' && url.pathname.startsWith('/api/quizzes/')) {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const quizId = url.pathname.split('/').pop();
      const quiz = db.quizzes.find((item) => item.id === quizId);
      if (!quiz) {
        sendJson(res, 404, { error: 'Quiz not found' });
        return;
      }
      const input = await bodyJson(req);
      const validated = validateQuizInput(input);
      if (validated.error) {
        sendJson(res, 400, { error: validated.error });
        return;
      }
      Object.assign(quiz, validated.quiz, { updatedAt: now() });
      logActivity(admin.id, `Updated quiz ${quiz.title}`);
      await saveDb();
      sendJson(res, 200, { quiz });
      return;
    }

    if (req.method === 'DELETE' && url.pathname.startsWith('/api/quizzes/')) {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const quizId = url.pathname.split('/').pop();
      const before = db.quizzes.length;
      db.quizzes = db.quizzes.filter((quiz) => quiz.id !== quizId);
      if (db.quizzes.length === before) {
        sendJson(res, 404, { error: 'Quiz not found' });
        return;
      }
      logActivity(admin.id, 'Deleted a quiz');
      await saveDb();
      sendJson(res, 200, { ok: true });
      return;
    }

    if (req.method === 'GET' && url.pathname === '/api/question-bank') {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      sendJson(res, 200, { questions: db.questionBank });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/question-bank') {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const input = await bodyJson(req);
      const validated = validateQuizInput({ title: 'Bank Item', questions: [input] }, { requireCourses: false });
      if (validated.error) {
        sendJson(res, 400, { error: validated.error });
        return;
      }
      const question = {
        ...validated.quiz.questions[0],
        id: randomUUID(),
        createdAt: now(),
        updatedAt: now(),
      };
      db.questionBank.push(question);
      logActivity(admin.id, 'Added question bank item');
      await saveDb();
      sendJson(res, 201, { question });
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/question-bank/import') {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const input = await bodyJson(req);
      const rows = String(input.csv || '').split(/\r?\n/).map((line) => line.trim()).filter(Boolean);
      const created = [];
      const skipped = [];
      for (const row of rows) {
        const [typeRaw, text, optionsRaw, correctRaw, category, explanation] = row.split(',').map((part) => String(part || '').trim());
        const type = ['mcq', 'true_false', 'short', 'written'].includes(typeRaw) ? typeRaw : 'mcq';
        const options = optionsRaw ? optionsRaw.split('|').map((item) => item.trim()).filter(Boolean) : [];
        const validated = validateQuizInput({
          title: 'Import',
          questions: [{
            type,
            text,
            options,
            correctIndex: type === 'true_false' ? (correctRaw.toLowerCase() === 'false' ? 1 : 0) : Math.max(0, Number(correctRaw || 1) - 1),
            correctText: correctRaw,
            category,
            explanation,
            points: 1,
          }],
        }, { requireCourses: false });
        if (validated.error) {
          skipped.push(row);
          continue;
        }
        const question = { ...validated.quiz.questions[0], id: randomUUID(), createdAt: now(), updatedAt: now() };
        db.questionBank.push(question);
        created.push(question);
      }
      logActivity(admin.id, `Imported ${created.length} bank questions`);
      await saveDb();
      sendJson(res, 201, { created, skipped });
      return;
    }

    if (req.method === 'DELETE' && url.pathname.startsWith('/api/question-bank/')) {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const id = url.pathname.split('/').pop();
      db.questionBank = db.questionBank.filter((question) => question.id !== id);
      logActivity(admin.id, 'Deleted question bank item');
      await saveDb();
      sendJson(res, 200, { ok: true });
      return;
    }

    if (req.method === 'GET' && url.pathname.startsWith('/api/quizzes/') && url.pathname.endsWith('/take')) {
      const user = requireAuth(req, res);
      if (!user || user.role !== 'student') {
        sendJson(res, 403, { error: 'Student access required' });
        return;
      }
      const quizId = url.pathname.split('/')[3];
      const quiz = db.quizzes.find((item) => item.id === quizId && item.status === 'published');
      const isPreviousPractice = quizPreviouslyAvailableForStudent(quiz, user.id);
      if (!quizAvailableForStudent(quiz, user.id) && !isPreviousPractice) {
        sendJson(res, 404, { error: 'Assigned quiz not found' });
        return;
      }
      if (!isPreviousPractice && attemptsFor(user.id, quiz.id).length >= 1) {
        sendJson(res, 403, { error: 'Retake limit reached' });
        return;
      }
      sendJson(res, 200, { quiz: stripQuizAnswers(quiz) });
      return;
    }

    if (req.method === 'POST' && url.pathname.startsWith('/api/quizzes/') && url.pathname.endsWith('/submit')) {
      const user = requireAuth(req, res);
      if (!user || user.role !== 'student') {
        sendJson(res, 403, { error: 'Student access required' });
        return;
      }
      const quizId = url.pathname.split('/')[3];
      const quiz = db.quizzes.find((item) => item.id === quizId && item.status === 'published');
      const isPreviousPractice = quizPreviouslyAvailableForStudent(quiz, user.id);
      if (!quizAvailableForStudent(quiz, user.id) && !isPreviousPractice) {
        sendJson(res, 404, { error: 'Assigned quiz not found' });
        return;
      }
      if (!isPreviousPractice && attemptsFor(user.id, quiz.id).length >= 1) {
        sendJson(res, 403, { error: 'Retake limit reached' });
        return;
      }
      const input = await bodyJson(req);
      const score = scoreAttempt(quiz, input.answers || {});
      const attempt = {
        id: randomUUID(),
        sessionId: db.activeSessionId,
        quizId: quiz.id,
        quizTitle: quiz.title,
        studentId: user.id,
        studentName: user.name,
        answers: input.answers || {},
        warnings: Math.max(0, Number(input.warnings || 0)),
        mode: isPreviousPractice ? 'practice' : 'live',
        ...score,
        submittedAt: now(),
      };
      db.attempts.push(attempt);
      db.drafts = db.drafts.filter((draft) => !(draft.quizId === quiz.id && draft.studentId === user.id));
      await sendResultEmail(
        user.email,
        `Quiz result: ${quiz.title}`,
        `Score: ${attempt.percentage}% (${attempt.reviewStatus === 'pending' ? 'pending review' : attempt.passed ? 'passed' : 'failed'})`,
      );
      await sendSms(user.studentMobile || user.mobile, renderTemplate(smsTemplate('quizResult'), {
        exam: quiz.title,
        name: user.name,
        roll: user.rollNumber || '-',
        mobile: user.studentMobile || user.mobile || '',
        percentage: attempt.percentage,
        mark: attempt.earned ?? attempt.correct ?? '',
        total: attempt.total ?? '',
        status: attempt.passed ? 'Passed' : 'Failed',
      }), 'result_sms', { attemptId: attempt.id, quizId: quiz.id, studentId: user.id });
      logActivity(user.id, `Submitted quiz ${quiz.title}`);
      await saveDb();
      sendJson(res, 201, { attempt });
      return;
    }

    if (req.method === 'POST' && url.pathname.startsWith('/api/quizzes/') && url.pathname.endsWith('/autosave')) {
      const user = requireAuth(req, res);
      if (!user || user.role !== 'student') {
        sendJson(res, 403, { error: 'Student access required' });
        return;
      }
      const quizId = url.pathname.split('/')[3];
      const quiz = db.quizzes.find((item) => item.id === quizId && item.status === 'published');
      if (!quizAvailableForStudent(quiz, user.id)) {
        sendJson(res, 404, { error: 'Assigned quiz not found' });
        return;
      }
      const input = await bodyJson(req);
      const draft = {
        id: randomUUID(),
        quizId,
        studentId: user.id,
        answers: input.answers || {},
        warnings: Math.max(0, Number(input.warnings || 0)),
        updatedAt: now(),
      };
      db.drafts = [
        draft,
        ...db.drafts.filter((item) => !(item.quizId === quizId && item.studentId === user.id)),
      ].slice(0, 500);
      await saveDb();
      sendJson(res, 200, { savedAt: draft.updatedAt });
      return;
    }

    if (req.method === 'PATCH' && url.pathname.startsWith('/api/attempts/')) {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const attemptId = url.pathname.split('/').pop();
      const attempt = db.attempts.find((item) => item.id === attemptId);
      if (!attempt) {
        sendJson(res, 404, { error: 'Attempt not found' });
        return;
      }
      const input = await bodyJson(req);
      const manualPoints = Math.max(0, Number(input.manualPoints || 0));
      attempt.earned = Math.min(Number(attempt.total || 0), Number(attempt.earned || 0) + manualPoints);
      attempt.percentage = attempt.total ? Math.round((attempt.earned / attempt.total) * 100) : 0;
      const quiz = db.quizzes.find((item) => item.id === attempt.quizId);
      attempt.passed = attempt.percentage >= Number(quiz?.passMark || 0);
      attempt.reviewStatus = 'graded';
      attempt.reviewNote = String(input.reviewNote || '').trim();
      attempt.reviewedAt = now();
      logActivity(admin.id, `Reviewed attempt for ${attempt.studentName}`);
      await saveDb();
      sendJson(res, 200, { attempt });
      return;
    }

    if (req.method === 'GET' && url.pathname === '/api/export/results.csv') {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const quizId = url.searchParams.get('quizId') || '';
      const filters = { examName: url.searchParams.get('examName') || '', courseId: url.searchParams.get('courseId') || '', batchId: url.searchParams.get('batchId') || '' };
      const quiz = quizId ? db.quizzes.find((item) => item.id === quizId) : null;
      const resultRows = resultExportRows(quizId, filters);
      const safeName = String(quiz?.title || 'results')
        .toLowerCase()
        .replace(/[^a-z0-9]+/g, '-')
        .replace(/^-+|-+$/g, '')
        .slice(0, 60) || 'quiz-results';
      const rows = [
        ['Mobile', 'Name', 'Roll', 'Batch', 'College', 'Subject', 'Mark', 'Negative Mark', 'Position', 'Highest', 'Total', 'Percentage', 'Status', 'Submitted'].map(csvEscape).join(','),
        ...resultRows.map((row) => [
          row.mobile,
          row.name,
          row.roll,
          row.batch,
          row.college,
          row.subject,
          row.mark,
          row.negativeMark,
          row.position,
          row.highest,
          row.total,
          `${row.percentage}%`,
          row.status,
          row.submittedAt,
        ].map(csvEscape).join(',')),
      ];
      res.writeHead(200, {
        ...securityHeaders(),
        'content-type': 'text/csv; charset=utf-8',
        'content-disposition': `attachment; filename="${quiz ? exportFileName(`results-${safeName}`, 'csv', url) : exportFileName('results', 'csv', url)}"`,
      });
      res.end(rows.join('\n'));
      return;
    }

    if (req.method === 'GET' && url.pathname === '/api/export/result-sms.csv') {
      const staff = requireMenuAccess(req, res, 'resultSms');
      if (!staff) return;
      const quizId = url.searchParams.get('quizId') || '';
      const template = url.searchParams.get('template') || '';
      const rows = resultExportRows(quizId, { examName: url.searchParams.get('examName') || '', courseId: url.searchParams.get('courseId') || '', batchId: url.searchParams.get('batchId') || '' });
      const headers = ['Exam', 'Subject', 'Name', 'Mobile', 'Roll', 'Batch', 'College', 'Mark', 'Negative Mark', 'Position', 'Highest', 'Final SMS'];
      const dataRows = rows.map((row) => [
        row.quizTitle,
        row.subject,
        row.name,
        row.mobile,
        row.roll,
        row.batch,
        row.college,
        row.mark,
        row.negativeMark,
        row.position,
        row.highest,
        renderResultSms(template, row),
      ]);
      res.writeHead(200, { ...securityHeaders(), 'content-type': 'text/csv; charset=utf-8', 'content-disposition': `attachment; filename="${exportFileName('result-sms-export', 'csv', url)}"` });
      res.end([headers.map(csvEscape).join(','), ...dataRows.map((row) => row.map(csvEscape).join(','))].join('\n'));
      return;
    }

    if (req.method === 'POST' && url.pathname === '/api/result-sms/send') {
      const staff = requireMenuAccess(req, res, 'resultSms');
      if (!staff) return;
      const input = await bodyJson(req);
      const rows = resultExportRows(String(input.quizId || ''), {
        examName: String(input.examName || ''),
        courseId: String(input.courseId || ''),
        batchId: String(input.batchId || ''),
      }).filter((row) => row.mobile);
      const template = String(input.template || '').trim() || smsTemplate('resultSms');
      const sent = [];
      for (const row of rows) {
        sent.push(await sendSms(row.mobile, renderResultSms(template, row), 'result_sms', {
          quizTitle: row.quizTitle,
          roll: row.roll,
          studentName: row.name,
        }));
      }
      logActivity(staff.id, `Sent/logged ${sent.length} result SMS`);
      await saveDb();
      sendJson(res, 200, { count: sent.length, sent });
      return;
    }

    if (req.method === 'GET' && ['/api/export/students.csv', '/api/export/students.pdf'].includes(url.pathname)) {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const students = filteredStudents(url);
      const headers = ['Name', 'Roll', 'Mobile', 'Guardian', 'College', 'Courses', 'Batches', 'Payable', 'Paid', 'Due', 'Status'];
      const rows = students.map((student) => {
        const finance = studentFinancialSummary(student.id);
        return [
          student.name,
          student.rollNumber,
          student.studentMobile,
          student.guardianMobile,
          student.college,
          finance.enrollments.map((item) => item.courseName).join(', '),
          (student.batchEnrollments || []).map((item) => db.batches.find((batch) => batch.id === item.batchId)?.name).filter(Boolean).join(', '),
          finance.totalPayable,
          finance.totalPaid,
          finance.totalDue,
          student.status,
        ];
      });
      if (url.pathname.endsWith('.pdf')) {
        const buffer = await createReportPdfBuffer('Student Report', headers, rows, `Session: ${activeSession()?.name || '-'} · Records: ${rows.length}`);
        res.writeHead(200, { ...securityHeaders(), 'content-type': 'application/pdf', 'content-disposition': `attachment; filename="${exportFileName('student-report', 'pdf', url)}"`, 'content-length': buffer.length });
        res.end(buffer);
      } else {
        res.writeHead(200, { ...securityHeaders(), 'content-type': 'text/csv; charset=utf-8', 'content-disposition': `attachment; filename="${exportFileName('student-report', 'csv', url)}"` });
        res.end([headers.map(csvEscape).join(','), ...rows.map((row) => row.map(csvEscape).join(','))].join('\n'));
      }
      return;
    }

    if (req.method === 'GET' && ['/api/export/payments.csv', '/api/export/payments.pdf'].includes(url.pathname)) {
      const staff = requireMenuAccess(req, res, 'studentPayments');
      if (!staff) return;
      const payments = filteredPayments(url).map(publicPayment);
      const headers = ['Receipt', 'Student', 'Roll', 'Course', 'Amount', 'Method', 'Date', 'Status', 'Reference'];
      const rows = payments.map((payment) => [
        payment.receiptNumber,
        payment.studentName,
        payment.studentRoll,
        payment.courseName,
        payment.amount,
        payment.method,
        payment.paidAt,
        payment.status,
        payment.reference || '',
      ]);
      if (url.pathname.endsWith('.pdf')) {
        const buffer = await createReportPdfBuffer('Payment Report', headers, rows, `Session: ${activeSession()?.name || '-'} · Records: ${rows.length}`);
        res.writeHead(200, { ...securityHeaders(), 'content-type': 'application/pdf', 'content-disposition': `attachment; filename="${exportFileName('payment-report', 'pdf', url)}"`, 'content-length': buffer.length });
        res.end(buffer);
      } else {
        res.writeHead(200, { ...securityHeaders(), 'content-type': 'text/csv; charset=utf-8', 'content-disposition': `attachment; filename="${exportFileName('payment-report', 'csv', url)}"` });
        res.end([headers.map(csvEscape).join(','), ...rows.map((row) => row.map(csvEscape).join(','))].join('\n'));
      }
      return;
    }

    if (req.method === 'GET' && ['/api/export/teacher-payments.csv', '/api/export/teacher-payments.pdf'].includes(url.pathname)) {
      const staff = requireMenuAccess(req, res, 'teacherPayments');
      if (!staff) return;
      const payments = filteredTeacherPayments(url);
      const headers = ['Teacher', 'Course', 'Batch', 'Month', 'Subject', 'Dates', 'Classes', 'Payable', 'Paid', 'Due', 'Method', 'Reference'];
      const rows = payments.map((payment) => [
        payment.teacherName,
        payment.courseName,
        payment.batchName,
        payment.month,
        payment.subject,
        (payment.classEntries || []).map((entry) => `${entry.date} (${entry.classes})`).join('; '),
        payment.totalClasses,
        payment.payable,
        payment.paid,
        payment.due,
        payment.method,
        payment.reference || '',
      ]);
      if (url.pathname.endsWith('.pdf')) {
        const buffer = await createReportPdfBuffer('Teacher Payment Report', headers, rows, `Session: ${activeSession()?.name || '-'} · Records: ${rows.length}`);
        res.writeHead(200, { ...securityHeaders(), 'content-type': 'application/pdf', 'content-disposition': `attachment; filename="${exportFileName('teacher-payment-report', 'pdf', url)}"`, 'content-length': buffer.length });
        res.end(buffer);
      } else {
        res.writeHead(200, { ...securityHeaders(), 'content-type': 'text/csv; charset=utf-8', 'content-disposition': `attachment; filename="${exportFileName('teacher-payment-report', 'csv', url)}"` });
        res.end([headers.map(csvEscape).join(','), ...rows.map((row) => row.map(csvEscape).join(','))].join('\n'));
      }
      return;
    }

    if (req.method === 'GET' && ['/api/export/employee-payments.csv', '/api/export/employee-payments.pdf'].includes(url.pathname)) {
      const staff = requireMenuAccess(req, res, 'employeePayments');
      if (!staff) return;
      const payments = filteredEmployeePayments(url);
      const headers = ['Employee', 'Designation', 'Month', 'Salary', 'Allowance', 'Bonus', 'Deduction', 'Advance', 'Payable', 'Paid', 'Due', 'Method', 'Reference'];
      const rows = payments.map((payment) => [
        payment.employeeName,
        payment.designation,
        payment.month,
        payment.salaryAmount,
        payment.allowance,
        payment.bonus,
        payment.deduction,
        payment.advance,
        payment.payable,
        payment.paid,
        payment.due,
        payment.method,
        payment.reference || '',
      ]);
      if (url.pathname.endsWith('.pdf')) {
        const buffer = await createReportPdfBuffer('Employee Payment Report', headers, rows, `Session: ${activeSession()?.name || '-'} · Records: ${rows.length}`);
        res.writeHead(200, { ...securityHeaders(), 'content-type': 'application/pdf', 'content-disposition': `attachment; filename="${exportFileName('employee-payment-report', 'pdf', url)}"`, 'content-length': buffer.length });
        res.end(buffer);
      } else {
        res.writeHead(200, { ...securityHeaders(), 'content-type': 'text/csv; charset=utf-8', 'content-disposition': `attachment; filename="${exportFileName('employee-payment-report', 'csv', url)}"` });
        res.end([headers.map(csvEscape).join(','), ...rows.map((row) => row.map(csvEscape).join(','))].join('\n'));
      }
      return;
    }

    if (req.method === 'GET' && ['/api/export/reference-payments.csv', '/api/export/reference-payments.pdf'].includes(url.pathname)) {
      const staff = requireMenuAccess(req, res, 'referencePayments');
      if (!staff) return;
      const payments = filteredReferencePayments(url);
      const headers = ['Student', 'Roll', 'Referrer', 'Mobile', 'Type', 'Commission', 'Paid', 'Due', 'Method', 'Reference', 'Date'];
      const rows = payments.map((payment) => [
        payment.studentName,
        payment.studentRoll,
        payment.referrerName,
        payment.referrerMobile,
        payment.referrerType,
        payment.commissionAmount,
        payment.paid,
        payment.due,
        payment.method,
        payment.reference || '',
        payment.paidAt,
      ]);
      if (url.pathname.endsWith('.pdf')) {
        const buffer = await createReportPdfBuffer('Reference Payment Report', headers, rows, `Session: ${activeSession()?.name || '-'} · Records: ${rows.length}`);
        res.writeHead(200, { ...securityHeaders(), 'content-type': 'application/pdf', 'content-disposition': `attachment; filename="${exportFileName('reference-payment-report', 'pdf', url)}"`, 'content-length': buffer.length });
        res.end(buffer);
      } else {
        res.writeHead(200, { ...securityHeaders(), 'content-type': 'text/csv; charset=utf-8', 'content-disposition': `attachment; filename="${exportFileName('reference-payment-report', 'csv', url)}"` });
        res.end([headers.map(csvEscape).join(','), ...rows.map((row) => row.map(csvEscape).join(','))].join('\n'));
      }
      return;
    }

    if (req.method === 'GET' && ['/api/export/other-payments.csv', '/api/export/other-payments.pdf'].includes(url.pathname)) {
      const staff = requireMenuAccess(req, res, 'otherPayments');
      if (!staff) return;
      const payments = filteredOtherPayments(url);
      const headers = ['Category', 'Title', 'Amount', 'Method', 'Reference', 'Date', 'Entered By', 'Note'];
      const rows = payments.map((payment) => [
        payment.category,
        payment.title || '',
        payment.amount,
        payment.method,
        payment.reference || '',
        payment.paidAt,
        payment.enteredByName || '',
        payment.note || '',
      ]);
      if (url.pathname.endsWith('.pdf')) {
        const buffer = await createReportPdfBuffer('Other Payment Report', headers, rows, `Session: ${activeSession()?.name || '-'} · Records: ${rows.length}`);
        res.writeHead(200, { ...securityHeaders(), 'content-type': 'application/pdf', 'content-disposition': `attachment; filename="${exportFileName('other-payment-report', 'pdf', url)}"`, 'content-length': buffer.length });
        res.end(buffer);
      } else {
        res.writeHead(200, { ...securityHeaders(), 'content-type': 'text/csv; charset=utf-8', 'content-disposition': `attachment; filename="${exportFileName('other-payment-report', 'csv', url)}"` });
        res.end([headers.map(csvEscape).join(','), ...rows.map((row) => row.map(csvEscape).join(','))].join('\n'));
      }
      return;
    }

    if (req.method === 'GET' && ['/api/export/shareholder-ledger.csv', '/api/export/shareholder-ledger.pdf', '/api/export/shareholder-ledger.xlsx'].includes(url.pathname)) {
      const staff = requireMenuAccess(req, res, 'payments');
      if (!staff) return;
      const entries = filteredShareholderLedger(url);
      const headers = ['Shareholder', 'Mobile', 'Type', 'Title', 'Debit', 'Credit', 'Balance Impact', 'Method', 'Reference', 'Date', 'Entered By', 'Note'];
      const rows = entries.map((entry) => {
        const amount = Number(entry.amount || 0);
        return [
          entry.shareholderName,
          entry.shareholderMobile,
          entry.typeLabel,
          entry.title || '',
          amount < 0 ? Math.abs(amount) : '',
          amount > 0 ? amount : '',
          amount,
          entry.method,
          entry.reference || '',
          entry.entryDate,
          entry.enteredByName || '',
          entry.note || '',
        ];
      });
      if (url.pathname.endsWith('.xlsx')) {
        const buffer = createTableWorkbook(headers, rows);
        res.writeHead(200, { ...securityHeaders(), 'content-type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'content-disposition': 'attachment; filename="shareholder-ledger-report.xlsx"', 'content-length': buffer.length });
        res.end(buffer);
      } else if (url.pathname.endsWith('.pdf')) {
        const buffer = await createReportPdfBuffer('Shareholder Ledger Report', headers, rows);
        res.writeHead(200, { ...securityHeaders(), 'content-type': 'application/pdf', 'content-disposition': 'attachment; filename="shareholder-ledger-report.pdf"', 'content-length': buffer.length });
        res.end(buffer);
      } else {
        res.writeHead(200, { ...securityHeaders(), 'content-type': 'text/csv; charset=utf-8', 'content-disposition': 'attachment; filename="shareholder-ledger-report.csv"' });
        res.end([headers.map(csvEscape).join(','), ...rows.map((row) => row.map(csvEscape).join(','))].join('\n'));
      }
      return;
    }

    if (req.method === 'GET' && ['/api/export/shareholder-session-closing.csv', '/api/export/shareholder-session-closing.pdf', '/api/export/shareholder-session-closing.xlsx'].includes(url.pathname)) {
      const staff = requireMenuAccess(req, res, 'payments');
      if (!staff) return;
      const sessionId = url.searchParams.get('sessionId') || db.activeSessionId;
      const session = db.sessionsList.find((item) => item.id === sessionId) || activeSession();
      const finance = sessionNetProfit(sessionId);
      const headers = ['Shareholder', 'Mobile', 'Share %', 'Opening', 'Investment', 'Reference Commission', 'Profit Share', 'Paid for Institute', 'Withdrawal / Advance', 'Expense Charged', 'Adjustment', 'Final Balance', 'Status'];
      const rows = shareholderClosingRows(sessionId).map((row) => [
        row.shareholder.name,
        row.shareholder.mobile || '',
        row.shareholder.sharePercentage || 0,
        row.opening,
        row.investment,
        row.commission,
        row.profitShare,
        row.paidForInstitute,
        row.withdrawal,
        row.expenseCharged,
        row.adjustments,
        row.balance,
        row.shareholder.status || 'active',
      ]);
      rows.push(['', '', '', '', '', '', '', '', '', '', '', '', '']);
      rows.push(['Session', session.name, 'Income', finance.income, 'Expense', finance.expense, 'Net Profit', finance.profit, '', '', '', '', '']);
      if (url.pathname.endsWith('.xlsx')) {
        const buffer = createTableWorkbook(headers, rows);
        res.writeHead(200, { ...securityHeaders(), 'content-type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', 'content-disposition': 'attachment; filename="shareholder-session-closing.xlsx"', 'content-length': buffer.length });
        res.end(buffer);
      } else if (url.pathname.endsWith('.pdf')) {
        const buffer = await createReportPdfBuffer(`Shareholder Session Closing - ${session.name}`, headers, rows);
        res.writeHead(200, { ...securityHeaders(), 'content-type': 'application/pdf', 'content-disposition': 'attachment; filename="shareholder-session-closing.pdf"', 'content-length': buffer.length });
        res.end(buffer);
      } else {
        res.writeHead(200, { ...securityHeaders(), 'content-type': 'text/csv; charset=utf-8', 'content-disposition': 'attachment; filename="shareholder-session-closing.csv"' });
        res.end([headers.map(csvEscape).join(','), ...rows.map((row) => row.map(csvEscape).join(','))].join('\n'));
      }
      return;
    }

    if (req.method === 'GET' && url.pathname === '/api/export/results.xlsx') {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const quizId = url.searchParams.get('quizId') || '';
      const quiz = db.quizzes.find((item) => item.id === quizId);
      if (!quiz) {
        sendJson(res, 404, { error: 'Quiz not found' });
        return;
      }
      const attempts = db.attempts.filter((attempt) => attempt.quizId === quizId);
      const workbook = createResultsWorkbook(quiz, attempts);
      const safeName = String(quiz.title || 'quiz-results')
        .toLowerCase()
        .replace(/[^a-z0-9]+/g, '-')
        .replace(/^-+|-+$/g, '')
        .slice(0, 60) || 'quiz-results';
      res.writeHead(200, {
        ...securityHeaders(),
        'content-type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        'content-disposition': `attachment; filename="${safeName}-results.xlsx"`,
        'content-length': workbook.length,
      });
      res.end(workbook);
      return;
    }

    if (req.method === 'GET' && url.pathname === '/api/export/questions.csv') {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      const rows = [
        ['Type', 'Question', 'Options', 'Correct', 'Category', 'Answer Explanation', 'Points'].map(csvEscape).join(','),
        ...db.questionBank.map((question) => [
          question.type,
          question.text,
          (question.options || []).join('|'),
          question.type === 'mcq' || question.type === 'true_false' ? Number(question.correctIndex || 0) + 1 : question.correctText,
          question.category || '',
          question.explanation || '',
          question.points || 1,
        ].map(csvEscape).join(',')),
      ];
      res.writeHead(200, {
        ...securityHeaders(),
        'content-type': 'text/csv; charset=utf-8',
        'content-disposition': 'attachment; filename="question-bank.csv"',
      });
      res.end(rows.join('\n'));
      return;
    }

    if (req.method === 'GET' && url.pathname === '/api/export/quizzes.json') {
      const admin = requireAdmin(req, res);
      if (!admin) return;
      res.writeHead(200, {
        ...securityHeaders(),
        'content-type': 'application/json; charset=utf-8',
        'content-disposition': 'attachment; filename="quizzes.json"',
      });
      res.end(JSON.stringify(db.quizzes, null, 2));
      return;
    }

    if (req.method === 'GET' && url.pathname.startsWith('/api/students/') && url.pathname.endsWith('/progress.pdf')) {
      const user = requireAuth(req, res);
      if (!user) return;
      const studentId = url.pathname.split('/')[3];
      if (!isStaff(user) && user.id !== studentId) {
        sendJson(res, 403, { error: 'You can only download your own progress report' });
        return;
      }
      const report = progressReportForStudent(studentId);
      if (!report) {
        sendJson(res, 404, { error: 'Student not found' });
        return;
      }
      const headers = ['Exam', 'Subject', 'Mark', 'Negative', 'Position', 'Highest', 'Source'];
      const rows = [
        ...report.attempts.map((attempt) => [
          attempt.quizTitle,
          quizSubject(db.quizzes.find((quiz) => quiz.id === attempt.quizId) || {}),
          attempt.earned ?? attempt.correct ?? 0,
          '',
          '',
          '',
          'Quiz',
        ]),
        ...report.manualResults.map((item) => [
          item.examName,
          item.subject || '',
          item.mark || 0,
          item.negativeMark ?? '',
          item.position || '',
          item.highest || '',
          'Manual',
        ]),
        ['Paid', '', report.finance.totalPaid || 0, '', '', '', 'Finance'],
        ['Due', '', report.finance.totalDue || 0, '', '', '', 'Finance'],
        ['Discount', '', report.finance.totalDiscount || 0, '', '', '', 'Finance'],
      ];
      const buffer = await createReportPdfBuffer(`Progress Report - ${report.student.name}`, headers, rows);
      res.writeHead(200, {
        ...securityHeaders(),
        'content-type': 'application/pdf',
        'content-disposition': `attachment; filename="progress-${report.student.rollNumber || studentId.slice(0, 8)}.pdf"`,
      });
      res.end(buffer);
      return;
    }

    if (req.method === 'GET' && url.pathname.startsWith('/api/students/') && url.pathname.endsWith('/profile.pdf')) {
      const staff = requireAdmin(req, res);
      if (!staff) return;
      const studentId = url.pathname.split('/')[3];
      const student = db.users.find((item) => item.id === studentId && item.role === 'student');
      if (!student) {
        sendJson(res, 404, { error: 'Student not found' });
        return;
      }
      const buffer = await createStudentProfilePdfBuffer(student);
      res.writeHead(200, {
        ...securityHeaders(),
        'content-type': 'application/pdf',
        'content-disposition': `attachment; filename="student-${student.rollNumber || student.id.slice(0, 8)}.pdf"`,
      });
      res.end(buffer);
      return;
    }

    if (req.method === 'GET' && url.pathname.startsWith('/api/payments/') && url.pathname.endsWith('/receipt.pdf')) {
      const user = requireAuth(req, res);
      if (!user) return;
      const paymentId = url.pathname.split('/')[3];
      const payment = db.payments.find((item) => item.id === paymentId);
      if (!payment || (!isStaff(user) && payment.studentId !== user.id)) {
        sendJson(res, 404, { error: 'Receipt not found' });
        return;
      }
      const buffer = await createReceiptPdfBuffer(payment);
      res.writeHead(200, {
        ...securityHeaders(),
        'content-type': 'application/pdf',
        'content-disposition': `attachment; filename="${payment.receiptNumber}.pdf"`,
      });
      res.end(buffer);
      return;
    }

    if (req.method === 'GET' && url.pathname.startsWith('/api/attempts/') && url.pathname.endsWith('/result.pdf')) {
      const user = requireAuth(req, res);
      if (!user) return;
      const attemptId = url.pathname.split('/')[3];
      const attempt = db.attempts.find((item) => item.id === attemptId);
      if (!attempt || (user.role !== 'admin' && attempt.studentId !== user.id)) {
        sendJson(res, 404, { error: 'Result not found' });
        return;
      }
      const buffer = await createResultPdfBuffer(attempt);
      res.writeHead(200, {
        ...securityHeaders(),
        'content-type': 'application/pdf',
        'content-disposition': `attachment; filename="result-${attempt.id.slice(0, 8)}.pdf"`,
      });
      res.end(buffer);
      return;
    }

    if (req.method === 'GET' && url.pathname.startsWith('/api/certificate/') && url.pathname.endsWith('.pdf')) {
      const user = requireAuth(req, res);
      if (!user) return;
      const attemptId = url.pathname.split('/').pop().replace(/\.pdf$/, '');
      const attempt = db.attempts.find((item) => item.id === attemptId);
      const quiz = db.quizzes.find((item) => item.id === attempt?.quizId);
      if (!attempt || !quiz || !quiz.certificateEnabled || !attempt.passed || (user.role !== 'admin' && attempt.studentId !== user.id)) {
        sendJson(res, 404, { error: 'Certificate not found' });
        return;
      }
      const buffer = await createCertificatePdfBuffer(attempt);
      res.writeHead(200, {
        ...securityHeaders(),
        'content-type': 'application/pdf',
        'content-disposition': `attachment; filename="certificate-${attempt.id.slice(0, 8)}.pdf"`,
      });
      res.end(buffer);
      return;
    }

    sendJson(res, 404, { error: 'API route not found' });
  } catch (error) {
    sendJson(res, 500, { error: error.message || 'Server error' });
  }
}

async function serveStatic(req, res, url) {
  const requested = url.pathname === '/' ? '/index.html' : url.pathname;
  const safePath = normalize(requested).replace(/^[/\\]+/, '').replace(/^(\.\.[/\\])+/, '');
  const filePath = join(publicDir, safePath);
  if (!filePath.startsWith(publicDir)) {
    res.writeHead(403, securityHeaders());
    res.end('Forbidden');
    return;
  }
  try {
    const content = await readFile(filePath);
    res.writeHead(200, {
      ...securityHeaders(),
      'content-type': mimeTypes[extname(filePath)] || 'application/octet-stream',
      'cache-control': 'no-store, max-age=0',
    });
    res.end(content);
  } catch {
    const content = await readFile(join(publicDir, 'index.html'));
    res.writeHead(200, { ...securityHeaders(), 'content-type': 'text/html; charset=utf-8', 'cache-control': 'no-store, max-age=0' });
    res.end(content);
  }
}

function serveCertificate(req, res, attemptId) {
  const user = sessionUser(req);
  if (!user) {
    res.writeHead(302, { ...securityHeaders(), location: '/' });
    res.end();
    return;
  }
  const attempt = db.attempts.find((item) => item.id === attemptId);
  const quiz = db.quizzes.find((item) => item.id === attempt?.quizId);
  if (!attempt || !quiz || !quiz.certificateEnabled || !attempt.passed || (user.role !== 'admin' && attempt.studentId !== user.id)) {
    res.writeHead(404, { ...securityHeaders(), 'content-type': 'text/plain; charset=utf-8' });
    res.end('Certificate not found');
    return;
  }
  const content = `<!doctype html>
<html><head><meta charset="utf-8"><title>Certificate</title>
<style>
body{font-family:Georgia,serif;background:#f4f1e9;margin:0;padding:40px;color:#162033}
.certificate{max-width:900px;margin:0 auto;background:#fff;border:12px solid #176b87;padding:56px;text-align:center}
h1{font-size:44px;margin:0 0 18px}.name{font-size:34px;margin:24px 0;color:#c95f2d}.meta{font-family:Arial,sans-serif;color:#68758a}
button{margin-top:28px;padding:10px 16px;border:0;background:#176b87;color:#fff;border-radius:6px}
@media print{button{display:none}body{background:#fff}.certificate{box-shadow:none}}
</style></head><body>
<div class="certificate">
<h1>Certificate of Achievement</h1>
<p>This certifies that</p>
<div class="name">${escapeHtml(attempt.studentName)}</div>
<p>successfully completed <strong>${escapeHtml(attempt.quizTitle)}</strong></p>
<p>Score: <strong>${attempt.percentage}%</strong></p>
<p class="meta">Issued on ${formatCertificateDate(attempt.submittedAt)} · Certificate ID ${attempt.id.slice(0, 8).toUpperCase()}</p>
<button onclick="window.print()">Print Certificate</button>
</div></body></html>`;
  res.writeHead(200, { ...securityHeaders(), 'content-type': 'text/html; charset=utf-8' });
  res.end(content);
}

function formatCertificateDate(value) {
  return new Date(value).toLocaleDateString('en-US', { year: 'numeric', month: 'long', day: 'numeric' });
}

function escapeHtml(value) {
  return String(value ?? '')
    .replaceAll('&', '&amp;')
    .replaceAll('<', '&lt;')
    .replaceAll('>', '&gt;')
    .replaceAll('"', '&quot;')
    .replaceAll("'", '&#039;');
}

function startServer() {
  createServer(async (req, res) => {
    const url = new URL(req.url, `http://${req.headers.host}`);
    if (url.pathname.startsWith('/certificate/')) {
      serveCertificate(req, res, url.pathname.split('/').pop());
      return;
    }
    if (url.pathname.startsWith('/api/')) {
      await handleApi(req, res, url);
      return;
    }
    await serveStatic(req, res, url);
  }).listen(port, '0.0.0.0', () => {
    console.log(`Coaching Management System running at http://localhost:${port}`);
    console.log('Admin: admin@example.com / admin123');
    console.log('Student: student@example.com / student123');
  });
}

ensureDb()
  .then(startServer)
  .catch((error) => {
    console.error(error);
    process.exit(1);
  });
