??????????????
??????????????
<?php
include('session.php');
include 'connection.php';

if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    die("Invalid request");
}

$id       = intval($_POST['id']);
$userId   = $_SESSION['uid'];

// First verify that this record belongs to the current user
$checkSql = "SELECT id FROM fees WHERE id = '$id' AND addedby = '$userId' LIMIT 1";
$checkRes = $conn->query($checkSql);

if ($checkRes->num_rows === 0) {
    die("You do not have permission to update this record.");
}

// Collect fields from POST
$admission_no    = $conn->real_escape_string($_POST['admission_no']);
$student_name    = $conn->real_escape_string($_POST['student_name']);
$fathers_name    = $conn->real_escape_string($_POST['fathers_name']);
$mobile_no       = $conn->real_escape_string($_POST['mobile_no']);
$course_name     = $conn->real_escape_string($_POST['course_name']);
$total_fee       = $conn->real_escape_string($_POST['total_fee']);
$due_amount      = $conn->real_escape_string($_POST['due_amount']);
$paid_amount     = $conn->real_escape_string($_POST['paid_amount']);
$transaction_id  = $conn->real_escape_string($_POST['transaction_id']);
$payment_date    = $conn->real_escape_string($_POST['payment_date']);
$received_amount = $conn->real_escape_string($_POST['received_amount']);
$bank_upi        = $conn->real_escape_string($_POST['bank_upi']);
$payment_mode    = isset($_POST['payment_mode']) ? $conn->real_escape_string($_POST['payment_mode']) : '';

// If payment mode is Cash, force Transaction ID and Bank/UPI to 'NA'
if (strcasecmp($payment_mode, 'Cash') === 0) {
    $transaction_id = 'NA';
    $bank_upi = 'NA';
}

// Get the old received amount from fees table
$old_received_sql = "SELECT received_amount FROM fees WHERE id = '$id'";
$old_received_result = $conn->query($old_received_sql);
$old_received_amount = 0;
if ($old_received_result && $old_received_result->num_rows > 0) {
    $old_received_amount = floatval($old_received_result->fetch_assoc()['received_amount']);
}

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

try {
    // Update fees table
    $setParts = [
        "admission_no    = '$admission_no'",
        "student_name    = '$student_name'",
        "fathers_name    = '$fathers_name'",
        "mobile_no       = '$mobile_no'",
        "course_name     = '$course_name'",
        "total_fee       = '$total_fee'",
        "due_amount      = '$due_amount'",
        "paid_amount     = '$paid_amount'",
        "transaction_id  = '$transaction_id'",
        "payment_date    = '$payment_date'",
        "received_amount = '$received_amount'",
        "bank_upi        = '$bank_upi'"
    ];

    // Check if payment_mode column exists
    $hasPaymentModeCol = false;
    $checkColSql = "SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fees' AND COLUMN_NAME = 'payment_mode'";
    $checkColRes = $conn->query($checkColSql);
    if ($checkColRes && $checkColRes->num_rows > 0) {
        $setParts[] = "payment_mode = '$payment_mode'";
    }

    $sql = "UPDATE fees SET " . implode(",\n            ", $setParts) . "
            WHERE id = '$id' AND addedby = '$userId'";
    
    $conn->query($sql);

    // Update accounts table
    // First get current accounts data
    $current_account_sql = "SELECT received_amount FROM accounts WHERE admission_no = '$admission_no'";
    $current_account_result = $conn->query($current_account_sql);
    
    if ($current_account_result->num_rows > 0) {
        $account_row = $current_account_result->fetch_assoc();
        $current_received = floatval($account_row['received_amount']);
        
        // Adjust the received amount: subtract old and add new
        $new_received_total = $current_received - $old_received_amount + floatval($received_amount);
        
        // Calculate new due and paid amounts
        $new_due_amount = floatval($total_fee) - $new_received_total;
        if ($new_due_amount < 0) $new_due_amount = 0;
        
        $new_paid_amount = floatval($total_fee) - $new_due_amount;

        // Update accounts table
        $update_account_sql = "UPDATE accounts SET 
            total_fees = '$total_fee',
            due_amount = '$new_due_amount',
            paid_amount = '$new_paid_amount',
            received_amount = '$new_received_total'
            WHERE admission_no = '$admission_no'";
        
        $conn->query($update_account_sql);
    } else {
        // Insert new record if doesn't exist
        $insert_account_sql = "INSERT INTO accounts (
            admission_no, total_fees, due_amount, paid_amount, received_amount
        ) VALUES (
            '$admission_no', 
            '$total_fee', 
            '$due_amount', 
            '$paid_amount', 
            '$received_amount'
        )";
        
        $conn->query($insert_account_sql);
    }

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

    echo "<script>
        alert('Fee and accounts updated successfully');
        window.location.href = 'create-fee.php';
    </script>";
    exit();

} catch (Exception $e) {
    // Rollback transaction on error
    $conn->rollback();
    echo "<script>
        alert('Error updating records: " . addslashes($e->getMessage()) . "');
        window.history.back();
    </script>";
}
?>