DeserializeJSON

DeserializeJSON(JSONVar[, strictMapping, useCustomSerializer, preserveCaseForStructKey])

<cfscript>
  record=deserializeJSON(
'{
"firstname": "John",
"lastname": "Smith",
"age": "36",
"address":{
      "number":"12345",
      "street":"my_street",
      "city":"any city"
}
}'
);
  writeOutput(record.firstname & " ");
  writeOutput(record.lastname & " ");
  writeOutput(record.address.number & " ");
  writeOutput(record.address.street & " ");
</cfscript>

<cfscript>
    jsonRecord='{
        "name": "Sherlock",
        "NAME": "Holmes",
        "age": "36",
        "address":{
            "Number":"221B",
            "Street":"Baker Street",
            "City":"London"
        }
    }'
    deserializedRecord=deserializeJSON(json=jsonRecord,preserveCaseForStructKey=false)
    writeDump(deserializedRecord)
    // preserveCaseForStructKey=TRUE or FALSE. FALSE by default
    writeOutput("<br/>" & "Output with preserveCaseForStructKey=TRUE" & "<br/>")
    deserializedRecordCaseStructKey=deserializeJSON(json=jsonRecord,preserveCaseForStructKey=true)
    writeDump(deserializedRecordCaseStructKey)
</cfscript>

<cfscript>
    myJSON=
    '
    [
      {
        "id": "1",
        "name": "One",
        "amount": "15"
      },
      {
        "id": "2",
        "name": "Two",
        "amount": "18"
      },
      {
        "id": "3",
        "name": "Three",
        "amount": "32"
      }
    ]
    '
    q=deserializeJSON(myJSON,"query")
    writeDump(q)
</cfscript>

<cfscript>

function processShoppingCart(cartJsonData, customerType = "regular") {
    try {
        // Deserialize cart JSON with validation
        cart = DeserializeJSON(cartJsonData);
        
        // Validate cart structure
        if (!structKeyExists(cart, "customer") || !structKeyExists(cart, "items")) {
            return {
                success: false,
                error: "Invalid cart structure",
                code: "CART_INVALID_STRUCTURE"
            };
        }
        
        if (!isArray(cart.items) || arrayLen(cart.items) == 0) {
            return {
                success: false,
                error: "Cart is empty or items not in array format",
                code: "CART_EMPTY"
            };
        }
        
        // Initialize cart processing
        processedCart = {
            customer: {
                id: cart.customer.id ?: createUUID(),
                name: cart.customer.name ?: "Guest Customer",
                email: cart.customer.email ?: "",
                membershipLevel: cart.customer.membershipLevel ?: "standard",
                address: cart.customer.address ?: {}
            },
            items: [],
            pricing: {
                subtotal: 0,
                discountTotal: 0,
                taxableAmount: 0,
                taxAmount: 0,
                shippingCost: 0,
                finalTotal: 0
            },
            discounts: [],
            shipping: {},
            metadata: {
                processedAt: now(),
                currency: cart.currency ?: "USD",
                source: cart.source ?: "web"
            }
        };
        
        // Process each cart item
        for (itemIndex = 1; itemIndex <= arrayLen(cart.items); itemIndex++) {
            item = cart.items[itemIndex];
            
            // Validate item structure
            if (!structKeyExists(item, "productId") || !structKeyExists(item, "price") || !structKeyExists(item, "quantity")) {
                continue; // Skip invalid items
            }
            
            // Calculate item pricing
            unitPrice = val(item.price);
            quantity = val(item.quantity);
            lineSubtotal = unitPrice * quantity;
            
            // Apply item-level discounts
            itemDiscount = 0;
            discountReason = "";
            
            // Bulk discount (5+ items)
            if (quantity >= 5) {
                itemDiscount = lineSubtotal * 0.05; // 5% bulk discount
                discountReason = "Bulk discount (5%)";
            }
            
            // Membership discounts
            switch (processedCart.customer.membershipLevel) {
                case "premium":
                    memberDiscount = lineSubtotal * 0.10; // 10% premium discount
                    if (memberDiscount > itemDiscount) {
                        itemDiscount = memberDiscount;
                        discountReason = "Premium member discount (10%)";
                    }
                    break;
                case "gold":
                    memberDiscount = lineSubtotal * 0.15; // 15% gold discount
                    if (memberDiscount > itemDiscount) {
                        itemDiscount = memberDiscount;
                        discountReason = "Gold member discount (15%)";
                    }
                    break;
                case "vip":
                    memberDiscount = lineSubtotal * 0.20; // 20% VIP discount
                    if (memberDiscount > itemDiscount) {
                        itemDiscount = memberDiscount;
                        discountReason = "VIP member discount (20%)";
                    }
                    break;
            }
            
            lineFinalPrice = lineSubtotal - itemDiscount;
            
            // Build processed item
            processedItem = {
                productId: item.productId,
                productName: item.productName ?: "Unknown Product",
                sku: item.sku ?: "",
                category: item.category ?: "general",
                unitPrice: unitPrice,
                quantity: quantity,
                subtotal: lineSubtotal,
                discount: itemDiscount,
                discountReason: discountReason,
                finalPrice: lineFinalPrice,
                taxable: item.taxable ?: true,
                weight: val(item.weight ?: 0),
                dimensions: item.dimensions ?: {}
            };
            
            arrayAppend(processedCart.items, processedItem);
            
            // Update cart totals
            processedCart.pricing.subtotal += lineSubtotal;
            processedCart.pricing.discountTotal += itemDiscount;
            
            // Track discounts applied
            if (itemDiscount > 0) {
                arrayAppend(processedCart.discounts, {
                    productId: item.productId,
                    discountAmount: itemDiscount,
                    reason: discountReason
                });
            }
        }
        
        // Calculate taxable amount
        processedCart.pricing.taxableAmount = processedCart.pricing.subtotal - processedCart.pricing.discountTotal;
        
        // Apply cart-level promotions
        cartLevelDiscount = 0;
        
        // Free shipping promotion
        if (processedCart.pricing.taxableAmount >= 100) {
            processedCart.shipping.freeShippingApplied = true;
            processedCart.shipping.freeShippingReason = "Orders over $100 qualify for free shipping";
        }
        
        // First-time customer discount
        if (structKeyExists(cart.customer, "isFirstTime") && cart.customer.isFirstTime) {
            cartLevelDiscount = min(processedCart.pricing.taxableAmount * 0.10, 50); // 10% up to $50
            arrayAppend(processedCart.discounts, {
                type: "cart_level",
                discountAmount: cartLevelDiscount,
                reason: "First-time customer discount (10%)"
            });
        }
        
        processedCart.pricing.discountTotal += cartLevelDiscount;
        processedCart.pricing.taxableAmount -= cartLevelDiscount;
        
        // Calculate tax (example: 8.5% sales tax)
        taxRate = 0.085;
        if (structKeyExists(cart.customer, "address") && structKeyExists(cart.customer.address, "state")) {
            // State-specific tax rates
            stateTaxRates = {
                "CA": 0.0975,
                "NY": 0.08,
                "TX": 0.0625,
                "FL": 0.06,
                "WA": 0.065
            };
            
            if (structKeyExists(stateTaxRates, cart.customer.address.state)) {
                taxRate = stateTaxRates[cart.customer.address.state];
            }
        }
        
        processedCart.pricing.taxAmount = processedCart.pricing.taxableAmount * taxRate;
        
        // Calculate shipping cost
        if (!processedCart.shipping.freeShippingApplied) {
            totalWeight = 0;
            for (item in processedCart.items) {
                totalWeight += item.weight * item.quantity;
            }
            
            // Weight-based shipping
            if (totalWeight <= 1) {
                processedCart.pricing.shippingCost = 5.99;
            } else if (totalWeight <= 5) {
                processedCart.pricing.shippingCost = 9.99;
            } else if (totalWeight <= 10) {
                processedCart.pricing.shippingCost = 14.99;
            } else {
                processedCart.pricing.shippingCost = 19.99;
            }
            
            // Expedited shipping option
            if (structKeyExists(cart, "shipping") && cart.shipping.expedited) {
                processedCart.pricing.shippingCost *= 2; // Double for expedited
                processedCart.shipping.expedited = true;
                processedCart.shipping.estimatedDelivery = "1-2 business days";
            } else {
                processedCart.shipping.estimatedDelivery = "5-7 business days";
            }
        } else {
            processedCart.pricing.shippingCost = 0;
        }
        
        // Calculate final total
        processedCart.pricing.finalTotal = processedCart.pricing.taxableAmount + 
                                           processedCart.pricing.taxAmount + 
                                           processedCart.pricing.shippingCost;
        
        // Add cart metadata
        processedCart.metadata.itemCount = arrayLen(processedCart.items);
        processedCart.metadata.totalQuantity = 0;
        for (item in processedCart.items) {
            processedCart.metadata.totalQuantity += item.quantity;
        }
        
        return {
            success: true,
            cart: processedCart,
            summary: {
                itemCount: processedCart.metadata.itemCount,
                totalQuantity: processedCart.metadata.totalQuantity,
                subtotal: processedCart.pricing.subtotal,
                discounts: processedCart.pricing.discountTotal,
                tax: processedCart.pricing.taxAmount,
                shipping: processedCart.pricing.shippingCost,
                total: processedCart.pricing.finalTotal
            }
        };
        
    } catch (any e) {
        return {
            success: false,
            error: "Cart processing failed: " & e.message,
            code: "CART_PROCESSING_ERROR",
            detail: e.detail ?: ""
        };
    }
}

// =============================================================================
// SAMPLE USAGE
// =============================================================================

// Sample complex cart JSON
cartJson = '{
    "customer": {
        "id": "CUST-12345",
        "name": "Sarah Johnson",
        "email": "sarah@example.com",
        "membershipLevel": "premium",
        "isFirstTime": false,
        "address": {
            "street": "123 Main St",
            "city": "Los Angeles",
            "state": "CA",
            "zipCode": "90210"
        }
    },
    "items": [
        {
            "productId": "PROD-001",
            "productName": "Premium Laptop",
            "sku": "LP-001",
            "category": "electronics",
            "price": 1299.99,
            "quantity": 1,
            "weight": 4.5,
            "taxable": true
        },
        {
            "productId": "PROD-002", 
            "productName": "Wireless Mouse",
            "sku": "MS-002",
            "category": "accessories",
            "price": 49.99,
            "quantity": 2,
            "weight": 0.3,
            "taxable": true
        },
        {
            "productId": "PROD-003",
            "productName": "USB-C Cable",
            "sku": "CB-003",
            "category": "accessories", 
            "price": 19.99,
            "quantity": 3,
            "weight": 0.2,
            "taxable": true
        }
    ],
    "currency": "USD",
    "source": "mobile_app",
    "shipping": {
        "expedited": false
    }
}';

// Execute cart processing
cartResult = processShoppingCart(cartJson);

// Display results
if (cartResult.success) {
    writeOutput("<h2>🛒 Shopping Cart Processing Results</h2>");
    writeOutput("<h3>Cart Summary</h3>");
    writeOutput("<p><strong>Items:</strong> " & cartResult.summary.itemCount & "</p>");
    writeOutput("<p><strong>Total Quantity:</strong> " & cartResult.summary.totalQuantity & "</p>");
    writeOutput("<p><strong>Subtotal:</strong> $" & numberFormat(cartResult.summary.subtotal, "999.99") & "</p>");
    writeOutput("<p><strong>Discounts:</strong> -$" & numberFormat(cartResult.summary.discounts, "999.99") & "</p>");
    writeOutput("<p><strong>Tax:</strong> $" & numberFormat(cartResult.summary.tax, "999.99") & "</p>");
    writeOutput("<p><strong>Shipping:</strong> $" & numberFormat(cartResult.summary.shipping, "999.99") & "</p>");
    writeOutput("<p><strong>Final Total:</strong> $" & numberFormat(cartResult.summary.total, "999.99") & "</p>");
    
    writeOutput("<h3>Customer Information</h3>");
    writeOutput("<p><strong>Name:</strong> " & cartResult.cart.customer.name & "</p>");
    writeOutput("<p><strong>Membership:</strong> " & cartResult.cart.customer.membershipLevel & "</p>");
    writeOutput("<p><strong>Email:</strong> " & cartResult.cart.customer.email & "</p>");
} else {
    writeOutput("<h2>❌ Cart Processing Error</h2>");
    writeOutput("<p><strong>Error:</strong> " & cartResult.error & "</p>");
    writeOutput("<p><strong>Code:</strong> " & cartResult.code & "</p>");
}

</cfscript>

<cfscript>

// =============================================================================
// SAMPLE LEAD JSON DATA
// =============================================================================
leadJson = '{
    "firstName": "Michael",
    "lastName": "Chen", 
    "emailAddress": "michael.chen@techsolutions.com",
    "phoneNumber": "5551234567",
    "companyName": "Tech Solutions Inc",
    "jobTitle": "VP Engineering",
    "industry": "Software",
    "employeeCount": 250,
    "annualRevenue": 5000000,
    "address": {
        "street": "123 Innovation Drive",
        "city": "San Francisco",
        "state": "CA",
        "zipCode": "94105"
    },
    "interestLevel": "high",
    "budgetRange": "50000-100000"
}';

try {
    // Test basic deserialization
    lead = DeserializeJSON(leadJson);
    
    writeOutput("<p class='success'><strong>✅ JSON Deserialization:</strong> SUCCESS</p>");
    writeOutput("<p><strong>First Name:</strong> " & lead.firstName & "</p>");
    writeOutput("<p><strong>Last Name:</strong> " & lead.lastName & "</p>");
    writeOutput("<p><strong>Company:</strong> " & lead.companyName & "</p>");
    writeOutput("<p><strong>Email:</strong> " & lead.emailAddress & "</p>");
    
    // Test case-sensitive deserialization 
    leadCaseSensitive = DeserializeJSON(leadJson, true, true, true);
    writeOutput("<p class='success'><strong>✅ Case-Sensitive Deserialization:</strong> SUCCESS</p>");
    
} catch (any e) {
    writeOutput("<p class='error'><strong>❌ JSON Deserialization:</strong> FAILED</p>");
    writeOutput("<p class='error'><strong>Error:</strong> " & e.message & "</p>");
}

// =============================================================================
// LEAD PROCESSING FUNCTION (SIMPLIFIED)
// =============================================================================
function processLead(leadData) {
    result = {
        success: false,
        processedData: {},
        salesforceRecord: {},
        errors: [],
        warnings: []
    };
    
    try {
        // Basic validation
        if (!structKeyExists(leadData, "firstName") || len(trim(leadData.firstName)) == 0) {
            arrayAppend(result.errors, "First name is required");
        }
        if (!structKeyExists(leadData, "lastName") || len(trim(leadData.lastName)) == 0) {
            arrayAppend(result.errors, "Last name is required");
        }
        if (!structKeyExists(leadData, "emailAddress") || len(trim(leadData.emailAddress)) == 0) {
            arrayAppend(result.errors, "Email address is required");
        }
        if (!structKeyExists(leadData, "companyName") || len(trim(leadData.companyName)) == 0) {
            arrayAppend(result.errors, "Company name is required");
        }
        
        // If no errors, process the data
        if (arrayLen(result.errors) == 0) {
            
            // Clean and format data
            result.processedData = {
                firstName: uCase(left(leadData.firstName, 1)) & lCase(mid(leadData.firstName, 2, len(leadData.firstName))),
                lastName: uCase(left(leadData.lastName, 1)) & lCase(mid(leadData.lastName, 2, len(leadData.lastName))),
                email: lCase(leadData.emailAddress),
                company: leadData.companyName,
                jobTitle: leadData.jobTitle ?: "",
                industry: leadData.industry ?: "",
                phone: leadData.phoneNumber ?: ""
            };
            
            // Calculate lead score
            score = 0;
            if (structKeyExists(leadData, "employeeCount") && val(leadData.employeeCount) > 100) {
                score += 25;
            }
            if (structKeyExists(leadData, "annualRevenue") && val(leadData.annualRevenue) > 1000000) {
                score += 30;
            }
            if (structKeyExists(leadData, "jobTitle") && 
                (findNoCase("VP", leadData.jobTitle) || findNoCase("Director", leadData.jobTitle))) {
                score += 20;
            }
            if (structKeyExists(leadData, "interestLevel") && leadData.interestLevel == "high") {
                score += 25;
            }
            
            result.processedData.leadScore = score;
            
            // Determine status based on score
            if (score >= 75) {
                result.processedData.status = "Hot";
            } else if (score >= 50) {
                result.processedData.status = "Warm";
            } else if (score >= 25) {
                result.processedData.status = "Qualified";
            } else {
                result.processedData.status = "New";
            }
            
            // Create Salesforce record structure
            result.salesforceRecord = {
                "FirstName": result.processedData.firstName,
                "LastName": result.processedData.lastName,
                "Email": result.processedData.email,
                "Company": result.processedData.company,
                "Title": result.processedData.jobTitle,
                "Industry": result.processedData.industry,
                "Phone": result.processedData.phone,
                "Status": result.processedData.status,
                "Lead_Score__c": result.processedData.leadScore
            };
            
            result.success = true;
        }
        
    } catch (any e) {
        arrayAppend(result.errors, "Processing failed: " & e.message);
    }
    
    return result;
}

// =============================================================================
// EXECUTE LEAD PROCESSING
// =============================================================================
writeOutput("<h2>🏢 Lead Processing Results</h2>");

try {
    // Process the lead
    processedLead = processLead(lead);
    
    if (processedLead.success) {
        writeOutput("<p class='success'><strong>✅ Lead Processing:</strong> SUCCESS</p>");
        
        writeOutput("<h3>📋 Processed Lead Information</h3>");
        writeOutput("<table>");
        writeOutput("<tr><th>Field</th><th>Value</th></tr>");
        writeOutput("<tr><td>Full Name</td><td>" & processedLead.processedData.firstName & " " & processedLead.processedData.lastName & "</td></tr>");
        writeOutput("<tr><td>Email</td><td>" & processedLead.processedData.email & "</td></tr>");
        writeOutput("<tr><td>Company</td><td>" & processedLead.processedData.company & "</td></tr>");
        writeOutput("<tr><td>Job Title</td><td>" & processedLead.processedData.jobTitle & "</td></tr>");
        writeOutput("<tr><td>Industry</td><td>" & processedLead.processedData.industry & "</td></tr>");
        writeOutput("<tr><td>Phone</td><td>" & processedLead.processedData.phone & "</td></tr>");
        writeOutput("<tr><td><strong>Lead Score</strong></td><td><strong>" & processedLead.processedData.leadScore & "/100</strong></td></tr>");
        writeOutput("<tr><td><strong>Status</strong></td><td><strong>" & processedLead.processedData.status & "</strong></td></tr>");
        writeOutput("</table>");
        
        writeOutput("<h3>🔗 Salesforce API Record</h3>");
        writeOutput("<table>");
        writeOutput("<tr><th>Salesforce Field</th><th>Value</th></tr>");
        for (field in processedLead.salesforceRecord) {
            writeOutput("<tr><td>" & field & "</td><td>" & processedLead.salesforceRecord[field] & "</td></tr>");
        }
        writeOutput("</table>");
        
    } else {
        writeOutput("<p class='error'><strong>❌ Lead Processing:</strong> FAILED</p>");
        
        if (arrayLen(processedLead.errors) > 0) {
            writeOutput("<h3>Errors:</h3>");
            writeOutput("<ul>");
            for (error in processedLead.errors) {
                writeOutput("<li class='error'>" & error & "</li>");
            }
            writeOutput("</ul>");
        }
    }
    
} catch (any e) {
    writeOutput("<p class='error'><strong>❌ Critical Error:</strong> " & e.message & "</p>");
}

// =============================================================================
// MULTIPLE TEST CASES
// =============================================================================

// Test case 2: High-value lead
highValueJson = '{
    "firstName": "Sarah", 
    "lastName": "Wilson", 
    "emailAddress": "sarah@bigcorp.com", 
    "companyName": "Big Corporation",
    "jobTitle": "VP Sales",
    "employeeCount": 500,
    "annualRevenue": 10000000,
    "interestLevel": "high"
}';
highValueLead = DeserializeJSON(highValueJson);
highValueResult = processLead(highValueLead);

writeOutput("<h3>Test 2: High-Value Lead</h3>");
writeOutput("<p><strong>Result:</strong> " & (highValueResult.success ? "<span class='success'>SUCCESS</span>" : "<span class='error'>FAILED</span>") & "</p>");
writeOutput("<p><strong>Lead Score:</strong> " & (highValueResult.success ? highValueResult.processedData.leadScore : "N/A") & "</p>");
writeOutput("<p><strong>Status:</strong> " & (highValueResult.success ? highValueResult.processedData.status : "N/A") & "</p>");


</cfscript>

<cfscript>
function processSalesAnalytics(salesJsonData, reportConfig = {}) {
    try {
        // Deserialize JSON array directly to ColdFusion query for performance
        salesQuery = DeserializeJSON(salesJsonData, "query");
        
        // Initialize analytics result structure
        analytics = {
            summary: {
                totalSales: 0,
                transactionCount: 0,
                averageOrderValue: 0,
                topPerformers: [],
                salesByRegion: {},
                salesByProduct: {},
                monthlyTrends: {},
                conversionMetrics: {}
            },
            detailedAnalytics: {
                salesTeamPerformance: [],
                productPerformance: [],
                regionalAnalysis: [],
                customerSegments: []
            },
            alerts: [],
            recommendations: [],
            metadata: {
                reportGeneratedAt: now(),
                recordsProcessed: salesQuery.recordCount,
                dateRange: {
                    startDate: "",
                    endDate: ""
                }
            }
        };
        
        // Validate query structure
        requiredColumns = ["transactionId", "saleDate", "salesperson", "customerId", "product", "amount", "quantity"];
        missingColumns = [];
        
        for (col in requiredColumns) {
            if (!listFindNoCase(salesQuery.columnList, col)) {
                arrayAppend(missingColumns, col);
            }
        }
        
        if (arrayLen(missingColumns) > 0) {
            return {
                success: false,
                error: "Missing required columns: " & arrayToList(missingColumns),
                code: "INVALID_DATA_STRUCTURE"
            };
        }
        
        // Initialize tracking variables
        salesBySalesperson = {};
        salesByProduct = {};
        salesByRegion = {};
        salesByMonth = {};
        customerData = {};
        dailySales = {};
        
        // Process each sales record
        for (row = 1; row <= salesQuery.recordCount; row++) {
            // Extract and validate row data
            transactionId = salesQuery.transactionId[row];
            saleDate = parseDateTime(salesQuery.saleDate[row]);
            salesperson = salesQuery.salesperson[row];
            customerId = salesQuery.customerId[row];
            productName = salesQuery.product[row];
            amount = val(salesQuery.amount[row]);
            quantity = val(salesQuery.quantity[row]);
            
            // Additional optional fields (with safe access)
            region = "Unknown";
            customerType = "Standard";
            channel = "Direct";
            
            try {
                if (listFindNoCase(salesQuery.columnList, "region")) {
                    region = salesQuery.region[row];
                }
            } catch (any e) {
                region = "Unknown";
            }
            
            try {
                if (listFindNoCase(salesQuery.columnList, "customerType")) {
                    customerType = salesQuery.customerType[row];
                }
            } catch (any e) {
                customerType = "Standard";
            }
            
            try {
                if (listFindNoCase(salesQuery.columnList, "channel")) {
                    channel = salesQuery.channel[row];
                }
            } catch (any e) {
                channel = "Direct";
            }
            
            // Validate data quality
            if (amount <= 0 || quantity <= 0) {
                arrayAppend(analytics.alerts, {
                    type: "data_quality",
                    severity: "medium",
                    message: "Invalid amount or quantity in transaction " & transactionId,
                    transactionId: transactionId
                });
                continue; // Skip invalid records
            }
            
            // Update summary totals
            analytics.summary.totalSales += amount;
            analytics.summary.transactionCount++;
            
            // Track date range
            if (analytics.metadata.dateRange.startDate == "" || saleDate < analytics.metadata.dateRange.startDate) {
                analytics.metadata.dateRange.startDate = saleDate;
            }
            if (analytics.metadata.dateRange.endDate == "" || saleDate > analytics.metadata.dateRange.endDate) {
                analytics.metadata.dateRange.endDate = saleDate;
            }
            
            // Salesperson performance tracking
            if (!structKeyExists(salesBySalesperson, salesperson)) {
                salesBySalesperson[salesperson] = {
                    totalSales: 0,
                    transactionCount: 0,
                    productsScore: {},
                    avgOrderValue: 0,
                    region: region,
                    customerTypes: {}
                };
            }
            
            salesBySalesperson[salesperson].totalSales += amount;
            salesBySalesperson[salesperson].transactionCount++;
            
            // Track customer types per salesperson
            if (!structKeyExists(salesBySalesperson[salesperson].customerTypes, customerType)) {
                salesBySalesperson[salesperson].customerTypes[customerType] = 0;
            }
            salesBySalesperson[salesperson].customerTypes[customerType]++;
            
            // Product performance tracking
            if (!structKeyExists(salesByProduct, productName)) {
                salesByProduct[productName] = {
                    totalRevenue: 0,
                    totalQuantity: 0,
                    transactionCount: 0,
                    averagePrice: 0,
                    topSalesperson: "",
                    topSalesAmount: 0
                };
            }
            
            salesByProduct[productName].totalRevenue += amount;
            salesByProduct[productName].totalQuantity += quantity;
            salesByProduct[productName].transactionCount++;
            
            // Track top salesperson per product
            if (amount > salesByProduct[productName].topSalesAmount) {
                salesByProduct[productName].topSalesperson = salesperson;
                salesByProduct[productName].topSalesAmount = amount;
            }
            
            // Regional analysis
            if (!structKeyExists(salesByRegion, region)) {
                salesByRegion[region] = {
                    totalSales: 0,
                    transactionCount: 0,
                    uniqueCustomers: {},
                    topProducts: {},
                    channels: {}
                };
            }
            
            salesByRegion[region].totalSales += amount;
            salesByRegion[region].transactionCount++;
            salesByRegion[region].uniqueCustomers[customerId] = true;
            
            // Track products by region
            if (!structKeyExists(salesByRegion[region].topProducts, productName)) {
                salesByRegion[region].topProducts[productName] = 0;
            }
            salesByRegion[region].topProducts[productName] += amount;
            
            // Track channels by region
            if (!structKeyExists(salesByRegion[region].channels, channel)) {
                salesByRegion[region].channels[channel] = 0;
            }
            salesByRegion[region].channels[channel] += amount;
            
            // Monthly trends
            monthKey = year(saleDate) & "-" & numberFormat(month(saleDate), "00");
            if (!structKeyExists(salesByMonth, monthKey)) {
                salesByMonth[monthKey] = {
                    totalSales: 0,
                    transactionCount: 0,
                    uniqueCustomers: {},
                    newCustomers: 0
                };
            }
            
            salesByMonth[monthKey].totalSales += amount;
            salesByMonth[monthKey].transactionCount++;
            salesByMonth[monthKey].uniqueCustomers[customerId] = true;
            
            // Customer analysis
            if (!structKeyExists(customerData, customerId)) {
                customerData[customerId] = {
                    totalSpent: 0,
                    transactionCount: 0,
                    firstPurchase: saleDate,
                    lastPurchase: saleDate,
                    customerType: customerType,
                    preferredProducts: {},
                    region: region
                };
            }
            
            customerData[customerId].totalSpent += amount;
            customerData[customerId].transactionCount++;
            
            if (saleDate > customerData[customerId].lastPurchase) {
                customerData[customerId].lastPurchase = saleDate;
            }
            if (saleDate < customerData[customerId].firstPurchase) {
                customerData[customerId].firstPurchase = saleDate;
            }
            
            // Track preferred products per customer
            if (!structKeyExists(customerData[customerId].preferredProducts, productName)) {
                customerData[customerId].preferredProducts[productName] = 0;
            }
            customerData[customerId].preferredProducts[productName] += amount;
        }
        
        // Calculate averages and derived metrics
        analytics.summary.averageOrderValue = analytics.summary.totalSales / analytics.summary.transactionCount;
        
        // Calculate salesperson averages
        for (salesperson in salesBySalesperson) {
            salesBySalesperson[salesperson].avgOrderValue = 
                salesBySalesperson[salesperson].totalSales / salesBySalesperson[salesperson].transactionCount;
        }
        
        // Calculate product averages
        for (product in salesByProduct) {
            salesByProduct[product].averagePrice = 
                salesByProduct[product].totalRevenue / salesByProduct[product].totalQuantity;
        }
        
        // Generate top performers list (sorted by total sales)
        topPerformers = [];
        for (salesperson in salesBySalesperson) {
            arrayAppend(topPerformers, {
                name: salesperson,
                totalSales: salesBySalesperson[salesperson].totalSales,
                avgOrderValue: salesBySalesperson[salesperson].avgOrderValue,
                transactionCount: salesBySalesperson[salesperson].transactionCount,
                region: salesBySalesperson[salesperson].region
            });
        }
        
        // Sort top performers by sales amount
        arraySort(topPerformers, function(a, b) {
            return b.totalSales - a.totalSales;
        });
        
        analytics.summary.topPerformers = topPerformers;
        
        // Generate alerts and recommendations
        
        // Alert: Low performing salespeople
        avgSalesPerPerson = analytics.summary.totalSales / structCount(salesBySalesperson);
        for (performer in topPerformers) {
            if (performer.totalSales < (avgSalesPerPerson * 0.5)) {
                arrayAppend(analytics.alerts, {
                    type: "performance",
                    severity: "medium",
                    message: performer.name & " is performing below 50% of team average",
                    salesperson: performer.name,
                    currentSales: performer.totalSales,
                    teamAverage: avgSalesPerPerson
                });
                
                arrayAppend(analytics.recommendations, {
                    type: "training",
                    priority: "medium",
                    message: "Consider additional training or coaching for " & performer.name,
                    salesperson: performer.name
                });
            }
        }
        
        // Alert: High-value customers
        for (customerId in customerData) {
            customer = customerData[customerId];
            if (customer.totalSpent > (analytics.summary.averageOrderValue * 10)) {
                arrayAppend(analytics.recommendations, {
                    type: "customer_retention",
                    priority: "high",
                    message: "High-value customer " & customerId & " - ensure premium service",
                    customerId: customerId,
                    totalSpent: customer.totalSpent
                });
            }
        }
        
        // Populate detailed analytics
        analytics.detailedAnalytics.salesTeamPerformance = topPerformers;
        analytics.detailedAnalytics.productPerformance = salesByProduct;
        analytics.detailedAnalytics.regionalAnalysis = salesByRegion;
        
        // Customer segmentation
        customerSegments = {
            "high_value": [],
            "medium_value": [],
            "low_value": [],
            "at_risk": []
        };
        
        for (customerId in customerData) {
            customer = customerData[customerId];
            
            // Determine customer segment based on spending
            if (customer.totalSpent > (analytics.summary.averageOrderValue * 5)) {
                arrayAppend(customerSegments.high_value, {
                    customerId: customerId,
                    totalSpent: customer.totalSpent,
                    transactionCount: customer.transactionCount
                });
            } else if (customer.totalSpent > analytics.summary.averageOrderValue) {
                arrayAppend(customerSegments.medium_value, {
                    customerId: customerId,
                    totalSpent: customer.totalSpent,
                    transactionCount: customer.transactionCount
                });
            } else {
                arrayAppend(customerSegments.low_value, {
                    customerId: customerId,
                    totalSpent: customer.totalSpent,
                    transactionCount: customer.transactionCount
                });
            }
            
            // Check for at-risk customers (no purchases in last 90 days)
            if (dateDiff("d", customer.lastPurchase, now()) > 90) {
                arrayAppend(customerSegments.at_risk, {
                    customerId: customerId,
                    daysSinceLastPurchase: dateDiff("d", customer.lastPurchase, now()),
                    totalSpent: customer.totalSpent
                });
            }
        }
        
        analytics.detailedAnalytics.customerSegments = customerSegments;
        
        // Populate summary objects
        analytics.summary.salesByRegion = salesByRegion;
        analytics.summary.salesByProduct = salesByProduct;
        analytics.summary.monthlyTrends = salesByMonth;
        
        return {
            success: true,
            analytics: analytics,
            rawQuery: salesQuery
        };
        
    } catch (any e) {
        return {
            success: false,
            error: "Sales analytics processing failed: " & e.message,
            code: "ANALYTICS_PROCESSING_ERROR",
            detail: e.detail ?: ""
        };
    }
}

// =============================================================================
// SAMPLE USAGE
// =============================================================================

// Sample complex sales JSON array
salesJson = '[
    {
        "transactionId": "TXN-2024-001",
        "saleDate": "2024-03-01T09:30:00",
        "salesperson": "Alice Johnson",
        "customerId": "CUST-001",
        "customerType": "Enterprise",
        "product": "Enterprise Software License",
        "amount": 15000.00,
        "quantity": 5,
        "region": "West",
        "channel": "Direct Sales"
    },
    {
        "transactionId": "TXN-2024-002",
        "saleDate": "2024-03-01T11:15:00",
        "salesperson": "Bob Smith",
        "customerId": "CUST-002",
        "customerType": "SMB",
        "product": "Standard Software License",
        "amount": 2500.00,
        "quantity": 10,
        "region": "East",
        "channel": "Online"
    },
    {
        "transactionId": "TXN-2024-003",
        "saleDate": "2024-03-01T14:45:00",
        "salesperson": "Alice Johnson",
        "customerId": "CUST-003",
        "customerType": "Enterprise",
        "product": "Consulting Services",
        "amount": 8000.00,
        "quantity": 1,
        "region": "West",
        "channel": "Partner"
    },
    {
        "transactionId": "TXN-2024-004",
        "saleDate": "2024-03-02T10:20:00",
        "salesperson": "Carol Davis",
        "customerId": "CUST-001",
        "customerType": "Enterprise",
        "product": "Support Contract",
        "amount": 5000.00,
        "quantity": 1,
        "region": "Central",
        "channel": "Direct Sales"
    },
    {
        "transactionId": "TXN-2024-005",
        "saleDate": "2024-03-02T16:30:00",
        "salesperson": "Bob Smith",
        "customerId": "CUST-004",
        "customerType": "Startup",
        "product": "Starter Package",
        "amount": 500.00,
        "quantity": 2,
        "region": "East",
        "channel": "Online"
    },
    {
        "transactionId": "TXN-2024-006",
        "saleDate": "2024-03-03T13:00:00",
        "salesperson": "Alice Johnson",
        "customerId": "CUST-005",
        "customerType": "SMB",
        "product": "Standard Software License",
        "amount": 3500.00,
        "quantity": 7,
        "region": "West",
        "channel": "Reseller"
    }
]';

// Test JSON deserialization first
writeOutput("<h2>🔧 JSON to Query Test</h2>");
try {
    testQuery = DeserializeJSON(salesJson, "query");
    writeOutput("<p><strong>Record Count:</strong> " & testQuery.recordCount & "</p>");
    writeOutput("<p><strong>Column List:</strong> " & testQuery.columnList & "</p>");
    
    // Show first record
    if (testQuery.recordCount > 0) {
        writeOutput("<p><strong>First Record:</strong></p>");
        writeOutput("<ul>");
        writeOutput("<li>Transaction ID: " & testQuery.transactionId[1] & "</li>");
        writeOutput("<li>Salesperson: " & testQuery.salesperson[1] & "</li>");
        writeOutput("<li>Amount: $" & numberFormat(testQuery.amount[1], "999.99") & "</li>");
        writeOutput("</ul>");
    }
} catch (any e) {
    writeOutput("<p><strong>❌ JSON to Query conversion:</strong> FAILED</p>");
    writeOutput("<p><strong>Error:</strong> " & e.message & "</p>");
}
writeOutput("<hr>");

// Execute sales analytics
writeOutput("<h2>📈 Processing Sales Analytics</h2>");
analyticsResult = processSalesAnalytics(salesJson);

// Display results
if (analyticsResult.success) {
    writeOutput("<h2>📊 Sales Analytics Results</h2>");
    
    writeOutput("<h3>Summary</h3>");
    writeOutput("<p><strong>Total Sales:</strong> $" & numberFormat(analyticsResult.analytics.summary.totalSales, "999,999.99") & "</p>");
    writeOutput("<p><strong>Total Transactions:</strong> " & analyticsResult.analytics.summary.transactionCount & "</p>");
    writeOutput("<p><strong>Average Order Value:</strong> $" & numberFormat(analyticsResult.analytics.summary.averageOrderValue, "999.99") & "</p>");
    
    writeOutput("<h3>Top Performers</h3>");
    for (i = 1; i <= min(3, arrayLen(analyticsResult.analytics.summary.topPerformers)); i++) {
        performer = analyticsResult.analytics.summary.topPerformers[i];
        writeOutput("<p><strong>" & i & ". " & performer.name & ":</strong> $" & numberFormat(performer.totalSales, "999,999.99") & 
                   " (" & performer.transactionCount & " transactions, $" & numberFormat(performer.avgOrderValue, "999.99") & " avg)</p>");
    }
    
    writeOutput("<h3>Regional Analysis</h3>");
    for (region in analyticsResult.analytics.summary.salesByRegion) {
        regionData = analyticsResult.analytics.summary.salesByRegion[region];
        writeOutput("<p><strong>" & region & ":</strong> $" & numberFormat(regionData.totalSales, "999,999.99") & 
                   " (" & regionData.transactionCount & " transactions)</p>");
    }
    
    writeOutput("<h3>Product Performance</h3>");
    for (product in analyticsResult.analytics.summary.salesByProduct) {
        productData = analyticsResult.analytics.summary.salesByProduct[product];
        writeOutput("<p><strong>" & product & ":</strong> $" & numberFormat(productData.totalRevenue, "999,999.99") & 
                   " (" & productData.totalQuantity & " units sold)</p>");
    }
    
    if (arrayLen(analyticsResult.analytics.alerts) > 0) {
        writeOutput("<h3>Alerts</h3>");
        for (alert in analyticsResult.analytics.alerts) {
            writeOutput("<p><strong>" & uCase(alert.type) & ":</strong> " & alert.message & "</p>");
        }
    }
    
    if (arrayLen(analyticsResult.analytics.recommendations) > 0) {
        writeOutput("<h3>Recommendations</h3>");
        for (recommendation in analyticsResult.analytics.recommendations) {
            writeOutput("<p><strong>" & uCase(recommendation.type) & ":</strong> " & recommendation.message & "</p>");
        }
    }
    
} else {
    writeOutput("<h2>❌ Analytics Processing Error</h2>");
    
    if (structKeyExists(analyticsResult, "error")) {
        writeOutput("<p><strong>Error:</strong> " & analyticsResult.error & "</p>");
    }
    if (structKeyExists(analyticsResult, "code")) {
        writeOutput("<p><strong>Code:</strong> " & analyticsResult.code & "</p>");
    }
    if (structKeyExists(analyticsResult, "detail")) {
        writeOutput("<p><strong>Detail:</strong> " & analyticsResult.detail & "</p>");
    }
    
    writeOutput("<p><strong>Debug Info:</strong> Check if JSON deserialization succeeded above.</p>");
}

</cfscript>