USE [master]
GO

CREATE DATABASE [Shop]
GO

USE [Shop]
GO

CREATE TABLE Promotions (
    Terms VARCHAR(320),
    Multiplier FLOAT(3),
    Availability VARCHAR(3),
    PromotionID int identity not null,
    PRIMARY KEY (PromotionID)
);
GO

CREATE TABLE Providers (
    FirmID int identity not null,
    Email VARCHAR(320),
    PhoneNumber char(12) not null,
    PRIMARY KEY (FirmID)
);
GO

CREATE TABLE Products (
    ProductName VARCHAR(320),
    ProductID int identity not null,
    Cost FLOAT(20),
    StorageInfo VARCHAR(140),
    ProductAmount int,
    Terms VARCHAR(320),
    PromotionID int,
    FirmID int not null,
    PRIMARY KEY (ProductID),
    FOREIGN KEY (FirmID) REFERENCES Providers(FirmID)
);
GO

CREATE TABLE Users (
    FullName VARCHAR(80),
    Email VARCHAR(320),
    PhoneNumber char(12) not null,
    UserID int identity not null,
    PRIMARY KEY (UserID)
);
GO


CREATE TABLE Orders (
    ExecutionStatus VARCHAR(20) not null,
    DeliveryInfo VARCHAR(100) not null,
    OrderDate DATETIME not null,
    OrderID int not null,
    UserID int not null,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (UserID) REFERENCES Users(UserID)
);
GO

CREATE TABLE OrderDetailing (

    TotalCost AS (Price * Quantity) PERSISTED,
    Quantity int not null,
    OrderID int,
    ProductID int,
    Price int not null,
    PRIMARY KEY (OrderID, ProductID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);
GO




-- Providers

INSERT INTO Providers
VALUES ('123@ms.ua','89995380535');
GO

INSERT INTO Providers
VALUES ('12321@mamamam.ua','89995380536');
GO

INSERT INTO Providers
VALUES ('123321123321123321@mamamam.ua','89995380537');
GO

INSERT INTO Promotions
VALUES ('If product is available for promotion then user gets 20% discount', '0.8', 'Yes');
GO

INSERT INTO Promotions
VALUES ('If product is available for promotion then user gets 40% discount', '0.6', 'No');
GO

INSERT INTO Promotions
VALUES ('If product is available for promotion then user gets 50% discount', '0.5', 'No');
GO


INSERT INTO Products
VALUES ('Apple MacBook Air i5 1.6/8Gb/128Gb SSD Gold (MREE2RU/A)', '104990', 'Available', '8', 'Available for any promotions', '1', '1');
GO

INSERT INTO Products
VALUES ('Apple MacBook Air i5 1.6/8Gb/256Gb SSD Space Grey MRE92', '120990', 'Not available', '0', 'Available for any promotions', '1', '1');
GO

INSERT INTO Products
VALUES ('Apple MacBook Air 13 i5 1.8/8Gb/128SSD (MQD32RU/A)', '69790', 'Available', '6', 'Not available', '1', '1');
GO