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

// Check if user is logged in
if (!isset($_SESSION['user_id'])) {
    header('Location: ../login.php');
    exit();
}

$user_role = $_SESSION['user_role'];
$user_name = $_SESSION['user_name'] ?? $_SESSION['username'] ?? 'ব্যবহারকারী';

// Get overall statistics
$stats = [];

// Student statistics
$stmt = $pdo->query("
    SELECT 
        COUNT(*) as total_students,
        SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_students,
        SUM(CASE WHEN gender = 'male' THEN 1 ELSE 0 END) as male_students,
        SUM(CASE WHEN gender = 'female' THEN 1 ELSE 0 END) as female_students
    FROM students
");
$stats['students'] = $stmt->fetch();

// Teacher statistics
$stmt = $pdo->query("
    SELECT 
        COUNT(*) as total_teachers,
        SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_teachers,
        AVG(experience_years) as avg_experience
    FROM teachers
");
$stats['teachers'] = $stmt->fetch();

// Class statistics
$stmt = $pdo->query("
    SELECT 
        COUNT(*) as total_classes,
        SUM(capacity) as total_capacity
    FROM classes WHERE status = 'active'
");
$stats['classes'] = $stmt->fetch();

// Fee statistics
$stmt = $pdo->query("
    SELECT 
        COUNT(*) as total_fees,
        SUM(amount) as total_amount,
        SUM(paid_amount) as total_paid,
        SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) as paid_fees,
        SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_fees,
        SUM(CASE WHEN status = 'overdue' THEN 1 ELSE 0 END) as overdue_fees
    FROM fees
");
$stats['fees'] = $stmt->fetch();

// Attendance statistics (last 30 days)
$stmt = $pdo->query("
    SELECT 
        COUNT(*) as total_attendance,
        SUM(CASE WHEN status = 'present' THEN 1 ELSE 0 END) as present_count,
        SUM(CASE WHEN status = 'absent' THEN 1 ELSE 0 END) as absent_count,
        ROUND((SUM(CASE WHEN status = 'present' THEN 1 ELSE 0 END) * 100.0 / COUNT(*)), 2) as attendance_rate
    FROM attendance 
    WHERE date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
");
$stats['attendance'] = $stmt->fetch();

// Library statistics
$stmt = $pdo->query("
    SELECT 
        COUNT(*) as total_books,
        SUM(total_copies) as total_copies,
        SUM(available_copies) as available_copies,
        (SELECT COUNT(*) FROM library_issues WHERE status = 'issued') as issued_books
    FROM library_books WHERE status = 'active'
");
$stats['library'] = $stmt->fetch();

// Exam statistics
$stmt = $pdo->query("
    SELECT 
        COUNT(*) as total_exams,
        SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed_exams,
        SUM(CASE WHEN status = 'scheduled' THEN 1 ELSE 0 END) as scheduled_exams
    FROM exams
");
$stats['exams'] = $stmt->fetch();

// Class-wise student distribution
$stmt = $pdo->query("
    SELECT c.name, c.section, COUNT(s.id) as student_count, c.capacity
    FROM classes c
    LEFT JOIN students s ON c.id = s.class_id AND s.status = 'active'
    WHERE c.status = 'active'
    GROUP BY c.id
    ORDER BY c.name, c.section
");
$class_distribution = $stmt->fetchAll();

// Monthly fee collection (last 6 months)
$stmt = $pdo->query("
    SELECT 
        DATE_FORMAT(payment_date, '%Y-%m') as month,
        SUM(amount) as amount
    FROM fee_payments 
    WHERE payment_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
    GROUP BY DATE_FORMAT(payment_date, '%Y-%m')
    ORDER BY month DESC
");
$monthly_fees = $stmt->fetchAll();

// Recent activities
$stmt = $pdo->query("
    SELECT 'student' as type, CONCAT(first_name, ' ', last_name) as name, created_at as date
    FROM students 
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
    UNION ALL
    SELECT 'teacher' as type, CONCAT(first_name, ' ', last_name) as name, created_at as date
    FROM teachers 
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
    UNION ALL
    SELECT 'exam' as type, name, created_at as date
    FROM exams 
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
    ORDER BY date DESC
    LIMIT 10
");
$recent_activities = $stmt->fetchAll();
?>

<!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.1.3/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/jessoreboard-theme.css" rel="stylesheet">
    <link href="../assets/css/sidebar.css" rel="stylesheet">
    <link href="../assets/css/custom-sidebar.css" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</head>
<body>
    <div class="sidebar-container">
        <?php echo renderSidebar('reports/index.php', $user_role, $user_name); ?>
        
        <div class="main-content">
            <div class="content-header">
                <h1 class="content-title">
                    <i class="fas fa-chart-bar me-2"></i>
                    রিপোর্ট ও পরিসংখ্যান
                </h1>
                <p class="content-breadcrumb">
                    <i class="fas fa-home me-1"></i>
                    হোম / রিপোর্ট
                </p>
            </div>
            
            <!-- Floating Sidebar Toggle Button -->
            <button class="sidebar-toggle-float" onclick="toggleSidebar()" title="সাইডবার টগল করুন">
                <i class="fas fa-bars"></i>
            </button>
            
            <div class="content-body">
                <div class="row mb-4">
                    <div class="col-md-12">
                        <div class="d-flex justify-content-between align-items-center">
                            <h2><i class="fas fa-chart-bar"></i> রিপোর্ট ও পরিসংখ্যান</h2>
                            <div>
                                <a href="students_report.php" class="btn btn-info me-2">
                                    <i class="fas fa-user-graduate"></i> শিক্ষার্থী রিপোর্ট
                                </a>
                                <a href="teachers_report.php" class="btn btn-success me-2">
                                    <i class="fas fa-chalkboard-teacher"></i> শিক্ষক রিপোর্ট
                                </a>
                                <a href="all_users_report.php" class="btn btn-primary me-2">
                                    <i class="fas fa-users"></i> সকল ইউজার
                                </a>
                                <a href="marksheet.php" class="btn btn-warning me-2">
                                    <i class="fas fa-certificate"></i> মার্কশীট
                                </a>
                                <a href="tabulation_sheet.php" class="btn btn-secondary me-2">
                                    <i class="fas fa-table"></i> টেবুলেশন শীট
                                </a>
                                <a href="subject_based_tabulation_sheet.php" class="btn btn-info me-2">
                                    <i class="fas fa-book"></i> বিষয় ভিত্তিক
                                </a>
                                <a href="subject_code_tabulation_sheet.php" class="btn btn-dark me-2">
                                    <i class="fas fa-list-ol"></i> বিষয় কোড ভিত্তিক
                                </a>
                                <a href="pass_fail_report.php" class="btn btn-info me-2">
                                    <i class="fas fa-chart-pie"></i> পাশ/ফেল তালিকা
                                </a>
                                <a href="student_subject_list_report.php" class="btn btn-primary me-2">
                                    <i class="fas fa-list"></i> শিক্ষার্থী বিষয় তালিকা
                                </a>
                                <a href="attendance_sheet.php" class="btn btn-success me-2">
                                    <i class="fas fa-calendar-check"></i> উপস্থিতি শীট
                                </a>
                                <a href="exam_attendance_sheet.php" class="btn btn-info me-2">
                                    <i class="fas fa-clipboard-list"></i> পরীক্ষার হাজিরা
                                </a>
                                <button onclick="window.print()" class="btn btn-dark me-2">
                                    <i class="fas fa-print"></i> প্রিন্ট
                                </button>
                            </div>
                        </div>
                    </div>
                </div>

                <!-- Overview Statistics -->
                <div class="row mb-4">
                    <div class="col-md-2">
                        <div class="card bg-primary text-white">
                            <div class="card-body text-center">
                                <i class="fas fa-users fa-2x mb-2"></i>
                                <h4><?php echo $stats['students']['active_students'] ?: 0; ?></h4>
                                <p class="mb-0">সক্রিয় ছাত্র-ছাত্রী</p>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-2">
                        <div class="card bg-success text-white">
                            <div class="card-body text-center">
                                <i class="fas fa-chalkboard-teacher fa-2x mb-2"></i>
                                <h4><?php echo $stats['teachers']['active_teachers'] ?: 0; ?></h4>
                                <p class="mb-0">সক্রিয় শিক্ষক</p>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-2">
                        <div class="card bg-info text-white">
                            <div class="card-body text-center">
                                <i class="fas fa-school fa-2x mb-2"></i>
                                <h4><?php echo $stats['classes']['total_classes'] ?: 0; ?></h4>
                                <p class="mb-0">মোট ক্লাস</p>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-2">
                        <div class="card bg-warning text-white">
                            <div class="card-body text-center">
                                <i class="fas fa-percentage fa-2x mb-2"></i>
                                <h4><?php echo round($stats['attendance']['attendance_rate'] ?: 0, 1); ?>%</h4>
                                <p class="mb-0">উপস্থিতির হার</p>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-2">
                        <div class="card bg-danger text-white">
                            <div class="card-body text-center">
                                <i class="fas fa-book fa-2x mb-2"></i>
                                <h4><?php echo $stats['library']['total_books'] ?: 0; ?></h4>
                                <p class="mb-0">লাইব্রেরি বই</p>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-2">
                        <div class="card bg-secondary text-white">
                            <div class="card-body text-center">
                                <i class="fas fa-clipboard-list fa-2x mb-2"></i>
                                <h4><?php echo $stats['exams']['total_exams'] ?: 0; ?></h4>
                                <p class="mb-0">মোট পরীক্ষা</p>
                            </div>
                        </div>
                    </div>
                </div>

                <div class="row">
                    <!-- Student Distribution Chart -->
                    <div class="col-md-6">
                        <div class="card">
                            <div class="card-header">
                                <h5><i class="fas fa-chart-pie"></i> ছাত্র-ছাত্রী বিতরণ</h5>
                            </div>
                            <div class="card-body">
                                <canvas id="studentDistributionChart" width="400" height="200"></canvas>
                            </div>
                        </div>
                    </div>

                    <!-- Fee Collection Chart -->
                    <div class="col-md-6">
                        <div class="card">
                            <div class="card-header">
                                <h5><i class="fas fa-chart-line"></i> মাসিক ফি সংগ্রহ</h5>
                            </div>
                            <div class="card-body">
                                <canvas id="feeCollectionChart" width="400" height="200"></canvas>
                            </div>
                        </div>
                    </div>
                </div>

                <div class="row mt-4">
                    <!-- Class-wise Student Distribution -->
                    <div class="col-md-8">
                        <div class="card">
                            <div class="card-header">
                                <h5><i class="fas fa-users"></i> ক্লাসভিত্তিক ছাত্র-ছাত্রী বিতরণ</h5>
                            </div>
                            <div class="card-body">
                                <div class="table-responsive">
                                    <table class="table table-striped">
                                        <thead>
                                            <tr>
                                                <th>ক্লাস</th>
                                                <th>শাখা</th>
                                                <th>ছাত্র-ছাত্রী</th>
                                                <th>ধারণক্ষমতা</th>
                                                <th>শতাংশ</th>
                                                <th>অবস্থা</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php foreach ($class_distribution as $class): ?>
                                                <?php 
                                                $percentage = $class['capacity'] > 0 ? round(($class['student_count'] / $class['capacity']) * 100, 1) : 0;
                                                $status_class = '';
                                                if ($percentage >= 90) $status_class = 'bg-danger';
                                                elseif ($percentage >= 75) $status_class = 'bg-warning';
                                                else $status_class = 'bg-success';
                                                ?>
                                                <tr>
                                                    <td><?php echo $class['name']; ?></td>
                                                    <td><?php echo $class['section']; ?></td>
                                                    <td><span class="badge bg-primary"><?php echo $class['student_count']; ?></span></td>
                                                    <td><?php echo $class['capacity']; ?></td>
                                                    <td><?php echo $percentage; ?>%</td>
                                                    <td>
                                                        <div class="progress" style="height: 20px;">
                                                            <div class="progress-bar <?php echo $status_class; ?>" 
                                                                 style="width: <?php echo $percentage; ?>%">
                                                                <?php echo $percentage; ?>%
                                                            </div>
                                                        </div>
                                                    </td>
                                                </tr>
                                            <?php endforeach; ?>
                                        </tbody>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Recent Activities -->
                    <div class="col-md-4">
                        <div class="card">
                            <div class="card-header">
                                <h5><i class="fas fa-history"></i> সাম্প্রতিক কার্যক্রম</h5>
                            </div>
                            <div class="card-body">
                                <?php if (empty($recent_activities)): ?>
                                    <p class="text-muted">কোন সাম্প্রতিক কার্যক্রম নেই</p>
                                <?php else: ?>
                                    <div class="timeline">
                                        <?php foreach ($recent_activities as $activity): ?>
                                            <div class="timeline-item mb-3">
                                                <div class="d-flex">
                                                    <div class="flex-shrink-0">
                                                        <?php
                                                        $icon = '';
                                                        $color = '';
                                                        switch ($activity['type']) {
                                                            case 'student':
                                                                $icon = 'fa-user-plus';
                                                                $color = 'text-primary';
                                                                break;
                                                            case 'teacher':
                                                                $icon = 'fa-chalkboard-teacher';
                                                                $color = 'text-success';
                                                                break;
                                                            case 'exam':
                                                                $icon = 'fa-clipboard-list';
                                                                $color = 'text-warning';
                                                                break;
                                                        }
                                                        ?>
                                                        <i class="fas <?php echo $icon; ?> <?php echo $color; ?>"></i>
                                                    </div>
                                                    <div class="flex-grow-1 ms-3">
                                                        <div class="fw-bold"><?php echo htmlspecialchars($activity['name']); ?></div>
                                                        <small class="text-muted">
                                                            <?php
                                                            switch ($activity['type']) {
                                                                case 'student':
                                                                    echo 'নতুন ছাত্র/ছাত্রী যোগ';
                                                                    break;
                                                                case 'teacher':
                                                                    echo 'নতুন শিক্ষক যোগ';
                                                                    break;
                                                                case 'exam':
                                                                    echo 'নতুন পরীক্ষা তৈরি';
                                                                    break;
                                                            }
                                                            ?>
                                                            - <?php echo date('d/m/Y H:i', strtotime($activity['date'])); ?>
                                                        </small>
                                                    </div>
                                                </div>
                                            </div>
                                        <?php endforeach; ?>
                                    </div>
                                <?php endif; ?>
                            </div>
                        </div>
                    </div>
                </div>

                <!-- Detailed Statistics -->
                <div class="row mt-4">
                    <div class="col-md-12">
                        <div class="card">
                            <div class="card-header">
                                <h5><i class="fas fa-table"></i> বিস্তারিত পরিসংখ্যান</h5>
                            </div>
                            <div class="card-body">
                                <div class="row">
                                    <div class="col-md-3">
                                        <h6 class="text-primary">ছাত্র-ছাত্রী তথ্য</h6>
                                        <ul class="list-unstyled">
                                            <li>মোট: <?php echo $stats['students']['total_students'] ?: 0; ?></li>
                                            <li>সক্রিয়: <?php echo $stats['students']['active_students'] ?: 0; ?></li>
                                            <li>ছেলে: <?php echo $stats['students']['male_students'] ?: 0; ?></li>
                                            <li>মেয়ে: <?php echo $stats['students']['female_students'] ?: 0; ?></li>
                                        </ul>
                                    </div>
                                    <div class="col-md-3">
                                        <h6 class="text-success">শিক্ষক তথ্য</h6>
                                        <ul class="list-unstyled">
                                            <li>মোট: <?php echo $stats['teachers']['total_teachers'] ?: 0; ?></li>
                                            <li>সক্রিয়: <?php echo $stats['teachers']['active_teachers'] ?: 0; ?></li>
                                            <li>গড় অভিজ্ঞতা: <?php echo round($stats['teachers']['avg_experience'] ?: 0, 1); ?> বছর</li>
                                        </ul>
                                    </div>
                                    <div class="col-md-3">
                                        <h6 class="text-info">ফি তথ্য</h6>
                                        <ul class="list-unstyled">
                                            <li>মোট ফি: ৳<?php echo number_format($stats['fees']['total_amount'] ?: 0); ?></li>
                                            <li>সংগৃহীত: ৳<?php echo number_format($stats['fees']['total_paid'] ?: 0); ?></li>
                                            <li>পরিশোধিত: <?php echo $stats['fees']['paid_fees'] ?: 0; ?></li>
                                            <li>বকেয়া: <?php echo $stats['fees']['overdue_fees'] ?: 0; ?></li>
                                        </ul>
                                    </div>
                                    <div class="col-md-3">
                                        <h6 class="text-warning">লাইব্রেরি তথ্য</h6>
                                        <ul class="list-unstyled">
                                            <li>মোট বই: <?php echo $stats['library']['total_books'] ?: 0; ?></li>
                                            <li>মোট কপি: <?php echo $stats['library']['total_copies'] ?: 0; ?></li>
                                            <li>উপলব্ধ: <?php echo $stats['library']['available_copies'] ?: 0; ?></li>
                                            <li>ইস্যুকৃত: <?php echo $stats['library']['issued_books'] ?: 0; ?></li>
                                        </ul>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
    <script src="../assets/js/script.js"></script>
    <script>
        // Student Distribution Chart
        const studentCtx = document.getElementById('studentDistributionChart').getContext('2d');
        new Chart(studentCtx, {
            type: 'doughnut',
            data: {
                labels: ['ছেলে', 'মেয়ে'],
                datasets: [{
                    data: [
                        <?php echo $stats['students']['male_students'] ?: 0; ?>,
                        <?php echo $stats['students']['female_students'] ?: 0; ?>
                    ],
                    backgroundColor: ['#007bff', '#e83e8c']
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: {
                        position: 'bottom'
                    }
                }
            }
        });

        // Fee Collection Chart
        const feeCtx = document.getElementById('feeCollectionChart').getContext('2d');
        new Chart(feeCtx, {
            type: 'line',
            data: {
                labels: [
                    <?php foreach (array_reverse($monthly_fees) as $fee): ?>
                        '<?php echo date('M Y', strtotime($fee['month'] . '-01')); ?>',
                    <?php endforeach; ?>
                ],
                datasets: [{
                    label: 'ফি সংগ্রহ (টাকা)',
                    data: [
                        <?php foreach (array_reverse($monthly_fees) as $fee): ?>
                            <?php echo $fee['amount'] ?: 0; ?>,
                        <?php endforeach; ?>
                    ],
                    borderColor: '#28a745',
                    backgroundColor: 'rgba(40, 167, 69, 0.1)',
                    tension: 0.4
                }]
            },
            options: {
                responsive: true,
                plugins: {
                    legend: {
                        display: false
                    }
                },
                scales: {
                    y: {
                        beginAtZero: true
                    }
                }
            }
        });

        function exportToExcel() {
            alert('এক্সেল এক্সপোর্ট ফিচার শীঘ্রই আসছে');
        }
        
        // Sidebar toggle function (to match the floating button functionality)
        function toggleSidebar() {
            const sidebar = document.getElementById('sidebar');
            const toggleBtn = document.querySelector('.sidebar-toggle i');
            
            sidebar.classList.toggle('collapsed');
            
            if (sidebar.classList.contains('collapsed')) {
                toggleBtn.className = 'fas fa-chevron-right';
                localStorage.setItem('sidebarCollapsed', 'true');
            } else {
                toggleBtn.className = 'fas fa-chevron-left';
                localStorage.setItem('sidebarCollapsed', 'false');
            }
        }
    </script>
</div> <!-- Close main-content -->
</div> <!-- Close sidebar-container -->
</body>
</html>