<?php
// create_order.php
require_once '../config.php';
require_once '../config_api.php';
require_once __DIR__ . '/../app/classes/BalanceHistory.php';
require_once __DIR__ . '/../app/classes/ExternalAPIService.php';
require_once __DIR__ . '/../app/classes/ApiKeyManager.php';
require_once __DIR__ . '/../app/classes/ExternalAPIPricingManager.php';
require_once __DIR__ . '/../app/classes/ExternalAPIOrderService.php';

use App\classes\BalanceHistory;
use App\classes\ExternalAPIPricingManager;
use App\classes\ExternalAPIOrderService;

date_default_timezone_set('GMT');
header('Content-Type: application/json; charset=utf-8');

// Initialize API key manager
$apiKeyManager = new ApiKeyManager($conn);
require_once __DIR__ . '/../app/classes/StorePricingManager.php';

// ------------------ Helpers ------------------
function respond($data, $code = 200) {
    http_response_code($code);
    echo json_encode($data);
    exit;
}

function validatePhone($phone) {
    $p = preg_replace('/[^0-9+]/', '', (string)$phone);
    $digits = preg_replace('/\D/', '', $p);
    return (strlen($digits) >= 9 && strlen($digits) <= 13) ? $digits : false;
}

function parsePackageSizeGb($raw) {
    if ($raw === null) return 0.0;
    $s = trim((string)$raw);
    if ($s === '') return 0.0;
    // MB specified
    if (stripos($s, 'MB') !== false) {
        $num = floatval(preg_replace('/[^0-9.]/', '', $s));
        return ($num > 0) ? round($num / 1024, 4) : 0.0;
    }
    // GB specified or just numeric
    if (stripos($s, 'GB') !== false) {
        $num = floatval(preg_replace('/[^0-9.]/', '', $s));
        return ($num > 0) ? round($num, 4) : 0.0;
    }
    // plain numeric
    $num = floatval(preg_replace('/[^0-9.]/', '', $s));
    return ($num > 0) ? round($num, 4) : 0.0;
}

function formatBundleLabel($gb) {
    // Make "5GB" or "1.5GB" nicely
    if (floor($gb) == $gb) return intval($gb) . 'GB';
    $str = rtrim(rtrim(sprintf('%.2f', $gb), '0'), '.');
    return $str . 'GB';
}


function detectNetworkType($phoneNumber) {
    $phone = preg_replace('/[^0-9]/', '', $phoneNumber);
    $prefix = substr($phone, 0, 3);
    
    $networkPrefixes = [
        'mtn' => ['024', '025', '053', '054', '055', '059'],
        'telecel' => ['020', '050'],
        'airtel_tigo' => ['027', '057', '026', '056', '023']
    ];
    
    foreach ($networkPrefixes as $network => $prefixes) {
        if (in_array($prefix, $prefixes)) {
            return $network;
        }
    }
    
    return null; // Return null if prefix not recognized
}

// Google Sheets integration removed - orders now go to database only

// ------------------ Accept input ------------------
$raw = file_get_contents('php://input');
$input = json_decode($raw, true);
if (!is_array($input)) {
    // fallback to form-data
    $input = $_POST;
}

if (!is_array($input)) {
    respond(['status' => 'error', 'message' => 'Invalid JSON input'], 400);
}

// ===========================================
// API KEY AUTHENTICATION
// ===========================================

// Get API key from headers or request body
$apiKey = null;
$apiSecret = null;

// Check Authorization header (Bearer token format)
$headers = getallheaders();
if (isset($headers['Authorization'])) {
    $authHeader = $headers['Authorization'];
    if (preg_match('/Bearer\s+(.+)/', $authHeader, $matches)) {
        $apiKey = $matches[1];
    }
}

// Check X-API-Key header
if (!$apiKey && isset($headers['X-API-Key'])) {
    $apiKey = $headers['X-API-Key'];
}

// Check request body for API key
if (!$apiKey) {
    $apiKey = $input['api_key'] ?? $input['apikey'] ?? null;
    $apiSecret = $input['api_secret'] ?? $input['apisecret'] ?? null;
}

if (!$apiKey) {
    respond(['status' => 'error', 'message' => 'API key required. Provide via Authorization header, X-API-Key header, or request body'], 401);
}

// Validate API key
$startTime = microtime(true);
$validation = $apiKeyManager->validateApiKey($apiKey, $apiSecret);
$responseTime = round((microtime(true) - $startTime) * 1000);

if (!$validation['valid']) {
    // Don't log failed authentication attempts with invalid API key IDs
    // This prevents foreign key constraint errors
    respond(['status' => 'error', 'message' => $validation['message']], 401);
}

// Check rate limit
$rateLimitCheck = $apiKeyManager->checkRateLimit($validation['key_data']['id'], 'create_order');
if (!$rateLimitCheck['allowed']) {
    respond(['status' => 'error', 'message' => $rateLimitCheck['message'], 'rate_limit' => $rateLimitCheck], 429);
}

// Extract user information from validation
$userId = $validation['user_id'];
$username = $validation['username'];
$userRole = $validation['role'];
$userBalance = $validation['balance'];
$apiKeyId = $validation['key_data']['id'] ?? null;

// Check if agent belongs to a store (MANDATORY - all agents must belong to a store)
// Also check agent status in store_agents table
$storeStmt = $conn->prepare("
    SELECT a.store_id, a.is_store_owner, s.owner_id, sa.status as agent_status
    FROM agents a 
    LEFT JOIN stores s ON a.store_id = s.id 
    LEFT JOIN store_agents sa ON a.id = sa.agent_id AND sa.store_id = a.store_id
    WHERE a.id = ?
");
$storeStmt->bind_param("i", $userId);
$storeStmt->execute();
$storeResult = $storeStmt->get_result();
$storeData = $storeResult->fetch_assoc();
$storeStmt->close();

$userStoreId = $storeData['store_id'] ?? null;
$storeOwnerId = $storeData['owner_id'] ?? null;
$isStoreOwner = isset($storeData['is_store_owner']) && $storeData['is_store_owner'] == 1;
$agentStatus = $storeData['agent_status'] ?? 'active';

// All agents MUST belong to a store - reject if they don't
if (!$userStoreId) {
    respond(['status' => 'error', 'message' => 'Agent must belong to a store. Please contact administrator.'], 400);
}

// Check if agent is suspended
if ($agentStatus === 'suspended') {
    respond(['status' => 'error', 'message' => 'Your account has been suspended. Please contact your store owner.'], 403);
}

// CRITICAL: Check if store is active - reject orders if store is inactive
$storeStatusStmt = $conn->prepare("SELECT status FROM stores WHERE id = ? LIMIT 1");
$storeStatusStmt->bind_param("i", $userStoreId);
$storeStatusStmt->execute();
$storeStatusResult = $storeStatusStmt->get_result();
$storeStatusData = $storeStatusResult->fetch_assoc();
$storeStatusStmt->close();

if (!$storeStatusData || $storeStatusData['status'] !== 'active') {
    respond(['status' => 'error', 'message' => 'Store is currently inactive and not accepting orders. Please contact the store owner.'], 403);
}

// Initialize store pricing manager (always required)
$storePricingManager = new StorePricingManager($conn);

// Get webhook URL from request (optional)
$webhookUrl = $input['webhook_url'] ?? $input['webhook'] ?? null;
if ($webhookUrl && !filter_var($webhookUrl, FILTER_VALIDATE_URL)) {
    // Invalid webhook URL, set to null
    $webhookUrl = null;
}

// Determine orders: bulk (orders array) or single (top-level beneficiary + package_size)
$incomingOrders = [];

if (isset($input['orders']) && is_array($input['orders'])) {
    // Bulk: each item may use different key names — accept aliases
    foreach ($input['orders'] as $idx => $item) {
        if (!is_array($item)) continue;
        $benefRaw = $item['beneficiary'] ?? $item['beneficiary_number'] ?? $item['phone'] ?? null;
        $sizeRaw  = $item['package_size'] ?? $item['bundle_size'] ?? $item['size'] ?? null;
        $incomingOrders[] = ['beneficiary_raw' => $benefRaw, 'size_raw' => $sizeRaw, 'index' => $idx];
    }
} else {
    // Single
    $benefRaw = $input['beneficiary'] ?? $input['beneficiary_number'] ?? $input['phone'] ?? null;
    $sizeRaw  = $input['package_size'] ?? $input['bundle_size'] ?? $input['size'] ?? null;
    $incomingOrders[] = ['beneficiary_raw' => $benefRaw, 'size_raw' => $sizeRaw, 'index' => 0];
}

// Validate & normalize incoming orders
$validOrders = [];
$failedOrders = [];

foreach ($incomingOrders as $i => $row) {
    $idx = $row['index'] ?? $i;
    $benefRaw = $row['beneficiary_raw'];
    $sizeRaw = $row['size_raw'];

    $phone = validatePhone($benefRaw);
    $sizeGb = parsePackageSizeGb($sizeRaw);

    if (!$phone) {
        $failedOrders[] = ['index' => $idx, 'reason' => 'Invalid beneficiary number', 'raw' => $benefRaw];
        continue;
    }
    if ($sizeGb <= 0) {
        $failedOrders[] = ['index' => $idx, 'reason' => 'Invalid package_size', 'raw' => $sizeRaw];
        continue;
    }

    // ALL agents MUST use store pricing (no fallback)
    $bundleLabel = formatBundleLabel($sizeGb);
    
    // Detect network type for pricing
    $networkType = detectNetworkType($phone);
    if ($networkType === null) {
        $failedOrders[] = ['index' => $idx, 'reason' => 'Unrecognized network prefix for phone number ' . $phone, 'raw' => $benefRaw];
        continue;
    }
    
    // Always use store pricing (mandatory for all agents)
    $pricingResult = $storePricingManager->calculatePrice($userStoreId, $bundleLabel, $networkType);
    $cost = $pricingResult['price'];
    
    if ($cost === null) {
        $failedOrders[] = ['index' => $idx, 'reason' => 'No store pricing set for ' . $bundleLabel . ' on ' . $networkType . ' network', 'raw' => $sizeRaw];
        continue;
    }

    $validOrders[] = [
        'index' => $idx,
        'beneficiary' => $phone,
        'size_gb' => $sizeGb,
        'bundle_label' => $bundleLabel,
        'cost' => $cost,
        'network_type' => $networkType
    ];
}

// If no valid orders, return error with details
if (count($validOrders) === 0) {
    respond(['status' => 'error', 'message' => 'No valid orders found', 'failed' => $failedOrders], 400);
}

// ------------------ User Information (Already Authenticated) ------------------
$agentId = $userId;
$role = $userRole;
// Note: $userStoreId and $storeOwnerId are already set above during pricing calculation

// Helper function to get admin price from agent_pricing_rules
function getAdminPriceForPackage($conn, $packageSizeGb, $networkType = 'mtn') {
    $stmt = $conn->prepare("
        SELECT price_per_gb FROM agent_pricing_rules 
        WHERE network_type = ? AND role = 'agent' AND is_active = 1 
        AND min_size_gb <= ? AND max_size_gb >= ?
        ORDER BY min_size_gb DESC
        LIMIT 1
    ");
    $stmt->bind_param("sdd", $networkType, $packageSizeGb, $packageSizeGb);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result->fetch_assoc();
    $stmt->close();
    
    if ($row) {
        return round($row['price_per_gb'] * $packageSizeGb, 2);
    }
    return null;
}

// Determine if this is a sub-agent order (requires two-level debit)
$isSubAgent = (!$isStoreOwner && $userStoreId && $storeOwnerId);

// Calculate total costs
$totalStoreCost = 0.0; // What sub-agent pays (store owner's price)
$totalAdminCost = 0.0;  // What store owner pays (admin price)

// Calculate admin prices for each order if sub-agent
if ($isSubAgent) {
    require_once __DIR__ . '/../app/classes/ExternalAPIPricingManager.php';
    $externalPricingManager = new ExternalAPIPricingManager($conn);
    
    foreach ($validOrders as &$order) {
        $order['store_cost'] = $order['cost']; // Store owner's price (what sub-agent pays)
        // Get admin price (what store owner pays)
        $adminPrice = $externalPricingManager->getAdminPrice($order['bundle_label'], $order['network_type']);
        if ($adminPrice === null) {
            // Fallback: calculate from package size
            $packageSizeGb = parsePackageSizeGb($order['bundle_label']);
            $adminPrice = getAdminPriceForPackage($conn, $packageSizeGb, $order['network_type']);
        }
        $order['admin_cost'] = $adminPrice ?? $order['cost']; // Fallback to store cost if admin price not found
        $totalStoreCost += $order['store_cost'];
        $totalAdminCost += $order['admin_cost'];
    }
    unset($order);
    $totalStoreCost = round($totalStoreCost, 2);
    $totalAdminCost = round($totalAdminCost, 2);
} else {
    // Store owner placing order - only pay admin price
    require_once __DIR__ . '/../app/classes/ExternalAPIPricingManager.php';
    $externalPricingManager = new ExternalAPIPricingManager($conn);
    
    foreach ($validOrders as &$order) {
        $adminPrice = $externalPricingManager->getAdminPrice($order['bundle_label'], $order['network_type']);
        if ($adminPrice === null) {
            $packageSizeGb = parsePackageSizeGb($order['bundle_label']);
            $adminPrice = getAdminPriceForPackage($conn, $packageSizeGb, $order['network_type']);
        }
        $order['admin_cost'] = $adminPrice ?? $order['cost'];
        $totalAdminCost += $order['admin_cost'];
    }
    unset($order);
    $totalAdminCost = round($totalAdminCost, 2);
    $totalStoreCost = $totalAdminCost; // Store owner pays admin price
}

// ------------------ Transaction: ensure balance & insert ------------------
$conn->begin_transaction();

// Lock and check sub-agent's balance (if sub-agent)
if ($isSubAgent) {
    $stmt = $conn->prepare("SELECT balance FROM agents WHERE id = ? FOR UPDATE");
    $stmt->bind_param("i", $agentId);
    $stmt->execute();
    $br = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    
    $subAgentBalance = floatval($br['balance']);
    if ($subAgentBalance < $totalStoreCost) {
        $conn->rollback();
        respond(['status' => 'error', 'message' => 'Insufficient balance', 'required' => $totalStoreCost, 'current_balance' => $subAgentBalance], 402);
    }
}

// Lock and check store owner's balance (always needed - either for sub-agent's admin cost or store owner's own cost)
$ownerBalanceId = $isSubAgent ? $storeOwnerId : $agentId;
$stmt = $conn->prepare("SELECT balance FROM agents WHERE id = ? FOR UPDATE");
$stmt->bind_param("i", $ownerBalanceId);
$stmt->execute();
$br = $stmt->get_result()->fetch_assoc();
$stmt->close();

$ownerBalance = floatval($br['balance']);
if ($ownerBalance < $totalAdminCost) {
    $conn->rollback();
    respond(['status' => 'error', 'message' => 'Insufficient balance', 'required' => $totalAdminCost, 'current_balance' => $ownerBalance], 402);
}

// Debit sub-agent's balance (if sub-agent)
if ($isSubAgent) {
    $newSubAgentBalance = round($subAgentBalance - $totalStoreCost, 2);
    $stmt = $conn->prepare("UPDATE agents SET balance = ? WHERE id = ?");
    $stmt->bind_param("di", $newSubAgentBalance, $agentId);
    if (!$stmt->execute()) {
        $stmt->close();
        $conn->rollback();
        respond(['status' => 'error', 'message' => 'Failed to update sub-agent balance'], 500);
    }
    $stmt->close();
}

// Debit store owner's balance (admin cost)
$newOwnerBalance = round($ownerBalance - $totalAdminCost, 2);
$stmt = $conn->prepare("UPDATE agents SET balance = ? WHERE id = ?");
if (!$stmt) {
    $conn->rollback();
    respond(['status' => 'error', 'message' => 'DB prepare failed (update balance)'], 500);
}
$stmt->bind_param("di", $newOwnerBalance, $ownerBalanceId);
$ok = $stmt->execute();
$stmt->close();
if (!$ok) {
    $conn->rollback();
    respond(['status' => 'error', 'message' => 'Failed to update store owner balance'], 500);
}

// Insert one orders_payments record for total bulk (record sub-agent's payment if applicable, otherwise store owner's)
$paymentAmount = $isSubAgent ? $totalStoreCost : $totalAdminCost;
$paymentUserId = $isSubAgent ? $agentId : $ownerBalanceId;
$stmt = $conn->prepare("INSERT INTO orders_payments (user_id, total_amount) VALUES (?, ?)");
if (!$stmt) {
    $conn->rollback();
    respond(['status' => 'error', 'message' => 'DB prepare failed (orders_payments insert)'], 500);
}
$stmt->bind_param("id", $paymentUserId, $paymentAmount);
$ok = $stmt->execute();
$stmt->close();
if (!$ok) {
    $conn->rollback();
    respond(['status' => 'error', 'message' => 'Failed to insert orders_payments'], 500);
}

// Check if webhook_url and api_key_id columns exist
$hasWebhookColumns = false;
$checkColumnStmt = $conn->prepare("
    SELECT COUNT(*) as col_count 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'agent_orders' 
    AND COLUMN_NAME IN ('webhook_url', 'api_key_id')
    HAVING COUNT(*) = 2
");
if ($checkColumnStmt) {
    if ($checkColumnStmt->execute()) {
        $checkResult = $checkColumnStmt->get_result();
        $checkRow = $checkResult->fetch_assoc();
        $hasWebhookColumns = ($checkRow && $checkRow['col_count'] == 2);
    }
    $checkColumnStmt->close();
}

// Insert orders with network type detection (including store_id, webhook_url and api_key_id if columns exist)
// Check if store_id column exists
$hasStoreIdColumn = false;
$checkStoreIdStmt = $conn->prepare("
    SELECT COUNT(*) as col_count 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'agent_orders' 
    AND COLUMN_NAME = 'store_id'
");
if ($checkStoreIdStmt) {
    if ($checkStoreIdStmt->execute()) {
        $checkResult = $checkStoreIdStmt->get_result();
        $checkRow = $checkResult->fetch_assoc();
        $hasStoreIdColumn = ($checkRow && $checkRow['col_count'] > 0);
    }
    $checkStoreIdStmt->close();
}

if ($hasWebhookColumns && $hasStoreIdColumn && $userStoreId) {
    $insertStmt = $conn->prepare("INSERT INTO agent_orders (agent_id, store_id, package_size, amount, beneficiary_number, network_type, order_source, status, webhook_url, api_key_id) VALUES (?, ?, ?, ?, ?, ?, ?, 'pending', ?, ?)");
} elseif ($hasWebhookColumns) {
    $insertStmt = $conn->prepare("INSERT INTO agent_orders (agent_id, package_size, amount, beneficiary_number, network_type, order_source, status, webhook_url, api_key_id) VALUES (?, ?, ?, ?, ?, ?, 'pending', ?, ?)");
} elseif ($hasStoreIdColumn && $userStoreId) {
    $insertStmt = $conn->prepare("INSERT INTO agent_orders (agent_id, store_id, package_size, amount, beneficiary_number, network_type, order_source, status) VALUES (?, ?, ?, ?, ?, ?, ?, 'pending')");
} else {
    // Fallback: insert without webhook columns if they don't exist
    $insertStmt = $conn->prepare("INSERT INTO agent_orders (agent_id, package_size, amount, beneficiary_number, network_type, order_source, status) VALUES (?, ?, ?, ?, ?, ?, 'pending')");
}
if (!$insertStmt) {
    $conn->rollback();
    respond(['status' => 'error', 'message' => 'DB prepare failed (agent_orders insert): ' . $conn->error], 500);
}

$orderIds = [];
$apiResults = [];

foreach ($validOrders as $v) {
    $pkg = $v['bundle_label'];
    $amt = $isSubAgent ? ($v['store_cost'] ?? $v['cost']) : ($v['admin_cost'] ?? $v['cost']); // Use store cost for sub-agent, admin cost for store owner
    $bn = $v['beneficiary'];
    $networkType = $v['network_type'];
    
    $orderSource = 'api';
    if ($hasWebhookColumns && $hasStoreIdColumn && $userStoreId) {
        // Parameters: agent_id (i), store_id (i), package_size (s), amount (d), beneficiary_number (s), network_type (s), order_source (s), webhook_url (s), api_key_id (i)
        $insertStmt->bind_param("iissssssi", $agentId, $userStoreId, $pkg, $amt, $bn, $networkType, $orderSource, $webhookUrl, $apiKeyId);
    } elseif ($hasWebhookColumns) {
        // Parameters: agent_id (i), package_size (s), amount (d), beneficiary_number (s), network_type (s), order_source (s), webhook_url (s), api_key_id (i)
        $insertStmt->bind_param("isdssssi", $agentId, $pkg, $amt, $bn, $networkType, $orderSource, $webhookUrl, $apiKeyId);
    } elseif ($hasStoreIdColumn && $userStoreId) {
        // Parameters: agent_id (i), store_id (i), package_size (s), amount (d), beneficiary_number (s), network_type (s), order_source (s)
        $insertStmt->bind_param("iisssss", $agentId, $userStoreId, $pkg, $amt, $bn, $networkType, $orderSource);
    } else {
        // Parameters: agent_id (i), package_size (s), amount (d), beneficiary_number (s), network_type (s), order_source (s)
        $insertStmt->bind_param("isdsss", $agentId, $pkg, $amt, $bn, $networkType, $orderSource);
    }
    if (!$insertStmt->execute()) {
        $insertStmt->close();
        $conn->rollback();
        respond(['status' => 'error', 'message' => 'Failed inserting order for ' . $bn], 500);
    }
    $orderId = $conn->insert_id;
    $orderIds[] = $orderId;

    // Log balance history for sub-agent (if applicable)
    if ($isSubAgent) {
        $orderStoreCost = $v['store_cost'] ?? $v['cost'];
        BalanceHistory::log($conn, $agentId, -1 * $orderStoreCost, 'order', $orderId, $bn, $subAgentBalance, $newSubAgentBalance);
    }
    
    // Log balance history for store owner (admin cost)
    $orderAdminCost = $isSubAgent ? ($v['admin_cost'] ?? $v['cost']) : ($v['admin_cost'] ?? $v['cost']);
    BalanceHistory::log($conn, $ownerBalanceId, -1 * $orderAdminCost, 'order', $orderId, $bn, $ownerBalance, $newOwnerBalance);
    
    // Create external API order entry for MTN orders (for profit tracking)
    if (strtolower($networkType) === 'mtn') {
        try {
            $externalPricingManager = new ExternalAPIPricingManager($conn);
            $adminPrice = $externalPricingManager->getAdminPrice($pkg, 'mtn');
            $externalPrice = $externalPricingManager->getExternalPrice($pkg, 'mtn');
            
            // Only create external API order if pricing is available
            if ($adminPrice !== null && $externalPrice !== null) {
                $profit = $adminPrice - $externalPrice;
                $packageSizeGb = parsePackageSizeGb($pkg);
                
                // Format package label
                $packageLabel = formatBundleLabel($packageSizeGb);
                
                $externalOrderStmt = $conn->prepare("
                    INSERT INTO external_api_orders 
                    (agent_id, network_type, package_size, package_size_gb, beneficiary_number, 
                     admin_price, external_api_price, profit, status, created_at)
                    VALUES (?, 'mtn', ?, ?, ?, ?, ?, ?, 'pending', NOW())
                ");
                
                if ($externalOrderStmt) {
                    $externalOrderStmt->bind_param("isddsdd", $agentId, $packageLabel, $packageSizeGb, $bn, $adminPrice, $externalPrice, $profit);
                    if ($externalOrderStmt->execute()) {
                        // Link external API order to agent order (optional - store reference)
                        $externalOrderId = $conn->insert_id;
                        error_log("External API Order created: ID $externalOrderId for agent_order $orderId");
                    } else {
                        error_log("Failed to create external API order: " . $externalOrderStmt->error);
                    }
                    $externalOrderStmt->close();
                }
            } else {
                error_log("External API pricing not available for package: $pkg (MTN). Admin: " . ($adminPrice ?? 'null') . ", External: " . ($externalPrice ?? 'null'));
            }
        } catch (Exception $e) {
            error_log("Error creating external API order: " . $e->getMessage());
            // Don't fail the main order if external API order creation fails
        }
    }
    
    // Process with external API if needed
    if (shouldUseExternalAPI($networkType)) {
        $apiService = new ExternalAPIService($networkType);
        $capacity = $apiService->convertPackageSize($pkg);
        $cleanPhone = $apiService->validatePhoneNumber($bn);
        
        // Call external API with internal network code and order ID for tracking
        $apiResponse = $apiService->purchaseData($cleanPhone, $networkType, $capacity, 'wallet', $orderId);
        
        if ($apiResponse['success']) {
            // Get the external API's transaction reference
            // ExternalAPIService wraps the response in data.data structure
            $externalApiReference = $apiResponse['data']['data']['transactionReference'] ?? null;
            
            // Get the data website's purchase ID (this is the order ID from data website)
            $purchaseId = $apiResponse['data']['data']['purchaseId'] ?? null;
            
            // Use the purchase ID directly as the transaction ID
            $dataWebsiteTransactionId = $purchaseId ? (string)$purchaseId : null;
            
            // Update order with both transaction IDs
            $api_response_data = json_encode($apiResponse['data']);
            $updateStmt = $conn->prepare("UPDATE agent_orders SET external_transaction_id = ?, external_reference = ?, api_response_data = ?, status_updated_at = NOW() WHERE id = ?");
            $updateStmt->bind_param("sssi", $dataWebsiteTransactionId, $externalApiReference, $api_response_data, $orderId);
            $updateStmt->execute();
            $updateStmt->close();
            
            // Log API submission (status remains pending)
            $history_stmt = $conn->prepare("INSERT INTO order_status_history (order_id, old_status, new_status, change_reason, changed_by, external_data) VALUES (?, 'pending', 'pending', 'Order submitted to external API successfully', 'system', ?)");
            $history_stmt->bind_param("is", $orderId, $api_response_data);
            $history_stmt->execute();
            $history_stmt->close();
            
            $apiResults[] = [
                'order_id' => $orderId,
                'beneficiary' => $bn,
                'status' => 'pending',
                'transaction_reference' => $dataWebsiteTransactionId,
                'message' => 'Order submitted to external API successfully, awaiting delivery confirmation'
            ];
        } else {
            // Update order status to failed
            $api_response_data = json_encode(['error' => $apiResponse['error']]);
            $updateStmt = $conn->prepare("UPDATE agent_orders SET status = 'failed', api_response_data = ?, status_updated_at = NOW() WHERE id = ?");
            $updateStmt->bind_param("si", $api_response_data, $orderId);
            $updateStmt->execute();
            $updateStmt->close();
            
            // Log failure
            $history_stmt = $conn->prepare("INSERT INTO order_status_history (order_id, old_status, new_status, change_reason, changed_by, external_data) VALUES (?, 'pending', 'failed', 'API call failed', 'system', ?)");
            $history_stmt->bind_param("is", $orderId, $api_response_data);
            $history_stmt->execute();
            $history_stmt->close();
            
            // Send SMS alert to admins
            try {
                require_once __DIR__ . '/../app/classes/AdminNotificationManager.php';
                $adminNotifier = new AdminNotificationManager($conn);
                $orderDetails = [
                    'beneficiary_number' => $bn,
                    'package_size' => $pkg,
                    'error' => $apiResponse['error'] ?? 'API call failed',
                    'network_type' => $networkType
                ];
                $adminNotifier->sendOrderFailedSMS($orderId, $orderDetails);
            } catch (Exception $e) {
                error_log("Failed to send order failure SMS alert: " . $e->getMessage());
            }
            
            $apiResults[] = [
                'order_id' => $orderId,
                'beneficiary' => $bn,
                'status' => 'failed',
                'error' => $apiResponse['error']
            ];
        }
    } else {
        // MTN orders are handled locally
        $apiResults[] = [
            'order_id' => $orderId,
            'beneficiary' => $bn,
            'status' => 'local',
            'message' => 'MTN order processed locally'
        ];
    }
}
$insertStmt->close();

$conn->commit();

// ------------------ Build response ------------------
$response = [
    'status' => count($failedOrders) ? 'partial' : 'success',
    'processed' => count($validOrders),
    'failed' => count($failedOrders),
    'errors' => $failedOrders,
    'total_cost' => $totalCost,
    'new_balance' => $newBalance,
    'order_ids' => $orderIds,
    'api_results' => $apiResults,
    'message' => 'Orders successfully saved to database and processed',
    'rate_limit' => $rateLimitCheck
];

// Log successful API usage
$totalResponseTime = round((microtime(true) - $startTime) * 1000);
$apiKeyManager->logApiUsage($validation['key_data']['id'], $userId, 'create_order', 
    $_SERVER['REQUEST_METHOD'], $_SERVER['REMOTE_ADDR'] ?? 'unknown', 
    $_SERVER['HTTP_USER_AGENT'] ?? '', $input, 201, $totalResponseTime);

respond($response, 201);
