create table `additional services`
(
	id int auto_increment
		primary key,
	name varchar(255) null,
	price int null
)
;

create table booking
(
	id int auto_increment
		primary key,
	id_client int null,
	id_program int null,
	id_service int null,
	date date null,
	constraint booking_additional services_id_fk
		foreign key (id_service) references `additional services` (id)
)
;

create index booking_program_id_fk
	on booking (id_program)
;

create index `booking_additional services_id_fk`
	on booking (id_service)
;

create index booking_clients_id_fk
	on booking (id_client)
;

create table clients
(
	id int auto_increment
		primary key,
	First_name varchar(255) not null,
	Last_name varchar(255) not null,
	Phone varchar(255) null,
	Payment int null
)
;

create index `clients_payment method_id_fk`
	on clients (Payment)
;

alter table booking
	add constraint booking_clients_id_fk
		foreign key (id_client) references clients (id)
;

create table guides
(
	id int auto_increment
		primary key,
	First_name varchar(255) null,
	Last_name varchar(255) null
)
;

create table museums
(
	id int auto_increment
		primary key,
	name varchar(255) not null
)
;

create table `payment method`
(
	id int auto_increment
		primary key,
	method varchar(255) null
)
;

alter table clients
	add constraint clients_payment method_id_fk
		foreign key (Payment) references `payment method` (id)
;

create table program
(
	id int auto_increment
		primary key,
	name varchar(255) null,
	museum_id int null,
	`duration (m)` int null,
	guide int null,
	spots int null,
	`start at` int null,
	price int null,
	constraint program_museums_id_fk
		foreign key (museum_id) references museums (id),
	constraint program_guides_id_fk
		foreign key (guide) references guides (id)
)
;

create index program_museums_id_fk
	on program (museum_id)
;

create index program_guides_id_fk
	on program (guide)
;

alter table booking
	add constraint booking_program_id_fk
		foreign key (id_program) references program (id)
;