# mysql -u root -p -h localhost 

DROP database IF EXISTS SecureStore;
CREATE DATABASE SecureStore;
USE SecureStore;

CREATE TABLE ProductSubCategory (
	ProdSubId INT NOT NULL AUTO_INCREMENT,
	ProdSubCat VARCHAR(64) NOT NULL,
	ProdSubName VARCHAR(64) NOT NULL,
	PRIMARY KEY (ProdSubId, ProdSubCat)
);

CREATE TABLE City ( 
	CityId INT NOT NULL AUTO_INCREMENT,
	CityName VARCHAR(64) NOT NULL UNIQUE,
	PostalCode INT(10) UNSIGNED NOT NULL UNIQUE,
	PRIMARY KEY (CityId)
);

CREATE TABLE Product ( 
	ProductId INT NOT NULL AUTO_INCREMENT,
	ProductSubCategory VARCHAR(64) NOT NULL,
	Cost FLOAT UNSIGNED NOT NULL, 
	Size VARCHAR(64) NOT NULL,
	Color VARCHAR(64) NOT NULL,
	Price FLOAT UNSIGNED NOT NULL,
	PRIMARY KEY (ProductId),
	CONSTRAINT fk_product_id_cat
	FOREIGN KEY (ProductId, ProductSubCategory) REFERENCES ProductSubCategory(ProdSubId, ProdSubCat) 
	ON DELETE RESTRICT
	ON UPDATE CASCADE
);

CREATE TABLE Customer (
	CustId INT NOT NULL AUTO_INCREMENT,
	CustCityId INT NOT NULL,
	FirstName VARCHAR(64) NOT NULL,
	LastName VARCHAR(64) NOT NULL,
	Phone VARCHAR(64) NOT NULL,
	Address VARCHAR(128) NOT NULL,
	PRIMARY KEY (CustId),
	CONSTRAINT fk_custcity_id
	FOREIGN KEY (CustCityId) REFERENCES City (CityId)
	ON DELETE RESTRICT
	ON UPDATE CASCADE
);

CREATE TABLE Store (
	StoreId INT NOT NULL AUTO_INCREMENT,
	StoreCityId INT NOT NULL,
	StoreName VARCHAR(64) NOT NULL,
	Phone VARCHAR(64) NOT NULL,
	PRIMARY KEY (StoreId),
	CONSTRAINT fk_storecity_id
	FOREIGN KEY (StoreCityId) REFERENCES City (CityId)
	ON DELETE RESTRICT
	ON UPDATE CASCADE
);

CREATE TABLE SalesPerson (
	SalesPersonId INT NOT NULL,
	SalesPersonStoreId INT NOT NULL,
	SalesFirstName VARCHAR(64) NOT NULL,
	SalesLastName VARCHAR(64) NOT NULL,
	PRIMARY KEY (SalesPersonId),
	CONSTRAINT fk_salespersonstore_id
	FOREIGN KEY (SalesPersonStoreId) REFERENCES Store (StoreId)
	ON DELETE RESTRICT
	ON UPDATE CASCADE
);

CREATE TABLE FactSales (
	SalesPersonId INT NOT NULL,
	ProductId INT NOT NULL,
	Quantity INT NOT NULL,
	
	OrderDateDay INT NOT NULL,
	OrderDateMonth INT NOT NULL,
	OrderDateYear INT NOT NULL,
	
	PRIMARY KEY (SalesPersonId, ProductId),
	CONSTRAINT fk_salesperson_id
	FOREIGN KEY (SalesPersonId) REFERENCES SalesPerson (SalesPersonId)
	ON DELETE RESTRICT
	ON UPDATE CASCADE,
	CONSTRAINT fk_product_id2
	FOREIGN KEY (ProductId) REFERENCES Product (ProductId)
	ON DELETE RESTRICT
	ON UPDATE CASCADE,
	CHECK (OrderDateDay BETWEEN 1 and 31),
	CHECK (OrderDateMonth BETWEEN 1 and 12),
	CHECK (OrderDateYear >= 1970)
);

CREATE TABLE Date ( 
	DayOfWeek VARCHAR(64) NOT NULL,
	DayOfMonth INT NOT NULL,
	Month INT NOT NULL,
	Year INT NOT NULL,
	CHECK (DayOfWeek in ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday')),
	CHECK (DayOfMonth BETWEEN 1 AND 31)	,
	CHECK (Month BETWEEN 1 and 12),
	Check (Year >= 1970)
);

-- Following the principle of least principle, every account is given write access only to specific tables of the database.
-- They are also given read (select) access on all other tables, since a more detailed access control policy was not taken into account.

-- The Sales manager needs to have read access the Customer, SalesPerson and FactSales table.
CREATE ROLE SalesManager;
GRANT SELECT ON SecureStore.* to SalesManager;
GRANT ALL ON SecureStore.Customer to SalesManage;
GRANT ALL ON SecureStore.SalesPerson to SalesManager; 
GRANT ALL ON SecureStore.FactSales to SalesManager;

-- The Product Manager needs to have access on the Product related tables.
CREATE ROLE ProductManager;
GRANT SELECT ON SecureStore.* to ProductManager;
GRANT ALL ON SecureStore.Product to ProductManager ;
GRANT ALL ON SecureStore.ProductSubCategory to ProductManager;

CREATE user nick;
GRANT SalesManager to nick;
CREATE user john;
GRANT ProductManager to john;

-- Login as nick:
mysql -u nick
show databases;  -- should show only information_schema
SELECT current_role; -- should be null
SET role ProductManager; -- should fail
SET role SalesManager; -- successful
show databases; -- SecureStore database appears
use SecureStore; 
-- Nick has write access to Customer/SalesPerson/FactSales and read access to everything
-- Similar scenario for john as a product manager.