redlight/server/routes/analytics.js

import { Router } from 'express';
import crypto from 'crypto';
import ExcelJS from 'exceljs';
import PDFDocument from 'pdfkit';
import { getDb } from '../config/database.js';
import { authenticateToken } from '../middleware/auth.js';
import { log } from '../config/logger.js';
import { getAnalyticsToken } from '../config/bbb.js';

const router = Router();

// POST /api/analytics/callback/:uid?token=... - BBB Learning Analytics callback (token-secured)
router.post('/callback/:uid', async (req, res) => {
  try {
    const { token } = req.query;
    const expectedToken = getAnalyticsToken(req.params.uid);

    // Constant-time comparison to prevent timing attacks
    if (!token || token.length !== expectedToken.length ||
        !crypto.timingSafeEqual(Buffer.from(token), Buffer.from(expectedToken))) {
      return res.status(403).json({ error: 'Invalid token' });
    }

    const db = getDb();
    const room = await db.get('SELECT id, uid, learning_analytics FROM rooms WHERE uid = ?', [req.params.uid]);

    if (!room) {
      return res.status(404).json({ error: 'Room not found' });
    }

    if (!room.learning_analytics) {
      return res.status(403).json({ error: 'Learning analytics not enabled for this room' });
    }

    const data = req.body;
    if (!data || typeof data !== 'object') {
      return res.status(400).json({ error: 'Invalid analytics data' });
    }

    // Extract meeting info from BBB learning analytics payload
    // Format: { meeting_id, internal_meeting_id, data: { metadata: { meeting_name }, duration, attendees, ... } }
    const meetingId = data.internal_meeting_id || data.meeting_id || room.uid;
    const meetingName = data.data?.metadata?.meeting_name || data.meeting_id || room.uid;

    // Upsert: update if same meeting already exists (BBB sends updates during the meeting)
    const existing = await db.get(
      'SELECT id FROM learning_analytics_data WHERE room_id = ? AND meeting_id = ?',
      [room.id, meetingId]
    );

    const jsonData = JSON.stringify(data);

    if (existing) {
      await db.run(
        'UPDATE learning_analytics_data SET data = ?, meeting_name = ?, created_at = CURRENT_TIMESTAMP WHERE id = ?',
        [jsonData, meetingName, existing.id]
      );
    } else {
      await db.run(
        'INSERT INTO learning_analytics_data (room_id, meeting_id, meeting_name, data) VALUES (?, ?, ?, ?)',
        [room.id, meetingId, meetingName, jsonData]
      );
    }

    log.server.info(`Analytics callback received for room ${room.uid} (meeting: ${meetingId})`);
    res.json({ success: true });
  } catch (err) {
    log.server.error(`Analytics callback error: ${err.message}`);
    res.status(500).json({ error: 'Error processing analytics data' });
  }
});

// GET /api/analytics/room/:uid - Get analytics for a room (authenticated)
router.get('/room/:uid', authenticateToken, async (req, res) => {
  try {
    const db = getDb();
    const room = await db.get('SELECT id, user_id, analytics_visibility FROM rooms WHERE uid = ?', [req.params.uid]);

    if (!room) {
      return res.status(404).json({ error: 'Room not found' });
    }

    // Check access: owner, shared (if visibility allows), or admin
    if (room.user_id !== req.user.id && req.user.role !== 'admin') {
      if (room.analytics_visibility !== 'shared') {
        return res.status(403).json({ error: 'No permission to view analytics for this room' });
      }
      const share = await db.get('SELECT id FROM room_shares WHERE room_id = ? AND user_id = ?', [room.id, req.user.id]);
      if (!share) {
        return res.status(403).json({ error: 'No permission to view analytics for this room' });
      }
    }

    const rows = await db.all(
      'SELECT id, meeting_id, meeting_name, data, created_at FROM learning_analytics_data WHERE room_id = ? ORDER BY created_at DESC',
      [room.id]
    );

    const analytics = rows.map(row => ({
      id: row.id,
      meetingId: row.meeting_id,
      meetingName: row.meeting_name,
      data: typeof row.data === 'string' ? JSON.parse(row.data) : row.data,
      createdAt: row.created_at,
    }));

    res.json({ analytics });
  } catch (err) {
    log.server.error(`Get analytics error: ${err.message}`);
    res.status(500).json({ error: 'Error fetching analytics' });
  }
});

// DELETE /api/analytics/:id - Delete analytics entry (authenticated, owner only)
router.delete('/:id', authenticateToken, async (req, res) => {
  try {
    const db = getDb();
    const entry = await db.get(
      `SELECT la.id, la.room_id, r.user_id
       FROM learning_analytics_data la
       JOIN rooms r ON la.room_id = r.id
       WHERE la.id = ?`,
      [req.params.id]
    );

    if (!entry) {
      return res.status(404).json({ error: 'Analytics entry not found' });
    }

    if (entry.user_id !== req.user.id && req.user.role !== 'admin') {
      return res.status(403).json({ error: 'No permission to delete this entry' });
    }

    await db.run('DELETE FROM learning_analytics_data WHERE id = ?', [req.params.id]);
    res.json({ success: true });
  } catch (err) {
    log.server.error(`Delete analytics error: ${err.message}`);
    res.status(500).json({ error: 'Error deleting analytics entry' });
  }
});

// Helper: extract flat attendee rows from analytics entry
function extractRows(entry) {
  const data = typeof entry.data === 'string' ? JSON.parse(entry.data) : entry.data;
  const attendees = data?.data?.attendees || [];
  const meetingName = data?.data?.metadata?.meeting_name || entry.meeting_name || '';
  const meetingDuration = data?.data?.duration || 0;
  const meetingStart = data?.data?.start || '';
  const meetingFinish = data?.data?.finish || '';

  return attendees.map(a => ({
    meetingName,
    meetingStart,
    meetingFinish,
    meetingDuration,
    name: a.name || '',
    role: a.moderator ? 'Moderator' : 'Viewer',
    duration: a.duration || 0,
    talkTime: a.engagement?.talk_time || 0,
    chats: a.engagement?.chats || 0,
    talks: a.engagement?.talks || 0,
    raiseHand: a.engagement?.raisehand || 0,
    emojis: a.engagement?.emojis || 0,
    pollVotes: a.engagement?.poll_votes || 0,
  }));
}

const COLUMNS = [
  { header: 'Meeting', key: 'meetingName', width: 25 },
  { header: 'Start', key: 'meetingStart', width: 20 },
  { header: 'End', key: 'meetingFinish', width: 20 },
  { header: 'Meeting Duration (s)', key: 'meetingDuration', width: 18 },
  { header: 'Name', key: 'name', width: 25 },
  { header: 'Role', key: 'role', width: 12 },
  { header: 'Duration (s)', key: 'duration', width: 14 },
  { header: 'Talk Time (s)', key: 'talkTime', width: 14 },
  { header: 'Chats', key: 'chats', width: 8 },
  { header: 'Talks', key: 'talks', width: 8 },
  { header: 'Raise Hand', key: 'raiseHand', width: 12 },
  { header: 'Emojis', key: 'emojis', width: 8 },
  { header: 'Poll Votes', key: 'pollVotes', width: 10 },
];

// GET /api/analytics/:id/export/:format - Export a single analytics entry (csv, xlsx, pdf)
router.get('/:id/export/:format', authenticateToken, async (req, res) => {
  try {
    const format = req.params.format;
    if (!['csv', 'xlsx', 'pdf'].includes(format)) {
      return res.status(400).json({ error: 'Unsupported format. Use csv, xlsx, or pdf.' });
    }

    const db = getDb();
    const entry = await db.get(
      `SELECT la.*, r.user_id, r.analytics_visibility
       FROM learning_analytics_data la
       JOIN rooms r ON la.room_id = r.id
       WHERE la.id = ?`,
      [req.params.id]
    );

    if (!entry) {
      return res.status(404).json({ error: 'Analytics entry not found' });
    }

    if (entry.user_id !== req.user.id && req.user.role !== 'admin') {
      if (entry.analytics_visibility !== 'shared') {
        return res.status(403).json({ error: 'No permission to export this entry' });
      }
      const share = await db.get('SELECT id FROM room_shares WHERE room_id = ? AND user_id = ?', [entry.room_id, req.user.id]);
      if (!share) {
        return res.status(403).json({ error: 'No permission to export this entry' });
      }
    }

    const rows = extractRows(entry);
    const safeName = (entry.meeting_name || 'analytics').replace(/[^a-zA-Z0-9_-]/g, '_');

    if (format === 'csv') {
      const header = COLUMNS.map(c => c.header).join(',');
      const csvRows = rows.map(r =>
        COLUMNS.map(c => {
          const val = r[c.key];
          if (typeof val === 'string' && (val.includes(',') || val.includes('"') || val.includes('\n'))) {
            return '"' + val.replace(/"/g, '""') + '"';
          }
          return val;
        }).join(',')
      );
      const csv = [header, ...csvRows].join('\n');
      res.setHeader('Content-Type', 'text/csv; charset=utf-8');
      res.setHeader('Content-Disposition', `attachment; filename="${safeName}.csv"`);
      return res.send('\uFEFF' + csv); // BOM for Excel UTF-8
    }

    if (format === 'xlsx') {
      const workbook = new ExcelJS.Workbook();
      const sheet = workbook.addWorksheet('Analytics');
      sheet.columns = COLUMNS;
      rows.forEach(r => sheet.addRow(r));
      // Style header row
      sheet.getRow(1).font = { bold: true };
      sheet.getRow(1).fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFE0E0E0' } };

      res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
      res.setHeader('Content-Disposition', `attachment; filename="${safeName}.xlsx"`);
      await workbook.xlsx.write(res);
      return res.end();
    }

    if (format === 'pdf') {
      const doc = new PDFDocument({ size: 'A4', layout: 'landscape', margin: 30 });
      res.setHeader('Content-Type', 'application/pdf');
      res.setHeader('Content-Disposition', `attachment; filename="${safeName}.pdf"`);
      doc.pipe(res);

      // Title
      doc.fontSize(16).text(entry.meeting_name || 'Learning Analytics', { align: 'center' });
      doc.moveDown(0.5);

      // Table header columns for PDF (subset for readability)
      const pdfCols = [
        { header: 'Name', key: 'name', width: 120 },
        { header: 'Role', key: 'role', width: 65 },
        { header: 'Duration (s)', key: 'duration', width: 75 },
        { header: 'Talk Time (s)', key: 'talkTime', width: 75 },
        { header: 'Chats', key: 'chats', width: 50 },
        { header: 'Talks', key: 'talks', width: 50 },
        { header: 'Raise Hand', key: 'raiseHand', width: 65 },
        { header: 'Emojis', key: 'emojis', width: 50 },
        { header: 'Poll Votes', key: 'pollVotes', width: 60 },
      ];

      const startX = doc.x;
      let y = doc.y;

      // Header
      doc.fontSize(8).font('Helvetica-Bold');
      pdfCols.forEach((col, i) => {
        const x = startX + pdfCols.slice(0, i).reduce((s, c) => s + c.width, 0);
        doc.text(col.header, x, y, { width: col.width, align: 'left' });
      });
      y += 14;
      doc.moveTo(startX, y).lineTo(startX + pdfCols.reduce((s, c) => s + c.width, 0), y).stroke();
      y += 4;

      // Rows
      doc.font('Helvetica').fontSize(8);
      rows.forEach(r => {
        if (y > doc.page.height - 50) {
          doc.addPage();
          y = 30;
        }
        pdfCols.forEach((col, i) => {
          const x = startX + pdfCols.slice(0, i).reduce((s, c) => s + c.width, 0);
          doc.text(String(r[col.key]), x, y, { width: col.width, align: 'left' });
        });
        y += 14;
      });

      doc.end();
      return;
    }
  } catch (err) {
    log.server.error(`Export analytics error: ${err.message}`);
    res.status(500).json({ error: 'Error exporting analytics data' });
  }
});

export default router;