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

// Get parameters
$exam_id = isset($_GET['exam_id']) ? intval($_GET['exam_id']) : 0;
$class_id = isset($_GET['class_id']) ? intval($_GET['class_id']) : 0;
$section_ids = isset($_GET['section_ids']) ? explode(',', $_GET['section_ids']) : [];

// Validate input
if (!$exam_id || !$class_id || empty($section_ids)) {
    die('Invalid parameters');
}

// 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_stmt->close();

$exam_name = ($exam_row['exam_type'] ?? '') . '_' . ($exam_row['exm_year'] ?? '');

// 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_stmt->close();

$class_name = $class_row['class'] ?? '';

// 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_name'];
}
$section_stmt->close();

// Get students
$student_query = "SELECT 
                    s.student_id,
                    s.regd_id,
                    s.name,
                    s.registration_no,
                    s.section,
                    sn.symbol_no as existing_symbol
                 FROM students s
                 LEFT JOIN symbolno_data sn ON s.student_id = sn.student_id AND sn.exam_id = ?
                 WHERE s.Class_id = ? 
                 AND s.section IN ($section_placeholders)
                 AND s.status = 'active'
                 ORDER BY s.section, s.registration_no ASC";

$student_stmt = $conn->prepare($student_query);
$types = 'ii' . str_repeat('s', count($section_ids));
$params = [$exam_id, $class_id];
foreach ($section_ids as $section_id) {
    $params[] = $section_id;
}
$student_stmt->bind_param($types, ...$params);
$student_stmt->execute();
$student_result = $student_stmt->get_result();

// Set headers for CSV download
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="symbol_template_' . $exam_name . '_' . $class_name . '.csv"');

// Create output stream
$output = fopen('php://output', 'w');

// Add BOM for UTF-8
fputs($output, $bom = (chr(0xEF) . chr(0xBB) . chr(0xBF)));

// Write headers
fputcsv($output, [
    'Student ID',
    'Registration ID',
    'Student Name',
    'Roll Number',
    'Section',
    'Existing Symbol',
    'New Symbol',
    'Remarks'
]);

// Write data rows
while ($student = $student_result->fetch_assoc()) {
    fputcsv($output, [
        $student['student_id'],
        $student['regd_id'],
        $student['name'],
        $student['registration_no'],
        $student['section'],
        $student['existing_symbol'] ?? '',
        '', // Empty column for new symbol
        ''  // Empty column for remarks
    ]);
}

// Write footer with instructions
fputcsv($output, []);
fputcsv($output, ['INSTRUCTIONS:']);
fputcsv($output, ['1. Fill in the "New Symbol" column with symbol numbers']);
fputcsv($output, ['2. Do NOT change the "Student ID" column']);
fputcsv($output, ['3. Keep the header row as is']);
fputcsv($output, ['4. Save as CSV file before uploading']);

$student_stmt->close();
fclose($output);
?>