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

// Debug - log what's being received
error_log("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_step6.php");
    exit();
}

// Check if we have any form data
if (empty($_POST)) {
    $_SESSION['error_message'] = "No form data received. Please try again.";
    header("Location: school_step6.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.');
    }

    // Get form data arrays
    $class_names = $_POST['Class_name'] ?? [];
    $total_male = $_POST['totalMale'] ?? [];
    $total_female = $_POST['totalFemale'] ?? [];
    $repeating_male = $_POST['repeatingMale'] ?? [];
    $repeating_female = $_POST['repeatingFemale'] ?? [];
    $orphan_male = $_POST['orphanMale'] ?? [];
    $orphan_female = $_POST['orphanFemale'] ?? [];
    $special_male = $_POST['specialMale'] ?? [];
    $special_female = $_POST['specialFemale'] ?? [];
    $scholar_male = $_POST['scholarMale'] ?? [];
    $scholar_female = $_POST['scholarFemale'] ?? [];

    // Validate that we have at least one class
    if (empty($class_names) || !is_array($class_names)) {
        throw new Exception('Please add at least one class.');
    }

    // Filter out empty class names
    $valid_classes = [];
    foreach ($class_names as $index => $class_name) {
        if (!empty(trim($class_name))) {
            $valid_classes[$index] = trim($class_name);
        }
    }

    if (empty($valid_classes)) {
        throw new Exception('Please select at least one valid class.');
    }

    // Begin transaction
    $conn->begin_transaction();

    // FIXED: Using 'class' table (not 'classes')
    $class_id_map = []; // Map class_name -> id
    
    // Get all classes from the 'class' table
    $placeholders = implode(',', array_fill(0, count($valid_classes), '?'));
    $types = str_repeat('s', count($valid_classes));
    
    // IMPORTANT: Using 'class' table with 'class_id' as the primary key
    $class_query = "SELECT class_id, class_name FROM class WHERE class_name IN ($placeholders)";
    $stmt = $conn->prepare($class_query);
    $stmt->bind_param($types, ...$valid_classes);
    $stmt->execute();
    $class_result = $stmt->get_result();
    
    if (!$class_result) {
        throw new Exception("Error fetching classes: " . $conn->error);
    }
    
    while ($row = $class_result->fetch_assoc()) {
        $class_id_map[$row['class_name']] = $row['class_id']; // Using class_id
        error_log("Mapped class '{$row['class_name']}' to ID: {$row['class_id']}");
    }
    
    // Check if all classes were found
    foreach ($valid_classes as $class_name) {
        if (!isset($class_id_map[$class_name])) {
            throw new Exception("Class '$class_name' not found in the class table.");
        }
    }

    // Check if school_students record exists
    $check_school = $conn->prepare("SELECT id FROM school_students WHERE emis_code = ?");
    $check_school->bind_param("s", $emis_code);
    $check_school->execute();
    $result = $check_school->get_result();
    
    // Initialize school totals
    $school_totals = [
        'total_male' => 0, 'total_female' => 0,
        'repeating_male' => 0, 'repeating_female' => 0,
        'orphan_male' => 0, 'orphan_female' => 0,
        'special_male' => 0, 'special_female' => 0,
        'scholar_male' => 0, 'scholar_female' => 0
    ];

    if ($result->num_rows > 0) {
        $row = $result->fetch_assoc();
        $school_students_id = $row['id'];
        
        // Delete existing summaries for this school
        $delete_summary = $conn->prepare("DELETE FROM school_students_summary WHERE ss_id = ?");
        $delete_summary->bind_param("i", $school_students_id);
        $delete_summary->execute();
        error_log("Deleted existing summaries for school_students_id: $school_students_id");
    } else {
        $school_students_id = null;
    }

    // Process each class and calculate totals
    $class_data = [];
    foreach ($valid_classes as $index => $class_name) {
        // Get values with defaults
        $t_male = isset($total_male[$index]) ? (int)$total_male[$index] : 0;
        $t_female = isset($total_female[$index]) ? (int)$total_female[$index] : 0;
        
        // Validate at least one student
        if ($t_male === 0 && $t_female === 0) {
            throw new Exception("Class '$class_name' must have at least one student.");
        }
        
        // Get the class_id from our map
        $class_id = $class_id_map[$class_name];
        
        // Get other values
        $r_male = isset($repeating_male[$index]) ? (int)$repeating_male[$index] : 0;
        $r_female = isset($repeating_female[$index]) ? (int)$repeating_female[$index] : 0;
        $o_male = isset($orphan_male[$index]) ? (int)$orphan_male[$index] : 0;
        $o_female = isset($orphan_female[$index]) ? (int)$orphan_female[$index] : 0;
        $s_male = isset($special_male[$index]) ? (int)$special_male[$index] : 0;
        $s_female = isset($special_female[$index]) ? (int)$special_female[$index] : 0;
        $sc_male = isset($scholar_male[$index]) ? (int)$scholar_male[$index] : 0;
        $sc_female = isset($scholar_female[$index]) ? (int)$scholar_female[$index] : 0;
        
        // Add to school totals
        $school_totals['total_male'] += $t_male;
        $school_totals['total_female'] += $t_female;
        $school_totals['repeating_male'] += $r_male;
        $school_totals['repeating_female'] += $r_female;
        $school_totals['orphan_male'] += $o_male;
        $school_totals['orphan_female'] += $o_female;
        $school_totals['special_male'] += $s_male;
        $school_totals['special_female'] += $s_female;
        $school_totals['scholar_male'] += $sc_male;
        $school_totals['scholar_female'] += $sc_female;
        
        // Store class data for insertion
        $class_data[] = [
            'class_id' => $class_id,
            'total_male' => $t_male,
            'total_female' => $t_female,
            'repeating_male' => $r_male,
            'repeating_female' => $r_female,
            'orphan_male' => $o_male,
            'orphan_female' => $o_female,
            'special_male' => $s_male,
            'special_female' => $s_female,
            'scholar_male' => $sc_male,
            'scholar_female' => $sc_female
        ];
        
        error_log("Prepared class data - Class: $class_name, class_id: $class_id, Male: $t_male, Female: $t_female");
    }

    // Insert or Update school_students
    if ($school_students_id) {
        // Update existing
        $update = $conn->prepare("
            UPDATE school_students SET 
                total_male = ?,
                total_female = ?,
                repeating_male = ?,
                repeating_female = ?,
                orphan_male = ?,
                orphan_female = ?,
                special_male = ?,
                special_female = ?,
                scholar_male = ?,
                scholar_female = ?,
                updated_at = CURRENT_TIMESTAMP
            WHERE id = ?
        ");
        $update->bind_param(
            "iiiiiiiiiii",
            $school_totals['total_male'],
            $school_totals['total_female'],
            $school_totals['repeating_male'],
            $school_totals['repeating_female'],
            $school_totals['orphan_male'],
            $school_totals['orphan_female'],
            $school_totals['special_male'],
            $school_totals['special_female'],
            $school_totals['scholar_male'],
            $school_totals['scholar_female'],
            $school_students_id
        );
        
        if (!$update->execute()) {
            throw new Exception("Failed to update school students record: " . $update->error);
        }
        
        error_log("Updated school_students record ID: $school_students_id");
    } else {
        // Insert new
        $insert = $conn->prepare("
            INSERT INTO school_students (
                emis_code,
                total_male,
                total_female,
                repeating_male,
                repeating_female,
                orphan_male,
                orphan_female,
                special_male,
                special_female,
                scholar_male,
                scholar_female
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");
        $insert->bind_param(
            "siiiiiiiiii",
            $emis_code,
            $school_totals['total_male'],
            $school_totals['total_female'],
            $school_totals['repeating_male'],
            $school_totals['repeating_female'],
            $school_totals['orphan_male'],
            $school_totals['orphan_female'],
            $school_totals['special_male'],
            $school_totals['special_female'],
            $school_totals['scholar_male'],
            $school_totals['scholar_female']
        );
        
        if (!$insert->execute()) {
            throw new Exception("Failed to insert school students record: " . $insert->error);
        }
        
        $school_students_id = $conn->insert_id;
        error_log("Inserted new school_students record with ID: $school_students_id");
    }

    // Insert class summaries
    if (!empty($class_data)) {
        $insert_summary = $conn->prepare("
            INSERT INTO school_students_summary (
                ss_id,
                class_id,
                total_male,
                total_female,
                repeating_male,
                repeating_female,
                orphan_male,
                orphan_female,
                special_male,
                special_female,
                scholar_male,
                scholar_female,
                created_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
        ");
        
        foreach ($class_data as $data) {
            $insert_summary->bind_param(
                "iiiiiiiiiiii",
                $school_students_id,
                $data['class_id'],
                $data['total_male'],
                $data['total_female'],
                $data['repeating_male'],
                $data['repeating_female'],
                $data['orphan_male'],
                $data['orphan_female'],
                $data['special_male'],
                $data['special_female'],
                $data['scholar_male'],
                $data['scholar_female']
            );
            
            if (!$insert_summary->execute()) {
                throw new Exception("Failed to insert class summary for class_id {$data['class_id']}: " . $insert_summary->error);
            }
            
            error_log("Inserted class summary for class_id: {$data['class_id']}");
        }
    }

    // Commit transaction
    $conn->commit();
    error_log("Transaction committed successfully");

    // Success
    $_SESSION['success_message'] = "Students data saved successfully for " . htmlspecialchars($school_name);
    
    // Clear any stored form data
    unset($_SESSION['form_data']);
    
    // Redirect to next step
    header("Location: school_step7.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 students data: " . $e->getMessage();
    
    // Log the full error for debugging
    error_log("School Step6 Save Error: " . $e->getMessage());
    error_log("Stack trace: " . $e->getTraceAsString());
    
    // Redirect back to step 6
    header("Location: school_step6.php");
    exit();
}
?>