RelationalDesign: kreiranje_app.sql

set search_path = "IND0_185022";

drop table if exists token_;
drop table if exists customer_weekday;
drop table if exists unit_price;
drop table if exists article_unit;
drop table if exists orders;
drop table if exists delivery;
drop table if exists pro_forma;
drop table if exists price;
drop table if exists article;
drop table if exists manufacturer;
drop table if exists driver;
drop table if exists manager;
drop table if exists customer;
drop table if exists users;
drop table if exists vehicle;
drop table if exists warehouse;
drop table if exists city;
drop table if exists region;
drop table if exists category;
drop table if exists weekday;
drop table if exists pro_forma_status;
drop table if exists delivery_status;
drop table if exists order_status;

drop schema if exists "IND0_185022";

create schema "IND0_185022";

set search_path = "IND0_185022";

-- ENTITIES:

create table order_status
(
    o_status_id   smallint generated by default as identity primary key,
    o_status_name varchar(255) not null unique,
    o_status_desc text         not null
);

create table delivery_status
(
    d_status_id   smallint generated by default as identity primary key,
    d_status_name varchar(255) not null unique,
    d_status_desc text         not null
);

create table pro_forma_status
(
    pf_status_id   smallint generated by default as identity primary key,
    pf_status_name varchar(255) not null unique,
    pf_status_desc text         not null
);

create table weekday
(
    day_id   smallint generated by default as identity primary key,
    day_name varchar(20) not null
);

create table category
(
    ctg_id   int generated by default as identity primary key,
    ctg_name varchar(255) not null
);

create table region
(
    region_id   int generated by default as identity primary key,
    region_name varchar(255) not null
);

create table city
(
    city_id   int generated by default as identity primary key,
    city_name varchar(255) not null,
    region_id int          not null,
    foreign key (region_id) references region (region_id)
);

create table warehouse
(
    wh_id   int generated by default as identity primary key,
    wh_adr  varchar(255) not null,
    city_id int          not null,
    foreign key (city_id) references city (city_id)
);

create table vehicle
(
    veh_id               int generated by default as identity primary key,
    veh_carry_weight     int         not null,
    veh_service_interval smallint    not null,
    veh_kilometers       int         not null,
    veh_last_service     date,
    veh_last_service_km  int,
    veh_plate            varchar(8)  not null,
    veh_vin              varchar(17) not null unique,
    veh_reg_date         date        not null,
    wh_id                int         not null,
    foreign key (wh_id) references warehouse (wh_id)
);

create table users
(
    user_id      bigint generated by default as identity primary key,
    user_name    varchar(255) not null,
    user_surname varchar(255) not null,
    user_pass    varchar(255) not null,
    user_salt    varchar(255) not null,
    user_email   varchar(255) not null,
    user_mobile  varchar(255) not null,
    user_active  boolean      not null,
    user_image   varchar(255),
    clazz_       varchar(255) not null,
    user_role    varchar(255) not null,

    city_id      int          not null,
    foreign key (city_id) references city (city_id)
);

create table customer
(
    user_id                 bigint primary key,
    cust_EDB                varchar(13)  not null,
    cust_company_name       varchar(255) not null,
    cust_adr                varchar(255) not null,
    cust_representative_img varchar(255),
    foreign key (user_id) references users (user_id)
);

create table manager
(
    user_id bigint primary key,
    wh_id   int not null,
    foreign key (wh_id) references warehouse (wh_id),
    foreign key (user_id) references users (user_id)
);

create table driver
(
    user_id bigint primary key,
    veh_id  int not null,
    foreign key (veh_id) references vehicle (veh_id),
    foreign key (user_id) references users (user_id)
);

create table manufacturer
(
    man_id     bigint generated by default as identity primary key,
    man_name   varchar(255) not null,
    man_adr    varchar(255) not null,
    man_mobile varchar(255) not null,
    man_email  varchar(255) not null
);

create table article
(
    art_id     bigint generated by default as identity primary key,
    art_name   varchar(255) not null,
    art_image  varchar(255) not null,
    art_weight int          not null,
    ctg_id     int          not null,
    man_id     bigint       not null,
    foreign key (ctg_id) references category (ctg_id),
    foreign key (man_id) references manufacturer (man_id)
);

create table price
(
    price_id       int generated by default as identity primary key,
    price          decimal   not null,
    price_eff_date timestamp not null default current_timestamp,
    art_id         bigint    not null,
    foreign key (art_id) references article (art_id)
);

create table pro_forma
(
    pf_id           bigint generated by default as identity primary key,
    pf_deadline     date     not null,
    pf_date_created date     not null,
    pf_status_id    smallint not null,
    foreign key (pf_status_id) references pro_forma_status (pf_status_id)
);

create table delivery
(
    del_id           bigint generated by default as identity primary key,
    del_date_created date     not null,
    del_date         date     not null,
    del_start_km     int,
    del_end_km       int,
    del_start_time   time,
    del_end_time     time,
    d_status_id      smallint not null,
    veh_id           int      not null,
    foreign key (d_status_id) references delivery_status (d_status_id),
    foreign key (veh_id) references vehicle (veh_id)
);

create table orders
(
    ord_id               bigint generated by default as identity primary key,
    ord_date             date     not null,
    ord_sum              int      not null,
    ord_fulfillment_date timestamp,
    ord_comment          text,
    o_status_id          smallint not null,
    cust_id              bigint   not null,
    del_id               bigint,
    pf_id                bigint,
    foreign key (o_status_id) references order_status (o_status_id),
    foreign key (cust_id) references customer (user_id),
    foreign key (del_id) references delivery (del_id),
    foreign key (pf_id) references pro_forma (pf_id)
);

create table article_unit
(
    unit_id               bigint generated by default as identity primary key,
    unit_expiration_date  date         not null,
    unit_serial_number    varchar(255) not null,
    unit_batch_number     varchar(255) not null,
    unit_manufacture_date date         not null,
    unit_cost_price       decimal      not null,
    wh_id                 int          not null,
    ord_id                bigint,
    foreign key (wh_id) references warehouse (wh_id),
    foreign key (ord_id) references orders (ord_id)
);

create table unit_price
(
    unit_id  bigint not null,
    price_id bigint not null,
    primary key (unit_id, price_id),
    foreign key (unit_id) references article_unit (unit_id),
    foreign key (price_id) references price (price_id)
);

create table customer_weekday
(
    cust_day_id bigint generated by default as identity primary key,
    cust_id     bigint   not null,
    day_id      smallint not null,
    start_time  time     not null,
    end_time    time     not null,
    foreign key (cust_id) references customer (user_id),
    foreign key (day_id) references weekday (day_id)
);

create table token_
(
    t_id     bigint generated by default as identity primary key,
    t_value  text         not null,
    t_date   timestamp         not null default current_timestamp,
    t_type   varchar(255) not null,
    t_expiry timestamp,
    t_validated_at timestamp,
    t_user   bigint       not null,
    foreign key (t_user) references users (user_id)
)