<?php
// ============================================================================
// school_step8_save.php
// Save handler for School Budget Registration (Step 8 of 10)
// ============================================================================

// Start session
require_once 'session_config_school.php';

// Validate school session
if (!validateSchoolSession()) {
    header("Location: login.php");
    exit;
}

// Database connection
require_once 'connect.php';

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

// Check if form was submitted - MORE FLEXIBLE CHECK
// Instead of checking for specific button, check if we have any POST data
if (empty($_POST)) {
    $_SESSION['error_message'] = "No form data received. Please try again.";
    header("Location: school_step8.php");
    exit();
}

// Check for the presence of financial year data (which should always be there)
if (!isset($_POST['financial_year_start']) || !isset($_POST['financial_year_end'])) {
    $_SESSION['error_message'] = "Invalid form submission. Financial year data missing.";
    $_SESSION['form_data'] = $_POST;
    header("Location: school_step8.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.');
    }

    // Verify that EMIS code exists in school_profile (foreign key constraint)
    $check_emis = "SELECT emis_code FROM school_profile WHERE emis_code = ?";
    $check_stmt = $conn->prepare($check_emis);
    $check_stmt->bind_param("s", $emis_code);
    $check_stmt->execute();
    $check_result = $check_stmt->get_result();
    
    if ($check_result->num_rows === 0) {
        throw new Exception("EMIS code '$emis_code' does not exist in school_profile table. Please complete Step 1 first.");
    }

    // Get school_students_id
    $students_query = "SELECT id FROM school_students WHERE emis_code = ?";
    $students_stmt = $conn->prepare($students_query);
    $students_stmt->bind_param("s", $emis_code);
    $students_stmt->execute();
    $students_result = $students_stmt->get_result();
    
    if ($students_result->num_rows === 0) {
        throw new Exception("School students record not found. Please complete Step 6 first.");
    }
    $school_students_id = $students_result->fetch_assoc()['id'];

    // Get financial year data from hidden fields
    $financial_year_start = intval($_POST['financial_year_start'] ?? 0);
    $financial_year_end = intval($_POST['financial_year_end'] ?? 0);
    $financial_year_display = $_POST['financial_year_display'] ?? '';
    $financial_year_calculated_on = $_POST['financial_year_calculated_on'] ?? date('Y-m-d H:i:s');

    if ($financial_year_start === 0 || $financial_year_end === 0) {
        throw new Exception("Financial year data is missing.");
    }

    // Get student counts (these are read-only from Step 6)
    $primary_students = intval($_POST['primary_students'] ?? 0);
    $middle_students = intval($_POST['middle_students'] ?? 0);
    $high_students = intval($_POST['high_students'] ?? 0);
    $higher_secondary_students = intval($_POST['higher_secondary_students'] ?? 0);
    $total_students = $primary_students + $middle_students + $high_students + $higher_secondary_students;

    // Get government budget data
    $govt_budget = floatval($_POST['govt_budget'] ?? 0);
    $budget_expenses = floatval($_POST['budget_expenses'] ?? 0);
    $govt_balance = $govt_budget - $budget_expenses;

    // Get school income data
    $previous_balance = floatval($_POST['previous_balance'] ?? 0);
    $donations = floatval($_POST['donations'] ?? 0);
    $fee_expenses = floatval($_POST['fee_expenses'] ?? 0);
    $submitted_govt = floatval($_POST['submitted_govt'] ?? 0);
    
    // Get total school funds from the hidden field (calculated by JavaScript)
    $total_school_funds = floatval($_POST['total_school_funds'] ?? 0);
    $fee_collected = $total_school_funds;

    // Calculate school net balance
    $school_net_balance = $previous_balance + $fee_collected + $donations - $fee_expenses - $submitted_govt;

    // Get cash position
    $cash_in_hand = floatval($_POST['cash_in_hand'] ?? 0);
    $cash_in_bank = floatval($_POST['cash_in_bank'] ?? 0);
    $total_cash_available = $cash_in_hand + $cash_in_bank;

    // Calculate fund utilization percentage
    $total_income = $previous_balance + $fee_collected + $donations;
    $total_expenses = $fee_expenses + $submitted_govt;
    $fund_utilization_percent = $total_income > 0 ? round(($total_expenses / $total_income) * 100, 2) : 0;

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

    // Check if record already exists for this EMIS and financial year
    $check_query = "SELECT id FROM school_budget 
                    WHERE emis_code = ? 
                    AND financial_year_start = ? 
                    AND financial_year_end = ?";
    
    $check_stmt = $conn->prepare($check_query);
    $check_stmt->bind_param("sii", $emis_code, $financial_year_start, $financial_year_end);
    $check_stmt->execute();
    $existing = $check_stmt->get_result();
    $existing_record = $existing->fetch_assoc();

    if ($existing_record) {
        // UPDATE existing record
        $budget_id = $existing_record['id'];
        
        // Fetch old data for history
        $old_data_query = "SELECT * FROM school_budget WHERE id = ?";
        $old_stmt = $conn->prepare($old_data_query);
        $old_stmt->bind_param("i", $budget_id);
        $old_stmt->execute();
        $old_data = $old_stmt->get_result()->fetch_assoc();

        // Update query
        $update_query = "UPDATE school_budget SET 
                         school_name = ?,
                         school_students_id = ?,
                         primary_students = ?,
                         middle_students = ?,
                         high_students = ?,
                         higher_secondary_students = ?,
                         total_students = ?,
                         govt_budget = ?,
                         budget_expenses = ?,
                         govt_balance = ?,
                         previous_balance = ?,
                         donations = ?,
                         fee_collected = ?,
                         fee_expenses = ?,
                         submitted_govt = ?,
                         school_net_balance = ?,
                         cash_in_hand = ?,
                         cash_in_bank = ?,
                         total_cash_available = ?,
                         total_school_funds = ?,
                         fund_utilization_percent = ?,
                         updated_at = NOW()
                         WHERE id = ?";

        $update_stmt = $conn->prepare($update_query);
        
        // FIXED: Correct bind string - 1 s, 7 i's, 14 d's = 22 parameters
        // The last i (budget_id) is included in the 7 i's, not as a separate i at the end
        $update_stmt->bind_param(
            "siiiiiiidddddddddddddd",  // 1 s + 7 i + 14 d = 22 parameters
            $school_name,                // s
            $school_students_id,         // i
            $primary_students,            // i
            $middle_students,             // i
            $high_students,               // i
            $higher_secondary_students,   // i
            $total_students,              // i
            $govt_budget,                 // d
            $budget_expenses,             // d
            $govt_balance,                // d
            $previous_balance,            // d
            $donations,                   // d
            $fee_collected,                // d
            $fee_expenses,                // d
            $submitted_govt,              // d
            $school_net_balance,          // d
            $cash_in_hand,                // d
            $cash_in_bank,                // d
            $total_cash_available,        // d
            $total_school_funds,          // d
            $fund_utilization_percent,    // d
            $budget_id                    // i (this is the 7th i, included in the "iiiiiii")
        );

        if (!$update_stmt->execute()) {
            throw new Exception("Failed to update budget record: " . $update_stmt->error);
        }

        $action = 'UPDATE';
        error_log("Updated budget record ID: $budget_id for EMIS: $emis_code");

    } else {
        // INSERT new record
        $insert_query = "INSERT INTO school_budget (
            emis_code, school_name, school_students_id,
            financial_year_start, financial_year_end, financial_year_display,
            financial_year_calculated_on,
            primary_students, middle_students, high_students,
            higher_secondary_students, total_students,
            govt_budget, budget_expenses, govt_balance,
            previous_balance, donations, fee_collected,
            fee_expenses, submitted_govt, school_net_balance,
            cash_in_hand, cash_in_bank, total_cash_available,
            total_school_funds, fund_utilization_percent,
            created_at, status
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), 'draft')";

        $insert_stmt = $conn->prepare($insert_query);
        $insert_stmt->bind_param(
            "ssisissiiiiidddddddddddddd",
            $emis_code,
            $school_name,
            $school_students_id,
            $financial_year_start,
            $financial_year_end,
            $financial_year_display,
            $financial_year_calculated_on,
            $primary_students,
            $middle_students,
            $high_students,
            $higher_secondary_students,
            $total_students,
            $govt_budget,
            $budget_expenses,
            $govt_balance,
            $previous_balance,
            $donations,
            $fee_collected,
            $fee_expenses,
            $submitted_govt,
            $school_net_balance,
            $cash_in_hand,
            $cash_in_bank,
            $total_cash_available,
            $total_school_funds,
            $fund_utilization_percent
        );

        if (!$insert_stmt->execute()) {
            // Check for duplicate key error
            if ($conn->errno == 1062) {
                throw new Exception("A budget record for financial year $financial_year_display already exists.");
            } else {
                throw new Exception("Failed to insert budget record: " . $insert_stmt->error);
            }
        }

        $budget_id = $conn->insert_id;
        $action = 'INSERT';
        $old_data = null;
        error_log("Inserted new budget record ID: $budget_id for EMIS: $emis_code");
    }

    // Handle fund details - Delete existing funds for this budget
    $delete_funds = "DELETE FROM school_budget_funds WHERE budget_id = ?";
    $delete_stmt = $conn->prepare($delete_funds);
    $delete_stmt->bind_param("i", $budget_id);
    $delete_stmt->execute();

    // ============================================================================
    // Define all funds - UPDATED with 3 new funds: building, admission, absentee
    // ============================================================================
    $funds = [
        'tuition' => 'Tuition Fund',
        'development' => 'Development Fund',
        'building' => 'Building Fund',           // NEW
        'admission' => 'Admission Fund',          // NEW
        'absentee' => 'Absentee Fine',            // NEW
        'scout' => 'Scout/Girl Guide Fund',
        'computer' => 'Computer Fund',
        'library' => 'Library Fund',
        'furniture' => 'Furniture Fund',
        'annual' => 'Annual Fund',
        'sports' => 'Sports Fund',
        'laboratory' => 'Laboratory Fund (Sci/Tech)',
        'medical' => 'Medical Fund',
        'mosque' => 'Mosque Fund',
        'other' => 'Other Fund'
    ];

    // Prepare fund insert statement
    $fund_insert = "INSERT INTO school_budget_funds (
        budget_id, financial_year_start, financial_year_end,
        fund_id, fund_name, fund_frequency,
        primary_amount, middle_amount, high_amount, higher_sec_amount, fund_total,
        created_at, updated_at
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())";

    $fund_stmt = $conn->prepare($fund_insert);

    // Insert each fund's data
    $funds_inserted = 0;
    foreach ($funds as $fund_id => $fund_name) {
        $frequency = $_POST[$fund_id . '_frequency'] ?? 'annual';
        $primary_amt = floatval($_POST[$fund_id . '_primary'] ?? 0);
        $middle_amt = floatval($_POST[$fund_id . '_middle'] ?? 0);
        $high_amt = floatval($_POST[$fund_id . '_high'] ?? 0);
        $higher_sec_amt = floatval($_POST[$fund_id . '_higher_sec'] ?? 0);

        // Calculate fund total based on student counts and frequency
        $multiplier = ($frequency === 'monthly') ? 12 : 1;
        $fund_total = ($primary_amt * $primary_students * $multiplier) +
                     ($middle_amt * $middle_students * $multiplier) +
                     ($high_amt * $high_students * $multiplier) +
                     ($higher_sec_amt * $higher_secondary_students * $multiplier);

        $fund_stmt->bind_param(
            "iiisssddddd",
            $budget_id,
            $financial_year_start,
            $financial_year_end,
            $fund_id,
            $fund_name,
            $frequency,
            $primary_amt,
            $middle_amt,
            $high_amt,
            $higher_sec_amt,
            $fund_total
        );

        if (!$fund_stmt->execute()) {
            throw new Exception("Failed to insert fund record for $fund_name: " . $fund_stmt->error);
        }
        $funds_inserted++;
    }

    error_log("Inserted $funds_inserted fund records for budget ID: $budget_id");

    // Get new data for history
    $new_data_query = "SELECT * FROM school_budget WHERE id = ?";
    $new_stmt = $conn->prepare($new_data_query);
    $new_stmt->bind_param("i", $budget_id);
    $new_stmt->execute();
    $new_data = $new_stmt->get_result()->fetch_assoc();

    // Save to history
    $history_query = "INSERT INTO school_budget_history (budget_id, action, previous_data, new_data, changed_at) 
                      VALUES (?, ?, ?, ?, NOW())";
    $history_stmt = $conn->prepare($history_query);
    $previous_json = $old_data ? json_encode($old_data) : null;
    $new_json = json_encode($new_data);
    $history_stmt->bind_param("isss", $budget_id, $action, $previous_json, $new_json);
    $history_stmt->execute();

    // Commit transaction
    $conn->commit();

    // Set success message
    $action_text = ($action === 'INSERT') ? 'saved' : 'updated';
    $_SESSION['success_message'] = "Budget data $action_text successfully for Financial Year $financial_year_display.";

    // Clear any stored form data
    unset($_SESSION['form_data']);

    // Redirect to next step
    header("Location: school_step9.php");
    exit();

} catch (Exception $e) {
    // Rollback on error
    if (isset($conn) && $conn->ping()) {
        $conn->rollback();
        error_log("Transaction rolled back due to error: " . $e->getMessage());
    }

    // Store form data and error for repopulation
    $_SESSION['form_data'] = $_POST;
    $_SESSION['error_message'] = "Error saving budget data: " . $e->getMessage();

    // Log the full error for debugging
    error_log("School Step8 Save Error: " . $e->getMessage());
    error_log("Stack trace: " . $e->getTraceAsString());

    // Redirect back to step 8
    header("Location: school_step8.php");
    exit();
}
?>