/*
===============================================================================
Author: Haroon Ashraf
Date: 16-Sep-2018
*******************************************************************************
File:
01-Setup OfficeSuppliesSampleV3 Database.sql
 
Description:
This script creates OfficeSuppliesSampleV3 database and its related objects
 
Dependencies/Pre-Conditions:
The script is compatible with SQL Server 2012 and upward version(s)

Note:
The script is free to use and is provided AS IS without warranty of any kind.
*******************************************************************************
** Change History
*******************************************************************************
Date         Author            Description
-----------  -------------    -------------------------------------------------

===============================================================================
*/
-- This script creates OfficeSuppliesSampleV3 database and its related objects (Haroon Ashraf 11-Dec-2017)
-- Please Note: The script is free to use and is provided AS IS without warranty of any kind

PRINT 'OfficeSuppliesSampleV3 database setup begins...'
IF NOT EXISTS (select * from  sys.databases where name='OfficeSuppliesSampleV3')  
Create Database OfficeSuppliesSampleV3;
GO


USE OfficeSuppliesSampleV3;
GO

-- (1) Drop tables if already exist
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='Orders') DROP TABLE  Orders
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='Customer') DROP TABLE  Customer
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='Product') DROP TABLE  Product;
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='OrderType') DROP TABLE  OrderType;
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='Date') DROP TABLE  [Date];
GO

-- (2) Create Product table
CREATE TABLE [dbo].[Product] (
    [ProductID] INT             IDENTITY (1, 1) NOT NULL,
    [Name]      VARCHAR (50)    NULL,
    [Stock]     INT             NULL,
    [Price]     DECIMAL (10, 2) NULL,
    CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED ([ProductID] ASC)

);

-- (3) Create Customer table	 
Create Table Customer
(CustomerID INT IDENTITY(1,1),
Name VARCHAR(70),
Email VARCHAR(320),
Phone VARCHAR(50)
CONSTRAINT [PK_Customer_CustomerID] PRIMARY KEY CLUSTERED ([CustomerID] ASC)
);
GO

-- (4) Create OrderType table
CREATE TABLE [dbo].[OrderType]
(
	[OrderTypeID] INT NOT NULL IDENTITY(1,1)
    CONSTRAINT [PK_OrderType] PRIMARY KEY ([OrderTypeID]),
	[Name] VARCHAR(40) NOT NULL,
	[Detail] VARCHAR(300) NULL
)


-- (5) Create Orders table 
CREATE TABLE [dbo].[Orders] (
    [OrderID]    INT             IDENTITY (1, 1) NOT NULL,
    [OrderTypeID] INT,
	[OrderDate]  DATETIME2 (7)   NULL,
    [CustomerId] INT             NULL,
    [ProductId]  INT             NULL,
    [Quantity]   INT             NULL,
    [TotalPrice] DECIMAL (10, 2) NULL,
    CONSTRAINT [PK_Orders_OrderID] PRIMARY KEY CLUSTERED ([OrderID] ASC),
    CONSTRAINT [FK_Orders_Customer] FOREIGN KEY ([CustomerId]) REFERENCES [dbo].[Customer] ([CustomerID]),
    CONSTRAINT [FK_Orders_Product] FOREIGN KEY ([ProductId]) REFERENCES [dbo].[Product] ([ProductID]), 
    CONSTRAINT [FK_Orders_OrderType] FOREIGN KEY ([OrderTypeID]) REFERENCES [dbo].[OrderType]([OrderTypeID]) ON DELETE CASCADE ON UPDATE CASCADE, 
);
GO

-- (6) Create Date table
CREATE TABLE [dbo].[Date] (
    [Date_Key]              DATETIME      NOT NULL,
    [Date_Name]             NVARCHAR (50) NULL,
    [Year]                  DATETIME      NULL,
    [Year_Name]             NVARCHAR (50) NULL,
    [Quarter]               DATETIME      NULL,
    [Quarter_Name]          NVARCHAR (50) NULL,
    [Month]                 DATETIME      NULL,
    [Month_Name]            NVARCHAR (50) NULL,
    [Day_Of_Year]           INT           NULL,
    [Day_Of_Year_Name]      NVARCHAR (50) NULL,
    [Day_Of_Quarter]        INT           NULL,
    [Day_Of_Quarter_Name]   NVARCHAR (50) NULL,
    [Day_Of_Month]          INT           NULL,
    [Day_Of_Month_Name]     NVARCHAR (50) NULL,
    [Month_Of_Year]         INT           NULL,
    [Month_Of_Year_Name]    NVARCHAR (50) NULL,
    [Month_Of_Quarter]      INT           NULL,
    [Month_Of_Quarter_Name] NVARCHAR (50) NULL,
    [Quarter_Of_Year]       INT           NULL,
    [Quarter_Of_Year_Name]  NVARCHAR (50) NULL,
    CONSTRAINT [PK_Date1] PRIMARY KEY CLUSTERED ([Date_Key] ASC)
);
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS V WHERE V.TABLE_NAME='OrdersView') DROP VIEW [dbo].[OrdersView];
GO

CREATE VIEW [dbo].[OrdersView]
	AS SELECT O.OrderID,T.Name AS OrderType,O.OrderDate,C.Name as Customer,P.Name as Product,O.Quantity,O.TotalPrice FROM Orders O
INNER JOIN OrderType T
ON T.OrderTypeID=O.OrderTypeID
INNER JOIN Customer C
ON C.CustomerID=O.CustomerID
INNER JOIN Product P
ON P.ProductID=O.ProductId;
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES R WHERE R.ROUTINE_NAME='AddCustomer') 
Drop PROC AddCustomer;
GO

CREATE PROC AddCustomer 
@Name VARCHAR(70),
@Email VARCHAR(320),
@Phone VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON

INSERT INTO [dbo].[Customer]
           ([Name]
           ,[Email]
           ,[Phone])
     VALUES
           (@Name
           ,@Email
           ,@Phone)
END
GO

PRINT 'OfficeSuppliesSampleV3 database and objects created successfully!'