import csv
import pymysql
import re
from datetime import datetime, timedelta

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

# --- Вспомогательные функции ---
def normalize_phone(phone):
    digits = re.sub(r'\D', '', phone)
    if digits.startswith('89'):
        digits = '7' + digits[1:]
    elif digits.startswith('9') and len(digits) == 10:
        digits = '7' + digits
    return digits if len(digits) >= 10 else None

def normalize_email(email):
    email = email.strip().lower()
    return email if re.match(r"[^@\s]+@[^@\s]+\.[^@\s]+", email) else None

def excel_date_to_iso(val):
    try:
        base = datetime(1899, 12, 30)
        return (base + timedelta(days=int(val))).date().isoformat()
    except:
        return None

def insert_or_get_id(table, column, value):
    cursor.execute(f"SELECT id FROM {table} WHERE {column} = %s", (value,))
    result = cursor.fetchone()
    if result:
        return result[0]
    cursor.execute(f"INSERT INTO {table} ({column}) VALUES (%s)", (value,))
    conn.commit()
    return cursor.lastrowid

def link_m2m(link_table, left_id_name, right_id_name, left_id, right_id):
    cursor.execute(f"REPLACE INTO {link_table} ({left_id_name}, {right_id_name}) VALUES (%s, %s)", (left_id, right_id))

# --- Загрузка CSV ---
with open('VB1.csv', newline='', encoding='utf-8-sig') as csvfile:
    reader = csv.DictReader(csvfile, delimiter=';')
    for i, row in enumerate(reader):
        company_name = row['Наименование'].strip()
        inn = row['ИНН'].strip()
        if not inn:
            continue

        ogrn = row['ОГРН'].strip()
        ogrn = '{:.0f}'.format(float(ogrn.replace(',', '.'))) if 'E+' in ogrn else ogrn
        company_reg_date = excel_date_to_iso(row['Дата регистрации']) if row['Дата регистрации'] else None
        address = row['Юридический адрес'].strip()
        region = row.get('Регион регистрации', '').strip()
        capital = row.get('Уставный капитал, RUB', '').replace(',', '').strip() or None
        revenue = row.get('2023, Выручка, RUB', '').replace(',', '').strip() or None
        profit = row.get('2023, Чистая прибыль (убыток), RUB', '').replace(',', '').strip() or None
        employees = row.get('2023, Среднесписочная численность работников', '').replace(',', '').strip() or None
        credit_limit = row.get('Кредитный лимит, RUB', '').replace(',', '').strip() or None

        activity = row.get('Вид деятельности/отрасль', '').strip()
        activity_code = row.get('Код основного вида деятельности', '').strip()

        cursor.execute("""
            INSERT INTO companies (name, inn, ogrn, registration_date, address, region, authorized_capital, revenue_2023, profit_2023, employees_count, credit_limit)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE
                name = IF(VALUES(name) != '' AND VALUES(name) IS NOT NULL, VALUES(name), name),
                ogrn = IF(VALUES(ogrn) != '' AND VALUES(ogrn) IS NOT NULL, VALUES(ogrn), ogrn),
                registration_date = IF(VALUES(registration_date) IS NOT NULL, VALUES(registration_date), registration_date),
                address = IF(VALUES(address) != '' AND VALUES(address) IS NOT NULL, VALUES(address), address),
                region = IF(VALUES(region) != '' AND VALUES(region) IS NOT NULL, VALUES(region), region),
                authorized_capital = IF(VALUES(authorized_capital) IS NOT NULL, VALUES(authorized_capital), authorized_capital),
                revenue_2023 = IF(VALUES(revenue_2023) IS NOT NULL, VALUES(revenue_2023), revenue_2023),
                profit_2023 = IF(VALUES(profit_2023) IS NOT NULL, VALUES(profit_2023), profit_2023),
                employees_count = IF(VALUES(employees_count) IS NOT NULL, VALUES(employees_count), employees_count),
                credit_limit = IF(VALUES(credit_limit) IS NOT NULL, VALUES(credit_limit), credit_limit),
                id=LAST_INSERT_ID(id)
        """, (company_name, inn, ogrn, company_reg_date, address, region, capital, revenue, profit, employees, credit_limit))
        company_id = cursor.lastrowid

        # --- ОКВЭД ---
        if activity_code:
            okved_id = insert_or_get_id('okved', 'code', activity_code)
            cursor.execute("UPDATE okved SET description = %s WHERE id = %s", (activity, okved_id))
            link_m2m('company_okveds', 'company_id', 'okved_id', company_id, okved_id)

        if i % 50 == 0:
            print(f"Обработано строк: {i}")
            conn.commit()

    conn.commit()
    print("Импорт завершен!")