---

-- 1. Таблица customers (Заказчики)
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    inn VARCHAR(20),
    address VARCHAR(300),
    phone VARCHAR(20),
    is_seller BOOLEAN DEFAULT false,
    is_buyer BOOLEAN DEFAULT false
);

-- 2. Таблица orders (Заказы)
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_num VARCHAR(50),
    order_date DATE,
    customer_id INTEGER REFERENCES customers(id),
    total NUMERIC(10, 2)
);

-- 3. Таблица products (Продукция)
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    unit VARCHAR(20),
    price NUMERIC(10, 2)
);

-- 4. Таблица materials (Материалы)
CREATE TABLE materials (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    unit VARCHAR(20),
    price NUMERIC(10, 2)
);

-- 5. Таблица order_lines (Позиции заказа)
CREATE TABLE order_lines (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    quantity NUMERIC(10, 3),
    price NUMERIC(10, 2)
);

-- 6. Таблица recipes (Рецепты/Состав продукции)
CREATE TABLE recipes (
    id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(id),
    material_id INTEGER REFERENCES materials(id),
    quantity NUMERIC(10, 3)
);