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

$academicYear = isset($_POST['academic_year']) ? $conn->real_escape_string($_POST['academic_year']) : '';

// Build where clause for academic year
$yearClause = $academicYear ? "WHERE ss.academic_year = '$academicYear'" : "";

// Get summary of all missing marks
$summaryQuery = "SELECT 
                COUNT(DISTINCT CONCAT(ss.class_id, '-', ss.section_id, '-', s.id)) as total_subjects_missing,
                COUNT(DISTINCT st.student_id) as total_students_missing,
                COUNT(DISTINCT CONCAT(ss.class_id, '-', ss.section_id)) as total_class_sections_affected
            FROM subjects s
            INNER JOIN class_subject_mapping csm ON s.id = csm.subject_id
            INNER JOIN studentsub ss ON csm.class_id = ss.class_id AND csm.section_id = ss.section_id
            INNER JOIN students st ON ss.student_id = st.student_id
            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
            $yearClause
            AND st.status = 'Active'
            AND (m.marks = 0 OR m.marks IS NULL)
            AND (
                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
            )";

$summaryResult = $conn->query($summaryQuery);
$summary = $summaryResult->fetch_assoc();

echo '<div class="alert alert-danger">';
echo '<h4><i class="fa fa-exclamation-triangle"></i> All Missing Marks Report</h4>';
if ($academicYear) {
    echo '<p><strong>Academic Year:</strong> ' . $academicYear . '</p>';
}
echo '<p><strong>Total Class-Sections Affected:</strong> ' . ($summary['total_class_sections_affected'] ?? 0) . '</p>';
echo '<p><strong>Total Subjects with Missing Marks:</strong> ' . ($summary['total_subjects_missing'] ?? 0) . '</p>';
echo '<p><strong>Total Students with Missing Marks:</strong> ' . ($summary['total_students_missing'] ?? 0) . '</p>';
echo '</div>';

// Get detailed breakdown by class-section
$detailsQuery = "SELECT 
                c.class_id,
                c.class,
                c.class_numeric,
                sec.section_id,
                sec.section_name,
                COUNT(DISTINCT s.id) as subjects_missing,
                COUNT(DISTINCT st.student_id) as students_missing
            FROM classes c
            INNER JOIN class_section cs ON c.class_id = cs.class_id
            INNER JOIN sections sec ON cs.section_id = sec.section_id
            INNER JOIN studentsub ss ON cs.class_id = ss.class_id AND cs.section_id = ss.section_id
            INNER JOIN students st ON ss.student_id = st.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
            $yearClause
            AND st.status = 'Active'
            AND (m.marks = 0 OR m.marks IS NULL)
            GROUP BY c.class_id, sec.section_id
            HAVING students_missing > 0
            ORDER BY c.class_numeric, sec.section_name";

$detailsResult = $conn->query($detailsQuery);

if ($detailsResult->num_rows > 0) {
    echo '<div class="table-responsive">';
    echo '<table class="table table-bordered table-hover" id="allMissingMarksTable">';
    echo '<thead class="thead-light">';
    echo '<tr>
            <th>#</th>
            <th>Class</th>
            <th>Section</th>
            <th>Subjects Missing</th>
            <th>Students Missing</th>
            <th>Action</th>
          </tr>';
    echo '</thead>';
    echo '<tbody>';
    
    $counter = 1;
    while ($row = $detailsResult->fetch_assoc()) {
        $classId = $row['class_id'];
        $sectionId = $row['section_id'];
        $className = 'Grade ' . $row['class_numeric'] . ' (' . $row['class'] . ')';
        $sectionName = $row['section_name'] ?: 'N/A';
        
        echo '<tr>';
        echo '<td>' . $counter . '</td>';
        echo '<td><strong>' . $className . '</strong></td>';
        echo '<td>' . $sectionName . '</td>';
        echo '<td><span class="badge badge-warning">' . $row['subjects_missing'] . ' subjects</span></td>';
        echo '<td><span class="badge badge-danger">' . $row['students_missing'] . ' students</span></td>';
        echo '<td>';
        echo '<button type="button" class="btn btn-sm btn-outline-primary view-class-missing-details" 
                data-class-id="' . $classId . '" 
                data-section-id="' . $sectionId . '"
                data-class-name="' . htmlspecialchars($className) . '"
                data-section-name="' . htmlspecialchars($sectionName) . '"
                data-academic-year="' . $academicYear . '">
            <i class="fa fa-eye"></i> View Details
        </button>';
        echo '</td>';
        echo '</tr>';
        $counter++;
    }
    
    echo '</tbody>';
    echo '</table>';
    echo '</div>';
    
    // Hidden container for class details (will be populated via AJAX)
    echo '<div id="classMissingDetailsContainer" style="display: none;">';
    echo '</div>';
    
    // Initialize DataTable and add event handlers
    echo '<script>
    $(document).ready(function() {
        // Initialize DataTable
        $("#allMissingMarksTable").DataTable({
            "paging": true,
            "pageLength": 10,
            "searching": true,
            "ordering": true,
            "info": true,
            "autoWidth": false,
            "responsive": true
        });
        
        // View class details button click handler
        $(document).on("click", ".view-class-missing-details", function() {
            var classId = $(this).data("class-id");
            var sectionId = $(this).data("section-id");
            var className = $(this).data("class-name");
            var sectionName = $(this).data("section-name");
            var academicYear = $(this).data("academic-year");
            
            // Show loading
            $("#classMissingDetailsContainer").html(`
                <div class="text-center">
                    <div class="spinner-border text-primary" role="status">
                        <span class="sr-only">Loading...</span>
                    </div>
                    <p>Loading detailed report...</p>
                </div>
            `).show();
            
            // Load class missing marks details via AJAX
            $.ajax({
                url: "ajax/get_class_missing_students.php",
                type: "POST",
                data: {
                    class_id: classId,
                    section_id: sectionId,
                    academic_year: academicYear
                },
                success: function(response) {
                    $("#classMissingDetailsContainer").html(response);
                    
                    // Initialize DataTable for student list
                    if ($("#classMissingStudentsTable").length) {
                        $("#classMissingStudentsTable").DataTable({
                            "paging": true,
                            "pageLength": 10,
                            "searching": true,
                            "ordering": true,
                            "info": true,
                            "autoWidth": false,
                            "responsive": true,
                            "order": [[1, "asc"]]
                        });
                    }
                    
                    // Scroll to the details
                    $("html, body").animate({
                        scrollTop: $("#classMissingDetailsContainer").offset().top - 20
                    }, 500);
                },
                error: function() {
                    $("#classMissingDetailsContainer").html(`
                        <div class="alert alert-danger">
                            <i class="fa fa-exclamation-triangle"></i> 
                            Failed to load detailed report. Please try again.
                        </div>
                    `);
                }
            });
        });
    });
    </script>';
} else {
    echo '<div class="alert alert-success">';
    echo '<i class="fa fa-check-circle"></i> Excellent! No missing marks found in the system.';
    echo '</div>';
}
?>