const { User, QuizSession, Category, Question, GuestSettings, sequelize } = require('../models');
const { Op } = require('sequelize');

/**
 * Get system-wide statistics (admin only)
 * @route GET /api/admin/statistics
 * @access Private (admin only)
 */
exports.getSystemStatistics = async (req, res) => {
  try {
    // Calculate date 7 days ago for active users
    const sevenDaysAgo = new Date();
    sevenDaysAgo.setDate(sevenDaysAgo.getDate() - 7);

    // Calculate date 30 days ago for user growth
    const thirtyDaysAgo = new Date();
    thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);

    // 1. Total Users
    const totalUsers = await User.count({
      where: { role: { [Op.ne]: 'admin' } } // Exclude admins from user count
    });

    // 2. Active Users (last 7 days)
    const activeUsers = await User.count({
      where: {
        role: { [Op.ne]: 'admin' },
        lastQuizDate: {
          [Op.gte]: sevenDaysAgo
        }
      }
    });

    // 3. Total Quiz Sessions
    const totalQuizSessions = await QuizSession.count({
      where: {
        status: { [Op.in]: ['completed', 'timeout'] }
      }
    });

    // 4. Average Score (from completed quizzes)
    const averageScoreResult = await QuizSession.findAll({
      attributes: [
        [sequelize.fn('AVG', sequelize.col('score')), 'avgScore'],
        [sequelize.fn('AVG', sequelize.col('total_points')), 'avgTotal']
      ],
      where: {
        status: { [Op.in]: ['completed', 'timeout'] }
      },
      raw: true
    });

    const avgScore = parseFloat(averageScoreResult[0].avgScore) || 0;
    const avgTotal = parseFloat(averageScoreResult[0].avgTotal) || 1;
    const averageScorePercentage = avgTotal > 0 ? Math.round((avgScore / avgTotal) * 100) : 0;

    // 5. Popular Categories (top 5 by quiz count)
    const popularCategories = await sequelize.query(`
      SELECT 
        c.id,
        c.name,
        c.slug,
        c.icon,
        c.color,
        COUNT(qs.id) as quizCount,
        ROUND(AVG(qs.score / qs.total_points * 100), 2) as avgScore
      FROM categories c
      LEFT JOIN quiz_sessions qs ON qs.category_id = c.id AND qs.status IN ('completed', 'timeout')
      WHERE c.is_active = true
      GROUP BY c.id, c.name, c.slug, c.icon, c.color
      ORDER BY quizCount DESC
      LIMIT 5
    `, {
      type: sequelize.QueryTypes.SELECT
    });

    // Format popular categories
    const formattedPopularCategories = popularCategories.map(cat => ({
      id: cat.id,
      name: cat.name,
      slug: cat.slug,
      icon: cat.icon,
      color: cat.color,
      quizCount: parseInt(cat.quizCount),
      averageScore: parseFloat(cat.avgScore) || 0
    }));

    // 6. User Growth (last 30 days)
    const userGrowth = await sequelize.query(`
      SELECT 
        DATE(created_at) as date,
        COUNT(*) as newUsers
      FROM users
      WHERE created_at >= :thirtyDaysAgo
        AND role != 'admin'
      GROUP BY DATE(created_at)
      ORDER BY date ASC
    `, {
      replacements: { thirtyDaysAgo },
      type: sequelize.QueryTypes.SELECT
    });

    // Format user growth data
    const formattedUserGrowth = userGrowth.map(item => ({
      date: item.date instanceof Date ? item.date.toISOString().split('T')[0] : item.date,
      newUsers: parseInt(item.newUsers)
    }));

    // 7. Quiz Activity (last 30 days)
    const quizActivity = await sequelize.query(`
      SELECT 
        DATE(completed_at) as date,
        COUNT(*) as quizzesCompleted
      FROM quiz_sessions
      WHERE completed_at >= :thirtyDaysAgo
        AND status IN ('completed', 'timeout')
      GROUP BY DATE(completed_at)
      ORDER BY date ASC
    `, {
      replacements: { thirtyDaysAgo },
      type: sequelize.QueryTypes.SELECT
    });

    // Format quiz activity data
    const formattedQuizActivity = quizActivity.map(item => ({
      date: item.date instanceof Date ? item.date.toISOString().split('T')[0] : item.date,
      quizzesCompleted: parseInt(item.quizzesCompleted)
    }));

    // 8. Total Questions
    const totalQuestions = await Question.count({
      where: { isActive: true }
    });

    // 9. Total Categories
    const totalCategories = await Category.count({
      where: { isActive: true }
    });

    // 10. Questions by Difficulty
    const questionsByDifficulty = await Question.findAll({
      attributes: [
        'difficulty',
        [sequelize.fn('COUNT', sequelize.col('id')), 'count']
      ],
      where: { isActive: true },
      group: ['difficulty'],
      raw: true
    });

    const difficultyBreakdown = {
      easy: 0,
      medium: 0,
      hard: 0
    };

    questionsByDifficulty.forEach(item => {
      difficultyBreakdown[item.difficulty] = parseInt(item.count);
    });

    // 11. Pass Rate
    const completedQuizzes = await QuizSession.count({
      where: {
        status: { [Op.in]: ['completed', 'timeout'] }
      }
    });

    const passedQuizzes = await QuizSession.count({
      where: {
        status: { [Op.in]: ['completed', 'timeout'] },
        score: {
          [Op.gte]: sequelize.literal('total_points * 0.7') // 70% pass threshold
        }
      }
    });

    const passRate = completedQuizzes > 0 
      ? Math.round((passedQuizzes / completedQuizzes) * 100) 
      : 0;

    // Build response
    const statistics = {
      users: {
        total: totalUsers,
        active: activeUsers,
        inactiveLast7Days: totalUsers - activeUsers
      },
      quizzes: {
        totalSessions: totalQuizSessions,
        averageScore: Math.round(avgScore),
        averageScorePercentage,
        passRate,
        passedQuizzes,
        failedQuizzes: completedQuizzes - passedQuizzes
      },
      content: {
        totalCategories,
        totalQuestions,
        questionsByDifficulty: difficultyBreakdown
      },
      popularCategories: formattedPopularCategories,
      userGrowth: formattedUserGrowth,
      quizActivity: formattedQuizActivity
    };

    res.status(200).json({
      success: true,
      data: statistics,
      message: 'System statistics retrieved successfully'
    });

  } catch (error) {
    console.error('Get system statistics error:', error);
    res.status(500).json({
      success: false,
      message: 'Internal server error',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
};

/**
 * Get guest settings
 * @route GET /api/admin/guest-settings
 * @access Private (admin only)
 */
exports.getGuestSettings = async (req, res) => {
  try {
    // Try to get existing settings
    let settings = await GuestSettings.findOne();

    // If no settings exist, return defaults
    if (!settings) {
      settings = {
        maxQuizzes: 3,
        expiryHours: 24,
        publicCategories: [],
        featureRestrictions: {
          allowBookmarks: false,
          allowReview: true,
          allowPracticeMode: true,
          allowTimedMode: false,
          allowExamMode: false
        }
      };
    }

    res.status(200).json({
      success: true,
      data: {
        maxQuizzes: settings.maxQuizzes,
        expiryHours: settings.expiryHours,
        publicCategories: settings.publicCategories,
        featureRestrictions: settings.featureRestrictions
      },
      message: 'Guest settings retrieved successfully'
    });

  } catch (error) {
    console.error('Get guest settings error:', error);
    res.status(500).json({
      success: false,
      message: 'Internal server error',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
};

/**
 * Update guest settings
 * @route PUT /api/admin/guest-settings
 * @access Private (admin only)
 */
exports.updateGuestSettings = async (req, res) => {
  try {
    const { maxQuizzes, expiryHours, publicCategories, featureRestrictions } = req.body;

    // Validate maxQuizzes
    if (maxQuizzes !== undefined) {
      if (typeof maxQuizzes !== 'number' || !Number.isInteger(maxQuizzes)) {
        return res.status(400).json({
          success: false,
          message: 'Max quizzes must be an integer'
        });
      }
      if (maxQuizzes < 1 || maxQuizzes > 50) {
        return res.status(400).json({
          success: false,
          message: 'Max quizzes must be between 1 and 50'
        });
      }
    }

    // Validate expiryHours
    if (expiryHours !== undefined) {
      if (typeof expiryHours !== 'number' || !Number.isInteger(expiryHours)) {
        return res.status(400).json({
          success: false,
          message: 'Expiry hours must be an integer'
        });
      }
      if (expiryHours < 1 || expiryHours > 168) {
        return res.status(400).json({
          success: false,
          message: 'Expiry hours must be between 1 and 168 (7 days)'
        });
      }
    }

    // Validate publicCategories
    if (publicCategories !== undefined) {
      if (!Array.isArray(publicCategories)) {
        return res.status(400).json({
          success: false,
          message: 'Public categories must be an array'
        });
      }

      // Validate each category UUID
      const uuidRegex = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;
      for (const categoryId of publicCategories) {
        if (!uuidRegex.test(categoryId)) {
          return res.status(400).json({
            success: false,
            message: `Invalid category UUID format: ${categoryId}`
          });
        }

        // Verify category exists
        const category = await Category.findByPk(categoryId);
        if (!category) {
          return res.status(404).json({
            success: false,
            message: `Category not found: ${categoryId}`
          });
        }
      }
    }

    // Validate featureRestrictions
    if (featureRestrictions !== undefined) {
      if (typeof featureRestrictions !== 'object' || Array.isArray(featureRestrictions)) {
        return res.status(400).json({
          success: false,
          message: 'Feature restrictions must be an object'
        });
      }

      // Validate boolean fields
      const validFields = ['allowBookmarks', 'allowReview', 'allowPracticeMode', 'allowTimedMode', 'allowExamMode'];
      for (const key in featureRestrictions) {
        if (!validFields.includes(key)) {
          return res.status(400).json({
            success: false,
            message: `Invalid feature restriction field: ${key}`
          });
        }
        if (typeof featureRestrictions[key] !== 'boolean') {
          return res.status(400).json({
            success: false,
            message: `Feature restriction "${key}" must be a boolean`
          });
        }
      }
    }

    // Check if settings exist
    let settings = await GuestSettings.findOne();

    if (settings) {
      // Update existing settings
      if (maxQuizzes !== undefined) settings.maxQuizzes = maxQuizzes;
      if (expiryHours !== undefined) settings.expiryHours = expiryHours;
      if (publicCategories !== undefined) settings.publicCategories = publicCategories;
      if (featureRestrictions !== undefined) {
        settings.featureRestrictions = {
          ...settings.featureRestrictions,
          ...featureRestrictions
        };
      }

      await settings.save();
    } else {
      // Create new settings
      settings = await GuestSettings.create({
        maxQuizzes: maxQuizzes !== undefined ? maxQuizzes : 3,
        expiryHours: expiryHours !== undefined ? expiryHours : 24,
        publicCategories: publicCategories || [],
        featureRestrictions: featureRestrictions || {
          allowBookmarks: false,
          allowReview: true,
          allowPracticeMode: true,
          allowTimedMode: false,
          allowExamMode: false
        }
      });
    }

    res.status(200).json({
      success: true,
      data: {
        maxQuizzes: settings.maxQuizzes,
        expiryHours: settings.expiryHours,
        publicCategories: settings.publicCategories,
        featureRestrictions: settings.featureRestrictions
      },
      message: 'Guest settings updated successfully'
    });

  } catch (error) {
    console.error('Update guest settings error:', error);
    res.status(500).json({
      success: false,
      message: 'Internal server error',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
};

/**
 * Get all users with pagination and filters (admin only)
 * @route GET /api/admin/users
 * @access Private (admin only)
 */
exports.getAllUsers = async (req, res) => {
  try {
    const { page = 1, limit = 10, role, isActive, sortBy = 'createdAt', sortOrder = 'desc' } = req.query;

    // Validate pagination
    const pageNum = Math.max(parseInt(page) || 1, 1);
    const limitNum = Math.min(Math.max(parseInt(limit) || 10, 1), 100);
    const offset = (pageNum - 1) * limitNum;

    // Build where clause
    const where = {};
    
    if (role) {
      if (!['user', 'admin'].includes(role)) {
        return res.status(400).json({
          success: false,
          message: 'Invalid role. Must be "user" or "admin"'
        });
      }
      where.role = role;
    }

    if (isActive !== undefined) {
      if (isActive !== 'true' && isActive !== 'false') {
        return res.status(400).json({
          success: false,
          message: 'Invalid isActive value. Must be "true" or "false"'
        });
      }
      where.isActive = isActive === 'true';
    }

    // Validate sort
    const validSortFields = ['createdAt', 'username', 'email', 'lastLogin'];
    const sortField = validSortFields.includes(sortBy) ? sortBy : 'createdAt';
    const order = sortOrder.toLowerCase() === 'asc' ? 'ASC' : 'DESC';

    // Get total count
    const total = await User.count({ where });

    // Get users
    const users = await User.findAll({
      where,
      attributes: {
        exclude: ['password']
      },
      order: [[sortField, order]],
      limit: limitNum,
      offset
    });

    // Format response
    const formattedUsers = users.map(user => ({
      id: user.id,
      username: user.username,
      email: user.email,
      role: user.role,
      isActive: user.isActive,
      profileImage: user.profileImage,
      totalQuizzes: user.totalQuizzes,
      quizzesPassed: user.quizzesPassed,
      totalQuestionsAnswered: user.totalQuestionsAnswered,
      correctAnswers: user.correctAnswers,
      currentStreak: user.currentStreak,
      longestStreak: user.longestStreak,
      lastLogin: user.lastLogin,
      lastQuizDate: user.lastQuizDate,
      createdAt: user.createdAt
    }));

    // Pagination metadata
    const totalPages = Math.ceil(total / limitNum);

    res.status(200).json({
      success: true,
      data: {
        users: formattedUsers,
        pagination: {
          currentPage: pageNum,
          totalPages,
          totalItems: total,
          itemsPerPage: limitNum,
          hasNextPage: pageNum < totalPages,
          hasPreviousPage: pageNum > 1
        },
        filters: {
          role: role || null,
          isActive: isActive !== undefined ? (isActive === 'true') : null
        },
        sorting: {
          sortBy: sortField,
          sortOrder: order
        }
      },
      message: 'Users retrieved successfully'
    });

  } catch (error) {
    console.error('Get all users error:', error);
    res.status(500).json({
      success: false,
      message: 'Internal server error',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
};

/**
 * Get user by ID (admin only)
 * @route GET /api/admin/users/:userId
 * @access Private (admin only)
 */
exports.getUserById = async (req, res) => {
  try {
    const { userId } = req.params;

    // Validate UUID format
    const uuidRegex = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;
    if (!uuidRegex.test(userId)) {
      return res.status(400).json({
        success: false,
        message: 'Invalid user ID format'
      });
    }

    // Get user
    const user = await User.findByPk(userId, {
      attributes: {
        exclude: ['password']
      }
    });

    if (!user) {
      return res.status(404).json({
        success: false,
        message: 'User not found'
      });
    }

    // Get recent quiz sessions (last 10)
    const recentSessions = await QuizSession.findAll({
      where: { userId },
      include: [{
        model: Category,
        as: 'category',
        attributes: ['id', 'name', 'slug', 'icon', 'color']
      }],
      order: [['completedAt', 'DESC']],
      limit: 10
    });

    // Format user data
    const userData = {
      id: user.id,
      username: user.username,
      email: user.email,
      role: user.role,
      isActive: user.isActive,
      profileImage: user.profileImage,
      stats: {
        totalQuizzes: user.totalQuizzes,
        quizzesPassed: user.quizzesPassed,
        passRate: user.totalQuizzes > 0 ? Math.round((user.quizzesPassed / user.totalQuizzes) * 100) : 0,
        totalQuestionsAnswered: user.totalQuestionsAnswered,
        correctAnswers: user.correctAnswers,
        accuracy: user.totalQuestionsAnswered > 0 
          ? Math.round((user.correctAnswers / user.totalQuestionsAnswered) * 100) 
          : 0,
        currentStreak: user.currentStreak,
        longestStreak: user.longestStreak
      },
      activity: {
        lastLogin: user.lastLogin,
        lastQuizDate: user.lastQuizDate,
        memberSince: user.createdAt
      },
      recentSessions: recentSessions.map(session => ({
        id: session.id,
        category: session.category,
        quizType: session.quizType,
        difficulty: session.difficulty,
        status: session.status,
        score: parseFloat(session.score) || 0,
        totalPoints: parseFloat(session.totalPoints) || 0,
        percentage: session.totalPoints > 0 
          ? Math.round((parseFloat(session.score) / parseFloat(session.totalPoints)) * 100) 
          : 0,
        questionsAnswered: session.questionsAnswered,
        correctAnswers: session.correctAnswers,
        completedAt: session.completedAt
      }))
    };

    res.status(200).json({
      success: true,
      data: userData,
      message: 'User details retrieved successfully'
    });

  } catch (error) {
    console.error('Get user by ID error:', error);
    res.status(500).json({
      success: false,
      message: 'Internal server error',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
};

/**
 * Update user role (admin only)
 * @route PUT /api/admin/users/:userId/role
 * @access Private (admin only)
 */
exports.updateUserRole = async (req, res) => {
  try {
    const { userId } = req.params;
    const { role } = req.body;

    // Validate UUID format
    const uuidRegex = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;
    if (!uuidRegex.test(userId)) {
      return res.status(400).json({
        success: false,
        message: 'Invalid user ID format'
      });
    }

    // Validate role
    if (!role) {
      return res.status(400).json({
        success: false,
        message: 'Role is required'
      });
    }

    if (!['user', 'admin'].includes(role)) {
      return res.status(400).json({
        success: false,
        message: 'Invalid role. Must be "user" or "admin"'
      });
    }

    // Get user
    const user = await User.findByPk(userId);

    if (!user) {
      return res.status(404).json({
        success: false,
        message: 'User not found'
      });
    }

    // If demoting from admin to user, check if there are other admins
    if (user.role === 'admin' && role === 'user') {
      const adminCount = await User.count({ where: { role: 'admin' } });
      if (adminCount <= 1) {
        return res.status(400).json({
          success: false,
          message: 'Cannot demote the last admin user'
        });
      }
    }

    // Update role
    user.role = role;
    await user.save();

    res.status(200).json({
      success: true,
      data: {
        id: user.id,
        username: user.username,
        email: user.email,
        role: user.role
      },
      message: `User role updated to ${role} successfully`
    });

  } catch (error) {
    console.error('Update user role error:', error);
    res.status(500).json({
      success: false,
      message: 'Internal server error',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
};

/**
 * Deactivate user (soft delete) (admin only)
 * @route DELETE /api/admin/users/:userId
 * @access Private (admin only)
 */
exports.deactivateUser = async (req, res) => {
  try {
    const { userId } = req.params;

    // Validate UUID format
    const uuidRegex = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;
    if (!uuidRegex.test(userId)) {
      return res.status(400).json({
        success: false,
        message: 'Invalid user ID format'
      });
    }

    // Get user
    const user = await User.findByPk(userId);

    if (!user) {
      return res.status(404).json({
        success: false,
        message: 'User not found'
      });
    }

    // Check if already deactivated
    if (!user.isActive) {
      return res.status(400).json({
        success: false,
        message: 'User is already deactivated'
      });
    }

    // Prevent deactivating admin if they are the last admin
    if (user.role === 'admin') {
      const adminCount = await User.count({ 
        where: { 
          role: 'admin',
          isActive: true
        } 
      });
      if (adminCount <= 1) {
        return res.status(400).json({
          success: false,
          message: 'Cannot deactivate the last active admin user'
        });
      }
    }

    // Deactivate user
    user.isActive = false;
    await user.save();

    res.status(200).json({
      success: true,
      data: {
        id: user.id,
        username: user.username,
        email: user.email,
        isActive: user.isActive
      },
      message: 'User deactivated successfully'
    });

  } catch (error) {
    console.error('Deactivate user error:', error);
    res.status(500).json({
      success: false,
      message: 'Internal server error',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
};

/**
 * Reactivate user (admin only)
 * @route PUT /api/admin/users/:userId/activate
 * @access Private (admin only)
 */
exports.reactivateUser = async (req, res) => {
  try {
    const { userId } = req.params;

    // Validate UUID format
    const uuidRegex = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;
    if (!uuidRegex.test(userId)) {
      return res.status(400).json({
        success: false,
        message: 'Invalid user ID format'
      });
    }

    // Get user
    const user = await User.findByPk(userId);

    if (!user) {
      return res.status(404).json({
        success: false,
        message: 'User not found'
      });
    }

    // Check if already active
    if (user.isActive) {
      return res.status(400).json({
        success: false,
        message: 'User is already active'
      });
    }

    // Reactivate user
    user.isActive = true;
    await user.save();

    res.status(200).json({
      success: true,
      data: {
        id: user.id,
        username: user.username,
        email: user.email,
        isActive: user.isActive
      },
      message: 'User reactivated successfully'
    });

  } catch (error) {
    console.error('Reactivate user error:', error);
    res.status(500).json({
      success: false,
      message: 'Internal server error',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
};

/**
 * Get guest analytics (admin only)
 * @route GET /api/admin/guest-analytics
 * @access Private (admin only)
 */
exports.getGuestAnalytics = async (req, res) => {
  try {
    const { GuestSession } = require('../models');
    
    // Calculate date ranges for time-based analytics
    const thirtyDaysAgo = new Date();
    thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);

    // 1. Total guest sessions created
    const totalGuestSessions = await GuestSession.count();

    // 2. Active guest sessions (not expired, not converted)
    const activeGuestSessions = await GuestSession.count({
      where: {
        expiresAt: {
          [Op.gte]: new Date()
        },
        convertedUserId: null
      }
    });

    // 3. Expired guest sessions
    const expiredGuestSessions = await GuestSession.count({
      where: {
        expiresAt: {
          [Op.lt]: new Date()
        },
        convertedUserId: null
      }
    });

    // 4. Converted guest sessions (guests who registered)
    const convertedGuestSessions = await GuestSession.count({
      where: {
        convertedUserId: {
          [Op.ne]: null
        }
      }
    });

    // 5. Conversion rate
    const conversionRate = totalGuestSessions > 0
      ? ((convertedGuestSessions / totalGuestSessions) * 100).toFixed(2)
      : 0;

    // 6. Guest quiz sessions (total quizzes taken by guests)
    const guestQuizSessions = await QuizSession.count({
      where: {
        guestSessionId: {
          [Op.ne]: null
        }
      }
    });

    // 7. Completed guest quiz sessions
    const completedGuestQuizzes = await QuizSession.count({
      where: {
        guestSessionId: {
          [Op.ne]: null
        },
        status: { [Op.in]: ['completed', 'timeout'] }
      }
    });

    // 8. Guest quiz completion rate
    const guestQuizCompletionRate = guestQuizSessions > 0
      ? ((completedGuestQuizzes / guestQuizSessions) * 100).toFixed(2)
      : 0;

    // 9. Average quizzes per guest session
    const avgQuizzesPerGuest = totalGuestSessions > 0
      ? (guestQuizSessions / totalGuestSessions).toFixed(2)
      : 0;

    // 10. Average quizzes before conversion
    // Get all converted guests with their quiz counts
    const convertedGuests = await GuestSession.findAll({
      where: {
        convertedUserId: {
          [Op.ne]: null
        }
      },
      attributes: ['id'],
      raw: true
    });

    let avgQuizzesBeforeConversion = 0;
    if (convertedGuests.length > 0) {
      const guestSessionIds = convertedGuests.map(g => g.id);
      const quizCountsResult = await QuizSession.findAll({
        attributes: [
          'guestSessionId',
          [sequelize.fn('COUNT', sequelize.col('id')), 'quizCount']
        ],
        where: {
          guestSessionId: {
            [Op.in]: guestSessionIds
          }
        },
        group: ['guestSessionId'],
        raw: true
      });

      const totalQuizzes = quizCountsResult.reduce((sum, item) => sum + parseInt(item.quizCount), 0);
      avgQuizzesBeforeConversion = (totalQuizzes / convertedGuests.length).toFixed(2);
    }

    // 11. Guest bounce rate (guests who took 0 quizzes)
    // Get all guest sessions
    const allGuestSessionIds = await GuestSession.findAll({
      attributes: ['id'],
      raw: true
    });

    if (allGuestSessionIds.length > 0) {
      // Count guest sessions with at least one quiz
      const guestsWithQuizzes = await QuizSession.count({
        attributes: ['guestSessionId'],
        where: {
          guestSessionId: {
            [Op.in]: allGuestSessionIds.map(g => g.id)
          }
        },
        group: ['guestSessionId'],
        raw: true
      });

      const guestsWithoutQuizzes = allGuestSessionIds.length - guestsWithQuizzes.length;
      var bounceRate = ((guestsWithoutQuizzes / allGuestSessionIds.length) * 100).toFixed(2);
    } else {
      var bounceRate = 0;
    }

    // 12. Recent guest activity (last 30 days)
    const recentGuestSessions = await GuestSession.count({
      where: {
        createdAt: {
          [Op.gte]: thirtyDaysAgo
        }
      }
    });

    // Count recent conversions (guests converted in last 30 days)
    // Since we don't have convertedAt, we use updatedAt for converted sessions
    const recentConversions = await GuestSession.count({
      where: {
        convertedUserId: {
          [Op.ne]: null
        },
        updatedAt: {
          [Op.gte]: thirtyDaysAgo
        }
      }
    });

    // 13. Average session duration for converted guests
    // Calculate time from creation to last update (approximation since no convertedAt field)
    const convertedWithDuration = await GuestSession.findAll({
      where: {
        convertedUserId: {
          [Op.ne]: null
        }
      },
      attributes: [
        'createdAt',
        'updatedAt',
        [sequelize.literal('TIMESTAMPDIFF(MINUTE, created_at, updated_at)'), 'durationMinutes']
      ],
      raw: true
    });

    let avgSessionDuration = 0;
    if (convertedWithDuration.length > 0) {
      const totalMinutes = convertedWithDuration.reduce((sum, item) => {
        return sum + (parseInt(item.durationMinutes) || 0);
      }, 0);
      avgSessionDuration = (totalMinutes / convertedWithDuration.length).toFixed(2);
    }

    res.status(200).json({
      success: true,
      data: {
        overview: {
          totalGuestSessions,
          activeGuestSessions,
          expiredGuestSessions,
          convertedGuestSessions,
          conversionRate: parseFloat(conversionRate)
        },
        quizActivity: {
          totalGuestQuizzes: guestQuizSessions,
          completedGuestQuizzes,
          guestQuizCompletionRate: parseFloat(guestQuizCompletionRate),
          avgQuizzesPerGuest: parseFloat(avgQuizzesPerGuest),
          avgQuizzesBeforeConversion: parseFloat(avgQuizzesBeforeConversion)
        },
        behavior: {
          bounceRate: parseFloat(bounceRate),
          avgSessionDurationMinutes: parseFloat(avgSessionDuration)
        },
        recentActivity: {
          last30Days: {
            newGuestSessions: recentGuestSessions,
            conversions: recentConversions
          }
        }
      },
      message: 'Guest analytics retrieved successfully'
    });

  } catch (error) {
    console.error('Get guest analytics error:', error);
    res.status(500).json({
      success: false,
      message: 'Internal server error',
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
};