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

if (isset($_POST['class_id']) && isset($_POST['section_id'])) {
    $classId = (int)$_POST['class_id'];
    $sectionId = (int)$_POST['section_id'];
    $academicYear = isset($_POST['academic_year']) ? $conn->real_escape_string($_POST['academic_year']) : '';
    
    // Get class and section details
    $classQuery = "SELECT c.class, c.class_numeric, sec.section_name 
                   FROM classes c 
                   LEFT JOIN sections sec ON sec.section_id = $sectionId 
                   WHERE c.class_id = $classId";
    $classResult = $conn->query($classQuery);
    $classData = $classResult->fetch_assoc();
    
    // Get students with missing marks for this class-section
    $query = "SELECT 
                st.student_id,
                st.regd_id,
                st.name,
                st.gender,
                st.email,
                st.phone,
                st.parent_name,
                st.parent_phone,
                GROUP_CONCAT(DISTINCT s.subject_code ORDER BY s.subject_code SEPARATOR ', ') as missing_subjects,
                GROUP_CONCAT(DISTINCT s.subject_name ORDER BY s.subject_name SEPARATOR ', ') as missing_subject_names,
                COUNT(DISTINCT s.id) as total_missing_subjects
            FROM students st
            INNER JOIN studentsub ss ON st.student_id = ss.student_id
            INNER JOIN subjects s ON (
                ss.sub1 = s.subject_code OR 
                ss.sub2 = s.subject_code OR 
                ss.sub3 = s.subject_code OR 
                ss.sub4 = s.subject_code OR 
                ss.sub5 = s.subject_code OR 
                ss.sub6 = s.subject_code OR 
                ss.sub7 = s.subject_code
            )
            LEFT JOIN marks m ON st.student_id = m.student_id 
                AND m.class_id = ss.class_id 
                AND m.section_id = ss.section_id
                AND m.subject_id = s.id
            WHERE ss.class_id = $classId 
            AND ss.section_id = $sectionId
            " . ($academicYear ? "AND ss.academic_year = '$academicYear'" : "") . "
            AND st.status = 'Active'
            AND (m.marks = 0 OR m.marks IS NULL)
            GROUP BY st.student_id
            HAVING total_missing_subjects > 0
            ORDER BY st.name";
    
    $result = $conn->query($query);
    
    echo '<div class="card mt-4">';
    echo '<div class="card-header bg-danger text-white">';
    echo '<h5 class="mb-0"><i class="fa fa-exclamation-triangle"></i> Detailed Missing Marks Report</h5>';
    echo '</div>';
    echo '<div class="card-body">';
    
    echo '<div class="row mb-3">';
    echo '<div class="col-md-6">';
    echo '<p><strong>Class:</strong> Grade ' . $classData['class_numeric'] . ' (' . $classData['class'] . ')</p>';
    echo '<p><strong>Section:</strong> ' . ($classData['section_name'] ?: 'N/A') . '</p>';
    if ($academicYear) {
        echo '<p><strong>Academic Year:</strong> ' . $academicYear . '</p>';
    }
    echo '</div>';
    echo '<div class="col-md-6 text-right">';
    echo '<p><strong>Total Students with Missing Marks:</strong> <span class="badge badge-danger">' . $result->num_rows . '</span></p>';
    echo '</div>';
    echo '</div>';
    
    // Export buttons
    echo '<div class="mb-3">';
    echo '<button type="button" class="btn btn-sm btn-success mr-2" onclick="printClassMissingStudents(' . $classId . ', ' . $sectionId . ', \'' . $academicYear . '\')">';
    echo '<i class="fa fa-print"></i> Print Report';
    echo '</button>';
    echo '<button type="button" class="btn btn-sm btn-primary" onclick="exportClassMissingStudents(' . $classId . ', ' . $sectionId . ', \'' . $academicYear . '\')">';
    echo '<i class="fa fa-download"></i> Export to Excel';
    echo '</button>';
    echo '</div>';
    
    if ($result->num_rows > 0) {
        echo '<div class="table-responsive">';
        echo '<table class="table table-bordered table-hover" id="classMissingStudentsTable">';
        echo '<thead class="thead-light">';
        echo '<tr>
                <th>#</th>
                <th>Reg. ID</th>
                <th>Student Name</th>
                <th>Gender</th>
                <th>Contact</th>
                <th>Parent Contact</th>
                <th>Missing Subjects</th>
                <th>Total Missing</th>
                <th>Action</th>
              </tr>';
        echo '</thead>';
        echo '<tbody>';
        
        $counter = 1;
        while ($row = $result->fetch_assoc()) {
            // Split missing subjects into arrays
            $subjectCodes = explode(', ', $row['missing_subjects']);
            $subjectNames = explode(', ', $row['missing_subject_names']);
            
            echo '<tr>';
            echo '<td>' . $counter . '</td>';
            echo '<td><strong>' . htmlspecialchars($row['regd_id']) . '</strong></td>';
            echo '<td>' . htmlspecialchars($row['name']) . '</td>';
            echo '<td>' . htmlspecialchars($row['gender']) . '</td>';
            echo '<td>' . htmlspecialchars($row['phone']) . '<br>' . htmlspecialchars($row['email']) . '</td>';
            echo '<td>' . htmlspecialchars($row['parent_name']) . '<br>' . htmlspecialchars($row['parent_phone']) . '</td>';
            
            // Display missing subjects with subject codes
            echo '<td>';
            for ($i = 0; $i < count($subjectCodes); $i++) {
                if ($i > 0) echo '<br>';
                echo '<span class="badge badge-warning">' . htmlspecialchars($subjectNames[$i]) . ' (' . htmlspecialchars($subjectCodes[$i]) . ')</span>';
            }
            echo '</td>';
            
            echo '<td class="text-center"><span class="badge badge-danger">' . $row['total_missing_subjects'] . '</span></td>';
            
            // Action buttons
            echo '<td>';
            echo '<button type="button" class="btn btn-sm btn-outline-info mr-1 view-student-subjects" 
                    data-student-id="' . $row['student_id'] . '"
                    data-student-name="' . htmlspecialchars($row['name']) . '"
                    data-regd-id="' . htmlspecialchars($row['regd_id']) . '"
                    data-class-id="' . $classId . '"
                    data-section-id="' . $sectionId . '"
                    data-academic-year="' . $academicYear . '">
                <i class="fa fa-list"></i> Subjects
            </button>';
            
            echo '<a href="enter_marks.php?student_id=' . $row['student_id'] . '&class_id=' . $classId . '&section_id=' . $sectionId . '&academic_year=' . $academicYear . '" class="btn btn-sm btn-success">
                <i class="fa fa-edit"></i> Enter Marks
            </a>';
            echo '</td>';
            echo '</tr>';
            $counter++;
        }
        
        echo '</tbody>';
        echo '</table>';
        echo '</div>';
        
        // Summary
        echo '<div class="alert alert-warning mt-3">';
        echo '<h6><i class="fa fa-info-circle"></i> Summary</h6>';
        echo '<p><strong>Total Students:</strong> ' . $result->num_rows . '</p>';
        
        // Get count of students by number of missing subjects
        $subjectCountQuery = "SELECT 
                                COUNT(DISTINCT st.student_id) as student_count,
                                COUNT(DISTINCT s.id) as subject_count
                            FROM students st
                            INNER JOIN studentsub ss ON st.student_id = ss.student_id
                            INNER JOIN subjects s ON (
                                ss.sub1 = s.subject_code OR 
                                ss.sub2 = s.subject_code OR 
                                ss.sub3 = s.subject_code OR 
                                ss.sub4 = s.subject_code OR 
                                ss.sub5 = s.subject_code OR 
                                ss.sub6 = s.subject_code OR 
                                ss.sub7 = s.subject_code
                            )
                            LEFT JOIN marks m ON st.student_id = m.student_id 
                                AND m.class_id = ss.class_id 
                                AND m.section_id = ss.section_id
                                AND m.subject_id = s.id
                            WHERE ss.class_id = $classId 
                            AND ss.section_id = $sectionId
                            " . ($academicYear ? "AND ss.academic_year = '$academicYear'" : "") . "
                            AND st.status = 'Active'
                            AND (m.marks = 0 OR m.marks IS NULL)
                            GROUP BY st.student_id";
        
        $subjectCountResult = $conn->query($subjectCountQuery);
        $subjectsCount = [];
        $totalMissingSubjects = 0;
        
        while ($scRow = $subjectCountResult->fetch_assoc()) {
            $count = $scRow['subject_count'];
            if (!isset($subjectsCount[$count])) {
                $subjectsCount[$count] = 0;
            }
            $subjectsCount[$count]++;
            $totalMissingSubjects += $count;
        }
        
        echo '<p><strong>Missing Subjects Distribution:</strong>';
        foreach ($subjectsCount as $count => $studentCount) {
            echo ' ' . $studentCount . ' student(s) with ' . $count . ' subject(s),';
        }
        echo '</p>';
        
        echo '<p><strong>Average missing subjects per student:</strong> ' . ($result->num_rows > 0 ? number_format($totalMissingSubjects / $result->num_rows, 2) : 0) . '</p>';
        echo '</div>';
        
    } else {
        echo '<div class="alert alert-success">';
        echo '<i class="fa fa-check-circle"></i> No students found with missing marks for this class-section.';
        echo '</div>';
    }
    
    echo '</div>'; // card-body
    echo '</div>'; // card
    
    // JavaScript functions for export and print
    echo '<script>
    function printClassMissingStudents(classId, sectionId, academicYear) {
        var url = "print_missing_students.php?class_id=" + classId + 
                  "&section_id=" + sectionId;
        if (academicYear) {
            url += "&academic_year=" + academicYear;
        }
        window.open(url, "_blank");
    }
    
    function exportClassMissingStudents(classId, sectionId, academicYear) {
        var url = "export_missing_students.php?class_id=" + classId + 
                  "&section_id=" + sectionId;
        if (academicYear) {
            url += "&academic_year=" + academicYear;
        }
        window.location.href = url;
    }
    
    // View student subjects button handler
    $(document).on("click", ".view-student-subjects", function() {
        var studentId = $(this).data("student-id");
        var studentName = $(this).data("student-name");
        var regdId = $(this).data("regd-id");
        var classId = $(this).data("class-id");
        var sectionId = $(this).data("section-id");
        var academicYear = $(this).data("academic-year");
        
        // Show loading in modal
        $("#studentSubjectsModalBody").html(`
            <div class="text-center">
                <div class="spinner-border text-primary" role="status">
                    <span class="sr-only">Loading...</span>
                </div>
                <p>Loading student subjects...</p>
            </div>
        `);
        
        // Load student subjects via AJAX
        $.ajax({
            url: "ajax/get_student_missing_subjects.php",
            type: "POST",
            data: {
                student_id: studentId,
                class_id: classId,
                section_id: sectionId,
                academic_year: academicYear
            },
            success: function(response) {
                $("#studentSubjectsModalBody").html(response);
            },
            error: function() {
                $("#studentSubjectsModalBody").html(`
                    <div class="alert alert-danger">
                        <i class="fa fa-exclamation-triangle"></i> 
                        Failed to load student subjects. Please try again.
                    </div>
                `);
            }
        });
        
        // Update modal title
        $("#studentSubjectsModalLabel").html(`
            <i class="fa fa-list"></i> Subjects for ${studentName} (${regdId})
        `);
        
        // Show modal
        $("#studentSubjectsModal").modal("show");
    });
    </script>';
} else {
    echo '<div class="alert alert-danger">Invalid parameters provided.</div>';
}
?>