<?php
// ajax/get_all_students_list.php
include('../includes/db.php');
header('Content-Type: application/json');

// Get POST data
$class_id = isset($_POST['class_id']) ? intval($_POST['class_id']) : 0;
$section_ids = isset($_POST['section_ids']) ? $_POST['section_ids'] : [];
$exam_id = isset($_POST['exam_id']) ? intval($_POST['exam_id']) : 0;
$subject_id = isset($_POST['subject_id']) ? intval($_POST['subject_id']) : 0;
$subject_code = isset($_POST['subject_code']) ? $_POST['subject_code'] : '';
$mark_type = isset($_POST['mark_type']) ? $_POST['mark_type'] : 'theory';

// Validate required fields
if (!$class_id || !$exam_id || !$subject_id) {
    echo json_encode(['success' => false, 'message' => 'Missing required fields: Class, Exam, Subject are required']);
    exit;
}

// Convert section_ids to array if it's a string and not empty
$use_section_filter = false;
if (!empty($section_ids)) {
    if (is_string($section_ids)) {
        $section_ids = explode(',', $section_ids);
    }
    $section_ids = array_map('intval', array_filter($section_ids));
    $use_section_filter = !empty($section_ids);
}

// Get current academic year
$ay_query = "SELECT year_id, ay_ad FROM academic_years WHERE status = 'active' LIMIT 1";
$ay_result = $conn->query($ay_query);
$academic_year = $ay_result->fetch_assoc();
$academic_year_id = $academic_year['year_id'] ?? 1;

// Get subject full marks
$subject_query = "SELECT subject_name, subject_code, theory_full_marks, internal_full_marks FROM subjects WHERE id = ?";
$subject_stmt = $conn->prepare($subject_query);
$subject_stmt->bind_param("i", $subject_id);
$subject_stmt->execute();
$subject_result = $subject_stmt->get_result();
$subject = $subject_result->fetch_assoc();
$subject_stmt->close();

if (!$subject) {
    echo json_encode(['success' => false, 'message' => 'Subject not found']);
    exit;
}

$full_marks = ($mark_type == 'theory') ? ($subject['theory_full_marks'] ?: 100) : ($subject['internal_full_marks'] ?: 50);
$ay_value = $academic_year['ay_ad'] ?? date('Y') . '/' . (date('Y') + 1);

// Build query based on whether sections are selected or not
if ($use_section_filter) {
    // Sections selected - filter by specific sections
    $section_placeholders = implode(',', array_fill(0, count($section_ids), '?'));
    $section_types = str_repeat('i', count($section_ids));
    
    $student_query = "SELECT 
                        DISTINCT s.student_id,
                        s.name,
                        s.regd_id,
                        s.registration_no,
                        sec.section_name,
                        sn.symbol_no as exam_symbol_no,
                        ss.academic_year
                     FROM students s
                     INNER JOIN student_class_section_data scsd ON s.student_id = scsd.student_id
                     INNER JOIN studentsub ss ON s.student_id = ss.student_id
                     LEFT JOIN symbolno_data sn ON s.student_id = sn.student_id AND sn.exam_id = ?
                     LEFT JOIN sections sec ON scsd.section_id = sec.section_id
                     WHERE scsd.class_id = ? 
                     AND scsd.section_id IN ($section_placeholders)
                     AND ss.class_id = ? 
                     AND ss.section_id IN ($section_placeholders)
                     AND ss.academic_year = ?
                     AND ss.status = 'active'
                     AND (
                         ss.sub1 = ? OR 
                         ss.sub2 = ? OR 
                         ss.sub3 = ? OR 
                         ss.sub4 = ? OR 
                         ss.sub5 = ? OR 
                         ss.sub6 = ? OR 
                         ss.sub7 = ?
                     )
                     AND scsd.status = 'active'
                     AND s.status = 'active'
                     ORDER BY sec.section_name ASC, sn.symbol_no ASC, s.name ASC";
    
    $student_stmt = $conn->prepare($student_query);
    if (!$student_stmt) {
        echo json_encode(['success' => false, 'message' => 'Database error: ' . $conn->error]);
        exit;
    }
    
    // Build parameters array for sections selected
    $params = array_merge(
        [$exam_id, $class_id],
        $section_ids,
        [$class_id],
        $section_ids,
        [$ay_value],
        array_fill(0, 7, $subject_code)
    );
    
    // Bind parameters dynamically
    $types = 'i' . 'i' . $section_types . 'i' . $section_types . 's' . str_repeat('s', 7);
    $student_stmt->bind_param($types, ...$params);
    
} else {
    // No sections selected - load ALL sections for this class
    $student_query = "SELECT 
                        DISTINCT s.student_id,
                        s.name,
                        s.regd_id,
                        s.registration_no,
                        sec.section_name,
                        sn.symbol_no as exam_symbol_no,
                        ss.academic_year
                     FROM students s
                     INNER JOIN student_class_section_data scsd ON s.student_id = scsd.student_id
                     INNER JOIN studentsub ss ON s.student_id = ss.student_id
                     LEFT JOIN symbolno_data sn ON s.student_id = sn.student_id AND sn.exam_id = ?
                     LEFT JOIN sections sec ON scsd.section_id = sec.section_id
                     WHERE scsd.class_id = ? 
                     AND ss.class_id = ? 
                     AND ss.academic_year = ?
                     AND ss.status = 'active'
                     AND (
                         ss.sub1 = ? OR 
                         ss.sub2 = ? OR 
                         ss.sub3 = ? OR 
                         ss.sub4 = ? OR 
                         ss.sub5 = ? OR 
                         ss.sub6 = ? OR 
                         ss.sub7 = ?
                     )
                     AND scsd.status = 'active'
                     AND s.status = 'active'
                     ORDER BY sec.section_name ASC, sn.symbol_no ASC, s.name ASC";
    
    $student_stmt = $conn->prepare($student_query);
    if (!$student_stmt) {
        echo json_encode(['success' => false, 'message' => 'Database error: ' . $conn->error]);
        exit;
    }
    
    // Build parameters array for all sections
    $params = array_merge(
        [$exam_id, $class_id, $class_id, $ay_value],
        array_fill(0, 7, $subject_code)
    );
    
    // Bind parameters dynamically
    $types = 'i' . 'i' . 'i' . 's' . str_repeat('s', 7);
    $student_stmt->bind_param($types, ...$params);
}

$student_stmt->execute();
$student_result = $student_stmt->get_result();

$students = [];
$student_ids = [];

while ($student = $student_result->fetch_assoc()) {
    $student_ids[] = $student['student_id'];
    $students[] = [
        'student_id' => $student['student_id'],
        'name' => $student['name'],
        'regd_id' => $student['regd_id'],
        'registration_no' => $student['registration_no'],
        'section_name' => $student['section_name'],
        'symbol_no' => !empty($student['exam_symbol_no']) ? $student['exam_symbol_no'] : '',
        'academic_year' => $student['academic_year']
    ];
}

$student_stmt->close();

// Get existing marks for these students
$existing_marks = [];
if (!empty($student_ids)) {
    $ids_string = implode(',', $student_ids);
    
    if ($use_section_filter) {
        // With section filter
        $section_placeholders = implode(',', array_fill(0, count($section_ids), '?'));
        $marks_query = "SELECT student_id, marks, remarks FROM marks 
                        WHERE class_id = ? AND section_id IN ($section_placeholders)
                        AND subject_id = ? AND exam_type = ? 
                        AND academic_year_id = ? AND mark_type = ? 
                        AND student_id IN ($ids_string)";
        $marks_stmt = $conn->prepare($marks_query);
        
        $marks_params = array_merge([$class_id], $section_ids, [$subject_id, $exam_id, $academic_year_id, $mark_type]);
        $marks_types = 'i' . $section_types . 'iiis';
        $marks_stmt->bind_param($marks_types, ...$marks_params);
        
    } else {
        // Without section filter - get all sections
        $marks_query = "SELECT student_id, marks, remarks FROM marks 
                        WHERE class_id = ? 
                        AND subject_id = ? AND exam_type = ? 
                        AND academic_year_id = ? AND mark_type = ? 
                        AND student_id IN ($ids_string)";
        $marks_stmt = $conn->prepare($marks_query);
        $marks_stmt->bind_param("iiiiis", $class_id, $subject_id, $exam_id, $academic_year_id, $mark_type);
    }
    
    $marks_stmt->execute();
    $marks_result = $marks_stmt->get_result();
    
    while ($mark = $marks_result->fetch_assoc()) {
        $existing_marks[$mark['student_id']] = [
            'marks' => $mark['marks'],
            'remarks' => $mark['remarks']
        ];
    }
    $marks_stmt->close();
}

// Merge existing marks into student data
foreach ($students as &$student) {
    if (isset($existing_marks[$student['student_id']])) {
        $student['existing_mark'] = $existing_marks[$student['student_id']]['marks'];
        $student['remarks'] = $existing_marks[$student['student_id']]['remarks'];
    } else {
        $student['existing_mark'] = '';
        $student['remarks'] = '';
    }
}

// Return JSON response
echo json_encode([
    'success' => true,
    'students' => $students,
    'total' => count($students),
    'full_marks' => $full_marks,
    'subject_name' => $subject['subject_name'],
    'subject_code' => $subject['subject_code'],
    'section_filter_applied' => $use_section_filter,
    'sections_selected' => $section_ids
]);

$conn->close();
?>