<?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']) : '';
    
    // Build where clause for academic year
    $yearClause = $academicYear ? "AND ss.academic_year = '$academicYear'" : "";
    
    // Get all subjects with missing marks for this class-section
    $query = "SELECT 
                s.subject_code,
                s.subject_name,
                COUNT(DISTINCT st.student_id) as missing_count,
                GROUP_CONCAT(DISTINCT st.name ORDER BY st.name SEPARATOR ', ') as student_names
            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
            WHERE ss.class_id = $classId 
            AND ss.section_id = $sectionId
            $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
            )
            GROUP BY s.id, s.subject_code, s.subject_name
            HAVING missing_count > 0
            ORDER BY missing_count DESC, s.subject_name";
    
    $result = $conn->query($query);
    
    // 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();
    
    echo '<div class="alert alert-danger">';
    echo '<h5><i class="fa fa-exclamation-triangle"></i> Class Missing Marks Summary</h5>';
    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>';
    
    if ($result->num_rows > 0) {
        $totalMissing = 0;
        
        echo '<div class="table-responsive">';
        echo '<table class="table table-bordered table-hover">';
        echo '<thead class="thead-light">';
        echo '<tr>
                <th>#</th>
                <th>Subject Code</th>
                <th>Subject Name</th>
                <th>Students Missing</th>
                <th>Action</th>
              </tr>';
        echo '</thead>';
        echo '<tbody>';
        
        $counter = 1;
        while ($row = $result->fetch_assoc()) {
            $totalMissing += $row['missing_count'];
            
            echo '<tr>';
            echo '<td>' . $counter . '</td>';
            echo '<td><strong>' . htmlspecialchars($row['subject_code']) . '</strong></td>';
            echo '<td>' . htmlspecialchars($row['subject_name']) . '</td>';
            echo '<td><span class="badge badge-danger">' . $row['missing_count'] . ' students</span></td>';
            echo '<td>';
            echo '<a href="enter_marks.php?class_id=' . $classId . '&section_id=' . $sectionId . '&subject_code=' . $row['subject_code'] . '&academic_year=' . ($academicYear ?: '') . '" class="btn btn-sm btn-success">';
            echo '<i class="fa fa-edit"></i> Enter Marks';
            echo '</a>';
            echo '</td>';
            echo '</tr>';
            $counter++;
        }
        
        echo '</tbody>';
        echo '</table>';
        echo '</div>';
        
        echo '<div class="alert alert-warning">';
        echo '<h6><i class="fa fa-info-circle"></i> Summary</h6>';
        echo '<p><strong>Total Subjects with Missing Marks:</strong> ' . $result->num_rows . '</p>';
        echo '<p><strong>Total Students Affected:</strong> ' . $totalMissing . '</p>';
        echo '</div>';
    } else {
        echo '<div class="alert alert-success">';
        echo '<i class="fa fa-check-circle"></i> Excellent! No missing marks found for this class-section.';
        echo '</div>';
    }
} else {
    echo '<div class="alert alert-danger">Invalid parameters.</div>';
}
?>