DDL: kreiranje_ddl.sql

create table product_category (
id serial,
category_name varchar(50) not null,
constraint pk_pc primary key(id)
);

create table product (
id serial,
sku varchar(255) default null,
description varchar(1000) not null,
units_in_stock integer not null,
unit_price decimal(13,2) not null,
image_url varchar(255) not null,
name varchar(100) not null,
active boolean default true,
date_created timestamp default null,
last_updated timestamp default null,
category_id integer not null,
constraint product_id primary key(id),
constraint fk_category foreign key(category_id) references product_category(id)
);


create table address(
id serial,
street varchar(255) not null,
city varchar(100) not null,
country varchar(100) not null,
zip_code varchar(50) default null,
constraint pk_address primary key(id)
);

create table customer(
id serial,
first_name varchar(100) not null,
last_name varchar(100) not null,
email varchar(255) unique not null,
constraint pk_customer primary key(id)
);

-- áèä弝è order å ðåçåðâèðàí çáîð âî postgresql òàáåëàòà ¼à èìàíóâàâ êàêî orders
create table orders(
  id bigserial,
  order_tracking_number varchar(255) default null,
  total_price decimal(19,2) not null,
  total_quantity int not null,
  date_created timestamp default null,
  customer_id int not null,
  billing_address_id int not null,
  shipping_address_id int not null,
  constraint pk_order primary key(id),
  constraint fk_customer_id foreign key(customer_id) references customer(id),
  constraint fk_billing_address_id foreign key(billing_address_id) references address(id),
  constraint fk_shipping_address_id foreign key(shipping_address_id) references address(id)
 );

create table order_item (
  o_id serial,
  order_id  bigserial,
  image_url varchar(255) default null,
  quantity int not null,
  unit_price decimal(19,2) not null,
  product_id int not null,
  constraint pk_order_item primary key(o_id, order_id),
  constraint fk_order_id foreign key(order_id) references orders(id),
  constraint fk_product_id foreign key(product_id) references product(id)
);

create table order_status(
 os_id serial,
 order_id bigserial,
 status varchar(128) default 'active',
 date_updated timestamp default null,
 constraint pk_order_status primary key(os_id, order_id),
 constraint fk_order foreign key(order_id) references orders(id)
);