<?php
session_start();
require_once '../config/database.php';

// Check if user is logged in and is admin or teacher
if (!isset($_SESSION['user_id']) || !in_array($_SESSION['user_role'], ['admin', 'teacher'])) {
    header('Location: ../login.php');
    exit();
}

// Get search filters
$search_session = isset($_GET['search_session']) ? $_GET['search_session'] : '';
$search_class = isset($_GET['search_class']) ? (int)$_GET['search_class'] : 0;
$search_group = isset($_GET['search_group']) ? $_GET['search_group'] : '';

// Get data for dropdowns
$subjects = $pdo->query("SELECT id, name, code FROM subjects WHERE status = 'active' ORDER BY name")->fetchAll();
$classes = $pdo->query("SELECT id, name, section FROM classes WHERE status = 'active' ORDER BY name, section")->fetchAll();
$sessions = $pdo->query("SELECT session_name FROM academic_sessions WHERE status = 'active' ORDER BY session_name DESC")->fetchAll();
$groups = $pdo->query("SELECT group_name, group_code, description FROM student_groups WHERE status = 'active' ORDER BY group_name")->fetchAll();

// Get class and group analysis data
$analysis_data = [];

if ($search_session || $search_class || $search_group) {
    // Build query with search filters
    $sql = "SELECT 
                c.name as class_name, 
                c.section,
                g.group_name as group_display_name,
                smd.session,
                COUNT(smd.id) as distribution_count,
                SUM(smd.total_marks) as total_marks_sum,
                AVG(smd.total_marks) as avg_total_marks
            FROM subject_mark_distribution smd
            JOIN classes c ON smd.class_id = c.id
            LEFT JOIN student_groups g ON smd.group_name = g.group_name
            WHERE 1=1";
    $params = [];
    
    if ($search_session) {
        $sql .= " AND smd.session = ?";
        $params[] = $search_session;
    }
    
    if ($search_class) {
        $sql .= " AND smd.class_id = ?";
        $params[] = $search_class;
    }
    
    if ($search_group) {
        $sql .= " AND smd.group_name = ?";
        $params[] = $search_group;
    }
    
    $sql .= " GROUP BY c.id, c.name, c.section, g.group_name, smd.session ORDER BY smd.session DESC, c.name, g.group_name";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $analysis_data = $stmt->fetchAll(PDO::FETCH_ASSOC);
} else {
    // Get all analysis data without search
    $sql = "SELECT 
                c.name as class_name, 
                c.section,
                g.group_name as group_display_name,
                smd.session,
                COUNT(smd.id) as distribution_count,
                SUM(smd.total_marks) as total_marks_sum,
                AVG(smd.total_marks) as avg_total_marks
            FROM subject_mark_distribution smd
            JOIN classes c ON smd.class_id = c.id
            LEFT JOIN student_groups g ON smd.group_name = g.group_name
            GROUP BY c.id, c.name, c.section, g.group_name, smd.session 
            ORDER BY smd.session DESC, c.name, g.group_name";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();
    $analysis_data = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// Get overall statistics
$stats_sql = "SELECT 
                COUNT(DISTINCT smd.session) as total_sessions,
                COUNT(DISTINCT c.id) as total_classes,
                COUNT(DISTINCT g.group_name) as total_groups,
                COUNT(smd.id) as total_distributions,
                AVG(smd.total_marks) as overall_avg_marks
            FROM subject_mark_distribution smd
            JOIN classes c ON smd.class_id = c.id
            LEFT JOIN student_groups g ON smd.group_name = g.group_name";

$stats_stmt = $pdo->prepare($stats_sql);
$stats_stmt->execute();
$overall_stats = $stats_stmt->fetch(PDO::FETCH_ASSOC);

// Group data by class for visualization
$grouped_by_class = [];
foreach ($analysis_data as $item) {
    $class_key = $item['class_name'] . ' - ' . $item['section'];
    if (!isset($grouped_by_class[$class_key])) {
        $grouped_by_class[$class_key] = [];
    }
    $grouped_by_class[$class_key][] = $item;
}

// Group data by group for visualization
$grouped_by_group = [];
foreach ($analysis_data as $item) {
    $group_key = $item['group_display_name'] ?: 'General';
    if (!isset($grouped_by_group[$group_key])) {
        $grouped_by_group[$group_key] = [];
    }
    $grouped_by_group[$group_key][] = $item;
}

// Group data by session for visualization
$grouped_by_session = [];
foreach ($analysis_data as $item) {
    $session_key = $item['session'];
    if (!isset($grouped_by_session[$session_key])) {
        $grouped_by_session[$session_key] = [];
    }
    $grouped_by_session[$session_key][] = $item;
}
?>

<!DOCTYPE html>
<html lang="bn">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>ক্লাস ও গ্রুপ বিশ্লেষণ - স্কুল ম্যানেজমেন্ট সিস্টেম</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/css/bootstrap.min.css" rel="stylesheet">
    <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css" rel="stylesheet">
    <link href="../assets/css/style.css" rel="stylesheet">
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <style>
        :root {
            --primary-color: #4361ee;
            --secondary-color: #3f37c9;
            --success-color: #4cc9f0;
            --info-color: #4895ef;
            --warning-color: #f72585;
            --light-bg: #f8f9fa;
            --dark-text: #212529;
        }
        
        body {
            background-color: #f5f7fb;
            font-family: 'Vazirmatn', 'Segoe UI', sans-serif;
        }
        
        .modern-card {
            border-radius: 12px;
            box-shadow: 0 4px 20px rgba(0, 0, 0, 0.08);
            border: none;
            transition: transform 0.3s ease, box-shadow 0.3s ease;
        }
        
        .modern-card:hover {
            transform: translateY(-5px);
            box-shadow: 0 8px 30px rgba(0, 0, 0, 0.12);
        }
        
        .stat-card {
            background: linear-gradient(135deg, #f5f7fa 0%, #e4edf5 100%);
            border-left: 4px solid var(--primary-color);
        }
        
        .header-gradient {
            background: linear-gradient(135deg, var(--primary-color) 0%, var(--secondary-color) 100%);
            color: white;
            border-radius: 12px 12px 0 0;
        }
        
        .btn-modern {
            border-radius: 8px;
            padding: 8px 16px;
            font-weight: 500;
            transition: all 0.3s ease;
        }
        
        .btn-primary-modern {
            background: linear-gradient(135deg, var(--primary-color) 0%, var(--secondary-color) 100%);
            border: none;
        }
        
        .btn-primary-modern:hover {
            background: linear-gradient(135deg, var(--secondary-color) 0%, #3a33b5 100%);
            transform: translateY(-2px);
        }
        
        .table-modern {
            border-collapse: separate;
            border-spacing: 0;
            border-radius: 12px;
            overflow: hidden;
        }
        
        .table-modern th {
            background-color: var(--primary-color);
            color: white;
            font-weight: 600;
        }
        
        .table-modern tr:nth-child(even) {
            background-color: rgba(67, 97, 238, 0.05);
        }
        
        .chart-container {
            position: relative;
            height: 300px;
            margin-bottom: 20px;
        }
        
        .filter-section {
            background: white;
            border-radius: 12px;
            padding: 20px;
            box-shadow: 0 2px 10px rgba(0, 0, 0, 0.05);
        }
        
        .highlight-box {
            background: linear-gradient(135deg, #e0f7fa 0%, #bbdefb 100%);
            border-left: 4px solid var(--info-color);
            border-radius: 8px;
            padding: 15px;
            margin-bottom: 15px;
        }
        
        .session-badge {
            background: linear-gradient(135deg, var(--success-color) 0%, #4895ef 100%);
            color: white;
            padding: 5px 12px;
            border-radius: 20px;
            font-size: 0.85rem;
            font-weight: 500;
        }
        
        .class-badge {
            background: linear-gradient(135deg, #ff9a9e 0%, #fad0c4 100%);
            color: #333;
            padding: 5px 12px;
            border-radius: 20px;
            font-size: 0.85rem;
            font-weight: 500;
        }
        
        .group-badge {
            background: linear-gradient(135deg, #a1c4fd 0%, #c2e9fb 100%);
            color: #333;
            padding: 5px 12px;
            border-radius: 20px;
            font-size: 0.85rem;
            font-weight: 500;
        }
    </style>
</head>
<body class="bengali-text">
    <!-- Navigation -->
    <nav class="navbar navbar-expand-lg navbar-dark bg-primary shadow-sm">
        <div class="container-fluid">
            <a class="navbar-brand d-flex align-items-center" href="../index.php">
                <i class="fas fa-chart-bar me-2"></i> ক্লাস ও গ্রুপ বিশ্লেষণ
            </a>
            
            <div class="navbar-nav ms-auto">
                <a class="nav-link" href="../index.php">
                    <i class="fas fa-home"></i> হোম
                </a>
                <a class="nav-link" href="setup.php">
                    <i class="fas fa-cog"></i> মার্কস সেটআপ
                </a>
                <a class="nav-link" href="analysis.php">
                    <i class="fas fa-chart-bar"></i> বিশ্লেষণ
                </a>
                <a class="nav-link" href="entry.php">
                    <i class="fas fa-edit"></i> মার্কস এন্ট্রি
                </a>
            </div>
        </div>
    </nav>

    <div class="container-fluid mt-4 px-4">
        <div class="row">
            <!-- Main Content -->
            <div class="col-12">
                <div class="modern-card">
                    <div class="header-gradient p-4">
                        <h2 class="mb-2"><i class="fas fa-analytics me-3"></i>ক্লাস ও গ্রুপ বিশ্লেষণ প্যানেল</h2>
                        <p class="mb-0 opacity-75">সকল সেশনের জন্য ক্লাস ও গ্রুপ অনুযায়ী বিশ্লেষণ</p>
                    </div>
                    
                    <div class="card-body p-4">
                        <!-- Statistics Overview -->
                        <div class="row mb-4">
                            <div class="col-md-2 col-6 mb-3">
                                <div class="stat-card p-3 text-center">
                                    <div class="fs-2 fw-bold text-primary"><?php echo $overall_stats['total_sessions']; ?></div>
                                    <div class="text-muted">মোট সেশন</div>
                                </div>
                            </div>
                            <div class="col-md-2 col-6 mb-3">
                                <div class="stat-card p-3 text-center">
                                    <div class="fs-2 fw-bold text-primary"><?php echo $overall_stats['total_classes']; ?></div>
                                    <div class="text-muted">মোট ক্লাস</div>
                                </div>
                            </div>
                            <div class="col-md-2 col-6 mb-3">
                                <div class="stat-card p-3 text-center">
                                    <div class="fs-2 fw-bold text-primary"><?php echo $overall_stats['total_groups']; ?></div>
                                    <div class="text-muted">মোট গ্রুপ</div>
                                </div>
                            </div>
                            <div class="col-md-2 col-6 mb-3">
                                <div class="stat-card p-3 text-center">
                                    <div class="fs-2 fw-bold text-primary"><?php echo $overall_stats['total_distributions']; ?></div>
                                    <div class="text-muted">মোট ডিস্ট্রিবিউশন</div>
                                </div>
                            </div>
                            <div class="col-md-2 col-6 mb-3">
                                <div class="stat-card p-3 text-center">
                                    <div class="fs-2 fw-bold text-primary"><?php echo round($overall_stats['overall_avg_marks'], 2); ?></div>
                                    <div class="text-muted">গড় নম্বর</div>
                                </div>
                            </div>
                            <div class="col-md-2 col-6 mb-3">
                                <div class="stat-card p-3 text-center">
                                    <div class="fs-2 fw-bold text-primary"><?php echo count($analysis_data); ?></div>
                                    <div class="text-muted">বিশ্লেষণ রেকর্ড</div>
                                </div>
                            </div>
                        </div>
                        
                        <!-- Filters -->
                        <div class="filter-section mb-4">
                            <h5 class="mb-3"><i class="fas fa-filter me-2"></i>ফিল্টার অপশন</h5>
                            <form method="GET" action="" class="row g-3">
                                <div class="col-md-4">
                                    <label for="search_session" class="form-label">সেশন</label>
                                    <select class="form-select" id="search_session" name="search_session">
                                        <option value="">সব সেশন</option>
                                        <?php foreach ($sessions as $session): ?>
                                            <option value="<?php echo $session['session_name']; ?>" <?php echo ($search_session == $session['session_name']) ? 'selected' : ''; ?>>
                                                <?php echo $session['session_name']; ?>
                                            </option>
                                        <?php endforeach; ?>
                                    </select>
                                </div>
                                
                                <div class="col-md-4">
                                    <label for="search_class" class="form-label">ক্লাস</label>
                                    <select class="form-select" id="search_class" name="search_class">
                                        <option value="">সব ক্লাস</option>
                                        <?php foreach ($classes as $class): ?>
                                            <option value="<?php echo $class['id']; ?>" <?php echo ($search_class == $class['id']) ? 'selected' : ''; ?>>
                                                <?php echo $class['name'] . ' - ' . $class['section']; ?>
                                            </option>
                                        <?php endforeach; ?>
                                    </select>
                                </div>
                                
                                <div class="col-md-4">
                                    <label for="search_group" class="form-label">গ্রুপ</label>
                                    <select class="form-select" id="search_group" name="search_group">
                                        <option value="">সব গ্রুপ</option>
                                        <?php foreach ($groups as $group): ?>
                                            <option value="<?php echo $group['group_name']; ?>" <?php echo ($search_group == $group['group_name']) ? 'selected' : ''; ?>>
                                                <?php echo $group['group_name'] . ' (' . $group['group_code'] . ')'; ?>
                                            </option>
                                        <?php endforeach; ?>
                                    </select>
                                </div>
                                
                                <div class="col-12">
                                    <div class="d-grid gap-2 d-md-flex justify-content-md-end">
                                        <a href="analysis.php" class="btn btn-outline-secondary me-md-2">
                                            <i class="fas fa-times"></i> রিসেট
                                        </a>
                                        <button type="submit" class="btn btn-primary-modern btn-modern">
                                            <i class="fas fa-search"></i> বিশ্লেষণ করুন
                                        </button>
                                    </div>
                                </div>
                            </form>
                        </div>
                        
                        <!-- Analysis Data Table -->
                        <div class="highlight-box">
                            <div class="d-flex justify-content-between align-items-center mb-3">
                                                            <h5><i class="fas fa-table me-2"></i>বিশ্লেষণ ডেটা</h5>
                                                            <a href="setup.php" class="btn btn-success btn-sm">
                                                                <i class="fas fa-plus-circle me-1"></i>ক্লাস ভিত্তিক সেটআপ
                                                            </a>
                                                        </div>
                            <?php if (empty($analysis_data)): ?>
                                <div class="alert alert-info text-center py-4">
                                    <i class="fas fa-info-circle fs-1 mb-3"></i>
                                    <h5>কোন বিশ্লেষণ ডেটা পাওয়া যায়নি</h5>
                                    <p class="mb-0">ফিল্টার পরিবর্তন করে আবার চেষ্টা করুন</p>
                                </div>
                            <?php else: ?>
                                <div class="table-responsive">
                                    <table class="table table-modern table-hover">
                                        <thead>
                                            <tr>
                                                <th>ক্লাস</th>
                                                <th>গ্রুপ</th>
                                                <th>সেশন</th>
                                                <th>ডিস্ট্রিবিউশন সংখ্যা</th>
                                                <th>মোট নম্বর যোগফল</th>
                                                <th>গড় নম্বর</th>
                                                <th>অ্যাকশন</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php foreach ($analysis_data as $data): ?>
                                                <tr>
                                                    <td>
                                                        <span class="class-badge">
                                                            <?php echo $data['class_name'] . ' - ' . $data['section']; ?>
                                                        </span>
                                                    </td>
                                                    <td>
                                                        <span class="group-badge">
                                                            <?php echo $data['group_display_name'] ?: 'সাধারণ'; ?>
                                                        </span>
                                                    </td>
                                                    <td>
                                                        <span class="session-badge">
                                                            <?php echo $data['session']; ?>
                                                        </span>
                                                    </td>
                                                    <td><strong><?php echo $data['distribution_count']; ?></strong></td>
                                                    <td><strong><?php echo $data['total_marks_sum']; ?></strong></td>
                                                    <td><strong><?php echo round($data['avg_total_marks'], 2); ?></strong></td>
                                                    <td>
                                                        <a href="setup.php?search_class=<?php echo $data['class_id']; ?>&search_session=<?php echo $data['session']; ?>&search_group=<?php echo $data['group_name']; ?>" class="btn btn-primary btn-sm">
                                                            <i class="fas fa-edit"></i> সম্পাদনা
                                                        </a>
                                                    </td>
                                                </tr>
                                            <?php endforeach; ?>
                                        </tbody>
                                    </table>
                                </div>
                            <?php endif; ?>
                        </div>
                        
                        <!-- Charts Section -->
                        <?php if (!empty($analysis_data)): ?>
                        <div class="row mt-4">
                            <div class="col-md-6">
                                <div class="card modern-card h-100">
                                    <div class="card-header bg-primary text-white">
                                        <h6 class="mb-0"><i class="fas fa-chart-bar me-2"></i>ক্লাস অনুযায়ী ডিস্ট্রিবিউশন</h6>
                                    </div>
                                    <div class="card-body">
                                        <canvas id="classDistributionChart"></canvas>
                                    </div>
                                </div>
                            </div>
                            <div class="col-md-6">
                                <div class="card modern-card h-100">
                                    <div class="card-header bg-primary text-white">
                                        <h6 class="mb-0"><i class="fas fa-chart-pie me-2"></i>গ্রুপ অনুযায়ী নম্বর বিতরণ</h6>
                                    </div>
                                    <div class="card-body">
                                        <canvas id="groupMarksChart"></canvas>
                                    </div>
                                </div>
                            </div>
                        </div>
                        
                        <div class="row mt-4">
                            <div class="col-12">
                                <div class="card modern-card">
                                    <div class="card-header bg-primary text-white">
                                        <h6 class="mb-0"><i class="fas fa-chart-line me-2"></i>সেশন অনুযায়ী গড় নম্বর ট্রেন্ড</h6>
                                    </div>
                                    <div class="card-body">
                                        <canvas id="sessionTrendChart" style="height: 300px;"></canvas>
                                    </div>
                                </div>
                            </div>
                        </div>
                        <?php endif; ?>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/js/bootstrap.bundle.min.js"></script>
    <script>
        // Prepare chart data
        <?php if (!empty($analysis_data)): ?>
        // Chart 1: Class Distribution
        const classData = <?php echo json_encode($grouped_by_class); ?>;
        const classLabels = Object.keys(classData);
        const classCounts = classLabels.map(cls => {
            return classData[cls].reduce((sum, item) => sum + item.distribution_count, 0);
        });
        
        // Chart 2: Group Marks Average
        const groupData = <?php echo json_encode($grouped_by_group); ?>;
        const groupLabels = Object.keys(groupData).filter(g => g !== 'General');
        const groupAverages = groupLabels.map(grp => {
            const items = groupData[grp];
            const total = items.reduce((sum, item) => sum + parseFloat(item.avg_total_marks || 0), 0);
            return items.length > 0 ? (total / items.length).toFixed(2) : 0;
        });
        
        // Chart 3: Session Trend
        const sessionData = <?php echo json_encode($grouped_by_session); ?>;
        const sessionLabels = Object.keys(sessionData).sort().reverse(); // Most recent first
        const sessionAverages = sessionLabels.map(sess => {
            const items = sessionData[sess];
            const total = items.reduce((sum, item) => sum + parseFloat(item.avg_total_marks || 0), 0);
            return items.length > 0 ? (total / items.length).toFixed(2) : 0;
        });

        // Render Charts
        document.addEventListener('DOMContentLoaded', function() {
            // Class Distribution Chart (Bar)
            const classCtx = document.getElementById('classDistributionChart').getContext('2d');
            new Chart(classCtx, {
                type: 'bar',
                data: {
                    labels: classLabels,
                    datasets: [{
                        label: 'ডিস্ট্রিবিউশন সংখ্যা',
                        data: classCounts,
                        backgroundColor: 'rgba(67, 97, 238, 0.7)',
                        borderColor: 'rgba(67, 97, 238, 1)',
                        borderWidth: 1
                    }]
                },
                options: {
                    responsive: true,
                    plugins: {
                        legend: {
                            display: false
                        }
                    },
                    scales: {
                        y: {
                            beginAtZero: true,
                            title: {
                                display: true,
                                text: 'ডিস্ট্রিবিউশন সংখ্যা'
                            }
                        }
                    }
                }
            });

            // Group Marks Chart (Doughnut)
            const groupCtx = document.getElementById('groupMarksChart').getContext('2d');
            new Chart(groupCtx, {
                type: 'doughnut',
                data: {
                    labels: groupLabels,
                    datasets: [{
                        data: groupAverages,
                        backgroundColor: [
                            'rgba(76, 201, 240, 0.7)',
                            'rgba(247, 37, 133, 0.7)',
                            'rgba(72, 149, 239, 0.7)',
                            'rgba(255, 99, 132, 0.7)',
                            'rgba(54, 162, 235, 0.7)',
                            'rgba(255, 205, 86, 0.7)',
                            'rgba(75, 192, 192, 0.7)'
                        ],
                        borderColor: [
                            'rgba(76, 201, 240, 1)',
                            'rgba(247, 37, 133, 1)',
                            'rgba(72, 149, 239, 1)',
                            'rgba(255, 99, 132, 1)',
                            'rgba(54, 162, 235, 1)',
                            'rgba(255, 205, 86, 1)',
                            'rgba(75, 192, 192, 1)'
                        ],
                        borderWidth: 1
                    }]
                },
                options: {
                    responsive: true,
                    plugins: {
                        legend: {
                            position: 'bottom'
                        }
                    }
                }
            });

            // Session Trend Chart (Line)
            const sessionCtx = document.getElementById('sessionTrendChart').getContext('2d');
            new Chart(sessionCtx, {
                type: 'line',
                data: {
                    labels: sessionLabels,
                    datasets: [{
                        label: 'গড় নম্বর',
                        data: sessionAverages,
                        fill: false,
                        borderColor: 'rgb(75, 192, 192)',
                        tension: 0.1,
                        pointBackgroundColor: 'rgb(75, 192, 192)',
                        pointBorderColor: '#fff',
                        pointBorderWidth: 2,
                        pointRadius: 6
                    }]
                },
                options: {
                    responsive: true,
                    plugins: {
                        legend: {
                            display: false
                        }
                    },
                    scales: {
                        y: {
                            title: {
                                display: true,
                                text: 'গড় নম্বর'
                            }
                        },
                        x: {
                            title: {
                                display: true,
                                text: 'সেশন'
                            }
                        }
                    }
                }
            });
        });
        <?php endif; ?>

        // Form validation
        (function() {
            'use strict';
            window.addEventListener('load', function() {
                var forms = document.getElementsByClassName('needs-validation');
                var validation = Array.prototype.filter.call(forms, function(form) {
                    form.addEventListener('submit', function(event) {
                        if (form.checkValidity() === false) {
                            event.preventDefault();
                            event.stopPropagation();
                        }
                        form.classList.add('was-validated');
                    }, false);
                });
            }, false);
        })();
    </script>
</body>
</html>