<?php
// ajax/get_control_sheet.php
header('Content-Type: application/json');

// Include database connection
require_once('../includes/db.php');

// 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'] : [];

// Validate input
if (!$exam_id || !$class_id || empty($section_ids)) {
    echo json_encode(['error' => 'Missing required parameters']);
    exit;
}

// Sanitize section IDs
$section_ids = array_map('intval', $section_ids);
$section_ids = array_filter($section_ids);

if (empty($section_ids)) {
    echo json_encode(['error' => 'Invalid section IDs']);
    exit;
}

try {
    // Get exam details
    $examQuery = "SELECT id, exam_type, exm_year FROM examination_type WHERE id = ?";
    $stmt = $conn->prepare($examQuery);
    $stmt->bind_param('i', $exam_id);
    $stmt->execute();
    $examResult = $stmt->get_result();
    $examData = $examResult->fetch_assoc();
    
    if (!$examData) {
        echo json_encode(['error' => 'Exam not found']);
        exit;
    }
    
    // Get class details (note: column name is 'class' not 'class_name')
    $classQuery = "SELECT class_id, class FROM classes WHERE class_id = ?";
    $stmt = $conn->prepare($classQuery);
    $stmt->bind_param('i', $class_id);
    $stmt->execute();
    $classResult = $stmt->get_result();
    $classData = $classResult->fetch_assoc();
    
    if (!$classData) {
        echo json_encode(['error' => 'Class not found']);
        exit;
    }
    
    // Create placeholders for section IDs
    $placeholders = implode(',', array_fill(0, count($section_ids), '?'));
    
    // Get section details
    $sectionQuery = "SELECT section_id, section_name FROM sections WHERE section_id IN ($placeholders) ORDER BY section_name";
    $stmt = $conn->prepare($sectionQuery);
    
    if (count($section_ids) > 0) {
        $stmt->bind_param(str_repeat('i', count($section_ids)), ...$section_ids);
    }
    
    $stmt->execute();
    $sectionResult = $stmt->get_result();
    
    $sections = [];
    while ($row = $sectionResult->fetch_assoc()) {
        $sections[$row['section_id']] = $row['section_name'];
    }
    
    // Get students with their symbol numbers from symbolno_data table
    $students = [];
    $totalStudents = 0;
    $studentsWithSymbols = 0;
    
    // Query to get students with their symbol numbers
    $query = "SELECT 
                s.student_id,
                s.name,
                s.registration_no,
                s.parent_name,
                s.phone as parent_phone,
                s.dob,
                sd.symbol_no,
                sec.section_name,
                sec.section_id
              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
              LEFT JOIN symbolno_data sd ON sd.student_id = s.student_id 
                AND sd.exam_id = ? AND sd.class_id = ?
              WHERE scsd.class_id = ? AND scsd.section_id IN ($placeholders) 
                
                AND scsd.status = 'active'
              ORDER BY sec.section_name, s.name ASC";
    
    // Prepare parameters
    $param_types = 'iii' . str_repeat('i', count($section_ids));
    $params = array_merge([$exam_id, $class_id, $class_id], $section_ids);
    
    $stmt = $conn->prepare($query);
    if ($stmt) {
        $stmt->bind_param($param_types, ...$params);
        $stmt->execute();
        $result = $stmt->get_result();
        
        while ($row = $result->fetch_assoc()) {
            $students[] = [
                'student_id' => $row['student_id'],
                'name' => $row['name'],
                'registration_no' => $row['registration_no'] ?? 'N/A',
                'father_name' => $row['parent_name'] ?? 'N/A',
                'mother_name' => '', // Not available in students table
                'symbol_no' => $row['symbol_no'] ?? null,
                'section_name' => $row['section_name'],
                'dob' => $row['dob'],
                'section_id' => $row['section_id']
            ];
            
            $totalStudents++;
            if (!empty($row['symbol_no'])) {
                $studentsWithSymbols++;
            }
        }
    }
    
    // If no students found, return appropriate message
    if (empty($students)) {
        echo json_encode([
            'success' => true,
            'exam_name' => $examData['exam_type'] . ' (' . $examData['exm_year'] . ')',
            'class_name' => $classData['class'] ?? 'N/A',
            'sections' => $sections,
            'summary' => [
                'total_students' => 0,
                'total_sections' => count($sections),
                'with_symbols' => 0
            ],
            'data' => [],
            'message' => 'No active students found for the selected criteria.'
        ]);
        exit;
    }
    
    echo json_encode([
        'success' => true,
        'exam_name' => $examData['exam_type'] . ' (' . $examData['exm_year'] . ')',
        'class_name' => $classData['class'] ?? 'N/A',
        'sections' => $sections,
        'summary' => [
            'total_students' => $totalStudents,
            'total_sections' => count($sections),
            'with_symbols' => $studentsWithSymbols
        ],
        'data' => $students
    ]);
    
} catch (Exception $e) {
    http_response_code(500);
    echo json_encode(['error' => 'Database error: ' . $e->getMessage()]);
}

// Close connection
if (isset($conn)) {
    $conn->close();
}
?>