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

// Check if parameters are provided
if (isset($_GET['class_id']) && isset($_GET['section_id']) && isset($_GET['subject_code']) && isset($_GET['academic_year'])) {
    $classId = (int)$_GET['class_id'];
    $sectionId = (int)$_GET['section_id'];
    $subjectCode = $conn->real_escape_string($_GET['subject_code']);
    $academicYear = $conn->real_escape_string($_GET['academic_year']);
    
    // Get school details
    $schoolQuery = "SELECT * FROM schools LIMIT 1";
    $schoolResult = $conn->query($schoolQuery);
    $schoolDetails = $schoolResult->fetch_assoc();
    
    // Get class and section details
    $classSectionQuery = "SELECT 
        c.class,
        c.class_numeric,
        s.section_name
    FROM classes c
    LEFT JOIN sections s ON s.section_id = $sectionId
    WHERE c.class_id = $classId";
    
    $classSectionResult = $conn->query($classSectionQuery);
    $classSection = $classSectionResult->fetch_assoc();
    
    // Get subject details
    $subjectQuery = "SELECT subject_name FROM subjects WHERE subject_code = '$subjectCode' LIMIT 1";
    $subjectResult = $conn->query($subjectQuery);
    $subject = $subjectResult->fetch_assoc();
    
    // Get student list
    $query = "SELECT 
                s.student_id,
                s.regd_id,
                s.name,
                s.gender,
                s.email,
                s.phone,
                COALESCE(m.marks, 0) as marks_obtained,
                m.created_at as marks_entered_date
            FROM studentsub ss
            INNER JOIN students s ON ss.student_id = s.student_id
            LEFT JOIN marks m ON s.student_id = m.student_id 
                AND m.class_id = ss.class_id 
                AND m.section_id = ss.section_id
                AND m.subject_id = (SELECT id FROM subjects WHERE subject_code = '$subjectCode' LIMIT 1)
            WHERE ss.class_id = $classId 
            AND ss.section_id = $sectionId
            AND ss.academic_year = '$academicYear'
            AND (
                ss.sub1 = '$subjectCode' OR 
                ss.sub2 = '$subjectCode' OR 
                ss.sub3 = '$subjectCode' OR 
                ss.sub4 = '$subjectCode' OR 
                ss.sub5 = '$subjectCode' OR 
                ss.sub6 = '$subjectCode' OR 
                ss.sub7 = '$subjectCode'
            )
            AND s.status = 'Active'
            ORDER BY s.regd_id";
    
    $result = $conn->query($query);
    
    // Set headers for CSV file download
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="student_marks_' . $classSection['class'] . '_' . $sectionId . '_' . $subjectCode . '_' . date('Y-m-d') . '.csv"');
    
    // Open output stream
    $output = fopen('php://output', 'w');
    
    // Add UTF-8 BOM for Excel compatibility
    fputs($output, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));
    
    // School information as comments
    fputcsv($output, ['School: ' . $schoolDetails['school_name']]);
    fputcsv($output, ['Address: ' . $schoolDetails['address']]);
    fputcsv($output, ['Contact: ' . $schoolDetails['phone'] . ' | Email: ' . $schoolDetails['email']]);
    fputcsv($output, ['Report: Student Marks Report | Date: ' . date('Y-m-d')]);
    fputcsv($output, ['Class: Grade ' . $classSection['class_numeric'] . ' (' . $classSection['class'] . ') | Section: ' . ($classSection['section_name'] ?: 'N/A')]);
    fputcsv($output, ['Subject: ' . $subject['subject_name'] . ' (' . $subjectCode . ') | Academic Year: ' . $academicYear]);
    fputcsv($output, []); // Empty row
    
    // Column headers
    fputcsv($output, [
        'S.N.',
        'Registration ID',
        'Student Name',
        'Gender',
        'Phone',
        'Email',
        'Marks Obtained',
        'Status',
        'Marks Entry Date'
    ]);
    
    // Student data
    $counter = 1;
    $studentsWithMarks = 0;
    $studentsWithoutMarks = 0;
    
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $hasMarks = !empty($row['marks_obtained']) && $row['marks_obtained'] > 0;
            
            if ($hasMarks) {
                $studentsWithMarks++;
                $status = 'Marks Entered';
                $marksDisplay = $row['marks_obtained'];
            } else {
                $studentsWithoutMarks++;
                $status = 'No Marks';
                $marksDisplay = 'Not Entered';
            }
            
            fputcsv($output, [
                $counter,
                $row['regd_id'],
                $row['name'],
                $row['gender'],
                $row['phone'],
                $row['email'],
                $marksDisplay,
                $status,
                $row['marks_entered_date'] ? date('Y-m-d', strtotime($row['marks_entered_date'])) : '-'
            ]);
            
            $counter++;
        }
    }
    
    // Empty row
    fputcsv($output, []);
    
    // Summary
    $totalStudents = $studentsWithMarks + $studentsWithoutMarks;
    $completionRate = $totalStudents > 0 ? round(($studentsWithMarks / $totalStudents) * 100, 2) : 0;
    
    fputcsv($output, ['SUMMARY:']);
    fputcsv($output, [
        'Total Students: ' . $totalStudents,
        'With Marks: ' . $studentsWithMarks,
        'Without Marks: ' . $studentsWithoutMarks,
        'Completion Rate: ' . $completionRate . '%'
    ]);
    
    // Footer with school codes
    fputcsv($output, []);
    $schoolCodes = [];
    if (!empty($schoolDetails['school_code'])) {
        $schoolCodes[] = 'School Code: ' . $schoolDetails['school_code'];
    }
    if (!empty($schoolDetails['neb_code'])) {
        $schoolCodes[] = 'NEB Code: ' . $schoolDetails['neb_code'];
    }
    if (!empty($schoolDetails['ctevt_code'])) {
        $schoolCodes[] = 'CTEVT Code: ' . $schoolDetails['ctevt_code'];
    }
    if (!empty($schoolDetails['pan'])) {
        $schoolCodes[] = 'PAN: ' . $schoolDetails['pan'];
    }
    
    if (!empty($schoolCodes)) {
        fputcsv($output, ['School Codes: ' . implode(' | ', $schoolCodes)]);
    }
    
    fclose($output);
    
} else {
    // If no parameters, redirect back
    header('Location: admin.php');
    exit();
}
?>