RelationalDesign: kreiranje.sql

drop table if exists IN_STOCK;
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 ARTICLE;
drop table if exists DRIVER;
drop table if exists VEHICLE;
drop table if exists MANAGER;
drop table if exists WAREHOUSE;
drop table if exists CUSTOMER;
drop table if exists USERS;
drop table if exists CITY;
drop table if exists CATEGORY;

drop type if exists deliveryStatus;
drop type if exists proFormaStatus;
drop type if exists orderStatus;

drop schema if exists "IND0_185022";

create schema "IND0_185022";

set search_path = "IND0_185022";

-- ENUMERATIONS:

create type orderStatus as enum (
        'PENDING',
        'SHIPPED',
        'OUT_FOR_DELIVERY',
        'DELIVERED',
        'CANCELED',
        'FAILED'
);

create type proFormaStatus as enum(
        'PENDING',
        'PAID',
        'CANCELLED',
        'EXPIRED'
);

create type deliveryStatus as enum(
        'PREPARING',
        'IN_TRANSIT',
        'DELIVERED',
        'DELAYED',
        'RETURNED',
        'CANCELLED'
);

-- ENTITIES:

create table CATEGORY(
        category_id int primary key,
        category_name varchar(255) not null
);

create table CITY(
        city_id int primary key,
        city_name varchar(255) not null
);

create table USERS(
        user_id bigint primary key,
        user_name varchar(255) not null,
        user_surname varchar(255) not null,
        user_email varchar(255) not null,
        user_mobile varchar(255) not null,
        city_id int,
        foreign key (city_id) references CITY(city_id)
);

create table CUSTOMER(
        user_id bigint primary key,
        customer_EDB int not null,
        customer_company_name varchar(255) not null,
        customer_address varchar(255) not null,
        customer_open_time time not null,
        customer_close_time time not null,
        customer_representative_img varchar(255) not null,
        foreign key (user_id) references USERS(user_id)
);

create table WAREHOUSE(
        warehouse_id int primary key,
        warehouse_address varchar(255) not null,
        city_id int not null,
        foreign key (city_id) references CITY(city_id)
);

create table MANAGER(
        user_id bigint primary key,
        warehouse_id int,
        foreign key (warehouse_id) references WAREHOUSE(warehouse_id),
        foreign key (user_id) references USERS(user_id)
);

create table VEHICLE(
        vehicle_id int primary key,
        vehicle_carry_weight int not null,
        vehicle_service_interval smallint not null,
        vehicle_kilometers int not null,
        vehicle_last_service date,
        warehouse_id int not null,
        foreign key (warehouse_id) references WAREHOUSE(warehouse_id)
);

create table DRIVER(
        user_id bigint primary key,
        vehicle_id int not null,
        foreign key (vehicle_id) references VEHICLE(vehicle_id),
        foreign key (user_id) references USERS(user_id) 
);

create table ARTICLE(
        article_id bigint primary key,
        article_name varchar(255) not null,
        article_image varchar(255) not null,
        article_weight int not null,
        article_price decimal not null,
        category_id int not null,
        foreign key (category_id) references CATEGORY(category_id)
);

create table PRO_FORMA(
        pro_forma_id bigint primary key,
        pro_forma_status proFormaStatus not null,
        pro_forma_deadline date not null,
        pro_forma_date_created date not null
);

create table DELIVERY(
        delivery_id bigint primary key,
        delivery_status deliveryStatus not null,
        delivery_date_created date not null,
        delivery_date date not null,
        vehicle_id int not null,
        foreign key (vehicle_id) references VEHICLE(vehicle_id)
);

create table ORDERS(
        order_id bigint primary key,
        order_date date not null,
        order_status orderStatus not null,
        order_sum int not null,
        order_fulfillment_date timestamp,
        customer_id bigint,
        delivery_id bigint,
        pro_forma_id bigint,
        foreign key (customer_id) references CUSTOMER(user_id),
        foreign key (delivery_id) references DELIVERY(delivery_id),
        foreign key (pro_forma_id) references PRO_FORMA(pro_forma_id)
);

create table ARTICLE_UNIT(
        unit_id bigint 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,
        article_id bigint not null,
        order_id bigint,
        foreign key (article_id) references ARTICLE(article_id),
        foreign key (order_id) references ORDERS(order_id)
);


-- RELATIONS:

create table IN_STOCK(
        article_id bigint,
        warehouse_id int,
        foreign key (article_id) references ARTICLE_UNIT(unit_id),
        foreign key (warehouse_id) references WAREHOUSE(warehouse_id),
        primary key (article_id, warehouse_id)
);