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

echo "Cleaning up existing test data...\n";

// Delete existing results for student ID 6185
$stmt = $pdo->prepare("DELETE FROM results WHERE student_id = ?");
$stmt->execute([6185]);

echo "Inserting clean test data...\n";

// Define clean subject data with unique subjects
$subjects_data = [
    ['name' => 'Bangla', 'code' => 'BAN101', 'marks' => 91, 'grade' => 'A+'],
    ['name' => 'English', 'code' => 'ENG101', 'marks' => 88, 'grade' => 'A+'],
    ['name' => 'Mathematics', 'code' => 'MATH101', 'marks' => 75, 'grade' => 'A'],
    ['name' => 'Science', 'code' => 'SCI101', 'marks' => 65, 'grade' => 'A-'],
    ['name' => 'Social Science', 'code' => 'SOC101', 'marks' => 55, 'grade' => 'B'],
];

// Get subject IDs or create subjects if they don't exist
$subject_ids = [];
foreach ($subjects_data as $subject_data) {
    // Check if subject exists
    $stmt = $pdo->prepare("SELECT id FROM subjects WHERE code = ?");
    $stmt->execute([$subject_data['code']]);
    $subject = $stmt->fetch();
    
    if (!$subject) {
        // Create subject
        $stmt = $pdo->prepare("INSERT INTO subjects (name, code) VALUES (?, ?)");
        $stmt->execute([$subject_data['name'], $subject_data['code']]);
        $subject_id = $pdo->lastInsertId();
    } else {
        $subject_id = $subject['id'];
    }
    
    $subject_ids[] = [
        'id' => $subject_id,
        'marks' => $subject_data['marks'],
        'grade' => $subject_data['grade']
    ];
}

// Insert clean results
foreach ($subject_ids as $subject) {
    $stmt = $pdo->prepare("INSERT INTO results (student_id, subject_id, marks_obtained, grade, session, exam_type) VALUES (?, ?, ?, ?, ?, ?)");
    $stmt->execute([6185, $subject['id'], $subject['marks'], $subject['grade'], '2025', 'midterm']);
    echo "Inserted result: Subject ID " . $subject['id'] . ", Marks " . $subject['marks'] . ", Grade " . $subject['grade'] . "\n";
}

echo "Clean test data inserted successfully!\n";
?>