
<?php
require_once '../../config/database.php';
require_once '../../config/auth.php';
require_once '../../config/rbac.php';
require_once '../../includes/functions.php';

// Set JSON response header
header('Content-Type: application/json');

// Check authentication
if (!isLoggedIn()) {
    http_response_code(401);
    echo json_encode(['success' => false, 'message' => 'Authentication required']);
    exit;
}

$db = getDB();
$userId = $_SESSION['user_id'];
$userRole = $_SESSION['role'] ?? 'student';
$method = $_SERVER['REQUEST_METHOD'];

try {
    $action = $_GET['action'] ?? $_POST['action'] ?? '';

    switch ($action) {
        case 'dashboard_stats':
            handleDashboardStats($db, $userId, $userRole);
            break;

        case 'course_analytics':
            handleCourseAnalytics($db, $userId, $userRole);
            break;

        case 'student_progress':
            handleStudentProgress($db, $userId, $userRole);
            break;

        case 'system_logs':
            handleSystemLogs($db, $userId, $userRole);
            break;

        case 'question_analysis':
            handleQuestionAnalysis($db, $userId, $userRole);
            break;

        case 'compliance_report':
            handleComplianceReport($db, $userId, $userRole);
            break;

        case 'export_data':
            handleExportData($db, $userId, $userRole);
            break;

        case 'real_time_stats':
            handleRealTimeStats($db, $userId, $userRole);
            break;

        default:
            throw new Exception('Invalid action');
    }

} catch (Exception $e) {
    http_response_code(400);
    echo json_encode(['success' => false, 'message' => $e->getMessage()]);
}

function handleDashboardStats($db, $userId, $userRole) {
    $stats = [];

    if ($userRole === 'admin') {
        // Admin system-wide stats
        $stats = getAdminDashboardStats($db);
    } elseif ($userRole === 'instructor') {
        // Instructor stats for their courses
        $stats = getInstructorDashboardStats($db, $userId);
    } else {
        // Student personal stats
        $stats = getStudentDashboardStats($db, $userId);
    }

    echo json_encode(['success' => true, 'data' => $stats]);
}

function getAdminDashboardStats($db) {
    // Total users
    $stmt = $db->query("SELECT COUNT(*) as total_users FROM users");
    $totalUsers = $stmt->fetch(PDO::FETCH_ASSOC)['total_users'];

    // Total courses
    $stmt = $db->query("SELECT COUNT(*) as total_courses FROM courses WHERE status = 'published'");
    $totalCourses = $stmt->fetch(PDO::FETCH_ASSOC)['total_courses'];

    // Total enrollments
    $stmt = $db->query("SELECT COUNT(*) as total_enrollments FROM course_enrollments WHERE status = 'completed'");
    $totalEnrollments = $stmt->fetch(PDO::FETCH_ASSOC)['total_enrollments'];

    // Active users (logged in within last 30 days)
    $stmt = $db->prepare("
        SELECT COUNT(DISTINCT user_id) as active_users
        FROM user_activities
        WHERE action_type = 'login' AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    ");
    $stmt->execute();
    $activeUsers = $stmt->fetch(PDO::FETCH_ASSOC)['active_users'];

    // Revenue (if applicable)
    $stmt = $db->query("SELECT SUM(payment_amount) as total_revenue FROM course_enrollments WHERE payment_status = 'paid'");
    $totalRevenue = $stmt->fetch(PDO::FETCH_ASSOC)['total_revenue'] ?? 0;

    // Recent activities
    $stmt = $db->query("
        SELECT ua.*, u.username
        FROM user_activities ua
        JOIN users u ON ua.user_id = u.id
        ORDER BY ua.created_at DESC
        LIMIT 10
    ");
    $recentActivities = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Course completion rate
    $stmt = $db->query("
        SELECT
            COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed,
            COUNT(*) as total
        FROM course_enrollments
    ");
    $completionData = $stmt->fetch(PDO::FETCH_ASSOC);
    $completionRate = $completionData['total'] > 0 ? round(($completionData['completed'] / $completionData['total']) * 100, 2) : 0;

    return [
        'overview' => [
            'total_users' => $totalUsers,
            'total_courses' => $totalCourses,
            'total_enrollments' => $totalEnrollments,
            'active_users' => $activeUsers,
            'total_revenue' => $totalRevenue,
            'completion_rate' => $completionRate
        ],
        'recent_activities' => $recentActivities,
        'charts' => [
            'user_growth' => getUserGrowthChart($db),
            'course_popularity' => getCoursePopularityChart($db),
            'revenue_trend' => getRevenueTrendChart($db)
        ]
    ];
}

function getInstructorDashboardStats($db, $userId) {
    // Courses taught by instructor
    $stmt = $db->prepare("SELECT COUNT(*) as total_courses FROM courses WHERE instructor_id = ?");
    $stmt->execute([$userId]);
    $totalCourses = $stmt->fetch(PDO::FETCH_ASSOC)['total_courses'];

    // Total students across all courses
    $stmt = $db->prepare("
        SELECT COUNT(DISTINCT ce.student_id) as total_students
        FROM course_enrollments ce
        JOIN courses c ON ce.course_id = c.id
        WHERE c.instructor_id = ?
    ");
    $stmt->execute([$userId]);
    $totalStudents = $stmt->fetch(PDO::FETCH_ASSOC)['total_students'];

    // Average course rating
    $stmt = $db->prepare("
        SELECT AVG(cr.rating) as avg_rating
        FROM course_reviews cr
        JOIN courses c ON cr.course_id = c.id
        WHERE c.instructor_id = ?
    ");
    $stmt->execute([$userId]);
    $avgRating = round($stmt->fetch(PDO::FETCH_ASSOC)['avg_rating'] ?? 0, 2);

    // Completion rate for instructor's courses
    $stmt = $db->prepare("
        SELECT
            COUNT(CASE WHEN ce.status = 'completed' THEN 1 END) as completed,
            COUNT(*) as total
        FROM course_enrollments ce
        JOIN courses c ON ce.course_id = c.id
        WHERE c.instructor_id = ?
    ");
    $stmt->execute([$userId]);
    $completionData = $stmt->fetch(PDO::FETCH_ASSOC);
    $completionRate = $completionData['total'] > 0 ? round(($completionData['completed'] / $completionData['total']) * 100, 2) : 0;

    // Recent enrollments
    $stmt = $db->prepare("
        SELECT ce.*, c.title as course_title, u.username as student_name
        FROM course_enrollments ce
        JOIN courses c ON ce.course_id = c.id
        JOIN users u ON ce.student_id = u.id
        WHERE c.instructor_id = ?
        ORDER BY ce.enrollment_date DESC
        LIMIT 10
    ");
    $stmt->execute([$userId]);
    $recentEnrollments = $stmt->fetchAll(PDO::FETCH_ASSOC);

    return [
        'overview' => [
            'total_courses' => $totalCourses,
            'total_students' => $totalStudents,
            'avg_rating' => $avgRating,
            'completion_rate' => $completionRate
        ],
        'recent_enrollments' => $recentEnrollments,
        'charts' => [
            'course_performance' => getCoursePerformanceChart($db, $userId),
            'student_engagement' => getStudentEngagementChart($db, $userId),
            'grade_distribution' => getGradeDistributionChart($db, $userId)
        ]
    ];
}

function getStudentDashboardStats($db, $userId) {
    // Enrolled courses
    $stmt = $db->prepare("SELECT COUNT(*) as enrolled_courses FROM course_enrollments WHERE student_id = ? AND status = 'enrolled'");
    $stmt->execute([$userId]);
    $enrolledCourses = $stmt->fetch(PDO::FETCH_ASSOC)['enrolled_courses'];

    // Completed courses
    $stmt = $db->prepare("SELECT COUNT(*) as completed_courses FROM course_enrollments WHERE student_id = ? AND status = 'completed'");
    $stmt->execute([$userId]);
    $completedCourses = $stmt->fetch(PDO::FETCH_ASSOC)['completed_courses'];

    // Average grade
    $stmt = $db->prepare("
        SELECT AVG(ea.percentage) as avg_grade
        FROM exam_attempts ea
        JOIN exams e ON ea.exam_id = e.id
        JOIN course_enrollments ce ON e.course_id = ce.course_id
        WHERE ce.student_id = ? AND ea.status = 'completed'
    ");
    $stmt->execute([$userId]);
    $avgGrade = round($stmt->fetch(PDO::FETCH_ASSOC)['avg_grade'] ?? 0, 2);

    // Study time this week
    $stmt = $db->prepare("
        SELECT SUM(clp.time_spent) as study_time
        FROM course_lesson_progress clp
        JOIN course_enrollments ce ON clp.enrollment_id = ce.id
        WHERE ce.student_id = ? AND clp.last_accessed >= DATE_SUB(NOW(), INTERVAL 7 DAY)
    ");
    $stmt->execute([$userId]);
    $studyTime = $stmt->fetch(PDO::FETCH_ASSOC)['study_time'] ?? 0;

    // Upcoming deadlines
    $stmt = $db->prepare("
        SELECT e.title, e.end_date, c.title as course_title
        FROM exams e
        JOIN courses c ON e.course_id = c.id
        JOIN course_enrollments ce ON c.id = ce.course_id
        WHERE ce.student_id = ? AND e.end_date > NOW()
        ORDER BY e.end_date ASC
        LIMIT 5
    ");
    $stmt->execute([$userId]);
    $upcomingDeadlines = $stmt->fetchAll(PDO::FETCH_ASSOC);

    return [
        'overview' => [
            'enrolled_courses' => $enrolledCourses,
            'completed_courses' => $completedCourses,
            'avg_grade' => $avgGrade,
            'study_time_week' => $studyTime
        ],
        'upcoming_deadlines' => $upcomingDeadlines,
        'charts' => [
            'progress_trend' => getStudentProgressChart($db, $userId),
            'grade_history' => getStudentGradeHistoryChart($db, $userId),
            'course_completion' => getCourseCompletionChart($db, $userId)
        ]
    ];
}

function handleCourseAnalytics($db, $userId, $userRole) {
    $courseId = intval($_GET['course_id'] ?? 0);

    if (!$courseId) {
        throw new Exception('Course ID is required');
    }

    // Verify access
    $stmt = $db->prepare("SELECT * FROM courses WHERE id = ?");
    $stmt->execute([$courseId]);
    $course = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$course) {
        throw new Exception('Course not found');
    }

    if ($userRole !== 'admin' && $course['instructor_id'] !== $userId) {
        throw new Exception('Access denied');
    }

    $analytics = [
        'overview' => getCourseOverviewStats($db, $courseId),
        'student_engagement' => getStudentEngagementStats($db, $courseId),
        'content_performance' => getContentPerformanceStats($db, $courseId),
        'assessment_stats' => getAssessmentStats($db, $courseId),
        'charts' => [
            'enrollment_trend' => getEnrollmentTrendChart($db, $courseId),
            'lesson_completion' => getLessonCompletionChart($db, $courseId),
            'grade_distribution' => getCourseGradeDistributionChart($db, $courseId)
        ]
    ];

    echo json_encode(['success' => true, 'data' => $analytics]);
}

function getCourseOverviewStats($db, $courseId) {
    // Basic course stats
    $stmt = $db->prepare("
        SELECT
            COUNT(CASE WHEN status = 'enrolled' THEN 1 END) as enrolled_students,
            COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_students,
            AVG(progress_percentage) as avg_progress,
            AVG(rating) as avg_rating
        FROM course_enrollments ce
        LEFT JOIN course_reviews cr ON ce.course_id = cr.course_id AND ce.student_id = cr.student_id
        WHERE ce.course_id = ?
    ");
    $stmt->execute([$courseId]);
    $stats = $stmt->fetch(PDO::FETCH_ASSOC);

    // Total lessons and modules
    $stmt = $db->prepare("SELECT COUNT(*) as total_lessons FROM course_lessons WHERE course_id = ?");
    $stmt->execute([$courseId]);
    $totalLessons = $stmt->fetch(PDO::FETCH_ASSOC)['total_lessons'];

    $stmt = $db->prepare("SELECT COUNT(*) as total_modules FROM course_modules WHERE course_id = ?");
    $stmt->execute([$courseId]);
    $totalModules = $stmt->fetch(PDO::FETCH_ASSOC)['total_modules'];

    return [
        'enrolled_students' => intval($stats['enrolled_students']),
        'completed_students' => intval($stats['completed_students']),
        'completion_rate' => intval($stats['enrolled_students']) > 0 ? round((intval($stats['completed_students']) / intval($stats['enrolled_students'])) * 100, 2) : 0,
        'avg_progress' => round(floatval($stats['avg_progress'] ?? 0), 2),
        'avg_rating' => round(floatval($stats['avg_rating'] ?? 0), 2),
        'total_lessons' => $totalLessons,
        'total_modules' => $totalModules
    ];
}

function getStudentEngagementStats($db, $courseId) {
    // Average time spent per student
    $stmt = $db->prepare("
        SELECT AVG(total_time) as avg_time_per_student
        FROM (
            SELECT SUM(clp.time_spent) as total_time
            FROM course_lesson_progress clp
            JOIN course_lessons cl ON clp.lesson_id = cl.id
            WHERE cl.course_id = ?
            GROUP BY clp.enrollment_id
        ) as student_times
    ");
    $stmt->execute([$courseId]);
    $avgTime = $stmt->fetch(PDO::FETCH_ASSOC)['avg_time_per_student'] ?? 0;

    // Most active students
    $stmt = $db->prepare("
        SELECT u.username, SUM(clp.time_spent) as total_time, COUNT(DISTINCT clp.lesson_id) as lessons_completed
        FROM course_lesson_progress clp
        JOIN course_enrollments ce ON clp.enrollment_id = ce.id
        JOIN users u ON ce.student_id = u.id
        JOIN course_lessons cl ON clp.lesson_id = cl.id
        WHERE cl.course_id = ?
        GROUP BY ce.student_id, u.username
        ORDER BY total_time DESC
        LIMIT 10
    ");
    $stmt->execute([$courseId]);
    $activeStudents = $stmt->fetchAll(PDO::FETCH_ASSOC);

    return [
        'avg_time_per_student' => intval($avgTime),
        'most_active_students' => $activeStudents
    ];
}

function getContentPerformanceStats($db, $courseId) {
    // Lesson completion rates
    $stmt = $db->prepare("
        SELECT cl.title, cl.id,
               COUNT(CASE WHEN clp.status = 'completed' THEN 1 END) as completed_count,
               COUNT(clp.enrollment_id) as total_views
        FROM course_lessons cl
        LEFT JOIN course_lesson_progress clp ON cl.id = clp.lesson_id
        WHERE cl.course_id = ?
        GROUP BY cl.id, cl.title
        ORDER BY cl.sort_order
    ");
    $stmt->execute([$courseId]);
    $lessons = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $lessonStats = array_map(function($lesson) {
        return [
            'title' => $lesson['title'],
            'completion_rate' => $lesson['total_views'] > 0 ? round(($lesson['completed_count'] / $lesson['total_views']) * 100, 2) : 0,
            'total_views' => intval($lesson['total_views'])
        ];
    }, $lessons);

    return ['lesson_performance' => $lessonStats];
}

function getAssessmentStats($db, $courseId) {
    // Exam statistics
    $stmt = $db->prepare("
        SELECT e.title, e.id,
               COUNT(ea.id) as total_attempts,
               AVG(ea.percentage) as avg_score,
               COUNT(CASE WHEN ea.is_passed = 1 THEN 1 END) as passed_count
        FROM exams e
        LEFT JOIN exam_attempts ea ON e.id = ea.exam_id
        WHERE e.course_id = ?
        GROUP BY e.id, e.title
    ");
    $stmt->execute([$courseId]);
    $exams = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $examStats = array_map(function($exam) {
        return [
            'title' => $exam['title'],
            'total_attempts' => intval($exam['total_attempts']),
            'avg_score' => round(floatval($exam['avg_score'] ?? 0), 2),
            'pass_rate' => intval($exam['total_attempts']) > 0 ? round((intval($exam['passed_count']) / intval($exam['total_attempts'])) * 100, 2) : 0
        ];
    }, $exams);

    return ['exam_performance' => $examStats];
}

// Chart data functions
function getUserGrowthChart($db) {
    $stmt = $db->query("
        SELECT DATE(created_at) as date, COUNT(*) as count
        FROM users
        WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
        GROUP BY DATE(created_at)
        ORDER BY date
    ");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getCoursePopularityChart($db) {
    $stmt = $db->query("
        SELECT c.title, COUNT(ce.id) as enrollments
        FROM courses c
        LEFT JOIN course_enrollments ce ON c.id = ce.course_id
        GROUP BY c.id, c.title
        ORDER BY enrollments DESC
        LIMIT 10
    ");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getRevenueTrendChart($db) {
    $stmt = $db->query("
        SELECT DATE(enrollment_date) as date, SUM(payment_amount) as revenue
        FROM course_enrollments
        WHERE payment_status = 'paid' AND enrollment_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
        GROUP BY DATE(enrollment_date)
        ORDER BY date
    ");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getCoursePerformanceChart($db, $userId) {
    $stmt = $db->prepare("
        SELECT c.title,
               COUNT(CASE WHEN ce.status = 'completed' THEN 1 END) as completed,
               COUNT(ce.id) as total_enrolled
        FROM courses c
        LEFT JOIN course_enrollments ce ON c.id = ce.course_id
        WHERE c.instructor_id = ?
        GROUP BY c.id, c.title
    ");
    $stmt->execute([$userId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getStudentEngagementChart($db, $userId) {
    $stmt = $db->prepare("
        SELECT DATE(clp.last_accessed) as date, COUNT(DISTINCT clp.enrollment_id) as active_students
        FROM course_lesson_progress clp
        JOIN course_lessons cl ON clp.lesson_id = cl.id
        JOIN courses c ON cl.course_id = c.id
        WHERE c.instructor_id = ? AND clp.last_accessed >= DATE_SUB(NOW(), INTERVAL 30 DAY)
        GROUP BY DATE(clp.last_accessed)
        ORDER BY date
    ");
    $stmt->execute([$userId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getGradeDistributionChart($db, $userId) {
    $stmt = $db->prepare("
        SELECT
            CASE
                WHEN ea.percentage >= 90 THEN 'A (90-100%)'
                WHEN ea.percentage >= 80 THEN 'B (80-89%)'
                WHEN ea.percentage >= 70 THEN 'C (70-79%)'
                WHEN ea.percentage >= 60 THEN 'D (60-69%)'
                ELSE 'F (0-59%)'
            END as grade_range,
            COUNT(*) as count
        FROM exam_attempts ea
        JOIN exams e ON ea.exam_id = e.id
        WHERE e.instructor_id = ?
        GROUP BY grade_range
        ORDER BY grade_range
    ");
    $stmt->execute([$userId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getStudentProgressChart($db, $userId) {
    $stmt = $db->prepare("
        SELECT DATE(clp.last_accessed) as date, AVG(clp.progress_percentage) as avg_progress
        FROM course_lesson_progress clp
        JOIN course_enrollments ce ON clp.enrollment_id = ce.id
        WHERE ce.student_id = ? AND clp.last_accessed >= DATE_SUB(NOW(), INTERVAL 30 DAY)
        GROUP BY DATE(clp.last_accessed)
        ORDER BY date
    ");
    $stmt->execute([$userId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getStudentGradeHistoryChart($db, $userId) {
    $stmt = $db->prepare("
        SELECT DATE(ea.completed_at) as date, ea.percentage as grade, e.title as exam_title
        FROM exam_attempts ea
        JOIN exams e ON ea.exam_id = e.id
        JOIN course_enrollments ce ON e.course_id = ce.course_id
        WHERE ce.student_id = ? AND ea.status = 'completed'
        ORDER BY ea.completed_at DESC
        LIMIT 20
    ");
    $stmt->execute([$userId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getCourseCompletionChart($db, $userId) {
    $stmt = $db->prepare("
        SELECT c.title, ce.progress_percentage as progress
        FROM course_enrollments ce
        JOIN courses c ON ce.course_id = c.id
        WHERE ce.student_id = ?
        ORDER BY ce.enrollment_date DESC
    ");
    $stmt->execute([$userId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getEnrollmentTrendChart($db, $courseId) {
    $stmt = $db->prepare("
        SELECT DATE(enrollment_date) as date, COUNT(*) as enrollments
        FROM course_enrollments
        WHERE course_id = ? AND enrollment_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
        GROUP BY DATE(enrollment_date)
        ORDER BY date
    ");
    $stmt->execute([$courseId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getLessonCompletionChart($db, $courseId) {
    $stmt = $db->prepare("
        SELECT cl.title,
               COUNT(CASE WHEN clp.status = 'completed' THEN 1 END) as completed,
               COUNT(clp.enrollment_id) as total_attempts
        FROM course_lessons cl
        LEFT JOIN course_lesson_progress clp ON cl.id = clp.lesson_id
        WHERE cl.course_id = ?
        GROUP BY cl.id, cl.title
        ORDER BY cl.sort_order
    ");
    $stmt->execute([$courseId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getCourseGradeDistributionChart($db, $courseId) {
    $stmt = $db->prepare("
        SELECT
            CASE
                WHEN ea.percentage >= 90 THEN 'A (90-100%)'
                WHEN ea.percentage >= 80 THEN 'B (80-89%)'
                WHEN ea.percentage >= 70 THEN 'C (70-79%)'
                WHEN ea.percentage >= 60 THEN 'D (60-69%)'
                ELSE 'F (0-59%)'
            END as grade_range,
            COUNT(*) as count
        FROM exam_attempts ea
        JOIN exams e ON ea.exam_id = e.id
        WHERE e.course_id = ?
        GROUP BY grade_range
        ORDER BY grade_range
    ");
    $stmt->execute([$courseId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function handleStudentProgress($db, $userId, $userRole) {
    if ($userRole !== 'student' && $userRole !== 'admin') {
        throw new Exception('Access denied');
    }

    $studentId = $userRole === 'admin' ? intval($_GET['student_id'] ?? $userId) : $userId;
    $courseId = intval($_GET['course_id'] ?? 0);
    $dateFrom = $_GET['date_from'] ?? '';
    $dateTo = $_GET['date_to'] ?? '';

    $dashboardStats = getStudentDashboardStats($db, $studentId);

    $progress = [
        'overview' => $dashboardStats['overview'],
        'course_progress' => getStudentCourseProgress($db, $studentId, $courseId, $dateFrom, $dateTo),
        'grade_history' => getStudentGradeHistory($db, $studentId, $courseId, $dateFrom, $dateTo),
        'achievement_timeline' => getStudentAchievementTimeline($db, $studentId, $dateFrom, $dateTo),
        'upcoming_deadlines' => $dashboardStats['upcoming_deadlines'],
        'study_time' => getStudentStudyTime($db, $studentId),
        'charts' => $dashboardStats['charts']
    ];

    echo json_encode(['success' => true, 'data' => $progress]);
}

function getStudentCourseProgress($db, $studentId, $courseId = 0, $dateFrom = '', $dateTo = '') {
    $where = 'ce.student_id = ?';
    $params = [$studentId];
    if ($courseId) {
        $where .= ' AND ce.course_id = ?';
        $params[] = $courseId;
    }
    if ($dateFrom) {
        $where .= ' AND ce.enrollment_date >= ?';
        $params[] = $dateFrom;
    }
    if ($dateTo) {
        $where .= ' AND ce.enrollment_date <= ?';
        $params[] = $dateTo;
    }
    $stmt = $db->prepare("
        SELECT ce.course_id, c.title, ce.progress_percentage, ce.status, ce.enrollment_date, ce.completion_date
        FROM course_enrollments ce
        JOIN courses c ON ce.course_id = c.id
        WHERE $where
        ORDER BY ce.enrollment_date DESC
    ");
    $stmt->execute($params);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getStudentGradeHistory($db, $studentId, $courseId = 0, $dateFrom = '', $dateTo = '') {
    $where = 'ce.student_id = ? AND ea.status = "completed"';
    $params = [$studentId];
    if ($courseId) {
        $where .= ' AND c.id = ?';
        $params[] = $courseId;
    }
    if ($dateFrom) {
        $where .= ' AND ea.completed_at >= ?';
        $params[] = $dateFrom;
    }
    if ($dateTo) {
        $where .= ' AND ea.completed_at <= ?';
        $params[] = $dateTo;
    }
    $stmt = $db->prepare("
        SELECT e.title as exam_title, c.title as course_title, ea.percentage, ea.is_passed, ea.completed_at
        FROM exam_attempts ea
        JOIN exams e ON ea.exam_id = e.id
        JOIN courses c ON e.course_id = c.id
        JOIN course_enrollments ce ON c.id = ce.course_id
        WHERE $where
        ORDER BY ea.completed_at DESC
    ");
    $stmt->execute($params);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getStudentAchievementTimeline($db, $studentId, $dateFrom = '', $dateTo = '') {
    $where = 'ua.user_id = ? AND ua.action_type IN ("course_completed", "exam_passed", "achievement_earned")';
    $params = [$studentId];
    if ($dateFrom) {
        $where .= ' AND ua.created_at >= ?';
        $params[] = $dateFrom;
    }
    if ($dateTo) {
        $where .= ' AND ua.created_at <= ?';
        $params[] = $dateTo;
    }
    $stmt = $db->prepare("
        SELECT ua.action_description, ua.created_at, ua.metadata
        FROM user_activities ua
        WHERE $where
        ORDER BY ua.created_at DESC
        LIMIT 20
    ");
    $stmt->execute($params);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getStudentStudyTime($db, $studentId) {
    // Weekly study time
    $stmt = $db->prepare("
        SELECT YEARWEEK(clp.last_accessed) as week, SUM(clp.time_spent) as total_time
        FROM course_lesson_progress clp
        JOIN course_enrollments ce ON clp.enrollment_id = ce.id
        WHERE ce.student_id = ? AND clp.last_accessed >= DATE_SUB(NOW(), INTERVAL 12 WEEK)
        GROUP BY YEARWEEK(clp.last_accessed)
        ORDER BY week
    ");
    $stmt->execute([$studentId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function handleSystemLogs($db, $userId, $userRole) {
    if ($userRole !== 'admin') {
        throw new Exception('Access denied');
    }

    $page = intval($_GET['page'] ?? 1);
    $limit = intval($_GET['limit'] ?? 50);
    $offset = ($page - 1) * $limit;

    $filter = $_GET['filter'] ?? '';
    $dateFrom = $_GET['date_from'] ?? '';
    $dateTo = $_GET['date_to'] ?? '';

    $whereClause = '';
    $params = [];

    if ($filter) {
        $whereClause .= " AND ua.action_type LIKE ?";
        $params[] = "%$filter%";
    }

    if ($dateFrom) {
        $whereClause .= " AND ua.created_at >= ?";
        $params[] = $dateFrom;
    }

    if ($dateTo) {
        $whereClause .= " AND ua.created_at <= ?";
        $params[] = $dateTo;
    }

    // Get logs
    $stmt = $db->prepare("
        SELECT ua.*, u.username, u.email
        FROM user_activities ua
        JOIN users u ON ua.user_id = u.id
        WHERE 1=1 $whereClause
        ORDER BY ua.created_at DESC
        LIMIT ? OFFSET ?
    ");
    $params[] = $limit;
    $params[] = $offset;
    $stmt->execute($params);
    $logs = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Get total count
    $stmt = $db->prepare("
        SELECT COUNT(*) as total
        FROM user_activities ua
        WHERE 1=1 $whereClause
    ");
    array_pop($params); // Remove limit and offset
    array_pop($params);
    $stmt->execute($params);
    $total = $stmt->fetch(PDO::FETCH_ASSOC)['total'];

    echo json_encode([
        'success' => true,
        'data' => [
            'logs' => $logs,
            'pagination' => [
                'page' => $page,
                'limit' => $limit,
                'total' => $total,
                'pages' => ceil($total / $limit)
            ]
        ]
    ]);
}

function handleQuestionAnalysis($db, $userId, $userRole) {
    $questionId = intval($_GET['question_id'] ?? 0);
    $courseId = intval($_GET['course_id'] ?? 0);

    if ($questionId) {
        // Single question analysis
        $analysis = getQuestionAnalysis($db, $questionId, $userId, $userRole);
    } elseif ($courseId) {
        // All questions in a course
        $analysis = getCourseQuestionAnalysis($db, $courseId, $userId, $userRole);
    } else {
        // General question analysis for instructor
        $analysis = getInstructorQuestionAnalysis($db, $userId, $userRole);
    }

    echo json_encode(['success' => true, 'data' => $analysis]);
}

function getQuestionAnalysis($db, $questionId, $userId, $userRole) {
    // Verify access
    $stmt = $db->prepare("SELECT * FROM questions WHERE id = ?");
    $stmt->execute([$questionId]);
    $question = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$question) {
        throw new Exception('Question not found');
    }

    if ($userRole !== 'admin' && $question['instructor_id'] !== $userId) {
        throw new Exception('Access denied');
    }

    // Get question statistics
    $stmt = $db->prepare("
        SELECT
            COUNT(*) as total_attempts,
            AVG(CASE WHEN qa.correct_answers > 0 THEN 1 ELSE 0 END) as success_rate,
            AVG(qa.average_time) as avg_time
        FROM question_analytics qa
        WHERE qa.question_id = ?
    ");
    $stmt->execute([$questionId]);
    $stats = $stmt->fetch(PDO::FETCH_ASSOC);

    // Get option analysis for MCQ questions
    $optionAnalysis = [];
    if (in_array($question['question_type'], ['mcq_single', 'mcq_multiple'])) {
        $stmt = $db->prepare("
            SELECT qo.option_text, qo.is_correct, COUNT(*) as times_selected
            FROM question_options qo
            LEFT JOIN exam_attempts ea ON JSON_CONTAINS(JSON_EXTRACT(ea.answers, CONCAT('$.', ?)), CAST(qo.id AS CHAR))
            WHERE qo.question_id = ?
            GROUP BY qo.id, qo.option_text, qo.is_correct
        ");
        $stmt->execute([$questionId, $questionId]);
        $optionAnalysis = $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    return [
        'question' => $question,
        'statistics' => [
            'total_attempts' => intval($stats['total_attempts'] ?? 0),
            'success_rate' => round(floatval($stats['success_rate'] ?? 0) * 100, 2),
            'avg_time' => intval($stats['avg_time'] ?? 0)
        ],
        'option_analysis' => $optionAnalysis,
        'difficulty_analysis' => calculateQuestionDifficulty($question, $stats)
    ];
}

function calculateQuestionDifficulty($question, $stats) {
    $successRate = floatval($stats['success_rate'] ?? 0);
    $avgTime = intval($stats['avg_time'] ?? 0);

    $difficulty = 'medium';
    if ($successRate > 0.8) {
        $difficulty = 'easy';
    } elseif ($successRate < 0.3) {
        $difficulty = 'hard';
    }

    return [
        'calculated_difficulty' => $difficulty,
        'discrimination_index' => $successRate, // Simplified
        'recommendations' => generateQuestionRecommendations($question, $stats)
    ];
}

function generateQuestionRecommendations($question, $stats) {
    $recommendations = [];
    $successRate = floatval($stats['success_rate'] ?? 0);

    if ($successRate > 0.9) {
        $recommendations[] = "Question may be too easy - consider increasing difficulty";
    } elseif ($successRate < 0.2) {
        $recommendations[] = "Question may be too difficult - consider simplifying or providing more context";
    }

    if ($question['usage_count'] < 5) {
        $recommendations[] = "Question has low usage - consider using it in more exams";
    }

    return $recommendations;
}

function getCourseQuestionAnalysis($db, $courseId, $userId, $userRole) {
    // Verify access
    $stmt = $db->prepare("SELECT * FROM courses WHERE id = ?");
    $stmt->execute([$courseId]);
    $course = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$course || ($userRole !== 'admin' && $course['instructor_id'] !== $userId)) {
        throw new Exception('Access denied');
    }

    $stmt = $db->prepare("
        SELECT q.*, qa.total_attempts, qa.success_rate, qa.average_time
        FROM questions q
        LEFT JOIN (
            SELECT question_id, SUM(times_used) as total_attempts,
                   AVG(success_rate) as success_rate, AVG(average_time) as average_time
            FROM question_analytics
            GROUP BY question_id
        ) qa ON q.id = qa.question_id
        WHERE q.course_id = ?
        ORDER BY q.usage_count DESC
    ");
    $stmt->execute([$courseId]);
    $questions = $stmt->fetchAll(PDO::FETCH_ASSOC);

    return ['questions' => $questions];
}

function getInstructorQuestionAnalysis($db, $userId, $userRole) {
    $stmt = $db->prepare("
        SELECT q.*, c.title as course_title, qa.total_attempts, qa.success_rate
        FROM questions q
        LEFT JOIN courses c ON q.course_id = c.id
        LEFT JOIN (
            SELECT question_id, SUM(times_used) as total_attempts, AVG(success_rate) as success_rate
            FROM question_analytics
            GROUP BY question_id
        ) qa ON q.id = qa.question_id
        WHERE q.instructor_id = ?
        ORDER BY q.created_at DESC
        LIMIT 50
    ");
    $stmt->execute([$userId]);
    $questions = $stmt->fetchAll(PDO::FETCH_ASSOC);

    return ['questions' => $questions];
}

function handleComplianceReport($db, $userId, $userRole) {
    if ($userRole !== 'admin') {
        throw new Exception('Access denied');
    }

    $reportType = $_GET['type'] ?? 'user_activity';

    switch ($reportType) {
        case 'user_activity':
            $report = getUserActivityReport($db);
            break;
        case 'data_access':
            $report = getDataAccessReport($db);
            break;
        case 'security_events':
            $report = getSecurityEventsReport($db);
            break;
        default:
            throw new Exception('Invalid report type');
    }

    echo json_encode(['success' => true, 'data' => $report]);
}

function getUserActivityReport($db) {
    $stmt = $db->query("
        SELECT u.username, u.email, u.role, u.created_at,
               COUNT(ua.id) as total_activities,
               MAX(ua.created_at) as last_activity
        FROM users u
        LEFT JOIN user_activities ua ON u.id = ua.user_id
        GROUP BY u.id, u.username, u.email, u.role, u.created_at
        ORDER BY u.created_at DESC
    ");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getDataAccessReport($db) {
    $stmt = $db->query("
        SELECT ua.*, u.username, u.role
        FROM user_activities ua
        JOIN users u ON ua.user_id = u.id
        WHERE ua.action_type IN ('login', 'data_export', 'admin_access')
        ORDER BY ua.created_at DESC
        LIMIT 1000
    ");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getSecurityEventsReport($db) {
    $stmt = $db->query("
        SELECT ua.*, u.username
        FROM user_activities ua
        JOIN users u ON ua.user_id = u.id
        WHERE ua.action_type IN ('failed_login', 'suspicious_activity', 'password_change')
        ORDER BY ua.created_at DESC
        LIMIT 500
    ");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function handleExportData($db, $userId, $userRole) {
    $exportType = $_GET['type'] ?? '';
    $format = $_GET['format'] ?? 'csv';

    switch ($exportType) {
        case 'user_data':
            if ($userRole !== 'admin') throw new Exception('Access denied');
            $data = getUserDataForExport($db);
            break;
        case 'course_data':
            $courseId = intval($_GET['course_id'] ?? 0);
            $data = getCourseDataForExport($db, $courseId, $userId, $userRole);
            break;
        case 'exam_results':
            $examId = intval($_GET['exam_id'] ?? 0);
            $data = getExamResultsForExport($db, $examId, $userId, $userRole);
            break;
        default:
            throw new Exception('Invalid export type');
    }

    if ($format === 'csv') {
        exportAsCSV($data, $exportType);
    } elseif ($format === 'json') {
        exportAsJSON($data);
    } else {
        throw new Exception('Unsupported export format');
    }
}

function getUserDataForExport($db) {
    $stmt = $db->query("
        SELECT u.id, u.username, u.email, u.role, u.created_at,
               up.first_name, up.last_name, up.phone,
               COUNT(ce.id) as enrolled_courses,
               COUNT(CASE WHEN ce.status = 'completed' THEN 1 END) as completed_courses
        FROM users u
        LEFT JOIN user_profiles up ON u.id = up.user_id
        LEFT JOIN course_enrollments ce ON u.id = ce.student_id
        GROUP BY u.id, u.username, u.email, u.role, u.created_at, up.first_name, up.last_name, up.phone
    ");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getCourseDataForExport($db, $courseId, $userId, $userRole) {
    // Verify access
    $stmt = $db->prepare("SELECT * FROM courses WHERE id = ?");
    $stmt->execute([$courseId]);
    $course = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$course || ($userRole !== 'admin' && $course['instructor_id'] !== $userId)) {
        throw new Exception('Access denied');
    }

    $stmt = $db->prepare("
        SELECT u.username, u.email, ce.enrollment_date, ce.status, ce.progress_percentage,
               ce.completion_date, ce.payment_amount
        FROM course_enrollments ce
        JOIN users u ON ce.student_id = u.id
        WHERE ce.course_id = ?
        ORDER BY ce.enrollment_date
    ");
    $stmt->execute([$courseId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getExamResultsForExport($db, $examId, $userId, $userRole) {
    // Verify access
    $stmt = $db->prepare("SELECT e.*, c.instructor_id FROM exams e JOIN courses c ON e.course_id = c.id WHERE e.id = ?");
    $stmt->execute([$examId]);
    $exam = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$exam || ($userRole !== 'admin' && $exam['instructor_id'] !== $userId)) {
        throw new Exception('Access denied');
    }

    $stmt = $db->prepare("
        SELECT u.username, u.email, ea.started_at, ea.completed_at,
               ea.score, ea.max_score, ea.percentage, ea.is_passed
        FROM exam_attempts ea
        JOIN users u ON ea.student_id = u.id
        WHERE ea.exam_id = ?
        ORDER BY ea.completed_at
    ");
    $stmt->execute([$examId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function exportAsCSV($data, $filename) {
    if (empty($data)) {
        throw new Exception('No data to export');
    }

    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="' . $filename . '.csv"');

    $output = fopen('php://output', 'w');

    // Write headers
    fputcsv($output, array_keys($data[0]));

    // Write data
    foreach ($data as $row) {
        fputcsv($output, $row);
    }

    fclose($output);
    exit;
}

function exportAsJSON($data) {
    header('Content-Type: application/json');
    header('Content-Disposition: attachment; filename="export.json"');
    echo json_encode($data);
    exit;
}

function handleRealTimeStats($db, $userId, $userRole) {
    $stats = [];

    if ($userRole === 'admin') {
        $stats = [
            'active_users' => getActiveUsersCount($db),
            'ongoing_exams' => getOngoingExamsCount($db),
            'system_load' => getSystemLoad($db),
            'recent_activities' => getRecentActivities($db, 5)
        ];
    } elseif ($userRole === 'instructor') {
        $stats = [
            'active_students' => getInstructorActiveStudents($db, $userId),
            'ongoing_exams' => getInstructorOngoingExams($db, $userId),
            'recent_submissions' => getRecentSubmissions($db, $userId, 5)
        ];
    } else {
        $stats = [
            'current_progress' => getStudentCurrentProgress($db, $userId),
            'upcoming_deadlines' => getStudentUpcomingDeadlines($db, $userId, 3)
        ];
    }

    echo json_encode(['success' => true, 'data' => $stats]);
}

function getActiveUsersCount($db) {
    $stmt = $db->query("
        SELECT COUNT(DISTINCT ua.user_id) as active_users
        FROM user_activities ua
        WHERE ua.created_at >= DATE_SUB(NOW(), INTERVAL 5 MINUTE)
    ");
    return $stmt->fetch(PDO::FETCH_ASSOC)['active_users'];
}

function getOngoingExamsCount($db) {
    $stmt = $db->query("
        SELECT COUNT(*) as ongoing_exams
        FROM exam_attempts
        WHERE status = 'in_progress'
    ");
    return $stmt->fetch(PDO::FETCH_ASSOC)['ongoing_exams'];
}

function getSystemLoad($db) {
    // Simplified system load - could be extended with actual server metrics
    $stmt = $db->query("SELECT COUNT(*) as active_sessions FROM user_activities WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR)");
    $activeSessions = $stmt->fetch(PDO::FETCH_ASSOC)['active_sessions'];

    return [
        'active_sessions' => $activeSessions,
        'status' => $activeSessions > 100 ? 'high' : ($activeSessions > 50 ? 'medium' : 'low')
    ];
}

function getRecentActivities($db, $limit) {
    $stmt = $db->prepare("
        SELECT ua.action_description, u.username, ua.created_at
        FROM user_activities ua
        JOIN users u ON ua.user_id = u.id
        ORDER BY ua.created_at DESC
        LIMIT ?
    ");
    $stmt->execute([$limit]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getInstructorActiveStudents($db, $userId) {
    $stmt = $db->prepare("
        SELECT COUNT(DISTINCT clp.enrollment_id) as active_students
        FROM course_lesson_progress clp
        JOIN course_lessons cl ON clp.lesson_id = cl.id
        JOIN courses c ON cl.course_id = c.id
        WHERE c.instructor_id = ? AND clp.last_accessed >= DATE_SUB(NOW(), INTERVAL 30 MINUTE)
    ");
    $stmt->execute([$userId]);
    return $stmt->fetch(PDO::FETCH_ASSOC)['active_students'];
}

function getInstructorOngoingExams($db, $userId) {
    $stmt = $db->prepare("
        SELECT COUNT(*) as ongoing_exams
        FROM exam_attempts ea
        JOIN exams e ON ea.exam_id = e.id
        WHERE e.instructor_id = ? AND ea.status = 'in_progress'
    ");
    $stmt->execute([$userId]);
    return $stmt->fetch(PDO::FETCH_ASSOC)['ongoing_exams'];
}

function getRecentSubmissions($db, $userId, $limit) {
    $stmt = $db->prepare("
        SELECT ea.id, u.username, e.title as exam_title, ea.completed_at
        FROM exam_attempts ea
        JOIN exams e ON ea.exam_id = e.id
        JOIN users u ON ea.student_id = u.id
        WHERE e.instructor_id = ? AND ea.status = 'completed'
        ORDER BY ea.completed_at DESC
        LIMIT ?
    ");
    $stmt->execute([$userId, $limit]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getStudentCurrentProgress($db, $userId) {
    $stmt = $db->prepare("
        SELECT c.title, ce.progress_percentage
        FROM course_enrollments ce
        JOIN courses c ON ce.course_id = c.id
        WHERE ce.student_id = ? AND ce.status = 'enrolled'
        ORDER BY ce.enrollment_date DESC
        LIMIT 5
    ");
    $stmt->execute([$userId]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getStudentUpcomingDeadlines($db, $userId, $limit) {
    $stmt = $db->prepare("
        SELECT e.title, e.end_date, c.title as course_title
        FROM exams e
        JOIN courses c ON e.course_id = c.id
        JOIN course_enrollments ce ON c.id = ce.course_id
        WHERE ce.student_id = ? AND e.end_date > NOW()
        ORDER BY e.end_date ASC
        LIMIT ?
    ");
    $stmt->execute([$userId, $limit]);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}