/*
    SpDevDemo: the small database the examples on spdev.app/blog run against.
    SQL Server 2016 or later (the articles were tested on SQL Server 2022). Takes a few seconds.
    The data is generated deterministically, so your row counts match the articles.

    20,000 customers, 500 products, 200,000 orders and 419,048 order lines.
    Skew on purpose: customer 1 (a marketplace account) has 20,000 orders; most customers have 9.
    90% of orders are Delivered, 6% Shipped, 3% Open, 1% Cancelled.
*/
IF DB_ID('SpDevDemo') IS NULL CREATE DATABASE SpDevDemo;
GO
USE SpDevDemo;
GO
DROP TABLE IF EXISTS dbo.OrderLines, dbo.Orders, dbo.Products, dbo.Customers;

CREATE TABLE dbo.Customers (
    CustomerId INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_Customers PRIMARY KEY,
    Name       NVARCHAR(100) NOT NULL,
    Email      VARCHAR(200)  NOT NULL,
    Country    CHAR(2)       NOT NULL,
    CreatedAt  DATETIME2(0)  NOT NULL
);

CREATE TABLE dbo.Products (
    ProductId INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_Products PRIMARY KEY,
    Name      NVARCHAR(100) NOT NULL,
    Category  VARCHAR(30)   NOT NULL,
    ListPrice DECIMAL(10, 2) NOT NULL
);

CREATE TABLE dbo.Orders (
    OrderId     INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_Orders PRIMARY KEY,
    CustomerId  INT            NOT NULL CONSTRAINT FK_Orders_Customers REFERENCES dbo.Customers (CustomerId),
    OrderNumber VARCHAR(20)    NOT NULL,
    Status      VARCHAR(20)    NOT NULL,
    OrderDate   DATE           NOT NULL,
    Total       DECIMAL(12, 2) NOT NULL
);

CREATE TABLE dbo.OrderLines (
    OrderLineId INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_OrderLines PRIMARY KEY,
    OrderId     INT            NOT NULL CONSTRAINT FK_OrderLines_Orders REFERENCES dbo.Orders (OrderId),
    ProductId   INT            NOT NULL CONSTRAINT FK_OrderLines_Products REFERENCES dbo.Products (ProductId),
    Quantity    INT            NOT NULL,
    UnitPrice   DECIMAL(10, 2) NOT NULL
);
GO

WITH n AS (SELECT TOP (20000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS i FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b)
INSERT dbo.Customers (Name, Email, Country, CreatedAt)
SELECT CONCAT(N'Customer ', i),
       CONCAT('customer', i, '@example.com'),
       CASE i % 5 WHEN 0 THEN 'US' WHEN 1 THEN 'GB' WHEN 2 THEN 'DE' WHEN 3 THEN 'PK' ELSE 'AE' END,
       DATEADD(DAY, i % 1400, '2022-06-01')
FROM n;

WITH n AS (SELECT TOP (500) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS i FROM sys.all_columns)
INSERT dbo.Products (Name, Category, ListPrice)
SELECT CONCAT(N'Product ', i),
       CASE i % 4 WHEN 0 THEN 'Hardware' WHEN 1 THEN 'Software' WHEN 2 THEN 'Services' ELSE 'Training' END,
       5 + (i * 37) % 495
FROM n;

WITH n AS (SELECT TOP (200000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS i FROM sys.all_columns AS a CROSS JOIN sys.all_columns AS b)
INSERT dbo.Orders (CustomerId, OrderNumber, Status, OrderDate, Total)
SELECT CASE WHEN i % 10 = 0 THEN 1 ELSE 2 + (i * 7919) % 19999 END,
       CONCAT('SO-', RIGHT(CONCAT('0000000', i), 7)),
       CASE WHEN i % 100 < 90 THEN 'Delivered' WHEN i % 100 < 96 THEN 'Shipped' WHEN i % 100 < 99 THEN 'Open' ELSE 'Cancelled' END,
       DATEADD(DAY, i * 1400 / 200000, '2023-01-01'),
       0
FROM n;

WITH n AS (SELECT TOP (3) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS line FROM sys.all_columns)
INSERT dbo.OrderLines (OrderId, ProductId, Quantity, UnitPrice)
SELECT o.OrderId, 1 + (o.OrderId * 31 + n.line * 17) % 500, 1 + (o.OrderId + n.line) % 5, p.ListPrice
FROM dbo.Orders AS o
JOIN n ON n.line <= 1 + o.OrderId % 3 + CASE WHEN o.OrderId % 7 = 0 THEN 1 ELSE 0 END
JOIN dbo.Products AS p ON p.ProductId = 1 + (o.OrderId * 31 + n.line * 17) % 500;

UPDATE o SET Total = l.Total
FROM dbo.Orders AS o
JOIN (SELECT OrderId, SUM(Quantity * UnitPrice) AS Total FROM dbo.OrderLines GROUP BY OrderId) AS l ON l.OrderId = o.OrderId;

CREATE UNIQUE INDEX IX_Orders_OrderNumber ON dbo.Orders (OrderNumber);
CREATE INDEX IX_Orders_CustomerId ON dbo.Orders (CustomerId);
CREATE INDEX IX_OrderLines_OrderId ON dbo.OrderLines (OrderId);
CREATE INDEX IX_Customers_Email ON dbo.Customers (Email);
GO
SELECT (SELECT COUNT(*) FROM dbo.Customers) AS Customers, (SELECT COUNT(*) FROM dbo.Products) AS Products,
       (SELECT COUNT(*) FROM dbo.Orders) AS Orders, (SELECT COUNT(*) FROM dbo.OrderLines) AS OrderLines;