SET ARITHABORT ON 
SET ANSI_NULLS ON 
SET QUOTED_IDENTIFIER ON

GO

--Create database structure

IF EXISTS(SELECT name FROM sysobjects WHERE name = 'RatchetPriceList')
	DROP VIEW RatchetPriceList

IF EXISTS(SELECT name FROM sysobjects WHERE name = 'RatchetDescriptions')
	DROP TABLE RatchetDescriptions
GO	
CREATE TABLE [dbo].[RatchetDescriptions] (
 [RatchetID] [int] NOT NULL ,
 [ShortDescription] nvarchar(2000) NULL ,
 [Description] [text] NULL ,
 [Picture] [image] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
 
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'RatchetPrices')
	DROP TABLE RatchetPrices
GO	
CREATE TABLE [dbo].[RatchetPrices] (
 [RecordID] [int] IDENTITY (1, 1) NOT NULL ,
 [RatchetID] [int] NULL ,
 [Price] [money] NULL ,
 [DateValidFrom] [datetime] NULL ,
 [DateValidTo] [datetime] NULL ,
 [Active] [char] (1) NULL 
) ON [PRIMARY]
GO
 
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'RatchetReferences')
	DROP TABLE RatchetReferences
GO 
CREATE TABLE [dbo].[RatchetReferences] (
 [RatchetID] [int] NOT NULL ,
 [Reference] [varchar] (50) NULL 
) ON [PRIMARY]
GO
 
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'Ratchets')
	DROP TABLE Ratchets
GO
CREATE TABLE [dbo].[Ratchets] (
 [RecordID] [int] IDENTITY (1, 1) NOT NULL ,
 [Description] [varchar] (50) NULL ,
 [SKU] [varchar] (20) NULL 
) ON [PRIMARY]
GO

IF EXISTS(SELECT name FROM sysobjects WHERE name = 'RatchetPurchases')
	DROP TABLE RatchetPurchases
GO
CREATE TABLE [dbo].[RatchetPurchases] (
 [PurchaseID] [int] IDENTITY (1, 1) NOT NULL ,
 [RatchetPriceID] [int] NOT NULL ,
 [Quantity] [int] NOT NULL DEFAULT (1) ,
 [InvoiceNumber] [nvarchar] (20) NULL ,
 [Date] [datetime] NOT NULL DEFAULT (getdate())
)
GO
 
 
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'Contacts')
	DROP TABLE Contacts
GO
CREATE TABLE [dbo].[Contacts] (
 [ID] [int] IDENTITY (1, 1) NOT NULL ,
 [Name] [nvarchar](100) NOT NULL ,
 [PhoneWork] [nvarchar](25) NULL ,
 [PhoneMobile] [nvarchar](25) NULL ,
 [Address1] [nvarchar](128) NULL ,
 [Address2] [nvarchar](128) NULL ,
 [Address3] [nvarchar](128) NULL ,
 [JoiningDate] [datetime] NULL DEFAULT (getdate()),
 [Email] [nvarchar](256) NULL
)
GO

ALTER TABLE [dbo].[RatchetDescriptions] WITH NOCHECK ADD 
 CONSTRAINT [PK_RatchetDescriptions] PRIMARY KEY  CLUSTERED 
 (
  [RatchetID]
 )  ON [PRIMARY] 
GO
 
ALTER TABLE [dbo].[RatchetPrices] WITH NOCHECK ADD 
 CONSTRAINT [DF_RatchetPrices_DateValidFrom] DEFAULT (getdate()) FOR [DateValidFrom],
 CONSTRAINT [DF_RatchetPrices_Active] DEFAULT ('N') FOR [Active],
 CONSTRAINT [PK_RatchetPrices] PRIMARY KEY  NONCLUSTERED 
 (
  [RecordID]
 )  ON [PRIMARY] 
GO
 
ALTER TABLE [dbo].[RatchetReferences] WITH NOCHECK ADD 
 CONSTRAINT [PK_RatchetReferences] PRIMARY KEY  NONCLUSTERED 
 (
  [RatchetID]
 )  ON [PRIMARY] 
GO
 
ALTER TABLE [dbo].[Ratchets] WITH NOCHECK ADD 
 CONSTRAINT [PK_Ratchets] PRIMARY KEY  NONCLUSTERED 
 (
  [RecordID]
 )  ON [PRIMARY] 
GO
 
ALTER TABLE [dbo].[Contacts] WITH NOCHECK ADD
 CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED
 (
  [ID]
 )
GO

 CREATE  INDEX [IX_RatchetPrices] ON [dbo].[RatchetPrices]([RatchetID]) ON [PRIMARY]
GO
 
 CREATE  INDEX [IX_RatchetPrices_1] ON [dbo].[RatchetPrices]([DateValidFrom]) ON [PRIMARY]
GO
 
 CREATE  INDEX [IX_RatchetPrices_2] ON [dbo].[RatchetPrices]([DateValidTo]) ON [PRIMARY]
GO

 CREATE UNIQUE CLUSTERED INDEX [IX_RatchetPurchases] ON [dbo].[RatchetPurchases]([PurchaseID]) ON [PRIMARY]
GO

-- Create indexed view
GO
CREATE VIEW dbo.RatchetPriceList
WITH SCHEMABINDING 
AS
SELECT     dbo.Ratchets.RecordID, dbo.Ratchets.Description AS Ratchet, dbo.RatchetPrices.Price
FROM       dbo.Ratchets INNER JOIN
           dbo.RatchetPrices ON dbo.Ratchets.RecordID = dbo.RatchetPrices.RecordID
GO

CREATE UNIQUE CLUSTERED INDEX [IX_RatchetPriceList] ON [dbo].[RatchetPriceList] ([RecordID])
GO