<?php
// Include database connection code
include("includes/db.php");

// Query to fetch class names and counts of male and female students
$query = "
    SELECT 
        classes.class_name, classes.section,
        COUNT(CASE WHEN students.gender = 'Male' THEN 1 END) AS male_count,
        COUNT(CASE WHEN students.gender = 'Female' THEN 1 END) AS female_count
    FROM students
    INNER JOIN classes ON students.class_id = classes.class_id
    GROUP BY classes.class_id
    ORDER BY classes.class_id
";

$result = $conn->query($query);

// Check if any data is available
if ($result->num_rows > 0) {
    $classData = $result->fetch_all(MYSQLI_ASSOC);
} else {
    $classData = [];
}
?>

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <meta http-equiv="X-UA-Compatible" content="IE=edge">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <title>SRMS Admin Manage Subjects</title>
        <link rel="stylesheet" href="css/bootstrap.min.css" media="screen">
        <link rel="stylesheet" href="css/font-awesome.min.css" media="screen">
        <link rel="stylesheet" href="css/animate-css/animate.min.css" media="screen">
        <link rel="stylesheet" href="css/lobipanel/lobipanel.min.css" media="screen">
        <link rel="stylesheet" href="css/prism/prism.css" media="screen">
        <link rel="stylesheet" href="css/select2/select2.min.css">
        <link rel="stylesheet" href="css/main.css" media="screen">
        <script src="js/modernizr/modernizr.min.js"></script>
        <script src="https://cdn.jsdelivr.net/npm/xlsx/dist/xlsx.full.min.js"></script>
        <style>
            .btn-container {
                margin-bottom: 20px;
                display: flex;
                gap: 10px;
                justify-content: flex-end;
            }
        </style>
    </head>
    <body class="top-navbar-fixed">
        <div class="main-wrapper">

            <!-- ========== TOP NAVBAR ========== -->
            <?php include('includes/topbar.php'); ?>
            <!-- ========== WRAPPER FOR BOTH SIDEBARS & MAIN CONTENT ========== -->
            <div class="content-wrapper">
                <div class="content-container">

                    <!-- ========== LEFT SIDEBAR ========== -->
                   <?php include('includes/leftbar.php');?>  
                    <!-- /.left-sidebar -->

                    <div class="main-page">

                     <div class="container-fluid">
                            <div class="row page-title-div">
                                <div class="col-md-6">
                                    <h2 class="title">Class-wise Male/Female Student Count</h2>
                                
                                </div>
                                
                                <!-- /.col-md-6 text-right -->
                            </div>
                            <!-- /.row -->
                            <div class="row breadcrumb-div">
                                <div class="col-md-6">
                                    <ul class="breadcrumb">
                                        <li><a href="dashboard.php"><i class="fa fa-home"></i> Home</a></li>
                                        <li> Report </li>
                                        <li class="">Gender list</li>
                                    </ul>
                                </div>
                            </div></div>

                        <div class="container-fluid">
                            <div class="row">
                                <div class="col-md-12">
                                    <div class="panel">
                                        <div class="panel-heading">
                                            <div class="panel-title">
                                                <h5>Class-wise Male/Female Student Count</h5>
                                            </div>
                                        </div>
                                        <div class="panel-body">

                                            <!-- Buttons for Print and Export -->
                                            <div class="btn-container">
                                                <button onclick="printTable()" class="btn btn-primary">Print Table</button>
                                                <button onclick="exportTableToExcel('subjectsTable', 'Classwise_Male_Female_Count')" class="btn btn-success">Export to Excel</button>
                                            </div>

                                            <!-- Table Display -->
                                            <table id="subjectsTable" class="display">
                                                <thead>
                                                    <tr>
                                                        <th>Class</th>
                                                        <th>Female Students</th>
                                                        <th>Male Students</th>
                                                        <th>Total Students</th>
                                                    </tr>
                                                </thead>
                                                <tbody>
                                                    <?php if (!empty($classData)): ?>
                                                        <?php foreach ($classData as $row): ?>
                                                            <tr>
                                                                <td style="text-align:left;"><?php echo htmlspecialchars($row['class_name']) . '-' . ($row['section']); ?></td>
                                                                <td><?php echo htmlspecialchars($row['male_count']); ?></td>
                                                                <td><?php echo htmlspecialchars($row['female_count']); ?></td>
                                                                <td><?php echo htmlspecialchars($row['male_count'] + $row['female_count']); ?></td>
                                                            </tr>
                                                        <?php endforeach; ?>
                                                    <?php else: ?>
                                                        <tr>
                                                            <td colspan="4">No data available</td>
                                                        </tr>
                                                    <?php endif; ?>
                                                </tbody>
                                            </table>
                                        </div>
                                    </div>
                                </div>
                                <!-- /.col-md-12 -->
                            </div>
                        </div>
                    </div>
                </div>
                <!-- /.content-container -->
            </div>
            <!-- /.content-wrapper -->
        </div>
        <!-- /.main-wrapper -->

        <script src="js/jquery/jquery-2.2.4.min.js"></script>
        <script src="js/bootstrap/bootstrap.min.js"></script>
        <script src="js/pace/pace.min.js"></script>
        <script src="js/lobipanel/lobipanel.min.js"></script>
        <script src="js/iscroll/iscroll.js"></script>
        <script src="js/prism/prism.js"></script>
        <script src="js/select2/select2.min.js"></script>
        <script src="js/main.js"></script>

        <script>
            // Print Table Function
            function printTable() {
                var printContent = document.getElementById('subjectsTable').outerHTML;
                var printWindow = window.open('', '_blank');
                printWindow.document.write('<html><head><title>Print Table</title></head><body>' + printContent + '</body></html>');
                printWindow.document.close();
                printWindow.print();
            }

            // Export Table to Excel Function
            function exportTableToExcel(tableID, filename = '') {
                var table = document.getElementById(tableID);
                var workbook = XLSX.utils.table_to_book(table, { sheet: "Sheet1" });
                XLSX.writeFile(workbook, filename + ".xlsx");
            }
        </script>
    </body>
</html>
