drop table administrators;
drop table business_users;
drop table clients;
drop table companies;
drop table contracts;
drop table delivery_man;
drop table is_responsible_for;
drop table locations;
drop table models;
drop table payment_cards;
drop table payments;
drop table registrations;
drop table users;
drop table vehicles;

create table users(
	user_id serial primary key,
	email varchar(255) unique not null,
	username varchar(255) unique not null,
	first_name varchar(255) not null,
	last_name varchar(255) not null,
	pass varchar(255) not null,
	created_on date not null,
	modified_on date 
);

create table administrators(
	user_id integer primary key references users(user_id)
	on delete cascade on update cascade
);

create table clients(
	user_id integer primary key references users(user_id)
	on delete cascade on update cascade
);

create table business_users(
	user_id integer primary key references users(user_id)
	on delete cascade on update cascade
);

create table companies(
	company_id serial primary key,
	company_name varchar(256) not null,
	company_email varchar(256) not null,
	created_on date not null,
	created_by integer not null,
	modified_on date,
	modified_by integer,
	business_user_id integer references business_users(user_id)
	on delete cascade on update cascade,
	administrator_id integer references administrators(user_id)
	on delete cascade on update cascade
);

create table delivery_man(
	user_id integer primary key references users(user_id)
	on delete cascade on update cascade,
	business_user_id integer references users(user_id)
	on delete cascade on update cascade,
	company_id integer references companies(company_id)
	on delete cascade on update cascade,
	salary decimal,
	date_of_employment date not null
);

create table models(
	model_id serial primary key,
	model_name varchar(100) not null,
	color varchar(30) not null,
	model_year date not null,
	num_of_seats integer not null,
	num_of_doors integer not null,
	fuel varchar(30) not null,
	transmission varchar(10) not null,
	vehicle_type varchar(30) not null,
    img_url varchar(10000)
);

create table registrations(
	registration_id serial primary key,
	plate_num varchar(8) not null,
	registred_on date not null,
	valid_thru date not null,
	is_available boolean not null
);

create table locations(
	location_id serial not null,
	city varchar(50) not null,
	street varchar(100) not null,
	street_number integer not null,
	company_id integer references companies(company_id)
	on delete cascade on update cascade,
	constraint pk_locations primary key (location_id,company_id)
);

create table vehicles(
	vehicle_id serial primary key,
	chassis_number varchar(17) not null,
	vehicle_type varchar(30) not null,
	fuel_efficiency varchar(15) not null,
	brand varchar(50) not null,
	daily_rental_price decimal not null,
	company_id integer,
	model_id integer references models(model_id)
	on delete cascade on update cascade,
	location_id integer,
	registration_id integer references registrations(registration_id)
	on delete cascade on update cascade,
	constraint fk_location foreign key (location_id,company_id) references locations(location_id,company_id)
	on delete cascade on update cascade
);

create table payment_cards(
	card_id serial primary key,
	card_number varchar(16) not null,
	cvc numeric check(cvc>=100 and cvc<=999) not null,
	valid_thru date not null,
	holder_name varchar(100) not null,
	user_id integer references clients(user_id)
	on delete cascade on update cascade
);

create table payments(
	payment_id serial not null unique,
	payment_date date not null,
	payment_time time not null,
	amount decimal not null,
	card_id integer references payment_cards(card_id)
	on delete cascade on update cascade,
	constraint pk_payment primary key (payment_id, card_id)
);

create table contracts (
	signed_date date not null,
	user_id integer references clients(user_id)
	on delete cascade on update cascade,
	vehicle_id integer references vehicles(vehicle_id)
	on delete cascade on update cascade,
	delivery_man_id integer references delivery_man(user_id)
	on delete cascade on update cascade,
	payment_id integer,
	card_id integer,
	address varchar(50) not null,
	start_date date not null,
	end_date date not null,
	review varchar(500),
    has_navigation boolean,
    has_baby_seat boolean,
    has_roofrack boolean,
    has_green_card boolean,
	constraint pk_contract primary key (signed_date,user_id,vehicle_id,delivery_man_id),
	constraint fk_contract_payment foreign key (payment_id,card_id) references payments(payment_id,card_id)
	on delete cascade on update cascade
);

create table is_responsible_for(
	user_id integer references delivery_man(user_id)
	on delete cascade on update cascade,
	vehicle_id integer references vehicles(vehicle_id)
	on delete cascade on update cascade,
	constraint pk_is_responsible_for primary key (user_id,vehicle_id)
);