bo/@Database/bo_create_schema.sql

-- 12.8.2021 1.0.6
-- Table User --------------------------
--Defines User identity with statistics informations. Defines if user is enabled and/or user is system account also.
CREATE TABLE [dbo].[User]
(
    [Id] [bigint] NOT NULL IDENTITY (1, 1),
    [IFReference] [varchar] (50) NOT NULL,
    [Name] [nvarchar] (200) NOT NULL,
    [LastLogged] [datetime] NULL,
    [LastDisabled] [datetime] NULL,
    [LastEnabled] [datetime] NULL,
    [Created] [datetime] NOT NULL CONSTRAINT DF_User_Created DEFAULT getdate(),
    [Modified] [datetime] NULL,
    [LangLCID] [int] NOT NULL CONSTRAINT DF_User_LangLCID DEFAULT 1033,
    [IsSystem] [bit] NOT NULL CONSTRAINT DF_User_IsSystem DEFAULT 0,
    [IsEnabled] [bit] NOT NULL CONSTRAINT DF_User_IsEnabled DEFAULT 0,
    CONSTRAINT PK_USER_ID PRIMARY KEY (Id)	
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User primary key' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'User', @level2type=N'COLUMN',@level2name=N'Id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to Identity framework IdentityUser.Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'User', @level2type=N'COLUMN',@level2name=N'IFReference'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Full user name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'User', @level2type=N'COLUMN',@level2name=N'Name'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp of user last successful login' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'User', @level2type=N'COLUMN',@level2name=N'LastLogged'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp of last user set to be disabled' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'User', @level2type=N'COLUMN',@level2name=N'LastDisabled'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp of last user set to be enabled' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'User', @level2type=N'COLUMN',@level2name=N'LastEnabled'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when user was created' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'User', @level2type=N'COLUMN',@level2name=N'Created'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when user was modified' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'User', @level2type=N'COLUMN',@level2name=N'Modified'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'LCID of preffered user language' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'User', @level2type=N'COLUMN',@level2name=N'LangLCID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Flag if user is system only account. Cannot log in.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'User', @level2type=N'COLUMN',@level2name=N'IsSystem'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Flag if user can sign on' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'User', @level2type=N'COLUMN',@level2name=N'IsEnabled'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User identity with properties' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'User'
GO


-- Table UserWorkspace --------------------------
--Association between User and Workspace. Which Workspace is assigned to user.
CREATE TABLE [dbo].[UserWorkspace]
(
    [UserId] [bigint] NOT NULL,
    [WorkspaceId] [bigint] NOT NULL,
    [IsDefault] [bit] NOT NULL CONSTRAINT DF_UserWorkspace_IsDefault DEFAULT 0
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UserWorkspace', @level2type=N'COLUMN',@level2name=N'UserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to workspace record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UserWorkspace', @level2type=N'COLUMN',@level2name=N'WorkspaceId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Flag if relation is default' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UserWorkspace', @level2type=N'COLUMN',@level2name=N'IsDefault'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Association between User and Workspace. Which Workspace is assigned to user.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'UserWorkspace'
GO


-- Table Workspace --------------------------
--Main workspace root structure holds information about working space (set of documents) and their business plan
CREATE TABLE [dbo].[Workspace]
(
    [Id] [bigint] NOT NULL IDENTITY (1, 1),
    [Name] [nvarchar] (200) NOT NULL,
    [BillingInfoId] [bigint] NOT NULL,
    [BillingPlanId] [bigint] NOT NULL,
    [APIKey] [uniqueidentifier] NOT NULL CONSTRAINT DF_Workspace_APIKey DEFAULT newid(),
    [APIPassword] [nvarchar] (50) NOT NULL,
    [Created] [datetime] NOT NULL CONSTRAINT DF_Workspace_Created DEFAULT getdate(),
    [Modified] [datetime] NULL,
    [CreatedByUserId] [bigint] NOT NULL,
    [ModifiedByUserId] [bigint] NULL,
    [IsGold] [bit] NOT NULL CONSTRAINT DF_Workspace_IsGold DEFAULT 0,
    CONSTRAINT PK_WORKSPACE_ID PRIMARY KEY (Id)	
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Workspace primary key' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Workspace', @level2type=N'COLUMN',@level2name=N'Id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Workspace name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Workspace', @level2type=N'COLUMN',@level2name=N'Name'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to current billing info record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Workspace', @level2type=N'COLUMN',@level2name=N'BillingInfoId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to current billing plan record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Workspace', @level2type=N'COLUMN',@level2name=N'BillingPlanId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'API Key for access to workspace via B2B interface' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Workspace', @level2type=N'COLUMN',@level2name=N'APIKey'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'API Key Password for access to workspace via B2B interface' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Workspace', @level2type=N'COLUMN',@level2name=N'APIPassword'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when workspace was created' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Workspace', @level2type=N'COLUMN',@level2name=N'Created'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when workspace was modified' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Workspace', @level2type=N'COLUMN',@level2name=N'Modified'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who created workspace' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Workspace', @level2type=N'COLUMN',@level2name=N'CreatedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who updated workspace' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Workspace', @level2type=N'COLUMN',@level2name=N'ModifiedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Define if workspace is GOLD mode (has custom billing plan)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Workspace', @level2type=N'COLUMN',@level2name=N'IsGold'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Main workspace root structure holds information about working space (set of documents) and their business plan' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Workspace'
GO


-- Table BillingInfo --------------------------
--Workspace billing information for invoicing
CREATE TABLE [dbo].[BillingInfo]
(
    [Id] [bigint] NOT NULL IDENTITY (1, 1),
    [WorkspaceId] [bigint] NOT NULL,
    [SubjectName] [nvarchar] (200) NOT NULL,
    [Address1] [nvarchar] (200) NOT NULL,
    [Address2] [nvarchar] (200) NULL,
    [City] [nvarchar] (100) NOT NULL,
    [ZIP] [nvarchar] (20) NOT NULL,
    [Country] [nvarchar] (50) NOT NULL,
    [TaxNumber] [nvarchar] (20) NOT NULL,
    [VATNumber] [nvarchar] (20) NULL,
    [Phone] [nvarchar] (20) NULL,
    [Email] [nvarchar] (20) NOT NULL,
    [Created] [datetime] NOT NULL CONSTRAINT DF_BillingInfo_Created DEFAULT getdate(),
    [Modified] [datetime] NULL,
    [CreatedByUserId] [bigint] NOT NULL,
    [ModifiedByUserId] [bigint] NULL,
    CONSTRAINT PK_BILLING_INFO_ID PRIMARY KEY (Id)	
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key of billing information record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingInfo', @level2type=N'COLUMN',@level2name=N'Id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to owning workspace' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingInfo', @level2type=N'COLUMN',@level2name=N'WorkspaceId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing Subject name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingInfo', @level2type=N'COLUMN',@level2name=N'SubjectName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing Address line 1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingInfo', @level2type=N'COLUMN',@level2name=N'Address1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing Address line 2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingInfo', @level2type=N'COLUMN',@level2name=N'Address2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing Address city' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingInfo', @level2type=N'COLUMN',@level2name=N'City'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing Address ZIP' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingInfo', @level2type=N'COLUMN',@level2name=N'ZIP'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing Address country' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingInfo', @level2type=N'COLUMN',@level2name=N'Country'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing Tax number' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingInfo', @level2type=N'COLUMN',@level2name=N'TaxNumber'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing VAT number' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingInfo', @level2type=N'COLUMN',@level2name=N'VATNumber'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing phone with format +XXXXXXXXX' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingInfo', @level2type=N'COLUMN',@level2name=N'Phone'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing email' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingInfo', @level2type=N'COLUMN',@level2name=N'Email'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when billing info was created' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingInfo', @level2type=N'COLUMN',@level2name=N'Created'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when billing info was modified' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingInfo', @level2type=N'COLUMN',@level2name=N'Modified'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who created billing info' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingInfo', @level2type=N'COLUMN',@level2name=N'CreatedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who updated billing info' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingInfo', @level2type=N'COLUMN',@level2name=N'ModifiedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Workspace billing information for invoicing' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingInfo'
GO


-- Table Structure --------------------------
--Abstract repository structure (folder-like) where documents are assignig
CREATE TABLE [dbo].[Structure]
(
    [Id] [bigint] NOT NULL IDENTITY (1, 1),
    [Name] [nvarchar] (200) NOT NULL,
    [WorkspaceId] [bigint] NOT NULL,
    [ParentId] [bigint] NULL,
    [Created] [datetime] NOT NULL CONSTRAINT DF_Structure_Created DEFAULT getdate(),
    [Modified] [datetime] NULL,
    [CreatedByUserId] [bigint] NOT NULL,
    [ModifiedByUserId] [bigint] NULL,
    CONSTRAINT PK_STRUCTURE_ID PRIMARY KEY (Id)	
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Structure primary key' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Structure', @level2type=N'COLUMN',@level2name=N'Id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Structure name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Structure', @level2type=N'COLUMN',@level2name=N'Name'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to owning workspace' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Structure', @level2type=N'COLUMN',@level2name=N'WorkspaceId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to parent structure (folder). If null then record is root' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Structure', @level2type=N'COLUMN',@level2name=N'ParentId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when workspace was created' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Structure', @level2type=N'COLUMN',@level2name=N'Created'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when workspace was modified' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Structure', @level2type=N'COLUMN',@level2name=N'Modified'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who created workspace' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Structure', @level2type=N'COLUMN',@level2name=N'CreatedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who updated workspace' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Structure', @level2type=N'COLUMN',@level2name=N'ModifiedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Abstract repository structure (folder-like) where documents are assignig' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Structure'
GO


-- Table Metadocument --------------------------
--Abstract single document header to process. Has setting parameters for computing and status
CREATE TABLE [dbo].[Metadocument]
(
    [Id] [bigint] NOT NULL IDENTITY (1, 1),
    [StructureId] [bigint] NOT NULL,
    [Name] [nvarchar] (200) NOT NULL,
    [Status] [int] NOT NULL CONSTRAINT DF_Metadocument_Status DEFAULT 0,
    [Created] [datetime] NOT NULL CONSTRAINT DF_Metadocument_Created DEFAULT getdate(),
    [Modified] [datetime] NULL,
    [LastDowloaded] [datetime] NULL,
    [CreatedByUserId] [bigint] NOT NULL,
    [ModifiedByUserId] [bigint] NULL,
    [LastDownloadedByUserId] [bigint] NULL,
    CONSTRAINT PK_METADOCUMENT_ID PRIMARY KEY (Id)	
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key of metadocument record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Metadocument', @level2type=N'COLUMN',@level2name=N'Id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to owning structure (workspace)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Metadocument', @level2type=N'COLUMN',@level2name=N'StructureId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Metadocument name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Metadocument', @level2type=N'COLUMN',@level2name=N'Name'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Metadocument status' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Metadocument', @level2type=N'COLUMN',@level2name=N'Status'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when metadocument was created (uploaded)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Metadocument', @level2type=N'COLUMN',@level2name=N'Created'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when metadocument was modified' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Metadocument', @level2type=N'COLUMN',@level2name=N'Modified'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when metadocument was last downloaded' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Metadocument', @level2type=N'COLUMN',@level2name=N'LastDowloaded'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who created (uploaded) metadocument' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Metadocument', @level2type=N'COLUMN',@level2name=N'CreatedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who updated metadocument' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Metadocument', @level2type=N'COLUMN',@level2name=N'ModifiedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who last metadocument dowloaded' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Metadocument', @level2type=N'COLUMN',@level2name=N'LastDownloadedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Abstract single document header to process. Has setting parameters for computing and status' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Metadocument'
GO


-- Table StatusHistory --------------------------
--Stores metadocument status changing log with messages
CREATE TABLE [dbo].[StatusHistory]
(
    [Id] [bigint] NOT NULL IDENTITY (1, 1),
    [MetadocumentId] [bigint] NOT NULL,
    [Status] [int] NOT NULL CONSTRAINT DF_StatusHistory_Status DEFAULT 0,
    [Message] [nvarchar] (4000) NULL,
    [Created] [datetime] NOT NULL CONSTRAINT DF_StatusHistory_Created DEFAULT getdate(),
    [CreatedByUserId] [bigint] NOT NULL,
    CONSTRAINT PK_STATUS_HISTORY_ID PRIMARY KEY (Id)	
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key of metadocument status history record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'StatusHistory', @level2type=N'COLUMN',@level2name=N'Id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to owning metadocument' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'StatusHistory', @level2type=N'COLUMN',@level2name=N'MetadocumentId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Metadocument status' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'StatusHistory', @level2type=N'COLUMN',@level2name=N'Status'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Status change message or error message. This value is optional.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'StatusHistory', @level2type=N'COLUMN',@level2name=N'Message'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when metadocument was created (uploaded)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'StatusHistory', @level2type=N'COLUMN',@level2name=N'Created'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who created (uploaded) metadocument' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'StatusHistory', @level2type=N'COLUMN',@level2name=N'CreatedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores metadocument status changing log with messages' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'StatusHistory'
GO


-- Table Artifact --------------------------
--Stores single part of metadocument (called artifact). Refresents fingle physical file included for processing.
CREATE TABLE [dbo].[Artifact]
(
    [Id] [bigint] NOT NULL IDENTITY (1, 1),
    [MetadocumentId] [bigint] NOT NULL,
    [Name] [nvarchar] (200) NOT NULL,
    [Extension] [nvarchar] (10) NULL,
    [MimeTypeId] [bigint] NOT NULL,
    [Type] [int] NOT NULL CONSTRAINT DF_Artifact_Type DEFAULT 0,
    [Length] [bigint] NOT NULL CONSTRAINT DF_Artifact_Length DEFAULT 0,
    [Created] [datetime] NOT NULL CONSTRAINT DF_Artifact_Created DEFAULT getdate(),
    [CreatedByUserId] [bigint] NOT NULL,
    CONSTRAINT PK_ARTIFACT_ID PRIMARY KEY (Id)	
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key of artifact record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Artifact', @level2type=N'COLUMN',@level2name=N'Id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to owning metadocument' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Artifact', @level2type=N'COLUMN',@level2name=N'MetadocumentId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Artifact file name without extension' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Artifact', @level2type=N'COLUMN',@level2name=N'Name'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Artifact file  extension' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Artifact', @level2type=N'COLUMN',@level2name=N'Extension'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to MIME Type enumeration' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Artifact', @level2type=N'COLUMN',@level2name=N'MimeTypeId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Defines type of artifact. 0 - Input, 1 - Output' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Artifact', @level2type=N'COLUMN',@level2name=N'Type'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Defines file content length in bytes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Artifact', @level2type=N'COLUMN',@level2name=N'Length'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when artifact was created (uploaded)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Artifact', @level2type=N'COLUMN',@level2name=N'Created'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who created (uploaded) artifact' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Artifact', @level2type=N'COLUMN',@level2name=N'CreatedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores single part of metadocument (called artifact). Refresents fingle physical file included for processing.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Artifact'
GO


-- Table MimeType --------------------------
--MIME Types global enumeration. Defines file extension and MIME type association, This table is managed only with administrator.
CREATE TABLE [dbo].[MimeType]
(
    [Id] [bigint] NOT NULL IDENTITY (1, 1),
    [MIMEType] [nvarchar] (500) NOT NULL,
    [Extensions] [nvarchar] (500) NOT NULL,
    [Description] [nvarchar] (200) NOT NULL,
    [Created] [datetime] NOT NULL CONSTRAINT DF_MimeType_Created DEFAULT getdate(),
    [Modified] [datetime] NULL,
    [CreatedByUserId] [bigint] NOT NULL,
    [ModifiedByUserId] [bigint] NULL,
    [IsEnabled] [bit] NOT NULL CONSTRAINT DF_MimeType_IsEnabled DEFAULT 0,
    CONSTRAINT PK_MIMETYPE_ID PRIMARY KEY (Id)	
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key of MIMEType record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MimeType', @level2type=N'COLUMN',@level2name=N'Id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'MIMEType code' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MimeType', @level2type=N'COLUMN',@level2name=N'MIMEType'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Extensions for mime type separated by semicolomn (example cad; cat; txt)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MimeType', @level2type=N'COLUMN',@level2name=N'Extensions'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Short description of MIMEType' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MimeType', @level2type=N'COLUMN',@level2name=N'Description'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when metadocument was created (uploaded)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MimeType', @level2type=N'COLUMN',@level2name=N'Created'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when metadocument was modified' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MimeType', @level2type=N'COLUMN',@level2name=N'Modified'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who created (uploaded) metadocument' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MimeType', @level2type=N'COLUMN',@level2name=N'CreatedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who updated metadocument' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MimeType', @level2type=N'COLUMN',@level2name=N'ModifiedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Flag if record is active, otherwise shuld be delete' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MimeType', @level2type=N'COLUMN',@level2name=N'IsEnabled'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'MIME Types global enumeration. Defines file extension and MIME type association, This table is managed only with administrator.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MimeType'
GO


-- Table Tag --------------------------
--Declares tag for workspace scope. Tag is used on metadocument.
CREATE TABLE [dbo].[Tag]
(
    [Id] [bigint] NOT NULL IDENTITY (1, 1),
    [WorkspaceId] [bigint] NOT NULL,
    [Name] [nvarchar] (50) NOT NULL,
    [RGB] [varchar] (50) NOT NULL,
    [Created] [datetime] NOT NULL CONSTRAINT DF_Tag_Created DEFAULT getdate(),
    [Modified] [datetime] NULL,
    [CreatedByUserId] [bigint] NOT NULL,
    [ModifiedByUserId] [bigint] NULL,
    CONSTRAINT PK_TAG_ID PRIMARY KEY (Id)	
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key of tag record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tag', @level2type=N'COLUMN',@level2name=N'Id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to owning workspace' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tag', @level2type=N'COLUMN',@level2name=N'WorkspaceId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Tag name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tag', @level2type=N'COLUMN',@level2name=N'Name'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Tag color in RGB format' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tag', @level2type=N'COLUMN',@level2name=N'RGB'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when billing info was created' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tag', @level2type=N'COLUMN',@level2name=N'Created'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when billing info was modified' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tag', @level2type=N'COLUMN',@level2name=N'Modified'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who created billing info' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tag', @level2type=N'COLUMN',@level2name=N'CreatedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who updated billing info' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tag', @level2type=N'COLUMN',@level2name=N'ModifiedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Declares tag for workspace scope. Tag is used on metadocument.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Tag'
GO


-- Table MetadocumentTag --------------------------
--Association of metadocument and tag
CREATE TABLE [dbo].[MetadocumentTag]
(
    [MetadocumentId] [bigint] NOT NULL,
    [TagId] [bigint] NOT NULL
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to owning metadocument' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MetadocumentTag', @level2type=N'COLUMN',@level2name=N'MetadocumentId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to Tag' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MetadocumentTag', @level2type=N'COLUMN',@level2name=N'TagId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Association of metadocument and tag' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MetadocumentTag'
GO


-- Table BillingPlan --------------------------
--Defines global Billing plan strategies (how to bill document processing in workspace). This table is managed only with administrator.
CREATE TABLE [dbo].[BillingPlan]
(
    [Id] [bigint] NOT NULL IDENTITY (1, 1),
    [Name] [nvarchar] (200) NOT NULL,
    [Code] [nvarchar] (10) NOT NULL,
    [Description] [nvarchar] (200) NOT NULL,
    [PSTInitialCredits] [int] NOT NULL,
    [PSTCreditsPerItem] [int] NOT NULL,
    [PSTWatermark] [bit] NOT NULL CONSTRAINT DF_BillingPlan_PSTWatermark DEFAULT 0,
    [PSTQuality] [int] NOT NULL CONSTRAINT DF_BillingPlan_PSTQuality DEFAULT 0,
    [PSTProcessProfile] [varchar] (5) NULL,
    [PSTTest] [bit] NOT NULL CONSTRAINT DF_BillingPlan_PSTTest DEFAULT 0,
    [Price] [decimal] NOT NULL CONSTRAINT DF_BillingPlan_Price DEFAULT 0,
    [Currency] [varchar] (3) NOT NULL,
    [Created] [datetime] NOT NULL CONSTRAINT DF_BillingPlan_Created DEFAULT getdate(),
    [Modified] [datetime] NULL,
    [CreatedByUserId] [bigint] NOT NULL,
    [ModifiedByUserId] [bigint] NULL,
    [IsCustom] [bit] NOT NULL CONSTRAINT DF_BillingPlan_IsCustom DEFAULT 0,
    [IsHidden] [bit] NOT NULL CONSTRAINT DF_BillingPlan_IsHidden DEFAULT 0,
    [IsEnabled] [bit] NOT NULL CONSTRAINT DF_BillingPlan_IsEnabled DEFAULT 0,
    CONSTRAINT PK_BILLINGPLAN_ID PRIMARY KEY (Id)	
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key of BillingPlan record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingPlan', @level2type=N'COLUMN',@level2name=N'Id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing plan name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingPlan', @level2type=N'COLUMN',@level2name=N'Name'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing plan short code' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingPlan', @level2type=N'COLUMN',@level2name=N'Code'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing plan short description' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingPlan', @level2type=N'COLUMN',@level2name=N'Description'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Preset - initial credit amount' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingPlan', @level2type=N'COLUMN',@level2name=N'PSTInitialCredits'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Preset - single item credit cost' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingPlan', @level2type=N'COLUMN',@level2name=N'PSTCreditsPerItem'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Preset - watermarked' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingPlan', @level2type=N'COLUMN',@level2name=N'PSTWatermark'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Preset - process quality 0 = lowest' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingPlan', @level2type=N'COLUMN',@level2name=N'PSTQuality'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Preset - processing profile code' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingPlan', @level2type=N'COLUMN',@level2name=N'PSTProcessProfile'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Preset - testing mode' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingPlan', @level2type=N'COLUMN',@level2name=N'PSTTest'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing plan price' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingPlan', @level2type=N'COLUMN',@level2name=N'Price'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing plan price currency' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingPlan', @level2type=N'COLUMN',@level2name=N'Currency'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when metadocument was created (uploaded)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingPlan', @level2type=N'COLUMN',@level2name=N'Created'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when metadocument was modified' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingPlan', @level2type=N'COLUMN',@level2name=N'Modified'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who created (uploaded) metadocument' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingPlan', @level2type=N'COLUMN',@level2name=N'CreatedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who updated metadocument' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingPlan', @level2type=N'COLUMN',@level2name=N'ModifiedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Flag if billing plan is custom dependent' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingPlan', @level2type=N'COLUMN',@level2name=N'IsCustom'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Flag if billing plan is private for use, only SuperAdmin can use it' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingPlan', @level2type=N'COLUMN',@level2name=N'IsHidden'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Flag if record is active, otherwise shuld be delete' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingPlan', @level2type=N'COLUMN',@level2name=N'IsEnabled'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Defines global Billing plan strategies (how to bill document processing in workspace). This table is managed only with administrator.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'BillingPlan'
GO


-- Table MetadocumentBilling --------------------------
--Association of metadocument and billing plan
CREATE TABLE [dbo].[MetadocumentBilling]
(
    [MetadocumentId] [bigint] NOT NULL,
    [BillingPlanId] [bigint] NOT NULL,
    [CreditCost] [int] NOT NULL CONSTRAINT DF_MetadocumentBilling_CreditCost DEFAULT 0,
    [Watermark] [bit] NOT NULL CONSTRAINT DF_MetadocumentBilling_Watermark DEFAULT 0,
    [Quality] [int] NOT NULL CONSTRAINT DF_MetadocumentBilling_Quality DEFAULT 0,
    [ProcessProfile] [varchar] (5) NULL,
    [Test] [bit] NOT NULL CONSTRAINT DF_MetadocumentBilling_Test DEFAULT 0,
    [Created] [datetime] NOT NULL CONSTRAINT DF_MetadocumentBilling_Created DEFAULT getdate(),
    [Modified] [datetime] NULL
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to owning metadocument' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MetadocumentBilling', @level2type=N'COLUMN',@level2name=N'MetadocumentId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to billing plan' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MetadocumentBilling', @level2type=N'COLUMN',@level2name=N'BillingPlanId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Numeric value of billing plan spending. Depends on BillingPlan.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MetadocumentBilling', @level2type=N'COLUMN',@level2name=N'CreditCost'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Flag if watermarked' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MetadocumentBilling', @level2type=N'COLUMN',@level2name=N'Watermark'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Process quality 0 = lowest' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MetadocumentBilling', @level2type=N'COLUMN',@level2name=N'Quality'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Processing profile code' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MetadocumentBilling', @level2type=N'COLUMN',@level2name=N'ProcessProfile'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Flag if testing mode' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MetadocumentBilling', @level2type=N'COLUMN',@level2name=N'Test'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when billing was created' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MetadocumentBilling', @level2type=N'COLUMN',@level2name=N'Created'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when billing was modified' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MetadocumentBilling', @level2type=N'COLUMN',@level2name=N'Modified'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Association of metadocument and billing plan' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'MetadocumentBilling'
GO


-- Table WorkspaceBilling --------------------------
--Deffines current state of billing plan for workspace
CREATE TABLE [dbo].[WorkspaceBilling]
(
    [WorkspaceId] [bigint] NOT NULL,
    [BillingPlanId] [bigint] NOT NULL,
    [Created] [datetime] NOT NULL CONSTRAINT DF_WorkspaceBilling_Created DEFAULT getdate(),
    [Modified] [datetime] NULL,
    [ValueNumber] [decimal] (16,4) NOT NULL CONSTRAINT DF_WorkspaceBilling_ValueNumber DEFAULT 0,
    [ValueDate] [datetime] NULL,
    [CustomPSTInitialCredits] [int] NOT NULL,
    [CustomPSTCreditsPerItem] [int] NOT NULL,
    [CustomPSTWatermark] [bit] NOT NULL CONSTRAINT DF_WorkspaceBilling_CustomPSTWatermark DEFAULT 0,
    [CustomPSTQuality] [int] NOT NULL CONSTRAINT DF_WorkspaceBilling_CustomPSTQuality DEFAULT 0,
    [CustomPSTProcessProfile] [varchar] (5) NULL,
    [CustomPSTTest] [bit] NOT NULL CONSTRAINT DF_WorkspaceBilling_CustomPSTTest DEFAULT 0
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to owning workspace' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WorkspaceBilling', @level2type=N'COLUMN',@level2name=N'WorkspaceId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to billing plan' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WorkspaceBilling', @level2type=N'COLUMN',@level2name=N'BillingPlanId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when billing was created' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WorkspaceBilling', @level2type=N'COLUMN',@level2name=N'Created'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when billing was modified' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WorkspaceBilling', @level2type=N'COLUMN',@level2name=N'Modified'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Numeric value of billing plan spending. Depends on BillingPlan.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WorkspaceBilling', @level2type=N'COLUMN',@level2name=N'ValueNumber'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'DateTime value of billing plan spending. Depends on BillingPlan' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WorkspaceBilling', @level2type=N'COLUMN',@level2name=N'ValueDate'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Custom Preset Override - initial credit amount' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WorkspaceBilling', @level2type=N'COLUMN',@level2name=N'CustomPSTInitialCredits'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Custom Preset Override - single item credit cost' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WorkspaceBilling', @level2type=N'COLUMN',@level2name=N'CustomPSTCreditsPerItem'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Custom Preset Override - watermarked' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WorkspaceBilling', @level2type=N'COLUMN',@level2name=N'CustomPSTWatermark'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Custom Preset Override - process quality 0 = lowest' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WorkspaceBilling', @level2type=N'COLUMN',@level2name=N'CustomPSTQuality'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Custom Preset Override - processing profile code' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WorkspaceBilling', @level2type=N'COLUMN',@level2name=N'CustomPSTProcessProfile'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Custom Preset Override - testing mode' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WorkspaceBilling', @level2type=N'COLUMN',@level2name=N'CustomPSTTest'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Deffines current state of billing plan for workspace' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WorkspaceBilling'
GO


-- Table Invoice --------------------------
--Stores invoice information of Workspace BusinessPlan 
CREATE TABLE [dbo].[Invoice]
(
    [Id] [bigint] NOT NULL IDENTITY (1, 1),
    [WorkspaceId] [bigint] NOT NULL,
    [InvoiceIssuerId] [bigint] NOT NULL,
    [Code] [varchar] (12) NOT NULL,
    [SubjectName] [nvarchar] (200) NOT NULL,
    [Address1] [nvarchar] (200) NOT NULL,
    [Address2] [nvarchar] (200) NULL,
    [City] [nvarchar] (100) NOT NULL,
    [ZIP] [nvarchar] (20) NOT NULL,
    [Country] [nvarchar] (50) NOT NULL,
    [TaxNumber] [nvarchar] (20) NOT NULL,
    [VATNumber] [nvarchar] (20) NULL,
    [Phone] [nvarchar] (20) NULL,
    [Email] [nvarchar] (20) NOT NULL,
    [TotalBasePrice] [decimal] (16,4) NOT NULL CONSTRAINT DF_Invoice_TotalBasePrice DEFAULT 0,
    [TotalBaseCurrency] [varchar] (3) NOT NULL,
    [Channel] [nvarchar] (20) NOT NULL,
    [ChannelReference] [nvarchar] (50) NOT NULL,
    [DueDate] [datetime] NOT NULL,
    [TaxDate] [datetime] NOT NULL,
    [Created] [datetime] NOT NULL CONSTRAINT DF_Invoice_Created DEFAULT getdate(),
    [Modified] [datetime] NULL,
    [CreatedByUserId] [bigint] NOT NULL,
    [ModifiedByUserId] [bigint] NULL,
    CONSTRAINT PK_INVOICE_ID PRIMARY KEY (Id)	
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key of invoice record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'Id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to owning workspace' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'WorkspaceId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to invoice issuer record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'InvoiceIssuerId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Invoice number' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'Code'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing Subject name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'SubjectName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing Address line 1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'Address1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing Address line 2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'Address2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing Address city' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'City'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing Address ZIP' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'ZIP'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing Address country' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'Country'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing Tax number' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'TaxNumber'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing VAT number' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'VATNumber'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing phone with format +XXXXXXXXX' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'Phone'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing email' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'Email'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Total base price (w/o VAT)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'TotalBasePrice'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Total base price currency (w/o VAT)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'TotalBaseCurrency'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Pay channel' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'Channel'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Pay channel reference (account number)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'ChannelReference'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Due date' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'DueDate'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Tax date' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'TaxDate'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when billing info was created' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'Created'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when billing info was modified' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'Modified'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who created billing info' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'CreatedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who updated billing info' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice', @level2type=N'COLUMN',@level2name=N'ModifiedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores invoice information of Workspace BusinessPlan ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Invoice'
GO


-- Table InvoiceItem --------------------------
CREATE TABLE [dbo].[InvoiceItem]
(
    [Id] [bigint] NOT NULL IDENTITY (1, 1),
    [InvoiceId] [bigint] NOT NULL,
    [BillingPlanId] [bigint] NOT NULL,
    [BillingPlanCode] [nvarchar] (10) NOT NULL,
    [UnitQuantity] [int] NOT NULL CONSTRAINT DF_InvoiceItem_UnitQuantity DEFAULT 1,
    [UnitBasePrice] [decimal] (16,4) NOT NULL CONSTRAINT DF_InvoiceItem_UnitBasePrice DEFAULT 0,
    [UnitBaseCurrency] [varchar] (3) NOT NULL,
    [Created] [datetime] NOT NULL CONSTRAINT DF_InvoiceItem_Created DEFAULT getdate(),
    [Modified] [datetime] NULL,
    [CreatedByUserId] [bigint] NOT NULL,
    [ModifiedByUserId] [bigint] NULL,
    CONSTRAINT PK_INVOICE_ITEM PRIMARY KEY (Id)	
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key of invoice item record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceItem', @level2type=N'COLUMN',@level2name=N'Id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to owning invoice' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceItem', @level2type=N'COLUMN',@level2name=N'InvoiceId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to billing plan' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceItem', @level2type=N'COLUMN',@level2name=N'BillingPlanId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing plan short code' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceItem', @level2type=N'COLUMN',@level2name=N'BillingPlanCode'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unit quantity' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceItem', @level2type=N'COLUMN',@level2name=N'UnitQuantity'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unit base price (w/o VAT)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceItem', @level2type=N'COLUMN',@level2name=N'UnitBasePrice'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unit base price currency (w/o VAT)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceItem', @level2type=N'COLUMN',@level2name=N'UnitBaseCurrency'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when billing info was created' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceItem', @level2type=N'COLUMN',@level2name=N'Created'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when billing info was modified' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceItem', @level2type=N'COLUMN',@level2name=N'Modified'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who created billing info' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceItem', @level2type=N'COLUMN',@level2name=N'CreatedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who updated billing info' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceItem', @level2type=N'COLUMN',@level2name=N'ModifiedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Invoice item' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceItem'
GO


-- Table InvoiceIssuer --------------------------
--Defines information about Invoice Issuer and its contacts information (used in invoice header)
CREATE TABLE [dbo].[InvoiceIssuer]
(
    [Id] [bigint] NOT NULL IDENTITY (1, 1),
    [SubjectName] [nvarchar] (200) NOT NULL,
    [Address1] [nvarchar] (200) NOT NULL,
    [Address2] [nvarchar] (200) NULL,
    [City] [nvarchar] (100) NOT NULL,
    [ZIP] [nvarchar] (20) NOT NULL,
    [Country] [nvarchar] (50) NOT NULL,
    [TaxNumber] [nvarchar] (20) NOT NULL,
    [VATNumber] [nvarchar] (20) NULL,
    [Phone] [nvarchar] (20) NULL,
    [Email] [nvarchar] (20) NOT NULL,
    [Created] [datetime] NOT NULL CONSTRAINT DF_InvoiceIssuer_Created DEFAULT getdate(),
    [Modified] [datetime] NULL,
    [CreatedByUserId] [bigint] NOT NULL,
    [ModifiedByUserId] [bigint] NULL,
    [IsEnabled] [bit] NOT NULL CONSTRAINT DF_InvoiceIssuer_IsEnabled DEFAULT 0,
    CONSTRAINT PK_INVOICE_ISSUER PRIMARY KEY (Id)	
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key of invoice issuer record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceIssuer', @level2type=N'COLUMN',@level2name=N'Id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing Subject name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceIssuer', @level2type=N'COLUMN',@level2name=N'SubjectName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing Address line 1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceIssuer', @level2type=N'COLUMN',@level2name=N'Address1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing Address line 2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceIssuer', @level2type=N'COLUMN',@level2name=N'Address2'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing Address city' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceIssuer', @level2type=N'COLUMN',@level2name=N'City'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing Address ZIP' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceIssuer', @level2type=N'COLUMN',@level2name=N'ZIP'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing Address country' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceIssuer', @level2type=N'COLUMN',@level2name=N'Country'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing Tax number' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceIssuer', @level2type=N'COLUMN',@level2name=N'TaxNumber'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing VAT number' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceIssuer', @level2type=N'COLUMN',@level2name=N'VATNumber'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing phone with format +XXXXXXXXX' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceIssuer', @level2type=N'COLUMN',@level2name=N'Phone'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Billing email' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceIssuer', @level2type=N'COLUMN',@level2name=N'Email'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when billing info was created' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceIssuer', @level2type=N'COLUMN',@level2name=N'Created'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when billing info was modified' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceIssuer', @level2type=N'COLUMN',@level2name=N'Modified'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who created billing info' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceIssuer', @level2type=N'COLUMN',@level2name=N'CreatedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Reference to user who updated billing info' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceIssuer', @level2type=N'COLUMN',@level2name=N'ModifiedByUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Flag if record is enabled' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceIssuer', @level2type=N'COLUMN',@level2name=N'IsEnabled'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Defines information about Invoice Issuer and its contacts information (used in invoice header)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'InvoiceIssuer'
GO


-- Table Statistic --------------------------
CREATE TABLE [dbo].[Statistic]
(
    [Id] [bigint] NOT NULL IDENTITY (1, 1),
    [Year] [int] NOT NULL,
    [Month] [int] NOT NULL,
    [Day] [int] NOT NULL,
    [UserNewCnt] [int] NOT NULL,
    [UserLoginCnt] [int] NOT NULL,
    [UserLoginB2BCnt] [int] NOT NULL,
    [UserTotalCnt] [int] NOT NULL,
    [WorkspaceNewCnt] [int] NOT NULL,
    [WorkspaceTotalCnt] [int] NOT NULL,
    [InvoiceNewCnt] [int] NOT NULL,
    [DocumentNewCnt] [int] NOT NULL,
    [DocumentFailedCnt] [int] NOT NULL,
    [DocumentDownloadedCnt] [int] NOT NULL,
    [DocumentTotalCnt] [int] NOT NULL,
    [DocumentProcessedCnt] [int] NOT NULL,
    [PSActiveCnt] [int] NOT NULL,
    [YieldWorthSum] [decimal] (16,4) NOT NULL,
    [YieldWorthCurrency] [varchar] (3) NOT NULL,
    [YieldWorthTotalSum] [decimal] (16,4) NOT NULL,
    [YieldWorthTotalCurrency] [varchar] (3) NOT NULL,
    [Created] [datetime] NOT NULL CONSTRAINT DF_Statistic_Created DEFAULT getdate(),
    [Modified] [datetime] NULL,
    CONSTRAINT PK_STA_ID PRIMARY KEY (Id)	
)
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Primary key of Statistic record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'Id'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Year number' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'Year'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Month number' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'Month'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Day number' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'Day'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'# of new created users' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'UserNewCnt'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'# of logged users (incl B2B)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'UserLoginCnt'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'# of logged B2B users' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'UserLoginB2BCnt'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'# of all users (incl system)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'UserTotalCnt'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'# of new created workspaces' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'WorkspaceNewCnt'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'# of all workspaces' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'WorkspaceTotalCnt'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'# of new created invoices' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'InvoiceNewCnt'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'# of new created/uploaded documents' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'DocumentNewCnt'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'# of failed documents' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'DocumentFailedCnt'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'# of downloaded documents' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'DocumentDownloadedCnt'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'# of all available documents' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'DocumentTotalCnt'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'# of processed documents' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'DocumentProcessedCnt'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'# of active ProcessServer instances' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'PSActiveCnt'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'sum of daily yield worth' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'YieldWorthSum'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'currency code of yieldworth' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'YieldWorthCurrency'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'sum of daily yield worth' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'YieldWorthTotalSum'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'currency code of yieldworth' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'YieldWorthTotalCurrency'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when billing info was created' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'Created'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Timestamp when billing info was modified' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic', @level2type=N'COLUMN',@level2name=N'Modified'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Holds statistics information on daily base' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Statistic'
GO



-- References for UserWorkspace --------------------------
ALTER TABLE [dbo].[UserWorkspace]
ADD 
    CONSTRAINT REL_UserWorkspace_User FOREIGN KEY (UserId) REFERENCES [dbo].[User] (Id), 
    CONSTRAINT REL_UserWorkspace_Workspace FOREIGN KEY (WorkspaceId) REFERENCES [dbo].[Workspace] (Id)
GO

-- References for Workspace --------------------------
ALTER TABLE [dbo].[Workspace]
ADD 
    CONSTRAINT REL_Workspace_User_Created FOREIGN KEY (CreatedByUserId) REFERENCES [dbo].[User] (Id), 
    CONSTRAINT REL_Workspace_User_Modified FOREIGN KEY (ModifiedByUserId) REFERENCES [dbo].[User] (Id), 
    CONSTRAINT REL_Workspace_BillingInfo FOREIGN KEY (BillingInfoId) REFERENCES [dbo].[BillingInfo] (Id)
GO

-- References for BillingInfo --------------------------
ALTER TABLE [dbo].[BillingInfo]
ADD 
    CONSTRAINT REL_BillingInfo_User_Created FOREIGN KEY (CreatedByUserId) REFERENCES [dbo].[User] (Id), 
    CONSTRAINT REL_BillingInfo_User_Modified FOREIGN KEY (ModifiedByUserId) REFERENCES [dbo].[User] (Id), 
    CONSTRAINT REL_BillingInfo_Workspace FOREIGN KEY (WorkspaceId) REFERENCES [dbo].[Workspace] (Id)
GO

-- References for Structure --------------------------
ALTER TABLE [dbo].[Structure]
ADD 
    CONSTRAINT REL_Structure_Workspace FOREIGN KEY (WorkspaceId) REFERENCES [dbo].[Workspace] (Id), 
    CONSTRAINT REL_Structure_Structure_Parent FOREIGN KEY (ParentId) REFERENCES [dbo].[Structure] (Id)
GO

-- References for Metadocument --------------------------
ALTER TABLE [dbo].[Metadocument]
ADD 
    CONSTRAINT REL_Metadocument_Structure FOREIGN KEY (StructureId) REFERENCES [dbo].[Structure] (Id), 
    CONSTRAINT REL_Metadocument_User_Created FOREIGN KEY (CreatedByUserId) REFERENCES [dbo].[User] (Id), 
    CONSTRAINT REL_Metadocument_User_Modified FOREIGN KEY (ModifiedByUserId) REFERENCES [dbo].[User] (Id), 
    CONSTRAINT REL_Metadocument_User_Downloaded FOREIGN KEY (LastDownloadedByUserId) REFERENCES [dbo].[User] (Id)
GO

-- References for StatusHistory --------------------------
ALTER TABLE [dbo].[StatusHistory]
ADD 
    CONSTRAINT REL_StatusHistory_User_Created FOREIGN KEY (CreatedByUserId) REFERENCES [dbo].[User] (Id), 
    CONSTRAINT REL_StatusHistory_Metadocument FOREIGN KEY (MetadocumentId) REFERENCES [dbo].[Metadocument] (Id)
GO

-- References for Artifact --------------------------
ALTER TABLE [dbo].[Artifact]
ADD 
    CONSTRAINT REL_Artifact_Metadocument FOREIGN KEY (MetadocumentId) REFERENCES [dbo].[Metadocument] (Id), 
    CONSTRAINT REL_Artifact_MimeType FOREIGN KEY (MimeTypeId) REFERENCES [dbo].[MimeType] (Id), 
    CONSTRAINT REL_Artifact_User_Created FOREIGN KEY (CreatedByUserId) REFERENCES [dbo].[User] (Id)
GO

-- References for MimeType --------------------------
ALTER TABLE [dbo].[MimeType]
ADD 
    CONSTRAINT REL_MimeType_User_Created FOREIGN KEY (CreatedByUserId) REFERENCES [dbo].[User] (Id), 
    CONSTRAINT REL_MimeType_User_Modified FOREIGN KEY (ModifiedByUserId) REFERENCES [dbo].[User] (Id)
GO

-- References for Tag --------------------------
ALTER TABLE [dbo].[Tag]
ADD 
    CONSTRAINT REL_Tag_Workspace FOREIGN KEY (WorkspaceId) REFERENCES [dbo].[Workspace] (Id), 
    CONSTRAINT REL_Tag_User_Created FOREIGN KEY (CreatedByUserId) REFERENCES [dbo].[User] (Id), 
    CONSTRAINT REL_Tag_User_Modified FOREIGN KEY (ModifiedByUserId) REFERENCES [dbo].[User] (Id)
GO

-- References for MetadocumentTag --------------------------
ALTER TABLE [dbo].[MetadocumentTag]
ADD 
    CONSTRAINT REL_MetadocumentTag_Metadocument FOREIGN KEY (MetadocumentId) REFERENCES [dbo].[Metadocument] (Id), 
    CONSTRAINT REL_MetadocumentTag_Tag FOREIGN KEY (TagId) REFERENCES [dbo].[Tag] (Id)
GO

-- References for BillingPlan --------------------------
ALTER TABLE [dbo].[BillingPlan]
ADD 
    CONSTRAINT REL_BillingPlan_User_Created FOREIGN KEY (CreatedByUserId) REFERENCES [dbo].[User] (Id), 
    CONSTRAINT REL_BillingPlan_User_Modified FOREIGN KEY (ModifiedByUserId) REFERENCES [dbo].[User] (Id)
GO

-- References for MetadocumentBilling --------------------------
ALTER TABLE [dbo].[MetadocumentBilling]
ADD 
    CONSTRAINT REL_MetadocumentBilling_Metadocument FOREIGN KEY (MetadocumentId) REFERENCES [dbo].[Metadocument] (Id), 
    CONSTRAINT REL_MetadocumentBilling_BillingPlan FOREIGN KEY (BillingPlanId) REFERENCES [dbo].[BillingPlan] (Id)
GO

-- References for WorkspaceBilling --------------------------
ALTER TABLE [dbo].[WorkspaceBilling]
ADD 
    CONSTRAINT REL_WorkspaceBilling_Workspace FOREIGN KEY (WorkspaceId) REFERENCES [dbo].[Workspace] (Id), 
    CONSTRAINT REL_WorkspaceBilling_BillingPlan FOREIGN KEY (BillingPlanId) REFERENCES [dbo].[BillingPlan] (Id)
GO

-- References for Invoice --------------------------
ALTER TABLE [dbo].[Invoice]
ADD 
    CONSTRAINT REL_Invoice_InvoiceIssuer FOREIGN KEY (InvoiceIssuerId) REFERENCES [dbo].[InvoiceIssuer] (Id), 
    CONSTRAINT REL_Invoice_Workspace FOREIGN KEY (WorkspaceId) REFERENCES [dbo].[Workspace] (Id), 
    CONSTRAINT REL_Invoice_User_Created FOREIGN KEY (CreatedByUserId) REFERENCES [dbo].[User] (Id), 
    CONSTRAINT REL_Invoice_User_Modified FOREIGN KEY (ModifiedByUserId) REFERENCES [dbo].[User] (Id)
GO

-- References for InvoiceItem --------------------------
ALTER TABLE [dbo].[InvoiceItem]
ADD 
    CONSTRAINT REL_InvoiceItem_Invoice FOREIGN KEY (InvoiceId) REFERENCES [dbo].[Invoice] (Id), 
    CONSTRAINT REL_InvoiceItem_BillingPlan FOREIGN KEY (BillingPlanId) REFERENCES [dbo].[BillingPlan] (Id), 
    CONSTRAINT REL_InvoiceItem_User_Created FOREIGN KEY (CreatedByUserId) REFERENCES [dbo].[User] (Id), 
    CONSTRAINT REL_InvoiceItem_User_Modified FOREIGN KEY (ModifiedByUserId) REFERENCES [dbo].[User] (Id)
GO

-- References for InvoiceIssuer --------------------------
ALTER TABLE [dbo].[InvoiceIssuer]
ADD 
    CONSTRAINT REL_InvoiceIssuer_User_Created FOREIGN KEY (CreatedByUserId) REFERENCES [dbo].[User] (Id), 
    CONSTRAINT REL_InvoiceIssuer_User_Modified FOREIGN KEY (ModifiedByUserId) REFERENCES [dbo].[User] (Id)
GO

EXEC sys.sp_updateextendedproperty @name=N'BO_Version', @value=N'1.0.6'
GO