<?php
session_start();

// Suppress all error output to ensure clean JSON response
error_reporting(E_ALL);
ini_set('display_errors', 0);
ini_set('log_errors', 1);

require_once 'config.php';

// Set content type to JSON
header('Content-Type: application/json');

// Error handler to catch any PHP errors and return JSON
set_error_handler(function($errno, $errstr, $errfile, $errline) {
    header('Content-Type: application/json');
    echo json_encode([
        'success' => false, 
        'message' => 'Server error: ' . $errstr,
        'debug' => "Error in $errfile on line $errline"
    ]);
    exit;
});

// Function to safely output JSON and exit
function safe_json_exit($data) {
    echo json_encode($data);
    exit;
}

// Check if user is logged in and is a teacher
if (!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true || $_SESSION["role"] !== 2) {
    safe_json_exit(['success' => false, 'message' => 'Unauthorized access']);
}

// Helper function to get subject details
function get_subject_details($subject_id, $stream_id, $school_id, $exam_id) {
    global $conn;
    $sql = "SELECT es.has_papers FROM exam_subjects es 
            WHERE es.exam_id = ? AND es.subject_id = ? AND es.stream_id = ? AND es.school_id = ?";
    if ($stmt = @$conn->prepare($sql)) {
        $stmt->bind_param("iiii", $exam_id, $subject_id, $stream_id, $school_id);
        if ($stmt->execute()) {
            $result = $stmt->get_result();
            $row = $result->fetch_assoc();
            $stmt->close();
            return $row;
        }
        $stmt->close();
    }
    return false;
}

// Helper function to find student by admission number
function find_student_by_admission_number($admission_number, $school_id) {
    global $conn;
    $sql = "SELECT u.id FROM users u WHERE u.admission_number = ? AND u.school_id = ? AND u.role = 3";
    if ($stmt = @$conn->prepare($sql)) {
        $stmt->bind_param("si", $admission_number, $school_id);
        if ($stmt->execute()) {
            $result = $stmt->get_result();
            $row = $result->fetch_assoc();
            $stmt->close();
            return $row ? $row['id'] : null;
        }
        $stmt->close();
    }
    return null;
}

// Helper function to find student by name
function find_student_by_name($student_name, $stream_id, $school_id) {
    global $conn;
    $sql = "SELECT u.id FROM users u 
            JOIN student_stream ss ON u.id = ss.student_id 
            WHERE u.name = ? AND ss.stream_id = ? AND u.school_id = ? AND u.role = 3";
    if ($stmt = @$conn->prepare($sql)) {
        $stmt->bind_param("sii", $student_name, $stream_id, $school_id);
        if ($stmt->execute()) {
            $result = $stmt->get_result();
            $row = $result->fetch_assoc();
            $stmt->close();
            return $row ? $row['id'] : null;
        }
        $stmt->close();
    }
    return null;
}

// Helper function to get paper ID
function get_paper_id($subject_id, $paper_name) {
    global $conn;
    $sql = "SELECT paper_id FROM subject_papers WHERE subject_id = ? AND paper_name = ?";
    if ($stmt = @$conn->prepare($sql)) {
        $stmt->bind_param("is", $subject_id, $paper_name);
        if ($stmt->execute()) {
            $result = $stmt->get_result();
            $row = $result->fetch_assoc();
            $stmt->close();
            return $row ? $row['paper_id'] : null;
        }
        $stmt->close();
    }
    return null;
}

// Helper function to save a single mark
function save_single_mark($student_id, $exam_id, $subject_id, $stream_id, $mark, $paper_id, $school_id, $teacher_user_id) {
    global $conn;
    
    try {
        // Check if mark already exists
        if ($paper_id) {
            $sql_check = "SELECT mark_id FROM marks WHERE student_id = ? AND exam_id = ? AND subject_id = ? AND stream_id = ? AND paper_id = ?";
            if ($stmt_check = @$conn->prepare($sql_check)) {
                $stmt_check->bind_param("iiiii", $student_id, $exam_id, $subject_id, $stream_id, $paper_id);
                if ($stmt_check->execute()) {
                    $result_check = $stmt_check->get_result();
                    $existing_mark = $result_check->fetch_assoc();
                    $stmt_check->close();
                }
            }
        } else {
            $sql_check = "SELECT mark_id FROM marks WHERE student_id = ? AND exam_id = ? AND subject_id = ? AND stream_id = ? AND (paper_id IS NULL OR paper_id = 0)";
            if ($stmt_check = @$conn->prepare($sql_check)) {
                $stmt_check->bind_param("iiii", $student_id, $exam_id, $subject_id, $stream_id);
                if ($stmt_check->execute()) {
                    $result_check = $stmt_check->get_result();
                    $existing_mark = $result_check->fetch_assoc();
                    $stmt_check->close();
                }
            }
        }
        
        if (isset($existing_mark) && $existing_mark) {
            // Update existing mark
            if ($paper_id) {
                $sql_update = "UPDATE marks SET mark = ?, date_recorded = NOW() WHERE mark_id = ?";
                if ($stmt_update = @$conn->prepare($sql_update)) {
                    $stmt_update->bind_param("di", $mark, $existing_mark['mark_id']);
                    $result = $stmt_update->execute();
                    $stmt_update->close();
                    return $result;
                }
            } else {
                $sql_update = "UPDATE marks SET mark = ?, date_recorded = NOW() WHERE mark_id = ?";
                if ($stmt_update = @$conn->prepare($sql_update)) {
                    $stmt_update->bind_param("di", $mark, $existing_mark['mark_id']);
                    $result = $stmt_update->execute();
                    $stmt_update->close();
                    return $result;
                }
            }
        } else {
            // Get exam details
            $sql_exam = "SELECT year, term FROM exams WHERE exam_id = ? AND school_id = ?";
            if ($stmt_exam = @$conn->prepare($sql_exam)) {
                $stmt_exam->bind_param("ii", $exam_id, $school_id);
                if ($stmt_exam->execute()) {
                    $result_exam = $stmt_exam->get_result();
                    $exam_data = $result_exam->fetch_assoc();
                    $stmt_exam->close();
                    
                    $academic_year = $exam_data['year'] ?? '';
                    $term = $exam_data['term'] ?? '';
                    
                    // Insert new mark (or update if exists)
                    if ($paper_id) {
                        $sql_insert = "INSERT INTO marks (student_id, exam_id, subject_id, stream_id, mark, paper_id, teacher_user_id, academic_year, term, school_id, date_recorded) 
                                      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW()) 
                                      ON DUPLICATE KEY UPDATE 
                                      mark = VALUES(mark), 
                                      teacher_user_id = VALUES(teacher_user_id),
                                      date_recorded = VALUES(date_recorded)";
                        if ($stmt_insert = @$conn->prepare($sql_insert)) {
                            $stmt_insert->bind_param("iiiiiiiiss", $student_id, $exam_id, $subject_id, $stream_id, $mark, $paper_id, $teacher_user_id, $academic_year, $term, $school_id);
                            $result = $stmt_insert->execute();
                            $stmt_insert->close();
                            return $result;
                        }
                    } else {
                        $sql_insert = "INSERT INTO marks (student_id, exam_id, subject_id, stream_id, mark, teacher_user_id, academic_year, term, school_id, date_recorded) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())";
                        if ($stmt_insert = @$conn->prepare($sql_insert)) {
                            $stmt_insert->bind_param("iiiidissi", $student_id, $exam_id, $subject_id, $stream_id, $mark, $teacher_user_id, $academic_year, $term, $school_id);
                            $result = $stmt_insert->execute();
                            $stmt_insert->close();
                            return $result;
                        }
                    }
                }
                $stmt_exam->close();
            }
        }
        return false;
    } catch (Exception $e) {
        error_log("Error saving single mark: " . $e->getMessage());
        return false;
    }
}

// Check if this is an Excel upload request
if (isset($_POST['action']) && $_POST['action'] === 'excel_upload' && isset($_FILES['excel_file'])) {
    // Handle Excel upload
    handle_excel_upload();
    exit;
}

// Check if required POST data exists
if (!isset($_POST['exam_id']) || !isset($_POST['subject_id']) || !isset($_POST['stream_id']) || !isset($_POST['school_id']) || !isset($_POST['marks'])) {
    safe_json_exit(['success' => false, 'message' => 'Missing required data']);
}

// Function to handle Excel file upload
function handle_excel_upload() {
    global $conn, $teacher_user_id, $current_user_school_id;
    
    try {
        // Check if PhpSpreadsheet is available
        if (!class_exists('PhpOffice\\PhpSpreadsheet\\IOFactory')) {
            require_once 'vendor/autoload.php';
        }
        
        $exam_id = intval($_POST['exam_id']);
        $subject_id = intval($_POST['subject_id']);
        $stream_id = intval($_POST['stream_id']);
        $school_id = intval($_POST['school_id']);
        
        // Verify school_id matches session
        if ($school_id !== $current_user_school_id) {
            safe_json_exit(['success' => false, 'message' => 'School ID mismatch']);
        }
        
        // Validate uploaded file
        if (!isset($_FILES['excel_file']) || $_FILES['excel_file']['error'] !== UPLOAD_ERR_OK) {
            safe_json_exit(['success' => false, 'message' => 'No file uploaded or upload error']);
        }
        
        $file = $_FILES['excel_file'];
        
        // Check file type
        $allowed_extensions = ['xlsx', 'xls'];
        $file_extension = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
        
        if (!in_array($file_extension, $allowed_extensions)) {
            safe_json_exit(['success' => false, 'message' => 'Only Excel files (.xlsx, .xls) are allowed']);
        }
        
        // Check file size (max 5MB)
        if ($file['size'] > 5 * 1024 * 1024) {
            safe_json_exit(['success' => false, 'message' => 'File size must be less than 5MB']);
        }
        
        // Get subject and stream details
        $subject_details = get_subject_details($subject_id, $stream_id, $school_id, $exam_id);
        if (!$subject_details) {
            safe_json_exit(['success' => false, 'message' => 'Subject not found or not assigned']);
        }
        
        $has_papers = $subject_details['has_papers'];
        
        // Process Excel file
        $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file['tmp_name']);
        $worksheet = $spreadsheet->getActiveSheet();
        $data = $worksheet->toArray();
        
        if (empty($data) || count($data) < 2) {
            safe_json_exit(['success' => false, 'message' => 'Excel file must contain at least a header row and one data row']);
        }
        
        // Validate header row
        $header_row = array_map('trim', $data[0]);
        $expected_headers = ['Student Name', 'Admission Number', 'Mark'];
        if ($has_papers) {
            $expected_headers[] = 'Paper';
        }
        
        foreach ($expected_headers as $expected) {
            if (!in_array($expected, $header_row)) {
                safe_json_exit(['success' => false, 'message' => 'Invalid header row. Expected columns: ' . implode(', ', $expected_headers)]);
            }
        }
        
        // Process data rows
        $processed_data = [];
        $errors = [];
        
        for ($i = 1; $i < count($data); $i++) {
            $row = $data[$i];
            
            // Skip empty rows
            if (array_filter($row) === []) continue;
            
            $student_name = trim($row[0] ?? '');
            $admission_number = trim($row[1] ?? '');
            $mark = floatval($row[2] ?? 0);
            $paper_name = trim($row[3] ?? '');
            
            if (empty($student_name) && empty($admission_number)) {
                $errors[] = "Row " . ($i + 1) . ": Student name or admission number is required";
                continue;
            }
            
            if ($mark < 0 || $mark > 100) {
                $errors[] = "Row " . ($i + 1) . ": Invalid mark (must be 0-100): " . $mark;
                continue;
            }
            
            // Find student in database
            $student_id = null;
            $paper_id = null;
            
            if (!empty($admission_number)) {
                $student_id = find_student_by_admission_number($admission_number, $school_id);
            }
            
            if (!$student_id && !empty($student_name)) {
                $student_id = find_student_by_name($student_name, $stream_id, $school_id);
            }
            
            if (!$student_id) {
                $errors[] = "Row " . ($i + 1) . ": Student not found in database";
                continue;
            }
            
            // Get paper_id if subject has papers
            if ($has_papers && !empty($paper_name)) {
                $paper_id = get_paper_id($subject_id, $paper_name);
                if (!$paper_id) {
                    $errors[] = "Row " . ($i + 1) . ": Paper not found: " . $paper_name;
                    continue;
                }
            }
            
            $processed_data[] = [
                'student_id' => $student_id,
                'mark' => $mark,
                'paper_id' => $paper_id,
                'row_number' => $i + 1
            ];
        }
        
        if (!empty($errors)) {
            safe_json_exit(['success' => false, 'message' => 'Data validation failed', 'errors' => $errors]);
        }
        
        if (empty($processed_data)) {
            safe_json_exit(['success' => false, 'message' => 'No valid data found in Excel file']);
        }
        
        // Begin transaction
        $conn->autocommit(FALSE);
        $saved_count = 0;
        
        // Save marks
        foreach ($processed_data as $data_item) {
            $student_id = $data_item['student_id'];
            $mark = $data_item['mark'];
            $paper_id = $data_item['paper_id'];
            
            if (save_single_mark($student_id, $exam_id, $subject_id, $stream_id, $mark, $paper_id, $school_id, $current_user_school_id)) {
                $saved_count++;
            }
        }
        
        // Update subject status
        if ($saved_count > 0) {
            $sql_status = "UPDATE exam_subjects SET status = 'Captured' WHERE exam_id = ? AND subject_id = ?";
            if ($stmt_status = @$conn->prepare($sql_status)) {
                $stmt_status->bind_param("ii", $exam_id, $subject_id);
                $stmt_status->execute();
                $stmt_status->close();
            }
        }
        
        // Commit transaction
        $conn->commit();
        $conn->autocommit(TRUE);
        
        safe_json_exit([
            'success' => true,
            'message' => "Successfully processed {$saved_count} marks from Excel file",
            'saved_count' => $saved_count,
            'total_processed' => count($processed_data)
        ]);
        
    } catch (Exception $e) {
        // Rollback transaction
        $conn->rollback();
        $conn->autocommit(TRUE);
        
        safe_json_exit([
            'success' => false,
            'message' => 'Error processing Excel file: ' . $e->getMessage()
        ]);
    }
}

$exam_id = intval($_POST['exam_id']);
$subject_id = intval($_POST['subject_id']);
$stream_id = intval($_POST['stream_id']);
$school_id = intval($_POST['school_id']);

// Get academic_year and term from POST or fallback to exam table
$academic_year = $_POST['academic_year'] ?? '';
$term = $_POST['term'] ?? '';
$paper_id = intval($_POST['paper_id'] ?? 0);

// VALIDATION: Check exam_id (required)
if ($exam_id <= 0) {
    safe_json_exit([
        'success' => false, 
        'message' => 'Invalid or missing exam_id - this is required',
        'debug_info' => [
            'received_exam_id' => $_POST['exam_id'] ?? 'MISSING',
            'validation_failed_at' => 'exam_id_check'
        ]
    ]);
}

// Note: academic_year and term can be empty - we have fallback logic below
// Debug removed for clean JSON output
// if (empty($academic_year)) {
//     echo "<!-- DEBUG: academic_year is empty, will try to get from exam table -->" . PHP_EOL;
// }
// if (empty($term)) {
//     echo "<!-- DEBUG: term is empty, will try to get from exam table -->" . PHP_EOL;
// }

// echo "<!-- DEBUG: Received exam_id=$exam_id, academic_year='$academic_year', term='$term' -->" . PHP_EOL;

// Get teacher ID from session
$teacher_user_id = $_SESSION["id"] ?? $_SESSION["user_id"] ?? 0;
$current_user_school_id = $_SESSION["school_id"] ?? 2;

// Verify school_id matches session
if ($school_id !== $current_user_school_id) {
    safe_json_exit(['success' => false, 'message' => 'School ID mismatch']);
}

// Decode marks data
$marks = json_decode($_POST['marks'], true);
if (!is_array($marks)) {
    safe_json_exit(['success' => false, 'message' => 'Invalid marks data']);
}

try {
    // Begin transaction
    $conn->autocommit(FALSE);
    $saved_count = 0;
    
    foreach ($marks as $mark_data) {
        $student_id = intval($mark_data['student_id']);
        $mark = floatval($mark_data['mark']);
        $paper_id = isset($mark_data['paper_id']) && $mark_data['paper_id'] !== '' ? intval($mark_data['paper_id']) : null;
        
        // Validate paper_id if provided
        if ($paper_id) {
            $sql_paper_check = "SELECT paper_id FROM subject_papers WHERE paper_id = ? AND subject_id = ?";
            if ($stmt_paper_check = @$conn->prepare($sql_paper_check)) {
                $stmt_paper_check->bind_param("ii", $paper_id, $subject_id);
                if ($stmt_paper_check->execute()) {
                    $result_paper_check = $stmt_paper_check->get_result();
                    if ($result_paper_check->num_rows == 0) {
                        $stmt_paper_check->close();
                        throw new Exception('Paper ID ' . $paper_id . ' not found for subject ' . $subject_id);
                    }
                }
                $stmt_paper_check->close();
            }
        }
        
        // Validate mark range
        if ($mark < 0 || $mark > 100) {
            throw new Exception('Invalid mark range: ' . $mark);
        }
        
        // Verify student exists and belongs to this school with role 3
        $sql_verify_student = "SELECT u.id FROM users u 
                              WHERE u.id = ? AND u.school_id = ? AND u.role = 3";
        if ($stmt_verify_student = @$conn->prepare($sql_verify_student)) {
            $stmt_verify_student->bind_param("ii", $student_id, $school_id);
            if ($stmt_verify_student->execute()) {
                $result_verify = $stmt_verify_student->get_result();
                if ($result_verify->num_rows == 0) {
                    $stmt_verify_student->close();
                    throw new Exception('Student not found in this school');
                }
            }
            $stmt_verify_student->close();
        } else {
            throw new Exception('Database error in student verification');
        }
        
        // Check if mark already exists (considering paper_id)
        if ($paper_id) {
            $sql_check = "SELECT mark_id FROM marks 
                         WHERE student_id = ? AND exam_id = ? AND subject_id = ? AND stream_id = ? AND paper_id = ?";
            if ($stmt_check = @$conn->prepare($sql_check)) {
                $stmt_check->bind_param("iiiii", $student_id, $exam_id, $subject_id, $stream_id, $paper_id);
                if ($stmt_check->execute()) {
                    $result_check = $stmt_check->get_result();
                    $existing_mark = $result_check->fetch_assoc();
                    $stmt_check->close();
                } else {
                    throw new Exception('Failed to execute mark check query: ' . $conn->error);
                }
            } else {
                throw new Exception('Failed to prepare mark check query: ' . $conn->error);
            }
        } else {
            $sql_check = "SELECT mark_id FROM marks 
                         WHERE student_id = ? AND exam_id = ? AND subject_id = ? AND stream_id = ? AND (paper_id IS NULL OR paper_id = 0)";
            if ($stmt_check = @$conn->prepare($sql_check)) {
                $stmt_check->bind_param("iiii", $student_id, $exam_id, $subject_id, $stream_id);
                if ($stmt_check->execute()) {
                    $result_check = $stmt_check->get_result();
                    $existing_mark = $result_check->fetch_assoc();
                    $stmt_check->close();
                } else {
                    throw new Exception('Failed to execute mark check query: ' . $conn->error);
                }
            } else {
                throw new Exception('Failed to prepare mark check query: ' . $conn->error);
            }
        }
        
        if (isset($existing_mark) && $existing_mark) {
            // Update existing mark
            if ($paper_id) {
                $sql_update = "UPDATE marks SET mark = ?, date_recorded = NOW() 
                              WHERE mark_id = ?";
                if ($stmt_update = @$conn->prepare($sql_update)) {
                    $stmt_update->bind_param("di", $mark, $existing_mark['mark_id']);
                    if ($stmt_update->execute()) {
                        $saved_count++;
                    }
                    $stmt_update->close();
                }
            } else {
                $sql_update = "UPDATE marks SET mark = ?, date_recorded = NOW() 
                              WHERE mark_id = ?";
                if ($stmt_update = @$conn->prepare($sql_update)) {
                    $stmt_update->bind_param("di", $mark, $existing_mark['mark_id']);
                    if ($stmt_update->execute()) {
                        $saved_count++;
                    }
                    $stmt_update->close();
                }
                $stmt_update->close();
            }
        } else {
            // Get exam details for academic year and term
            $academic_year = '';
            $term = '';
            $sql_exam = "SELECT year, term FROM exams WHERE exam_id = ? AND school_id = ?";
            if ($stmt_exam = @$conn->prepare($sql_exam)) {
                $stmt_exam->bind_param("ii", $exam_id, $school_id);
                if ($stmt_exam->execute()) {
                    $result_exam = $stmt_exam->get_result();
                    $exam_data = $result_exam->fetch_assoc();
                    if ($exam_data) {
                        $academic_year = $exam_data['year'];
                        $term = $exam_data['term'];
                    }
                }
                $stmt_exam->close();
            }
            
            // Insert new mark (or update if exists)
            if ($paper_id) {
                $sql_insert = "INSERT INTO marks (student_id, exam_id, subject_id, stream_id, mark, paper_id, teacher_user_id, academic_year, term, school_id, date_recorded) 
                              VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW()) 
                              ON DUPLICATE KEY UPDATE 
                              mark = VALUES(mark), 
                              teacher_user_id = VALUES(teacher_user_id),
                              date_recorded = VALUES(date_recorded)";
                if ($stmt_insert = @$conn->prepare($sql_insert)) {
                    $stmt_insert->bind_param("iiiiiiiiss", $student_id, $exam_id, $subject_id, $stream_id, $mark, $paper_id, $teacher_user_id, $academic_year, $term, $school_id);
                    if ($stmt_insert->execute()) {
                        $saved_count++;
                    } else {
                        $stmt_insert->close();
                        throw new Exception('Failed to execute INSERT for paper mark: ' . $conn->error);
                    }
                    $stmt_insert->close();
                } else {
                    throw new Exception('Failed to prepare INSERT for paper mark: ' . $conn->error);
                }
            } else {
                $sql_insert = "INSERT INTO marks (student_id, exam_id, subject_id, stream_id, mark, teacher_user_id, academic_year, term, school_id, date_recorded) 
                              VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW()) 
                              ON DUPLICATE KEY UPDATE 
                              mark = VALUES(mark), 
                              teacher_user_id = VALUES(teacher_user_id),
                              date_recorded = VALUES(date_recorded)";
                if ($stmt_insert = @$conn->prepare($sql_insert)) {
                    $stmt_insert->bind_param("iiiidissi", $student_id, $exam_id, $subject_id, $stream_id, $mark, $teacher_user_id, $academic_year, $term, $school_id);
                    if ($stmt_insert->execute()) {
                        $saved_count++;
                    } else {
                        $stmt_insert->close();
                        throw new Exception('Failed to execute INSERT for regular mark: ' . $conn->error);
                    }
                    $stmt_insert->close();
                } else {
                    throw new Exception('Failed to prepare INSERT for regular mark: ' . $conn->error);
                }
            }
        }
    }
    
    // Update subject status to 'Captured' after saving marks
    if ($saved_count > 0) {
        $sql_status = "UPDATE exam_subjects SET status = 'Captured' WHERE exam_id = ? AND subject_id = ?";
        if ($stmt_status = @$conn->prepare($sql_status)) {
            $stmt_status->bind_param("ii", $exam_id, $subject_id);
            $stmt_status->execute();
            $stmt_status->close();
        }
    }
    
    // Commit transaction
    $conn->commit();
    $conn->autocommit(TRUE);
    
    safe_json_exit([
        'success' => true, 
        'message' => 'Marks saved/updated successfully. Status updated to Captured.', 
        'saved_count' => $saved_count
    ]);
    
} catch (Exception $e) {
    // Rollback transaction
    $conn->rollback();
    $conn->autocommit(TRUE);
    
    error_log("Error saving marks: " . $e->getMessage());
    error_log("Request data: " . json_encode($_POST));
    safe_json_exit([
        'success' => false, 
        'message' => 'Error saving marks: ' . $e->getMessage() . ' (If this is a duplicate entry error, the marks may have already been saved - try refreshing the page)',
        'debug_info' => [
            'request_data' => $_POST,
            'error_details' => $e->getMessage()
        ]
    ]);
}
?>