import sqlite3
import pandas as pd
import os
import re

def extract_price(price_str):
    """Извлекает число из строки с ценой"""
    if pd.isna(price_str):
        return 0
    # Ищем число в строке (может быть "500 руб." или "500")
    numbers = re.findall(r'\d+', str(price_str))
    return int(numbers[0]) if numbers else 0

def find_column(columns, possible_names):
    """Ищет колонку по возможным названиям"""
    for col in columns:
        col_lower = col.lower()
        for name in possible_names:
            if name in col_lower:
                return col
    return columns[0]  # возвращаем первую колонку если ничего не найдено

def load_from_excel_to_db():
    """Загрузка данных из Excel файлов в базу данных"""
    
    # Подключаемся к БД
    conn = sqlite3.connect('horse_farm.db')
    cursor = conn.cursor()
    
    # Очищаем таблицы перед загрузкой
    cursor.execute("DELETE FROM services")
    cursor.execute("DELETE FROM abonements")
    cursor.execute("DELETE FROM certificates")
    
    # Сбрасываем автоинкремент
    cursor.execute("DELETE FROM sqlite_sequence WHERE name='services'")
    cursor.execute("DELETE FROM sqlite_sequence WHERE name='abonements'")
    cursor.execute("DELETE FROM sqlite_sequence WHERE name='certificates'")
    
    # Загрузка услуг из Excel
    services_file = 'resource/services.xlsx'
    if os.path.exists(services_file):
        df_services = pd.read_excel(services_file)
        print(f"Загружаем услуги из {services_file}")
        print(f"Колонки: {list(df_services.columns)}")
        
        # Определяем колонки
        name_col = find_column(df_services.columns, ['name', 'название', 'имя'])
        desc_col = find_column(df_services.columns, ['desc', 'опис', 'описание'])
        price_col = find_column(df_services.columns, ['price', 'цен', 'стоим'])
        duration_col = find_column(df_services.columns, ['duration', 'длит', 'врем'])
        
        print(f"Используем колонки: name='{name_col}', description='{desc_col}', price='{price_col}', duration='{duration_col}'")
        
        for _, row in df_services.iterrows():
            price = extract_price(row[price_col])
            
            cursor.execute('''
                INSERT INTO services (name, description, price, duration)
                VALUES (?, ?, ?, ?)
            ''', (
                str(row[name_col]), 
                str(row[desc_col]) if pd.notna(row[desc_col]) else '', 
                price,
                str(row[duration_col]) if pd.notna(row[duration_col]) else ''
            ))
        print(f"Загружено {len(df_services)} услуг")
    else:
        print(f"Файл {services_file} не найден")
    
    # Загрузка абонементов из Excel
    abonements_file = 'resource/abonements.xlsx'
    if os.path.exists(abonements_file):
        df_abonements = pd.read_excel(abonements_file)
        print(f"\nЗагружаем абонементы из {abonements_file}")
        print(f"Колонки: {list(df_abonements.columns)}")
        
        # Определяем колонки
        name_col = find_column(df_abonements.columns, ['name', 'название', 'имя'])
        price_col = find_column(df_abonements.columns, ['price', 'цен', 'стоим'])
        desc_col = find_column(df_abonements.columns, ['desc', 'опис', 'описание'])
        
        print(f"Используем колонки: name='{name_col}', price='{price_col}', description='{desc_col}'")
        
        for _, row in df_abonements.iterrows():
            price = extract_price(row[price_col])
            
            cursor.execute('''
                INSERT INTO abonements (name, price, description)
                VALUES (?, ?, ?)
            ''', (
                str(row[name_col]), 
                price,
                str(row[desc_col]) if pd.notna(row[desc_col]) else ''
            ))
        print(f"Загружено {len(df_abonements)} абонементов")
    else:
        print(f"Файл {abonements_file} не найден")
    
    # Загрузка сертификатов из Excel
    certificates_file = 'resource/certificates.xlsx'
    if os.path.exists(certificates_file):
        df_certificates = pd.read_excel(certificates_file)
        print(f"\nЗагружаем сертификаты из {certificates_file}")
        print(f"Колонки: {list(df_certificates.columns)}")
        
        # Определяем колонки
        name_col = find_column(df_certificates.columns, ['name', 'название', 'имя'])
        price_col = find_column(df_certificates.columns, ['price', 'цен', 'стоим'])
        desc_col = find_column(df_certificates.columns, ['desc', 'опис', 'описание'])
        
        print(f"Используем колонки: name='{name_col}', price='{price_col}', description='{desc_col}'")
        
        for _, row in df_certificates.iterrows():
            price = extract_price(row[price_col])
            
            cursor.execute('''
                INSERT INTO certificates (name, price, description)
                VALUES (?, ?, ?)
            ''', (
                str(row[name_col]), 
                price,
                str(row[desc_col]) if pd.notna(row[desc_col]) else ''
            ))
        print(f"Загружено {len(df_certificates)} сертификатов")
    else:
        print(f"Файл {certificates_file} не найден")
    
    conn.commit()
    conn.close()
    print("\nЗагрузка данных из Excel завершена!")

if __name__ == '__main__':
    load_from_excel_to_db()