-- AuroraRetail — Skupa demo database seed
-- Creates a realistic retail schema with genuine cross-object dependencies
-- (FKs, view/proc/function references, a trigger) so a Skupa scan produces
-- a rich swimlane graph. Safe to re-run: drops demo objects first.
--
-- Target: any Azure SQL Database (the serverless/free tier is plenty).
-- Run in the Azure portal Query Editor or SSMS, then scan with Skupa.

------------------------------------------------------------ clean slate
DROP PROCEDURE IF EXISTS sales.usp_MonthlySalesReport;
DROP PROCEDURE IF EXISTS dbo.usp_RestockInventory;
DROP PROCEDURE IF EXISTS dbo.usp_PlaceOrder;
DROP VIEW IF EXISTS sales.vw_CustomerLifetimeValue;
DROP VIEW IF EXISTS dbo.vw_ProductCatalog;
DROP VIEW IF EXISTS dbo.vw_OpenOrders;
DROP TRIGGER IF EXISTS dbo.trg_Orders_Audit;
DROP FUNCTION IF EXISTS dbo.fn_CustomerTier;
DROP FUNCTION IF EXISTS dbo.fn_OrderTotal;
DROP FUNCTION IF EXISTS dbo.fn_CurrentPrice;
DROP TYPE IF EXISTS dbo.OrderLineList;
IF SCHEMA_ID('audit') IS NOT NULL DROP SCHEMA [audit];
IF SCHEMA_ID('sales') IS NOT NULL DROP SCHEMA sales;
GO

CREATE SCHEMA [audit];
GO
CREATE SCHEMA sales;
GO

------------------------------------------------------------ tables
CREATE TABLE dbo.Categories (
    CategoryId INT IDENTITY PRIMARY KEY,
    Name       NVARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE dbo.Suppliers (
    SupplierId INT IDENTITY PRIMARY KEY,
    Name       NVARCHAR(200) NOT NULL,
    Country    NVARCHAR(64)  NOT NULL DEFAULT 'USA'
);

CREATE TABLE dbo.Customers (
    CustomerId INT IDENTITY PRIMARY KEY,
    Email      NVARCHAR(256) NOT NULL UNIQUE,
    FullName   NVARCHAR(200) NOT NULL,
    CreatedUtc DATETIME2     NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE dbo.Products (
    ProductId  INT IDENTITY PRIMARY KEY,
    CategoryId INT NOT NULL CONSTRAINT FK_Products_Categories REFERENCES dbo.Categories(CategoryId),
    SupplierId INT NOT NULL CONSTRAINT FK_Products_Suppliers  REFERENCES dbo.Suppliers(SupplierId),
    Sku        NVARCHAR(32)  NOT NULL UNIQUE,
    Name       NVARCHAR(200) NOT NULL,
    ListPrice  DECIMAL(10,2) NOT NULL
);

CREATE TABLE dbo.Orders (
    OrderId    INT IDENTITY PRIMARY KEY,
    CustomerId INT NOT NULL CONSTRAINT FK_Orders_Customers REFERENCES dbo.Customers(CustomerId),
    Status     TINYINT   NOT NULL DEFAULT 0,   -- 0 new, 1 picking, 2 shipped
    PlacedUtc  DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE TABLE dbo.OrderLines (
    OrderLineId BIGINT IDENTITY 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
);

CREATE TABLE dbo.Inventory (
    ProductId  INT NOT NULL PRIMARY KEY
        CONSTRAINT FK_Inventory_Products REFERENCES dbo.Products(ProductId),
    OnHand     INT NOT NULL DEFAULT 0,
    ReorderAt  INT NOT NULL DEFAULT 10
);

CREATE TABLE audit.AuditLog (
    AuditId    BIGINT IDENTITY PRIMARY KEY,
    TableName  SYSNAME   NOT NULL,
    KeyValue   INT       NOT NULL,
    ChangedUtc DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

CREATE TYPE dbo.OrderLineList AS TABLE (ProductId INT NOT NULL, Quantity INT NOT NULL);
GO

------------------------------------------------------------ functions
CREATE FUNCTION dbo.fn_CurrentPrice (@ProductId INT)
RETURNS DECIMAL(10,2) AS
BEGIN
    RETURN (SELECT ListPrice FROM dbo.Products WHERE ProductId = @ProductId);
END;
GO

CREATE FUNCTION dbo.fn_OrderTotal (@OrderId INT)
RETURNS DECIMAL(12,2) AS
BEGIN
    RETURN (SELECT SUM(Quantity * UnitPrice) FROM dbo.OrderLines WHERE OrderId = @OrderId);
END;
GO

CREATE FUNCTION dbo.fn_CustomerTier (@CustomerId INT)
RETURNS NVARCHAR(10) AS
BEGIN
    DECLARE @spend DECIMAL(14,2) = (
        SELECT SUM(l.Quantity * l.UnitPrice)
        FROM dbo.Orders o JOIN dbo.OrderLines l ON l.OrderId = o.OrderId
        WHERE o.CustomerId = @CustomerId);
    RETURN CASE WHEN @spend >= 5000 THEN N'gold'
                WHEN @spend >= 1000 THEN N'silver'
                ELSE N'bronze' END;
END;
GO

------------------------------------------------------------ views
CREATE VIEW dbo.vw_OpenOrders AS
SELECT o.OrderId, c.FullName, o.PlacedUtc,
       dbo.fn_OrderTotal(o.OrderId) AS OrderTotal
FROM dbo.Orders o
JOIN dbo.Customers c ON c.CustomerId = o.CustomerId
WHERE o.Status IN (0, 1);
GO

CREATE VIEW dbo.vw_ProductCatalog AS
SELECT p.Sku, p.Name, cat.Name AS Category, s.Name AS Supplier,
       p.ListPrice, i.OnHand
FROM dbo.Products p
JOIN dbo.Categories cat ON cat.CategoryId = p.CategoryId
JOIN dbo.Suppliers s    ON s.SupplierId   = p.SupplierId
JOIN dbo.Inventory i    ON i.ProductId    = p.ProductId;
GO

CREATE VIEW sales.vw_CustomerLifetimeValue AS
SELECT c.CustomerId, c.FullName,
       SUM(l.Quantity * l.UnitPrice) AS LifetimeValue,
       dbo.fn_CustomerTier(c.CustomerId) AS Tier
FROM dbo.Customers c
JOIN dbo.Orders o     ON o.CustomerId = c.CustomerId
JOIN dbo.OrderLines l ON l.OrderId    = o.OrderId
GROUP BY c.CustomerId, c.FullName;
GO

------------------------------------------------------------ procedures
CREATE PROCEDURE dbo.usp_PlaceOrder
    @CustomerId INT, @Lines dbo.OrderLineList READONLY
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRAN;
        INSERT dbo.Orders (CustomerId) VALUES (@CustomerId);
        DECLARE @OrderId INT = SCOPE_IDENTITY();
        INSERT dbo.OrderLines (OrderId, ProductId, Quantity, UnitPrice)
        SELECT @OrderId, ProductId, Quantity, dbo.fn_CurrentPrice(ProductId)
        FROM @Lines;
        UPDATE i SET OnHand = i.OnHand - l.Quantity
        FROM dbo.Inventory i JOIN @Lines l ON l.ProductId = i.ProductId;
    COMMIT;
END;
GO

CREATE PROCEDURE dbo.usp_RestockInventory
    @ProductId INT, @Quantity INT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.Inventory SET OnHand = OnHand + @Quantity WHERE ProductId = @ProductId;
    SELECT s.Name AS Supplier
    FROM dbo.Products p JOIN dbo.Suppliers s ON s.SupplierId = p.SupplierId
    WHERE p.ProductId = @ProductId;
END;
GO

CREATE PROCEDURE sales.usp_MonthlySalesReport
    @Year INT, @Month INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM dbo.vw_OpenOrders
    WHERE YEAR(PlacedUtc) = @Year AND MONTH(PlacedUtc) = @Month;
    SELECT TOP 10 * FROM sales.vw_CustomerLifetimeValue ORDER BY LifetimeValue DESC;
END;
GO

------------------------------------------------------------ trigger
CREATE TRIGGER dbo.trg_Orders_Audit ON dbo.Orders AFTER INSERT, UPDATE AS
INSERT audit.AuditLog (TableName, KeyValue)
SELECT 'Orders', OrderId FROM inserted;
GO

------------------------------------------------------------ sample data
INSERT dbo.Categories (Name) VALUES (N'Lighting'), (N'Furniture'), (N'Textiles');
INSERT dbo.Suppliers (Name, Country) VALUES
    (N'Borealis Wholesale', N'Canada'), (N'Hudson Supply Co.', N'USA');
INSERT dbo.Customers (Email, FullName) VALUES
    (N'ava@example.com',  N'Ava Chen'),
    (N'liam@example.com', N'Liam Ortiz'),
    (N'noor@example.com', N'Noor Haddad');
INSERT dbo.Products (CategoryId, SupplierId, Sku, Name, ListPrice) VALUES
    (1, 1, N'LM-100', N'Aurora Floor Lamp',    129.00),
    (1, 2, N'LM-220', N'Polar Desk Light',      54.50),
    (2, 2, N'FN-310', N'Fjord Oak Side Table', 219.00),
    (3, 1, N'TX-405', N'Glacier Throw Blanket', 74.25);
INSERT dbo.Inventory (ProductId, OnHand) VALUES (1, 42), (2, 108), (3, 17), (4, 63);

DECLARE @lines dbo.OrderLineList;
INSERT @lines VALUES (1, 1), (4, 2);
EXEC dbo.usp_PlaceOrder @CustomerId = 1, @Lines = @lines;
DELETE @lines; INSERT @lines VALUES (2, 3), (3, 1);
EXEC dbo.usp_PlaceOrder @CustomerId = 2, @Lines = @lines;
GO

SELECT 'AuroraRetail seeded.' AS Result,
       (SELECT COUNT(*) FROM dbo.Orders)     AS Orders,
       (SELECT COUNT(*) FROM dbo.OrderLines) AS OrderLines;
