56 lines
1.5 KiB
SQL
56 lines
1.5 KiB
SQL
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
|
|
);
|