# 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.