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