import sqlite3
import pandas as pd

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

# Загрузка категорий
print("📦 Загрузка категорий...")
df = pd.read_excel('Resource/categories_import.xlsx')
for _, row in df.iterrows():
    cursor.execute('''
        INSERT OR IGNORE INTO categories (id, name, description)
        VALUES (?, ?, ?)
    ''', (row['ID'], row['CategoryName'], row['Description']))

# Загрузка производителей
print("🏭 Загрузка производителей...")
df = pd.read_excel('Resource/manufacturers_import.xlsx')
for _, row in df.iterrows():
    cursor.execute('''
        INSERT OR IGNORE INTO manufacturers (id, name, country, phone, email)
        VALUES (?, ?, ?, ?, ?)
    ''', (row['ID'], row['ManufacturerName'], row['Country'], row['Phone'], row['Email']))

# Загрузка лекарств
print("💊 Загрузка лекарств...")
df = pd.read_excel('Resource/medicines_import.xlsx')
for _, row in df.iterrows():
    cursor.execute('''
        INSERT OR IGNORE INTO medicines 
        (id, name, category_id, manufacturer_id, price, expiration_date, stock_quantity, season_coeff)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    ''', (row['ID'], row['MedicineName'], row['CategoryID'], row['ManufacturerID'],
          row['Price'], row['ExpirationDate'], row['StockQty'], row['SeasonCoeff']))

conn.commit()
conn.close()
print("✅ Данные загружены!")
input("Нажми Enter для выхода...")