← back to Dear Bubbe Admin
backend/routes/reports.js
575 lines
const express = require('express')
const User = require('../models/User')
const Session = require('../models/Session')
const Conversation = require('../models/Conversation')
const Alert = require('../models/Alert')
const fs = require('fs')
const path = require('path')
const moment = require('moment')
const router = express.Router()
// Generate comprehensive daily report
router.get('/daily', async (req, res) => {
try {
const { date } = req.query
const targetDate = date ? new Date(date) : new Date()
const startOfDay = new Date(targetDate.setHours(0, 0, 0, 0))
const endOfDay = new Date(targetDate.setHours(23, 59, 59, 999))
console.log(`Generating daily report for ${startOfDay.toDateString()}`)
// User activity
const userStats = {
newRegistrations: await User.countDocuments({
createdAt: { $gte: startOfDay, $lte: endOfDay }
}),
activeUsers: await Session.countDocuments({
startTime: { $gte: startOfDay, $lte: endOfDay }
}),
totalSessions: await Session.countDocuments({
startTime: { $gte: startOfDay, $lte: endOfDay }
})
}
// Conversation stats
const conversationStats = await Conversation.aggregate([
{
$match: {
timestamp: { $gte: startOfDay, $lte: endOfDay }
}
},
{
$group: {
_id: null,
totalConversations: { $sum: 1 },
uniqueUsers: { $addToSet: '$userId' },
avgResponseTime: { $avg: '$analysis.responseTime' },
sentimentBreakdown: {
positive: { $sum: { $cond: [{ $eq: ['$message.sentiment', 'positive'] }, 1, 0] } },
neutral: { $sum: { $cond: [{ $eq: ['$message.sentiment', 'neutral'] }, 1, 0] } },
negative: { $sum: { $cond: [{ $eq: ['$message.sentiment', 'negative'] }, 1, 0] } }
},
modeUsage: {
bubbe: { $sum: { $cond: [{ $eq: ['$response.mode', 'bubbe'] }, 1, 0] } },
meshugana: { $sum: { $cond: [{ $eq: ['$response.mode', 'meshugana'] }, 1, 0] } },
comedian: { $sum: { $cond: [{ $eq: ['$response.mode', 'comedian'] }, 1, 0] } }
},
voiceUsage: { $sum: { $cond: ['$response.voiceGenerated', 1, 0] } }
}
}
])
// Alert statistics
const alertStats = await Alert.aggregate([
{
$match: {
timestamp: { $gte: startOfDay, $lte: endOfDay }
}
},
{
$group: {
_id: null,
totalAlerts: { $sum: 1 },
bySeverity: {
critical: { $sum: { $cond: [{ $eq: ['$severity', 'critical'] }, 1, 0] } },
high: { $sum: { $cond: [{ $eq: ['$severity', 'high'] }, 1, 0] } },
medium: { $sum: { $cond: [{ $eq: ['$severity', 'medium'] }, 1, 0] } },
low: { $sum: { $cond: [{ $eq: ['$severity', 'low'] }, 1, 0] } }
},
byType: {
antisemitism: { $sum: { $cond: [{ $eq: ['$type', 'antisemitism'] }, 1, 0] } },
racism: { $sum: { $cond: [{ $eq: ['$type', 'racism'] }, 1, 0] } },
harassment: { $sum: { $cond: [{ $eq: ['$type', 'harassment'] }, 1, 0] } },
spam: { $sum: { $cond: [{ $eq: ['$type', 'spam'] }, 1, 0] } }
},
uniqueIPs: { $addToSet: '$userInfo.ip' },
uniqueUsers: { $addToSet: '$userInfo.userId' }
}
}
])
// Top topics
const topTopics = await Conversation.aggregate([
{
$match: {
timestamp: { $gte: startOfDay, $lte: endOfDay }
}
},
{
$unwind: '$topics'
},
{
$group: {
_id: '$topics',
count: { $sum: 1 }
}
},
{
$sort: { count: -1 }
},
{
$limit: 10
}
])
// Geographic distribution
const geoStats = await Session.aggregate([
{
$match: {
startTime: { $gte: startOfDay, $lte: endOfDay }
}
},
{
$group: {
_id: '$location.country',
sessions: { $sum: 1 },
uniqueUsers: { $addToSet: '$userId' }
}
},
{
$addFields: {
userCount: { $size: '$uniqueUsers' }
}
},
{
$sort: { sessions: -1 }
},
{
$limit: 10
}
])
const report = {
reportType: 'daily',
date: startOfDay.toDateString(),
generatedAt: new Date(),
generatedBy: req.user.userId,
userActivity: {
...userStats,
uniqueActiveUsers: conversationStats[0]?.uniqueUsers?.length || 0
},
conversations: conversationStats[0] || {},
alerts: {
...alertStats[0],
uniqueOffendingIPs: alertStats[0]?.uniqueIPs?.length || 0,
uniqueOffendingUsers: alertStats[0]?.uniqueUsers?.length || 0
},
topTopics,
geography: geoStats,
summary: {
totalInteractions: conversationStats[0]?.totalConversations || 0,
securityIncidents: alertStats[0]?.totalAlerts || 0,
userGrowth: userStats.newRegistrations,
engagement: conversationStats[0]?.totalConversations / Math.max(userStats.activeUsers, 1),
alertRate: (alertStats[0]?.totalAlerts || 0) / Math.max(conversationStats[0]?.totalConversations || 1, 1) * 100
}
}
res.json({
success: true,
report
})
} catch (error) {
console.error('Error generating daily report:', error)
res.status(500).json({
success: false,
error: 'Failed to generate daily report'
})
}
})
// Generate weekly report
router.get('/weekly', async (req, res) => {
try {
const { week } = req.query
const now = new Date()
const startOfWeek = week ? new Date(week) : new Date(now.setDate(now.getDate() - now.getDay()))
const endOfWeek = new Date(startOfWeek)
endOfWeek.setDate(endOfWeek.getDate() + 6)
endOfWeek.setHours(23, 59, 59, 999)
startOfWeek.setHours(0, 0, 0, 0)
// Daily breakdown for the week
const dailyStats = []
for (let i = 0; i < 7; i++) {
const dayStart = new Date(startOfWeek)
dayStart.setDate(dayStart.getDate() + i)
dayStart.setHours(0, 0, 0, 0)
const dayEnd = new Date(dayStart)
dayEnd.setHours(23, 59, 59, 999)
const dayStats = {
date: dayStart.toDateString(),
conversations: await Conversation.countDocuments({
timestamp: { $gte: dayStart, $lte: dayEnd }
}),
alerts: await Alert.countDocuments({
timestamp: { $gte: dayStart, $lte: dayEnd }
}),
sessions: await Session.countDocuments({
startTime: { $gte: dayStart, $lte: dayEnd }
})
}
dailyStats.push(dayStats)
}
// Week totals
const weekTotals = {
conversations: await Conversation.countDocuments({
timestamp: { $gte: startOfWeek, $lte: endOfWeek }
}),
alerts: await Alert.countDocuments({
timestamp: { $gte: startOfWeek, $lte: endOfWeek }
}),
newUsers: await User.countDocuments({
createdAt: { $gte: startOfWeek, $lte: endOfWeek }
}),
sessions: await Session.countDocuments({
startTime: { $gte: startOfWeek, $lte: endOfWeek }
})
}
// Top users of the week
const topUsers = await Conversation.aggregate([
{
$match: {
timestamp: { $gte: startOfWeek, $lte: endOfWeek }
}
},
{
$group: {
_id: '$userId',
messageCount: { $sum: 1 },
topics: { $addToSet: '$topics' }
}
},
{
$sort: { messageCount: -1 }
},
{
$limit: 10
}
])
const report = {
reportType: 'weekly',
weekStart: startOfWeek.toDateString(),
weekEnd: endOfWeek.toDateString(),
generatedAt: new Date(),
generatedBy: req.user.userId,
dailyBreakdown: dailyStats,
weekTotals,
topUsers,
trends: {
conversationTrend: calculateTrend(dailyStats.map(d => d.conversations)),
alertTrend: calculateTrend(dailyStats.map(d => d.alerts)),
sessionTrend: calculateTrend(dailyStats.map(d => d.sessions))
}
}
res.json({
success: true,
report
})
} catch (error) {
console.error('Error generating weekly report:', error)
res.status(500).json({
success: false,
error: 'Failed to generate weekly report'
})
}
})
// Generate security report
router.get('/security', async (req, res) => {
try {
const { timeframe = '7d' } = req.query
const now = new Date()
let startTime
switch (timeframe) {
case '24h':
startTime = new Date(now - 24 * 60 * 60 * 1000)
break
case '7d':
startTime = new Date(now - 7 * 24 * 60 * 60 * 1000)
break
case '30d':
startTime = new Date(now - 30 * 24 * 60 * 60 * 1000)
break
default:
startTime = new Date(now - 7 * 24 * 60 * 60 * 1000)
}
// Alert analysis
const alertAnalysis = await Alert.aggregate([
{
$match: {
timestamp: { $gte: startTime }
}
},
{
$group: {
_id: {
type: '$type',
severity: '$severity'
},
count: { $sum: 1 },
uniqueIPs: { $addToSet: '$userInfo.ip' },
uniqueUsers: { $addToSet: '$userInfo.userId' },
patterns: { $addToSet: '$violation.pattern' },
locations: { $addToSet: '$location.country' }
}
}
])
// Repeat offenders
const repeatOffenders = await Alert.aggregate([
{
$match: {
timestamp: { $gte: startTime }
}
},
{
$group: {
_id: '$userInfo.ip',
alertCount: { $sum: 1 },
severities: { $push: '$severity' },
types: { $push: '$type' },
locations: { $addToSet: '$location' },
userIds: { $addToSet: '$userInfo.userId' }
}
},
{
$match: {
alertCount: { $gte: 2 }
}
},
{
$sort: { alertCount: -1 }
},
{
$limit: 20
}
])
// Blocked users
const blockedUsers = await User.aggregate([
{
$match: {
'security.blocked': true,
'security.blockedAt': { $gte: startTime }
}
},
{
$group: {
_id: '$security.blockReason',
count: { $sum: 1 },
users: { $push: { userId: '$userId', blockedAt: '$security.blockedAt' } }
}
}
])
// Geographic threat distribution
const geoThreats = await Alert.aggregate([
{
$match: {
timestamp: { $gte: startTime },
severity: { $in: ['high', 'critical'] }
}
},
{
$group: {
_id: '$location.country',
alerts: { $sum: 1 },
uniqueIPs: { $addToSet: '$userInfo.ip' },
types: { $addToSet: '$type' }
}
},
{
$addFields: {
uniqueIPCount: { $size: '$uniqueIPs' }
}
},
{
$sort: { alerts: -1 }
}
])
const report = {
reportType: 'security',
timeframe,
generatedAt: new Date(),
generatedBy: req.user.userId,
alertAnalysis,
repeatOffenders,
blockedUsers,
geoThreats,
summary: {
totalAlerts: alertAnalysis.reduce((sum, item) => sum + item.count, 0),
criticalIncidents: alertAnalysis
.filter(item => item._id.severity === 'critical')
.reduce((sum, item) => sum + item.count, 0),
repeatOffenderCount: repeatOffenders.length,
topThreatCountry: geoThreats[0]?._id || 'Unknown',
blockedUserCount: blockedUsers.reduce((sum, item) => sum + item.count, 0)
}
}
res.json({
success: true,
report
})
} catch (error) {
console.error('Error generating security report:', error)
res.status(500).json({
success: false,
error: 'Failed to generate security report'
})
}
})
// Export report to file
router.post('/export', async (req, res) => {
try {
const { reportType, data } = req.body
if (!reportType || !data) {
return res.status(400).json({
success: false,
error: 'Report type and data required'
})
}
const timestamp = moment().format('YYYY-MM-DD_HH-mm-ss')
const filename = `${reportType}_report_${timestamp}.json`
const exportDir = path.join(__dirname, '../exports')
// Ensure export directory exists
if (!fs.existsSync(exportDir)) {
fs.mkdirSync(exportDir, { recursive: true })
}
const filepath = path.join(exportDir, filename)
// Add metadata
const exportData = {
...data,
exportedAt: new Date(),
exportedBy: req.user.userId,
filename
}
fs.writeFileSync(filepath, JSON.stringify(exportData, null, 2))
console.log(`Report exported: ${filename} by ${req.user.userId}`)
res.json({
success: true,
filename,
filepath,
size: fs.statSync(filepath).size
})
} catch (error) {
console.error('Error exporting report:', error)
res.status(500).json({
success: false,
error: 'Failed to export report'
})
}
})
// Get list of exported reports
router.get('/exports', async (req, res) => {
try {
const exportDir = path.join(__dirname, '../exports')
if (!fs.existsSync(exportDir)) {
return res.json({
success: true,
exports: []
})
}
const files = fs.readdirSync(exportDir)
const exports = files
.filter(file => file.endsWith('.json'))
.map(file => {
const filepath = path.join(exportDir, file)
const stats = fs.statSync(filepath)
return {
filename: file,
size: stats.size,
created: stats.birthtime,
modified: stats.mtime
}
})
.sort((a, b) => new Date(b.created) - new Date(a.created))
res.json({
success: true,
exports
})
} catch (error) {
console.error('Error listing exports:', error)
res.status(500).json({
success: false,
error: 'Failed to list exports'
})
}
})
// Download exported report
router.get('/exports/:filename', async (req, res) => {
try {
const { filename } = req.params
const exportDir = path.join(__dirname, '../exports')
const filepath = path.join(exportDir, filename)
if (!fs.existsSync(filepath)) {
return res.status(404).json({
success: false,
error: 'Export file not found'
})
}
res.download(filepath, filename, (err) => {
if (err) {
console.error('Error downloading file:', err)
res.status(500).json({
success: false,
error: 'Failed to download file'
})
}
})
} catch (error) {
console.error('Error downloading export:', error)
res.status(500).json({
success: false,
error: 'Failed to download export'
})
}
})
// Helper function to calculate trend
function calculateTrend(values) {
if (values.length < 2) return 0
const first = values[0]
const last = values[values.length - 1]
if (first === 0) return last > 0 ? 100 : 0
return ((last - first) / first) * 100
}
module.exports = router