/**
 * Fees Controller
 * Student billing and fee groups
 */

const express = require('express');
const router = express.Router();
const { v4: uuidv4 } = require('uuid');
const Database = require('better-sqlite3');
const path = require('path');
const AuditService = require('../services/AuditService');
const { outstandingForFee, outstandingForInvoice } = require('../lib/outstanding');

const dbPath = process.env.DB_PATH || path.join(__dirname, '../../data/school.db');
const getDb = () => {
  const db = new Database(dbPath);
  db.pragma('foreign_keys = ON');
  return db;
};

// Auth middleware
const auth = (req, res, next) => {
  const token = req.headers.authorization?.split(' ')[1];
  if (!token) return res.status(401).json({ error: 'No token provided' });
  try {
    const jwt = require('jsonwebtoken');
    const { jwtSecret: JWT_SECRET } = require('../config');
    req.user = jwt.verify(token, JWT_SECRET);
    next();
  } catch (err) {
    res.status(401).json({ error: 'Invalid token' });
  }
};

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

// §10a — broaden write access to include Bursar and Accountant (operational
// roles). Student/Parent still get the read-self scope they had before.
const feesWrite = (req, res, next) => {
  if (!['school_admin', 'systems_admin', 'principal', 'bursar', 'accountant'].includes(req.user.role)) {
    return res.status(403).json({ error: 'Finance / accounting role required' });
  }
  next();
};

// ============ FEE GROUPS ============

/**
 * @openapi
 * /api/fees/groups:
 *   get:
 *     summary: List fee groups
 *     description: |
 *       Returns all active fee groups ordered by name. Each group
 *       defines a recurring fee (e.g. tuition, levies) that can be
 *       assigned to individual students.
 *     tags:
 *       - Fees
 *     security:
 *       - bearerAuth: []
 *     responses:
 *       200:
 *         description: Fee group list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *       500: { description: Server error }
 */
router.get('/groups', auth, (req, res) => {
  try {
    const groups = getDb().prepare('SELECT * FROM fee_groups WHERE is_deleted = 0 ORDER BY name').all();
    res.json(groups);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

router.post('/groups', auth, feesWrite, async (req, res) => {
  try {
    const { name, description, amount, type, frequency, academic_year, due_date } = req.body;
    const uid = uuidv4();

    getDb().prepare(`
      INSERT INTO fee_groups (uid, name, description, amount, type, frequency, academic_year, due_date)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `).run(uid, name, description, amount, type, frequency, academic_year, due_date);

    await AuditService.log(req, 'CREATE_FEE_GROUP', {
      entityType: 'fee_group',
      entityId: uid,
      newData: { name, amount, academic_year }
    });

    res.status(201).json({ success: true, uid });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

router.patch('/groups/:id', auth, feesWrite, async (req, res) => {
  try {
    const id = Number(req.params.id);
    if (!Number.isFinite(id)) return res.status(400).json({ error: 'Invalid id' });
    const existing = getDb().prepare('SELECT id FROM fee_groups WHERE id = ? AND is_deleted = 0').get(id);
    if (!existing) return res.status(404).json({ error: 'Fee group not found' });

    const updates = [];
    const params = [];
    for (const f of ['name', 'description', 'amount', 'type', 'frequency', 'academic_year', 'due_date']) {
      if (req.body[f] !== undefined) { updates.push(`${f} = ?`); params.push(req.body[f]); }
    }
    if (updates.length === 0) return res.status(400).json({ error: 'No updatable fields supplied' });
    updates.push(`updated_at = datetime('now','localtime')`, `sync_status = 'pending'`);
    params.push(id);
    getDb().prepare(`UPDATE fee_groups SET ${updates.join(', ')} WHERE id = ?`).run(...params);

    await AuditService.log(req, 'UPDATE_FEE_GROUP', { entityType: 'fee_group', entityId: String(id) });
    res.json(getDb().prepare('SELECT * FROM fee_groups WHERE id = ?').get(id));
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

router.delete('/groups/:id', auth, feesWrite, async (req, res) => {
  try {
    const id = Number(req.params.id);
    if (!Number.isFinite(id)) return res.status(400).json({ error: 'Invalid id' });
    const existing = getDb().prepare('SELECT id FROM fee_groups WHERE id = ? AND is_deleted = 0').get(id);
    if (!existing) return res.status(404).json({ error: 'Fee group not found' });

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

    await AuditService.log(req, 'ARCHIVE_FEE_GROUP', { entityType: 'fee_group', entityId: String(id) });
    res.json({ success: true });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ STUDENT FEES ============

/**
 * @openapi
 * /api/fees/students:
 *   get:
 *     summary: List student fees
 *     description: |
 *       Returns active per-student fee records joined with the
 *       parent fee group and the student user. Filters: `student_id`
 *       (admin), `term`, `class_id` (queried via fee group), and
 *       `status` (`pending`, `partial`, `paid`, etc.). Students
 *       automatically receive only their own rows; parents receive
 *       rows for any of their linked children; admins/staff receive
 *       everything (optionally filtered by `student_id`).
 *     tags:
 *       - Fees
 *     security:
 *       - bearerAuth: []
 *     parameters:
 *       - in: query
 *         name: student_id
 *         schema: { type: integer }
 *         description: Filter to a single student (admin only)
 *       - in: query
 *         name: term
 *         schema: { type: string }
 *       - in: query
 *         name: class_id
 *         schema: { type: integer }
 *       - in: query
 *         name: status
 *         schema: { type: string }
 *         description: Filter by fee status
 *     responses:
 *       200:
 *         description: Student fee list
 *         content:
 *           application/json:
 *             schema:
 *               type: array
 *               items: { type: object }
 *       401: { description: Unauthorized }
 *       500: { description: Server error }
 */
router.get('/students', auth, (req, res) => {
  if (!['school_admin', 'systems_admin', 'principal', 'bursar', 'accountant', 'student', 'parent'].includes(req.user.role)) {
    return res.status(403).json({ error: 'Finance / accounting role required' });
  }
  try {
    const { student_id, status } = req.query;
    
    let query = `
      SELECT sf.*, fg.name as fee_name, fg.description as fee_description,
             u.first_name, u.last_name, u.email,
             (SELECT c.name FROM enrollments e
              JOIN classes c ON e.class_id = c.id
              WHERE e.student_id = sf.student_id
                AND e.status = 'active' AND e.is_deleted = 0
              ORDER BY e.id DESC LIMIT 1) AS class_name,
             (SELECT c.section FROM enrollments e
              JOIN classes c ON e.class_id = c.id
              WHERE e.student_id = sf.student_id
                AND e.status = 'active' AND e.is_deleted = 0
              ORDER BY e.id DESC LIMIT 1) AS class_section
      FROM student_fees sf
      JOIN fee_groups fg ON sf.fee_group_id = fg.id
      JOIN users u ON sf.student_id = u.id
      WHERE sf.is_deleted = 0
    `;
    const params = [];
    
    if (req.user.role === 'student') {
      query += ' AND sf.student_id = ?';
      params.push(req.user.id);
    } else if (req.user.role === 'parent') {
      query += ' AND sf.student_id IN (SELECT student_id FROM parent_students WHERE parent_id = ? AND is_deleted = 0)';
      params.push(req.user.id);
    } else if (student_id) {
      query += ' AND sf.student_id = ?';
      params.push(student_id);
    }
    
    if (status) {
      query += ' AND sf.status = ?';
      params.push(status);
    }
    
    query += ' ORDER BY sf.due_date ASC';
    
    const fees = getDb().prepare(query).all(...params);
    res.json(fees.map((fee) => ({ ...fee, ...outstandingForFee(fee) })));
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

router.post('/students', auth, feesWrite, async (req, res) => {
  try {
    const { student_id, fee_group_id, amount, due_date } = req.body;
    const uid = uuidv4();
    
    getDb().prepare(`
      INSERT INTO student_fees (uid, student_id, fee_group_id, amount, due_date)
      VALUES (?, ?, ?, ?, ?)
    `).run(uid, student_id, fee_group_id, amount, due_date);
    
    await AuditService.log(req, 'ASSIGN_STUDENT_FEE', {
      entityType: 'student_fee',
      entityId: uid,
      newData: { student_id, fee_group_id, amount }
    });

    res.status(201).json({ success: true, uid });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============================================================
// §10a — Fee plans + installments + discounts (engine surface)
// Apply step (POST /plans/:uid/apply, POST /discounts/:uid/apply) lands in
// §10b. Here we expose CRUD + list endpoints only.
// ============================================================

// ============ FEE PLANS ============

router.get('/plans', auth, (req, res) => {
  try {
    const { fee_group_id, academic_year } = req.query;
    let sql = `
      SELECT p.*, fg.name AS fee_name, fg.type AS fee_type, c.name AS class_name
      FROM fee_plans p
      JOIN fee_groups fg ON p.fee_group_id = fg.id
      LEFT JOIN classes c ON p.auto_apply_to_class_id = c.id
      WHERE p.is_deleted = 0
    `;
    const params = [];
    if (fee_group_id)    { sql += ' AND p.fee_group_id = ?';  params.push(Number(fee_group_id)); }
    if (academic_year)   { sql += ' AND p.academic_year = ?';  params.push(academic_year); }
    sql += ' ORDER BY p.start_date DESC, p.id DESC';
    res.json(getDb().prepare(sql).all(...params));
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

router.get('/plans/:uid', auth, (req, res) => {
  try {
    const head = getDb().prepare(`
      SELECT p.*, fg.name AS fee_name, fg.type AS fee_type, c.name AS class_name
      FROM fee_plans p JOIN fee_groups fg ON p.fee_group_id = fg.id
      LEFT JOIN classes c ON p.auto_apply_to_class_id = c.id
      WHERE p.uid = ? AND p.is_deleted = 0
    `).get(req.params.uid);
    if (!head) return res.status(404).json({ error: 'Fee plan not found' });
    const installments = getDb().prepare(`
      SELECT * FROM fee_plan_installments WHERE fee_plan_id = ? AND is_deleted = 0 ORDER BY sequence ASC
    `).all(head.id);
    res.json({ ...head, installments });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

router.post('/plans', auth, feesWrite, async (req, res) => {
  try {
    const { name, fee_group_id, total_amount, installments_count, start_date, end_date, academic_year, auto_apply_to_class_id, notes } = req.body;
    if (!name || !fee_group_id || !total_amount || !installments_count) {
      return res.status(400).json({ error: 'name, fee_group_id, total_amount, installments_count are required' });
    }
    const fg = getDb().prepare('SELECT id FROM fee_groups WHERE id = ? AND is_deleted = 0').get(Number(fee_group_id));
    if (!fg) return res.status(400).json({ error: 'fee_group_id not found' });

    const uid = uuidv4();
    let classId = null;
    if (auto_apply_to_class_id) {
      const c = getDb().prepare('SELECT id FROM classes WHERE id = ? AND is_deleted = 0').get(Number(auto_apply_to_class_id));
      if (c) classId = c.id;
    }

    const insRes = getDb().prepare(`
      INSERT INTO fee_plans (
        uid, name, fee_group_id, total_amount, installments_count,
        start_date, end_date, academic_year, auto_apply_to_class_id, notes, created_by
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      uid, name, fg.id, Number(total_amount), Number(installments_count),
      start_date || null, end_date || null, academic_year || null,
      classId, notes || null, req.user.id,
    );
    const planId = insRes.lastInsertRowid;

    // Auto-split into equal installments if `installments[]` not supplied.
    const supplied = Array.isArray(req.body.installments) ? req.body.installments : [];
    if (supplied.length > 0) {
      const ins = getDb().prepare(`
        INSERT INTO fee_plan_installments (uid, fee_plan_id, sequence, label, amount, due_date)
        VALUES (?, ?, ?, ?, ?, ?)
      `);
      supplied.forEach((it, idx) => {
        ins.run(uuidv4(), planId, idx + 1, it.label || `Installment ${idx + 1}`,
                Number(it.amount) || 0, it.due_date || null);
      });
    } else {
      const n = Math.max(1, Number(installments_count) || 1);
      const each = Number(total_amount) / n;
      const ins = getDb().prepare(`
        INSERT INTO fee_plan_installments (uid, fee_plan_id, sequence, label, amount, due_date)
        VALUES (?, ?, ?, ?, ?, ?)
      `);
      const start = start_date ? new Date(start_date) : null;
      for (let i = 0; i < n; i++) {
        const due = start ? new Date(start.getTime()) : null;
        if (due) due.setMonth(due.getMonth() + i);
        ins.run(uuidv4(), planId, i + 1, `Installment ${i + 1} of ${n}`,
                Math.round(each * 100) / 100,
                due ? due.toISOString().slice(0, 10) : null);
      }
    }

    await AuditService.log(req, 'CREATE_FEE_PLAN', { entityType: 'fee_plan', entityId: uid, newData: { name, total_amount, installments_count: Number(installments_count) } });
    res.status(201).json({ uid, id: planId });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

router.patch('/plans/:uid', auth, feesWrite, async (req, res) => {
  try {
    const existing = getDb().prepare('SELECT id FROM fee_plans WHERE uid = ? AND is_deleted = 0').get(req.params.uid);
    if (!existing) return res.status(404).json({ error: 'Fee plan not found' });
    const updates = [];
    const params = [];
    for (const f of ['name', 'total_amount', 'installments_count', 'start_date', 'end_date', 'academic_year', 'notes']) {
      if (req.body[f] !== undefined) { updates.push(`${f} = ?`); params.push(req.body[f]); }
    }
    if (req.body.fee_group_id !== undefined) {
      const fg = getDb().prepare('SELECT id FROM fee_groups WHERE id = ?').get(Number(req.body.fee_group_id));
      if (!fg) return res.status(400).json({ error: 'fee_group_id not found' });
      updates.push('fee_group_id = ?'); params.push(fg.id);
    }
    if (updates.length === 0) return res.status(400).json({ error: 'No updatable fields supplied' });
    updates.push(`updated_at = datetime('now','localtime')`, `sync_status = 'pending'`);
    params.push(req.params.uid);
    getDb().prepare(`UPDATE fee_plans SET ${updates.join(', ')} WHERE uid = ?`).run(...params);
    await AuditService.log(req, 'UPDATE_FEE_PLAN', { entityType: 'fee_plan', entityId: req.params.uid });
    res.json(getDb().prepare('SELECT * FROM fee_plans WHERE uid = ?').get(req.params.uid));
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

router.delete('/plans/:uid', auth, feesWrite, async (req, res) => {
  try {
    const existing = getDb().prepare('SELECT id FROM fee_plans WHERE uid = ? AND is_deleted = 0').get(req.params.uid);
    if (!existing) return res.status(404).json({ error: 'Fee plan not found' });
    getDb().prepare(`
      UPDATE fee_plans SET is_deleted = 1, updated_at = datetime('now','localtime'),
                          sync_status = 'pending', last_synced_at = NULL WHERE uid = ?
    `).run(req.params.uid);
    getDb().prepare(`
      UPDATE fee_plan_installments SET is_deleted = 1, sync_status = 'pending', last_synced_at = NULL
      WHERE fee_plan_id = ?
    `).run(existing.id);
    await AuditService.log(req, 'DELETE_FEE_PLAN', { entityType: 'fee_plan', entityId: req.params.uid });
    res.json({ success: true });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// Replaces the installments on a plan in one shot. Total amount stays on the plan.
router.put('/plans/:uid/installments', auth, feesWrite, async (req, res) => {
  try {
    const plan = getDb().prepare('SELECT id FROM fee_plans WHERE uid = ? AND is_deleted = 0').get(req.params.uid);
    if (!plan) return res.status(404).json({ error: 'Fee plan not found' });
    const items = Array.isArray(req.body.installments) ? req.body.installments : null;
    if (!items || items.length === 0) return res.status(400).json({ error: 'installments[] is required' });

    const tx = getDb().transaction(() => {
      getDb().prepare('UPDATE fee_plan_installments SET is_deleted = 1, sync_status = ? WHERE fee_plan_id = ?')
        .run('pending', plan.id);
      const ins = getDb().prepare(`
        INSERT INTO fee_plan_installments (uid, fee_plan_id, sequence, label, amount, due_date)
        VALUES (?, ?, ?, ?, ?, ?)
      `);
      items.forEach((it, idx) => {
        ins.run(uuidv4(), plan.id, idx + 1, it.label || `Installment ${idx + 1}`,
                Number(it.amount) || 0, it.due_date || null);
      });
      getDb().prepare('UPDATE fee_plans SET installments_count = ?, updated_at = datetime(\'now\',\'localtime\'), sync_status = ? WHERE id = ?')
        .run(items.length, 'pending', plan.id);
    });
    tx();
    res.json({ success: true, installments_count: items.length });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ DISCOUNTS ============

router.get('/discounts', auth, (req, res) => {
  try {
    const { scope, scope_id, academic_year } = req.query;
    let sql = 'SELECT * FROM discounts WHERE is_deleted = 0';
    const params = [];
    if (scope) { sql += ' AND scope = ?'; params.push(scope); }
    if (scope_id !== undefined && scope_id !== '') { sql += ' AND scope_id = ?'; params.push(Number(scope_id)); }
    if (academic_year) { sql += ' AND academic_year = ?'; params.push(academic_year); }
    sql += ' ORDER BY created_at DESC';
    res.json(getDb().prepare(sql).all(...params));
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

router.get('/discounts/:uid', auth, (req, res) => {
  try {
    const row = getDb().prepare('SELECT * FROM discounts WHERE uid = ? AND is_deleted = 0').get(req.params.uid);
    if (!row) return res.status(404).json({ error: 'Discount not found' });
    res.json(row);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

router.post('/discounts', auth, feesWrite, async (req, res) => {
  try {
    const { name, kind, value, scope, scope_id, reason, valid_from, valid_to, academic_year } = req.body;
    if (!name || !kind || !value || !scope) {
      return res.status(400).json({ error: 'name, kind, value, scope are required' });
    }
    if (!['percent', 'fixed'].includes(kind)) return res.status(400).json({ error: 'kind must be percent|fixed' });
    if (!['student', 'group', 'fee_group', 'all'].includes(scope)) {
      return res.status(400).json({ error: 'scope must be student|group|fee_group|all' });
    }
    if (scope !== 'all' && !scope_id) {
      return res.status(400).json({ error: 'scope_id is required for non-"all" scopes' });
    }

    const uid = uuidv4();
    getDb().prepare(`
      INSERT INTO discounts (
        uid, name, kind, value, scope, scope_id, reason, valid_from, valid_to,
        academic_year, created_by
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      uid, name, kind, Number(value), scope,
      scope === 'all' ? null : Number(scope_id),
      reason || null,
      valid_from || null, valid_to || null,
      academic_year || null,
      req.user.id,
    );

    await AuditService.log(req, 'CREATE_DISCOUNT', { entityType: 'discount', entityId: uid, newData: { name, scope } });
    res.status(201).json(getDb().prepare('SELECT * FROM discounts WHERE uid = ?').get(uid));
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

router.patch('/discounts/:uid', auth, feesWrite, async (req, res) => {
  try {
    const existing = getDb().prepare('SELECT id FROM discounts WHERE uid = ? AND is_deleted = 0').get(req.params.uid);
    if (!existing) return res.status(404).json({ error: 'Discount not found' });
    const updates = [];
    const params = [];
    for (const f of ['name', 'kind', 'value', 'scope', 'scope_id', 'reason', 'valid_from', 'valid_to', 'academic_year']) {
      if (req.body[f] !== undefined) { updates.push(`${f} = ?`); params.push(req.body[f]); }
    }
    if (updates.length === 0) return res.status(400).json({ error: 'No updatable fields supplied' });
    updates.push(`updated_at = datetime('now','localtime')`, `sync_status = 'pending'`);
    params.push(req.params.uid);
    getDb().prepare(`UPDATE discounts SET ${updates.join(', ')} WHERE uid = ?`).run(...params);
    await AuditService.log(req, 'UPDATE_DISCOUNT', { entityType: 'discount', entityId: req.params.uid });
    res.json(getDb().prepare('SELECT * FROM discounts WHERE uid = ?').get(req.params.uid));
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

router.delete('/discounts/:uid', auth, feesWrite, async (req, res) => {
  try {
    const existing = getDb().prepare('SELECT id FROM discounts WHERE uid = ? AND is_deleted = 0').get(req.params.uid);
    if (!existing) return res.status(404).json({ error: 'Discount not found' });
    getDb().prepare(`
      UPDATE discounts SET is_deleted = 1, updated_at = datetime('now','localtime'),
                          sync_status = 'pending', last_synced_at = NULL WHERE uid = ?
    `).run(req.params.uid);
    getDb().prepare(`
      UPDATE student_fee_discounts SET is_deleted = 1, sync_status = 'pending', last_synced_at = NULL
      WHERE discount_id = ?
    `).run(existing.id);
    res.json({ success: true });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ FEE STRUCTURE (FR-SMS4 — read-only meta view) ============
//
// Returns the school's complete fee structure for an academic year: every
// active fee group, every fee plan with its installments, and every active
// discount. Used by the parent / student / admin dashboards as a single
// round-trip when they need to render the "what does the school charge"
// screen. Optional `?academic_year=2026` filter; defaults to current year.

router.get('/structure', auth, (req, res) => {
  try {
    const db = getDb();
    const academicYear = req.query.academic_year || String(new Date().getFullYear());

    const groups = db.prepare(`
      SELECT * FROM fee_groups WHERE is_deleted = 0 ORDER BY name
    `).all();

    const plans = db.prepare(`
      SELECT p.*, fg.name AS fee_name, fg.type AS fee_type,
             c.name AS class_name,
             (SELECT COUNT(*) FROM fee_plan_installments i
              WHERE i.fee_plan_id = p.id AND i.is_deleted = 0) AS installments_count_actual
      FROM fee_plans p
      JOIN fee_groups fg ON p.fee_group_id = fg.id
      LEFT JOIN classes c ON p.auto_apply_to_class_id = c.id
      WHERE p.is_deleted = 0
        AND (p.academic_year = ? OR p.academic_year IS NULL)
      ORDER BY p.start_date DESC, p.id DESC
    `).all(academicYear);

    const discounts = db.prepare(`
      SELECT * FROM discounts
      WHERE is_deleted = 0
        AND (academic_year = ? OR academic_year IS NULL)
        AND (valid_from IS NULL OR valid_from <= date('now'))
        AND (valid_to IS NULL OR valid_to >= date('now'))
      ORDER BY created_at DESC
    `).all(academicYear);

    // Per-class outstanding balance summary (helps admins see at a glance
    // which classes are behind on payments).
    const outstandingRows = db.prepare(`
      SELECT
        c.id AS class_id,
        c.name AS class_name,
        c.section,
        sf.student_id,
        sf.amount,
        sf.fine_amount,
        sf.discount_amount,
        sf.paid_amount
      FROM classes c
      JOIN enrollments e ON e.class_id = c.id AND e.status = 'active' AND e.is_deleted = 0
      JOIN student_fees sf ON sf.student_id = e.student_id AND sf.is_deleted = 0
      WHERE c.is_deleted = 0
    `).all();

    const outstandingByClass = new Map();
    for (const row of outstandingRows) {
      let summary = outstandingByClass.get(row.class_id);
      if (!summary) {
        summary = {
          class_id: row.class_id,
          class_name: row.class_name,
          section: row.section,
          student_ids: new Set(),
          total_outstanding: 0,
        };
        outstandingByClass.set(row.class_id, summary);
      }
      summary.student_ids.add(row.student_id);
      summary.total_outstanding += outstandingForFee(row).outstanding;
    }

    const outstanding = Array.from(outstandingByClass.values())
      .map((summary) => ({
        class_id: summary.class_id,
        class_name: summary.class_name,
        section: summary.section,
        students_with_fees: summary.student_ids.size,
        total_outstanding: summary.total_outstanding,
      }))
      .sort((a, b) => b.total_outstanding - a.total_outstanding);

    res.json({
      academic_year: academicYear,
      generated_at: new Date().toISOString(),
      fee_groups: groups,
      fee_plans: plans,
      active_discounts: discounts,
      outstanding_by_class: outstanding,
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ INVOICES (FR-SMS4 — read-only alias to finance-invoices) ============
//
// The canonical invoice CRUD lives in finance-invoices.controller.js
// (mounted at /api/finance/invoices). This block re-exposes a read-only
// view under /api/fees/invoices so the SRS surface is reachable from
// both prefixes. Admins/bursar/accountants can list; students/parents
// can read their own (parent must be linked via parent_students).

router.get('/invoices', auth, (req, res) => {
  try {
    if (!['school_admin', 'systems_admin', 'principal', 'bursar', 'accountant', 'hr'].includes(req.user.role)) {
      return res.status(403).json({ error: 'Finance / accounting role required' });
    }
    const { student_uid, status, fee_group_id, period_label, search, limit, offset } = req.query;
    let sql = `
      SELECT i.*, u.first_name AS student_first_name, u.last_name AS student_last_name,
             u.uid AS student_uid_link, fg.name AS fee_name
      FROM invoices i
      JOIN users u ON i.student_id = u.id
      LEFT JOIN fee_groups fg ON i.fee_group_id = fg.id
      WHERE i.is_deleted = 0
    `;
    const params = [];
    if (student_uid)   { sql += ' AND u.uid = ?';          params.push(student_uid); }
    if (status)       { sql += ' AND i.status = ?';       params.push(status); }
    if (fee_group_id) { sql += ' AND i.fee_group_id = ?';  params.push(Number(fee_group_id)); }
    if (period_label) { sql += ' AND i.period_label = ?';  params.push(period_label); }
    if (search)       {
      sql += ' AND (u.first_name LIKE ? OR u.last_name LIKE ? OR i.invoice_number LIKE ?)';
      const q = `%${search}%`; params.push(q, q, q);
    }
    sql += ' ORDER BY i.issued_at DESC, i.id DESC';
    if (limit)  { sql += ` LIMIT ${Number(limit)}`; }
    if (offset) { sql += ` OFFSET ${Number(offset)}`; }
    res.json(getDb().prepare(sql).all(...params));
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

router.get('/invoices/:uid', auth, (req, res) => {
  try {
    const row = getDb().prepare(`
      SELECT i.*, u.first_name AS student_first_name, u.last_name AS student_last_name,
             u.uid AS student_uid_link, fg.name AS fee_name
      FROM invoices i
      JOIN users u ON i.student_id = u.id
      LEFT JOIN fee_groups fg ON i.fee_group_id = fg.id
      WHERE i.uid = ? AND i.is_deleted = 0
    `).get(req.params.uid);
    if (!row) return res.status(404).json({ error: 'Invoice not found' });
    const financeRoles = ['school_admin', 'systems_admin', 'principal', 'bursar', 'accountant', 'hr'];
    const isOwner = req.user.role === 'student' && row.student_id === req.user.id;
    const isLinkedParent = req.user.role === 'parent' && getDb().prepare(
      'SELECT 1 FROM parent_students WHERE parent_id = ? AND student_id = ? AND is_deleted = 0'
    ).get(req.user.id, row.student_id);
    if (!financeRoles.includes(req.user.role) && !isOwner && !isLinkedParent) {
      return res.status(403).json({ error: 'Access denied' });
    }
    res.json({ ...row, ...outstandingForInvoice(row) });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// ============ MANUAL PAYMENT RECORDING (FR-SMS4) ============
//
// POST /api/fees/pay
// Records a manual (non-Paynow) payment against a student_fee. Atomically
// updates the fee's paid_amount + status and — if a recent invoice links
// back to this student+fee_group — the invoice's paid_amount + status.
// RBAC: feesWrite (school_admin, systems_admin, principal, bursar, accountant).
//
// Body: { student_fee_id, amount, reference?, method? }
//   method: 'cash' | 'bank' | 'mobile_money' | 'other'.
//     The DB CHECK constraint accepts 'cash'|'bank'|'card'|'paynow'|
//     'ecocash'|'internal_transfer'; we map 'mobile_money' → 'ecocash'
//     (Zimbabwean mobile money == EcoCash in practice) and reject 'other'
//     with 400 since no schema value covers it.
//   reference: optional. Defaults to MANUAL-<ms>-<rand>. Used as the
//     idempotency token (caller-supplied references are unique-indexed
//     via the UNIQUE-ish behaviour enforced by callers and by retry
//     semantics downstream).
router.post('/pay', auth, feesWrite, async (req, res) => {
  const db = getDb();
  const { student_fee_id, amount, reference, method } = req.body || {};

  // --- Validation -------------------------------------------------------
  const studentFeeIdNum = Number(student_fee_id);
  if (!Number.isFinite(studentFeeIdNum) || studentFeeIdNum <= 0) {
    return res.status(400).json({ error: 'student_fee_id is required and must be a positive number' });
  }
  const amountNum = Number(amount);
  if (!Number.isFinite(amountNum) || amountNum <= 0) {
    return res.status(400).json({ error: 'amount must be a positive number' });
  }

  // Load the student_fee row. 404 if missing/soft-deleted.
  const feeRow = db.prepare(`
    SELECT id, uid, student_id, fee_group_id, amount, paid_amount,
           discount_amount, fine_amount, status
    FROM student_fees
    WHERE id = ? AND is_deleted = 0
  `).get(studentFeeIdNum);
  if (!feeRow) {
    return res.status(404).json({ error: 'Student fee not found' });
  }

  // Compute outstanding using the same formula the rest of the fees surface
  // uses (no shared helper exists in this branch — inline the math).
  const totalDue = (feeRow.amount || 0) + (feeRow.fine_amount || 0) - (feeRow.discount_amount || 0);
  const outstanding = Math.max(0, totalDue - (feeRow.paid_amount || 0));
  if (amountNum > outstanding) {
    return res.status(400).json({ error: `amount exceeds outstanding balance of ${outstanding}` });
  }

  // Method validation + mapping. Default = 'cash'.
  const ALLOWED_DB_METHODS = new Set(['cash', 'bank', 'card', 'paynow', 'ecocash', 'internal_transfer']);
  const METHOD_MAP = { cash: 'cash', bank: 'bank', mobile_money: 'ecocash' };
  const suppliedMethod = method == null ? 'cash' : String(method).toLowerCase();
  let storedMethod;
  if (Object.prototype.hasOwnProperty.call(METHOD_MAP, suppliedMethod)) {
    storedMethod = METHOD_MAP[suppliedMethod];
  } else if (ALLOWED_DB_METHODS.has(suppliedMethod)) {
    storedMethod = suppliedMethod;
  } else {
    return res.status(400).json({ error: `method must be one of cash, bank, mobile_money` });
  }

  // Reference: optional, default to MANUAL-<timestamp>-<random>. Trim to fit
  // the column comfortably (TEXT, no length cap, but keep it short).
  const fallbackRef = `MANUAL-${Date.now()}-${Math.random().toString(36).slice(2, 8).toUpperCase()}`;
  const finalReference = (reference && String(reference).trim()) || fallbackRef;

  // --- Action — atomic via db.transaction() ------------------------------
  const paymentUid = uuidv4();
  let invoiceUid = null;
  let newPaidAmount = feeRow.paid_amount;
  let newStatus = feeRow.status;

  try {
    db.transaction(() => {
      // (1) Insert the payment row.
      db.prepare(`
        INSERT INTO payments (
          uid, student_fee_id, amount, payment_method, reference_number,
          collected_by, status, paid_at, sync_status, last_synced_at
        ) VALUES (?, ?, ?, ?, ?, ?, 'completed', datetime('now','localtime'), 'pending', NULL)
      `).run(paymentUid, feeRow.id, amountNum, storedMethod, finalReference, req.user.id);

      // (2) Update the student_fee: cap paid_amount at totalDue; recompute status.
      newPaidAmount = Math.min((feeRow.paid_amount || 0) + amountNum, totalDue);
      newStatus = newPaidAmount >= totalDue
        ? 'paid'
        : (newPaidAmount > 0 ? 'partial' : feeRow.status);
      const paidAtClause = newStatus === 'paid' ? `paid_at = datetime('now','localtime'),` : '';

      db.prepare(`
        UPDATE student_fees
        SET paid_amount = ?,
            status = ?,
            ${paidAtClause}
            updated_at = datetime('now','localtime'),
            sync_status = 'pending'
        WHERE id = ?
      `).run(newPaidAmount, newStatus, feeRow.id);

      // (3) Best-effort invoice link. There's no `student_fee_invoice` link
      //     table in this branch, so we match on student_id + fee_group_id
      //     and pick the most recent non-deleted invoice. If none, skip.
      const invoiceRow = db.prepare(`
        SELECT uid, total, paid_amount
        FROM invoices
        WHERE student_id = ? AND fee_group_id = ? AND is_deleted = 0
        ORDER BY issued_at DESC, id DESC
        LIMIT 1
      `).get(feeRow.student_id, feeRow.fee_group_id);

      if (invoiceRow) {
        const newInvoicePaid = Math.min((invoiceRow.paid_amount || 0) + amountNum, invoiceRow.total || 0);
        const newInvoiceStatus = newInvoicePaid >= (invoiceRow.total || 0)
          ? 'paid'
          : (newInvoicePaid > 0 ? 'partial' : 'issued');
        db.prepare(`
          UPDATE invoices
          SET paid_amount = ?,
              status = ?,
              updated_at = datetime('now','localtime'),
              sync_status = 'pending'
          WHERE uid = ?
        `).run(newInvoicePaid, newInvoiceStatus, invoiceRow.uid);
        invoiceUid = invoiceRow.uid;
      }
    })();
  } catch (err) {
    // UNIQUE conflicts on uid / reference_number surface here.
    if (err && (err.code === 'SQLITE_CONSTRAINT_UNIQUE' || /UNIQUE/i.test(err.message || ''))) {
      return res.status(409).json({ error: 'Duplicate reference — payment already recorded' });
    }
    console.error('POST /api/fees/pay failed:', err);
    return res.status(500).json({ error: 'Failed to record payment' });
  }

  // Audit log (outside the transaction — non-critical, and uses its own DB handle).
  await AuditService.log(req, 'MANUAL_PAYMENT', {
    entityType: 'payment',
    entityId: paymentUid,
    newData: {
      student_fee_id: feeRow.id,
      amount: amountNum,
      reference: finalReference,
      method: storedMethod,
    },
  });

  const outstandingAfter = Math.max(0, totalDue - newPaidAmount);
  return res.status(201).json({
    success: true,
    payment_id: paymentUid,
    student_fee_id: feeRow.id,
    new_paid_amount: newPaidAmount,
    outstanding_after: outstandingAfter,
    invoice_updated: invoiceUid,
  });
});

module.exports = router;
