<?php
// Enable error reporting
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

session_start();
require_once('../public/connection.php');

$class = $_GET['class'] ?? '';
$term = $_GET['term'] ?? '';
$session_val = $_GET['session'] ?? '';
$subject_id = $_GET['subject_id'] ?? '';
$exam_type = $_GET['exam_type'] ?? '';

if (empty($class) || empty($term) || empty($session_val) || empty($subject_id) || empty($exam_type)) {
    $_SESSION['msg'] = "Class, term, session, subject, and exam type are required to migrate results.";
    $_SESSION['type'] = "danger";
    echo "<script>window.history.back();</script>";
    exit;
}

// Fetch subject name
$subject_stmt = $conn->prepare("SELECT subjects FROM school_subjects WHERE id = ?");
$subject_stmt->bind_param("i", $subject_id);
$subject_stmt->execute();
$subject_result = $subject_stmt->get_result();
$subject_row = $subject_result->fetch_assoc();
$subject = $subject_row['subjects'] ?? '';
$subject_stmt->close();

if (empty($subject)) {
    $_SESSION['msg'] = "Invalid subject selected.";
    $_SESSION['type'] = "danger";
    echo "<script>window.history.back();</script>";
    exit;
}

// Select results from exam_group_results
$query = "
    SELECT 
        CONCAT_WS(' ', s.lastname, s.firstname, s.othername) AS name,
        s.class, s.reg_number,
        r.term, r.session, r.subject, r.exam_type,
        r.total
    FROM exam_group_results r
    JOIN students s ON r.student_id = s.id
    WHERE r.class = ? AND r.term = ? AND r.session = ? AND r.subject = ? AND r.exam_type = ?
";

$stmt = $conn->prepare($query);
$stmt->bind_param("sssss", $class, $term, $session_val, $subject, $exam_type);
$stmt->execute();
$result = $stmt->get_result();

$inserted = 0;

if ($result && $result->num_rows > 0) {
    $insert_stmt = $conn->prepare("INSERT INTO results1 (name, class, term, subjects, reg_number, session, ca1, ca2, exam) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
    $insert_stmt->bind_param("ssssssddd", $name, $class_val, $term_val, $subject_val, $reg_number, $session_db, $ca1, $ca2, $exam_score);

    while ($row = $result->fetch_assoc()) {
        $name = $row['name'];
        $class_val = $row['class'];
        $term_val = $row['term'];
        $subject_val = $row['subject'];
        $reg_number = $row['reg_number'];
        $session_db = $row['session'];
        $row_exam_type = $row['exam_type'];
        $total = $row['total'];

        // Map total to the correct column based on exam_type
        $ca1 = ($row_exam_type === 'ca1') ? $total : null;
        $ca2 = ($row_exam_type === 'ca2') ? $total : null;
        $exam_score = ($row_exam_type === 'exam') ? $total : null;

        // Check for duplicate
        $check = $conn->prepare("SELECT 1 FROM results1 WHERE reg_number = ? AND class = ? AND term = ? AND session = ? AND subjects = ?");
        $check->bind_param("sssss", $reg_number, $class_val, $term_val, $session_db, $subject_val);
        $check->execute();
        $check->store_result();

        if ($check->num_rows == 0) {
            $insert_stmt->execute();
            $inserted++;
        } else {
            // Update existing record
            $update_stmt = $conn->prepare("UPDATE results1 SET ca1 = COALESCE(?, ca1), ca2 = COALESCE(?, ca2), exam = COALESCE(?, exam) WHERE reg_number = ? AND class = ? AND term = ? AND session = ? AND subjects = ?");
            $update_stmt->bind_param("dddsssss", $ca1, $ca2, $exam_score, $reg_number, $class_val, $term_val, $session_db, $subject_val);
            $update_stmt->execute();
            if ($update_stmt->affected_rows > 0) {
                $inserted++;
            }
            $update_stmt->close();
        }
        $check->close();
    }

    $insert_stmt->close();
    $stmt->close();

    $_SESSION['msg'] = $inserted > 0
        ? "$inserted result(s) successfully migrated to School Portal."
        : "No new results were migrated — they already exist in the School Portal.";
    $_SESSION['type'] = $inserted > 0 ? "success" : "warning";
} else {
    $_SESSION['msg'] = "No results found for the selected filters.";
    $_SESSION['type'] = "danger";
}
?>

<script>
  window.history.back();
</script>