ideas-generator/server/routes/analytics.js

const express = require('express');
const { Conversation, Message, User, Assistant } = require('../models');
const { Op } = require('sequelize');

const router = express.Router();

// Get usage statistics
router.get('/usage', async (req, res, next) => {
  try {
    const { startDate, endDate, userId, agentKey } = req.query;
    
    // Build where clause for filtering
    const whereClause = {};
    
    if (startDate || endDate) {
      whereClause.createdAt = {};
      if (startDate) {
        whereClause.createdAt[Op.gte] = new Date(startDate);
      }
      if (endDate) {
        whereClause.createdAt[Op.lte] = new Date(endDate);
      }
    }
    
    if (userId) {
      whereClause.userId = userId;
    }
    
    if (agentKey) {
      // Look in metadata for assistantKey
      whereClause['metadata.assistantKey'] = agentKey;
    }

    // Get conversations with message counts
    const conversations = await Conversation.findAll({
      where: whereClause,
      include: [
        {
          model: Message,
          as: 'messages',
          attributes: []
        },
        {
          model: Assistant,
          as: 'assistant',
          attributes: ['key', 'name']
        }
      ],
      attributes: {
        include: [
          [Conversation.sequelize.fn('COUNT', Conversation.sequelize.col('messages.id')), 'messageCount']
        ]
      },
      group: ['Conversation.id', 'assistant.id'],
      order: [['createdAt', 'DESC']]
    });

    // Calculate duration for each conversation (rough estimate based on message timestamps)
    const conversationData = await Promise.all(
      conversations.map(async (conv) => {
        const messages = await Message.findAll({
          where: { conversationId: conv.id },
          order: [['createdAt', 'ASC']],
          limit: 2 // First and last message to calculate duration
        });
        
        let duration = '--';
        if (messages.length >= 2) {
          const start = new Date(messages[0].createdAt);
          const end = new Date(messages[messages.length - 1].createdAt);
          const diffMinutes = Math.round((end - start) / 60000);
          duration = `${diffMinutes} min`;
        }

        return {
          id: conv.id,
          date: conv.createdAt,
          userName: conv.userId || 'Anonymous',
          agentName: conv.assistant?.name || 'Unknown Agent',
          messageCount: parseInt(conv.dataValues.messageCount) || 0,
          duration: duration,
          status: conv.status || 'active'
        };
      })
    );

    res.json({
      conversations: conversationData,
      total: conversations.length
    });

  } catch (error) {
    console.error('Analytics error:', error);
    next(error);
  }
});

// Get usage statistics summary
router.get('/stats', async (req, res, next) => {
  try {
    const { startDate, endDate } = req.query;
    
    // Build date filter
    const dateFilter = {};
    if (startDate || endDate) {
      dateFilter.createdAt = {};
      if (startDate) {
        dateFilter.createdAt[Op.gte] = new Date(startDate);
      }
      if (endDate) {
        dateFilter.createdAt[Op.lte] = new Date(endDate);
      }
    }

    // Get total conversations
    const totalConversations = await Conversation.count({
      where: dateFilter
    });

    // Get total messages
    const totalMessages = await Message.count({
      where: dateFilter
    });

    // Get unique users (approximation since we don't have proper user tracking yet)
    const uniqueUserIds = await Conversation.findAll({
      where: dateFilter,
      attributes: [[Conversation.sequelize.fn('DISTINCT', Conversation.sequelize.col('userId')), 'userId']],
      raw: true
    });

    const activeUsers = uniqueUserIds.filter(u => u.userId).length;

    // Get most used agent
    const agentUsage = await Conversation.findAll({
      where: dateFilter,
      include: [
        {
          model: Assistant,
          as: 'assistant',
          attributes: ['name']
        }
      ],
      attributes: [
        'assistantId',
        [Conversation.sequelize.fn('COUNT', Conversation.sequelize.col('Conversation.id')), 'usage_count']
      ],
      group: ['assistantId', 'assistant.id'],
      order: [[Conversation.sequelize.fn('COUNT', Conversation.sequelize.col('Conversation.id')), 'DESC']],
      limit: 1,
      raw: true
    });

    const mostUsedAgent = agentUsage.length > 0 
      ? agentUsage[0]['assistant.name'] || 'Unknown'
      : 'None';

    res.json({
      totalConversations,
      totalMessages,
      activeUsers,
      mostUsedAgent
    });

  } catch (error) {
    console.error('Stats error:', error);
    next(error);
  }
});

// Get usage trends over time
router.get('/trends', async (req, res, next) => {
  try {
    const { period = 'daily', days = 30 } = req.query;
    
    // Calculate date range
    const endDate = new Date();
    const startDate = new Date();
    startDate.setDate(startDate.getDate() - parseInt(days));

    // Get conversations grouped by date
    const query = `
      SELECT 
        DATE("createdAt") as date,
        COUNT(*) as conversations,
        COUNT(DISTINCT "userId") as unique_users,
        (
          SELECT COUNT(*) 
          FROM messages 
          WHERE DATE(messages."createdAt") = DATE(conversations."createdAt")
        ) as messages
      FROM conversations 
      WHERE "createdAt" >= $1 AND "createdAt" <= $2 
      GROUP BY DATE("createdAt")
      ORDER BY DATE("createdAt")
    `;

    const trends = await Conversation.sequelize.query(query, {
      bind: [startDate, endDate],
      type: Conversation.sequelize.QueryTypes.SELECT
    });

    // Fill in missing dates with zero values
    const dateMap = new Map();
    trends.forEach(trend => {
      dateMap.set(trend.date, {
        date: trend.date,
        conversations: parseInt(trend.conversations),
        unique_users: parseInt(trend.unique_users),
        messages: parseInt(trend.messages) || 0
      });
    });

    // Generate complete date range
    const result = [];
    const current = new Date(startDate);
    while (current <= endDate) {
      const dateStr = current.toISOString().split('T')[0];
      result.push(dateMap.get(dateStr) || {
        date: dateStr,
        conversations: 0,
        unique_users: 0,
        messages: 0
      });
      current.setDate(current.getDate() + 1);
    }

    res.json({
      trends: result,
      period,
      days: parseInt(days)
    });

  } catch (error) {
    console.error('Trends error:', error);
    next(error);
  }
});

// Get agent usage trends
router.get('/agent-trends', async (req, res, next) => {
  try {
    const { days = 30 } = req.query;
    
    // Calculate date range
    const endDate = new Date();
    const startDate = new Date();
    startDate.setDate(startDate.getDate() - parseInt(days));

    // Get agent usage over time  
    const agentTrends = await Conversation.findAll({
      where: {
        createdAt: {
          [Op.gte]: startDate,
          [Op.lte]: endDate
        },
        assistantId: {
          [Op.not]: null
        }
      },
      include: [{
        model: Assistant,
        as: 'assistant',
        attributes: ['name', 'key']
      }],
      attributes: [
        [Conversation.sequelize.fn('DATE', Conversation.sequelize.col('Conversation.createdAt')), 'date'],
        [Conversation.sequelize.fn('COUNT', Conversation.sequelize.col('Conversation.id')), 'usage_count']
      ],
      group: [
        Conversation.sequelize.fn('DATE', Conversation.sequelize.col('Conversation.createdAt')),
        'assistant.id',
        'assistant.name',
        'assistant.key'
      ],
      order: [
        [Conversation.sequelize.fn('DATE', Conversation.sequelize.col('Conversation.createdAt')), 'ASC'],
        [Conversation.sequelize.fn('COUNT', Conversation.sequelize.col('Conversation.id')), 'DESC']
      ],
      raw: true
    });

    // Group by agent
    const agentData = {};
    agentTrends.forEach(trend => {
      const agentName = trend['assistant.name'];
      if (!agentData[agentName]) {
        agentData[agentName] = [];
      }
      agentData[agentName].push({
        date: trend.date,
        usage: parseInt(trend.usage_count)
      });
    });

    res.json({
      agent_trends: agentData,
      days: parseInt(days)
    });

  } catch (error) {
    console.error('Agent trends error:', error);
    next(error);
  }
});

module.exports = router;