s8jUFFK4

1drop table if exists order_product_links cascade;
2drop table if exists orders cascade;
3drop table if exists customers cascade;
4drop table if exists products cascade;
5drop table if exists rubrics cascade;
6drop table if exists brands cascade;
7
8create table rubrics(
9	rubric_id serial primary key,
10	rubric_name varchar(50) unique,
11	sub_rubric_id int default null
12);
13
14create table brands(
15	brand_id serial primary key,
16	brand_name varchar(50) unique
17);
18
19create table products(
20	product_id serial primary key,
21	description varchar(255) not null,
22	weight_in_grams int not null,
23	price numeric(10,2) not null check (price >= 0),
24	stored_count int not null check (stored_count >= 0),
25	brand_id int references brands,
26	product_code varchar(255) not null,
27	rubric_id int not null references rubrics (rubric_id) on update cascade on delete restrict,
28	unique (brand_id, product_code)
29);
30
31create table customers(
32	customer_id serial primary key,
33	customer_email varchar(255) not null unique,
34	customer_address varchar(255) not null
35);
36
37drop type if exists delivery_statuses;
38create type delivery_statuses as enum ('delivered', 'ready to go', 'on the way', 'not ready');
39
40create table orders(
41	order_id serial primary key,
42	customer_id int references customers on update cascade on delete restrict,
43	order_paid bool not null default False,
44	delivery_status delivery_statuses not null,
45	full_price numeric(10,2) not null check (full_price >= 0),
46	delivery_address varchar(255),
47	order_date timestamptz not null,
48	delivery_date timestamptz
49);
50
51create table order_product_links(
52	order_id int references orders on update cascade on delete cascade,
53	product_id int references products on update cascade on delete restrict,
54	products_count int not null check (products_count > 0),
55	price numeric(10,2) not null check (price >= 0),
56	unique (order_id, product_id)
57);