CREATE TABLE IF NOT EXISTS Admin_Requirements (
  Req_ID mediumint(9) NOT NULL AUTO_INCREMENT,
  Req_Description text NOT NULL,
  PRIMARY KEY (Req_ID)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;


INSERT INTO Admin_Requirements (Req_ID, Req_Description) VALUES
(1, 'Casual Dress'),
(2, 'Fancy Dress'),
(3, 'Corporate/Business'),
(4, 'Jersey Shore'),
(5, 'Age 18+'),
(6, 'Age 21+'),
(7, 'College ID'),
(8, 'Underwear only'),
(9, 'Swimsuit'),
(10, 'Cover fee');


CREATE TABLE IF NOT EXISTS Entertainment (
  E_ID mediumint(9) NOT NULL AUTO_INCREMENT,
  E_Description mediumtext NOT NULL,
  PRIMARY KEY (E_ID)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;


INSERT INTO Entertainment (E_ID, E_Description) VALUES
(1, 'House Music'),
(2, 'Dub_Step Music'),
(3, 'Trance Music'),
(4, 'HipHop Music'),
(5, 'Rap Music'),
(6, 'Live DJ'),
(7, 'Highlighter party');



CREATE TABLE IF NOT EXISTS Food (
  Party_ID bigint(20) NOT NULL AUTO_INCREMENT,
  Name VARCHAR(256) NOT NULL,
  Price int(11) NOT NULL,
  PRIMARY KEY (Party_ID,Name)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;



INSERT INTO Food (Party_ID, Name, Price) VALUES
(1, 'Chinese', 2),
(1, 'Pizza', 4),
(2, 'Jack D.', 3),
(2, 'Vodka', 4),
(5, 'beer', 3),
(7, 'pasta', 6),
(7, 'peaches', 2),
(8, 'shots', 2),
(8, 'vodka', 10),
(9, 'oranges', 1),
(11, 'bannana', 8),
(13, 'Vodka', 3);


CREATE TABLE IF NOT EXISTS Messages (
  TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  TO VARCHAR(256) NOT NULL,
  FROM VARCHAR(256) NOT NULL,
  Contents longtext NOT NULL,
  PRIMARY KEY (TIME,TO,FROM),
  KEY TO (TO),
  KEY FROM (FROM)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO Messages (TIME, TO, FROM, Contents) VALUES
('2011_04_16 14:49:01', 'Test1@pitt.edu', 'Test2@pitt.edu', 'This is a test message from 1_2'),
('2011_04_16 14:49:32', 'Test2@pitt.edu', 'Test4@pitt.edu', 'This is a test message from 2_4'),
('2011_04_16 14:49:55', 'Test3@pitt.edu', 'Test1@pitt.edu', 'This is a test message from 3_1'),
('2011_04_18 15:32:29', 'Test7@pitt.edu', 'Test9@pitt.edu', 'weruihuhihiu'),
('2011_04_18 15:33:05', 'Test5@pitt.edu', 'Test1@pitt.edu', 'This is super');


CREATE TABLE IF NOT EXISTS Party (
  Party_ID bigint(20) NOT NULL AUTO_INCREMENT,
  Zip int(5) NOT NULL,
  Address mediumtext NOT NULL,
  User_ID VARCHAR(256) NOT NULL,
  Additional_Description mediumtext NOT NULL,
  Activite int(1) NOT NULL,
  PRIMARY KEY (Party_ID),
  KEY Zip (Zip),
  KEY User_ID (User_ID)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;


INSERT INTO Party (Party_ID, Zip, Address, User_ID, Additional_Description, Activite) VALUES
(1, 15213, '238 Meyran Ave.', 'Test3@pitt.edu', 'Addmission price is 5 dollars', 1),
(2, 10013, '174 Canal Street', 'Test2@pitt.edu', 'Theme is togas', 1),
(3, 15213, '444 Bouquet street', 'Test9@pitt.edu', 'uew', 0),
(4, 15213, '345 apple street', 'Test8@pitt.edu', 'hihi', 1),
(5, 10013, '658 boring street', 'Test4@pitt.edu', 'joewoi', 1),
(6, 82353, '222 melwood', 'Test3@pitt.edu', 'djhoi', 0),
(7, 15213, '234 Craig', 'Test10@pitt.edu', 'eawfjoi', 1),
(8, 32144, '777 nothing', 'Test5@pitt.edu', 'uaewifr', 1),
(9, 28917, '888 melwood', 'Test8@pitt.edu', 'adusfhisa', 1),
(10, 10013, '567 fake street', 'Test7@pitt.edu', 'hiw2', 1),
(11, 48392, '999 bayard ave', 'Test5@pitt.edu', '324', 1),
(12, 12345, '483 fake street', 'Test7@pitt.edu', 'awei', 0),
(13, 10013, '174 aiid', 'Test6@pitt.edu', 'fas', 1);


CREATE TABLE IF NOT EXISTS Party_entertainment (
  Party_ID bigint(20) NOT NULL,
  E_ID mediumint(9) NOT NULL,
  PRIMARY KEY (Party_ID,E_ID),
  KEY E_ID (E_ID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO Party_entertainment (Party_ID, E_ID) VALUES
(1, 1),
(8, 1),
(9, 2),
(4, 3),
(7, 3),
(9, 3),
(11, 4),
(4, 5),
(9, 5),
(2, 6),
(5, 6),
(11, 6),
(8, 7),
(11, 7);


CREATE TABLE IF NOT EXISTS Party_requirements (
  Party_ID bigint(20) NOT NULL,
  Req_ID mediumint(9) NOT NULL,
  PRIMARY KEY (Party_ID,Req_ID),
  KEY Req_ID (Req_ID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO Party_requirements (Party_ID, Req_ID) VALUES
(7, 1),
(4, 2),
(8, 2),
(2, 3),
(9, 4),
(2, 5),
(6, 5),
(9, 6),
(1, 7),
(11, 7),
(4, 8),
(11, 9),
(1, 10),
(5, 10),
(10, 10);



CREATE TABLE IF NOT EXISTS RANK (
  Party_ID bigint(20) NOT NULL,
  Email VARCHAR(256) NOT NULL,
  RANK int(11) NOT NULL,
  PRIMARY KEY (Party_ID,Email),
  KEY Email (Email)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



INSERT INTO RANK (Party_ID, Email, RANK) VALUES
(1, 'Test1@pitt.edu', 6),
(1, 'Test4@pitt.edu', 2),
(2, 'Test3@pitt.edu', 1),
(4, 'Test5@pitt.edu', 1),
(9, 'Test7@pitt.edu', 4),
(12, 'Test10@pitt.edu', 7);



CREATE TABLE IF NOT EXISTS Users (
  Email VARCHAR(256) NOT NULL,
  Password longtext NOT NULL,
  Zip int(5) NOT NULL,
  RANK mediumint(9) NOT NULL,
  Active int(1) NOT NULL,
  PRIMARY KEY (Email)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO Users (Email, Password, Zip, RANK, Active) VALUES
('Test10@pitt.edu', '*AF31C6CBDECD88726D0A9B3798C71EF41F1624D5', 89402, 10, 1),
('Test1@pitt.edu', '*AF31C6CBDECD88726D0A9B3798C71EF41F1624D5', 15213, 1, 1),
('Test2@pitt.edu', '*AF31C6CBDECD88726D0A9B3798C71EF41F1624D5', 10013, 2, 1),
('Test3@pitt.edu', '*AF31C6CBDECD88726D0A9B3798C71EF41F1624D5', 7052, 3, 1),
('Test4@pitt.edu', '*AF31C6CBDECD88726D0A9B3798C71EF41F1624D5', 12345, 5, 0),
('Test5@pitt.edu', '*AF31C6CBDECD88726D0A9B3798C71EF41F1624D5', 10013, 6, 0),
('Test6@pitt.edu', '*AF31C6CBDECD88726D0A9B3798C71EF41F1624D5', 15213, 3, 1),
('Test7@pitt.edu', '*AF31C6CBDECD88726D0A9B3798C71EF41F1624D5', 15213, 8, 0),
('Test8@pitt.edu', '*AF31C6CBDECD88726D0A9B3798C71EF41F1624D5', 45930, 8, 1),
('Test9@pitt.edu', '*AF31C6CBDECD88726D0A9B3798C71EF41F1624D5', 28917, 9, 1);


ALTER TABLE Food
  ADD CONSTRAINT Food_ibfk_1 FOREIGN KEY (Party_ID) REFERENCES Party (Party_ID);


ALTER TABLE Messages
  ADD CONSTRAINT Messages_ibfk_1 FOREIGN KEY (TO) REFERENCES Users (Email),
  ADD CONSTRAINT Messages_ibfk_2 FOREIGN KEY (FROM) REFERENCES Users (Email);


ALTER TABLE Party
  ADD CONSTRAINT Party_ibfk_1 FOREIGN KEY (User_ID) REFERENCES Users (Email);

ALTER TABLE Party_entertainment
  ADD CONSTRAINT Party@002dentertainment_ibfk_1 FOREIGN KEY (Party_ID) REFERENCES Party (Party_ID),
  ADD CONSTRAINT Party@002dentertainment_ibfk_2 FOREIGN KEY (E_ID) REFERENCES Entertainment (E_ID);


ALTER TABLE Party_requirements
  ADD CONSTRAINT Party@002drequirements_ibfk_1 FOREIGN KEY (Party_ID) REFERENCES Party (Party_ID),
  ADD CONSTRAINT Party@002drequirements_ibfk_2 FOREIGN KEY (Req_ID) REFERENCES Admin Requirements (Req_ID);


ALTER TABLE RANK
  ADD CONSTRAINT Rank_ibfk_1 FOREIGN KEY (Party_ID) REFERENCES Party (Party_ID),
  ADD CONSTRAINT Rank_ibfk_2 FOREIGN KEY (Email) REFERENCES Users (Email);