<?php
session_start();
require_once 'config.php';

// Set content type to JSON
header('Content-Type: application/json');

// Start output buffering
ob_start();

// Enable error logging for debugging
error_log("STUDENT DEBUG: Starting student query for class_id=" . $_POST['class_id'] . ", stream_id=" . $_POST['stream_id']);

// Check if database connection is available
if (!isset($conn) || $conn->connect_error) {
    ob_clean();
    error_log("STUDENT DEBUG: Database connection error");
    echo json_encode(['success' => false, 'message' => 'Database connection error']);
    exit;
}

// Check if required POST data exists
if (!isset($_POST['exam_id']) || !isset($_POST['class_id']) || !isset($_POST['stream_id']) || !isset($_POST['subject_id']) || !isset($_POST['school_id'])) {
    ob_clean();
    error_log("STUDENT DEBUG: Missing required data");
    echo json_encode(['success' => false, 'message' => 'Missing required data']);
    exit;
}

$exam_id = intval($_POST['exam_id']);
$class_id = intval($_POST['class_id']);
$stream_id = intval($_POST['stream_id']);
$subject_id = intval($_POST['subject_id']);
$school_id = intval($_POST['school_id']);

// Get session data safely
$user_id = isset($_SESSION["id"]) ? $_SESSION["id"] : 0;
$current_user_school_id = isset($_SESSION["school_id"]) ? $_SESSION["school_id"] : 0;
$user_role = isset($_SESSION["role"]) ? $_SESSION["role"] : 0;

// Verify school_id matches session (only if session data is available)
if ($current_user_school_id > 0 && $school_id !== $current_user_school_id) {
    ob_clean();
    error_log("STUDENT DEBUG: School ID mismatch");
    echo json_encode(['success' => false, 'message' => 'School ID mismatch']);
    exit;
}

$students = [];

error_log("STUDENT DEBUG: Query parameters - class_id: $class_id, stream_id: $stream_id, school_id: $school_id, exam_id: $exam_id, subject_id: $subject_id");

try {
    // SIMPLIFIED APPROACH: Students are directly in users table with class_id/stream_id columns
    // Based on database structure analysis, we know this works
    $sql_students = "SELECT u.id as user_id, u.username, 
                    CONCAT(u.username) as display_name
                    FROM users u 
                    WHERE u.role = 3 
                    AND u.status = 'active' 
                    AND u.school_id = ?
                    AND u.class_id = ? 
                    AND u.stream_id = ?
                    ORDER BY u.username";
    
    $bind_params = [$school_id, $class_id, $stream_id];
    $param_types = 'iii';
    
    error_log("STUDENT DEBUG: Using direct users table query (class_id/stream_id directly in users table)");
    error_log("STUDENT DEBUG: SQL: " . $sql_students);
    error_log("STUDENT DEBUG: Parameters: " . json_encode($bind_params));
    
    // Execute the query
    if ($stmt = $conn->prepare($sql_students)) {
        $stmt->bind_param($param_types, ...$bind_params);
        
        if ($stmt->execute()) {
            $result = $stmt->get_result();
            error_log("STUDENT DEBUG: Query executed successfully, found " . $result->num_rows . " students");
            
            while ($row = $result->fetch_assoc()) {
                $students[] = [
                    'user_id' => $row['user_id'],
                    'username' => $row['username'], // Use clean username without ID suffix
                    'display_name' => $row['display_name'],
                    'current_mark' => null, // Will be populated below if marks exist
                    'paper_marks' => [] // Initialize for paper-specific marks
                ];
            }
        } else {
            error_log("STUDENT DEBUG: Query execution failed: " . $stmt->error);
        }
        $stmt->close();
    } else {
        error_log("STUDENT DEBUG: Failed to prepare statement: " . $conn->error);
    }
    
} catch (Exception $e) {
    error_log("STUDENT DEBUG: Exception occurred: " . $e->getMessage());
    $students = [];
}

// Function to get subject papers
function getSubjectPapers($subject_id, $conn) {
    $papers = [];
    $sql_papers = "SELECT paper_id, paper_name FROM subject_papers 
                  WHERE subject_id = ? ORDER BY paper_id";
    
    if ($stmt_papers = @$conn->prepare($sql_papers)) {
        $stmt_papers->bind_param("i", $subject_id);
        if ($stmt_papers->execute()) {
            $result_papers = $stmt_papers->get_result();
            while ($row_paper = $result_papers->fetch_assoc()) {
                $papers[] = [
                    'paper_id' => $row_paper['paper_id'],
                    'paper_name' => $row_paper['paper_name']
                ];
            }
        }
        $stmt_papers->close();
    }
    return $papers;
}

// Get papers for this subject
$papers = getSubjectPapers($subject_id, $conn);
$has_papers = !empty($papers);

// Get existing marks for these students (if marks table exists)
if (!empty($students)) {
    error_log("STUDENT DEBUG: Retrieving existing marks for " . count($students) . " students");
    
    $student_ids = array_column($students, 'user_id');
    $placeholders = str_repeat('?,', count($student_ids) - 1) . '?';
    
    if ($has_papers) {
        // Get paper-specific marks
        $marks_sql = "SELECT student_id, paper_id, mark FROM marks 
                     WHERE exam_id = ? AND subject_id = ? AND student_id IN ($placeholders) 
                     AND paper_id IS NOT NULL ORDER BY paper_id";
    } else {
        // Get regular marks (no paper_id or paper_id = 0/NULL)
        $marks_sql = "SELECT student_id, paper_id, mark FROM marks 
                     WHERE exam_id = ? AND subject_id = ? AND student_id IN ($placeholders) 
                     AND (paper_id IS NULL OR paper_id = 0) ORDER BY paper_id";
    }
    
    try {
        if ($marks_stmt = $conn->prepare($marks_sql)) {
            $marks_params = array_merge([$exam_id, $subject_id], $student_ids);
            $marks_types = str_repeat('i', count($marks_params));
            $marks_stmt->bind_param($marks_types, ...$marks_params);
            
            if ($marks_stmt->execute()) {
                $marks_result = $marks_stmt->get_result();
                $marks_data = [];
                
                while ($mark_row = $marks_result->fetch_assoc()) {
                    $key = $mark_row['student_id'] . '_' . ($mark_row['paper_id'] ?? 'null');
                    $marks_data[$key] = $mark_row['mark'];
                }
                
                // Merge marks data with students
                foreach ($students as &$student) {
                    if ($has_papers) {
                        // Initialize paper marks array
                        $student['paper_marks'] = [];
                        foreach ($papers as $paper) {
                            $key = $student['user_id'] . '_' . $paper['paper_id'];
                            $student['paper_marks'][$paper['paper_id']] = isset($marks_data[$key]) ? $marks_data[$key] : null;
                        }
                        // Also check for any marks without paper_id (for backward compatibility)
                        $key_null = $student['user_id'] . '_null';
                        if (isset($marks_data[$key_null])) {
                            $student['current_mark'] = $marks_data[$key_null];
                        }
                    } else {
                        // Regular subject - single mark
                        $key = $student['user_id'] . '_null';
                        if (isset($marks_data[$key])) {
                            $student['current_mark'] = $marks_data[$key];
                        }
                    }
                }
                
                error_log("STUDENT DEBUG: Retrieved " . count($marks_data) . " existing marks");
            }
            $marks_stmt->close();
        }
    } catch (Exception $e) {
        error_log("STUDENT DEBUG: Error retrieving marks: " . $e->getMessage());
        // Continue without marks - this is not critical
    }
}

// Clean output and send response
ob_clean();

try {
    if (empty($students)) {
        echo json_encode([
            'success' => false, 
            'message' => 'No students found for this class/stream combination',
            'students' => [],
            'has_papers' => $has_papers,
            'papers' => $papers,
            'debug' => [
                'class_id' => $class_id,
                'stream_id' => $stream_id,
                'school_id' => $school_id,
                'exam_id' => $exam_id,
                'subject_id' => $subject_id,
                'expected_student_role' => 3,
                'expected_status' => 'active'
            ]
        ]);
    } else {
        echo json_encode([
            'success' => true, 
            'message' => 'Students loaded successfully (' . count($students) . ' students)',
            'students' => $students,
            'has_papers' => $has_papers,
            'papers' => $papers,
            'debug' => [
                'total_found' => count($students),
                'class_id' => $class_id,
                'stream_id' => $stream_id,
                'school_id' => $school_id,
                'exam_id' => $exam_id,
                'subject_id' => $subject_id,
                'has_papers' => $has_papers,
                'papers_count' => count($papers)
            ]
        ]);
    }
} catch (Exception $e) {
    ob_clean();
    echo json_encode([
        'success' => false, 
        'message' => 'An error occurred while loading students',
        'students' => []
    ]);
}

?>