DROP TABLE Product CASCADE;
DROP TABLE Product_Type CASCADE;
DROP TABLE Product_Location CASCADE;
DROP TABLE Users CASCADE;

CREATE TABLE Users(
    User_ID serial PRIMARY KEY,
    Username VARCHAR(20) CHECK(CHAR_LENGTH(Username) >= 5),
    Password VARCHAR(30) CHECK(CHAR_LENGTH(Password) >= 8),
    Privelege INT NOT NULL,
    FName VARCHAR(20) NOT NULL,
    LName VARCHAR(20) NOT NULL,
    Address VARCHAR(30) DEFAULT(''),
    Address2 VARCHAR(30) NOT NULL,
    City VARCHAR(15) NOT NULL,
    State VARCHAR(2) NOT NULL,
    Zip VARCHAR(10) CHECK(Zip SIMILAR TO '\\d\\d\\d\\d\\d(\\-\\d\\d\\d\\d)*'),
    Email VARCHAR(50) CHECK(Email SIMILAR TO '[a-zA-Z0-9_\\.]+@(([a-zA-Z0-9_]+\\.)+)([a-zA-Z][a-zA-Z]+)' OR Email = NULL)
);

CREATE TABLE Product_Type(
    Type_ID serial PRIMARY KEY,
    Name VARCHAR(30)
);

CREATE TABLE Product(
    Product_ID serial PRIMARY KEY,
    Product_Name VARCHAR(30) NOT NULL,
    Product_Description VARCHAR(250) NOT NULL DEFAULT(''),
    Available INT NOT NULL CHECK(Available >= 0),
    Type_ID INT,
    FOREIGN KEY (Type_ID) REFERENCES Product_Type(Type_ID)
);

CREATE TABLE Product_Location(
    Location_ID serial PRIMARY KEY,
    Product_ID INT,
    Owner_ID INT,
    Quantity INT NOT NULL CHECK(Quantity > 0),
    FOREIGN KEY (Product_ID) REFERENCES Product(Product_ID),
    FOREIGN KEY (Owner_ID) REFERENCES Users(User_ID)
);

INSERT INTO Users (Username, Password, Privelege, FName, LName, Address, Address2, City, State, Zip, Email) VALUES('Schmeev', 'ilovemesomuch', 10, 'Steve', 'Shrum',
    '211 Blossom Lane', '', 'Sewickley', 'PA', '15143', 'steevshrum@yahoo.com');
INSERT INTO Users (Username, Password, Privelege, FName, LName, Address, Address2, City, State, Zip, Email) VALUES('Vhyrrimyr', 'gameloss', 10, 'Ian', 'McKissick',
    '118 Covewood Drive', '', 'Butler', 'PA', '16001-1303', 'vhyrrimyr@gmail.com');
INSERT INTO Users (Username, Password, Privelege, FName, LName, Address, Address2, City, State, Zip, Email) VALUES('Valarnin', 'asdfqwer', 10, 'Rob', 'Kontz',
    '1014 Sheffield St', 'Apt 313', 'Pittsburgh', 'PA', '15233', 'valarnin@gmail.com');
INSERT INTO Users (Username, Password, Privelege, FName, LName, Address, Address2, City, State, Zip, Email) VALUES('Thatguy', 'hiimthatguy', 5, 'That', 'Guy',
    '9001 That Place', '', 'Hell', 'MI', '48169', 'thatguy@example.com');
INSERT INTO Users (Username, Password, Privelege, FName, LName, Address, Address2, City, State, Zip, Email) VALUES('TheHighlander', 'therecanbeonly', 1, 'Christopher', 'Lambert',
    '314 Scotland Yard', '', 'Scotland', 'SC', '11111', 'lambert@highland.er');

INSERT INTO Product_Type (Name) VALUES('Chairs');
INSERT INTO Product_Type (Name) VALUES('Tables');
INSERT INTO Product_Type (Name) VALUES('Utensils');
INSERT INTO Product_Type (Name) VALUES('Couches');
INSERT INTO Product_Type (Name) VALUES('Beds');

INSERT INTO Product (Product_Name, Product_Description, Available, Type_ID) VALUES('Sexy Chair', 'Damn, this chair is SEXY', 2, 1);
INSERT INTO Product (Product_Name, Product_Description, Available, Type_ID) VALUES('Black Table', 'This table stole my TV', 5, 2);
INSERT INTO Product (Product_Name, Product_Description, Available, Type_ID) VALUES('Spork', 'The fanciest of utensils', 3, 3);
INSERT INTO Product (Product_Name, Product_Description, Available, Type_ID) VALUES('Pimp Couch', '+40 CHA', 1, 4);
INSERT INTO Product (Product_Name, Product_Description, Available, Type_ID) VALUES('Sleeping Bag', 'It''s a bed...IN A BAG', 1, 5);

INSERT INTO Product (Product_Name, Product_Description, Available, Type_ID) VALUES('Stool', 'No, not from a baby', 12, 1);
INSERT INTO Product (Product_Name, Product_Description, Available, Type_ID) VALUES('D&D Table', '5% chance of natural 20', 2, 2);
INSERT INTO Product (Product_Name, Product_Description, Available, Type_ID) VALUES('Knife', 'For all your stabbing needs', 10, 3);
INSERT INTO Product (Product_Name, Product_Description, Available, Type_ID) VALUES('The Sofa', 'It''s Sofaking great!', 3, 4);
INSERT INTO Product (Product_Name, Product_Description, Available, Type_ID) VALUES('Bale of Hay', 'Does not include throat cancer', 25, 5);

INSERT INTO Product_Location (Product_ID, Owner_ID, Quantity) VALUES(3, 1, 2);
INSERT INTO Product_Location (Product_ID, Owner_ID, Quantity) VALUES(1, 5, 1);
INSERT INTO Product_Location (Product_ID, Owner_ID, Quantity) VALUES(5, 2, 5);
INSERT INTO Product_Location (Product_ID, Owner_ID, Quantity) VALUES(2, 4, 2);
INSERT INTO Product_Location (Product_ID, Owner_ID, Quantity) VALUES(4, 3, 5);

INSERT INTO Product_Location (Product_ID, Owner_ID, Quantity) VALUES(8, 1, 2);
INSERT INTO Product_Location (Product_ID, Owner_ID, Quantity) VALUES(9, 5, 7);
INSERT INTO Product_Location (Product_ID, Owner_ID, Quantity) VALUES(7, 2, 5);
INSERT INTO Product_Location (Product_ID, Owner_ID, Quantity) VALUES(10, 4, 8);
INSERT INTO Product_Location (Product_ID, Owner_ID, Quantity) VALUES(6, 3, 21);