<?php
require_once('source/dbconfig.php');
require_once('source/function.php');
session_start();

// Verify exam session
if (!isset($_SESSION['student']) || !isset($_SESSION['current_exam'])) {
    header("Location: cbt_index.php");
    exit();
}

// Initialize variables
$student = $_SESSION['student'];
$exam_info = $_SESSION['current_exam'];
$answers = $_POST['answers'] ?? [];
$score = 0;
$totalQuestions = count($_SESSION['current_exam']['question_ids']);
$correctAnswers = 0;

// ... [previous code remains the same until the try block]

try {
    $DBH->beginTransaction();

    // 1. Calculate score (original code remains the same)
    if (!empty($answers)) {
        $placeholders = implode(',', array_fill(0, $totalQuestions, '?'));
        $stmt = $DBH->prepare("
            SELECT id, correct_answer 
            FROM tbl_question 
            WHERE id IN ($placeholders)
        ");
        $stmt->execute($_SESSION['current_exam']['question_ids']);
        $correctAnswersData = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

        foreach ($answers as $questionId => $selectedOption) {
            if (isset($correctAnswersData[$questionId]) && 
                $correctAnswersData[$questionId] === $selectedOption) {
                $correctAnswers++;
            }
        }
        $score = round(($correctAnswers / $totalQuestions) * 100, 2);
    }

    // 2. Save results to tbl_cbt_results (original code remains the same)
    $timeSpent = ($_SESSION['current_exam']['duration'] * 60) - ($_SESSION['current_exam']['end_time'] - time());
    $timeSpent = max(0, min($timeSpent, $_SESSION['current_exam']['duration'] * 60));

    $stmt = $DBH->prepare("
        INSERT INTO tbl_cbt_results (
            registration_no,
            subject,
            class,
            term,
            session,
            score,
            total_questions,
            correct_answers,
            answers,
            date_taken,
            time_spent
        ) VALUES (
            :reg_no,
            :subject,
            :class,
            :term,
            :session,
            :score,
            :total_questions,
            :correct_answers,
            :answers,
            NOW(),
            :time_spent
        )
    ");

    $stmt->execute([
        'reg_no' => $student['registration_no'],
        'subject' => $exam_info['subject_name'],
        'class' => $exam_info['class'],
        'term' => $_SESSION['exam_info']['term'],
        'session' => $_SESSION['exam_info']['session'],
        'score' => $score,
        'total_questions' => $totalQuestions,
        'correct_answers' => $correctAnswers,
        'answers' => json_encode($answers),
        'time_spent' => $timeSpent
    ]);

    // 3. Calculate exam score out of 40
    $examScoreOutOf40 = round(($correctAnswers / $totalQuestions) * 40, 2);

    // 4. Get student's full name from tbl_user
    $stmt = $DBH->prepare("
        SELECT fullname 
        FROM tbl_user 
        WHERE registration_no = :reg_no 
        LIMIT 1
    ");
    $stmt->execute(['reg_no' => $student['registration_no']]);
    $studentData = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if (!$studentData || !isset($studentData['fullname'])) {
        throw new Exception("Student record not found in tbl_user");
    }
    
    $studentName = $studentData['fullname'];

    // 5. Update or insert into tbl_student_results
    $stmt = $DBH->prepare("
        SELECT id, test1, test2, project 
        FROM tbl_student_results 
        WHERE registration_no = :reg_no 
        AND subject = :subject 
        AND class = :class 
        AND term = :term 
        AND year = :year
        LIMIT 1
    ");
    
    $stmt->execute([
        'reg_no' => $student['registration_no'],
        'subject' => $exam_info['subject_name'],
        'class' => $exam_info['class'],
        'term' => $_SESSION['exam_info']['term'],
        'year' => $_SESSION['exam_info']['session']
    ]);
    
    $existingRecord = $stmt->fetch(PDO::FETCH_ASSOC);
    
    if ($existingRecord) {
        // Update existing record
        $test1 = $existingRecord['test1'] ?? 0;
        $test2 = $existingRecord['test2'] ?? 0;
        $project = $existingRecord['project'] ?? 0;
        
        $total = $test1 + $test2 + $project + $examScoreOutOf40;
        
        $updateStmt = $DBH->prepare("
            UPDATE tbl_student_results 
            SET exam = :exam,
                total = :total,
                updated_at = NOW()
            WHERE id = :id
        ");
        
        $updateStmt->execute([
            'exam' => $examScoreOutOf40,
            'total' => $total,
            'id' => $existingRecord['id']
        ]);
    } else {
        // Insert new record (exam only, others will be 0)
        $total = $examScoreOutOf40;
        
        $insertStmt = $DBH->prepare("
            INSERT INTO tbl_student_results (
                name,
                registration_no,
                test1,
                test2,
                project,
                exam,
                total,
                class,
                subject,
                year,
                term,
                created_at,
                updated_at
            ) VALUES (
                :name,
                :reg_no,
                0,
                0,
                0,
                :exam,
                :total,
                :class,
                :subject,
                :year,
                :term,
                NOW(),
                NOW()
            )
        ");
        
        $insertStmt->execute([
            'name' => $studentName,  // Using the fetched fullname
            'reg_no' => $student['registration_no'],
            'exam' => $examScoreOutOf40,
            'total' => $total,
            'class' => $exam_info['class'],
            'subject' => $exam_info['subject_name'],
            'year' => $_SESSION['exam_info']['session'],
            'term' => $_SESSION['exam_info']['term']
        ]);
    }

    // Commit all changes 
    $DBH->commit();
    
    // 5. Clear exam session and redirect to results
    unset($_SESSION['current_exam']);
    $_SESSION['exam_completed'] = [
        'score' => $score,
        'correct' => $correctAnswers,
        'total' => $totalQuestions,
        'subject' => $exam_info['subject_name'],
        'class' => $exam_info['class'],
        'term' => $_SESSION['exam_info']['term'],
        'session' => $_SESSION['exam_info']['session']
    ];

    header("Location: exam_results.php");
    exit();

} catch (PDOException $e) {
    if ($DBH->inTransaction()) {
        $DBH->rollBack();
    }
    die("Database error: " . $e->getMessage());
} catch (Exception $e) {
    if ($DBH->inTransaction()) {
        $DBH->rollBack();
    }
    die("Error: " . $e->getMessage());
}