<?php
session_start();
require_once 'config.php';
require_once 'config_paystack.php';
require_once 'app/classes/BalanceHistory.php';
require_once 'app/classes/NotificationManager.php';
require_once 'app/classes/AdminNotificationManager.php';
use App\classes\BalanceHistory;

// SECURITY: Rate limiting to prevent abuse (simple in-memory approach)
// For production, consider using Redis or database-based rate limiting
$ipAddress = $_SERVER['REMOTE_ADDR'] ?? 'unknown';
static $rateLimitCache = [];
$cacheKey = 'paystack_callback_' . $ipAddress;
$currentTime = time();
$timeWindow = 60;
$maxAttempts = 10;

// Clean old entries (older than timeWindow)
if (isset($rateLimitCache[$cacheKey])) {
    $rateLimitCache[$cacheKey] = array_filter($rateLimitCache[$cacheKey], function($timestamp) use ($currentTime, $timeWindow) {
        return ($currentTime - $timestamp) < $timeWindow;
    });
} else {
    $rateLimitCache[$cacheKey] = [];
}

// Check if limit exceeded
if (count($rateLimitCache[$cacheKey]) >= $maxAttempts) {
    error_log("SECURITY: Rate limit exceeded for IP: $ipAddress - " . count($rateLimitCache[$cacheKey]) . " attempts in $timeWindow seconds");
    http_response_code(429);
    echo 'Too many requests';
    exit;
}

// Add current request to cache
$rateLimitCache[$cacheKey][] = $currentTime;

// Clean up old paystack topup session data (older than 1 hour)
$currentTime = time();
foreach ($_SESSION as $key => $value) {
    if (strpos($key, 'paystack_topup_') === 0 && is_array($value) && isset($value['timestamp'])) {
        if ($currentTime - $value['timestamp'] > 3600) { // 1 hour
            unset($_SESSION[$key]);
        }
    }
}

$reference = isset($_GET['reference']) ? trim($_GET['reference']) : '';
if ($reference === '') {
    http_response_code(400);
    echo 'Missing payment reference';
    exit;
}

// SECURITY: Validate reference format (alphanumeric and hyphens only)
if (!preg_match('/^[a-zA-Z0-9_-]+$/', $reference)) {
    error_log("SECURITY: Invalid reference format - Reference: $reference, IP: $ipAddress");
    http_response_code(400);
    echo 'Invalid payment reference format';
    exit;
}

// CRITICAL: Check if this is a sub-agent top-up BEFORE verification to get the correct store_id
// Sub-agent top-ups use store-specific Paystack credentials, so we need to verify with the correct credentials
$subAgentStoreId = null;
$checkSubAgentStmt = $conn->prepare("
    SELECT store_id 
    FROM subagent_topup_tracking 
    WHERE payment_reference = ? 
    LIMIT 1
");
$checkSubAgentStmt->bind_param("s", $reference);
$checkSubAgentStmt->execute();
$subAgentResult = $checkSubAgentStmt->get_result();
$subAgentData = $subAgentResult->fetch_assoc();
$checkSubAgentStmt->close();

if ($subAgentData && !empty($subAgentData['store_id'])) {
    $subAgentStoreId = intval($subAgentData['store_id']);
    error_log("Sub-Agent Top-up detected - Reference: $reference, Store ID: $subAgentStoreId - Using store-specific Paystack credentials");
}

// Verify payment with Paystack - use store-specific credentials if this is a sub-agent top-up
$verify = paystackVerifyTransaction($reference, $subAgentStoreId);
$ok = $verify['success'] && (($verify['data']['data']['status'] ?? '') === 'success');

// SECURITY: If payment verification failed, check if it's a sub-agent top-up and update tracking
if (!$ok) {
    // Check if this is a sub-agent top-up that failed
    $checkFailedTopupStmt = $conn->prepare("
        SELECT id, user_id, store_id, store_owner_id 
        FROM subagent_topup_tracking 
        WHERE payment_reference = ? AND payment_status = 'pending'
        LIMIT 1
    ");
    $checkFailedTopupStmt->bind_param("s", $reference);
    $checkFailedTopupStmt->execute();
    $failedTopupResult = $checkFailedTopupStmt->get_result();
    $failedTopup = $failedTopupResult->fetch_assoc();
    $checkFailedTopupStmt->close();
    
    if ($failedTopup) {
        // Extract error message from Paystack response
        $errorMessage = 'Payment verification failed';
        $paystackStatus = 'unknown';
        $httpCode = $verify['status'] ?? 'unknown';
        
        // Try to get error message from different possible locations in Paystack response
        if (isset($verify['error'])) {
            $errorMessage = $verify['error'];
        } elseif (isset($verify['data']['message'])) {
            $errorMessage = $verify['data']['message'];
        } elseif (isset($verify['data']['data']['message'])) {
            $errorMessage = $verify['data']['data']['message'];
        }
        
        // Try to get status from different possible locations
        if (isset($verify['data']['data']['status'])) {
            $paystackStatus = $verify['data']['data']['status'];
        } elseif (isset($verify['data']['status'])) {
            $paystackStatus = $verify['data']['status'] ? 'success' : 'failed';
        }
        
        // Log the full Paystack response for debugging (truncated to prevent log spam)
        $paystackResponse = json_encode($verify['data'] ?? []);
        if (strlen($paystackResponse) > 500) {
            $paystackResponse = substr($paystackResponse, 0, 500) . '... (truncated)';
        }
        error_log("Paystack verification failure details - Reference: $reference, HTTP Code: $httpCode, Response: $paystackResponse");
        
        // Start transaction to ensure both tables are updated atomically
        $conn->begin_transaction();
        
        try {
            // Update tracking record to failed
            $updateFailedStmt = $conn->prepare("
                UPDATE subagent_topup_tracking 
                SET payment_status = 'failed',
                    notes = ?,
                    updated_at = NOW()
                WHERE payment_reference = ? AND payment_status = 'pending'
            ");
            $failureNote = "Payment verification failed (HTTP $httpCode). Status: $paystackStatus, Error: $errorMessage";
            $updateFailedStmt->bind_param("ss", $failureNote, $reference);
            $updateFailedStmt->execute();
            $updateFailedStmt->close();
            
            // Also update orders_payments table to mark payment as failed
            $updatePaymentStmt = $conn->prepare("
                UPDATE orders_payments 
                SET payment_status = 'failed',
                    payment_date = NOW()
                WHERE payment_reference = ? AND payment_status = 'pending'
            ");
            $updatePaymentStmt->bind_param("s", $reference);
            $updatePaymentStmt->execute();
            $updatePaymentStmt->close();
            
            // Commit transaction
            $conn->commit();
            
            error_log("SECURITY: Sub-Agent Top-up Failed - Tracking ID: {$failedTopup['id']}, User ID: {$failedTopup['user_id']}, Store ID: {$failedTopup['store_id']}, Reference: $reference, Reason: $failureNote");
            
            // Redirect user to top-up page with error message
            // Use a user-friendly message for "Transaction reference not found" (payment was cancelled/not completed)
            if (stripos($errorMessage, 'not found') !== false || stripos($errorMessage, 'reference not found') !== false) {
                $userFriendlyError = "Payment was not completed. The transaction was cancelled or not initiated. Please try again if you wish to top up your account.";
            } else {
                $userFriendlyError = $errorMessage;
            }
            
            header('Location: pages/subagent_topup.php?topup=failed&ref=' . urlencode($reference) . '&error=' . urlencode($userFriendlyError));
            exit;
            
        } catch (Exception $e) {
            // Rollback on error
            $conn->rollback();
            error_log("ERROR: Failed to update sub-agent top-up failure status - Reference: $reference, Error: " . $e->getMessage());
            
            // Still redirect user even if database update fails
            header('Location: pages/subagent_topup.php?topup=failed&ref=' . urlencode($reference) . '&error=' . urlencode('Payment verification failed. Please contact support.'));
            exit;
        }
    }
}

// If payment successful, update user balance and redirect
if ($ok) {
    $paymentData = $verify['data']['data'] ?? [];
    $customerEmail = $paymentData['customer']['email'] ?? '';
    $amountPaid = intval($paymentData['amount'] ?? 0); // in pesewas (total including charge)
    $totalAmountGHS = $amountPaid / 100; // convert to GHS
    $externalTxnId = $paymentData['id'] ?? null;
    
    // Get the base amount from metadata (excluding charge)
    $metadata = $paymentData['metadata'] ?? [];
    $baseAmount = floatval($metadata['base_amount'] ?? 0);
    $chargeAmount = floatval($metadata['charge_amount'] ?? 0);
    
    // If metadata is not available, check session
    if ($baseAmount <= 0 && isset($_SESSION['paystack_topup_' . $reference])) {
        $sessionData = $_SESSION['paystack_topup_' . $reference];
        $baseAmount = floatval($sessionData['base_amount'] ?? 0);
        $chargeAmount = floatval($sessionData['charge_amount'] ?? 0);
        
        // Clean up session data after retrieving it
        unset($_SESSION['paystack_topup_' . $reference]);
    }
    
    // Fallback to full amount if base amount is not available (backward compatibility)
    // This ensures old payments without the charge system still work
    $amountGHS = ($baseAmount > 0) ? $baseAmount : $totalAmountGHS;
    
    // Log the crediting details for audit purposes
    error_log("Paystack Top-up - Ref: $reference, Total Paid: GHS $totalAmountGHS, Crediting: GHS $amountGHS, Charge: GHS $chargeAmount");
    
    if ($customerEmail && $amountGHS > 0) {
        // 🔒 START DATABASE TRANSACTION FOR ATOMICITY
        $conn->begin_transaction();
        
        try {
            // 🔒 CRITICAL: IDEMPOTENCY CHECK - Prevent duplicate processing
            // This prevents replay attacks where the callback URL is visited multiple times
            $checkStmt = $conn->prepare("SELECT id, user_id, total_amount, payment_status FROM orders_payments WHERE payment_reference = ? LIMIT 1");
            if (!$checkStmt) {
                throw new Exception("Database error: Failed to prepare idempotency check");
            }
            $checkStmt->bind_param("s", $reference);
            $checkStmt->execute();
            $existingPayment = $checkStmt->get_result()->fetch_assoc();
            $checkStmt->close();
            
            if ($existingPayment) {
                // SECURITY: Check if payment was already completed
                $existingStatus = $existingPayment['payment_status'] ?? 'pending';
                if ($existingStatus === 'completed') {
                    // Payment already processed - log the attempt and redirect without crediting again
                    error_log("SECURITY: Attempted duplicate payment processing for reference: $reference by IP: " . ($_SERVER['REMOTE_ADDR'] ?? 'unknown'));
                    $conn->commit();
                    
                    // Determine redirect based on user role
                    $userCheckStmt = $conn->prepare("SELECT role, is_store_owner FROM agents WHERE id = ?");
                    $userCheckStmt->bind_param("i", $existingPayment['user_id']);
                    $userCheckStmt->execute();
                    $userCheckResult = $userCheckStmt->get_result();
                    $userCheckData = $userCheckResult->fetch_assoc();
                    $userCheckStmt->close();
                    
                    if ($userCheckData && ($userCheckData['role'] === 'store_owner' || $userCheckData['is_store_owner'] == 1)) {
                        header('Location: pages/store_topup.php?topup=already_processed&ref=' . urlencode($reference));
                    } else {
                        header('Location: user_index.php?topup=already_processed&ref=' . urlencode($reference));
                    }
                    exit;
                }
                // If status is 'pending', continue processing
            }
            
            // SECURITY: Verify payment intent exists and get user ID from it (more secure than email lookup)
            $intentStmt = $conn->prepare("
                SELECT op.id, op.user_id, op.total_amount, op.payment_status, a.id as agent_id, a.username, a.role, a.balance, a.email, a.is_store_owner
                FROM orders_payments op
                JOIN agents a ON op.user_id = a.id
                WHERE op.payment_reference = ? AND op.payment_status = 'pending'
                LIMIT 1
            ");
            if (!$intentStmt) {
                throw new Exception("Database error: Failed to prepare payment intent lookup");
            }
            $intentStmt->bind_param("s", $reference);
            $intentStmt->execute();
            $intentResult = $intentStmt->get_result();
            $paymentIntent = $intentResult->fetch_assoc();
            $intentStmt->close();
            
            if (!$paymentIntent) {
                // Fallback to email lookup for backward compatibility
                $normalizedEmail = strtolower(trim($customerEmail));
                $stmt = $conn->prepare("SELECT id, username, role, balance, email, is_store_owner FROM agents WHERE LOWER(email) = ? LIMIT 1");
                if (!$stmt) {
                    throw new Exception("Database error: Failed to prepare user lookup");
                }
                $stmt->bind_param("s", $normalizedEmail);
                $stmt->execute();
                $result = $stmt->get_result();
                $user = $result->fetch_assoc();
                $stmt->close();
                
                // If user not found with normalized email, try direct match as fallback
                if (!$user) {
                    $fallbackStmt = $conn->prepare("SELECT id, username, role, balance, email, is_store_owner FROM agents WHERE email = ? LIMIT 1");
                    if ($fallbackStmt) {
                        $fallbackStmt->bind_param("s", $customerEmail);
                        $fallbackStmt->execute();
                        $fallbackResult = $fallbackStmt->get_result();
                        $user = $fallbackResult->fetch_assoc();
                        $fallbackStmt->close();
                        
                        if ($user) {
                            error_log("WARNING: Email case mismatch detected. Paystack email: $customerEmail, DB email: " . $user['email']);
                        }
                    }
                }
            } else {
                // SECURITY: Use payment intent data (more secure)
                $user = [
                    'id' => $paymentIntent['agent_id'],
                    'username' => $paymentIntent['username'],
                    'role' => $paymentIntent['role'],
                    'balance' => $paymentIntent['balance'],
                    'email' => $paymentIntent['email'],
                    'is_store_owner' => $paymentIntent['is_store_owner']
                ];
                
                // SECURITY: Verify email matches payment intent
                if (strtolower($user['email']) !== strtolower($customerEmail)) {
                    error_log("SECURITY: Email mismatch in payment callback - Reference: $reference, Intent Email: " . $user['email'] . ", Paystack Email: $customerEmail, IP: " . ($_SERVER['REMOTE_ADDR'] ?? 'unknown'));
                    // Continue processing but log the mismatch
                }
                
                // SECURITY: Verify amount matches payment intent (allow small differences for charge calculations)
                $intentAmount = floatval($paymentIntent['total_amount']);
                if (abs($amountGHS - $intentAmount) > 0.50) { // Allow 0.50 GHS difference for charge variations
                    error_log("SECURITY: Amount mismatch in payment callback - Reference: $reference, Intent Amount: $intentAmount, Paystack Amount: $amountGHS, IP: " . ($_SERVER['REMOTE_ADDR'] ?? 'unknown'));
                    // Use the base amount from intent if available
                    if ($baseAmount > 0) {
                        $amountGHS = $baseAmount;
                    }
                }
            }
            
            if ($user) {
                $userId = $user['id'];
                $userRole = $user['role'];
                $currentBalance = $user['balance'];
                
                // 1. UPDATE OR INSERT PAYMENT RECORD
                // Check if payment intent exists (created during top-up initialization)
                $checkIntentStmt = $conn->prepare("SELECT id, payment_status FROM orders_payments WHERE payment_reference = ? LIMIT 1");
                $checkIntentStmt->bind_param("s", $reference);
                $checkIntentStmt->execute();
                $intentCheck = $checkIntentStmt->get_result()->fetch_assoc();
                $checkIntentStmt->close();
                
                $rawData = json_encode($paymentData);
                
                if ($intentCheck) {
                    // UPDATE existing pending payment record
                    if ($intentCheck['payment_status'] === 'completed') {
                        // Already completed - this is a duplicate callback
                        error_log("SECURITY: Duplicate callback for already completed payment - Reference: $reference, IP: " . ($_SERVER['REMOTE_ADDR'] ?? 'unknown'));
                        $conn->commit();
                        
                        // Redirect based on user role
                        $userCheckStmt = $conn->prepare("SELECT role, is_store_owner FROM agents WHERE id = ?");
                        $userCheckStmt->bind_param("i", $userId);
                        $userCheckStmt->execute();
                        $userCheckResult = $userCheckStmt->get_result();
                        $userCheckData = $userCheckResult->fetch_assoc();
                        $userCheckStmt->close();
                        
                        if ($userCheckData && ($userCheckData['role'] === 'store_owner' || $userCheckData['is_store_owner'] == 1)) {
                            header('Location: pages/store_topup.php?topup=already_processed&ref=' . urlencode($reference));
                        } else {
                            header('Location: user_index.php?topup=already_processed&ref=' . urlencode($reference));
                        }
                        exit;
                    }
                    
                    // Update existing pending payment to completed
                    $updatePayment = $conn->prepare("
                        UPDATE orders_payments 
                        SET payment_status = 'completed', 
                            external_transaction_id = ?,
                            raw_response_data = ?,
                            payment_date = NOW()
                        WHERE payment_reference = ? AND payment_status = 'pending'
                    ");
                    if (!$updatePayment) {
                        throw new Exception("Database error: Failed to prepare payment update");
                    }
                    
                    $updatePayment->bind_param("sss", $externalTxnId, $rawData, $reference);
                    
                    if (!$updatePayment->execute()) {
                        $errorMsg = $updatePayment->error;
                        $updatePayment->close();
                        error_log("DATABASE ERROR: Failed to update payment record. Error: $errorMsg");
                        throw new Exception("Failed to update payment record - database error: $errorMsg");
                    }
                    
                    $affectedRows = $updatePayment->affected_rows;
                    $updatePayment->close();
                    
                    if ($affectedRows === 0) {
                        // Payment was already updated by another callback
                        error_log("SECURITY: Payment already updated by concurrent callback - Reference: $reference, IP: " . ($_SERVER['REMOTE_ADDR'] ?? 'unknown'));
                        $conn->commit();
                        
                        // Redirect based on user role
                        $userCheckStmt = $conn->prepare("SELECT role, is_store_owner FROM agents WHERE id = ?");
                        $userCheckStmt->bind_param("i", $userId);
                        $userCheckStmt->execute();
                        $userCheckResult = $userCheckStmt->get_result();
                        $userCheckData = $userCheckResult->fetch_assoc();
                        $userCheckStmt->close();
                        
                        if ($userCheckData && ($userCheckData['role'] === 'store_owner' || $userCheckData['is_store_owner'] == 1)) {
                            header('Location: pages/store_topup.php?topup=already_processed&ref=' . urlencode($reference));
                        } else {
                            header('Location: user_index.php?topup=already_processed&ref=' . urlencode($reference));
                        }
                        exit;
                    }
                } else {
                    // INSERT new payment record (backward compatibility for payments without intent)
                    $insertPayment = $conn->prepare("
                        INSERT INTO orders_payments 
                        (user_id, total_amount, payment_method, payment_status, payment_reference, 
                         payment_type, external_transaction_id, raw_response_data, payment_date, created_at) 
                        VALUES (?, ?, 'paystack', 'completed', ?, 'topup', ?, ?, NOW(), NOW())
                    ");
                    if (!$insertPayment) {
                        throw new Exception("Database error: Failed to prepare payment insert");
                    }
                    
                    $insertPayment->bind_param("idsss", $userId, $amountGHS, $reference, $externalTxnId, $rawData);
                    
                    if (!$insertPayment->execute()) {
                        // Check if it's a duplicate key error (race condition)
                        $errorCode = $conn->errno;
                        $errorMsg = $conn->error;
                        
                        if ($errorCode == 1062) { // Duplicate entry error
                            error_log("DUPLICATE: Payment reference $reference already exists. Attempting update instead. Error: $errorMsg");
                            $insertPayment->close();
                            
                            // Try to update instead
                            $updatePayment = $conn->prepare("
                                UPDATE orders_payments 
                                SET payment_status = 'completed', 
                                    external_transaction_id = ?,
                                    raw_response_data = ?,
                                    payment_date = NOW()
                                WHERE payment_reference = ? AND payment_status = 'pending'
                            ");
                            if ($updatePayment) {
                                $updatePayment->bind_param("sss", $externalTxnId, $rawData, $reference);
                                if (!$updatePayment->execute() || $updatePayment->affected_rows === 0) {
                                    $updatePayment->close();
                                    throw new Exception("Payment already processed - duplicate reference detected");
                                }
                                $updatePayment->close();
                            } else {
                                throw new Exception("Payment already processed - duplicate reference detected");
                            }
                        } else {
                            // Other database error
                            error_log("DATABASE ERROR: Failed to insert payment record. Error Code: $errorCode, Message: $errorMsg");
                            $insertPayment->close();
                            throw new Exception("Failed to record payment - database error: $errorMsg");
                        }
                    } else {
                        $insertPayment->close();
                    }
                }
                
                // 1b. INSERT CHARGE AUDIT TRAIL (if charge was applied)
                if ($chargeAmount > 0) {
                    try {
                        $chargeType = $metadata['charge_type'] ?? 'unknown';
                        $insertChargeAudit = $conn->prepare("
                            INSERT INTO paystack_charge_audit 
                            (order_reference, user_id, base_amount, charge_amount, total_amount, charge_type, created_at) 
                            VALUES (?, ?, ?, ?, ?, ?, NOW())
                        ");
                        if ($insertChargeAudit) {
                            $insertChargeAudit->bind_param("siddds", $reference, $userId, $baseAmount, $chargeAmount, $totalAmountGHS, $chargeType);
                            $insertChargeAudit->execute();
                            $insertChargeAudit->close();
                        }
                    } catch (Exception $auditError) {
                        // Log but don't fail transaction - audit is not critical
                        error_log("Charge audit error for payment $reference: " . $auditError->getMessage());
                    }
                }
                
                // SECURITY: Check if this is a sub-agent top-up and update tracking BEFORE crediting
                $checkSubAgentTopupStmt = $conn->prepare("
                    SELECT id, store_id, store_owner_id, payment_status, base_amount, charge_amount, total_amount
                    FROM subagent_topup_tracking 
                    WHERE payment_reference = ? AND user_id = ?
                    LIMIT 1
                ");
                $checkSubAgentTopupStmt->bind_param("si", $reference, $userId);
                $checkSubAgentTopupStmt->execute();
                $subAgentTopupResult = $checkSubAgentTopupStmt->get_result();
                $subAgentTopup = $subAgentTopupResult->fetch_assoc();
                $checkSubAgentTopupStmt->close();
                
                $isSubAgentTopup = !empty($subAgentTopup);
                
                // 🔒 CRITICAL SECURITY: Verify amount matches exactly what was initiated
                if ($isSubAgentTopup) {
                    $originalBaseAmount = floatval($subAgentTopup['base_amount'] ?? 0);
                    $originalChargeAmount = floatval($subAgentTopup['charge_amount'] ?? 0);
                    $originalTotalAmount = floatval($subAgentTopup['total_amount'] ?? 0);
                    
                    // SECURITY: The amount to credit MUST match the original base_amount exactly
                    // This prevents manipulation where user tries to credit a different amount
                    if ($originalBaseAmount <= 0) {
                        error_log("SECURITY: Invalid base_amount in tracking record - Reference: $reference, User ID: $userId, Base Amount: $originalBaseAmount, IP: " . ($_SERVER['REMOTE_ADDR'] ?? 'unknown'));
                        throw new Exception("Invalid top-up record. Base amount is missing or invalid.");
                    }
                    
                    // SECURITY: Verify the amount being credited matches the original base_amount exactly
                    // Allow only tiny rounding differences (0.01 GHS) for floating point precision
                    $amountDifference = abs($amountGHS - $originalBaseAmount);
                    if ($amountDifference > 0.01) {
                        error_log("SECURITY: Amount manipulation detected - Reference: $reference, User ID: $userId, Original Base: GH₵ $originalBaseAmount, Attempted Credit: GH₵ $amountGHS, Difference: GH₵ $amountDifference, IP: " . ($_SERVER['REMOTE_ADDR'] ?? 'unknown'));
                        throw new Exception("SECURITY: Amount mismatch detected. The amount being credited (GH₵ " . number_format($amountGHS, 2) . ") does not match the original top-up amount (GH₵ " . number_format($originalBaseAmount, 2) . "). Transaction rejected.");
                    }
                    
                    // SECURITY: Use the original base_amount from tracking table (most secure)
                    // This ensures we credit exactly what was initiated, regardless of what Paystack metadata says
                    $amountGHS = $originalBaseAmount;
                    
                    error_log("SECURITY: Amount verified - Reference: $reference, Original Base: GH₵ $originalBaseAmount, Original Charge: GH₵ $originalChargeAmount, Original Total: GH₵ $originalTotalAmount, Crediting: GH₵ $amountGHS");
                }
                
                // SECURITY: Update sub-agent top-up tracking BEFORE crediting account
                if ($isSubAgentTopup) {
                    if ($subAgentTopup['payment_status'] === 'completed') {
                        // Already processed - duplicate callback
                        error_log("SECURITY: Duplicate callback for sub-agent top-up - Reference: $reference, User ID: $userId, IP: " . ($_SERVER['REMOTE_ADDR'] ?? 'unknown'));
                        $conn->commit();
                        
                        // Redirect based on user role
                        $userCheckStmt = $conn->prepare("SELECT role, is_store_owner, store_id FROM agents WHERE id = ?");
                        $userCheckStmt->bind_param("i", $userId);
                        $userCheckStmt->execute();
                        $userCheckResult = $userCheckStmt->get_result();
                        $userCheckData = $userCheckResult->fetch_assoc();
                        $userCheckStmt->close();
                        
                        if ($userCheckData && ($userCheckData['role'] === 'store_owner' || $userCheckData['is_store_owner'] == 1)) {
                            header('Location: pages/store_topup.php?topup=already_processed&ref=' . urlencode($reference));
                        } else if ($userCheckData && !empty($userCheckData['store_id'])) {
                            header('Location: pages/subagent_topup.php?topup=already_processed&ref=' . urlencode($reference));
                        } else {
                            header('Location: user_index.php?topup=already_processed&ref=' . urlencode($reference));
                        }
                        exit;
                    }
                    
                    // Update tracking record BEFORE crediting
                    $updateTrackingStmt = $conn->prepare("
                        UPDATE subagent_topup_tracking 
                        SET payment_status = 'processing',
                            external_transaction_id = ?,
                            raw_response_data = ?,
                            updated_at = NOW()
                        WHERE payment_reference = ? AND user_id = ? AND payment_status = 'pending'
                    ");
                    
                    if ($updateTrackingStmt) {
                        $updateTrackingStmt->bind_param("sssi", $externalTxnId, $rawData, $reference, $userId);
                        if (!$updateTrackingStmt->execute()) {
                            error_log("WARNING: Failed to update sub-agent top-up tracking - Reference: $reference, Error: " . $updateTrackingStmt->error);
                        }
                        $updateTrackingStmt->close();
                    }
                    
                    error_log("SECURITY: Sub-Agent Top-up Processing - Tracking ID: {$subAgentTopup['id']}, User ID: $userId, Store ID: {$subAgentTopup['store_id']}, Store Owner ID: {$subAgentTopup['store_owner_id']}, Reference: $reference, Amount: GH₵ $amountGHS");
                }
                
                // 2. UPDATE USER BALANCE (only if payment record was successfully inserted)
                $updateStmt = $conn->prepare("UPDATE agents SET balance = balance + ? WHERE id = ?");
                if (!$updateStmt) {
                    throw new Exception("Database error: Failed to prepare balance update");
                }
                $updateStmt->bind_param("di", $amountGHS, $userId);
                if (!$updateStmt->execute()) {
                    $errorMsg = $updateStmt->error;
                    $updateStmt->close();
                    
                    // SECURITY: Update tracking record to failed if balance update fails
                    if ($isSubAgentTopup) {
                        $updateTrackingFailedStmt = $conn->prepare("
                            UPDATE subagent_topup_tracking 
                            SET payment_status = 'failed',
                                notes = ?,
                                updated_at = NOW()
                            WHERE payment_reference = ? AND user_id = ?
                        ");
                        $errorNote = "Balance update failed: $errorMsg";
                        $updateTrackingFailedStmt->bind_param("ssi", $errorNote, $reference, $userId);
                        $updateTrackingFailedStmt->execute();
                        $updateTrackingFailedStmt->close();
                    }
                    
                    error_log("BALANCE UPDATE ERROR: Failed to update balance for user $userId. Error: $errorMsg");
                    
                    // Notify admins about balance update failure (outside transaction)
                    try {
                        $adminNotifier = new AdminNotificationManager($conn);
                        $adminTitle = "⚠️ Critical: Balance Update Failed - Paystack Topup";
                        $adminMessage = "Failed to credit user account after successful Paystack payment.\n\n" .
                                      "Payment Reference: $reference\n" .
                                      "User ID: $userId\n" .
                                      "User Email: $customerEmail\n" .
                                      "Amount to Credit: GHS " . number_format($amountGHS, 2) . "\n" .
                                      "Error: $errorMsg\n" .
                                      "Timestamp: " . date('Y-m-d H:i:s') . "\n\n" .
                                      "ACTION REQUIRED: Manual intervention needed to credit user account.";
                        $adminNotifier->notifyAdmins($adminTitle, $adminMessage, 'urgent');
                    } catch (Exception $notifEx) {
                        error_log("Failed to notify admins about balance update failure: " . $notifEx->getMessage());
                    }
                    
                    throw new Exception("Failed to update user balance: $errorMsg");
                }
                
                // Verify that balance was actually updated (check affected rows)
                $affectedRows = $updateStmt->affected_rows;
                $updateStmt->close();
                
                // SECURITY: Update sub-agent top-up tracking to completed AFTER successful balance credit
                if ($isSubAgentTopup && $affectedRows > 0) {
                    $updateTrackingCompletedStmt = $conn->prepare("
                        UPDATE subagent_topup_tracking 
                        SET payment_status = 'completed',
                            credited_at = NOW(),
                            updated_at = NOW()
                        WHERE payment_reference = ? AND user_id = ?
                    ");
                    $updateTrackingCompletedStmt->bind_param("si", $reference, $userId);
                    if (!$updateTrackingCompletedStmt->execute()) {
                        error_log("WARNING: Failed to update sub-agent top-up tracking to completed - Reference: $reference, Error: " . $updateTrackingCompletedStmt->error);
                    }
                    $updateTrackingCompletedStmt->close();
                    
                    error_log("SECURITY: Sub-Agent Top-up Completed - Tracking ID: {$subAgentTopup['id']}, User ID: $userId, Store ID: {$subAgentTopup['store_id']}, Amount Credited: GH₵ $amountGHS");
                }
                
                if ($affectedRows === 0) {
                    error_log("WARNING: Balance update returned 0 affected rows for user $userId. User may not exist or balance update failed silently.");
                    
                    // Notify admins about balance update failure (outside transaction)
                    try {
                        $adminNotifier = new AdminNotificationManager($conn);
                        $adminTitle = "⚠️ Critical: Balance Update Failed - No Rows Affected";
                        $adminMessage = "Balance UPDATE executed but affected 0 rows. User account was not credited.\n\n" .
                                      "Payment Reference: $reference\n" .
                                      "User ID: $userId\n" .
                                      "User Email: $customerEmail\n" .
                                      "Amount to Credit: GHS " . number_format($amountGHS, 2) . "\n" .
                                      "Current Balance: GHS " . number_format($currentBalance, 2) . "\n" .
                                      "Timestamp: " . date('Y-m-d H:i:s') . "\n\n" .
                                      "ACTION REQUIRED: Verify user exists and manually credit if needed.";
                        $adminNotifier->notifyAdmins($adminTitle, $adminMessage, 'urgent');
                    } catch (Exception $notifEx) {
                        error_log("Failed to notify admins about balance update failure: " . $notifEx->getMessage());
                    }
                    
                    throw new Exception("Balance update failed - no rows affected. User ID: $userId");
                }
                
                // SECURITY: Double-check: Verify the balance was actually credited by reading it back
                $verifyStmt = $conn->prepare("SELECT balance FROM agents WHERE id = ? FOR UPDATE");
                $verifyStmt->bind_param("i", $userId);
                $verifyStmt->execute();
                $verifyResult = $verifyStmt->get_result();
                $newBalanceData = $verifyResult->fetch_assoc();
                $verifyStmt->close();
                
                if (!$newBalanceData) {
                    error_log("CRITICAL: User $userId not found after balance update attempt! Reference: $reference");
                    throw new Exception("User verification failed after balance update");
                }
                
                $expectedBalance = $currentBalance + $amountGHS;
                $actualBalance = floatval($newBalanceData['balance']);
                
                // SECURITY: Allow small floating point differences (0.01 GHS tolerance)
                if (abs($actualBalance - $expectedBalance) > 0.01) {
                    error_log("CRITICAL: Balance mismatch! Expected: $expectedBalance, Actual: $actualBalance for user $userId, payment $reference, IP: " . ($_SERVER['REMOTE_ADDR'] ?? 'unknown'));
                    
                    // SECURITY: Update sub-agent top-up tracking to failed if balance verification fails
                    if (isset($isSubAgentTopup) && $isSubAgentTopup) {
                        $updateTrackingFailedStmt = $conn->prepare("
                            UPDATE subagent_topup_tracking 
                            SET payment_status = 'failed',
                                notes = ?,
                                updated_at = NOW()
                            WHERE payment_reference = ? AND user_id = ?
                        ");
                        $balanceErrorNote = "Balance verification failed - expected $expectedBalance but got $actualBalance";
                        $updateTrackingFailedStmt->bind_param("ssi", $balanceErrorNote, $reference, $userId);
                        $updateTrackingFailedStmt->execute();
                        $updateTrackingFailedStmt->close();
                    }
                    
                    // SECURITY: Rollback transaction if balance doesn't match
                    $conn->rollback();
                    
                    // Notify admins about balance verification failure
                    try {
                        $adminNotifier = new AdminNotificationManager($conn);
                        $adminTitle = "🚨 CRITICAL: Balance Verification Failed - Mismatch Detected";
                        $adminMessage = "Balance verification failed after payment processing. Transaction rolled back!\n\n" .
                                      "Payment Reference: $reference\n" .
                                      "User ID: $userId\n" .
                                      "User Email: $customerEmail\n" .
                                      "Amount to Credit: GHS " . number_format($amountGHS, 2) . "\n" .
                                      "Previous Balance: GHS " . number_format($currentBalance, 2) . "\n" .
                                      "Expected Balance: GHS " . number_format($expectedBalance, 2) . "\n" .
                                      "Actual Balance: GHS " . number_format($actualBalance, 2) . "\n" .
                                      "Difference: GHS " . number_format(abs($actualBalance - $expectedBalance), 2) . "\n" .
                                      "IP Address: " . ($_SERVER['REMOTE_ADDR'] ?? 'unknown') . "\n" .
                                      "Timestamp: " . date('Y-m-d H:i:s') . "\n\n" .
                                      "ACTION REQUIRED: Investigate immediately. Transaction was rolled back to prevent balance corruption.";
                        $adminNotifier->notifyAdmins($adminTitle, $adminMessage, 'urgent');
                    } catch (Exception $notifEx) {
                        error_log("Failed to notify admins about balance verification failure: " . $notifEx->getMessage());
                    }
                    
                    throw new Exception("Balance verification failed - expected $expectedBalance but got $actualBalance. Transaction rolled back.");
                }
                
                // SECURITY: Log successful balance update for audit
                error_log("SECURITY: Balance verified successfully - User ID: $userId, Reference: $reference, Previous: GH₵ $currentBalance, Credited: GH₵ $amountGHS, New: GH₵ $actualBalance");
                
                // 3. LOG BALANCE CHANGE FOR AUDIT TRAIL
                BalanceHistory::log($conn, $userId, $amountGHS, 'topup', null, $reference, $currentBalance, $actualBalance);
                
                // 4. SEND NOTIFICATION (non-critical, don't fail transaction if this fails)
                try {
                    $notificationManager = new NotificationManager($conn);
                    $notificationData = [
                        'amount' => number_format($amountGHS, 2),
                        'balance' => number_format($currentBalance + $amountGHS, 2)
                    ];
                    $notificationManager->sendNotification($userId, 'balance_topup', $notificationData);
                } catch (Exception $notifError) {
                    // Log but don't fail the transaction
                    error_log("Notification error for payment $reference: " . $notifError->getMessage());
                }
                
                // 🔒 COMMIT TRANSACTION - All changes are atomic
                $conn->commit();
                
                // Log successful payment processing
                error_log("SUCCESS: Paystack payment processed - Ref: $reference, User: $userId, Amount: GHS $amountGHS");
                
                // Check if user is a store owner and redirect accordingly
                $checkStoreOwnerStmt = $conn->prepare("SELECT role, is_store_owner FROM agents WHERE id = ?");
                $checkStoreOwnerStmt->bind_param("i", $userId);
                $checkStoreOwnerStmt->execute();
                $storeOwnerResult = $checkStoreOwnerStmt->get_result();
                $storeOwnerData = $storeOwnerResult->fetch_assoc();
                $checkStoreOwnerStmt->close();
                
                if ($storeOwnerData && ($storeOwnerData['role'] === 'store_owner' || $storeOwnerData['is_store_owner'] == 1)) {
                    // Redirect store owner to store dashboard
                    header('Location: pages/store_topup.php?topup=success&amount=' . $amountGHS);
                } else {
                    // Check if user is a sub-agent (has store_id)
                    $userStoreStmt = $conn->prepare("SELECT store_id FROM agents WHERE id = ?");
                    $userStoreStmt->bind_param("i", $userId);
                    $userStoreStmt->execute();
                    $userStoreResult = $userStoreStmt->get_result();
                    $userStoreData = $userStoreResult->fetch_assoc();
                    $userStoreStmt->close();
                    
                    if ($userStoreData && !empty($userStoreData['store_id'])) {
                        // Sub-agent - redirect to sub-agent top-up page
                        header('Location: pages/subagent_topup.php?topup=success&amount=' . $amountGHS);
                    } else {
                        // Regular user
                        header('Location: user_index.php?topup=success&amount=' . $amountGHS);
                    }
                }
                exit;
                
            } else {
                // User not found after all attempts
                $conn->rollback();
                error_log("ERROR: User not found for email: $customerEmail (all search methods failed), Reference: $reference");
                
                // Notify admins about user not found (critical issue - payment verified but user doesn't exist)
                try {
                    $adminNotifier = new AdminNotificationManager($conn);
                    $adminTitle = "⚠️ Critical: User Not Found - Paystack Payment";
                    $adminMessage = "Paystack payment was verified but user account not found in database.\n\n" .
                                  "Payment Reference: $reference\n" .
                                  "Customer Email: $customerEmail\n" .
                                  "Amount Paid: GHS " . number_format($totalAmountGHS, 2) . "\n" .
                                  "Timestamp: " . date('Y-m-d H:i:s') . "\n\n" .
                                  "ACTION REQUIRED: Create user account or verify email address. Payment may need to be refunded if user doesn't exist.";
                    $adminNotifier->notifyAdmins($adminTitle, $adminMessage, 'urgent');
                } catch (Exception $notifEx) {
                    error_log("Failed to notify admins about user not found: " . $notifEx->getMessage());
                }
                
                // Show error page
                http_response_code(404);
                echo "<!DOCTYPE html><html><head><title>User Not Found</title></head>";
                echo "<body style='font-family:Arial;text-align:center;padding:50px;'>";
                echo "<h2>⚠️ Payment Verified but User Not Found</h2>";
                echo "<p>Reference: " . htmlspecialchars($reference) . "</p>";
                echo "<p>Email: " . htmlspecialchars($customerEmail) . "</p>";
                echo "<p>Please contact support with this reference number.</p>";
                echo "</body></html>";
                exit;
            }
            
        } catch (Exception $e) {
            // 🔒 ROLLBACK TRANSACTION on any error
            $conn->rollback();
            
            // SECURITY: Update sub-agent top-up tracking to failed if there's an error
            $checkErrorTopupStmt = $conn->prepare("
                SELECT id, user_id, store_id, store_owner_id 
                FROM subagent_topup_tracking 
                WHERE payment_reference = ? AND payment_status IN ('pending', 'processing')
                LIMIT 1
            ");
            $checkErrorTopupStmt->bind_param("s", $reference);
            $checkErrorTopupStmt->execute();
            $errorTopupResult = $checkErrorTopupStmt->get_result();
            $errorTopup = $errorTopupResult->fetch_assoc();
            $checkErrorTopupStmt->close();
            
            if ($errorTopup) {
                $errorNote = "Payment processing failed: " . $e->getMessage();
                $updateErrorTrackingStmt = $conn->prepare("
                    UPDATE subagent_topup_tracking 
                    SET payment_status = 'failed',
                        notes = ?,
                        updated_at = NOW()
                    WHERE payment_reference = ? AND payment_status IN ('pending', 'processing')
                ");
                $updateErrorTrackingStmt->bind_param("ss", $errorNote, $reference);
                $updateErrorTrackingStmt->execute();
                $updateErrorTrackingStmt->close();
                
                error_log("SECURITY: Sub-Agent Top-up Failed (Exception) - Tracking ID: {$errorTopup['id']}, User ID: {$errorTopup['user_id']}, Store ID: {$errorTopup['store_id']}, Reference: $reference, Error: " . $e->getMessage());
            }
            
            // Log the error with comprehensive details for debugging
            $errorDetails = [
                'reference' => $reference,
                'error_message' => $e->getMessage(),
                'error_code' => $conn->errno ?? 'N/A',
                'sql_error' => $conn->error ?? 'N/A',
                'customer_email' => $customerEmail ?? 'N/A',
                'amount' => $amountGHS ?? 'N/A',
                'ip_address' => $_SERVER['REMOTE_ADDR'] ?? 'unknown',
                'user_agent' => $_SERVER['HTTP_USER_AGENT'] ?? 'unknown',
                'timestamp' => date('Y-m-d H:i:s')
            ];
            error_log("CRITICAL: Paystack callback error - " . json_encode($errorDetails));
            
            // Notify admins about payment processing failure (only for critical errors, not duplicates)
            // Skip notification for duplicate payment attempts (already processed)
            $isDuplicate = strpos($e->getMessage(), 'Duplicate entry') !== false || 
                          strpos($e->getMessage(), 'already processed') !== false ||
                          strpos($e->getMessage(), 'duplicate reference') !== false;
            
            if (!$isDuplicate) {
                try {
                    $adminNotifier = new AdminNotificationManager($conn);
                    $adminTitle = "🚨 CRITICAL: Paystack Payment Processing Failed";
                    $adminMessage = "Payment was verified by Paystack but failed during processing. Transaction rolled back.\n\n" .
                                  "Payment Reference: $reference\n" .
                                  "Customer Email: " . ($customerEmail ?? 'N/A') . "\n" .
                                  "Amount: GHS " . ($amountGHS ?? 'N/A') . "\n" .
                                  "Error: " . $e->getMessage() . "\n" .
                                  "Error Code: " . ($conn->errno ?? 'N/A') . "\n" .
                                  "SQL Error: " . ($conn->error ?? 'N/A') . "\n" .
                                  "IP Address: " . ($_SERVER['REMOTE_ADDR'] ?? 'unknown') . "\n" .
                                  "Timestamp: " . date('Y-m-d H:i:s') . "\n\n" .
                                  "ACTION REQUIRED: Investigate and manually process if payment was successful.";
                    $adminNotifier->notifyAdmins($adminTitle, $adminMessage, 'urgent');
                } catch (Exception $notifEx) {
                    error_log("Failed to notify admins about payment processing failure: " . $notifEx->getMessage());
                }
            }
            
            // Check if it's a duplicate key error or already processed
            if (strpos($e->getMessage(), 'Duplicate entry') !== false || 
                strpos($e->getMessage(), 'already processed') !== false ||
                strpos($e->getMessage(), 'duplicate reference') !== false) {
                // This is a duplicate attempt - check if user is store owner and redirect accordingly
                if (isset($userId)) {
                    $checkStoreOwnerStmt = $conn->prepare("SELECT role, is_store_owner FROM agents WHERE id = ?");
                    $checkStoreOwnerStmt->bind_param("i", $userId);
                    $checkStoreOwnerStmt->execute();
                    $storeOwnerResult = $checkStoreOwnerStmt->get_result();
                    $storeOwnerData = $storeOwnerResult->fetch_assoc();
                    $checkStoreOwnerStmt->close();
                    
                    if ($storeOwnerData && ($storeOwnerData['role'] === 'store_owner' || $storeOwnerData['is_store_owner'] == 1)) {
                        header('Location: pages/store_topup.php?topup=already_processed&ref=' . urlencode($reference));
                    } else {
                        header('Location: user_index.php?topup=already_processed&ref=' . urlencode($reference));
                    }
                } else {
                    header('Location: user_index.php?topup=already_processed&ref=' . urlencode($reference));
                }
                exit;
            }
            
            // Check if user was not found
            if (strpos($e->getMessage(), 'User not found') !== false || 
                strpos($e->getMessage(), 'User verification failed') !== false) {
                http_response_code(404);
                echo "<!DOCTYPE html><html><head><title>User Not Found</title></head>";
                echo "<body style='font-family:Arial;text-align:center;padding:50px;'>";
                echo "<h2>⚠️ Payment Verified but User Not Found</h2>";
                echo "<p>Reference: " . htmlspecialchars($reference) . "</p>";
                echo "<p>Email: " . htmlspecialchars($customerEmail ?? 'N/A') . "</p>";
                echo "<p>Please contact support with this reference number.</p>";
                echo "</body></html>";
                exit;
            }
            
            // Show error page for other errors
            http_response_code(500);
            echo "<!DOCTYPE html><html><head><title>Payment Processing Error</title></head>";
            echo "<body style='font-family:Arial;text-align:center;padding:50px;'>";
            echo "<h2>⚠️ Payment Processing Error</h2>";
            echo "<p>Your payment was verified by Paystack but encountered an error during processing.</p>";
            echo "<p><strong>Reference:</strong> " . htmlspecialchars($reference) . "</p>";
            echo "<p><strong>Error:</strong> " . htmlspecialchars($e->getMessage()) . "</p>";
            echo "<p>Please contact support with this reference number. Do not retry the payment.</p>";
            echo "<p><small>Error logged at: " . date('Y-m-d H:i:s') . "</small></p>";
            echo "</body></html>";
            exit;
        }
    }
}

// Show verification status if not successful or user not found
?>
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Payment <?php echo $ok ? 'Successful' : 'Verification'; ?></title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <script src="https://cdn.tailwindcss.com"></script>
</head>
<body class="bg-[#0f172a] text-white min-h-screen flex items-center justify-center p-6">
    <div class="bg-[#111827] border border-white/10 rounded-2xl p-8 max-w-md text-center">
        <div class="w-16 h-16 mx-auto mb-4 rounded-full flex items-center justify-center <?php echo $ok ? 'bg-green-500/20 text-green-300' : 'bg-yellow-500/20 text-yellow-300'; ?>">
            <i class="fas <?php echo $ok ? 'fa-check' : 'fa-clock'; ?> text-2xl"></i>
        </div>
        <h1 class="text-2xl font-bold mb-2"><?php echo $ok ? 'Payment Successful' : 'Payment Pending Verification'; ?></h1>
        <p class="text-gray-300 mb-6">
            Reference: <span class="font-mono"><?php echo htmlspecialchars($reference); ?></span>
        </p>
        <a href="order-status.php" class="inline-flex items-center px-6 py-3 rounded-xl font-semibold bg-accent-500 text-black hover:bg-accent-400 transition-all">
            <i class="fas fa-search mr-2"></i>Check Order Status
        </a>
    </div>
</body>
</html>




