<?php
// school_step5_save.php
// Session started
require_once 'session_config_school.php';

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

// Database connection
include_once 'connect.php';

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

// Check if form was submitted - FIXED: Just check if it's POST
if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    $_SESSION['error_message'] = 'Invalid request method.';
    header("Location: school_step5.php");
    exit;
}

// Verify CSRF token
if (!isset($_POST['csrf_token']) || $_POST['csrf_token'] !== $_SESSION['csrf_token']) {
    $_SESSION['error_message'] = 'Security validation failed. Please try again.';
    header("Location: school_step5.php");
    exit;
}

// Get session variables
$emis_code = $_SESSION['emis_code'] ?? '';
$school_name = $_SESSION['school_name'] ?? '';

if (empty($emis_code)) {
    $_SESSION['error_message'] = 'School information missing. Please login again.';
    header("Location: login.php");
    exit;
}

// Get form data
$post_names = $_POST['post_name'] ?? [];
$male_staff = $_POST['male'] ?? [];
$female_staff = $_POST['female'] ?? [];
$budget_posts = $_POST['post_as_per_budget'] ?? [];
$filled_posts = $_POST['filled_post'] ?? [];
$vacant_posts = $_POST['vacant_post'] ?? [];
$school_posts_id = $_POST['school_posts_id'] ?? '';

// Debug - log the received data
error_log("Post names: " . print_r($post_names, true));
error_log("School posts ID: " . $school_posts_id);

// Validate that we have at least one post
if (empty($post_names) || !is_array($post_names)) {
    $_SESSION['error_message'] = 'Please add at least one staff post.';
    $_SESSION['form_data'] = $_POST;
    header("Location: school_step5.php");
    exit;
}

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

try {
    // Calculate totals
    $total_male = 0;
    $total_female = 0;
    $total_budget = 0;
    $total_filled = 0;
    $total_vacant = 0;
    
    $posts_data = [];
    $valid_posts = [];
    
    // Process and validate each post row
    $has_valid_post = false;
    
    foreach ($post_names as $index => $des_id) {
        // Skip empty rows
        if (empty($des_id)) {
            continue;
        }
        
        $has_valid_post = true;
        
        // Get values for this row
        $male = isset($male_staff[$index]) ? (int)$male_staff[$index] : 0;
        $female = isset($female_staff[$index]) ? (int)$female_staff[$index] : 0;
        $budget = isset($budget_posts[$index]) ? (int)$budget_posts[$index] : 0;
        
        // Validate required fields
        if ($budget <= 0) {
            throw new Exception("Budget posts must be greater than 0 for all posts.");
        }
        
        if ($male == 0 && $female == 0) {
            throw new Exception("At least one staff (Male or Female) must be entered for each post.");
        }
        
        // Calculate filled and vacant
        $filled = $male + $female;
        $vacant = max(0, $budget - $filled);
        
        // Validate that filled doesn't exceed budget
        if ($filled > $budget) {
            throw new Exception("Filled posts cannot exceed budget posts.");
        }
        
        // Add to totals
        $total_male += $male;
        $total_female += $female;
        $total_budget += $budget;
        $total_filled += $filled;
        $total_vacant += $vacant;
        
        // Store for later insertion
        $posts_data[] = [
            'des_id' => $des_id,
            'male' => $male,
            'female' => $female,
            'budget' => $budget,
            'filled' => $filled,
            'vacant' => $vacant
        ];
        
        $valid_posts[] = $des_id;
    }
    
    if (!$has_valid_post) {
        throw new Exception("Please add at least one valid staff post.");
    }
    
    // Check for duplicate posts
    if (count($valid_posts) !== count(array_unique($valid_posts))) {
        throw new Exception("Duplicate posts are not allowed. Each designation can only be added once.");
    }
    
    // STEP 1: Handle master school_posts record
    if (empty($school_posts_id)) {
        // Check if a record already exists for this EMIS code
        $check_stmt = $conn->prepare("SELECT id FROM school_posts WHERE emis_code = ?");
        $check_stmt->bind_param("s", $emis_code);
        $check_stmt->execute();
        $check_result = $check_stmt->get_result();
        
        if ($check_result->num_rows > 0) {
            // Record exists, use it
            $row = $check_result->fetch_assoc();
            $school_posts_id = $row['id'];
            
            // Update the master record with new totals
            $update_master = $conn->prepare("
                UPDATE school_posts 
                SET male_staff = ?, female_staff = ?, budget_posts = ?, 
                    filled_posts = ?, vacant_posts = ?, updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
            ");
            $update_master->bind_param("iiiiii", 
                $total_male, $total_female, $total_budget, 
                $total_filled, $total_vacant, $school_posts_id
            );
            
            if (!$update_master->execute()) {
                throw new Exception("Failed to update master post record: " . $update_master->error);
            }
            $update_master->close();
            
        } else {
            // Create new master record
            $insert_master = $conn->prepare("
                INSERT INTO school_posts (emis_code, male_staff, female_staff, budget_posts, filled_posts, vacant_posts)
                VALUES (?, ?, ?, ?, ?, ?)
            ");
            $insert_master->bind_param("siiiii", 
                $emis_code, $total_male, $total_female, $total_budget, 
                $total_filled, $total_vacant
            );
            
            if (!$insert_master->execute()) {
                throw new Exception("Failed to create master post record: " . $insert_master->error);
            }
            
            $school_posts_id = $insert_master->insert_id;
            $insert_master->close();
        }
        $check_stmt->close();
    } else {
        // Update existing master record
        $update_master = $conn->prepare("
            UPDATE school_posts 
            SET male_staff = ?, female_staff = ?, budget_posts = ?, 
                filled_posts = ?, vacant_posts = ?, updated_at = CURRENT_TIMESTAMP
            WHERE id = ? AND emis_code = ?
        ");
        $update_master->bind_param("iiiiiii", 
            $total_male, $total_female, $total_budget, 
            $total_filled, $total_vacant, $school_posts_id, $emis_code
        );
        
        if (!$update_master->execute()) {
            throw new Exception("Failed to update master post record: " . $update_master->error);
        }
        
        if ($update_master->affected_rows === 0) {
            throw new Exception("Master post record not found or doesn't belong to this school.");
        }
        $update_master->close();
    }
    
    // STEP 2: Handle school_posts_summary records (detail table)
    
    // First, get existing designation IDs for this school_posts_id
    $existing_des_ids = [];
    $select_existing = $conn->prepare("SELECT des_id FROM school_posts_summary WHERE sp_id = ?");
    $select_existing->bind_param("i", $school_posts_id);
    $select_existing->execute();
    $existing_result = $select_existing->get_result();
    
    while ($row = $existing_result->fetch_assoc()) {
        $existing_des_ids[] = $row['des_id'];
    }
    $select_existing->close();
    
    // Determine which posts to insert, update, or delete
    $new_des_ids = array_column($posts_data, 'des_id');
    
    // Posts to delete (exist in DB but not in current submission)
    $to_delete = array_diff($existing_des_ids, $new_des_ids);
    if (!empty($to_delete)) {
        // Build placeholders for IN clause
        $placeholders = implode(',', array_fill(0, count($to_delete), '?'));
        $types = str_repeat('i', count($to_delete));
        
        $delete_query = "DELETE FROM school_posts_summary WHERE sp_id = ? AND des_id IN ($placeholders)";
        $delete_stmt = $conn->prepare($delete_query);
        
        // Create array of parameters with sp_id first, then all des_ids to delete
        $params = array_merge([$school_posts_id], $to_delete);
        $delete_stmt->bind_param("i" . $types, ...$params);
        
        if (!$delete_stmt->execute()) {
            throw new Exception("Failed to remove old posts: " . $delete_stmt->error);
        }
        $delete_stmt->close();
    }
    
    // Process each post (insert or update)
    foreach ($posts_data as $post) {
        if (in_array($post['des_id'], $existing_des_ids)) {
            // Update existing record
            $update_detail = $conn->prepare("
                UPDATE school_posts_summary 
                SET male_staff = ?, female_staff = ?, budget_posts = ?, 
                    filled_posts = ?, vacant_posts = ?, created_at = CURRENT_TIMESTAMP
                WHERE sp_id = ? AND des_id = ?
            ");
            $update_detail->bind_param("iiiiiii", 
                $post['male'], $post['female'], $post['budget'],
                $post['filled'], $post['vacant'],
                $school_posts_id, $post['des_id']
            );
            
            if (!$update_detail->execute()) {
                throw new Exception("Failed to update post: " . $update_detail->error);
            }
            $update_detail->close();
        } else {
            // Insert new record
            $insert_detail = $conn->prepare("
                INSERT INTO school_posts_summary 
                (sp_id, des_id, male_staff, female_staff, budget_posts, filled_posts, vacant_posts)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            ");
            $insert_detail->bind_param("iiiiiii", 
                $school_posts_id, $post['des_id'],
                $post['male'], $post['female'], $post['budget'],
                $post['filled'], $post['vacant']
            );
            
            if (!$insert_detail->execute()) {
                throw new Exception("Failed to insert post: " . $insert_detail->error);
            }
            $insert_detail->close();
        }
    }
    
    // Commit transaction
    $conn->commit();
    
    // Set success message and redirect
    $_SESSION['success_message'] = 'School posts information saved successfully!';
    
    // Always go to next step (step 6)
    header("Location: school_step6.php");
    exit;
    
} catch (Exception $e) {
    // Rollback transaction on error
    $conn->rollback();
    
    // Log error for debugging
    error_log("School Posts Save Error: " . $e->getMessage());
    error_log("Stack trace: " . $e->getTraceAsString());
    
    // Set error message and redirect back
    $_SESSION['error_message'] = 'Error saving posts: ' . $e->getMessage();
    $_SESSION['form_data'] = $_POST;
    header("Location: school_step5.php");
    exit;
}