<?php
//get_students_for_symbols.php
include('../includes/db.php');
header('Content-Type: text/html');

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

// Convert string to array if needed
if (is_string($section_ids)) {
    $section_ids = explode(',', $section_ids);
}

// Validate input
if (!$exam_id || !$class_id || empty($section_ids)) {
    echo '<div class="alert alert-danger">Please select exam, class, and sections.</div>';
    exit;
}

// Get class name
$class_query = "SELECT class FROM classes WHERE class_id = ?";
$class_stmt = $conn->prepare($class_query);
$class_stmt->bind_param("i", $class_id);
$class_stmt->execute();
$class_result = $class_stmt->get_result();
$class_row = $class_result->fetch_assoc();
$class_name = $class_row['class'] ?? '';
$class_stmt->close();

// Get section names
$section_names = [];
$section_placeholders = str_repeat('?,', count($section_ids) - 1) . '?';
$section_query = "SELECT section_id, section_name FROM sections WHERE section_id IN ($section_placeholders)";
$section_stmt = $conn->prepare($section_query);
$section_stmt->bind_param(str_repeat('i', count($section_ids)), ...$section_ids);
$section_stmt->execute();
$section_result = $section_stmt->get_result();
while ($row = $section_result->fetch_assoc()) {
    $section_names[$row['section_id']] = $row['section_name'];
}
$section_stmt->close();

// Get exam details
$exam_query = "SELECT exam_type, exm_year FROM examination_type WHERE id = ?";
$exam_stmt = $conn->prepare($exam_query);
$exam_stmt->bind_param("i", $exam_id);
$exam_stmt->execute();
$exam_result = $exam_stmt->get_result();
$exam_row = $exam_result->fetch_assoc();
$exam_name = ($exam_row['exam_type'] ?? '') . ' (' . ($exam_row['exm_year'] ?? '') . ')';
$exam_stmt->close();

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

// Get students from selected class and sections using student_class_section_data table
$student_query = "SELECT 
                    s.student_id,
                    s.regd_id,
                    s.name,
                    s.symbol_no as existing_symbol,
                    s.registration_no,
                    scsd.section_id,
                    sec.section_name
                 FROM students s
                 INNER JOIN student_class_section_data scsd ON s.student_id = scsd.student_id
                 INNER JOIN sections sec ON scsd.section_id = sec.section_id
                 WHERE scsd.class_id = ? 
                 AND scsd.section_id IN ($section_placeholders)
                 AND scsd.status = 'active'
                 AND scsd.academic_year = ?
                 AND s.status = 'active'
                 ORDER BY sec.section_name, s.registration_no ASC";

// Prepare statement
$student_stmt = $conn->prepare($student_query);
if (!$student_stmt) {
    echo '<div class="alert alert-danger">Error preparing query: ' . $conn->error . '</div>';
    exit;
}

// Bind parameters: class_id + all section_ids + academic_year
$types = 'i' . str_repeat('i', count($section_ids)) . 's';
$params = [$class_id];
foreach ($section_ids as $section_id) {
    $params[] = $section_id;
}
$params[] = $current_academic_year;

$student_stmt->bind_param($types, ...$params);
$student_stmt->execute();
$student_result = $student_stmt->get_result();

if ($student_result->num_rows === 0) {
    // Try without academic year filter as fallback
    $student_query2 = "SELECT 
                        s.student_id,
                        s.regd_id,
                        s.name,
                        s.symbol_no as existing_symbol,
                        s.registration_no,
                        scsd.section_id,
                        sec.section_name
                     FROM students s
                     INNER JOIN student_class_section_data scsd ON s.student_id = scsd.student_id
                     INNER JOIN sections sec ON scsd.section_id = sec.section_id
                     WHERE scsd.class_id = ? 
                     AND scsd.section_id IN ($section_placeholders)
                     AND scsd.status = 'active'
                     AND s.status = 'active'
                     ORDER BY sec.section_name, s.registration_no ASC";
    
    $student_stmt2 = $conn->prepare($student_query2);
    $types2 = 'i' . str_repeat('i', count($section_ids));
    $params2 = [$class_id];
    foreach ($section_ids as $section_id) {
        $params2[] = $section_id;
    }
    
    $student_stmt2->bind_param($types2, ...$params2);
    $student_stmt2->execute();
    $student_result = $student_stmt2->get_result();
    
    if ($student_result->num_rows === 0) {
        echo '<div class="alert alert-warning">
                <i class="fa fa-exclamation-triangle"></i> 
                No active students found in the selected class and sections for the current academic year.
              </div>';
        exit;
    }
    $student_stmt = $student_stmt2;
}

// Get existing symbol numbers for this exam
$existing_symbols = [];
$symbol_query = "SELECT student_id, symbol_no FROM symbolno_data WHERE exam_id = ? AND class_id = ?";
$symbol_stmt = $conn->prepare($symbol_query);
$symbol_stmt->bind_param("ii", $exam_id, $class_id);
$symbol_stmt->execute();
$symbol_result = $symbol_stmt->get_result();
while ($row = $symbol_result->fetch_assoc()) {
    $existing_symbols[$row['student_id']] = $row['symbol_no'];
}
$symbol_stmt->close();

// Display summary
echo '<div class="alert alert-info">
        <i class="fa fa-info-circle"></i> 
        Found <strong>' . $student_result->num_rows . '</strong> students in 
        <strong>' . $class_name . '</strong> - 
        Sections: <strong>' . implode(', ', $section_names) . '</strong>
        <br><small>Academic Year: ' . $current_academic_year . '</small>
      </div>';

// Start students list
$output = '<div class="students-list-container">';
$current_section_id = '';
$section_counter = 1;

while ($student = $student_result->fetch_assoc()) {
    // Start new section group
    if ($current_section_id != $student['section_id']) {
        if ($current_section_id != '') {
            $output .= '</div>'; // Close previous section
        }
        $current_section_id = $student['section_id'];
        $section_name = $student['section_name'] ?? 'Unknown';
        
        $output .= '<div class="section-group mb-4">';
        $output .= '<h6 class="bg-light p-2 rounded">
                      <i class="fa fa-columns"></i> Section ' . $section_counter . ': ' . 
                      htmlspecialchars($section_name) . '
                    </h6>';
        $section_counter++;
    }
    
    $student_id = $student['student_id'];
    $has_existing_symbol = isset($existing_symbols[$student_id]);
    $existing_symbol = $has_existing_symbol ? $existing_symbols[$student_id] : '';
    
    $status_class = $has_existing_symbol ? 'info' : 'warning';
    $status_text = $has_existing_symbol ? 'Assigned' : 'Pending';
    $status_icon = $has_existing_symbol ? 'fa-check-circle' : 'fa-clock-o';
    
    $output .= '<div class="student-item d-flex justify-content-between align-items-center p-2 border-bottom">';
    $output .= '<div class="student-info">';
    $output .= '<strong>' . htmlspecialchars($student['name']) . '</strong>';
    $output .= '<div class="small text-muted">';
    $output .= 'Reg ID: ' . htmlspecialchars($student['regd_id']) . ' | ';
    $output .= 'Roll No: ' . htmlspecialchars($student['registration_no'] ?? 'N/A');
    $output .= '</div>';
    $output .= '</div>';
    
    $output .= '<div class="symbol-input-group d-flex align-items-center">';
    $output .= '<span class="symbol-badge badge badge-' . $status_class . ' mr-2">';
    $output .= '<i class="fa ' . $status_icon . '"></i> ' . $status_text;
    $output .= '</span>';
    
    $output .= '<input type="text" 
                         name="symbols[' . $student_id . ']"
                         class="form-control symbol-input" 
                         data-student-id="' . $student_id . '"
                         placeholder="Enter symbol"
                         value="' . htmlspecialchars($existing_symbol) . '"
                         style="width: 150px;">';
    $output .= '</div>';
    $output .= '</div>';
}

if ($current_section_id != '') {
    $output .= '</div>'; // Close last section
}

$output .= '</div>';

echo $output;

// Close statement
if (isset($student_stmt) && $student_stmt) {
    $student_stmt->close();
}
?>