<?php
include '../config.php';

if (!isAdmin()) {
    redirect('../login.php');
}

// Set default date range (last 30 days)
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-d', strtotime('-30 days'));
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-d');

// Validate dates
if ($start_date > $end_date) {
    $start_date = date('Y-m-d', strtotime('-30 days'));
    $end_date = date('Y-m-d');
}

// Overall Statistics
$sql_stats = "SELECT 
    (SELECT COUNT(*) FROM lms_users WHERE role = 'siswa') as total_siswa,
    (SELECT COUNT(*) FROM lms_users WHERE role = 'guru') as total_guru,
    (SELECT COUNT(*) FROM lms_courses) as total_lms_courses,
    (SELECT COUNT(*) FROM lms_enrollments) as total_lms_enrollments,
    (SELECT COUNT(*) FROM lms_enrollments WHERE status = 'completed') as completed_lms_enrollments,
    (SELECT COUNT(*) FROM lms_enrollments WHERE DATE(tanggal_enroll) BETWEEN '$start_date' AND '$end_date') as new_lms_enrollments,
    (SELECT COUNT(*) FROM lms_users WHERE role = 'siswa' AND DATE(tanggal_daftar) BETWEEN '$start_date' AND '$end_date') as new_siswa,
    (SELECT COUNT(*) FROM lms_courses WHERE DATE(created_at) BETWEEN '$start_date' AND '$end_date') as new_lms_courses";

$stats = $conn->query($sql_stats)->fetch_assoc();

// Monthly enrollment data for chart
$sql_monthly_lms_enrollments = "SELECT 
    DATE_FORMAT(tanggal_enroll, '%Y-%m') as month,
    COUNT(*) as count
    FROM lms_enrollments 
    WHERE tanggal_enroll >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
    GROUP BY DATE_FORMAT(tanggal_enroll, '%Y-%m')
    ORDER BY month DESC 
    LIMIT 6";

$monthly_lms_enrollments = $conn->query($sql_monthly_lms_enrollments);

// Top lms_courses by enrollment
$sql_top_lms_courses = "SELECT 
    c.judul,
    cat.nama_kategori,
    u.nama_lengkap as guru,
    COUNT(e.id) as total_lms_enrollments,
    c.rating,
    c.total_siswa
    FROM lms_courses c
    LEFT JOIN lms_enrollments e ON c.id = e.id_course
    JOIN lms_users u ON c.id_guru = u.id
    JOIN lms_categories cat ON c.id_kategori = cat.id
    GROUP BY c.id
    ORDER BY total_lms_enrollments DESC
    LIMIT 10";

$top_lms_courses = $conn->query($sql_top_lms_courses);

// User registration trend
$sql_user_trend = "SELECT 
    DATE_FORMAT(tanggal_daftar, '%Y-%m-%d') as date,
    SUM(CASE WHEN role = 'siswa' THEN 1 ELSE 0 END) as siswa,
    SUM(CASE WHEN role = 'guru' THEN 1 ELSE 0 END) as guru,
    SUM(CASE WHEN role = 'admin' THEN 1 ELSE 0 END) as admin
    FROM lms_users 
    WHERE tanggal_daftar BETWEEN '$start_date' AND '$end_date'
    GROUP BY DATE_FORMAT(tanggal_daftar, '%Y-%m-%d')
    ORDER BY date";

$user_trend = $conn->query($sql_user_trend);

// Course completion rate
$sql_completion_rate = "SELECT 
    c.judul,
    COUNT(e.id) as total_lms_enrollments,
    SUM(CASE WHEN e.status = 'completed' THEN 1 ELSE 0 END) as completed,
    ROUND((SUM(CASE WHEN e.status = 'completed' THEN 1 ELSE 0 END) / COUNT(e.id)) * 100, 2) as completion_rate
    FROM lms_courses c
    LEFT JOIN lms_enrollments e ON c.id = e.id_course
    GROUP BY c.id
    HAVING total_lms_enrollments > 0
    ORDER BY completion_rate DESC
    LIMIT 10";

$completion_rates = $conn->query($sql_completion_rate);

// Popular lms_categories - PERBAIKAN: Handle NULL values
$sql_popular_lms_categories = "SELECT 
    cat.nama_kategori,
    COUNT(c.id) as total_lms_courses,
    COUNT(e.id) as total_lms_enrollments,
    COALESCE(AVG(c.rating), 0) as avg_rating
    FROM lms_categories cat
    LEFT JOIN lms_courses c ON cat.id = c.id_kategori
    LEFT JOIN lms_enrollments e ON c.id = e.id_course
    GROUP BY cat.id
    ORDER BY total_lms_enrollments DESC";

$popular_lms_categories = $conn->query($sql_popular_lms_categories);
?>

<!DOCTYPE html>
<html lang="id">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Laporan - SMANSAB LMS</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/css/bootstrap.min.css" rel="stylesheet">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.10.0/font/bootstrap-icons.css">
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <style>
        .stat-card {
            border: none;
            border-radius: 10px;
            transition: transform 0.3s;
        }
        .stat-card:hover {
            transform: translateY(-5px);
        }
        .chart-container {
            position: relative;
            height: 300px;
            margin-bottom: 2rem;
        }
        .table-hover tbody tr:hover {
            background-color: rgba(0, 123, 255, 0.1);
        }
        .completion-high { background-color: #d4edda; }
        .completion-medium { background-color: #fff3cd; }
        .completion-low { background-color: #f8d7da; }
        .no-rating { color: #6c757d; }
    </style>
</head>
<body>
    <!-- Navbar Admin -->
    <nav class="navbar navbar-expand-lg navbar-dark bg-primary">
        <div class="container">
            <a class="navbar-brand fw-bold" href="dashboard.php">SMANSAB LMS - Admin</a>
            <div class="navbar-nav ms-auto">
                <a class="nav-link" href="manage_users.php">Kelola User</a>
                <a class="nav-link" href="manage_courses.php">Kelola Kursus</a>
                <a class="nav-link active" href="reports.php">Laporan</a>
                <a class="nav-link" href="../logout.php">Logout</a>
            </div>
        </div>
    </nav>

    <div class="container mt-4">
        <!-- Header -->
        <div class="d-flex justify-content-between align-items-center mb-4">
            <h2><i class="bi bi-graph-up"></i> Laporan & Analitik</h2>
            <div class="btn-group">
                <button class="btn btn-outline-primary" onclick="window.print()">
                    <i class="bi bi-printer"></i> Print
                </button>
                <a href="reports.php?export=csv" class="btn btn-outline-success">
                    <i class="bi bi-file-earmark-spreadsheet"></i> Export CSV
                </a>
            </div>
        </div>

        <!-- Date Filter -->
        <div class="card mb-4">
            <div class="card-header">
                <h5 class="card-title mb-0"><i class="bi bi-filter"></i> Filter Laporan</h5>
            </div>
            <div class="card-body">
                <form method="GET" action="" class="row g-3">
                    <div class="col-md-4">
                        <label for="start_date" class="form-label">Dari Tanggal</label>
                        <input type="date" class="form-control" id="start_date" name="start_date" 
                               value="<?php echo $start_date; ?>">
                    </div>
                    <div class="col-md-4">
                        <label for="end_date" class="form-label">Sampai Tanggal</label>
                        <input type="date" class="form-control" id="end_date" name="end_date" 
                               value="<?php echo $end_date; ?>">
                    </div>
                    <div class="col-md-4 d-flex align-items-end">
                        <button type="submit" class="btn btn-primary w-100">
                            <i class="bi bi-filter"></i> Terapkan Filter
                        </button>
                    </div>
                </form>
            </div>
        </div>

        <!-- Overall Statistics -->
        <div class="row mb-4">
            <div class="col-md-3">
                <div class="stat-card card bg-primary text-white">
                    <div class="card-body text-center">
                        <i class="bi bi-people display-6"></i>
                        <h3><?php echo $stats['total_siswa']; ?></h3>
                        <p class="mb-0">Total Siswa</p>
                        <small>+<?php echo $stats['new_siswa']; ?> baru</small>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="stat-card card bg-success text-white">
                    <div class="card-body text-center">
                        <i class="bi bi-person-badge display-6"></i>
                        <h3><?php echo $stats['total_guru']; ?></h3>
                        <p class="mb-0">Total Guru</p>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="stat-card card bg-warning text-dark">
                    <div class="card-body text-center">
                        <i class="bi bi-book display-6"></i>
                        <h3><?php echo $stats['total_lms_courses']; ?></h3>
                        <p class="mb-0">Total Kursus</p>
                        <small>+<?php echo $stats['new_lms_courses']; ?> baru</small>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="stat-card card bg-info text-white">
                    <div class="card-body text-center">
                        <i class="bi bi-graph-up display-6"></i>
                        <h3><?php echo $stats['total_lms_enrollments']; ?></h3>
                        <p class="mb-0">Total Enrollment</p>
                        <small>+<?php echo $stats['new_lms_enrollments']; ?> baru</small>
                    </div>
                </div>
            </div>
        </div>

        <!-- Charts Row -->
        <div class="row mb-4">
            <div class="col-md-8">
                <div class="card">
                    <div class="card-header">
                        <h5 class="card-title mb-0"><i class="bi bi-bar-chart"></i> Trend Pendaftaran (6 Bulan)</h5>
                    </div>
                    <div class="card-body">
                        <div class="chart-container">
                            <canvas id="enrollmentChart"></canvas>
                        </div>
                    </div>
                </div>
            </div>
            <div class="col-md-4">
                <div class="card">
                    <div class="card-header">
                        <h5 class="card-title mb-0"><i class="bi bi-pie-chart"></i> Distribusi Kategori</h5>
                    </div>
                    <div class="card-body">
                        <div class="chart-container">
                            <canvas id="categoryChart"></canvas>
                        </div>
                    </div>
                </div>
            </div>
        </div>

        <!-- Top lms_courses -->
        <div class="card mb-4">
            <div class="card-header">
                <h5 class="card-title mb-0"><i class="bi bi-trophy"></i> 10 Kursus Terpopuler</h5>
            </div>
            <div class="card-body">
                <div class="table-responsive">
                    <table class="table table-striped table-hover">
                        <thead>
                            <tr>
                                <th>#</th>
                                <th>Kursus</th>
                                <th>Guru</th>
                                <th>Kategori</th>
                                <th>Enrollment</th>
                                <th>Rating</th>
                                <th>Siswa</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php $rank = 1; ?>
                            <?php while($course = $top_lms_courses->fetch_assoc()): ?>
                            <tr>
                                <td><?php echo $rank++; ?></td>
                                <td>
                                    <strong><?php echo htmlspecialchars($course['judul']); ?></strong>
                                </td>
                                <td><?php echo htmlspecialchars($course['guru']); ?></td>
                                <td>
                                    <span class="badge bg-info"><?php echo htmlspecialchars($course['nama_kategori']); ?></span>
                                </td>
                                <td>
                                    <span class="badge bg-primary"><?php echo $course['total_lms_enrollments']; ?></span>
                                </td>
                                <td>
                                    <div class="d-flex align-items-center">
                                        <i class="bi bi-star-fill text-warning me-1"></i>
                                        <span><?php echo number_format($course['rating'], 1); ?></span>
                                    </div>
                                </td>
                                <td><?php echo $course['total_siswa']; ?></td>
                            </tr>
                            <?php endwhile; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>

        <!-- Completion Rates -->
        <div class="card mb-4">
            <div class="card-header">
                <h5 class="card-title mb-0"><i class="bi bi-check-circle"></i> Tingkat Penyelesaian Kursus</h5>
            </div>
            <div class="card-body">
                <div class="table-responsive">
                    <table class="table table-striped table-hover">
                        <thead>
                            <tr>
                                <th>Kursus</th>
                                <th>Total Enrollment</th>
                                <th>Selesai</th>
                                <th>Tingkat Penyelesaian</th>
                                <th>Status</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php while($rate = $completion_rates->fetch_assoc()): ?>
                            <?php 
                            $completion_class = '';
                            if ($rate['completion_rate'] >= 70) {
                                $completion_class = 'completion-high';
                            } elseif ($rate['completion_rate'] >= 40) {
                                $completion_class = 'completion-medium';
                            } else {
                                $completion_class = 'completion-low';
                            }
                            ?>
                            <tr class="<?php echo $completion_class; ?>">
                                <td><strong><?php echo htmlspecialchars($rate['judul']); ?></strong></td>
                                <td><?php echo $rate['total_lms_enrollments']; ?></td>
                                <td><?php echo $rate['completed']; ?></td>
                                <td>
                                    <div class="progress">
                                        <div class="progress-bar 
                                            <?php echo $rate['completion_rate'] >= 70 ? 'bg-success' : 
                                                  ($rate['completion_rate'] >= 40 ? 'bg-warning' : 'bg-danger'); ?>" 
                                            style="width: <?php echo $rate['completion_rate']; ?>%">
                                            <?php echo $rate['completion_rate']; ?>%
                                        </div>
                                    </div>
                                </td>
                                <td>
                                    <span class="badge 
                                        <?php echo $rate['completion_rate'] >= 70 ? 'bg-success' : 
                                              ($rate['completion_rate'] >= 40 ? 'bg-warning' : 'bg-danger'); ?>">
                                        <?php echo $rate['completion_rate'] >= 70 ? 'Tinggi' : 
                                              ($rate['completion_rate'] >= 40 ? 'Sedang' : 'Rendah'); ?>
                                    </span>
                                </td>
                            </tr>
                            <?php endwhile; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>

        <!-- Popular lms_categories -->
        <div class="card">
            <div class="card-header">
                <h5 class="card-title mb-0"><i class="bi bi-tags"></i> Kategori Populer</h5>
            </div>
            <div class="card-body">
                <div class="table-responsive">
                    <table class="table table-striped table-hover">
                        <thead>
                            <tr>
                                <th>Kategori</th>
                                <th>Jumlah Kursus</th>
                                <th>Total Enrollment</th>
                                <th>Rating Rata-rata</th>
                                <th>Popularitas</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php while($category = $popular_lms_categories->fetch_assoc()): ?>
                            <tr>
                                <td>
                                    <strong><?php echo htmlspecialchars($category['nama_kategori']); ?></strong>
                                </td>
                                <td><?php echo $category['total_lms_courses']; ?></td>
                                <td>
                                    <span class="badge bg-primary"><?php echo $category['total_lms_enrollments']; ?></span>
                                </td>
                                <td>
                                    <div class="d-flex align-items-center">
                                        <i class="bi bi-star-fill text-warning me-1"></i>
                                        <span class="<?php echo $category['avg_rating'] == 0 ? 'no-rating' : ''; ?>">
                                            <?php 
                                            // Handle rating display untuk nilai 0 (tidak ada rating)
                                            if ($category['avg_rating'] == 0) {
                                                echo 'Belum ada rating';
                                            } else {
                                                echo number_format($category['avg_rating'], 1);
                                            }
                                            ?>
                                        </span>
                                    </div>
                                </td>
                                <td>
                                    <?php 
                                    $popularity = 'Sedang';
                                    $badge_class = 'bg-info';
                                    
                                    if ($category['total_lms_enrollments'] > 100) {
                                        $popularity = 'Sangat Populer';
                                        $badge_class = 'bg-danger';
                                    } elseif ($category['total_lms_enrollments'] > 50) {
                                        $popularity = 'Populer';
                                        $badge_class = 'bg-warning';
                                    } elseif ($category['total_lms_enrollments'] == 0) {
                                        $popularity = 'Belum Ada';
                                        $badge_class = 'bg-secondary';
                                    }
                                    ?>
                                    <span class="badge <?php echo $badge_class; ?>"><?php echo $popularity; ?></span>
                                </td>
                            </tr>
                            <?php endwhile; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

    <script>
        // Enrollment Chart
        const enrollmentCtx = document.getElementById('enrollmentChart').getContext('2d');
        <?php
        // Prepare data for enrollment chart
        $monthly_lms_enrollments->data_seek(0);
        $months = [];
        $enrollment_data = [];
        while($row = $monthly_lms_enrollments->fetch_assoc()) {
            $months[] = date('M Y', strtotime($row['month']));
            $enrollment_data[] = $row['count'];
        }
        $months = array_reverse($months);
        $enrollment_data = array_reverse($enrollment_data);
        ?>
        
        const enrollmentChart = new Chart(enrollmentCtx, {
            type: 'line',
            data: {
                labels: [<?php echo "'" . implode("','", $months) . "'"; ?>],
                datasets: [{
                    label: 'Pendaftaran',
                    data: [<?php echo implode(',', $enrollment_data); ?>],
                    borderColor: '#007bff',
                    backgroundColor: 'rgba(0, 123, 255, 0.1)',
                    tension: 0.4,
                    fill: true
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                scales: {
                    y: {
                        beginAtZero: true,
                        ticks: {
                            stepSize: 1
                        }
                    }
                }
            }
        });

        // Category Chart
        const categoryCtx = document.getElementById('categoryChart').getContext('2d');
        <?php
        // Prepare data for category chart
        $popular_lms_categories->data_seek(0);
        $category_labels = [];
        $category_data = [];
        while($category = $popular_lms_categories->fetch_assoc()) {
            $category_labels[] = $category['nama_kategori'];
            $category_data[] = $category['total_lms_enrollments'];
        }
        ?>
        
        const categoryChart = new Chart(categoryCtx, {
            type: 'doughnut',
            data: {
                labels: [<?php echo "'" . implode("','", $category_labels) . "'"; ?>],
                datasets: [{
                    data: [<?php echo implode(',', $category_data); ?>],
                    backgroundColor: [
                        '#007bff', '#28a745', '#ffc107', '#dc3545', '#6f42c1',
                        '#e83e8c', '#fd7e14', '#20c997', '#6610f2', '#6c757d'
                    ]
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: {
                        position: 'bottom'
                    }
                }
            }
        });
    </script>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>