-- Drop table if exists to make script repeatable
GO

-- Create demo table with 2 rich JSON columns
CREATE TABLE dbo.CustomerProfile
(
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName NVARCHAR(100) NOT NULL,
    CustomerType NVARCHAR(50),
    ProfileData JSON,
    ActivityData JSON
);
GO

-- Populate with fixed decimal precision
DECLARE @CurrentTimestamp NVARCHAR(30) = CONVERT(VARCHAR(19), GETDATE(), 126);

INSERT INTO dbo.CustomerProfile (CustomerName, CustomerType, ProfileData, ActivityData)
SELECT TOP 25
    CONCAT(p.FirstName, ' ', p.LastName) AS CustomerName,
    'Individual' AS CustomerType,
    
    -- ProfileData
    JSON_OBJECT(
        'personalInfo': JSON_OBJECT(
            'firstName': p.FirstName,
            'lastName': p.LastName,
            'middleName': p.MiddleName,
            'title': p.Title,
            'suffix': p.Suffix,
            'personType': p.PersonType
        ),
        'contactMethods': JSON_OBJECT(
            'emails': JSON_ARRAY(
                JSON_OBJECT(
                    'type': 'primary',
                    'address': ea.EmailAddress,
                    'verified': CAST(1 AS BIT),
                    'verifiedDate': CONVERT(VARCHAR(10), ea.ModifiedDate, 23)
                ),
                JSON_OBJECT(
                    'type': 'alternate',
                    'address': CONCAT('alt.', ea.EmailAddress),
                    'verified': CAST(0 AS BIT)
                )
            ),
            'phones': JSON_ARRAY(
                JSON_OBJECT(
                    'type': pt.Name,
                    'number': pp.PhoneNumber,
                    'isPrimary': CAST(1 AS BIT),
                    'countryCode': '+1',
                    'canSMS': CASE WHEN pt.Name LIKE '%Cell%' THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END
                ),
                JSON_OBJECT(
                    'type': 'Mobile',
                    'number': CONCAT('555-', RIGHT(CAST(p.BusinessEntityID * 1234 AS VARCHAR), 4)),
                    'isPrimary': CAST(0 AS BIT),
                    'countryCode': '+1',
                    'canSMS': CAST(1 AS BIT)
                )
            )
        ),
        'addresses': JSON_ARRAY(
            JSON_OBJECT(
                'addressId': a.AddressID,
                'type': at.Name,
                'isPrimary': CAST(1 AS BIT),
                'location': JSON_OBJECT(
                    'street': JSON_OBJECT(
                        'line1': a.AddressLine1,
                        'line2': a.AddressLine2
                    ),
                    'city': a.City,
                    'stateProvince': JSON_OBJECT(
                        'code': sp.StateProvinceCode,
                        'name': sp.Name,
                        'countryCode': sp.CountryRegionCode,
                        'territoryId': sp.TerritoryID
                    ),
                    'postalCode': a.PostalCode,
                    'country': JSON_OBJECT(
                        'code': cr.CountryRegionCode,
                        'name': cr.Name
                    ),
                    'coordinates': JSON_OBJECT(
                        'latitude': CAST(sp.TerritoryID * 10.5 AS DECIMAL(18,6)),
                        'longitude': CAST(a.AddressID * -0.5 AS DECIMAL(18,6))
                    )
                ),
                'metadata': JSON_OBJECT(
                    'lastVerified': CONVERT(VARCHAR(10), a.ModifiedDate, 23),
                    'usageCount': (a.AddressID % 50),
                    'isCommercial': CAST(0 AS BIT)
                )
            )
        ),
        'preferences': JSON_OBJECT(
            'communication': JSON_OBJECT(
                'preferredChannel': CASE (p.BusinessEntityID % 3)
                    WHEN 0 THEN 'email'
                    WHEN 1 THEN 'sms'
                    ELSE 'phone'
                END,
                'language': JSON_OBJECT(
                    'primary': 'en-US',
                    'alternates': JSON_ARRAY('es-ES', 'fr-FR')
                ),
                'timezone': 'America/Los_Angeles',
                'bestContactTimes': JSON_ARRAY(
                    JSON_OBJECT('day': 'weekday', 'startTime': '09:00', 'endTime': '17:00'),
                    JSON_OBJECT('day': 'weekend', 'startTime': '10:00', 'endTime': '14:00')
                )
            ),
            'notifications': JSON_OBJECT(
                'channels': JSON_OBJECT(
                    'email': JSON_OBJECT(
                        'enabled': CAST(1 AS BIT),
                        'frequency': 'daily',
                        'categories': JSON_ARRAY('orders', 'promotions', 'newsletters', 'recommendations')
                    ),
                    'sms': JSON_OBJECT(
                        'enabled': CAST(1 AS BIT),
                        'frequency': 'immediate',
                        'categories': JSON_ARRAY('shipping', 'delivery', 'urgent')
                    ),
                    'push': JSON_OBJECT(
                        'enabled': CASE WHEN (p.BusinessEntityID % 2 = 0) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END,
                        'deviceTokens': JSON_ARRAY(
                            JSON_OBJECT('platform': 'iOS', 'token': CONCAT('ios-', NEWID())),
                            JSON_OBJECT('platform': 'Android', 'token': CONCAT('android-', NEWID()))
                        )
                    )
                )
            ),
            'privacy': JSON_OBJECT(
                'dataSharing': JSON_OBJECT(
                    'allowAnalytics': CAST(1 AS BIT),
                    'allowPersonalization': CAST(1 AS BIT),
                    'allowThirdParty': CAST(0 AS BIT),
                    'allowMarketing': CASE WHEN (p.BusinessEntityID % 2 = 0) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END
                ),
                'cookies': JSON_OBJECT(
                    'necessary': CAST(1 AS BIT),
                    'functional': CAST(1 AS BIT),
                    'analytics': CAST(1 AS BIT),
                    'marketing': CASE WHEN (p.BusinessEntityID % 3 = 0) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END,
                    'consentDate': CONVERT(VARCHAR(10), DATEADD(DAY, -45, GETDATE()), 23),
                    'consentVersion': '2.1'
                )
            ),
            'shopping': JSON_OBJECT(
                'favoriteCategories': JSON_ARRAY(
                    JSON_OBJECT('id': 1, 'name': 'Bikes', 'priority': 1),
                    JSON_OBJECT('id': 2, 'name': 'Components', 'priority': 2),
                    JSON_OBJECT('id': 3, 'name': 'Accessories', 'priority': 3)
                ),
                'priceRange': JSON_OBJECT(
                    'min': 0,
                    'max': 5000,
                    'currency': 'USD',
                    'flexible': CAST(1 AS BIT)
                ),
                'paymentMethods': JSON_ARRAY(
                    JSON_OBJECT(
                        'id': 1,
                        'type': 'credit_card',
                        'provider': 'Visa',
                        'lastFour': '4242',
                        'expiryMonth': 12,
                        'expiryYear': 2026,
                        'isDefault': CAST(1 AS BIT),
                        'billingAddress': 'primary'
                    ),
                    JSON_OBJECT(
                        'id': 2,
                        'type': 'paypal',
                        'email': ea.EmailAddress,
                        'isDefault': CAST(0 AS BIT)
                    )
                ),
                'savedForLater': JSON_ARRAY(
                    JSON_OBJECT('productId': 680, 'dateAdded': '2025-06-01', 'priceWhenSaved': 1431.50),
                    JSON_OBJECT('productId': 706, 'dateAdded': '2025-06-15', 'priceWhenSaved': 2384.07)
                )
            ),
            'loyalty': JSON_OBJECT(
                'program': JSON_OBJECT(
                    'memberId': CONCAT('MEMBER-', REPLICATE('0', 8 - LEN(CAST(p.BusinessEntityID AS VARCHAR))), p.BusinessEntityID),
                    'tier': CASE
                        WHEN ISNULL(c.CustomerID, 0) % 5 = 0 THEN JSON_OBJECT('level': 'platinum', 'benefits': JSON_ARRAY('free_shipping', 'priority_support', '20%_discount', 'exclusive_access'))
                        WHEN ISNULL(c.CustomerID, 0) % 3 = 0 THEN JSON_OBJECT('level': 'gold', 'benefits': JSON_ARRAY('free_shipping', '10%_discount', 'early_access'))
                        ELSE JSON_OBJECT('level': 'silver', 'benefits': JSON_ARRAY('points_earning', '5%_discount'))
                    END,
                    'points': JSON_OBJECT(
                        'current': (p.BusinessEntityID * 100) % 10000,
                        'lifetime': (p.BusinessEntityID * 250) % 50000,
                        'pendingExpiration': JSON_ARRAY(
                            JSON_OBJECT('points': 500, 'expiryDate': '2025-08-31'),
                            JSON_OBJECT('points': 750, 'expiryDate': '2025-09-30')
                        )
                    ),
                    'status': JSON_OBJECT(
                        'joinDate': CONVERT(VARCHAR(10), DATEADD(YEAR, -2, GETDATE()), 23),
                        'lastActivity': CONVERT(VARCHAR(10), DATEADD(DAY, -15, GETDATE()), 23),
                        'tierExpiryDate': CONVERT(VARCHAR(10), DATEADD(YEAR, 1, GETDATE()), 23),
                        'renewalEligible': CAST(1 AS BIT)
                    )
                )
            )
        )
    ) AS ProfileData,
    
    -- ActivityData
    JSON_OBJECT(
        'orders': ISNULL((
            SELECT 
                soh.SalesOrderID AS orderId,
                soh.SalesOrderNumber AS orderNumber,
                JSON_OBJECT(
                    'ordered': CONVERT(VARCHAR(10), soh.OrderDate, 23),
                    'due': CONVERT(VARCHAR(10), soh.DueDate, 23),
                    'shipped': CONVERT(VARCHAR(10), soh.ShipDate, 23)
                ) AS dates,
                JSON_OBJECT(
                    'code': soh.Status,
                    'description': CASE soh.Status
                        WHEN 1 THEN 'In Process'
                        WHEN 2 THEN 'Approved'
                        WHEN 3 THEN 'Backordered'
                        WHEN 4 THEN 'Rejected'
                        WHEN 5 THEN 'Shipped'
                        WHEN 6 THEN 'Cancelled'
                        ELSE 'Unknown'
                    END,
                    'isOnline': soh.OnlineOrderFlag,
                    'purchaseOrderNumber': soh.PurchaseOrderNumber
                ) AS [status],
                JSON_OBJECT(
                    'subtotal': CONVERT(VARCHAR(20), CAST(soh.SubTotal AS DECIMAL(18,2))),
                    'taxAmount': CONVERT(VARCHAR(20), CAST(soh.TaxAmt AS DECIMAL(18,2))),
                    'freight': CONVERT(VARCHAR(20), CAST(soh.Freight AS DECIMAL(18,2))),
                    'totalDue': CONVERT(VARCHAR(20), CAST(soh.TotalDue AS DECIMAL(18,2)))
                ) AS financial,
                JSON_OBJECT(
                    'method': sm.Name,
                    'city': ship_addr.City,
                    'stateProvince': ship_sp.StateProvinceCode
                ) AS shipping
            FROM (
                SELECT TOP 3 *
                FROM AdventureWorks2025.Sales.SalesOrderHeader
                WHERE CustomerID = c.CustomerID
                ORDER BY OrderDate DESC
            ) soh
                LEFT JOIN AdventureWorks2025.Purchasing.ShipMethod sm ON soh.ShipMethodID = sm.ShipMethodID
                LEFT JOIN AdventureWorks2025.Person.Address ship_addr ON soh.ShipToAddressID = ship_addr.AddressID
                LEFT JOIN AdventureWorks2025.Person.StateProvince ship_sp ON ship_addr.StateProvinceID = ship_sp.StateProvinceID
            FOR JSON PATH
        ), '[]'),
        'analytics': JSON_OBJECT(
            'customerValue': JSON_OBJECT(
                'lifetimeValue': (ISNULL(c.CustomerID, p.BusinessEntityID) % 200) * 250,
                'averageOrderValue': (ISNULL(c.CustomerID, p.BusinessEntityID) % 100) * 25,
                'totalOrders': (ISNULL(c.CustomerID, p.BusinessEntityID) % 50) + 1,
                'totalSpent': (ISNULL(c.CustomerID, p.BusinessEntityID) % 300) * 100,
                'predictedNextPurchase': CONVERT(VARCHAR(10), DATEADD(DAY, (ISNULL(c.CustomerID, p.BusinessEntityID) % 30) + 15, GETDATE()), 23)
            ),
            'segmentation': JSON_OBJECT(
                'primarySegment': CASE
                    WHEN ISNULL(c.CustomerID, 0) % 5 = 0 THEN 'VIP'
                    WHEN ISNULL(c.CustomerID, 0) % 3 = 0 THEN 'Frequent'
                    ELSE 'Standard'
                END,
                'subSegments': JSON_ARRAY(
                    CASE WHEN (ISNULL(c.CustomerID, 0) % 2 = 0) THEN 'high-value' ELSE 'standard-value' END,
                    CASE ISNULL(st.[Group], 'North America')
                        WHEN 'North America' THEN 'domestic'
                        ELSE 'international'
                    END,
                    'email-verified'
                ),
                'riskProfile': JSON_OBJECT(
                    'churnRisk': CASE
                        WHEN ISNULL(c.CustomerID, 0) % 10 < 3 THEN 'low'
                        WHEN ISNULL(c.CustomerID, 0) % 10 < 7 THEN 'medium'
                        ELSE 'high'
                    END,
                    'churnProbability': CAST((ISNULL(c.CustomerID, 0) % 100) AS FLOAT) / 100.0,
                    'factors': JSON_ARRAY('order_frequency', 'engagement_score', 'support_tickets')
                )
            ),
            'behavior': JSON_OBJECT(
                'engagement': JSON_OBJECT(
                    'score': (ISNULL(c.CustomerID, p.BusinessEntityID) % 100),
                    'level': CASE
                        WHEN (ISNULL(c.CustomerID, p.BusinessEntityID) % 100) > 75 THEN 'highly_engaged'
                        WHEN (ISNULL(c.CustomerID, p.BusinessEntityID) % 100) > 50 THEN 'moderately_engaged'
                        ELSE 'low_engagement'
                    END,
                    'lastActivity': CONVERT(VARCHAR(10), DATEADD(DAY, -(ISNULL(c.CustomerID, p.BusinessEntityID) % 30), GETDATE()), 23)
                ),
                'browsing': JSON_OBJECT(
                    'totalPageViews': (ISNULL(c.CustomerID, p.BusinessEntityID) * 17) % 1000,
                    'averageSessionDuration': (ISNULL(c.CustomerID, p.BusinessEntityID) % 45) + 5,
                    'recentSessions': JSON_ARRAY(
                        JSON_OBJECT('date': CONVERT(VARCHAR(10), DATEADD(DAY, -1, GETDATE()), 23), 'duration': 420, 'pages': 8, 'converted': CAST(1 AS BIT)),
                        JSON_OBJECT('date': CONVERT(VARCHAR(10), DATEADD(DAY, -3, GETDATE()), 23), 'duration': 180, 'pages': 3, 'converted': CAST(0 AS BIT)),
                        JSON_OBJECT('date': CONVERT(VARCHAR(10), DATEADD(DAY, -7, GETDATE()), 23), 'duration': 540, 'pages': 12, 'converted': CAST(1 AS BIT))
                    ),
                    'topCategories': JSON_ARRAY('Bikes', 'Components', 'Accessories')
                ),
                'interactions': JSON_ARRAY(
                    JSON_OBJECT('type': 'email_opened', 'date': CONVERT(VARCHAR(10), DATEADD(DAY, -5, GETDATE()), 23), 'campaign': 'summer_sale'),
                    JSON_OBJECT('type': 'support_ticket', 'date': CONVERT(VARCHAR(10), DATEADD(DAY, -20, GETDATE()), 23), 'resolved': CAST(1 AS BIT)),
                    JSON_OBJECT('type': 'product_review', 'date': CONVERT(VARCHAR(10), DATEADD(DAY, -35, GETDATE()), 23), 'rating': 5)
                )
            ),
            'territory': JSON_OBJECT(
                'id': st.TerritoryID,
                'name': st.Name,
                'territoryGroup': st.[Group],
                'region': CASE ISNULL(st.[Group], 'North America')
                    WHEN 'North America' THEN 'Americas'
                    WHEN 'Europe' THEN 'EMEA'
                    WHEN 'Pacific' THEN 'APAC'
                    ELSE 'Other'
                END,
                'performance': JSON_OBJECT(
                    'salesYTD': CONVERT(VARCHAR(20), CAST(ISNULL(st.SalesYTD, 0) AS DECIMAL(18,2))),
                    'salesLastYear': CONVERT(VARCHAR(20), CAST(ISNULL(st.SalesLastYear, 0) AS DECIMAL(18,2))),
                    'costYTD': CONVERT(VARCHAR(20), CAST(ISNULL(st.CostYTD, 0) AS DECIMAL(18,2))),
                    'costLastYear': CONVERT(VARCHAR(20), CAST(ISNULL(st.CostLastYear, 0) AS DECIMAL(18,2)))
                )
            )
        ),
        'metadata': JSON_OBJECT(
            'source': 'AdventureWorks2025',
            'ingestion': JSON_OBJECT(
                'timestamp': @CurrentTimestamp,
                'batchId': CONCAT('BATCH-', FORMAT(GETDATE(), 'yyyyMMdd-HHmmss')),
                'version': '2.0'
            ),
            'quality': JSON_OBJECT(
                'completeness': 0.95,
                'accuracy': 0.98,
                'lastValidated': @CurrentTimestamp,
                'validationRules': JSON_ARRAY('email_format', 'phone_format', 'address_complete', 'order_consistency')
            ),
            'integrations': JSON_OBJECT(
                'externalIds': JSON_ARRAY(
                    JSON_OBJECT('system': 'Salesforce', 'id': CONCAT('SF-', p.BusinessEntityID), 'syncedAt': @CurrentTimestamp),
                    JSON_OBJECT('system': 'Marketo', 'id': CONCAT('MKT-', p.BusinessEntityID), 'syncedAt': @CurrentTimestamp),
                    JSON_OBJECT('system': 'GoogleAnalytics', 'id': CONCAT('GA-', NEWID()), 'syncedAt': @CurrentTimestamp)
                )
            ),
            'audit': JSON_ARRAY(
                JSON_OBJECT(
                    'timestamp': CONVERT(VARCHAR(19), DATEADD(DAY, -60, GETDATE()), 126),
                    'action': 'account_created',
                    'userId': 'system',
                    'ipAddress': CONCAT('192.168.', (p.BusinessEntityID % 255), '.', ((p.BusinessEntityID * 7) % 255))
                ),
                JSON_OBJECT(
                    'timestamp': CONVERT(VARCHAR(19), DATEADD(DAY, -30, GETDATE()), 126),
                    'action': 'profile_updated',
                    'userId': ea.EmailAddress,
                    'changes': JSON_ARRAY(
                        JSON_OBJECT('field': 'phone', 'oldValue': 'xxx-xxx-1234', 'newValue': pp.PhoneNumber),
                        JSON_OBJECT('field': 'email_verified', 'oldValue': CAST(0 AS BIT), 'newValue': CAST(1 AS BIT))
                    )
                ),
                JSON_OBJECT(
                    'timestamp': @CurrentTimestamp,
                    'action': 'data_export',
                    'userId': 'admin',
                    'purpose': 'demo_generation'
                )
            )
        )
    ) AS ActivityData
FROM AdventureWorks2025.Person.Person p
    INNER JOIN AdventureWorks2025.Person.EmailAddress ea ON p.BusinessEntityID = ea.BusinessEntityID
    INNER JOIN AdventureWorks2025.Person.PersonPhone pp ON p.BusinessEntityID = pp.BusinessEntityID
    INNER JOIN AdventureWorks2025.Person.PhoneNumberType pt ON pp.PhoneNumberTypeID = pt.PhoneNumberTypeID
    INNER JOIN AdventureWorks2025.Person.BusinessEntityAddress bea ON p.BusinessEntityID = bea.BusinessEntityID
    INNER JOIN AdventureWorks2025.Person.AddressType at ON bea.AddressTypeID = at.AddressTypeID
    INNER JOIN AdventureWorks2025.Person.Address a ON bea.AddressID = a.AddressID
    INNER JOIN AdventureWorks2025.Person.StateProvince sp ON a.StateProvinceID = sp.StateProvinceID
    INNER JOIN AdventureWorks2025.Person.CountryRegion cr ON sp.CountryRegionCode = cr.CountryRegionCode
    LEFT JOIN AdventureWorks2025.Sales.Customer c ON p.BusinessEntityID = c.PersonID
    LEFT JOIN AdventureWorks2025.Sales.SalesTerritory st ON c.TerritoryID = st.TerritoryID
WHERE ea.EmailAddress IS NOT NULL
ORDER BY p.BusinessEntityID;
GO
