PgDJGtGd

1CREATE SEQUENCE public.entity_id_sequence INCREMENT 1 START 1 MINVALUE 1;
2
3CREATE TABLE IF NOT EXISTS products
4(
5    id          BIGINT PRIMARY KEY,
6    name        VARCHAR(30)                       NOT NULL,
7    price       NUMERIC(15, 2)                    NOT NULL,
8    category_id BIGINT REFERENCES categories (id) NOT NULL
9);
10
11
12CREATE TABLE IF NOT EXISTS orders
13(
14    id         BIGINT PRIMARY KEY,
15    user_id    BIGINT REFERENCES users (id) NOT NULL,
16    created_at date                         NOT NULL
17);
18
19
20CREATE TABLE IF NOT EXISTS order_entries
21(
22    product_id       BIGINT REFERENCES products (id) NOT NULL,
23    order_id         BIGINT REFERENCES orders (id)   NOT NULL,
24    product_quantity BIGINT                          NOT NULL,
25    price            NUMERIC(15, 2)                  NOT NULL,
26    total            NUMERIC(15, 2)                  NOT NULL
27);
28CREATE TABLE IF NOT EXISTS cart
29(
30    id               BIGINT PRIMARY KEY,
31    product_id       BIGINT REFERENCES products (id) NOT NULL,
32    product_quantity BIGINT                          NOT NULL,
33    user_id          BIGINT REFERENCES users (id)    NOT NULL
34);
35CREATE TABLE IF NOT EXISTS categories
36(
37    id        BIGINT PRIMARY KEY,
38    name      VARCHAR(30) NOT NULL,
39    parent_id BIGINT REFERENCES categories (id)
40);
41
42CREATE TABLE IF NOT EXISTS users
43(
44    id         BIGINT PRIMARY KEY,
45    username   VARCHAR(30) UNIQUE NOT NULL,
46    first_name VARCHAR(30)        NOT NULL,
47    last_name  VARCHAR(30)        NOT NULL,
48    password   VARCHAR(50)        NOT NULL,
49    email      VARCHAR(50) UNIQUE NOT NULL,
50    role       VARCHAR(10)        NOT NULL,
51    created_at date               NOT NULL
52);
53
54
55CREATE INDEX IDX_NAME_CATEGORIES ON categories (lower(name));
56CREATE INDEX IDX_NAME_PARENT_ID_CATEGORIES ON categories (parent_id, lower(name))