<?php
// Include the database connection
include("includes/db.php");

if (isset($_POST['exam_id1'], $_POST['class_id'], $_POST['student_id'], $_POST['marks'])) {
    // Extract variables
    $examId = $_POST['exam_id1'];
    $classId = $_POST['class_id'];
    $studentId = $_POST['student_id'];

    // Start a transaction to ensure that all data is inserted together
    $conn->begin_transaction();

    $subjectDetails = []; // Array to store subject details (subject_code, subject_name)

    try {
        // Loop through the marks array and insert/update for each subject
        foreach ($_POST['marks'] as $subjectCode => $marksData) {
            // Theory marks (assuming you're saving the 'theory' field for each subject)
            $theoryMarks = isset($marksData['theory']) ? $marksData['theory'] : 0;

            // Fetch the subject_id, subject_name, and full marks for the subject
            $subjectQuery = "SELECT id, theory_full_marks, subject_name FROM subjects WHERE subject_code = ?";
            $stmtSubject = $conn->prepare($subjectQuery);
            $stmtSubject->bind_param("s", $subjectCode);  // Bind the subject_code
            $stmtSubject->execute();
            $subjectResult = $stmtSubject->get_result();

            if ($subjectResult->num_rows > 0) {
                // Get the subject_id, theory_full_marks, and subject_name from the result
                $subjectData = $subjectResult->fetch_assoc();
                $subjectId = $subjectData['id'];  // subject_id from subjects table
                $theoryFullMarks = $subjectData['theory_full_marks']; 
                $subjectName = $subjectData['subject_name']; // subject_name

                // Store subject code and name in the subjectDetails array for the response
                $subjectDetails[] = "$subjectCode - $subjectName";
            } else {
                // If subject does not exist in subjects table, skip or handle error
                throw new Exception("Subject with code $subjectCode not found in the subjects table.");
            }

            // Validate the entered marks do not exceed the full marks
            if ($theoryMarks > $theoryFullMarks) {
                throw new Exception("Entered marks for $subjectCode ($subjectName) exceed the full marks ($theoryFullMarks).");
            }

            // Check if marks already exist for this student, subject, and exam
            $query = "SELECT id FROM marks WHERE student_id = ? AND class_id = ? AND exam_type = ? AND subject_id = ?";
            $stmt = $conn->prepare($query);
            $stmt->bind_param("iiis", $studentId, $classId, $examId, $subjectId);  // Use subject_id here
            $stmt->execute();
            $result = $stmt->get_result();

            if ($result->num_rows > 0) {
                // Update existing record
                $updateQuery = "UPDATE marks SET marks = ?, full_marks = ? WHERE student_id = ? AND class_id = ? AND exam_type = ? AND subject_id = ?";
                $stmtUpdate = $conn->prepare($updateQuery);
                $stmtUpdate->bind_param("iiisii", $theoryMarks, $theoryFullMarks, $studentId, $classId, $examId, $subjectId);
                $stmtUpdate->execute();
            } else {
                // Insert new record
                $insertQuery = "INSERT INTO marks (student_id, class_id, exam_type, subject_id, full_marks, marks) VALUES (?, ?, ?, ?, ?, ?)";
                $stmtInsert = $conn->prepare($insertQuery);
                $stmtInsert->bind_param("iiisii", $studentId, $classId, $examId, $subjectId, $theoryFullMarks, $theoryMarks);
                $stmtInsert->execute();
            }

            // Check for any errors
            if ($stmt->error) {
                throw new Exception("Error processing marks for subject $subjectCode: " . $stmt->error);
            }
        }

        // Commit the transaction
        $conn->commit();

        // Success response with subject names and codes
        $subjectNames = implode(', ', $subjectDetails);  // Join subject names and codes
        echo json_encode(["status" => "success", "message" => "Marks submitted successfully for: $subjectNames."]);

    } catch (Exception $e) {
        // Rollback the transaction if there's an error
        $conn->rollback();

        // Send an error response with subject names
        $subjectNames = implode(', ', $subjectDetails);  // Join subject names and codes
        echo json_encode(["status" => "error", "message" => "Error: " . $e->getMessage() . " for subjects: $subjectNames."]);
    }
} else {
    // Missing necessary data
    echo json_encode(["status" => "error", "message" => "Invalid data received or missing form fields."]);
}

$conn->close();
?>
