-- DELETE OLD TABLES  ----------------------------
IF OBJECT_ID('Review')
	IS NOT NULL DROP TABLE Review
GO

IF OBJECT_ID('RelatedProduct')
	IS NOT NULL DROP TABLE RelatedProduct
GO

--IF OBJECT_ID('SubCategoryProduct')
--	IS NOT NULL DROP TABLE SubCategoryProduct
--GO

IF OBJECT_ID('ProductSpecifics')
	IS NOT NULL DROP TABLE ProductSpecifics
GO

IF OBJECT_ID('Product')
	IS NOT NULL DROP TABLE Product
GO

IF OBJECT_ID('SubCategory')
	IS NOT NULL DROP TABLE SubCategory
GO

IF OBJECT_ID('Category')
	IS NOT NULL DROP TABLE Category
GO

IF OBJECT_ID('Business')
	IS NOT NULL DROP TABLE Business
GO

-- CREATE TABLES & INDEXES ----------------------------
CREATE TABLE Category
(
	categoryId INTEGER IDENTITY(1,1) PRIMARY KEY,
	categoryName VARCHAR(255) NOT NULL
);
GO 

CREATE TABLE SubCategory
(
	subCategoryId INTEGER IDENTITY(1,1) PRIMARY KEY,
	categoryId INTEGER NOT NULL, 
	subCategoryName VARCHAR(255) NOT NULL,
	subCategoryImgPath VARCHAR(255) NOT NULL,	
	isPublished BIT DEFAULT 0 NOT NULL,		
	FOREIGN KEY(categoryId) REFERENCES Category(categoryId)
);
GO 

CREATE TABLE Product
(
	productId INTEGER IDENTITY(1,1) PRIMARY KEY,
	productCode VARCHAR(255) NOT NULL, 
	subCategoryId INTEGER NOT NULL,
	productName VARCHAR(255) NOT NULL,
	productDescription VARCHAR(255),
	materials VARCHAR(255),
	maintenance VARCHAR(255),
	FOREIGN KEY(subCategoryId) REFERENCES SubCategory(subCategoryId),
);
GO 

--CREATE TABLE SubCategoryProduct
--(
--	productId INT,
--	subCategoryId INT,
--	FOREIGN KEY (productId) REFERENCES Product(productId),
--	FOREIGN KEY (subCategoryId) REFERENCES SubCategory(subCategoryId),
--	PRIMARY KEY (productId, subCategoryId)
--);
--


CREATE TABLE ProductSpecifics
(
	productSpecificsId INTEGER IDENTITY(1,1) PRIMARY KEY,	
	productId INTEGER NOT NULL,
	productTypeName VARCHAR(255), 
	price MONEY DEFAULT 0.00,						 

	salePrice MONEY DEFAULT 0.00,
	widthInches DECIMAL DEFAULT 0.0,
	heightInches DECIMAL DEFAULT 0.0,
	depthInches DECIMAL DEFAULT 0.0,
	color VARCHAR(255),

	isOnSale BIT DEFAULT 0 NOT NULL,
	saleStart DATETIME,
	saleEnd DATETIME,

	isPublished	BIT DEFAULT 0 NOT NULL,
	isNew BIT DEFAULT 1 NOT NULL,
	isFeatured	BIT DEFAULT 0 NOT NULL,

	imgPath VARCHAR(255) NOT NULL,
	alert VARCHAR(255),

	FOREIGN KEY(productId) REFERENCES Product(productId)
);
GO 

CREATE TABLE RelatedProduct
(
	primaryProductId INT, 
	relatedProductId INT,
	FOREIGN KEY (primaryProductId) REFERENCES ProductSpecifics(productSpecificsId),
	PRIMARY KEY (primaryProductId, relatedProductId)
);
GO

CREATE TABLE Review
(
	reviewId INTEGER IDENTITY(1,1) PRIMARY KEY,	
	productId INTEGER NOT NULL,
	reviewer VARCHAR(255) DEFAULT 'Anonymous' NOT NULL,
	reviewContent VARCHAR(255) NOT NULL,
	rating integer DEFAULT 5 NOT NULL,								-- ?? depends on proposal
	reviewTime DATETIME DEFAULT GETDATE() NOT NULL,
	isPublished BIT DEFAULT 0,
	FOREIGN KEY(productId) REFERENCES Product(productId)
);
GO 

CREATE TABLE Business
(
	businessID INTEGER IDENTITY(1,1) PRIMARY KEY,
	phone1 VARCHAR(255) NOT NULL,	
	phone2 VARCHAR(255) NOT NULL,	
	fax VARCHAR(255) NOT NULL,	
	email VARCHAR(255),
	address VARCHAR(255),	
	city  VARCHAR(255),
	province VARCHAR(255),
	country VARCHAR(255),
	postal VARCHAR(255),
	monday VARCHAR(255),
	tuesday VARCHAR(255),
	wednesday VARCHAR(255),
	thursday VARCHAR(255),
	friday VARCHAR(255),
	saturday VARCHAR(255),
	sunday VARCHAR(255),
	warranty  NVARCHAR(MAX),			
	qualityFurniture  NVARCHAR(MAX),
	saleInfo NVARCHAR(MAX)
);
GO 

---------------------------------
----- BEGIN TO CREATE INDEX -----
---------------------------------

IF EXISTS (SELECT name FROM sys.indexes WHERE name = 'productCode_idx') DROP INDEX productCode_idx ON Product;
GO
CREATE INDEX productCode_idx ON Product (productCode);

IF EXISTS (SELECT name FROM sys.indexes WHERE name = 'productName_idx') DROP INDEX productName_idx ON Product;
GO
CREATE INDEX productName_idx ON Product (productName);

IF EXISTS (SELECT name FROM sys.indexes WHERE name = 'userRole_idx') DROP INDEX userRole_idx ON AspNetUserRoles;
GO
CREATE INDEX userRole_idx ON AspNetUserRoles (UserId, RoleId);

IF EXISTS (SELECT name FROM sys.indexes WHERE name = 'user_idx') DROP INDEX user_idx ON AspNetUsers;
GO
CREATE INDEX user_idx ON AspNetUsers (UserName, Email);

----------------------------------
----- FINISH TO CREATE INDEX -----
----------------------------------


-- INSERT SAMPLE DATA ----------------------------

---- INSERT Category ----
INSERT INTO Category( categoryName ) VALUES( 'Living Room' ) ; 
INSERT INTO Category( categoryName ) VALUES( 'Dining Room' ) ; 
INSERT INTO Category( categoryName ) VALUES( 'Bedroom' ) ; 
INSERT INTO Category( categoryName ) VALUES( 'Office' ) ; 
INSERT INTO Category( categoryName ) VALUES( 'Accents' ) ; 


---- INSERT SubCategory ----
/*
INSERT INTO SubCategory( categoryId, subCategoryName, subCategoryImgUrl )  
	VALUES ( '1', 'Sofas', 
	'http://static.wixstatic.com/media/fa493d_32a67c20d3ef434c99bde2f0f9c4b4c7.jpg' ) ; 
*/
INSERT INTO SubCategory( categoryId, subCategoryName, subCategoryImgPath, isPublished )  
	VALUES ( 1, 'Sofas', 
	'/media/fa493d_32a67c20d3ef434c99bde2f0f9c4b4c7.jpg', 1 ) ; 
INSERT INTO SubCategory( categoryId, subCategoryName, subCategoryImgPath, isPublished )  
	VALUES ( 1, 'Sofabeds', 
	'/media/fa493d_e36139b6ebf7484594111e36ba460183.jpg', 1 ) ; 
INSERT INTO SubCategory( categoryId, subCategoryName, subCategoryImgPath, isPublished )  
	VALUES ( 1, 'Coffee Tables / End Tables', 
	'/media/fa493d_2b571db82e8d416b9fe45fd5cd25926e.jpg', 1 ) ; 
INSERT INTO SubCategory( categoryId, subCategoryName, subCategoryImgPath, isPublished )  
	VALUES ( 1, 'Bookcase', 
	'/media/fa493d_2b571f2328fds416b9fe453422db.jpg', 1 ) ;


---- INSERT Product ----

INSERT INTO Product( productCode, subCategoryId, productName, productDescription, 
		materials, maintenance ) 
	VALUES('Lily_B181', 2, 'Leather Sofa', 'Available in different color options.',
	 'Genuine leather', 'Use leather cleaner to remove spills ans stains by sponge.');

INSERT INTO Product( productCode, subCategoryId, productName, productDescription, 
		materials, maintenance ) 
	VALUES('Flow_B182', 1, 'Leather Sofa', 'Available in different color options.',
	 'Genuine leather', 'Use leather cleaner to remove spills ans stains by sponge.');

INSERT INTO Product( productCode, subCategoryId, productName, productDescription, 
		materials, maintenance ) 
	VALUES('Square_B185', 3, 'Leather Sofa', 'Available in different color options.',
	 'Genuine leather', 'Use leather cleaner to remove spills ans stains by sponge.');

INSERT INTO Product( productCode, subCategoryId, productName, productDescription, 
		materials, maintenance ) 
	VALUES('Square_B042', 2, 'birch veneer', 'Available in different color options.',
	 'Hardwood', 'Wipe clean using a damp cloth and a mild cleaner..');

INSERT INTO Product( productCode, subCategoryId, productName, productDescription, 
		materials, maintenance ) 
	VALUES('Billy_B071', 1, 'Bookcase', 'Available in different color options.',
	 'Hardwood', 'Adjustable shelves can be arranged according to your needs.');


---- INSERT ProductSpecifics ----

/*
INSERT INTO ProductSpecifics( productId, price, msrp, salePrice, widthInches, heightInches, depthInches,
		color, isOnSale, isPublished, isNew, isFeatured, imgUrl )
	VALUES(1, 199.99, 99.99, 39.99, 134, 40, 26, 'Brown', 1, 1, 1, 1, 
	'http://static.wixstatic.com/media/fa493d_22cd10b48e184c19aa405261e151cb2f.jpg');
*/

INSERT INTO ProductSpecifics( productId, productTypeName, price, salePrice, widthInches, heightInches, depthInches,
		color, isOnSale, saleStart, saleEnd, isPublished, isNew, isFeatured, imgPath )
	VALUES(1, 'XL', 199.99, 39.99, 134, 40, 26, 'Brown', 1, '2015-02-10', '2015-07-10', 1, 1, 1, 
	'/media/fa493d_22cd10b48e184c19aa405261e151cb2f.jpg');

INSERT INTO ProductSpecifics( productId, productTypeName, price, salePrice, widthInches, heightInches, depthInches,
		color, isOnSale, saleStart, saleEnd, isPublished, isNew, isFeatured, imgPath )
	VALUES(1, 'M', 279.99, 49.99, 81, 40, 26, 'Brown', 1, '2015-04-22', '2015-06-01', 1, 1, 1, 
	'/fa493d_22cd10b48e184c19aa405261e151cb2f.jpg');

INSERT INTO ProductSpecifics( productId, productTypeName, price, salePrice, widthInches, heightInches, depthInches,
		color, isOnSale, saleStart, saleEnd, isPublished, isNew, isFeatured, imgPath )
	VALUES(2, 'S', 255.99, 45.99, 152, 39, 24, 'black', 0, NULL, NULL, 1, 1, 1, 
	'/media/fa493d_0fd8b9b9050f4682a269937b4fb9e0d2.jpg');

INSERT INTO ProductSpecifics( productId, productTypeName, price, salePrice, widthInches, heightInches, depthInches,
		color, isOnSale, saleStart, saleEnd, isPublished, isNew, isFeatured, imgPath )
	VALUES(4, 'Double', 269.99, 65.99, 105.08, 90, 24, 'tomato', 0, NULL, NULL, 1, 1, 1, 
	'/media/fa493d32_0df_4682a26993fdfb9e0d2.jpg');

INSERT INTO ProductSpecifics( productId, productTypeName, price, salePrice, widthInches, heightInches, depthInches,
		color, isOnSale, saleStart, saleEnd, isPublished, isNew, isFeatured, imgPath )
	VALUES(5, 'King', 1089.99, 39.99, 112, 99, 52, 'grey', 1, '2015-03-02', '2015-08-02', 1, 1, 1, 
	'/media/fa493d_0fd8b9b9050f468gfd23fffs.jpg');


---- INSERT Review ----

INSERT INTO Review( productId, reviewer, reviewContent, rating ) 
	VALUES(1, 'Deanna Mcarthur', 'I bought it a few weeks ago and ...', 10 );
INSERT INTO Review( productId, reviewer, reviewContent, rating ) 
	VALUES(1, 'Bella Husband', 'The color is bright.', 10 );
INSERT INTO Review( productId, reviewer, reviewContent, rating ) 
	VALUES(1, 'Atefeh Hoskin', 'The leather is quite good.', 10 );

INSERT INTO Business( phone1, phone2, fax, email, address, 
	monday, tuesday, wednesday, thursday, friday, saturday, sunday,
	province, city, country, postal, warranty, qualityFurniture )
	VALUES('778-3535-4242', '664-8989-4747', '345-234-3211', 'info@stmoya.com', '#150-12111 Bridgeport Road',
	'10:00 am - 6:00 pm', '10:00 am - 6:00 pm', '10:00 am - 6:00 pm', '10:00 am - 6:00 pm',
	'10:00 am - 6:00 pm', '10:00 am - 6:00 pm', '11:00 am - 6:00 pm', 
	'BC', 'Richmond', 'Canada', 'V6V 1J4',
	'St.Moya offers a one year limited warranty on each product from the date of purchase. Our warranty only covers products that fails based on manufacturers defect in materials or workmanship. ',
	'Getting best quality furniture is quite within your budget'); 



---- INSERT RelatedProduct ----

INSERT INTO RelatedProduct VALUES(1, 4);
INSERT INTO RelatedProduct VALUES(1, 2);
INSERT INTO RelatedProduct VALUES(2, 5);
INSERT INTO RelatedProduct VALUES(1, 3);
INSERT INTO RelatedProduct VALUES(4, 5);
INSERT INTO RelatedProduct VALUES(5, 4);
INSERT INTO RelatedProduct VALUES(3, 4);
INSERT INTO RelatedProduct VALUES(2, 1);
INSERT INTO RelatedProduct VALUES(3, 2);


---- INSERT SubCategoryProduct ----

--INSERT INTO SubCategoryProduct VALUES(2, 2);
--INSERT INTO SubCategoryProduct VALUES(2, 4);
--INSERT INTO SubCategoryProduct VALUES(1, 2);
--INSERT INTO SubCategoryProduct VALUES(1, 1);;
--INSERT INTO SubCategoryProduct VALUES(4, 3);
--INSERT INTO SubCategoryProduct VALUES(3, 1);
--INSERT INTO SubCategoryProduct VALUES(2, 3);


-- SHOW DATA  ----------------------------
SELECT * FROM Category;
SELECT * FROM SubCategory;
SELECT * FROM Product;
SELECT * FROM ProductSpecifics;
SELECT * FROM Review;
SELECT * FROM Business; 
SELECT * FROM RelatedProduct; 
-- END --------------------------------