streamflow/backend/routes/stats.js

const express = require('express');
const router = express.Router();
const { authenticate, requireAdmin } = require('../middleware/auth');
const { readLimiter } = require('../middleware/rateLimiter');
const { db } = require('../database/db');
const os = require('os');
const logger = require('../utils/logger');

/**
 * Get overall statistics (admin only)
 */
router.get('/overview', readLimiter, authenticate, requireAdmin, (req, res) => {
  const stats = {};

  // Get total counts
  db.get('SELECT COUNT(*) as count FROM users', [], (err, result) => {
    stats.totalUsers = result?.count || 0;

    db.get('SELECT COUNT(*) as count FROM channels WHERE is_active = 1', [], (err, result) => {
      stats.totalChannels = result?.count || 0;

      db.get('SELECT COUNT(*) as count FROM channels WHERE is_radio = 1 AND is_active = 1', [], (err, result) => {
        stats.totalRadioChannels = result?.count || 0;
        stats.totalTvChannels = stats.totalChannels - stats.totalRadioChannels;

        db.get('SELECT COUNT(*) as count FROM playlists', [], (err, result) => {
          stats.totalPlaylists = result?.count || 0;

          db.get('SELECT COUNT(*) as count FROM watch_history', [], (err, result) => {
            stats.totalWatchHistory = result?.count || 0;

            // Channel health stats
            db.all(`
              SELECT health_status, COUNT(*) as count 
              FROM channels 
              WHERE is_active = 1 
              GROUP BY health_status
            `, [], (err, rows) => {
              stats.channelHealth = {
                healthy: 0,
                degraded: 0,
                dead: 0,
                unknown: 0
              };

              rows.forEach(row => {
                if (row.health_status) {
                  stats.channelHealth[row.health_status] = row.count;
                }
              });

              // System resource usage
              stats.system = {
                cpuUsage: process.cpuUsage(),
                memoryUsage: process.memoryUsage(),
                uptime: process.uptime(),
                platform: os.platform(),
                totalMemory: os.totalmem(),
                freeMemory: os.freemem(),
                cpuCount: os.cpus().length,
                loadAverage: os.loadavg()
              };

              res.json(stats);
            });
          });
        });
      });
    });
  });
});

/**
 * Get most watched channels across all users (admin only)
 */
router.get('/top-channels', readLimiter, authenticate, requireAdmin, (req, res) => {
  const { limit = 20, days = 30 } = req.query;

  db.all(`
    SELECT 
      c.id,
      c.name,
      c.logo,
      c.custom_logo,
      c.group_name,
      c.is_radio,
      COUNT(wh.id) as watch_count,
      COUNT(DISTINCT wh.user_id) as unique_users,
      SUM(wh.duration) as total_duration,
      AVG(wh.duration) as avg_duration,
      MAX(wh.watched_at) as last_watched
    FROM watch_history wh
    INNER JOIN channels c ON wh.channel_id = c.id
    WHERE wh.watched_at >= datetime('now', '-' || ? || ' days')
    GROUP BY c.id
    ORDER BY watch_count DESC
    LIMIT ?
  `, [days, parseInt(limit)], (err, rows) => {
    if (err) {
      logger.error('Error fetching top channels:', err);
      return res.status(500).json({ error: 'Failed to fetch top channels' });
    }
    res.json(rows);
  });
});

/**
 * Get usage patterns by hour (admin only)
 */
router.get('/usage-by-hour', readLimiter, authenticate, requireAdmin, (req, res) => {
  const { days = 7 } = req.query;

  db.all(`
    SELECT 
      CAST(strftime('%H', watched_at) AS INTEGER) as hour,
      COUNT(*) as view_count,
      COUNT(DISTINCT user_id) as unique_users
    FROM watch_history
    WHERE watched_at >= datetime('now', '-' || ? || ' days')
    GROUP BY hour
    ORDER BY hour
  `, [days], (err, rows) => {
    if (err) {
      logger.error('Error fetching usage by hour:', err);
      return res.status(500).json({ error: 'Failed to fetch usage by hour' });
    }
    res.json(rows);
  });
});

/**
 * Get usage trends (admin only)
 */
router.get('/trends', readLimiter, authenticate, requireAdmin, (req, res) => {
  const { days = 30 } = req.query;

  db.all(`
    SELECT 
      DATE(watched_at) as date,
      COUNT(*) as view_count,
      COUNT(DISTINCT user_id) as unique_users,
      COUNT(DISTINCT channel_id) as unique_channels,
      SUM(duration) as total_duration
    FROM watch_history
    WHERE watched_at >= datetime('now', '-' || ? || ' days')
    GROUP BY date
    ORDER BY date DESC
  `, [days], (err, rows) => {
    if (err) {
      logger.error('Error fetching trends:', err);
      return res.status(500).json({ error: 'Failed to fetch trends' });
    }
    res.json(rows);
  });
});

/**
 * Get user activity stats (admin only)
 */
router.get('/user-activity', readLimiter, authenticate, requireAdmin, (req, res) => {
  const { days = 30 } = req.query;

  db.all(`
    SELECT 
      u.id,
      u.username,
      u.email,
      COUNT(wh.id) as watch_count,
      SUM(wh.duration) as total_duration,
      MAX(wh.watched_at) as last_active
    FROM users u
    LEFT JOIN watch_history wh ON u.id = wh.user_id 
      AND wh.watched_at >= datetime('now', '-' || ? || ' days')
    WHERE u.is_active = 1
    GROUP BY u.id
    ORDER BY watch_count DESC
  `, [days], (err, rows) => {
    if (err) {
      logger.error('Error fetching user activity:', err);
      return res.status(500).json({ error: 'Failed to fetch user activity' });
    }
    res.json(rows);
  });
});

/**
 * Get current user's personal stats
 */
router.get('/my-stats', readLimiter, authenticate, (req, res) => {
  const user_id = req.user.userId;
  const { days = 30 } = req.query;

  const stats = {};

  // Total watch count
  db.get(`
    SELECT 
      COUNT(*) as watch_count,
      SUM(duration) as total_duration,
      COUNT(DISTINCT channel_id) as unique_channels
    FROM watch_history
    WHERE user_id = ?
      AND watched_at >= datetime('now', '-' || ? || ' days')
  `, [user_id, days], (err, result) => {
    if (err) {
      return res.status(500).json({ error: 'Failed to fetch stats' });
    }

    stats.overview = result;

    // Most watched group
    db.get(`
      SELECT c.group_name, COUNT(*) as count
      FROM watch_history wh
      INNER JOIN channels c ON wh.channel_id = c.id
      WHERE wh.user_id = ?
        AND wh.watched_at >= datetime('now', '-' || ? || ' days')
        AND c.group_name IS NOT NULL
      GROUP BY c.group_name
      ORDER BY count DESC
      LIMIT 1
    `, [user_id, days], (err, favGroup) => {
      stats.favoriteGroup = favGroup?.group_name || null;

      // Watch by day of week
      db.all(`
        SELECT 
          CAST(strftime('%w', watched_at) AS INTEGER) as day_of_week,
          COUNT(*) as count
        FROM watch_history
        WHERE user_id = ?
          AND watched_at >= datetime('now', '-' || ? || ' days')
        GROUP BY day_of_week
        ORDER BY day_of_week
      `, [user_id, days], (err, weekData) => {
        stats.byDayOfWeek = weekData || [];
        res.json(stats);
      });
    });
  });
});

module.exports = router;