PRAGMA foreign_keys = ON;

-- 1. Типы металлов
CREATE TABLE metal_types (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    purity INTEGER NOT NULL,
    price_per_gram REAL NOT NULL
);

-- 2. Продукция
CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    metal_type_id INTEGER NOT NULL REFERENCES metal_types(id),
    sku TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    weight_grams REAL NOT NULL,
    complexity_factor REAL NOT NULL DEFAULT 1.0,
    calculated_price REAL NOT NULL,
    description TEXT,
    is_active INTEGER NOT NULL DEFAULT 1
);

-- 3. Партнеры
CREATE TABLE partners (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    company_name TEXT NOT NULL,
    director_name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    phone TEXT NOT NULL,
    legal_address TEXT NOT NULL,
    inn TEXT NOT NULL UNIQUE,
    total_sales REAL NOT NULL DEFAULT 0
);

-- 4. Заявки
CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    partner_id INTEGER NOT NULL REFERENCES partners(id),
    order_number TEXT NOT NULL UNIQUE,
    order_date TEXT NOT NULL,
    discount_percent REAL NOT NULL,
    total_amount REAL NOT NULL,
    notes TEXT
);

-- 5. Позиции заявки
CREATE TABLE order_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id),
    quantity REAL NOT NULL,
    price_at_moment REAL NOT NULL,
    line_total REAL NOT NULL
);