= Креирање на табели 
----

**create table users**\\
(\\
    id           bigserial primary key,\\
    first_name   varchar(255),\\
    last_name    varchar(255),\\
    phone_number varchar(255),\\
    email        varchar(255) unique,\\
    password     varchar(255),\\
    role         varchar(255)\\
);\\

----

**create table address**\\
(
    id      bigserial primary key,\\
    address varchar(255),\\
    user_id bigint,\\
    foreign key (user_id) references users (id)\\
        on delete cascade\\
        on update cascade\\
);\\

----

**create table category**\\
(
    id          bigserial primary key,\\
    name        varchar(255),\\
    category_id bigint references category (id)\\
        on delete cascade\\
        on update cascade\\
);\\

----

**create table supplier**\\
(\\
    id           bigserial primary key,\\
    name         varchar(255),\\
    location     varchar(255),\\
    phone_number varchar(255)\\
);\\

----

**create table product**\\
(\\
    id          bigserial primary key,\\
    name        varchar(255),\\
    description varchar(255),\\
    image       bytea,\\
    quantity    integer,\\
    category_id bigint references category (id)\\
        on delete set null\\
        on update cascade,\\
    supplier_id bigint references supplier (id)\\
        on delete set null\\
        on update cascade\\
);\\

----

**create table product_price**\\
(\\
    product_id bigint,\\
    price      integer,\\
    start_date date,\\
    end_date   date,\\
    foreign key (product_id) references product (id)\\
        on delete cascade\\
        on update cascade,\\
    primary key (product_id, price, start_date),\\
    check ( price > 0 ),\\
    check ( end_date >= start_date )\\
);\\

----


**create table property**\\
(\\
    id   bigserial primary key,\\
    name varchar(255)\\
);\\

----

**create table product_property**\\
(\\
    product_id  bigint,\\
    property_id bigint,\\
    value       varchar(255),\\
    foreign key (product_id) references product (id)\\
        on delete cascade\\
        on update cascade,\\
    foreign key (property_id) references property (id)\\
        on delete cascade\\
        on update cascade,\\
    primary key (product_id, property_id)\\
);\\

----

**create table orders**\\
(
    id      bigserial primary key,\\
    status  varchar(255),\\
    date    date,\\
    price   integer,\\
    user_id bigint,\\
    foreign key (user_id) references users (id)\\
        on delete set null\\
        on update cascade\\
);\\

----

**create table order_details**\\
(\\
    order_id   bigint,\\
    product_id bigint,\\
    quantity   integer,\\
    foreign key (order_id) references orders (id)\\
        on delete cascade\\
        on update cascade,\\
    foreign key (product_id) references product (id)\\
        on delete cascade\\
        on update cascade,\\
    primary key (order_id, product_id)\\
);\\

----

= Полнење на табели со информации (пример скрипти)
----

INSERT INTO users (first_name, last_name, phone_number, email, password, role)\\
SELECT 'First' !|| (i) + 1,\\
       'Last' !|| (i) + 1,\\
       '123-456-7890',\\
       'user' !|| (i) + 1 !|| !'@example.com',\\
       'password' !|| (i) + 1,\\
       CASE\\
           WHEN i <= 2 THEN 'ADMIN'\\
           ELSE 'USER'\\
           END\\
FROM generate_series(1, 10000) AS i;\\

----

SELECT setseed(0.5);\\
DO\\
$$\\
    DECLARE\\
        i integer;\\
    BEGIN\\
        FOR i IN 1..(500 + floor(random() * 501))\\
            LOOP\\
                INSERT INTO category (name, category_id)\\
                VALUES ('Category_' !|| i,\\
                        CASE\\
                            WHEN random() < 0.2 THEN NULL\\
                            ELSE (SELECT id FROM category WHERE random() < 0.4 LIMIT 1)\\
                            END);\\
            END LOOP;\\
    END\\
$$;\\