<?php
/**
 * Payment Gateway Integration Class
 * Handles Paynow and PayPal payment processing
 * 
 * Updated to use official PayNow REST API
 * Documentation: https://developers.paynow.co.zw/
 */

require_once __DIR__ . '/paynow/PayNowClient.php'; // New official PayNow API client
require_once __DIR__ . '/../vendor/autoload.php'; // Composer autoloader for PayPal
require_once __DIR__ . '/notifications.php'; // Email notifications
require_once __DIR__ . '/../config/payment_security.php'; // Security measures

class PaymentGateway {
    private $db;
    private $gateway;

    public function __construct($gatewayName = 'paynow', $environment = 'sandbox') {
        $this->db = getDB();
        $this->gateway = $gatewayName;

        // Get gateway settings
        $stmt = $this->db->prepare("
            SELECT * FROM payment_gateway_settings
            WHERE gateway_name = ? AND gateway_type = ? AND is_active = 1
        ");
        $stmt->execute([$gatewayName, $environment]);
        $this->settings = $stmt->fetch();

        if (!$this->settings) {
            throw new Exception("Payment gateway {$gatewayName} ({$environment}) is not configured or active.");
        }
    }

    /**
     * Create a payment transaction record
     */
    private function createTransaction($userId, $itemType, $itemId, $amount, $currency = 'USD', $discountCode = null) {
        // Generate unique reference number
        $referenceNumber = 'TXN_' . time() . '_' . rand(1000, 9999);
        $transactionId = uniqid('txn_', true);

        // Calculate discount if applicable
        $discountAmount = 0.00;
        $finalAmount = $amount;

        if ($discountCode) {
            $discount = $this->validateDiscountCode($discountCode, $amount);
            if ($discount) {
                $discountAmount = $discount['discount_amount'];
                $finalAmount = $amount - $discountAmount;

                // Update discount usage
                $this->db->prepare("UPDATE discount_codes SET usage_count = usage_count + 1 WHERE code = ?")
                        ->execute([$discountCode]);
            }
        }

        // Create transaction record
        $stmt = $this->db->prepare("
            INSERT INTO payment_transactions
            (transaction_id, reference_number, gateway_name, gateway_type, user_id, item_type, item_id,
             amount, currency, discount_code, discount_amount, final_amount, status, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending', NOW())
        ");

        $stmt->execute([
            $transactionId, $referenceNumber, $this->gateway, $this->settings['gateway_type'],
            $userId, $itemType, $itemId, $amount, $currency, $discountCode,
            $discountAmount, $finalAmount
        ]);

        return [
            'transaction_id' => $transactionId,
            'reference_number' => $referenceNumber,
            'final_amount' => $finalAmount
        ];
    }

    /**
     * Validate discount code
     */
    private function validateDiscountCode($code, $purchaseAmount) {
        $stmt = $this->db->prepare("
            SELECT * FROM discount_codes
            WHERE code = ? AND is_active = 1
            AND (valid_from IS NULL OR valid_from <= NOW())
            AND (valid_until IS NULL OR valid_until >= NOW())
        ");
        $stmt->execute([$code]);
        $discount = $stmt->fetch();

        if (!$discount) {
            return false;
        }

        // Check usage limit
        if ($discount['usage_limit'] && $discount['usage_count'] >= $discount['usage_limit']) {
            return false;
        }

        // Check minimum purchase amount
        if ($purchaseAmount < $discount['min_purchase_amount']) {
            return false;
        }

        // Calculate discount amount
        if ($discount['discount_type'] === 'percentage') {
            $discountAmount = ($purchaseAmount * $discount['discount_value']) / 100;

            // Apply max discount limit if set
            if ($discount['max_discount_amount'] && $discountAmount > $discount['max_discount_amount']) {
                $discountAmount = $discount['max_discount_amount'];
            }
        } else {
            $discountAmount = $discount['discount_value'];
        }

        return [
            'discount_amount' => min($discountAmount, $purchaseAmount), // Don't exceed purchase amount
            'code' => $code
        ];
    }

    /**
     * Process Paynow payment using official PayNow REST API
     */
    public function processPaynowPayment($userId, $itemType, $itemId, $amount, $currency = 'USD',
                                       $discountCode = null, $userEmail = null, $itemName = null) {
        try {
            // Security checks
            PaymentSecurity::validateEnvironment();
            PaymentSecurity::checkRateLimit($userId);

            // Validate input data
            $validatedData = PaymentSecurity::sanitizeInput([
                'amount' => $amount,
                'currency' => $currency,
                'discount_code' => $discountCode
            ], array_intersect_key(PAYMENT_VALIDATION_RULES, ['amount' => true, 'currency' => true, 'discount_code' => true]));

            $amount = $validatedData['amount'];
            $currency = $validatedData['currency'];
            $discountCode = $validatedData['discount_code'];

            // Create transaction record
            $transaction = $this->createTransaction($userId, $itemType, $itemId, $amount, $currency, $discountCode);

            // Initialize new PayNow API client
            $isStaging = ($this->settings['gateway_type'] !== 'live');
            
            $paynow = new \PayNow\PayNowClient(
                $this->settings['api_key'],           // Integration ID
                $this->settings['api_secret'],        // Integration Key
                $this->settings['merchant_id'] ?? '', // Merchant ID
                $isStaging,                            // Use staging for non-live
                $this->settings['merchant_email'] ?? '',
                $this->settings['merchant_mobile'] ?? ''
            );

            // Create payment using official API
            $description = $itemName ?: 'Course/Exam Purchase';
            $siteUrl = ((isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on') || (isset($_SERVER['HTTP_X_FORWARDED_PROTO']) && $_SERVER['HTTP_X_FORWARDED_PROTO'] === 'https') ? 'https' : 'http') . '://' . ($_SERVER['HTTP_HOST'] ?? 'localhost');
            $returnUrl = $siteUrl . '/student/payment_return.php?ref=' . urlencode($transaction['reference_number']);
            $response = $paynow->createPayment(
                $transaction['reference_number'],
                $transaction['final_amount'],
                $description,
                $userEmail ?: ($_SESSION['user_email'] ?? ''),
                '',
                [],
                $returnUrl
            );

            if ($response['success']) {
                // Update transaction with Paynow details
                $stmt = $this->db->prepare("
                    UPDATE payment_transactions
                    SET gateway_response = ?, gateway_metadata = JSON_OBJECT('poll_url', ?, 'browser_url', ?),
                        status = 'processing'
                    WHERE transaction_id = ?
                ");

                $stmt->execute([
                    json_encode(['success' => true, 'redirect_url' => $response['redirect_url']]),
                    $response['poll_url'],
                    $response['redirect_url'],
                    $transaction['transaction_id']
                ]);

                return [
                    'success' => true,
                    'transaction_id' => $transaction['transaction_id'],
                    'reference_number' => $transaction['reference_number'],
                    'redirect_url' => $response['redirect_url'],
                    'poll_url' => $response['poll_url'],
                    'amount' => $transaction['final_amount']
                ];
            } else {
                // Update transaction as failed
                $errorMessage = $response['raw_response']['error'] ?? 'Payment initiation failed';
                $this->updateTransactionStatus($transaction['transaction_id'], 'failed', $errorMessage);

                $this->logPaymentError('paynow_payment_error', $errorMessage, [
                    'user_id' => $userId,
                    'reference' => $transaction['reference_number']
                ]);

                return [
                    'success' => false,
                    'error' => $errorMessage
                ];
            }

        } catch (\PayNow\PayNowException $e) {
            // PayNow specific error
            $this->logPaymentError('paynow_api_error', $e->getMessage(), [
                'user_id' => $userId,
                'reference' => $transaction['reference_number'] ?? null
            ]);

            return [
                'success' => false,
                'error' => 'Payment service temporarily unavailable. Please try again.',
                'technical_error' => $e->getMessage()
            ];
        } catch (Exception $e) {
            // Log error
            $this->logPaymentError('paynow_payment_error', $e->getMessage(), [
                'user_id' => $userId,
                'item_type' => $itemType,
                'item_id' => $itemId,
                'amount' => $amount
            ]);

            return [
                'success' => false,
                'error' => $e->getMessage()
            ];
        }
    }

    /**
     * Process PayPal payment
     */
    public function processPaypalPayment($userId, $itemType, $itemId, $amount, $currency = 'USD',
                                       $discountCode = null, $userEmail = null, $itemName = null) {
        try {
            // Security checks
            PaymentSecurity::validateEnvironment();
            PaymentSecurity::checkRateLimit($userId);

            // Validate input data
            $validatedData = PaymentSecurity::sanitizeInput([
                'amount' => $amount,
                'currency' => $currency,
                'discount_code' => $discountCode
            ], array_intersect_key(PAYMENT_VALIDATION_RULES, ['amount' => true, 'currency' => true, 'discount_code' => true]));

            $amount = $validatedData['amount'];
            $currency = $validatedData['currency'];
            $discountCode = $validatedData['discount_code'];

            // Create transaction record
            $transaction = $this->createTransaction($userId, $itemType, $itemId, $amount, $currency, $discountCode);

            // Initialize PayPal SDK
            $clientId = $this->settings['api_key'];
            $clientSecret = $this->settings['api_secret'];

            if (empty($clientId) || empty($clientSecret)) {
                throw new Exception('PayPal API credentials not configured');
            }

            $environment = $this->settings['gateway_type'] === 'live'
                ? new \PayPalServerSdk\Environment\Production($clientId, $clientSecret)
                : new \PayPalServerSdk\Environment\Sandbox($clientId, $clientSecret);

            $client = new \PayPalServerSdk\PaypalServerSdkClient($environment);

            // Create PayPal order
            $orderRequest = [
                "body" => [
                    "intent" => "CAPTURE",
                    "purchase_units" => [
                        [
                            "amount" => [
                                "currency_code" => $currency,
                                "value" => number_format($transaction['final_amount'], 2, '.', '')
                            ],
                            "description" => $itemName ?: 'Course/Exam Purchase',
                            "reference_id" => $transaction['reference_number']
                        ]
                    ],
                    "application_context" => [
                        "return_url" => $this->settings['return_url'] ?: ((isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on') || (isset($_SERVER['HTTP_X_FORWARDED_PROTO']) && $_SERVER['HTTP_X_FORWARDED_PROTO'] === 'https') ? 'https' : 'http') . '://' . ($_SERVER['HTTP_HOST'] ?? 'localhost') . '/student/payment_return.php',
                        "cancel_url" => $this->settings['cancel_url'] ?: ((isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on') || (isset($_SERVER['HTTP_X_FORWARDED_PROTO']) && $_SERVER['HTTP_X_FORWARDED_PROTO'] === 'https') ? 'https' : 'http') . '://' . ($_SERVER['HTTP_HOST'] ?? 'localhost') . '/student/payment_cancel.php',
                        "brand_name" => "Mutalex Academy",
                        "landing_page" => "BILLING",
                        "shipping_preference" => "NO_SHIPPING",
                        "user_action" => "PAY_NOW"
                    ]
                ]
            ];

            $apiResponse = $client->getOrdersController()->ordersCreate($orderRequest);

            if ($apiResponse->getResult()->id) {
                $orderId = $apiResponse->getResult()->id;

                // Update transaction with PayPal order ID
                $stmt = $this->db->prepare("
                    UPDATE payment_transactions
                    SET gateway_response = ?, gateway_metadata = JSON_OBJECT('paypal_order_id', ?),
                        status = 'processing'
                    WHERE transaction_id = ?
                ");

                $stmt->execute([
                    json_encode(['paypal_order_id' => $orderId]),
                    $orderId,
                    $transaction['transaction_id']
                ]);

                // Find approval URL
                $approvalUrl = null;
                foreach ($apiResponse->getResult()->links as $link) {
                    if ($link->rel === 'approve') {
                        $approvalUrl = $link->href;
                        break;
                    }
                }

                return [
                    'success' => true,
                    'transaction_id' => $transaction['transaction_id'],
                    'reference_number' => $transaction['reference_number'],
                    'redirect_url' => $approvalUrl,
                    'paypal_order_id' => $orderId,
                    'amount' => $transaction['final_amount']
                ];
            } else {
                // Update transaction as failed
                $this->updateTransactionStatus($transaction['transaction_id'], 'failed', 'PayPal order creation failed');

                return [
                    'success' => false,
                    'error' => 'Failed to create PayPal order'
                ];
            }

        } catch (Exception $e) {
            // Log error
            $this->logPaymentError('paypal_payment_error', $e->getMessage(), [
                'user_id' => $userId,
                'item_type' => $itemType,
                'item_id' => $itemId,
                'amount' => $amount
            ]);

            return [
                'success' => false,
                'error' => $e->getMessage()
            ];
        }
    }

    /**
     * Check payment status using poll URL (updated for new PayNow API)
     */
    public function checkPaymentStatus($transactionId) {
        try {
            // Get transaction details
            $stmt = $this->db->prepare("
                SELECT * FROM payment_transactions
                WHERE transaction_id = ? OR reference_number = ?
            ");
            $stmt->execute([$transactionId, $transactionId]);
            $transaction = $stmt->fetch();

            if (!$transaction) {
                return ['success' => false, 'error' => 'Transaction not found'];
            }

            // Load correct gateway settings based on transaction
            $stmt = $this->db->prepare("
                SELECT * FROM payment_gateway_settings
                WHERE gateway_name = ? AND gateway_type = ? AND is_active = 1 LIMIT 1
            ");
            $stmt->execute([$transaction['gateway_name'], $transaction['gateway_type']]);
            $settings = $stmt->fetch();

            if (!$settings) {
                return ['success' => false, 'error' => 'Gateway not configured for this transaction'];
            }

            if ($transaction['gateway_name'] === 'paynow') {
                // Get poll URL from metadata
                $metadata = json_decode($transaction['gateway_metadata'] ?? '{}', true);
                $pollUrl = $metadata['poll_url'] ?? null;

                if (!$pollUrl) {
                    return ['success' => false, 'error' => 'Poll URL not available'];
                }

                // Initialize new PayNow client and check status
                $isStaging = ($transaction['gateway_type'] !== 'live');
                $paynow = new \PayNow\PayNowClient(
                    $settings['api_key'],
                    $settings['api_secret'],
                    $settings['merchant_id'] ?? '',
                    $isStaging
                );

                $status = $paynow->checkStatus($pollUrl);

                $newStatus = 'pending';
                if ($status['paid']) {
                    $newStatus = 'completed';
                } elseif ($status['status'] === 'Cancelled') {
                    $newStatus = 'cancelled';
                } elseif ($status['status'] === 'Failed' || $status['status'] === 'Error') {
                    $newStatus = 'failed';
                }

                // Update transaction status
                $this->updateTransactionStatus($transaction['id'], $newStatus, null, $status);

                return [
                    'success' => true,
                    'status' => $newStatus,
                    'paid' => $status['paid'],
                    'transaction_id' => $transaction['transaction_id'],
                    'paynow_status' => $status['status']
                ];

            } elseif ($transaction['gateway_name'] === 'paypal') {
                // Get PayPal order ID from metadata
                $metadata = json_decode($transaction['gateway_metadata'], true);
                $orderId = $metadata['paypal_order_id'] ?? null;

                if (!$orderId) {
                    return ['success' => false, 'error' => 'PayPal order ID not available'];
                }

                // Initialize PayPal SDK
                $clientId = $settings['api_key'];
                $clientSecret = $settings['api_secret'];

                $environment = $transaction['gateway_type'] === 'live'
                    ? new \PayPalServerSdk\Environment\Production($clientId, $clientSecret)
                    : new \PayPalServerSdk\Environment\Sandbox($clientId, $clientSecret);

                $client = new \PayPalServerSdk\PaypalServerSdkClient($environment);

                // Get order details
                $orderRequest = ["id" => $orderId];
                $apiResponse = $client->getOrdersController()->ordersCapture($orderRequest);

                $orderStatus = $apiResponse->getResult()->status;
                $newStatus = 'pending';

                if ($orderStatus === 'COMPLETED') {
                    $newStatus = 'completed';
                } elseif (in_array($orderStatus, ['VOIDED', 'CANCELLED'])) {
                    $newStatus = 'cancelled';
                }

                // Update transaction status
                $this->updateTransactionStatus($transaction['id'], $newStatus, null, $apiResponse->getResult());

                return [
                    'success' => true,
                    'status' => $newStatus,
                    'paid' => $newStatus === 'completed',
                    'transaction_id' => $transaction['transaction_id'],
                    'paypal_status' => $orderStatus
                ];
            }

            return ['success' => false, 'error' => 'Status checking not implemented for this gateway'];

        } catch (Exception $e) {
            $this->logPaymentError('status_check_error', $e->getMessage(), ['transaction_id' => $transactionId]);
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }

    /**
     * Update transaction status
     */
    private function updateTransactionStatus($transactionId, $status, $errorMessage = null, $gatewayResponse = null) {
        $stmt = $this->db->prepare("
            UPDATE payment_transactions
            SET status = ?, gateway_response = ?, processed_at = NOW()
            WHERE id = ?
        ");

        $responseJson = $gatewayResponse ? json_encode($gatewayResponse) : null;

        $stmt->execute([$status, $responseJson, $transactionId]);
    }

    /**
     * Process successful payment
     */
    public function processSuccessfulPayment($transactionId) {
        try {
            // Get transaction
            $stmt = $this->db->prepare("SELECT * FROM payment_transactions WHERE transaction_id = ?");
            $stmt->execute([$transactionId]);
            $transaction = $stmt->fetch();

            if (!$transaction || $transaction['status'] !== 'completed') {
                return ['success' => false, 'error' => 'Invalid transaction'];
            }

            // Update enrollment or exam attempt based on item type
            if ($transaction['item_type'] === 'course') {
                // Check if enrollment already exists
                $stmt = $this->db->prepare("SELECT id FROM course_enrollments WHERE course_id = ? AND student_id = ?");
                $stmt->execute([$transaction['item_id'], $transaction['user_id']]);
                $existingEnrollment = $stmt->fetch();

                if ($existingEnrollment) {
                    // Update existing enrollment
                    $stmt = $this->db->prepare("
                        UPDATE course_enrollments
                        SET payment_status = 'paid', payment_amount = ?, updated_at = NOW()
                        WHERE course_id = ? AND student_id = ?
                    ");
                    $stmt->execute([$transaction['final_amount'], $transaction['item_id'], $transaction['user_id']]);
                } else {
                    // Create new enrollment
                    $stmt = $this->db->prepare("
                        INSERT INTO course_enrollments
                        (course_id, student_id, payment_status, payment_amount, status, enrollment_date, created_at)
                        VALUES (?, ?, 'paid', ?, 'enrolled', NOW(), NOW())
                    ");
                    $stmt->execute([$transaction['item_id'], $transaction['user_id'], $transaction['final_amount']]);
                }

            } elseif ($transaction['item_type'] === 'cart') {
                // Handle cart payment - enroll all courses and exams in the cart
                $cartItemIds = json_decode($transaction['item_id'], true);

                if (is_array($cartItemIds)) {
                    // Get cart items to know which are courses and which are exams
                    $stmt = $this->db->prepare("SELECT item_id, item_type FROM cart_items WHERE user_id = ?");
                    $stmt->execute([$transaction['user_id']]);
                    $cartItems = $stmt->fetchAll(PDO::FETCH_ASSOC);

                    foreach ($cartItems as $cartItem) {
                        $itemId = $cartItem['item_id'];
                        $itemType = $cartItem['item_type'];

                        if ($itemType === 'course') {
                            // Process course enrollment
                            $stmt = $this->db->prepare("SELECT id FROM course_enrollments WHERE course_id = ? AND student_id = ?");
                            $stmt->execute([$itemId, $transaction['user_id']]);
                            $existingEnrollment = $stmt->fetch();

                            if ($existingEnrollment) {
                                $stmt = $this->db->prepare("
                                    UPDATE course_enrollments
                                    SET payment_status = 'paid', payment_amount = ?, updated_at = NOW()
                                    WHERE course_id = ? AND student_id = ?
                                ");
                                $stmt->execute([$transaction['final_amount'], $itemId, $transaction['user_id']]);
                            } else {
                                $stmt = $this->db->prepare("
                                    INSERT INTO course_enrollments
                                    (course_id, student_id, payment_status, payment_amount, status, enrollment_date, created_at)
                                    VALUES (?, ?, 'paid', ?, 'enrolled', NOW(), NOW())
                                ");
                                $stmt->execute([$itemId, $transaction['user_id'], $transaction['final_amount']]);
                            }
                        } elseif ($itemType === 'exam') {
                            // Process exam access
                            $stmt = $this->db->prepare("
                                INSERT INTO exam_access (exam_id, student_id, payment_status, payment_amount, access_granted_at)
                                VALUES (?, ?, 'paid', ?, NOW())
                                ON DUPLICATE KEY UPDATE payment_status = 'paid', payment_amount = ?, access_granted_at = NOW()
                            ");
                            $stmt->execute([$itemId, $transaction['user_id'], $transaction['final_amount'], $transaction['final_amount']]);
                        }
                    }

                    // Clear the cart after successful processing
                    $stmt = $this->db->prepare("DELETE FROM cart_items WHERE user_id = ?");
                    $stmt->execute([$transaction['user_id']]);
                }

            } elseif ($transaction['item_type'] === 'exam') {
                // Grant exam access after payment
                $stmt = $this->db->prepare("
                    INSERT INTO exam_access (exam_id, student_id, payment_status, payment_amount, currency, access_granted_at)
                    VALUES (?, ?, 'paid', ?, ?, NOW())
                    ON DUPLICATE KEY UPDATE payment_status = 'paid', payment_amount = ?, access_granted_at = NOW()
                ");
                $stmt->execute([
                    $transaction['item_id'], 
                    $transaction['user_id'],
                    $transaction['final_amount'],
                    $transaction['currency'],
                    $transaction['final_amount']
                ]);
            }

            // Send payment success notification
            sendPaymentNotification('payment_success', $transaction['transaction_id']);

            // Log successful payment
            $this->logPaymentEvent('payment_completed', 'Payment completed successfully', [
                'transaction_id' => $transactionId,
                'amount' => $transaction['final_amount'],
                'user_id' => $transaction['user_id']
            ]);

            return ['success' => true];

        } catch (Exception $e) {
            $this->logPaymentError('payment_processing_error', $e->getMessage(), ['transaction_id' => $transactionId]);
            return ['success' => false, 'error' => $e->getMessage()];
        }
    }

    /**
     * Log payment events
     */
    private function logPaymentEvent($action, $message, $context = []) {
        $stmt = $this->db->prepare("
            INSERT INTO payment_logs (action, level, message, context, created_at)
            VALUES (?, 'info', ?, ?, NOW())
        ");
        $stmt->execute([$action, $message, json_encode($context)]);
    }

    /**
     * Log payment errors
     */
    private function logPaymentError($action, $message, $context = []) {
        $stmt = $this->db->prepare("
            INSERT INTO payment_logs (action, level, message, context, created_at)
            VALUES (?, 'error', ?, ?, NOW())
        ");
        $stmt->execute([$action, $message, json_encode($context)]);
    }

    /**
     * Get gateway settings
     */
    public function getSettings() {
        return $this->settings;
    }

    /**
     * Update payment analytics
     * This should be called periodically (e.g., daily) to aggregate payment data
     */
    public static function updateAnalytics($date = null) {
        $db = getDB();
        $date = $date ?: date('Y-m-d');

        // Get analytics data for the specified date
        $query = "
            SELECT
                gateway_name,
                gateway_type,
                currency,
                COUNT(*) as total_transactions,
                SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as successful_transactions,
                SUM(CASE WHEN status IN ('failed', 'cancelled') THEN 1 ELSE 0 END) as failed_transactions,
                SUM(final_amount) as total_amount,
                SUM(CASE WHEN status = 'refunded' THEN final_amount ELSE 0 END) as refunded_amount,
                SUM(CASE WHEN status = 'completed' THEN final_amount ELSE 0 END) as net_amount,
                AVG(CASE WHEN status = 'completed' THEN final_amount END) as average_transaction
            FROM payment_transactions
            WHERE DATE(created_at) = ?
            GROUP BY gateway_name, gateway_type, currency
        ";

        $stmt = $db->prepare($query);
        $stmt->execute([$date]);
        $analytics = $stmt->fetchAll();

        // Insert/update analytics records
        foreach ($analytics as $row) {
            $stmt = $db->prepare("
                INSERT INTO payment_analytics
                (date, gateway_name, currency, total_transactions, successful_transactions,
                 failed_transactions, total_amount, refunded_amount, net_amount, average_transaction, created_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
                ON DUPLICATE KEY UPDATE
                total_transactions = VALUES(total_transactions),
                successful_transactions = VALUES(successful_transactions),
                failed_transactions = VALUES(failed_transactions),
                total_amount = VALUES(total_amount),
                refunded_amount = VALUES(refunded_amount),
                net_amount = VALUES(net_amount),
                average_transaction = VALUES(average_transaction)
            ");

            $stmt->execute([
                $date,
                $row['gateway_name'],
                $row['currency'],
                $row['total_transactions'],
                $row['successful_transactions'],
                $row['failed_transactions'],
                $row['total_amount'],
                $row['refunded_amount'],
                $row['net_amount'],
                $row['average_transaction']
            ]);
        }

        return count($analytics);
    }
}

/**
 * Helper function to get active payment gateway
 */
function getPaymentGateway($gatewayName = null, $environment = null) {
    // If no gateway specified, get the first active one
    if (!$gatewayName) {
        $db = getDB();
        $stmt = $db->query("
            SELECT gateway_name, gateway_type FROM payment_gateway_settings
            WHERE is_active = 1 ORDER BY gateway_name LIMIT 1
        ");
        $gateway = $stmt->fetch();
        if ($gateway) {
            $gatewayName = $gateway['gateway_name'];
            $environment = $gateway['gateway_type'];
        } else {
            throw new Exception('No active payment gateway configured');
        }
    }

    return new PaymentGateway($gatewayName, $environment ?: 'sandbox');
}