source: DB/kreiranje.sql

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)
);