import sqlite3

conn = sqlite3.connect('pharmacy.db')
cursor = conn.cursor()

# Категории лекарств
cursor.execute('''
CREATE TABLE IF NOT EXISTS categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    description TEXT
)
''')

# Производители
cursor.execute('''
CREATE TABLE IF NOT EXISTS manufacturers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    country TEXT,
    phone TEXT,
    email TEXT UNIQUE
)
''')

# Лекарства
cursor.execute('''
CREATE TABLE IF NOT EXISTS medicines (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    category_id INTEGER,
    manufacturer_id INTEGER,
    price REAL NOT NULL,
    expiration_date DATE NOT NULL,
    stock_quantity INTEGER DEFAULT 0,
    season_coeff REAL DEFAULT 1.0,
    FOREIGN KEY (category_id) REFERENCES categories(id),
    FOREIGN KEY (manufacturer_id) REFERENCES manufacturers(id)
)
''')

# Заказы (для истории продаж)
cursor.execute('''
CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    medicine_id INTEGER,
    quantity INTEGER NOT NULL,
    order_date DATE NOT NULL,
    total_price REAL,
    FOREIGN KEY (medicine_id) REFERENCES medicines(id)
)
''')

print("✅ Таблицы созданы")
conn.commit()
conn.close()