CREATE database HotelManagement;

	CREATE TABLE HotelManagement.Room (
	  roomID            INT not null,
	  roomTaken        TINYINT(1),
	  beds              INT not null,
	  size              INT not null,
	  roomRank          INT not null,
	  PRIMARY KEY(roomID));


	CREATE TABLE HotelManagement.HotelTask (
	  taskType      INT         not null,
	  taskStatus    TINYINT(1)  not null,
	  whichRoom        INT         not null,
	  note          VARCHAR(255),
	  FOREIGN KEY (whichRoom) REFERENCES HotelManagement.Room(roomID));


	CREATE TABLE HotelManagement.Guest (
	  firstName           varchar(25)   not null,
	  lastName            varchar(25)   not null,
	  userPassword        varchar(25)   not null,
	  email               varchar(25)   not null,
	  PRIMARY KEY (userPassword, email),
	  INDEX(email));


	CREATE TABLE HotelManagement.Reservation (
	  reservationID     INT not null,
	  id_room            INT not null,
	  guestEmail             varchar(25) not null,
	  fromDate          DATE not null,
	  toDate            DATE not null,
	  PRIMARY KEY (reservationID),
	  FOREIGN KEY (guestEmail) REFERENCES HotelManagement.Guest(email)

	  );

INSERT INTO HotelManagement.Room (roomID, roomTaken, beds, size, roomRank)
VALUES ('1','1','2','12','5');
INSERT INTO HotelManagement.Room (roomID, roomTaken, beds, size, roomRank)
VALUES ('2','0','4','23','7');
INSERT INTO HotelManagement.Room (roomID, roomTaken, beds, size, roomRank)
VALUES ('3','0','6','55','10');
INSERT INTO HotelManagement.Room (roomID, roomTaken, beds, size, roomRank)
VALUES ('4','1','4','11','4');
INSERT INTO HotelManagement.Room (roomID, roomTaken, beds, size, roomRank)
VALUES ('5','1','2','16','6');
INSERT INTO HotelManagement.Room (roomID, roomTaken, beds, size, roomRank)
VALUES ('6','1','4','30','8');

/* Hotel Tasks taskType : 0 : service, 1 : maintenance, 2 : cleaning*/

INSERT INTO HotelManagement.HotelTask (taskType, taskStatus, whichRoom, note)
VALUES ('0','0','1','Noen har bestilt lobster');
INSERT INTO HotelManagement.HotelTask (taskType, taskStatus, whichRoom, note)
VALUES ('0','0','5','Noen har bestilt champagne');

INSERT INTO HotelManagement.HotelTask (taskType, taskStatus, whichRoom, note)
VALUES ('1','0','6','Fikse strømkontakt');
INSERT INTO HotelManagement.HotelTask (taskType, taskStatus, whichRoom, note)
VALUES ('2','0','6','Rengjør seng og bad');

INSERT INTO HotelManagement.HotelTask (taskType, taskStatus, whichRoom, note)
VALUES ('2','0','4','Rengjør hele rommet');
INSERT INTO HotelManagement.HotelTask (taskType, taskStatus, whichRoom, note)
VALUES ('1','0','4','Madrass knirker');

/* Gjester */
INSERT INTO HotelManagement.Guest (firstName, lastName, userPassword, email)
VALUES ('Steinar','Vik','pass123','sample1@mail.com');
INSERT INTO HotelManagement.Guest (firstName, lastName, userPassword, email)
VALUES ('Hilde','Andersen','pass123','sample2@mail.com');
INSERT INTO HotelManagement.Guest (firstName, lastName, userPassword, email)
VALUES ('Are','Berge','pass123','sample3@mail.com');
INSERT INTO HotelManagement.Guest (firstName, lastName, userPassword, email)
VALUES ('Pernille','Sørensen','pass123','sample4@mail.com');
INSERT INTO HotelManagement.Guest (firstName, lastName, userPassword, email)
VALUES ('Atle ','Antosen','pass123','sample5@mail.com');
INSERT INTO HotelManagement.Guest (firstName, lastName, userPassword, email)
VALUES ('Henrik','Ibsen','pass123','sample6@mail.com');


/* Reservasjoner */
INSERT INTO HotelManagement.Reservation (reservationID, id_room, guestEmail, fromDate, toDate)
VALUES ('98278','1','sample1@mail.com','2016-02-15','2016-02-30');
INSERT INTO HotelManagement.Reservation (reservationID, id_room, guestEmail, fromDate, toDate)
VALUES ('3451','2','sample2@mail.com','2016-02-13','2016-02-21');
INSERT INTO HotelManagement.Reservation (reservationID, id_room, guestEmail, fromDate, toDate)
VALUES ('93831','3','sample3@mail.com','2016-03-03','2016-03-08');
INSERT INTO HotelManagement.Reservation (reservationID, id_room, guestEmail, fromDate, toDate)
VALUES ('45645','4','sample4@mail.com','2016-02-17','2016-02-28');
INSERT INTO HotelManagement.Reservation (reservationID, id_room, guestEmail, fromDate, toDate)
VALUES ('15892','5','sample5@mail.com','2016-02-23','2016-02-25');
INSERT INTO HotelManagement.Reservation (reservationID, id_room, guestEmail, fromDate, toDate)
VALUES ('7832','6','sample6@mail.com','2016-02-20','2016-02-27');