CREATE TABLE ACCOUNT(
UserID Number NOT NULL,
ScreenName VARCHAR2,
Email VARCHAR2,
Address VARCHAR2,
DOB Date,
BALANCE DECIMAL(6,2) DEFAULT 0,
CONSTRAINT ACCOUNT_PK PRIMARY KEY (UserID));


CREATE TABLE FRIENDSHIP(
UserID1 NUMBER,
UserID2 NUMBER,
FriendshipDate DATE,
CONSTRAINT FRIENDSHIP_PK PRIMARY KEY (UserID1, UserID2)); 

CREATE TABLE STEAMGROUP(
GroupID NUMBER NOT NULL,
Name VARCHAR2,
Description VARCHAR2,
CONSTRAINT GROUP_PK PRIMARY KEY (GroupID)); 

CREATE TABLE MEMBERSHIP(
DateJoined DATE,
UserID NUMBER,
GroupID NUMBER,
CONSTRAINT MEMBERSHIP_FK1 FOREIGN KEY (UserID) REFERENCES ACCOUNT(UserID),
CONSTRAINT MEMBERSHIP_FK2 FOREIGN KEY (GroupID) REFERENCES STEAMGROUP(GroupID));

CREATE TABLE EMPLOYEE(
EmployeeID NUMBER,
Name VARCHAR2,
HireDate DATE,
TerminationDate DATE,
Skill VARCHAR2,
CONSTRAINT EMPLOYEE_PK PRIMARY KEY (EmployeeID));

CREATE TABLE SUPPORT_TICKET(
SupportTicketID NUMBER,
DateFiled DATE,
DateResolved DATE,
Reason VARCHAR2,
EmployeeID NUMBER,
UserID NUMBER,
CONSTRAINT SUPPORT_TICKET_PK PRIMARY KEY (SupportTicketID),
CONSTRAINT SUPPORT_TICKET_FK1 FOREIGN KEY (EmployeeID) REFERENCES EMPLOYEE(EmployeeID),
CONSTRAINT SUPPORT_TICKET_FK2 FOREIGN KEY (UserID) REFERENCES ACCOUNT(UserID));

CREATE TABLE ITEM(
ItemID NUMBER,
ItemType VARCHAR2,
ItemPrice DECIMAL(6,2),
CONSTRAINT ITEM_PK PRIMARY KEY (ItemID));

CREATE TABLE INVENTORY(
InventoryID NUMBER,
DateAcquired DATE,
UserID NUMBER,
ItemID NUMBER,
CONSTRAINT INVENTORY_PK PRIMARY KEY (InventoryID),
CONSTRAINT INVENTORY_FK1 FOREIGN KEY (UserID) REFERENCES ACCOUNT(UserID),
CONSTRAINT INVENTORY_FK2 FOREIGN KEY (ItemID) REFERENCES ITEM(ItemID));

CREATE TABLE PURCHASE(
UserID Number NOT NULL,
ProductID Number NOT NULL,
DateOfPurchase TIMESTAMP,
PurchaseAmount DECIMAL(6,2) DEFAULT 0,
CONSTRAINT PURCHASE_PK PRIMARY KEY (ProductID, UserID));

CREATE TABLE PRODUCT(
ProductID NUMBER NOT NULL,
Title VARCHAR2 NOT NULL,
Description VARCHAR2,
Price DECIMAL(5,2) DEFAULT 0,
ReleaseDate DATE,
ProductType VARCHAR2 NOT NULL,
CONSTRAINT PRODUCT_PK PRIMARY KEY (ProductID)); 

CREATE TABLE PUBLISHER(
PublisherID NUMBER NOT NULL,
PublisherName VARCHAR2,
YearEstablished NUMBER,
CONSTRAINT PUBLISHER_PK PRIMARY KEY (PublisherID));

CREATE TABLE PROGRAM(
ProductID NUMBER NOT NULL,
ProgramType VARCHAR2,
PublisherID NUMBER,
CONSTRAINT PROGRAM_FK FOREIGN KEY (PublisherID) REFERENCES PUBLISHER(PublisherID)
CONSTRAINT PROGRAM_PK PRIMARY KEY (ProductID)); 

CREATE TABLE GAME(
ProductID NUMBER NOT NULL,
Genre VARCHAR2,
Rating VARCHAR2,
CONSTRAINT GAME_PK PRIMARY KEY (ProductID));

CREATE TABLE SOFTWARE(
ProductID NUMBER NOT NULL,
Category VARCHAR2,
CONSTRAINT SOFTWARE_PK PRIMARY KEY (ProductID));

CREATE TABLE MOVIE(
ProductID NUMBER NOT NULL,
RunTime TIME,
Genre VARCHAR2,
Rating VARCHAR2,
CONSTRAINT ITEM_PK PRIMARY KEY (ProductID));

INSERT INTO ACCOUNT VALUES 
(100001, 'Gamer1', 'gamer1@gmail.com', '1234 Gamer Ln Ames, IA 50010', '1995-01-01', 0), 
(100002, 'Gamer2', 'gamer3@gmail.com', '1235 Gamer Ln Ames, IA 50010', '1995-01-01', -10.00),
(100003, 'Gamer3', 'gamer3@gmail.com', '1236 Gamer Ln Ames, IA 50010', '1995-01-01', 10.00),
(100004, 'Gamer4', 'gamer4@gmail.com', '1237 Gamer Ln Ames, IA 50010', '1995-01-01', 9999.99),
(100005, 'Gamer5', 'gamer5@gmail.com', '1238 Gamer Ln Ames, IA 50010', '1995-01-01', 100.00);

INSERT INTO FRIENDSHIP VALUES
(100001,100002, '2015-04-13'),
(100002,100003, '2015-04-13'),
(100005,100003, '2015-04-15'),
(100003,100001, '2015-04-13');

INSERT INTO STEAMGROUP VALUES
(1000, 'Gamers Unite', 'Place for gamers to make friends and play games'),
(1001, 'MIS320', 'Group for students taking MIS320 to unwind and play some games');

INSERT INTO MEMBERSHIP VALUES
('2015-04-13', 100001, 1000),
('2015-04-15', 100002, 1000),
('2016-04-19', 100004, 1001),
('2015-04-28', 100005, 1001);

INSERT INTO EMPLOYEE VALUES
(100, 'Employee1', '2008-11-05', NULL, 'Customer Service') ,
(101, 'Employee2', '2008-11-05', NULL, 'Public Relations'),
(102, 'Employee3', '2008-11-22', NULL, 'Publisher Relations'),
(103, 'Employee4', '2008-05-11', '2016-01-01', 'Customer Service');

INSERT INTO SUPPORT_TICKET VALUES
(10000, '2016-09-10', '2016-09-10', 'Refund needed on No Man"s Sky', 100, 100001),
(10001, '2016-04-11', '2016-05-11', 'Refund needed for Call of Duty: Infinite Warfare', 101, 100002),
(10002, '2017-04-13', NULL, 'Request for Battletoads', 101, 100003);

INSERT INTO ITEM VALUES
(100, 'CSGOSKIN', 0.99),
(101, 'TF2HAT', 10.00),
(102, 'PROFILEBACKGROUND', .50),
(103, 'CSGOSKIN', 500.00);

INSERT INTO INVENTORY VALUES
(10000,  '2017-01-01', 100001, 100),
(10001,  '2017-04-11', 100002, 101),
(10002,  '2017-04-13', 100001, 102);

INSERT INTO PURCHASE VALUES 
(100001, 60001, '2016-03-01 08:24:01', 18.99), 
(100001, 60003, '2016-03-01 08:24:01', 5.99),
(100002, 60002, '2016-03-01 08:24:29', 59.99),
(100003, 60002, '2016-03-01 08:25:16', 59.99),
(100003, 60004, '2016-03-01 08:25:16', 29.99);

INSERT INTO PRODUCT VALUES
(60001, 'BattleToads', 'Fight as toads', 18.99, '2012-08-08', 'P'),
(60002, 'Call of Booty: Pirate Warfare', 59.99, 'Fast paced naval shooter', '2015-11-15', 'P'),
(60003, "Harold Bowler and the Magician's Rock", 'Young magician performs geological tricks', 5.99, '2014-08-14', 'M'),
(60004, 'Abode PhotoCrop', "World's most advanced image cropping tool", 29.99, '2014-01-13', 'P');
INSERT INTO PUBLISHER VALUES
(90001, 'Inactivision', 2003),
(90002, 'Abode', 1995),
(90003, 'Common', 1985);

INSERT INTO PROGRAM VALUES
(60001, 'G', 90003),
(60002, 'G', 90001),
(60004, 'S', 90002);

INSERT INTO GAME VALUES
(60001,  'Sidescroller', 'T'),
(60002,  'First Person Shooter', 'M');

INSERT INTO SOFTWARE VALUES
(60004, 'Image-Editing');

INSERT INTO MOVIE VALUES
(60003,  '1:20:59', 'Fantasy', 'PG-13');