<?php
// school_step7_save.php
// Start session
require_once 'session_config_school.php';
require_once 'connect.php';

// Debug - log what's being received
error_log("School Step7 POST data received: " . print_r($_POST, true));

// Check if this is a POST request
if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    $_SESSION['error_message'] = "Invalid request method. Please use the form to submit data.";
    header("Location: school_step7.php");
    exit();
}

// Check if form was submitted with the expected button
if (!isset($_POST['school_board_results_submit'])) {
    $_SESSION['error_message'] = "Invalid form submission. Please use the Save button.";
    header("Location: school_step7.php");
    exit();
}

try {
    // Get school information from session
    $emis_code = $_SESSION['emis_code'] ?? '';
    $school_name = $_SESSION['school_name'] ?? '';

    if (empty($emis_code)) {
        throw new Exception('School EMIS code not found in session. Please restart the registration process.');
    }

    // Debug: Log the structure of POST data
    error_log("Processing board results for EMIS: $emis_code");
    
    // Initialize arrays to store processed data
    $all_entries = [];
    $validation_errors = [];
    
    // Process each session from the form data
    // First, identify all session years from the class_name array
    if (isset($_POST['class_name']) && is_array($_POST['class_name'])) {
        foreach ($_POST['class_name'] as $session_year => $class_names) {
            error_log("Processing session: $session_year with " . count($class_names) . " entries");
            
            foreach ($class_names as $index => $class_name) {
                // Skip empty class names
                if (empty($class_name)) {
                    continue;
                }
                
                // Get values for this entry
                $arts_total = isset($_POST['arts_total'][$session_year][$index]) ? (int)$_POST['arts_total'][$session_year][$index] : 0;
                $arts_pass = isset($_POST['arts_pass'][$session_year][$index]) ? (int)$_POST['arts_pass'][$session_year][$index] : 0;
                $science_total = isset($_POST['science_total'][$session_year][$index]) ? (int)$_POST['science_total'][$session_year][$index] : 0;
                $science_pass = isset($_POST['science_pass'][$session_year][$index]) ? (int)$_POST['science_pass'][$session_year][$index] : 0;
                
                // Validate that pass does not exceed total
                if ($arts_pass > $arts_total) {
                    $validation_errors[] = "Session $session_year, Class " . getClassNameDisplay($class_name) . ": Arts Pass ($arts_pass) cannot exceed Arts Total ($arts_total)";
                }
                
                if ($science_pass > $science_total) {
                    $validation_errors[] = "Session $session_year, Class " . getClassNameDisplay($class_name) . ": Science Pass ($science_pass) cannot exceed Science Total ($science_total)";
                }
                
                // Validate that at least one student exists
                if ($arts_total === 0 && $science_total === 0) {
                    $validation_errors[] = "Session $session_year, Class " . getClassNameDisplay($class_name) . ": At least one student (Arts or Science) is required";
                }
                
                // Calculate derived values
                $arts_fail = $arts_total - $arts_pass;
                $science_fail = $science_total - $science_pass;
                $total_students = $arts_total + $science_total;
                $total_pass = $arts_pass + $science_pass;
                $total_fail = $total_students - $total_pass;
                
                $arts_percent = ($arts_total > 0) ? round(($arts_pass / $arts_total) * 100, 2) : 0;
                $science_percent = ($science_total > 0) ? round(($science_pass / $science_total) * 100, 2) : 0;
                $total_percent = ($total_students > 0) ? round(($total_pass / $total_students) * 100, 2) : 0;
                
                // Add to valid entries
                $all_entries[] = [
                    'session_year' => $session_year,
                    'class_name' => $class_name,
                    'arts_students' => $arts_total,
                    'arts_pass' => $arts_pass,
                    'arts_fail' => $arts_fail,
                    'arts_percent' => $arts_percent,
                    'science_students' => $science_total,
                    'science_pass' => $science_pass,
                    'science_fail' => $science_fail,
                    'science_percent' => $science_percent,
                    'total_students' => $total_students,
                    'total_pass' => $total_pass,
                    'total_fail' => $total_fail,
                    'total_percent' => $total_percent
                ];
                
                error_log("Found entry: Session $session_year, Class $class_name, Arts: $arts_total, Science: $science_total");
            }
        }
    } else {
        error_log("No class_name array found in POST data");
        // Debug: Show what's in POST
        error_log("POST keys: " . implode(", ", array_keys($_POST)));
    }
    
    // Check for validation errors
    if (!empty($validation_errors)) {
        throw new Exception("Validation errors:\n- " . implode("\n- ", $validation_errors));
    }
    
    // Check if we have any valid entries
    if (empty($all_entries)) {
        // Try alternative format - maybe the data is structured differently
        error_log("No entries found in expected format, checking alternative format...");
        
        // Look for any array in POST that might contain data
        foreach ($_POST as $key => $value) {
            if (is_array($value) && !empty($value)) {
                error_log("Found array in POST: $key = " . print_r($value, true));
            }
        }
        
        throw new Exception('No valid board result entries found. Please add at least one class with student data.');
    }
    
    // Begin transaction
    $conn->begin_transaction();
    
    // Delete all existing board results for this school
    $delete_query = "DELETE FROM school_board_results WHERE emis_code = ?";
    $delete_stmt = $conn->prepare($delete_query);
    $delete_stmt->bind_param("s", $emis_code);
    $delete_stmt->execute();
    
    $deleted_count = $delete_stmt->affected_rows;
    error_log("Deleted $deleted_count existing board results for EMIS: $emis_code");
    
    // Prepare insert statement for board results
    $insert_query = "
        INSERT INTO school_board_results (
            emis_code,
            session_year,
            class_name,
            arts_students,
            arts_pass,
            arts_fail,
            arts_percent,
            science_students,
            science_pass,
            science_fail,
            science_percent,
            total_students,
            total_pass,
            total_fail,
            total_percent
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ";
    
    $insert_stmt = $conn->prepare($insert_query);
    
    if (!$insert_stmt) {
        throw new Exception("Failed to prepare insert statement: " . $conn->error);
    }
    
    // Insert each valid entry
    $inserted_count = 0;
    $total_students_all = 0;
    $total_passed_all = 0;
    
    foreach ($all_entries as $entry) {
        $insert_stmt->bind_param(
            "sssiiidiiidiiid",
            $emis_code,
            $entry['session_year'],
            $entry['class_name'],
            $entry['arts_students'],
            $entry['arts_pass'],
            $entry['arts_fail'],
            $entry['arts_percent'],
            $entry['science_students'],
            $entry['science_pass'],
            $entry['science_fail'],
            $entry['science_percent'],
            $entry['total_students'],
            $entry['total_pass'],
            $entry['total_fail'],
            $entry['total_percent']
        );
        
        if (!$insert_stmt->execute()) {
            // Check for duplicate key error (MySQL error code 1062)
            if ($conn->errno == 1062) {
                throw new Exception("Duplicate entry: Class " . getClassNameDisplay($entry['class_name']) . " in session {$entry['session_year']} already exists.");
            } else {
                throw new Exception("Failed to insert record for Class " . getClassNameDisplay($entry['class_name']) . ": " . $insert_stmt->error);
            }
        }
        
        $inserted_count++;
        $total_students_all += $entry['total_students'];
        $total_passed_all += $entry['total_pass'];
        
        error_log("Inserted board result: Session {$entry['session_year']}, Class {$entry['class_name']}");
    }
    
    // Commit transaction
    $conn->commit();
    
    // Calculate overall percentage
    $overall_percentage = ($total_students_all > 0) ? round(($total_passed_all / $total_students_all) * 100, 2) : 0;
    
    // Get unique session count
    $unique_sessions = [];
    foreach ($all_entries as $entry) {
        $unique_sessions[$entry['session_year']] = true;
    }
    $session_count = count($unique_sessions);
    
    // Store summary in session for potential use in next steps
    $_SESSION['board_results_summary'] = [
        'total_entries' => $inserted_count,
        'total_students' => $total_students_all,
        'total_passed' => $total_passed_all,
        'overall_percentage' => $overall_percentage
    ];
    
    error_log("Transaction committed successfully. Inserted $inserted_count records across $session_count sessions.");

    // Success message
    $entry_text = ($inserted_count == 1) ? 'entry' : 'entries';
    $session_text = ($session_count == 1) ? 'session' : 'sessions';
    $_SESSION['success_message'] = "Board results saved successfully for " . htmlspecialchars($school_name) . ". Saved $inserted_count result $entry_text across $session_count $session_text.";
    
    // Clear any stored form data
    unset($_SESSION['form_data']);
    
    // Redirect to next step
    header("Location: school_step8.php");
    exit();

} catch (Exception $e) {
    // Rollback on error
    if (isset($conn) && $conn->ping()) {
        $conn->rollback();
        error_log("Transaction rolled back due to error");
    }
    
    // Store form data and error for repopulation
    $_SESSION['form_data'] = $_POST;
    $_SESSION['error_message'] = "Error saving board results: " . $e->getMessage();
    
    // Log the full error for debugging
    error_log("School Step7 Save Error: " . $e->getMessage());
    error_log("Stack trace: " . $e->getTraceAsString());
    
    // Redirect back to step 7
    header("Location: school_step7.php");
    exit();
}

/**
 * Helper function to get class display name
 */
function getClassNameDisplay($class_value) {
    $class_names = [
        '5' => '5th',
        '8' => '8th',
        '9' => '9th',
        '10' => '10th',
        '11' => 'First Year',
        '12' => 'Second Year'
    ];
    return $class_names[$class_value] ?? $class_value;
}
?>