<?php
session_start();
header('Content-Type: application/json');
include('../includes/db_pdo.php');

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    
    // Validate input
    if (!isset($_POST['student_id']) || !isset($_POST['status'])) {
        echo json_encode([
            'success' => false,
            'message' => 'Missing required parameters'
        ]);
        exit;
    }
    
    $student_id = intval($_POST['student_id']);
    $status = trim($_POST['status']);
    $remarks = isset($_POST['remarks']) ? trim($_POST['remarks']) : '';
    
    // Validate student ID
    if ($student_id <= 0) {
        echo json_encode([
            'success' => false,
            'message' => 'Invalid student ID'
        ]);
        exit;
    }
    
    // Define allowed statuses
    $allowed_student_statuses = ['active', 'inactive', 'transferred', 'dropout', 'repeated', 'others'];
    $allowed_section_statuses = ['active', 'promoted', 'transferred', 'dropout', 'repeated', 'others'];
    
    // Validate student status
    if (!in_array($status, $allowed_student_statuses)) {
        echo json_encode([
            'success' => false,
            'message' => 'Invalid status value. Allowed: ' . implode(', ', $allowed_student_statuses)
        ]);
        exit;
    }
    
    // Map status for section table
    $section_status = $status;
    if ($status === 'inactive') {
        $section_status = 'dropout';
    }
    
    // Validate section status
    if (!in_array($section_status, $allowed_section_statuses)) {
        echo json_encode([
            'success' => false,
            'message' => 'Invalid mapped status value'
        ]);
        exit;
    }
    
    try {
        // Begin transaction
        $conn->beginTransaction();
        
        // Check if student exists
        $check_query = "SELECT student_id, status as old_status FROM students WHERE student_id = :student_id";
        $check_stmt = $conn->prepare($check_query);
        $check_stmt->execute([':student_id' => $student_id]);
        
        if ($check_stmt->rowCount() == 0) {
            echo json_encode([
                'success' => false,
                'message' => 'Student not found'
            ]);
            $conn->rollBack();
            exit;
        }
        
        $student_data = $check_stmt->fetch(PDO::FETCH_ASSOC);
        $old_status = $student_data['old_status'];
        
        // Update student status in students table
        $update_query = "UPDATE students 
                        SET status = :status,
                            updated_at = NOW()
                        WHERE student_id = :student_id";
        $update_stmt = $conn->prepare($update_query);
        $update_stmt->execute([
            ':status' => $status,
            ':student_id' => $student_id
        ]);
        
        // Check if student has records in section data
        $check_section_query = "SELECT COUNT(*) as count FROM student_class_section_data WHERE student_id = :student_id";
        $check_section_stmt = $conn->prepare($check_section_query);
        $check_section_stmt->execute([':student_id' => $student_id]);
        $section_count = $check_section_stmt->fetch(PDO::FETCH_ASSOC)['count'];
        
        if ($section_count > 0) {
            // Update existing records
            if (!empty($remarks)) {
                // With remarks
                $update_section_query = "UPDATE student_class_section_data 
                                        SET status = :status,
                                            remarks = CONCAT(
                                                IFNULL(remarks, ''),
                                                IF(:remarks != '', CONCAT(' | ', :remarks2), ''),
                                                ' | Status changed to ', :status2, ' on ', NOW()
                                            ),
                                            updated_at = NOW()
                                        WHERE student_id = :student_id2";
                $update_section_stmt = $conn->prepare($update_section_query);
                $update_section_stmt->execute([
                    ':status' => $section_status,
                    ':remarks' => $remarks,
                    ':remarks2' => $remarks,
                    ':status2' => $section_status,
                    ':student_id2' => $student_id
                ]);
            } else {
                // Without remarks
                $update_section_query = "UPDATE student_class_section_data 
                                        SET status = :status,
                                            remarks = CONCAT(
                                                IFNULL(remarks, ''),
                                                ' | Status changed to ', :status2, ' on ', NOW()
                                            ),
                                            updated_at = NOW()
                                        WHERE student_id = :student_id";
                $update_section_stmt = $conn->prepare($update_section_query);
                $update_section_stmt->execute([
                    ':status' => $section_status,
                    ':status2' => $section_status,
                    ':student_id' => $student_id
                ]);
            }
        }
        
        // Commit transaction
        $conn->commit();
        
        // Prepare success message
        $status_display = ucfirst(str_replace('_', ' ', $status));
        $message = "Student status updated from '{$old_status}' to '{$status_display}' successfully";
        
        echo json_encode([
            'success' => true,
            'message' => $message,
            'data' => [
                'student_id' => $student_id,
                'old_status' => $old_status,
                'new_status' => $status,
                'section_status' => $section_status
            ]
        ]);
        
    } catch (PDOException $e) {
        // Rollback transaction on error
        $conn->rollBack();
        
        // Log error
        error_log("Status Update Error: " . $e->getMessage() . " for student ID: " . $student_id);
        
        echo json_encode([
            'success' => false,
            'message' => 'Database error: ' . $e->getMessage()
        ]);
    }
    
} else {
    echo json_encode([
        'success' => false,
        'message' => 'Invalid request method. POST required.'
    ]);
}
?>