create table user_table(
	id_user serial primary key,
	date_created_user date not null, 
	name_user varchar(100) not null, 
	email_user varchar(100) not null unique, 
	password_user varchar(20) not null, 
	telephone_user varchar(20) 
);

create table surendee(
	id_user integer primary key, 
	constraint fk_surendee_id_user foreign key (id_user)
		references user_table (id_user) on delete cascade
);

create table admin_table(
	id_user integer primary key,
	constraint fk_admin_table_id_user foreign key (id_user)
		references user_table (id_user) on delete cascade
);

create table donor(
	id_user integer primary key,
	is_from_organisation bool not null,
	name_organisation_donor varchar(100),
	constraint fk_donor_id_user foreign key (id_user)
		references user_table (id_user) on delete cascade
);

create table organisation(
	id_organisation serial primary key,
	name_organisation varchar(100) not null, 
	email_organisation varchar(100) not null unique,
	billing_information varchar(20) not null unique
);

create table shelter(
	id_shelter serial primary key,
	address_shelter varchar(100) not null, 
	telephone_shelter varchar(20) not null,
	id_organisation integer,
	name_shelter varchar(100) not null,
	email_shelter varchar(100) not null unique,
	constraint fk_shelter_organisation foreign key (id_organisation)
		references organisation (id_organisation)
);

create table employee(
	id_user integer primary key,
	position_employee varchar(20) not null,
	id_shelter integer not null,
	is_verified bool not null,
	verified_by_admin integer,
	constraint fk_employee_id_user foreign key (id_user)
		references user_table (id_user) on delete cascade,
	constraint fk_employee_id_shelter foreign key (id_shelter)
		references shelter (id_shelter) on delete cascade,
	constraint fk_employee_id_admin foreign key (verified_by_admin)
		references admin_table (id_user)
);

create table adopter(
	id_user integer primary key, 
	free_time integer, 
	funds integer, 
	has_other_pets bool, 
	has_kids bool, 
	housing integer,  
	physical_activity_adopters integer, 
	will_foster bool, 
	is_verified bool not null,
	verified_by_employee integer,
	constraint fk_adopter_id_user foreign key (id_user)
		references user_table (id_user) on delete cascade,
	constraint fk_adopter_id_employee foreign key (verified_by_employee)
		references employee (id_user)
);

create table adoption(
	id_adoption serial primary key,  
	start_date date not null, 
	end_date_foster date,
	approved bool not null,
	id_adopter integer not null,
	constraint fk_adoption_adopter foreign key (id_adopter)
		references adopter(id_user)
);

create table pet(
	id_pet serial primary key,
	url_pet_image varchar(200) , 
	age_group integer not null, 
	size_pet integer not null, 
	breed varchar(100) , 
	name_pet varchar(20) , 
	species integer not null, 
	gender integer not null, 
	can_be_fostered bool not null,
	id_adoption integer,
	id_shelter integer,
	constraint fk_pet_adoption foreign key (id_adoption)
		references adoption(id_adoption),
	constraint fk_pet_shelter foreign key (id_shelter)
		references shelter(id_shelter)
);

create table post(
	id_post serial primary key, 
	date_post date not null, 
	url_thumbanail varchar(200),
	id_pet integer not null,
	id_surendee integer,
	id_employee integer,
	constraint fk_post_pet foreign key (id_pet)
		references pet (id_pet) on delete cascade,
	constraint fk_post_surendee foreign key (id_surendee)
		references surendee (id_user),
	constraint fk_post_employee foreign key (id_employee)
		references employee (id_user),
	constraint chk_post_poster check (
	    (id_surendee is not null and id_employee is null)
	    or 
	    (id_surendee is null and id_employee is not null)
	)
);

create table category(
	id_category serial primary key,
	name_category varchar(100) not null
);

create table food(
	id_food serial primary key, 
	manufacturer varchar(100) not null, 
	name_food varchar(20) not null, 
	type_food integer not null
);

create table therapy(
	id_therapy serial primary key, 
	health_problem varchar(100) not null, 
	start_date date, 
	end_date date 
);

create table vet_clinic(
	id_vet_clinic serial primary key,
	telephone_vet_clinic varchar(20) not null, 
	address_vet_clinic varchar(100) not null,
	name_vet_clinic varchar(100) not null
);

create table personal_profile(
	id_pet integer primary key, 
	friendly_to_kids integer not null, 
	friendly_to_pets integer not null, 
	attention integer not null, 
	physical_activity integer not null, 
	grooming_needed integer not null,
	constraint fk_personal_profile_id_pet foreign key (id_pet)
		references pet (id_pet) on delete cascade
);

create table donor_donates_to_organisation(
	id_user integer not null,
	id_organisation integer not null,
	constraint pk_donor_donates_to_organisation primary key
		(id_user, id_organisation),
	constraint fk_donor_donates_to_organisation_user foreign key (id_user)
		references donor(id_user) on delete cascade,
	constraint fk_donor_donates_to_organisation_organisation foreign key (id_organisation)
		references organisation(id_organisation) on delete cascade
);

create table pet_belongs_to_category(
	id_pet integer not null,
	id_category integer not null,
	constraint pk_pet_belongs_to_category primary key
		(id_pet, id_category),
	constraint fk_pet_belongs_to_category_pet foreign key (id_pet)
		references pet(id_pet) on delete cascade,
	constraint fk_pet_belongs_to_category_category foreign key (id_category)
		references category(id_category) on delete cascade
);

create table pet_needs_therapy(
	id_pet integer not null,
	id_therapy integer not null,
	constraint pk_pet_needs_therapy primary key
		(id_pet, id_therapy),
	constraint fk_pet_needs_therapy_pet foreign key (id_pet)
		references pet(id_pet) on delete cascade,
	constraint fk_pet_needs_therapy_therapy foreign key (id_therapy)
		references therapy(id_therapy) on delete cascade
);

create table pet_preferably_eats_food(
	id_pet integer not null,
	id_food integer not null,
	quantity_a_day integer not null,
	constraint pk_pet_preferably_eats_food primary key
		(id_pet, id_food),
	constraint fk_pet_preferably_eats_food_pet foreign key (id_pet)
		references pet(id_pet) on delete cascade,
	constraint fk_pet_preferably_eats_food_food foreign key (id_food)
		references food(id_food) on delete cascade
);

create table pet_needs_intervention_in_vet_clinic(
	id_pet integer not null,
	id_vet_clinic integer not null,
	date_of_interventing date not null,
	description varchar(100),
	constraint pk_pet_needs_intervention_in_vet_clinic primary key
		(id_pet, id_vet_clinic),
	constraint fk_pet_needs_intervention_in_vet_clinic_pet foreign key (id_pet)
		references pet(id_pet) on delete cascade,
	constraint fk_pet_needs_intervention_in_vet_clinic_vet_clinic foreign key (id_vet_clinic)
		references vet_clinic(id_vet_clinic) on delete cascade
);

