<?php
session_start();
ob_start();
error_reporting(E_ALL);
ini_set('display_errors', 1);

include "../include/config.php";

// Check login and instructor role
if (!isset($_SESSION['email'])) {
    header("location:../index.php");
    exit();
}

$success = "";
$error = "";

// Handle grade submission
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['save_grade'])) {
    $submission_id = intval($_POST['submission_id']);
    $marks = floatval($_POST['marks']);
    $instructor_email = trim($_SESSION['email']);
    
    // Get submission details including student and assignment info
    $stmt = $conn->prepare("
        SELECT s.*, a.course, a.period, a.year, a.intake, a.program, 
               st.studentid, st.email as student_email
        FROM submit s
        JOIN assignments a ON s.assignment_id = a.id
        JOIN students st ON s.student_id = st.studentid
        WHERE s.id = ? AND a.email = ?
    ");
    $stmt->bind_param("is", $submission_id, $instructor_email);
    $stmt->execute();
    $result = $stmt->get_result();
    
    if ($result->num_rows > 0) {
        $submission = $result->fetch_assoc();
        
        // Update the submission with marks
        $update_stmt = $conn->prepare("UPDATE submit SET marks = ? WHERE id = ?");
        $update_stmt->bind_param("di", $marks, $submission_id);
        
        if ($update_stmt->execute()) {
            // Now update the results table - TRIM ALL VALUES
            $studentid = trim($submission['studentid']);
            $course = trim($submission['course']);
            $intake = trim($submission['intake']);
            $year = trim($submission['year']);
            $term = trim($submission['period']);
            $program = trim($submission['program']);
            
            // Escape after trimming
            $studentid_esc = $conn->real_escape_string($studentid);
            $course_esc = $conn->real_escape_string($course);
            $intake_esc = $conn->real_escape_string($intake);
            $year_esc = $conn->real_escape_string($year);
            $term_esc = $conn->real_escape_string($term);
            $program_esc = $conn->real_escape_string($program);
            
            // ✅ Update results table with CA marks
            $new_ca = $marks; // Use the marks from the form
            
            error_log("=== GRADING DEBUG ===");
            error_log("Looking for: studentid='$studentid', course='$course', term='$term', year='$year', intake='$intake'");
            
            // Check existing results - Use TRIM() in SQL queries
            // First check with exact match using TRIM
            $result_check = mysqli_query($conn, "SELECT id, ca, ca1, ca2 
                                                 FROM results 
                                                 WHERE TRIM(studentid) = '$studentid_esc' 
                                                 AND TRIM(course) = '$course_esc' 
                                                 AND TRIM(intake) = '$intake_esc' 
                                                 AND TRIM(term) = '$term_esc' 
                                                 AND TRIM(year) = '$year_esc'");
            
            error_log("Exact TRIM check found: " . mysqli_num_rows($result_check) . " rows");
            
            // If not found, check case-insensitive with LOWER
            if(mysqli_num_rows($result_check) == 0) {
                $result_check = mysqli_query($conn, "SELECT id, ca, ca1, ca2 
                                                     FROM results 
                                                     WHERE LOWER(TRIM(studentid)) = LOWER('$studentid_esc') 
                                                     AND LOWER(TRIM(course)) = LOWER('$course_esc') 
                                                     AND LOWER(TRIM(intake)) = LOWER('$intake_esc') 
                                                     AND LOWER(TRIM(term)) = LOWER('$term_esc') 
                                                     AND LOWER(TRIM(year)) = LOWER('$year_esc')");
                
                error_log("Case-insensitive check found: " . mysqli_num_rows($result_check) . " rows");
            }
            
            // If still not found, check with just student+course+term+year
            if(mysqli_num_rows($result_check) == 0) {
                $result_check = mysqli_query($conn, "SELECT id, ca, ca1, ca2 
                                                     FROM results 
                                                     WHERE TRIM(studentid) = '$studentid_esc' 
                                                     AND TRIM(course) = '$course_esc' 
                                                     AND TRIM(term) = '$term_esc' 
                                                     AND TRIM(year) = '$year_esc'");
                
                error_log("Without intake check found: " . mysqli_num_rows($result_check) . " rows");
            }
            
            if(mysqli_num_rows($result_check) > 0) {
                $row = mysqli_fetch_assoc($result_check);
                $result_id = $row['id'];
                
                error_log("Found existing row ID: $result_id, ca={$row['ca']}, ca1={$row['ca1']}, ca2={$row['ca2']}");
                
                // Check in order: ca → ca1 → ca2 (same as your exam code)
                if(empty($row['ca']) || $row['ca'] === '' || $row['ca'] === null || $row['ca'] == 0) {
                    $sql = "UPDATE results SET ca = $new_ca, email = '$instructor_email' WHERE id = $result_id";
                    error_log("Updating CA column");
                } 
                else if(empty($row['ca1']) || $row['ca1'] === '' || $row['ca1'] === null || $row['ca1'] == 0) {
                    $sql = "UPDATE results SET ca1 = $new_ca, email = '$instructor_email' WHERE id = $result_id";
                    error_log("Updating CA1 column");
                }
                else if(empty($row['ca2']) || $row['ca2'] === '' || $row['ca2'] === null || $row['ca2'] == 0) {
                    $sql = "UPDATE results SET ca2 = $new_ca, email = '$instructor_email' WHERE id = $result_id";
                    error_log("Updating CA2 column");
                }
                else {
                    $sql = "UPDATE results SET ca = $new_ca, email = '$instructor_email' WHERE id = $result_id";
                    error_log("All CA fields full, updating CA column");
                }
                
                $conn->query($sql);
                error_log("Updated existing results entry ID: $result_id for student: $studentid, course: $course");
                
            } else {
                // No existing entry - create one with ca field
                error_log("No existing entry found, creating new one");
                
                // Check if program column exists in results table
                $check_columns = mysqli_query($conn, "SHOW COLUMNS FROM results LIKE 'program'");
                
                if(mysqli_num_rows($check_columns) > 0) {
                    // Program column exists, include it
                    $sql = "INSERT INTO results 
                            (studentid, course, intake, term, year, program, ca, email) 
                            VALUES ('$studentid_esc', '$course_esc', '$intake_esc', '$term_esc', '$year_esc', '$program_esc', $new_ca, '$instructor_email')";
                } else {
                    // Program column doesn't exist
                    $sql = "INSERT INTO results 
                            (studentid, course, intake, term, year, ca, email) 
                            VALUES ('$studentid_esc', '$course_esc', '$intake_esc', '$term_esc', '$year_esc', $new_ca, '$instructor_email')";
                }
                
                $conn->query($sql);
                error_log("Created new results entry for student: $studentid, course: $course");
            }
            
            $_SESSION['success'] = "Assignment graded successfully! Marks saved to results.";
            
        } else {
            $_SESSION['error'] = "Error updating submission: " . $conn->error;
        }
        $update_stmt->close();
    } else {
        $_SESSION['error'] = "Submission not found or you don't have permission to grade it!";
    }
    
    $stmt->close();
    
    // Redirect back to assignment page
    header("Location: assignment.php#submissions");
    exit();
}

// If not a POST request, redirect
header("Location: assignment.php");
exit();
?>