---

DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS pickup_points;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    role VARCHAR(30) NOT NULL CHECK (role IN ('Администратор', 'Менеджер', 'Авторизированный клиент')),
    full_name VARCHAR(150) NOT NULL,
    login VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL
);

CREATE TABLE products (
    article VARCHAR(20) PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    unit VARCHAR(20),
    price NUMERIC(10, 2) NOT NULL,
    supplier VARCHAR(150),
    manufacturer VARCHAR(150),
    category VARCHAR(100),
    discount NUMERIC(5, 2) DEFAULT 0,
    stock_qty INTEGER NOT NULL DEFAULT 0,
    description TEXT,
    photo VARCHAR(255)
);

CREATE TABLE pickup_points (
    id SERIAL PRIMARY KEY,
    address VARCHAR(255) NOT NULL
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    client_id INTEGER NOT NULL REFERENCES users(id),
    order_date DATE NOT NULL,
    delivery_date DATE,
    pickup_point_id INTEGER REFERENCES pickup_points(id),
    pickup_code INTEGER,
    status VARCHAR(30) NOT NULL
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_article VARCHAR(20) NOT NULL REFERENCES products(article),
    quantity INTEGER NOT NULL CHECK (quantity > 0)
);