import sqlite3
from datetime import datetime, timedelta

def seed_demo_data(conn):
    cursor = conn.cursor()
    print("Наполнение базы данных демо-данными...")

    # 1. Типы металлов
    metals = [
        ("Золото", 585, 3500.0),
        ("Золото", 750, 4500.0),
        ("Серебро", 925, 85.0),
        ("Платина", 950, 2800.0),
    ]
    cursor.executemany("INSERT INTO metal_types (name, purity, price_per_gram) VALUES (?, ?, ?)", metals)
    
    cursor.execute("SELECT id, price_per_gram, purity FROM metal_types")
    metal_data = cursor.fetchall()
    gold_585_id = metal_data[0][0]
    gold_750_id = metal_data[1][0]
    silver_id = metal_data[2][0]
    platinum_id = metal_data[3][0]

    # 2. Продукция
    products = [
        (gold_585_id, "RNG-001", "Кольцо 'Классическое'", 3.5, 1.2, ""),
        (gold_585_id, "RNG-002", "Кольцо 'Виктория'", 4.2, 1.5, "С бриллиантами"),
        (gold_585_id, "EAR-001", "Серьги 'Капля'", 2.8, 1.5, ""),
        (gold_750_id, "CHN-001", "Цепь 'Плетение'", 12.0, 1.0, "Длина 50 см"),
        (gold_750_id, "PNT-001", "Подвеска 'Сердце'", 3.2, 1.3, ""),
        (silver_id, "BRC-001", "Браслет 'Нежный'", 6.5, 1.3, ""),
        (silver_id, "EAR-002", "Серьги 'Цветок'", 3.0, 1.2, ""),
        (platinum_id, "RNG-003", "Кольцо 'Престиж'", 5.5, 2.0, "Эксклюзив"),
    ]
    
    for p in products:
        metal_row = next(m for m in metal_data if m[0] == p[0])
        price = (metal_row[1] * p[3]) * (metal_row[2] / 1000) * p[4]
        cursor.execute("""
            INSERT INTO products (metal_type_id, sku, name, weight_grams, complexity_factor, calculated_price, description)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        """, (p[0], p[1], p[2], p[3], p[4], round(price, 2), p[5]))

    # 3. Партнеры
    partners = [
        ("Ювелирный Дом 'Блеск'", "Иван Петров", "blisk@example.com", "+7 (495) 123-45-67", "Москва, ул. Цветная, д.1", "770123456789", 0),
        ("ИП Сидорова А.А.", "Анна Сидорова", "sidorova@example.com", "+7 (812) 765-43-21", "СПб, Невский пр., д.10", "781234567890", 0),
        ("Магазин 'Золотой Лотос'", "Сергей Иванов", "lotus@example.com", "+7 (383) 555-11-22", "Новосибирск, ул. Ленина, д.5", "540123456789", 0),
        ("Торговый Дом 'Алмаз'", "Елена Соколова", "almaz@example.com", "+7 (343) 222-33-44", "Екатеринбург, пр. Ленина, д.15", "660123456789", 0),
    ]
    cursor.executemany("""
        INSERT INTO partners (company_name, director_name, email, phone, legal_address, inn, total_sales)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    """, partners)

    # 4. Получаем ID для создания заявок
    cursor.execute("SELECT id, company_name FROM partners")
    partners_list = cursor.fetchall()
    partner1_id = partners_list[0][0]
    partner2_id = partners_list[1][0]
    partner3_id = partners_list[2][0]

    cursor.execute("SELECT id, calculated_price, name FROM products")
    products_list = cursor.fetchall()
    
    # 5. Создаем заявки
    orders_data = [
        (partner1_id, "ORD-001", datetime.now().strftime("%Y-%m-%d"), 0, 0, "Срочная поставка"),
        (partner2_id, "ORD-002", (datetime.now() - timedelta(days=5)).strftime("%Y-%m-%d"), 0, 0, ""),
        (partner3_id, "ORD-003", (datetime.now() - timedelta(days=15)).strftime("%Y-%m-%d"), 0, 0, "Пробная партия"),
    ]
    
    order_ids = []
    for o in orders_data:
        cursor.execute("""
            INSERT INTO orders (partner_id, order_number, order_date, discount_percent, total_amount, notes)
            VALUES (?, ?, ?, ?, ?, ?)
        """, o)
        order_ids.append(cursor.lastrowid)

    # 6. Позиции для заявок
    items_data = [
        # Заявка 1
        [(order_ids[0], products_list[0][0], 2, products_list[0][1], 2*products_list[0][1]),
         (order_ids[0], products_list[1][0], 1, products_list[1][1], 1*products_list[1][1])],
        # Заявка 2
        [(order_ids[1], products_list[2][0], 1, products_list[2][1], 1*products_list[2][1]),
         (order_ids[1], products_list[3][0], 3, products_list[3][1], 3*products_list[3][1])],
        # Заявка 3
        [(order_ids[2], products_list[4][0], 2, products_list[4][1], 2*products_list[4][1]),
         (order_ids[2], products_list[5][0], 1, products_list[5][1], 1*products_list[5][1])],
    ]
    
    for items in items_data:
        for item in items:
            cursor.execute("""
                INSERT INTO order_items (order_id, product_id, quantity, price_at_moment, line_total)
                VALUES (?, ?, ?, ?, ?)
            """, item)

    # 7. Обновляем суммы заявок и total_sales партнеров
    for i, order_id in enumerate(order_ids):
        cursor.execute("SELECT SUM(line_total) FROM order_items WHERE order_id = ?", (order_id,))
        total = cursor.fetchone()[0] or 0
        cursor.execute("UPDATE orders SET total_amount = ? WHERE id = ?", (total, order_id))
        
        partner_id = orders_data[i][0]
        cursor.execute("UPDATE partners SET total_sales = total_sales + ? WHERE id = ?", (total, partner_id))

    conn.commit()
    print("Демо-данные успешно загружены.")