source: branches/1888_OaaS/HeuristicLab.Services.Optimization.Billing.Test/DDLScript.sql @ 16371

create table [dbo].[ContactInformations] (
    [ContactInformationId] [bigint] not null identity,
    [OrganizationName] [nvarchar](max) null,
    [Street] [nvarchar](max) null,
    [City] [nvarchar](max) null,
    [State] [nvarchar](max) null,
    [PostalCode] [nvarchar](max) null,
    [LastName] [nvarchar](max) null,
    [FirstName] [nvarchar](max) null,
    [Email] [nvarchar](max) null,
    primary key ([ContactInformationId])
);
create table [dbo].[Invoices] (
    [InvoiceId] [bigint] not null identity,
    [UserId] [bigint] not null,
    [OrderId] [bigint] not null,
    [InvoiceDate] [datetime] not null,
    [Due] [datetime] not null,
    [StatusValue] [int] not null,
    [SubTotal] [float] not null,
    [Total] [float] not null,
    [Tax] [float] not null,
    [InvoiceDocument] [nvarchar](max) null,
    primary key ([InvoiceId])
);
create table [dbo].[InvoiceLines] (
    [InvoiceLineId] [bigint] not null identity,
    [InvoiceId] [bigint] not null,
    [ProductId] [bigint] not null,
    [Quantity] [int] not null,
    [ProductPrice] [float] not null,
    primary key ([InvoiceLineId])
);
create table [dbo].[Orders] (
    [OrderId] [bigint] not null identity,
    [UserId] [bigint] not null,
    [StateValue] [int] not null,
    [BillingTypeValue] [int] not null,
    [BillingPeriodValue] [int] not null,
    [ActiveSince] [datetime] null,
    [ActiveUntil] [datetime] null,
    [LastBillableDay] [datetime] null,
    [NextBillableDay] [datetime] null,
    primary key ([OrderId])
);
create table [dbo].[OrderLines] (
    [OrderLineId] [bigint] not null identity,
    [OrderId] [bigint] not null,
    [ProductId] [bigint] not null,
    [Quantity] [int] not null,
    [ProductPrice] [float] not null,
    primary key ([OrderLineId])
);
create table [dbo].[PaymentInformations] (
    [PaymentInformationId] [bigint] not null identity,
    [UserId] [bigint] not null,
    [CardNumber] [nvarchar](max) null,
    [PaymentMethodValue] [int] not null,
    [AdditionalInformation] [nvarchar](max) null,
    primary key ([PaymentInformationId])
);
create table [dbo].[Products] (
    [ProductId] [bigint] not null identity,
    [Name] [nvarchar](max) null,
    [Description] [nvarchar](max) null,
    [ProductType] [nvarchar](max) null,
    [Price] [float] not null,
    primary key ([ProductId])
);
create table [dbo].[UsageRecords] (
    [UsageRecordId] [bigint] not null identity,
    [UserId] [bigint] not null,
    [ProductId] [bigint] not null,
    [Begin] [datetime] not null,
    [End] [datetime] not null,
    [ServiceIdentifier] [int] not null,
    [ResourceIdentifier] [int] not null,
    primary key ([UsageRecordId])
);
create table [dbo].[Users] (
    [UserId] [bigint] not null identity,
    [ContactInformationId] [bigint] not null,
    [Name] [nvarchar](max) null,
    primary key ([UserId])
);
alter table [dbo].[Invoices] add constraint [Invoice_Order] foreign key ([OrderId]) references [dbo].[Orders]([OrderId]);
alter table [dbo].[Invoices] add constraint [Invoice_User] foreign key ([UserId]) references [dbo].[Users]([UserId]);
alter table [dbo].[InvoiceLines] add constraint [InvoiceLine_Invoice] foreign key ([InvoiceId]) references [dbo].[Invoices]([InvoiceId]) on delete cascade;
alter table [dbo].[InvoiceLines] add constraint [InvoiceLine_Product] foreign key ([ProductId]) references [dbo].[Products]([ProductId]);
alter table [dbo].[Orders] add constraint [Order_User] foreign key ([UserId]) references [dbo].[Users]([UserId]);
alter table [dbo].[OrderLines] add constraint [OrderLine_Order] foreign key ([OrderId]) references [dbo].[Orders]([OrderId]) on delete cascade;
alter table [dbo].[OrderLines] add constraint [OrderLine_Product] foreign key ([ProductId]) references [dbo].[Products]([ProductId]);
alter table [dbo].[UsageRecords] add constraint [UsageRecord_Product] foreign key ([ProductId]) references [dbo].[Products]([ProductId]);
alter table [dbo].[UsageRecords] add constraint [UsageRecord_User] foreign key ([UserId]) references [dbo].[Users]([UserId]);
alter table [dbo].[Users] add constraint [User_ContactInformation] foreign key ([ContactInformationId]) references [dbo].[ContactInformations]([ContactInformationId]);
alter table [dbo].[PaymentInformations] add constraint [User_PaymentInformation] foreign key ([UserId]) references [dbo].[Users]([UserId]);