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

// 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 json_encode(['success' => false, 'error' => 'Invalid input parameters']);
    exit;
}

// Get exam details for prefix
$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_year = substr($exam_row['exm_year'] ?? '', 2, 2); // Last 2 digits of year
$exam_prefix = strtoupper(substr($exam_row['exam_type'] ?? '', 0, 3)) . $exam_year;

// Get class info - FIXED: use class_numeric instead of class_code
$class_query = "SELECT class, class_numeric 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();

// Use class_numeric for code, or pad class_id if not available
$class_code = isset($class_row['class_numeric']) ? str_pad($class_row['class_numeric'], 2, '0', STR_PAD_LEFT) : str_pad($class_id, 2, '0', STR_PAD_LEFT);

// Get existing highest symbol number for this exam and class
$max_symbol_query = "SELECT MAX(CAST(SUBSTRING(symbol_no, -4) AS UNSIGNED)) as max_num 
                     FROM symbolno_data 
                     WHERE exam_id = ? AND class_id = ?";
$max_stmt = $conn->prepare($max_symbol_query);
$max_stmt->bind_param("ii", $exam_id, $class_id);
$max_stmt->execute();
$max_result = $max_stmt->get_result();
$max_row = $max_result->fetch_assoc();
$max_stmt->close();

$start_number = ($max_row['max_num'] ?? 0) + 1;

// Get current academic year
$academic_year_query = "SELECT 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();
$current_academic_year = $academic_year['ay_ad'] ?? '2024-2025';

// Get students using student_class_section_data table (consistent with get_students_for_symbols.php)
$section_placeholders = str_repeat('?,', count($section_ids) - 1) . '?';
$student_query = "SELECT 
                    s.student_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";

$student_stmt = $conn->prepare($student_query);
$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
    $student_query2 = "SELECT 
                        s.student_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 json_encode(['success' => false, 'error' => 'No active students found in the selected class and sections']);
        exit;
    }
    $student_stmt = $student_stmt2;
}

// Get existing symbols to avoid duplicates
$existing_query = "SELECT student_id, symbol_no FROM symbolno_data WHERE exam_id = ? AND class_id = ?";
$existing_stmt = $conn->prepare($existing_query);
$existing_stmt->bind_param("ii", $exam_id, $class_id);
$existing_stmt->execute();
$existing_result = $existing_stmt->get_result();
$existing_symbols = [];
while ($row = $existing_result->fetch_assoc()) {
    $existing_symbols[$row['student_id']] = $row['symbol_no'];
}
$existing_stmt->close();

$generated_symbols = [];
$current_number = $start_number;

// Begin transaction
$conn->begin_transaction();

try {
    while ($student = $student_result->fetch_assoc()) {
        $student_id = $student['student_id'];
        
        // Skip if already has a symbol
        if (isset($existing_symbols[$student_id])) {
            $generated_symbols[] = [
                'student_id' => $student_id,
                'symbol_no' => $existing_symbols[$student_id],
                'status' => 'existing'
            ];
            continue;
        }
        
        // Generate new symbol number
        // Format: PREFIX-CLASS-0001 (e.g., TERM24-11-0001)
        $symbol_no = $exam_prefix . '-' . $class_code . '-' . str_pad($current_number, 4, '0', STR_PAD_LEFT);
        
        // Insert into database
        $insert_query = "INSERT INTO symbolno_data (student_id, class_id, exam_id, symbol_no) 
                         VALUES (?, ?, ?, ?)";
        $insert_stmt = $conn->prepare($insert_query);
        $insert_stmt->bind_param("iiis", $student_id, $class_id, $exam_id, $symbol_no);
        
        if ($insert_stmt->execute()) {
            // Update students table
            $update_student = $conn->prepare("UPDATE students SET symbol_no = ? WHERE student_id = ?");
            $update_student->bind_param("si", $symbol_no, $student_id);
            $update_student->execute();
            $update_student->close();
            
            $generated_symbols[] = [
                'student_id' => $student_id,
                'symbol_no' => $symbol_no,
                'status' => 'generated'
            ];
            
            $current_number++;
        }
        $insert_stmt->close();
    }
    
    // Commit transaction
    $conn->commit();
    
    $generated_count = count(array_filter($generated_symbols, function($item) {
        return $item['status'] === 'generated';
    }));
    
    $existing_count = count(array_filter($generated_symbols, function($item) {
        return $item['status'] === 'existing';
    }));
    
    echo json_encode([
        'success' => true,
        'message' => "Generated $generated_count new symbol numbers. $existing_count already had symbols.",
        'symbols' => $generated_symbols,
        'generated' => $generated_count,
        'existing' => $existing_count,
        'format' => $exam_prefix . '-' . $class_code . '-XXXX'
    ]);
    
} catch (Exception $e) {
    // Rollback transaction on error
    $conn->rollback();
    
    echo json_encode([
        'success' => false,
        'error' => 'Failed to generate symbols: ' . $e->getMessage()
    ]);
}

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