CREATE TABLE roles (
    role_id SERIAL PRIMARY KEY,
    role_name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    fio VARCHAR(150) NOT NULL,
    login VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    role_id INT REFERENCES roles(role_id) ON DELETE RESTRICT
);

CREATE TABLE pickup_points (
    point_id INT PRIMARY KEY,
    address TEXT NOT NULL
);

CREATE TABLE products (
    sku VARCHAR(50) PRIMARY KEY,
    product_name TEXT NOT NULL,
    unit_of_measure VARCHAR(20) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    supplier VARCHAR(100) NOT NULL,
    manufacturer VARCHAR(100) NOT NULL,
    category VARCHAR(100) NOT NULL,
    discount INT DEFAULT 0,
    stock_quantity INT NOT NULL,
    description TEXT,
    photo VARCHAR(255)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE NOT NULL,
    delivery_date DATE NOT NULL,
    pickup_point_id INT REFERENCES pickup_points(point_id) ON DELETE RESTRICT,
    client_id INT REFERENCES users(user_id) ON DELETE RESTRICT,
    pickup_code INT NOT NULL,
    order_status VARCHAR(50) NOT NULL
);

CREATE TABLE order_items (
    order_id INT REFERENCES orders(order_id) ON DELETE CASCADE,
    sku VARCHAR(50) REFERENCES products(sku) ON DELETE RESTRICT,
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, sku)
);