---

-- =========================
-- ПАРТНЁРЫ
-- =========================
CREATE TABLE partners (
    id INT IDENTITY(1,1) PRIMARY KEY,
    type NVARCHAR(255),
    company_name NVARCHAR(255),
    legal_address NVARCHAR(255),
    inn NVARCHAR(255),
    director_full_name NVARCHAR(255),
    phone NVARCHAR(50),
    email NVARCHAR(100),
    logo NVARCHAR(255),
    rating INT,
    sales_places NVARCHAR(255)
);

CREATE TABLE partner_sales_history (
    id INT IDENTITY(1,1) PRIMARY KEY,
    partner_id INT,
    sales_data NVARCHAR(MAX),
    FOREIGN KEY (partner_id) REFERENCES partners(id)
);

-- =========================
-- СОТРУДНИКИ
-- =========================
CREATE TABLE employees (
    id INT IDENTITY(1,1) PRIMARY KEY,
    full_name NVARCHAR(255),
    birth_date DATETIME,
    passport_data NVARCHAR(255),
    bank_details NVARCHAR(255),
    has_family BIT,
    health_status NVARCHAR(255)
);

-- =========================
-- ДОПУСК К ОБОРУДОВАНИЮ
-- =========================
CREATE TABLE employee_equipment_access (
    id INT IDENTITY(1,1) PRIMARY KEY,
    employee_id INT,
    equipment NVARCHAR(255),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

-- =========================
-- ДОСТУП
-- =========================
CREATE TABLE access_cards (
    id INT IDENTITY(1,1) PRIMARY KEY,
    employee_id INT,
    card_code NVARCHAR(50),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

CREATE TABLE access_logs (
    id INT IDENTITY(1,1) PRIMARY KEY,
    employee_id INT,
    access_time DATETIME,
    direction NVARCHAR(50),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

-- =========================
-- ПОСТАВЩИКИ
-- =========================
CREATE TABLE suppliers (
    id INT IDENTITY(1,1) PRIMARY KEY,
    type NVARCHAR(255),
    name NVARCHAR(255),
    inn NVARCHAR(255)
);

CREATE TABLE supplier_material_history (
    id INT IDENTITY(1,1) PRIMARY KEY,
    supplier_id INT,
    material_data NVARCHAR(MAX),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
);

-- =========================
-- МАТЕРИАЛЫ
-- =========================
CREATE TABLE materials (
    id INT IDENTITY(1,1) PRIMARY KEY,
    type NVARCHAR(255),
    name NVARCHAR(255),
    package_quantity INT,
    unit NVARCHAR(50),
    description NVARCHAR(MAX),
    image NVARCHAR(255),
    cost DECIMAL(18, 2),
    stock_quantity INT,
    min_stock_quantity INT
);

CREATE TABLE material_stock_history (
    id INT IDENTITY(1,1) PRIMARY KEY,
    material_id INT,
    change_data NVARCHAR(MAX),
    FOREIGN KEY (material_id) REFERENCES materials(id)
);

CREATE TABLE material_supplier_link (
    id INT IDENTITY(1,1) PRIMARY KEY,
    material_id INT,
    supplier_id INT,
    FOREIGN KEY (material_id) REFERENCES materials(id),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
);

-- =========================
-- СКЛАД
-- =========================
CREATE TABLE warehouse_transactions (
    id INT IDENTITY(1,1) PRIMARY KEY,
    material_id INT,
    transaction_type NVARCHAR(50),
    quantity INT,
    transaction_time DATETIME,
    FOREIGN KEY (material_id) REFERENCES materials(id)
);

-- =========================
-- ПРОДУКЦИЯ
-- =========================
CREATE TABLE products (
    id INT IDENTITY(1,1) PRIMARY KEY,
    article NVARCHAR(255),
    type NVARCHAR(255),
    name NVARCHAR(255),
    description NVARCHAR(MAX),
    image NVARCHAR(255),
    min_partner_price DECIMAL(18, 2),
    length DECIMAL(18, 2),
    width DECIMAL(18, 2),
    height DECIMAL(18, 2),
    net_weight DECIMAL(18, 2),
    gross_weight DECIMAL(18, 2),
    quality_certificate NVARCHAR(255),
    standard_number NVARCHAR(255),
    production_time NVARCHAR(50),
    cost_price DECIMAL(18, 2),
    workshop_number INT,
    production_people_count INT
);

CREATE TABLE product_min_price_history (
    id INT IDENTITY(1,1) PRIMARY KEY,
    product_id INT,
    price_data NVARCHAR(MAX),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE product_materials (
    id INT IDENTITY(1,1) PRIMARY KEY,
    product_id INT,
    material_id INT,
    quantity INT,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (material_id) REFERENCES materials(id)
);

-- =========================
-- ЗАЯВКИ
-- =========================
CREATE TABLE orders (
    id INT IDENTITY(1,1) PRIMARY KEY,
    partner_id INT,
    manager_id INT,
    status NVARCHAR(50),
    prepayment_received BIT,
    created_at DATETIME,
    FOREIGN KEY (partner_id) REFERENCES partners(id),
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

CREATE TABLE order_items (
    id INT IDENTITY(1,1) PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    cost DECIMAL(18, 2),
    production_date DATETIME,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);