/***************************************************************************
* Copyright (C) 2025 DataKings / Kers IT All rights reserved.
'***************************************************************************/

------------------------------------------------------------------------------------------------------------------------
-- Table: CSEasyWMDefs
------------------------------------------------------------------------------------------------------------------------
if OBJECT_ID('CSEasyWMDefs') is null
begin
	CREATE TABLE [dbo].[CSEasyWMDefs](
		[ID] [uniqueidentifier] NOT NULL default(newId()),
		[HID] [int] IDENTITY(1,1) NOT NULL,
		[Title] [nvarchar](60) NOT NULL,
		[Description] [nvarchar](max) NULL,
		[TemplateDocumentID] [uniqueidentifier] NULL,
		[Status] [nchar](1) NOT NULL default(N'D'),
		[NewPagePerRecord] [tinyint] not null default(1),
		[SkipAttachments] [tinyint] NOT NULL default(0),
		[SkipPreview] [tinyint] NOT NULL default(0),
		[SkipResult] [tinyint] NOT NULL default(0),
		[SQLQuerySelect] [nvarchar](max) NULL,
		[SQLQueryFrom] [nvarchar](max) NULL,
		[SQLQueryWhere] [nvarchar](max) NULL,
		[SQLQueryOrderBy] [nvarchar](max) NULL,
		[RunInBackgroundJob] [tinyint] NOT NULL default(0),
		[LastRun] [datetime] NULL,
		[Syscreated] [datetime] NOT NULL default(getDate()),
		[Syscreator] [int] NOT NULL default(0),
		[Sysmodified] [datetime] NOT NULL default(getDate()),
		[Sysmodifier] [int] NOT NULL default(0),
		[Sysguid] [uniqueidentifier] NOT NULL ROWGUIDCOL default(newId()),
		[Timestamp] [timestamp] NOT NULL,
	 CONSTRAINT [PK_CSEasyWMDef] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
end
GO

IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE name = 'CSEasyWMDefs' AND id = object_id('CSEasyWMDefs')) BEGIN
	CREATE UNIQUE INDEX [CSEasyWMDefs] ON [dbo].[CSEasyWMDefs]([Title])
END
GO

IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE name = 'CSEasyWMDefsHID' AND id = object_id('CSEasyWMDefs')) BEGIN
	CREATE UNIQUE INDEX [CSEasyWMDefsHID] ON [dbo].[CSEasyWMDefs]([HID])
END
GO

if not exists(select * from DDTests where TableName = 'CSEasyWMDefs' and FieldName = 'Status')
begin
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('D', 'CSEasyWMDefs', 'Status', 'D', 'Development', 15760, '', 0)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('T', 'CSEasyWMDefs', 'Status', 'T', 'Test mode', 421058, '', 1)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('R', 'CSEasyWMDefs', 'Status', 'R', 'Released', 23341, '', 2)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('A', 'CSEasyWMDefs', 'Status', 'A', 'Archived', 305, '', 3)
end
go

if OBJECT_ID('FK_CSEasyWMDefs_TemplateDocumentID') is null
begin
	alter table dbo.CSEasyWMDefs
		ADD CONSTRAINT FK_CSEasyWMDefs_TemplateDocumentID FOREIGN KEY ([TemplateDocumentID]) REFERENCES [BacoDiscussions]([ID]) on update cascade
end
go



------------------------------------------------------------------------------------------------------------------------
-- Table: CSEasyWMDefColumns
------------------------------------------------------------------------------------------------------------------------
if OBJECT_ID('CSEasyWMDefColumns') is null
begin
	CREATE TABLE [dbo].[CSEasyWMDefColumns](
		[ID] [uniqueidentifier] NOT NULL default(newId()),
		[DefID] [uniqueidentifier] NOT NULL,
		[Alias] [nvarchar](128) NOT NULL,
		[ColumnName] [nvarchar](max) NOT NULL,
		[DataType] [nvarchar](128) NOT NULL,
		[FormattingOptions] [nchar](1) NULL,
		[BinaryType] [nchar](1) NULL,
		[BinaryAction] [nchar](1) NULL,
		[PreventDoubleSpacing] [tinyint] NOT NULL default(0),
		[IntegerType] [nchar](1) NOT NULL default(N'V'),
		[Syscreated] [datetime] NOT NULL default(getDate()),
		[Syscreator] [int] NOT NULL default(0),
		[Sysmodified] [datetime] NOT NULL default(getDate()),
		[Sysmodifier] [int] NOT NULL default(0),
		[Sysguid] [uniqueidentifier] NOT NULL ROWGUIDCOL default(newId()),
		[Timestamp] [timestamp] NOT NULL,
	 CONSTRAINT [PK_CSEasyWMDefColumns] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
end
else
begin
	alter table CSEasyWMDefColumns
		alter column [ColumnName] [nvarchar](max) NOT NULL
end
GO

IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE name = 'CSEasyWMDefColumns' AND id = object_id('CSEasyWMDefColumns')) BEGIN
	CREATE UNIQUE INDEX [CSEasyWMDefColumns] ON [dbo].[CSEasyWMDefColumns]([DefID], [Alias])
END
GO

if not exists(select * from DDTests where TableName = 'CSEasyWMDefColumns' and FieldName = 'FormattingOptions')
begin
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('0', 'CSEasyWMDefColumns', 'FormattingOptions', '0', 'Uppercase', 35688, '', 0)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('1', 'CSEasyWMDefColumns', 'FormattingOptions', '1', 'Lowercase', 37422, '', 1)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('2', 'CSEasyWMDefColumns', 'FormattingOptions', '2', 'dd-mm-yyyy', 0, '', 2)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('3', 'CSEasyWMDefColumns', 'FormattingOptions', '3', 'dd-mm-yyyy HH:mm', 0, '', 2)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('4', 'CSEasyWMDefColumns', 'FormattingOptions', '4', 'dd mmmm yyyy', 0, '', 4)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('5', 'CSEasyWMDefColumns', 'FormattingOptions', '5', '0,00', 0, '', 5)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('6', 'CSEasyWMDefColumns', 'FormattingOptions', '6', '0.000,00', 0, '', 6)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('7', 'CSEasyWMDefColumns', 'FormattingOptions', '7', '0', 0, '', 7)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('8', 'CSEasyWMDefColumns', 'FormattingOptions', '8', '0.000', 0, '', 8)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('9', 'CSEasyWMDefColumns', 'FormattingOptions', '9', 'HH:mm', 0, '', 9)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('A', 'CSEasyWMDefColumns', 'FormattingOptions', 'A', '00.000.000', 0, '', 10)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('B', 'CSEasyWMDefColumns', 'FormattingOptions', 'B', '0 or 1', 421066, '', 11)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('C', 'CSEasyWMDefColumns', 'FormattingOptions', 'C', 'Yes or No', 421067, '', 12)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('D', 'CSEasyWMDefColumns', 'FormattingOptions', 'D', 'yes or no', 421068, '', 13)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('E', 'CSEasyWMDefColumns', 'FormattingOptions', 'E', 'First character uppercase', 421205, '', 14)
	-- keep same as the formatting options of CSEasyWMDefListColumns
end
go

if not exists(select * from DDTests where TableName = 'CSEasyWMDefColumns' and FieldName = 'BinaryType')
begin
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('P', 'CSEasyWMDefColumns', 'BinaryType', 'P', 'Picture', 220, '', 0)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('D', 'CSEasyWMDefColumns', 'BinaryType', 'D', 'Document', 7226, '', 1)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('M', 'CSEasyWMDefColumns', 'BinaryType', 'M', 'Document (Mergeable attachment)', 421264, '', 2)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('F', 'CSEasyWMDefColumns', 'BinaryType', 'F', 'Document (File attachment)', 421265, '', 3)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('E', 'CSEasyWMDefColumns', 'BinaryType', 'E', 'Document (Existing document)', 421275, '', 5)
end
go

if not exists(select * from DDTests where TableName = 'CSEasyWMDefColumns' and FieldName = 'BinaryAction')
begin
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('I', 'CSEasyWMDefColumns', 'BinaryAction', 'I', 'Insert document', 421203, '', 0)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('M', 'CSEasyWMDefColumns', 'BinaryAction', 'M', 'Mergeable attachment', 421196, '', 1)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('F', 'CSEasyWMDefColumns', 'BinaryAction', 'F', 'File attachment', 421197, '', 2)
end
go

if not exists(select * from DDTests where TableName = 'CSEasyWMDefColumns' and FieldName = 'IntegerType')
begin
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('V', 'CSEasyWMDefColumns', 'IntegerType', 'V', 'Use value', 421277, '', 0)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('D', 'CSEasyWMDefColumns', 'IntegerType', 'D', 'Document action', 421278, '', 1)
end
go


if OBJECT_ID('FK_CSEasyWMDefColumns_DefID') is null
begin
	alter table dbo.CSEasyWMDefColumns
		ADD CONSTRAINT FK_CSEasyWMDefColumns_DefID FOREIGN KEY ([DefID]) REFERENCES [CSEasyWMDefs]([ID])
end
go




------------------------------------------------------------------------------------------------------------------------
-- Table: CSEasyWMDefSecRoles
------------------------------------------------------------------------------------------------------------------------
if OBJECT_ID('CSEasyWMDefSecRoles') is null
begin
	CREATE TABLE [dbo].[CSEasyWMDefSecRoles](
		[ID] [uniqueidentifier] NOT NULL default(newId()),
		[DefID] [uniqueidentifier] NOT NULL,
		[RoleID] [int] NOT NULL,
		[Syscreated] [datetime] NOT NULL default(getDate()),
		[Syscreator] [int] NOT NULL default(0),
		[Sysmodified] [datetime] NOT NULL default(getDate()),
		[Sysmodifier] [int] NOT NULL default(0),
		[Sysguid] [uniqueidentifier] NOT NULL ROWGUIDCOL default(newId()),
		[Timestamp] [timestamp] NOT NULL,
	 CONSTRAINT [PK_CSEasyWMDefSecRoles] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
end
GO

IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE name = 'CSEasyWMDefSecRoles' AND id = object_id('CSEasyWMDefSecRoles')) BEGIN
	CREATE UNIQUE INDEX [CSEasyWMDefSecRoles] ON [dbo].[CSEasyWMDefSecRoles]([DefID], [RoleID])
END
GO


if OBJECT_ID('FK_CSEasyWMDefSecRoles_DefID') is null
begin
	alter table dbo.CSEasyWMDefSecRoles
		ADD CONSTRAINT FK_CSEasyWMDefSecRoles_DefID FOREIGN KEY ([DefID]) REFERENCES [CSEasyWMDefs]([ID])
end
go
if OBJECT_ID('FK_CSEasyWMDefSecRoles_RoleID') is null
begin
	alter table dbo.CSEasyWMDefSecRoles
		ADD CONSTRAINT FK_CSEasyWMDefSecRoles_RoleID FOREIGN KEY ([RoleID]) REFERENCES [HRRoleDefs]([ID]) on update cascade
end
go


------------------------------------------------------------------------------------------------------------------------
-- Table: CSEasyWMDefSecFuncs
------------------------------------------------------------------------------------------------------------------------
if OBJECT_ID('CSEasyWMDefSecFuncs') is null
begin
	CREATE TABLE [dbo].[CSEasyWMDefSecFuncs](
		[ID] [uniqueidentifier] NOT NULL default(newId()),
		[DefID] [uniqueidentifier] NOT NULL,
		[FunctionID] [int] NOT NULL,
		[Syscreated] [datetime] NOT NULL default(getDate()),
		[Syscreator] [int] NOT NULL default(0),
		[Sysmodified] [datetime] NOT NULL default(getDate()),
		[Sysmodifier] [int] NOT NULL default(0),
		[Sysguid] [uniqueidentifier] NOT NULL ROWGUIDCOL default(newId()),
		[Timestamp] [timestamp] NOT NULL,
	 CONSTRAINT [PK_CSEasyWMDefSecFuncs] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
end
GO

IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE name = 'CSEasyWMDefSecFuncs' AND id = object_id('CSEasyWMDefSecFuncs')) BEGIN
	CREATE UNIQUE INDEX [CSEasyWMDefSecFuncs] ON [dbo].[CSEasyWMDefSecFuncs]([DefID], [FunctionID])
END
GO

if OBJECT_ID('FK_CSEasyWMDefSecFuncs_DefID') is null
begin
	alter table dbo.CSEasyWMDefSecFuncs
		ADD CONSTRAINT FK_CSEasyWMDefSecFuncs_DefID FOREIGN KEY ([DefID]) REFERENCES [CSEasyWMDefs]([ID])
end
go
if OBJECT_ID('FK_CSEasyWMDefSecFuncs_FunctionID') is null
begin
	alter table dbo.CSEasyWMDefSecFuncs
		ADD CONSTRAINT FK_CSEasyWMDefSecFuncs_FunctionID FOREIGN KEY ([FunctionID]) REFERENCES [BacoFunctions]([ID]) on update cascade
end
go





------------------------------------------------------------------------------------------------------------------------
-- Table: CSEasyWMDefFormats
------------------------------------------------------------------------------------------------------------------------
if OBJECT_ID('CSEasyWMDefFormats') is null
begin
	CREATE TABLE [dbo].[CSEasyWMDefFormats](
		[ID] [uniqueidentifier] NOT NULL default(newId()),
		[DefID] [uniqueidentifier] NOT NULL,
		[Format] [nchar](1) NOT NULL default(N'0'),
		[IsDefault] [tinyint] NOT NULL default(0),
		[Version] [nvarchar](20) NULL,
		[SaveDocument] [tinyint] NOT NULL default(1),
		[DocumentName] [nvarchar](128) NULL,
		[DocumentSubject] [nvarchar](256) NULL,
		[DocumentType] [int] NULL,
		[ProjectNr] [uniqueidentifier] NULL,
		[Assortment] [uniqueidentifier] NULL,
		[ItemCode] [uniqueidentifier] NULL,
		[Associate] [uniqueidentifier] NULL,
		[Contact] [uniqueidentifier] NULL,
		[SecurityLevel] [uniqueidentifier] NULL,
		[Resource] [uniqueidentifier] NULL,
		[Opportunity] [uniqueidentifier] NULL,
		[DocumentTags] [nvarchar](max) NULL,
		[Owner] [int] NULL,
		[OwnerType] [smallint] NULL,
		[OwnerTypeValue] [nvarchar](50) NULL,
		[Syscreated] [datetime] NOT NULL default(getDate()),
		[Syscreator] [int] NOT NULL default(0),
		[Sysmodified] [datetime] NOT NULL default(getDate()),
		[Sysmodifier] [int] NOT NULL default(0),
		[Sysguid] [uniqueidentifier] NOT NULL ROWGUIDCOL default(newId()),
		[Timestamp] [timestamp] NOT NULL,
	 CONSTRAINT [PK_CSEasyWMDefFormats] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
end
GO

IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE name = 'CSEasyWMDefFormats' AND id = object_id('CSEasyWMDefFormats')) BEGIN
	CREATE UNIQUE INDEX [CSEasyWMDefFormats] ON [dbo].[CSEasyWMDefFormats]([DefID], [Format])
END
GO

if not exists(select * from DDTests where TableName = 'CSEasyWMDefFormats' and FieldName = 'Format')
begin
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('0', 'CSEasyWMDefFormats', 'Format', '0', 'Docx', 0, '', 0)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('1', 'CSEasyWMDefFormats', 'Format', '1', 'PDF', 0, '', 3)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('2', 'CSEasyWMDefFormats', 'Format', '2', 'Html', 0, '', 4)
end
go

if OBJECT_ID('FK_CSEasyWMDefFormats_DefID') is null
begin
	alter table dbo.CSEasyWMDefFormats
		ADD CONSTRAINT FK_CSEasyWMDefFormats_DefID FOREIGN KEY ([DefID]) REFERENCES [CSEasyWMDefs]([ID])
end
go
if OBJECT_ID('FK_CSEasyWMDefFormats_DocumentType') is null
begin
	alter table dbo.CSEasyWMDefFormats
		ADD CONSTRAINT FK_CSEasyWMDefFormats_DocumentType FOREIGN KEY ([DocumentType]) REFERENCES [BacoDiscussionFixedTypes]([TypeID])
end
go

if OBJECT_ID('FK_CSEasyWMDefFormats_ProjectNr') is null
begin
	alter table dbo.CSEasyWMDefFormats
		ADD CONSTRAINT FK_CSEasyWMDefFormats_ProjectNr FOREIGN KEY ([ProjectNr]) REFERENCES [CSEasyWMDefColumns]([ID])
end
go
if OBJECT_ID('FK_CSEasyWMDefFormats_Assortment') is null
begin
	alter table dbo.CSEasyWMDefFormats
		ADD CONSTRAINT FK_CSEasyWMDefFormats_Assortment FOREIGN KEY ([Assortment]) REFERENCES [CSEasyWMDefColumns]([ID])
end
go
if OBJECT_ID('FK_CSEasyWMDefFormats_ItemCode') is null
begin
	alter table dbo.CSEasyWMDefFormats
		ADD CONSTRAINT FK_CSEasyWMDefFormats_ItemCode FOREIGN KEY ([ItemCode]) REFERENCES [CSEasyWMDefColumns]([ID])
end
go
if OBJECT_ID('FK_CSEasyWMDefFormats_Associate') is null
begin
	alter table dbo.CSEasyWMDefFormats
		ADD CONSTRAINT FK_CSEasyWMDefFormats_Associate FOREIGN KEY ([Associate]) REFERENCES [CSEasyWMDefColumns]([ID])
end
go
if OBJECT_ID('FK_CSEasyWMDefFormats_Contact') is null
begin
	alter table dbo.CSEasyWMDefFormats
		ADD CONSTRAINT FK_CSEasyWMDefFormats_Contact FOREIGN KEY ([Contact]) REFERENCES [CSEasyWMDefColumns]([ID])
end
go
if OBJECT_ID('FK_CSEasyWMDefFormats_SecurityLevel') is null
begin
	alter table dbo.CSEasyWMDefFormats
		ADD CONSTRAINT FK_CSEasyWMDefFormats_SecurityLevel FOREIGN KEY ([SecurityLevel]) REFERENCES [CSEasyWMDefColumns]([ID])
end
go
if OBJECT_ID('FK_CSEasyWMDefFormats_Resource') is null
begin
	alter table dbo.CSEasyWMDefFormats
		ADD CONSTRAINT FK_CSEasyWMDefFormats_Resource FOREIGN KEY ([Resource]) REFERENCES [CSEasyWMDefColumns]([ID])
end
go

if OBJECT_ID('FK_CSEasyWMDefFormats_Opportunity') is null
begin
	alter table dbo.CSEasyWMDefFormats
		ADD CONSTRAINT FK_CSEasyWMDefFormats_Opportunity FOREIGN KEY ([Opportunity]) REFERENCES [CSEasyWMDefColumns]([ID])
end
go






------------------------------------------------------------------------------------------------------------------------
-- Table: CSEasyWMDefSelection
------------------------------------------------------------------------------------------------------------------------
if OBJECT_ID('CSEasyWMDefSelection') is null
begin
	CREATE TABLE [dbo].[CSEasyWMDefSelection](
		[ID] [uniqueidentifier] NOT NULL default(newId()),
		[DefID] [uniqueidentifier] NOT NULL,
		[DisplayOrder] [int] NOT NULL default(0),
		[Topic] [nchar](1) NOT NULL,
		[Caption] [nvarchar](60) NOT NULL,
		[CaptionTermID] [int] NOT NULL default(0),		
		[SelectionCriteria] [nvarchar](max) NULL,
		[SelectionValues] [nvarchar](max) NULL,
		[ColumnName] [nvarchar](128) NULL,
		[LinkToSelectionID] [uniqueidentifier] NULL,
		[Syscreated] [datetime] NOT NULL default(getDate()),
		[Syscreator] [int] NOT NULL default(0),
		[Sysmodified] [datetime] NOT NULL default(getDate()),
		[Sysmodifier] [int] NOT NULL default(0),
		[Sysguid] [uniqueidentifier] NOT NULL ROWGUIDCOL default(newId()),
		[Timestamp] [timestamp] NOT NULL,
	 CONSTRAINT [PK_CSEasyWMDefSelection] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
end
GO

IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE name = 'CSEasyWMDefSelection' AND id = object_id('CSEasyWMDefSelection')) BEGIN
	CREATE UNIQUE INDEX [CSEasyWMDefSelection] ON [dbo].[CSEasyWMDefSelection]([DefID], [ColumnName])
END
GO

if not exists(select * from DDTests where TableName = 'CSEasyWMDefSelection' and FieldName = 'Topic')
begin
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('0', 'CSEasyWMDefSelection', 'Topic', '0', 'Project', 8657, '', 0)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('1', 'CSEasyWMDefSelection', 'Topic', '1', 'Parent project', 13216, '', 1)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('2', 'CSEasyWMDefSelection', 'Topic', '2', 'Debtor', 3264, '', 2)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('3', 'CSEasyWMDefSelection', 'Topic', '3', 'Creditor', 8504, '', 3)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('4', 'CSEasyWMDefSelection', 'Topic', '4', 'Account', 8, '', 4)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('5', 'CSEasyWMDefSelection', 'Topic', '5', 'Contact person', 8484, '', 5)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('6', 'CSEasyWMDefSelection', 'Topic', '6', 'Assortment', 222, '', 6)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('7', 'CSEasyWMDefSelection', 'Topic', '7', 'Item', 8702, '', 7)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('8', 'CSEasyWMDefSelection', 'Topic', '8', 'Employee', 9551, '', 8)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('9', 'CSEasyWMDefSelection', 'Topic', '9', 'Opportunity', 34831, '', 9)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('A', 'CSEasyWMDefSelection', 'Topic', 'A', 'Request', 16, '', 10)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('B', 'CSEasyWMDefSelection', 'Topic', 'B', 'Date', 8516, '', 11)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('C', 'CSEasyWMDefSelection', 'Topic', 'C', 'Date range', 9027, '', 12)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('D', 'CSEasyWMDefSelection', 'Topic', 'D', 'Yes/no field', 20273, '', 13)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('E', 'CSEasyWMDefSelection', 'Topic', 'E', 'Text', 8893, '', 14)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('F', 'CSEasyWMDefSelection', 'Topic', 'F', 'Integer', 33006, '', 15)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('G', 'CSEasyWMDefSelection', 'Topic', 'G', 'Double', 36088, '', 16)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('H', 'CSEasyWMDefSelection', 'Topic', 'H', 'Quotation', 3819, '', 25)
end
go

if OBJECT_ID('FK_CSEasyWMDefSelection_DefID') is null
begin
	alter table dbo.CSEasyWMDefSelection
		ADD CONSTRAINT FK_CSEasyWMDefSelection_DefID FOREIGN KEY ([DefID]) REFERENCES [CSEasyWMDefs]([ID])
end
go
if OBJECT_ID('FK_CSEasyWMDefSelection_LinkToSelectionID') is null
begin
	alter table dbo.CSEasyWMDefSelection
		ADD CONSTRAINT FK_CSEasyWMDefSelection_LinkToSelectionID FOREIGN KEY ([LinkToSelectionID]) REFERENCES [CSEasyWMDefSelection]([ID])
end
go




------------------------------------------------------------------------------------------------------------------------
-- Table: CSEasyWMGroups
------------------------------------------------------------------------------------------------------------------------
if OBJECT_ID('CSEasyWMGroups') is null
begin
	CREATE TABLE [dbo].[CSEasyWMGroups](
		[ID] [uniqueidentifier] NOT NULL default(newId()),
		[HID] [int] IDENTITY(1,1) NOT NULL,
		[Title] [nvarchar](60) NOT NULL,
		[TitleTermID] [int] NOT NULL default(0),
		[Description] [nvarchar](max) NULL,
		[DisplayOrder] [int] NOT NULL default(0),
		[Syscreated] [datetime] NOT NULL default(getDate()),
		[Syscreator] [int] NOT NULL default(0),
		[Sysmodified] [datetime] NOT NULL default(getDate()),
		[Sysmodifier] [int] NOT NULL default(0),
		[Sysguid] [uniqueidentifier] NOT NULL ROWGUIDCOL default(newId()),
		[Timestamp] [timestamp] NOT NULL,
	 CONSTRAINT [PK_CSEasyWMGroups] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
end
GO

IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE name = 'CSEasyWMGroups' AND id = object_id('CSEasyWMGroups')) BEGIN
	CREATE UNIQUE INDEX [CSEasyWMGroups] ON [dbo].[CSEasyWMGroups]([Title])
END
GO

IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE name = 'CSEasyWMGroupsHID' AND id = object_id('CSEasyWMGroups')) BEGIN
	CREATE UNIQUE INDEX [CSEasyWMGroupsHID] ON [dbo].[CSEasyWMGroups]([HID])
END
GO


------------------------------------------------------------------------------------------------------------------------
-- Table: CSEasyWMGroupDefs
------------------------------------------------------------------------------------------------------------------------
if OBJECT_ID('CSEasyWMGroupDefs') is null
begin
	CREATE TABLE [dbo].[CSEasyWMGroupDefs](
		[ID] [uniqueidentifier] NOT NULL default(newId()),
		[GroupID] [uniqueidentifier] NOT NULL,
		[DefID] [uniqueidentifier] NOT NULL,
		[DisplayOrder] [int] NOT NULL default(0),
		[Syscreated] [datetime] NOT NULL default(getDate()),
		[Syscreator] [int] NOT NULL default(0),
		[Sysmodified] [datetime] NOT NULL default(getDate()),
		[Sysmodifier] [int] NOT NULL default(0),
		[Sysguid] [uniqueidentifier] NOT NULL ROWGUIDCOL default(newId()),
		[Timestamp] [timestamp] NOT NULL,
	 CONSTRAINT [PK_CSEasyWMGroupDefs] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
end
GO

IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE name = 'CSEasyWMGroupDefs' AND id = object_id('CSEasyWMGroupDefs')) BEGIN
	CREATE UNIQUE INDEX [CSEasyWMGroupDefs] ON [dbo].[CSEasyWMGroupDefs]([GroupID], [DefID])
END
GO


if OBJECT_ID('FK_CSEasyWMGroupDefs_GroupID') is null
begin
	alter table dbo.CSEasyWMGroupDefs
		ADD CONSTRAINT FK_CSEasyWMGroupDefs_GroupID FOREIGN KEY ([GroupID]) REFERENCES [CSEasyWMGroups]([ID]) on delete cascade
end
go
if OBJECT_ID('FK_CSEasyWMGroupDefs_DefID') is null
begin
	alter table dbo.CSEasyWMGroupDefs
		ADD CONSTRAINT FK_CSEasyWMGroupDefs_DefID FOREIGN KEY ([DefID]) REFERENCES [CSEasyWMDefs]([ID])
end
go




------------------------------------------------------------------------------------------------------------------------
-- Table: CSEasyWMDefLists
------------------------------------------------------------------------------------------------------------------------
if OBJECT_ID('CSEasyWMDefLists') is null
begin
	CREATE TABLE [dbo].[CSEasyWMDefLists](
		[ID] [uniqueidentifier] NOT NULL default(newId()),
		[DefID] [uniqueidentifier] NOT NULL,
		[Name] [nvarchar](20) NOT NULL,
		[Description] [nvarchar](max) NULL,
		[SQLQuerySelect] [nvarchar](max) NULL,
		[SQLQueryFrom] [nvarchar](max) NULL,
		[SQLQueryWhere] [nvarchar](max) NULL,
		[SQLQueryOrderBy] [nvarchar](max) NULL,
		[UseNoData] [tinyint] NOT NULL default(0),
		[Syscreated] [datetime] NOT NULL default(getDate()),
		[Syscreator] [int] NOT NULL default(0),
		[Sysmodified] [datetime] NOT NULL default(getDate()),
		[Sysmodifier] [int] NOT NULL default(0),
		[Sysguid] [uniqueidentifier] NOT NULL ROWGUIDCOL default(newId()),
		[Timestamp] [timestamp] NOT NULL,
	 CONSTRAINT [PK_CSEasyWMDefLists] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
end
GO

IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE name = 'CSEasyWMDefLists' AND id = object_id('CSEasyWMDefLists')) BEGIN
	CREATE UNIQUE INDEX [CSEasyWMDefLists] ON [dbo].[CSEasyWMDefLists]([DefID], [Name])
END
GO

if OBJECT_ID('FK_CSEasyWMDefLists_DefID') is null
begin
	alter table dbo.CSEasyWMDefLists
		ADD CONSTRAINT FK_CSEasyWMDefLists_DefID FOREIGN KEY ([DefID]) REFERENCES [CSEasyWMDefs]([ID])
end
go




------------------------------------------------------------------------------------------------------------------------
-- Table: CSEasyWMDefListColumns
------------------------------------------------------------------------------------------------------------------------
if OBJECT_ID('CSEasyWMDefListColumns') is null
begin
	CREATE TABLE [dbo].[CSEasyWMDefListColumns](
		[ID] [uniqueidentifier] NOT NULL default(newId()),
		[ListID] [uniqueidentifier] NOT NULL,
		[Alias] [nvarchar](128) NOT NULL,
		[ColumnName] [nvarchar](max) NOT NULL,
		[DataType] [nvarchar](128) NOT NULL,
		[FormattingOptions] [nchar](1) NULL,
		[BinaryType] [nchar](1) NULL,
		[BinaryAction] [nchar](1) NULL,
		[PreventDoubleSpacing] [tinyint] NOT NULL default(0),
		[Syscreated] [datetime] NOT NULL default(getDate()),
		[Syscreator] [int] NOT NULL default(0),
		[Sysmodified] [datetime] NOT NULL default(getDate()),
		[Sysmodifier] [int] NOT NULL default(0),
		[Sysguid] [uniqueidentifier] NOT NULL ROWGUIDCOL default(newId()),
		[Timestamp] [timestamp] NOT NULL,
	 CONSTRAINT [PK_CSEasyWMDefListColumns] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
end
GO

IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE name = 'CSEasyWMDefListColumns' AND id = object_id('CSEasyWMDefListColumns')) BEGIN
	CREATE UNIQUE INDEX [CSEasyWMDefListColumns] ON [dbo].[CSEasyWMDefListColumns]([ListID], [Alias])
END
GO

if not exists(select * from DDTests where TableName = 'CSEasyWMDefListColumns' and FieldName = 'FormattingOptions')
begin
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('0', 'CSEasyWMDefListColumns', 'FormattingOptions', '0', 'Uppercase', 35688, '', 0)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('1', 'CSEasyWMDefListColumns', 'FormattingOptions', '1', 'Lowercase', 37422, '', 1)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('2', 'CSEasyWMDefListColumns', 'FormattingOptions', '2', 'dd-mm-yyyy', 0, '', 2)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('3', 'CSEasyWMDefListColumns', 'FormattingOptions', '3', 'dd-mm-yyyy HH:mm', 0, '', 2)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('4', 'CSEasyWMDefListColumns', 'FormattingOptions', '4', 'dd mmmm yyyy', 0, '', 4)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('5', 'CSEasyWMDefListColumns', 'FormattingOptions', '5', '0,00', 0, '', 5)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('6', 'CSEasyWMDefListColumns', 'FormattingOptions', '6', '0.000,00', 0, '', 6)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('7', 'CSEasyWMDefListColumns', 'FormattingOptions', '7', '0', 0, '', 7)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('8', 'CSEasyWMDefListColumns', 'FormattingOptions', '8', '0.000', 0, '', 8)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('9', 'CSEasyWMDefListColumns', 'FormattingOptions', '9', 'HH:mm', 0, '', 9)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('A', 'CSEasyWMDefListColumns', 'FormattingOptions', 'A', '00.000.000', 0, '', 10)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('B', 'CSEasyWMDefListColumns', 'FormattingOptions', 'B', '0 or 1', 421066, '', 11)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('C', 'CSEasyWMDefListColumns', 'FormattingOptions', 'C', 'Yes or No', 421067, '', 12)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('D', 'CSEasyWMDefListColumns', 'FormattingOptions', 'D', 'yes or no', 421068, '', 13)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('E', 'CSEasyWMDefListColumns', 'FormattingOptions', 'E', 'First character uppercase', 421205, '', 14)
	-- keep same as the formatting options of CSEasyWMDefColumns
end
go

if not exists(select * from DDTests where TableName = 'CSEasyWMDefListColumns' and FieldName = 'BinaryType')
begin
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('P', 'CSEasyWMDefListColumns', 'BinaryType', 'P', 'Picture', 220, '', 0)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('D', 'CSEasyWMDefListColumns', 'BinaryType', 'D', 'Document', 7226, '', 1)
end
go

if not exists(select * from DDTests where TableName = 'CSEasyWMDefListColumns' and FieldName = 'BinaryAction')
begin
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('I', 'CSEasyWMDefListColumns', 'BinaryAction', 'I', 'Insert document', 421203, '', 0)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('M', 'CSEasyWMDefListColumns', 'BinaryAction', 'M', 'Mergeable attachment', 421196, '', 1)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('F', 'CSEasyWMDefListColumns', 'BinaryAction', 'F', 'File attachment', 421197, '', 2)
end
go

if OBJECT_ID('FK_CSEasyWMDefListColumns_ListID') is null
begin
	alter table dbo.CSEasyWMDefListColumns
		ADD CONSTRAINT FK_CSEasyWMDefListColumns_ListID FOREIGN KEY ([ListID]) REFERENCES [CSEasyWMDefLists]([ID])
end
go



------------------------------------------------------------------------------------------------------------------------
-- Table: CSEasyWMDefListSelection
------------------------------------------------------------------------------------------------------------------------
if OBJECT_ID('CSEasyWMDefListSelection') is null
begin
	CREATE TABLE [dbo].[CSEasyWMDefListSelection](
		[ID] [uniqueidentifier] NOT NULL default(newId()),
		[ListID] [uniqueidentifier] NOT NULL,
		[DefColumnID] [uniqueidentifier] NOT NULL,
		[ListColumnID] [uniqueidentifier] NOT NULL,
		[Syscreated] [datetime] NOT NULL default(getDate()),
		[Syscreator] [int] NOT NULL default(0),
		[Sysmodified] [datetime] NOT NULL default(getDate()),
		[Sysmodifier] [int] NOT NULL default(0),
		[Sysguid] [uniqueidentifier] NOT NULL ROWGUIDCOL default(newId()),
		[Timestamp] [timestamp] NOT NULL,
	 CONSTRAINT [PK_CSEasyWMDefListSelection] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
end
GO

IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE name = 'CSEasyWMDefListSelection' AND id = object_id('CSEasyWMDefListSelection')) BEGIN
	CREATE UNIQUE INDEX [CSEasyWMDefListSelection] ON [dbo].[CSEasyWMDefListSelection]([ListID], [DefColumnID], [ListColumnID])
END
GO

if OBJECT_ID('FK_CSEasyWMDefListSelection_ListID') is null
begin
	alter table dbo.CSEasyWMDefListSelection
		ADD CONSTRAINT FK_CSEasyWMDefListSelection_ListID FOREIGN KEY ([ListID]) REFERENCES [CSEasyWMDefLists]([ID])
end
go
if OBJECT_ID('FK_CSEasyWMDefListSelection_DefColumnID') is null
begin
	alter table dbo.CSEasyWMDefListSelection
		ADD CONSTRAINT FK_CSEasyWMDefListSelection_DefColumnID FOREIGN KEY ([DefColumnID]) REFERENCES [CSEasyWMDefColumns]([ID])
end
go
if OBJECT_ID('FK_CSEasyWMDefListSelection_ListColumnID') is null
begin
	alter table dbo.CSEasyWMDefListSelection
		ADD CONSTRAINT FK_CSEasyWMDefListSelection_ListColumnID FOREIGN KEY ([ListColumnID]) REFERENCES [CSEasyWMDefListColumns]([ID])
end
go





------------------------------------------------------------------------------------------------------------------------
-- Table: CSEasyWMDefFixedAttachments
------------------------------------------------------------------------------------------------------------------------
if OBJECT_ID('CSEasyWMDefFixedAttachments') is null
begin
	CREATE TABLE [dbo].[CSEasyWMDefFixedAttachments](
		[ID] [uniqueidentifier] NOT NULL default(newId()),
		[DefID] [uniqueidentifier] NOT NULL,
		[DocumentID] [uniqueidentifier] NULL,
		[AttachmentType] [nchar](1) NOT NULL default(N'A'),
		[AttachmentMergeType] [nchar](1) NOT NULL default(N'M'),
		[Syscreated] [datetime] NOT NULL default(getDate()),
		[Syscreator] [int] NOT NULL default(0),
		[Sysmodified] [datetime] NOT NULL default(getDate()),
		[Sysmodifier] [int] NOT NULL default(0),
		[Sysguid] [uniqueidentifier] NOT NULL ROWGUIDCOL default(newId()),
		[Timestamp] [timestamp] NOT NULL,
	 CONSTRAINT [PK_CSEasyWMDefFixedAttachments] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
end
GO

IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE name = 'CSEasyWMDefFixedAttachments' AND id = object_id('CSEasyWMDefFixedAttachments')) BEGIN
	CREATE UNIQUE INDEX [CSEasyWMDefFixedAttachments] ON [dbo].[CSEasyWMDefFixedAttachments]([DefID], [DocumentID])
END
GO

if not exists(select * from DDTests where TableName = 'CSEasyWMDefFixedAttachments' and FieldName = 'AttachmentType')
begin
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('A', 'CSEasyWMDefFixedAttachments', 'AttachmentType', 'A', 'Always include attachment', 421090, '', 0)
	--insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('O', 'CSEasyWMDefFixedAttachments', 'AttachmentType', 'O', 'Optionally include attachment', 421091, '', 1)
end
go

if not exists(select * from DDTests where TableName = 'CSEasyWMDefFixedAttachments' and FieldName = 'AttachmentMergeType')
begin
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('M', 'CSEasyWMDefFixedAttachments', 'AttachmentMergeType', 'M', 'Mergeable attachment', 421196, '', 0)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('F', 'CSEasyWMDefFixedAttachments', 'AttachmentMergeType', 'F', 'File attachment', 421197, '', 1)
end
go


if OBJECT_ID('FK_CSEasyWMDefFixedAttachments_DefID') is null
begin
	alter table dbo.CSEasyWMDefFixedAttachments
		ADD CONSTRAINT FK_CSEasyWMDefFixedAttachments_DefID FOREIGN KEY ([DefID]) REFERENCES [CSEasyWMDefs]([ID])
end
go
if OBJECT_ID('FK_CSEasyWMDefFixedAttachments_DocumentID') is null
begin
	alter table dbo.CSEasyWMDefFixedAttachments
		ADD CONSTRAINT FK_CSEasyWMDefFixedAttachments_DocumentID FOREIGN KEY ([DocumentID]) REFERENCES [BacoDiscussions]([ID])
end
go




------------------------------------------------------------------------------------------------------------------------
-- Table: CSEasyWMDocumentType
------------------------------------------------------------------------------------------------------------------------
if OBJECT_ID('CSEasyWMDocumentType') is null
begin
	CREATE TABLE [dbo].[CSEasyWMDocumentType](
		[ContainsTemplates] [tinyint] NOT NULL default(0),
		[LINKID] [int] NOT NULL,
)
end
GO


IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE name = 'CSEasyWMDocumentType' AND id = object_id('CSEasyWMDocumentType')) BEGIN
	CREATE UNIQUE CLUSTERED INDEX [CSEasyWMDocumentType] ON [dbo].[CSEasyWMDocumentType]([LINKID])
END
GO

if OBJECT_ID('FK_CSEasyWMDocumentType_LINKID') is null
begin
	alter table dbo.CSEasyWMDocumentType
		ADD CONSTRAINT FK_CSEasyWMDocumentType_LINKID FOREIGN KEY ([LINKID]) REFERENCES [BacoDiscussionFixedTypes]([TypeID]) on update cascade on delete cascade
end
go

insert into CSEasyWMDocumentType(ContainsTemplates, LinkID)
	select 0, TypeID
	from BacoDiscussionFixedTypes
	where TypeID not in (select LinkID from CSEasyWMDocumentType)
go



------------------------------------------------------------------------------------------------------------------------
-- Table: CSEasyWMASPXSupportedGroups
------------------------------------------------------------------------------------------------------------------------
if OBJECT_ID('CSEasyWMASPXSupportedGroups') is null
begin
	CREATE TABLE [dbo].[CSEasyWMASPXSupportedGroups](
		[ID] [int] NOT NULL,
		[TermID] [int] NOT NULL,
		[Caption] [nvarchar](128) NOT NULL,
		[DisplayOrder] [int] NOT NULL,
	 CONSTRAINT [PK_CSEasyWMASPXSupportedGroups] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
end
GO


if not exists(select * from CSEasyWMASPXSupportedGroups)
begin
	insert into CSEasyWMASPXSupportedGroups(ID, TermID, Caption, DisplayOrder) values(0, 8887, 'Other', 0)
	insert into CSEasyWMASPXSupportedGroups(ID, TermID, Caption, DisplayOrder) values(1, 1, 'CRM', 1)
	insert into CSEasyWMASPXSupportedGroups(ID, TermID, Caption, DisplayOrder) values(2, 224, 'Workflow', 2)
	insert into CSEasyWMASPXSupportedGroups(ID, TermID, Caption, DisplayOrder) values(3, 8671, 'Project', 3)
	insert into CSEasyWMASPXSupportedGroups(ID, TermID, Caption, DisplayOrder) values(4, 32656, 'HRM', 4)
	insert into CSEasyWMASPXSupportedGroups(ID, TermID, Caption, DisplayOrder) values(5, 19646, 'Logistics', 5)
end
go



------------------------------------------------------------------------------------------------------------------------
-- Table: CSEasyWMASPXSupported
------------------------------------------------------------------------------------------------------------------------
if OBJECT_ID('CSEasyWMASPXSupported') is null
begin
	CREATE TABLE [dbo].[CSEasyWMASPXSupported](
		[ID] [uniqueidentifier] NOT NULL default(newId()),
		[PageName] [nvarchar](128) NOT NULL,
		[GroupID] [int] NOT NULL default(0),
		[CaptionID] [int] NOT NULL,
		[Caption] [nvarchar](128) NOT NULL,
		[CaptionSuffixID] [int] NULL,
		[CaptionSuffix] [nvarchar](128) NULL,
	 CONSTRAINT [PK_CSEasyWMASPXSupported] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
end
GO

IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE name = 'CSEasyWMASPXSupported' AND id = object_id('CSEasyWMASPXSupported')) BEGIN
	CREATE UNIQUE INDEX [CSEasyWMASPXSupported] ON [dbo].[CSEasyWMASPXSupported]([PageName])
END
GO

if OBJECT_ID('FK_CSEasyWMASPXSupported_GroupID') is null
begin
	alter table dbo.CSEasyWMASPXSupported
		ADD CONSTRAINT FK_CSEasyWMASPXSupported_GroupID FOREIGN KEY ([GroupID]) REFERENCES [CSEasyWMASPXSupportedGroups]([ID])
end
go

if not exists(select * from [CSEasyWMASPXSupported])
begin
	insert into [CSEasyWMASPXSupported]([PageName], [GroupID], [CaptionID], [Caption], [CaptionSuffixID], [CaptionSuffix]) values('ProCard.aspx', 3, 8657, 'Project', null, null)
	insert into [CSEasyWMASPXSupported]([PageName], [GroupID], [CaptionID], [Caption], [CaptionSuffixID], [CaptionSuffix]) values('ProSearch.aspx', 3, 8671, 'Projects', 8788, ':Search')

	insert into [CSEasyWMASPXSupported]([PageName], [GroupID], [CaptionID], [Caption], [CaptionSuffixID], [CaptionSuffix]) values('CRMAccountCard.aspx', 1, 8, 'Account', null, null)
	insert into [CSEasyWMASPXSupported]([PageName], [GroupID], [CaptionID], [Caption], [CaptionSuffixID], [CaptionSuffix]) values('CRMContactCard.aspx', 1, 8484, 'Contact person', null, null)
	insert into [CSEasyWMASPXSupported]([PageName], [GroupID], [CaptionID], [Caption], [CaptionSuffixID], [CaptionSuffix]) values('CRMPersonAccountCard.aspx', 1, 8, 'Account', 8476, 'Person')
	insert into [CSEasyWMASPXSupported]([PageName], [GroupID], [CaptionID], [Caption], [CaptionSuffixID], [CaptionSuffix]) values('CRMSearch.aspx', 1, 9, 'Accounts', 8788, ':Search')
	insert into [CSEasyWMASPXSupported]([PageName], [GroupID], [CaptionID], [Caption], [CaptionSuffixID], [CaptionSuffix]) values('CRMOpportunityCard.aspx', 1, 34972, 'Opportunity card', null, null)
	insert into [CSEasyWMASPXSupported]([PageName], [GroupID], [CaptionID], [Caption], [CaptionSuffixID], [CaptionSuffix]) values('CRMOppSearch.aspx', 1, 34680, 'Opportunities', 8788, ':Search')

	insert into [CSEasyWMASPXSupported]([PageName], [GroupID], [CaptionID], [Caption], [CaptionSuffixID], [CaptionSuffix]) values('WflRequest.aspx', 2, 16, 'Request', null, null)
	insert into [CSEasyWMASPXSupported]([PageName], [GroupID], [CaptionID], [Caption], [CaptionSuffixID], [CaptionSuffix]) values('WflSearch.aspx', 2, 224, 'Workflow', 8788, ':Search')

	insert into [CSEasyWMASPXSupported]([PageName], [GroupID], [CaptionID], [Caption], [CaptionSuffixID], [CaptionSuffix]) values('HRMResourceCard.aspx', 4, 32656, 'HRM', 12, ': Resource')
	insert into [CSEasyWMASPXSupported]([PageName], [GroupID], [CaptionID], [Caption], [CaptionSuffixID], [CaptionSuffix]) values('HRMResourceSearch.aspx', 4, 32656, 'HRM', 421222, ':Search - Resources')

	insert into [CSEasyWMASPXSupported]([PageName], [GroupID], [CaptionID], [Caption], [CaptionSuffixID], [CaptionSuffix]) values('LogItemCard.aspx', 5, 8702, 'Item', null, null)
	insert into [CSEasyWMASPXSupported]([PageName], [GroupID], [CaptionID], [Caption], [CaptionSuffixID], [CaptionSuffix]) values('LogSearch.aspx', 5, 8788, 'Search', 9889, ':Items')	
end
go



------------------------------------------------------------------------------------------------------------------------
-- Table: CSEasyWMIntegrationPages
------------------------------------------------------------------------------------------------------------------------
if OBJECT_ID('CSEasyWMIntegrationPages') is null
begin
	CREATE TABLE [dbo].[CSEasyWMIntegrationPages](
		[ID] [uniqueidentifier] NOT NULL default(newId()),
		[PageID] [uniqueidentifier] NOT NULL,
		[Caption] [nvarchar](60) NOT NULL,
		[CaptionTermID] [int] NOT NULL default(0),
		[ButtonType] [nchar](1) NOT NULL default(N'D'),
		[ShowAsButtons] [tinyint] NOT NULL default(0),
		[DisplayOrder] [int] NOT NULL default(0),
		[DisplayCriteria] [nvarchar](max) NULL, 
		[Syscreated] [datetime] NOT NULL default(getDate()),
		[Syscreator] [int] NOT NULL default(0),
		[Sysmodified] [datetime] NOT NULL default(getDate()),
		[Sysmodifier] [int] NOT NULL default(0),
		[Sysguid] [uniqueidentifier] NOT NULL ROWGUIDCOL default(newId()),
		[Timestamp] [timestamp] NOT NULL,
	 CONSTRAINT [PK_CSEasyWMIntegrationPages] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
end
GO

if not exists(select * from DDTests where TableName = 'CSEasyWMIntegrationPages' and FieldName = 'ButtonType')
begin
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('D', 'CSEasyWMIntegrationPages', 'ButtonType', 'D', 'Definition', 421027, '', 0)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('G', 'CSEasyWMIntegrationPages', 'ButtonType', 'G', 'Definitions', 421029, '', 1)
end
go

if OBJECT_ID('FK_CSEasyWMIntegrationPages_PageID') is null
begin
	alter table dbo.CSEasyWMIntegrationPages
		ADD CONSTRAINT FK_CSEasyWMIntegrationPages_PageID FOREIGN KEY ([PageID]) REFERENCES [CSEasyWMASPXSupported]([ID])
end
go




------------------------------------------------------------------------------------------------------------------------
-- Table: CSEasyWMIntegrationPageDefs
------------------------------------------------------------------------------------------------------------------------
if OBJECT_ID('CSEasyWMIntegrationPageDefs') is null
begin
	CREATE TABLE [dbo].[CSEasyWMIntegrationPageDefs](
		[ID] [uniqueidentifier] NOT NULL default(newId()),
		[IntegrationID] [uniqueidentifier] NOT NULL,
		[DefID] [uniqueidentifier] NULL,
		[DefColumnName] [nvarchar](128) NOT NULL,
		[DefColumnType] [nchar](1) NOT NULL default(N'N'),
		[Syscreated] [datetime] NOT NULL default(getDate()),
		[Syscreator] [int] NOT NULL default(0),
		[Sysmodified] [datetime] NOT NULL default(getDate()),
		[Sysmodifier] [int] NOT NULL default(0),
		[Sysguid] [uniqueidentifier] NOT NULL ROWGUIDCOL default(newId()),
		[Timestamp] [timestamp] NOT NULL,
	 CONSTRAINT [PK_CSEasyWMIntegrationPageDefs] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
end
GO

IF NOT EXISTS(SELECT 1 FROM sysindexes WHERE name = 'CSEasyWMIntegrationPageDefs' AND id = object_id('CSEasyWMIntegrationPageDefs')) BEGIN
	CREATE UNIQUE INDEX [CSEasyWMIntegrationPageDefs] ON [dbo].[CSEasyWMIntegrationPageDefs]([IntegrationID], [DefID])
END
GO

if OBJECT_ID('FK_CSEasyWMIntegrationPageDefs_IntegrationID') is null
begin
	alter table dbo.CSEasyWMIntegrationPageDefs
		ADD CONSTRAINT FK_CSEasyWMIntegrationPageDefs_IntegrationID FOREIGN KEY ([IntegrationID]) REFERENCES [CSEasyWMIntegrationPages]([ID])
end
go
if OBJECT_ID('FK_CSEasyWMIntegrationPageDefs_DefID') is null
begin
	alter table dbo.CSEasyWMIntegrationPageDefs
		ADD CONSTRAINT FK_CSEasyWMIntegrationPageDefs_DefID FOREIGN KEY ([DefID]) REFERENCES [CSEasyWMDefs]([ID])
end
go

if not exists(select * from DDTests where TableName = 'CSEasyWMIntegrationPageDefs' and FieldName = 'DefColumnType')
begin
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('N', 'CSEasyWMIntegrationPageDefs', 'DefColumnType', 'N', 'N/A', 3502, '', 0)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('A', 'CSEasyWMIntegrationPageDefs', 'DefColumnType', 'A', 'Account', 8, '', 1)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('C', 'CSEasyWMIntegrationPageDefs', 'DefColumnType', 'C', 'Contact person', 8484, '', 2)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('R', 'CSEasyWMIntegrationPageDefs', 'DefColumnType', 'R', 'Request', 16, '', 3)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('P', 'CSEasyWMIntegrationPageDefs', 'DefColumnType', 'P', 'Project', 8657, '', 8)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('F', 'CSEasyWMIntegrationPageDefs', 'DefColumnType', 'F', 'Resource', 12, '', 9)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('J', 'CSEasyWMIntegrationPageDefs', 'DefColumnType', 'J', 'Item', 8702, '', 10)
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('O', 'CSEasyWMIntegrationPageDefs', 'DefColumnType', 'O', 'Opportunity', 34831, '', 11)
end
go




------------------------------------------------------------------------------------------------------------------------
-- Table: CSEasyWMDefActions
------------------------------------------------------------------------------------------------------------------------
if OBJECT_ID('CSEasyWMDefActions') is null
begin
	CREATE TABLE [dbo].[CSEasyWMDefActions](
		[ID] [uniqueidentifier] NOT NULL default(newId()),
		[DefID] [uniqueidentifier] NOT NULL,
		[Title] [nvarchar](60) NOT NULL,
		[Description] [nvarchar](max) NULL,
		[ActionOrder] [int] NOT NULL default(0),
		[Moment] [nchar](1) NOT NULL default(N'S'),
		[Assembly] [nvarchar](500) NOT NULL,
		[ClassName] [nvarchar](500) NOT NULL,
		[Params] [xml] NOT NULL,
		[Syscreated] [datetime] NOT NULL default(getDate()),
		[Syscreator] [int] NOT NULL default(0),
		[Sysmodified] [datetime] NOT NULL default(getDate()),
		[Sysmodifier] [int] NOT NULL default(0),
		[Sysguid] [uniqueidentifier] NOT NULL ROWGUIDCOL default(newId()),
		[Timestamp] [timestamp] NOT NULL,
	 CONSTRAINT [PK_CSEasyWMDefActions] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
end
GO

if not exists(select * from DDTests where TableName = 'CSEasyWMDefActions' and FieldName = 'Moment')
begin
	insert into DDTests(DatabaseChar, Tablename, FieldName, DisplayChar, Description, TermId, Options, SeqNr) values('S', 'CSEasyWMDefActions', 'Moment', 'S', 'After single document', 421111, '', 0)
end
go

if OBJECT_ID('FK_CSEasyWMDefActions_DefID') is null
begin
	alter table dbo.CSEasyWMDefActions
		ADD CONSTRAINT FK_CSEasyWMDefActions_DefID FOREIGN KEY ([DefID]) REFERENCES [CSEasyWMDefs]([ID])
end
go





------------------------------------------------------------------------------------------------------------------------
-- Table: CSEasyWMSearchMergeCache
------------------------------------------------------------------------------------------------------------------------
if OBJECT_ID('CSEasyWMSearchMergeCache') is null
begin
	CREATE TABLE [dbo].[CSEasyWMSearchMergeCache](
		[ID] [uniqueidentifier] NOT NULL default(newId()),
		[Select] [nvarchar](max) NULL,
		[From] [nvarchar](max) NULL,
		[Where] [nvarchar](max) NULL,
		[OrderBy] [nvarchar](max) NULL,
		[GroupBy] [nvarchar](max) NULL,
		[Syscreated] [datetime] NOT NULL default(getDate()),
		[Syscreator] [int] NOT NULL default(0),
		[Sysmodified] [datetime] NOT NULL default(getDate()),
		[Sysmodifier] [int] NOT NULL default(0),
		[Sysguid] [uniqueidentifier] NOT NULL ROWGUIDCOL default(newId()),
		[Timestamp] [timestamp] NOT NULL,
	 CONSTRAINT [PK_CSEasyWMSearchMergeCache] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
	) ON [PRIMARY]
end
GO

------------------------------------------------------------------------------------------------------------------------
-- Table: CSEasyWMDefEmployeeFields
------------------------------------------------------------------------------------------------------------------------
if OBJECT_ID('CSEasyWMDefEmployeeFields') is null
begin
	CREATE TABLE [dbo].[CSEasyWMDefEmployeeFields](
		[ID] [uniqueidentifier] NOT NULL default(newId()),
		[DefID] [uniqueidentifier] NOT NULL,
		[Level] [nchar](1) NULL,
		[ColumnName] [nvarchar](128) not null default(0),
		[DisplayName] [nvarchar](128) not null default(0),
		[DisplayNameTermID] [int] NOT NULL,
		[InUse] [Tinyint] NULL,
		[Syscreated] [datetime] NOT NULL default(getDate()),
		[Syscreator] [int] NOT NULL default(0),
		[Sysmodified] [datetime] NOT NULL default(getDate()),
		[Sysmodifier] [int] NOT NULL default(0),
		[Sysguid] [uniqueidentifier] NOT NULL ROWGUIDCOL default(newId()),
		[Timestamp] [timestamp] NOT NULL,
	 CONSTRAINT [PK_CSEasyWMDefEmployeeFields] PRIMARY KEY NONCLUSTERED
		([ID] ASC) ON [PRIMARY]) ON [PRIMARY]
end
GO

if OBJECT_ID('FK_CSEasyWMDefEmpFields_DefID') is null
begin
	alter table dbo.CSEasyWMDefEmployeeFields
		ADD CONSTRAINT FK_CSEasyWMDefEmpFields_DefID FOREIGN KEY ([DefID]) REFERENCES [CSEasyWMDefs]([ID]) on update cascade on delete cascade
end
go
