<?php
require_once 'config/database.php';

function get_subject_ids_by_codes($pdo, $subject_codes_str) {
    if (empty($subject_codes_str)) return [];
    
    // Split by comma to get individual code segments
    $segments = array_map('trim', explode(',', $subject_codes_str));
    
    // Process each segment to handle both individual codes and ranges
    $all_codes = [];
    foreach ($segments as $segment) {
        if (strpos($segment, '_') !== false) {
            // Handle range notation (e.g., "101_102" or "253_254")
            $parts = explode('_', $segment);
            if (count($parts) == 2) {
                $start = (int)$parts[0];
                $end = (int)$parts[1];
                // Add all codes in the range
                for ($i = $start; $i <= $end; $i++) {
                    $all_codes[] = (string)$i;
                }
            } else {
                // If not a proper range, treat as individual code
                $all_codes[] = $segment;
            }
        } else {
            // Individual code
            $all_codes[] = $segment;
        }
    }
    
    // Remove duplicates
    $all_codes = array_unique($all_codes);
    
    if (empty($all_codes)) return [];
    
    $placeholders = rtrim(str_repeat('?,', count($all_codes)), ',');
    
    $stmt = $pdo->prepare("SELECT id FROM subjects WHERE code IN ({$placeholders})");
    $stmt->execute($all_codes);
    $results = $stmt->fetchAll(PDO::FETCH_COLUMN);
    return $results;
}

// Test the complete student upload process with range notation
echo "=== Testing Complete Student Upload Process ===\n\n";

try {
    $pdo->beginTransaction();
    
    // First, let's create a test student
    $stmt_insert = $pdo->prepare("
        INSERT INTO students (
            student_id, first_name, last_name, father_name, mother_name, email, phone, address,
            date_of_birth, gender, class_id, session, group_name, roll_number, admission_date,
            guardian_name, guardian_phone, guardian_email, guardian_address, status
        ) VALUES (
            :student_id, :first_name, :last_name, :father_name, :mother_name, :email, :phone, :address,
            :date_of_birth, :gender, :class_id, :session, :group_name, :roll_number, :admission_date,
            :guardian_name, :guardian_phone, :guardian_email, :guardian_address, 'active'
        )
    ");
    
    $student_data = [
        ':student_id' => 'TEST' . time(),
        ':first_name' => 'Test',
        ':last_name' => 'Student',
        ':father_name' => 'Test Father',
        ':mother_name' => 'Test Mother',
        ':email' => 'test@example.com',
        ':phone' => '1234567890',
        ':address' => 'Test Address',
        ':date_of_birth' => '2010-01-01',
        ':gender' => 'male',
        ':class_id' => 13,  // Using valid class ID from our check
        ':session' => '2025',
        ':group_name' => 'Science',
        ':roll_number' => 999,
        ':admission_date' => '2025-01-01',
        ':guardian_name' => 'Test Guardian',
        ':guardian_phone' => '1234567890',
        ':guardian_email' => 'guardian@example.com',
        ':guardian_address' => 'Test Guardian Address'
    ];
    
    $stmt_insert->execute($student_data);
    $student_db_id = $pdo->lastInsertId();
    
    echo "Created test student with ID: $student_db_id\n";
    
    // Now test the subject assignment with range notation
    $subject_codes_input = "101_102";
    echo "Testing subject codes input: '$subject_codes_input'\n";
    
    $subject_ids = get_subject_ids_by_codes($pdo, $subject_codes_input);
    echo "Subject IDs found: " . json_encode($subject_ids) . "\n";
    
    if (!empty($subject_ids)) {
        $stmt_subj = $pdo->prepare("INSERT INTO student_subjects (student_id, subject_id, is_fourth_subject) VALUES (?, ?, ?)");
        foreach ($subject_ids as $subject_id) {
            echo "Inserting subject ID: $subject_id for student ID: $student_db_id\n";
            try {
                $stmt_subj->execute([$student_db_id, $subject_id, 0]);
                echo "  Success!\n";
            } catch (PDOException $e) {
                echo "  Error: " . $e->getMessage() . "\n";
                // Ignore duplicate subject error
                if (isset($e->errorInfo[1]) && $e->errorInfo[1] != 1062) {
                    throw $e;
                }
            }
        }
    }
    
    // Now verify the subjects were inserted correctly
    $stmt_verify = $pdo->prepare("
        SELECT s.id, s.code, s.name 
        FROM student_subjects ss 
        JOIN subjects s ON ss.subject_id = s.id 
        WHERE ss.student_id = ?
    ");
    $stmt_verify->execute([$student_db_id]);
    $assigned_subjects = $stmt_verify->fetchAll(PDO::FETCH_ASSOC);
    
    echo "\nAssigned subjects for student ID $student_db_id:\n";
    foreach ($assigned_subjects as $subject) {
        echo "  ID: {$subject['id']} | Code: {$subject['code']} | Name: {$subject['name']}\n";
    }
    
    echo "\nExpected: Both Bangla 1st Paper (101) and Bangla 2nd Paper (102)\n";
    echo "Actual count: " . count($assigned_subjects) . " subjects\n";
    
    // Rollback to avoid polluting the database
    $pdo->rollBack();
    echo "\nTest completed and rolled back.\n";
    
} catch (Exception $e) {
    $pdo->rollBack();
    echo "Error: " . $e->getMessage() . "\n";
}
?>