<?php
include("includes/db.php");

// Query to fetch class-wise student details from active classes (status = 'on')
$studentQuery = "
    SELECT students.gender, classes.class_name, classes.section
    FROM students
    INNER JOIN classes ON students.Class_id = classes.class_id
    WHERE classes.status = 'on'
    ORDER BY classes.class_name, classes.section
";

$studentResult = $conn->query($studentQuery);

if ($studentResult->num_rows > 0) {
    $currentClass = "";
    $currentSection = "";
    $serialNumber = 1;
    $totalFemale = 0;
    $totalMale = 0;
    $output = "";

    // Initialize table structure
    $output .= "
        <table class='table table-striped table-bordered'>
            <thead class='thead-dark'>
                <tr>
                    <th>S.N.</th>
                    <th>Class (Section)</th>
                    <th>Female</th>
                    <th>Male</th>
                    <th>Total</th>
                    <th>Remarks</th>
                </tr>
            </thead>
            <tbody>
    ";

    $classData = [];

    // Loop through student data
    while ($row = $studentResult->fetch_assoc()) {
        $className = htmlspecialchars($row['class_name']);
        $section = htmlspecialchars($row['section']);
        $gender = htmlspecialchars($row['gender']);

        $classKey = $className . ' - ' . $section;

        // Initialize data for each new class-section combination
        if (!isset($classData[$classKey])) {
            $classData[$classKey] = ['female' => 0, 'male' => 0];
        }

        // Count male and female students
        if (strtolower($gender) == 'female') {
            $classData[$classKey]['female']++;
        } else if (strtolower($gender) == 'male') {
            $classData[$classKey]['male']++;
        }
    }

    // Display the aggregated class data
    foreach ($classData as $classSection => $counts) {
        $total = $counts['female'] + $counts['male'];

        // Display each class with totals
        $output .= "
            <tr>
                <td>{$serialNumber}</td>
                <td>{$classSection}</td>
                <td>{$counts['female']}</td>
                <td>{$counts['male']}</td>
                <td>{$total}</td>
                <td></td> <!-- Remarks column -->
            </tr>
        ";

        $serialNumber++;
        $totalFemale += $counts['female'];
        $totalMale += $counts['male'];
    }

    // Close the table and display totals for all classes
    $totalStudents = $totalFemale + $totalMale;
    $output .= "
        <tr>
            <td colspan='2'><strong>Grand Total</strong></td>
            <td><strong>{$totalFemale}</strong></td>
            <td><strong>{$totalMale}</strong></td>
            <td><strong>{$totalStudents}</strong></td>
            <td></td>
        </tr>
    ";

    $output .= "</tbody></table>";

    // Output the table
    echo $output;
} else {
    echo "<p>No students found for active classes.</p>";
}
?>
