use master
go

if exists ( select * from sys.databases where name = 'AdventureWorksDW_UTS' )
	drop database AdventureWorksDW_UTS;
go

create database AdventureWorksDW_UTS;
go


-- Simple Data Warehouses dont really need the full transac logs, they just take up space and will continue to grow forever
-- If you need to restore from a back up, u should just restore, and run ur batches again.

alter database AdventureWorksDW_UTS
SET RECOVERY SIMPLE
go

use AdventureWorksDW_UTS
go

CREATE TYPE dbo.Name FROM nvarchar(50) NULL;
CREATE TYPE dbo.Flag FROM bit NOT NULL;
CREATE TYPE dbo.OrderNumber FROM nvarchar(25) NULL
CREATE TYPE dbo.AccountNumber FROM nvarchar(15) NULL
go

-- Create Product Category table
CREATE TABLE dbo.DimProductCategory(
	ProductCategoryID int NOT NULL,
	EnglishProductCategoryName nvarchar(50) NOT NULL
 CONSTRAINT PK_DimProductCategory_ProductCategoryID PRIMARY KEY CLUSTERED 
	(
		ProductCategoryID ASC
	)
)
go
-- Create Sub Category table
CREATE TABLE dbo.DimProductSubcategory(
	ProductSubcategoryID int not NULL,
	EnglishProductSubcategoryName nvarchar(50) NOT NULL,
	ProductCategoryID int NOT NULL,
 CONSTRAINT PK_DimProductSubcategory_ProductSubcategoryID PRIMARY KEY CLUSTERED 
	(ProductSubcategoryID)
)
GO

-- We shouldnt have foreign constraints, because in a Data Warehouse, 
--if there is a missing relational value, we dont want to reject it, but rather say that it is unknown

--ALTER TABLE dbo.DimProductSubcategory  WITH CHECK 
--ADD  CONSTRAINT FK_DimProductSubcategory_DimProductCategory 
--	FOREIGN KEY(ProductCategoryID)
--	REFERENCES dbo.DimProductCategory (ProductCategoryID)
GO
CREATE TABLE DimProduct(
	ProductID int NOT NULL,
	Name dbo.Name NOT NULL,
	ProductNumber nvarchar(25) NOT NULL,
	Color nvarchar(15) NULL,
	SafetyStockLevel smallint NOT NULL,
	ReorderPoint smallint NOT NULL,
	StandardCost money NOT NULL,
	ListPrice money NOT NULL,
	Size nvarchar(5) NULL,
	SizeUnitMeasureCode nchar(3) NULL,
	WeightUnitMeasureCode nchar(3) NULL,
	Weight decimal(8, 2) NULL,
	DaysToManufacture int NOT NULL,
	ProductLine nchar(2) NULL,
	Class nchar(2) NULL,
	Style nchar(2) NULL,
	ProductSubcategoryID int NULL,
	ProductModelID int NULL,
	SellStartDate datetime NOT NULL,
	SellEndDate datetime NULL,
	DiscontinuedDate datetime NULL,
	rowguid uniqueidentifier ROWGUIDCOL  NOT NULL,
	ModifiedDate datetime NOT NULL,
	 CONSTRAINT PK_Product_ProductID PRIMARY KEY CLUSTERED 
	(
		ProductID ASC
	)
)
go
if exists (
	select *
	from sys.tables
	where name = 'DimDate')
		drop table DimDate
go

--Time Dimension/Date Dimension 
CREATE TABLE dbo.DimDate(
	DateKey int IDENTITY(1,1) NOT NULL,
	FullDateAlternateKey datetime NOT NULL,
	DayNumberOfWeek tinyint NULL,
	EnglishDayNameOfWeek nvarchar(10) NULL,
	DayNumberOfMonth tinyint NULL,
	DayNumberOfYear smallint NULL,
	WeekNumberOfYear tinyint NULL,
	EnglishMonthName nvarchar(10)  NULL,
	MonthNumberOfYear tinyint NULL,
	CalendarQuarter tinyint NULL,
	CalendarYear char(4)  NULL,
	FiscalQuarter tinyint NULL,
	FiscalYear char(4)  NULL,
 CONSTRAINT AK_DimDate_DateKey UNIQUE NONCLUSTERED 
	(DateKey),
 CONSTRAINT PK_DimTime_FullDateAlternateKey PRIMARY KEY CLUSTERED 
	(FullDateAlternateKey)
)
go
if exists ( 
	select *
	from sys.procedures
	where name = 'procDimDateInsert')
	DROP PROCEDURE procDimDateInsert
GO
create procedure procDimDateInsert (
	@DateVal datetime,
	@Days int)
as
begin
	declare @DateCount int
	set @DateCount = 0
	while @DateCount < @Days
	begin
	insert into DimDate
	(
		-- DateKey, out as it is an identity column
		FullDateAlternateKey,
		DayNumberOfWeek,
		EnglishDayNameOfWeek,
		DayNumberOfMonth,
		DayNumberOfYear,
		WeekNumberOfYear,
		EnglishMonthName,
		MonthNumberOfYear,
		CalendarQuarter,
		CalendarYear,
		FiscalQuarter,
		FiscalYear)
	select @DateVal, -- FullDateAlternateKey
		datepart(dw, @DateVal), -- DayNumberOfWeek
		datename(dw, @DateVal), -- EnglishDayNameOfWeek,
		datepart(dd, @DateVal),	-- DayNumberOfMonth,
		datepart(dy, @DateVal), -- DayNumberOfYear,
		datepart(wk, @DateVal), -- WeekNumberOfYear,
		datename(mm, @DateVal), -- EnglishMonthName,
		datepart(mm, @DateVal), -- MonthNumberOfYear,
		datepart(qq, @DateVal), -- CalendarQuarter,
		datepart(yyyy, @DateVal), -- CalendarYear,
		case when (datepart( qq, @DateVal)+2)>4 then -4 else 0 end + (datepart( qq, @DateVal)+2), -- FiscalQuarter,
		datepart(yyyy, @DateVal) -
			case 
				when Month(@DateVal) <7 then 1
				else 0
			end
			-- FiscalYear
		set @DateVal = @DateVal + 1
		set @DateCount = @DateCount + 1
	end
end
go

set dateformat dmy
declare	@DateVal datetime, @Days int
set @DateVal = '01/01/1990'
set @Days = 366 * 30
exec procDimDateInsert @DateVal, @Days
go

-- Generate Sales Order Header Table
CREATE TABLE SalesOrderHeader(
	SalesOrderID int NOT NULL,
	RevisionNumber tinyint NOT NULL,
	OrderDate datetime NOT NULL,
	OrderDateKey int null,
	DueDate datetime NOT NULL,
	ShipDate datetime NULL,
	Status tinyint NOT NULL ,
	OnlineOrderFlag dbo.Flag NOT NULL,
	SalesOrderNumber nvarchar(25),
	PurchaseOrderNumber dbo.OrderNumber NULL,
	AccountNumber dbo.AccountNumber NULL,
	CustomerID int NOT NULL,
	ContactID int NOT NULL,
	SalesPersonID int NULL,
	TerritoryID int NULL,
	BillToAddressID int NOT NULL,
	ShipToAddressID int NOT NULL,
	ShipMethodID int NOT NULL,
	CreditCardID int NULL,
	CreditCardApprovalCode varchar(15) ,
	CurrencyRateID int NULL,
	SubTotal money NOT NULL ,
	TaxAmt money NOT NULL,
	Freight money NOT NULL,
	TotalDue money not null,
	Comment nvarchar(128),
	rowguid uniqueidentifier ROWGUIDCOL  NOT NULL,
	ModifiedDate datetime NOT NULL,
 CONSTRAINT PK_SalesOrderHeader_SalesOrderID PRIMARY KEY CLUSTERED 
(
	SalesOrderID ASC
)
)
go
CREATE TABLE SalesOrderDetail(
	SalesOrderID int NOT NULL,
	SalesOrderDetailID int NOT NULL,
	CarrierTrackingNumber nvarchar(25) NULL,
	OrderQty smallint NOT NULL,
	ProductID int NOT NULL,
	SpecialOfferID int NOT NULL,
	UnitPrice money NOT NULL,
	UnitPriceDiscount money NOT NULL ,
	LineTotal  AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),
	rowguid uniqueidentifier ROWGUIDCOL  NOT NULL ,
	ModifiedDate datetime NOT NULL ,
 CONSTRAINT PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID PRIMARY KEY CLUSTERED 
(
	SalesOrderID ASC,
	SalesOrderDetailID ASC
)
)

GO
use AdventureWorksDW_UTS
go
if exists (
	select *
	from sys.procedures
	where name = 'procDWInitialize' )
	drop procedure procDWInitialize
go

create procedure procDWInitialize as
begin
	truncate table dbo.SalesOrderDetail;
	truncate table dbo.SalesOrderHeader;
	truncate table dbo.DimProduct;
	truncate table dbo.DimProductSubcategory;
	truncate table dbo.DimProductCategory;
end;
go