CREATE DATABASE makeapp;
USE makeapp;


CREATE TABLE Category_ (
    CategoryName                VARCHAR(30) PRIMARY KEY
);


CREATE TABLE Color (
    ColorHEXCode                VARCHAR(6) PRIMARY KEY
);

CREATE TABLE Texture (
    TextureName                 VARCHAR(30) PRIMARY KEY
);

CREATE TABLE Vegan (
    IsVegan                     VARCHAR(1) PRIMARY KEY
);

CREATE TABLE Producer (
    ProducerID                  INT PRIMARY KEY,
    ProducerName                VARCHAR(30),
    WebSite                     VARCHAR(100)
);

CREATE TABLE Product (
    ProductID                   INT PRIMARY KEY,
    ProductName                 VARCHAR(30),
    ProducerID                  INT,
    ColorHEXCode                VARCHAR(6),
    TextureName                 VARCHAR(30),
    IsVegan                     VARCHAR(1),
    CategoryName                VARCHAR(30),
    FOREIGN KEY (ProducerID)    REFERENCES Producer (ProducerID),
    FOREIGN KEY (ColorHEXCode)  REFERENCES Color (ColorHEXCode),
    FOREIGN KEY (TextureName)   REFERENCES Texture (TextureName),
    FOREIGN KEY (IsVegan)       REFERENCES Vegan (IsVegan),
    FOREIGN KEY (CategoryName)  REFERENCES Category_ (CategoryName)
);

CREATE TABLE Wishlist (
    WishlistID                  INT PRIMARY KEY
);

CREATE TABLE Collection (
    CollectionID                INT PRIMARY KEY
);

CREATE TABLE Userr (
    UserID                      INT PRIMARY KEY,
    UserName                    VARCHAR(50),
    WishlistID                  INT,
    CollectionID                INT,
    FOREIGN KEY (WishlistID)    REFERENCES Wishlist(WishlistID),
    FOREIGN KEY (CollectionID)  REFERENCES Collection(CollectionID)
);

CREATE TABLE ProductWishlist (
    ProductID                   INT,
    WishlistID                  INT,
    FOREIGN KEY (ProductID)     REFERENCES Product(ProductID),
    FOREIGN KEY (WishlistID)    REFERENCES Wishlist(WishlistID),
    PRIMARY KEY                 (ProductID, WishlistID)
);

CREATE TABLE ProductCollection (
    ProductID                   INT,
    CollectionID                INT,
    FOREIGN KEY (ProductID)     REFERENCES Product(ProductID),
    FOREIGN KEY (CollectionID)  REFERENCES Collection(CollectionID),
    PRIMARY KEY                 (ProductID, CollectionID)
);

CREATE TABLE Review (
    ReviewID                    INT PRIMARY KEY,
    ReviewText                  VARCHAR(1000),
    ProductID                   INT,
    UserID                      INT,
    FOREIGN KEY (ProductID)     REFERENCES Product(ProductID),
    FOREIGN KEY (UserID)        REFERENCES Userr(UserID)
);