---

-- =========================
-- ПАРТНЁРЫ
-- =========================
CREATE TABLE partners (
    id SERIAL PRIMARY KEY,
    type TEXT,
    company_name TEXT,
    legal_address TEXT,
    inn TEXT,
    director_full_name TEXT,
    phone TEXT,
    email TEXT,
    logo TEXT,
    rating INTEGER,
    sales_places TEXT
);

CREATE TABLE partner_sales_history (
    id SERIAL PRIMARY KEY,
    partner_id INTEGER REFERENCES partners(id),
    sales_data TEXT
);

-- =========================
-- СОТРУДНИКИ
-- =========================
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    full_name TEXT,
    birth_date DATE,
    passport_data TEXT,
    bank_details TEXT,
    has_family BOOLEAN,
    health_status TEXT
);

-- =========================
-- ДОПУСК К ОБОРУДОВАНИЮ (КАДРЫ)
-- =========================
CREATE TABLE employee_equipment_access (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(id),
    equipment TEXT
);

-- =========================
-- ДОСТУП (КАРТЫ И ТУРНИКЕТЫ)
-- =========================
CREATE TABLE access_cards (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(id),
    card_code TEXT
);

CREATE TABLE access_logs (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER REFERENCES employees(id),
    access_time TIMESTAMP,
    direction TEXT
);

-- =========================
-- ПОСТАВЩИКИ
-- =========================
CREATE TABLE suppliers (
    id SERIAL PRIMARY KEY,
    type TEXT,
    name TEXT,
    inn TEXT
);

CREATE TABLE supplier_material_history (
    id SERIAL PRIMARY KEY,
    supplier_id INTEGER REFERENCES suppliers(id),
    material_data TEXT
);

-- =========================
-- МАТЕРИАЛЫ (СЫРЬЁ)
-- =========================
CREATE TABLE materials (
    id SERIAL PRIMARY KEY,
    type TEXT,
    name TEXT,
    package_quantity INTEGER,
    unit TEXT,
    description TEXT,
    image TEXT,
    cost NUMERIC,
    stock_quantity INTEGER,
    min_stock_quantity INTEGER
);

CREATE TABLE material_stock_history (
    id SERIAL PRIMARY KEY,
    material_id INTEGER REFERENCES materials(id),
    change_data TEXT
);

CREATE TABLE material_supplier_link (
    id SERIAL PRIMARY KEY,
    material_id INTEGER REFERENCES materials(id),
    supplier_id INTEGER REFERENCES suppliers(id)
);

-- =========================
-- СКЛАД
-- =========================
CREATE TABLE warehouse_transactions (
    id SERIAL PRIMARY KEY,
    material_id INTEGER REFERENCES materials(id),
    transaction_type TEXT,
    quantity INTEGER,
    transaction_time TIMESTAMP
);

-- =========================
-- ПРОДУКЦИЯ
-- =========================
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    article TEXT,
    type TEXT,
    name TEXT,
    description TEXT,
    image TEXT,
    min_partner_price NUMERIC,
    length NUMERIC,
    width NUMERIC,
    height NUMERIC,
    net_weight NUMERIC,
    gross_weight NUMERIC,
    quality_certificate TEXT,
    standard_number TEXT,
    production_time TEXT,
    cost_price NUMERIC,
    workshop_number INTEGER,
    production_people_count INTEGER
);

CREATE TABLE product_min_price_history (
    id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(id),
    price_data TEXT
);

CREATE TABLE product_materials (
    id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(id),
    material_id INTEGER REFERENCES materials(id),
    quantity INTEGER
);

-- =========================
-- ЗАЯВКА (ЗАКАЗ)
-- =========================
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    partner_id INTEGER REFERENCES partners(id),
    manager_id INTEGER REFERENCES employees(id),
    status TEXT,
    prepayment_received BOOLEAN,
    created_at TIMESTAMP
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER,
    cost NUMERIC,
    production_date DATE
);