<?php
/**
 * Webhook endpoint for external API status updates
 * This endpoint receives status change notifications from external APIs
 * FIXED VERSION - Properly handles webhook integration with data/ website
 */

require_once '../../config.php';
require_once '../../app/classes/NotificationManager.php';
// Removed Composer autoloading for PHP 7.2 compatibility

// Set content type to JSON
header('Content-Type: application/json');

// Create detailed webhook log file
$webhook_log_file = '../../webhook_debug.log';

// Function to log webhook details
function logWebhookDetails($message, $data = null) {
    global $webhook_log_file;
    $timestamp = date('Y-m-d H:i:s');
    $log_entry = "[$timestamp] $message";
    if ($data !== null) {
        $log_entry .= " | Data: " . json_encode($data, JSON_PRETTY_PRINT);
    }
    $log_entry .= "\n" . str_repeat('-', 80) . "\n";
    file_put_contents($webhook_log_file, $log_entry, FILE_APPEND | LOCK_EX);
    error_log($log_entry);
}

// Log webhook access attempt
logWebhookDetails("=== WEBHOOK ACCESS ATTEMPT ===", [
    'method' => $_SERVER['REQUEST_METHOD'],
    'user_agent' => isset($_SERVER['HTTP_USER_AGENT']) ? $_SERVER['HTTP_USER_AGENT'] : 'Unknown',
    'remote_addr' => isset($_SERVER['REMOTE_ADDR']) ? $_SERVER['REMOTE_ADDR'] : 'Unknown',
    'http_host' => isset($_SERVER['HTTP_HOST']) ? $_SERVER['HTTP_HOST'] : 'Unknown',
    'request_uri' => isset($_SERVER['REQUEST_URI']) ? $_SERVER['REQUEST_URI'] : 'Unknown',
    'content_type' => isset($_SERVER['CONTENT_TYPE']) ? $_SERVER['CONTENT_TYPE'] : 'Unknown',
    'content_length' => isset($_SERVER['CONTENT_LENGTH']) ? $_SERVER['CONTENT_LENGTH'] : 'Unknown'
]);

// Only allow POST requests
if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    logWebhookDetails("METHOD NOT ALLOWED", [
        'received_method' => $_SERVER['REQUEST_METHOD'],
        'expected_method' => 'POST'
    ]);
    http_response_code(405);
    echo json_encode(['success' => false, 'message' => 'Method not allowed']);
    exit;
}

// Get the raw POST data
$raw_input = file_get_contents('php://input');

// Log raw input data
logWebhookDetails("RAW POST DATA RECEIVED", [
    'raw_input' => $raw_input,
    'input_length' => strlen($raw_input),
    'is_empty' => empty($raw_input)
]);

// Also log $_POST data if any
if (!empty($_POST)) {
    logWebhookDetails("POST DATA (form-encoded)", $_POST);
}

// Also log $_GET data if any
if (!empty($_GET)) {
    logWebhookDetails("GET DATA", $_GET);
}

// Log all headers
// Get headers manually for PHP 7.2 compatibility
$headers = array();
foreach ($_SERVER as $key => $value) {
    if (strpos($key, 'HTTP_') === 0) {
        $header_name = str_replace('_', '-', substr($key, 5));
        $headers[$header_name] = $value;
    }
}
logWebhookDetails("HTTP HEADERS", $headers);

// Try to decode JSON
$webhook_data = json_decode($raw_input, true);
$json_error = json_last_error();

logWebhookDetails("JSON DECODING RESULT", [
    'json_error_code' => $json_error,
    'json_error_message' => json_last_error_msg(),
    'decoded_data' => $webhook_data,
    'is_array' => is_array($webhook_data)
]);

// Validate webhook data
if (!$webhook_data || !is_array($webhook_data)) {
    logWebhookDetails("INVALID JSON DATA", [
        'raw_input' => $raw_input,
        'json_error' => json_last_error_msg()
    ]);
    http_response_code(400);
    echo json_encode(['success' => false, 'message' => 'Invalid JSON data']);
    exit;
}

// Extract required fields - FIXED: Handle multiple possible field names
$external_transaction_id = null;
if (isset($webhook_data['transaction_id'])) {
    $external_transaction_id = $webhook_data['transaction_id'];
} elseif (isset($webhook_data['external_transaction_id'])) {
    $external_transaction_id = $webhook_data['external_transaction_id'];
} elseif (isset($webhook_data['transactionId'])) {
    $external_transaction_id = $webhook_data['transactionId'];
} elseif (isset($webhook_data['reference'])) {
    $external_transaction_id = $webhook_data['reference'];
}

$new_status = isset($webhook_data['status']) ? $webhook_data['status'] : null;
$webhook_type = isset($webhook_data['type']) ? $webhook_data['type'] : 'status_update';

// Validate required fields
if (!$external_transaction_id || !$new_status) {
    logWebhookDetails("MISSING REQUIRED FIELDS", [
        'transaction_id' => $external_transaction_id,
        'status' => $new_status,
        'webhook_data' => $webhook_data
    ]);
    http_response_code(400);
    echo json_encode([
        'success' => false, 
        'message' => 'Missing required fields: transaction_id and status'
    ]);
    exit;
}

// Map external status to internal status - ENHANCED mapping
$status_mapping = [
    'completed' => 'delivered',
    'success' => 'delivered',
    'delivered' => 'delivered',
    'successful' => 'delivered',
    'failed' => 'failed',
    'error' => 'failed',
    'unsuccessful' => 'failed',
    'cancelled' => 'cancelled',
    'canceled' => 'cancelled',
    'refunded' => 'refunded',
    'pending' => 'processing',
    'processing' => 'processing',
    'in_progress' => 'processing',
    'submitted' => 'processing'
];

$internal_status = isset($status_mapping[strtolower($new_status)]) ? $status_mapping[strtolower($new_status)] : 'failed';

logWebhookDetails("STATUS MAPPING", [
    'external_status' => $new_status,
    'internal_status' => $internal_status,
    'transaction_id' => $external_transaction_id
]);

try {
    // Start transaction
    $conn->begin_transaction();
    
    logWebhookDetails("STARTING ORDER LOOKUP", [
        'transaction_id' => $external_transaction_id,
        'webhook_data' => $webhook_data
    ]);
    
    // IMPROVED ORDER LOOKUP LOGIC
    $order = null;
    $order_id = null;
    $old_status = null;
    $user_id = null;
    
    // Method 1: Find by external_transaction_id (exact match) - search both tables
    $order_found = false;
    $order_id = null;
    $old_status = null;
    $user_id = null;
    $table_name = null;
    
    // First, search in orders table
    $stmt = $conn->prepare("SELECT id, status, user_id FROM orders WHERE external_transaction_id = ?");
    $stmt->bind_param("s", $external_transaction_id);
    $stmt->execute();
    $result = $stmt->get_result();
    
    if ($result->num_rows > 0) {
        $order = $result->fetch_assoc();
        $order_id = $order['id'];
        $old_status = $order['status'];
        $user_id = $order['user_id'];
        $table_name = 'orders';
        $order_found = true;
    }
    $stmt->close();
    
    // If not found in orders table, search in agent_orders table
    if (!$order_found) {
        $stmt = $conn->prepare("SELECT id, status, agent_id FROM agent_orders WHERE external_transaction_id = ?");
        $stmt->bind_param("s", $external_transaction_id);
        $stmt->execute();
        $result = $stmt->get_result();
        
        if ($result->num_rows > 0) {
            $order = $result->fetch_assoc();
            $order_id = $order['id'];
            $old_status = $order['status'];
            $user_id = $order['agent_id']; // agent_id is the user_id for agent orders
            $table_name = 'agent_orders';
            $order_found = true;
        }
        $stmt->close();
    }
    
    // If still not found, search in workflow tables
    if (!$order_found) {
        $workflow_tables = [
            'pending_databundle_orders' => 'pending',
            'verified_databundle_orders' => 'verified', 
            'processing_databundle_orders' => 'processing',
            'completed_databundle_orders' => 'delivered'
        ];
        
        foreach ($workflow_tables as $table => $status) {
            $stmt = $conn->prepare("SELECT id, reference, momo_name, momo_number, package, amount_paid FROM $table WHERE external_transaction_id = ?");
            $stmt->bind_param("s", $external_transaction_id);
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($result->num_rows > 0) {
                $order = $result->fetch_assoc();
                $order_id = $order['id'];
                $old_status = $status;
                $user_id = null; // Workflow tables don't have user_id
                $table_name = $table;
                $order_found = true;
                $stmt->close();
                break;
            }
            $stmt->close();
        }
    }
    
    if ($order_found) {
        logWebhookDetails("FOUND ORDER BY EXTERNAL TRANSACTION ID", [
            'order_id' => $order_id,
            'external_transaction_id' => $external_transaction_id,
            'current_status' => $old_status,
            'user_id' => $user_id,
            'table' => $table_name
        ]);
    } else {
        // Method 2: Find by order details from webhook payload
        $phone_number = isset($webhook_data['phoneNumber']) ? $webhook_data['phoneNumber'] : '';
        $package_size = isset($webhook_data['packageSize']) ? $webhook_data['packageSize'] : '';
        $amount = isset($webhook_data['amount']) ? $webhook_data['amount'] : 0;
        $username = isset($webhook_data['username']) ? $webhook_data['username'] : '';
        
        logWebhookDetails("SEARCHING BY ORDER DETAILS", [
            'phone_number' => $phone_number,
            'package_size' => $package_size,
            'amount' => $amount,
            'username' => $username
        ]);
        
        if ($phone_number && $package_size && $amount && $username) {
            // First, search in orders table
            $stmt = $conn->prepare("
                SELECT o.id, o.status, o.user_id, o.external_transaction_id
                FROM orders o 
                JOIN agents a ON o.user_id = a.id 
                WHERE o.beneficiary_number = ? 
                AND o.package_size = ? 
                AND o.amount = ? 
                AND a.username = ?
                ORDER BY o.created_at DESC 
                LIMIT 1
            ");
            $stmt->bind_param("ssds", $phone_number, $package_size, $amount, $username);
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($result->num_rows > 0) {
                $order = $result->fetch_assoc();
                $order_id = $order['id'];
                $old_status = $order['status'];
                $user_id = $order['user_id'];
                $table_name = 'orders';
                
                // Update the order with the external_transaction_id if it doesn't have one
                if (empty($order['external_transaction_id'])) {
                    $update_stmt = $conn->prepare("UPDATE orders SET external_transaction_id = ? WHERE id = ?");
                    $update_stmt->bind_param("si", $external_transaction_id, $order_id);
                    $update_stmt->execute();
                    $update_stmt->close();
                    
                    logWebhookDetails("UPDATED ORDER WITH EXTERNAL TRANSACTION ID", [
                        'order_id' => $order_id,
                        'external_transaction_id' => $external_transaction_id
                    ]);
                }
                
                logWebhookDetails("FOUND ORDER BY DETAILS", [
                    'order_id' => $order_id,
                    'current_status' => $old_status,
                    'user_id' => $user_id,
                    'external_transaction_id' => $order['external_transaction_id'],
                    'table' => $table_name
                ]);
            } else {
                // If not found in orders table, search in agent_orders table
                $stmt = $conn->prepare("
                    SELECT ao.id, ao.status, ao.agent_id, ao.external_transaction_id
                    FROM agent_orders ao 
                    JOIN agents a ON ao.agent_id = a.id 
                    WHERE ao.beneficiary_number = ? 
                    AND ao.package_size = ? 
                    AND ao.amount = ? 
                    AND a.username = ?
                    ORDER BY ao.created_at DESC 
                    LIMIT 1
                ");
                $stmt->bind_param("ssds", $phone_number, $package_size, $amount, $username);
                $stmt->execute();
                $result = $stmt->get_result();
                
                if ($result->num_rows > 0) {
                    $order = $result->fetch_assoc();
                    $order_id = $order['id'];
                    $old_status = $order['status'];
                    $user_id = $order['agent_id'];
                    $table_name = 'agent_orders';
                    
                    // Update the order with the external_transaction_id if it doesn't have one
                    if (empty($order['external_transaction_id'])) {
                        $update_stmt = $conn->prepare("UPDATE agent_orders SET external_transaction_id = ? WHERE id = ?");
                        $update_stmt->bind_param("si", $external_transaction_id, $order_id);
                        $update_stmt->execute();
                        $update_stmt->close();
                        
                        logWebhookDetails("UPDATED AGENT ORDER WITH EXTERNAL TRANSACTION ID", [
                            'order_id' => $order_id,
                            'external_transaction_id' => $external_transaction_id
                        ]);
                    }
                    
                    logWebhookDetails("FOUND AGENT ORDER BY DETAILS", [
                        'order_id' => $order_id,
                        'current_status' => $old_status,
                        'user_id' => $user_id,
                        'external_transaction_id' => $order['external_transaction_id'],
                        'table' => $table_name
                    ]);
                }
            }
            $stmt->close();
        }
    }
    
    // If order still not found, create new order
    if (!$order) {
        logWebhookDetails("ORDER NOT FOUND - ATTEMPTING TO CREATE", [
            'external_transaction_id' => $external_transaction_id,
            'webhook_data' => $webhook_data
        ]);
        
        // Extract order details from webhook payload
        $phone_number = isset($webhook_data['phoneNumber']) ? $webhook_data['phoneNumber'] : '';
        $package_size = isset($webhook_data['packageSize']) ? $webhook_data['packageSize'] : '';
        $amount = isset($webhook_data['amount']) ? $webhook_data['amount'] : 0;
        $username = isset($webhook_data['username']) ? $webhook_data['username'] : '';
        $unique_id = isset($webhook_data['uniqueID']) ? $webhook_data['uniqueID'] : '';
        $created_at = isset($webhook_data['createdAt']) ? $webhook_data['createdAt'] : date('Y-m-d H:i:s');
        
        logWebhookDetails("EXTRACTED ORDER DETAILS FOR CREATION", [
            'phone_number' => $phone_number,
            'package_size' => $package_size,
            'amount' => $amount,
            'username' => $username,
            'unique_id' => $unique_id,
            'created_at' => $created_at,
            'new_status' => $new_status
        ]);
        
        // Find or create the user in DATADASH
        $user_id = null;
        
        if ($username && $unique_id) {
            // Try to find user by username and uniqueID
            $user_query = "SELECT id FROM agents WHERE username = ?";
            $user_stmt = $conn->prepare($user_query);
            $user_stmt->bind_param("s", $username);
            $user_stmt->execute();
            $user_result = $user_stmt->get_result();
            
            if ($user_result->num_rows > 0) {
                $user = $user_result->fetch_assoc();
                $user_id = $user['id'];
                logWebhookDetails("FOUND EXISTING USER", [
                    'user_id' => $user_id,
                    'username' => $username,
                    'unique_id' => $unique_id
                ]);
            } else {
                // Try to find user by username only (case insensitive)
                $user_query2 = "SELECT id FROM agents WHERE LOWER(username) = LOWER(?)";
                $user_stmt2 = $conn->prepare($user_query2);
                $user_stmt2->bind_param("s", $username);
                $user_stmt2->execute();
                $user_result2 = $user_stmt2->get_result();
                
                if ($user_result2->num_rows > 0) {
                    $user = $user_result2->fetch_assoc();
                    $user_id = $user['id'];
                    logWebhookDetails("FOUND USER BY USERNAME ONLY", [
                        'user_id' => $user_id,
                        'username' => $username
                    ]);
                } else {
                    // Create the user if they don't exist
                    $create_user = "INSERT INTO agents (username, uniqueID, role, status) VALUES (?, ?, 'agent', 'active')";
                    $create_stmt = $conn->prepare($create_user);
                    $create_stmt->bind_param("ss", $username, $unique_id);
                    
                    if ($create_stmt->execute()) {
                        $user_id = $conn->insert_id;
                        logWebhookDetails("CREATED NEW USER", [
                            'user_id' => $user_id, 
                            'username' => $username,
                            'unique_id' => $unique_id
                        ]);
                    } else {
                        $error_message = $create_stmt->error;
                        logWebhookDetails("FAILED TO CREATE USER", [
                            'error' => $error_message,
                            'username' => $username,
                            'unique_id' => $unique_id
                        ]);
                        $create_stmt->close();
                        $conn->rollback();
                        http_response_code(500);
                        echo json_encode(['success' => false, 'message' => 'Failed to create user: ' . $error_message]);
                        exit;
                    }
                    $create_stmt->close();
                }
                $user_stmt2->close();
            }
            $user_stmt->close();
        }
        
        // If we still don't have a user_id, we can't create the order
        if (!$user_id) {
            logWebhookDetails("CANNOT CREATE ORDER - NO USER ID", [
                'username' => $username,
                'unique_id' => $unique_id
            ]);
            $conn->rollback();
            http_response_code(400);
            echo json_encode(['success' => false, 'message' => 'Cannot create order: User information missing']);
            exit;
        }
        
        // Create the order with external_transaction_id
        $create_order = "INSERT INTO orders (user_id, package_size, amount, beneficiary_number, status, created_at, external_transaction_id, network_type) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        $create_stmt = $conn->prepare($create_order);
        $network_type = 'mtn'; // Default network type
        $create_stmt->bind_param("isssssss", $user_id, $package_size, $amount, $phone_number, $internal_status, $created_at, $external_transaction_id, $network_type);
        
        if ($create_stmt->execute()) {
            $order_id = $conn->insert_id;
            $old_status = $internal_status; // New order starts with the status from webhook
            
            logWebhookDetails("CREATED NEW ORDER", [
                'order_id' => $order_id,
                'user_id' => $user_id,
                'status' => $internal_status,
                'external_transaction_id' => $external_transaction_id
            ]);
            $create_stmt->close();
        } else {
            $error_message = $create_stmt->error;
            logWebhookDetails("FAILED TO CREATE ORDER", [
                'error' => $error_message,
                'user_id' => $user_id,
                'package_size' => $package_size,
                'amount' => $amount,
                'phone_number' => $phone_number,
                'status' => $internal_status
            ]);
            $create_stmt->close();
            $conn->rollback();
            http_response_code(500);
            echo json_encode(['success' => false, 'message' => 'Failed to create order: ' . $error_message]);
            exit;
        }
    }
    
    // CRITICAL: Prevent webhooks from resetting processed orders back to pending
    // Once an order is in 'processing', 'completed', or 'failed' state, it should never go back to 'pending'
    // This prevents the Process/Cancel buttons from reappearing after an order has been processed
    $final_states = ['processing', 'completed', 'failed', 'delivered', 'cancelled'];
    if (in_array(strtolower($old_status), $final_states) && strtolower($internal_status) === 'pending') {
        logWebhookDetails("WEBHOOK BLOCKED - Status reset prevented", [
            'order_id' => $order_id,
            'old_status' => $old_status,
            'attempted_status' => $internal_status,
            'reason' => 'Order already processed, cannot reset to pending'
        ]);
        
        // Log webhook attempt (but don't process it)
        $log_stmt = $conn->prepare("INSERT INTO webhook_logs (order_id, external_transaction_id, webhook_type, payload, response_status, processed) VALUES (?, ?, ?, ?, ?, 0)");
        $log_stmt->bind_param("issss", $order_id, $external_transaction_id, $webhook_type, $raw_input, $old_status);
        $log_stmt->execute();
        $log_stmt->close();
        
        $conn->commit();
        echo json_encode([
            'success' => true, 
            'message' => 'Order already processed, status reset blocked',
            'order_id' => $order_id,
            'current_status' => $old_status,
            'blocked_status' => $internal_status
        ]);
        exit;
    }
    
    // Check if status actually changed
    if ($old_status === $internal_status) {
        // Status hasn't changed, just log the webhook
        logWebhookDetails("STATUS UNCHANGED", [
            'order_id' => $order_id,
            'status' => $internal_status
        ]);
        
        // Log webhook attempt
        $log_stmt = $conn->prepare("INSERT INTO webhook_logs (order_id, external_transaction_id, webhook_type, payload, response_status, processed) VALUES (?, ?, ?, ?, ?, 1)");
        $log_stmt->bind_param("issss", $order_id, $external_transaction_id, $webhook_type, $raw_input, $internal_status);
        $log_stmt->execute();
        $log_stmt->close();
        
        $conn->commit();
        echo json_encode(['success' => true, 'message' => 'Status unchanged']);
        exit;
    }
    
    // Update order status in the correct table
    if ($table_name === 'agent_orders') {
        $update_stmt = $conn->prepare("UPDATE agent_orders SET status = ?, status_updated_at = NOW(), last_status_check = NOW(), api_response_data = ? WHERE id = ?");
        $update_stmt->bind_param("ssi", $internal_status, $raw_input, $order_id);
        $update_stmt->execute();
        $update_stmt->close();
    } elseif (in_array($table_name, ['pending_databundle_orders', 'verified_databundle_orders', 'processing_databundle_orders', 'completed_databundle_orders'])) {
        // For workflow tables, we need to move the order to the appropriate table based on status
        $target_table = null;
        switch ($internal_status) {
            case 'pending':
                $target_table = 'pending_databundle_orders';
                break;
            case 'verified':
                $target_table = 'verified_databundle_orders';
                break;
            case 'processing':
                $target_table = 'processing_databundle_orders';
                break;
            case 'delivered':
            case 'completed':
                $target_table = 'completed_databundle_orders';
                break;
            case 'failed':
            case 'cancelled':
                // Keep in current table but update status
                $target_table = $table_name;
                break;
        }
        
        if ($target_table && $target_table !== $table_name) {
            // Move order to new table
            $move_stmt = $conn->prepare("INSERT INTO $target_table SELECT * FROM $table_name WHERE id = ?");
            $move_stmt->bind_param("i", $order_id);
            $move_stmt->execute();
            $move_stmt->close();
            
            // Delete from old table
            $delete_stmt = $conn->prepare("DELETE FROM $table_name WHERE id = ?");
            $delete_stmt->bind_param("i", $order_id);
            $delete_stmt->execute();
            $delete_stmt->close();
            
            logWebhookDetails("ORDER MOVED TO WORKFLOW TABLE", [
                'order_id' => $order_id,
                'from_table' => $table_name,
                'to_table' => $target_table,
                'new_status' => $internal_status
            ]);
        } else {
            // Just update status in current table
            $update_stmt = $conn->prepare("UPDATE $table_name SET api_response_data = ? WHERE id = ?");
            $update_stmt->bind_param("si", $raw_input, $order_id);
            $update_stmt->execute();
            $update_stmt->close();
        }
    } else {
        // Regular orders table
        $update_stmt = $conn->prepare("UPDATE orders SET status = ?, status_updated_at = NOW(), last_status_check = NOW(), api_response_data = ? WHERE id = ?");
        $update_stmt->bind_param("ssi", $internal_status, $raw_input, $order_id);
        $update_stmt->execute();
        $update_stmt->close();
    }
    
    logWebhookDetails("ORDER STATUS UPDATED", [
        'order_id' => $order_id,
        'old_status' => $old_status,
        'new_status' => $internal_status
    ]);
    
    // Log status change in history (if table exists)
    $history_table_exists = false;
    $result = mysqli_query($conn, "SHOW TABLES LIKE 'order_status_history'");
    if ($result && mysqli_num_rows($result) > 0) {
        $history_table_exists = true;
    }
    
    if ($history_table_exists) {
        $history_stmt = $conn->prepare("INSERT INTO order_status_history (order_id, old_status, new_status, change_reason, changed_by, external_data) VALUES (?, ?, ?, ?, 'webhook', ?)");
        $reason = "Status updated via webhook from external API";
        $history_stmt->bind_param("issss", $order_id, $old_status, $internal_status, $reason, $raw_input);
        $history_stmt->execute();
        $history_stmt->close();
        
        logWebhookDetails("STATUS HISTORY LOGGED", [
            'order_id' => $order_id,
            'old_status' => $old_status,
            'new_status' => $internal_status
        ]);
    }
    
    // Send SMS alert to admins if status changed to 'failed'
    if ($internal_status === 'failed' && $old_status !== 'failed') {
        try {
            require_once __DIR__ . '/../../app/classes/AdminNotificationManager.php';
            $adminNotifier = new AdminNotificationManager($conn);
            
            // Get order details for SMS
            $orderDetails = [];
            if ($table_name === 'agent_orders') {
                $detailStmt = $conn->prepare("SELECT beneficiary_number, package_size, network_type FROM agent_orders WHERE id = ?");
                $detailStmt->bind_param("i", $order_id);
                $detailStmt->execute();
                $detailResult = $detailStmt->get_result();
                if ($detailRow = $detailResult->fetch_assoc()) {
                    $orderDetails = [
                        'beneficiary_number' => $detailRow['beneficiary_number'] ?? 'N/A',
                        'package_size' => $detailRow['package_size'] ?? 'N/A',
                        'network_type' => $detailRow['network_type'] ?? 'N/A',
                        'error' => 'Status changed to failed via webhook'
                    ];
                }
                $detailStmt->close();
            } else {
                $detailStmt = $conn->prepare("SELECT beneficiary_number, package_size, network_type FROM orders WHERE id = ?");
                $detailStmt->bind_param("i", $order_id);
                $detailStmt->execute();
                $detailResult = $detailStmt->get_result();
                if ($detailRow = $detailResult->fetch_assoc()) {
                    $orderDetails = [
                        'beneficiary_number' => $detailRow['beneficiary_number'] ?? 'N/A',
                        'package_size' => $detailRow['package_size'] ?? 'N/A',
                        'network_type' => $detailRow['network_type'] ?? 'N/A',
                        'error' => 'Status changed to failed via webhook'
                    ];
                }
                $detailStmt->close();
            }
            
            if (!empty($orderDetails)) {
                $adminNotifier->sendOrderFailedSMS($order_id, $orderDetails);
                logWebhookDetails("SMS alert sent to admins for failed order", ['order_id' => $order_id]);
            }
        } catch (Exception $e) {
            logWebhookDetails("Failed to send order failure SMS alert: " . $e->getMessage(), ['order_id' => $order_id]);
            error_log("Failed to send order failure SMS alert: " . $e->getMessage());
        }
    }
    
    // Log successful webhook processing
    $log_stmt = $conn->prepare("INSERT INTO webhook_logs (order_id, external_transaction_id, webhook_type, payload, response_status, processed) VALUES (?, ?, ?, ?, ?, 1)");
    $log_stmt->bind_param("issss", $order_id, $external_transaction_id, $webhook_type, $raw_input, $internal_status);
    $log_stmt->execute();
    $log_stmt->close();
    
    // If order is now completed or failed, remove from polling queue (if table exists)
    $polling_table_exists = false;
    $result = mysqli_query($conn, "SHOW TABLES LIKE 'api_polling_queue'");
    if ($result && mysqli_num_rows($result) > 0) {
        $polling_table_exists = true;
    }
    
    if ($polling_table_exists && in_array($internal_status, ['delivered', 'failed', 'cancelled', 'refunded'])) {
        $polling_stmt = $conn->prepare("UPDATE api_polling_queue SET status = 'completed' WHERE order_id = ?");
        $polling_stmt->bind_param("i", $order_id);
        $polling_stmt->execute();
        $polling_stmt->close();
        
        logWebhookDetails("POLLING QUEUE UPDATED", [
            'order_id' => $order_id,
            'status' => 'completed'
        ]);
    }
    
    // Send notification if order is delivered and user exists
    if ($internal_status === 'delivered' && $user_id) {
        try {
            $notificationManager = new NotificationManager($conn);
            
            // Get order details for notification
            $orderDetails = null;
            if ($table_name === 'orders') {
                $stmt = $conn->prepare("SELECT package_size, amount, beneficiary_number, network_type FROM orders WHERE id = ?");
                $stmt->bind_param("i", $order_id);
                $stmt->execute();
                $result = $stmt->get_result();
                if ($result->num_rows > 0) {
                    $orderDetails = $result->fetch_assoc();
                }
                $stmt->close();
            } elseif ($table_name === 'agent_orders') {
                $stmt = $conn->prepare("SELECT package_size, amount, beneficiary_number, network_type FROM agent_orders WHERE id = ?");
                $stmt->bind_param("i", $order_id);
                $stmt->execute();
                $result = $stmt->get_result();
                if ($result->num_rows > 0) {
                    $orderDetails = $result->fetch_assoc();
                }
                $stmt->close();
            }
            
            if ($orderDetails) {
                $notificationData = [
                    'package_size' => $orderDetails['package_size'],
                    'phone_number' => $orderDetails['beneficiary_number'],
                    'network' => $orderDetails['network_type'],
                    'amount' => number_format($orderDetails['amount'], 2)
                ];
                
                $notificationManager->sendNotification($user_id, 'order_success', $notificationData);
                
                logWebhookDetails("NOTIFICATION SENT", [
                    'order_id' => $order_id,
                    'user_id' => $user_id,
                    'notification_type' => 'order_success'
                ]);
            }
        } catch (Exception $e) {
            logWebhookDetails("NOTIFICATION FAILED", [
                'order_id' => $order_id,
                'user_id' => $user_id,
                'error' => $e->getMessage()
            ]);
        }
    }
    
    $conn->commit();
    
    // Log successful processing
    logWebhookDetails("WEBHOOK PROCESSED SUCCESSFULLY", [
        'order_id' => $order_id,
        'old_status' => $old_status,
        'new_status' => $internal_status,
        'transaction_id' => $external_transaction_id
    ]);
    
    echo json_encode([
        'success' => true, 
        'message' => 'Status updated successfully',
        'order_id' => $order_id,
        'old_status' => $old_status,
        'new_status' => $internal_status,
        'transaction_id' => $external_transaction_id
    ]);
    
} catch (Exception $e) {
    $conn->rollback();
    
    $error_message = $e->getMessage();
    logWebhookDetails("WEBHOOK PROCESSING ERROR", [
        'error' => $error_message,
        'transaction_id' => $external_transaction_id,
        'webhook_data' => $webhook_data
    ]);
    
    // Log failed webhook
    $log_stmt = $conn->prepare("INSERT INTO webhook_logs (external_transaction_id, webhook_type, payload, processed, error_message) VALUES (?, ?, ?, 0, ?)");
    $log_stmt->bind_param("ssss", $external_transaction_id, $webhook_type, $raw_input, $error_message);
    $log_stmt->execute();
    $log_stmt->close();
    
    http_response_code(500);
    echo json_encode([
        'success' => false, 
        'message' => 'Internal server error',
        'error' => $error_message
    ]);
}
?>
