<?php
// Prevent any output before JSON headers
ob_start();

// Disable error display to prevent HTML output
ini_set('display_errors', 0);
error_reporting(E_ALL);

// Apply secure CORS configuration
require_once 'cors_security.php';
applySecureCORS();

header('Content-Type: application/json');

require_once 'db_config.php';

// Main API handler
try {
    $method = $_SERVER['REQUEST_METHOD'];
    $input = json_decode(file_get_contents('php://input'), true);
    
    switch ($method) {
        case 'POST':
            if (isset($_GET['action'])) {
                switch ($_GET['action']) {
                    case 'register':
                        echo json_encode(handleRegistration($input));
                        break;
                    case 'get_config':
                        // Support special access links via ?access=CODE parameter
                        $accessCode = $_GET['access'] ?? null;
                        echo json_encode(getConfiguration($accessCode));
                        break;
                    case 'create_stripe_session':
                        echo json_encode(createStripeSession($input));
                        break;
                    default:
                        throw new Exception('Invalid action');
                }
            } else {
                throw new Exception('No action specified');
            }
            break;
            
        case 'GET':
            if (isset($_GET['action'])) {
                switch ($_GET['action']) {
                    case 'get_config':
                        // Support special access links via ?access=CODE parameter
                        $accessCode = $_GET['access'] ?? null;
                        echo json_encode(getConfiguration($accessCode));
                        break;
                    case 'get_stats':
                        echo json_encode(getRegistrationStats());
                        break;
                    case 'get_stripe_key':
                        echo json_encode(getStripePublishableKey());
                        break;
                    case 'create_stripe_session':
                        echo json_encode(createStripeSession($input));
                        break;

                    default:
                        throw new Exception('Invalid action');
                }
            } else {
                throw new Exception('No action specified');
            }
            break;
            
        default:
            throw new Exception('Method not allowed');
    }
    
} catch (Exception $e) {
    // Clear any output buffer to prevent HTML errors
    ob_clean();
    error_log('API Error: ' . $e->getMessage());
    echo json_encode(['error' => $e->getMessage()]);
} finally {
    // Ensure clean output
    if (ob_get_level()) {
        ob_end_flush();
    }
}

// Handle registration submission
function handleRegistration($data) {
    // Basic input validation
    if (empty($data['delegateProducts']) || !is_array($data['delegateProducts'])) {
        throw new Exception('Missing required delegate product information');
    }
    
    if (empty($data['contact']['email']) || empty($data['contact']['fullName'])) {
        throw new Exception('Missing required contact information');
    }
    
    // Defer payment method validation until after total calculation to allow zero-amount flow
    
    $pdo = getDBConnection();
    
    try {
        $pdo->beginTransaction();
        
        // Generate registration number
        $registrationNumber = generateRegistrationNumber();
        
        // Debug: Log registration number generation
        error_log("Generated registration number: " . $registrationNumber);
        
        // Validate registration number was generated
        if (empty($registrationNumber)) {
            throw new Exception("Failed to generate registration number");
        }
        
        // Calculate total amount using live database data
        // Pass access code if available to get correct product pricing for special access links
        $accessCode = !empty($data['accessCode']) ? $data['accessCode'] : null;
        $config = getConfiguration($accessCode);
        
        // Calculate delegate costs from new structure
        $totalAmount = 0;
        foreach ($data['delegateProducts'] as $delegateSelection) {
            $delegateType = $delegateSelection['type'];
            $delegateCount = intval($delegateSelection['count']);
            
            // Find the delegate product price
            if (isset($config['frontend']['delegatePasses']['products'])) {
                foreach ($config['frontend']['delegatePasses']['products'] as $product) {
                    if ($product['product_code'] === $delegateType) {
                        // Find current active tier or use base price
                        $activeTier = null;
                        if (isset($product['pricing_tiers'])) {
                            foreach ($product['pricing_tiers'] as $tier) {
                                if ($tier['is_current']) {
                                    $activeTier = $tier;
                                    break;
                                }
                            }
                        }
                        $delegatePrice = $activeTier ? $activeTier['price'] : $product['base_price'];
                        $totalAmount += $delegatePrice * $delegateCount;
                        break;
                    }
                }
            }
        }
        
        // Add table costs using new data format
        if (!empty($data['tables'])) {
            foreach ($data['tables'] as $tableSelection) {
                $tableType = $tableSelection['type'];
                $tableCount = intval($tableSelection['count']);
                
                // Find the table product price
                if (isset($config['frontend']['tablePresentations']['products'])) {
                    foreach ($config['frontend']['tablePresentations']['products'] as $product) {
                        if ($product['product_code'] === $tableType) {
                            $totalAmount += $product['base_price'] * $tableCount;
                            break;
                        }
                    }
                }
            }
        }
        
        // Add sponsorship costs
        if (!empty($data['sponsorships'])) {
            foreach ($data['sponsorships'] as $sponsorshipSelection) {
                $sponsorshipType = $sponsorshipSelection['type'];
                $sponsorshipCount = intval($sponsorshipSelection['count']);
                
                // Find the sponsorship product price
                if (isset($config['frontend']['sponsorship']['products'])) {
                    foreach ($config['frontend']['sponsorship']['products'] as $product) {
                        if ($product['product_code'] === $sponsorshipType) {
                            $totalAmount += $product['base_price'] * $sponsorshipCount;
                            break;
                        }
                    }
                }
            }
        }
        
        // Add other sponsorship costs
        if (!empty($data['otherSponsorships'])) {
            foreach ($data['otherSponsorships'] as $otherSponsorshipSelection) {
                $otherSponsorshipType = $otherSponsorshipSelection['type'];
                $otherSponsorshipCount = intval($otherSponsorshipSelection['count']);
                
                // Find the other sponsorship product price
                if (isset($config['frontend']['otherSponsorship']['products'])) {
                    foreach ($config['frontend']['otherSponsorship']['products'] as $product) {
                        if ($product['product_code'] === $otherSponsorshipType) {
                            $totalAmount += $product['base_price'] * $otherSponsorshipCount;
                            break;
                        }
                    }
                }
            }
        }
        
        // Determine if registration total is free
        $isFreeRegistration = ($totalAmount <= 0);

        // If free and no payment method selected, default to gateway (no transaction to create)
        if ($isFreeRegistration && empty($data['paymentMethod'])) {
            $data['paymentMethod'] = 'gateway';
        }

        // Validate payment method only when payment is required
        if (!$isFreeRegistration && empty($data['paymentMethod'])) {
            throw new Exception('Payment method not selected');
        }

        // Set payment deadline (7 days for bank transfer, immediate for gateway)
        $paymentDeadline = $data['paymentMethod'] === 'tt' ? 
            date('Y-m-d', strtotime('+7 days')) : 
            date('Y-m-d');
        
        // Insert main registration
        $stmt = $pdo->prepare("
            INSERT INTO registrations (
                registration_number, delegate_type, delegate_count, 
                has_standard_table, has_double_table,
                contact_salutation, contact_full_name, contact_badge_name, 
                contact_designation, contact_department, contact_email, 
                contact_mobile, university_name, country,
                payment_method, referral_code, total_amount, payment_deadline,
                ip_address, user_agent
            ) VALUES (
                ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
            )
        ");
        
        // Check for table selections using new data format
        $hasStandardTable = 0;
        $hasDoubleTable = 0;
        
        if (!empty($data['tables'])) {
            foreach ($data['tables'] as $tableSelection) {
                if ($tableSelection['type'] === 'standard' && intval($tableSelection['count']) > 0) {
                    $hasStandardTable = 1;
                }
                if ($tableSelection['type'] === 'double' && intval($tableSelection['count']) > 0) {
                    $hasDoubleTable = 1;
                }
            }
        }
        
        // Calculate total delegate count from new structure
        $totalDelegateCount = 0;
        $primaryDelegateType = '';
        foreach ($data['delegateProducts'] as $delegateSelection) {
            $totalDelegateCount += intval($delegateSelection['count']);
            if (empty($primaryDelegateType)) {
                $primaryDelegateType = $delegateSelection['type'];
            }
        }
        
        // Map delegate type product code to valid ENUM value
        $mappedDelegateType = mapDelegateTypeToEnum($primaryDelegateType);
        
        // Get referral code from data (special access link code)
        $referralCode = !empty($data['accessCode']) ? $data['accessCode'] : null;
        
        // Debug: Log the values being inserted
        $insertValues = [
            $registrationNumber,
            $mappedDelegateType, // Use mapped value instead of raw product code
            $totalDelegateCount,
            $hasStandardTable,
            $hasDoubleTable,
            $data['contact']['salutation'] ?? '',
            $data['contact']['fullName'] ?? '',
            $data['contact']['badgeName'] ?? '',
            $data['contact']['designation'] ?? '',
            $data['contact']['department'] ?? '',
            $data['contact']['email'] ?? '',
            $data['contact']['mobile'] ?? '',
            $data['contact']['university'] ?? '',
            $data['contact']['country'] ?? '',
            $data['paymentMethod'],
            $referralCode,
            $totalAmount,
            $paymentDeadline,
            $_SERVER['REMOTE_ADDR'] ?? '',
            $_SERVER['HTTP_USER_AGENT'] ?? ''
        ];
        
        error_log("Inserting registration with values: " . json_encode($insertValues));
        
        if (!$stmt->execute($insertValues)) {
            $errorInfo = $stmt->errorInfo();
            error_log("SQL Error: " . json_encode($errorInfo));
            throw new Exception("Database insertion failed: " . $errorInfo[2]);
        }
        
        $registrationId = $pdo->lastInsertId();
        
        // Insert delegates
        if (!empty($data['delegates'])) {
            $delegateStmt = $pdo->prepare("
                INSERT INTO delegates (
                    registration_id, delegate_number, full_name, badge_name, 
                    designation, email, dietary_requirements, special_assistance
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            ");
            
            foreach ($data['delegates'] as $index => $delegate) {
                $delegateStmt->execute([
                    $registrationId,
                    $index + 1,
                    $delegate['fullName'],
                    $delegate['badgeName'],
                    $delegate['designation'],
                    $delegate['email'] ?? null,
                    $delegate['dietary'] ?? null,
                    $delegate['assistance'] ?? null
                ]);
            }
        }
        
        // Insert purchased products into registration_products table
        if (isset($config['frontend'])) {
            // Insert delegate products using new structure
            if (isset($config['frontend']['delegatePasses']['products']) && !empty($data['delegateProducts'])) {
                foreach ($data['delegateProducts'] as $delegateSelection) {
                    $delegateType = $delegateSelection['type'];
                    $delegateCount = intval($delegateSelection['count']);
                    
                    foreach ($config['frontend']['delegatePasses']['products'] as $product) {
                        if ($product['product_code'] === $delegateType) {
                            // Find current active tier or use base price
                            $activeTier = null;
                            $pricingTierName = null;
                            if (isset($product['pricing_tiers'])) {
                                foreach ($product['pricing_tiers'] as $tier) {
                                    if ($tier['is_current']) {
                                        $activeTier = $tier;
                                        $pricingTierName = $tier['tier_name'];
                                        break;
                                    }
                                }
                            }
                            $unitPrice = $activeTier ? $activeTier['price'] : $product['base_price'];
                            $totalPrice = $unitPrice * $delegateCount;
                            
                            $productStmt = $pdo->prepare("
                                INSERT INTO registration_products (
                                    registration_id, product_id, quantity, unit_price, total_price, 
                                    pricing_tier, stock_status, confirmed_at
                                ) VALUES (?, ?, ?, ?, ?, ?, 'confirmed', NOW())
                            ");
                            $productStmt->execute([
                                $registrationId,
                                $product['id'],
                                $delegateCount,
                                $unitPrice,
                                $totalPrice,
                                $pricingTierName
                            ]);
                            break;
                        }
                    }
                }
            }
            
            // Insert table products
            if (!empty($data['tables']) && isset($config['frontend']['tablePresentations']['products'])) {
                foreach ($data['tables'] as $tableSelection) {
                    $tableType = $tableSelection['type'];
                    $tableCount = intval($tableSelection['count']);
                    
                    foreach ($config['frontend']['tablePresentations']['products'] as $product) {
                        if ($product['product_code'] === $tableType && $tableCount > 0) {
                            $unitPrice = $product['base_price'];
                            $totalPrice = $unitPrice * $tableCount;
                            
                            $productStmt = $pdo->prepare("
                                INSERT INTO registration_products (
                                    registration_id, product_id, quantity, unit_price, total_price, 
                                    pricing_tier, stock_status, confirmed_at
                                ) VALUES (?, ?, ?, ?, ?, ?, 'confirmed', NOW())
                            ");
                            $productStmt->execute([
                                $registrationId,
                                $product['id'],
                                $tableCount,
                                $unitPrice,
                                $totalPrice,
                                null // Tables don't have pricing tiers
                            ]);
                            break;
                        }
                    }
                }
            }
            
            // Insert sponsorship products
            if (!empty($data['sponsorships']) && isset($config['frontend']['sponsorship']['products'])) {
                foreach ($data['sponsorships'] as $sponsorshipSelection) {
                    $sponsorshipType = $sponsorshipSelection['type'];
                    $sponsorshipCount = intval($sponsorshipSelection['count']);
                    
                    foreach ($config['frontend']['sponsorship']['products'] as $product) {
                        if ($product['product_code'] === $sponsorshipType && $sponsorshipCount > 0) {
                            $unitPrice = $product['base_price'];
                            $totalPrice = $unitPrice * $sponsorshipCount;
                            
                            $productStmt = $pdo->prepare("
                                INSERT INTO registration_products (
                                    registration_id, product_id, quantity, unit_price, total_price, 
                                    pricing_tier, stock_status, confirmed_at
                                ) VALUES (?, ?, ?, ?, ?, ?, 'confirmed', NOW())
                            ");
                            $productStmt->execute([
                                $registrationId,
                                $product['id'],
                                $sponsorshipCount,
                                $unitPrice,
                                $totalPrice,
                                null // Sponsorships don't have pricing tiers
                            ]);
                            break;
                        }
                    }
                }
            }
            
            // Insert other sponsorship products
            if (!empty($data['otherSponsorships']) && isset($config['frontend']['otherSponsorship']['products'])) {
                foreach ($data['otherSponsorships'] as $otherSponsorshipSelection) {
                    $otherSponsorshipType = $otherSponsorshipSelection['type'];
                    $otherSponsorshipCount = intval($otherSponsorshipSelection['count']);
                    
                    foreach ($config['frontend']['otherSponsorship']['products'] as $product) {
                        if ($product['product_code'] === $otherSponsorshipType && $otherSponsorshipCount > 0) {
                            $unitPrice = $product['base_price'];
                            $totalPrice = $unitPrice * $otherSponsorshipCount;
                            
                            $productStmt = $pdo->prepare("
                                INSERT INTO registration_products (
                                    registration_id, product_id, quantity, unit_price, total_price, 
                                    pricing_tier, stock_status, confirmed_at
                                ) VALUES (?, ?, ?, ?, ?, ?, 'confirmed', NOW())
                            ");
                            $productStmt->execute([
                                $registrationId,
                                $product['id'],
                                $otherSponsorshipCount,
                                $unitPrice,
                                $totalPrice,
                                null // Other sponsorships don't have pricing tiers
                            ]);
                            break;
                        }
                    }
                }
            }
        }
        
        // Create initial payment transaction only when payment is required
        if ($data['paymentMethod'] !== 'gateway' && !$isFreeRegistration) {
            $paymentReference = generatePaymentReference($data['paymentMethod'], $registrationNumber);
            
            $paymentStmt = $pdo->prepare("
                INSERT INTO payment_transactions (
                    registration_id, registration_number, transaction_type, amount, 
                    transaction_reference, bank_reference
                ) VALUES (?, ?, ?, ?, ?, ?)
            ");
            
            $paymentStmt->execute([
                $registrationId,
                $registrationNumber,
                $data['paymentMethod'] === 'gateway' ? 'gateway' : 'bank_transfer',
                $totalAmount,
                $paymentReference,
                $data['paymentMethod'] === 'tt' ? $paymentReference : null
            ]);
        } else {
            // For online payments, payment reference will be generated by Stripe
            $paymentReference = null;
        }
        
        $pdo->commit();

        // If free, auto-mark as paid for reporting
        if ($isFreeRegistration) {
            try {
                $statusStmt = $pdo->prepare("UPDATE registrations SET payment_status = 'paid' WHERE id = ?");
                $statusStmt->execute([$registrationId]);
            } catch (Exception $e) {
                error_log('Failed to auto-mark free registration as paid: ' . $e->getMessage());
            }
        }
        
        // Send webhook notification for new registration
        try {
            require_once 'webhook_service.php';
            $webhookService = new WebhookService($pdo);
            
            // Get full registration data
            $fullRegistrationData = $webhookService->getFullRegistrationData($registrationId);
            
            $webhookData = [
                'event_type' => 'registration_created',
                'trigger' => 'public_registration',
                'payment_method' => $data['paymentMethod'],
                'total_amount' => $totalAmount,
                'created_at' => date('c')
            ];
            
            // Merge with full registration data
            if ($fullRegistrationData) {
                $webhookData = array_merge($webhookData, $fullRegistrationData);
            }
            
            $webhookService->sendWebhook('registration_created', $webhookData, $registrationId);
            error_log("New registration webhook: Sent comprehensive webhook for registration $registrationNumber");
        } catch (Exception $e) {
            error_log('Webhook notification failed: ' . $e->getMessage());
            // Don't fail the registration if webhook fails
        }
        
        // Send pending registration email for bank transfers only
        if ($data['paymentMethod'] === 'tt' && !$isFreeRegistration) {
            try {
                require_once 'email_notifications.php';
                sendPendingRegistrationEmail($registrationId);
                
                // Send admin notification for new registration
                sendAdminNotification('new_registration', $registrationId);
            } catch (Exception $e) {
                error_log('Failed to send pending registration email: ' . $e->getMessage());
                // Don't fail the registration if email fails
            }
        } else {
            // For gateway payments, only send admin notification
            try {
                require_once 'email_notifications.php';
                sendAdminNotification('new_registration', $registrationId);
            } catch (Exception $e) {
                error_log('Failed to send admin notification: ' . $e->getMessage());
            }
        }
        
        return [
            'success' => true,
            'registrationNumber' => $registrationNumber,
            'paymentReference' => $paymentReference,
            'totalAmount' => $totalAmount,
            'paymentDeadline' => $paymentDeadline,
            'isFree' => $isFreeRegistration
        ];
        
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}

// Get configuration from database only (uses same logic as config_utils.php)
function getConfiguration($accessCode = null) {
    try {
        $pdo = getDBConnection();
        
        // Get all settings from database
        $stmt = $pdo->query("SELECT setting_key, setting_value, setting_type FROM admin_settings");
        $settings = $stmt->fetchAll();
        
        if (empty($settings)) {
            throw new Exception('No configuration found in database. Please run the migration script.');
        }
        
        // Config structure - all values loaded from database
        $config = [
            'pricing' => [],
            'registration' => [],
            'event' => [],
            'payment' => [],
            'dietaryOptions' => []
        ];
        
        // Map database settings to config structure
        foreach ($settings as $setting) {
            $key = $setting['setting_key'];
            $value = $setting['setting_value'];
            $type = $setting['setting_type'];
            
            // Convert value based on type
            if ($type === 'boolean') {
                $value = filter_var($value, FILTER_VALIDATE_BOOLEAN);
            } elseif ($type === 'number') {
                $value = is_numeric($value) ? (float)$value : $value;
            } elseif ($type === 'json') {
                $value = json_decode($value, true);
                if (json_last_error() !== JSON_ERROR_NONE) {
                    $value = [];
                }
            }
            
            // Map to config structure
            switch ($key) {
                case 'eventName':
                    $config['event']['name'] = $value;
                    break;
                case 'eventDates':
                    $config['event']['dates'] = $value;
                    break;
                case 'eventLocation':
                    $config['event']['location'] = $value;
                    break;
                case 'bankName':
                    $config['payment']['bankName'] = $value;
                    break;
                case 'accountName':
                    $config['payment']['accountName'] = $value;
                    break;
                case 'accountNumber':
                    $config['payment']['accountNumber'] = $value;
                    break;
                case 'swiftCode':
                    $config['payment']['swiftCode'] = $value;
                    break;
                case 'referencePrefix':
                    $config['payment']['referencePrefix'] = $value;
                    break;
                case 'dietaryOptions':
                    $config['dietaryOptions'] = $value;
                    break;
                case 'systemName':
                    $config['system']['name'] = $value;
                    break;
                case 'companyAddress':
                    $config['system']['companyAddress'] = $value;
                    break;
                case 'companyAddress2':
                    $config['system']['companyAddress2'] = $value;
                    break;
                case 'companyPhone':
                    $config['system']['companyPhone'] = $value;
                    break;
                case 'invoiceTermsConditions':
                    $config['system']['invoiceTermsConditions'] = $value;
                    break;
            }
        }
        
        // Add frontend display configuration for dynamic categories (with special access support)
        $config['frontend'] = getFrontendDisplayConfig($pdo, $accessCode);
        
        return $config;
        
    } catch (Exception $e) {
        error_log('Configuration error in API: ' . $e->getMessage());
        throw new Exception('Database configuration not available: ' . $e->getMessage());
    }
}

// Get frontend display configuration for categories
function getFrontendDisplayConfig($pdo, $accessCode = null) {
    try {
        // Handle special access links
        $specialAccessLink = null;
        $specialAccessProductIds = [];
        
        if ($accessCode) {
            // Validate and get special access link
            $stmt = $pdo->prepare("
                SELECT * FROM special_access_links 
                WHERE link_code = ? 
                AND is_active = 1 
                AND (expires_at IS NULL OR expires_at > NOW())
            ");
            $stmt->execute([$accessCode]);
            $specialAccessLink = $stmt->fetch(PDO::FETCH_ASSOC);
            
            if ($specialAccessLink) {
                // Track access
                $updateStmt = $pdo->prepare("
                    UPDATE special_access_links 
                    SET access_count = access_count + 1, 
                        last_accessed_at = NOW() 
                    WHERE id = ?
                ");
                $updateStmt->execute([$specialAccessLink['id']]);
                
                // Get product IDs from special link
                $specialAccessProductIds = json_decode($specialAccessLink['product_ids'], true) ?? [];
                error_log('Special access link used: ' . $accessCode . ' with ' . count($specialAccessProductIds) . ' products');
            } else {
                error_log('Invalid or expired special access link: ' . $accessCode);
            }
        }
        
        // Get all active product categories
        $stmt = $pdo->query("
            SELECT id, name, description, is_active 
            FROM product_categories 
            WHERE is_active = 1 
            ORDER BY name
        ");
        $allCategories = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Get frontend display configuration - get categories for each section
        $stmt = $pdo->query("
            SELECT fdc.page_section, fdc.category_id, fdc.delegate_description, fdc.table_description, pc.name, pc.description
            FROM frontend_display_config fdc
            LEFT JOIN product_categories pc ON fdc.category_id = pc.id
            WHERE fdc.is_active = 1
            ORDER BY fdc.page_section, fdc.display_order
        ");
        $displayConfigs = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Get all active products that are visible in frontend with their pricing tiers
        // OR products included in special access link
        try {
            $productQuery = "
                SELECT p.id, p.product_code, p.name, p.description, p.category_id, p.is_active, p.show_in_frontend,
                       p.base_price, p.currency, p.total_stock, p.max_per_registration,
                       pp.id as pricing_id, pp.pricing_tier, pp.price as tier_price, pp.valid_from, pp.valid_until, pp.is_active as pricing_active";
            
            // Add special access priority flag if accessing via special link
            if ($specialAccessLink && !empty($specialAccessProductIds)) {
                $placeholders = implode(',', array_fill(0, count($specialAccessProductIds), '?'));
                $productQuery .= ",
                       CASE WHEN p.id IN ($placeholders) THEN 0 ELSE 1 END as is_special_access";
            } else {
                $productQuery .= ",
                       1 as is_special_access";
            }
            
            $productQuery .= "
                FROM products p
                LEFT JOIN product_pricing pp ON p.id = pp.product_id AND pp.is_active = 1
                WHERE p.is_active = 1 AND (";
            
            if ($specialAccessLink) {
                // If special access link, show special products
                if (!empty($specialAccessProductIds)) {
                    $placeholders = implode(',', array_fill(0, count($specialAccessProductIds), '?'));
                    $productQuery .= "p.id IN ($placeholders)";
                    
                    // If include_visible_products is true, also show normally visible products
                    if ($specialAccessLink['include_visible_products']) {
                        $productQuery .= " OR p.show_in_frontend = 1";
                    }
                } else {
                    // No special products defined, just show normal visible ones
                    $productQuery .= "p.show_in_frontend = 1";
                }
            } else {
                // Normal mode - show only visible products
                $productQuery .= "p.show_in_frontend = 1";
            }
            
            // Order by: special access products first (0), then regular products (1), then by category and product ID
            $productQuery .= ") ORDER BY is_special_access ASC, p.category_id, p.id, pp.valid_from ASC";
            
            $stmt = $pdo->prepare($productQuery);
            
            // Bind parameters if we have special access products
            // Need to pass IDs twice: once for CASE statement, once for WHERE clause
            if ($specialAccessLink && !empty($specialAccessProductIds)) {
                // Combine the arrays: first for CASE statement, second for WHERE clause
                $executeParams = array_merge($specialAccessProductIds, $specialAccessProductIds);
                $stmt->execute($executeParams);
            } else {
                $stmt->execute();
            }
            
            $productData = $stmt->fetchAll(PDO::FETCH_ASSOC);
            error_log('Found ' . count($productData) . ' product rows' . ($accessCode ? ' with access code: ' . $accessCode : ''));
        } catch (Exception $e) {
            error_log('Product query error: ' . $e->getMessage());
            $productData = [];
        }
        
        // Get default tier names for all products
        $defaultTierNames = [];
        try {
            $stmt = $pdo->query("
                SELECT p.id as product_id, pp.pricing_tier as default_tier_name
                FROM products p
                LEFT JOIN product_pricing pp ON p.id = pp.product_id 
                WHERE p.is_active = 1 AND pp.valid_from IS NULL AND pp.valid_until IS NULL AND pp.is_active = 1
                ORDER BY pp.created_at ASC
            ");
            $defaultTiers = $stmt->fetchAll(PDO::FETCH_ASSOC);
            foreach ($defaultTiers as $tier) {
                $defaultTierNames[$tier['product_id']] = $tier['default_tier_name'];
            }
        } catch (Exception $e) {
            error_log('Default tier query error: ' . $e->getMessage());
        }
        
        // Organize products by category
        $productsByCategory = [];
        foreach ($productData as $row) {
            $categoryId = $row['category_id'];
            $productId = $row['id'];
            
            if (!isset($productsByCategory[$categoryId])) {
                $productsByCategory[$categoryId] = [];
            }
            
            if (!isset($productsByCategory[$categoryId][$productId])) {
                $productsByCategory[$categoryId][$productId] = [
                    'id' => $row['id'],
                    'product_code' => $row['product_code'],
                    'name' => $row['name'],
                    'description' => $row['description'],
                    'category_id' => $row['category_id'],
                    'is_active' => $row['is_active'],
                    'base_price' => (float)$row['base_price'],
                    'currency' => $row['currency'],
                    'total_stock' => $row['total_stock'],
                    'max_per_registration' => $row['max_per_registration'],
                    'default_tier_name' => isset($defaultTierNames[$productId]) ? $defaultTierNames[$productId] : null,
                    'is_special_access' => ($row['is_special_access'] == 0), // true if from special access link
                    'pricing_tiers' => []
                ];
            }
            
            if ($row['pricing_id']) {
                $currentDateTime = new DateTime();
                $validFrom = $row['valid_from'] ? new DateTime($row['valid_from']) : null;
                $validUntil = $row['valid_until'] ? new DateTime($row['valid_until']) : null;
                
                // Determine tier status based on current date
                $tierStatus = 'active';
                if ($validFrom && $currentDateTime < $validFrom) {
                    $tierStatus = 'upcoming';
                } elseif ($validUntil && $currentDateTime > $validUntil) {
                    $tierStatus = 'expired';
                }
                
                $productsByCategory[$categoryId][$productId]['pricing_tiers'][] = [
                    'id' => $row['pricing_id'],
                    'tier_name' => $row['pricing_tier'],
                    'price' => (float)$row['tier_price'],
                    'valid_from' => $row['valid_from'],
                    'valid_until' => $row['valid_until'],
                    'is_active' => $row['pricing_active'],
                    'status' => $tierStatus,
                    'is_current' => $tierStatus === 'active'
                ];
            }
        }
        
        $frontendConfig = [
            'categories' => $allCategories,
            'products' => $productsByCategory,
            'delegatePasses' => [
                'displayMode' => 'all',
                'selectedCategories' => [],
                'products' => [],
                'description' => ''
            ],
            'tablePresentations' => [
                'displayMode' => 'all', 
                'selectedCategories' => [],
                'products' => [],
                'description' => ''
            ],
            'sponsorship' => [
                'displayMode' => 'all',
                'selectedCategories' => [],
                'products' => [],
                'description' => ''
            ],
            'otherSponsorship' => [
                'displayMode' => 'all',
                'selectedCategories' => [],
                'products' => [],
                'description' => ''
            ]
        ];
        
        // Get descriptions from database first
        try {
            // Get section descriptions from section_descriptions table
            $stmtSectionDesc = $pdo->prepare("SELECT page_section, description FROM section_descriptions WHERE page_section IN ('delegate_passes', 'table_presentations', 'sponsorship', 'other_sponsorship')");
            $stmtSectionDesc->execute();
            $sectionDescriptions = $stmtSectionDesc->fetchAll(PDO::FETCH_KEY_PAIR);
            
            // Set descriptions for each section if available
            if (isset($sectionDescriptions['delegate_passes'])) {
                $frontendConfig['delegatePasses']['description'] = $sectionDescriptions['delegate_passes'];
            }
            
            if (isset($sectionDescriptions['table_presentations'])) {
                $frontendConfig['tablePresentations']['description'] = $sectionDescriptions['table_presentations'];
            }
            
            if (isset($sectionDescriptions['sponsorship'])) {
                $frontendConfig['sponsorship']['description'] = $sectionDescriptions['sponsorship'];
            }
            
            if (isset($sectionDescriptions['other_sponsorship'])) {
                $frontendConfig['otherSponsorship']['description'] = $sectionDescriptions['other_sponsorship'];
            }
            
        } catch (Exception $e) {
            // If description queries fail, continue without descriptions
            error_log("Description query error: " . $e->getMessage());
        }

        // Process display configurations
        $delegateCategories = [];
        $tableCategories = [];
        $sponsorshipCategories = [];
        $otherSponsorshipCategories = [];
        
        foreach ($displayConfigs as $config) {
            if ($config['page_section'] === 'delegate_passes') {
                if ($config['category_id'] === null) {
                    // NULL category_id means show all categories
                    $frontendConfig['delegatePasses']['displayMode'] = 'all';
                } else {
                    $delegateCategories[] = $config['category_id'];
                }
            } elseif ($config['page_section'] === 'table_presentations') {
                if ($config['category_id'] === null) {
                    // NULL category_id means show all categories
                    $frontendConfig['tablePresentations']['displayMode'] = 'all';
                } else {
                    $tableCategories[] = $config['category_id'];
                }
            } elseif ($config['page_section'] === 'sponsorship') {
                if ($config['category_id'] === null) {
                    // NULL category_id means show all categories
                    $frontendConfig['sponsorship']['displayMode'] = 'all';
                } else {
                    $sponsorshipCategories[] = $config['category_id'];
                }
            } elseif ($config['page_section'] === 'other_sponsorship') {
                if ($config['category_id'] === null) {
                    // NULL category_id means show all categories
                    $frontendConfig['otherSponsorship']['displayMode'] = 'all';
                } else {
                    $otherSponsorshipCategories[] = $config['category_id'];
                }
            }
        }
        
        // If we have specific categories selected, change to 'selected' mode and get products
        if (!empty($delegateCategories)) {
            $delegateProducts = [];
            foreach ($delegateCategories as $categoryId) {
                if (isset($productsByCategory[$categoryId])) {
                    $delegateProducts = array_merge($delegateProducts, array_values($productsByCategory[$categoryId]));
                }
            }
            
            $frontendConfig['delegatePasses'] = [
                'displayMode' => 'selected',
                'selectedCategories' => $delegateCategories,
                'products' => $delegateProducts,
                'description' => $frontendConfig['delegatePasses']['description']
            ];
        } else if ($frontendConfig['delegatePasses']['displayMode'] === 'all') {
            // Show all products from all categories
            $allProducts = [];
            foreach ($productsByCategory as $categoryProducts) {
                $allProducts = array_merge($allProducts, array_values($categoryProducts));
            }
            $frontendConfig['delegatePasses']['products'] = $allProducts;
        }
        
        if (!empty($tableCategories)) {
            $tableProducts = [];
            foreach ($tableCategories as $categoryId) {
                if (isset($productsByCategory[$categoryId])) {
                    $tableProducts = array_merge($tableProducts, array_values($productsByCategory[$categoryId]));
                }
            }
            
            $frontendConfig['tablePresentations'] = [
                'displayMode' => 'selected',
                'selectedCategories' => $tableCategories,
                'products' => $tableProducts,
                'description' => $frontendConfig['tablePresentations']['description']
            ];
        } else if ($frontendConfig['tablePresentations']['displayMode'] === 'all') {
            // Show all products from all categories
            $allProducts = [];
            foreach ($productsByCategory as $categoryProducts) {
                $allProducts = array_merge($allProducts, array_values($categoryProducts));
            }
            $frontendConfig['tablePresentations']['products'] = $allProducts;
        }
        
        // Process sponsorship products
        if (!empty($sponsorshipCategories)) {
            $sponsorshipProducts = [];
            foreach ($sponsorshipCategories as $categoryId) {
                if (isset($productsByCategory[$categoryId])) {
                    $sponsorshipProducts = array_merge($sponsorshipProducts, array_values($productsByCategory[$categoryId]));
                }
            }
            
            $frontendConfig['sponsorship'] = [
                'displayMode' => 'selected',
                'selectedCategories' => $sponsorshipCategories,
                'products' => $sponsorshipProducts,
                'description' => $frontendConfig['sponsorship']['description']
            ];
        } else if ($frontendConfig['sponsorship']['displayMode'] === 'all') {
            // Show all products from all categories
            $allProducts = [];
            foreach ($productsByCategory as $categoryProducts) {
                $allProducts = array_merge($allProducts, array_values($categoryProducts));
            }
            $frontendConfig['sponsorship']['products'] = $allProducts;
        }
        
        // Process other sponsorship products
        if (!empty($otherSponsorshipCategories)) {
            $otherSponsorshipProducts = [];
            foreach ($otherSponsorshipCategories as $categoryId) {
                if (isset($productsByCategory[$categoryId])) {
                    $otherSponsorshipProducts = array_merge($otherSponsorshipProducts, array_values($productsByCategory[$categoryId]));
                }
            }
            
            $frontendConfig['otherSponsorship'] = [
                'displayMode' => 'selected',
                'selectedCategories' => $otherSponsorshipCategories,
                'products' => $otherSponsorshipProducts,
                'description' => $frontendConfig['otherSponsorship']['description']
            ];
        } else if ($frontendConfig['otherSponsorship']['displayMode'] === 'all') {
            // Show all products from all categories
            $allProducts = [];
            foreach ($productsByCategory as $categoryProducts) {
                $allProducts = array_merge($allProducts, array_values($categoryProducts));
            }
            $frontendConfig['otherSponsorship']['products'] = $allProducts;
        }
        
        // Add special access information if applicable
        if ($specialAccessLink) {
            $frontendConfig['specialAccess'] = [
                'enabled' => true,
                'customMessage' => $specialAccessLink['custom_message'] ?? 'You have exclusive access to additional registration options',
                'linkName' => $specialAccessLink['link_name']
            ];
        }
        
        return $frontendConfig;
        
    } catch (Exception $e) {
        error_log('Frontend config error: ' . $e->getMessage());
        // Return default configuration if there's an error
        return [
            'categories' => [],
            'delegatePasses' => ['displayMode' => 'all', 'selectedCategories' => [], 'products' => [], 'description' => ''],
            'tablePresentations' => ['displayMode' => 'all', 'selectedCategories' => [], 'products' => [], 'description' => ''],
            'sponsorship' => ['displayMode' => 'all', 'selectedCategories' => [], 'products' => [], 'description' => ''],
            'otherSponsorship' => ['displayMode' => 'all', 'selectedCategories' => [], 'products' => [], 'description' => '']
        ];
    }
}

// Generate payment reference
function generatePaymentReference($paymentMethod, $registrationNumber) {
    if ($paymentMethod === 'tt') {
        return $registrationNumber;
    } else {
        return 'GW-' . $registrationNumber;
    }
}

// Get registration statistics
function getRegistrationStats() {
    $pdo = getDBConnection();
    
    $stmt = $pdo->query("SELECT * FROM registration_stats");
    $stats = $stmt->fetch();
    
    $dietaryStmt = $pdo->query("SELECT * FROM dietary_summary");
    $dietary = $dietaryStmt->fetchAll();
    
    $assistanceStmt = $pdo->query("SELECT COUNT(*) as count FROM special_assistance_summary");
    $assistance = $assistanceStmt->fetch();
    
    return [
        'overview' => $stats,
        'dietary' => $dietary,
        'specialAssistance' => $assistance['count']
    ];
}

// Get Stripe publishable key
function getStripePublishableKey() {
    try {
        require_once 'stripe_config.php';
        
        $publishableKey = StripeConfig::getPublishableKey();
        
        // Check if key is configured
        if ($publishableKey === 'pk_test_YOUR_PUBLISHABLE_KEY_HERE') {
            return [
                'success' => false,
                'error' => 'Stripe not configured'
            ];
        }
        
        return [
            'success' => true,
            'publishable_key' => $publishableKey
        ];
        
    } catch (Exception $e) {
        return [
            'success' => false,
            'error' => 'Failed to load Stripe configuration'
        ];
    }
}

// Create Stripe checkout session
function createStripeSession($data) {
    try {
        require_once 'stripe_config.php';
        require_once 'vendor/autoload.php';
        
        \Stripe\Stripe::setApiKey(StripeConfig::getSecretKey());
        
        // Get pricing configuration with access code support for special access links
        $accessCode = !empty($data['accessCode']) ? $data['accessCode'] : null;
        $config = getConfiguration($accessCode);
        if (!$config || !isset($config['frontend'])) {
            throw new Exception('Unable to load pricing configuration');
        }
        
        // Prepare line items
        $lineItems = [];
        $delegateTotal = 0;
        
        // Add delegate passes from new structure
        if (isset($data['delegates']) && !empty($data['delegates'])) {
            foreach ($data['delegates'] as $delegateSelection) {
                $delegateType = $delegateSelection['type'];
                $delegateCount = intval($delegateSelection['count']);
                $delegatePrice = 0;
                $delegateName = '';
                
                // Find the delegate product price from configuration
                if (isset($config['frontend']['delegatePasses']['products'])) {
                    foreach ($config['frontend']['delegatePasses']['products'] as $product) {
                        if ($product['product_code'] === $delegateType) {
                            // Find current active tier or use base price
                            $activeTier = null;
                            if (isset($product['pricing_tiers'])) {
                                foreach ($product['pricing_tiers'] as $tier) {
                                    if ($tier['is_current']) {
                                        $activeTier = $tier;
                                        break;
                                    }
                                }
                            }
                            $delegatePrice = $activeTier ? $activeTier['price'] : $product['base_price'];
                            $delegateName = $product['name'];
                            break;
                        }
                    }
                }
                
                if ($delegatePrice > 0 && $delegateCount > 0) {
                    $delegateTotal += $delegatePrice * $delegateCount;
                    
                    $lineItems[] = [
                        'price_data' => [
                            'currency' => StripeConfig::getCurrency(),
                            'product_data' => [
                                'name' => $delegateName,
                                'description' => 'Registration: ' . $data['registrationNumber'] . ' | Access to all sessions, exhibits, and networking',
                            ],
                            'unit_amount' => $delegatePrice * 100, // Convert to cents
                        ],
                        'quantity' => $delegateCount,
                    ];
                }
            }
        }
        
        // Add table-top presentations if selected
        $tableTotal = 0;
        if (isset($data['tables']) && !empty($data['tables'])) {
            foreach ($data['tables'] as $tableSelection) {
                $tableType = $tableSelection['type'];
                $tableCount = intval($tableSelection['count']);
                $tablePrice = 0;
                $tableName = '';
                $tableDescription = '';
                
                // Find the table product price from configuration
                if (isset($config['frontend']['tablePresentations']['products'])) {
                    foreach ($config['frontend']['tablePresentations']['products'] as $product) {
                        if ($product['product_code'] === $tableType) {
                            $tablePrice = $product['base_price'];
                            $tableName = $product['name'];
                            // Clean HTML tags from description for Stripe
                            $tableDescription = strip_tags($product['description'] ?? '');
                            break;
                        }
                    }
                }
                
                if ($tablePrice > 0 && $tableCount > 0) {
                    $tableTotal += $tablePrice * $tableCount;
                    
                    $lineItems[] = [
                        'price_data' => [
                            'currency' => StripeConfig::getCurrency(),
                            'product_data' => [
                                'name' => $tableName,
                                'description' => 'Registration: ' . $data['registrationNumber'] . ' | ' . $tableDescription,
                            ],
                            'unit_amount' => $tablePrice * 100, // Convert to cents
                        ],
                        'quantity' => $tableCount,
                    ];
                }
            }
        }
        
        // Add sponsorship products if selected
        $sponsorshipTotal = 0;
        if (isset($data['sponsorships']) && !empty($data['sponsorships'])) {
            foreach ($data['sponsorships'] as $sponsorshipSelection) {
                $sponsorshipType = $sponsorshipSelection['type'];
                $sponsorshipCount = intval($sponsorshipSelection['count']);
                $sponsorshipPrice = 0;
                $sponsorshipName = '';
                $sponsorshipDescription = '';
                
                // Find the sponsorship product price from configuration
                if (isset($config['frontend']['sponsorship']['products'])) {
                    foreach ($config['frontend']['sponsorship']['products'] as $product) {
                        if ($product['product_code'] === $sponsorshipType) {
                            $sponsorshipPrice = $product['base_price'];
                            $sponsorshipName = $product['name'];
                            // Clean HTML tags from description for Stripe
                            $sponsorshipDescription = strip_tags($product['description'] ?? '');
                            break;
                        }
                    }
                }
                
                if ($sponsorshipPrice > 0 && $sponsorshipCount > 0) {
                    $sponsorshipTotal += $sponsorshipPrice * $sponsorshipCount;
                    
                    $lineItems[] = [
                        'price_data' => [
                            'currency' => StripeConfig::getCurrency(),
                            'product_data' => [
                                'name' => $sponsorshipName,
                                'description' => 'Registration: ' . $data['registrationNumber'] . ' | ' . $sponsorshipDescription,
                            ],
                            'unit_amount' => $sponsorshipPrice * 100, // Convert to cents
                        ],
                        'quantity' => $sponsorshipCount,
                    ];
                }
            }
        }
        
        // Add other sponsorship products if selected
        $otherSponsorshipTotal = 0;
        if (isset($data['otherSponsorships']) && !empty($data['otherSponsorships'])) {
            foreach ($data['otherSponsorships'] as $otherSponsorshipSelection) {
                $otherSponsorshipType = $otherSponsorshipSelection['type'];
                $otherSponsorshipCount = intval($otherSponsorshipSelection['count']);
                $otherSponsorshipPrice = 0;
                $otherSponsorshipName = '';
                $otherSponsorshipDescription = '';
                
                // Find the other sponsorship product price from configuration
                if (isset($config['frontend']['otherSponsorship']['products'])) {
                    foreach ($config['frontend']['otherSponsorship']['products'] as $product) {
                        if ($product['product_code'] === $otherSponsorshipType) {
                            $otherSponsorshipPrice = $product['base_price'];
                            $otherSponsorshipName = $product['name'];
                            // Clean HTML tags from description for Stripe
                            $otherSponsorshipDescription = strip_tags($product['description'] ?? '');
                            break;
                        }
                    }
                }
                
                if ($otherSponsorshipPrice > 0 && $otherSponsorshipCount > 0) {
                    $otherSponsorshipTotal += $otherSponsorshipPrice * $otherSponsorshipCount;
                    
                    $lineItems[] = [
                        'price_data' => [
                            'currency' => StripeConfig::getCurrency(),
                            'product_data' => [
                                'name' => $otherSponsorshipName,
                                'description' => 'Registration: ' . $data['registrationNumber'] . ' | ' . $otherSponsorshipDescription,
                            ],
                            'unit_amount' => $otherSponsorshipPrice * 100, // Convert to cents
                        ],
                        'quantity' => $otherSponsorshipCount,
                    ];
                }
            }
        }
        
        // Create checkout session
        $session = \Stripe\Checkout\Session::create([
            'payment_method_types' => ['card'],
            'line_items' => $lineItems,
            'mode' => 'payment',
            'success_url' => StripeConfig::getSuccessUrl() . '?session_id={CHECKOUT_SESSION_ID}&registration_number=' . $data['registrationNumber'],
            'cancel_url' => StripeConfig::getCancelUrl() . '?registration_number=' . $data['registrationNumber'],
            'metadata' => [
                'registration_number' => $data['registrationNumber'],
                'delegates' => !empty($data['delegates']) ? json_encode($data['delegates']) : '',
                'tables' => !empty($data['tables']) ? json_encode($data['tables']) : '',
                'sponsorships' => !empty($data['sponsorships']) ? json_encode($data['sponsorships']) : '',
                'other_sponsorships' => !empty($data['otherSponsorships']) ? json_encode($data['otherSponsorships']) : '',
                'contact_email' => $data['contactEmail'] ?? '',
                'university' => $data['university'] ?? ''
            ],
            'customer_email' => $data['contactEmail'] ?? null,
            'billing_address_collection' => 'auto',
            'payment_intent_data' => [
                'description' => 'XChange 2025 Registration: ' . $data['registrationNumber'],
                'statement_descriptor_suffix' => substr($data['registrationNumber'], -10), // Last 10 chars for bank statement
                'metadata' => [
                    'registration_number' => $data['registrationNumber'],
                    'event' => 'XChange 2025',
                    'university' => $data['university'] ?? ''
                ]
            ],
        ]);
        
        // Store checkout session in payment_transactions table
        $pdo = new PDO(
            "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME,
            DB_USER,
            DB_PASS,
            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
        );
        
        // Get registration_id from registration_number
        $regStmt = $pdo->prepare("SELECT id FROM registrations WHERE registration_number = ?");
        $regStmt->execute([$data['registrationNumber']]);
        $registration = $regStmt->fetch();
        $registrationId = $registration ? $registration['id'] : null;
        
        $stmt = $pdo->prepare("
            INSERT INTO payment_transactions 
            (registration_id, registration_number, transaction_type, gateway_transaction_id, amount, currency, transaction_status, created_at) 
            VALUES (?, ?, 'gateway', ?, ?, ?, 'pending', NOW())
        ");
        
        // Calculate total amount from all products
        $totalAmount = $delegateTotal + $tableTotal + $sponsorshipTotal + $otherSponsorshipTotal;
        
        $stmt->execute([
            $registrationId,
            $data['registrationNumber'],
            $session->id,
            $totalAmount,
            StripeConfig::getCurrency()
        ]);
        
        return [
            'success' => true,
            'session_id' => $session->id,
            'checkout_url' => $session->url
        ];
        
    } catch (\Stripe\Exception\ApiErrorException $e) {
        error_log('Stripe API Error: ' . $e->getMessage());
        return [
            'success' => false,
            'error' => 'Payment setup failed: ' . $e->getMessage()
        ];
    } catch (Exception $e) {
        error_log('Stripe Session Error: ' . $e->getMessage());
        return [
            'success' => false,
            'error' => 'Payment setup failed: ' . $e->getMessage()
        ];
    }
}

// Error handling
function handleError($message, $code = 400) {
    http_response_code($code);
    echo json_encode(['error' => $message]);
    exit;
}

/**
 * Maps delegate type product codes to valid ENUM values for the database
 * The registrations.delegate_type column is ENUM('earlyBird','standard')
 * 
 * @param string $productCode The product code from the form submission
 * @return string A valid ENUM value ('earlyBird' or 'standard')
 */
function mapDelegateTypeToEnum($productCode) {
    // Convert product code to lowercase for case-insensitive comparison
    $lcProductCode = strtolower($productCode);
    
    // Map product codes to valid ENUM values
    if (strpos($lcProductCode, 'early') !== false || strpos($lcProductCode, 'bird') !== false) {
        return 'earlyBird';
    } else {
        // Default to 'standard' for any other product code
        return 'standard';
    }
}
?>
