CREATE TABLE Clothes (
    ClothingID INT NOT NULL,
    Brand VARCHAR(20) NOT NULL,
    Model VARCHAR(20) NOT NULL,
    Country VARCHAR(20) NOT NULL,
    ArticleNr INT NOT NULL,
    Weight INT NOT NULL, # Grams
    CSize VARCHAR(20) NOT NULL,
    SalesPrice INT NOT NULL,
    SupplierPrice INT NOT NULL,
    Color VARCHAR(20) NOT NULL,
    AmmountLeft INT NOT NULL,
    Category VARCHAR(20) NOT NULL,
    AmmountSold INT NOT NULL,

    PRIMARY KEY(ClothingID)
);
# Det är ju egentligen ingen skillnad på ClothingID och ArticleNr, behöver vi båda?
INSERT INTO Clothes VALUES
                            (0, 'Ralph Lauren', 'Oxford Shirt', 'United States', 0, 300, 'L', 900, 500, 'White', 10, 'Shirts Men', 0),
                           (1,'Gant', 'Collage','United Kingdom',1, 600, 'L', 1100, 750, 'Red', 15, 'Shits Women', 0),
                           (2, 'Gant', 'Oxford Shirt', 'United Kingdom', 2, 400, 'M', 999 , 600, 'Blue', 15, 'Shirts Men', 0),
                           (3, 'Sail Racing', 'T-shirt', 'United Kingdom', 3, 500, 'S', 500, 250, 'Black', 10, 'Shirts Women', 0),
                           (4, 'Ralph Lauren', 'T-Shirt', 'United States', 4, 600, 'XL', 800, 500, 'White', 10, 'Shirts Women', 0),
                           (5, 'Sail Racing', 'Oxford Shirt', 'United Kingdom', 5, 900, 'M', 900, 500, 'White', 10, 'Shirts Men', 0),
                           (6, 'Ralph Lauren', 'Sweater', 'United States', 6, 1010, 'L', 1200, 700, 'Blue', 5, 'Sweater Men', 0),
                           (7,'Gant', 'Sweater','United Kingdom',7, 1200, 'XL', 1300, 700, 'Green', 5, 'Sweater Women', 0),
                           (8, 'Gant', 'Jacket', 'United Kingdom', 8, 1400, 'M', 3700 , 2500, 'Black', 15, 'Jacket Men', 0),
                           (9, 'Sail Racing', 'T-shirt', 'United States', 9, 500, 'S', 700, 300, 'Yellow', 15, 'Shirts Women', 0),
                           (10, 'Ralph Lauren', 'Collage' , 'United States', 10, 1100, 'XL', 1500, 900, 'White', 10, 'Sweater Men', 0);

SELECT * FROM Clothes;

CREATE TABLE Orders (
    OrderNr INT NOT NULL,
    PNr VARCHAR(20) NOT NULL, # Social security number
    TotalSum INT NOT NULL,
    DeliveryHomeAdress VARCHAR(30) NOT NULL,
    DeliveryPostAdress VARCHAR(20) NOT NULL,
    Country VARCHAR(20) NOT NULL,
    TotalWeight FLOAT NOT NULL,
    OrderDate DATE NOT NULL,
    OrderTime TIME NOT NULL,
    DiscountID INT NOT NULL,
    ClothingID INT NOT NULL,

    PRIMARY KEY(OrderNr, PNr)#,
    #FOREIGN KEY (OrderNr) REFERENCES Revenue(OrderNr),
    #FOREIGN KEY (Pnr) REFERENCES Customer(PNr),
    #FOREIGN KEY (DiscountID) REFERENCES Discount(DiscountID)
);

INSERT INTO Orders VALUES
                          (0, '9603074833', 900, 'Gamla Infartsvägen 3b', '371 41 Karlskrona', 'Sweden', 300.0, NOW(), NOW(), 0, 0),
                          (1, '9804208172', 2000, 'Valhallavägen 2', '371 40 Karlskrona', 'Sweden', 1500.0, NOW(),NOW(),800, 1),
                          (2, '9402208182', 500, 'Infartsvägen 3b', '371 41 Karlskrona', 'Sweden', 400.0, NOW(), NOW(), 0, 2),
                          (3, '9803074823', 5000, 'Lotsvägen 7', '541 55 Alingsas', 'Sweden', 2000.0, '2019-04-18','12:04:18', 0, 3),
                          (4, '9209234823', 1100, 'Arklimästaregatan 25b' , '371 42 Karlskrona', 'Sweden', 1100.0, NOW(), NOW(), 0, 4),
                          (5, '9012075431', 900, 'Gamla Djuret 31b', '371 45 Karlskrona', 'Sweden', 500.0, NOW(), NOW(), 0, 5),
                          (6, '0503071234',15000, 'Andralang 2b', '343 41 Stockholm', 'Sweden', 5000.0, NOW(), NOW(), 0, 6),
                          (7, '0211278723', 2200, 'Seglaregatan 3', '441 51 Alingsas', 'Sweden', 1500.0, NOW(), NOW(), 0, 7);

SELECT * FROM Orders;

CREATE TABLE Revenue (
    OrderNr INT NOT NULL,
    TotalSum INT NOT NULL,
    TransortationCost INT NOT NULL,
    SupplierPrice INT NOT NULL,
    LastReturnDate DATE NOT NULL,

    PRIMARY KEY(OrderNr)
);

INSERT INTO Revenue VALUES(0, 900, 50, 550, DATE_ADD(NOW(), INTERVAL 14 DAY));

SELECT * FROM Revenue;

CREATE TABLE Customer (
    PNr VARCHAR(20) NOT NULL,
    FName VARCHAR(20) NOT NULL,
    LName VARCHAR(20) NOT NULL,
    MobileNr VARCHAR(20) NOT NULL,
    EMail VARCHAR(30) NOT NULL,
    Gender VARCHAR(20) NOT NULL,
    HomeAdress VARCHAR(30) NOT NULL,
    PostAdress VARCHAR(20) NOT NULL,
    ShoppedSum FLOAT NOT NULL,

    CONSTRAINT PK
    PRIMARY KEY (PNr)
);

INSERT INTO Customer VALUES
                            ('9603074833', 'Anton', 'Karlsson', '0723535592', 'antonamkarlsson@hotmail.com', 'Male', 'Gamla Infartsvägen 3b', '371 41 Karlskrona', 900),
                            ('9804208172', 'Emil', 'Einerskog', '0707453881', 'emil@einerskog.se', 'Male', 'Valhallavägen 2', '371 40 Karlskrona', 1200),
                            ('9402208182', 'Lars', 'Svensson', '0708233212', 'larssvensson@hotmail.com', 'Male', 'Infartsvägen 3b', '371 41 Karlskrona', 500),
                            ('9803074823', 'Markus', 'Nilsson', '0707083421', 'markusnilsson@hotmail.com', 'Male', 'Lotsvägen 7', '541 55 Alingsas', 5000),
                            ('9209234823', 'Sven', 'Svensson', '0745621212', 'svensvensson@hotmail.com', 'Male', 'Arklimästaregatan 25b' , '371 42 Karlskrona', 1100),
                            ('9012075431', 'John', 'Ren', '070732142122', 'johnren@hotmail.com', 'Male', 'Gamla Djuret 31b', '371 45 Karlskrona', 900),
                            ('0503071234', 'Lisa', 'Axelsson', '07235421592', 'lisaaxelsson@hotmail.com', 'Female', 'Andralang 2b', '343 41 Stockholm', 15000),
                            ('0211278723', 'Felicia', 'Abrahamsson', '0723532123', 'FeliciaAbrahamsson@hotmail.com', 'Female', 'Seglaregatan 3', '441 51 Alingsas', 900);

SELECT * FROM Customer;

CREATE TABLE Discount (
    DiscountID INT NOT NULL,
    CodeName VARCHAR(20) NOT NULL,
    AmountDiscount FLOAT NOT NULL,
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL,

    PRIMARY KEY (DiscountID)
);

INSERT INTO Discount VALUES
                            (0, 'Easter Deal', 0.8, "2019-04-18", "2019-04-22"),
                            (1, 'Summer Deal', 0.6, "2019-06-10", "2019-08-12"),
                            (2, 'Spring Deal', 0.7, "2019-03-18", "2019-04-14"),
                            (3, 'New Customer Deal', 0.9, NOW(),DATE_ADD(NOW(), INTERVAL 1 DAY ));

SELECT * FROM Discount;

CREATE TABLE BoughtClothes (
    OrderNr INT NOT NULL,
    ClothingID INT NOT NULL,

    PRIMARY KEY (OrderNr,ClothingID)#,
    #FOREIGN KEY (ClothingID) REFERENCES Clothes(ClothingID),
    #FOREIGN KEY (OrderNr) REFERENCES Orders(OrderNr)
);

INSERT INTO BoughtClothes VALUES(0, 0);

SELECT * FROM BoughtClothes;

#VIEWS
CREATE VIEW CheapClothes AS
    SELECT * FROM Clothes WHERE SalesPrice < 500;

SELECT * FROM CheapClothes;

CREATE VIEW BestSeller AS
    SELECT * FROM Clothes GROUP BY AmmountSold DESC LIMIT 10;

SELECT * FROM BestSeller;

#CustomerView
#BuyerView
#CorporateView ska göras

DROP TABLE BoughtClothes;
DROP TABLE Clothes;
DROP TABLE Orders;
DROP TABLE Revenue;
DROP TABLE Customer;
DROP TABLE Discount;