<?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;

try {
    $DBH->beginTransaction();

    // 1. Calculate score
    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. Calculate time spent
    $timeSpent = ($_SESSION['current_exam']['duration'] * 60) - ($_SESSION['current_exam']['end_time'] - time());
    $timeSpent = max(0, min($timeSpent, $_SESSION['current_exam']['duration'] * 60));

    // 3. Get assessment type and determine contribution
    $assessmentType = $exam_info['assessment_type'] ?? $_SESSION['exam_info']['assessment_type'] ?? 'Term Exam';
    $contributionPercentage = 0;
    $targetColumn = '';
    
    switch($assessmentType) {
        case 'CA1':
            $contributionPercentage = 20;
            $targetColumn = 'test1';
            break;
        case 'CA2':
            $contributionPercentage = 20;
            $targetColumn = 'test2';
            break;
        case 'CA3':
            $contributionPercentage = 20;
            $targetColumn = 'project';
            break;
        case 'Term Exam':
            $contributionPercentage = 60;
            $targetColumn = 'exam';
            break;
        case 'Mock Exam':
            $contributionPercentage = 100;
            $targetColumn = 'mock';
            break;
        default:
            $contributionPercentage = 60;
            $targetColumn = 'exam';
    }
    
    // Calculate the score that goes to the term result (percentage of total)
    $termResultScore = round(($correctAnswers / $totalQuestions) * $contributionPercentage, 2);

    // 4. Save results to tbl_cbt_results with assessment type
    $stmt = $DBH->prepare("
        INSERT INTO tbl_cbt_results (
            registration_no,
            subject,
            class,
            term,
            session,
            assessment_type,
            score,
            total_questions,
            correct_answers,
            answers,
            date_taken,
            time_spent
        ) VALUES (
            :reg_no,
            :subject,
            :class,
            :term,
            :session,
            :assessment_type,
            :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'],
        'assessment_type' => $assessmentType,
        'score' => $score,
        'total_questions' => $totalQuestions,
        'correct_answers' => $correctAnswers,
        'answers' => json_encode($answers),
        'time_spent' => $timeSpent
    ]);

    // 5. 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'];

    // 6. Update or insert into tbl_student_results with the correct column
    $stmt = $DBH->prepare("
        SELECT id, test1, test2, project, exam, mock 
        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 with the appropriate column
        $test1 = $existingRecord['test1'] ?? 0;
        $test2 = $existingRecord['test2'] ?? 0;
        $project = $existingRecord['project'] ?? 0;
        $exam = $existingRecord['exam'] ?? 0;
        $mock = $existingRecord['mock'] ?? 0;
        
        // Calculate total based on which column is being updated
        if ($targetColumn == 'test1') {
            $total = $termResultScore + $test2 + $project + $exam + $mock;
        } elseif ($targetColumn == 'test2') {
            $total = $test1 + $termResultScore + $project + $exam + $mock;
        } elseif ($targetColumn == 'project') {
            $total = $test1 + $test2 + $termResultScore + $exam + $mock;
        } elseif ($targetColumn == 'exam') {
            $total = $test1 + $test2 + $project + $termResultScore + $mock;
        } elseif ($targetColumn == 'mock') {
            $total = $test1 + $test2 + $project + $exam + $termResultScore;
        } else {
            $total = $test1 + $test2 + $project + $exam + $mock;
        }
        
        $updateStmt = $DBH->prepare("
            UPDATE tbl_student_results 
            SET $targetColumn = :score,
                total = :total,
                updated_at = NOW()
            WHERE id = :id
        ");
        
        $updateStmt->execute([
            'score' => $termResultScore,
            'total' => $total,
            'id' => $existingRecord['id']
        ]);
    } else {
        // Insert new record with the appropriate column
        $insertStmt = $DBH->prepare("
            INSERT INTO tbl_student_results (
                name,
                registration_no,
                test1,
                test2,
                project,
                exam,
                mock,
                total,
                class,
                subject,
                year,
                term,
                created_at,
                updated_at
            ) VALUES (
                :name,
                :reg_no,
                :test1,
                :test2,
                :project,
                :exam,
                :mock,
                :total,
                :class,
                :subject,
                :year,
                :term,
                NOW(),
                NOW()
            )
        ");
        
        // Set all columns to 0 initially, then set the target column to the score
        $test1 = ($targetColumn == 'test1') ? $termResultScore : 0;
        $test2 = ($targetColumn == 'test2') ? $termResultScore : 0;
        $project = ($targetColumn == 'project') ? $termResultScore : 0;
        $exam = ($targetColumn == 'exam') ? $termResultScore : 0;
        $mock = ($targetColumn == 'mock') ? $termResultScore : 0;
        $total = $termResultScore;
        
        $insertStmt->execute([
            'name' => $studentName,
            'reg_no' => $student['registration_no'],
            'test1' => $test1,
            'test2' => $test2,
            'project' => $project,
            'exam' => $exam,
            'mock' => $mock,
            '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();
    
    // 7. Clear exam session and set results session
    unset($_SESSION['current_exam']);
    $_SESSION['exam_completed'] = [
        'score' => $score,
        'term_score' => $termResultScore,
        'correct' => $correctAnswers,
        'total' => $totalQuestions,
        'subject' => $exam_info['subject_name'],
        'class' => $exam_info['class'],
        'term' => $_SESSION['exam_info']['term'],
        'session' => $_SESSION['exam_info']['session'],
        'assessment_type' => $assessmentType,
        'contribution' => $contributionPercentage,
        'target_column' => $targetColumn,
        'exam_subject_id' => $exam_info['exam_subject_id']
    ];

    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());
}