import pandas as pd
import mysql.connector

# Dane do logowania (Twoje już wpisane)
db_config = {
    "host": "serwer168851.systemopiekunszkody.pl",
    "user": "serwer168851_systemopiekunszkody",
    "password": "Przyjemnaty2023!",
    "database": "systemopiekunszkody"
}

def generate_sprawa_id(index, rok="2025"):
    return f"SP{rok}{str(index + 1).zfill(5)}"

def import_excel_to_mysql(excel_path):
    df = pd.read_excel(excel_path, sheet_name="Harmonogram & Przebieg najmu")

    conn = mysql.connector.connect(**db_config)
    cursor = conn.cursor()

    for index, row in df.iterrows():
        id_sprawy = generate_sprawa_id(index)
        opis = row.get("opis_szkody", "")
        harmonogram = row.get("harmonogram", "")
        
        row_data = row.drop(labels=["opis_szkody", "harmonogram"], errors="ignore")
        data_dict = row_data.to_dict()

        columns = ", ".join(f"`{col}`" for col in data_dict.keys())
        placeholders = ", ".join(["%s"] * len(data_dict))
        values = list(data_dict.values())

        columns += ", id_sprawy, opis_szkody, harmonogram"
        placeholders += ", %s, %s, %s"
        values.extend([id_sprawy, opis, harmonogram])

        query = f"INSERT INTO sprawy ({columns}) VALUES ({placeholders})"
        cursor.execute(query, values)

    conn.commit()
    cursor.close()
    conn.close()
    print(f"Zaimportowano {len(df)} spraw do bazy.")import_excel_to_mysql(r"C:\Users\Lenovo\Desktop\dane.xlsx")