SET CONCAT_NULL_YIELDS_NULL, ANSI_NULLS, ANSI_PADDING, QUOTED_IDENTIFIER, ANSI_WARNINGS, ARITHABORT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO

USE master
GO

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'BicycleStoreDemo'
GO

ALTER DATABASE [BicycleStoreDemo] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

DROP DATABASE IF EXISTS BicycleStoreDemo
GO

IF DB_ID('BicycleStoreDemo') IS NULL
  CREATE DATABASE BicycleStoreDemo
GO


USE BicycleStoreDemo
GO

IF DB_NAME() <> N'BicycleStoreDemo' SET NOEXEC ON
GO

--
-- Create schema [Sales]
--
CREATE SCHEMA [Sales] AUTHORIZATION [dbo]
GO

--
-- Create table [Sales].[Shift]
--
CREATE TABLE [Sales].[Shift] (
  [ShiftID] [tinyint] IDENTITY,
  [StartTime] [time](0) NOT NULL,
  [EndTime] [time](0) NOT NULL,
  CONSTRAINT [PK_Shift_ShiftID] PRIMARY KEY CLUSTERED ([ShiftID])
)
ON [PRIMARY]
GO

--
-- Create schema [Production]
--
CREATE SCHEMA [Production] AUTHORIZATION [dbo]
GO

--
-- Create table [Sales].[Store]
--
CREATE TABLE [Sales].[Store] (
  [StoreId] [int] IDENTITY,
  [StoreName] [varchar](255) NOT NULL,
  [Phone] [varchar](25) NULL,
  [Email] [varchar](255) NULL,
  [Street] [varchar](255) NULL,
  [City] [varchar](255) NULL,
  [State] [varchar](10) NULL,
  [Zip] [varchar](5) NULL,
  PRIMARY KEY CLUSTERED ([StoreId])
)
ON [PRIMARY]
GO

--
-- Create table [Sales].[Staff]
--
CREATE TABLE [Sales].[Staff] (
  [StaffId] [int] IDENTITY,
  [FirstName] [varchar](50) NOT NULL,
  [LastName] [varchar](50) NOT NULL,
  DOB date NOT NULL,
  [Email] [varchar](255) NOT NULL,
  [Phone] [varchar](25) NULL,
  [Active] [tinyint] NOT NULL,
  [StoreId] [int] NOT NULL,
  [ManagerId] [int] NULL,
  [ShiftId] tinyint NULL
  PRIMARY KEY CLUSTERED ([StaffId]),
  UNIQUE ([Email])
)
ON [PRIMARY]
GO

ALTER TABLE Sales.Staff
  ADD CONSTRAINT FK_Staff_ShiftId FOREIGN KEY (ShiftId) REFERENCES Sales.Shift (ShiftID)
GO

--
-- Create foreign key on table [Sales].[Staff]
--
ALTER TABLE [Sales].[Staff]
  ADD FOREIGN KEY ([ManagerId]) REFERENCES [Sales].[Staff] ([StaffId])
GO

--
-- Create foreign key on table [Sales].[Staff]
--
ALTER TABLE [Sales].[Staff]
  ADD FOREIGN KEY ([StoreId]) REFERENCES [Sales].[Store] ([StoreId])
GO

--
-- Create table [Sales].[Customer]
--
CREATE TABLE [Sales].[Customer] (
  [CustomerId] [int] IDENTITY,
  [FirstName] [varchar](255) NOT NULL,
  [LastName] [varchar](255) NOT NULL,
  [Phone] [varchar](30) NULL,
  [Email] [varchar](255) NOT NULL,
  [Street] [varchar](255) NULL,
  [City] [varchar](50) NULL,
  [State] [varchar](25) NULL,
  [Zip] [varchar](5) NULL,
  PRIMARY KEY CLUSTERED ([CustomerId])
)
ON [PRIMARY]
GO

--
-- Create table [Sales].[Order]
--
CREATE TABLE [Sales].[Order] (
  [OrderId] [int] IDENTITY,
  [CustomerId] [int] NULL,
  [OrderStatus] [tinyint] NOT NULL,
  [OrderDate] [date] NOT NULL,
  [RequiredDate] [date] NOT NULL,
  [ShippedDate] [date] NULL,
  [StoreId] [int] NOT NULL,
  [StaffId] [int] NOT NULL,
  PRIMARY KEY CLUSTERED ([OrderId])
)
ON [PRIMARY]
GO

--
-- Create foreign key on table [Sales].[Order]
--
ALTER TABLE [Sales].[Order]
  ADD FOREIGN KEY ([CustomerId]) REFERENCES [Sales].[Customer] ([CustomerId])
GO

--
-- Create foreign key on table [Sales].[Order]
--
ALTER TABLE [Sales].[Order]
  ADD FOREIGN KEY ([StaffId]) REFERENCES [Sales].[Staff] ([StaffId])
GO

--
-- Create foreign key on table [Sales].[Order]
--
ALTER TABLE [Sales].[Order]
  ADD FOREIGN KEY ([StoreId]) REFERENCES [Sales].[Store] ([StoreId])
GO

--
-- Create table [Production].[Category]
--
CREATE TABLE [Production].[Category] (
  [CategoryId] [int] IDENTITY,
  [CategoryName] [varchar](255) NOT NULL,
  PRIMARY KEY CLUSTERED ([CategoryId])
)
ON [PRIMARY]
GO

--
-- Create table [Production].[Brand]
--
CREATE TABLE [Production].[Brand] (
  [BrandId] [int] IDENTITY,
  [BrandName] [varchar](255) NOT NULL,
  [URL] [varchar](max) NULL,
  [Picture] [varbinary](max) NULL,
  PRIMARY KEY CLUSTERED ([BrandId])
)
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO

--
-- Create table [Production].[Product]
--
CREATE TABLE [Production].[Product] (
  [ProductId] [int] IDENTITY,
  [Name] [varchar](60) NOT NULL,
  [BrandId] [int] NOT NULL,
  [CategoryId] [int] NOT NULL,
  [ModelYear] [smallint] NULL,
  [Price] [decimal](10, 2) NOT NULL,
  PRIMARY KEY CLUSTERED ([ProductId])
)
ON [PRIMARY]
GO

--
-- Create foreign key on table [Production].[Product]
--
ALTER TABLE [Production].[Product]
  ADD FOREIGN KEY ([BrandId]) REFERENCES [Production].[Brand] ([BrandId]) ON DELETE CASCADE ON UPDATE CASCADE
GO

--
-- Create foreign key on table [Production].[Product]
--
ALTER TABLE [Production].[Product]
  ADD FOREIGN KEY ([CategoryId]) REFERENCES [Production].[Category] ([CategoryId]) ON DELETE CASCADE ON UPDATE CASCADE
GO

--
-- Create table [Sales].[OrderItem]
--
CREATE TABLE [Sales].[OrderItem] (
  [OrderId] [int] NOT NULL,
  [ItemId] [int] NOT NULL,
  [ProductId] [int] NOT NULL,
  [Qty] [int] NOT NULL,
  [Price] [decimal](10, 2) NOT NULL,
  [Discount] [decimal](4, 2) NOT NULL DEFAULT (0),
  PRIMARY KEY CLUSTERED ([OrderId], [ItemId])
)
ON [PRIMARY]
GO

--
-- Create foreign key on table [Sales].[OrderItem]
--
ALTER TABLE [Sales].[OrderItem]
  ADD FOREIGN KEY ([OrderId]) REFERENCES [Sales].[Order] ([OrderId]) ON DELETE CASCADE ON UPDATE CASCADE
GO

--
-- Create foreign key on table [Sales].[OrderItem]
--
ALTER TABLE [Sales].[OrderItem]
  ADD FOREIGN KEY ([ProductId]) REFERENCES [Production].[Product] ([ProductId]) ON DELETE CASCADE ON UPDATE CASCADE
GO

--
-- Create table [Production].[Stock]
--
CREATE TABLE [Production].[Stock] (
  [StoreId] [int] NOT NULL,
  [ProductId] [int] NOT NULL,
  [Qty] [int] NULL,
  PRIMARY KEY CLUSTERED ([StoreId], [ProductId])
)
ON [PRIMARY]
GO

--
-- Create foreign key on table [Production].[Stock]
--
ALTER TABLE [Production].[Stock]
  ADD FOREIGN KEY ([ProductId]) REFERENCES [Production].[Product] ([ProductId]) ON DELETE CASCADE ON UPDATE CASCADE
GO

--
-- Create foreign key on table [Production].[Stock]
--
ALTER TABLE [Production].[Stock]
  ADD FOREIGN KEY ([StoreId]) REFERENCES [Sales].[Store] ([StoreId]) ON DELETE CASCADE ON UPDATE CASCADE
GO

--
-- Set NOEXEC to off
--
SET NOEXEC OFF
GO