create table Country(
country_ID integer not null,
country_name varchar(255),
country_iso varchar(3) unique,
primary key (country_ID));

insert into country(country_id, country_name, country_iso)
values (1, 'საქართველო', 'GEO'),
(2, 'გერმანია', 'DEU'),
(3, 'საფრანგეთი', 'FRA'),
(4, 'ჩეხეთი', 'CZE'),
(5, 'იტალია', 'ITA');


create table City(
city_ID integer not null,
country_ID integer,
city_name varchar(255),
city_phone_code varchar(30),
primary key (city_ID),
FOREIGN KEY (country_ID) REFERENCES Country(country_ID));

insert into city(city_id, country_id, city_name, city_phone_code)
values (1, 1, 'თბილისი', '+995 32'),
(2, 1, 'ქუთაისი', '+995 331'),
(3, 1, 'ბათუმი', '+995 222'),
(4, 2, 'ბერლინი', '+49 30'),
(5, 2, 'მიუნხენი', '+49 89'),
(6, 3, 'პარიზი', '+33 1'),
(7, 3, 'მარსელი', '+33 4'),
(8, 3, 'ლიონი', '+33 4'),
(9, 4, 'პრაღა', '+420'),
(10, 5, 'რომი', '+39 6'),
(11, 5, 'ვენეცია', '+39 41'),
(12, 5, 'ფლორენცია', '+39 55');

create table Hotels(
hotel_ID integer not null,
city_ID integer,
hotel_name varchar(255),
Adreess varchar(255),
stars varchar(255),
primary key (hotel_ID),
foreign key (city_ID) REFERENCES city(city_ID)
);


insert into hotels(hotel_id, city_id, hotel_name, Adreess, stars)
values (1, 1, 'სასტუმრო კოპალა' , 'ძველი თბილისი ჩეხოვის #8', '4 ვარსკვლავიანი'),
(2, 1, 'სასტუმრო მეიდანი', 'ძველი თბილისი გრიშაშვილის #19','3 ვარსკვლავიანი'),
(3, 1, 'სასტუმრო შერატონ მეტეხი პალასი', 'თელავის ქუჩა #20', '5 ვარსკვლავიანი'),
(4, 2, 'საოჯახო სასტუმრო გარდენ ჰაუსი', 'თამარ მეფის ქუჩა #98', '3 ვარსკვლავიანი'),
(5, 2, 'სასტუმრო მაგნოლია პარადისო', 'დავით და კონსტანტინეს ქუჩა #13', '3 ვარსკვლავიანი'),
(6, 2, 'სასტუმრო ედელვაისი', 'ნინოშვილის ქუჩა #145', '3 ვარსკვლავიანი'),
(7, 3, 'სასტუმრო ორბი რეზიდენსი', 'კობალაძის ქუჩა #2', '3 ვარსკვლავიანი'),
(8, 3, 'სასტუმრო ალექსი', 'მელიქიშვილის ქუჩა #102', '3 ვარსკვლავიანი'),
(9, 3, 'საოჯახო სასტუმრო მწვანე კონცხი', 'ბაღის ქუჩა #1', '2 ვარსკვლავიანი'),
(10, 4, 'სასტუმრო Adlon Kempinski', 'Unter den Linden #77', '4 ვარსკვლავიანი'),
(11, 4, 'სასტუმრო Hilton', 'Mohrenstrasse #30', '4 ვარსკვლავიანი'),
(12, 5, 'სასტუმრო Sofitel Munich Bayerpost', 'Bayerstrasse #12', '4 ვარსკვლავიანი'),
(13, 5, 'სასტუმრო Vier Jahreszeiten Kempinski', 'Maximilianstrasse #17', '5 ვარსკვლავიანი'),
(14, 6, 'სასტუმრო Plaza Athenee', '25 Avenue Montaigne', '5 ვარსკვლავიანი'),
(15, 6, 'სასტუმრო Georgette',' 36 Rue du Grenier Saint-Lazare', '3 ვარსკვლავიანი'),
(16, 6, 'სასტუმრო Maison Souquet', '10 Rue de Bruxelles', '4 ვარსკვლავიანი'),
(17, 7, 'სასტუმრო Villa Monticelli',' 96 Rue du Commandant Rolland', '5 ვარსკვლავიანი'),
(18, 7, 'სასტუმრო Dieu', '1 Place Daviel', '5 ვარსკვლავიანი'),
(19, 8, 'სასტუმრო Lyon Bellecour', '20 Quai Dr Gailleton', '4 ვარსკვლავიანი'),
(20, 8, 'სასტუმრო Cour des Loges', '6 Rue du Boeuf', '4 ვარსკვლავიანი'),
(21, 9, 'სასტუმრო Four Seasons', 'Veleslavinova 1098/2a', '5 ვარსკვლავიანი'),
(22, 9, 'სასტუმრო Corinthia', 'Kongresova 1655/1', '4 ვარსკვლავიანი'),
(23, 9, 'სასტუმრო Maximilian', 'Hastalska 752/14', '5 ვარსკვლავიანი'),
(24, 10, 'სასტუმრო The Fifteen Keys', 'Via Urbana 6/7', '3 ვარსკვლავიანი'),
(25, 10, 'სასტუმრო Artemide', 'Via Nazionale 22', '5 ვარსკვლავიანი'),
(26, 11, 'სასტუმრო Locanda Vivaldi', 'Riva degli Schiavoni 4152/4153', '4 ვარსკვლავიანი'),
(27, 11, 'სასტუმრო Metropole', 'Riva degli Schiavoni 4149', '5 ვარსკვლავიანი'),
(28, 11, 'სასტუმრო Palazzina Grassi', 'Ramo Grassi 3247',' 4 ვარსკვლავიანი'),
(29, 12, 'სასტუმრო Villa Cora', 'Viale Machiavelli 18',' 5 ვარსკვლავიანი'),
(30, 12, 'სასტუმრო Santa Maria Novella', 'Piazza di Santa Maria Novella 1',' 4 ვარსკვლავიანი');

create table costumers(
costumer_ID integer not null,
Personal_ID varchar(11),
costumer_firstname varchar(255),
costumer_lastname varchar (255),
costumer_sex varchar (10),
costumer_birthday date,
costumer_phone varchar(100),
costumer_email varchar(255),
primary key (costumer_ID)
);

create table booked_hotels(
book_id integer not null,
costumer_id integer not null,
hotel_id integer not null,
from_date date,
till_date date,
primary key (book_id),
foreign key (costumer_id) references costumers(costumer_id),
foreign key (hotel_id) references hotels(hotel_id)
);

-- 3
insert into costumers(costumer_id, personal_id, costumer_firstname, costumer_lastname, costumer_sex, costumer_birthday, costumer_phone, costumer_email)
values (1, '12345678901', 'jaba', 'ioseliani', 'male', '1939-9-23', '551906834', 'mxedrioni@mail.ru'),
(2, '12345678902', 'juba', 'darsia', 'male', '1944-3-23', '551906844', 'j.dars@mail.ru'),
(3, '12345678903', 'jaba', 'bojgua', 'male', '1964-2-23', '551906824', 'mx.xed@mail.ru'),
(4, '12345678904', 'jimi', 'hendrixi', 'male', '1936-3-21', '551906834', 'rioni@mail.ru'),
(5, '12345678905', 'jimbo', 'nolani', 'male', '1969-7-24', '551986034', 'iori@mail.ru'),
(6, '12345678906', 'goga', 'chachia', 'male', '1999-11-27', '551606834', 'g.cggi@mail.ru'),
(7, '12345678907', 'gia', 'trapaidze', 'male', '1944-4-13', '551906539', 'g.trap@mail.ru'),
(8, '12345678908', 'gaga', 'gvencadze', 'male', '1929-7-4', '551906433', 'dioni@mail.ru'),
(9, '12345678909', 'shura', 'pachulia', 'male', '1999-2-5', '551936834', 'lazika@mail.ru'),
(10, '12345678910', 'gio', 'tabidze', 'male', '1936-11-11', '551916834', 'galaktioni@mail.ru'),
(11, '12345678911', 'zaza', 'chachava', 'male', '1949-8-23', '555206834', 'chachav@mail.ru');

-- 4
insert into booked_hotels(book_id, costumer_id, hotel_id, from_date, till_date)
values (1, 1, 1, '2003-3-23', '1944-3-25'),
(2, 2, 1, '2003-6-25', '2004-3-25'),
(3, 2, 12, '2003-7-23', '2004-3-27'),
(4, 2, 2, '2003-4-23', '2004-1-22'),
(5, 2, 2, '2003-1-23', '2004-3-23'),
(6, 2, 3, '2003-8-6', '2004-12-23'),
(7, 2, 3, '2003-11-23', '2004-11-11'),
(8, 2, 3, '2005-4-22', '2005-12-23'),
(9, 3, 11, '2005-3-21', '2006-3-23'),
(10, 7, 10, '2006-3-4', '2007-3-23'),
(11, 3, 4, '2010-3-11', '2010-3-23'),
(12, 4, 3, '2010-4-12', '2011-4-26'),
(13, 5, 13, '2010-3-1', '2011-3-28'),
(14, 6, 6, '2010-7-5', '2011-1-4'),
(15, 7, 7, '2010-12-31', '2011-4-6'),
(16, 8, 1, '2010-4-21', '2011-2-13'),
(17, 9, 23, '2010-1-1', '2011-11-15'),
(18, 10, 3, '2010-11-5', '2011-7-11'),
(19, 11, 5, '2010-5-11', '2012-1-18'),
(20, 4, 28, '2010-3-22', '2011-8-29'),
(21, 7, 19, '2010-3-14', '2011-12-28'),
(22, 7, 19, '2010-4-11', '2011-1-28'),
(23, 7, 19, '2010-3-21', '2011-12-21'),
(24, 7, 19, '2010-6-1', '2011-4-4'),
(25, 7, 19, '2010-7-10', '2011-12-27');

-- 5
select c.country_name, c.country_iso, ct.city_name, ct.city_phone_code, h.hotel_name, h.Adreess
from country c
join city ct on (c.country_id = ct.country_id)
join hotels h on (ct.city_ID = h.city_ID)
order by c.country_name;

-- 6
select c.country_name, c.country_iso, ct.city_name, ct.city_phone_code, h.hotel_name, h.Adreess, count(b.book_id) booked_number
from country c
join city ct on (c.country_id = ct.country_id)
join hotels h on (ct.city_ID = h.city_ID)
join booked_hotels b on (b.hotel_id = h.hotel_id)
group by c.country_name, c.country_iso, ct.city_name, ct.city_phone_code, h.hotel_name, h.Adreess;


-- 7
select c.country_name, ct.city_name, h.hotel_name, count(b.book_id) booked_number
from country c
join city ct on (c.country_id = ct.country_id)
join hotels h on (ct.city_ID = h.city_ID)
join booked_hotels b on (b.hotel_id = h.hotel_id)
group by c.country_name, ct.city_name, h.hotel_name
having count(b.book_id) = (select count(book_id) d from booked_hotels group by hotel_id order by d desc limit 1);


-- 8
select c.personal_id, concat(c.costumer_firstname,' ',c.costumer_lastname), c.costumer_birthday, count(bh.book_id)
from costumers c
join booked_hotels bh on (c.costumer_id = bh.costumer_id)
group by c.personal_id, concat(c.costumer_firstname,' ',c.costumer_lastname), c.costumer_birthday
having count(bh.book_id) = (select count(book_id) d from booked_hotels group by costumer_id order by d desc limit 1);