CREATE TABLE room_type(
id int Primary key AUTO_INCREMENT,
description varchar (80),
max_capacity int);
SELECT * FROM room_type;
INSERT INTO room_type(description, max_capacity)
VALUES ('super lux', 1),
('super lux', 1),
('standart', 2),
('bedroom', 3),
('apartment', 4);

CREATE TABLE room(
id int Primary key AUTO_INCREMENT,
number varchar (10),
name varchar (40),
status varchar (10),
smoke bit,
room_type_id int,
foreign key (room_type_id) references room_type(id));
SELECT * FROM room;
INSERT INTO room(number, name, status, smoke, room_type_id)
VALUES ('E10', 'sv', 'open', 1, 1),
('B33', 'gg', 'close', 1, 1),
('T12', 'rt', 'open', 1, 2),
('G55', 'iu', 'close', 0, 3),
('S77', 'er', 'open', 1, 4),
('H34', 'kj', 'open', 1, 5);

CREATE TABLE guest(
id int Primary key AUTO_INCREMENT,
first_name varchar (80),
last_name varchar (80),
member_since date);
SELECT * FROM guest;
INSERT INTO guest(first_name, last_name, member_since)
VALUES ('Valeria', 'Allik', '2024-04-16'),
('Daria', 'Halchenko', '2024-04-17'),
('Alexandra', 'Semjonova', '2024-05-06'),
('Maria', 'Gorbunova', '2024-04-29'),
('Martin', 'Sild', '2024-05-30');

CREATE TABLE reservation(
id int Primary key AUTO_INCREMENT,
date_in date,
date_out date,
made_by varchar (20),
guest_id int,
foreign key (guest_id) references guest(id));
SELECT * FROM reservation;
INSERT INTO reservation(date_in, date_out, made_by, guest_id)
VALUES ('2024-04-16', '2024-04-26', 'TallinnHotell', 1),
('2024-04-01', '2024-04-05', 'TallinnHotell', 2),
('2024-04-17', '2024-04-20', 'TallinnHotell', 3),
('2024-04-01', '2024-04-11', 'TallinnHotell', 4),
('2024-04-24', '2024-04-25', 'TallinnHotell', 5);

CREATE TABLE reserved_room(
id int Primary key AUTO_INCREMENT,
number_of_rooms int,
room_type_id int,
reservation_id int,
status varchar(20),
foreign key (room_type_id) references room_type(id),
foreign key (reservation_id) references reservation(id));
SELECT * FROM reserved_room;
INSERT INTO reserved_room(number_of_rooms, room_type_id, reservation_id, status)
VALUES ('505', 1, 1 , 'puhastamine'),
('222', 2, 1 , 'puhastamine'),
('333', 3, 2 , 'puhastamine'),
('444', 4, 3 , 'puhastamine'),
('555', 5, 4 , 'puhastamine'),
('666', 6, 5 , 'puhastamine');

CREATE TABLE occupied_room(
id int Primary key AUTO_INCREMENT,
check_in datetime,
check_out datetime,
room_id int,
reservation_id int,
foreign key (room_id) references room(id),
foreign key (reservation_id) references reservation(id));
SELECT * FROM occupied_room;
INSERT INTO occupied_room(check_in, check_out, room_id, reservation_id)
VALUES ('2024-04-23 15:30:45', '2024-05-01 12:15:40', 1, 1),
('2024-04-17 05:43:19', '2024-04-17 14:31:36', 2, 2),
('2024-04-09 09:37:19', '2024-04-17 07:14:38', 3, 3),
('2024-04-02 08:40:40', '2024-04-29 06:21:40', 4, 4),
('2024-04-17 07:18:17', '2024-04-25 05:21:36', 5, 5);

CREATE TABLE hosted_at(
id int Primary key AUTO_INCREMENT,
guest_id int,
occupied_room_id int,
FOREIGN KEY (guest_id) REFERENCES guest(id),
FOREIGN KEY (occupied_room_id) REFERENCES occupied_room(id));
SELECT * FROM hosted_at; 
INSERT INTO hosted_at(guest_id, occupied_room_id)
VALUES (1, 1),
(2, 2),
(3, 3),
(4, 4),
(5, 5);

select description
FROM room_type
WHERE description like 's%';