01_retail.sql

-- ==========================================================================
-- RetailDB - E-commerce / order management (primary vertical)
-- Canonical OLTP shape: small dimensions feeding large append-heavy facts.
-- All tables have an IDENTITY PK + Change Tracking (Openflow requirement).
-- Data is loaded separately by the Python loader (loader/); this file only
-- creates objects.
-- ==========================================================================

IF DB_ID('RetailDB') IS NOT NULL
BEGIN
    ALTER DATABASE RetailDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE RetailDB;
END
GO

CREATE DATABASE RetailDB;
GO

USE RetailDB;
GO

-- SIMPLE recovery keeps the transaction log small during large bulk loads.
ALTER DATABASE RetailDB SET RECOVERY SIMPLE;
GO

ALTER DATABASE RetailDB SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
GO

-- --------------------------------------------------------------------------
-- Dimensions
-- --------------------------------------------------------------------------

CREATE TABLE Categories (
    CategoryID       INT IDENTITY(1,1) PRIMARY KEY,
    ParentCategoryID INT NULL,
    Name             NVARCHAR(100) NOT NULL,
    CreatedAt        DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT FK_Categories_Parent FOREIGN KEY (ParentCategoryID) REFERENCES Categories(CategoryID)
);
GO

CREATE TABLE Customers (
    CustomerID     INT IDENTITY(1,1) PRIMARY KEY,
    CustomerNumber NVARCHAR(20)  NOT NULL CONSTRAINT UQ_Customers_Number UNIQUE,
    Email          NVARCHAR(256) NOT NULL CONSTRAINT UQ_Customers_Email  UNIQUE,
    FirstName      NVARCHAR(100),
    LastName       NVARCHAR(100),
    Phone          NVARCHAR(30),
    Status         NVARCHAR(20)  NOT NULL DEFAULT 'active',
    CreatedAt      DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt      DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    IsDeleted      BIT           NOT NULL DEFAULT 0
);
GO

CREATE TABLE Products (
    ProductID  INT IDENTITY(1,1) PRIMARY KEY,
    SKU        NVARCHAR(40)  NOT NULL CONSTRAINT UQ_Products_SKU UNIQUE,
    CategoryID INT           NOT NULL,
    Name       NVARCHAR(200) NOT NULL,
    UnitPrice  DECIMAL(18,2) NOT NULL,
    Cost       DECIMAL(18,2),
    Status     NVARCHAR(20)  NOT NULL DEFAULT 'active',
    CreatedAt  DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt  DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    IsDeleted  BIT           NOT NULL DEFAULT 0,
    CONSTRAINT FK_Products_Category FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);
GO

CREATE TABLE Addresses (
    AddressID   INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID  INT NOT NULL,
    AddressType NVARCHAR(10),
    Line1       NVARCHAR(200),
    Line2       NVARCHAR(200),
    City        NVARCHAR(100),
    Region      NVARCHAR(100),
    PostalCode  NVARCHAR(20),
    CountryCode CHAR(2),
    CreatedAt   DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt   DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT FK_Addresses_Customer FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
GO

CREATE TABLE Inventory (
    InventoryID      INT IDENTITY(1,1) PRIMARY KEY,
    ProductID        INT NOT NULL,
    WarehouseID      INT NOT NULL,
    QuantityOnHand   INT NOT NULL DEFAULT 0,
    QuantityReserved INT NOT NULL DEFAULT 0,
    UpdatedAt        DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT FK_Inventory_Product FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
GO

-- --------------------------------------------------------------------------
-- Facts
-- --------------------------------------------------------------------------

CREATE TABLE Orders (
    OrderID           INT IDENTITY(1,1) PRIMARY KEY,
    OrderNumber       NVARCHAR(30)  NOT NULL CONSTRAINT UQ_Orders_Number UNIQUE,
    CustomerID        INT           NOT NULL,
    BillingAddressID  INT NULL,
    ShippingAddressID INT NULL,
    Status            NVARCHAR(20)  NOT NULL,
    OrderDate         DATETIME2(3)  NOT NULL,
    Subtotal          DECIMAL(18,2),
    TaxAmount         DECIMAL(18,2),
    ShippingAmount    DECIMAL(18,2),
    TotalAmount       DECIMAL(18,2) NOT NULL,
    CurrencyCode      CHAR(3)       NOT NULL DEFAULT 'USD',
    CreatedAt         DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt         DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT FK_Orders_Customer   FOREIGN KEY (CustomerID)        REFERENCES Customers(CustomerID),
    CONSTRAINT FK_Orders_BillAddr   FOREIGN KEY (BillingAddressID)  REFERENCES Addresses(AddressID),
    CONSTRAINT FK_Orders_ShipAddr   FOREIGN KEY (ShippingAddressID) REFERENCES Addresses(AddressID)
);
GO

CREATE TABLE OrderLineItems (
    OrderLineID    BIGINT IDENTITY(1,1) PRIMARY KEY,
    OrderID        INT           NOT NULL,
    ProductID      INT           NOT NULL,
    Quantity       INT           NOT NULL,
    UnitPrice      DECIMAL(18,2) NOT NULL,
    DiscountAmount DECIMAL(18,2) NOT NULL DEFAULT 0,
    LineTotal      DECIMAL(18,2) NOT NULL,
    CreatedAt      DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT FK_OrderLines_Order   FOREIGN KEY (OrderID)   REFERENCES Orders(OrderID),
    CONSTRAINT FK_OrderLines_Product FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
GO

CREATE TABLE Payments (
    PaymentID    BIGINT IDENTITY(1,1) PRIMARY KEY,
    OrderID      INT           NOT NULL,
    Amount       DECIMAL(18,2) NOT NULL,
    CurrencyCode CHAR(3),
    Method       NVARCHAR(20),
    Status       NVARCHAR(20),
    ProcessedAt  DATETIME2(3),
    CreatedAt    DATETIME2(3)  NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT FK_Payments_Order FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);
GO

CREATE TABLE Shipments (
    ShipmentID     BIGINT IDENTITY(1,1) PRIMARY KEY,
    OrderID        INT NOT NULL,
    Carrier        NVARCHAR(50),
    TrackingNumber NVARCHAR(60),
    Status         NVARCHAR(20),
    ShippedAt      DATETIME2(3),
    DeliveredAt    DATETIME2(3),
    CreatedAt      DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT FK_Shipments_Order FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);
GO

CREATE TABLE Returns (
    ReturnID     BIGINT IDENTITY(1,1) PRIMARY KEY,
    OrderLineID  BIGINT NOT NULL,
    Reason       NVARCHAR(100),
    Quantity     INT,
    RefundAmount DECIMAL(18,2),
    Status       NVARCHAR(20),
    CreatedAt    DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt    DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT FK_Returns_OrderLine FOREIGN KEY (OrderLineID) REFERENCES OrderLineItems(OrderLineID)
);
GO

-- --------------------------------------------------------------------------
-- Secondary indexes (FK columns + date ranges). SQL Server does not create
-- FK indexes automatically.
-- --------------------------------------------------------------------------
CREATE INDEX IX_Products_Category      ON Products(CategoryID);
CREATE INDEX IX_Addresses_Customer     ON Addresses(CustomerID);
CREATE INDEX IX_Inventory_Product      ON Inventory(ProductID);
CREATE INDEX IX_Orders_Customer        ON Orders(CustomerID);
CREATE INDEX IX_Orders_OrderDate       ON Orders(OrderDate);
CREATE INDEX IX_OrderLines_Order       ON OrderLineItems(OrderID);
CREATE INDEX IX_OrderLines_Product     ON OrderLineItems(ProductID);
CREATE INDEX IX_Payments_Order         ON Payments(OrderID);
CREATE INDEX IX_Shipments_Order        ON Shipments(OrderID);
CREATE INDEX IX_Returns_OrderLine      ON Returns(OrderLineID);
GO

-- --------------------------------------------------------------------------
-- Enable Change Tracking on every table.
-- --------------------------------------------------------------------------
ALTER TABLE Categories     ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE Customers      ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE Products       ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE Addresses      ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE Inventory      ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE Orders         ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE OrderLineItems ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE Payments       ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE Shipments      ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE Returns        ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
GO

PRINT 'RetailDB schema created.';
GO