<?php
include("includes/db.php");

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    // Collect basic student form data
    $studentId = mysqli_real_escape_string($conn, $_POST['student_id']);
    $symbol_no = mysqli_real_escape_string($conn, $_POST['symbol_no']);
    $regd_id = mysqli_real_escape_string($conn, $_POST['regd_id']);
    $name = mysqli_real_escape_string($conn, $_POST['name']);
    $dob = mysqli_real_escape_string($conn, $_POST['dob']);
    $address = mysqli_real_escape_string($conn, $_POST['address']);
    $phone = mysqli_real_escape_string($conn, $_POST['phone']);
    $email = mysqli_real_escape_string($conn, $_POST['email']);
    $parentName = mysqli_real_escape_string($conn, $_POST['parent_name']);
    $parentPhone = mysqli_real_escape_string($conn, $_POST['parent_phone']);
    $classId = mysqli_real_escape_string($conn, $_POST['studentClass']);
    $sectionId = mysqli_real_escape_string($conn, $_POST['section']);
    $academicYear = mysqli_real_escape_string($conn, $_POST['academic_year']);
    $status = mysqli_real_escape_string($conn, $_POST['status']);
    $remarks = mysqli_real_escape_string($conn, $_POST['remarks']);
    $gender = $conn->real_escape_string($_POST["gender"]);
    $std_group = $conn->real_escape_string($_POST["group"]);
    
    // Get class name for the students table
    $classNameQuery = "SELECT class FROM classes WHERE class_id = '$classId'";
    $classNameResult = $conn->query($classNameQuery);
    $classRow = $classNameResult->fetch_assoc();
    $className = mysqli_real_escape_string($conn, $classRow['class']);
    
    // Photo handling
    $existingPhoto = isset($_POST['existing_photo']) ? mysqli_real_escape_string($conn, $_POST['existing_photo']) : '';
    $newPhoto = '';
    
    if (!empty($_FILES['photo']['name'])) {
        $newPhoto = generateUniquePhotoName($regd_id, $name, $dob);
        $photoPath = "uploads/" . $newPhoto;
    }
    
    // Start transaction for multiple updates
    $conn->begin_transaction();
    
    try {
        // SQL query to update student details
        $query = "UPDATE students SET 
                    regd_id = '$regd_id',
                    symbol_no = '$symbol_no',
                    name = '$name',
                    dob = '$dob',
                    address = '$address',
                    phone = '$phone',
                    email = '$email',
                    parent_name = '$parentName',
                    parent_phone = '$parentPhone',
                    class = '$className',
                    Class_id = '$classId',
                    gender = '$gender',
                    std_group = '$std_group'";
                    
        if (!empty($newPhoto)) {
            if (!empty($existingPhoto) && $existingPhoto != $newPhoto && file_exists("uploads/" . $existingPhoto)) {
                unlink("uploads/" . $existingPhoto);
            }
            
            move_uploaded_file($_FILES['photo']['tmp_name'], $photoPath);
            $query .= ", photo = '$newPhoto'";
        }
        
        $query .= " WHERE student_id = '$studentId'";

        // Execute the student update query
        if (!$conn->query($query)) {
            throw new Exception("Error updating student details: " . $conn->error);
        }
        
        // Check if academic record exists for this student and academic year
        $checkAcademicQuery = "SELECT id FROM student_class_section_data 
                               WHERE student_id = '$studentId' AND academic_year = '$academicYear'";
        $academicResult = $conn->query($checkAcademicQuery);
        
        if ($academicResult->num_rows > 0) {
            // Update existing academic record
            $updateAcademicQuery = "UPDATE student_class_section_data SET
                                    class_id = '$classId',
                                    section_id = '$sectionId',
                                    status = '$status',
                                    remarks = '$remarks',
                                    updated_at = CURRENT_TIMESTAMP
                                    WHERE student_id = '$studentId' AND academic_year = '$academicYear'";
            
            if (!$conn->query($updateAcademicQuery)) {
                throw new Exception("Error updating academic record: " . $conn->error);
            }
        } else {
            // Insert new academic record
            $insertAcademicQuery = "INSERT INTO student_class_section_data 
                                    (student_id, class_id, section_id, academic_year, status, remarks, created_at, updated_at)
                                    VALUES ('$studentId', '$classId', '$sectionId', '$academicYear', '$status', '$remarks', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)";
            
            if (!$conn->query($insertAcademicQuery)) {
                throw new Exception("Error inserting academic record: " . $conn->error);
            }
        }
        
        // Update the marks table if the class_id has changed
        $marksQuery = "UPDATE marks SET class_id = '$classId' WHERE student_id = '$studentId'";
        if (!$conn->query($marksQuery)) {
            throw new Exception("Error updating marks: " . $conn->error);
        }
        
        // Update the studentsub table if the class_id has changed
        $studentsubQuery = "UPDATE studentsub SET class_id = '$classId' WHERE student_id = '$studentId'";
        if (!$conn->query($studentsubQuery)) {
            throw new Exception("Error updating subjects: " . $conn->error);
        }
        
        // Commit transaction
        $conn->commit();
        
        echo "<script>
            window.onload = function() {
                Swal.fire({
                    icon: 'success',
                    title: 'Update Successful',
                    text: 'Student details, academic record, marks, and subjects have been updated.',
                    showConfirmButton: false,
                    timer: 2000
                }).then(() => {
                    window.location.href = 'student_detail.php?student_id=$studentId&success=1';
                });
            }
        </script>";
        
    } catch (Exception $e) {
        // Rollback transaction on error
        $conn->rollback();
        
        echo "<script>
            window.onload = function() {
                Swal.fire({
                    icon: 'error',
                    title: 'Update Failed',
                    text: '" . addslashes($e->getMessage()) . "',
                    showConfirmButton: true
                }).then(() => {
                    window.history.back();
                });
            }
        </script>";
    }
    
    $conn->close();
} else {
    // Redirect if accessed directly
    echo "<script>
        window.onload = function() {
            Swal.fire({
                icon: 'error',
                title: 'Access Denied',
                text: 'Direct access not allowed.',
                showConfirmButton: true
            }).then(() => {
                window.location.href = 'manage-students.php';
            });
        }
    </script>";
}

// Function to generate a unique photo name
function generateUniquePhotoName($regd_id, $name, $dob) {
    $hash = $name . "-" . $regd_id . "-" . $dob;
    $extension = pathinfo($_FILES['photo']['name'], PATHINFO_EXTENSION);
    return $hash . '_' . time() . '.' . $extension;
}
?>

<!-- SweetAlert JS -->
<script src="https://cdn.jsdelivr.net/npm/sweetalert2@11"></script>