CREATE SEQUENCE public.entity_id_sequence INCREMENT 1 START 1 MINVALUE 1;

CREATE TABLE IF NOT EXISTS products
(
    id          BIGINT PRIMARY KEY,
    name        VARCHAR(30)                       NOT NULL,
    price       NUMERIC(15, 2)                    NOT NULL,
    category_id BIGINT REFERENCES categories (id) NOT NULL
);


CREATE TABLE IF NOT EXISTS orders
(
    id         BIGINT PRIMARY KEY,
    user_id    BIGINT REFERENCES users (id) NOT NULL,
    created_at date                         NOT NULL
);


CREATE TABLE IF NOT EXISTS order_entries
(
    product_id       BIGINT REFERENCES products (id) NOT NULL,
    order_id         BIGINT REFERENCES orders (id)   NOT NULL,
    product_quantity BIGINT                          NOT NULL,
    price            NUMERIC(15, 2)                  NOT NULL,
    total            NUMERIC(15, 2)                  NOT NULL
);
CREATE TABLE IF NOT EXISTS cart
(
    id               BIGINT PRIMARY KEY,
    product_id       BIGINT REFERENCES products (id) NOT NULL,
    product_quantity BIGINT                          NOT NULL,
    user_id          BIGINT REFERENCES users (id)    NOT NULL
);
CREATE TABLE IF NOT EXISTS categories
(
    id        BIGINT PRIMARY KEY,
    name      VARCHAR(30) NOT NULL,
    parent_id BIGINT REFERENCES categories (id)
);

CREATE TABLE IF NOT EXISTS users
(
    id         BIGINT PRIMARY KEY,
    username   VARCHAR(30) UNIQUE NOT NULL,
    first_name VARCHAR(30)        NOT NULL,
    last_name  VARCHAR(30)        NOT NULL,
    password   VARCHAR(50)        NOT NULL,
    email      VARCHAR(50) UNIQUE NOT NULL,
    role       VARCHAR(10)        NOT NULL,
    created_at date               NOT NULL
);


CREATE INDEX IDX_NAME_CATEGORIES ON categories (lower(name));
CREATE INDEX IDX_NAME_PARENT_ID_CATEGORIES ON categories (parent_id, lower(name))