---

create database `hotel`;
use `hotel`;

/* ==============================================================
*		То что относится к номерам.
*  ============================================================== */

create table `room_types` (
	id int primary key auto_increment,
    name varchar(255)
);

create table `room_categories` (
	id int primary key auto_increment,
    name varchar(255)
);

create table `rooms` (
	id int primary key auto_increment,
    type_id int,
    category_id int,
    number varchar(255),
    price decimal(10, 2),
    status varchar(255),
    
    foreign key (type_id) references room_types(id),
    foreign key (category_id) references room_categories(id)
);

/* ==============================================================
*		То что относится к парковкам.
*  ============================================================== */

create table `parking_types` (
	id int primary key auto_increment,
    name varchar(255)
);

create table `parking` (
	id int primary key auto_increment,
    type_id int,
    number varchar(255),
    price decimal(10, 2),
    
    foreign key (type_id) references parking_types(id)
);

/* ==============================================================
*		То что относится к гостям.
*  ============================================================== */

create table `guests` (
	id int primary key auto_increment,
    passport int
);

/* ==============================================================
*		То что относится к заездам(?).
*  ============================================================== */

create table `orders` (
	id int primary key auto_increment,
    checkin datetime,
    checkout datetime,
    guest_id int,
    guests_count int,
    room_payment decimal(10, 2),
    car_number varchar(255),
    parking_id int,
    parking_payment decimal(10, 2),
    
    foreign key (guest_id) references guests(id),
    foreign key (parking_id) references parking(id)
);


/* ==============================================================
*		Задание 1.
*  ============================================================== */

select guests.id, guests.passport
from orders
join rooms on orders.room_id = rooms.id
join guests on orders.guest_id = guests.id
where rooms.type_id = 2 and orders.parking_payment = 0.00;

/* ==============================================================
*		Задание 2.
*  ============================================================== */

delimiter //

create function `get_id_by_passport` (p_passport varchar(255))
returns int
begin
	declare out_id int;

	select guests.id into out_id
    from guests
    where guests.passport = p_passport;
    
    return out_id;
end //

delimiter ;

delimiter //

create procedure `new_procedure` (in p_passport varchar(255))
begin
	declare room_price decimal(10, 2);
    declare parking_price decimal(10, 2);
    
    declare guest_id int;
    
    select get_id_by_passport(p_passport) into guest_id;
    
    select rooms.price, parking.price
    into room_price, parking_price
    from orders
    join rooms on orders.room_id = rooms.id
    join parking on orders.parking_id = parking.id
    where orders.guest_id = guest_id;
    
    select room_price as room_payment, parking_price as parking_payment; 
end //

delimiter ;