/****** Object:  Table [dbo].[Stages]    Script Date: 10/21/2011 15:04:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Stages](
	[StageID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](200) NULL,
	[IsApproved] [int] NOT NULL CONSTRAINT [DF_Stages_IsApproved]  DEFAULT ((0)),
	[DTS] [datetime] NOT NULL CONSTRAINT [DF_Stages_DTS]  DEFAULT (getdate()),
	[UserID] [nvarchar](100) NOT NULL CONSTRAINT [DF_Stages_UserID]  DEFAULT (upper(suser_sname())),
	[LastChanged] [timestamp] NOT NULL,
 CONSTRAINT [PK_Stages] PRIMARY KEY CLUSTERED 
(
	[StageID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{datetime}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stages', @level2type=N'COLUMN',@level2name=N'DTS'

/****** Object:  Table [dbo].[Revisions]    Script Date: 10/21/2011 15:06:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Revisions](
	[RevisionID] [int] IDENTITY(1,1) NOT NULL,
	[ItemID] [int] NOT NULL,
	[TypeID] [int] NOT NULL CONSTRAINT [DF_Revisions_TypeID]  DEFAULT ((1)),
	[RevisionNumber] [nvarchar](50) NULL,
	[RevisionDate] [datetime] NULL,
	[Notes] [nvarchar](max) NULL,
	[Config] [xml] NULL,
	[DTS] [datetime] NOT NULL CONSTRAINT [DF_Revisions_DTS]  DEFAULT (getdate()),
	[UserID] [nvarchar](100) NOT NULL CONSTRAINT [DF_Revisions_UserID]  DEFAULT (upper(suser_sname())),
	[LastChanged] [timestamp] NOT NULL,
 CONSTRAINT [PK_Revisions] PRIMARY KEY CLUSTERED 
(
	[RevisionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{datetime}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Revisions', @level2type=N'COLUMN',@level2name=N'RevisionDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{datetime}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Revisions', @level2type=N'COLUMN',@level2name=N'DTS'
GO

/****** Object:  Table [dbo].[Checks]    Script Date: 10/21/2011 15:07:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Checks](
	[CheckID] [int] IDENTITY(1,1) NOT NULL,
	[RevisionID] [int] NOT NULL,
	[StageID] [int] NOT NULL,
	[ConsistencyChecks] [xml] NULL,
	[DTS] [datetime] NOT NULL CONSTRAINT [DF_Checks_DTS]  DEFAULT (getdate()),
	[UserID] [nvarchar](100) NOT NULL CONSTRAINT [DF_Checks_UserID]  DEFAULT (upper(suser_sname())),
	[LastChanged] [timestamp] NOT NULL,
 CONSTRAINT [PK_Checks] PRIMARY KEY CLUSTERED 
(
	[CheckID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{datetime}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Checks', @level2type=N'COLUMN',@level2name=N'DTS'
GO

ALTER TABLE [dbo].[Checks]  WITH CHECK ADD  CONSTRAINT [FK_Checks_Revisions] FOREIGN KEY([RevisionID])
REFERENCES [dbo].[Revisions] ([RevisionID])
GO
ALTER TABLE [dbo].[Checks] CHECK CONSTRAINT [FK_Checks_Revisions]
GO
ALTER TABLE [dbo].[Checks]  WITH CHECK ADD  CONSTRAINT [FK_Checks_Stages] FOREIGN KEY([StageID])
REFERENCES [dbo].[Stages] ([StageID])
GO
ALTER TABLE [dbo].[Checks] CHECK CONSTRAINT [FK_Checks_Stages]

/****** Object:  Table [dbo].[Versions]    Script Date: 10/21/2011 15:09:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Versions](
	[VersionID] [int] IDENTITY(1,1) NOT NULL,
	[RevisionID] [int] NOT NULL,
	[StageID] [int] NOT NULL,
	[PDF] [varbinary](max) NULL,
	[SummaryPDF] [varbinary](max) NULL,
	[DTS] [datetime] NOT NULL,
	[UserID] [nvarchar](200) NOT NULL,
	[LastChanged] [timestamp] NOT NULL,
 CONSTRAINT [PK_Version] PRIMARY KEY CLUSTERED 
(
	[VersionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{datetime}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Versions', @level2type=N'COLUMN',@level2name=N'DTS'
GO

ALTER TABLE [dbo].[Versions]  WITH CHECK ADD  CONSTRAINT [FK_Versions_Revisions] FOREIGN KEY([RevisionID])
REFERENCES [dbo].[Revisions] ([RevisionID])
GO
ALTER TABLE [dbo].[Versions] CHECK CONSTRAINT [FK_Versions_Revisions]
GO
ALTER TABLE [dbo].[Versions]  WITH CHECK ADD  CONSTRAINT [FK_Versions_Stages] FOREIGN KEY([StageID])
REFERENCES [dbo].[Stages] ([StageID])
GO
ALTER TABLE [dbo].[Versions] CHECK CONSTRAINT [FK_Versions_Stages]


--put in stage data
INSERT INTO [dbo].[Stages] ([Name],[Description],[IsApproved],[DTS],[UserID]) VALUES ('Input to review','',0,getdate(),'VOLIAN\BODINE')
GO
INSERT INTO [dbo].[Stages] ([Name],[Description],[IsApproved],[DTS],[UserID]) VALUES ('Initial review','',0,getdate(),'VOLIAN\BODINE')
GO
INSERT INTO [dbo].[Stages] ([Name],[Description],[IsApproved],[DTS],[UserID]) VALUES ('Final review','',0,getdate(),'VOLIAN\BODINE')
GO
INSERT INTO [dbo].[Stages] ([Name],[Description],[IsApproved],[DTS],[UserID]) VALUES ('Verification','',0,getdate(),'VOLIAN\BODINE')
GO
INSERT INTO [dbo].[Stages] ([Name],[Description],[IsApproved],[DTS],[UserID]) VALUES ('Validation','',0,getdate(),'VOLIAN\BODINE')
GO
INSERT INTO [dbo].[Stages] ([Name],[Description],[IsApproved],[DTS],[UserID]) VALUES ('Issued','',1,getdate(),'VOLIAN\BODINE')
GO
INSERT INTO [dbo].[Stages] ([Name],[Description],[IsApproved],[DTS],[UserID]) VALUES ('Published','',1,getdate(),'VOLIAN\BODINE')
GO
INSERT INTO [dbo].[Stages] ([Name],[Description],[IsApproved],[DTS],[UserID]) VALUES ('Approved','',1,getdate(),'VOLIAN\BODINE')
GO
--end put in stage data