iridium_server 0.1.12

//! Schema definitions for playground database
//!
//! Follows SRP: only responsible for DDL structure

/// DDL statements to create playground tables
pub const DDL_STATEMENTS: &[&str] = &[
    // Customers table
    r#"
CREATE TABLE dbo.Customers (
    CustomerId INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Phone VARCHAR(20),
    CreatedDate DATETIME2 NOT NULL DEFAULT GETDATE(),
    IsActive BIT NOT NULL DEFAULT 1
);
"#,
    // Products table
    r#"
CREATE TABLE dbo.Products (
    ProductId INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Description NVARCHAR(500),
    Price DECIMAL(10,2) NOT NULL,
    Stock INT NOT NULL DEFAULT 0,
    Category NVARCHAR(50),
    IsAvailable BIT NOT NULL DEFAULT 1
);
"#,
    // Orders table
    r#"
CREATE TABLE dbo.Orders (
    OrderId INT IDENTITY(1,1) PRIMARY KEY,
    CustomerId INT NOT NULL,
    OrderDate DATETIME2 NOT NULL DEFAULT GETDATE(),
    TotalAmount DECIMAL(12,2) NOT NULL DEFAULT 0,
    Status NVARCHAR(20) NOT NULL DEFAULT 'Pending',
    CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerId) REFERENCES dbo.Customers(CustomerId)
);
"#,
    // OrderItems table
    r#"
CREATE TABLE dbo.OrderItems (
    OrderItemId INT IDENTITY(1,1) PRIMARY KEY,
    OrderId INT NOT NULL,
    ProductId INT NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(10,2) NOT NULL,
    Subtotal DECIMAL(12,2) NOT NULL,
    CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (OrderId) REFERENCES dbo.Orders(OrderId),
    CONSTRAINT FK_OrderItems_Products FOREIGN KEY (ProductId) REFERENCES dbo.Products(ProductId)
);
"#,
    // Employees table
    r#"
CREATE TABLE dbo.Employees (
    EmployeeId INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Department NVARCHAR(50),
    Salary DECIMAL(12,2),
    HireDate DATE NOT NULL,
    ManagerId INT NULL,
    CONSTRAINT FK_Employees_Managers FOREIGN KEY (ManagerId) REFERENCES dbo.Employees(EmployeeId)
);
"#,
    // Categories table (for reporting)
    r#"
CREATE TABLE dbo.Categories (
    CategoryId INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL,
    ParentCategoryId INT NULL,
    Description NVARCHAR(200),
    CONSTRAINT FK_Categories_Parent FOREIGN KEY (ParentCategoryId) REFERENCES dbo.Categories(CategoryId)
);
"#,
    r#"
CREATE TRIGGER dbo.tr_CustomerUpdate ON dbo.Customers
AFTER UPDATE
AS
BEGIN
    PRINT 'Customer updated';
END
"#,
    r#"
CREATE PROCEDURE dbo.uspGetOrderDetails AS BEGIN SELECT 1 END
"#,
    r#"
CREATE PROCEDURE dbo.xp_instance_regread AS BEGIN SELECT 1 END
"#,
    r#"
CREATE PROCEDURE dbo.sp_msgetversion AS BEGIN SELECT 1 END
"#,
];