drop schema if exists project cascade;
create schema project;



create table pilots(
	pilot_id serial not null,
	pilot_name varchar(100) not null,
	first_name varchar(100) not null,
	last_name varchar(100) not null,
	birth_date date null,
	email varchar(100) not null,
	"password" varchar not null,
	member_since date not null,
	bio varchar(2000) null,
	constraint pilot_id_pk primary key (pilot_id)
);

---------------------------------------------------------------

create table pilot_videos(
	video_id serial not null,
	video_link varchar(100) not null,
	pilot_id integer not null,
	CONSTRAINT pilot_video_pk primary key (video_id),
	constraint pilot_videos_pilot_id_fk foreign key (pilot_id) references pilots(pilot_id)
);

---------------------------------------------------------------


create table pilot_images(
	image_id serial not null,
	image_data bytea not null,
	is_profilePicture boolean not null,
	pilot_id integer not null,
	constraint image_id_pk primary key (image_id),
	constraint pilot_images_pilot_id_fk foreign key (pilot_id) references pilots(pilot_id)
);


---------------------------------------------------------------

create table organizers(
	organizer_id serial not null,
	organizer_name varchar(100) not null,
	constraint organizer_id_pk primary key (organizer_id)
);

---------------------------------------------------------------

create table drones(
	drone_id serial not null,
	motors varchar(100) null, 
	esc varchar(100) null,
	frame varchar(100) null,
	flight_controller varchar(100) null,
	battery varchar(100) null,
	drone_pilot_id integer not null,
	constraint drone_id_pk primary key (drone_id),
	constraint drone_pilot_id_fk foreign key (drone_pilot_id) references pilots(pilot_id)
);

---------------------------------------------------------------

create table countries(
	country_id serial not null,
	country_name varchar(100) not null,
	constraint country_id_pk primary key (country_id)
	
);



---------------------------------------------------------------

create table cities(
	city_id serial not null,
	city_name varchar(100) not null,
	country_id integer not null,
	constraint city_id_pk primary key (city_id),
	constraint country_id_fk foreign key (country_id) references countries(country_id)
);



---------------------------------------------------------------


create table races(
	race_id serial not null,
	track_photo bytea null,
	race_description varchar(100) null,
	start_date date not null,
	end_date date not null,
	city_id integer not null,
	organizer_id integer not null,
	constraint organizer_id_fk foreign key (organizer_id) references organizers(organizer_id),
	constraint race_id_pk primary key (race_id),
	constraint race_location_fk foreign key (city_id) references cities(city_id)
);

---------------------------------------------------------------

create table participants(
	pilot_id integer not null,
	race_id integer not null,
	constraint participating_pilot_id_fk foreign key (pilot_id) references pilots(pilot_id),
	constraint participating_race_id_fk foreign key (race_id) references races (race_id)
);