import csv
import pymysql

# --- Настройки подключения к БД ---
conn = pymysql.connect(
    host='localhost',
    user='b2b_leads',
    password='v01lEHeGIdJUXSjQ',
    database='b2b_leads',
    charset='utf8mb4'
)
cursor = conn.cursor()

# --- Чтение ИНН из файла ---
inn_list = set()
with open('2_5471886698445039350.csv', newline='', encoding='utf-8-sig') as f:
    reader = csv.reader(f)
    for row in reader:
        if row:
            inn_list.add(row[0].strip())

# --- Подготовка к экспорту ---
with open('exported_sellers.csv', 'w', newline='', encoding='utf-8-sig') as f:
    writer = csv.writer(f)
    writer.writerow([
        'ИНН', 'Компания', 'Адрес', 'Регион', 'ОГРН', 'Дата регистрации',
        'Капитал', 'Выручка', 'Прибыль',
        'ОКВЭДы', 'Контакты (ФИО)', 'Должности', 'Телефоны', 'Emails', 'Вебсайты',
        'WB URL', 'Рейтинг', 'Отзывы', 'Продажи'
    ])

    for i, inn in enumerate(inn_list):
        cursor.execute("SELECT id, name, address, region, ogrn, registration_date, authorized_capital, revenue_2023, profit_2023 FROM companies WHERE inn = %s", (inn,))
        company = cursor.fetchone()
        if not company:
            continue

        company_id = company[0]

        # --- ОКВЭДы ---
        cursor.execute("""
            SELECT o.code, o.description FROM okved o
            JOIN company_okveds co ON co.okved_id = o.id
            WHERE co.company_id = %s
        """, (company_id,))
        okveds = ['{} {}'.format(code, desc) for code, desc in cursor.fetchall()]

        # --- Контакты ---
        cursor.execute("SELECT id, name, position FROM contacts WHERE entity_type = 'company' AND entity_id = %s", (company_id,))
        contacts = cursor.fetchall()
        contact_names = []
        contact_positions = []
        phone_numbers = []
        email_list = []
        for contact in contacts:
            contact_id, name, position = contact
            contact_names.append(name or '')
            contact_positions.append(position or '')

            cursor.execute("""
                SELECT p.number FROM phones p
                JOIN contact_phones cp ON cp.phone_id = p.id
                WHERE cp.contact_id = %s
            """, (contact_id,))
            phone_numbers += [p[0] for p in cursor.fetchall()]

            cursor.execute("""
                SELECT e.email FROM emails e
                JOIN contact_emails ce ON ce.email_id = e.id
                WHERE ce.contact_id = %s
            """, (contact_id,))
            email_list += [e[0] for e in cursor.fetchall()]

        # --- Вебсайты ---
        cursor.execute("SELECT url FROM company_websites WHERE company_id = %s", (company_id,))
        websites = [r[0] for r in cursor.fetchall()]

        # --- Продавец ---
        cursor.execute("SELECT wb_url, rating, reviews_count, products_sold FROM marketplace_sellers WHERE company_id = %s", (company_id,))
        seller = cursor.fetchone() or ('', '', '', '')

        # --- Запись строки ---
        writer.writerow([
            inn,
            company[1], company[2], company[3], company[4], company[5],
            company[6], company[7], company[8],
            ', '.join(okveds),
            ', '.join(contact_names), ', '.join(contact_positions),
            ', '.join(phone_numbers), ', '.join(email_list), ', '.join(websites),
            seller[0], seller[1], seller[2], seller[3]
        ])

        if i % 100 == 0:
            print(f"Обработано ИНН: {i}/{len(inn_list)}")

print("Готово. Список продавцов экспортирован в exported_sellers.csv")