/**
 * Finance — Trips Controller (§10a)
 *
 * CRUD on `trips` plus the per-student payment workflow on `trip_payments`.
 * Trip payments are auto-created on student assignment so the operator
 * doesn't have to back-fill them.
 */

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

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

const router = express.Router();

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) {
    return res.status(401).json({ error: 'Invalid token' });
  }
};

const financeAccess = (req, res, next) => {
  if (!['school_admin', 'systems_admin', 'principal', 'bursar', 'accountant', 'hr'].includes(req.user.role)) {
    return res.status(403).json({ error: 'Finance / accounting role required' });
  }
  next();
};

// ============ TRIPS ============

router.get('/', auth, financeAccess, (req, res) => {
  try {
    const { status } = req.query;
    let sql = `
      SELECT t.*,
             (SELECT COUNT(*) FROM trip_payments tp WHERE tp.trip_id = t.id AND tp.is_deleted = 0) AS billed_count,
             (SELECT COUNT(*) FROM trip_payments tp WHERE tp.trip_id = t.id AND tp.status = 'paid' AND tp.is_deleted = 0) AS paid_count,
             (SELECT COALESCE(SUM(tp.paid_amount),0) FROM trip_payments tp WHERE tp.trip_id = t.id AND tp.is_deleted = 0) AS collected
      FROM trips t
      WHERE t.is_deleted = 0
    `;
    const params = [];
    if (status) { sql += ' AND t.status = ?'; params.push(status); }
    sql += ' ORDER BY t.departure_date DESC, t.id DESC';
    res.json(db.prepare(sql).all(...params));
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

router.get('/:uid', auth, financeAccess, (req, res) => {
  try {
    const head = db.prepare('SELECT * FROM trips WHERE uid = ? AND is_deleted = 0').get(req.params.uid);
    if (!head) return res.status(404).json({ error: 'Trip not found' });
    const students = db.prepare(`
      SELECT tp.*, u.first_name, u.last_name, u.email
      FROM trip_payments tp JOIN users u ON tp.student_id = u.id
      WHERE tp.trip_id = ? AND tp.is_deleted = 0
      ORDER BY u.first_name, u.last_name
    `).all(head.id);
    res.json({ ...head, students });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

router.post('/', auth, financeAccess, async (req, res) => {
  try {
    const { name, destination, departure_date, return_date, cost_per_student, total_capacity, status, description } = req.body;
    if (!name) return res.status(400).json({ error: 'name is required' });

    const uid = uuidv4();
    db.prepare(`
      INSERT INTO trips (
        uid, name, destination, departure_date, return_date,
        cost_per_student, total_capacity, status, description, created_by
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      uid, name, destination || null,
      departure_date || null, return_date || null,
      Number(cost_per_student) || 0,
      total_capacity ? Number(total_capacity) : null,
      ['draft', 'published', 'closed', 'completed', 'cancelled'].includes(status) ? status : 'draft',
      description || null,
      req.user.id,
    );
    await AuditService.log(req, 'CREATE_TRIP', { entityType: 'trip', entityId: uid, newData: { name, cost_per_student } });
    res.status(201).json(db.prepare('SELECT * FROM trips WHERE uid = ?').get(uid));
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

router.patch('/:uid', auth, financeAccess, async (req, res) => {
  try {
    const existing = db.prepare('SELECT id FROM trips WHERE uid = ? AND is_deleted = 0').get(req.params.uid);
    if (!existing) return res.status(404).json({ error: 'Trip not found' });

    const updates = [];
    const params = [];
    for (const f of ['name', 'destination', 'departure_date', 'return_date', 'description']) {
      if (req.body[f] !== undefined) { updates.push(`${f} = ?`); params.push(req.body[f]); }
    }
    if (req.body.cost_per_student !== undefined) { updates.push('cost_per_student = ?'); params.push(Number(req.body.cost_per_student) || 0); }
    if (req.body.total_capacity !== undefined)    { updates.push('total_capacity = ?');    params.push(req.body.total_capacity === null ? null : Number(req.body.total_capacity)); }
    if (req.body.status !== undefined) {
      if (!['draft', 'published', 'closed', 'completed', 'cancelled'].includes(req.body.status)) {
        return res.status(400).json({ error: 'invalid status' });
      }
      updates.push('status = ?'); params.push(req.body.status);
    }
    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);
    db.prepare(`UPDATE trips SET ${updates.join(', ')} WHERE uid = ?`).run(...params);
    await AuditService.log(req, 'UPDATE_TRIP', { entityType: 'trip', entityId: req.params.uid });
    res.json(db.prepare('SELECT * FROM trips WHERE uid = ?').get(req.params.uid));
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

router.delete('/:uid', auth, financeAccess, async (req, res) => {
  try {
    const existing = db.prepare('SELECT id, paid_count FROM trips WHERE uid = ?').get(req.params.uid);
    if (!existing) return res.status(404).json({ error: 'Trip not found' });
    if (existing.paid_count > 0) {
      return res.status(409).json({ error: 'Cannot delete: students have already paid for this trip.' });
    }
    db.prepare(`
      UPDATE trips SET is_deleted = 1, status = 'cancelled', updated_at = datetime('now','localtime'),
                       sync_status = 'pending', last_synced_at = NULL WHERE uid = ?
    `).run(req.params.uid);
    // Soft-delete the dangling trip_payments (none paid, by precondition).
    db.prepare(`
      UPDATE trip_payments SET is_deleted = 1, sync_status = 'pending', last_synced_at = NULL
      WHERE trip_id = ?
    `).run(existing.id);
    res.json({ success: true });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// ============ TRIP STUDENTS ============
//
// Bulk-add students. Body: { mode: 'ids'|'class'|'all', student_uids?: [],
// class_id?: number }. For each student not yet on the trip, insert a
// trip_payments row with invoiced_amount = trip.cost_per_student.
router.post('/:uid/students', auth, financeAccess, async (req, res) => {
  try {
    const trip = db.prepare('SELECT id, cost_per_student FROM trips WHERE uid = ? AND is_deleted = 0').get(req.params.uid);
    if (!trip) return res.status(404).json({ error: 'Trip not found' });

    const { mode, student_uids, class_id } = req.body;
    let studentIds = [];
    if (mode === 'ids') {
      if (!Array.isArray(student_uids) || student_uids.length === 0) {
        return res.status(400).json({ error: 'student_uids[] is required for mode=ids' });
      }
      const placeholders = student_uids.map(() => '?').join(',');
      studentIds = db.prepare(`SELECT id FROM users WHERE uid IN (${placeholders}) AND role = 'student' AND is_deleted = 0`).all(...student_uids).map((r) => r.id);
    } else if (mode === 'class') {
      if (!class_id) return res.status(400).json({ error: 'class_id is required for mode=class' });
      studentIds = db.prepare(`
        SELECT DISTINCT u.id FROM users u
        JOIN enrollments e ON e.student_id = u.id
        WHERE e.class_id = ? AND e.status = 'active' AND e.is_deleted = 0
          AND u.role = 'student' AND u.is_deleted = 0
      `).all(Number(class_id)).map((r) => r.id);
    } else if (mode === 'all') {
      studentIds = db.prepare(`SELECT id FROM users WHERE role = 'student' AND is_deleted = 0`).all().map((r) => r.id);
    } else {
      return res.status(400).json({ error: "mode must be one of 'ids' | 'class' | 'all'" });
    }

    if (studentIds.length === 0) return res.json({ added: 0, skipped: 0 });

    const insert = db.prepare(`
      INSERT INTO trip_payments (
        uid, trip_id, student_id, invoiced_amount, status, created_by
      ) VALUES (?, ?, ?, ?, 'pending', ?)
    `);
    let added = 0;
    let skipped = 0;
    const tx = db.transaction((ids) => {
      for (const sid of ids) {
        try {
          insert.run(uuidv4(), trip.id, sid, trip.cost_per_student, req.user.id);
          added++;
        } catch (e2) {
          // UNIQUE collision means the student is already on this trip → skip.
          if (String(e2.message).includes('UNIQUE')) skipped++;
          else throw e2;
        }
      }
    });
    tx(studentIds);

    await AuditService.log(req, 'ASSIGN_TRIP_STUDENTS', {
      entityType: 'trip', entityId: req.params.uid, newData: { mode, added, skipped },
    });

    res.json({ added, skipped, total_assigned: added + (db.prepare('SELECT COUNT(*) AS n FROM trip_payments WHERE trip_id = ? AND is_deleted = 0').get(trip.id).n) });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

router.delete('/:uid/students/:studentUid', auth, financeAccess, async (req, res) => {
  try {
    const trip = db.prepare('SELECT id FROM trips WHERE uid = ? AND is_deleted = 0').get(req.params.uid);
    if (!trip) return res.status(404).json({ error: 'Trip not found' });
    const student = db.prepare('SELECT id FROM users WHERE uid = ?').get(req.params.studentUid);
    if (!student) return res.status(404).json({ error: 'Student not found' });
    const tp = db.prepare('SELECT id, paid_amount, status FROM trip_payments WHERE trip_id = ? AND student_id = ? AND is_deleted = 0').get(trip.id, student.id);
    if (!tp) return res.status(404).json({ error: 'Trip payment not found' });
    if (Number(tp.paid_amount) > 0) return res.status(409).json({ error: 'Cannot remove a student who already paid' });

    db.prepare(`
      UPDATE trip_payments
      SET is_deleted = 1, status = 'cancelled', sync_status = 'pending', last_synced_at = NULL,
          updated_at = datetime('now','localtime')
      WHERE id = ?
    `).run(tp.id);
    res.json({ success: true });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// ============ TRIP PAYMENTS ============
//
// Mark a student's trip payment as paid/partial/refunded. Body: { amount,
// payment_method, reference_number, notes }. Records the transaction on
// `payments` (so bank-recon picks it up), updates trip_payments in lockstep.
router.post('/:uid/students/:studentUid/pay', auth, financeAccess, async (req, res) => {
  try {
    const trip = db.prepare('SELECT * FROM trips WHERE uid = ? AND is_deleted = 0').get(req.params.uid);
    if (!trip) return res.status(404).json({ error: 'Trip not found' });
    const student = db.prepare('SELECT id FROM users WHERE uid = ?').get(req.params.studentUid);
    if (!student) return res.status(404).json({ error: 'Student not found' });
    const tp = db.prepare('SELECT * FROM trip_payments WHERE trip_id = ? AND student_id = ? AND is_deleted = 0').get(trip.id, student.id);
    if (!tp) return res.status(404).json({ error: 'Student not on this trip' });

    const amount = Number(req.body.amount);
    if (!Number.isFinite(amount) || amount <= 0) return res.status(400).json({ error: 'amount must be > 0' });

    const remaining = Number(tp.invoiced_amount) - Number(tp.paid_amount);
    if (amount > remaining + 0.01) return res.status(400).json({ error: `amount exceeds remaining ${remaining.toFixed(2)}` });

    const newPaid = Number(tp.paid_amount) + amount;
    const newStatus = newPaid >= Number(tp.invoiced_amount) - 0.01 ? 'paid'
                    : newPaid > 0                       ? 'partial'
                    : 'pending';

    db.transaction(() => {
      // Reflect the receipt directly on trip_payments. A future §10c pass
      // can also insert a payments row (NULL student_fee_id) so bank-recon
      // picks trip receipts up; for now we keep trip receipts self-contained.
      db.prepare(`
        UPDATE trip_payments
        SET paid_amount = ?, status = ?, paid_at = datetime('now','localtime'),
            updated_at = datetime('now','localtime'), sync_status = 'pending'
        WHERE id = ?
      `).run(newPaid, newStatus, tp.id);
    })();

    await AuditService.log(req, 'PAY_TRIP', { entityType: 'trip_payment', entityId: tp.id, newData: { amount, newStatus } });
    res.json({ success: true, new_paid_amount: newPaid, status: newStatus });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

// Refund: not part of sprint scope; mark cancelled and recommend manual Paynow flow.
router.post('/:uid/students/:studentUid/refund', auth, financeAccess, async (req, res) => {
  try {
    const trip = db.prepare('SELECT id FROM trips WHERE uid = ?').get(req.params.uid);
    if (!trip) return res.status(404).json({ error: 'Trip not found' });
    const student = db.prepare('SELECT id FROM users WHERE uid = ?').get(req.params.studentUid);
    if (!student) return res.status(404).json({ error: 'Student not found' });
    const tp = db.prepare('SELECT id FROM trip_payments WHERE trip_id = ? AND student_id = ?').get(trip.id, student.id);
    if (!tp) return res.status(404).json({ error: 'Trip payment row not found' });

    db.prepare(`
      UPDATE trip_payments SET status = 'refunded', updated_at = datetime('now','localtime'),
                              sync_status = 'pending'
      WHERE id = ?
    `).run(tp.id);
    res.json({ success: true, status: 'refunded' });
  } catch (e) {
    res.status(500).json({ error: e.message });
  }
});

module.exports = router;
