DROP TABLE IF EXISTS [dbo].[noOrder];
GO

-- Tworzenie tabeli noOrder
CREATE TABLE [dbo].[noOrder] (
    OrderId INT PRIMARY KEY,
    jsonData NVARCHAR(MAX)
);
GO

-- Wstawianie danych do noOrder
INSERT INTO [dbo].[noOrder] (OrderId, jsonData)
SELECT soh.SalesOrderID AS OrderId,
    (
        SELECT 
            soh.SalesOrderID AS [Order.Id],
            soh.OrderDate AS [Order.Date],
            soh.TotalDue AS [Order.Total],
            (
                SELECT 
                    c.CustomerID AS [Id],
                    p.FirstName + ' ' + p.LastName AS [Name],
                    (
                        SELECT 
                            ea.EmailAddress AS [Email]
                        FROM AdventureWorks2019.Person.EmailAddress ea
                        WHERE ea.BusinessEntityID = c.PersonID
                        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
                    ) AS Contact
                FROM AdventureWorks2019.Sales.Customer c
                INNER JOIN AdventureWorks2019.Person.Person p
                    ON c.PersonID = p.BusinessEntityID
                WHERE c.CustomerID = soh.CustomerID
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            ) AS Customer,
            (
                SELECT 
                    e.BusinessEntityID AS [Id],
                    pe.FirstName + ' ' + pe.LastName AS [Name],
                    e.JobTitle AS [JobTitle]
                FROM AdventureWorks2019.HumanResources.Employee e
                INNER JOIN AdventureWorks2019.Person.Person pe
                    ON e.BusinessEntityID = pe.BusinessEntityID
                WHERE e.BusinessEntityID = soh.SalesPersonID
                FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            ) AS Employee,
            (
                SELECT 
                    sod.ProductID AS [Id],
                    pr.Name AS [Name],
                    sod.OrderQty AS [Quantity],
                    sod.UnitPrice AS [Price]
                FROM AdventureWorks2019.Sales.SalesOrderDetail sod
                INNER JOIN AdventureWorks2019.Production.Product pr
                    ON sod.ProductID = pr.ProductID
                WHERE sod.SalesOrderID = soh.SalesOrderID
                FOR JSON PATH
            ) AS Products
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) AS jsonData
FROM AdventureWorks2019.Sales.SalesOrderHeader soh;
GO